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

Question

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
Problè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;
Remarque

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

Vue

Dé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

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

Mission

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

Créer un index

Dé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_nom_joueuse ON echecs.joueuse (nom);
  • Recherche longue si beaucoup de lignes
  • Système clé-valeur (index -> ligne)

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 Trigger

Définition

Un trigger est une fonction qui s’exécute automatiquement en réponse à une mise à jour d’une table

Il permet par exemple de :

  • Garantir l’intégrité des données
  • Implémenter des règles métier
  • Synchroniser des données

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.