MySQL skatu izmantošana operētājsistēmā Debian 7

Ievads

MySQL ir lieliska funkcija, kas pazīstama kā "skati". Skatījumi ir saglabāti vaicājumi. Uztveriet tos kā aizstājvārdu citādi gariem vaicājumiem. Šajā rokasgrāmatā es jums parādīšu, kā izmantot skatus, lai efektīvāk sakārtotu datus.

Priekšnoteikumi

• Vultr SSD mākoņserveris, kas ielādēts ar Debian 7.
• Lietotājs ar administratora (saknes) tiesībām.
• Pamatzināšanas par Linux, komandrindu un SSH.

Pirmais solis - instalējiet MySQL serveri

MySQL instalēšana uz Debian 7.x ir ļoti vienkārša. Pirmkārt, mums ir jānodrošina, lai mūsu avoti tiktu atjaunināti, izpildot:

sudo apt-get update

Tālāk mēs varam instalēt MySQL serveri:

sudo apt-get install -y mysql-server

Tiks parādīts dialoglodziņš ar aicinājumu izveidot paroli "saknes" lietotājam. Noteikti atcerieties šo paroli.

Pastiprināsim mūsu instalācijas drošību, izpildot:

sudo mysql_secure_installation

Pēc izpildes jums tiks parādīta virkne uzvedņu. Katra no atbildēm, kas jums vajadzētu atlasīt, ir parādīta zemāk.

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

Otrais solis – instalējiet datu bāzes paraugus

Šobrīd mums nav datu par serveri, ar kuriem varētu eksperimentēt. Šajā apmācībā mēs izmantosim darbinieku datu bāzi, jo ar to ir viegli strādāt un tā ir brīvi pieejama MySQL vietnē.

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

Mums ir jāinstalē, bzip2lai mēs varētu izvilkt failu.

sudo apt-get install bzip2

Izvelciet datu bāzi. Fails ir diezgan liels, tāpēc tas var aizņemt dažus mirkļus.

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

Kad fails būs izvilkts, jums būs mape ar nosaukumu employees_db. Lai instalētu datu bāzi, mums ir jāpārvietojas šajā direktorijā.

cd employees_db 
ls -l

Izvade izskatīsies šādi:

-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

Izpildiet šo komandu, lai izveidotu savienojumu ar MySQL serveri, izveidotu datu bāzi un importētu datus:

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

Parādīsies uzvedne, kurā tiks prasīta jūsu root parole. Šī ir parole, kuru iestatījāt pirmajā darbībā.

Tā kā datu bāze ir diezgan liela, datu pilnīga importēšana, iespējams, prasīs 1–3 minūtes. Ja viss tika izdarīts pareizi, jūs redzēsit šādu izvadi.

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

Tagad mēs varam pieteikties MySQL un skatīt importētos datus.

sudo mysql -h localhost -u root -p

Ievadiet saknes paroli, ko iestatījāt iepriekšējā sadaļā.

Pārbaudiet mūsu jaunizveidotās darbinieku datu bāzes datu bāzu sarakstu.

show databases;

Izvade izskatīsies šādi:

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

Izmantosim darbinieku datu bāzi.

use employees;

Pārbaudiet tajā esošās tabulas.

show tables;

Tas izvadīs:

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

Trešais solis — skatu izveide, izmantošana un noņemšana

Šajā solī jūs iemācīsities izveidot un izmantot skatus. Esmu sadalījis šo soli mazākās sadaļās datu saskaņošanai un datu apvienošanai organizācijai. Ir pienācis laiks sākt mijiedarboties ar mūsu testa datiem.

Datu sapludināšana/saskaņošana

Tālāk man ir vaicājums, kas parāda visus darbiniekus, kuru gada alga ir vienāda ar 50 000 USD vai lielāka.

select * from salaries where salary >= 50000;

Izvade (saīsināta):

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

Kā redzat, tas parāda tikai darbinieku numurus. Tas varētu radīt neērtības, mēģinot ātri identificēt darbinieku. Par laimi, mēs varam izveidot skatu, kurā tiks saglabāts diezgan garš vaicājums, kas var saskaņot darbinieku numurus ar darbinieku vārdiem, izvelkot un saskaņojot datus no vairākām tabulām. Vaicājums ir parādīts zemāk.

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;

Ievērojiet, kā esmu izlaidis >= 50000vaicājumu. Mēs izmantosim šo vērtību pēc skata izveides.

Lai izveidotu skatu, mēs vienkārši pievienojam create view view_name asvaicājumam. Šajā gadījumā es izveidošu skatu ar nosaukumu 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;

Mēs attēlojam datus no skata tādā pašā veidā kā datus no tabulas.

select * from named_salaries

Ja skats ir izveidots pareizi, jūs redzēsiet šādu izvadi (dati ir saīsināti):

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

Tā kā mēs varam mijiedarboties ar skatiem tādā pašā veidā, kā mēs varam mijiedarboties ar tabulu, ir iespējams ņemt >= 50000no sākotnējā vaicājuma un lietot to skatam.

select * from named_salaries where salary >= 50000;

Izvade (saīsināta):

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

Kā redzat, vaicājumā skats ir apstrādāts tāpat kā tradicionālajā tabulā.

Izmantosim skatu citā piemērā. Tālāk man ir diezgan garš vaicājums, kurā ir uzskaitīti nodaļu vadītāji, viņu vārdi/uzvārdi, darbinieku numuri, nodaļu nosaukumi un nodaļu numuri. Vaicājums apkopo datus no vairākām dažādām tabulām.

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;

Izvade (saīsināta):

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

Kā redzat, būtu nedaudz neērti ievadīt šo vaicājumu katru reizi, kad jums ir nepieciešams ielādēt nodaļu vadītāju sarakstu. Izveidosim skatu, lai būtu vieglāk. Skatu es saukšu par "vadību".

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;

Tagad mēs varam vienkārši rakstīt, select * from management;lai izgūtu tos pašus datus. Protams, mēs varam tam piemērot arī papildu parametrus - gluži kā tradicionālajai tabulai. Piemēram, pieņemsim, ka vēlamies rādīt tikai klientu apkalpošanas nodaļu vadītājus.

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

Izvade:

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

Vai varbūt mēs vēlamies "klientu apkalpošanu" un "cilvēkresursus":

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

Izvade:

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

Skata noņemšana

Skata dzēšana ir ļoti vienkārša. Līdzīgi kā noņemot tabulu, jūs ierakstāt drop view view_name;. Piemēram, ja mēs vēlamies dzēst skatu named_salries , komanda būtu šāda: drop view named_salaries;.