Общее табличное выражение (CTE) – важная функция MySQL, которая используется для генерации временного набора результатов. Его можно использовать с любым оператором SQL, таким как SELECT, INSERT, UPDATE и т. д. Сложные запросы можно упростить с помощью CTE. Набор результатов любого запроса сохраняется как объект для производной таблицы во время выполнения запроса. Но CTE может ссылаться на себя, что означает, что на один и тот же запрос можно ссылаться несколько раз с помощью CTE. По этой причине производительность CTE лучше, чем у производной таблицы. Предложение WITH используется для определения CTE, и с помощью этого предложения в одном операторе можно определить более одного CTE. В этой статье объясняется, как можно применить CTE в запросе, чтобы сделать его более читаемым и повысить производительность запроса.
Преимущества использования CTE:
- Это делает запрос более читабельным.
- Это улучшает производительность запросов.
- Его можно использовать как альтернативу VIEW.
- Для упрощения запроса можно создать цепочку CTE.
- Рекурсивные запросы можно легко реализовать с помощью CTE.
Синтаксис:
WITH CTE-Name (column1,column2,… columnn) AS ( Query ) SELECT * FROM CTE-Name;
Здесь вы можете определить любой оператор SQL как запрос, SELECT, UPDATE, DELETE, INSERT или CREATE. Если вы определяете список столбцов в предложении WITH, то количество столбцов в запросе должно совпадать с количеством столбцов, определенным в предложении WITH.
Предпосылка:
Функция CTE не поддерживается ни одной версией MySQL ниже 8.0. Итак, вам необходимо установить MySQL 8.0, прежде чем практиковаться в примере из этой статьи. Вы можете проверить текущую установленную версию MySQL, выполнив следующую команду.
$ mysql -V
Вывод показывает, что в системе установлен MySQL версии 8.0.19.
Если установлена правильная версия, создайте базу данных с именем mydb и две таблицы с именами users и users_profile с некоторыми данными, чтобы узнать об использовании CTE в MySQL. Выполните следующие операторы SQL для выполнения задач. Эти операторы создадут две связанные таблицы с именами users и users_profile. Затем некоторые данные будут вставлены в обе таблицы операторами INSERT.
CREATE DATABASE mydb;
USE mydb;
CREATE TABLE users (
username VARCHAR(50) PRIMARY KEY,
password VARCHAR(50) NOT NULL,
status VARCHAR(10) NOT NULL);
CREATE TABLE users_profile (
username VARCHAR(50) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE);
INSERT INTO users values
('admin' , '7856', 'Active'),
('staff' , '90802', 'Active'),
('manager' , '35462', 'Inactive');
INSERT INTO users_profile values
('admin', 'Administrator' , 'andreyex', 'admin@andreyex.ru' ),
('staff', 'Progger' , 'destroyer', 'destroyer@andreyex.ru' ),
('manager', 'Tester' , 'maxim', 'maxim@andreyex.ru' );
Использование простого CTE:
Здесь создается очень простой CTE с именем cte_users_profile, в котором не определен список полей с именем CTE в предложении WITH, и он будет извлекать все данные из таблицы users_profile. Затем оператор SELECT используется для чтения всех записей из CTE cte_users_profile.
WITH cte_users_profile AS ( SELECT * FROM users_profile ) SELECT * FROM cte_users_profile;
Использование простого CTE со списком столбцов:
Вы можете создать CTE более конкретно, определив список полей с именем CTE в предложении WITH. В этом случае имена полей, определенные с помощью имени CTE, будут такими же, как имена полей, определенные в запросе SELECT внутри предложения WITH. Здесь поля имени и адреса электронной почты используются в обоих местах.
WITH cte_users_profile(name, email) AS ( SELECT name, email FROM users_profile ) SELECT * FROM cte_users_profile;
Использование простого CTE с предложением WHERE:
Оператор SELECT с предложением WHERE может быть определен в операторе CTE как другой запрос SELECT. Запрос SELECT с извлечением записей из таблиц users и users_profile, где значения поля username равны для обеих таблиц, а значение username не равно «staff».
WITH cte_users AS ( SELECT users.username, users_profile.name, users_profile.address, users_profile.email FROM users, users_profile WHERE users.username = users_profile.username and users_profile.username <> 'staff' ) SELECT name as Name, address as Address FROM cte_users;
Использование простого CTE с предложением GROUP BY:
В запросе, используемом в CTE, можно использовать любую агрегатную функцию. Следующий оператор CTE показывает использование запроса SELECT с функцией COUNT(). Первый оператор SELECT используется для отображения всех записей таблицы пользователей, а последний оператор SELECT используется для отображения вывода CTE, который подсчитывает общее количество активных пользователей из таблицы пользователей.
SELECT * FROM users; WITH cte_users AS ( SELECT COUNT(*) as total FROM users WHERE status='Active' GROUP BY status ) SELECT total as `Всего активных пользователей` FROM cte_users;
Использование простого CTE с оператором UNION:
Следующий оператор CTE показывает использование оператора UNION в операторе CTE. В выходных данных будут отображаться значения имени пользователя из таблицы пользователей, где значением статуса является «Inactive», и другие значения имени пользователя из таблицы users_profile .
WITH cte_users AS ( SELECT users.username FROM users WHERE status = 'Inactive' UNION SELECT users_profile.username FROM users_profile ) SELECT * FROM cte_users;
Использование простого CTE с LEFT JOIN:
Следующий оператор CTE показывает использование LEFT JOIN в CTE. В выходных данных будут отображаться значения полей имени и электронной почты из таблицы users_profile путем применения LEFT JOIN на основе поля имени пользователя между таблицами users и users_profile и условия WHERE, которое отфильтрует эти записи из таблицы пользователей, где значение статуса – «Inactive».
WITH cte_users AS ( SELECT name, email FROM users_profile LEFT JOIN users ON users.username= users_profile.username WHERE users.status = 'Inactive' ) SELECT * FROM cte_users;
Вывод:
Если вы хотите повысить производительность запроса и получить результат запроса быстрее, CTE – лучший вариант, чем другие варианты MySQL. Эта статья поможет пользователям MySQL очень легко изучить использование CTE для запроса SELECT.