LIF4-Cours-4-SQL2

De
Publié par

LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2Op´erations ensemblistesPermettent de combiner les r´esultats de plusieurs SELECT.LIF4 - Initiation aux Bases de donn´ees :Op´erateur :SQL - 2 " : UNION# : INTERSECTION$ : MINUSE.CoqueryPas de doubles (DISTINCT implicite).emmanuel.coquery@liris.cnrs.fr Les SELECT doivent contenir le mˆeme nombre d’attributs.Les noms des attributs sont ceux du premier SELECT.http ://liris.cnrs.fr/!ecoqueryC’est l’ordre des attributs qui compte.Seul le dernier SELECT peut contenir un ORDER BY.Les colones `a utiliser pour le tri sont pr´ecis´ees par leur num´eroet pas par leur attribut.LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2Exemple ExpressionsIl est possible d’utiliser des expressions plus complexes que simplesSch´ema :attributs.Employe1(Nom, Num, Fonction, NumSup, Embauche, Salaire, NumDept)Employe2(Nom, Num, F Numsup, NumDept)Entre autres :Fonctions et expressions arithm´etiquesListe des d´epartement ayant des employ´e dans 2 filiales dont lesFonctions sur les chaˆınes de caract`eresemploy´es sont donn´es par Employe1 et Employe2 :F sur les datesFonctions de conversion(SELECT NumDept FROM Employe1)INTERSECTIl existe ´egalement des fonctions de groupes permettant de traiter(SELECT NumDept FROM Employe2);plusieurs lignes `a la fois.LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux ...
Publié le : samedi 24 septembre 2011
Lecture(s) : 72
Nombre de pages : 7
Voir plus Voir moins

LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Op´erations ensemblistes
Permettent de combiner les r´esultats de plusieurs SELECT.LIF4 - Initiation aux Bases de donn´ees :
Op´erateur :
SQL - 2 " : UNION
# : INTERSECTION
$ : MINUS
E.Coquery
Pas de doubles (DISTINCT implicite).
emmanuel.coquery@liris.cnrs.fr Les SELECT doivent contenir le mˆeme nombre d’attributs.
Les noms des attributs sont ceux du premier SELECT.http ://liris.cnrs.fr/!ecoquery
C’est l’ordre des attributs qui compte.
Seul le dernier SELECT peut contenir un ORDER BY.
Les colones `a utiliser pour le tri sont pr´ecis´ees par leur num´ero
et pas par leur attribut.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple Expressions
Il est possible d’utiliser des expressions plus complexes que simples
Sch´ema :
attributs.
Employe1(Nom, Num, Fonction, NumSup, Embauche, Salaire, NumDept)
Employe2(Nom, Num, F Numsup, NumDept)
Entre autres :
Fonctions et expressions arithm´etiques
Liste des d´epartement ayant des employ´e dans 2 filiales dont les
Fonctions sur les chaˆınes de caract`eres
employ´es sont donn´es par Employe1 et Employe2 :
F sur les dates
Fonctions de conversion
(SELECT NumDept FROM Employe1)
INTERSECT
Il existe ´egalement des fonctions de groupes permettant de traiter(SELECT NumDept FROM Employe2);
plusieurs lignes `a la fois.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Expressions - 2 Quelques fonctions num´eriques
+ : unaire et binaire;
Ces expressions sont utilisables : $ : unaire et binaire;
& : multiplication et / : division;
Dans le SELECT :
ABS(e) : valeur absolue de e;le nom dans la relation r´esultat est en g´en´eral l’expression
COS(e) : cosinus de e avec e en radians;elle-mˆeme
% utiliser le renommage. SQRT(e) : racine carr´ee de e;
MOD(m,n) : reste de la division enti`ere de m par n,
Dans le WHERE : vaut 0 si n = 0;
permet d’exprimer des conditions plus complexes ROUND(e,n) : valeur arrondie de e `a n chi!res apr`es la
virgule, n optionnel et vaut 0 par d´efaut;
Dans le ORDER BY : TRUNC(e,n) : valeur tronqu´ee de e `a n chi!res apr`es la
il est ainsi possible de trier les lignes selon des valeur plus virgule, n optionnel et vaut 0 par d´efaut.
complexes que de simples attributs
Pour ROUND et TRUNC, si n est n´egatif cela indique des chi!res
avant la virgule.LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple Exemple - 2
Sch´ema :Sch´ema :
Employe(Nom, Num, Fonction, Num sup, Embauche,Employe(Nom, Num, Fonction, Num sup, Embauche,
Salaire, Commission, Num Dept)Salaire, Commission, Num Dept)
Donner la liste des commerciaux class´ee par rapport
Donner pour chaque commercial son revenu (salaire +
commission/salaire d´ecroissant.
commission) :
SELECT Nom, (Commission/Salaire) Rapport
SELECT Nom, (Salaire + Commission) Revenu FROM Employe
FROM Employe WHERE Fonction = ’commercial’
WHERE Fonction = ’commercial’; ORDER BY Commission/Salaire;
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple - 3 Fonctions sur les chaˆınes de caract`eres
CONCAT(e ,e ) : concat´enation de e et e1 2 1 2
Dans certains syst`emes, CONCAT peut prendre plus de deux
Sch´ema :
arguments.
Employe(Nom, Num, Fonction, Num sup, Embauche,
Dans certains syst`emes, CONCAT est repr´esent´e par
Salaire, Commission, Num Dept) l’op´erateur binaire ||.
REPLACE(e,old,new) : Renvoie e dans laquelle les
occurrences de old on ´et´e remplac´ees par new.Donner, avec leur salaire journalier arrondi au centime pr`es, la liste
UPPER(e) : convertit e en majuscules.des employ´es dont la commission est inf´erieure `a 50% du salaire.
LENGTH(e) : longueur de e.
INSTR(e,s) : donne la position de la premi`ere occurrence s
SELECT Nom, ROUND(Salaire/(22*12), 2) SJournalier
dans e.
FROM Employe
SUBSTR(e,n,l) ou SUBSTRING(e,n,l) : renvoie la
WHERE Commission <= Salaire * 0.5; sous-chaˆıne de e commen¸cant au caract`ere n et de longueur l
si l n’est pas pr´ecis´e, on prend la sous-chaˆıne du caract`ere n
jusqu’`a la fin de e.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Fonctions sur les dates Exemple
Oracle :
d +n ou d$n : d est une date, le r´esultat est d ±n jours.
Sch´ema :
ADD MONTHS(d,n) : ajoute n mois `a d.
Employe(Nom, Num, Fonction, Num sup, Embauche,
d $d : nombre de jours entre d et d .1 2 1 2 Salaire, Commission, Num Dept)
SYSDATE : date courante.
MySQL :
Donner nombre de jours depuis l’embauche de chaque employ´e.ADDDATE(d,INTERVAL n DAY) : ajoute n jours `a d.
DAY peut ˆetre remplac´e par SECOND, MINUTE, HOUR,
MONTH, ou YEAR.
SELECT Nom, DATEDIFF(SYSDATE(),Embauche)SUBDATE(d,INTERVAL n DAY) : similaire `a ADDDATE,
FROM Employe;mais e!ectue une soustraction.
DATEDIFF(d ,d ) : nombre de jour entre d et d .1 2 1 2
SYSDATE() : date courante.LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Fonctions de conversion Fonction de conversion - 2
ASCII(e) : renvoie le code ASCII du premier caract`ere de e.
Oracle :
CHR(e) : renvoie le caract`ere dont le code ASCII est e.
MySQL :
TO NUMBER(e) convertit la chaˆıne e en nombre.
CAST(e AS type) ou CONVERT(e,type) : convertit e en
TO CHAR(e,format) convertit e en chaˆıne de caract`eres. type.
e peut ˆetre un nombre ou une date; type peut ˆetre BINARY, CHAR, DATE, TIME, DATETIME,
format indique la forme que doit avoir le r´esultat. SIGNED, UNSIGNED
TO DATE(e,format) convertit une chaˆıne de caract`eres en
date.
format est un chaˆıne de caract`eres contenant une indication
sur la repr´esentation de la date.
ex : TO DATE(’12122003’,’ddmmyyyy’) donne la date
’2003-12-12’
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Ex´ecution na¨ıve Ex´ecution na¨ıve - 2
SELECT att , att , ...1 2SELECT att , att , ...1 2
FROM table , table , ...1 2FROM table , table , ...1 2
WHERE condition
WHERE condition
ORDER BY att , att , ...i jORDER BY att , att , ...i j
Les requˆetes imbriqu´ees dans le FROM sont ex´ecut´ees juste
R´ecup´eration des donn´ees dans le FROM avant la cr´eation du produit cart´esien.
' on obtient un produit cart´esien table (table ( ...1 2 Les requˆetes imbriqu´ees dans le WHERE sont ex´ecut´ees pour
Filtrage des n-uplets obtenus en utilisant la condition du chaque n-uplet `a tester.
WHERE
En r´ealit´e, le SGBD optimise l’ex´ecution des requˆetes.Tri des n-uplets restant suivant l’ordre sp´ecifi´e par ORDER BY
Par exemple, les sous-requˆetes dans le WHERE qui neCalcul des n-uplets indiqu´e dans le SELECT `a partir des
d´ependent pas de la requˆete principale ne seront ex´ecut´eesrestant n-uplets tri´es.
qu’une seule fois.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple Exemple - 2
Departement Batiment
Sch´ema : Num dept Nom dept Num bat Num bat Nom bat Ent princ Ent Sec
10 Marketing 1 1 Turing Nord OuestDepartement(Num dept, Nom dept, Num bat) 20 Developpement 2 1 Turing Nord
30 Direction 3 1 Turing Nord OuestBatiment(Num bat, Nom bat, Ent princ, Ent Sec) 10 Marketing 1 2 Einstein Ouest NULL
20 Developpement 2 2 Einstein Ouest NULL
30 Direction 3 2 Einstein Ouest NULL
10 Marketing 1 3 Newton Sud Nord
20 Developpement 2 3 Sud Nord
30 Direction 3 3 Newton Sud Nord
10 Marketing 1 4 Pointcarre Est NULLSELECT Nom dept, Batiment.Nom bat
20 Developpement 2 4 Prre Est
30 Direction 3 4 Pointcarre Est NULLFROM Departement, Batiment
WHERE Departement.Num bat = Batiment.Num bat
ORDER BY Nom dept; FROM Departement, BatimentLIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple - 2 Exemple - 3
Departement Batiment
Num dept Nom dept Num bat Num bat Nom bat Ent princ Ent SecDepartement Batiment
20 Developpement 2 2 Einstein Ouest NULLNum dept Nom dept Num bat Num bat Nom bat Ent princ Ent Sec
30 Direction 3 3 Newton Sud Nord10 Marketing 1 1 Turing Nord Ouest
10 Marketing 1 1 Turing Nord Ouest20 Developpement 2 1 Turing Nord
30 Direction 3 1 Turing Nord Ouest
10 Marketing 1 2 Einstein Ouest NULL
20 Developpement 2 2n ORDER BY Nom dept
30 Direction 3 2 Einstein Ouest NULL
10 Marketing 1 3 Newton Sud Nord
20 Developpement 2 3 Sud Nord
30 Direction 3 3 Newton Sud Nord
10 Marketing 1 4 Pointcarre Est NULL
20 Developpement 2 4 Pointcarre Est NULL
30 Direction 3 4 Pointcarre Est NULL Nom dept Num bat
Developpement Einstein
Direction Newton
Marketing Turing
WHERE Departement.Num bat = Batiment.Num bat
SELECT Nom dept, Batiment.Nom bat
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Regroupements Cons´equences du regroupement
SELECT att , att , ...1 2
FROM table , table , ...1 2
La requˆete ne renvoie qu’un seul n-uplet par groupe.
WHERE condition
GROUP BY att , att , ...k l
ORDER BY att , att , ...i j
Le SELECT et le ORDER BY ne peuvent utiliser que des
attributs pr´esents dans le GROUP BY.Le GROUP BY, ex´ecut´e apr`es le WHERE, indique de proc´eder `a
Dans un groupe, la valeur pour les attributs du GROUP BYune r´epartition du r´esultat en groupes de n-uplets :
est fixe, on peut donc l’utiliser.
Deux n-uplets sont dans un groupe s’il ont mˆemes valeurs sur En revanche, la valeur pour les autres attributs peut varier, ce
les attributs att , att , ...k l qui rend leur utilisation directe impossible.
(On ne saurait pas quelle valeur utiliser.)Si deux n-uplets sont dans deux groupes, alors il y a au moins
un attribut parmi att , att , ... pour lequel ils ont une valeurk l
di!´erente.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple Exemple - 2
Sch´ema : Employe(Nom, Num, Fonction, Salaire, Num Dept) SELECT Fonction
SELECT Fonction,Num Dept
FROM Employe
FROM Employe
GROUP BY Fonction, Num DeptGROUP BY Fonction, Num Dept
ORDER BY Num Dept;
Nom Num Fonction Salaire Num dept
Nom Num Fonction Salaire Num dept Bellot 13021 ingenieur 25000 20
Bellot 13021 ingenieur 25000 20 Jones 19563 20000 20
Dupuis 14028 commercial 20000 10 Brown 20663 ingenieur 20
LambertJr 15630 stagiaire 6000 20 Dupuis 14028 commercial 20000 10
Martin 16712 directeur 40000 30 Fildou 25631 10
Dupont 17574 gestionnaire 30000 30 LambertJr 15630 stagiaire 6000 20
Jones 19563 ingenieur 20000 20 Martin 16712 directeur 40000 30
Brown 20663 20 Dupont 17574 gestionnaire 30000 30
Lambert 25012 directeur 30000 20 Lambert 25012 directeur 20
Fildou 25631 commercial 20000 10 Soule 28963 25000 10
Soule 28963 directeur 25000 10LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple - 3 Exemple - 4
ORDER BY Num Dept
SELECT Fonction, Num Dept
Nom Num Fonction Salaire Num dept
Dupuis 14028 commercial 20000 10
Fonction Num deptFildou 25631 10
commercial 10Soule 28963 directeur 25000 10
directeur 10Bellot 13021 ingenieur 20
ingenieur 20Jones 19563 ingenieur 20000 20
stagiaire 20Brown 20663 ingenieur 20000 20
directeur 20LambertJr 15630 stagiaire 6000 20 30Lambert 25012 directeur 30000 20
gestionnaire 30Martin 16712 40000 30
Dupont 17574 gestionnaire 30000 30
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Fonctions d’agr´egation Exemple
Fonctions agissant sur un ensemble de valeurs atomiques.
Sch´ema : Employe(Nom, Num, Fonction, Salaire, Num Dept)
Utilisables en conjonction avec un GROUP BY pour combiner
les valeurs des attributs qui ne font pas partie du GROUP BY.
Donner le salaire moyen pour chaque fonction :
Utilis´ees dans le SELECT et dans le ORDER BY.
On ne peut pas les utiliser dans le WHERE.
SELECT Fonction, AVG(Salaire) SalaireMoyen
(Le where a lieu avant regroupement.)
FROM Employe
GROUP BY Fonction;
Par exemple, AVG(e) donne la moyenne de l’expression e pour
le groupe consid´er´e.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Fonctions d’agr´egation - 2 Exemple
COUNT(e) : Le nombre d’occurrences de e dans le groupe.
Les n-uplets pour lesquels e vaut NULL ne sont pas compt´es. Sch´ema :
* peut remplacer e. Compte alors le nombre de n-uplets du Employe(Nom, Num, Fonction, Salaire, Num Dept)
groupe.
Departement(Num dept, Nom dept, Num bat)
MAX(e) : La valeur maximale de e pour le groupe.
MIN(e) : La valeur minimale de e pour le groupe.
Donner pour chaque d´epartement le nombre de fonction di!´erentes
SUM(e) : La somme des valeurs de e pour le groupe. occup´ee dans ce d´epartement :
AVG(e) : La moyenne de l’´evaluation de e sur le groupe.
STDDEV(e) : L’´ecart-type de e pour le groupe.
SELECT Nom dept, COUNT(DISTINCT Fonction) NbFonctions
VARIANCE(e) : La variance de e pour le groupe. FROM Employe, Departement
WHERE Employe.Num dept = Departement.Num depte peut ˆetre pr´ec´ed´e du mot cl´e DISTINCT : dans ce cas, on
GROUP BY Departement.Num dept, Nom dept;´elimine les doublons.
Important pour COUNT, SUM, AVG, STDDEV et
VARIANCE.LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple - 2 S´election des groupes
SELECT att , att , ...1 2
FROM table , table , ...Sch´ema : Employe(Nom, Num, Fonction, Salaire, Num Dept) 1 2
WHERE condition
GROUP BY att , att , ...k l
Donner pour chaque d´epartement le ou les employ´es qui ont le plus
HAVING condition groupe
haut salaire :
ORDER BY att , att , ...i j
Le WHERE ne pour que sur les n-uplets individuels, avantSELECT Num dept, Nom, Salaire
regroupement.FROM Employe
La condition du HAVING pour sur les groupes et pas sur lesWHERE (Num dept, Salaire) IN
n-uplets individuels :(SELECT Num dept, MAX(Salaire)
Utilisation directe des attributs du GROUP BY possible.FROM Employe des autres `a travers les fonctions
GROUP BY Num dept);
d’agr´egation.
Ex´ecut´e entre le GROUP BY et le ORDER BY.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple Exemple - 2
SELECT Num Dept, COUNT(DISTINCT Fonction) NbFonctions
FROM Employe FROM Employe WHERE Salaire > 15000
WHERE Salaire > 15000
GROUP BY Num Dept
HAVING COUNT(*) > 2; Nom Num Fonction Salaire Num dept
Bellot 13021 ingenieur 25000 20
Nom Num Fonction Salaire Num dept Dupuis 14028 commercial 20000 10
Bellot 13021 ingenieur 25000 20 LambertJr 15630 stagiaire 6000 20
Dupuis 14028 commercial 20000 10 Martin 16712 directeur 40000 30
LambertJr 15630 stagiaire 6000 20 Dupont 17574 gestionnaire 30000 30
Martin 16712 directeur 40000 30 Jones 19563 ingenieur 20000 20
Dupont 17574 gestionnaire 30000 30 Brown 20663 20
Jones 19563 ingenieur 20000 20 Lambert 25012 directeur 30000 20
Brown 20663 20 Fildou 25631 commercial 20000 10
Lambert 25012 directeur 30000 20 Soule 28963 directeur 25000 10
Fildou 25631 commercial 20000 10
Soule 28963 directeur 25000 10
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple - 3 Exemple - 4
GROUP BY Num Dept HAVING COUNT(*) > 2
Nom Num Fonction Salaire Num dept Nom Num Fonction Salaire Num dept
Bellot 13021 ingenieur 25000 20 Bellot 13021 ingenieur 25000 20
Jones 19563 ingenieur 20000 20 Jones 19563 ingenieur 20000 20
Brown 20663 20 Brown 20663 20
Lambert 25012 directeur 30000 20 Lambert 25012 directeur 30000 20
Martin 16712 40000 30Martin 16712 40000 30
Dupont 17574 gestionnaire 30000 30 Dupont 17574 gestionnaire 30000 30
Dupuis 14028 commercial 20000 10Dupuis 14028 commercial 20000 10
Fildou 25631 10Fildou 25631 10
Soule 28963 directeur 25000 10Soule 28963 directeur 25000 10LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple - 5 Tout regrouper
Utilisation d’une fonction d’agr´egation sans GROUP BY :
SELECT Num Dept, COUNT(DISTINCT Fonction) NbFonctions
Provoque la cr´eation d’un groupe englobant tous les n-uplets
s´electionn´es.
Num dept NbFonctions Le SELECT ne peut alors contenir que des fonctions
10 2 d’agr´egation.
20 2
Utile pour obtenir des informations sur l’ensemble des lignes
s´electionn´ees.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2 LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple Double regroupement
Utilisation d’une fonction d’agr´egation au r´esultat d’une fonctionSch´ema :
d’agr´egation dans un SELECT :Employe(Nom, Num, Fonction, Salaire, Num Dept)
Possible uniquement dans une requˆete avec un GROUP BY.
Cette utilisation provoque deux regroupements :
Premier regroupement classique par le GROUP BYDonner le total des salaires du d´epartement 10 :
Deuxi`eme regroupement implicite duˆ `a la fonction d’agr´egation
dans le SELECT
SELECT SUM(Salaire)
FROM Employe
Remarque : non impl´ement´e dans MySQL, mais possibilit´e d’imiter
WHERE Num dept = 10;
ce comportement `a l’aide d’une requˆete imbriqu´ee.
LIF4 - Initiation aux Bases de donn´ees : SQL - 2
Exemple
Sch´ema :
Employe(Nom, Num, Fonction, Salaire, Num Dept)
Donner la taille du plus gros d´epartement en termes de nombre
d’employ´es.
SELECT MAX(COUNT(*)) SELECT MAX(NbEmp)
FROM Employe FROM ( SELECT COUNT(*) NbEmp
GROUP BY Num dept; FROM Employe
GROUP BY Num dept)
CountEmp;

Soyez le premier à déposer un commentaire !

17/1000 caractères maximum.