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

Въведение

MySQL има страхотна функция, известна като "views". Прегледите са съхранени заявки. Мислете за тях като псевдоним за иначе дълга заявка. В това ръководство ще ви покажа как да използвате изгледи, за да организирате данните по-ефективно.

Предпоставки

• 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;.