Перейти к содержанию
Предметы
  • 1 Основы запросов
    1.1

    SELECT и FROM

    База данных хранит данные в таблицах. Запрос читает данные с помощью 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 по привычке пишутся ЗАГЛАВНЫМИ БУКВАМИ.
    • Завершение запроса ставится после точки с запятой ;.
    1.2

    Выбор столбцов

    Перечислите нужные столбцы, разделяя их запятыми. Используйте 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 удаляет дубликаты из результата.
    1.3

    Фильтрация с WHERE

    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;
    
    • Считывайте части в этом логическом порядке: FROM → WHERE → SELECT. (База данных может выполнять работу иначе, но результат тот же.)

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

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

    AND, OR, NOT

    Объединяйте условия с помощью 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;
    
    AND требует всех истинных; OR требует любого; NOT инвертирует одно
    AND требует всех истинных; OR требует любого; NOT инвертирует одно
    2.2

    LIKE, IN и BETWEEN

    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
    
    Шаблон Совпадает
    'M%' начинается с M
    '%a' заканчивается на a
    '%an%' содержит "an"
    'M_i' M, затем ровно один символ, затем i

    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;
    
    2.3

    NULL

    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 включает оба конца диапазона.
  • 3 Сортировка и ограничение
    3.1

    ORDER BY и LIMIT

    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 ограничивает количество возвращаемых строк, но только после сортировки.
    ORDER BY сортирует; LIMIT оставляет первые n после сортировки
    ORDER BY сортирует; LIMIT оставляет первые n после сортировки
  • 4 Агрегаты и группировка
    4.1

    Агрегатные функции

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

    Функция Дает
    COUNT(*) количество строк
    SUM(col) общая сумма
    AVG(col) среднее арифметическое
    MIN(col) / MAX(col) минимальное / максимальное значение
    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;
    
    4.2

    GROUP BY и HAVING

    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;
    
    GROUP BY собирает строки в одну группу на каждое значение; каждая группа становится одной сводной строкой
    GROUP BY собирает строки в одну группу на каждое значение; каждая группа становится одной сводной строкой

    Записывайте SQL-подразделы в требуемом синтаксическом порядке. Приведённая ниже таблица — логическая модель для рассуждений о запросе; движок может оптимизировать порядок физической реализации:

    Шаг Оператор
    1 FROM (и любой JOIN)
    2 WHERE — фильтрует строки
    3 GROUP BY — формирует группы
    4 HAVING — фильтрует группы
    5 SELECT — вычисляет столбцы вывода
    6 ORDER BY, затем LIMIT

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

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

    Ключи и отношения

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

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

    INNER JOIN

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

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
    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;
    
    INNER JOIN сопоставляет каждый внешний ключ с первичным ключом и объединяет совпавшие строки
    INNER JOIN сопоставляет каждый внешний ключ с первичным ключом и объединяет совпавшие строки
    5.3

    Объединение с группировкой

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

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
    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.id, c.name;
    

    Группируйте также по ключу c.id и имени: два класса, случайно имеющих одинаковое название, иначе были бы объединены в одну строку.

    5.4

    LEFT JOIN

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

    CREATE TABLE class (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER REFERENCES class(id));
    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, чтобы сохранить их.
  • 6 Изменение данных
    6.1

    INSERT

    INSERT INTO ... VALUES ... добавляет новые строки. Укажите имена столбцов, затем значения в том же порядке. (Каждый блок ниже заканчивается на SELECT, чтобы вы могли увидеть результат.)

    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 (...), (...); добавляет несколько строк в одном операторе.
    INSERT добавляет новую строку в таблицу
    INSERT добавляет новую строку в таблицу
    6.2

    UPDATE

    UPDATE ... SET ... WHERE ... изменяет существующие строки. Всегда добавляйте WHERE, иначе изменятся все строки.

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

    DELETE

    DELETE FROM ... WHERE ... удаляет строки. Без WHERE он очистит всю таблицу.

    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;
    

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

    • UPDATE и DELETE без изменения WHERE на каждой строке — всегда добавляйте WHERE.
    • В INSERT значения должны соответствовать списку столбцов по порядку и типу.
    • Тестируйте рискованную операцию DELETE сначала как SELECT с теми же WHERE.
  • 7 Определение таблиц
    7.1

    CREATE TABLE & типы данных

    CREATE TABLE определяет схему таблицы: имена столбцов и их типы данных. Основные типы SQLite — INTEGER, TEXT и REAL (число с плавающей точкой: быстрый, но не всегда точный, поэтому 0.1 + 0.2 = 0.3 неверно).

    CREATE TABLE student (id INTEGER, name TEXT, score REAL);
    INSERT INTO student VALUES (1, 'Mei', 88.5);
    SELECT * FROM student;
    
    CREATE TABLE задает имена столбцов и их типы
    CREATE TABLE задает имена столбцов и их типы
    7.2

    Ключи и ограничения

    Ограничение (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. В SQLite включите контроль для каждого соединения с помощью PRAGMA foreign_keys = ON; перед началом транзакции. Само объявление не гарантирует выполнение контроля.

    PRAGMA foreign_keys = ON;
    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) отдельной строкой.
    7.3

    ALTER TABLE

    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; полностью удаляет таблицу — структуру и данные.

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

    • Объявите подходящий тип для каждого столбца. SQLite допускает пропуск имён типов и обычно использует гибкую типизацию; само объявление типа не валидирует каждое хранящееся значение.
    • Разработайте первичный ключ, который будет уникальным и непустым. В обычных таблицах SQLite некоторые объявления первичного ключа всё ещё допускают NULL; используйте NOT NULL явно для текстового ключа. INTEGER PRIMARY KEY имеет специальное поведение идентификатора строки: вставка NULL выделяет целочисленный id.
    • При включённом контроле внешних ключей непустое значение внешнего ключа должно совпадать со ссылочным ключом. NULL разрешён, если только у столбца также нет ограничения NOT NULL.
  • 8 Проектирование баз данных
    8.1

    Отношения и ER-диаграммы

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

    Отношение Пример
    один к одному человек и его паспорт
    один ко многим класс и его студенты
    многие ко многим студенты и клубы
    На стороне «многие» находится внешний ключ; связь «многие ко многим» требует таблицы-связи
    На стороне «многие» находится внешний ключ; связь «многие ко многим» требует таблицы-связи

    Таблица связей содержит по одной строке на каждое соединение, и каждый её столбец является внешним ключом. Первичный ключ — пара (student_id, club_id), составной ключ, поэтому один и тот же студент не может записаться в один и тот же клуб дважды. Оба столбца являются NOT NULL, поэтому каждая запись memberships указывает на студента и клуб. Здесь Мэй состоит в двух клубах, а в шахматном клубе два участника:

    CREATE TABLE student (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE club (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE membership (
      student_id INTEGER NOT NULL REFERENCES student(id),
      club_id INTEGER NOT NULL REFERENCES club(id),
      PRIMARY KEY (student_id, club_id)
    );
    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;
    
    8.2

    Нормализация

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

    • 1NF: каждая ячейка содержит одно атомарное значение — никаких списков внутри ячейки.
    • 2NF: ни один столбец не зависит только от части составного ключа.
    • 3NF: ни один столбец не зависит от другого столбца, не являющегося ключом.
    Ненормализованная (плохо) Нормализованная (лучше)
    student(name, club1, club2) student(name) + membership(student, club)

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

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

Войти или создать аккаунт

IGCSE, A-Level & AP