Використання представлень MySQL на Debian 7

Вступ

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

З'явиться запит на введення пароля root. Це пароль, який ви встановили на першому кроці.

Оскільки база даних досить велика, для повного імпорту даних, ймовірно, знадобиться від 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)

Крок третій – створення, використання та видалення представлень

In this step, you will learn to create and use views. I have broken up this step into smaller sections for matching data, and combining data for organization. It's time to start interacting with our test data.

Merging/matching data

Below, I have a query which displays all employees who have a yearly salary equal to, or greater than $50,000.

select * from salaries where salary >= 50000;

Output (truncated):

+--------+--------+------------+------------+
| 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 |
(...)

As you can see, this only displays employee numbers. It could be a nuisance when trying to identify an employee quickly. Luckily, we can create a view that will store a fairly lengthy query that can match employee numbers to employee names by pulling and matching data from multiple tables. The query is shown below.

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;

Notice how I have omitted >= 50000 from the query. We will be using this value after our view has been created.

Щоб створити представлення, ми просто додаємо 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;.