Cours

Informatique
Chapitre 5 : INITIATION AUX BASES DE DONNEES
Objectifs du chapitre
- Savoir prototyper et créer une base de données simple, à l'aide d'un outil interactif
- Savoir traduire les questions posées dans un langage de requête, consulter une base de données à travers des
requêtes de type SQL
I – INTERET DES BASES DE DONNEES
1/ Les limites de l'approche fichiers
Le responsable d'une bibliothèque peut être tenté de gérer les emprunts par un simple fichier sur un tableur. Les
premières lignes de ce fichier peuvent ressembler à ce qui suit :
On peut tout de suite relever différentes difficultés avec cette organisation :
- l'orthographe des différents noms de famille peut manquer de cohérence (Le Dray ou Ledray ?) ;
- même chose pour les titres des ouvrages : Mme ou Madame Bovary ?
- la recopie des numéros de téléphone est fastidieuse et comporte des risques d'erreur ;
- même l'entrée de la date peut poser problème (deuxième emprunt de Mme Bovary : 6 janvier 2011 ou 2010 ??).
Si le fichier comporte des milliers de lignes, il devient alors délicat de retrouver les prêts de M. Castel, ou encore de
chercher quels sont les ouvrages qui ont été prêtés depuis plus de 2 semaines. On dit que la structure du fichier de
gestion de la bibliothèque est "plate", car elle impose une lecture séquentielle complète du tableau pour chaque
interrogation. Les algorithmes de recherche simples sont alors très coûteux (en temps de calcul), ce qui les rend
inopérants.
En résumé :
Gestion impossible !
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-1-
Informatique
2/ Solution avec une base de données (BD) et un système de gestion de base de données (SGBD)
Le modèle relationnel des bases de données permet de résoudre ces problèmes d'intégrité et de cohérence des
données tout en assurant l'efficacité des recherches et interrogations.
Les systèmes de gestion de bases de données (SGBD) actuels permettent l'utilisation de quantités considérables de
données dans des bases partagées par de multiples utilisateurs et souvent disponibles en ligne.
La stratégie est la suivante :
Définition d'une Base de Données (BD) :
- ensemble d’informations exhaustif, non redondant,
structuré, persistant ;
- les informations sont disponibles pour plusieurs applications
en parallèle.
Définition d'un Système de gestion de Bases de Données (SGBD) :
- C'est un logiciel qui permet de décrire, modifier, interroger la base de données ;
- il fait l'interface entre les utilisateurs et les fichiers physiques de la base de données ; l'utilisateur dispose ainsi
facilement et immédiatement de données à jour.
Les SGBD sont des logiciels complexes, résultats de dizaines d’années de recherche et de développement. Ils permettent
à des individus ou des programmes d’exprimer des "requêtes" pour interroger des bases de données ou pour les
modifier. En TP, on se servira par exemple du logiciel libre très utilisé SQLite.
Sur l'exemple de la bibliothèque, le modèle relationnel conduit par exemple à créer 5 tables au lieu d'un fichier :
La table des Livres comporte une colonne
identifiant le livre, puis une autre identifiant
l'auteur par son numéro, et de même pour le
genre. Les titres ne sont pas répétés, ce qui
garantit la cohérence des données.
Même principe pour la table
des Auteurs.
La table des Emprunts permet de
trouver le numéro de l'emprunteur et
celui de l'ouvrage emprunté. La date de
rendu est éventuellement NULL si
l'ouvrage est encore dehors.
Table des emprunteurs : là
encore, la non répétition des
abonnés de la bibliothèque
permet
de
garantir
la
cohérence des données.
La table des genres complète
la base de données.
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-2-
Informatique
3/ Les architectures physiques des systèmes de gestion des données
• Une première architecture est celle des systèmes client/serveur. La base de données est gérée sur un serveur (SGBD +
fichiers physiques de la BD). L’application tourne sur une autre machine, le client. De plus en plus, cette architecture se
complique avec l’introduction d’un troisième "tiers", une machine qui gère l’interface, typiquement un navigateur Web
sur une tablette ou un PC portable. On a alors l'architecture trois-tiers.
• Ex. d'architecture 3 tiers : BD des notes du lycée
- application et serveur de BD : PRONOTE
- clients avec accès à l'interface : profs, élèves, administration.
• Nous pouvons noter différentes évolutions générées par des
améliorations dans les matériels disponibles :
– l’accroissement des performances, notamment fondées sur
les mémoires vives de plus en plus vastes ;
– l’utilisation de plus en plus de parallélisme massif dans des
grappes de machines pour traiter d’énormes volumes de
données. On parle parfois de "big data" ;
– pour simplifier la gestion de données, on tend à la déporter dans les nuages (le "cloud computing"), c’est-à-dire à
mettre ses données dans des grappes de machines gérées par des spécialistes.
II – LE MODELE RELATIONNEL ET SON VOCABULAIRE
• Un système de gestion de bases de données doit proposer un langage facilement utilisable par des êtres humains,
destiné à exprimer des "requêtes". Ces exigences forment le point de départ du modèle relationnel proposé par Ted
Codd, un chercheur d’IBM, dans les années 1970.
1/ Relation, attribut, n-uplet, schéma de relation
• Dans le modèle relationnel, les données sont organisées en tables à deux dimensions appelées des "relations",
notées R.
• Ex : Un extrait de base de données relationnelles :
Relations
Attribut
Schéma de relation
N-uplet d'arité 3 = triplet dans la relation SEANCE.
• Les noms des colonnes sont appelés "attributs", notés A.
Par exemple : Titre.
On note les attributs A1, A2, A3, A4… À chaque attribut est associé son domaine, qui est l'ensemble des valeurs qu'il peut
prendre : un entier, une chaîne de caractères, etc. On note Dom(Ai) le domaine de l'attribut Ai. On note R.Ai l'ensemble
des valeurs de l'attribut Ai de la relation R. Par exemple : SEANCE.Titre.
• Une base de données est constituée d'un nombre fini de relations : 1 par table. On dit que la relation R suit le
"schéma de relation" S = (A1, A2, A3,…). L'ensemble des schémas de relation de la base de données s'appelle le
"schéma relationnel".
Par exemple : (Titre, Réalisateur, Acteur) est le schéma de la relation FILM.
Pour marquer cette association, on désigne cette relation par FILM(Titre, Réalisateur, Acteur).
• Une ligne d'une relation est appelée "n-uplet" (tuple en anglais), noté t.
Un n-uplet est noté de la façon suivante : (Casablanca, M. Curtiz, Humphrey Bogart).
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-3-
Informatique
C'est donc un élément de Dom(A1) × Dom(A2) × Dom(A3) ×… L'ensemble des n-uplets de la table constitue la relation.
Deux n-uplets de la même relation diffèrent au moins sur un attribut.
Si t est le n-uplet (Les 400 coups, F. Truffaut, J.-P. Léaud), on note t[Réalisateur, Acteur] = (F. Truffaut, J.-P.
Léaud), t[Titre] = (Les 400 coups) et ainsi de suite. On peut aussi noter t.Titre = Les 400 coups.
• Une base de données est constituée d’un ensemble de relations R(S), chacune associée à son schéma de relation S.
2/ Clé primaire
• Pour une relation R(S), une clé est le plus petit ensemble d'attributs de R(S) qui permet de distinguer 2 n-uplets.
Par exemple, Titre, Réalisateur ou (Titre, Réalisateur) ne sont pas des clés de la relation FILM(Titre, Réalisateur,
Acteur). Par contre, Acteur est une clé, tant qu'on ne rajoute pas un autre film d'un des 4 acteurs !
Ex : Donner une clé pour la relation suivante :
• Une clé primaire est simplement un choix d'une clé.
Pour l'indiquer, on souligne les attributs correspondants dans la table. On la nomme souvent id, ce qui a été fait dans
quatre des cinq tables de la base de données de la bibliothèque p.2. Pour la table Emprunts, c'est le triplet (qui, quoi,
date) qui peut servir de clé primaire.
• Indiquer au SGBD une clé primaire pour chaque table permet une indexation des données à l'aide de cette clé, ce qui
renforce l'efficacité des procédures d'interrogation de la table. En effet, une clé primaire A permet d'identifier un nuplet t uniquement par la donnée de t.A.
Grâce à cela, on pourra créer des liens entre plusieurs relations.
3/ Comment interroger une base de données ?
• Les bases de données sont interrogées en utilisant comme langage le "calcul relationnel".
Le calcul relationnel s’appuie sur des noms qui représentent les relations comme FILM ou SEANCE, des entrées de
ces relations comme Star Wars, des variables comme ti, h, et des symboles logiques, (et), (ou),
(non),
(implique), (existe), (pour tout). À partir de ces ingrédients, des formules logiques, ou "requêtes", peuvent être
construites telles que :
{res(s, h)| ti, r ; (FILM(ti, r, "Humphrey Bogart")
SEANCE(ti, s, h))}
qui se lit :
"Donnez-moi les résultats couples (salle s, heure h) pour lesquels il existe un titre ti et un réalisateur r tels que le n-uplet
(ti, r, "Humphrey Bogart") se trouve dans la relation FILM, et le n-uplet (ti, s, h) dans SEANCE".
Les variables s et h sont dites libres, car on n'impose pas leur valeur : ce sont elles que l'auteur de la requête cherche à
connaître. On peut donc lire plus clairement : "Où et à quelle heure puis-je voir un film avec Humphrey Bogart ?".
• En pratique, les machines utilisent le langage SQL (pour Structured Query Language) qui exprime différemment les
mêmes questions. Par exemple la question précédente s’exprime en requête SQL comme :
SELECT Salle, Heure
FROM FILM ⋈ SEANCE
WHERE FILM.Titre = SEANCE.Titre AND Acteur = "Humphrey Bogart"
Que l'utilisateur de la base de données s’exprime en français ou qu’il utilise une interface graphique, le SGBD
transforme sa question en requête SQL.
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-4-
Informatique
III – LES OPERATEURS DE L'ALGEBRE RELATIONNELLE
• Alors que le calcul relationnel ou le SQL permettent de formuler une requête, l'algèbre relationnelle permet de
"calculer la réponse". Afin de manipuler un ensemble de relations, on doit en effet définir des opérateurs algébriques
opérant sur les relations.
• Dans tous les cas, les opérandes seront des relations, ou au moins une relation ; le résultat est une relation.
1/ Les opérateurs ensembliste, adaptés au contexte relationnel : union, intersection, différence
• Si deux relations ont le même schéma, alors il est possible d’appliquer un opérateur ensembliste sur ces relations.
• Union :
Soit le schéma de relation CE(Couleur, Etat) utilisé par un concessionnaire de voitures d'occasion. On a par exemple
les 2 relations suivantes :
CE1
et CE2
. Alors CE3 = CE1 CE2 donne :
L'union R1 R2 de 2 relations R1(S) et R2(S) produit une relation avec les n-uplets
compris dans R1 OU dans R2 (les doubles sont éliminés).
• Intersection
L'intersection R1
dans R2.
: CE4 = CE1
CE2 donne : CE4
R2 de 2 relations R1(S) et R2(S) produit une relation avec les n-uplets présents à la fois dans R1 ET
• Différence – : CE5 = CE1 – CE2 donne : CE5
La différence R1 – R2 de 2 relations R1(S) et R2(S) produit une relation avec les n-uplets de R1 qui ne sont pas
présents dans R2.
2/ Les opérateurs spécifiques de l'algèbre relationnelle
• Projection :
Opérandes : 1 relation et un ou des attributs.
Soit le schéma de relation CME(Couleur, Marque, Etat) utilisé par le concessionnaire de voitures d'occasion.
CME
La projection de CME selon les attributs (Couleur, Marque) donne :
Couleur, Marque(CME)
La projection de CME selon l'attribut (Couleur) donne :
Couleur(CME)
La projection Ai(R) d'une relations R(S) selon les attributs Ai donne la relation restant après conservation des
colonnes des attributs Ai et suppression des n-uplets devenus identiques.
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-5-
Informatique
• Sélection ou Restriction :
Opérandes : 1 relation et une condition.
La sélection de CME selon la condition Couleur = Jaune donne : σCouleur = Jaune(CME)
La sélection (ou restriction) A=a(R) d'une relations R(S) selon la condition A = a (avec a
restant après suppression des n-uplets ne satisfaisant pas la condition.
Dom(A)) donne la relation
Rq : La condition peut aussi se présenter sous la forme d'une inégalité.
Rq2 : La condition peut faire intervenir plusieurs attributs. On obtient alors une opération plus puissante :
Fruits
Pommes + Bananes(stock)
donne :
On peut ainsi vérifier l’intégrité de la relation (c’est-à-dire que le nombre de
fruits correspond bien à la somme des pommes et des bananes) et voir qu'il y a
une erreur dans la caisse 2.
• On peut commencer à écrire des sélections composées :
Dans la relation élève d'une base de données d'élèves de CPGE, on cherche la sélection
complexe correspondant à la condition :
"Soit le nom de l’élève est Michel mais son prénom n’est pas Jean, soit il est en PCSI 1".
Cette sélection se traduit par :
[
Nom = Michel(élève)
-
Prénom
Jean(élève)
]
Classe = PCSI 1(élève)
• Renommage :
Opérandes : 1 schéma de relation et un attribut.
Il est possible, souvent pour des raisons pratiques afin de lever une ambiguïté, de renommer un attribut Ai d’une
relation à l’aide d’un opérateur , dit de renommage. Les domaines du nouvel attribut Bi et de l'ancien Ai doivent être
les mêmes.
Le renommage Ai Bi(S) d'un schéma S donne une relation identique à la relation de départ, mis à part le schéma de
relation qui a été changé pour intégrer le nouveau nom d'attribut Bi.
On peut renommer plusieurs attributs en une seule opération avec A1, …, An
Avec l'exemple de la relation élève(Nom, Prénom) précédente : Nom, Prénom
• Produit cartésien x :
Opérandes : 2 relations.
Le produit cartésien de C
et E
B1, …, Bn(S).
LastName, FirstName(élève)
donne :
est CE6 = C x E
Le produit cartésien R1 x R2 de 2 relations R1(S) et R2(S) donne
une relation avec toutes les combinaisons possibles des n-uplets
de R1 et de R2.
• Division cartésienne :
Opérandes : 2 relations.
On peut dire qu'il s'agit de l'opération inverse du produit cartésien ; elle peut être formulée de la façon suivante :
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-6-
Informatique
La division cartésienne R1 R2 de la relation R1(A11, A12, …, A1n) par la (sous-)relation R2(A1p+1, …, A1n) est la relation
R3(A11, A12, …, A1p) formée des n-uplets qui, concaténés à chaque n-uplet de R2, donnent toujours un n-uplet de R1.
Le résultat de la division est une nouvelle relation qui a les attributs non communs à R1 et R2.
Par exemple : lors d'un meeting d'athlétisme, on veut savoir quel athlète a participé à toutes les épreuves. On a les
relations
et
. Alors, QUOTIENT = PARTICIPER EPREUVES donne :
Il ne peut pas y avoir Durand dans le quotient, car le couple (Durand, 200
m) n'est pas présent dans PARTICIPER. Idem pour Martin car (Martin,
400 m) ne fait pas partie de PARTICIPER.
• Jointure (symétrique) ⋈ :
Opérandes : 2 relations et une condition portant sur un attribut de la 1ère relation et sur un attribut de la 2nde.
Prenons les schémas de relation :
E(Nom, Dept, Fonction) des employés d'une entreprise :
et
L(Int, Lieu, NBEmp) des différentes
localisations de cette entreprise en France :
Alors la jointure EL = E ⋈ L(Dept = Int) donne :
La jointure R1 ⋈ R2(A1i = A2j) des schémas (A11, A12, …) et (A21, A22, …) selon (A1i, A2j) donne le produit cartésien R1
x R2 suivi de l’élimination des n-uplets ne satisfaisant pas la condition (sélection) sur A1i et A2j.
L'opérateur de jointure permet de "recoller" 2 relations : le résultat contient l’ensemble des n-uplets obtenus en
recollant un n-uplet de R1 et un n-uplet de R2 dans le cas où les attributs sélectionnés A1i, A2j y sont identiques.
Il est à noter que la présence des deux attributs A1i, A2j est redondante après jointure. On peut éliminer cette
redondance avec une projection : Nom, Dept, Fonction, Lieu, NBEmp (EL) :
On peut ne pas mettre de condition si 2 attributs de R1 et R2 portent déjà le même nom :
Soit les relations :
Alors R ⋈ S donne :
(il est implicite que la jointure se fait
selon (B, B)). On parle alors de jointure
naturelle.
• Agrégation :
Opérandes : une relation et un ou plusieurs attributs.
Le dernier concept (complexe !) à connaître est celui de l'agrégation. Prenons la relation suivante :
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-7-
Informatique
L’agrégation va permettre de regrouper les Elève d’une même Classe (ce groupe de nuplets est appelé un agrégat) et d’effectuer une opération sur chacun des agrégats. Par
exemple, on peut calculer la moyenne sur chaque Classe ce qui produit la relation :
Classe moyenne(Note)(relevé) :
Rq : on peut grouper selon plusieurs attributs à la fois.
On a utilisé ici une fonction moyenne pouvant être appliquée sur un nombre quelconque de valeurs d'un attribut,
indépendamment de leur ordre. On l'appelle fonction d'agrégation f ; cinq fonctions d'agrégation sont utilisées :
- comptage(A) associe à tout agrégat de n-uplets le nombre de valeurs de A ;
- max(A) (resp. min(A)) associe à tout agrégat de n-uplets la plus grande (resp. la plus petite) valeur de A ;
- moyenne(A) (resp. somme(A)) calcule la moyenne (resp. la somme) des valeurs de A pour chaque agrégat de n-uplets.
On considère un jeu d'attributs A i, Aj d'une relation R. On regroupe les n-uplets t de R qui ont les mêmes valeurs sur
les attributs Ai ; on obtient, pour chaque valeur commune t.Ai, un sous-ensemble de n-uplets. Pour chacun de ces
sous-ensembles, on applique la fonction d'agrégation choisie f sur les valeurs de l'attribut Aj. Le résultat est la relation
Ai f(Aj)(R).
3/ Savoir traduire dans le langage de l’algèbre relationnelle des requêtes écrites en langage courant
Pour traduire une requête, on commence par les opérations les plus simples (sous-requêtes) que l’on combine ensuite
pour former des opérations plus complexes.
1- Traduire les critères présents dans la requête sous forme de sélections.
2- Si nécessaire, utiliser des opérations ensemblistes pour combiner les sélections entre elles.
3- Lorsqu’une requête concerne des informations réparties dans plusieurs relations, effectuer une jointure pour
les rassembler en une seule relation.
4- Lorsqu’une requête demande d’effectuer un calcul sur un ensemble de valeurs, identifier la fonction
correspondante et effectuer une agrégation.
5- Utiliser des projections pour ne conserver que les informations utiles.
Cet ordre convient bien pour les requêtes courantes, mais il n’est pas forcément approprié pour des requêtes
complexes.
IV – LE LANGAGE SQL (Structured Query Language)
1/ Implémentation d'une BD avec un SGBD : choix de SQLite
• Les bases de données, par leur complexité et leur grande optimisation, nécessitent des logiciels dédiés, les SGBD. Il en
existe un grand nombre :
– des professionnels (puissants et payants) : Oracle, …
– des systèmes développés par des organisations à buts non lucratifs (universités), mais aussi largement utilisés par le
monde professionnel : PostgreSQL, MySQL, SQLite, …
• Contrairement aux autres SGBD, SQLite a l'intérêt de ne pas utiliser un schéma habituel clientserveur mais d'être directement intégré aux programmes applicatifs. L'intégralité des tables de la
BD est stockée dans un fichier indépendant de la plateforme ; il n'y a donc plus besoin de serveur.
SQLite est actuellement le moteur de base de données le plus distribué au monde, grâce à son utilisation dans de
nombreux logiciels grand public (Firefox, Skype) et dans les bibliothèques standards de nombreux langages comme PHP
ou Python.
De par son extrême légèreté, il est aussi employé sur les systèmes embarqués, notamment sur la plupart des
smartphones modernes : l'iPhone et les systèmes d'exploitation mobiles Symbian et Android l'utilisent.
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-8-
Informatique
2/ Utilisation du SGBD SQLite avec une interface graphique
• Un SGBD est une boite noire : il n’y a pas besoin de savoir comment il gère effectivement les données pour l’utiliser.
Au contraire, on lui délègue le travail difficile. Il n’est pas conçu pour être regardé directement par l’utilisateur : il ne
contient pas d’interface graphique, tout est caché (et c’est très bien).
• Respectant le modèle d'architecture 3-tiers, on l’utilise au travers d’une application, par
exemple un script Python qui va demander à la base des renseignements (ou écrire de nouvelles
informations, les mettre à jour, les traiter, …). Avant cela, pour administrer cette base, on utilisera
comme application une interface graphique de la base, par exemple SQLite Manager, qui est un
module complémentaire de Firefox.
3/ Un langage commun aux SGBD : le SQL
• Le langage SQL est le langage de programmation que parlent les BD. Il
permet de tout faire en ligne de commande, sans passer par l’interface
graphique : définition, interrogation et modification des données.
En SQL, on rencontre la dénomination suivante pour les objets manipulés
: tables (= relations), colonnes (= attributs) et lignes (= n-uplets).
• On peut écrire une requête SQL sur plusieurs lignes pour plus de lisibilité.
Schéma relationnel en exemple :
Notes(nom, prenom, cours, note)
Eleves(nom, prenom, email, classe)
Cours(intitule, …)
4/ Création et alimentation d'une BD
Création d'une table
• CREATE TABLE permet de fabriquer une table, avec la description précise de tous ses champs (type, valeur par
défaut, …).
Voici la requête SQL de création d'une table Notes(nom, prenom, cours, note) :
CREATE TABLE Notes (nom VARCHAR (80), prenom VARCHAR (80), cours VARCHAR (80), note INT,
PRIMARY KEY(nom, prenom, cours),
FOREIGN KEY(nom, prenom) REFERENCES Eleves(nom, prenom),
FOREIGN KEY(cours) REFERENCES Cours(intitule));
• Les types de données (pour les attributs) sont :
CHAR(n), VARCHAR(n) : chaîne de caractères fixe ou variable, de longueur maximale n ;
INT ou INTEGER : entiers
FLOAT : réels (flottants)
DATE, TIME, TIMESTAMP : "estampilles" temporelles.
Rq : On évite les accents comme d'habitude.
• Les clés sont définies de la manière suivante :
PRIMARY KEY définit la clé primaire (1 seule par table !), FOREIGN KEY définit une clé étrangère, qui sert à relier 2
tables entre elles (ici, (nom, prénom) est une clé étrangère qui relie la table Notes et la table Eleves, cours est une clé
étrangère qui relie la table Notes et la table Cours).
Destruction d'une table
DROP TABLE R; supprime la table R.
Ajout d'une colonne
ALTER TABLE R(Ai type_de_Ai); ajoute l'attribut Ai.
On ne peut pas supprimer directement une colonne, il faut définir une nouvelle table.
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
-9-
Informatique
Renommage d'une table
RENAME ancien_nom TO nouveau_nom;
Alimentation d'une table
• On insère un n-uplet de valeurs des attributs A1, A2, … de la manière suivante :
INSERT INTO Nom_de_la_table VALUES (valeur_de_A1, valeur_de_A2, …);
Rq : Le "--" permet de saisir des
commentaires.
Par exemple :
INSERT INTO Notes VALUES ("Vador ", "Dark ", "Pilotage", 20);
-- L'élève Dark Vador a eu 20/20 en pilotage
• On supprime un n-uplet avec :
DELETE FROM Nom_de_la_table WHERE A1 = valeur_de_A1 AND A2 = valeur_de_A2;
Par exemple :
DELETE FROM Notes WHERE nom = "Vador" AND prenom = "Dark";
-- supprime toutes les notes de Dark Vador
Sans WHERE, toutes les lignes sont mises à jour.
• On met à jour les valeurs d'une ou plusieurs colonnes avec :
UPDATE Nom_de_la_table SET A1 = nouvelle_valeur_de_A1 WHERE A2 = valeur_de_A2;
Par exemple :
UPDATE Notes SET note = note * 1.1 WHERE cours = "SII" -- Modifie les notes de SII et met à jour la table Notes.
5/ Les requêtes d'interrogation en SQL
• Le langage SQL est conçu pour pouvoir exprimer la plupart des opérateurs de l’algèbre relationnelle. Nous indiquerons
ici la syntaxe de ce langage pour faire des requêtes de recherche en présentant la traduction des opérations vues
précédemment.
• On peut toujours désigner l'attribut Ai d'une relation R en utilisant la notation préfixée : R.Ai . Cela permet, en cas de
nécessité, de distinguer des attributs qui portent le même nom dans différentes tables.
Requête pour la projection
• Pour effectuer la projection
SELECT Ai FROM R;
Ai(R),
on évalue la requête :
Il est possible d’obtenir l’intégralité de la relation grâce à l’utilisation du joker * à la place des attributs :
SELECT * FROM R;
-- Renvoie toutes les colonnes de R
Par exemple, pour la relation Notes(prenom, nom, cours, note) :
SELECT nom, note FROM Notes ;
-- Sélectionne les noms des élèves, ainsi que leurs notes
• La projection peut renvoyer des doublons. On peut éviter cela avec SELECT DISTINCT Ai FROM R;.
Requête pour la sélection
• En fait, la commande SELECT admet un paramètre supplémentaire WHERE permettant d’imposer une condition. La
sélection A=a(R) s’écrit donc :
SELECT * FROM R WHERE A = a;
Par exemple en combinant avec une projection :
SELECT nom, note FROM Notes WHERE note >= 10;
-- Sélectionne les noms et notes des élèves qui ont eu >10
• Pour les sélections composées il est possible d’utiliser directement les opérateurs booléens dans la condition.
Par exemple, la condition : "Soit le nom de l’élève est Skywalker mais son prénom n’est pas Luke, soit il est en TSI1" se
traduit en :
SELECT * FROM Eleves WHERE (nom = "Skywalker" AND prenom != "Luke") OR filiere = "TSI1";
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
- 10 -
Informatique
• Le mot-clé LIKE permet aussi d'écrire des conditions incomplètes : … WHERE nom LIKE "S%"; sélectionnera tous
les noms commençant par la lettre S.
Si la valeur d'un attribut n'est pas renseignée, on dit qu'il a la valeur NULL. Cette "valeur" peut être sélectionnée avec
… WHERE A IS NULL; ou à l'inverse : … WHERE A IS NOT NULL;
Requêtes pour les opérations ensemblistes
• Pour combiner dans un résultat unique des lignes venant d’interrogations différentes, on réalise des opérations
ensemblistes. On utilise les mots-clés UNION, INTERSECT ou EXCEPT pour réaliser les opérations d’union,
d’intersection et de différence. Pour rappel, les lignes doivent avoir rigoureusement le même nombre de colonnes avec
des types identiques. Les noms des colonnes du résultat sont ceux du premier SELECT.
Par exemple, pour effectuer R1
R2, on écrit :
SELECT * FROM R1 INTERSECT SELECT * FROM R2;
Renommage
• Pour effectuer le renommage d’un attribut, on "décore" une projection par le mot clé AS.
Si R est une relation de schéma (A1, … , An, C1, …, Cm) où l’on souhaite obtenir A1, …, An B1, …, Bn(S), on doit effectuer la
requête :
SELECT A1 AS B1, ..., An AS Bn, C1, ..., Cm FROM R;
On voit qu'il est nécessaire d’indiquer tous les attributs.
Requête de jointure
• La jointure R1 ⋈ R2(A1i = A2j) s’écrit :
SELECT * FROM R1 JOIN R2 ON A1i = A2j ;
Là encore, il s’agit d’une extension de la commande SELECT, et on peut donc directement la combiner avec une
projection (A1j à la place de *) voire une sélection (WHERE…).
Requêtes avec fonctions d'agrégation
• Les fonctions utilisées après agrégation se traduisent en SQL de la façon
suivante :
Pour calculer le résultat de la fonction f sur les valeurs de l'attribut A i de la
relation R, on utilise là encore une projection spéciale :
SELECT f(Ai) FROM R;
• Il est possible de coupler ce calcul à une projection / sélection / renommage comme on l’a vu précédemment, par
exemple :
SELECT MAX(note) AS notemax FROM Eleves WHERE filiere = TSI1;
-- Renommage de la note max. des
élèves de la classe TSI1
ou encore avec une "sous-requête" :
SELECT * FROM Eleves WHERE note >= (SELECT AVG(note) FROM Eleves); -- Elèves avec note > moy. des notes
• Enfin, le regroupement avant application de la fonction d'agrégation, c'est-à-dire Ai
SELECT f(Aj) FROM R GROUP BY Ai;
f(Aj)(R),
s'écrit avec GROUP BY :
Par exemple :
SELECT nom, prenom, AVG(note) FROM Notes GROUP BY nom, prenom; -- calcule la moyenne des notes de tous
les cours pour chaque élève
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
- 11 -
Informatique
ou encore en spécifiant une condition avec HAVING (remplace WHERE, mais ne porte que sur des caractéristiques de
groupe) :
SELECT nom, prenom, AVG(note) FROM Notes GROUP BY nom, prenom, HAVING AVG(note) > 10;
-- ne garde que les élèves qui ont une moyenne supérieure à 10
• Une requête cumulant WHERE, HAVING, GROUP BY est évaluée dans l'ordre suivant :
1 – Application de WHERE : sélectionne des lignes
2 – Application de GROUP BY : constitution des groupes
3 – Application de HAVING : sélection des groupes
4 – évaluation des fonctions d'agrégation sur les groupes constitués.
Sous-requêtes
• On peut bien entendu combiner les requêtes grâce aux sous-requêtes :
Toujours avec le schéma Notes(nom, prenom, cours, note) et Eleves (nom, prenom, email, classe) :
SELECT nom FROM Eleves WHERE nom IN (SELECT nom FROM Notes); -- Combine 2 requêtes pour avoir le
nom des élèves qui ont une note
Permet de chercher dans l'ensemble de lignes renvoyé par la sous-requête.
6/ Savoir traduire une question dans le langage de requête SQL en respectant sa syntaxe
• Le principe reste le même que pour la traduction d’une question dans le langage de l’algèbre relationnelle : construire
la requête en commençant par les opérations les plus simples que l’on combine en opérations complexes. La
construction de requêtes élaborées est facilitée par le fait que les commandes SQL réalisent simultanément plusieurs
opérations.
• Les requêtes SQL ne sont pas sensibles à la casse, mais il est d’usage de mettre les mots-clés en majuscules et les
attributs en minuscules. Les valeurs des attributs non numériques sont écrites entre guillemets simples ou doubles.
• On note que :
- toutes les requêtes commencent par le mot-clé SELECT qui effectuera une projection en fin de requête ;
- la relation sur laquelle on opère est précisée par FROM ;
- les requêtes SQL se terminent systématiquement par un point-virgule.
- Attention au faux ami, SELECT n’effectue pas une sélection !
Sources :
- Serge Abiteboul, Benjamin Nguyen, Yannick Le Bras, "Introduction aux bases de données relationnelles", 2014
- Benjamin Wack et al., "Informatique pour tous en classes préparatoires aux grandes écoles", Eyrolles, 2013
- Laurent Chéno, IG Mathématiques, Introduction aux bases de données et au langage SQL, 2013
- Sylvain Bouveret & Noha Ibrahim, "Introduction aux Systèmes de Gestion de Bases de Données", ENSIMAG 2013
- Stéphane Devismes, "Base de données", POLYTECH GRENOBLE 2013
- Python et bases de données, Formation continue SUPELEC 2013
CPGE TSI – Lycée P.-P. Riquet – St-Orens de Gameville
- 12 -