Разработка базы данных. Структурированные запросы

Учебниковая опора

• Кадиркулов Р. А., Нурмуханбетова Г. К. «Информатика. Учебник для 10 классов ЕМН», 10 класс, Алматыкітап баспасы, 2019, с. 116–147 (§§ 18–23)

Главное

• SQL (Structured Query Language) — язык структурированных запросов со строгой типизацией.
• DDL — описание структуры: CREATE, ALTER, DROP. DML — работа с данными: INSERT, SELECT, UPDATE, DELETE.
• Ограничения: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK.
• Связи таблиц: 1:1, 1:M, M:M (через промежуточную таблицу); связь — через FOREIGN KEY.
• Запрос: SELECT … FROM … WHERE … ORDER BY … LIMIT ….
• Агрегаты: COUNT, SUM, AVG, MIN, MAX; GROUP BY — группы, HAVING — фильтр групп.
• Форма — для ввода данных, отчёт — для сводного вывода и печати.

Теория

Типы данных SQL

Тип	Назначение
INT, SMALLINT, BIGINT, TINYINT	целые (4, 2, 8, 1 байт)
DECIMAL(p, s)	точные дроби (деньги)
FLOAT, DOUBLE	числа с плавающей точкой
CHAR(n)	строка фиксированной длины (дополняется пробелами)
VARCHAR(n)	строка переменной длины до n
TEXT	длинные тексты
DATE, TIME, DATETIME	дата YYYY-MM-DD, время, дата и время
BOOLEAN	TRUE / FALSE

Создание и изменение

CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    grade INT CHECK (grade >= 1 AND grade <= 11),
    city VARCHAR(50) DEFAULT 'Алматы'
);
INSERT INTO Students (first_name, grade) VALUES ('Данияр', 10);
UPDATE Students SET grade = 11 WHERE id = 1;
DELETE FROM Students WHERE id = 1;

Без WHERE команды UPDATE и DELETE затрагивают все строки.

Многотабличная БД. Одна большая таблица порождает дублирование; нормализация разбивает данные на связанные таблицы. 1:1 — гражданин и паспорт; 1:M — класс и ученики; M:M — студенты и курсы (нужна таблица связей). FOREIGN KEY (customer_id) REFERENCES Customers(customer_id); ON DELETE CASCADE — удалить дочерние вместе с родителем, ON DELETE RESTRICT — запретить удаление родителя при наличии дочерних.

Запрос SELECT — декларативный: описываем, что получить.

Конструкция	Смысл
=, <>, !=, <, >	сравнение
AND, OR, NOT	логика
BETWEEN a AND b	диапазон
IN (…)	множество значений
LIKE 'Ахмет%'	шаблон: % — любое число символов, _ — ровно один
IS NULL	пустое значение
ORDER BY … ASC/DESC	сортировка (по умолчанию ASC)
LIMIT n	первые n строк
INNER JOIN	только совпадающие строки обеих таблиц
LEFT JOIN	все строки левой + совпадения справа (иначе NULL)

Отчёты. Данные агрегируются: COUNT(*), SUM, AVG, MIN, MAX. WHERE фильтрует строки до группировки, HAVING — группы после. Структура отчёта: заголовок, верхний колонтитул (названия колонок), область данных, итоги, нижний колонтитул.

Формы. В настольных СУБД (Access, LibreOffice Base) — конструктор форм; в вебе — тег <form>: action — адрес серверного скрипта, method — GET (данные в URL, для поиска) или POST (в теле запроса, для добавления записей и конфиденциальных данных). Элементы: <input type="text|password|number|date|submit">, <textarea>, <select>/<option>, атрибут required.

Примеры

1. Ученики 10 класса из Астаны по алфавиту, не больше 20.

SELECT last_name, first_name FROM Students
WHERE city = 'Астана' AND grade = 10
ORDER BY last_name ASC
LIMIT 20;

2. Классы со средним баллом не ниже 4.

SELECT class_name, COUNT(student_id) AS total, AVG(gpa) AS avg_gpa
FROM Students JOIN Classes ON Students.class_id = Classes.class_id
GROUP BY class_name
HAVING AVG(gpa) >= 4.0;

3. Какой тип выбрать для цены товара? DECIMAL(10, 2) — без ошибок округления.
4. Тот же запрос из Python (модуль sqlite3)

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE Students (id INTEGER PRIMARY KEY, name TEXT, grade INT)")
con.executemany("INSERT INTO Students (name, grade) VALUES (?, ?)",
                [("Арман", 10), ("Айзере", 11), ("Дмитрий", 10)])
rows = con.execute("SELECT name FROM Students WHERE grade = 10 ORDER BY name").fetchall()
print(rows)   # [('Арман',), ('Дмитрий',)]

Типичные ошибки на ЕНТ

• Относят SELECT к DDL — это DML; CREATE — DDL.
• Путают WHERE и HAVING: агрегатные условия (AVG(...) > …) пишут в HAVING.
• Забывают WHERE в DELETE/UPDATE — меняются все строки.
• Путают % (любое число символов) и _ (один символ) в LIKE.
• Пытаются реализовать M:M без промежуточной таблицы.
• Считают DESC сортировкой по умолчанию — по умолчанию ASC.

Самопроверка

1. Какая команда создаёт таблицу? — CREATE TABLE.
2. Что вернёт LIKE 'А_'? — Строки из двух символов, начинающиеся с «А».
3. Какой тип связи между «Класс» и «Ученики»? — Один-ко-многим (1:M).
4. Какая функция считает число записей? — COUNT.
5. Каким методом формы передавать пароль? — POST.