Notkun MySQL Views á Debian 7

Kynning

MySQL hefur frábæran eiginleika sem kallast „skoðanir“. Skoðanir eru geymdar fyrirspurnir. Hugsaðu um þá sem samnefni fyrir annars langa fyrirspurn. Í þessari handbók mun ég sýna þér hvernig á að nota skoðanir til að skipuleggja gögn á skilvirkari hátt.

Forkröfur

• Vultr SSD skýjaþjónn hlaðinn Debian 7.
• Notandi með stjórnanda (rót) réttindi.
• Grunnþekking á Linux, skipanalínunni og SSH.

Skref eitt - Settu upp MySQL netþjón

Það er mjög einfalt að setja upp MySQL á Debian 7.x. Í fyrsta lagi þurfum við að tryggja að heimildir okkar séu uppfærðar með því að framkvæma:

sudo apt-get update

Næst getum við sett upp MySQL netþjóninn:

sudo apt-get install -y mysql-server

Gluggi mun birtast sem biður þig um að búa til lykilorð fyrir „rót“ notandann. Vertu viss um að muna eftir þessu lykilorði.

Við skulum herða öryggi uppsetningar okkar með því að framkvæma:

sudo mysql_secure_installation

Eftir framkvæmd verður þér kynnt röð leiðbeininga. Hvert af svörunum sem þú ættir að velja eru birt hér að neðan.

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

Skref tvö - Settu upp sýnishornsgagnagrunn

Á þessum tímapunkti höfum við engin gögn á þjóninum til að gera tilraunir með. Fyrir þessa kennslu munum við nota starfsmannagagnagrunninn þar sem það er auðvelt að vinna með hann og aðgengilegur á vefsíðu MySQL.

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

Við þurfum að setja upp bzip2svo við getum dregið út skrána.

sudo apt-get install bzip2

Dragðu út gagnagrunninn. Skráin er frekar stór, svo það gæti tekið smá stund.

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

Þegar búið er að draga út skrána muntu hafa möppu sem heitir employees_db. Við þurfum að fara inn í þessa möppu til að setja upp gagnagrunninn.

cd employees_db 
ls -l

Úttakið mun líta svona út:

-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

Framkvæmdu eftirfarandi skipun til að tengjast MySQL þjóninum, búa til gagnagrunninn og flytja gögnin inn:

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

Hvetja mun birtast sem biður um rót lykilorðið þitt. Þetta er lykilorðið sem þú stilltir í skrefi eitt.

Þar sem gagnagrunnurinn er nokkuð stór mun það líklega taka allt frá 1-3 mínútur að flytja gögnin að fullu inn. Ef allt var gert rétt muntu sjá eftirfarandi úttak.

+-----------------------------+
| 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ú getum við skráð okkur inn á MySQL og skoðað innfluttu gögnin.

sudo mysql -h localhost -u root -p

Sláðu inn kerfisstjóralykilorðið lykilorð sem þú setur í fyrri hlutanum.

Athugaðu listann yfir gagnagrunna fyrir nýstofnaðan starfsmannagagnagrunn okkar .

show databases;

Úttakið mun líta svona út:

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

Notum gagnagrunn starfsmanna .

use employees;

Athugaðu töflurnar innan þess.

show tables;

Þetta mun gefa út:

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

Skref þrjú - Búa til, nota og fjarlægja skoðanir

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.

Til að búa til yfirlitið bætum við einfaldlega við create view view_name asfyrirspurnina. Í þessu tilviki mun ég búa til útsýni sem heitir named_salaris .

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;

Við birtum gögn úr skjá á sama hátt og við birtum gögn úr töflu.

select * from named_salaries

Ef yfirlitið hefur verið búið til á réttan hátt muntu sjá eftirfarandi úttak (gögn hafa verið stytt):

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

Þar sem við getum haft samskipti við skoðanir á sama hátt og við getum haft samskipti við töflu, er hægt að taka >= 50000úr upprunalegu fyrirspurninni og nota það á útsýnið.

select * from named_salaries where salary >= 50000;

Úttak (styttur):

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

Eins og þú sérð hefur fyrirspurnin farið með útsýnið alveg eins og hefðbundna töflu.

Við skulum nota sýn í öðru dæmi. Hér fyrir neðan er ég með nokkuð langa fyrirspurn sem sýnir deildarstjórana, fornöfn/eftirnöfn þeirra, starfsmannanúmer, deildarnöfn þeirra og deildarnúmer. Fyrirspurnin dregur saman gögn úr nokkrum mismunandi töflum.

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;

Úttak (styttur):

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

Eins og þú sérð, væri nokkuð óþægilegt að slá inn þá fyrirspurn í hvert skipti sem þú þarft að sækja lista yfir deildarstjóra. Við skulum búa til útsýni til að gera það auðveldara. Ég ætla að kalla skoðunina "stjórnun".

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ú getum við einfaldlega skrifað select * from management;til að sækja sömu gögnin. Auðvitað getum við líka beitt viðbótarbreytum á það - alveg eins og hefðbundin borð. Segjum til dæmis að við vildum aðeins sýna deildastjórana fyrir „Viðskiptavinaþjónustu“.

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

Framleiðsla:

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

Eða kannski viljum við „viðskiptavinaþjónustu“ og „mannauðs“:

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

Framleiðsla:

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

Fjarlægir útsýni

Það er mjög einfalt að eyða yfirliti. Svipað og þegar þú fjarlægir borð myndirðu slá inn drop view view_name;. Til dæmis, ef við vildum eyða named_salaris yfirlitinu, væri skipunin: drop view named_salaries;.