MySQL-weergaven gebruiken op Debian 7

• Invoering
• Vereisten
• Stap één - Installeer MySQL-server
• Stap twee - Installeer een voorbeelddatabase
• Stap drie - Weergaven maken, gebruiken en verwijderen

Invoering

MySQL heeft een geweldige functie die bekend staat als "views". Weergaven zijn opgeslagen zoekopdrachten. Beschouw ze als een alias voor een anders lange zoekopdracht. In deze gids laat ik u zien hoe u weergaven kunt gebruiken om gegevens efficiënter te organiseren.

Vereisten

• Een Vultr SSD-cloud-server geladen met Debian 7.
• Gebruiker met beheerdersrechten (root).
• Basiskennis van Linux, de opdrachtregel en SSH.

Stap één - Installeer MySQL-server

MySQL installeren op Debian 7.x is heel eenvoudig. Ten eerste moeten we ervoor zorgen dat onze bronnen worden bijgewerkt door het uitvoeren van:

sudo apt-get update

Vervolgens kunnen we de MySQL-server installeren:

sudo apt-get install -y mysql-server

Er wordt een dialoogvenster weergegeven waarin u wordt gevraagd een wachtwoord te maken voor de "root" -gebruiker. Zorg ervoor dat u dit wachtwoord onthoudt.

Laten we de beveiliging van onze installatie verbeteren door het volgende uit te voeren:

sudo mysql_secure_installation

Na uitvoering krijgt u een reeks prompts te zien. Elk van de antwoorden die u moet selecteren, wordt hieronder weergegeven.

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

Stap twee - Installeer een voorbeelddatabase

Op dit moment hebben we geen gegevens op de server om mee te experimenteren. Voor deze tutorial zullen we de werknemersdatabase gebruiken, omdat het gemakkelijk is om mee te werken en vrij beschikbaar is op de MySQL-website.

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

We moeten installeren bzip2zodat we het bestand kunnen uitpakken.

sudo apt-get install bzip2

Pak de database uit. Het bestand is vrij groot, dus het kan even duren.

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

Zodra het bestand is uitgepakt, heeft u een map met de titel employees_db. We moeten naar deze map navigeren om de database te installeren.

cd employees_db 
ls -l

De uitvoer ziet er als volgt uit:

-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

Voer de volgende opdracht uit om verbinding te maken met de MySQL-server, de database te maken en de gegevens te importeren:

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

Er verschijnt een prompt waarin om uw root-wachtwoord wordt gevraagd. Dit is het wachtwoord dat u in stap één heeft ingesteld.

Omdat de database vrij groot is, duurt het waarschijnlijk 1-3 minuten om de gegevens volledig te importeren. Als alles correct is gedaan, ziet u de volgende uitvoer.

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

Nu kunnen we inloggen op MySQL en de geïmporteerde gegevens bekijken.

sudo mysql -h localhost -u root -p

Voer het root- wachtwoord in dat u in de vorige sectie hebt ingesteld.

Bekijk de lijst met databases voor onze nieuwe database voor werknemers .

show databases;

De uitvoer ziet er als volgt uit:

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

Laten we de werknemersdatabase gebruiken .

use employees;

Controleer de tabellen erin.

show tables;

Dit zal het volgende opleveren:

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

Stap drie - Weergaven maken, gebruiken en verwijderen

In deze stap leert u hoe u weergaven maakt en gebruikt. Ik heb deze stap opgesplitst in kleinere secties voor het matchen van gegevens en het combineren van gegevens voor organisatie. Het is tijd om te beginnen met interactie met onze testgegevens.

Gegevens samenvoegen / matchen

Hieronder heb ik een vraag die alle werknemers weergeeft met een jaarsalaris gelijk aan of hoger dan $ 50.000.

select * from salaries where salary >= 50000;

Uitvoer (afgekapt):

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

Zoals u kunt zien, worden hier alleen werknemersnummers weergegeven. Het kan hinderlijk zijn bij het snel identificeren van een medewerker. Gelukkig kunnen we een weergave maken die een vrij lange query opslaat die werknemersaantallen kan matchen met namen van werknemers door gegevens uit meerdere tabellen te halen en te matchen. De vraag wordt hieronder weergegeven.

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;

Merk op hoe ik >= 50000de vraag heb weggelaten . We zullen deze waarde gebruiken nadat onze visie is gecreëerd.

Om de weergave te maken, voegen we eenvoudig create view view_name asde query toe. In dit geval maak ik een weergave met de naam named_salaries .

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;

We geven gegevens uit een weergave weer op dezelfde manier als we gegevens uit een tabel weergeven.

select * from named_salaries

Als de weergave correct is gemaakt, ziet u de volgende uitvoer (gegevens zijn afgekapt):

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

Omdat we op dezelfde manier met weergaven kunnen omgaan als met een tabel, is het mogelijk om de >= 50000van de oorspronkelijke query te nemen en deze toe te passen op de weergave.

select * from named_salaries where salary >= 50000;

Uitvoer (afgekapt):

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

Zoals u kunt zien, heeft de query de weergave behandeld als een traditionele tabel.

Laten we een weergave in een ander voorbeeld gebruiken. Hieronder heb ik een vrij lange vraag met de afdelingsmanagers, hun voor- / achternaam, werknemersnummers, hun afdelingsnamen en de afdelingsnummers. De query haalt gegevens uit verschillende tabellen bij elkaar.

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;

Uitvoer (afgekapt):

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

Zoals u kunt zien, zou het enigszins lastig zijn om die query elke keer in te voeren wanneer u een lijst met afdelingsmanagers moet ophalen. Laten we een weergave maken om het gemakkelijker te maken. Ik noem de visie "management".

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;

Nu kunnen we gewoon typen select * from management;om dezelfde gegevens op te halen. Daar kunnen we natuurlijk ook aanvullende parameters op toepassen - net als bij een traditionele tafel. Stel dat we alleen de afdelingsmanagers willen laten zien voor 'Klantenservice'.

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

Uitgang:

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

Of misschien willen we 'Klantenservice' en 'Human Resources':

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

Uitgang:

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

Een weergave verwijderen

Een weergave verwijderen is heel eenvoudig. Net als bij het verwijderen van een tabel, typt u drop view view_name;. Bijvoorbeeld, als we wilden het verwijderen named_salaries uitzicht, zou de opdracht zijn: drop view named_salaries;.