Maîtriser PostgreSQL
Thème :
Intermédiaire
1 vues ·8/9 leçons

EXPLICATION DÉTAILLÉE SCRIPT SQL POSTGESQL

SECTION 1 : CRÉATION DE LA BASE DE DONNÉES

CREATE DATABASE shop_db
WITH ENCODING 'UTF8'
LC_COLLATE='fr_FR.UTF-8'
LC_CTYPE='fr_FR.UTF-8';
Ligne Explication Pourquoi
CREATE DATABASE shop_db Crée une nouvelle base de données nommée shop_db Conteneur pour stocker toutes les tables et données de la boutique
WITH ENCODING 'UTF8' Définit l’encodage en UTF-8 (supporte tous les caractères : accents, emojis, etc.) Permet de stocker du texte en français avec accents (é, è, ê, etc.) sans problèmes
LC_COLLATE='fr_FR.UTF-8' Définit l’ordre de tri selon les règles françaises Quand on trie alphabétiquement, les accents sont gérés correctement (é = e)
LC_CTYPE='fr_FR.UTF-8' Définit la classification des caractères en français Les fonctions comme UPPER() respectent les règles françaises 
\c shop_db
Ligne Explication Pourquoi
\c shop_db Commande psql : se connecter à la base shop_db Nécessaire pour exécuter les commandes suivantes dans cette base 
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Ligne Explication Pourquoi
CREATE EXTENSION Ajoute une fonctionnalité supplémentaire à PostgreSQL Étend les capacités de la base
IF NOT EXISTS Crée seulement si l’extension n’existe pas déjà Évite les erreurs si on exécute le script plusieurs fois
"uuid-ossp" Extension pour générer des UUID (identifiants uniques universels) Permet de créer des IDs uniques sans risque de collision (optionnel ici)

SECTION 2 : TABLE CATEGORIES

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
Ligne Explication Pourquoi
CREATE TABLE categories Crée une table nommée categories Stocke les catégories de produits (Électronique, Livres, etc.)
id SERIAL PRIMARY KEY Crée une colonne id auto-incrémentée et clé primaire Identifiant unique pour chaque catégorie ; auto-incrémentation = +1 automatiquement 
    name VARCHAR(100) NOT NULL UNIQUE,
Ligne Explication Pourquoi
name VARCHAR(100) Colonne texte de max 100 caractères Stocke le nom de la catégorie (ex: “Électronique”)
NOT NULL La colonne ne peut pas être vide Garantit que chaque catégorie a un nom
UNIQUE Deux catégories ne peuvent pas avoir le même nom Évite les doublons (pas deux “Électronique”) 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
Ligne Explication Pourquoi
TIMESTAMP WITH TIME ZONE Stocke date + heure + fuseau horaire Traçabilité complète ; utile pour les sites internationaux
DEFAULT CURRENT_TIMESTAMP Remplit automatiquement avec la date/heure actuelle Pas besoin de le spécifier manuellement à chaque insertion
created_at Enregistre quand la catégorie a été créée Audit : savoir quand les données ont été ajoutées
updated_at Enregistre quand la catégorie a été modifiée Audit : savoir quand les données ont changé 
    CONSTRAINT chk_name_not_empty CHECK (TRIM(name) <> '')
);
Ligne Explication Pourquoi
CONSTRAINT chk_name_not_empty Nomme la contrainte pour faciliter le débogage Identification claire en cas d’erreur
CHECK (TRIM(name) <> '') Vérifie que le nom n’est pas vide (même après suppression des espaces) Empêche les noms comme " " (que des espaces)

SECTION 3 : TABLE PRODUCTS

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
Ligne Explication Pourquoi
id SERIAL PRIMARY KEY Identifiant auto-incrémenté unique Chaque produit a un ID unique
name VARCHAR(200) NOT NULL Nom du produit (max 200 caractères, obligatoire) Chaque produit doit avoir un nom
description TEXT Description longue (pas de limite de caractères) Permet des descriptions détaillées sans limite 
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
Ligne Explication Pourquoi
price NUMERIC(10, 2) Nombre décimal avec 10 chiffres au total, 2 après la virgule Stocke les prix avec précision (ex: 1299.99)
NOT NULL Le prix est obligatoire Pas de produit sans prix
CHECK (price >= 0) Le prix ne peut pas être négatif Validation métier : un prix doit être positif 
    quantity INT NOT NULL DEFAULT 0 CHECK (quantity >= 0),
Ligne Explication Pourquoi
quantity INT Nombre entier (quantité en stock) Représente le nombre de produits disponibles
DEFAULT 0 Si non spécifié, commence à 0 Produit nouveau = pas encore en stock
CHECK (quantity >= 0) La quantité ne peut pas être négative Validation : on ne peut pas avoir -5 produits 
    category_id INT NOT NULL,
Ligne Explication Pourquoi
category_id INT NOT NULL Référence l’ID de la catégorie Lie chaque produit à une catégorie 
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
Ligne Explication Pourquoi
(Même que pour categories) Traçabilité des modifications Audit complet 
    CONSTRAINT fk_product_category
        FOREIGN KEY (category_id)
        REFERENCES categories(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
Ligne Explication Pourquoi
CONSTRAINT fk_product_category Nomme la clé étrangère Identification claire
FOREIGN KEY (category_id) Déclare une clé étrangère Crée une relation entre products et categories
REFERENCES categories(id) Pointe vers la colonne id de categories Chaque category_id doit exister dans categories
ON DELETE RESTRICT Empêche de supprimer une catégorie si elle a des produits Protection : pas de catégories orphelines
ON UPDATE CASCADE Si on change l’ID d’une catégorie, met à jour automatiquement les produits Cohérence des données 
    CONSTRAINT chk_name_not_empty CHECK (TRIM(name) <> '')
);
Ligne Explication Pourquoi
(Même logique que pour categories) Empêche les noms vides Validation de données

SECTION 4 : INDEX (OPTIMISATION)

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_name ON products(name);
CREATE INDEX idx_products_price ON products(price);
Ligne Explication Pourquoi
CREATE INDEX idx_products_category Crée un index sur category_id Accélère les recherches par catégorie (ex: “tous les produits de Électronique”)
ON products(category_id) L’index porte sur la colonne category_id Les recherches par catégorie seront 100x plus rapides
idx_products_name Index sur le nom Accélère les recherches par nom (ex: rechercher “Laptop”)
idx_products_price Index sur le prix Accélère les filtres par prix (ex: “produits < 50€”)

Pourquoi les index :

  • Sans index : PostgreSQL doit scanner toute la table (lent)
  • Avec index : PostgreSQL utilise une structure optimisée (rapide)

SECTION 5 : TRIGGER (MISE À JOUR AUTOMATIQUE)

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Ligne Explication Pourquoi
CREATE OR REPLACE FUNCTION Crée une fonction (ou remplace si elle existe) Fonction réutilisable
update_updated_at_column() Nom de la fonction Mise à jour automatique du timestamp
RETURNS TRIGGER Cette fonction est un trigger (déclencheur) S’exécute automatiquement lors d’événements
NEW.updated_at = CURRENT_TIMESTAMP Remplace la valeur de updated_at par l’heure actuelle Chaque modification met à jour automatiquement le timestamp
RETURN NEW Retourne la ligne modifiée Valide la modification
LANGUAGE plpgsql Langage PL/pgSQL (langage procédural de PostgreSQL) Permet de créer des fonctions complexes 
CREATE TRIGGER update_categories_updated_at
BEFORE UPDATE ON categories
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
Ligne Explication Pourquoi
CREATE TRIGGER update_categories_updated_at Crée un trigger nommé Déclenche une action automatique
BEFORE UPDATE ON categories S’exécute AVANT chaque modification de categories Capture l’ancienne valeur avant la modification
FOR EACH ROW S’exécute pour chaque ligne modifiée Si on modifie 10 lignes, le trigger s’exécute 10 fois
EXECUTE FUNCTION update_updated_at_column() Appelle la fonction créée plus haut Lance la mise à jour du timestamp 
CREATE TRIGGER update_products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
Ligne Explication Pourquoi
(Même logique) Même trigger pour la table products Chaque modification de produit met à jour updated_at automatiquement

SECTION 6 : VUE (STATISTIQUES)

CREATE VIEW product_stats AS
SELECT
    c.id AS category_id,
    c.name AS category_name,
    COUNT(p.id) AS product_count,
    COALESCE(SUM(p.quantity), 0) AS total_quantity,
    COALESCE(AVG(p.price), 0) AS avg_price,
    COALESCE(MIN(p.price), 0) AS min_price,
    COALESCE(MAX(p.price), 0) AS max_price
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.id, c.name;
Ligne Explication Pourquoi
CREATE VIEW product_stats AS Crée une vue (requête sauvegardée) Permet de réutiliser cette requête complexe
SELECT c.id, c.name Récupère l’ID et le nom de chaque catégorie Identifie la catégorie
COUNT(p.id) AS product_count Compte le nombre de produits par catégorie Combien de produits dans “Électronique” ?
COALESCE(SUM(p.quantity), 0) Somme les quantités ; si NULL, retourne 0 Quantité totale en stock par catégorie
COALESCE(AVG(p.price), 0) Prix moyen ; si NULL, retourne 0 Prix moyen des produits
MIN(p.price), MAX(p.price) Prix minimum et maximum Gamme de prix par catégorie
FROM categories c LEFT JOIN products p Récupère toutes les catégories, même sans produits Les catégories vides apparaissent aussi
ON c.id = p.category_id Lie les catégories aux produits Associe chaque produit à sa catégorie
GROUP BY c.id, c.name Regroupe les résultats par catégorie Un résultat par catégorie

SECTION 7 : DONNÉES DE TEST

INSERT INTO categories (name) VALUES
('Électronique'),
('Livres'),
('Vêtements'),
('Alimentation');
Ligne Explication Pourquoi
INSERT INTO categories (name) Ajoute des données dans la table categories Remplit la base avec des catégories
VALUES (...) Spécifie les valeurs à insérer 4 catégories différentes 
INSERT INTO products (name, description, price, quantity, category_id) VALUES
('Laptop Dell XPS 13', 'Ordinateur portable haute performance', 1299.99, 15, 1),
('iPhone 15 Pro', 'Smartphone dernière génération', 1199.00, 25, 1),
('Le Petit Prince', 'Roman classique de Saint-Exupéry', 12.50, 100, 2),
('T-Shirt Coton Bio', 'T-shirt écologique et confortable', 29.99, 50, 3),
('Café Arabica 1kg', 'Café en grains origine Colombie', 24.90, 200, 4);
Ligne Explication Pourquoi
INSERT INTO products (...) Ajoute 5 produits Données de test pour vérifier que tout fonctionne
category_id Lie chaque produit à une catégorie (1=Électronique, 2=Livres, etc.) Démontre les relations entre tables

RÉSUMÉ : POURQUOI CETTE STRUCTURE ?

✅ Intégrité des données : Contraintes CHECK, NOT NULL, UNIQUE, FOREIGN KEY ✅ Performance : Index sur les colonnes fréquemment recherchées ✅ Traçabilité : Timestamps created_at et updated_at ✅ Automatisation : Triggers pour mettre à jour updated_at automatiquement ✅ Facilité d’utilisation : Vue pour obtenir rapidement les statistiques ✅ Internationalisation : Encodage UTF-8 et collation française

Leçon précédente

CRUD avec PDO PostgreSQL

Prochaine leçon

Instruc

Prêt à passer à la suite ?

Vous avez terminé cette leçon sur EXPLICATION DÉTAILLÉE SCRIPT SQL POSTGESQL. Continuez votre apprentissage avec la prochaine leçon ou explorez d'autres formations.

Continuer avec Instruc Explorer toutes les formations