MySQL - Общие таблицы выражений: Пособие для начинающих

Здравствуйте, будущие маги баз данных! Сегодня мы отправимся в увлекательное путешествие в мир общих таблиц выражений MySQL (CTE). Не волнуйтесь, если вы новички в программировании - я буду вашим доброжелательным проводником, и мы будем двигаться шаг за шагом. К концу этого руководства вы будете создавать CTE, как профи!

MySQL - Common Table Expressions

Что такое общие таблицы выражений?

Прежде чем мы углубимся, давайте начнем с азов. Представьте, что вы организовываете большой праздник (кто не любит хороший базовый праздник, верно?). Вы можете составить список гостей, список блюд и список мероприятий. Эти списки помогают вам систематизировать мысли и упрощают планирование. Общие таблицы выражений resemble такие списки, но для ваших запросов к базе данных!

Общая таблица выражений, или CTE для краткости, это временныйnamed результат, к которому вы можете обращаться в SELECT, INSERT, UPDATE, DELETE или MERGE операторах. Это как создание временной таблицы, существующей только дляduration вашего запроса. Круто, правда?

Клаузула MySQL WITH: ваша魔法ательная палочка для CTE

В MySQL мы используем клаузулу WITH для создания CTE. Это как сказать: "Эй MySQL, я хочу создать временный результат, и я буду называть его X". Давайте рассмотрим простой пример:

WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;

Давайте разберем это:

WITH говорит MySQL, что мы собираемся определить CTE.
cte_name - это имя, которое мы даем нашему CTE (вы можете выбрать любое имя, которое вам нравится).
AS следует за запросом, который определяет, какие данные будут в нашем CTE.
После определения CTE у нас есть основной запрос, который использует CTE.

Теперь давайте рассмотрим реальный пример. Представьте, что у нас есть таблица студентов и их оценок:

CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
grade INT
);

INSERT INTO students VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 78),
(4, 'Diana', 95),
(5, 'Eva', 88);

WITH high_achievers AS (
SELECT name, grade
FROM students
WHERE grade > 90
)
SELECT * FROM high_achievers;

В этом примере мы создали CTE под названием high_achievers, который включает только студентов с оценками выше 90. Затем мы选择了 все столбцы из этого CTE. Результат покажет Боба и Диану, наших отличников!

CTE из нескольких таблиц: участие в празднике

CTE не ограничиваются одной таблицей. О, нет, они могут быть такими сложными, как вам нужно! Давайте добавим таблицу курсов к нашей школе базы данных:

CREATE TABLE courses (
id INT PRIMARY KEY,
name VARCHAR(50),
teacher VARCHAR(50)
);

INSERT INTO courses VALUES
(1, 'Math', 'Mr. Smith'),
(2, 'Science', 'Ms. Johnson'),
(3, 'History', 'Mrs. Brown');

CREATE TABLE enrollments (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO enrollments VALUES
(1, 1), (1, 2), (2, 2), (3, 3), (4, 1), (4, 3), (5, 2);

WITH student_courses AS (
SELECT s.name AS student_name, c.name AS course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
),
science_students AS (
SELECT student_name
FROM student_courses
WHERE course_name = 'Science'
)
SELECT * FROM science_students;

Ух, это было много! Давайте разберем это:

Мы создали две новые таблицы: courses и enrollments.
Мы определили CTE под названием student_courses, который объединяет эти таблицы, чтобы получить список студентов и их курсов.
Мы определили другой CTE под названием science_students, который использует первый CTE, чтобы найти студентов, изучающих науку.
Наконец, мы选择了 всех студентов-учащихся.

Этот запрос покажет нам Элис, Боба и Еву - наших будущих ученых!

Мощь CTE: почему вам должно быть это важно?

Вы можете задаваться вопросом: "Зачем тратить столько усилий? Разве я не могу просто использовать подзапросы?" Отличный вопрос! CTE предлагают несколько преимуществ:

Читаемость: CTE делают сложные запросы легче для чтения и понимания. Это как разбиение большой проблемы на более мелкие, управляемые части.
Повторное использование: Вы можете ссылаться на CTE несколько раз в основном запросе, что может быть более эффективным, чем повторение подзапросов.
Рекурсия: CTE могут быть рекурсивными, что позволяет легко работать с иерархическими или древовидными данными (но это тема на другой день!).

Методы CTE: ваш швейцарский армейский нож для инструментов запросов

Давайте подытожим различные способы использования CTE в удобной таблице:

Метод	Описание	Пример
Основной CTE	Определение простого named результата	`WITH cte AS (SELECT * FROM table)`
Множественные CTE	Определение нескольких CTE в одном запросе	`WITH cte1 AS (...), cte2 AS (...)`
Вложенные CTE	Использование одного CTE внутри другого	`WITH outer_cte AS (WITH inner_cte AS (...) SELECT * FROM inner_cte)`
Рекурсивные CTE	Создание CTE, который ссылается на сам себя	`WITH RECURSIVE cte AS (...)`

Помните, как и любое мощное средство, используйте CTE мудро. Они великолепны для организации сложных запросов, но для простых операций может хватить обычного SELECT.

Заключение: ваше путешествие с CTE начинается!

Поздравляю! Вы сделали первые шаги в мир общих таблиц выражений. Мы рассмотрели основы, узнали, как использовать клаузулу WITH, и даже разобрали CTE из нескольких таблиц. Помните, что практика makes perfect, поэтому не бойтесь экспериментировать с своими базами данных.

Пока вы продолжаете свое путешествие в MySQL, вы обнаружите, что CTE - это неоценимые инструменты в вашем наборе для создания запросов. Они помогут вам писать чище, эффективнее и более maintainable код. И кто знает? Может быть,有一天 вы будете учить других магии CTE!

Продолжайте программировать, оставайтесь любопытными, и пусть ваши запросы всегда возвращают ожидаемые результаты! До свидания, счастливого управления данными!

Credits: Image by storyset

Предыдущий учебник:

MySQL - UUID

Следующий учебник:

MySQL - On Delete Cascade