Använda MySQL Views på Debian 7

Introduktion

MySQL har en fantastisk funktion som kallas "vyer". Visningar är lagrade frågor. Se dem som ett alias för en annars lång fråga. I den här guiden kommer jag att visa dig hur du använder vyer för att organisera data mer effektivt.

Förutsättningar

• En Vultr SSD-molnserver laddad med Debian 7.
• Användare med administratörsbehörighet (root).
• Grundläggande kunskaper om Linux, kommandoraden och SSH.

Steg ett - Installera MySQL-servern

Att installera MySQL på Debian 7.x är mycket enkelt. Först måste vi se till att våra källor uppdateras genom att köra:

sudo apt-get update

Därefter kan vi installera MySQL-servern:

sudo apt-get install -y mysql-server

En dialogruta kommer att visas som uppmanar dig att skapa ett lösenord för "root"-användaren. Se till att du kommer ihåg detta lösenord.

Låt oss stärka säkerheten för vår installation genom att köra:

sudo mysql_secure_installation

Efter utförandet kommer du att presenteras med en rad uppmaningar. Vart och ett av svaren som du bör välja visas nedan.

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

Steg två - Installera exempeldatabas

För närvarande har vi inga data på servern att experimentera med. För denna handledning kommer vi att använda personaldatabasen eftersom den är lätt att arbeta med och fritt tillgänglig från MySQL:s webbplats.

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

Vi måste installera bzip2så att vi kan extrahera filen.

sudo apt-get install bzip2

Extrahera databasen. Filen är ganska stor, så det kan ta en stund.

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

När filen har extraherats kommer du att ha en mapp med titeln employees_db. Vi måste navigera in i den här katalogen för att installera databasen.

cd employees_db 
ls -l

Utgången kommer att se ut så här:

-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

Kör följande kommando för att ansluta till MySQL-servern, skapa databasen och importera data:

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

En prompt kommer att visas som ber om ditt root-lösenord. Detta är lösenordet som du angav i steg ett.

Eftersom databasen är ganska stor, kommer det förmodligen att ta allt från 1-3 minuter att importera data helt. Om allt gjordes korrekt kommer du att se följande utdata.

+-----------------------------+
| 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 logga in på MySQL och se den importerade informationen.

sudo mysql -h localhost -u root -p

Ange root- lösenordet som du angav i föregående avsnitt.

Kontrollera listan med databaser för vår nyskapade personaldatabas .

show databases;

Utgången kommer att se ut så här:

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

Låt oss använda personaldatabasen .

use employees;

Kontrollera tabellerna i den.

show tables;

Detta kommer att mata ut:

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

Steg tre - Skapa, använda och ta bort vyer

I det här steget lär du dig att skapa och använda vyer. Jag har delat upp detta steg i mindre sektioner för att matcha data och kombinera data för organisation. Det är dags att börja interagera med våra testdata.

Sammanfoga/matcha data

Nedan har jag en fråga som visar alla anställda som har en årslön som är lika med eller större än $50 000.

select * from salaries where salary >= 50000;

Utdata (trunkerat):

+--------+--------+------------+------------+
| 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 visar detta bara antal anställda. Det kan vara en olägenhet när man försöker identifiera en anställd snabbt. Lyckligtvis kan vi skapa en vy som lagrar en ganska lång fråga som kan matcha anställdas nummer med anställdas namn genom att dra och matcha data från flera tabeller. Frågan visas nedan.

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;

Lägg märke till hur jag har utelämnat >= 50000frågan. Vi kommer att använda detta värde efter att vår vy har skapats.

För att skapa vyn lägger vi helt enkelt create view view_name astill frågan. I det här fallet kommer jag att skapa en vy som heter 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 visar data från en vy på samma sätt som vi visar data från en tabell.

select * from named_salaries

Om vyn har skapats korrekt, kommer du att se följande utdata (data har trunkerats):

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

Eftersom vi kan interagera med vyer på samma sätt som vi kan interagera med en tabell, är det möjligt att ta >= 50000från den ursprungliga frågan och tillämpa den på vyn.

select * from named_salaries where salary >= 50000;

Utdata (trunkerat):

+------------+-----------+--------+--------+------------+------------+
| 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 frågan behandlat vyn precis som ett traditionellt bord.

Låt oss använda en vy i ett annat exempel. Nedan har jag en ganska lång fråga som listar avdelningschefer, deras för-/efternamn, personalnummer, deras avdelningsnamn och avdelningsnummer. Frågan drar ihop data från flera olika 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;

Utdata (trunkerat):

+-------------+--------------+--------+------------+------------+--------------------+---------+
| 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 skulle det vara något obekvämt att skriva in den frågan varje gång du behöver hämta en lista över avdelningschefer. Låt oss skapa en vy för att göra det enklare. Jag kommer att kalla synen för "förvaltning".

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 helt enkelt skriva för select * from management;att hämta samma data. Naturligtvis kan vi också tillämpa ytterligare parametrar på det - precis som ett traditionellt bord. Säg till exempel att vi bara ville visa avdelningschefer för "Kundservice".

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 så kanske vi vill ha "Kundservice" och "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    |
+------------+--------------+--------+------------+------------+------------------+---------+

Ta bort en vy

Att ta bort en vy är väldigt enkelt. På samma sätt som att ta bort en tabell skulle du skriva drop view view_name;. Om vi ​​till exempel vill ta bort vyn namngivna_löner , skulle kommandot vara: drop view named_salaries;.