„MySQL Views“ naudojimas „Debian 7“.

Įvadas

„MySQL“ turi puikią funkciją, vadinamą „vaizdais“. Rodiniai yra saugomos užklausos. Pagalvokite apie juos kaip apie kitaip ilgos užklausos slapyvardį. Šiame vadove parodysiu, kaip naudoti rodinius duomenims tvarkyti efektyviau.

Būtinos sąlygos

• Vultr SSD debesies serveris, įkeltas su Debian 7.
• Vartotojas, turintis administratoriaus (root) teises.
• Pagrindinės Linux, komandinės eilutės ir SSH žinios.

Pirmas žingsnis – įdiekite MySQL serverį

MySQL įdiegimas Debian 7.x yra labai paprastas. Pirmiausia turime užtikrinti, kad mūsų šaltiniai būtų atnaujinti, vykdant:

sudo apt-get update

Tada galime įdiegti MySQL serverį:

sudo apt-get install -y mysql-server

Bus rodomas dialogo langas, raginantis sukurti „root“ vartotojo slaptažodį. Būtinai prisiminkite šį slaptažodį.

Sustiprinkime savo diegimo saugumą vykdydami:

sudo mysql_secure_installation

Po vykdymo jums bus pateikta daugybė raginimų. Kiekvienas iš atsakymų, kuriuos turėtumėte pasirinkti, rodomas žemiau.

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

Antras žingsnis – įdiekite pavyzdinę duomenų bazę

Šiuo metu mes neturime duomenų apie serverį, su kuriais galėtume eksperimentuoti. Šioje pamokoje naudosime darbuotojų duomenų bazę, nes su ja lengva dirbti ir ji laisvai pasiekiama MySQL svetainėje.

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

Turime įdiegti bzip2, kad galėtume išskleisti failą.

sudo apt-get install bzip2

Išskleiskite duomenų bazę. Failas yra gana didelis, todėl tai gali užtrukti keletą akimirkų.

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

Kai failas bus ištrauktas, turėsite aplanką pavadinimu employees_db. Norėdami įdiegti duomenų bazę, turime pereiti į šį katalogą.

cd employees_db 
ls -l

Išvestis atrodys taip:

-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

Vykdykite šią komandą, kad prisijungtumėte prie MySQL serverio, sukurtumėte duomenų bazę ir importuotumėte duomenis:

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

Pasirodys raginimas, kuriame bus prašoma įvesti root slaptažodį. Tai slaptažodis, kurį nustatėte atlikdami pirmąjį veiksmą.

Kadangi duomenų bazė yra gana didelė, visiškai importuoti duomenis tikriausiai prireiks 1–3 minučių. Jei viskas buvo padaryta teisingai, pamatysite šią išvestį.

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

Dabar galime prisijungti prie MySQL ir peržiūrėti importuotus duomenis.

sudo mysql -h localhost -u root -p

Įveskite root slaptažodį, kurį nustatėte ankstesniame skyriuje.

Patikrinkite mūsų naujai sukurtos darbuotojų duomenų bazės duomenų bazių sąrašą.

show databases;

Išvestis atrodys taip:

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

Pasinaudokime darbuotojų duomenų baze.

use employees;

Patikrinkite jame esančias lenteles.

show tables;

Tai išves:

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

Trečias veiksmas – rodinių kūrimas, naudojimas ir pašalinimas

Šiame žingsnyje išmoksite kurti ir naudoti rodinius. Šį veiksmą suskaidžiau į mažesnes dalis, kad suderinčiau duomenis ir sujungčiau organizacijos duomenis. Atėjo laikas pradėti sąveikauti su mūsų bandymų duomenimis.

Duomenų sujungimas / suderinimas

Žemiau turiu užklausą, kurioje rodomi visi darbuotojai, kurių metinis atlyginimas yra 50 000 USD arba didesnis.

select * from salaries where salary >= 50000;

Išvestis (sutrumpinta):

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

Kaip matote, rodomi tik darbuotojų numeriai. Tai gali būti nemalonu, kai bandoma greitai atpažinti darbuotoją. Laimei, galime sukurti vaizdą, kuriame bus saugoma gana ilga užklausa, kuri gali suderinti darbuotojų numerius su darbuotojų vardais, ištraukiant ir suderinant duomenis iš kelių lentelių. Užklausa parodyta žemiau.

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;

Atkreipkite dėmesį, kaip aš praleidau >= 50000iš užklausos. Šią vertę naudosime po to, kai bus sukurtas vaizdas.

Norėdami sukurti vaizdą, tiesiog create view view_name aspridedame prie užklausos. Tokiu atveju sukursiu rodinį pavadinimu named_salries .

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;

Rodinio duomenis rodome taip pat, kaip duomenis iš lentelės.

select * from named_salaries

Jei vaizdas buvo sukurtas tinkamai, pamatysite šią išvestį (duomenys buvo sutrumpinti):

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

Kadangi galime sąveikauti su rodiniais taip pat, kaip ir su lentele, galima paimti >= 50000iš pradinės užklausos ir pritaikyti ją rodiniui.

select * from named_salaries where salary >= 50000;

Išvestis (sutrumpinta):

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

Kaip matote, užklausoje vaizdas buvo traktuojamas kaip tradicinė lentelė.

Naudokime vaizdą kitame pavyzdyje. Žemiau turiu gana ilgą užklausą, kurioje pateikiami skyrių vadovai, jų vardai / pavardės, darbuotojų numeriai, skyrių pavadinimai ir skyrių numeriai. Užklausa surenka duomenis iš kelių skirtingų lentelių.

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;

Išvestis (sutrumpinta):

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

Kaip matote, būtų šiek tiek nepatogu įvesti tą užklausą kiekvieną kartą, kai reikia gauti skyrių vadovų sąrašą. Sukurkime vaizdą, kad būtų lengviau. Šį vaizdą pavadinsiu „vadyba“.

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;

Dabar galime tiesiog įvesti select * from management;tekstą, kad gautume tuos pačius duomenis. Žinoma, tam galime pritaikyti ir papildomus parametrus – kaip ir tradicinei lentelei. Pavyzdžiui, tarkime, kad norime rodyti tik „Klientų aptarnavimo“ skyrių vadovus.

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

Išvestis:

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

O gal norime „Klientų aptarnavimo“ ir „Žmogiškųjų išteklių“:

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

Išvestis:

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

Rodinio pašalinimas

Ištrinti rodinį labai paprasta. Panašiai kaip pašalindami lentelę, įvestumėte drop view view_name;. Pavyzdžiui, jei norėtume ištrinti named_salries rodinį, komanda būtų tokia: drop view named_salaries;.