SQL avancé

Slides
Author

Ludovic Deneuville

Données utilisées

CREATE TABLE echecs.joueuse (
    id_joueuse     SERIAL         PRIMARY KEY,   
    nom            VARCHAR(50)    NOT NULL, 
    prenom         VARCHAR(50)    NOT NULL, 
    date_naissance TIMESTAMP,                 
    elo            INT            CHECK (elo > 0),  
    mail           VARCHAR(100)   UNIQUE,         
    est_arbitre    BOOLEAN        DEFAULT false,
    code_pays      VARCHAR(2),          
    taux_victoires FLOAT          CHECK (taux_victoires BETWEEN 0 AND 1)
);

1 Sous-requête

Dans un filtre

Vous souhaitez lister les joueuses avec un elo supérieur à la moyenne.


SELECT nom, 
       prenom,
       elo
FROM echecs.joueuse
WHERE elo >= (SELECT AVG(elo) 
                FROM echecs.joueuse);

Vu dans les 1er TP :

  • SELECT AVG(elo) FROM echecs.joueuse renvoie un nombre
  • on peut le comparer à un autre nombre

Imbriquer les SELECT

SELECT j.nom, 
       j.prenom, 
       j.elo,
       mec.max_elo_club
  FROM (SELECT id_club,
               MAX(elo) AS max_elo_club
          FROM joueuse
         GROUP BY id_club) mec
  JOIN joueuse j USING(id_club);

Ceci est également une table et peut être requêtée par la suite

SELECT id_club,
       MAX(elo) AS max_elo_club
  FROM joueuse
 GROUP BY id_club

EXISTS

SELECT *
  FROM echecs.club c
 WHERE EXISTS (SELECT 1
                 FROM echecs.joueuse j
                WHERE c.id_club = j.id_club
                  AND j.elo > 2400);

La syntaxe ressemble à celle du IN mais le EXISTS est plus performant.

SELECT *
  FROM echecs.club c
 WHERE c.id_club IN (SELECT j.id_club
                       FROM echecs.joueuse j
                      WHERE j.elo > 2400);
  • EXISTS : dès qu’une correspondance est trouvée, c’est gagné
  • IN : génére l’intégralité de la sous-table

2 Les vues

Joueuses Françaises

NoteQuestion

Imaginons que nous avons de nombreuses requêtes à écrire sur les joueuses françaises ayant plus de 2000 elo.

Et nous n’avons pas envie de nous embêter à écrire à chaque fois toutes les restrictions.

Nouvelle table

Solution 1 : créons une nouvelle table

CREATE TABLE echecs.joueuse_fr_2000
AS
SELECT *
  FROM echecs.joueuse
 WHERE code_pays = 'FR'
   AND elo >= 2000
CautionProblèmes
  • Duplication des données
  • Mise à jour

Common Table Expression

Pour une utilisation ponctuelle, utilisez une CTE 😎

  • Commencez par définir votre sous-requête joueuses_fr_elo2000 avec WITH
  • Appelez joueuses_fr_elo2000 dans la requête principale
WITH joueuses_fr_elo2000 
AS (SELECT *
      FROM echecs.joueuse
     WHERE code_pays = 'FR' 
       AND elo > 2000
)
SELECT nom, prenom, elo
  FROM joueuses_fr_elo2000
 WHERE prenom LIKE 'A%';
  • Usage avancé
  • Assez classe
  • Comme une sous-requête
  • Ou comme si vous avez créé une vue temporaire

Table temporaire

N’existe que le temps de la session.


CREATE TEMPORARY TABLE joueuses_fr_elo2000 
AS
SELECT *
  FROM echecs.joueuse
 WHERE code_pays = 'FR' 
   AND elo > 2000;
CautionRemarque

Une table temporaire n’est pas définie dans un schéma.

Vue

NoteDéfinition

Une vue représente une table virtuelle, dont la structure et les données proviennent du résultat d’une requête SQL prédéfinie.

Elle ne stocke pas directement les données, mais la requête elle-même, et s’exécute à chaque utilisation.


CREATE VIEW echecs.joueuses_fr_elo2000 
AS
SELECT *
  FROM echecs.joueuse
 WHERE code_pays = 'FR' 
   AND elo > 2000;
  • Se requête comme si c’est une table
  • SELECT only
  • PAS de UPDATE, DELETE, INSERT

Avantages et inconvénients

  • ✔️ Simplicité d’accès aux données
  • ✔️ Meilleure organisation des données
  • ✔️ Contrôle des accès aux données
  • ❌ Performance
  • Interroger sans se soucier des structures complexes des tables
  • Sauvegarde de requêtes complexes
  • Structurer logiquement les données
  • Masquer des colonnes / lignes sensibles
  • ❌ s’exécutent dynamiquement à chaque appel

Vues matérialisées

Photo à un instant t (snapshot) 📷


CREATE MATERIALIZED VIEW joueuses_fr_elo2000 
AS
SELECT *
  FROM echecs.joueuse
 WHERE code_pays = 'FR' 
   AND elo > 2000;

3 Gérer les droits

L’utilisateur qui crée une table peut distribuer des droits aux autres.

Voir section priviléges de la doc PostgreSQL

Donner des droits sur une table

-- Lecture pour tous
GRANT SELECT ON echecs.joueuse TO PUBLIC;

-- Tous les droits
GRANT ALL PRIVILEGES ON echecs.joueuse TO chantal WITH GRANT OPTION;


  • PUBLIC : tous les utilisateurs
  • Suffixe WITH GRANT OPTION pour donner le droit de transmettre ce droit

Remarque :

  • Si vous donnez les droits WITH GRANT OPTION à quelqu’un
  • Cette personne peut ensuite vous retirer les droits !

Retirer des droits

-- Lecture
REVOKE SELECT ON echecs.joueuse TO chantal;

-- Tous les droits
REVOKE ALL PRIVILEGES ON echecs.joueuse TO chantal;


Suffixe CASCADE pour retirer les droits à ceux qui ont reçu les droits de la part de…

Rôle

Pour une gestion simplifiée des droits

-- créer un rôle
CREATE ROLE eleve;

-- ajouter des droits au rôle
GRANT SELECT, INSERT ON echecs.joueuse TO eleve;

-- donner le rôle à Chantal
GRANT eleve TO chantal;

Imaginons que pour chaque nouvel élève, il faut donner les droits sur 20 tables.

Plus pratique avec les rôles.

Droits sur un schéma

-- droit d'accès au schéma i.e. de consulter les objets du schéma
GRANT USAGE ON SCHEMA echecs TO PUBLIC;

-- retire à Chantal le droit de créer de nouveaux objets (tables, vues, etc.) dans le schéma
REVOKE CREATE ON SCHEMA echecs FROM chantal;

-- Autorise à effectuer des requêtes SELECT sur toutes les tables du schéma
GRANT SELECT ON ALL TABLES IN SCHEMA echecs TO PUBLIC;

pas de question à l’exam sur les droits de schéma

4 Transactions

Exemple classique

ImportantMission

Vous devez transférer discrétement et sans vous tromper 10 millions entre les comptes de Shakira et Cristiano.

Malheureusement l’opération est assez longue et vous devez vous prémunir contre le risque d’une interruption.

Comment faire ? 😕

Transfert de fonds

BEGIN; -- Début de la transaction

UPDATE compte SET solde = solde - 10000000 WHERE id = 1;
UPDATE compte SET solde = solde + 10000000 WHERE id = 2;

COMMIT; -- Valider si ok


ROLLBACK; -- en cas d'erreur
  • Plusieurs utilisateurs peuvent faire des requêtes en même temps
  • LOCK
  • SAVEPOINT my_savepoint;
  • ROLLBACK TO my_savepoint;
  • mode AUTOCOMMIT

Propriétés ACID

Pour définir la fiabilité des transactions :

  • Atomicité : Une transaction est “tout ou rien”
  • Cohérence : La base reste dans un état valide avant et après la transaction
  • Isolation : Les transactions concurrentes n’interfèrent pas entre elles
  • Durabilité : Une transaction validée est enregistrée de manière permanente, même en cas de panne
  • Soit toutes les opérations réussissent, soit elles sont annulées (rollback)

5 Index

Optimisation

Possible d’optimiser à plusieurs niveaux :

  • Logique : Normalisation / Dénormalisation
  • Physique : Indexation, Partitionnement
  • Requêtes SQL
  • Normaliser pour éviter la redondance et les anomalies
  • Dé-normaliser quand les lectures sont plus fréquentes que les écritures
  • Bon typage
  • Indexation, Partitionnement
  • Éviter SELECT * → ne charger que les colonnes utiles.
  • Vérifier le plan d’exécution :

EXPLAIN SELECT * FROM joueuse WHERE prenom = ‘Amandine’;

Créer un index

NoteDéfinition

Un index est une structure de données qui améliore la vitesse des opérations de lecture sur une table en facilitant la recherche des données.

Il permet une lecture plus rapide sur une colonne utilisée fréquemment dans WHERE, JOIN, GROUP BY, ORDER BY

CREATE INDEX echecs.idx_prenom_joueuse ON echecs.joueuse (prenom);
  • Recherche longue si beaucoup de lignes
  • Système clé-valeur (index -> ligne)

Application

SELECT *
  FROM echecs.joueuse
 WHERE prenom = 'Amandine';
  • Sans index : parcourt intégral de la table
  • Avec index :
    • recherche sous forme d’arbre, récupère les id
    • lit directement les lignes concernées
  • index stocké sous forme d’un arbre B+ trié sur prenom
  • recherche logarithmique : très rapide O(log n)
  • index contient des pointeurs vers les lignes correspondantes dans la table

Avantages et inconvénients

  • ✔️ Haute cardinalité
  • ✔️ Peu de valeurs nulles
  • ❌ Créations, mises à jour
  • ❌ Prend de la place
  • si Haute cardinalité : beaucoup de valeurs distinctes
  • Si une colonne est souvent modifiée, l’index associé doit être mis à jour
  • Index sous jacent sur colonnes UNIQUE (PK incluse)

6 Fonction

  • Bloc de code nommé qui retourne une valeur
  • Encapsuler une logique métier simple.
SELECT nom, 
       get_categorie(date_naissance)
  FROM echecs.joueuse;

Exemple

CREATE OR REPLACE FUNCTION echecs.get_categorie(p_date_naissance DATE)
RETURNS VARCHAR(20) AS $$
DECLARE
    age INT;
BEGIN
    age := DATE_PART('year', AGE(CURRENT_DATE, p_date_naissance));

    IF age < 20 THEN
        RETURN 'Junior';
    ELSIF age < 60 THEN
        RETURN 'Senior';
    ELSE
        RETURN 'Vétéran';
    END IF;
END;
$$ LANGUAGE plpgsql;

7 Procédure

  • Bloc de code SQL exécuté manuellement avec CALL
  • Peut modifier la base de données
  • Ne retourne pas de valeur
  • Automatiser une série d’actions
CALL augmenter_elo(3, 50);

8 Trigger

  • Se déclenchent quand un événement survient sur une table
  • Servent à maintenir la cohérence ou enregistrer des historiques

Exemples :

  • Tenir un journal d’audit, historiser les changements
  • Appliquer des règles automatiques

Peut se déclencher avant ou après une maj

  • Garantir l’intégrité des données : Valider ou corriger les données avant leur enregistrement.
  • Implémenter des règles métier : Empêcher des suppressions ou modifications non autorisées.
  • Synchroniser des données : Mettre à jour des tables liées automatiquement.