WITH в MySQL: общее табличное выражение (CTE)

Общее табличное выражение (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.

(2 оценок, среднее: 5,00 из 5)

Загрузка...