Шпаргалка SQL — четыре СУБД на одной схеме

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику Архитектору Инженеру

Материал собран по авторской шпаргалке "ШПАРГАЛКА SQL" и встроен в энциклопедию. Здесь один и тот же набор задач (чтение, запись, DDL, JOIN, представления) на нейтральной схеме TABLE1–TABLE3 и сравнение синтаксиса SQLite, Oracle, MySQL и PostgreSQL.

Соседние материалы

Рецепты на схеме интернет-магазина (categories, orders, …) — Шпаргалка с типичными задачами по SQL. Тот же магазин с построчным разбором для учёбы — SQL — реальные кейсы. Подключение, API и углубление по каждой СУБД — SQLite, PostgreSQL, MySQL, Microsoft SQL Server. Теория JOIN — Алиасы, JOIN и объединение таблиц; VIEW — Представления (VIEW) - виртуальные таблицы; порядок выполнения SELECT — Оператор SELECT — синтаксис и стиль и Принципы работы SQL-движка.

Пять вопросов перед запросом

Перед написанием SQL полезно ответить себе по шагам (как в исходной шпаргалке):

С какой СУБД работаем? — от этого зависят типы, функции даты и возможности ALTER.
Из какой таблицы (или представления) берём строки? — FROM.
Какие столбцы нужны в ответе? — SELECT (или * только осознанно).
Нужна ли фильтрация или отсечение по диапазону / периоду? — WHERE, иногда HAVING.
Как сформулировать задачу одним предложением на русском? — проверка, что запрос отвечает именно на этот вопрос.

Сформулированное предложение затем переводят в каркас из Оператор SELECT — синтаксис и стиль: SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Учебная схема TABLE1–TABLE3

Три связанные таблицы с внешними ключами в обе стороны (многие-ко-многим через поля IDFROMTABLE*):

TABLE1		TABLE2		TABLE3
`ID1`	PK	`ID2`	PK	`ID3`
`NAME1`		`NAME2`		`NAME3`
`DESCRIPTION1`		`DESCRIPTION2`		`DESCRIPTION3`
`NUM1`		`NUM2`		`NUM3`
`IDFROMTABLE2`	→ TABLE2	`IDFROMTABLE1`	→ TABLE1	`IDFROMTABLE2`
`IDFROMTABLE3`	→ TABLE3	`IDFROMTABLE3`	→ TABLE3	`IDFROMTABLE1`

Связь "центральная" таблица TABLE1 с TABLE2 и TABLE3 через IDFROMTABLE2 и IDFROMTABLE3 повторяет типовой учебный JOIN из Алиасы, JOIN и объединение таблиц.

Чтение данных — общий SQL

На всех четырёх СУБД ниже синтаксис совпадает (кроме ограничений по JOIN в SQLite — см. раздел JOIN по диалектам).

Все столбцы и сортировка

SELECT *
FROM TABLE1;

SELECT *
FROM TABLE1
ORDER BY NAME1 ASC;   -- DESC — по убыванию

Фильтрация

SELECT *
FROM TABLE1
WHERE NUM1 = 10;

SELECT ID1, NAME1, DESCRIPTION1, NUM1, IDFROMTABLE2, IDFROMTABLE3
FROM TABLE1
WHERE IDFROMTABLE2 = 5;

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

SELECT ID1 AS id_pervoy_tablicy
FROM TABLE1;

В SQLite для пробелов в имени алиаса удобны квадратные скобки: AS [ID первой таблицы]. В Oracle/PostgreSQL/MySQL — двойные кавычки: AS "ID первой таблицы".

Три таблицы через JOIN

SELECT TABLE1.ID1, TABLE1.NAME1, TABLE2.NAME2, TABLE3.NAME3
FROM TABLE1
JOIN TABLE2 ON TABLE1.IDFROMTABLE2 = TABLE2.ID2
JOIN TABLE3 ON TABLE1.IDFROMTABLE3 = TABLE3.ID3;

LIKE, AND, OR, CASE

Код ITЗагрузка примера кода…

WHERE и HAVING

-- строки до группировки
SELECT *
FROM TABLE1
WHERE NUM1 > 10;

-- группы после агрегата
SELECT IDFROMTABLE2, COUNT(*) AS cnt
FROM TABLE1
GROUP BY IDFROMTABLE2
HAVING COUNT(*) > 1;

Подробнее про агрегаты — Фильтрация и группировка в SQL.

DISTINCT и подзапрос

SELECT DISTINCT NAME1 FROM TABLE1;

SELECT *
FROM TABLE1
WHERE NUM1 > (SELECT AVG(NUM1) FROM TABLE1);

Разбор подзапросов — Подзапросы, EXISTS и IN.

Запись и удаление данных

Общий синтаксис DML:

Код ITЗагрузка примера кода…

DROP TABLE IF EXISTS в старых версиях Oracle может отсутствовать — проверяйте версию или используйте PL/SQL с обработкой ошибки.

Текущая дата в SELECT

СУБД	Выражение в шпаргалке
SQLite	`DATE('now')`
Oracle	`SYSDATE`
MySQL	`CURDATE()`
PostgreSQL	`CURRENT_DATE`

Пример для PostgreSQL:

SELECT ID1, NAME1, CURRENT_DATE AS current_date
FROM TABLE1;

CREATE TABLE — типы по СУБД

Одна логическая таблица TABLE4 с внешними ключами на TABLE1–3:

SQLite

CREATE TABLE TABLE4 (
    ID4 INTEGER PRIMARY KEY,
    NAME4 TEXT NOT NULL,
    DESCRIPTION4 TEXT,
    NUM4 REAL,
    IDFROMTABLE1 INTEGER,
    IDFROMTABLE2 INTEGER,
    IDFROMTABLE3 INTEGER,
    FOREIGN KEY (IDFROMTABLE1) REFERENCES TABLE1(ID1),
    FOREIGN KEY (IDFROMTABLE2) REFERENCES TABLE2(ID2),
    FOREIGN KEY (IDFROMTABLE3) REFERENCES TABLE3(ID3)
);

Oracle

CREATE TABLE TABLE4 (
    ID4 NUMBER PRIMARY KEY,
    NAME4 VARCHAR2(100) NOT NULL,
    DESCRIPTION4 VARCHAR2(255),
    NUM4 NUMBER,
    IDFROMTABLE1 NUMBER,
    IDFROMTABLE2 NUMBER,
    IDFROMTABLE3 NUMBER,
    CONSTRAINT fk_table4_table1 FOREIGN KEY (IDFROMTABLE1) REFERENCES TABLE1(ID1),
    CONSTRAINT fk_table4_table2 FOREIGN KEY (IDFROMTABLE2) REFERENCES TABLE2(ID2),
    CONSTRAINT fk_table4_table3 FOREIGN KEY (IDFROMTABLE3) REFERENCES TABLE3(ID3)
);

MySQL

CREATE TABLE TABLE4 (
    ID4 INT PRIMARY KEY,
    NAME4 VARCHAR(100) NOT NULL,
    DESCRIPTION4 VARCHAR(255),
    NUM4 INT,
    IDFROMTABLE1 INT,
    IDFROMTABLE2 INT,
    IDFROMTABLE3 INT,
    FOREIGN KEY (IDFROMTABLE1) REFERENCES TABLE1(ID1),
    FOREIGN KEY (IDFROMTABLE2) REFERENCES TABLE2(ID2),
    FOREIGN KEY (IDFROMTABLE3) REFERENCES TABLE3(ID3)
);

PostgreSQL

CREATE TABLE TABLE4 (
    ID4 SERIAL PRIMARY KEY,
    NAME4 VARCHAR(100) NOT NULL,
    DESCRIPTION4 TEXT,
    NUM4 INT,
    IDFROMTABLE1 INT,
    IDFROMTABLE2 INT,
    IDFROMTABLE3 INT,
    FOREIGN KEY (IDFROMTABLE1) REFERENCES TABLE1(ID1),
    FOREIGN KEY (IDFROMTABLE2) REFERENCES TABLE2(ID2),
    FOREIGN KEY (IDFROMTABLE3) REFERENCES TABLE3(ID3)
);

ALTER TABLE — сравнение диалектов

Операция	SQLite	Oracle	MySQL	PostgreSQL
Добавить столбец	`ADD COLUMN col TYPE`	`ADD col TYPE`	`ADD COLUMN col TYPE`	`ADD COLUMN col TYPE`
Переименовать столбец	`RENAME COLUMN` (≥ 3.25) или обход через новую таблицу	`RENAME COLUMN`	`CHANGE old new TYPE`	`RENAME COLUMN`
Сменить тип столбца	ограниченно; часто пересоздание таблицы	пересоздание или миграция	`MODIFY COLUMN`	`ALTER COLUMN … TYPE`
Удалить столбец	`DROP COLUMN` (≥ 3.35) или пересоздание	`DROP COLUMN`	`DROP COLUMN`	`DROP COLUMN`

Добавить столбец TESTCOLUMN

-- SQLite, MySQL, PostgreSQL
ALTER TABLE TABLE1 ADD COLUMN TESTCOLUMN VARCHAR(100);

-- Oracle (без слова COLUMN)
ALTER TABLE TABLE1 ADD TESTCOLUMN VARCHAR2(100);

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

-- PostgreSQL, Oracle (12c+)
ALTER TABLE TABLE1 RENAME COLUMN DESCRIPTION1 TO NEW_DESCRIPTION1;

-- MySQL
ALTER TABLE TABLE1 CHANGE DESCRIPTION1 NEW_DESCRIPTION1 VARCHAR(255);

-- SQLite 3.25+
ALTER TABLE TABLE1 RENAME COLUMN DESCRIPTION1 TO NEW_DESCRIPTION1;

Сменить тип NUM1 на BIGINT / NUMBER

-- PostgreSQL
ALTER TABLE TABLE1 ALTER COLUMN NUM1 TYPE BIGINT;

-- MySQL (при совместимых данных)
ALTER TABLE TABLE1 MODIFY COLUMN NUM1 BIGINT;

Удалить столбец

ALTER TABLE TABLE1 DROP COLUMN DESCRIPTION1;

SQLite — старые версии и "тяжёлый" ALTER

В учебной шпаргалке для SQLite описан обходной путь — временная таблица без лишнего столбца (или с новым типом), INSERT … SELECT, DROP старой, ALTER … RENAME TO. Он по-прежнему нужен на встроенных сборках без DROP COLUMN / RENAME COLUMN. На актуальном SQLite (3.35+) предпочтительны прямые команды из таблицы выше. Подробности — SQLite — практическая работа и API.

Обходной путь SQLite (пересоздание TABLE1)

Если прямой ALTER недоступен — шаблон из исходной шпаргалки:

Код ITЗагрузка примера кода…

Для удаления столбца из списка полей временной таблицы просто не указывают ненужный столбец; для переименования — задают новое имя в CREATE и в списке INSERT.

JOIN по диалектам

Базовые INNER и LEFT одинаковы:

SELECT TABLE1.ID1, TABLE1.NAME1, TABLE2.NAME2
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.IDFROMTABLE2 = TABLE2.ID2;

SELECT TABLE1.ID1, TABLE1.NAME1, TABLE2.NAME2
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.IDFROMTABLE2 = TABLE2.ID2;

Тип JOIN	SQLite	Oracle	MySQL	PostgreSQL
INNER	да	да	да	да
LEFT	да	да	да	да
RIGHT	эмуляция через LEFT и порядок таблиц	да	да	да
FULL OUTER	`UNION ALL` двух LEFT (см. ниже)	да	с 8.0.14+ нативно; иначе UNION	да

RIGHT JOIN (эквивалент через порядок таблиц)

SELECT TABLE2.ID2, TABLE2.NAME2, TABLE1.NAME1
FROM TABLE2
LEFT JOIN TABLE1 ON TABLE2.ID2 = TABLE1.IDFROMTABLE2;

FULL OUTER — PostgreSQL и Oracle

SELECT TABLE1.ID1, TABLE1.NAME1, TABLE2.NAME2
FROM TABLE1
FULL OUTER JOIN TABLE2 ON TABLE1.IDFROMTABLE2 = TABLE2.ID2;

FULL OUTER — обход для SQLite и старого MySQL

SELECT TABLE1.ID1, TABLE1.NAME1, TABLE2.NAME2
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.IDFROMTABLE2 = TABLE2.ID2
UNION ALL
SELECT TABLE2.ID2, TABLE2.NAME2, TABLE1.NAME1
FROM TABLE2
LEFT JOIN TABLE1 ON TABLE2.ID2 = TABLE1.IDFROMTABLE2
WHERE TABLE1.ID1 IS NULL;

Представление (VIEW)

Один и тот же запрос на трёх таблицах сохраняют как виртуальную таблицу:

CREATE VIEW my_view AS
SELECT TABLE1.ID1, TABLE1.NAME1, TABLE2.NAME2, TABLE3.NAME3
FROM TABLE1
JOIN TABLE2 ON TABLE1.IDFROMTABLE2 = TABLE2.ID2
JOIN TABLE3 ON TABLE1.IDFROMTABLE3 = TABLE3.ID3;

SELECT * FROM my_view;

Теория и ограничения VIEW — Представления (VIEW) - виртуальные таблицы.

Microsoft SQL Server

В исходном docx раздела T-SQL нет; для SQL Server те же идеи, но другой синтаксис типов (INT, NVARCHAR), GETDATE(), ALTER COLUMN, TOP вместо LIMIT. Сводка — Microsoft SQL Server — практическая работа и API и T-SQL.

Быстрая навигация по разделу SQL

Задача	Куда идти
Первый запрос в psql / установка	Первые шаги с SQL
Тренажёр shop_data	SQL - язык структурированных запросов, Практикум shop_data
Рецепты на схеме магазина	Шпаргалка с типичными задачами по SQL
Четыре СУБД на TABLE1–3	эта страница
Категории команд DDL/DML	Категории SQL-команд - DDL, DML, DCL, TCL
Оптимизация и индексы	Оптимизация SQL-запросов, Сложные индексы

Шпаргалка SQL — четыре СУБД на одной схеме

Пять вопросов перед запросом

Учебная схема TABLE1–TABLE3

Чтение данных — общий SQL

Все столбцы и сортировка

Фильтрация

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

Три таблицы через JOIN

LIKE, AND, OR, CASE

WHERE и HAVING

DISTINCT и подзапрос

Запись и удаление данных

Текущая дата в SELECT

CREATE TABLE — типы по СУБД

ALTER TABLE — сравнение диалектов

Добавить столбец TESTCOLUMN

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

Сменить тип NUM1 на BIGINT / NUMBER

Удалить столбец

Обходной путь SQLite (пересоздание TABLE1)

JOIN по диалектам

RIGHT JOIN (эквивалент через порядок таблиц)

FULL OUTER — PostgreSQL и Oracle

FULL OUTER — обход для SQLite и старого MySQL

Представление (VIEW)

Microsoft SQL Server

Быстрая навигация по разделу SQL

См. также

SQL - язык структурированных запросов

Первые шаги с SQL

Эволюция систем хранения данных

Реляционная модель данных

Нормализация данных

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

Резервное копирование и восстановление PostgreSQL

Оператор SELECT — синтаксис и стиль

Подзапросы, EXISTS и IN

Фильтрация и трёхзначная логика

Блокировки и конкурентный доступ в PostgreSQL

Практикум shop_data

Пять вопросов перед запросом​

Учебная схема TABLE1–TABLE3​

Чтение данных — общий SQL​

Все столбцы и сортировка​

Фильтрация​

Псевдонимы столбцов​

Три таблицы через JOIN​

LIKE, AND, OR, CASE​

WHERE и HAVING​

DISTINCT и подзапрос​

Запись и удаление данных​

Текущая дата в SELECT​

CREATE TABLE — типы по СУБД​

ALTER TABLE — сравнение диалектов​

Добавить столбец TESTCOLUMN​

Переименовать столбец DESCRIPTION1​

Сменить тип NUM1 на BIGINT / NUMBER​

Удалить столбец​

Обходной путь SQLite (пересоздание TABLE1)​

JOIN по диалектам​

RIGHT JOIN (эквивалент через порядок таблиц)​

FULL OUTER — PostgreSQL и Oracle​

FULL OUTER — обход для SQLite и старого MySQL​

Представление (VIEW)​

Microsoft SQL Server​

Быстрая навигация по разделу SQL​

См. также

SQL - язык структурированных запросов

Первые шаги с SQL

Эволюция систем хранения данных

Реляционная модель данных

Нормализация данных

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

Резервное копирование и восстановление PostgreSQL

Оператор SELECT — синтаксис и стиль

Подзапросы, EXISTS и IN

Фильтрация и трёхзначная логика

Блокировки и конкурентный доступ в PostgreSQL

Практикум shop_data

Пять вопросов перед запросом

Учебная схема TABLE1–TABLE3

Чтение данных — общий SQL

Все столбцы и сортировка

Фильтрация

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

Три таблицы через JOIN

LIKE, AND, OR, CASE

WHERE и HAVING

DISTINCT и подзапрос

Запись и удаление данных

Текущая дата в SELECT

CREATE TABLE — типы по СУБД

ALTER TABLE — сравнение диалектов

Добавить столбец TESTCOLUMN

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

Сменить тип NUM1 на BIGINT / NUMBER

Удалить столбец

Обходной путь SQLite (пересоздание TABLE1)

JOIN по диалектам

RIGHT JOIN (эквивалент через порядок таблиц)

FULL OUTER — PostgreSQL и Oracle

FULL OUTER — обход для SQLite и старого MySQL

Представление (VIEW)

Microsoft SQL Server

Быстрая навигация по разделу SQL