Uporaba pogledov MySQL v Debianu 7

Uvod

MySQL ima odlično funkcijo, znano kot "pogledi". Pogledi so shranjene poizvedbe. Pomislite nanje kot vzdevek za sicer dolgo poizvedbo. V tem priročniku vam bom pokazal, kako uporabljati poglede za učinkovitejšo organizacijo podatkov.

Predpogoji

• Oblačni strežnik Vultr SSD, naložen z Debianom 7.
• Uporabnik s skrbniškimi (root) pravicami.
• Osnovno poznavanje Linuxa, ukazne vrstice in SSH.

Prvi korak - Namestite strežnik MySQL

Namestitev MySQL v Debian 7.x je zelo enostavna. Najprej moramo zagotoviti, da so naši viri posodobljeni z izvajanjem:

sudo apt-get update

Nato lahko namestimo strežnik MySQL:

sudo apt-get install -y mysql-server

Prikazalo se bo pogovorno okno, ki vas bo pozvalo, da ustvarite geslo za "root" uporabnika. Prepričajte se, da si zapomnite to geslo.

Okrepimo varnost naše namestitve z izvedbo:

sudo mysql_secure_installation

Po izvedbi se vam bo predstavila vrsta pozivov. Spodaj je prikazan vsak od odgovorov, ki jih morate izbrati.

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

Drugi korak - Namestite vzorčno bazo podatkov

Trenutno na strežniku nimamo podatkov, s katerimi bi lahko eksperimentirali. Za to vadnico bomo uporabljali bazo podatkov zaposlenih, saj je z njo enostavno delati in je prosto dostopna na spletnem mestu MySQL.

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

Namestiti bzip2moramo, da lahko izvlečemo datoteko.

sudo apt-get install bzip2

Ekstrahirajte bazo podatkov. Datoteka je precej velika, zato lahko traja nekaj trenutkov.

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

Ko je datoteka ekstrahirana, boste imeli mapo z naslovom employees_db. Za namestitev baze podatkov se moramo pomakniti v ta imenik.

cd employees_db 
ls -l

Izhod bo videti takole:

-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

Izvedite naslednji ukaz, da se povežete s strežnikom MySQL, ustvarite bazo podatkov in uvozite podatke:

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

Prikaže se poziv, ki bo zahteval vaše root geslo. To je geslo, ki ste ga nastavili v prvem koraku.

Ker je baza podatkov precej velika, bo za popoln uvoz podatkov verjetno trajalo od 1 do 3 minute. Če je bilo vse opravljeno pravilno, boste videli naslednji rezultat.

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

Zdaj se lahko prijavimo v MySQL in si ogledamo uvožene podatke.

sudo mysql -h localhost -u root -p

Vnesite korensko geslo, ki ste ga nastavili v prejšnjem razdelku.

Preverite seznam baz podatkov za našo novo ustvarjeno bazo podatkov zaposlenih .

show databases;

Izhod bo videti takole:

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

Uporabimo bazo zaposlenih .

use employees;

Preverite tabele v njej.

show tables;

To bo izpisalo:

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

Tretji korak - Ustvarjanje, uporaba in odstranjevanje pogledov

V tem koraku se boste naučili ustvarjati in uporabljati poglede. Ta korak sem razdelil na manjše dele za ujemanje podatkov in združevanje podatkov za organizacijo. Čas je, da začnemo komunicirati z našimi testnimi podatki.

Spajanje/ujemanje podatkov

Spodaj imam poizvedbo, ki prikazuje vse zaposlene, ki imajo letno plačo enako ali večjo od 50.000 $.

select * from salaries where salary >= 50000;

Izhod (okrnjeno):

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

Kot lahko vidite, to prikazuje samo številke zaposlenih. To bi lahko bila nadloga, ko poskušate hitro identificirati zaposlenega. Na srečo lahko ustvarimo pogled, ki bo shranil precej dolgo poizvedbo, ki lahko poveže številke zaposlenih z imeni zaposlenih, tako da povlečemo in primerjamo podatke iz več tabel. Poizvedba je prikazana spodaj.

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;

Upoštevajte, kako sem izpustil >= 50000poizvedbo. To vrednost bomo uporabili, ko bo naš pogled ustvarjen.

Za ustvarjanje pogleda preprosto dodamo create view view_name aspoizvedbi. V tem primeru bom ustvaril pogled z imenom 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;

Podatke iz pogleda prikažemo na enak način kot podatke iz tabele.

select * from named_salaries

Če je bil pogled pravilno ustvarjen, boste videli naslednji izhod (podatki so bili skrajšani):

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

Ker lahko s pogledi komuniciramo na enak način kot s tabelo, je mogoče vzeti >= 50000izvirno poizvedbo in jo uporabiti za pogled.

select * from named_salaries where salary >= 50000;

Izhod (okrnjeno):

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

Kot lahko vidite, je poizvedba obravnavala pogled tako kot tradicionalno tabelo.

Uporabimo pogled v drugem primeru. Spodaj imam precej dolgo poizvedbo, ki navaja vodje oddelkov, njihova imena/priimke, številke zaposlenih, njihova imena oddelkov in številke oddelkov. Poizvedba zbere podatke iz več različnih tabel.

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;

Izhod (okrnjeno):

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

Kot lahko vidite, bi bilo nekoliko neprijetno vnašati to poizvedbo vsakič, ko bi morali pridobiti seznam vodij oddelkov. Ustvarimo pogled, da bo lažje. Pogled bom poimenoval "upravljanje".

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;

Zdaj lahko preprosto vnesemo, select * from management;da pridobimo iste podatke. Seveda lahko za to uporabimo tudi dodatne parametre - tako kot tradicionalno mizo. Recimo, da želimo prikazati samo vodje oddelkov za »Storitev za stranke«.

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

Izhod:

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

Ali pa morda želimo "Storitev za stranke" in "Človeški viri":

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

Izhod:

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

Odstranjevanje pogleda

Brisanje pogleda je zelo preprosto. Podobno kot pri odstranjevanju tabele bi vnesli drop view view_name;. Na primer, če želimo izbrisati pogled named_salaries , bi bil ukaz: drop view named_salaries;.