Представления MySQL и виртуальные таблицы

Введение

В этой лабораторной работе вы научитесь работать с представлениями MySQL (MySQL views). Представление — это виртуальная таблица, основанная на результирующем наборе SQL-запроса. Представления полезны для упрощения сложных запросов, скрытия сложности данных и обеспечения уровня абстракции для доступа к данным. Вы научитесь создавать, обновлять, запрашивать и удалять представления MySQL.

Вы начнете с подключения к серверу MySQL, создания базы данных с именем company и ее заполнения таблицей employees. Затем вы создадите представление с именем sales_employees, которое будет фильтровать таблицу employees, чтобы отображать только сотрудников из отдела 'Sales'. Вы узнаете, как проверить структуру представления и запрашивать его как обычную таблицу.

Подключение к MySQL и создание базы данных/таблицы

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

Сначала откройте терминал в виртуальной машине LabEx. Вы должны уже находиться в каталоге ~/project.

Подключитесь к серверу MySQL от имени пользователя root. Поскольку у вас есть привилегии sudo, а пользователь root в MySQL настроен на использование плагина auth_socket, вы можете подключиться без пароля, используя sudo.

sudo mysql -u root

Теперь вы находитесь в оболочке MySQL. Приглашение изменится на mysql>.

В оболочке MySQL создайте базу данных с именем company:

CREATE DATABASE company;

Переключитесь на базу данных company, чтобы последующие команды выполнялись в пределах этой базы данных:

USE company;

Теперь создайте таблицу с именем employees для хранения информации о сотрудниках:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

Вставьте некоторые образцы данных в таблицу employees:

INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');

Вы можете проверить данные в таблице employees, выбрав все строки:

SELECT * FROM employees;

Вывод должен показать вставленные данные о сотрудниках:

+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department  |
+----+------------+-----------+----------+-------------+
|  1 | John       | Doe       | 60000.00 | Sales       |
|  2 | Jane       | Smith     | 75000.00 | Marketing   |
|  3 | Robert     | Jones     | 50000.00 | Sales       |
|  4 | Emily      | Brown     | 80000.00 | Engineering |
|  5 | Michael    | Davis     | 65000.00 | Marketing   |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)

Вы успешно подключились к MySQL, создали базу данных и заполнили таблицу данными.

Создание представления

На этом шаге вы создадите представление на основе таблицы employees. Представление — это сохраненный SQL-запрос, который действует как виртуальная таблица. Оно само по себе не хранит данные, а представляет данные из одной или нескольких базовых таблиц.

Вы должны по-прежнему находиться в оболочке MySQL, подключенной к базе данных company. Если нет, переподключитесь, используя sudo mysql -u root, а затем USE company;.

Мы создадим представление с именем sales_employees, которое будет отображать только сотрудников, работающих в отделе 'Sales'. Это представление будет выбирать определенные столбцы из таблицы employees.

CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

Это оператор определяет представление с именем sales_employees. Определение представления — это запрос SELECT, следующий за ключевым словом AS. Этот запрос выбирает столбцы id, first_name, last_name и salary из таблицы employees, но только для строк, где столбец department равен 'Sales'.

Чтобы убедиться, что представление было создано, вы можете вывести список таблиц и представлений в текущей базе данных:

SHOW TABLES;

Вы должны увидеть в списке как employees, так и sales_employees.

+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
| sales_employees   |
+-------------------+
2 rows in set (0.00 sec)

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

DESCRIBE sales_employees;

Это покажет столбцы, включенные в представление:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Вы успешно создали представление с именем sales_employees.

Запрос данных с использованием представления

На этом шаге вы научитесь запрашивать данные с использованием созданного вами представления sales_employees. Представления можно запрашивать так же, как и обычные таблицы, предоставляя упрощенный способ доступа к определенным подмножествам данных.

Чтобы получить все данные из представления sales_employees, используйте стандартный оператор SELECT:

SELECT * FROM sales_employees;

Этот запрос выполняет базовый оператор SELECT, определенный в представлении, и возвращает результат. Вы должны увидеть только сотрудников из отдела 'Sales':

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
|    3 | Robert     | Jones     | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)

Вы также можете применять дополнительную фильтрацию или сортировку к данным, полученным из представления. Например, чтобы найти сотрудника отдела продаж с зарплатой выше 55000:

SELECT * FROM sales_employees WHERE salary > 55000;

Это вернет только те строки из представления, где зарплата больше 55000:

+------+------------+-----------+----------+
| id   | first_name | last_name | salary   |
+------+------------+-----------+----------+
|    1 | John       | Doe       | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)

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

SELECT first_name, last_name FROM sales_employees;

Это отобразит только имена и фамилии сотрудников отдела продаж:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John       | Doe       |
| Robert     | Jones     |
+------------+-----------+
2 rows in set (0.00 sec)

Запрос представления по сути такой же, как и запрос таблицы, но представление предоставляет предопределенный фильтр и выборку столбцов из исходного источника данных.

Обновление определения представления с помощью ALTER VIEW

На этом шаге вы научитесь изменять определение существующего представления с помощью оператора ALTER VIEW. Это полезно, когда вам нужно изменить столбцы, включенные в представление, или критерии фильтрации.

В настоящее время представление sales_employees включает id, first_name, last_name и salary. Давайте изменим представление, чтобы оно также включало столбец department.

ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';

Этот оператор использует ALTER VIEW, за которым следует имя представления и новый запрос SELECT, определяющий представление. Новый запрос теперь включает столбец department.

Чтобы проверить, было ли представление обновлено, опишите его снова:

DESCRIBE sales_employees;

Теперь вы должны увидеть столбец department в выводе:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | int           | NO   |     | NULL    |       |
| first_name | varchar(50)   | YES  |     | NULL    |       |
| last_name  | varchar(50)   | YES  |     | NULL    |       |
| salary     | decimal(10,2) | YES  |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Запросите обновленное представление, чтобы увидеть новый столбец:

SELECT * FROM sales_employees;

Вывод теперь будет включать столбец department:

+------+------------+-----------+----------+------------+
| id   | first_name | last_name | salary   | department |
+------+------------+-----------+----------+------------+
|    1 | John       | Doe       | 60000.00 | Sales      |
|    3 | Robert     | Jones     | 50000.00 | Sales      |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)

Вы успешно обновили определение представления sales_employees.

Удаление представления и очистка

На этом заключительном шаге вы научитесь удалять (дропать) представление и очищать базу данных и таблицу, созданные в ходе этой лабораторной работы.

Чтобы удалить представление sales_employees, используйте оператор DROP VIEW:

DROP VIEW sales_employees;

Эта команда безвозвратно удаляет представление sales_employees из базы данных company.

Чтобы подтвердить, что представление было удалено, вы можете попытаться описать его:

DESCRIBE sales_employees;

Это должно привести к сообщению об ошибке, указывающему, что представление не существует:

ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist

Теперь давайте очистим таблицу employees и базу данных company.

Удалите таблицу employees:

DROP TABLE employees;

Удалите базу данных company:

DROP DATABASE company;

Вы можете выйти из оболочки MySQL, набрав:

exit

Вы успешно удалили представление, таблицу и базу данных, очистив ресурсы, использованные в этой лабораторной работе.

Резюме

В этой лабораторной работе вы научились работать с представлениями MySQL. Вы начали с подключения к серверу MySQL и настройки базы данных и таблицы. Затем вы создали представление с именем sales_employees для предоставления отфильтрованного представления таблицы employees.

Вы практиковались в запросах к представлению с помощью операторов SELECT, демонстрируя, как представления упрощают доступ к данным. Вы также узнали, как изменять определение существующего представления с помощью оператора ALTER VIEW для включения дополнительных столбцов. Наконец, вы узнали, как удалять представление с помощью оператора DROP VIEW и очистили базу данных и таблицу.

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