Usare le viste MySQL su Debian 7

• introduzione
• Prerequisiti
• Fase 1: installare il server MySQL
• Passaggio 2: installare il database di esempio
• Passaggio tre: creazione, utilizzo e rimozione di viste

introduzione

MySQL ha una grande funzionalità nota come "viste". Le viste sono query memorizzate. Pensa a loro come alias per una query altrimenti lunga. In questa guida, ti mostrerò come utilizzare le viste per organizzare i dati in modo più efficiente.

Prerequisiti

• Un server cloud SSD Vultr caricato con Debian 7.
• Utente con privilegi di amministratore (root).
• Conoscenza di base di Linux, riga di comando e SSH.

Fase 1: installare il server MySQL

Installare MySQL su Debian 7.x è molto semplice. Innanzitutto, dobbiamo garantire che le nostre fonti vengano aggiornate eseguendo:

sudo apt-get update

Successivamente, possiamo installare il server MySQL:

sudo apt-get install -y mysql-server

Verrà visualizzata una finestra di dialogo che richiede di creare una password per l'utente "root". Assicurati di ricordare questa password.

Rafforziamo la sicurezza della nostra installazione eseguendo:

sudo mysql_secure_installation

Dopo l'esecuzione, ti verrà presentata una serie di istruzioni. Ciascuna delle risposte che è necessario selezionare sono visualizzate di seguito.

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

Passaggio 2: installare il database di esempio

A questo punto, non abbiamo dati sul server con cui sperimentare. Per questo tutorial, utilizzeremo il database dei dipendenti in quanto è facile da lavorare e disponibile gratuitamente dal sito Web di MySQL.

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

Dobbiamo installarlo in bzip2modo da poter estrarre il file.

sudo apt-get install bzip2

Estrarre il database. Il file è abbastanza grande, quindi potrebbero essere necessari alcuni istanti.

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

Una volta estratto il file, avrai una cartella intitolata employees_db. Dobbiamo navigare in questa directory per installare il database.

cd employees_db 
ls -l

L'output sarà simile al seguente:

-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

Eseguire il comando seguente per connettersi al server MySQL, creare il database e importare i dati:

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

Viene visualizzato un messaggio che richiede la password di root. Questa è la password impostata nel passaggio uno.

Dato che il database è piuttosto grande, probabilmente ci vorranno da 1 a 3 minuti per importare completamente i dati. Se tutto è stato fatto correttamente, vedrai il seguente output.

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

Ora possiamo accedere a MySQL e visualizzare i dati importati.

sudo mysql -h localhost -u root -p

Immettere la password di root impostata nella sezione precedente.

Controlla l'elenco dei database per il nostro database dei dipendenti appena creato .

show databases;

L'output sarà simile al seguente:

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

Usiamo il database dei dipendenti .

use employees;

Controlla le tabelle al suo interno.

show tables;

Questo produrrà:

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

Passaggio tre: creazione, utilizzo e rimozione di viste

In questo passaggio, imparerai a creare e utilizzare le viste. Ho suddiviso questo passaggio in sezioni più piccole per la corrispondenza dei dati e la combinazione di dati per l'organizzazione. È tempo di iniziare a interagire con i nostri dati di test.

Unione / corrispondenza dei dati

Di seguito, ho una query che mostra tutti i dipendenti che hanno uno stipendio annuale pari o superiore a $ 50.000.

select * from salaries where salary >= 50000;

Uscita (troncata):

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

Come puoi vedere, questo mostra solo i numeri dei dipendenti. Potrebbe essere una seccatura quando si cerca di identificare rapidamente un dipendente. Fortunatamente, possiamo creare una vista che memorizzerà una query abbastanza lunga in grado di abbinare i numeri dei dipendenti ai nomi dei dipendenti estraendo e abbinando i dati da più tabelle. La query è mostrata di seguito.

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;

Nota come ho omesso >= 50000la query. Utilizzeremo questo valore dopo la creazione della nostra vista.

Per creare la vista, aggiungiamo semplicemente create view view_name asalla query. In questo caso, creerò una vista chiamata 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;

Visualizziamo i dati da una vista nello stesso modo in cui visualizziamo i dati da una tabella.

select * from named_salaries

Se la vista è stata creata correttamente, verrà visualizzato il seguente output (i dati sono stati troncati):

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

Dal momento che possiamo interagire con le viste nello stesso modo in cui possiamo interagire con una tabella, è possibile prendere il >= 50000dalla query originale e applicarlo alla vista.

select * from named_salaries where salary >= 50000;

Uscita (troncata):

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

Come puoi vedere, la query ha trattato la vista proprio come una tabella tradizionale.

Usiamo una vista in un altro esempio. Di seguito, ho una query abbastanza lunga che elenca i responsabili di reparto, i loro nomi / cognomi, i numeri dei dipendenti, i nomi dei loro dipartimenti e i numeri dei dipartimenti. La query raccoglie i dati da diverse tabelle.

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;

Uscita (troncata):

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

Come puoi vedere, sarebbe in qualche modo scomodo digitare quella query ogni volta che devi recuperare un elenco di responsabili di reparto. Creiamo una vista per renderlo più semplice. Chiamerò la vista "gestione".

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;

Ora, possiamo semplicemente digitare select * from management;per recuperare gli stessi dati. Naturalmente, possiamo anche applicare parametri aggiuntivi a questo, proprio come una tabella tradizionale. Ad esempio, supponiamo di voler mostrare solo i responsabili dei dipartimenti per "Servizio clienti".

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

Produzione:

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

O forse vogliamo "Servizio clienti" e "Risorse umane":

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

Produzione:

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

Rimozione di una vista

L'eliminazione di una vista è molto semplice. Simile alla rimozione di una tabella, digitare drop view view_name;. Ad esempio, se volessimo eliminare la named_salaries vista, il comando sarà: drop view named_salaries;.