Bruke MySQL Views på Debian 7

Introduksjon

MySQL har en flott funksjon kjent som "visninger". Visninger er lagrede spørringer. Tenk på dem som et alias for en ellers lang spørring. I denne veiledningen vil jeg vise deg hvordan du bruker visninger for å organisere data mer effektivt.

Forutsetninger

• En Vultr SSD-skyserver lastet med Debian 7.
• Bruker med administratorrettigheter (root).
• Grunnleggende kunnskap om Linux, kommandolinjen og SSH.

Trinn én - Installer MySQL-server

Det er veldig enkelt å installere MySQL på Debian 7.x. Først må vi sørge for at kildene våre er oppdatert ved å utføre:

sudo apt-get update

Deretter kan vi installere MySQL-serveren:

sudo apt-get install -y mysql-server

En dialogboks vil bli vist som ber deg opprette et passord for "root"-brukeren. Pass på at du husker dette passordet.

La oss styrke sikkerheten til installasjonen vår ved å utføre:

sudo mysql_secure_installation

Etter utførelse vil du bli presentert med en rekke ledetekster. Hvert av svarene du bør velge 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...

Trinn to - Installer eksempeldatabase

På dette tidspunktet har vi ingen data på serveren å eksperimentere med. For denne opplæringen, vil vi bruke den ansatte database som det er lett å arbeide med og fritt tilgjengelig fra MySQL hjemmeside.

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

Vi må installere bzip2slik at vi kan pakke ut filen.

sudo apt-get install bzip2

Pakk ut databasen. Filen er ganske stor, så det kan ta litt tid.

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 pakket ut, vil du ha en mappe med tittelen employees_db. Vi må navigere inn i denne katalogen for å installere databasen.

cd employees_db 
ls -l

Utgangen vil se slik ut:

-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

Utfør følgende kommando for å koble til MySQL-serveren, opprett databasen og importer dataene:

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

Det vises en melding som ber om root-passordet ditt. Dette er passordet du satte i trinn én.

Siden databasen er ganske stor, vil det sannsynligvis ta alt fra 1-3 minutter å importere dataene fullstendig. Hvis alt ble gjort riktig, vil du se følgende utgang.

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

Nå kan vi logge på MySQL og se de importerte dataene.

sudo mysql -h localhost -u root -p

Skriv inn root- passordet du anga i forrige del.

Sjekk listen over databaser for vår nyopprettede medarbeiderdatabase .

show databases;

Utgangen vil se slik ut:

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

La oss bruke den ansatte databasen.

use employees;

Sjekk tabellene i den.

show tables;

Dette vil gi ut:

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

Trinn tre – Opprette, bruke og fjerne visninger

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.

For å lage visningen legger vi ganske enkelt create view view_name astil spørringen. I dette tilfellet vil jeg lage en visning kalt 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åte som vi viser data fra en tabell.

select * from named_salaries

Hvis visningen er opprettet på riktig måte, vil du se følgende utdata (data er avkortet):

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

Siden vi kan samhandle med visninger på samme måte som vi kan samhandle med en tabell, er det mulig å ta >= 50000fra den opprinnelige spørringen og bruke den på visningen.

select * from named_salaries where salary >= 50000;

Utgang (avkortet):

+------------+-----------+--------+--------+------------+------------+
| 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 spørringen behandlet visningen akkurat som en tradisjonell tabell.

La oss bruke en visning i et annet eksempel. Nedenfor har jeg en ganske lang spørring som viser avdelingslederne, deres for-/etternavn, ansattnummer, avdelingsnavn og avdelingsnummer. Spørringen trekker sammen data fra flere forskjellige 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;

Utgang (avkortet):

+-------------+--------------+--------+------------+------------+--------------------+---------+
| 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 litt upraktisk å skrive inn den spørringen hver gang du trenger å hente en liste over avdelingsledere. La oss lage en visning for å gjøre det enklere. Jeg kommer til å kalle 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;

Nå kan vi ganske enkelt skrive for select * from management;å hente de samme dataene. Selvfølgelig kan vi også bruke tilleggsparametre til det - akkurat som et tradisjonelt bord. Si for eksempel at vi bare ønsket å vise avdelingslederne for "Kundeservice".

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

Produksjon:

+------------+-------------+--------+------------+------------+------------------+---------+
| 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 kanskje vi vil ha "Kundeservice" og "Human Resources":

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

Produksjon:

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

Fjerner en visning

Det er veldig enkelt å slette en visning. På samme måte som å fjerne en tabell, skriver du drop view view_name;. Hvis vi for eksempel ønsker å slette named_salaris- visningen, vil kommandoen være: drop view named_salaries;.