Utilizarea vizualizărilor MySQL pe Debian 7

• Introducere
• Cerințe preliminare
• Pasul unu - Instalează serverul MySQL
• Pasul doi - Instalați baza de date de mostre
• Pasul trei - Crearea, utilizarea și eliminarea vizualizărilor

Introducere

MySQL are o caracteristică excelentă cunoscută sub numele de „vizualizări”. Vizualizările sunt interogări stocate. Gândiți-vă la ele ca la un alias pentru o întrebare altfel lungă. În acest ghid, vă voi arăta cum să utilizați vizualizările pentru a organiza datele mai eficient.

Cerințe preliminare

• Un server cloud SSD Vultr încărcat cu Debian 7.
• Utilizator cu privilegii de administrator (root).
• Cunoștințe de bază despre Linux, linia de comandă și SSH.

Pasul unu - Instalează serverul MySQL

Instalarea MySQL pe Debian 7.x este foarte simplă. În primul rând, trebuie să ne asigurăm că sursele noastre sunt actualizate executând:

sudo apt-get update

În continuare, putem instala serverul MySQL:

sudo apt-get install -y mysql-server

Va fi afișată o casetă de dialog care vă va solicita să creați o parolă pentru utilizatorul „root”. Asigurați-vă că vă amintiți această parolă.

Să întărim securitatea instalării noastre executând:

sudo mysql_secure_installation

După executare, vi se va prezenta o serie de prompturi. Fiecare dintre răspunsurile pe care ar trebui să le selectați sunt afișate mai jos.

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

Pasul doi - Instalați baza de date de mostre

În acest moment, nu avem date pe server cu care să experimentăm. Pentru acest tutorial, vom folosi baza de date a angajaților , deoarece este ușor de lucrat și disponibil gratuit de pe site-ul MySQL.

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

Trebuie să instalăm bzip2astfel încât să putem extrage fișierul.

sudo apt-get install bzip2

Extrageți baza de date. Fișierul este destul de mare, deci poate dura câteva momente.

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

Odată extras fișierul, veți avea un folder cu titlul employees_db. Pentru instalarea bazei de date trebuie să navigăm în acest director.

cd employees_db 
ls -l

Rezultatul va arăta astfel:

-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

Executați următoarea comandă pentru a vă conecta la serverul MySQL, a crea baza de date și a importa datele:

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

Va apărea un prompt care vă cere parola root. Aceasta este parola pe care ați setat-o ​​în primul pas.

Fiindcă baza de date este destul de mare, probabil că va dura oriunde între 1-3 minute pentru a importa complet datele. Dacă totul a fost făcut corect, veți vedea următoarea ieșire.

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

Acum, ne putem conecta la MySQL și vizualiza datele importate.

sudo mysql -h localhost -u root -p

Introduceți parola rădăcină setată în secțiunea anterioară.

Verificați lista bazelor de date pentru baza noastră de date de angajați nou creată .

show databases;

Rezultatul va arăta astfel:

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

Să folosim baza de date a angajaților .

use employees;

Verificați tabelele din ea.

show tables;

Aceasta va produce:

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

Pasul trei - Crearea, utilizarea și eliminarea vizualizărilor

În acest pas, veți învăța să creați și să folosiți vizualizări. Am împărțit acest pas în secțiuni mai mici pentru potrivirea datelor și combinarea datelor pentru organizare. Este timpul să începeți să interacționați cu datele noastre de testare.

Fuzionarea / potrivirea datelor

Mai jos, am o întrebare care afișează toți angajații care au un salariu anual egal sau mai mare de 50.000 USD.

select * from salaries where salary >= 50000;

Ieșire (trunchiată):

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

După cum puteți vedea, aceasta afișează doar numerele angajaților. Ar putea fi o problemă când încercați să identificați rapid un angajat. Din fericire, putem crea o vizualizare care va stoca o interogare destul de lungă, care poate potrivi numerele de angajați cu numele angajaților prin tragerea și potrivirea datelor din mai multe tabele. Interogarea este prezentată mai jos.

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;

Observați cum am omis >= 50000din interogare. Vom folosi această valoare după crearea opiniei noastre.

Pentru a crea vizualizarea, pur și simplu anexăm create view view_name asinterogarea. În acest caz, voi crea o vizualizare numită nume_salari .

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;

Afișăm date dintr-o vedere în același mod în care afișăm datele dintr-un tabel.

select * from named_salaries

Dacă vizualizarea a fost creată corect, atunci veți vedea următoarea ieșire (datele au fost trunchiate):

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

Întrucât putem interacționa cu vizualizările în același mod în care putem interacționa cu un tabel, este posibil să preluăm >= 50000interogarea inițială și să o aplicăm pe vizualizare.

select * from named_salaries where salary >= 50000;

Ieșire (trunchiată):

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

După cum vedeți, interogarea a tratat vizualizarea la fel ca un tabel tradițional.

Să folosim o vedere într-un alt exemplu. Mai jos, am o întrebare destul de lungă, care enumera managerii de departament, prenumele / prenumele, numărul angajaților, numele departamentelor și numerele departamentului. Interogarea reunește datele din mai multe tabele diferite.

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;

Ieșire (trunchiată):

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

După cum puteți vedea, ar fi oarecum incomod să introduceți această interogare de fiecare dată când aveți nevoie pentru a obține o listă de manageri de departament. Haideți să creăm o vedere pentru a fi mai ușor. Voi numi vizualizarea „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;

Acum, putem tasta pur și simplu select * from management;pentru a prelua aceleași date. Desigur, putem aplica și parametri suplimentari în acest sens - la fel ca un tabel tradițional. De exemplu, să spunem că am vrut să arătăm managerii departamentelor doar pentru „Serviciul Clienți”.

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

ieşire:

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

Sau poate dorim „Serviciu clienți” și „Resurse umane”:

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

ieşire:

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

Eliminarea unei vederi

Ștergerea unei vizualizări este foarte simplă. Similar cu eliminarea unui tabel, ați tasta drop view view_name;. De exemplu, dacă ne - am dorit să ștergeți named_salaries de vedere, comanda va fi: drop view named_salaries;.