Používanie pohľadov MySQL na Debian 7

Úvod

MySQL má skvelú funkciu známu ako „views“. Zobrazenia sú uložené dopyty. Predstavte si ich ako alias pre inak dlhý dopyt. V tejto príručke vám ukážem, ako používať zobrazenia na efektívnejšie usporiadanie údajov.

Predpoklady

• Cloudový server Vultr SSD načítaný s Debianom 7.
• Používateľ s oprávneniami správcu (root).
• Základná znalosť Linuxu, príkazového riadku a SSH.

Prvý krok - Nainštalujte server MySQL

Inštalácia MySQL na Debian 7.x je veľmi jednoduchá. Najprv musíme zabezpečiť, aby sa naše zdroje aktualizovali vykonaním:

sudo apt-get update

Ďalej môžeme nainštalovať server MySQL:

sudo apt-get install -y mysql-server

Zobrazí sa dialógové okno s výzvou na vytvorenie hesla pre používateľa „root“. Uistite sa, že si toto heslo zapamätáte.

Posilnime bezpečnosť našej inštalácie vykonaním:

sudo mysql_secure_installation

Po vykonaní sa vám zobrazí séria výziev. Nižšie sú zobrazené všetky odpovede, ktoré by ste mali vybrať.

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

Krok dva - Nainštalujte vzorovú databázu

V tejto chvíli nemáme na serveri žiadne údaje, s ktorými by sme mohli experimentovať. V tomto návode budeme používať databázu zamestnancov, pretože sa s ňou ľahko pracuje a je voľne dostupná z webovej stránky MySQL.

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

Musíme nainštalovať bzip2, aby sme mohli extrahovať súbor.

sudo apt-get install bzip2

Extrahujte databázu. Súbor je pomerne veľký, takže to môže chvíľu trvať.

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

Po rozbalení súboru budete mať priečinok s názvom employees_db. Na inštaláciu databázy musíme prejsť do tohto adresára.

cd employees_db 
ls -l

Výstup bude vyzerať takto:

-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

Vykonajte nasledujúci príkaz na pripojenie k serveru MySQL, vytvorenie databázy a import údajov:

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

Zobrazí sa výzva na zadanie hesla root. Toto je heslo, ktoré ste nastavili v prvom kroku.

Vzhľadom na to, že databáza je pomerne veľká, úplný import údajov bude pravdepodobne trvať 1 až 3 minúty. Ak bolo všetko vykonané správne, uvidíte nasledujúci výstup.

+-----------------------------+
| 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 |
+------------------+

Teraz sa môžeme prihlásiť do MySQL a zobraziť importované údaje.

sudo mysql -h localhost -u root -p

Zadajte heslo root, ktoré ste nastavili v predchádzajúcej časti.

Pozrite si zoznam databáz pre našu novovytvorenú databázu zamestnancov .

show databases;

Výstup bude vyzerať takto:

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

Využime databázu zamestnancov .

use employees;

Skontrolujte si tabuľky v ňom.

show tables;

Výsledkom bude:

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

Tretí krok – Vytváranie, používanie a odstraňovanie zobrazení

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.

Ak chcete vytvoriť zobrazenie, jednoducho ho pripojíme create view view_name ask dotazu. V tomto prípade vytvorím pohľad s názvom 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;

Údaje z pohľadu zobrazujeme rovnakým spôsobom, akým zobrazujeme údaje z tabuľky.

select * from named_salaries

Ak bolo zobrazenie vytvorené správne, uvidíte nasledujúci výstup (údaje boli skrátené):

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

Keďže môžeme interagovať so zobrazeniami rovnakým spôsobom, ako môžeme interagovať s tabuľkou, je možné prevziať >= 50000z pôvodného dotazu a použiť ho na zobrazenie.

select * from named_salaries where salary >= 50000;

Výstup (skrátený):

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

Ako vidíte, dotaz zaobchádzal so zobrazením rovnako ako s tradičnou tabuľkou.

Použime zobrazenie v inom príklade. Nižšie mám pomerne zdĺhavý dotaz, ktorý uvádza vedúcich oddelení, ich mená/priezvisko, čísla zamestnancov, názvy oddelení a čísla oddelení. Dotaz zhromažďuje údaje z niekoľkých rôznych tabuliek.

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;

Výstup (skrátený):

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

Ako vidíte, bolo by trochu nepohodlné zadávať tento dotaz zakaždým, keď potrebujete získať zoznam manažérov oddelení. Aby sme to uľahčili, vytvoríme si pohľad. Pohľad nazvem „manažment“.

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;

Teraz môžeme jednoducho zadať, select * from management;aby sme získali rovnaké údaje. Samozrejme, že na to môžeme aplikovať aj ďalšie parametre – rovnako ako tradičný stôl. Povedzme napríklad, že sme chceli zobraziť iba manažérov oddelení pre „Služby zákazníkom“.

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

Výkon:

+------------+-------------+--------+------------+------------+------------------+---------+
| 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    |
+------------+-------------+--------+------------+------------+------------------+---------+

Alebo možno chceme „Služby zákazníkom“ a „Ľudské zdroje“:

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

Výkon:

+------------+--------------+--------+------------+------------+------------------+---------+
| 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    |
+------------+--------------+--------+------------+------------+------------------+---------+

Odstránenie zobrazenia

Odstránenie zobrazenia je veľmi jednoduché. Podobne ako pri odstraňovaní tabuľky by ste napísali drop view view_name;. Napríklad, ak by sme chceli vymazať pohľad Named_salaries , príkaz by bol: drop view named_salaries;.