SQL

I. Présentation de SQL

Afin d'effectuer toutes les requêtes possibles et imaginables sur les données d'une base de données, il faut diposer d'un langage de requête. Très vite, un langage dédié s'est détâché, et est maintenant reconnu par la plupart des SGBD : il s'agit de SQL (Structured Query Language). SQL n'est en fait pas qu'un langage de requête ; il est constitué de 4 parties :

• Langage d'Interrogation de Données (LID) : pour retrouver des informations dans la base de données ;
• Langage de Manipulation de Données (LMD) : pour modifier les données de la base ;
• Langage de Description de Données (LDD) : pour définir la structure d'une base de données ;
• Langage de Contrôle de Données (LCD) : pour définir les droits d'accès à la base.

Dans le cadre de ce cours, nous survolerons les aspects LID, LMD et LDD, mais nous n'aborderons pas l'aspect LCD.

II. SQL comme Langage d'Interrogation de Données

A. Tâche préliminaire

• Créez un répertoire appelé Ludotheque ;
• Copiez dans le répertoire Ludotheque le fichier creationTableIllustrateur.py. Ne vous souciez pas de son contenu pour le moment ;
• Exécutez le fichier creationTableIllustrateur.py. Vous constatez que cela crée dans le répertoire Ludotheque un fichier appelé ludotheque.db. Ce fichier contient la base de données (réduite pour le moment à la table Illustrateur) au format sqllite3 ;

B. Introduction : affichez le contenu de la table Auteur

1. Première solution : si vous êtes sous linux (ou Mac OS)

Le programme sqlite3 est certainement installé. Vous pouvez alors le lancer ainsi :

sqlite3 ludotheque.db

Vous pouvez alors taper l'instruction suivante (en n'oubliant pas le ";" final), qui permet d'afficher le contenu de la table Illustrateur :

sqlite3> select * from illustrateur;

Vous devez alors obtenir l'affichage suivant :

1|Delval|Julien|Française
2|Coimbra|Miguel|Française
3|Quilliams|Chris|Canadienne
4|Balixa|Bruno|Américaine
5|Alsop|Dave|Américaine
6|Torres|Francisco Rico|Américaine
  

N.B. : Pour toutes les tables que vous aurez à manipuler dans cette partie, les noms de champs existant dans plusieurs tables sont suffixés par le nom de la table. Ainsi, les champs de la table Illustrateur s'appellent "idIllustrateur, nomIllustrateur, prenomIllustrateur, et nationaliteIllustrateur.

Pour quitter sqlite3, tapez ctrl-D ou .quit.

2. Deuxième solution : universelle

Récupérez le programme python lectureLudotheque.py et mettez-le dans le répertoire Ludotheque, puis exécutez-le en tapant la requête suivante (sans ';') :

Requête : select * from illustrateur
(1, 'Delval', 'Julien', 'Française')
(2, 'Coimbra', 'Miguel', 'Française')
(3, 'Quilliams', 'Chris', 'Canadienne')
(4, 'Balixa', 'Bruno', 'Américaine')
(5, 'Alsop', 'Dave', 'Américaine')
(6, 'Torres', 'Francisco Rico', 'Américaine')

C. Projection

La base d'une requête SQL est constituée de 2 mots-clés introduisant 2 parties :

1. SELECT introduit la liste des champs que l'on veut afficher (séparés par des ","). "*" est un raccourci pour désigner tous les champs
2. FROM introduit la liste des tables à partir dans lesquelles la recherche doit être faite. Dans un premier temps on se limitera aux requêtes ne portant que sur une table.

En général, on préfère afficher des résultats triés. Pour ce faire, on peut compléter la requête SQL avec une troisième partie, introduite par le mot-clé ORDER BY, et donnant la liste des champs à prendre en compte dans le tri.

Remarques sur le tri

• Le tri est effectué avec un ordre numérique sur les attributs numériques, et un ordre alphanumérique sur les attributs contenant du texte ;
• Si l'on veut un tri par ordre inverse sur certaines attributs, il suffit, dans la clause order by, de faire suivre le nom du champ du mot-clé DESC (comme descending order).

D. Selection

1. Sélection simple

Une autre fonctionnalité du langage SQL est de permettre de ne sélectionner que les tuples d'une table vérifiant certaines propriétés. Pour cela, on va rajouter à notre requête une clause WHERE condition. Par exemple, pour n'afficher que les illustrateurs français :

select * from illustrateur where nationaliteIllustrateur='Française'

On remarque alors que :

• Les constantes de type texte doivent être mises entre apostrophes simples ;
• La recherche est sensible à la casse (majuscule/minuscule) : essayez avec 'française' au lieu de 'Française' ;
• En utilisant l'opérateur "=", il faut une égalité stricte : essayez avec 'Français' au lieu de 'française'.

Pour ne pas tenir compte de la casse, on peut faire une égalité stricte entre les valeurs converties en majuscule grâce à la fonction upper:

select * from illustrateur where upper(nationaliteIllustrateur)=upper('Française')

Pour ne pas se restreindre à l'égalité strict, on peut utiliser l'opérateur de comparaison LIKE. Celui-ci est suivi d'un modèle dans lequel :

• Le caractère '%' est un joker qui correspond à un nombre quelconque de caractères, y compris 0 ;
• Le caractère '_' est un joker qui correspond exactement 1 caractère quelconque ;
• Tous les autres caractères doivent être présents tels quels.

Par exemple pour afficher la liste des illustrateurs dont la nationalité contient un "i" puis un "e" séparés par exactement 1 caractère :

select * from illustrateur where nationaliteIllustrateur like '%i_e%'

Bien sûr, on peut également utiliser des opérateurs tels que <, >, <=, >=, <> IN, BETWEEN, ... Les relations d'ordre peuvent également être utilisées sur les chaînes de caractères, mais dans ce cas-là, il faut savoir que toutes les majuscules sont considérées comme inférieures à toutes les minuscules.

2. Sélection multiple

Si la sélection porte sur plusieurs conditions, il eset possible de les combiner avec les opérateurs AND, OR, avec éventuellement des parenthèses. Par exemple, pour retrouver les illustrateurs français dont le nom commence par une des 12 premières lettres de l'alphabet :

SELECT *
FROM   illustrateur
WHERE  nationalitéIllustrateur = 'Française'
AND    nomIllustrateur < 'M'

SELECT *
FROM illustrateur
WHERE (nationaliteIllustrateur = 'Américaine' AND
      upper(prenomIllustrateur) LIKE '%O%')
OR    nationaliteIllustrateur = 'Française'

3. Combiner sélection et projection

Il est bien sûr possible de combiner sélection et projection. La projection est effectuée en dernier, ce qui signifie que la sélection peut tout à fait porter sur des champs qui ne sont pas renvoyés par la requête.

SELECT prenomIllustrateur
FROM illustrateur
WHERE nationaliteIllustrateur = 'Française'
ORDER BY prenomIllustrateur

4. Éviter les occurrences multiples

Pour récupérer la liste des nationalités des illustateurs de la base de données, on pourrait taper la requête suivante :

SELECT nationaliteIllustrateur FROM illustrateur

Mais cela produit l'affichage suivant :

Française
Française
Canadienne
Américaine
Américaine
Américaine

Pour éviter cette redondance dans le résultat, on peut rajouter le mot-clé DISTINCT après le mot-clé SELECT :

SELECT DISTINCT nationaliteIllustrateur FROM illustrateur

L'affichage produit est alors le suivant :

Française
Canadienne
Américaine

5. Fonctions d'agréation

Il existe également des fonctions d'agrégation dans SQL qui permettent d'effectuer des statistiques sur les données de la base. Voici quelques exemples :

1. Nombre d'illustrateurs dans la base :

SELECT COUNT(*) FROM Illustrateur

2. Nombre de nationalités différentes parmi les illustrateurs :

SELECT COUNT(DISTINCT nationaliteIllustrateur) FROM Illustrateur

3. Identifiant le plus grand parmi les illustrateurs :

SELECT MAX(idIllustrateur) FROM Illustrateur

E. Requêtes portant sur plusieurs tables

1. Étape prélimiaire

Récupérez le programmme python creationTablesEditeurJeu.py, placez-le dans votre répertoire Ludotheque, et exécutez-le. Ce programme crée les tables Editeur et Jeu avec leur contenu. Le résultat peut être vérifié avec les 2 requêtes suivantes :

SELECT * FROM editeur
(1, 'Days of wonder', 'Française')
(2, 'EggertSpiele', 'Allemande')
(3, 'Iello', 'Française')
SELECT * FROM jeu
(1, 'Les chevaliers de la table ronde', 3, 7, 90, 1)
(2, 'Cargo Noir', 2, 5, 60, 1)
(3, 'Era: medieval age', 1, 4, 50, 2)
(4, 'Smash up', 2, 4, 45, 3)/pre>

2. Produit cartésien

Que se passe-t-il si on effectue naïvement une requête en spécifiant plusieurs tables dans la clause FROM de la requête ? Essayons avec l'exemple suivant :

SELECT * FROM jeu, editeur
(1, 'Les chevaliers de la table ronde', 3, 7, 90, 1, 1, 'Days of wonder', 'Française')
(1, 'Les chevaliers de la table ronde', 3, 7, 90, 1, 2, 'EggertSpiele', 'Allemande,')
(1, 'Les chevaliers de la table ronde', 3, 7, 90, 1, 3, 'Iello', 'Française')
(2, 'Cargo Noir', 2, 5, 60, 1, 1, 'Days of wonder', 'Française')
(2, 'Cargo Noir', 2, 5, 60, 1, 2, 'EggertSpiele', 'Allemande,')
(2, 'Cargo Noir', 2, 5, 60, 1, 3, 'Iello', 'Française')
(3, 'Era: medieval age', 1, 4, 50, 2, 1, 'Days of wonder', 'Française')
(3, 'Era: medieval age', 1, 4, 50, 2, 2, 'EggertSpiele', 'Allemande,')
(3, 'Era: medieval age', 1, 4, 50, 2, 3, 'Iello', 'Française')
(4, 'Smash up', 2, 4, 45, 3, 1, 'Days of wonder', 'Française')
(4, 'Smash up', 2, 4, 45, 3, 2, 'EggertSpiele', 'Allemande')
(4, 'Smash up', 2, 4, 45, 3, 3, 'Iello', 'Française')

Comme on peut le voir sur l'exemple qui précède, en mettant directement plusieurs tables dans la clause FROM d'une requête SQL, la réponse donnée est un produit cartésien des tables précisées, c'est-à-dire que l'on obtient tous les tuples qu'il est possible de faire en mettant pour les premières composantes les tuples de la première table et pour les dernières composantes tous les tuples de la deuxième table. Ceci n'est pas très intéressant.

3. Jointure

Il serait en effet plus intéressant de ne récupérer que des tuples constitués d'un jeu et de l'éditeur correspondant. Pour ce faire, on va utiliser, dans la clause FROM, l'opérateur JOIN... ON, pour préciser que l'on ne constituera que des tuples dont la valeur du champ idEditeur dans la table Jeu (noté Jeu.idEditeur) est égale à la valeur du champidEditeur dans la table Editeur (noté Editeur.idEditeur:

SELECT * FROM jeu JOIN editeur ON jeu.idEditeur = editeur.idEditeur
1|Les chevaliers de la table ronde|3|7|90|1|1|Days of wonder|Française
2|Cargo Noir|2|5|60|1|1|Days of wonder|Française
3|Era: medieval age|1|4|50|2|2|EggertSpiele|Allemande
4|Smash up|2|4|45|3|3|Iello|Française

Les champs sur lesquels faire la jointure ayant les mêmes noms dans les 2 tables, cette requête peut aussi être écrite en utilisant le mot-clé USING ainsi :

SELECT * FROM jeu JOIN editeur USING (idEditeur)

Il est alors possible d'effectuer des sélections et des projections sur le résultat d'une jointure.

SELECT   nomJeu, nationaliteEditeur
FROM     Jeu JOIN Editeur ON Jeu.idEditeur = Editeur.IdEditeur
ORDER BY nationaliteEditeur, nomJeu

SELECT   nomJeu
FROM     Jeu JOIN Editeur USING (idEditeur)
WHERE    nationaliteEditeur = 'Française'
ORDER BY nationaliteEditeur, nomJeu

4. Le cas des association N-M

Commencez par récupérer et exécuter le programme python creationTableEstDessinePar.py.

Supposons que l'on souhaite retrouver, pour chaque jeu, tous ses illustrateurs. Nous avons besoin de la table Jeu pour avoir le nom du jeu, de la table Illustrateur pour avoir le nom de l'illustrateur, et de la table EstDessinePar pour faire le lien entre les jeux et leurs illustrateurs.

La requête à écrire est alors la suivante :

SELECT nomJeu, prenomIllustrateur, nomIllustrateur
FROM Jeu JOIN EstDessinePar USING (idJeu) JOIN Illustrateur USING (idIllustrateur)
ORDER BY nomJeu, nomIllustrateur

On obtient alors le résultat suivant :

Cargo Noir|Miguel|Coimbra
Era: medieval age|Chris|Quilliams
Les chevaliers de la table ronde|Julien|Delval
Smash up|Dave|Alsop
Smash up|Bruno|Balixa
Smash up|Francisco Rico|Torres

Comme on peut le voir, s'il y a plusieurs dessinateurs pour un jeu, on récupère une ligne pour chaque couple (jeu, dessinateur).

Les fonctionnalités de la partie LID de SQL sont bien plus larges que cet aperçu, mais cela dépasse le cadre de ce cours. Voici 2 exercices que vous pouvez faire sur papier (les tables nécessaires ne sont pas encore créées) en utilisant les tables qui ont été définies dans l'exercice 4 du cours sur la notion de modèle conceptuel.

SELECT nomJeu
FROM jeu JOIN parleDe USING (idJeu) JOIN theme USING (idTheme)
WHERE nbJoueursMin <= 4
AND nbJoueursMax >= 4
AND upper(nomTheme) = 'NAVIGATION MARCHANDE'

SELECT nomJeu
FROM illustrateur JOIN estDessinePar USING(idIllustrateur)
                  JOIN jeu USING (idJeu)
                  JOIN editeur USING (idEditeur)
WHERE nationaliteIllustrateur = nationaliteEditeur

F. Absence de valeur pour un champ

Pour un tuple donné d'une table, il se peut qu'un attribut n'ait pas de valeur connue. Cette absence de valeur peut être testée dans la clause WHERE d'une requête avec l'expression IS NULL ou IS NOT NULL. Attention : utiliser = NULL ne marchera pas, NULL n'étant pas une valeur, mais représentant l'absence de valeur.

III. SQL comme Langage de Manipulation de Données (LMD)

En tant que langage de manipulation de données, SQL permet 3 types d'action :

• L'insertion d'un tuple dans une table
• La suppression de tuples dans une table
• La modificaiton de tuples dans une table

Nous allons étudier chacune de ces fonctionnalités dans la suite de cette partie.

A. Insertion d'un tuple

L'insertion d'un tuple dans une table se fait grâce à l'instruction suivante :

INSERT INTO NomTable VALUES (valeurChamp1, valeurChamp2, ... valeurChampn)

INSERT INTO Illustrateur VALUES (7, 'Cochard', 'David', 'Française')

Si on ne souhaite pas préciser de valeur pour un champ, il faut passer le mot-clé dédié NULL. Ainsi, l'illustrateur Naiade serait inséré ainsi :

INSERT INTO Illustrateur VALUES (8, 'Naiade', NULL, 'Française')

Une autre façon de faire est de spécifier explicitement la liste des champs auxquels correspondent les valeurs passées :

INSERT INTO Illustrateur(idIllustrateur, nomIllustrateur, nationaliteIllustrateur) VALUES (8, 'Naiade', 'Française')

On pourra noter que l'éventuelle violation d'une contrainte telle que l'unicité d'une clé est signalée et l'insertion refusée :

INSERT INTO Illustrateur values (1, 'Cardouat', 'Marie', 'Française')
Error: UNIQUE constraint failed: illustrateur.idIllustrateur

B. Suppression de tuples

La suppression de tuples dans une table se fait grâce à la commande suivante :

DELETE FROM NomTable
WHERE condition

Ainsi, pour supprimer les tuples rajoutés dans la partie précédente, on peut lancer la requête suivante :

DELETE FROM Illustrateur
WHERE idIllustrateur >= 7

C. Modification de tuples

La modification de tuples dans une table s'effectue avec la commande suivante :

UPDATE nomTable
SET champx = valx, champy = valy, ...
WHERE condition

Notez qu'étant donnée la forme de l'instruction, cela permet de modifier éventuellement plusieurs enregistrements. Pour éviter cela, on passera souvent par un WHERE portant sur la clé du champ à modifier. Par exemple, pour mettre un majuscule à "Wonder" dans le nom de l'éditeur Days of Wonder, on effectuera la requête suivante :

UPDATE Editeur
SET nomEditeur='Days of Wonder'
WHERE idEditeur = 1

Mais si l'on veut modifier la nationalité pour supprimer la cédille dans "Française" pour tous les éditeurs, on pourra procéder en une seule fois ainsi :

UPDATE Editeur
SET nationaliteEditeur = 'Francaise'
WHERE nationaliteEditeur = 'Française'

IV. SQL comme Langage de Description de Données (LDD)

La partie Langage de Description de Données du langage SQL permet de créer, modifier et supprimer des tables au sein du SGBD.

A. Création de table

Nous allons présenter la création de tables au moyen de quelques exemples progressifs.

Voici par exemple comment créer la table Editeur de notre étude de cas :

CREATE TABLE editeur (
     idEditeur int primary key not null,
     nomEditeur text not null,
     nationaliteEditeur text
)

Comme on peut le voir sur l'exemple précédent, on utilise l'instruction CREATE TABLE nomTable (liste des champs).

Les champs sont définis par leur nom suivi de leur type. Les types mentionnés ici (int, text) sont des types propres à SQLite. Pour plus de détails sur les types des champs, voir cette page.

On remarque également que la définition du champ idEditeur est suivie de 2 contraintes :

• une contrainte de clé primaire : primary key. Ainsi, il ne sera pas possible d'insérer dans cette table 2 tuples ayant même valeur pour le champ idEditeur
• une contrainte de non nullité : not null. Ainsi, il ne sera pas possible d'insérer dans cette table de tuple n'ayant pas de valeur pour le champ idEditeur.

Dans la plupart des SGBD, préciser qu'un champ est "clé primaire" implique que ce champ ne peut pas contenir de valeur NULL, mais ce n'est pas le cas avec SQLite.

Dans la table Editeur, on remarque que le champ nomEditeur possède lui aussi la contrainte NOT NULL (définir un éditeur sans lui donner de nom n'a pas de sens), mais ce n'est pas le cas du champ nationaliteEditeur ; cela permet par exemple de faire figurer malgré tout dans la base un éditeur dont on ne connaît pas la nationalité.

Voici maintenant comment la table Jeu a été créée :

CREATE TABLE jeu (
     idJeu int primary key not null,
     nomJeu text not null,
     nbJoueursMin int,
     nbJoueursMax int,
     duree int,
     idEditeur int REFERENCES Editeur(idEditeur)
)

Comme on peut le voir, une contrainte de clé étrangère a été associée au champ idEditeur. Ceci doit normalement garantir que les seule valeurs autorisées pour ce champ sont des valeurs existant pour l'attribut idEditeur de la table Editeur. Toutefois, avec la version 3 de SQLite, ces contraintes ne sont pas vérifiées par défaut. Pour qu'elles soient vérifiées, il faut, avant d'utiliser la base (pour faire des modifications par exemple), exécuter la commande suivante :

PRAGMA foreign_keys = ON

Voici maintenant le code ayant permis de créer la table EstDessinePar :

CREATE TABLE EstDessinePar (
     idIllustrateur int not null,
     idJeu int not null,
     PRIMARY KEY (idJeu, idIllustrateur),
     FOREIGN KEY(idJeu) REFERENCES Jeu(idJeu),
     FOREIGN KEY(idIllustrateur) REFERENCES Illustrateur(IdIllustrateur)
)

Comme on peut le voir, les contraintes de clé étrangère ont ici été déclarées en fin de définition de la table. C'est tout à fait possible, mais cela nécessite de repréciser le nom du champ concerné.

La contrainte de clé primaire est elle aussi déclarée en fin de définition de la table. Elle ne peut cette fois-ci pas être déclarée sur la même ligne qu'un champ car la clé est constituée de 2 champs.

B. Supprimer une table

Pour supprimer une table, il suffit d'utiliser une instruction SQL de la forme :

DROP TABLE NomTable

Important : Avec SQLite, il est ainsi possible de supprimer une table référencée dans d'autres tables, rompant ainsi la structure de la table. Il faut donc faire très attention. Ceci n'est fort heureusement pas possible avec la plupart des autres SGBD

La requête ci-dessus génére une erreur si la table n'existe pas. Si on souhaite éviter cela (pratique dans un programme par exemple) on peut plutôt utiliser cette version-ci :

DROP TABLE IF EXISTS NomTable

V. Quelques considérations générales

1. Sensibilité ou non à la casse

Concernant les noms des tables, des attributs et les mots-clés, SQL n'est pas sensible à la casse. Il est cependant recommandé de se définir une norme à respecter, comme par exemple :

• Les mots-clés SQL en majuscule ;
• Les noms des tables en CamelCase ;
• Les noms des champs en camelCase.

Par contre, concernant les données elles-mêmes, SQL est bel et bien sensible à la casse.

2. Nommage des champs

Dans tout le support de cours, pour clarifier les choses, je me suis efforcé de données des noms différents à tous les champs. En fait, 2 champs de 2 tables différentes peuvent très bien avoir le même nom. S'il est nécessaire de les distinguer, lors d'une jointure par exemple, il suffit de les préfixer avec le nom de la table puis le caractère "." : Jeu.idJeu par exemple.

3. Vérification des contraintes de référence

4. Quelques commandes de l'outil sqlite3

Au sein de l'outil slite3, on trouve quelques commandes (qui ne sont pas du SQL) fort pratiques. Elles sont toutes préfixées par un ".", et ne requièrent pas de ";" final. En voici quelques-unes :

.help

affiche la liste des commandes

.quit

permet de quitter sqlite3

.tables

affiche la liste des tables de la base courante

.schema NomTable

affiche la requête ayant permis la création de la table NomTable

.fullschema

affiche la liste des requêtes ayant permis de créer toutes les tables de la base

.read fichier

Exécute les commandes SQL contenues dans le fichier fichier.

.dump

génère le code nécessaire à la re-création de la base (création et remplissage des tables). Une utilisation pratique pour la mise en oeuvre d'un TP avec une base pré-configurée est la suivante. Une fois la base créée et remplie, effectuer depuis SQLite3 les commandes suivantes :

1. .output initialisation.sql (les affichages seront faits dans le fichier )
2. .dump
3. .output (les affichages auront de nouveau lieu à l'écran)

En éditant le fichier généré, on peut ainsi facilement ne garder que les créations de tables et tuples intéressants, ou les séparer en différentes parties. Les élèves pourront alors faire un .read fichier.sql depuis SQLite3 pour initialiser la base dans l'état souhaité. Si on souhaite faire travailler les élèves sur toute la base telle qu'elle, autant leur donner directement le fichier .db.

VI. Exercice