GoupillWiki

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Requêtes SQL ======

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

<WRAP info>EN 1970, Aurait dû s'appeler SEQUEL pour Strutuctured English QUEry Language, mais la marque SEQUEL existait déjà. SQL a donc été choisi en 1975.</WRAP>

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 [[https://sqlitebrowser.org/dl/|SQLiteBrowser]].

===== Exemple =====

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

{{ nsi:terminales:database:sql-1.png?direct&400 |}}

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.

<WRAP important>Nous allons utiliser des requêtes SQL pour ajouter des films, des acteurs... Évidemment, en situation réelle, on ne demandera pas à la personne chargée de rentrer les données, peut-être un simple internaute, d'écrire ces commandes complexes. Pour l'internaute ou l'utilisateur moyen, il faudra prévoir une interface, comme une page web avec des cases à remplir. Mais nous verrons cela plus tard, on commence avec les requêtes SQL brutes.</WRAP>

===== Créer les tables =====

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.

<WRAP tip>Dans ce qui suit, j'écrirais certaines commande en majuscules comme ''CREATE''. Ce n'est pas obligé, cela fonctionne aussi en minuscules.

On revient à la ligne pour plus de lisibilité mais on pourrait tout écrire à la suite. Le '';'' indique qu'une commande est complète.
</WRAP>

<code sql linenums>
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)
);
</code>

<WRAP box>Dans //sqlitebrowser//,
  * commencer par créer une bdd vide,
  * chercher "Éditer les pragmas" dans les onglets, cocher "Foreign keys" -- ainsi les clés étrangères seront prises en comtpe,
  * chercher "Exécuter le SQL" dans les onglets puis exécuter les requêtes SQL précédentes. Un message indique si la requête a abouti ou non.
</WRAP>

==== Les domaines = types de données ====

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''.

===== Insérer des données =====

==== Insertion d'un artiste ====

<code sql linenums>
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');
</code>

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

<WRAP tip>Dans sqlitebrowser, l'onglet "Parcourir les données" vous permet de voir que Bruce Willis a bien été ajouté dans la table ARTISTE.</WRAP>

==== Gestion des apostrophes ====

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.

<WRAP tip>
==== Préciser ou non la clé primaire ? ====

On a le choix : on peut décider de préciser un ''idArtiste''. Dans ce cas on pourra écrire

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

et on précise l'identifiant voulu **à condition qu'il ne soit pas déjà utilisé**.

Puisque dans la requête précédente figurent toutes les colonnes de la table, on peut se contenter de :

<code sql>
INSERT INTO ARTISTE VALUES(129, 'Jo', 'Bill', 'blablabla', '1955-03-19');
</code>

Dans les sujets de bac, l'identifiant est précisé en général. Mais dans un cas d'utilisation réelle, il sera sans doute plus pratique de laisser le SGBD automatiquement identifiant : sans cela il faudrait d'abord consulter la BDD pour savoir quel identifiant est disponible...
</WRAP>

==== Erreur si insertion de film sans idReal ====

Essayons maintenant une commande qui ne passe pas.

<code sql linenums>
INSERT INTO FILM(titreVO, titreVF, année) VALUES('Die Hard', 'Piège de Cristal', 1988);
</code>

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.

==== idReal est une clé étrangère ====

Cette autre commande échoue.

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

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.

<code sql linenums>
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');
</code>

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 :

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

==== insertion d'une association JOUEDANS ====

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

<code sql linenums>
INSERT INTO JOUEDANS(idArtiste, idFilm, rôle) VALUES (1,1,'Mc Lain');
</code>

===== Modification d'une donnée =====

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

<code sql linenums>
UPDATE ARTISTE SET naissance = '1951-01-08' WHERE idArtiste = 2;
</code>

Là encore la commande est assez explicite.

<WRAP important>Attention cependant au mot-clé ''WHERE'' qui permet de préciser quelle(s) ligne(s) de la table on souhaite modifier. On pourrait très bien modifier plusieurs lignes simultanément. Par exemple, on pourrait décider de mettre les noms des artistes en majuscules :

<code sql linenums>
UPDATE ARTISTE SET nom = UPPER(nom);
</code>

En l'absence de clause ''WHERE'' **toutes les lignes** sont modifiées. Il faut donc faire attention.
</WRAP>


Bien sûr, la commande suivante échoue :

<code sql linenums>
UPDATE FILM SET idReal = 25 WHERE titreVO = 'Die Hard';
</code>

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

<code sql linenums>
UPDATE FILM SET idReal = 1 WHERE titreVO = 'Die Hard';
</code>

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

//Remettez idReal à 2 pour la suite.//

===== Suppression d'une donnée =====

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

<code sql linenums>
DELETE FROM ARTISTE WHERE prénom = 'Bruce';
</code>

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// :

<code sql linenums>
DELETE FROM JOUEDANS WHERE idArtiste = 1 AND idFilm = 1;
</code>

===== Consulter la table =====

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.

<WRAP box>Une base est construite par des milliers d'insertions individuelles. On ne va donc pas le faire et nous allons continuer à travailler avec une base déjà faite.

Vous disposez du fichier {{ nsi:terminales:database:films.sql |}} qui contient toutes les commandes pour créer une base de donnée. Dans //sqlitebrowser// vous pouvez importer un fichier ''*.sql''. <wrap tip>Fichier -> Importer -> Base de données à partir du fichier SQL...</wrap>
</WRAP>

==== Sélection ====

=== cas le plus simple ===

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

<code sql linenums>
SELECT * FROM ARTISTE;
</code>

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

=== projection ===

<code sql linenums>
SELECT nom, prénom FROM ARTISTE;
</code>

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

=== clause WHERE ===

<code sql linenums>
SELECT nom, prénom FROM ARTISTE WHERE naissance > '1970-01-01';
</code>

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

=== clause LIKE ===

<code sql linenums>
SELECT nom, prénom FROM ARTISTE WHERE nom LIKE 'Bo%';
</code>

''LIKE'' signifie //RESSEMBLE//, ici on ne garde que les artistes dont le nom commence par "Bo".

=== doublons, clause DISTINCT ===

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.

<code sql linenums>
SELECT rôle FROM JOUEDANS;
SELECT DISTINCT rôle FROM JOUEDANS;
</code>

=== tri ===

Et on peut trier :
  * tri par naissance puis nom dans le premier cas,
  * ordre descendant dans le second cas -- mot clé ''DESC''.

<code sql linenums>
SELECT nom, prénom FROM ARTISTE ORDER BY naissance, nom;
SELECT nom, prénom FROM ARTISTE ORDER BY naissance DESC;
</code>

<wrap important>Dans le cas du requête avec ''WHERE'' et ''ORDER BY'', il faut placer ''ORDER BY'' après.</wrap>

=== compte ===

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

<code sql>
SELECT nom from ARTISTE;
</code>

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'' :

<code sql>
SELECT COUNT(nom) from ARTISTE;
</code>

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

<WRAP tip>On compte tous les noms apparaissant, même s'ils viennent en plusieurs exemplaires. La fonction ''COUNT'' ignorera les cas ''NULL'', c'est à dire les cases vides (en admettant que l'on ait autorisé qu'un artiste n'ait pas de nom renseigné).</WRAP>

<WRAP info>Pour aller plus loin, on pourrait préciser que l'on ne veut pas compter plusieurs fois un même nom. On ajoute alors le mot-clé ''DISCTINCT'' comme ceci : ''SELECT COUNT(DISTINCT nom) from ARTISTE''.

Plus loin encore : on pourrait produire une liste avec chaque nom associé avec le nombre d'artiste portant ce nom : ''SELECT nom, COUNT(idArtiste) FROM ARTISTE GROUP BY nom''.  Le mot-clé ''GROUP BY'' indique que l'on veut regrouper tous les artistes de même nom et la fonction ''COUNT'' se charge de compter les différents artistes groupés sous ce nom.</WRAP>
==== Jointure ====

Si je veux afficher les films

<code sql linenums>
SELECT * FROM FILM;
</code>

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**.

<code sql linenums>
SELECT * FROM FILM JOIN ARTISTE ON FILM.idReal = ARTISTE.idArtiste;
</code>

Et voilà !

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

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

<wrap important>ORDER BY doit être après WHERE</wrap>

<WRAP tip>**Autre façon de faire :** Si on écrit
<code sql linenums>
SELECT * FROM FILM, ARTISTE;
</code>
On obtient tous les couples ''(FILM, ARTISTE)'' même s'ils n'ont rien à voir l'un avec l'autre. Si on ne veut que les paires où l'artiste est le réalisateur du film, on peut préciser :
<code sql linenums>
SELECT * FROM FILM, ARTISTE WHERE FILM.idReal = ARTISTE.idArtiste;
</code>
Ce qui revient à peu près au même que la  jointure.

//Dans tous les cas, inutile de vous remplir la tête de code SQL. On souhaite surtout que vous sachiez écrire des requêtes simples et que vous arriviez à comprendre. La requête précédente, même si elle n'est pas facile de la trouver tout seul, ne devrait pas être trop difficile à comprendre.//
</WRAP>