Практическая работа в SQLite
Создадим новую базу данных:
sqlite3 sample.db
sqlite3 sample.db создает пустую базу данных в файле sample.db и запускает утилиту SQLite (в Windows вместо sqlite3 будет sqlite3.exe). Через утилиту мы управляем базой и делаем запросы. Если файл sample.db уже существует — SQLite откроет его вместо того, чтобы создавать новую базу.
Поменяем умолчательные настройки:
.mode column
.headers on
.mode column и .headers on настраивают режим отрисовки таблиц. .mode и .headers — специальные команды SQLite, не часть языка SQL. Специальные команды всегда начинаются с точки, чтобы проще было отличать от обычных SQL-инструкций.
Создадим таблицу, добавим и запросим данные:
create table city (id integer primary key, name text);
insert into city (name) values ('Москва'), ('Самара'), ('Ростов');
select * from city;
create table city ... создает таблицу городов из двух столбцов — id и name.
insert into city ... заполняет таблицу данными (не менее 5).
select * from city выбирает данные из таблицы и показывает результат.
Результат:
id name
---------- ----------
1 Москва
2 Самара
3 Ростов
И выйдем из SQLite:
.exit
Пришло время поработать с SQLite в песочнице!
Песочница — это SQLite, который работает прямо в браузере:
Создайте таблицу по образцу:
Выполните в песочнице команды:
create table customer(name);
select *
from customer;
.schema customer
Вот что здесь происходит:
Первая команда (create table) создает таблицу customer с единственным столбцом name.
Вторая команда (select) показывает содержимое таблицы customer (она пустая).
Третья команда (.schema) показывает список и структуру всех таблиц в базе.
create и select — это SQL-запросы, часть стандарта SQL. Запрос может занимать несколько строк, а в конце всегда ставится точка с запятой.
.schema — это специальная команда SQLite, не часть стандарта SQL. Специальные команды всегда начинаются с точки, занимают ровно одну строку, а точку запятой в конце ставить не надо.
Вопрос: Что вернула команда .schema?
Ответ:
С помощью команды .help найдите в песочнице команду, которая отвечает за вывод времени выполнения запроса. Если ее включить, в результатах запроса добавится строчка:
Run Time: real XXX user XXX sys XXX
Например:
.SOMETHING on
select 'Hello, world!' as message;
-- ...
-- Run Time: real 0.000 user 0.000106 sys 0.000069
Вопрос: Какая команда должна быть вместо SOMETHING?
Ответ:
Чтобы не добавлять города вручную, возьмем готовый набор данных — city.csv (⬇скачать). Скачаем файл и загрузим данные:
sqlite3 city-1.db
.mode box
.import --csv city.csv city
select count(*) from city;
┌──────────┐
│ count(*) │
├──────────┤
│ 1117 │
└──────────┘
Команда .import автоматически создала таблицу city со всеми столбцами из city.csv и загрузила данные из файла. Неплохо!
Посмотрим, какие столбцы есть в таблице:
.schema city
CREATE TABLE city(
"address" TEXT,
"postal_code" TEXT,
"country" TEXT,
"federal_district" TEXT,
"region_type" TEXT,
"region" TEXT,
"area_type" TEXT,
"area" TEXT,
"city_type" TEXT,
"city" TEXT,
"settlement_type" TEXT,
"settlement" TEXT,
"kladr_id" TEXT,
"fias_id" TEXT,
"fias_level" TEXT,
"capital_marker" TEXT,
"okato" TEXT,
"oktmo" TEXT,
"tax_office" TEXT,
"timezone" TEXT,
"geo_lat" TEXT,
"geo_lon" TEXT,
"population" TEXT,
"foundation_year" TEXT
);
И взглянем на содержимое:
select federal_district, city, population
from city limit 10;
┌──────────────────┬───────────────┬────────────┐
│ federal_district │ city │ population │
├──────────────────┼───────────────┼────────────┤
│ Южный │ Адыгейск │ 12689 │
│ Южный │ Майкоп │ 144055 │
│ Сибирский │ Горно-Алтайск │ 62861 │
│ Сибирский │ Алейск │ 28528 │
│ Сибирский │ Барнаул │ 635585 │
│ Сибирский │ Белокуриха │ 15072 │
│ Сибирский │ Бийск │ 203826 │
│ Сибирский │ Горняк │ 13040 │
│ Сибирский │ Заринск │ 47035 │
│ Сибирский │ Змеиногорск │ 10569 │
└──────────────────┴───────────────┴────────────┘
Условие limit 10 указывает, что вернется не более 10 записей. Рекомендую всегда использовать limit, если запрос может вернуть много записей.
Загрузите файл city.csv:
.import --csv city.csv city
Затем выполните такой запрос:
select max(length(city)) from city;
Вопрос: Какое число он вернул? Ответ:
Загрузите файл city.csv с помощью команды .import, но без использования опции --csv. Способ не самый очевидный, так что его поиск поможет вам лучше освоиться с командной строкой SQLite.
Всего у вас получится две команды:
do_something
.import city.csv city
Вопрос: Какая команда должна быть вместо do_something? Ответ.
Выяснить ответ поможет .help import
Продолжаем работу на сайте: https://stepik.org/lesson/468979/step/1?unit=672179
