SQL Handout · ⁨Раздаточный материал SQL⁩

База данных хранит данные в таблицах. Запрос читает данные с помощью SELECT. SELECT * возвращает все столбцы; FROM называет таблицу.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT * FROM student;

• Каждая строка — это одна запись. Ключевые слова SQL по привычке пишутся ЗАГЛАВНЫМИ БУКВАМИ.
• Завершение запроса ставится после точки с запятой ;.

Перечислите нужные столбцы, разделяя их запятыми. Используйте AS для переименования столбца в результате (алиас).

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score AS mark FROM student;

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

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
SELECT name, score + 5 AS bonus FROM student;

• SELECT DISTINCT col удаляет дубликаты из результата.

WHERE оставляет только те строки, которые соответствуют условию. Сравнивайте с =, <> (не равно), <, >, <=, >=. Текст заключайте в одинарные кавычки.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score >= 80;

• SQL выполняет части запроса в этом порядке: FROM → WHERE → SELECT.

Распространенные ошибки

• Значения текста берите в одинарные кавычки: WHERE name = 'Ann'; названия столбцов кавычками не обводятся.
• SELECT * возвращает все столбцы — указывайте только необходимые столбцы.
• WHERE фильтрует строки; он располагается после FROM.

Объединяйте условия с помощью AND, OR, NOT. AND требует истинности всех условий; OR требует истинности хотя бы одного; NOT инвертирует одно условие. Используйте скобки для задания приоритета операций.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE form = '11A' AND score >= 90;

LIKE совпадает с текстовым шаблоном: % обозначает любой текст, а _ — один символ; это подстановочные знаки (wildcards).

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name FROM student WHERE name LIKE 'M%';        -- starts with M

IN совпадает с набором значений; BETWEEN совпадает с диапазоном (оба конца включены).

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT name, score FROM student
WHERE form IN ('11A', '11C') AND score BETWEEN 80 AND 100;

NULL помечает отсутствующее значение — в этой ячейке ничего не было сохранено. NULL не является 0 и не пустой строкой. Сравнение со = или <> никогда не даст совпадения; проверяйте с помощью IS NULL или IS NOT NULL.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name FROM student WHERE score IS NULL;

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', NULL), (3, 'Ana', 95);
SELECT name, score FROM student WHERE score IS NOT NULL;

• WHERE score = NULL не вернёт ни одной строки — даже строку Сэма.
• NULL в арифметических операциях даёт NULL: score + 5 останется NULL для Сэма.

Распространенные ошибки

• Проверяйте пустое значение с помощью IS NULL, никогда не = NULL.
• В LIKE, % совпадает с любым текстом, а _ — с одним символом: 'A%' означает "начинается с A".
• IN (1, 2, 3) короче многих OR; BETWEEN a AND b включает оба конца диапазона.

ORDER BY сортирует строки результата. Добавьте DESC для нисходящего порядка (от высокого к низкому); по умолчанию используется восходящий (от низкого к высокому).

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC;

Сортировка по нескольким столбцам

Перечислите несколько столбцов. При равенстве значений в первом столбце разрыв решается следующим.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',88),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC, name ASC;

LIMIT (топ-N)

LIMIT n оставляет только первые n строк — используйте его вместе с ORDER BY для получения списка топ-N.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei',88),(2,'Sam',71),(3,'Ana',95);
SELECT name, score FROM student ORDER BY score DESC LIMIT 2;

• ORDER BY также может сортировать по алиасу или вычислению: ORDER BY avg_score DESC.
• Текст сортируется алфавитно: ORDER BY name выводит от A до Z.

Распространенные ошибки

• ORDER BY сортирует по возрастанию по умолчанию; добавьте DESC для нисходящего порядка.
• ORDER BY располагается ближе к концу запроса, после WHERE и GROUP BY.
• LIMIT ограничивает количество возвращаемых строк, но только после сортировки.

Агрегатная функция преобразует множество строк в одно сводное значение. Оберните среднее в ROUND(x, 2), чтобы округлить результат.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT COUNT(*), ROUND(AVG(score), 2), MAX(score) FROM student;

GROUP BY создает одну сводную строку для каждой группы. HAVING фильтрует эти группы — это похоже на WHERE, но выполняется после группировки.

CREATE TABLE student (id INTEGER, name TEXT, form TEXT, score INTEGER);
INSERT INTO student VALUES (1,'Mei','11A',88),(2,'Sam','11B',71),(3,'Ana','11A',95);
SELECT form, COUNT(*) AS n, ROUND(AVG(score), 2) AS avg_score
FROM student
GROUP BY form
HAVING COUNT(*) > 1;

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

Распространенные ошибки

• Вы не можете выбрать обычный столбец рядом с агрегатной функцией, если он не находится в GROUP BY.
• Фильтруйте строки с помощью WHERE (до группировки) и фильтруйте группы с помощью HAVING (после).
• COUNT(*) считает строки; COUNT(col) пропускает NULL в этом столбце.

Первичный ключ однозначно идентифицирует каждую строку в таблице. Внешний ключ в одной таблице ссылается на первичный ключ другой таблицы — это устанавливает связь между ними.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT * FROM student;

Объединение (join) совмещает строки из двух таблиц. INNER JOIN ... ON ... оставляет только те строки, у которых ключи совпадают. Короткие псевдонимы (s, c) делают запрос более читаемым.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;

Сначала выполните join, затем GROUP BY для создания сводных данных по объединенным строкам.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', 2), (3, 'Ana', 1);
SELECT c.name AS class, COUNT(*) AS n
FROM student s INNER JOIN class c ON s.class_id = c.id
GROUP BY c.name;

INNER JOIN оставляет только совпавшие строки. LEFT JOIN сохраняет все строки левой (первой) таблицы; где нет совпадения, столбцы правой таблицы возвращаются как NULL.

CREATE TABLE class (id INTEGER, name TEXT);
CREATE TABLE student (id INTEGER, name TEXT, class_id INTEGER);
INSERT INTO class VALUES (1, 'Maths'), (2, 'Art');
INSERT INTO student VALUES (1, 'Mei', 1), (2, 'Sam', NULL);
SELECT s.name, c.name AS class
FROM student s LEFT JOIN class c ON s.class_id = c.id;

• У Сэма нет класса, но строка все равно появляется — со значениями class, равными NULL.
• Чтобы найти только несовпавшие строки, добавьте WHERE c.id IS NULL.

Распространенные ошибки

• Join без условия ON соединяет каждую строку с каждой (cross join).
• Сопоставьте внешний ключ с первичным ключом: ON orders.customer_id = customers.id.
• INNER JOIN отбрасывает строки, не имеющие совпадений на другой стороне; используйте LEFT JOIN, чтобы сохранить их.

INSERT INTO ... VALUES ... adds new rows 行. Name the columns, then give the values in the same order. (Each block below ends with a SELECT so you can see the result.)

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student (id, name, score) VALUES (1, 'Mei', 88);
INSERT INTO student VALUES (2, 'Sam', 71);
SELECT * FROM student;

• INSERT INTO t VALUES (...), (...); adds several rows in one statement.

DELETE FROM ... WHERE ... removes rows. Without WHERE it empties the whole table 表.

CREATE TABLE student (id INTEGER, name TEXT, score INTEGER);
INSERT INTO student VALUES (1, 'Mei', 88), (2, 'Sam', 71);
DELETE FROM student WHERE score < 80;
SELECT * FROM student;

Common mistakes

• UPDATE and DELETE without a WHERE change every row — always add the WHERE.
• In INSERT, the values must line up with the column list in order and type.
• Test a risky DELETE first as a SELECT with the same WHERE.

CREATE TABLE определяет схему таблицы: имена столбцов и их типы данных. Основные типы SQLite — это INTEGER, TEXT и REAL (десятичное число).

CREATE TABLE student (id INTEGER, name TEXT, score REAL);
INSERT INTO student VALUES (1, 'Mei', 88.5);
SELECT * FROM student;

Ограничение (constraint) — это правило для столбца: PRIMARY KEY (уникальный ID), NOT NULL (обязательное наличие значения), UNIQUE и DEFAULT (значение по умолчанию).

CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    score INTEGER DEFAULT 0
);
INSERT INTO student (id, name) VALUES (1, 'Mei');
SELECT * FROM student;

Заявите внешний ключ с помощью REFERENCES — это фиксирует, что столбец ссылается на первичный ключ другой таблицы:

CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    class_id INTEGER REFERENCES class(id)
);
INSERT INTO class VALUES (1, 'Maths');
INSERT INTO student VALUES (1, 'Mei', 1);
SELECT s.name, c.name AS class
FROM student s INNER JOIN class c ON s.class_id = c.id;

• Полная форма записывается как FOREIGN KEY (class_id) REFERENCES class(id) отдельной строкой.

ALTER TABLE ... ADD COLUMN ... изменяет схему уже существующей таблицы. Значение в DEFAULT заполняет новый столбец в старых строках.

CREATE TABLE student (id INTEGER, name TEXT);
INSERT INTO student VALUES (1, 'Mei');
ALTER TABLE student ADD COLUMN score INTEGER DEFAULT 0;
SELECT * FROM student;

• ALTER TABLE student RENAME TO pupil; переименовывает всю таблицу.
• DROP TABLE student; полностью удаляет таблицу — структуру и данные.

Распространенные ошибки

• Каждый столбец требует типа данных (например, INTEGER, TEXT).
• Первичный ключ должен быть уникальным и не может содержать NULL.
• Значение внешнего ключа должно существовать в таблице, на которую оно указывает.

Таблицы связаны через отношения. «Один ко многим» — наиболее распространенный: один класс имеет много студентов. «Многие ко многим» требует промежуточной таблицы-связи. Диаграмма «сущность-связь» (ER diagram) изображает каждую сущность в виде прямоугольника, а каждую связь — в виде линии.

Таблица-связь содержит по одной строке на каждую связь. Здесь Мэй состоит в двух клубах, а Шахматы — в двух участниках:

CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE club (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE membership (student_id INTEGER, club_id INTEGER);
INSERT INTO student VALUES (1, 'Mei'), (2, 'Sam');
INSERT INTO club VALUES (1, 'Chess'), (2, 'Art');
INSERT INTO membership VALUES (1, 1), (1, 2), (2, 1);
SELECT s.name, c.name AS club
FROM membership m
INNER JOIN student s ON m.student_id = s.id
INNER JOIN club c ON m.club_id = c.id;

Нормализация организует таблицы во избежание избыточности (повторения одних и тех же данных) и ошибок при обновлении, которые она вызывает. Первые три нормальные формы:

• 1NF: каждая ячейка содержит одно атомарное значение — никаких списков внутри ячейки.
• 2NF: ни один столбец не зависит только от части составного ключа.
• 3NF: ни один столбец не зависит от другого столбца, не являющегося ключом.

Распространенные ошибки

• Разделите повторяющиеся группы в отдельную таблицу (нормализация) вместо множества похожих столбцов.
• Каждая таблица должна описывать ОДИН вид объектов.
• Соедините таблицы внешним ключом, который ссылается на первичный ключ другой таблицы.