Korištenje MySQL pogleda na Debianu 7

Uvod

MySQL ima sjajnu značajku poznatu kao "pregledi". Pregledi su pohranjeni upiti. Zamislite ih kao pseudonim za inače dug upit. U ovom vodiču pokazat ću vam kako koristiti poglede za učinkovitiju organizaciju podataka.

Preduvjeti

• Vultr SSD poslužitelj u oblaku napunjen s Debianom 7.
• Korisnik s administratorskim (root) povlasticama.
• Osnovno poznavanje Linuxa, naredbenog retka i SSH-a.

Prvi korak - Instalirajte MySQL poslužitelj

Instalacija MySQL-a na Debian 7.x vrlo je jednostavna. Prvo, moramo osigurati da se naši izvori ažuriraju izvršavanjem:

sudo apt-get update

Zatim možemo instalirati MySQL poslužitelj:

sudo apt-get install -y mysql-server

Prikazat će se dijaloški okvir koji od vas traži da kreirate lozinku za "root" korisnika. Budite sigurni da zapamtite ovu lozinku.

Pojačajmo sigurnost naše instalacije izvođenjem:

sudo mysql_secure_installation

Nakon izvršenja, prikazat će vam se niz upita. Svaki od odgovora koje biste trebali odabrati prikazan je u nastavku.

...
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 - Instalirajte uzorak baze podataka

U ovom trenutku nemamo podataka na poslužitelju s kojima bismo mogli eksperimentirati. Za ovaj vodič koristit ćemo bazu podataka zaposlenika jer je s njom lako raditi i slobodno je dostupna na MySQL web stranici.

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

Moramo instalirati bzip2kako bismo mogli izdvojiti datoteku.

sudo apt-get install bzip2

Ekstrahirajte bazu podataka. Datoteka je prilično velika, pa može potrajati nekoliko trenutaka.

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

Nakon što je datoteka ekstrahirana, imat ćete mapu pod nazivom employees_db. Moramo se navigirati u ovaj direktorij da bismo instalirali bazu podataka.

cd employees_db 
ls -l

Izlaz će izgledati ovako:

-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

Izvršite sljedeću naredbu za povezivanje s MySQL poslužiteljem, kreiranje baze podataka i uvoz podataka:

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

Pojavit će se upit koji traži vašu root lozinku. Ovo je lozinka koju ste postavili u prvom koraku.

Budući da je baza podataka prilično velika, vjerojatno će trebati od 1-3 minute za potpuni uvoz podataka. Ako je sve učinjeno ispravno, vidjet ćete sljedeći izlaz.

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

Sada se možemo prijaviti u MySQL i vidjeti uvezene podatke.

sudo mysql -h localhost -u root -p

Unesite root lozinku koju ste postavili u prethodnom odjeljku.

Provjerite popis baza podataka za našu novostvorenu bazu podataka zaposlenika .

show databases;

Izlaz će izgledati ovako:

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

Koristimo bazu podataka o zaposlenicima .

use employees;

Provjerite tablice unutar njega.

show tables;

Ovo će dati:

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

Treći korak - Stvaranje, korištenje i uklanjanje pogleda

U ovom koraku naučit ćete stvarati i koristiti poglede. Razbio sam ovaj korak na manje odjeljke za uparivanje podataka i kombiniranje podataka za organizaciju. Vrijeme je za početak interakcije s našim testnim podacima.

Spajanje/podudaranje podataka

U nastavku imam upit koji prikazuje sve zaposlenike koji imaju godišnju plaću jednaku ili veću od 50.000 USD.

select * from salaries where salary >= 50000;

Izlaz (skraćeno):

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

Kao što vidite, ovo prikazuje samo brojeve zaposlenika. To bi moglo biti smetnja kada pokušavate brzo identificirati zaposlenika. Srećom, možemo stvoriti prikaz koji će pohraniti prilično dugačak upit koji može upariti brojeve zaposlenika s imenima zaposlenika povlačenjem i podudaranjem podataka iz više tablica. Upit je prikazan u nastavku.

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;

Primijetite kako sam izostavio >= 50000upit. Ovu vrijednost ćemo koristiti nakon što je naš pogled stvoren.

Da bismo stvorili pogled, jednostavno pridodajemo create view view_name asupitu. U ovom slučaju, kreirat ću pogled pod nazivom 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 prikazujemo na isti način na koji prikazujemo podatke iz tablice.

select * from named_salaries

Ako je prikaz ispravno kreiran, vidjet ćete sljedeći izlaz (podaci su skraćeni):

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

Budući da možemo komunicirati s pogledima na isti način na koji možemo komunicirati s tablicom, moguće je uzeti >= 50000iz izvornog upita i primijeniti ga na pogled.

select * from named_salaries where salary >= 50000;

Izlaz (skraćeno):

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

Kao što možete vidjeti, upit je tretirao pogled baš kao tradicionalnu tablicu.

Upotrijebimo pogled u drugom primjeru. U nastavku imam prilično dugačak upit koji navodi voditelje odjela, njihova imena/prezimena, brojeve zaposlenika, nazive njihovih odjela i brojeve odjela. Upit prikuplja podatke iz nekoliko različitih tablica.

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;

Izlaz (skraćeno):

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

Kao što vidite, bilo bi pomalo nezgodno upisivati ​​taj upit svaki put kada trebate dohvatiti popis voditelja odjela. Kreirajmo pogled kako bismo ga olakšali. Pogled ću nazvati "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;

Sada možemo jednostavno upisati select * from management;da bismo dohvatili iste podatke. Naravno, na to možemo primijeniti i dodatne parametre - baš kao na tradicionalni stol. Na primjer, recimo da želimo prikazati samo voditelje odjela za "Uslugu za korisnike".

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

Izlaz:

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

Ili možda želimo "Uslugu za korisnike" i "Ljudske resurse":

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

Izlaz:

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

Uklanjanje pogleda

Brisanje pogleda je vrlo jednostavno. Slično uklanjanju tablice, upisali biste drop view view_name;. Na primjer, ako želimo izbrisati pogled named_salaries , naredba bi bila: drop view named_salaries;.