LIF4-Cours-4-SQL2

Emmanuel Coquery

Informations

• Publié par: Foed
• Nombre de lectures: 80
• Langue: Français

Extrait

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).
[e-mail] 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.N