Aller au contenu

Cours : Le langage SQL

1. Introduction

1.1. Historique

SQL (Structured Query Language) est un langage de requêtes créé en 1974 par IBM, puis normalisé en 1986. Il permet d'interagir avec les bases de données relationnelles.

SQL est aujourd'hui le standard universel pour : - Créer et modifier la structure des bases de données - Insérer, modifier et supprimer des données - Interroger les données (requêtes)

1.2. Les SGBD

Un SGBD (Système de Gestion de Base de Données) est un logiciel qui permet de stocker, organiser et manipuler des données. Exemples :

SGBD Type Utilisation
SQLite Léger, fichier local Applications mobiles, embarqué
MySQL / MariaDB Serveur Sites web, applications
PostgreSQL Serveur avancé Applications complexes
Oracle Entreprise Grandes entreprises
Microsoft SQL Server Entreprise Environnement Windows

En NSI, nous utilisons principalement SQLite avec l'outil DB Browser for SQLite.

2. Structure d'une base de données

2.1. Tables, attributs et enregistrements

Une base de données relationnelle est composée de tables. Chaque table contient : - Des colonnes (ou attributs) : les caractéristiques des données - Des lignes (ou enregistrements) : les données elles-mêmes

Exemple : Table LIVRES

id titre auteur ann_publi note
1 1984 Orwell 1949 10
2 Dune Herbert 1965 8
3 Fondation Asimov 1951 9

2.2. Types de données

Type SQL Description Exemple
INT ou INTEGER Nombre entier 42, -7, 2024
REAL ou FLOAT Nombre décimal 3.14, -0.5
TEXT ou VARCHAR Chaîne de caractères 'Bonjour', 'Alice'
DATE Date '2024-01-15'
BOOLEAN Booléen TRUE, FALSE

2.3. Clés primaires et étrangères

  • Clé primaire (PRIMARY KEY) : Identifiant unique de chaque enregistrement
  • Clé étrangère (FOREIGN KEY) : Référence vers la clé primaire d'une autre table
Table AUTEURS                    Table LIVRES
+----+--------+                  +----+--------+-----------+
| id | nom    |                  | id | titre  | id_auteur |
+----+--------+                  +----+--------+-----------+
| 1  | Orwell |<─────────────────| 1  | 1984   | 1         |
| 2  | Asimov |<─────────────────| 2  | Robots | 2         |
+----+--------+                  +----+--------+-----------+
     PK                               PK        FK

3. Création et modification de tables

3.1. Créer une table : CREATE TABLE

CREATE TABLE LIVRES (
    id INTEGER PRIMARY KEY,
    titre TEXT,
    auteur TEXT,
    ann_publi INTEGER,
    note INTEGER
);

3.2. Créer une table avec clé étrangère

CREATE TABLE LIVRES (
    id INTEGER PRIMARY KEY,
    titre TEXT,
    id_auteur INTEGER,
    ann_publi INTEGER,
    FOREIGN KEY (id_auteur) REFERENCES AUTEURS(id)
);

3.3. Supprimer une table : DROP TABLE

DROP TABLE LIVRES;

⚠️ Attention : Cette opération est irréversible !

3.4. Modifier une table : ALTER TABLE

-- Ajouter une colonne
ALTER TABLE LIVRES ADD COLUMN editeur TEXT;

-- Supprimer une colonne (selon le SGBD)
ALTER TABLE LIVRES DROP COLUMN editeur;

4. Manipulation des données

4.1. Insérer des données : INSERT INTO

-- Insérer un enregistrement
INSERT INTO LIVRES (id, titre, auteur, ann_publi, note)
VALUES (1, '1984', 'Orwell', 1949, 10);

-- Insérer plusieurs enregistrements
INSERT INTO LIVRES (id, titre, auteur, ann_publi, note)
VALUES
    (2, 'Dune', 'Herbert', 1965, 8),
    (3, 'Fondation', 'Asimov', 1951, 9);

4.2. Modifier des données : UPDATE

-- Modifier tous les enregistrements correspondant à la condition
UPDATE LIVRES
SET note = 10
WHERE auteur = 'Asimov';

-- Modifier plusieurs colonnes
UPDATE LIVRES
SET note = 9, ann_publi = 1950
WHERE id = 3;

⚠️ Attention : Sans clause WHERE, tous les enregistrements seront modifiés !

4.3. Supprimer des données : DELETE

-- Supprimer les enregistrements correspondant à la condition
DELETE FROM LIVRES
WHERE ann_publi < 1950;

-- Supprimer tous les enregistrements (vider la table)
DELETE FROM LIVRES;

⚠️ Attention : Sans clause WHERE, tous les enregistrements seront supprimés !

5. Requêtes de sélection : SELECT

5.1. Syntaxe de base

SELECT colonnes
FROM table
WHERE conditions
ORDER BY colonne [ASC|DESC];

5.2. Sélectionner des colonnes

-- Toutes les colonnes
SELECT * FROM LIVRES;

-- Colonnes spécifiques
SELECT titre, auteur FROM LIVRES;

-- Avec alias
SELECT titre AS "Titre du livre", auteur AS "Écrivain"
FROM LIVRES;

5.3. Filtrer avec WHERE

Opérateurs de comparaison :

Opérateur Signification
= Égal à
<> ou != Différent de
<, <= Inférieur (ou égal)
>, >= Supérieur (ou égal)
BETWEEN a AND b Entre a et b (inclus)
IN (v1, v2, ...) Dans la liste
LIKE Correspondance de motif
IS NULL Est nul

Exemples :

-- Égalité
SELECT * FROM LIVRES WHERE auteur = 'Asimov';

-- Comparaison
SELECT * FROM LIVRES WHERE ann_publi > 1960;

-- Entre deux valeurs
SELECT * FROM LIVRES WHERE ann_publi BETWEEN 1950 AND 1970;

-- Dans une liste
SELECT * FROM LIVRES WHERE auteur IN ('Asimov', 'Bradbury', 'K.Dick');

-- Correspondance de motif (LIKE)
SELECT * FROM LIVRES WHERE titre LIKE '%Robot%';
-- % = n'importe quelle suite de caractères
-- _ = un seul caractère

-- Valeur nulle
SELECT * FROM LIVRES WHERE note IS NULL;

5.4. Combiner les conditions : AND, OR, NOT

-- ET logique
SELECT * FROM LIVRES
WHERE auteur = 'Asimov' AND ann_publi > 1950;

-- OU logique
SELECT * FROM LIVRES
WHERE auteur = 'Asimov' OR auteur = 'Bradbury';

-- Négation
SELECT * FROM LIVRES
WHERE NOT auteur = 'Asimov';

-- Combinaison avec parenthèses
SELECT * FROM LIVRES
WHERE (auteur = 'Asimov' OR auteur = 'Bradbury') AND note >= 8;

5.5. Trier les résultats : ORDER BY

-- Tri croissant (par défaut)
SELECT * FROM LIVRES ORDER BY ann_publi;
SELECT * FROM LIVRES ORDER BY ann_publi ASC;

-- Tri décroissant
SELECT * FROM LIVRES ORDER BY note DESC;

-- Tri multiple
SELECT * FROM LIVRES ORDER BY auteur ASC, ann_publi DESC;

5.6. Éliminer les doublons : DISTINCT

-- Liste des auteurs (sans doublons)
SELECT DISTINCT auteur FROM LIVRES;

5.7. Limiter les résultats : LIMIT

-- Les 5 premiers résultats
SELECT * FROM LIVRES LIMIT 5;

-- Pagination : 5 résultats à partir du 10e
SELECT * FROM LIVRES LIMIT 5 OFFSET 10;

6. Fonctions d'agrégation

Les fonctions d'agrégation effectuent des calculs sur un ensemble de valeurs.

6.1. Les fonctions principales

Fonction Description
COUNT(*) Nombre d'enregistrements
COUNT(colonne) Nombre de valeurs non nulles
SUM(colonne) Somme des valeurs
AVG(colonne) Moyenne des valeurs
MIN(colonne) Valeur minimale
MAX(colonne) Valeur maximale

6.2. Exemples

-- Nombre total de livres
SELECT COUNT(*) FROM LIVRES;

-- Nombre de livres par auteur
SELECT COUNT(*) FROM LIVRES WHERE auteur = 'Asimov';

-- Note moyenne
SELECT AVG(note) FROM LIVRES;

-- Note minimale et maximale
SELECT MIN(note), MAX(note) FROM LIVRES;

-- Somme des notes (peu utile ici, mais illustratif)
SELECT SUM(note) FROM LIVRES;

-- Année du livre le plus ancien
SELECT MIN(ann_publi) FROM LIVRES;

6.3. Regrouper les données : GROUP BY

GROUP BY permet de regrouper les enregistrements par valeur d'une colonne.

-- Nombre de livres par auteur
SELECT auteur, COUNT(*) AS nb_livres
FROM LIVRES
GROUP BY auteur;

-- Note moyenne par auteur
SELECT auteur, AVG(note) AS note_moyenne
FROM LIVRES
GROUP BY auteur;

-- Résultat :
-- +----------+--------------+
-- | auteur   | note_moyenne |
-- +----------+--------------+
-- | Asimov   | 8.67         |
-- | Bradbury | 7.50         |
-- | K.Dick   | 8.33         |
-- +----------+--------------+

6.4. Filtrer les groupes : HAVING

HAVING filtre les résultats après le regroupement (contrairement à WHERE qui filtre avant).

-- Auteurs ayant écrit plus de 2 livres
SELECT auteur, COUNT(*) AS nb_livres
FROM LIVRES
GROUP BY auteur
HAVING COUNT(*) > 2;

-- Auteurs avec une note moyenne >= 8
SELECT auteur, AVG(note) AS note_moyenne
FROM LIVRES
GROUP BY auteur
HAVING AVG(note) >= 8;

Différence WHERE / HAVING :

-- WHERE filtre les lignes AVANT le regroupement
SELECT auteur, AVG(note)
FROM LIVRES
WHERE ann_publi > 1950      -- Filtre les livres publiés après 1950
GROUP BY auteur;

-- HAVING filtre les groupes APRÈS le regroupement
SELECT auteur, AVG(note)
FROM LIVRES
GROUP BY auteur
HAVING AVG(note) > 8;       -- Garde les auteurs avec moyenne > 8

7. Jointures

Les jointures permettent de combiner des données provenant de plusieurs tables.

7.1. INNER JOIN (jointure interne)

Retourne uniquement les enregistrements qui ont une correspondance dans les deux tables.

SELECT LIVRES.titre, AUTEURS.nom, AUTEURS.prenom
FROM LIVRES
INNER JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id;

Schéma : 

LIVRES          AUTEURS         Résultat INNER JOIN
+----+------+   +----+------+   +------+------+
| id | id_a |   | id | nom  |   | titre| nom  |
+----+------+   +----+------+   +------+------+
| 1  | 1    |   | 1  | Orwell|  | 1984 | Orwell|
| 2  | 2    |   | 2  | Asimov|  | Fond.| Asimov|
| 3  | NULL |   | 3  | Dick  |   +------+------+
+----+------+   +----+------+   (le livre 3 est exclu car id_a est NULL)

7.2. LEFT JOIN (jointure externe gauche)

Retourne tous les enregistrements de la table de gauche, même sans correspondance.

SELECT LIVRES.titre, AUTEURS.nom
FROM LIVRES
LEFT JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id;

7.3. Alias de tables

Pour simplifier l'écriture :

SELECT L.titre, A.nom, A.prenom
FROM LIVRES L
INNER JOIN AUTEURS A ON L.id_auteur = A.id
WHERE A.langue_ecriture = 'français';

7.4. Jointures multiples

SELECT L.titre, A.nom, E.nom_editeur
FROM LIVRES L
INNER JOIN AUTEURS A ON L.id_auteur = A.id
INNER JOIN EDITEURS E ON L.id_editeur = E.id;

8. Ordre d'exécution des clauses

L'ordre d'écriture et l'ordre d'exécution sont différents :

Ordre d'écriture : 

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

Ordre d'exécution : 1. FROM et JOIN : Sélection des tables 2. WHERE : Filtrage des lignes 3. GROUP BY : Regroupement 4. HAVING : Filtrage des groupes 5. SELECT : Sélection des colonnes 6. DISTINCT : Élimination des doublons 7. ORDER BY : Tri 8. LIMIT : Limitation du nombre de résultats

9. Bonnes pratiques

9.1. Conventions d'écriture

  • Mots-clés SQL en MAJUSCULES : SELECT, FROM, WHERE
  • Noms de tables en MAJUSCULES : LIVRES, AUTEURS
  • Noms de colonnes en minuscules : titre, auteur
  • Indentation pour la lisibilité
SELECT L.titre,
       A.nom,
       A.prenom
FROM LIVRES L
INNER JOIN AUTEURS A ON L.id_auteur = A.id
WHERE A.langue_ecriture = 'français'
  AND L.ann_publi > 1950
ORDER BY L.ann_publi DESC;

9.2. Sécurité : Injections SQL

⚠️ Ne jamais construire des requêtes SQL par concaténation de chaînes avec des entrées utilisateur !

# DANGEREUX - Injection SQL possible
requete = "SELECT * FROM USERS WHERE nom = '" + nom_utilisateur + "'"

# SÉCURISÉ - Requête paramétrée
cursor.execute("SELECT * FROM USERS WHERE nom = ?", (nom_utilisateur,))

10. Synthèse

┌─────────────────────────────────────────────────────────────────┐
│                        REQUÊTE SQL                              │
├─────────────────────────────────────────────────────────────────┤
│  SELECT [DISTINCT] colonnes, AGG(colonne)                       │
│  FROM table1                                                    │
│  [INNER|LEFT] JOIN table2 ON condition                          │
│  WHERE conditions                                               │
│  GROUP BY colonne                                               │
│  HAVING condition_agregation                                    │
│  ORDER BY colonne [ASC|DESC]                                    │
│  LIMIT n [OFFSET m]                                             │
└─────────────────────────────────────────────────────────────────┘

┌──────────────┬────────────────────────────────────────────────┐
│ Commande     │ Action                                         │
├──────────────┼────────────────────────────────────────────────┤
│ CREATE TABLE │ Créer une table                                │
│ DROP TABLE   │ Supprimer une table                            │
│ INSERT INTO  │ Insérer des données                            │
│ UPDATE       │ Modifier des données                           │
│ DELETE       │ Supprimer des données                          │
│ SELECT       │ Interroger des données                         │
└──────────────┴────────────────────────────────────────────────┘

┌──────────────┬────────────────────────────────────────────────┐
│ Agrégation   │ Description                                    │
├──────────────┼────────────────────────────────────────────────┤
│ COUNT(*)     │ Nombre d'enregistrements                       │
│ SUM(col)     │ Somme                                          │
│ AVG(col)     │ Moyenne                                        │
│ MIN(col)     │ Minimum                                        │
│ MAX(col)     │ Maximum                                        │
└──────────────┴────────────────────────────────────────────────┘

Pour aller plus loin

Auteur : Florian Mathieu

Licence CC BY NC

Licence Creative Commons
Ce cours est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International.