NSI Terminale

Contenus : Langage SQL : requêtes d’interrogation et de mise à jour d’une base de données

Capacités attendues :

    Identifier les composants d’une requête.
    Construire des requêtes d’interrogation à l’aide des clauses du langage SQL : SELECT, FROM, WHERE, JOIN.
    Construire des requêtes d’insertion et de mise à jour à l’aide de : UPDATE, INSERT, DELETE.

Commentaires : On peut utiliser DISTINCT, ORDER BY ou les fonctions d’agrégation sans utiliser les clauses GROUP BY et HAVING

Cours

1) introduction

Nous avons eu l'occasion d'étudier la structure d'une base de données relationnelle, nous allons maintenant apprendre à réaliser des requêtes.
C'est-à-dire que nous allons apprendre à :

  • créer une base des données,
  • créer des attributs,
  • ajouter des données,
  • modifier des données
  • et enfin, nous allons surtout apprendre à interroger une base de données afin d'obtenir des informations.

Pour réaliser toutes ces requêtes, nous allons devoir apprendre un langage de requêtes : SQL (Structured Query Language).
SQL est propre aux bases de données relationnelles, les autres types de bases de données utilisent d'autres langages pour effectuer des requêtes.

Pour créer une base de données et effectuer des requêtes sur cette dernière, nous allons utiliser le logiciel "DB Browser for SQLite" : https://sqlitebrowser.org/.

SQLite est un système de gestion de base de données relationnelle très répandu.
Noter qu'il existe d'autres systèmes de gestion de base de données relationnelle comme MySQL ou PostgreSQL.
Dans tous les cas, le langage de requête utilisé est le SQL (même si parfois on peut noter quelques petites différences).
Ce qui sera vu ici avec SQLite pourra, à quelques petites modifications près, être utilisé avec, par exemple, MySQL.

Pendant ce cours nous allons travailler avec les 2 tables (relations) suivantes :

Table AUTEURS

id nom prenom ann_naissance langue_ecriture
1 Orwell George 1903 anglais
2 Herbert Frank 1920 anglais
3 Asimov Isaac 1920 anglais
4 Huxley Aldous 1894 anglais
5 Bradbury Ray 1920 anglais
6 K.Dick Philip 1928 anglais
7 Barjavel René 1911 français
8 Boulle Pierre 1912 français
9 Van Vogt Alfred Elton 1912 anglais
10 Verne Jules 1828 français

Table LIVRES

id titre id_auteur ann_publi note
1 1984 1 1949 10
2 Dune 2 1965 8
3 Fondation 3 1951 9
4 Le meilleur des mondes 4 1931 7
5 Fahrenheit 451 5 1953 7
6 Ubik 6 1969 9
7 Chroniques martiennes 5 1950 8
8 La nuit des temps 7 1968 7
9 Blade Runner 6 1968 8
10 Les Robots 3 1950 9
11 La Planète des singes 8 1963 8
12 Ravage 7 1943 8
13 Le Maître du Haut Château 6 1962 8
14 Le monde des Ā 9 1945 7
15 La Fin de l’éternité 3 1955 8
16 De la Terre à la Lune 10 1865 10

2) requêtes d'interrogation

a) requêtes d'interrogation "simples"

Quand on désire extraire des informations d'une table, on effectue une requête d'interrogation à l'aide du mot clé SELECT. Voici un exemple de requête d'interrogation : 

SELECT id, titre, id_auteur, ann_publi, note
FROM LIVRES
Cette requête va nous permettre d'obtenir l'id, le titre, l'id de l'auteur, l'année de publication et la note de tous les livres présents dans la table LIVRES.

D'une façon générale, le mot clé SELECT est suivi par les attributs que l'on désire obtenir. Le mot clé FROM est suivi par la table concernée.

Il est possible d'obtenir uniquement certains attributs. Par exemple : 

SELECT nom, prenom
FROM AUTEURS
nous permettra d'obtenir le nom et le prénom de tous les auteurs présents dans la table AUTEURS.

À noter qu'il est possible d'obtenir tous les attributs sans être obligé de tous les noter grâce au caractère étoile * : 

SELECT *
FROM AUTEURS
sera équivalent à 
SELECT id, nom, prenom, ann_naissance, langue_ecriture
FROM AUTEURS

b) sélectionner certaines lignes : la clause WHERE

Il est possible d'utiliser la clause WHERE afin d'imposer une (ou des) condition(s) permettant de sélectionner uniquement certaines lignes.

La condition doit suivre le mot-clé WHERE : 

SELECT titre
FROM LIVRES
WHERE note > 9
La requête ci-dessus permettra d'afficher uniquement les titres qui ont une note strictement supérieure à 9 (soit "1984" et "De la Terre à la Lune")

Il est possible de combiner les conditions à l'aide d'un OR ou d'un AND : 

SELECT nom
FROM AUTEURS
WHERE langue_ecriture = 'français' AND ann_naissance > 1900
Cette requête permet d'obtenir le nom des auteurs nés après 1900 qui écrivent en français (soit ici "Barjavel" et "Boulle")

Il est aussi possible d'utiliser le OR à la place du AND : 

SELECT nom
FROM AUTEURS
WHERE langue_ecriture = 'français' OR ann_naissance > 1920
Cette requête permet d'obtenir le nom des auteurs qui écrivent en français (quelle que soit leur date de naissance) et des auteurs qui n'écrivent pas en français, mais qui sont nés après 1920 (soit ici "Barjavel", "Boulle", "Verne" et "K.Dick")

c) mettre dans l'ordre les réponses : la clause ORDER BY

Il est possible de classer les résultats d'une requête par ordre croissant grâce à la clause ORDER BY : 

SELECT nom
FROM AUTEURS
WHERE langue_ecriture = 'français' ORDER BY ann_naissance
La requête ci-dessus permettra d'obtenir le nom des auteurs écrivant en français classé en fonction de leur année de naissance (ici on obtiendra donc : "Verne", "Barjavel", "Boulle")

En rajoutant DESC, on obtient l'ordre décroissant : 

SELECT nom
FROM AUTEURS
WHERE langue_ecriture = 'français' ORDER BY ann_naissance DESC
On obtiendra ici : "Boulle", "Barjavel", "Verne"

À noter que si la clause ORDER BY porte sur une chaine de caractères, on obtient alors l'ordre alphabétique : 

SELECT nom
FROM AUTEURS
WHERE langue_ecriture = 'français' ORDER BY nom
On obtiendra : "Barjavel", "Boulle", "Verne"

d) la clause DISTINCT

Il est possible d'éviter les doublons dans une réponse grâce à la clause DISTINCT. Imaginons la table suivante :

Table MACHINES

numero type proprietaire
1 X23 Marc
2 Y43 Pierre
3 Z24 Kevin
4 Y44 Marc

La requête suivante :

SELECT proprietaire
FROM MACHINES
donnerait le résultat suivant : Marc, Pierre, Kevin, Marc

Nous avons donc un doublon : Marc apparait 2 fois

Pour éviter ce doublon, nous pouvons écrire : 

SELECT DISTINCT proprietaire
FROM MACHINES
qui nous donnera comme résultat : Marc, Pierre, Kevin

e) les jointures

Nous avons 2 tables, grâce aux jointures nous allons pouvoir associer ces 2 tables dans une même requête.

En général, les jointures consistent à associer des lignes de 2 tables. Elles permettent d'établir un lien entre 2 tables. Qui dit lien entre 2 tables dit souvent clé étrangère et clé primaire.

Analysons la requête suivante : 

SELECT *
FROM LIVRES
INNER JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id
Le "FROM LIVRES INNER JOIN AUTEURS" permet de créer une jointure entre les tables LIVRES et AUTEURS ("rassembler" les tables LIVRES et AUTEURS en une seule grande table). Le "ON LIVRES.id_auteur = AUTEURS.id" signifie qu'une ligne quelconque A de la table LIVRES devra être fusionnée avec la ligne B de la table AUTEURS à condition que l'attribut id_auteur de la ligne A soit égal à l'attribut id de la ligne B.

Par exemple, la ligne 1 (id=1) de la table LIVRES (que l'on nommera dans la suite ligne A) sera fusionnée avec la ligne 1 (id=1) de la table AUTEURS (que l'on nommera dans la suite B) car l'attribut id_auteur de la ligne A est égal à 1 et l'attribut id de la ligne B est aussi égal à 1.

Autre exemple, la ligne 1 (id=1) de la table LIVRES (que l'on nommera dans la suite ligne A) ne sera pas fusionnée avec la ligne 2 (id=2) de la table AUTEURS (que l'on nommera dans la suite B') car l'attribut id_auteur de la ligne A est égal à 1 alors que l'attribut id de la ligne B' est égal à 2.

Dans notre exemple l'attribut "id_auteur" de la tables LIVRES est bien une clé étrangère puisque cet attribut correspond à l'attribut "id" de la table "AUTEURS".

La requête ci-dessus permettra d'obtenir le résultat suivant :

id titre id_auteur ann_publi note id nom prenom ann_naissance langue_ecriture
1 1984 1 1949 10 1 Orwell George 1903 anglais
2 Dune 2 1965 8 2 Herbert Frank 1920 anglais
3 Fondation 3 1951 9 3 Asimov Isaac 1920 anglais
4 Le meilleur des mondes 4 1931 7 4 Huxley Aldous 1894 anglais
5 Fahrenheit 451 5 1953 7 5 Bradbury Ray 1920 anglais
6 Ubik 6 1969 9 6 K.Dick Philip 1928 anglais
7 Chroniques martiennes 5 1950 8 5 Bradbury Ray 1920 anglais
8 La nuit des temps 7 1968 7 7 Barjavel René 1911 français
9 Blade Runner 6 1968 8 6 K.Dick Philip 1928 anglais
10 Les Robots 3 1950 9 3 Asimov Isaac 1920 anglais
11 La Planète des singes 8 1963 8 8 Boulle Pierre 1912 français
12 Ravage 7 1943 8 7 Barjavel René 1911 français
13 Le Maître du Haut Château 6 1962 8 6 K.Dick Philip 1928 anglais
14 Le monde des Ā 9 1945 7 9 Van Vogt Alfred Elton 1912 anglais
15 La Fin de l’éternité 3 1955 8 3 Asimov Isaac 1920 anglais
16 De la Terre à la Lune 10 1865 10 10 Verne Jules 1828 français

À noter que pour éviter toute confusion il est souvent judicieux d'ajouter le nom de la table juste devant le nom de l'attribut : on écrira AUTEURS.id au lieu de simplement id, en effet, si on écrivait seulement id, il n'y aurait aucun moyen de distinguer l'id de la table LIVRES et l'id de la table AUTEURS. Je vous conseille d'adopter cette écriture systématiquement en cas de jointure, même quand cela n'est pas obligatoire (par exemple on aurait pu écrire id_auteur à la place de LIVRES.id_auteur puisqu'il y a uniquement un id_auteur dans la table LIVRES), cela vous permettra d'éviter certains déboires.

Dans le cas d'une jointure, il est tout à fait possible de sélectionner certains attributs et pas d'autres (aucune obligation de sélectionner tous les attributs des 2 tables : 

SELECT LIVRES.titre, AUTEURS.nom, AUTEURS.prenom
FROM AUTEURS
INNER JOIN LIVRES ON LIVRES.id_auteur = AUTEURS.id
On obtiendra alors une jointure uniquement avec l'attribut titre de la table LIVRES et les attributs nom, prenom de la table AUTEURS.

titre nom prenom
1984 Orwell George
Dune Herbert Frank
Fondation Asimov Isaac
Le meilleur des mondes Huxley Aldous
Fahrenheit 451 Bradbury Ray
Ubik K.Dick Philip
Chroniques martiennes Bradbury Ray
La nuit des temps Barjavel René
Blade Runner K.Dick Philip
Les Robots Asimov Isaac
La Planète des singes Boulle Pierre
Ravage Barjavel René
Le Maître du Haut Château K.Dick Philip
Le monde des Ā Van Vogt Alfred Elton
La Fin de l’éternité Asimov Isaac
De la Terre à la Lune Verne Jules

f) utilisation du WHERE dans les jointures

Suite à une jointure il est possible de sélectionner certaines lignes grâce à la clause WHERE : 

SELECT LIVRES.titre,AUTEURS.nom, AUTEURS.prenom
FROM LIVRES
INNER JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id
WHERE LIVRES.ann_publi>1965
On obtient avec cette requête le résultat suivant :

titre nom prenom
Ubik K.Dick Philip
La nuit des temps Barjavel René
Blade Runner K.Dick Philip

g) les jointures plus complexes

Pour terminer avec les jointures, vous devez savoir que nous avons abordé la jointure la plus simple (INNER JOIN). Il existe des jointures plus complexes (CROSS JOIN, LEFT JOIN, RIGHT JOIN), ces autres jointures ne seront pas abordées dans ce cours.

3) requêtes d'insertion

Il est possible d'ajouter une entrée à une table grâce à une requête d'insertion : 

INSERT INTO LIVRES
(id,titre,id_auteur,ann_publi,note)
VALUES
(17,'Hypérion',11,1989,8);
On emploie les mots clés INSERT INTO suivi de la table concernée (ici LIVRES). Ensuite on indique les noms des attributs que l'on désire ajouter (ici (id,titre,auteur,ann_publi,note)), et enfin pour terminer les valeurs de chaque attribut (ici (17,'Hypérion','Simmons',1989,8), attention à bien respecter l'ordre : 17 correspond à id, Hypérion correspond au titre, 11 correspond à id_auteur, 1989 correspond à ann_publi et 8 correspond à note).

4) requêtes de mise à jour

"UPDATE" va permettre de modifier une ou des entrées. Nous utiliserons "WHERE", comme dans le cas d'un "SELECT", pour spécifier les entrées à modifier. Voici un exemple de modification : 

UPDATE LIVRES
SET note=7
WHERE titre = 'Hypérion'
Cette requête permet de modifier la note du(des) livre(s) ayant pour titre Hypérion

5) requêtes de suppression

"DELETE" est utilisée pour effectuer la suppression d'une (ou de plusieurs) entrée(s). Ici aussi c'est le "WHERE" qui permettra de sélectionner les entrées à supprimer : 

DELETE FROM LIVRES
WHERE titre='Hypérion'
Cette requête permet de supprimer le(les) livre(s) ayant pour titre Hypérion

Attention à l'utilisation de cette requête DELETE notamment si on oublie le WHERE. Un :

DELETE FROM LIVRES
supprimerait toutes les entrées de la table LIVRES