Použití zobrazení MySQL na Debianu 7

Úvod

MySQL má skvělou funkci známou jako „views“. Pohledy jsou uložené dotazy. Představte si je jako alias pro jinak dlouhý dotaz. V této příručce vám ukážu, jak používat pohledy k efektivnější organizaci dat.

Předpoklady

• Cloudový server Vultr SSD nabitý Debianem 7.
• Uživatel s právy správce (root).
• Základní znalost Linuxu, příkazového řádku a SSH.

Krok jedna – Instalace serveru MySQL

Instalace MySQL na Debian 7.x je velmi přímočará. Nejprve musíme zajistit, aby byly naše zdroje aktualizovány provedením:

sudo apt-get update

Dále můžeme nainstalovat server MySQL:

sudo apt-get install -y mysql-server

Zobrazí se dialogové okno s výzvou k vytvoření hesla pro uživatele „root“. Ujistěte se, že si toto heslo pamatujete.

Posílíme zabezpečení naší instalace provedením:

sudo mysql_secure_installation

Po provedení se vám zobrazí řada výzev. Níže jsou zobrazeny všechny odpovědi, které byste měli vybrat.

...
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 – Instalace ukázkové databáze

V tuto chvíli nemáme na serveru žádná data, se kterými bychom mohli experimentovat. V tomto tutoriálu budeme používat databázi zaměstnanců, protože se s ní snadno pracuje a je volně dostupná na webu 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 nainstalovat bzip2, abychom mohli extrahovat soubor.

sudo apt-get install bzip2

Extrahujte databázi. Soubor je poměrně velký, takže to může chvíli trvat.

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

Po rozbalení souboru budete mít složku s názvem employees_db. K instalaci databáze musíme přejít do tohoto adresáře.

cd employees_db 
ls -l

Výstup bude vypadat 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

Provedením následujícího příkazu se připojte k serveru MySQL, vytvořte databázi a importujte data:

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

Zobrazí se výzva k zadání hesla uživatele root. Toto je heslo, které jste nastavili v prvním kroku.

Vzhledem k tomu, že databáze je poměrně velká, úplný import dat bude pravděpodobně trvat 1-3 minuty. Pokud bylo vše provedeno správně, uvidíte následující 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 |
+------------------+

Nyní se můžeme přihlásit do MySQL a zobrazit importovaná data.

sudo mysql -h localhost -u root -p

Zadejte heslo uživatele root, které jste nastavili v předchozí části.

Podívejte se na seznam databází pro naši nově vytvořenou databázi zaměstnanců .

show databases;

Výstup bude vypadat takto:

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

Využijme databázi zaměstnanců .

use employees;

Zkontrolujte tabulky v něm.

show tables;

Toto vypíše:

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

Krok tři – Vytváření, používání a odstraňování pohledů

V tomto kroku se naučíte vytvářet a používat pohledy. Tento krok jsem rozdělil na menší části pro párování dat a kombinování dat pro organizaci. Je čas začít pracovat s našimi testovacími daty.

Slučování/párování dat

Níže mám dotaz, který zobrazuje všechny zaměstnance, kteří mají roční plat rovný nebo vyšší než 50 000 $.

select * from salaries where salary >= 50000;

Výstup (zkrácený):

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

Jak vidíte, zobrazuje se pouze čísla zaměstnanců. Při snaze rychle identifikovat zaměstnance to může být nepříjemné. Naštěstí můžeme vytvořit pohled, který bude ukládat poměrně zdlouhavý dotaz, který dokáže přiřadit čísla zaměstnanců ke jménům zaměstnanců tím, že získá a porovná data z více tabulek. Dotaz je uveden níže.

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;

Všimněte si, jak jsem >= 50000z dotazu vynechal . Tuto hodnotu použijeme po vytvoření našeho pohledu.

Pro vytvoření pohledu jednoduše připojíme create view view_name ask dotazu. V tomto případě vytvořím pohled s názvem 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;

Data z pohledu zobrazujeme stejným způsobem, jakým zobrazujeme data z tabulky.

select * from named_salaries

Pokud byl pohled vytvořen správně, uvidíte následující výstup (data byla zkrácena):

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

Vzhledem k tomu, že můžeme interagovat s pohledy stejným způsobem, jako můžeme interagovat s tabulkou, je možné převzít >= 50000z původního dotazu a aplikovat jej na pohled.

select * from named_salaries where salary >= 50000;

Výstup (zkrácený):

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

Jak vidíte, dotaz zacházel s pohledem stejně jako s tradiční tabulkou.

Použijme pohled v jiném příkladu. Níže mám poměrně dlouhý dotaz, který uvádí vedoucí oddělení, jejich jména/příjmení, čísla zaměstnanců, jména jejich oddělení a čísla oddělení. Dotaz stahuje dohromady data z několika různých tabulek.

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 (zkrácený):

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

Jak vidíte, bylo by poněkud nepohodlné zadávat tento dotaz pokaždé, když potřebujete získat seznam vedoucích oddělení. Pojďme si vytvořit pohled, aby to bylo jednodušší. Budu tento pohled nazývat „management“.

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;

Nyní můžeme jednoduše zadat select * from management;a získat stejná data. Samozřejmě na to můžeme aplikovat i další parametry – stejně jako tradiční stůl. Řekněme například, že jsme chtěli zobrazit pouze manažery oddělení pro „Službu zákazníkům“.

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

Výstup:

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

Nebo možná chceme „Služby zákazníkům“ a „Lidské zdroje“:

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

Výstup:

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

Odebrání pohledu

Odstranění pohledu je velmi jednoduché. Podobně jako při odstraňování tabulky byste zadali drop view view_name;. Pokud bychom například chtěli smazat pohled named_salaries , příkaz by byl: drop view named_salaries;.