SQl полный гайд с запросами и примерами 2024

Публикую шпаргалку по SQL, которая долгое время помогала мне, да и сейчас я периодически в неё заглядываю.

Все примеры изначально писались для СУБД SQLite, но почти всё из этого применимо также и к другим СУБД.

Вначале идут очень простые запросы, с них можно начать новичкам. Если хочется чего-то более интересного — листайте вниз. Здесь есть и примеры довольно сложных запросов с агрегирующими функциями, триггерами, длинными подзапросами, с оконными функциями. Помимо этого, часть примеров посвящена работе с SQL в Python, используя sqlite3, pandas, polars. Этот список запросов с комментариями можно использовать как наглядное пособие для изучения SQL.

Большинство советов я публиковал в своем канале по анализу данных, где вы найдете большое количество советов, инструментов и примеров с кодом. А здесь большая полезная папка, которую я собрал в которой куча полезного для работы с данными.

Кстати, все эти примеры SQL заботливо собраны в одном архиве, вы можете скачать его и экспериментировать локально. После скачивания и разархивирования, у вас будет 3 группы файлов.

SQl полный гайд с запросами и примерами 2024

SELECT * FROM little_penguins; Adelie|Dream|37.2|18.1|178|3900|MALE Adelie|Dream|37.6|19.3|181|3300|FEMALE Gentoo|Biscoe|50|15.3|220|5550|MALE Adelie|Torgersen|37.3|20.5|199|3775|MALE Adelie|Biscoe|39.6|17.7|186|3500|FEMALE Gentoo|Biscoe|47.7|15|216|4750|FEMALE Adelie|Dream|36.5|18|182|3150|FEMALE Gentoo|Biscoe|42|13.5|210|4150|FEMALE Adelie|Torgersen|42.1|19.1|195|4000|MALE Gentoo|Biscoe|54.3|15.7|231|5650|MALE

ничего особенного, выбираем все записи из таблички little_penguins

src/admin_commands.sql

.headers on .mode markdown SELECT * FROM little_penguins;

out/admin_commands.out

| species | island | bill_length_mm | bill_depth_mm | flipper_length_mm | body_mass_g | sex | |---------|-----------|----------------|---------------|-------------------|-------------|--------| | Adelie | Dream | 37.2 | 18.1 | 178 | 3900 | MALE | | Adelie | Dream | 37.6 | 19.3 | 181 | 3300 | FEMALE | | Gentoo | Biscoe | 50 | 15.3 | 220 | 5550 | MALE | | Adelie | Torgersen | 37.3 | 20.5 | 199 | 3775 | MALE | | Adelie | Biscoe | 39.6 | 17.7 | 186 | 3500 | FEMALE | | Gentoo | Biscoe | 47.7 | 15 | 216 | 4750 | FEMALE | | Adelie | Dream | 36.5 | 18 | 182 | 3150 | FEMALE | | Gentoo | Biscoe | 42 | 13.5 | 210 | 4150 | FEMALE | | Adelie | Torgersen | 42.1 | 19.1 | 195 | 4000 | MALE | | Gentoo | Biscoe | 54.3 | 15.7 | 231 | 5650 | MALE |

включаем заголовки и режим markdown; в SQLite подобные команды начинаются с ., а в PostgreSQL с \
кстати, для просмотра дополнительной инфы или чтобы узнать, какие команды есть, используйте .help

src/specify_columns.sql

SELECT species, island, sex FROM little_penguins;

out/specify_columns.out

| species | island | sex | |---------|-----------|--------| | Adelie | Dream | MALE | | Adelie | Dream | FEMALE | | Gentoo | Biscoe | MALE | | Adelie | Torgersen | MALE | | Adelie | Biscoe | FEMALE | | Gentoo | Biscoe | FEMALE | | Adelie | Dream | FEMALE | | Gentoo | Biscoe | FEMALE | | Adelie | Torgersen | MALE | | Gentoo | Biscoe | MALE |

выбираем колонки species, island, sex из таблички little_penguins

src/sort.sql

SELECT species, sex, island FROM little_penguins ORDER BY island ASC, sex DESC;

out/sort.out

| species | sex | island | |---------|--------|-----------| | Gentoo | MALE | Biscoe | | Gentoo | MALE | Biscoe | | Adelie | FEMALE | Biscoe | | Gentoo | FEMALE | Biscoe | | Gentoo | FEMALE | Biscoe | | Adelie | MALE | Dream | | Adelie | FEMALE | Dream | | Adelie | FEMALE | Dream | | Adelie | MALE | Torgersen | | Adelie | MALE | Torgersen |

выбираем столбцы species, island, sex из таблички little_penguins
сортируем все значения из island в возрастающем порядке (от A к Z)
строки с одинаковыми значениями island дополнительно сортируем по их значениям sex в обратном порядке, от большего к меньшему (от Z к A)

Full dataset has 344 rows

src/limit.sql

SELECT species, sex, island FROM penguins ORDER BY species, sex, island LIMIT 10;

out/limit.out

| species | sex | island | |---------|--------|-----------| | Adelie | | Dream | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe |

выбираем столбцы species, sex, island из таблички penguins
сортируем по species в порядке возрастания, строки с одинаковым значением species сортируются по sex, с одинаковым sex дополнительно сортируются по island
ну и выводим только первые 10 строк

src/page.sql

SELECT species, sex, island FROM penguins ORDER BY species, sex, island LIMIT 10 OFFSET 3;

out/page.out

| species | sex | island | |---------|--------|-----------| | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe | | Adelie | FEMALE | Biscoe |

OFFSET указывается после LIMIT и позволяет пропустить сколько-то первых строк, в данном случае пропущены 3 первых строки

src/distinct.sql

SELECT DISTINCT species, sex, island FROM penguins;

out/distinct.out

| species | sex | island | |-----------|--------|-----------| | Adelie | MALE | Torgersen | | Adelie | FEMALE | Torgersen | | Adelie | | Torgersen | | Adelie | FEMALE | Biscoe | | Adelie | MALE | Biscoe | | Adelie | FEMALE | Dream | | Adelie | MALE | Dream | | Adelie | | Dream | | Chinstrap | FEMALE | Dream | | Chinstrap | MALE | Dream | | Gentoo | FEMALE | Biscoe | | Gentoo | MALE | Biscoe | | Gentoo | | Biscoe |

SELECT DISTINCT — выбираем уникальные комбинации из столбцов species, sex, island

src/filter.sql

SELECT DISTINCT species, sex, island FROM penguins WHERE island = 'Biscoe';

out/filter.out

| species | sex | island | |---------|--------|--------| | Adelie | FEMALE | Biscoe | | Adelie | MALE | Biscoe | | Gentoo | FEMALE | Biscoe | | Gentoo | MALE | Biscoe | | Gentoo | | Biscoe |

выбираем уникальные комбинации значений species, sex, island из penguins, где значения поля island равно Biscoe

src/filter_and.sql

SELECT DISTINCT species, sex, island FROM penguins WHERE island = 'Biscoe' AND sex != 'MALE';

out/filter_and.out

| species | sex | island | |---------|--------|--------| | Adelie | FEMALE | Biscoe | | Gentoo | FEMALE | Biscoe |

выбираем уникальные комбинации значений species, sex, island из penguins, где значения поля island равно Biscoe, а значения поля sex не равно MALE

src/calculations.sql

SELECT flipper_length_mm / 10.0, body_mass_g / 1000.0 FROM penguins LIMIT 3;

out/calculations.out

| flipper_length_mm / 10.0 | body_mass_g / 1000.0 | |--------------------------|----------------------| | 18.1 | 3.75 | | 18.6 | 3.8 | | 19.5 | 3.25 |

выводим 3 первых строки значений flipper_length_mm, делённых на 10.0, и значений body_mass_g, делённых на 1000.0

src/rename_columns.sql

SELECT flipper_length_mm / 10.0 AS flipper_cm, body_mass_g / 1000.0 AS weight_kg, island AS where_found FROM penguins LIMIT 3;

out/rename_columns.out

| flipper_cm | weight_kg | where_found | |------------|-----------|-------------| | 18.1 | 3.75 | Torgersen | | 18.6 | 3.8 | Torgersen | | 19.5 | 3.25 | Torgersen |

делим значения flipper_length_mm на 10.0, делим значения body_mass_g на 1000.0
переименовываем столбцы flipper_length_mm — в flipper_cm, body_mass_g — в weight_kg, island — в where_found
выводим первые 3 строки

Взаимосвязь рассмотренных понятий SQL можно показать так:

concept map: selection

src/show_missing_values.sql

SELECT flipper_length_mm / 10.0 AS flipper_cm, body_mass_g / 1000.0 AS weight_kg, island AS where_found FROM penguins LIMIT 5;

out/show_missing_values.out

| flipper_cm | weight_kg | where_found | |------------|-----------|-------------| | 18.1 | 3.75 | Torgersen | | 18.6 | 3.8 | Torgersen | | 19.5 | 3.25 | Torgersen | | | | Torgersen | | 19.3 | 3.45 | Torgersen |

делим значения из flipper_length_mm на 10, затем присваиваем результаты новому столбцу flipper_cm
делим значения из столбца body_mass_g на 1000 и затем присваивание результатов новому столбцу weight_kg
переименовываем island в where_found

Repeated from above so it doesn’t count against our query limit

src/filter.sql

SELECT DISTINCT species, sex, island FROM penguins WHERE island = 'Biscoe';

out/filter.out

| species | sex | island | |---------|--------|--------| | Adelie | FEMALE | Biscoe | | Adelie | MALE | Biscoe | | Gentoo | FEMALE | Biscoe | | Gentoo | MALE | Biscoe | | Gentoo | | Biscoe |

выбираем столбцы species, sex, island
выводим все записи из penguins, где значение island равно 'Biscoe'

src/null_equality.sql

SELECT DISTINCT species, sex, island FROM penguins WHERE island = 'Biscoe' AND sex = 'FEMALE';

out/null_equality.out

| species | sex | island | |---------|--------|--------| | Adelie | FEMALE | Biscoe | | Gentoo | FEMALE | Biscoe |

выводим все записи из penguins, где значение island равно 'Biscoe' и значение sex равно 'FEMALE'

условие с оператором отрицания != тоже без проблем работает

src/null_inequality.sql

SELECT DISTINCT species, sex, island FROM penguins WHERE island = 'Biscoe' AND sex != 'FEMALE';

out/null_inequality.out

| species | sex | island | |---------|------|--------| | Adelie | MALE | Biscoe | | Gentoo | MALE | Biscoe |

src/safe_null_equality.sql

SELECT species, sex, island FROM penguins WHERE sex IS NULL;

out/safe_null_equality.out

| species | sex | island | |---------|-----|-----------| | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Torgersen | | Adelie | | Dream | | Gentoo | | Biscoe | | Gentoo | | Biscoe | | Gentoo | | Biscoe | | Gentoo | | Biscoe | | Gentoo | | Biscoe |

выбираем строки со значениями species, sex, island из таблички penguins, где значения sex нет (NULL)

Вот так можно показать связь понятий SQL, которые мы рассмотрели выше:

concept map: null

src/simple_sum.sql

SELECT sum(body_mass_g) AS total_mass FROM penguins;

out/simple_sum.out

| total_mass | |------------| | 1437000 |

суммируем все значения колонки body_mass_g, сохраняем в новый столбец total_mass

src/common_aggregations.sql

SELECT MAX(bill_length_mm) AS longest_bill, MIN(flipper_length_mm) AS shortest_flipper, AVG(bill_length_mm) / AVG(bill_depth_mm) AS weird_ratio FROM penguins;

out/common_aggregations.out

| longest_bill | shortest_flipper | weird_ratio | |--------------|------------------|------------------| | 59.6 | 172 | 2.56087082530644 |

находим максимальное значение из столбца bill_length_mm, записываем это значение как longest_bill
аналогично находим минимальное из flipper_length_mm, находим среднее из bill_length_mm, среднее из bill_depth_mm

src/count_behavior.sql

SELECT COUNT(*) AS count_star, COUNT(sex) AS count_specific, COUNT(DISTINCT sex) AS count_distinct FROM penguins;

out/count_behavior.out

| count_star | count_specific | count_distinct | |------------|----------------|----------------| | 344 | 333 | 2 |

COUNT(*) — считаем все значения из count_star
COUNT(sex) — считаем все значения из столбца sex
COUNT(DISTINCT sex) — считаем уникальные значения из sex (очевидно их 2: MALE, FEMALE)
записываем эти 3 числа как count_star, count_specific, count_distinct соответственно

src/simple_group.sql

SELECT AVG(body_mass_g) AS average_mass_g FROM penguins GROUP BY sex;

out/simple_group.out

| average_mass_g | |------------------| | 4005.55555555556 | | 3862.27272727273 | | 4545.68452380952 |

из таблички penguins находим среднее всех значений body_mass_g, сохраняем как average_mass_g
группируем по значениям sex (группы FEMALE, MALE, NULL)

src/unaggregated_columns.sql

SELECT sex, AVG(body_mass_g) AS average_mass_g FROM penguins GROUP BY sex;

out/unaggregated_columns.out

| sex | average_mass_g | |--------|------------------| | | 4005.55555555556 | | FEMALE | 3862.27272727273 | | MALE | 4545.68452380952 |

для того, чтобы было видно названия отдельных групп, выбираем не только среднее AVG(body_mass_g), но и sex
видим 3 группы: NULL, FEMALE, MALE

src/arbitrary_in_aggregation.sql

SELECT sex, body_mass_g FROM penguins GROUP BY sex;

out/arbitrary_in_aggregation.out

| sex | body_mass_g | |--------|-------------| | | | | FEMALE | 3800 | | MALE | 3750 |

здесь у нас популярная ошибка, мы просто выбираем body_mass_g, а не находим среднее, поэтому SQL выбирает любые значения из body_mass_g. Аккуратнее)

src/filter_aggregation.sql

SELECT sex, AVG(body_mass_g) AS average_mass_g FROM penguins GROUP BY sex HAVING average_mass_g > 4000.0;

out/filter_aggregation.out

| sex | average_mass_g | |------|------------------| | | 4005.55555555556 | | MALE | 4545.68452380952 |

здесь мы используем HAVING вместо WHERE (эффект тот же самый), оставляем только те значения из average_mass_g, которые больше 4000

src/readable_aggregation.sql

SELECT sex, ROUND(AVG(body_mass_g), 1) AS average_mass_g FROM penguins GROUP BY sex HAVING average_mass_g > 4000.0;

out/readable_aggregation.out

| sex | average_mass_g | |------|----------------| | | 4005.6 | | MALE | 4545.7 |

округляем среднее AVG(body_mass_g до 1 знака после запятой, используя ROUND

src/filter_aggregate_inputs.sql

SELECT sex, ROUND( AVG(body_mass_g) FILTER (WHERE body_mass_g < 4000.0), 1) AS average_mass_g FROM penguins GROUP BY sex;

out/filter_aggregate_inputs.out

| sex | average_mass_g | |--------|----------------| | | 3362.5 | | FEMALE | 3417.3 | | MALE | 3752.5 |

при помощи FILTER мы находим среднее только тех значений body_mass_g, которые меньше 4000
округляем до 1 знака после запятой, сохраняем в столбец average_mass_g
группируем по sex

Вот так выглядит связь основных понятий, которые мы только что обсуждали:

concept map: aggregation

Кстати, вот так выглядит создание БД в оперативной памяти:

src/in_memory_db.sh

sqlite3 :memory:

запускаем интерактивную оболочку SQLite, создаём новую базу данных в оперативной памяти для более быстрой работы

src/create_work_job.sql

CREATE TABLE job (name text NOT NULL, billable real NOT NULL); CREATE TABLE work (person text NOT NULL, job text NOT NULL);

создаём таблицу job со столбцами: name — столбец текстовых значений, не может быть пустым (NOT NULL), billable — содержит вещественные числа, не может быть пустым
создаём табличку work со столбцами: person — текстовый, не может быть пустым, job — текстовый, не может быть пустым

src/populate_work_job.sql

INSERT INTO job VALUES ('calibrate', 1.5), ('clean', 0.5); INSERT INTO work VALUES ('mik', 'calibrate'), ('mik', 'clean'), ('mik', 'complain'), ('po', 'clean'), ('po', 'complain'), ('tay', 'complain');

out/insert_values.out

| name | billable | |-----------|----------| | calibrate | 1.5 | | clean | 0.5 | | person | job | |--------|-----------| | mik | calibrate | | mik | clean | | mik | complain | | po | clean | | po | complain | | tay | complain |

ничего особенного, заполняем табличку job парами name–billable, и так же заполняем табличку work парами person–job

src/update_work_job.sql

UPDATE work SET person = "tae" WHERE person = "tay";

out/update_rows.out

| person | job | |--------|-----------| | mik | calibrate | | mik | clean | | mik | complain | | po | clean | | po | complain | | tae | complain |

меняем все записи "tay" на "tae"

src/delete_rows.sql

DELETE FROM work WHERE person = "tae"; SELECT * FROM work;

out/delete_rows.out

| person | job | |--------|-----------| | mik | calibrate | | mik | clean | | mik | complain | | po | clean | | po | complain |

удаляем все строки, где значение person равно "tae"

src/backing_up.sql

CREATE TABLE backup (person text NOT NULL, job text NOT NULL); INSERT INTO backup SELECT person, job FROM work WHERE person = 'tae'; DELETE FROM work WHERE person = 'tae'; SELECT * FROM backup;

out/backing_up.out

| person | job | |--------|----------| | tae | complain |

создаём табличку backup c текстовыми столбцами person и job
помещаем внутрь backup значения столбцов person и job из таблицы work, где значения столбца person равно 'tae'
удаляем из work все записи со значением person равным 'tae'
отображаем записи таблички backup

Вот так выглядит связь основных понятий, которые мы только что обсуждали:

concept map: data definition and modification

src/cross_join.sql

SELECT * FROM work CROSS JOIN job;

out/cross_join.out

| person | job | name | billable | |--------|-----------|-----------|----------| | mik | calibrate | calibrate | 1.5 | | mik | calibrate | clean | 0.5 | | mik | clean | calibrate | 1.5 | | mik | clean | clean | 0.5 | | mik | complain | calibrate | 1.5 | | mik | complain | clean | 0.5 | | po | clean | calibrate | 1.5 | | po | clean | clean | 0.5 | | po | complain | calibrate | 1.5 | | po | complain | clean | 0.5 | | tay | complain | calibrate | 1.5 | | tay | complain | clean | 0.5 |

делаем CROSS JOIN для 2 таблиц work и job — все возможные комбинации строк из этих таблиц (если в work 3 строки, а в job 4 строки, то результат будет иметь 4 ⋅ 3 = 12 строк)

src/inner_join.sql

SELECT * FROM work INNER JOIN job ON work.job = job.name;

out/inner_join.out

| person | job | name | billable | |--------|-----------|-----------|----------| | mik | calibrate | calibrate | 1.5 | | mik | clean | clean | 0.5 | | po | clean | clean | 0.5 |

объединяем 2 таблицы work и job — берём те записи, где значение job из work совпадает со значением name из job

src/aggregate_join.sql

SELECT work.person, SUM(job.billable) AS pay FROM work INNER JOIN job ON work.job = job.name GROUP BY work.person;

out/aggregate_join.out

| person | pay | |--------|-----| | mik | 2.0 | | po | 0.5 |

объединяем те строки таблиц work и job, где значение job в таблице work соответствует значению name в job
суммируем значения billable из таблицы job для каждого значения person из таблицы work
группируем результаты по значениям person из work

src/left_join.sql

SELECT * FROM work LEFT JOIN job ON work.job = job.name;

out/left_join.out

| person | job | name | billable | |--------|-----------|-----------|----------| | mik | calibrate | calibrate | 1.5 | | mik | clean | clean | 0.5 | | mik | complain | | | | po | clean | clean | 0.5 | | po | complain | | | | tay | complain | | |

склеиваем таблицы work и job по соответствующим значениям столбца job
если в таблице work есть строки, для которых нет совпадений в таблице job, то они все равно будут включены в результат с пустыми (NULL) значениями
использование LEFT JOIN гарантирует, что все строки из левой таблицы work будут включены в результат, независимо от наличия совпадающих строк в правой таблице job

src/aggregate_left_join.sql

SELECT work.person, sum(job.billable) AS pay FROM work LEFT JOIN job ON work.job = job.name GROUP BY work.person;

out/aggregate_left_join.out

| person | pay | |--------|-----| | mik | 2.0 | | po | 0.5 | | tay | |

вычисляем сумму значений столбца billable из job, сохраняем как pay
используем LEFT JOIN, чтобы гарантированно включить все строки из work в job
группируем по столбцу person из work

Вот так выглядит связь основных понятий, которые мы только что обсуждали:

concept map: join

src/coalesce.sql

SELECT work.person, COALESCE(SUM(job.billable), 0.0) AS pay FROM work LEFT JOIN job ON work.job = job.name GROUP BY work.person;

out/coalesce.out

| person | pay | |--------|-----| | mik | 2.0 | | po | 0.5 | | tay | 0.0 |

COALESCE используется для замены NULL на 0.0, если сумма billable для данного person равна NULL
LEFT JOIN включает все записи из work и только соответствующие записи из job
группируем по значениям столбца person из work

src/negate_incorrectly.sql

SELECT DISTINCT person FROM work WHERE job != 'calibrate';

out/negate_incorrectly.out

| person | |--------| | mik | | po | | tay |

выбираем уникальные значения из столбца person, где поле job не равно calibrate

src/set_membership.sql

SELECT * FROM work WHERE person NOT IN ('mik', 'tay');

out/set_membership.out

| person | job | |--------|----------| | po | clean | | po | complain |

выбираем все строки из work, где person не равно 'mik' и не равно 'tay'

src/subquery_set.sql

SELECT DISTINCT person FROM work WHERE person not in (SELECT DISTINCT person FROM work WHERE job = 'calibrate');

out/subquery_set.out

| person | |--------| | po | | tay |

внутренний подзапрос выбирает уникальные значения столбца person из work, где в поле job стоит 'calibrate'
внешний, главный запрос выбирает те уникальные значения person, где person не равно значениям из внутренного подзапроса

src/autoincrement.sql

CREATE TABLE person (ident integer PRIMARY KEY autoincrement, name text NOT NULL); INSERT INTO person VALUES (NULL, 'mik'), (NULL, 'po'), (NULL, 'tay'); SELECT * FROM person; INSERT INTO person VALUES (1, "prevented");

out/autoincrement.out

| ident | name | |-------|------| | 1 | mik | | 2 | po | | 3 | tay | Runtime error near line 12: UNIQUE constraint failed: person.ident (19)

создаём табличку person с 2 столбцами: ident с целочисленными значениями, name с текстовыми значениями; столбец ident устанавливаем как PRIMARY KEY, включаем автоматическое инкрементирование значений
помещаем в таблицу person 3 пары ident–name
при попытке добавить ещё одну пару (1, "prevented") возникает ошибка, поскольку уже существует строка с indent равным 1

Внутренняя табличка:

src/sequence_table.sql

SELECT * FROM sqlite_sequence;

out/sequence_table.out

| name | seq | |--------|-----| | person | 3 |

выводим все текущие значения автоинкрементных счетчиков для таблиц в БД SQLite

src/alter_tables.sql

ALTER TABLE job ADD ident integer NOT NULL DEFAULT -1; UPDATE job SET ident = 1 WHERE name = 'calibrate'; UPDATE job SET ident = 2 WHERE name = 'clean'; SELECT * FROM job;

out/alter_tables.out

| name | billable | ident | |-----------|----------|-------| | calibrate | 1.5 | 1 | | clean | 0.5 | 2 |

добавляем новый столбец ident в табличку job; столбец заполняется целыми числами, не может быть пустым; ставим значение по умолчанию -1 для этого столбца
делаем значение столбца ident равным 1 там, где name равен 'calibrate'
устанавливаем значение ident равным 2 для строки, где name равен clean

src/insert_select.sql

CREATE TABLE new_work (person_id integer NOT NULL, job_id integer NOT NULL, FOREIGN key(person_id) REFERENCES person(ident), FOREIGN key(job_id) REFERENCES job(ident)); INSERT INTO new_work SELECT person.ident AS person_id, job.ident AS job_id FROM (person JOIN work ON person.name = work.person) JOIN job ON job.name = work.job; SELECT * FROM new_work;

out/insert_select.out

| person_id | job_id | |-----------|--------| | 1 | 1 | | 1 | 2 | | 2 | 2 |

создаём таблицу new_work с 2 целочисленными столбцами: person_id и job_id; оба столбца не могут быть пустыми
2 FOREIGN KEY ограничения добавляются, чтобы связать столбцы person_id и job_id новой таблицы new_work с соответствующими столбцами ident в таблицах person и job
добавляем данные в таблицу new_work, используя результат запроса SELECT
FROM (person JOIN work ON person.name = work.person) — данные будут выбраны из результатов соединения таблиц person и work по условию равенства значений столбца name в таблице person и столбца person в таблице work
JOIN job ON job.name = work.job — результаты предыдущего соединения будут дополнительно соединены с таблицей job по условию равенства значений столбца name в таблице job и столбца job в work

src/drop_table.sql

DROP TABLE work; ALTER TABLE new_work RENAME TO work;

удаляем work из БД
изменяем имя таблички new_work на work

out/drop_table.out

CREATE TABLE job (ident integer PRIMARY KEY autoincrement, name text NOT NULL, billable real NOT NULL); CREATE TABLE sqlite_sequence(name, seq); CREATE TABLE person (ident integer PRIMARY KEY autoincrement, name text NOT NULL); CREATE TABLE IF NOT EXISTS "work" (person_id integer NOT NULL, job_id integer NOT NULL, FOREIGN key(person_id) REFERENCES person(ident), FOREIGN key(job_id) REFERENCES job(ident));

создаём таблицу job с 3 колонками: ident хранит целые числа, используется в качестве первичного ключа (PRIMARY KEY) и автоматически увеличивается (autoincrement);name текстовый столбец, не может быть пустым (NOT NULL);billable — столбец вещественных чисел, не может быть пустым
создаём sqlite_sequence с 2 колонками: name и seq
создаём таблицу person с 2 колонками: ident — хранит целые числа, используется в качестве первичного ключа и автоматически увеличивается (autoincrement), name — хранит текст, не может быть пустым
создаём work с 4 колонками: person_id – хранит целые числа, не может быть пустым; аналогичный столбец job_id
устанавливаем внешние ключи, связывающие person_id с ident в таблице person и job_id с ident в таблице job

src/compare_individual_aggregate.sql

SELECT body_mass_g FROM penguins WHERE body_mass_g > (SELECT AVG(body_mass_g) FROM penguins) LIMIT 5;

out/compare_individual_aggregate.out

| body_mass_g | |-------------| | 4675 | | 4250 | | 4400 | | 4500 | | 4650 |

выбираем только те строки, где значение в столбце body_mass_g больше, чем среднее значение body_mass_g по всем строкам в таблице penguins
ну и выводим только первые 5 строк

src/compare_within_groups.sql

SELECT penguins.species, penguins.body_mass_g, Round(averaged.avg_mass_g, 1) AS avg_mass_g FROM penguins JOIN (SELECT species, Avg(body_mass_g) AS avg_mass_g FROM penguins GROUP BY species) AS averaged ON penguins.species = averaged.species WHERE penguins.body_mass_g > averaged.avg_mass_g LIMIT 5;

out/compare_within_groups.out

| species | body_mass_g | avg_mass_g | |---------|-------------|------------| | Adelie | 3750 | 3700.7 | | Adelie | 3800 | 3700.7 | | Adelie | 4675 | 3700.7 | | Adelie | 4250 | 3700.7 | | Adelie | 3800 | 3700.7 |

выбираем столбцы species и body_mass_g из таблицы penguins
вычисляем среднюю массу для каждого вида пингвина, округляем до 1 знака после запятой, используя подзапрос, который связывается с исходной таблицей penguins по полю species
используя результаты подзапроса, фильтруем только те записи, где масса пингвина больше средней массы для его вида
выводим только первые 5 записей

src/common_table_expressions.sql

WITH grouped AS (SELECT species, avg(body_mass_g) AS avg_mass_g FROM penguins GROUP BY species) SELECT penguins.species, penguins.body_mass_g, round(grouped.avg_mass_g, 1) AS avg_mass_g FROM penguins JOIN grouped WHERE penguins.body_mass_g > grouped.avg_mass_g LIMIT 5;

out/common_table_expressions.out

создаём табличку grouped (с помощью WITH), которая содержит среднюю массу тела пингвинов (AVG(body_mass_g)) для каждого вида из penguins (GROUP BY species)
из penguins выбираем такие столбцы: species, body_mass_g; и из из общей таблицы grouped выбираем avg_mass_g, округлённое до 1 знака
объединяем penguins с общей таблицей grouped (через JOIN); для каждого пингвина будет найдена соответствующая средняя масса тела для его вида
WHERE — фильтруем; оставляем только тех, у которых масса тела больше средней массы их вида
выводим только первые 5 строк

src/explain_query_plan.sql

EXPLAIN query PLAN SELECT species, AVG(body_mass_g) FROM penguins GROUP BY species;

out/explain_query_plan.out

QUERY PLAN |--SCAN penguins `--USE TEMP B-TREE FOR GROUP BY

EXPLAIN query PLAN — получаем план выполнения запроса, как будет выполнен запрос в базе данных
выбираем столбец species, вычисляем среднее значение столбца body_mass_g для каждого вида из penguins
GROUP BY species — группируем результаты по столбцу species

каждая таблица имеет специальный столбец rowid с уникальными числовыми идентификаторами

src/rowid.sql

SELECT rowid, species, island FROM penguins LIMIT 5;

out/rowid.out

| rowid | species | island | |-------|---------|-----------| | 1 | Adelie | Torgersen | | 2 | Adelie | Torgersen | | 3 | Adelie | Torgersen | | 4 | Adelie | Torgersen | | 5 | Adelie | Torgersen |

src/if_else.sql

WITH sized_penguins AS (SELECT species, iif(body_mass_g < 3500, 'small', 'large') AS size FROM penguins) SELECT species, size, count(*) AS num FROM sized_penguins GROUP BY species, size ORDER BY species, num;

out/if_else.out

| species | size | num | |-----------|-------|-----| | Adelie | small | 54 | | Adelie | large | 98 | | Chinstrap | small | 17 | | Chinstrap | large | 51 | | Gentoo | large | 124 |

создаём временную таблицу sized_penguins, которая содержит два столбца: species и size
size определяется на основе условия: если body_mass_g меньше 3500, то он считается 'small', в противном случае – 'large'
выбираем столбцы species и size из временной таблицы sized_penguins, а подсчитываем количество записей для каждой комбинации species и size, используя функцию count(*)
группируем данные (GROUP BY) по species и size

А если нам нужны маленькие, средние и большие?

Можно вложить if, но он быстро становится нечитаемым

src/case_when.sql

WITH sized_penguins AS (SELECT species, CASE WHEN body_mass_g < 3500 THEN 'small' WHEN body_mass_g < 5000 THEN 'medium' ELSE 'large' END AS SIZE FROM penguins) SELECT species, SIZE, count(*) AS num FROM sized_penguins GROUP BY species, SIZE ORDER BY species, num;

out/case_when.out

| species | size | num | |-----------|--------|-----| | Adelie | large | 1 | | Adelie | small | 54 | | Adelie | medium | 97 | | Chinstrap | small | 17 | | Chinstrap | medium | 51 | | Gentoo | medium | 56 | | Gentoo | large | 68 |

в блоке WITH создаём набор данных с именем sized_penguins, где находится species и size, определенные на body_mass_g
CASE разделяет пингвинов на 3 категории: 'small', 'medium' и 'large' в зависимости от их массы
в основном блоке SELECT выбираются вид пингвина, его размер и количество пингвинов каждого размера (num) из набора sized_penguins
результаты группируются по виду пингвина и их размеру с помощью GROUP BY
в конце запроса результаты сортируются сначала по species в алфавитном порядке, а затем по num

src/check_range.sql

WITH sized_penguins AS (SELECT species, CASE WHEN body_mass_g BETWEEN 3500 AND 5000 THEN 'normal' ELSE 'abnormal' END AS SIZE FROM penguins) SELECT species, SIZE, count(*) AS num FROM sized_penguins GROUP BY species, SIZE ORDER BY species, num;

out/check_range.out

| species | size | num | |-----------|----------|-----| | Adelie | abnormal | 55 | | Adelie | normal | 97 | | Chinstrap | abnormal | 17 | | Chinstrap | normal | 51 | | Gentoo | abnormal | 62 | | Gentoo | normal | 62 |

создаём общую таблицу выражений (CTE) sized_penguins, она выбирает вид пингвина и определяет его размер в зависимости от массы тела; если масса в диапазоне от 3500 до 5000 г, это размер normal, в противном случае – abnormal
затем из этой CTE извлекаем данные с указанием видов пингвинов, их размеров и количества пингвинов каждого вида и размера, используя SELECT с агрегирующей функцией COUNT(*)
группируем по виду и размеру пингвина с помощью GROUP BY
сортируем результат по виду и количеству пингвинов в порядке возрастания с помощью ORDER BY

**Ещё одна БД: **

ER-диаграмма показывает отношения между отдельными табличками и выглядит так:

assay database table diagramassay ER diagram

src/assay_staff.sql

SELECT * FROM staff;

out/assay_staff.out

| ident | personal | family | dept | age | |-------|----------|-----------|------|-----| | 1 | Kartik | Gupta | | 46 | | 2 | Divit | Dhaliwal | hist | 34 | | 3 | Indrans | Sridhar | mb | 47 | | 4 | Pranay | Khanna | mb | 51 | | 5 | Riaan | Dua | | 23 | | 6 | Vedika | Rout | hist | 45 | | 7 | Abram | Chokshi | gen | 23 | | 8 | Romil | Kapoor | hist | 38 | | 9 | Ishaan | Ramaswamy | mb | 35 | | 10 | Nitya | Lal | gen | 52 |

src/like_glob.sql

SELECT personal, family FROM staff WHERE personal LIKE '%ya%' OR family GLOB '*De*';

out/like_glob.out

| personal | family | |----------|--------| | Nitya | Lal |

SELECT personal, family — хотим выбрать столбцы personal и family из таблицы staff
FROM staff — ну понятно, запрос будет выполнен в таблице staff
'%ya%' — хотим выбрать строки, в которых значение столбца personal содержит подстроку ya (с помощью LIKE) или значение столбца family содержит De (с помощью GLOB)

src/union_all.sql

SELECT * FROM (SELECT * FROM (SELECT * FROM experiment ORDER BY started ASC LIMIT 5) UNION ALL SELECT * FROM (SELECT * FROM experiment ORDER BY started DESC LIMIT 5)) ORDER BY started ASC ;

out/union_all.out

| ident | kind | started | ended | |-------|-------------|------------|------------| | 17 | trial | 2023-01-29 | 2023-01-30 | | 35 | calibration | 2023-01-30 | 2023-01-30 | | 36 | trial | 2023-02-02 | 2023-02-03 | | 25 | trial | 2023-02-12 | 2023-02-14 | | 2 | calibration | 2023-02-14 | 2023-02-14 | | 40 | calibration | 2024-01-21 | 2024-01-21 | | 12 | trial | 2024-01-26 | 2024-01-28 | | 44 | trial | 2024-01-27 | 2024-01-29 | | 34 | trial | 2024-02-01 | 2024-02-02 | | 14 | calibration | 2024-02-03 | 2024-02-03 |

выбираем 5 самых старых записей из таблицы experiment, отсортированных по возрастанию даты начала (started ASC) с помощью подзапроса (внутренний SELECT)
выбираем 5 самых новых записей из experiment, отсортированных по убыванию даты начала (started DESC) с помощью другого подзапроса
объединяем эти 2 подзапроса с помощью UNION ALL, так мы получаем временную таблицу, содержащую 10 записей (5 самых старых и 5 самых новых)
из временной таблицы выбираем все столбцы для каждой записи (SELECT *) и окончательно сортируем записи по возрастанию даты начала (started ASC) с помощью внешнего ORDER BY

src/intersect.sql

SELECT personal, family, dept, age FROM staff WHERE dept = 'mb' INTERSECT SELECT personal, family, dept, age FROM staff WHERE age < 50 ;

out/intersect.out

| personal | family | dept | age | |----------|-----------|------|-----| | Indrans | Sridhar | mb | 47 | | Ishaan | Ramaswamy | mb | 35 |

здесь мы используем INTERSECT для объединения результатов двух отдельных запросов
вначале выбираем данные из таблицы staff, в которых значение поля dept равно 'mb'
потом выбираем данные из таблицы staff, в которых значение поля age меньше 50
с помощью INTERSECT объединяем результаты этих двух запросов
в результате будут выбраны строки, которые присутствуют в обоих результатах, то есть записи из staff, где значение dept равно 'mb' и значение age меньше 50

src/except.sql

SELECT personal, family, dept, age FROM staff WHERE dept = 'mb' EXCEPT SELECT personal, family, dept, age FROM staff WHERE age < 50 ;

out/except.out

| personal | family | dept | age | |----------|--------|------|-----| | Pranay | Khanna | mb | 51 |

при помощи SELECT извлекаем 4 поля из staff: personal, family, dept и age
затем используем WHERE, чтобы отфильтровать только те строки, в которых значение dept равно 'mb'
после этого при помощи EXCEPT удаляем из исходного результата любые строки, которые также присутствуют в результате второго запроса
второй запрос SELECT также извлекает четыре поля из staff: personal, family, dept и age
используем WHERE, чтобы отфильтровать только те строки, где значение age меньше 50

src/random_numbers.sql

WITH decorated AS (SELECT random() AS rand, personal || ' ' || family AS name FROM staff) SELECT rand, abs(rand) % 10 AS selector, name FROM decorated WHERE selector < 5;

out/random_numbers.out

| rand | selector | name | |----------------------|----------|-----------------| | 7176652035743196310 | 0 | Divit Dhaliwal | | -2243654635505630380 | 2 | Indrans Sridhar | | -6940074802089166303 | 5 | Pranay Khanna | | 8882650891091088193 | 9 | Riaan Dua | | -45079732302991538 | 5 | Vedika Rout | | -8973877087806386134 | 2 | Abram Chokshi | | 3360598450426870356 | 9 | Romil Kapoor |

создаём временную таблицу decorated

в этой таблице извлекается случайное число с помощью random()
конкатенируем значения personal и family под именем name с помощью ' ' для разделения
таким образом создаём временную таблицу, содержащую столбцы rand с случайными числами и name со значениями из столбцов personal и family таблицы staff
делаем выборку из временной таблицы decorated; в выборку включаем столбцы rand, name
abs(rand) % 10 — это мы вычисляем остаток от деления абсолютного значения rand на 10
ну и в конце оставляем только строки, где selector меньше 5

SQl полный гайд с запросами и примерами 2024

Выбираем все значения из таблички

Дополнительные команды SQL

Выбираем нужные столбцы

Сортировка

Ограничение выводимых записей

Ещё некоторые параметры вывода

Удаляем дубликаты

Фильтруем результаты

Более сложные условия фильтрации

Некоторые математические действия

Переименовываем столбцы

Подсчёт с пропущенными значениями

Вывод с условием при помощи WHERE

Условие с отрицанием

Выбираем NULL значения

Агрегирование в SQL

Распространённые агрегирующие функции в SQL

Подсчёт значений при помощи COUNT

Группировка

Как себя ведут неагрегированные столбцы

Выбор нужных столбцов для агрегирования

Фильтрация агрегированных значений

Читабельный вывод

Фильтрация входных данных

Создание табличек

Вставляем данные

Обновляем строки

Удаляем строки

Резервное копирование

Объединение табличек при помощи JOIN

INNER JOIN

Агрегирование объединённых через JOIN записей

LEFT JOIN

Агрегирование данных, собранных через LEFT JOIN

Объединение значений

SELECT DISTINCT и условие WHERE

Использование набора в условии WHERE при помощи IN

Подзапросы

Автоикремент и PRIMARY KEY

Изменение таблички при помощи ALTER

Создание новой таблички на базе старой

Удаление таблички

Сравнение отдельных значений с агрегированными

Сравнение отдельных значений с агрегированными внутри групп

CTE — табличные выражения

Смотрим план запроса с помощью EXPLAIN

Нумеруем строки

Условия if-else

Выбираем с помощью SELECT и CASE

Работаем с диапазоном значений

Ищем по фрагменту с помощью LIKE

Выбираем первую и последнюю строки

Пересечение отдельных табличек

Исключение

Случайные значения в SQL