MySQL Views használata Debian 7 rendszeren

Bevezetés

A MySQL-nek van egy nagyszerű funkciója, az úgynevezett "nézetek". A nézetek tárolt lekérdezések. Tekintse őket álnévnek egy egyébként hosszú lekérdezéshez. Ebben az útmutatóban bemutatom, hogyan használhatja a nézeteket az adatok hatékonyabb rendszerezésére.

Előfeltételek

• Egy Vultr SSD felhőkiszolgáló Debian 7-tel.
• Rendszergazda (root) jogosultsággal rendelkező felhasználó.
• Alapvető Linux, parancssor és SSH ismeretek.

Első lépés – Telepítse a MySQL szervert

A MySQL telepítése Debian 7.x-re nagyon egyszerű. Először is meg kell győződnünk arról, hogy forrásaink frissítésre kerülnek a következő parancs végrehajtásával:

sudo apt-get update

Ezután telepíthetjük a MySQL szervert:

sudo apt-get install -y mysql-server

Megjelenik egy párbeszédpanel, amely arra kéri, hogy hozzon létre jelszót a "root" felhasználó számára. Ügyeljen arra, hogy emlékezzen erre a jelszóra.

Növeljük telepítésünk biztonságát a következő végrehajtással:

sudo mysql_secure_installation

A végrehajtás után egy sor felszólítás jelenik meg. Az alábbiakban mindegyik válasz megjelenik.

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

Második lépés – Mintaadatbázis telepítése

Jelenleg nincs adatunk a szerverről, amellyel kísérletezhetnénk. Ebben az oktatóanyagban az alkalmazottak adatbázisát fogjuk használni , mivel könnyen kezelhető, és szabadon elérhető a MySQL webhelyéről.

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

Telepíteni kell bzip2, hogy ki tudjuk csomagolni a fájlt.

sudo apt-get install bzip2

Bontsa ki az adatbázist. A fájl meglehetősen nagy, így eltarthat néhány pillanatig.

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

Miután kicsomagolta a fájlt, lesz egy mappa, melynek neve employees_db. Az adatbázis telepítéséhez ebbe a könyvtárba kell navigálnunk.

cd employees_db 
ls -l

A kimenet így fog kinézni:

-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

Hajtsa végre a következő parancsot a MySQL szerverhez való csatlakozáshoz, az adatbázis létrehozásához és az adatok importálásához:

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

Megjelenik egy kérdés, amely a root jelszavát kéri. Ez az első lépésben beállított jelszó.

Mivel az adatbázis meglehetősen nagy, valószínűleg 1-3 percet vesz igénybe az adatok teljes importálása. Ha mindent helyesen csinált, akkor a következő kimenetet fogja látni.

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

Most már bejelentkezhetünk a MySQL-be, és megtekinthetjük az importált adatokat.

sudo mysql -h localhost -u root -p

Írja be az előző részben beállított root jelszót.

Tekintse meg az újonnan létrehozott alkalmazotti adatbázisunk adatbázisainak listáját .

show databases;

A kimenet így fog kinézni:

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

Használjuk az alkalmazottak adatbázisát.

use employees;

Ellenőrizze a benne lévő táblázatokat.

show tables;

Ez a következő kimenetet adja:

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

Harmadik lépés – Nézetek létrehozása, használata és eltávolítása

In this step, you will learn to create and use views. I have broken up this step into smaller sections for matching data, and combining data for organization. It's time to start interacting with our test data.

Merging/matching data

Below, I have a query which displays all employees who have a yearly salary equal to, or greater than $50,000.

select * from salaries where salary >= 50000;

Output (truncated):

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

As you can see, this only displays employee numbers. It could be a nuisance when trying to identify an employee quickly. Luckily, we can create a view that will store a fairly lengthy query that can match employee numbers to employee names by pulling and matching data from multiple tables. The query is shown below.

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;

Notice how I have omitted >= 50000 from the query. We will be using this value after our view has been created.

A nézet létrehozásához egyszerűen hozzá create view view_name askell fűzni a lekérdezést. Ebben az esetben létrehozok egy named_salaries nevű nézetet .

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;

A nézet adatait ugyanúgy jelenítjük meg, mint a táblázat adatait.

select * from named_salaries

Ha a nézetet megfelelően hozta létre, akkor a következő kimenetet fogja látni (az adatok csonkolva):

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

Mivel a nézetekkel ugyanúgy interakcióba léphetünk, mint egy táblával, lehetséges, hogy >= 50000az eredeti lekérdezésből kivesszük és a nézetre alkalmazzuk.

select * from named_salaries where salary >= 50000;

Kimenet (csonka):

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

Mint látható, a lekérdezés a nézetet úgy kezelte, mint egy hagyományos táblázatot.

Használjunk egy nézetet egy másik példában. Az alábbiakban van egy meglehetősen hosszadalmas lekérdezésem, amely felsorolja az osztályvezetőket, vezeték- és keresztnevüket, dolgozói számát, osztálynevét és osztályszámát. A lekérdezés több különböző tábla adatait gyűjti össze.

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;

Kimenet (csonka):

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

Amint látja, kissé kényelmetlen lenne beírni ezt a lekérdezést minden alkalommal, amikor le kell kérnie az osztályvezetők listáját. Hozzunk létre egy nézetet, hogy megkönnyítsük. A nézetet "menedzsmentnek" fogom hívni.

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;

Most egyszerűen select * from management;begépelhetjük ugyanazokat az adatokat. Természetesen további paramétereket is alkalmazhatunk ehhez - akár egy hagyományos táblázathoz. Tegyük fel például, hogy csak az „Ügyfélszolgálat” részlegvezetőit akartuk megjeleníteni.

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

Kimenet:

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

Vagy talán "ügyfélszolgálatot" és "emberi erőforrást" szeretnénk:

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

Kimenet:

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

Nézet eltávolítása

A nézet törlése nagyon egyszerű. Hasonló eltávolításával asztalra, akkor írja drop view view_name;. Például, ha törölni akarjuk a named_salaries nézetet, akkor a parancs a következő lenne: drop view named_salaries;.