Types de données dans PostgreSQL
Types numériques
- INTEGER (nombre entier)
- SERIAL (auto-incrément)
- BIGINT (entier grande capacité)
- DECIMAL(p, s) (précision fixe)
- NUMERIC(p, s) (précision flottante)
Types textuels
- VARCHAR(n) (chaîne de caractères de longueur variable)
- TEXT (chaîne de longueur illimitée)
- CHAR(n) (chaîne de caractères fixe)
Types date/heure
- DATE (date sans heure)
- TIME (heure sans date)
- TIMESTAMP (date et heure)
- INTERVAL (durée)
Types booléens
- BOOLEAN (valeur TRUE ou FALSE)
Types géométriques
- POINT, LINE, POLYGON, CIRCLE, etc. (utilisés pour le stockage géospatial)
Types JSON
- JSON (stockage de données au format JSON)
- JSONB (JSON binaire, pour des performances optimisées)
Types réseau
- INET, CIDR (adresses IP et réseaux)
Types d'énumération
- ENUM (pour définir une liste de valeurs possibles)
Exemple de création
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC(10, 2),
birth_date DATE,
hire_date TIMESTAMP,
active BOOLEAN,
location POINT
);
content_copy
CRUD
Créer des données
INSERT INTO employees (name, salary, birth_date, hire_date, active, location)
VALUES ('John Doe', 50000.00, '1990-05-10', '2024-09-01 09:00:00', TRUE, POINT(40.7128, -74.0060));
content_copy
Lire des données
SELECT * FROM employees WHERE active = TRUE;
content_copy
Mettre à jour des données
UPDATE employees SET salary = 55000 WHERE id = 1;
content_copy
Supprimer des données
DELETE FROM employees WHERE id = 1;
content_copy
Ajouter une colonne
ALTER TABLE nom_bd
ADD COLUMN nom_colonne type_donnee;
content_copy
Supprimer une colonne
ALTER TABLE nom_de_la_table DROP COLUMN nom_de_la_colonne;
content_copy
Supprimer plusieurs colonnes
ALTER TABLE nom_de_la_table
DROP COLUMN nom_de_la_colonne_1,
DROP COLUMN nom_de_la_colonne_2,
... ;
content_copy
Créations & Insertions
Création d'une table
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
latitude DECIMAL(9, 6) NOT NULL,
longitude DECIMAL(9, 6) NOT NULL,
description TEXT
);
content_copy
Insertion de données
INSERT INTO locations (name, latitude, longitude, description) VALUES
('Paris', 48.8566, 2.3522, 'Capitale de la France'),
('New York', 40.7128, -74.0060, 'Ville de New York, USA'),
('Tokyo', 35.6895, 139.6917, 'Capitale du Japon'),
('Cairo', 30.0444, 31.2357, 'Capitale de l\'Égypte'),
('Sydney', -33.8688, 151.2093, 'Ville principale d\'Australie');
content_copy
Sélections Simples
Sélectionner tous les emplacements
SELECT * FROM locations;
content_copy
Count
Count simple :
SELECT COUNT(*) FROM users;
content_copy
Plusieurs Count :
Une approche
SELECT
(SELECT COUNT(*) FROM users) +
(SELECT COUNT(*) FROM orders) AS total_count;
content_copy
Trouver un emplacement par nom
SELECT * FROM locations WHERE name = 'Paris';
content_copy
Sélectionner uniquement les noms et coordonnées des emplacements
SELECT name, latitude, longitude FROM locations;
content_copy
Tri des résultats
SELECT name, salary FROM employees ORDER BY salary DESC;
content_copy
Agrégations
SELECT AVG(salary) FROM employees WHERE active = TRUE;
content_copy
Requêtes imbriqués
Utilisation de IN et WHERE
On possède deux tables : clients et orders.
Objectif : Récupérer toutes les commandes
effectuées par les clients dont le nom est "Google" ou "Microsoft".
Requête avec IN
SELECT *
FROM orders
WHERE client_id IN (
SELECT id
FROM client
WHERE name IN ('Microsoft', 'Google')
);
content_copy
Requête avec WHERE
SELECT *
FROM orders
WHERE client_id = (
SELECT id
FROM client
WHERE name = 'Google'
);
content_copy
Quand utiliser IN vs =
Utilisation de IN : lorsque la sous-requête retourne plusieurs résultats.
Utilisation de = lorsque la sous-requête retourne un
seul résultat ou lorsqu'on souhaite comparer avec une seule valeur.
Performances :
- Les sous-requêtes avec = sont généralement plus rapides
si elles retournent un seul résultat.
- Les sous-requêtes avec IN peuvent être plus coûteuses sur des grands
ensembles de données, mais elles sont nécessaires pour gérer plusieurs valeurs.
Exclusion de valeurs
Le but de cette requête c'est de voir comment exclure une valeur dans une requête :
Nous avons deux tables clients et orders
Objectif : Récupérer tous les types de commandes distincts,
sauf celles de type 'cancelled', pour un client donné (par exemple, "Mahmoud ILLOURMANE").
Avec jointures
SELECT DISTINCT o.type
FROM orders o
JOIN clients c ON c.id = o.client_id
WHERE c.name = 'Mahmoud ILLOURMANE'
AND o.type != 'cancelled';
content_copy
Exclure plusieurs types
SELECT DISTINCT o.type
FROM orders o
JOIN clients c ON c.id = o.client_id
WHERE c.name = 'Mahmoud ILLOURMANE'
AND o.type NOT IN ('cancelled', 'returned');
content_copy
Sans jointures
SELECT DISTINCT type
FROM orders
WHERE client_id IN (
SELECT id
FROM clients
WHERE name = 'Mahmoud ILLOURMANE'
)
AND type != 'cancelled';
content_copy
Exclure plusieurs types
SELECT DISTINCT type
FROM orders
WHERE client_id IN (
SELECT id
FROM clients
WHERE name = 'John Doe'
)
AND type NOT IN ('cancelled', 'returned');
content_copy
Requêtes intermédiaires
Trouver des emplacements dans l'hémisphère nord (latitude > 0)
SELECT name, latitude, longitude
FROM locations
WHERE latitude > 0;
content_copy
Trouver les emplacements proches d'une certaine latitude/longitude (ex. près de Paris)
SELECT name, latitude, longitude,
(3959 * acos(cos(radians(48.8566)) * cos(radians(latitude)) *
cos(radians(longitude) - radians(2.3522)) + sin(radians(48.8566)) *
sin(radians(latitude)))) AS distance
FROM locations
ORDER BY distance
LIMIT 5;
content_copy
Fenêtres et partitions
SELECT name, salary, RANK() OVER (ORDER BY salary DESC)
FROM employees;
content_copy
Requêtes récursives (exemple : gestion d'une hiérarchie)
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
content_copy
Utilisation de EXPLAIN pour analyser les performances
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 40000;
content_copy
Ajouts de colonnes
Installer l'extension PostGIS
CREATE EXTENSION postgis;
content_copy
Modification de la table locations pour ajouter un champ géométrique
ALTER TABLE locations ADD COLUMN geom GEOGRAPHY(POINT);
content_copy
Mise à jour d'une colonne
UPDATE locations
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
content_copy
Créer un index
CREATE INDEX idx_geom ON locations USING GIST (geom);
content_copy
Agrégations et Jointures
Table des visiteurs (relation entre des utilisateurs et des lieux)
CREATE TABLE visitors (
id SERIAL PRIMARY KEY,
location_id INT REFERENCES locations(id),
visitor_name VARCHAR(255),
visit_date DATE
);
content_copy
Compter le nombre de visites par lieu
SELECT l.name, COUNT(v.id) AS visit_count
FROM locations l
JOIN visitors v ON l.id = v.location_id
GROUP BY l.name
ORDER BY visit_count DESC;
content_copy
SUM : Somme des valeurs d'une colonne
SELECT SUM(salary) AS total_salaries FROM employees;
content_copy
AVG : Moyenne des valeurs
SELECT AVG(salary) AS average_salary FROM employees;
content_copy
MAX et MIN : Valeurs maximale et minimale
SELECT MAX(salary) AS highest_salary, MIN(salary) AS lowest_salary FROM employees;
content_copy
Agrégat sur plusieurs groupes : GROUP BY
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
content_copy
Compter les employés par département
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
content_copy
Somme des salaires par statut d'emploi
SELECT active, SUM(salary) AS total_salaries
FROM employees
GROUP BY active;
content_copy
Filtrer les départements avec une moyenne de salaire supérieure à 50,000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
content_copy
Calculer le total des salaires tout en affichant chaque employé
SELECT name, salary, SUM(salary) OVER () AS total_salaries
FROM employees;
content_copy
Classement des employés par salaire avec RANK()
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
content_copy
Compter les employés par département avec une sous-requête
SELECT d.name, (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS employee_count
FROM departments d;
content_copy
Utiliser un CTE pour obtenir la somme des salaires par département et lister les départements avec plus de 10 employés
WITH salary_totals AS (
SELECT department_id, SUM(salary) AS total_salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
)
SELECT department_id, total_salary
FROM salary_totals
WHERE employee_count > 10;
content_copy
Somme des salaires par mois
SELECT DATE_TRUNC('month', hire_date) AS month, SUM(salary) AS total_salaries
FROM employees
GROUP BY DATE_TRUNC('month', hire_date)
ORDER BY month;
content_copy
Trouver les visiteurs qui ont visité plus d'un lieu
SELECT visitor_name, COUNT(DISTINCT location_id) AS location_count
FROM visitors
GROUP BY visitor_name
HAVING COUNT(DISTINCT location_id) > 1;
content_copy
Requête pour les lieux avec au moins deux visiteurs et la distance au lieu le plus proche
WITH visit_counts AS (
SELECT location_id, COUNT(DISTINCT visitor_name) AS visitor_count
FROM visitors
GROUP BY location_id
),
closest_places AS (
SELECT l1.id AS loc1_id, l2.id AS loc2_id,
ST_Distance(l1.geom::geography, l2.geom::geography) AS distance
FROM locations l1, locations l2
WHERE l1.id != l2.id
ORDER BY distance
)
SELECT l.name, v.visitor_count, cp.distance
FROM visit_counts v
JOIN locations l ON l.id = v.location_id
JOIN closest_places cp ON cp.loc1_id = l.id
WHERE v.visitor_count >= 2
ORDER BY cp.distance;
content_copy
Les transactions
Démarrer une transaction
BEGIN;
content_copy
Exécuter des requêtes dans la transaction
UPDATE employees SET salary = salary + 1000 WHERE id = 1;
UPDATE employees SET salary = salary + 2000 WHERE id = 2;
content_copy
Valider la transaction
COMMIT;
content_copy
Annuler la transaction
ROLLBACK;
content_copy