Brug af MySQL Views på Debian 7

Introduktion

MySQL har en fantastisk funktion kendt som "visninger". Visninger er gemte forespørgsler. Tænk på dem som et alias for en ellers lang forespørgsel. I denne guide vil jeg vise dig, hvordan du bruger visninger til at organisere data mere effektivt.

Forudsætninger

• En Vultr SSD-skyserver indlæst med Debian 7.
• Bruger med administratorrettigheder (root).
• Grundlæggende viden om Linux, kommandolinjen og SSH.

Trin et - Installer MySQL-server

Installation af MySQL på Debian 7.x er meget ligetil. Først skal vi sikre, at vores kilder er opdateret ved at udføre:

sudo apt-get update

Dernæst kan vi installere MySQL-serveren:

sudo apt-get install -y mysql-server

En dialogboks vil blive vist, der beder dig om at oprette en adgangskode til "root"-brugeren. Sørg for at huske denne adgangskode.

Lad os hærde sikkerheden ved vores installation ved at udføre:

sudo mysql_secure_installation

Efter udførelse vil du blive præsenteret for en række prompter. Hvert af de svar, du skal vælge, vises nedenfor.

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

Trin to - Installer eksempeldatabase

På dette tidspunkt har vi ingen data på serveren at eksperimentere med. Til denne vejledning vil vi bruge medarbejderdatabasen, da den er nem at arbejde med og frit tilgængelig fra MySQL's hjemmeside.

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

Vi skal installere, bzip2så vi kan udpakke filen.

sudo apt-get install bzip2

Udpak databasen. Filen er ret stor, så det kan tage et øjeblik.

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

Når filen er blevet pakket ud, vil du have en mappe med titlen employees_db. Vi skal navigere ind i denne mappe for at installere databasen.

cd employees_db 
ls -l

Outputtet vil se sådan ud:

-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

Udfør følgende kommando for at oprette forbindelse til MySQL-serveren, oprette databasen og importere dataene:

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

Der vises en prompt, der beder om din root-adgangskode. Dette er den adgangskode, du indstillede i trin et.

Da databasen er ret stor, vil det sandsynligvis tage alt fra 1-3 minutter at importere dataene fuldt ud. Hvis alt blev gjort korrekt, vil du se følgende output.

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

Nu kan vi logge på MySQL og se de importerede data.

sudo mysql -h localhost -u root -p

Indtast root- adgangskoden, som du har angivet i forrige afsnit.

Tjek listen over databaser til vores nyoprettede medarbejderdatabase .

show databases;

Outputtet vil se sådan ud:

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

Lad os bruge medarbejderdatabasen .

use employees;

Tjek tabellerne i den.

show tables;

Dette vil udsende:

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

Trin tre - Oprettelse, brug og fjernelse af visninger

I dette trin lærer du at oprette og bruge visninger. Jeg har delt dette trin op i mindre sektioner for at matche data og kombinere data til organisation. Det er tid til at begynde at interagere med vores testdata.

Sammenfletning/matchning af data

Nedenfor har jeg en forespørgsel, som viser alle medarbejdere, der har en årsløn, der er lig med eller større end $50.000.

select * from salaries where salary >= 50000;

Output (trunkeret):

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

Som du kan se, viser dette kun medarbejdernumre. Det kan være en gene, når man forsøger at identificere en medarbejder hurtigt. Heldigvis kan vi oprette en visning, der gemmer en temmelig lang forespørgsel, der kan matche medarbejdernumre med medarbejdernavne ved at trække og matche data fra flere tabeller. Forespørgslen er vist nedenfor.

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;

Bemærk, hvordan jeg har udeladt >= 50000fra forespørgslen. Vi vil bruge denne værdi, efter at vores visning er blevet oprettet.

For at oprette visningen tilføjer vi blot create view view_name astil forespørgslen. I dette tilfælde vil jeg oprette en visning kaldet named_salaris .

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;

Vi viser data fra en visning på samme måde, som vi viser data fra en tabel.

select * from named_salaries

Hvis visningen er blevet oprettet korrekt, vil du se følgende output (data er blevet afkortet):

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

Da vi kan interagere med visninger på samme måde, som vi kan interagere med en tabel, er det muligt at tage >= 50000fra den originale forespørgsel og anvende den på visningen.

select * from named_salaries where salary >= 50000;

Output (trunkeret):

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

Som du kan se, har forespørgslen behandlet visningen ligesom en traditionel tabel.

Lad os bruge en visning i et andet eksempel. Nedenfor har jeg en ret lang forespørgsel, der viser afdelingslederne, deres for-/efternavne, medarbejdernumre, deres afdelingsnavne og afdelingsnumrene. Forespørgslen samler data fra flere forskellige tabeller.

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;

Output (trunkeret):

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

Som du kan se, ville det være noget ubelejligt at indtaste den forespørgsel, hver gang du skal hente en liste over afdelingsledere. Lad os skabe en visning for at gøre det nemmere. Jeg vil kalde visningen "ledelse".

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;

Nu kan vi blot skrive for select * from management;at hente de samme data. Det kan vi selvfølgelig også anvende yderligere parametre på - ligesom et traditionelt bord. Sig for eksempel, at vi kun ønskede at vise afdelingslederne for "Kundeservice".

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

Produktion:

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

Eller måske vil vi have "Kundeservice" og "Human Resources":

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

Produktion:

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

Fjernelse af en visning

Det er meget ligetil at slette en visning. På samme måde som at fjerne en tabel, ville du skrive drop view view_name;. For eksempel, hvis vi ville slette visningen named_salaris , ville kommandoen være: drop view named_salaries;.