Le langage SQL
Cours
Introduction
Le SQL (Structured Query Language) est un langage permettant de communiquer avec une base de données. Ce langage informatique est notamment très utilisé par les développeurs web pour communiquer avec les données d'un site web.
La partie langage de manipulation des données de SQL permet de rechercher, d'ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles. C'est cette partie qui nous intéresse en terminale NSI, mais il existe trois autres grandes parties :
- la partie langage de définition des données permet de créer et de modifier l'organisation des données dans la base de données,
- la partie langage de contrôle de transaction permet de commencer et de terminer des transactions,
- la partie langage de contrôle des données permet d'autoriser ou d'interdire l'accès à certaines données à certaines personnes.
Créé en 1974, normalisé depuis 1986, le langage SQL est reconnu par la grande majorité des systèmes de gestion de bases de données relationnelles (SGBDR) du marché. En revanche, chaque SGBDR peut avoir quelques spécificités propres.
Cette année, nous utiliserons principalement SQLite comme moteur de base de données relationnelles.
Généralités sur SQLite
Remarque
Avant d'entrer dans le cœur du sujet, arrêtons-nous un instant sur le moteur SGBDR que nous allons utiliser pour effectuer nos requêtes SQL : SQLite !
SQLite est une bibliothèque écrite en langage C qui propose un moteur de base de données relationnelle accessible par le langage SQL. Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur, mais d'être directement intégrée aux programmes. L'intégralité de la base de données est stockée dans un fichier indépendant de la plateforme.
Ainsi donc, il nous facilitera la mise en œuvre, car vous pourrez disposer du moteur SQLite sur vos propres machines sans faire appel à un serveur SQL à installer.
Dwayne Richard Hipp, le créateur de SQLite, a choisi de mettre cette bibliothèque ainsi que son code source dans le domaine public, ce qui permet son utilisation sans restriction aussi bien dans les projets open source que dans les projets propriétaires. Le créateur ainsi qu'une partie des développeurs principaux de SQLite sont employés par la société américaine Hwaci.
De ce fait, SQLite est le moteur de base de données le plus utilisé au monde, grâce à son utilisation dans de nombreux logiciels grand public comme Firefox, Skype, Google Gears, dans certains produits d'Apple, d'Adobe et de McAfee et dans les bibliothèques standards de nombreux langages comme PHP ou Python. Grâce à son extrême légèreté (moins de 600 Ko), il est également très populaire sur les systèmes embarqués, notamment sur la plupart des smartphones et tablettes modernes. Au total, on peut dénombrer plus d'un milliard de copies connues et déclarées de la bibliothèque.
Manipuler SQL dans le terminal
Harlequin est un client de base de données simple, rapide et beau pour le terminal. Il est compatible avec tous les moteurs de base de données, dont SQLite, et se manipule sur tout type d'OS, dont Windows.
On l'installe avec une commande pip : pip install harlequin.
Pour manipuler un fichier SQLite, il faut utiliser l'adaptateur comme suit : harlequin -a sqlite "D:\dossier\bdd_notes.db".
Manipulations de tables avec CREATE / INSERT
Nous allons utiliser différentes commandes pour créer cette table, que l'on notera notes :
| Nom | NSI | Mathématiques | LV1 | EPS | Philosophie | Histoire & Géographie |
|---|---|---|---|---|---|---|
| Marion | 15 | 16 | 14 | 18 | 14 | 13 |
| Nicolas | 12 | 12 | 15 | 14 | 15 | 16 |
| Tom | 10 | 15 | 18 | 19 | 14 | 11 |
Création d'une table
La commande CREATE TABLE permet de créer une table en indiquant la clé primaire :
CREATE TABLE "notes" (
"Nom" TEXT PRIMARY KEY,
"NSI" INTEGER,
"Mathématiques" INTEGER,
"LV1" INTEGER,
"EPS" INTEGER,
"Philosophie" INTEGER,
"Histoire & Géographie" INTEGER
);
Les types suivants sont disponibles : INTEGER, TEXT, BLOB, REAL, NUMERIC.
On pourra rajouter l'information suivante qui permet de ne créer la table uniquement si elle n'existe pas déjà :
CREATE TABLE IF NOT EXISTS "notes" ( ... );
Insertion des enregistrements
On insère les valeurs sous forme de tuples correspondant aux lignes à enregistrer :
INSERT INTO notes (Nom, NSI, Mathématiques, LV1, EPS, Philosophie, 'Histoire & Géographie')
VALUES ('Marion', 15, 16, 14, 18, 14 ,13), ('Nicolas', 12, 12, 15, 14, 15 ,16), ('Tom', 10, 15, 18, 19, 14, 11) ;
On remarquera qu'Histoire & Géographie est entre ' à cause des espaces notamment.
Si une note est inexistante (absence à une épreuve), on pourra mettre le mot clé NULL.
Si on essaie d'insérer des enregistrements déjà présents (clé primaire déjà existante), nous avons une erreur d'intégrité de ce type :
Result: UNIQUE constraint failed: notes.Nom
Fichier .sql
Pour plus de confort et pour créer une table rapidement, on peut créer un fichier SQL table_notes.sql que l'on
pourra utiliser avec un terminal, par exemple avec SQLite3.
Même si ce n'est pas indispensable immédiatement, mais pour prendre de bonnes habitudes, on prendra soin de commencer
ce fichier par BEGIN TRANSACTION; et le terminer par COMMIT;.
Attention, vous remarquerez que toutes nos commandes se terminent par ;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "notes" (
"Nom" TEXT PRIMARY KEY,
"NSI" INTEGER,
"Mathématiques" INTEGER,
"LV1" INTEGER,
"EPS" INTEGER,
"Philosophie" INTEGER,
"Histoire & Géographie" INTEGER
);
INSERT INTO notes
VALUES ('Marion', 15, 16, 14, 18, 14 ,13), ('Nicolas', 12, 12, 15, 14, 15 ,16), ('Tom', 10, 15, 18, 19, 14, 11) ;
COMMIT;
Extraction de données avec SELECT
La commande SELECT
La commande SELECT permet de sélectionner des colonnes d'une ou de plusieurs tables données en paramètres.
SELECT colonne(s) FROM nom_tables(s);
Si l'on souhaite toutes les colonnes, on utilise le caractère générique *. Pour notre exemple ci-dessus, la commande :
SELECT * FROM notes;
renverra ceci dans un terminal SQLite3 :
Marion|15|16|14|18|14|13
Nicolas|12|12|15|14|15|16
Tom|10|15|18|19|14|11
On peut améliorer l'affichage avec ces 2 commandes précédant la commande SELECT :
sqlite> .headers ON
sqlite> .mode column
sqlite> SELECT * FROM notes;
pour obtenir finalement :
Nom NSI Mathématiques LV1 EPS Philosophie Histoire & Géographie
---------- ---------- ------------- ---------- ---------- ----------- ---------------------
Marion 15 16 14 18 14 13
Nicolas 12 12 15 14 15 16
Tom 10 15 18 19 14 11
Obtenir uniquement 1 ou des colonnes
SELECT nsi, nom FROM notes;
renverra ceci :
NSI Nom
---------- ----------
15 Marion
12 Nicolas
10 Tom
On remarquera 2 choses :
- l'affichage des colonnes respecte l'ordre demandé dans la requête : nsi puis nom ;
- SQLite n'est pas sensible à la casse des caractères.
La commande WHERE
WHERE permet de spécifier des critères de sélection. Par exemple, savoir qui a au moins 15 en NSI
et Mathématiques. La commande ci-dessous :
SELECT Nom
FROM notes
WHERE NSI >= 15 AND Mathématiques >=15;
renverra :
Nom
----------
Marion
Ainsi donc, on peut aussi utiliser les opérateurs logiques NOT, OR ou AND.
Les fonctions de groupes - Agrégation
Avec la commande WHERE, nous pouvons obtenir des informations en travaillant sur les lignes. Avec les fonctions
de groupe, on travaille cette fois-ci sur les colonnes. Voici quelques exemples de telles fonctions :
AVGcalcule la moyenne,SUMcalcule la somme d'une colonne,MIN,MAXcalculent le minimum et le maximum d'une colonneCOUNTdonne le nombre de lignes d'une colonne.
Deux exemples :
- combien d'élèves ont plus de 15 en EPS ?
- quelle est la moyenne de groupe en NSI ?
SELECT COUNT(*) FROM notes WHERE EPS > 15 ;
SELECT AVG(NSI) FROM notes ;
Les deux requêtes précédentes donnent ce résultat :
sqlite> SELECT COUNT(*) FROM notes WHERE EPS > 15 ;
COUNT(*)
----------
2
sqlite> SELECT AVG(NSI) FROM notes ;
AVG(NSI)
----------------
12.3333333333333
DISTINCT : Supprimer les doublons
L'utilisation de la commande SELECT en SQL permet de lire toutes les données d'une ou plusieurs colonnes. Cette
commande peut potentiellement afficher des lignes en doubles. Pour éviter des redondances dans les résultats,
il faut simplement ajouter DISTINCT après le mot SELECT.
Reprenons notre exemple et affichons les notes de Philosophie :
SELECT Philosophie FROM notes ;
Philosophie
-----------
14
15
14
On pourrait vouloir afficher que les notes différentes de Philosophie en utilisant DISTINCT :
SELECT DISTINCT Philosophie FROM notes;
qui supprimerait les doublons de notes de Philosophie :
Philosophie
-----------
14
15
Cette requête ne s'applique que sur une seule colonne !
Modification de tables et valeurs avec UPDATE, DELETE, ALTER et DROP
UPDATE : Agir sur les enregistrements d'une table
Pour modifier des valeurs dans une table, on utilisera UPDATE de la sorte :
UPDATE table SET attribut = valeur WHERE attribut = valeur;
Si on souhaite corriger la note de Tom est lui mettre 11 en NSI :
UPDATE notes SET NSI = 11 WHERE Nom = 'Tom';
DELETE : Supprimer un enregistrement - une ligne
DELETE FROM table WHERE condition;
Supprimons pour l'exemple la ligne dont les notes sont supérieures à 14 en NSI :
DELETE FROM notes WHERE NSI > 14;
Ce qui suit n'est pas officiellement au programme !
ALTER TABLE : ajouter une colonne (ADD COLUMN)
ALTER TABLE table ADD COLUMN définition_colonne;
Ajoutons la LV2 :
ALTER TABLE notes ADD COLUMN LV2 INT;
La nouvelle colonne sera ajoutée, mais non remplie bien évidemment (Marion ayant disparue précédemment) !
sqlite> SELECT * FROM notes;
Nom NSI Mathématiques LV1 EPS Philosophie Histoire & Géographie LV2
---------- ---------- ------------- ---------- ---------- ----------- --------------------- ----------
Nicolas 12 12 15 14 15 16
Tom 11 15 18 19 14 11
ALTER TABLE : renommer une table (RENAME TO)
ALTER TABLE table RENAME TO nouveau_nom_table;
DROP TABLE : supprimer une table
DROP TABLE table;
Jointures de tables
Les jointures en SQL permettent d'associer plusieurs tables dans une même requête. Cela permet d'exploiter la puissance des bases de données relationnelles pour obtenir des résultats qui combinent les données de plusieurs tables de manière efficace.
Jointure classique JOIN
Cette commande JOIN (ou INNER JOIN) retourne les enregistrements lorsqu'il y a au moins une ligne dans
chaque colonne qui correspond à la condition. La syntaxe est la suivante :
SELECT * -- ou choix des attibuts de projection
FROM table1
JOIN table2 -- ou table1 INNER JOIN table2
ON table1.attibut1 = table2.attibut2 ;
Join - A et B étant deux ensembles
Pour lever toute ambiguïté, on peut préfixer par le nom de la table ou faire un renommage avec AS.
Soit la table mentions ci-dessous :
| Note | Mention |
|---|---|
| 10 | Peut mieux faire |
| 11 | Passable |
| 12 | Honorable |
| 13 | Assez bien |
| 14 | Bien |
| 15 | Très bien |
| 16 | Excellent |
Jointure entre notes et mentions
La requête de jointure pour attribuer une mention à un nom d'élève pour ses notes de NSI sera donc la suivante :
SELECT Nom, Note, Mention
FROM notes
JOIN mentions
ON notes.NSI = mentions.Note;
qui renverra
Nom Note Mention
---------- ---------- ----------------
Marion 15 Très bien
Nicolas 12 Honorable
Tom 10 Peut mieux faire
On remarquera que l'on aurait pu écrire la requête suivante, qui aurait donné le même résultat.
SELECT Nom, NSI, Mention
FROM notes
JOIN mentions
ON notes.NSI = mentions.Note;
On pourra améliorer la lecture avec des AS pour lever les ambiguïtés possibles :
SELECT Nom, Note AS NOTE_NSI, Mention AS Mention_NSI
FROM notes AS n
JOIN mentions AS m
ON n.NSI = m.Note;
qui retourne :
Nom Note_NSI Mention_NSI
---------- ---------- ----------------
Marion 15 Très bien
Nicolas 12 Honorable
Tom 10 Peut mieux faire
JOIN = INNER JOIN
Cette requête renvoie exactement la même chose :
SELECT Nom, Note AS NOTE_NSI, Mention_NSI
FROM notes AS n
INNER JOIN mentions AS m
ON n.NSI = m.Note;
Créer une nouvelle table à partir d'une requête
Si l'on veut créer une nouvelle table à partir d'une sélection, on peut utiliser la commande suivante :
CREATE TABLE Appreciations
AS
SELECT Nom, Note AS NOTE_NSI, Mention_NSI
FROM notes AS n
JOIN mentions AS m
ON n.NSI = m.Note ;
Autres jointures
De nombreuses jointures existent, mais elles ne sont pas au programme de terminale NSI. Vous pouvez avoir un aperçu ici : https://sql.sh/cours/jointures
Cet article détaillé en anglais vous donnera un bel aperçu, dont voici une synthèse en image.
Exercices
Il existe un site important à signaler concernant SQL : SQL.sh. Il pourra vous aider en complément du cours.
Pour l'utiliser, copiez-le dans votre dossier contenant vos exercices de SQL. Vous pouvez ensuite double-cliquer
dessus pour l'ouvrir, puis taper .open nom_du_fichier.db pour ouvrir (ou créer) une base de données.
Exercice 1 : Pratiquer SQLite avec un terminal
- Reprendre tout le cours pour créer la base de données
bdd_notes.dbavec un terminal. - Exercez-vous à taper les différentes commandes SQL.
Exercice 2 : Gestion d'un réseau d'agences de location de voitures
Réaliser les deux premières analyses du TP sur la gestion d'un réseau d'agences de location de voitures
Exercice 2 bis : Gestion d'un réseau d'agences de location de voitures
Terminer les analyses 3 et 4 du TP sur la gestion d'un réseau d'agences de location de voitures.
Exercice 3 : Base de données de films
Rédiger un script SQL permettant de créer en remplir la base de données de films, contenant les tables
film, realisateur, genre, nationalite.
Rappel de la base film
