Introduction
Dans ce laboratoire, vous apprendrez à travailler avec les vues MySQL. Une vue est une table virtuelle basée sur le résultat d'une instruction SQL. Les vues sont utiles pour simplifier des requêtes complexes, masquer la complexité des données et fournir un niveau d'abstraction pour l'accès aux données. Vous apprendrez à créer, mettre à jour, interroger et supprimer des vues MySQL.
Vous commencerez par vous connecter à un serveur MySQL, créer une base de données nommée company et la peupler avec une table employees. Ensuite, vous créerez une vue nommée sales_employees qui filtrera la table employees pour n'afficher que les employés du département 'Sales'. Vous apprendrez à vérifier la structure de la vue et à l'interroger comme une table ordinaire.
Se connecter à MySQL et créer la base de données/table
Dans cette étape, vous allez vous connecter au serveur MySQL et configurer la base de données et la table nécessaires pour le laboratoire. Cela implique la création d'une base de données nommée company et d'une table employees en son sein, peuplée de quelques données d'exemple.
Tout d'abord, ouvrez un terminal dans la VM LabEx. Vous devriez déjà être dans le répertoire ~/project.
Connectez-vous au serveur MySQL en tant qu'utilisateur root. Comme vous disposez de privilèges sudo et que l'utilisateur root dans MySQL est configuré pour utiliser le plugin auth_socket, vous pouvez vous connecter sans mot de passe en utilisant sudo.
sudo mysql -u root
Vous êtes maintenant dans le shell MySQL. L'invite changera pour mysql>.
Dans le shell MySQL, créez une base de données nommée company :
CREATE DATABASE company;
Basculez vers la base de données company afin que les commandes suivantes s'exécutent dans cette base de données :
USE company;
Maintenant, créez une table nommée employees pour stocker les informations des employés :
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(10, 2),
department VARCHAR(50)
);
Insérez des données d'exemple dans la table employees :
INSERT INTO employees (id, first_name, last_name, salary, department) VALUES
(1, 'John', 'Doe', 60000.00, 'Sales'),
(2, 'Jane', 'Smith', 75000.00, 'Marketing'),
(3, 'Robert', 'Jones', 50000.00, 'Sales'),
(4, 'Emily', 'Brown', 80000.00, 'Engineering'),
(5, 'Michael', 'Davis', 65000.00, 'Marketing');
Vous pouvez vérifier les données dans la table employees en sélectionnant toutes les lignes :
SELECT * FROM employees;
La sortie devrait afficher les données des employés insérées :
+----+------------+-----------+----------+-------------+
| id | first_name | last_name | last_name | department |
+----+------------+-----------+----------+-------------+
| 1 | John | Doe | 60000.00 | Sales |
| 2 | Jane | Smith | 75000.00 | Marketing |
| 3 | Robert | Jones | 50000.00 | Sales |
| 4 | Emily | Brown | 80000.00 | Engineering |
| 5 | Michael | Davis | 65000.00 | Marketing |
+----+------------+-----------+----------+-------------+
5 rows in set (0.00 sec)
Vous vous êtes connecté avec succès à MySQL, avez créé une base de données et avez peuplé une table avec des données.
Créer une vue
Dans cette étape, vous allez créer une vue basée sur la table employees. Une vue est une requête SQL stockée qui agit comme une table virtuelle. Elle ne stocke pas de données en elle-même mais présente des données provenant d'une ou plusieurs tables sous-jacentes.
Vous devriez toujours être dans le shell MySQL, connecté à la base de données company. Sinon, reconnectez-vous en utilisant sudo mysql -u root puis USE company;.
Nous allons créer une vue nommée sales_employees qui affichera uniquement les employés travaillant dans le département 'Sales'. Cette vue sélectionnera des colonnes spécifiques de la table employees.
CREATE VIEW sales_employees AS
SELECT id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales';
Cette instruction définit une vue nommée sales_employees. La définition de la vue est la requête SELECT qui suit le mot-clé AS. Cette requête sélectionne les colonnes id, first_name, last_name et salary de la table employees, mais uniquement pour les lignes où la colonne department est égale à 'Sales'.
Pour confirmer que la vue a été créée, vous pouvez lister les tables et les vues dans la base de données actuelle :
SHOW TABLES;
Vous devriez voir employees et sales_employees listées.
+-------------------+
| Tables_in_company |
+-------------------+
| employees |
| sales_employees |
+-------------------+
2 rows in set (0.00 sec)
Vous pouvez également décrire la structure de la vue, tout comme vous le feriez pour une table :
DESCRIBE sales_employees;
Cela affichera les colonnes incluses dans la vue :
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Vous avez créé avec succès une vue nommée sales_employees.
Interroger les données à l'aide de la vue
Dans cette étape, vous apprendrez à interroger des données à l'aide de la vue sales_employees que vous avez créée. Les vues peuvent être interrogées comme des tables ordinaires, offrant un moyen simplifié d'accéder à des sous-ensembles de données spécifiques.
Vous devriez toujours être dans le shell MySQL, connecté à la base de données company. Sinon, reconnectez-vous en utilisant sudo mysql -u root puis USE company;.
Pour récupérer toutes les données de la vue sales_employees, utilisez une instruction SELECT standard :
SELECT * FROM sales_employees;
Cette requête exécute l'instruction SELECT sous-jacente définie dans la vue et renvoie le résultat. Vous ne devriez voir que les employés du département 'Sales' :
+------+------------+-----------+----------+
| id | first_name | last_name | salary |
+------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
| 3 | Robert | Jones | 50000.00 |
+------+------------+-----------+----------+
2 rows in set (0.00 sec)
Vous pouvez également appliquer des filtres ou des tris supplémentaires aux données récupérées à partir de la vue. Par exemple, pour trouver l'employé commercial dont le salaire est supérieur à 55000 :
SELECT * FROM sales_employees WHERE salary > 55000;
Cela renverra uniquement les lignes de la vue où le salaire est supérieur à 55000 :
+------+------------+-----------+----------+
| id | first_name | last_name | salary |
+------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
+------+------------+-----------+----------+
1 row in set (0.00 sec)
Vous pouvez également sélectionner des colonnes spécifiques de la vue :
SELECT first_name, last_name FROM sales_employees;
Cela affichera uniquement les prénoms et noms de famille des employés commerciaux :
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Robert | Jones |
+------------+-----------+
2 rows in set (0.00 sec)
L'interrogation d'une vue est essentiellement la même que l'interrogation d'une table, mais la vue fournit un filtre prédéfini et une sélection de colonnes à partir de la source de données sous-jacente.
Mettre à jour la définition de la vue avec ALTER VIEW
Dans cette étape, vous apprendrez à modifier la définition d'une vue existante à l'aide de l'instruction ALTER VIEW. Ceci est utile lorsque vous devez modifier les colonnes incluses dans la vue ou les critères de filtrage.
Vous devriez toujours être dans le shell MySQL, connecté à la base de données company. Sinon, reconnectez-vous en utilisant sudo mysql -u root puis USE company;.
Actuellement, la vue sales_employees inclut id, first_name, last_name et salary. Modifions la vue pour inclure également la colonne department.
ALTER VIEW sales_employees AS
SELECT id, first_name, last_name, salary, department
FROM employees
WHERE department = 'Sales';
Cette instruction utilise ALTER VIEW suivi du nom de la vue et de la nouvelle requête SELECT qui définit la vue. La nouvelle requête inclut maintenant la colonne department.
Pour vérifier que la vue a été mise à jour, décrivez-la à nouveau :
DESCRIBE sales_employees;
Vous devriez maintenant voir la colonne department dans la sortie :
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| department | varchar(50) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Interrogez la vue mise à jour pour voir la nouvelle colonne :
SELECT * FROM sales_employees;
La sortie inclura maintenant la colonne department :
+------+------------+-----------+----------+------------+
| id | first_name | last_name | salary | department |
+------+------------+-----------+----------+------------+
| 1 | John | Doe | 60000.00 | Sales |
| 3 | Robert | Jones | 50000.00 | Sales |
+------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)
Vous avez mis à jour avec succès la définition de la vue sales_employees.
Supprimer la vue et nettoyer
Dans cette dernière étape, vous apprendrez à supprimer (effacer) la vue et à nettoyer la base de données et la table créées pendant ce laboratoire.
Vous devriez toujours être dans le shell MySQL, connecté à la base de données company. Sinon, reconnectez-vous en utilisant sudo mysql -u root puis USE company;.
Pour supprimer la vue sales_employees, utilisez l'instruction DROP VIEW :
DROP VIEW sales_employees;
Cette commande supprime définitivement la vue sales_employees de la base de données company.
Pour confirmer que la vue a été supprimée, vous pouvez essayer de la décrire :
DESCRIBE sales_employees;
Cela devrait produire un message d'erreur indiquant que la vue n'existe pas :
ERROR 1146 (42S02): Table 'company.sales_employees' doesn't exist
Maintenant, nettoyons la table employees et la base de données company.
Supprimez la table employees :
DROP TABLE employees;
Supprimez la base de données company :
DROP DATABASE company;
Vous pouvez quitter le shell MySQL en tapant :
exit
Vous avez supprimé avec succès la vue, la table et la base de données, nettoyant ainsi les ressources utilisées dans ce laboratoire.
Résumé
Dans ce laboratoire, vous avez appris à travailler avec les vues MySQL. Vous avez commencé par vous connecter au serveur MySQL et configurer une base de données et une table. Vous avez ensuite créé une vue nommée sales_employees pour fournir une vue filtrée de la table employees.
Vous avez pratiqué l'interrogation de la vue à l'aide d'instructions SELECT, démontrant comment les vues simplifient l'accès aux données. Vous avez également appris à modifier la définition d'une vue existante à l'aide de l'instruction ALTER VIEW pour inclure des colonnes supplémentaires. Enfin, vous avez appris à supprimer une vue à l'aide de l'instruction DROP VIEW et avez nettoyé la base de données et la table.
Vous comprenez maintenant les concepts et opérations de base pour travailler avec les vues MySQL, qui sont des outils précieux pour gérer et accéder aux données dans une base de données.
