Практическая работа "Создание SQL-запросов"

Практическая работа в  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;

1. create table city ... создает таблицу городов из двух столбцов — id и name.
2. insert into city ... заполняет таблицу данными (не менее 5).
3. 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?

Ответ: CREATE TABLE customer(name);

С помощью команды .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?

Ответ: .timer on|off

Чтобы не добавлять города вручную, возьмем готовый набор данных — 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?

Ответ. .mode csv

Выяснить ответ поможет .help import

Продолжаем работу на сайте: https://stepik.org/lesson/468979/step/1?unit=672179

Скачано с www.znanio.ru