Использование MySQL Views в Debian 7

• Вступление
• Предпосылки
• Шаг первый - установить сервер MySQL
• Шаг второй - Установите образец базы данных
• Шаг третий - Создание, использование и удаление представлений

Вступление

MySQL имеет отличную функцию, известную как «представления». Представления хранятся запросы. Думайте о них как о псевдониме для длинного запроса. В этом руководстве я покажу вам, как использовать представления для более эффективной организации данных.

Предпосылки

• Облачный сервер Vultr SSD, загруженный с Debian 7.
• Пользователь с правами администратора (root).
• Базовые знания Linux, командной строки и SSH.

Шаг первый - установить сервер MySQL

Установить MySQL на Debian 7.x очень просто. Во-первых, мы должны убедиться, что наши источники обновлены, выполнив:

sudo apt-get update

Далее мы можем установить сервер MySQL:

sudo apt-get install -y mysql-server

Появится диалоговое окно с предложением создать пароль для пользователя «root». Убедитесь, что вы помните этот пароль.

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

sudo mysql_secure_installation

После выполнения вам будет представлен ряд подсказок. Каждый из ответов, которые вы должны выбрать, показан ниже.

...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

Шаг второй - Установите образец базы данных

На данный момент у нас нет данных на сервере для экспериментов. В этом руководстве мы будем использовать базу данных сотрудников, так как с ней легко работать и которую можно бесплатно найти на веб-сайте MySQL.

sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

Нам нужно установить, bzip2чтобы мы могли извлечь файл.

sudo apt-get install bzip2

Извлеките базу данных. Файл довольно большой, поэтому это может занять несколько минут.

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

Как только файл будет извлечен, у вас будет папка с названием employees_db. Нам нужно перейти в этот каталог, чтобы установить базу данных.

cd employees_db 
ls -l

Вывод будет выглядеть так:

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

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

sudo mysql -h localhost -u root -p -t < employees.sql

Появится приглашение с запросом вашего корневого пароля. Это пароль, который вы установили на первом этапе.

Поскольку база данных достаточно велика, для полного импорта данных, вероятно, потребуется от 1 до 3 минут. Если все было сделано правильно, вы увидите следующий вывод.

+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

Теперь мы можем войти в MySQL и просмотреть импортированные данные.

sudo mysql -h localhost -u root -p

Введите пароль root, который вы установили в предыдущем разделе.

Проверьте список баз данных для нашей вновь созданной базы данных сотрудников .

show databases;

Вывод будет выглядеть так:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

Давайте использовать базу данных сотрудников .

use employees;

Проверьте таблицы в нем.

show tables;

Это выведет:

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

Шаг третий - Создание, использование и удаление представлений

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

Слияние / сопоставление данных

Ниже у меня есть запрос, который отображает всех сотрудников, чья годовая зарплата равна или превышает 50 000 долларов США.

select * from salaries where salary >= 50000;

Вывод (усеченный):

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
(...)

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

select employees.first_name,employees.last_name,employees.emp_no,salaries.salary,salaries.to_date,salaries.from_date from employees, salaries where employees.emp_no = salaries.emp_no;

Обратите внимание, как я пропустил >= 50000в запросе. Мы будем использовать это значение после того, как наше представление будет создано.

Чтобы создать представление, мы просто добавляем create view view_name asзапрос. В этом случае я буду создавать представление с именем named_salaries .

create view named_salaries as select employees.first_name,employees.last_name,employees.emp_no,salaries.salary,salaries.to_date,salaries.from_date from employees, salaries where employees.emp_no = salaries.emp_no;

Мы отображаем данные из представления так же, как мы отображаем данные из таблицы.

select * from named_salaries

Если представление было создано правильно, то вы увидите следующий вывод (данные были усечены):

+------------+-----------+--------+--------+------------+------------+
| first_name | last_name | emp_no | salary | to_date    | from_date  |
+------------+-----------+--------+--------+------------+------------+
| Georgi     | Facello   |  10001 |  60117 | 1987-06-26 | 1986-06-26 |
| Georgi     | Facello   |  10001 |  62102 | 1988-06-25 | 1987-06-26 |
| Georgi     | Facello   |  10001 |  66074 | 1989-06-25 | 1988-06-25 |
| Georgi     | Facello   |  10001 |  66596 | 1990-06-25 | 1989-06-25 |
| Georgi     | Facello   |  10001 |  66961 | 1991-06-25 | 1990-06-25 |
| Georgi     | Facello   |  10001 |  71046 | 8  1992-06-24 | 1991-06-25 |
(...)

Поскольку мы можем взаимодействовать с представлениями так же, как мы можем взаимодействовать с таблицей, можно взять >= 50000исходный запрос и применить его к представлению.

select * from named_salaries where salary >= 50000;

Вывод (усеченный):

+------------+-----------+--------+--------+------------+------------+
| first_name | last_name | emp_no | salary | to_date    | from_date  |
+------------+-----------+--------+--------+------------+------------+
| Georgi     | Facello   |  10001 |  60117 | 1987-06-26 | 1986-06-26 |
(...)
| Bezalel    | Simmel    |  10002 |  65828 | 1997-08-03 | 1996-08-03 |
(...)
| Chirstian  | Koblick   |  10004 |  50594 | 1992-11-29 | 1991-11-30 |
(...)
| Kyoichi    | Maliniak  |  10005 |  78228 | 1990-09-12 | 1989-09-12 |
(...)
| Anneke     | Preusig   |  10006 |  53747 | 1998-08-03 | 1997-08-03 |
(...)
+------------+-----------+--------+--------+------------+------------+

Как вы можете видеть, запрос обрабатывал представление как традиционную таблицу.

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

select employees.first_name,employees.last_name,employees.emp_no,dept_manager.to_date,dept_manager.from_date,departments.dept_name,departments.dept_no from employees, dept_manager, departments where employees.emp_no = dept_manager.emp_no AND departments.dept_no = dept_manager.dept_no;

Вывод (усеченный):

+-------------+--------------+--------+------------+------------+--------------------+---------+
| first_name  | last_name    | emp_no | to_date    | from_date  | dept_name          | dept_no |
+-------------+--------------+--------+------------+------------+--------------------+---------+
| Tonny       | Butterworth  | 111692 | 1988-10-17 | 1985-01-01 | Customer Service   | d009    |
| Marjo       | Giarratana   | 111784 | 1992-09-08 | 1988-10-17 | Customer Service   | d009    |
| Xiaobin     | Spinelli     | 111877 | 1996-01-03 | 1992-09-08 | Customer Service   | d009    |
| Yuchang     | Weedman      | 111939 | 9999-01-01 | 1996-01-03 | Customer Service   | d009    |
| DeForest    | Hagimont     | 110511 | 1992-04-25 | 1985-01-01 | Development        | d005    |
| Leon        | DasSarma     | 110567 | 9999-01-01 | 1992-04-25 | Development        | d005    |
(...)

Как видите, вводить этот запрос было бы несколько неудобно каждый раз, когда вам нужно получить список руководителей отделов. Давайте создадим представление, чтобы сделать это проще. Я собираюсь назвать вид "менеджмент".

create view management as select employees.first_name,employees.last_name,employees.emp_no,dept_manager.to_date,dept_manager.from_date,departments.dept_name,departments.dept_no from employees, dept_manager, departments where employees.emp_no = dept_manager.emp_no AND departments.dept_no = dept_manager.dept_no;

Теперь мы можем просто напечатать, select * from management;чтобы получить те же данные. Конечно, мы также можем применить к этому дополнительные параметры - как в традиционной таблице. Например, скажем, мы хотели показать только руководителей отделов для «Служба поддержки клиентов».

select * from management where dept_name = 'Customer Service';

Вывод:

+------------+-------------+--------+------------+------------+------------------+---------+
| first_name | last_name   | emp_no | to_date    | from_date  | dept_name        | dept_no |
+------------+-------------+--------+------------+------------+------------------+---------+
| Tonny      | Butterworth | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009    |
| Marjo      | Giarratana  | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009    |
| Xiaobin    | Spinelli    | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009    |
| Yuchang    | Weedman     | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009    |
+------------+-------------+--------+------------+------------+------------------+---------+

Или, может быть, мы хотим «Обслуживание клиентов» и «Кадры»:

select * from management where dept_name = 'Customer Service' OR dept_name = 'Human Resources';

Вывод:

+------------+--------------+--------+------------+------------+------------------+---------+
| first_name | last_name    | emp_no | to_date    | from_date  | dept_name        | dept_no |
+------------+--------------+--------+------------+------------+------------------+---------+
| Tonny      | Butterworth  | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009    |
| Marjo      | Giarratana   | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009    |
| Xiaobin    | Spinelli     | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009    |
| Yuchang    | Weedman      | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009    |
| Shirish    | Ossenbruggen | 110183 | 1992-03-21 | 1985-01-01 | Human Resources  | d003    |
| Karsten    | Sigstam      | 110228 | 9999-01-01 | 1992-03-21 | Human Resources  | d003    |
+------------+--------------+--------+------------+------------+------------------+---------+

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

Удалить вид очень просто. Подобно удалению таблицы, вы наберете drop view view_name;. Например, если мы хотим , чтобы удалить named_salaries представление, то команда будет выглядеть так : drop view named_salaries;.