Requêtes SQL

Structured Query Language : C'est un langage visant à exploiter des bases de données relationnelles.

Ce langage est utilisé, à quelques variantes près, par tous les grands noms des SGBDR – systèmes gestion BDD relationnelle – Oracle, MySQL, MariaDB, Access, Microsoft SQL Server, PostgreSQL, SQLite, MongoDB

Il s'agit d'emettre des requêtes, c'est à dire des commandes, consistant à modifier une données, en insérer une, en effacer ou bien à lire le contenu de la base de données. En général, ces requêtes sont émises automatiquement depuis un programme écrit dans n'importe quel langage : Python, PHP…

Dans le cadre de ce premier cours, nous allons utiliser un outil permettant d'envoyer manuellement des requêtes SQL et de visualiser la base comme dans Microsoft Access, sous forme de tables. Je propose l'utilisation de SQLiteBrowser.

Afin d'illustrer notre travail, on utilisera un exemple.

Pour cette base, nous besoin de deux tables FILM et ARTISTE et aussi d'une table JOUEDANS pour la relation du haut :

• ARTISTE : idArtiste, nom, prénom, biographie, naissance
• FILM : idFilm, titreVO, titreVF, année, idRéal
• JOUEDANS : idArtiste, idFilm, rôle

Les clés primaires sont soulignées et les clés étrangères sont en italique.

Pour créer une table on doit se poser ce genre de question :

• quel est le nom de la table,
• pour chaque attribut, quel est son domaine – quel est le type de valeur autorisée,
• quel est la clé primaire (ou les clés primaires)
• y a t-il des clés étrangères.

CREATE TABLE ARTISTE (
    idArtiste INTEGER PRIMARY KEY,
    nom VARCHAR(20),
    prénom VARCHAR(20),
    biographie TEXT,
    naissance DATE
);

CREATE TABLE FILM (
    idFilm INTEGER PRIMARY KEY,
    titreVO VARCHAR(40),
    titreVF VARCHAR(40),
    année INTEGER,
    idReal INTEGER NOT NULL,
    FOREIGN KEY(idReal) REFERENCES ARTISTE(idArtiste)
);

CREATE TABLE JOUEDANS (
    idArtiste INTEGER NOT NULL,
    idFilm INTEGER NOT NULL,
    rôle VARCHAR(20),
    PRIMARY KEY (idArtiste, idFilm),
	FOREIGN KEY(idArtiste) REFERENCES ARTISTE(idArtiste),
    FOREIGN KEY(idFilm) REFERENCES FILM(idFilm)
);

Vous pouvez voir différents types de données :

• INTEGER : entier 4 octets. Il existe des types entiers prenant moins de place, des plus grands, des types pour les flottants…
• VARCHAR : du texte. On précise la taille de la chaîne. VARCHAR(20) est une chaîne qui ne pourra pas dépasser 20 caractères. Se pose le problème de l'encodage. Unicode tend à s'imposer. Nous n'en parlerons pas ici.
• TEXT : du texte en taille illimitée.
• DATE : une date au format AAAA-MM-JJ. On utilise aussi souvent DATETIME au format AAAA-MM-JJ hh:mm:ss

Vous remarquez également quelques NOT NULL destinés à empêcher qu'un champ reste vide. Si on avait toléré qu'un film n'ait pas de réalisateur, on n'aurait pas mis le NOT NULL. En écrivant NOT NULL pour idReal, on interdit l'absence de réalisateur.

Enfin, vous pouvez voir l'écriture des contraintes de clés étrangères. Ici, un FILM doit avoir un idReal, et FILM.idReal doit correspondre à un ARTISTE.idArtiste.

INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES('Bruce', 'Willis', 'Né sur une base américaine à Idar-Oberstein en Allemagne de l''Ouest où son père, un soldat américain, était affecté, Bruce Willis passe le reste de son enfance dans le New Jersey. Au Collège d''Etat de Montclair, il s''adonne à la musique, joue de l''harmonica et suit les cours de la section théâtrale.', '1955-03-19');

Exemple d'une insertion d'un artiste, Bruce Willis. La syntaxe est assez explicite.

Dans le texte de la biographie, vous pouvez voir la présence de caractères ', mais ces caractères servent à délimiter les chaînes de texte… La convention est de doubler les ' pour qu'ils soient compris comme une simple apostrophe. C'est pourquoi vous pouvez voir des '' ici et là dans la biographie.

INSERT INTO ARTISTE(idArtiste, prénom, nom, biographie, naissance) VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19');

INSERT INTO ARTISTE VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19');

Essayons maintenant une commande qui ne passe pas.

INSERT INTO FILM(titreVO, titreVF, année) VALUES('Die Hard', 'Piège de Cristal', 1988);

Cette commande renverra une erreur car on n'a pas précisé idReal, or on a dit lors de la définition que idReal est NOT NULL, il faut une valeur.

Cette autre commande échoue.

INSERT INTO FILM(titreVO, titreVF, année, idReal) VALUES('Die Hard', 'Piège de Cristal', 1988, 45);

Cette fois, on a bien précisé un idReal mais il n'y a pour l'instant aucun artiste avec un idArtiste = 45. C'est la clé étrangère qui fait échouer la commande.

On comprend qu'il faudra d'abord insérer l'artiste réalisateur avant de pouvoir insérer le film.

INSERT INTO ARTISTE(prénom, nom, biographie, naissance) VALUES('John', 'McTiernan', 'Diplômé en théâtre de l''école de Juillard dans l''état de New York,...', '1952-01-08');

Après l'insertion du réalisateur, on peut aller voir qu'il a bien été créé avec idArtiste = 2, et donc maintenant on peut exécuter :

INSERT INTO FILM(titreVO, titreVF, année, idReal) VALUES('Die Hard', 'Piège de Cristal', 1988, 2);

Pour compléter, ajoutons une entrée JOUEDANS :

INSERT INTO JOUEDANS(idArtiste, idFilm, rôle) VALUES (1,1,'Mc Lain');

John McTiernan n'est pas né le 08/01/1952 mais le 08/01/1951. On souhaite donc modifier cette donnée.

UPDATE ARTISTE SET naissance = '1951-01-08' WHERE idArtiste = 2;

Là encore la commande est assez explicite.

UPDATE ARTISTE SET nom = UPPER(nom);

Bien sûr, la commande suivante échoue :

UPDATE FILM SET idReal = 25 WHERE titreVO = 'Die Hard';

à cause de la contrainte de clé étrangère : l'artiste 25 n'existe pas. En revanche, ceci fonctionne :

UPDATE FILM SET idReal = 1 WHERE titreVO = 'Die Hard';

S'il y avait plusieurs films de même titreVO, ils seraient tous affectés.

Remettez idReal à 2 pour la suite.

La commande est DELETE. Commençons par un cas qui ne fonctionne pas pour cause de clé étrangère :

DELETE FROM ARTISTE WHERE prénom = 'Bruce';

D'abord cette commande est un peu dangereuse car elle effacera tous les artistes de prénom Bruce, il pourrait y en avoir plusieurs.

Ici, comme Bruce Willis est rattaché au film Die Hard, la suppression de Bruce Willis est impossible.

En revanche, celle ci fonctionne – remarquez au passage l'utilisation de AND :

DELETE FROM JOUEDANS WHERE idArtiste = 1 AND idFilm = 1;

Nous avons vu comment créer la base, créer des items, en supprimer, en modifier.

Nous allons passer maintenant aux commandes permettant d'en explorer le contenu.

La commande SELECT permet d'extraire des données de la base. Au plus simple :

SELECT * FROM ARTISTE;

* est un symbole couramment utilisé quand on veut dire TOUT. Donc ici on renvoie toutes les colonnes pour toute la table ARTISTE.

SELECT nom, prénom FROM ARTISTE;

Presque la même chose, sinon que l'on ne demande que la colonne nom et la colonne prénom. On parle de projection.

SELECT nom, prénom FROM ARTISTE WHERE naissance > '1970-01-01';

La clause WHERE permet de limiter le retour aux seuls artistes dont la naissance vient après le 01/01/1970.

SELECT nom, prénom FROM ARTISTE WHERE nom LIKE 'Bo%';

LIKE signifie RESSEMBLE, ici on ne garde que les artistes dont le nom commence par “Bo”.

On peut éviter d'afficher des doublons. Ci-dessous la première commande fait apparaître des rôle identiques – Belatrix Lestrange par exemple, même rôle dans 2 films différents – la deuxième commande supprime les doublons.

SELECT rôle FROM JOUEDANS;
SELECT DISTINCT rôle FROM JOUEDANS;

Et on peut trier :

• tri par naissance puis nom dans le premier cas,
• ordre descendant dans le second cas – mot clé DESC.

SELECT nom, prénom FROM ARTISTE ORDER BY naissance, nom;
SELECT nom, prénom FROM ARTISTE ORDER BY naissance DESC;

Dans le cas du requête avec WHERE et ORDER BY, il faut placer ORDER BY après.

On peut compter les items. Par exemple la requête suivante renvoie une liste de noms avec éventuellement des doublons :

SELECT nom from ARTISTE;

Si nous n'étions intéressé que par le nombre de lignes de cette liste – ce qui revient à compter le nombre d'artistes – on peut utiliser le mot clef COUNT :

SELECT COUNT(nom) from ARTISTE;

Cette requête est différente puisqu'elle ne renvoie pas une liste de valeurs, elle ne renvoie que le résultat du compte.

Si je veux afficher les films

SELECT * FROM FILM;

On n'est pas très satisfait car on ne voit pas le nom du réalisateur, seulement son id.

Un film possède toujours un réalisateur, on devrait donc pouvoir, pour chaque film, afficher le nom du réalisateur correspondant.

On réalise pour cela une jointure.

SELECT * FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste;

Et voilà !

On peut bien sûr limiter les colonnes, filtrer avec WHERE, trier, tout cela en même temps :

SELECT titreVO, année, nom, prénom FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste WHERE année > 2000 ORDER BY naissance;

ORDER BY doit être après WHERE

SELECT * FROM FILM, ARTISTE;

SELECT * FROM FILM, ARTISTE WHERE FILM.idReal = ARTISTE.idArtiste;