Команды SQL: список основных операторов SQL с примерами

Команды SQL в одной таблице

Команды SQL делятся на несколько групп по тому, что они делают. В таблице собраны те, которые нужны чаще всего, а в каждом разделе ниже одна из них запускается на примере.

DDL означает data definition language (язык определения данных), DML означает data manipulation language (язык манипулирования данными), DQL означает data query language (язык запросов), а TCL означает transaction control language (язык управления транзакциями). Важнее названий само разделение: DDL меняет форму базы данных, DML меняет её содержимое.

CREATE TABLE: создать таблицу

CREATE TABLE задаёт имя таблицы и перечисляет её столбцы с типами и правилами. Всем остальным командам на этой странице нужна таблица, с которой они будут работать.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    grade INTEGER
);

SELECT name, sql FROM sqlite_master WHERE type = 'table';

На странице про CREATE TABLE разобраны правила вроде NOT NULL, UNIQUE и DEFAULT.

INSERT: добавить строки

INSERT INTO добавляет одну строку или несколько. Перечислите столбцы, затем значения в том же порядке.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);

INSERT INTO students (name, grade) VALUES ('Ada', 91);
INSERT INTO students (name, grade) VALUES
    ('Boris', 78),
    ('Rosa', 85);

SELECT * FROM students;

Столбец id заполнился сам: INTEGER PRIMARY KEY получает следующий номер, если вы его не указали. Подробнее на странице INSERT.

SELECT: прочитать строки

SELECT это команда, которую вы будете писать чаще всего. Выберите столбцы, отфильтруйте строки через WHERE и отсортируйте их через ORDER BY.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85), ('Chen', 62);

SELECT name, grade
FROM students
WHERE grade >= 80
ORDER BY grade DESC;

Возвращаются только строки, прошедшие проверку WHERE, и первой идёт самая высокая оценка. Подробности на страницах основы SELECT и WHERE.

UPDATE: изменить строки

UPDATE записывает новые значения в уже существующие строки. Какие строки менять, решает условие WHERE; если его не указать, изменятся все строки.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85);

UPDATE students
SET grade = 82
WHERE name = 'Boris';

SELECT * FROM students;

Если вы не уверены, каким строкам соответствует условие WHERE, сначала выполните его как SELECT. На странице UPDATE есть другие приёмы.

DELETE: удалить строки

DELETE FROM удаляет строки, которые подходят под условие WHERE. Как и UPDATE, без условия команда затрагивает все строки.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL, grade INTEGER);
INSERT INTO students (name, grade) VALUES ('Ada', 91), ('Boris', 78), ('Rosa', 85), ('Chen', 62);

DELETE FROM students
WHERE grade < 70;

SELECT * FROM students;

Строки Chen больше нет, остальные не тронуты. См. DELETE.

ALTER TABLE и DROP TABLE: изменить или удалить таблицу

ALTER TABLE меняет существующую таблицу, не теряя её строк. DROP TABLE удаляет таблицу полностью.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO students (name) VALUES ('Ada'), ('Boris');

ALTER TABLE students ADD COLUMN email TEXT;
UPDATE students SET email = lower(name) || '@school.test';
SELECT * FROM students;

CREATE TABLE old_notes (id INTEGER PRIMARY KEY, body TEXT);
DROP TABLE old_notes;
SELECT name FROM sqlite_master WHERE type = 'table';

Новый столбец в существующих строках содержит NULL, пока вы его не заполните. На странице ALTER и DROP TABLE перечислено, что разрешает SQLite.

JOIN: объединить таблицы

JOIN читает строки сразу из двух таблиц, сопоставляя их по общему столбцу.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE enrollments (student_id INTEGER, course TEXT);

INSERT INTO students (name) VALUES ('Ada'), ('Boris'), ('Rosa');
INSERT INTO enrollments VALUES (1, 'Math'), (1, 'Physics'), (3, 'Art');

SELECT students.name, enrollments.course
FROM students
INNER JOIN enrollments ON enrollments.student_id = students.id
ORDER BY students.name;

У Boris нет записей о курсах, поэтому INNER JOIN его не включает. LEFT JOIN оставил бы его с пустым курсом.

GROUP BY: подвести итоги по группам

GROUP BY сворачивает строки с одинаковым значением в одну строку, а агрегатная функция вроде COUNT или AVG подводит итог по каждой группе.

CREATE TABLE enrollments (student TEXT, course TEXT, score INTEGER);
INSERT INTO enrollments VALUES
    ('Ada', 'Math', 91), ('Boris', 'Math', 78),
    ('Ada', 'Art', 88), ('Rosa', 'Art', 95), ('Chen', 'Art', 70);

SELECT course, COUNT(*) AS students, AVG(score) AS average
FROM enrollments
GROUP BY course
ORDER BY course;

Подробнее на страницах агрегатные функции и GROUP BY и HAVING.

BEGIN, COMMIT и ROLLBACK: объединить изменения

Транзакция заставляет несколько изменений считаться одним. COMMIT сохраняет их все, ROLLBACK отменяет их все.

CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Ada', 100), ('Boris', 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Ada';
UPDATE accounts SET balance = balance + 30 WHERE name = 'Boris';
COMMIT;

BEGIN;
UPDATE accounts SET balance = 0;
ROLLBACK;

SELECT * FROM accounts;

Перевод был зафиксирован, а второе изменение откатилось, поэтому у Ada в итоге 70, а у Boris 80. Когда нужны транзакции, объясняет страница Транзакции.

Чем отличается SQLite

Команды на этой странице относятся к стандартному SQL, и всё, что вы изучите здесь, пригодится в MySQL и PostgreSQL. Но запускаемые примеры написаны для SQLite, и в больших СУБД меняются три детали:

• Список таблиц. Примеры читают sqlite_master, собственную таблицу схемы SQLite. В MySQL для этого есть SHOW TABLES, а в PostgreSQL представление information_schema.tables (или \dt в psql).
• Автоматические id. В SQLite столбец, объявленный как INTEGER PRIMARY KEY, сам нумерует новые строки, поэтому в примерах с INSERT столбец id пропущен. В MySQL столбцу нужен AUTO_INCREMENT, а в PostgreSQL столбец идентичности (GENERATED BY DEFAULT AS IDENTITY) или SERIAL.
• Права и изменения схемы. В SQLite нет GRANT и REVOKE, потому что база SQLite представляет собой один файл без учётных записей, а её ALTER TABLE не умеет менять тип столбца.