Занятие 30. Отработка основных операторов. Продолжение

📁 Блок: SQL / MySQL / Повторение и закрепление ⏱️ Время изучения: ~90 мин 🎯 Сложность: Средняя

⚡ Кратко: суть темы

Решаем задачи на базе "computer firm" из четырёх таблиц. Главное — правильно выбрать оператор: фильтр, JOIN, группировка, подзапрос или самосоединение.

Схема: Product(maker, model, type), PC(code, model, speed, ram, hd, cd, price), Laptop(..., screen), Printer(..., color, type, price).
WHERE — фильтр строк; HAVING — фильтр групп.
JOIN нужен, когда данные разнесены по таблицам.
Подзапросы ищут экстремальные значения; самосоединение — пары по одинаковым параметрам.

Что запомнить: порядок выполнения — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Частая ошибка: пытаться отфильтровать результат агрегатной функции в WHERE.

📖 Схема базы данных "computer firm"

Занятие использует классическую учебную схему из четырёх таблиц:

Product — производитель, модель, тип продукта.
PC — персональные компьютеры и их характеристики.
Laptop — ноутбуки и их характеристики.
Printer — принтеры и их характеристики.

CREATE TABLE Product (
    maker VARCHAR(50),
    model VARCHAR(50) PRIMARY KEY,
    type  VARCHAR(50)
);

CREATE TABLE PC (
    code  INT PRIMARY KEY,
    model VARCHAR(50),
    speed INT,
    ram   INT,
    hd    INT,
    cd    VARCHAR(10),
    price DECIMAL(10,2)
);

CREATE TABLE Laptop (
    code   INT PRIMARY KEY,
    model  VARCHAR(50),
    speed  INT,
    ram    INT,
    hd     INT,
    screen DECIMAL(4,2),
    price  DECIMAL(10,2)
);

CREATE TABLE Printer (
    code  INT PRIMARY KEY,
    model VARCHAR(50),
    color CHAR(1),
    type  VARCHAR(50),
    price DECIMAL(10,2)
);

Связь между таблицами — по полю model: таблица Product хранит общую информацию о производителе и типе, а таблицы PC, Laptop и Printer — специфические характеристики каждой модели.

📖 Повторение операторов

ORDER BY

Сортирует результат по одному или нескольким столбцам. По умолчанию — по возрастанию (ASC).

SELECT model, price
FROM PC
ORDER BY price DESC, model ASC;

LIMIT и OFFSET

LIMIT ограничивает число строк, OFFSET пропускает первые N строк. Используются для постраничного вывода.

SELECT * FROM PC
ORDER BY price DESC
LIMIT 5 OFFSET 10;

DISTINCT

Удаляет полные дубликаты строк. Применяется к одному или нескольким столбцам.

SELECT DISTINCT maker FROM Product WHERE type = 'Printer';

Группировка и HAVING

GROUP BY объединяет строки в группы. HAVING фильтрует группы после агрегации.

SELECT hd, COUNT(*) AS pc_count
FROM PC
GROUP BY hd
HAVING COUNT(*) >= 2;

📖 Соединение таблиц

JOIN используется, когда в запросе нужны данные из нескольких таблиц. В учебной схеме Product связывается с PC, Laptop и Printer по полю model.

SELECT DISTINCT maker, speed
FROM Product
JOIN Laptop USING (model)
WHERE hd >= 10;

Здесь мы узнаём производителя и скорость ноутбуков с жёстким диском не менее 10 Гбайт.

📖 Подзапросы

Подзапрос — это запрос внутри другого запроса. Часто используется для поиска максимальных и минимальных значений.

SELECT model, price
FROM Printer
WHERE price = (SELECT MAX(price) FROM Printer);

Внутренний запрос находит максимальную цену, внешний — выбирает модели с такой ценой.

📖 Самосоединение

Самосоединение применяется, когда нужно сравнить строки одной таблицы между собой. Для этого таблице дают два псевдонима.

SELECT DISTINCT pc1.model, pc2.model, pc1.speed, pc1.ram
FROM PC AS pc1
JOIN PC AS pc2
  ON pc1.speed = pc2.speed
 AND pc1.ram   = pc2.ram
 AND pc1.model > pc2.model;

Условие pc1.model > pc2.model гарантирует, что каждая пара встретится только один раз.

📖 Порядок выполнения SQL-запроса

FROM — выбор таблиц и соединений.
WHERE — фильтрация строк.
GROUP BY — группировка.
HAVING — фильтрация групп.
SELECT — выбор столбцов и вычисления.
DISTINCT — удаление дубликатов.
ORDER BY — сортировка.
LIMIT/OFFSET — ограничение результата.