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/db_access("Connect To Database")
postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table")
postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE")
postgresql/PostgreSQLGroup -.-> postgresql/data_sort("Sort Query Results")
subgraph Lab Skills
postgresql/db_access -.-> lab-550898{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
postgresql/table_init -.-> lab-550898{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
postgresql/data_where -.-> lab-550898{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
postgresql/data_sort -.-> lab-550898{{"Filtrage de données et requêtes simples dans PostgreSQL"}}
end
Filtrer les données avec la clause WHERE
Dans cette étape, nous allons apprendre à 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.
Avant de commencer, connectons-nous à la base de données PostgreSQL. Ouvrez un terminal dans votre VM LabEx. Vous pouvez utiliser le terminal Xfce par défaut.
Tout d'abord, connectez-vous à la base de données PostgreSQL en utilisant la commande psql. Nous allons nous connecter à la base de données postgres avec l'utilisateur postgres. Vous devrez peut-être utiliser sudo pour exécuter la commande.
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.
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 à ceci :
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). Nous pouvons utiliser la requête suivante :
SELECT * FROM employees WHERE department = 'Sales';
Cette requête ne renverra que 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 dont le salaire est supérieur à 70000, vous pouvez utiliser 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 à l'aide d'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 dont le salaire est supérieur à 58000, vous pouvez utiliser 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)
Enfin, quittez l'interpréteur psql :
\q
Cela vous ramènera au terminal de l'utilisateur labex.
Utiliser LIKE pour la correspondance de motifs (Pattern Matching)
Dans cette étape, nous allons apprendre à 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.
Tout d'abord, connectons-nous à la base de données PostgreSQL. Ouvrez un terminal dans votre VM LabEx.
Connectez-vous à la base de données PostgreSQL en utilisant la commande psql :
Nous allons continuer à utiliser la table employees (employés) créée à l'étape précédente. Si vous ne l'avez pas créée, veuillez vous référer à l'étape précédente pour créer la table et insérer des données.
Passons en revue les données de la table employees :
SELECT * FROM employees;
Vous devriez voir les données des employés comme auparavant.
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', vous pouvez utiliser 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', vous pouvez utiliser 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', vous pouvez utiliser 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, vous pouvez utiliser 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, 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'.
Enfin, quittez l'interpréteur psql :
\q
Cela vous ramènera au terminal de l'utilisateur labex.
Trier les données avec ORDER BY
Dans cette étape, nous allons apprendre à 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 (result set) d'une requête en fonction d'une ou plusieurs colonnes, soit par ordre croissant, soit par ordre décroissant.
Tout d'abord, connectons-nous à la base de données PostgreSQL. Ouvrez un terminal dans votre VM LabEx.
Connectez-vous à la base de données PostgreSQL en utilisant la commande psql :
Nous allons continuer à utiliser la table employees (employés) créée lors des étapes précédentes. Si vous ne l'avez pas créée, veuillez vous référer aux étapes précédentes pour créer la table et insérer des données.
Passons en revue les données de la table employees :
SELECT * FROM employees;
Vous devriez voir les données des employés comme auparavant.
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, vous pouvez utiliser 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, vous pouvez utiliser 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, vous pouvez utiliser 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)
Enfin, quittez l'interpréteur psql :
\q
Cela vous ramènera au terminal de l'utilisateur labex.
Limiter les résultats avec LIMIT et OFFSET
Dans cette étape, nous allons apprendre à 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.
Tout d'abord, connectons-nous à la base de données PostgreSQL. Ouvrez un terminal dans votre VM LabEx.
Connectez-vous à la base de données PostgreSQL en utilisant la commande psql :
Nous allons continuer à utiliser la table employees (employés) créée lors des étapes précédentes. Si vous ne l'avez pas créée, veuillez vous référer aux étapes précédentes pour créer la table et insérer des données.
Passons en revue les données de la table employees :
SELECT * FROM employees;
Vous devriez voir les données des employés comme auparavant.
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, vous pouvez utiliser 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, vous pouvez utiliser 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, vous pouvez utiliser 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, vous pouvez utiliser 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)
Enfin, quittez l'interpréteur psql :
\q
Cela vous ramènera au terminal de l'utilisateur labex.
Résumé
Dans ce labo, nous avons commencé par nous connecter à une base de données PostgreSQL en utilisant la commande psql et avons créé une table employees (employés) avec des colonnes pour l'ID, le nom, le département et le salaire. Nous avons ensuite rempli la table avec des données d'employés et vérifié son contenu à l'aide d'une requête SELECT.
L'objectif initial était d'utiliser la clause WHERE pour filtrer les données en fonction de conditions spécifiques. Cela permet de récupérer uniquement les lignes qui répondent aux critères définis, ce qui permet une récupération ciblée des données de la table employees.
