MySQL-näkymien käyttäminen Debian 7:ssä

Johdanto

MySQL:ssä on hieno ominaisuus, joka tunnetaan nimellä "näkymät". Näkymät ovat tallennettuja kyselyitä. Ajattele niitä aliaksena muuten pitkälle kyselylle. Tässä oppaassa näytän sinulle, kuinka voit käyttää näkymiä tietojen järjestämiseen tehokkaammin.

Edellytykset

• Vultr SSD -pilvipalvelin, joka on ladattu Debian 7:llä.
• Käyttäjä, jolla on järjestelmänvalvojan (root) oikeudet.
• Perustiedot Linuxista, komentoriviltä ja SSH:sta.

Vaihe yksi - Asenna MySQL-palvelin

MySQL:n asentaminen Debian 7.x:ään on hyvin yksinkertaista. Ensinnäkin meidän on varmistettava, että lähteemme päivitetään suorittamalla:

sudo apt-get update

Seuraavaksi voimme asentaa MySQL-palvelimen:

sudo apt-get install -y mysql-server

Näyttöön tulee valintaikkuna, jossa sinua pyydetään luomaan salasana "root"-käyttäjälle. Muista muistaa tämä salasana.

Kovennetaan asennuksemme turvallisuutta suorittamalla:

sudo mysql_secure_installation

Suorituksen jälkeen sinulle esitetään sarja kehotteita. Kaikki vastaukset, jotka sinun tulee valita, näkyvät alla.

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

Vaihe kaksi - Asenna mallitietokanta

Tässä vaiheessa meillä ei ole palvelimella testattavaa dataa. Tässä opetusohjelmassa käytämme työntekijöiden tietokantaa, koska sen kanssa on helppo työskennellä ja se on vapaasti saatavilla MySQL:n verkkosivuilta.

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

Meidän on asennettava, bzip2jotta voimme purkaa tiedoston.

sudo apt-get install bzip2

Pura tietokanta. Tiedosto on melko suuri, joten se voi kestää hetken.

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

Kun tiedosto on purettu, sinulla on kansio nimeltä employees_db. Meidän on navigoitava tähän hakemistoon asentaaksemme tietokannan.

cd employees_db 
ls -l

Tulos näyttää tältä:

-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

Suorita seuraava komento muodostaaksesi yhteyden MySQL-palvelimeen, luodaksesi tietokannan ja tuodaksesi tiedot:

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

Näkyviin tulee kehote, jossa kysytään root-salasanaa. Tämä on salasana, jonka määritit vaiheessa yksi.

Koska tietokanta on melko suuri, tietojen tuominen kokonaisuudessaan kestää todennäköisesti 1-3 minuuttia. Jos kaikki tehtiin oikein, näet seuraavan tulosteen.

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

Nyt voimme kirjautua MySQL:ään ja tarkastella tuotuja tietoja.

sudo mysql -h localhost -u root -p

Anna root- salasana, jonka määritit edellisessä osiossa.

Tarkista äskettäin luodun työntekijätietokannan tietokannat.

show databases;

Tulos näyttää tältä:

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

Käytetään työntekijöiden tietokantaa.

use employees;

Tarkista siinä olevat taulukot.

show tables;

Tämä tulostaa:

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

Vaihe kolme - Näkymien luominen, käyttäminen ja poistaminen

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.

Luodaksesi näkymän, lisäämme create view view_name askyselyyn. Tässä tapauksessa luon näkymän nimeltä 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;

Näytämme tiedot näkymästä samalla tavalla kuin näytämme tiedot taulukosta.

select * from named_salaries

Jos näkymä on luotu oikein, näet seuraavan tulosteen (tiedot on katkaistu):

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

Koska voimme olla vuorovaikutuksessa näkymien kanssa samalla tavalla kuin taulukon kanssa, on mahdollista ottaa >= 50000alkuperäisestä kyselystä ja soveltaa sitä näkymään.

select * from named_salaries where salary >= 50000;

Tulos (katkaistu):

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

Kuten näet, kysely on käsitellyt näkymää aivan kuten perinteistä taulukkoa.

Käytetään näkymää toisessa esimerkissä. Alla minulla on melko pitkä kysely, jossa luetellaan osastopäälliköt, heidän etu-/sukunimet, työntekijöiden numerot, osastojen nimet ja osastojen numerot. Kysely kerää tietoja useista eri taulukoista.

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;

Tulos (katkaistu):

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

Kuten näet, olisi hieman hankalaa kirjoittaa kyseinen kysely joka kerta, kun sinun on haettava luettelo osastopäälliköistä. Luodaan näkymä helpottamaan sitä. Aion kutsua näkymää "johtamiseksi".

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;

Nyt voimme yksinkertaisesti kirjoittaa select * from management;hakeaksemme samat tiedot. Voimme tietysti käyttää siihen myös lisäparametreja - aivan kuten perinteisessä taulukossa. Oletetaan esimerkiksi, että halusimme näyttää vain osastopäälliköt kohdassa "Asiakaspalvelu".

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

Lähtö:

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

Tai ehkä haluamme "asiakaspalvelua" ja "henkilöstöä":

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

Lähtö:

+------------+--------------+--------+------------+------------+------------------+---------+
| 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äkymän poistaminen

Näkymän poistaminen on hyvin yksinkertaista. Samalla tavalla kuin poistaisit taulukon, kirjoitat drop view view_name;. Jos esimerkiksi halusimme poistaa named_salries -näkymän, komento olisi: drop view named_salaries;.