Utiliser les vues MySQL sur Debian 7

• introduction
• Conditions préalables
• Première étape - Installer le serveur MySQL
• Étape deux - Installer l'exemple de base de données
• Étape 3 - Création, utilisation et suppression de vues

introduction

MySQL a une grande fonctionnalité connue sous le nom de "vues". Les vues sont des requêtes stockées. Considérez-les comme un alias pour une requête autrement longue. Dans ce guide, je vais vous montrer comment utiliser les vues pour organiser les données plus efficacement.

Conditions préalables

• Un serveur cloud SSD Vultr chargé avec Debian 7.
• Utilisateur avec des privilèges d'administrateur (root).
• Connaissance de base de Linux, de la ligne de commande et de SSH.

Première étape - Installer le serveur MySQL

Installer MySQL sur Debian 7.x est très simple. Tout d'abord, nous devons nous assurer que nos sources sont mises à jour en exécutant:

sudo apt-get update

Ensuite, nous pouvons installer le serveur MySQL:

sudo apt-get install -y mysql-server

Une boîte de dialogue s'affiche, vous invitant à créer un mot de passe pour l'utilisateur "root". Assurez-vous de vous souvenir de ce mot de passe.

Durcissons la sécurité de notre installation en exécutant:

sudo mysql_secure_installation

Après l'exécution, une série d'invites vous sera présentée. Chacune des réponses que vous devez sélectionner est affichée ci-dessous.

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

Étape deux - Installer l'exemple de base de données

À ce stade, nous n'avons aucune donnée sur le serveur à tester. Pour ce tutoriel, nous utiliserons la base de données des employés car elle est facile à utiliser et disponible gratuitement sur le site Web de MySQL.

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

Nous devons installer bzip2pour pouvoir extraire le fichier.

sudo apt-get install bzip2

Extrayez la base de données. Le fichier est assez volumineux, cela peut donc prendre quelques instants.

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

Une fois le fichier extrait, vous aurez un dossier intitulé employees_db. Nous devons naviguer dans ce répertoire pour installer la base de données.

cd employees_db 
ls -l

La sortie ressemblera à ceci:

-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

Exécutez la commande suivante pour vous connecter au serveur MySQL, créer la base de données et importer les données:

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

Une invite apparaîtra vous demandant votre mot de passe root. Il s'agit du mot de passe que vous avez défini à l'étape un.

Étant donné que la base de données est assez volumineuse, il faudra probablement de 1 à 3 minutes pour importer complètement les données. Si tout a été fait correctement, vous verrez la sortie suivante.

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

Maintenant, nous pouvons nous connecter à MySQL et afficher les données importées.

sudo mysql -h localhost -u root -p

Saisissez le mot de passe root que vous avez défini dans la section précédente.

Consultez la liste des bases de données pour notre nouvelle base de données des employés .

show databases;

La sortie ressemblera à ceci:

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

Utilisons la base de données des employés .

use employees;

Vérifiez les tableaux qu'il contient.

show tables;

Cela produira:

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

Étape 3 - Création, utilisation et suppression de vues

Dans cette étape, vous apprendrez à créer et à utiliser des vues. J'ai divisé cette étape en sections plus petites pour faire correspondre les données et combiner les données pour l'organisation. Il est temps de commencer à interagir avec nos données de test.

Fusion / correspondance de données

Ci-dessous, j'ai une requête qui affiche tous les employés qui ont un salaire annuel égal ou supérieur à 50 000 $.

select * from salaries where salary >= 50000;

Sortie (tronquée):

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

Comme vous pouvez le voir, cela n'affiche que les numéros d'employés. Cela pourrait être gênant lorsque vous essayez d'identifier rapidement un employé. Heureusement, nous pouvons créer une vue qui stockera une requête assez longue qui peut faire correspondre les numéros des employés aux noms des employés en extrayant et en faisant correspondre les données de plusieurs tables. La requête est présentée ci-dessous.

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;

Remarquez comment j'ai omis >= 50000de la requête. Nous utiliserons cette valeur une fois notre vue créée.

Pour créer la vue, nous ajoutons simplement create view view_name asà la requête. Dans ce cas, je vais créer une vue appelée 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;

Nous affichons les données d'une vue de la même manière que nous affichons les données d'une table.

select * from named_salaries

Si la vue a été créée correctement, vous verrez la sortie suivante (les données ont été tronquées):

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

Étant donné que nous pouvons interagir avec les vues de la même manière que nous pouvons interagir avec une table, il est possible de prendre le >= 50000de la requête d'origine et de l'appliquer à la vue.

select * from named_salaries where salary >= 50000;

Sortie (tronquée):

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

Comme vous pouvez le voir, la requête a traité la vue comme une table traditionnelle.

Utilisons une vue dans un autre exemple. Ci-dessous, j'ai une requête assez longue qui répertorie les chefs de département, leurs prénoms / noms de famille, les numéros d'employés, leurs noms de département et les numéros de département. La requête rassemble les données de plusieurs tables différentes.

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;

Sortie (tronquée):

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

Comme vous pouvez le voir, il serait quelque peu gênant de taper cette requête chaque fois que vous avez besoin de récupérer une liste de chefs de service. Créons une vue pour la rendre plus facile. Je vais appeler la vue "gestion".

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;

Maintenant, nous pouvons simplement taper select * from management;pour récupérer les mêmes données. Bien sûr, nous pouvons également appliquer des paramètres supplémentaires à cela - tout comme une table traditionnelle. Par exemple, supposons que nous voulions afficher uniquement les responsables des départements pour le "Service client".

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

Production:

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

Ou peut-être voulons-nous «service client» et «ressources humaines»:

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

Production:

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

Supprimer une vue

La suppression d'une vue est très simple. Comme pour supprimer une table, vous devez taper drop view view_name;. Par exemple, si nous voulions supprimer l' named_salaries vue, la commande serait: drop view named_salaries;.