Практическое руководство по MySQL
SQL - это язык программирования, используемый для управления данными, хранящимися в реляционных базах данных, и их обслуживания. Реляционная база данных состоит из таблиц с помеченными строками и столбцами.
Одна из наиболее частых операций с SQL - запрос к базе данных для получения данных. Типичная реляционная база данных содержит множество таблиц, связанных друг с другом с помощью общих значений в столбце, известном как внешний ключ.
По мере увеличения количества таблиц написание запросов становится сложным. Кроме того, нам обычно требуется фильтровать или преобразовывать данные при извлечении из базы данных. Таким образом, мы пишем сложные и длинные запросы для получения желаемых данных.
Один или два раза можно написать сложный запрос. Однако, если нам нужно использовать его много раз, необходим более практичный подход. Один из вариантов - сохранить запрос в текстовом или sql-файле и при необходимости скопировать его. Это нормально, но есть лучший вариант - представления SQL или просто представления.
Представление - это сохраненный запрос. Представления сохраняются на сервере базы данных, поэтому нам не нужно копировать их из какого-либо другого файла. Мы создаем представление, присваивая имя запросу.
Начнем с примера, чтобы иметь представление о том, как выглядит представление. После этого я более подробно остановлюсь на примерах и объясню взгляды.
Я создал таблицу клиентов и заказов с фиктивными данными. Таблицы содержат некоторую информацию о клиентах компании и их заказах. Вот обзор таблиц:
Таблицы связаны столбцом идентификатора клиента (cust_id), поэтому мы используем столбец идентификатора клиента для объединения этих таблиц.
Рассмотрим случай, когда необходимо найти общую сумму заказа для каждого местоположения. Вот запрос, выполнивший эту задачу.
#Example 1
mysql> select customer.location, sum(orders.amount)
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id
-> group by customer.location;
+----------+--------------------+
| location | sum(orders.amount) |
+----------+--------------------+
| Austin | 9811.09 |
| Dallas | 23288.40 |
| Houston | 12978.33 |
+----------+--------------------+
Выбираем место из таблицы клиентов и сумму суммы из таблицы заказов. Таблицы объединяются по столбцу идентификатора клиента. Предложение group by группирует записи (т. Е. Строки) по местоположению.
Мы можем сохранить этот запрос, используя следующее представление:
#Example 2
mysql> create view totalAmount
-> as
-> select customer.location, sum(orders.amount)
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id
-> group by customer.location;
Оператор «создать представление» используется вместе с именем для представления. Затем мы пишем запрос после ключевого слова «as».
Как вы заметили, создание представления ничего не возвращает или не выполняет оператор select. Представления генерируют набор результатов только тогда, когда они вызываются.
Мы можем вызывать представления так же, как мы запрашиваем таблицу. Вот как это делается:
#Example 3 mysql> select * from totalAmount; +----------+--------------------+ | location | sum(orders.amount) | +----------+--------------------+ | Austin | 9811.09 | | Dallas | 23288.40 | | Houston | 12978.33 | +----------+--------------------+
В приведенном выше примере представления выглядят как таблицы. С точки зрения запросов представления можно рассматривать как таблицы. Однако они не хранят никаких данных. После выполнения представления SQL извлекает данные из таблиц, указанных внутри представления. Таким образом, представления иногда называют виртуальными таблицами.
Примечание. Важно подчеркнуть, что мы используем MySQL в качестве системы управления реляционными базами данных (СУБД). Хотя синтаксис SQL в разных RDBM в основном одинаков, могут быть небольшие различия.
Мы можем фильтровать представление, используя предложение where.
#Example 4
mysql> select * from totalAmount
-> where location = "Austin";
+----------+--------------------+
| location | sum(orders.amount) |
+----------+--------------------+
| Austin | 9811.09 |
+----------+--------------------+
Мы можем фильтровать только на основе столбцов, включенных в представление. Например, в таблице заказов есть столбец с датой, но его нельзя использовать для фильтрации. Представление totalAmount относится только к местоположению и сумме суммы. Это единственные два значения, которые мы можем использовать для фильтрации.
Давайте создадим другое представление, которое позволяет фильтровать по столбцам даты и суммы.
#Example 5
mysql> create view cityOrders
-> as
-> select
-> customer.location,
-> orders.date,
-> orders.amount
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id;
Представление cityOrders относится к трем столбцам, которые представляют собой местоположение из таблицы клиентов, а также дату и сумму из таблицы заказов.
Мы можем использовать представление cityOrders для расчета общей суммы заказа для каждого местоположения на определенную дату.
#Example 6
mysql> select location, sum(amount)
-> from cityOrders
-> where date = '2020-01-31'
-> group by location;
+----------+-------------+
| location | sum(amount) |
+----------+-------------+
| Austin | 77.94 |
| Dallas | 260.77 |
| Houston | 72.46 |
+----------+-------------+
Также мы можем рассчитать количество заказов, превышающих определенную сумму для каждого города.
#Example 7
mysql> select location, count(amount)
-> from cityOrders
-> where amount > 20
-> group by location;
+----------+---------------+
| location | count(amount) |
+----------+---------------+
| Austin | 160 |
| Dallas | 383 |
| Houston | 205 |
+----------+---------------+
Хотя представления totalAmount и cityOrders относятся к столбцу суммы, мы не можем использовать суммы отдельных заказов для фильтрации в представлении totalAmount. Причина в том, что представление totalAmount относится к сумме сумм, а не к суммам отдельных заказов.
Мы можем обновить представление, используя операторы «изменить представление» или «создать или заменить представление». Разница в том, что для использования оператора «alter view» представление должно существовать. Если мы не уверены, существует ли уже представление, мы должны использовать оператор «создать или заменить представление».
Давайте изменим представление cityOrders.
#Example 8
mysql> alter view cityOrders
-> as
-> select
-> customer.location,
-> customer.age,
-> orders.date,
-> orders.amount
-> from customer
-> join orders
-> on customer.cust_id = orders.cust_id;
Мы добавили столбец возраста из таблицы клиентов в представление cityOrders. Теперь мы можем использовать возраст для фильтрации или агрегирования. Следующий запрос возвращает среднее количество заказов клиентов старше 30 лет. Он также группирует результаты по местоположению.
#Example 9
mysql> select location, avg(amount)
-> from cityOrders
-> where age > 30
-> group by location;
+----------+-------------+
| location | avg(amount) |
+----------+-------------+
| Austin | 53.740153 |
| Dallas | 47.069703 |
| Houston | 49.287771 |
+----------+-------------+
Если представление нам больше не нужно, мы можем его отбросить. Синтаксис такой же, как при удалении таблицы.
#Example 10 mysql> drop view totalAmount; Query OK, 0 rows affected (0.00 sec) mysql> select * from totalAmount; ERROR 1146 (42S02): Table 'practice.totalAmount' doesn't exist
Заключение
Представление - важная концепция для SQL. Использование представления дает множество преимуществ. В первую очередь, это упрощает сложные запросы. Примеры, которые мы сделали, включали соединение двух таблиц. В случае больших баз данных с большим количеством таблиц запросы могут быть очень сложными. Таким образом, представления предоставляют более практичный способ.
Представления также избавляют нас от повторения одних и тех же запросов снова и снова. Представьте, что у нас есть очень сложный рутинный расчет, который включает в себя несколько фильтров, агрегатов и вложенных операторов выбора. Гораздо лучше иметь его на виду, чем каждый раз писать запрос.
У нас также есть больший контроль над доступом к базе данных с помощью представлений. Таблица может содержать конфиденциальную информацию, доступ к которой имеют только определенные сотрудники. Используя представления, мы можем предоставить доступ только к определенным столбцам таблицы.
Спасибо за чтение. Пожалуйста, дайте мне знать, если у вас есть какие-либо отзывы.
