Langage de manipulation de données : obtention d`informations

PCSI 1
Dumont d’Urville
2013-2014
Langage de manipulation de données : obtention d’informations, langage SQL
Un peu d’histoire
Les bases de données relationnelles sont issues des travaux d’IBM durant la période des années 60. C’est E.F.
Codd, directeur du centre de recherches d’IBM à San José, qui invente en 1970 ( article publié à cette date) le
modèle relationnel que l’on utilise.
De 1974 à 1979, IBM travaille sur un prototype de bases de données relationnelles reposant sur la théorie de
Codd, et le langage de programmation de ce système est appelé SEQUEL ( Structured English Query Langage
en anglais).
En s’enrichissant, ce langage sera rebaptisé SQL ( pour des raisons phonétiques)
De 1978 à 1986, plusieurs langages concurrents sont développés par différentes sociétés ( Oracle, TeraData,
RTI...)
En 1986 ,l’ANSI ( American National Standard Institute) normalise pour la première fois le langage SQL, et
le SQL est adopté comme norme internationale en 1987 par l’organisation internationale de normalisation (
International Organisation for Standardization - ISO)
Microsoft n’entre sur le marché des éditeurs de SGBD ( Système de Gestion de Bases de Données) qu’en 1994.
Le succès du langage SQL est dû en grande partie à sa normalisation.
Phénomène rare dans le domaine de l’informatique, ce langage a été adopté paar pratiquement tous les éditeurs
de SGBD commerciaux.
Catégories d’instructions du langage SQL
Les instructions du langage SQL sont regroupées en cinq catégories en fonction de leur utilité et des entités
manipulées.
Le langage de définition des données ( Data Definition Language, soit DDL) permet de créer, modifier
et supprimer des relations dans des bases de données.
Les instructions du DDL sont : CREATE, ALTER, DROP , RENAME , TRUNCATE .
Le langage de manipulation des données ( Data Manipulation Language, soit DML en anglais) permet
l’extraction, l’ajout, la suppression et la modification des données de la base de données.
Les instructions du DML sont : SELECT , INSERT, UPDATE, DELETE.
Le langage de protection d’accès ( Data Control Language, soit DCL en anglais) s’occupe de gérer les
privilèges accordés aux utilisateuurs, comme les droits d’accès aux tables.
Les instructions du DCL sont GRANT, REVOKE.
Les deux autres catégories sont le langage de contrôle des transactions ( TCL) et le SQL intégré, qui ne sont
pas à notre programme.
Les bases de données servant pour les exemples sont :
• la base cinéma, dont le schéma relationnel est le suivant :
personne(idPersonne,nom,prenom)
film( idFilm,idRéalisateur,titre,genre,année)
jouer(idActeur,idFilm,rôle)
cinema ( idCinema,nom,adresse)
projection( idCinema,idFilm,jour)
Avec les clés étrangères suivantes :
dans film : idRéalisateur fait référence à la relation personne
dans jouer : idActeur fait référence à la relation personne et idFilm fait référence à la relation film
dans projection : idCinema fait référence à la relation cinema et idFilm fait référence à la relation film
• la base eleves dont le schéma relationnel est
eleves( nom, prenom, date_naissance, notemaths, option)
Interroger une base de données (DML)
La structure de base est le bloc de qualification :
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
;
A1 , A2 , · · · , An
R
F
A
H
T
−−
−−
−−
−−
−−
−−
colonnes
relation
assertion
regroupement
assertion
tri
Projection et sélection simple
Projection simple :
SELECT A1 , A2 , · · ·
FROM R ;
Exemple :
SELECT genre FROM film ;
Attention, cela renvoie toutes les lignes, éventuellement égales. Si on veut traduire l’opérateur de projection π,
il faut écrire
SELECT DISTINCT genre FROM film ;
Sélection simple :
SELECT *
FROM R
WHERE F ;
traduit σ(F ) R.
Exemple :
SELECT * FROM film WHERE genre=’Policier’ ;
Le caractère étoile (*) récupère tous les attributs de la table.
Si on ne veut que certains attributs, on les précise.
On n’oublie pas les apostrophes autour des chaines de caractères.
On peut modifier l’affichage des colonnes que l’on sélectionne de la manière suivante :
SELECT nom, notemaths/2 FROM eleves ;
On peut aussi préciser un nouveau nom ( valable uniquement à l’affichage de cette requête) pour cette colonne :
SELECT nom, notemaths/2 AS note_sur_10 FROM eleves ;
Il est également possible d’utiliser l’opérateur | | ( double barre verticale) pour concaténer des champs de type
caractères.
SELECT ’ La date de naissance de ’ | | prenom | | ’ ’ | | nom | | ’ est ’ | | date_naissance FROM eleves ;
Expression logique de sélection : clause WHERE F :
F est une assertion concernant des constantes et des noms de colonnes de la relation R.
Différents opérateurs peuvent intervenir :
• < , <=, = , >, >=, !=
• AND, OR, NOT
•
•
•
•
•
•
IN
BETWEEN
LIKE ou ∼ ou SIMILAR TO
IS NULL
IS NOT NULL
et bien d’autres
Exemples :
• SELECT nom FROM eleves WHERE notemaths BETWEEN 10 and 12 ;
• tous les noms des élèves dont le nom contient la lettre a
SELECT nom FROM eleves WHERE nom ∼0 a0 ;
• tous les noms des élèves dont le nom contient la lettre a ou A
SELECT nom FROM eleves WHERE nom ∼ ∗0 a0 ;
• tous les noms des élèves dont le nom contient la séquence on ou an
SELECT nom FROM eleves WHERE nom ∼0 [oa]n0 ;
• tous les noms des élèves dont le nom ne contient pas la lettre u et ayant une note de maths supérieure à 10.
SELECT nom FROM eleves WHERE nom ! ∼0 u0 AND notemaths >10 ;
Pour plus de précisions sur ∼, aller sur Google " expressions POSIX".
Tri des colonnes :
On peut trier les résultats d’une requête grâce à ORDER BY, ASC et DESC
Exemple :
SELECT * FROM eleves ORDER BY notemaths DESC ;
Produit et jointure
Lorsqu’on a effectue une requête croisée ( on a besoin d’informations dans plusieurs relations), il faut faire
figurer les relations nécessaires après la clause FROM.
On peut le faire de deux manières différentes, l’une en effectuant le produit cartésien, l’autre en effectuant une
jointure.
Il est préférable d’utiliser une jointure, c’est moins couteux en temps de calcul.
Le produit cartésien est simplement remplacé par une virgule :
R1 × R2 s’écrit SELECT * FROM R1 , R2 ;
La jointure peut se traduire par des syntaxes différentes, mais nous n’utiliserons que celle-ci :
SELECT * FROM R1 JOIN R2 ON condition ;
La clause ON correspond à la condition de jointure la plus générale. La condition est une expression logique
de même nature que celle de la clause WHERE. C’est souvent une égalité entre deux attributs.
Exemple : Quels sont les noms et prénoms des réalisateurs ?
SELECT personne.nom, personne.prenom
FROM personne JOIN film ON personne.idPersonne = film.idRealisateur ;
On peut écrire de manière plus condensée en utilisant des alias pour les noms des relations :
SELECT P.nom, P.prenom FROM personne (AS) P JOIN film (AS) F ON P.idPersonne=F.idRealisateur ;
AS est optionnel : on peut l’écrire ou pas .
FROM personne P ou
FROM personne AS P
Ce renommage n’est effectif qu’à l’intérieur de cette requête, et P doit remplacer personne dans toute la requête.
Opérations ensemblistes
Les opérations INTERSECT, UNION et EXCEPT permettent de travailler avec des relations ayant le même
schéma.
Ils traduisent l’intersection, la réunion et la différence de l’algèbre relationnelle.
Si R1 et R2 ont le même schéma :
R1 ∪ R2 s’écrit SELECT * FROM R1 UNION SELECT * FROM R2 ;
R1 ∩ R2 s’écrit SELECT * FROM R1 INTERSECT SELECT * FROM R2 ;
R1 − R2 s’écrit SELECT * FROM R1 EXCEPT SELECT * FROM R2
Opérateurs d’agrégation
Ces opérateurs permettent d’effectuer des opérations arithmétiques dur les résultats :
• COUNT compte les valeurs d’une colonne
• COUNT(*) compte les lignes d’une table
• SUM additionne les valeurs d’une colonne numérique
• AVG calcule la moyenne des valeurs d’une colonne
• MIN extrait la plus petite valeur d’une colonne
• MAX extrait la plus grande valeur d’une colonne
On peut utiliser DISTINCT dans les requêtes
SELECT AVG(notemaths) FROM eleves ;
Regroupements ou agrégats
On peut partitionner une table suivant certains attributs : la clause GROUP BY A1 , A2 · · · permet de définir
les regroupements qui sont projetés dans la table résultat où chaque regroupement se traduit par une ligne.
On applique en général un opérateur d’agrégation à chacun des groupes obtenus.
SELECT A1,OP(A2) FROM R WHERE F GROUP BY P ;
SELECT genre, COUNT(titre) FROM film GROUP BY genre
On peut imposer un critère de sélection aux regroupements : en utilisant la clause HAVING.
Le prédicat dans la clause HAVING suit les mêmes règles de syntaxe qu’un prédicat figurant dans une clause
WHERE. Cependant, il ne peut porter que sur des caractéristiques du groupe.
Du plus simple :
SELECT option, AVG(notemaths) FROM eleves
GROUP BY option
HAVING nom ∼ ’[ae]’ ;
au plus compliqué
SELECT nom,prenom,COUNT(DISTINCT idFilm ) AS ’nombre_film’
FROM personne JOIN jouer ON personne.idPersonne=jouer.idActeur
JOIN film ON jouer.idFilm=film.idFilm
GROUP BY nom,prenom
HAVING COUNT(DISTINCT idFilm)>1
ORDER BY nom
Que fait cette requête ? ? ?
Dernier exemple : base de données Colles de schéma relationnel
eleves ( id, nom, prenom )
profs ( id, nom, prenom )
colles ( idprof,ideleve, semaine, note )
Requête :
SELECT ideleve,eleves.nom, COUNT(*) AS bonnes_colles
FROM eleves JOIN colles ON ideleve=eleves.id
WHERE note>15
GROUP BY ideleve HAVING bonnes_colles >=5
ORDER BY bonnes_colles ;