MySQL-i vaadete kasutamine Debian 7-s

Sissejuhatus

MySQL-il on suurepärane funktsioon, mida nimetatakse "vaadeteks". Vaated on salvestatud päringud. Mõelge neile kui muidu pika päringu varjunimele. Selles juhendis näitan teile, kuidas kasutada vaateid andmete tõhusamaks korraldamiseks.

Eeltingimused

• Debian 7-ga laaditud Vultr SSD pilveserver.
• Administraatori (juur)õigustega kasutaja.
• Algteadmised Linuxist, käsureast ja SSH-st.

Esimene samm – installige MySQL-server

MySQL-i installimine versioonile Debian 7.x on väga lihtne. Esiteks peame tagama, et meie allikaid värskendatakse, käivitades:

sudo apt-get update

Järgmisena saame installida MySQL-i serveri:

sudo apt-get install -y mysql-server

Kuvatakse dialoogiboks, mis palub teil luua "root" kasutaja parool. Kindlasti mäletate seda parooli.

Tugevdame oma installi turvalisust, käivitades:

sudo mysql_secure_installation

Pärast täitmist esitatakse teile rida viipasid. Kõik vastused, mille peaksite valima, kuvatakse allpool.

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

Teine samm – installige näidisandmebaas

Praegu pole meil serveri kohta andmeid, millega katsetada. Selle õpetuse jaoks kasutame töötajate andmebaasi, kuna sellega on lihtne töötada ja see on MySQL-i veebisaidilt vabalt saadaval.

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

Peame installima bzip2, et saaksime faili ekstraktida.

sudo apt-get install bzip2

Ekstraktige andmebaas. Fail on üsna suur, nii et see võib võtta mõne hetke.

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

Kui fail on ekstraktitud, on teil kaust nimega employees_db. Andmebaasi installimiseks peame sellesse kataloogi navigeerima.

cd employees_db 
ls -l

Väljund näeb välja selline:

-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

MySQL-serveriga ühenduse loomiseks, andmebaasi loomiseks ja andmete importimiseks käivitage järgmine käsk:

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

Ilmub viip, mis küsib teie juurparooli. See on parool, mille määrasite esimeses etapis.

Kuna andmebaas on üsna suur, kulub andmete täielikuks importimiseks tõenäoliselt 1–3 minutit. Kui kõik tehti õigesti, näete järgmist väljundit.

+-----------------------------+
| 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üüd saame MySQL-i sisse logida ja imporditud andmeid vaadata.

sudo mysql -h localhost -u root -p

Sisestage root parooli, mida eelmises sektsioonis.

Kontrollige meie vastloodud töötajate andmebaasi andmebaaside loendit .

show databases;

Väljund näeb välja selline:

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

Kasutame töötajate andmebaasi.

use employees;

Kontrollige selle sees olevaid tabeleid.

show tables;

See väljastab:

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

Kolmas samm – vaadete loomine, kasutamine ja eemaldamine

Selles etapis õpite vaateid looma ja kasutama. Jagasin selle etapi andmete sobitamiseks ja organisatsiooni andmete kombineerimiseks väiksemateks osadeks. On aeg hakata meie testandmetega suhtlema.

Andmete ühendamine/sobitamine

Allpool on mul päring, mis kuvab kõik töötajad, kelle aastapalk on 50 000 dollarit või suurem.

select * from salaries where salary >= 50000;

Väljund (kärbitud):

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

Nagu näete, kuvab see ainult töötajate numbreid. See võib olla häiriv, kui üritate töötajat kiiresti tuvastada. Õnneks saame luua vaate, mis salvestab üsna pika päringu, mis suudab mitmest tabelist andmeid tõmmata ja sobitada töötajate numbrid töötajate nimedega. Päring on näidatud allpool.

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;

Pange tähele, kuidas ma olen >= 50000päringust välja jätnud . Kasutame seda väärtust pärast vaate loomist.

Vaate loomiseks lisame lihtsalt create view view_name aspäringule. Sel juhul loon vaate nimega_palgad .

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;

Kuvame andmeid vaatest samal viisil, nagu kuvame andmeid tabelist.

select * from named_salaries

Kui vaade on õigesti loodud, näete järgmist väljundit (andmed on kärbitud):

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

Kuna saame vaadetega suhelda samamoodi nagu tabeliga, on võimalik võtta >= 50000algsest päringust ja rakendada seda vaatele.

select * from named_salaries where salary >= 50000;

Väljund (kärbitud):

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

Nagu näete, on päring käsitlenud vaadet nagu tavalist tabelit.

Kasutame vaadet teises näites. Allpool on mul üsna pikk päring, mis loetleb osakonnajuhatajad, nende ees- ja perekonnanimed, töötajate numbrid, osakondade nimed ja osakondade numbrid. Päring koondab andmed mitmest erinevast tabelist.

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;

Väljund (kärbitud):

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

Nagu näete, oleks mõnevõrra ebamugav seda päringut sisestada iga kord, kui peate hankima osakonnajuhtide loendi. Loome lihtsamaks muutmiseks vaate. Ma hakkan vaadet nimetama "juhtimiseks".

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üüd saame select * from management;samade andmete hankimiseks lihtsalt tippida . Loomulikult saame sellele rakendada ka täiendavaid parameetreid – täpselt nagu traditsioonilise tabeli puhul. Oletagem näiteks, et tahame kuvada ainult "Klienditeeninduse" osakonnajuhatajad.

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

Väljund:

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

Või äkki tahame "klienditeenindust" ja "personaliressursse":

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

Väljund:

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

Vaate eemaldamine

Vaate kustutamine on väga lihtne. Sarnaselt tabeli eemaldamisega peaksite tippima drop view view_name;. Näiteks kui sooviksime kustutada vaadet nimega_palgad , oleks käsk järgmine: drop view named_salaries;.