Dans ce laboratoire, vous apprendrez à effectuer le filtrage de données et des requêtes simples dans PostgreSQL. Vous commencerez par vous connecter à une base de données PostgreSQL et à créer une table employees (employés) avec des données.
Le laboratoire vous guidera ensuite dans l'utilisation de la clause WHERE pour filtrer les données en fonction de conditions spécifiques, en utilisant LIKE pour la correspondance de motifs (pattern matching), en triant les résultats avec ORDER BY, et en limitant le nombre de lignes renvoyées à l'aide de LIMIT et OFFSET.
Skills Graph
%%%%{init: {'theme':'neutral'}}%%%%
flowchart RL
postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"])
postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table")
postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row")
postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row")
postgresql/PostgreSQLGroup -.-> postgresql/func_call("Call Stored Function")
subgraph Lab Skills
postgresql/table_init -.-> lab-550947{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
postgresql/row_add -.-> lab-550947{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
postgresql/row_drop -.-> lab-550947{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
postgresql/func_call -.-> lab-550947{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
end
Filtrer les données avec la clause WHERE
Dans cette étape, vous apprendrez à utiliser la clause WHERE dans PostgreSQL pour filtrer les données en fonction de conditions spécifiques. La clause WHERE est un outil puissant qui vous permet de récupérer uniquement les lignes qui répondent à vos critères.
Tout d'abord, ouvrez un terminal dans votre VM LabEx. Vous pouvez utiliser le terminal Xfce par défaut.
Connectez-vous à la base de données PostgreSQL en utilisant la commande psql :
Maintenant, créons une table d'exemple nommée employees (employés) et insérons des données dedans. Cette table stockera les informations des employés, y compris leur ID, nom, département et salaire. Exécutez les instructions SQL suivantes :
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary INTEGER
);
INSERT INTO employees (name, department, salary) VALUES
('Alice Smith', 'Sales', 60000),
('Bob Johnson', 'Marketing', 75000),
('Charlie Brown', 'Sales', 55000),
('David Lee', 'Engineering', 90000),
('Eve Wilson', 'Marketing', 80000),
('Frank Miller', 'Engineering', 95000);
Pour vérifier que la table a été créée et remplie correctement, exécutez la requête SQL suivante :
SELECT * FROM employees;
Vous devriez voir une sortie similaire à celle-ci :
id | name | department | salary
----+-----------------+-------------+--------
1 | Alice Smith | Sales | 60000
2 | Bob Johnson | Marketing | 75000
3 | Charlie Brown | Sales | 55000
4 | David Lee | Engineering | 90000
5 | Eve Wilson | Marketing | 80000
6 | Frank Miller | Engineering | 95000
(6 rows)
Maintenant, utilisons la clause WHERE pour filtrer les données. Supposons que nous voulions récupérer uniquement les employés qui travaillent dans le département 'Sales' (Ventes). Exécutez la requête suivante :
SELECT * FROM employees WHERE department = 'Sales';
Cette requête renverra uniquement les lignes où la colonne department est égale à 'Sales'. La sortie devrait être :
id | name | department | salary
----+-----------------+------------+--------
1 | Alice Smith | Sales | 60000
3 | Charlie Brown | Sales | 55000
(2 rows)
Vous pouvez également utiliser d'autres opérateurs de comparaison dans la clause WHERE, tels que >, <, >=, <=, et <>. Par exemple, pour récupérer les employés ayant un salaire supérieur à 70000, exécutez la requête suivante :
SELECT * FROM employees WHERE salary > 70000;
La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
2 | Bob Johnson | Marketing | 75000
4 | David Lee | Engineering | 90000
5 | Eve Wilson | Marketing | 80000
6 | Frank Miller | Engineering | 95000
(4 rows)
Vous pouvez également combiner plusieurs conditions en utilisant des opérateurs logiques tels que AND et OR. Par exemple, pour récupérer les employés qui travaillent dans le département 'Sales' et ont un salaire supérieur à 58000, exécutez la requête suivante :
SELECT * FROM employees WHERE department = 'Sales' AND salary > 58000;
La sortie devrait être :
id | name | department | salary
----+---------------+------------+--------
1 | Alice Smith | Sales | 60000
(1 row)
Utiliser LIKE pour la correspondance de motifs (Pattern Matching)
Dans cette étape, vous apprendrez à utiliser l'opérateur LIKE dans PostgreSQL pour la correspondance de motifs (pattern matching). L'opérateur LIKE vous permet de rechercher des données qui correspondent à un motif spécifique, ce qui est particulièrement utile lorsque vous ne connaissez pas la valeur exacte que vous recherchez.
Nous continuerons à utiliser la table employees (employés) créée à l'étape précédente.
L'opérateur LIKE est utilisé dans une clause WHERE pour trouver les lignes qui correspondent à un motif spécifié. Le motif peut inclure des caractères génériques (wildcard characters) :
% : Représente zéro ou plusieurs caractères.
_ : Représente un seul caractère.
Par exemple, pour trouver tous les employés dont le nom commence par 'A', exécutez la requête suivante :
SELECT * FROM employees WHERE name LIKE 'A%';
Cette requête renverra toutes les lignes où la colonne name commence par 'A'. La sortie devrait être :
id | name | department | salary
----+---------------+------------+--------
1 | Alice Smith | Sales | 60000
(1 row)
Pour trouver tous les employés dont le nom contient la lettre 'o', exécutez la requête suivante :
SELECT * FROM employees WHERE name LIKE '%o%';
Cette requête renverra toutes les lignes où la colonne name contient la lettre 'o'. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
2 | Bob Johnson | Marketing | 75000
3 | Charlie Brown | Sales | 55000
5 | Eve Wilson | Marketing | 80000
(3 rows)
Pour trouver tous les employés dont le nom se termine par 'n', exécutez la requête suivante :
SELECT * FROM employees WHERE name LIKE '%n';
Cette requête renverra toutes les lignes où la colonne name se termine par 'n'. La sortie devrait être :
id | name | department | salary
----+---------------+------------+--------
2 | Bob Johnson | Marketing | 75000
3 | Charlie Brown | Sales | 55000
5 | Eve Wilson | Marketing | 80000
(3 rows)
Vous pouvez également utiliser le caractère générique _ pour correspondre à un seul caractère. Par exemple, pour trouver tous les employés dont le nom a 'e ' comme deuxième et troisième caractères, exécutez la requête suivante :
SELECT * FROM employees WHERE name LIKE '_e%';
Cette requête renverra toutes les lignes où la colonne name a 'e' comme deuxième caractère. La sortie devrait être :
id | name | department | salary
----+------+------------+--------
(0 rows)
L'opérateur LIKE est sensible à la casse (case-sensitive). Si vous souhaitez effectuer une recherche insensible à la casse (case-insensitive), vous pouvez utiliser l'opérateur ILIKE. Par exemple :
SELECT * FROM employees WHERE name ILIKE 'a%';
Cette requête renverra toutes les lignes où la colonne name commence par 'a' ou 'A'.
Trier les données avec ORDER BY
Dans cette étape, vous apprendrez à utiliser la clause ORDER BY dans PostgreSQL pour trier les données. La clause ORDER BY vous permet de trier l'ensemble de résultats d'une requête en fonction d'une ou plusieurs colonnes, soit par ordre croissant, soit par ordre décroissant.
Nous continuerons à utiliser la table employees (employés).
La clause ORDER BY est utilisée pour trier l'ensemble de résultats d'une requête. Par défaut, la clause ORDER BY trie les données par ordre croissant.
Par exemple, pour trier les employés par salaire par ordre croissant, exécutez la requête suivante :
SELECT * FROM employees ORDER BY salary;
Cette requête renverra toutes les lignes de la table employees, triées par la colonne salary par ordre croissant. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
3 | Charlie Brown | Sales | 55000
1 | Alice Smith | Sales | 60000
2 | Bob Johnson | Marketing | 75000
5 | Eve Wilson | Marketing | 80000
4 | David Lee | Engineering | 90000
6 | Frank Miller | Engineering | 95000
(6 rows)
Pour trier les données par ordre décroissant, vous pouvez utiliser le mot-clé DESC après le nom de la colonne. Par exemple, pour trier les employés par salaire par ordre décroissant, exécutez la requête suivante :
SELECT * FROM employees ORDER BY salary DESC;
Cette requête renverra toutes les lignes de la table employees, triées par la colonne salary par ordre décroissant. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
6 | Frank Miller | Engineering | 95000
4 | David Lee | Engineering | 90000
5 | Eve Wilson | Marketing | 80000
2 | Bob Johnson | Marketing | 75000
1 | Alice Smith | Sales | 60000
3 | Charlie Brown | Sales | 55000
(6 rows)
Vous pouvez également trier les données par plusieurs colonnes. Par exemple, pour trier les employés par département par ordre croissant, puis par salaire par ordre décroissant, exécutez la requête suivante :
SELECT * FROM employees ORDER BY department ASC, salary DESC;
Cette requête triera d'abord les données par la colonne department par ordre croissant. Au sein de chaque département, les données seront triées par la colonne salary par ordre décroissant. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
4 | David Lee | Engineering | 90000
6 | Frank Miller | Engineering | 95000
2 | Bob Johnson | Marketing | 75000
5 | Eve Wilson | Marketing | 80000
1 | Alice Smith | Sales | 60000
3 | Charlie Brown | Sales | 55000
(6 rows)
Limiter les résultats avec LIMIT et OFFSET
Dans cette étape, vous apprendrez à utiliser les clauses LIMIT et OFFSET dans PostgreSQL pour limiter le nombre de lignes renvoyées par une requête et pour ignorer un certain nombre de lignes, respectivement. Ces clauses sont utiles pour implémenter la pagination ou pour récupérer un sous-ensemble spécifique de données.
Nous continuerons à utiliser la table employees (employés).
La clause LIMIT est utilisée pour limiter le nombre de lignes renvoyées par une requête. Par exemple, pour récupérer uniquement les 3 premiers employés, exécutez la requête suivante :
SELECT * FROM employees LIMIT 3;
Cette requête renverra les 3 premières lignes de la table employees. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
1 | Alice Smith | Sales | 60000
2 | Bob Johnson | Marketing | 75000
3 | Charlie Brown | Sales | 55000
(3 rows)
La clause OFFSET est utilisée pour ignorer un certain nombre de lignes avant de commencer à renvoyer des lignes. Elle est souvent utilisée conjointement avec la clause LIMIT pour implémenter la pagination. Par exemple, pour récupérer les 3 employés suivants après avoir ignoré les 2 premiers, exécutez la requête suivante :
SELECT * FROM employees LIMIT 3 OFFSET 2;
Cette requête ignorera les 2 premières lignes, puis renverra les 3 lignes suivantes de la table employees. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
3 | Charlie Brown | Sales | 55000
4 | David Lee | Engineering | 90000
5 | Eve Wilson | Marketing | 80000
(3 rows)
Vous pouvez également combiner LIMIT et OFFSET avec la clause ORDER BY. Par exemple, pour récupérer les 2 employés les mieux payés, exécutez la requête suivante :
SELECT * FROM employees ORDER BY salary DESC LIMIT 2;
Cette requête triera d'abord les employés par salaire par ordre décroissant, puis renverra les 2 premières lignes. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
6 | Frank Miller | Engineering | 95000
4 | David Lee | Engineering | 90000
(2 rows)
Pour récupérer les 3ème et 4ème employés les mieux payés, exécutez la requête suivante :
SELECT * FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 2;
Cette requête triera d'abord les employés par salaire par ordre décroissant, ignorera les 2 premières lignes, puis renverra les 2 lignes suivantes. La sortie devrait être :
id | name | department | salary
----+-----------------+-------------+--------
5 | Eve Wilson | Marketing | 80000
2 | Bob Johnson | Marketing | 75000
(2 rows)
Résumé
Dans ce labo, vous avez appris à effectuer le filtrage de données et des requêtes simples dans PostgreSQL. Vous avez commencé par vous connecter à une base de données PostgreSQL à l'aide de la commande psql et avez créé une table employees (employés) échantillon.
Vous avez ensuite appris à utiliser la clause WHERE pour filtrer les données en fonction de conditions spécifiques, l'opérateur LIKE pour la correspondance de motifs (pattern matching), la clause ORDER BY pour trier les données, et les clauses LIMIT et OFFSET pour contrôler la taille de l'ensemble de résultats. Ce sont des compétences fondamentales pour travailler avec des bases de données relationnelles comme PostgreSQL.
