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

Разбор темы

Этапы разработки базы данных 10 класс

1) Анализ предметной области: какие объекты есть (ученики, курсы, учителя), какую информацию о них хранить, какие запросы понадобятся. 2) Инфологическое (концептуальное) проектирование: ER-диаграмма. 3) Логическое проектирование: переход к таблицам, выбор полей, первичных и внешних ключей, нормализация. 4) Физическое проектирование: выбор СУБД, типов данных, индексов. 5) Создание таблиц (CREATE TABLE) и заполнение данными. 6) Создание запросов, форм и отчётов; тестирование.

Однотабличная БД (цель 10.3.2.2) подходит для простых списков: телефонный справочник, список книг. Как только в таблице начинают повторяться одни и те же данные (название курса и фамилия учителя в каждой строке), её разбивают на несколько связанных таблиц — получается многотабличная БД (цель 10.3.2.3).

ER-диаграмма: сущности, атрибуты, связи 10 класс

Сущность изображают прямоугольником, атрибут — овалом (или списком внутри прямоугольника), ключевой атрибут подчёркивают, связь — ромбом или линией с подписью. На концах линии указывают кратность: 1 или М (N, «вороньи лапки»).

Пример «Школа»: сущности Ученик(id, имя, класс, город, балл) и Курс(id, название, учитель); связь «записан на» типа М:М с собственным атрибутом «оценка». При переходе к таблицам каждая сущность становится таблицей (students, courses), атрибуты — полями, а связь М:М — отдельной таблицей enroll(student_id, course_id, mark).

Правила перехода: связь 1:М — внешний ключ в таблицу на стороне «М»; связь 1:1 — внешний ключ в любую из таблиц (или объединение таблиц); связь М:М — новая таблица-связка с двумя внешними ключами, которые вместе образуют составной первичный ключ.

Создание и изменение данных: CREATE, INSERT, UPDATE, DELETE 10 класс

Команды SQL делятся на группы: DDL — описание структуры (CREATE TABLE, ALTER TABLE, DROP TABLE) и DML — работа с данными (SELECT, INSERT, UPDATE, DELETE). Ключевые слова принято писать заглавными буквами, команда завершается точкой с запятой, строковые значения берутся в одинарные кавычки.

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

INSERT INTO students(name, grade, city, score) VALUES ('Алия', 11, 'Астана', 117); — добавляет одну запись (можно перечислить несколько наборов VALUES через запятую). UPDATE students SET score = score + 10 WHERE city = 'Шымкент'; — меняет значения в отобранных записях. DELETE FROM enroll WHERE mark < 4; — удаляет отобранные записи.

Главная опасность: UPDATE и DELETE без WHERE действуют на ВСЕ записи таблицы. DELETE FROM students; очищает таблицу целиком (структура остаётся), а DROP TABLE students; удаляет и саму таблицу. Ограничения NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY ... REFERENCES не дают внести некорректные данные.

SELECT: выборка, сортировка, DISTINCT, LIMIT 10 класс

Общий вид: SELECT поля FROM таблица WHERE условие GROUP BY поле HAVING условие ORDER BY поле LIMIT n; — части пишутся именно в таком порядке, обязательны только SELECT и FROM. * означает все поля. AS даёт столбцу псевдоним: SELECT name AS ученик.

ORDER BY сортирует по возрастанию (ASC, по умолчанию) или убыванию (DESC); можно по нескольким полям: ORDER BY grade, score DESC. LIMIT n оставляет первые n строк результата — вместе с ORDER BY DESC это «топ-n». DISTINCT убирает повторяющиеся строки: SELECT DISTINCT city FROM students выведет 3 строки, а без DISTINCT — 8.

Порядок, в котором СУБД логически выполняет запрос: FROM (и JOIN) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Поэтому WHERE не может использовать агрегатные функции, а LIMIT срабатывает после сортировки.

Условия: сравнения, AND/OR/NOT, BETWEEN, IN, LIKE 10 класс

В WHERE используют =, <> (или !=), >, <, >=, <= и логические AND, OR, NOT (приоритет NOT → AND → OR, скобки меняют порядок).

BETWEEN a AND b — значение в диапазоне от a до b ВКЛЮЧИТЕЛЬНО: score BETWEEN 91 AND 112 отберёт Нурлана (97), Дану (112), Мадину (103) и Санжара (91) — 4 записи. IN (список) — значение совпадает с одним из перечисленных: city IN ('Астана', 'Шымкент') то же, что два условия через OR. NOT IN — не совпадает ни с одним.

LIKE — сравнение строки с шаблоном: % — любое число любых символов (в том числе ноль), _ — ровно один символ. name LIKE 'А%' — начинается на «А» (Айгерим, Аружан); name LIKE '%н' — кончается на «н» (Нурлан, Ерлан, Аружан); name LIKE '_а%' — вторая буква «а» (Дана, Мадина, Санжар). В конструкторе запросов Access вместо % и _ используются * и ?, как в масках файлов.

IS NULL / IS NOT NULL проверяют пустое значение; сравнение = NULL не работает.

Агрегатные функции, GROUP BY и HAVING 10 класс

Агрегатные функции сворачивают много строк в одно значение: COUNT(*) — число строк, COUNT(поле) — число непустых значений, SUM — сумма, AVG — среднее, MIN, MAX — наименьшее и наибольшее. Для students: COUNT(*) = 8, SUM(score) = 816, AVG(score) = 102, MAX = 135, MIN = 66.

GROUP BY разбивает строки на группы с одинаковым значением поля и считает агрегат для каждой группы; в результате столько строк, сколько групп. SELECT city, COUNT(*) FROM students GROUP BY city → 3 строки: Алматы 3, Астана 3, Шымкент 2.

HAVING фильтрует уже готовые группы по агрегату, а WHERE — отдельные строки до группировки. ... GROUP BY city HAVING AVG(score) > 100 → Алматы (≈107,7) и Шымкент (107,5); Астана (≈92,7) отсекается. Запомните: условие с COUNT/SUM/AVG/MIN/MAX — в HAVING, обычное условие — в WHERE.

Соединение таблиц: JOIN 10 класс

JOIN (INNER JOIN) соединяет строки двух таблиц, у которых совпадают значения связанных полей: FROM enroll JOIN students ON students.id = enroll.student_id. В результат попадают только пары, нашедшие «партнёра». Чтобы различать одноимённые поля, пишут таблица.поле (students.id, courses.id).

SELECT students.name, courses.title, enroll.mark
FROM enroll
JOIN students ON students.id = enroll.student_id
JOIN courses ON courses.id = enroll.course_id
WHERE enroll.mark = 5;

Этот запрос выводит 7 строк: Айгерим — Математика и Информатика, Дана — Информатика, Аружан — Математика, Информатика и Физика, Санжар — История. LEFT JOIN оставляет все строки левой таблицы, даже если пары нет (недостающие поля будут NULL).

JOIN часто сочетают с GROUP BY: средняя оценка по каждому курсу — Информатика 4,5, История ≈4,33, Математика 4,25, Физика 4,0.

Формы, отчёты и связь с веб-страницей 10 класс

Форма (цель 10.3.2.4) — окно для удобного ввода и просмотра данных: поля ввода, выпадающие списки, флажки, кнопки «Сохранить», «Следующая запись». Пользователь не видит таблицу целиком и меньше ошибается; за кнопкой «Сохранить» стоит команда INSERT или UPDATE.

Отчёт (цель 10.3.2.5) — оформленный для печати или экспорта результат запроса: заголовок, группировка (например, по городам), промежуточные и общие итоги (COUNT, AVG), колонтитулы. Отчёт только показывает данные, изменить их через отчёт нельзя.

Связь веб-страницы с БД (цель 10.3.3.2): пользователь заполняет HTML-форму <form> → браузер отправляет данные на сервер → серверный скрипт (PHP, Python) формирует SQL-запрос к СУБД (MySQL, PostgreSQL) → результат превращается в HTML-страницу. Так работают интернет-магазины и электронные журналы. Данные из формы нельзя вставлять в запрос напрямую — это открывает дорогу атаке SQL-инъекция; используют параметризованные запросы.