MySQL - производные таблицы

Здравствуйте,野心勃勃的数据库爱好者们！我很高兴能成为您在这激动人心的MySQL производные таблицы之旅中的向导。作为一个教计算机科学多年的老师，我可以向您保证，虽然这个主题一开始听起来可能令人畏惧，但实际上它非常有趣且非常有用。那么，让我们开始吧！

Что такое производные таблицы в MySQL?

Представьте, что вы организовываете большой праздник (ведь кто не любит хорошую数据库 вечеринку, правда?). У вас есть список всех ваших друзей, но вы хотите создать отдельный список только тех, кто живет поблизости. Вот что делает производная таблица в MySQL - это временная таблица, созданная внутри запроса, чтобы помочь вам организовать и анализировать ваши данные более эффективно.

На техническом языке производная таблица - это подзапрос, который出现在 предложении FROM другого SQL-предложения. Это как создание временной таблицы на ходу, которую вы затем можете использовать в вашем основном запросе.

Давайте рассмотрим простой пример:

SELECT * FROM
(SELECT name, age FROM people WHERE age > 18) AS adults
WHERE adults.age < 30;

В этом запросе все, что находится в скобках,是我们的 производная таблица. Мы создаем временную таблицу всех людей старше 18 лет, а затем выбираем из этой таблицы, чтобы найти тех, кто младше 30 лет.

Использование предложения WHERE с производными таблицами

Предложение WHERE - ваш лучший друг при работе с производными таблицами. Оно позволяет вам фильтровать данные как в производной таблице, так и в основном запросе. Давайте расширить наш предыдущий пример:

SELECT * FROM
(SELECT name, age, city FROM people WHERE age > 18) AS adults
WHERE adults.city = 'New York' AND adults.age < 30;

Здесь мы сначала создаем производную таблицу всех взрослых (возраст > 18), а затем из этой таблицы выбираем только тех, кто живет в Нью-Йорке и младше 30 лет. Это как двойной фильтр!

Присвоение имени столбцу в производной таблице

Иногда вам может понадобиться дать вашим столбцам более описательные имена в производной таблице. Вот где aliasing comes in handy. Давайте посмотрим:

SELECT young_adult_name, years_alive FROM
(SELECT name AS young_adult_name, age AS years_alive
FROM people
WHERE age BETWEEN 18 AND 25) AS young_adults;

В этом примере мы дали имя 'name' как 'young_adult_name', а 'age' как 'years_alive'. Это может сделать ваши запросы более читаемыми и понятными.

Отображение агрегатных функций как производных таблиц

Агрегатные функции, такие как COUNT, AVG, SUM, могут быть super useful в производных таблицах. Давайте представим, что мы хотим узнать, сколько людей у нас в каждой возрастной группе:

SELECT age_group, count FROM
(SELECT
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age BETWEEN 18 AND 30 THEN '18-30'
WHEN age BETWEEN 31 AND 50 THEN '31-50'
ELSE 'Over 50'
END AS age_group,
COUNT(*) as count
FROM people
GROUP BY age_group) AS age_distribution
ORDER BY count DESC;

Этот запрос создает производную таблицу, которая分组ирует людей по возрастным категориям и подсчитывает, сколько людей в каждой группе. Затем мы выбираем из этой производной таблицы, чтобы отобразить результаты.

Создание таблицы с использованием клиентской программы

Хотя мы обычно пишем наши запросы напрямую в MySQL, вы также можете создавать производные таблицы с использованием клиентских программ. Вот простой пример на Python:

import mysql.connector

# Подключиться к базе данных
cnx = mysql.connector.connect(user='your_username', password='your_password',
host='127.0.0.1', database='your_database')
cursor = cnx.cursor()

# Создать запрос с производной таблицей
query = """
SELECT * FROM
(SELECT name, age FROM people WHERE age > 18) AS adults
WHERE adults.age < 30;
"""

# Выполнить запрос
cursor.execute(query)

# Получить и напечатать результаты
for (name, age) in cursor:
print(f"{name} is {age} years old")

# Закрыть соединение
cursor.close()
cnx.close()

Этот скрипт Python подключается к вашей базе данных MySQL, выполняет запрос с производной таблицей и затем выводит результаты.

Заключение

И вот мы arrived at the end of our journey through the land of MySQL Derived Tables, from basic concepts to more advanced uses. Remember, derived tables are like your secret weapon in SQL - they allow you to create temporary tables on the fly, helping you organize and analyze your data in powerful ways.

As with all things in programming, practice makes perfect. So don't be afraid to experiment with derived tables in your own projects. Who knows? You might just find yourself throwing the best database party in town!

Вот быстрое справочное руководство по методам, которые мы рассмотрели:

Метод	Описание
Основная производная таблица	Создание временной таблицы в предложении FROM
Предложение WHERE	Фильтрация данных в производной таблице и основном запросе
Присвоение имени	Давать столбцам более описательные имена в производной таблице
Агрегатные функции	Использование функций, таких как COUNT, AVG, SUM, в производных таблицах
Клиентская программа	Создание производных таблиц с использованием внешних языков программирования

Счастливого запроса, и пусть ваши базы данных всегда будут нормализованы!

Credits: Image by storyset