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

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

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

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

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

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

---

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

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

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

Сформулированное предложение затем переводят в каркас из 107: 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 из 55.

---

Чтение данных — общий 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

SELECT *
FROM TABLE1
WHERE NAME1 LIKE 'A%';

SELECT *
FROM TABLE1
WHERE NUM1 > 10 AND IDFROMTABLE2 = 5;

SELECT *
FROM TABLE1
WHERE NUM1 > 10 OR IDFROMTABLE2 = 5;

SELECT ID1, NAME1,
  CASE
    WHEN NUM1 > 10 THEN 'Большое значение'
    ELSE 'Маленькое значение'
  END AS value_category
FROM TABLE1;

WHERE и HAVING

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

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

Подробнее про агрегаты — 6.

DISTINCT и подзапрос

SELECT DISTINCT NAME1 FROM TABLE1;

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

Разбор подзапросов — 108.

---

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

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

INSERT INTO TABLE1 (ID1, NAME1, DESCRIPTION1, NUM1, IDFROMTABLE2, IDFROMTABLE3)
VALUES (1, 'Пример', 'Описание', 100, 2, 3);

UPDATE TABLE1
SET NAME1 = 'Новое имя', NUM1 = 200
WHERE ID1 = 1;

UPDATE TABLE1
SET DESCRIPTION1 = 'Новое описание'
WHERE ID1 = 1;

-- без WHERE затронет все строки — только осознанно
UPDATE TABLE1
SET DESCRIPTION1 = 'Обновленное описание';

DELETE FROM TABLE1
WHERE ID1 = 1;

DROP TABLE IF EXISTS TABLE1;

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+) предпочтительны прямые команды из таблицы выше. Подробности — 887.

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

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

CREATE TABLE temp_table1 (
    ID1 INTEGER,
    NAME1 TEXT,
    DESCRIPTION1 TEXT,
    NUM1 REAL,
    IDFROMTABLE2 INTEGER,
    IDFROMTABLE3 INTEGER
);

INSERT INTO temp_table1 (ID1, NAME1, DESCRIPTION1, NUM1, IDFROMTABLE2, IDFROMTABLE3)
SELECT ID1, NAME1, DESCRIPTION1, NUM1, IDFROMTABLE2, IDFROMTABLE3
FROM TABLE1;

DROP TABLE TABLE1;
ALTER TABLE temp_table1 RENAME TO TABLE1;

Для удаления столбца из списка полей временной таблицы просто не указывают ненужный столбец; для переименования — задают новое имя в 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 — 8.

---

Microsoft SQL Server

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

---

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

Задача	Куда идти
Первый запрос в psql / установка	101
Тренажёр shop_data	1, 111
Рецепты на схеме магазина	885
Четыре СУБД на TABLE1–3	эта страница
Категории команд DDL/DML	22
Оптимизация и индексы	881, 884