Поиск по сайту:

Нам хорошо иногда, Богу — всегда (Аристотель).

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

4 мин для чтения
FavoriteLoadingДобавить в избранное
1 Звезда2 Звезды3 Звезды4 Звезды5 Звезд (1 оценок, среднее: 5,00 из 5)
Загрузка...
17 февраля 2021
Как установить MySQL 8.0 На Ubuntu 18.04
Общее табличное выражение (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' );

 

Читать  Как обновить WordPress до MySQL 8 на CentOS

Использование простого 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.

Читать  Выбор процессора для SQL Server

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Поделиться в соц. сетях:
5 1 голос
Рейтинг статьи
Подписаться
Уведомить о
guest
0 комментариев
Межтекстовые Отзывы
Посмотреть все комментарии

Читайте также

0
Оставьте комментарий! Напишите, что думаете по поводу статьи.x
()
x

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам:

Заполните форму и наш менеджер перезвонит Вам в самое ближайшее время!

badge
Обратный звонок 1
Отправить
galka

Спасибо! Ваша заявка принята

close
galka

Спасибо! Ваша заявка принята

close