Réda DEHAK [email protected].

fr
 SQL
• SQL : Structured Query Language (Langage d’interrogation
structuré)
• Langage de gestion de bases de données relationnelles pour :
– Interroger une base de données
– Mettre à jour les informations contenues dans les tables de la base
de données (LMD : Langage de Manipulation des Données)
– Définition et Modification du schéma de la base de données (DDL :
Langage de Définition des Données)
– Gestion des droits d’accès (DCL : Langage de Contrôle de l’accès)
– Gestion de la concurrence des opérations (TCL : Langage de
contrôle des transactions)

2
 Subdivisions du SQL

• Définition des données : DDL

• Manipulation des données : DML
• Gestion des droits d’accès : DCL
• Gestion des transactions : TCL
• Programmation dynamique.

3
 SQL

TCL
SET TRANSACTION, COMMIT, ROLLBACK

DML DCL
DDL
SELECT, CONNECT,
CREATE,
INSERT, GRANT,
ALTER, UPDATE, REVOKE
DROP DELETE

Programmation SQL
DECLARE, FETCH, PREPARE,
DESCRIBE, EXECUTE
4
 SQL

• SQL est insensible à la casse (majuscule/minuscule) des

caractères.
• SQL est insensible à la césure comme à l’indentation.
• Commentaires :
/* */
Commencer la ligne par - -
• Chaîne de caractères : suite de caractères entre deux
apostrophes .
• Noms des objets : doivent commencer par une lettre et ne
contenir que des caractère de A à Z, les chiffres de 0 à 9 et le
caractère souligné _. (norme : max = 128 caractères) .
• Liste : la virgule est utilisée comme séparateur pour les
éléments de la liste.

5
 Type de données

Type SQL

Chaîne de Temporels Nombres Chaînes

caractères de bits

DATE TIME
REELS ENTIERS BIT
ASCII TIMESTAMP
BIT VARYING
EXACT INTEGER
UNICODE
CHAR SMALLINT
DECIMAL
VARCHAR NCHAR APPROCHES
NCHAR VARYING
REAL FLOAT
DOUBLE PRECISION 6
 Type alphanumérique

• ASCII :
– CHARACTER(N) : CHAR(N)
– CHARACTER VARYING(N): VARCHAR(N)
• UNICODE :
– NATIONAL CHARACTER(N) : NCHAR(N)
– NATIONAL CHARACTER VARYING(N) : NCHAR
VARYING(N)
• Exemples :
nom char(32)
observation varchar(3200)
Attention : Les types CHAR(N) et NCHAR(N) complètent les
données par des blancs si la taille réelle est inférieure à N
7
 Type numérique

1. ENTIER :
• INTEGER : INT
Entier long, souvent compris entre -2147483648 et +2147483647
• SMALLINT :
Entier court, souvent compris entre -32768 et 32767
Exemples :
age smallint
quantite int

8
 Type numérique

2. Réels :
• DECIMAL(N, M) : DEC, NUMERIC
Valeurs décimales exactes
• FLOAT(N, M) :
Réel représenté sous forme binaire.
• REAL(N, M) :
Réel plus petit que float en nombre de bits (précision ↓)
• DOUBLE PRECISION(N, M) :
Réel plut grand que float en nombre de bits
Exemples :
solde NUMERIC (15, 2)
15 chiffres significatifs dont 2 après la virgule
somme FLOAT (10, 5)
9
 Type temporels

– DATE : date du calendrier grégorien entre le 1 janvier 1 et

31 décembre 9999.
– TIME : Temps sur 24 heures
– TIMESTAMP : DATE + TIME. (DATETIME)
– INTERVAL : Durée
• Exemples :
date_naiss DATE
format iso : ‘aaaa-mm-jj’
heure_depart TIME
format iso : ‘hh:mm:ss.nnn’
Arrive TIMESTAMP
format iso :’aaaa-mm-jj hh:mm:ss.nnn’
10
 Type intervalle

• INTERVAL precision_min [ TO precision_max ]

• Precision :
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}
• Exemples :
jours INTERVAL DAY
trimestre INTERVAL MONTH TO DAY
tache INTERVAL HOUR TO SECOND
INTERVAL 7 DAY
INTERVAL ‘2:15:30’ HOUR TO SECOND

11
 Type chaîne de bits

• BIT(N) : chaîne de bits de longueur N

• BIT VARYING(N) : chaîne de bits de longueur N.
• Exemples :
couleur BIT(3)

12
 Type BLOB
• BLOB : Binary Large OBjects
• Famille de type :
– Image
– Son
– Vidéo
– …
• D’autres types courants :
– TEXT : MEMO, LONGTEXT, …
– BINARY : données hexadécimales
– GRAPHIC : image dans un format particulier.

13
 SQL3

• BOOLEAN ou LOGICAL :
– Logique à trois états : true, false and NULL
• MONEY :
– Un type numeric avec une échelle maximale et une précision
spécifique (en général 4 chiffres après la virgule)
• AUTOINC : ROWID, SERIAL
– Entier à incrémentation automatique dont la valeur est
incrémentée à chaque ajout d’une nouvelle ligne dans la table.

14
 Création de table
CREATE [ OR REPLACE] TABLE nom_table
( colonne | contrainte_de_table [ { , colonne | contrainte_de_table }... ] )
colonne ::= nom_colonne { type | domaine } [ DEFAULT valeur_default ]
[ contrainte_de_colonne... ]
contrainte_de_colonne ::= [CONSTRAINT nom_contrainte]
[NOT] NULL
| UNIQUE | PRIMARY KEY
| CHECK ( prédicat_de_colonne )
| FOREIGN KEY [colonne] REFERENCES table (colonne)
[ON [DELETE | INSERT| UPDATE] [CASCADE | NO ACTION | SET
[DEFAULT|NULL] ] ]
contrainte_de_table ::= CONSTRAINT nom_contrainte
{ UNIQUE | PRIMARY KEY ( liste_colonne )
| CHECK ( prédicat_de_table )
| FOREIGN KEY liste colonne REFERENCES nom_table (liste_colonne) }
[ON [DELETE | INSERT| UPDATE] [CASCADE | NO ACTION | SET [DEFAULT
| NULL] ] ] 15
 Exemple

CREATE TABLE Article

( ref CHAR(8) NOT NULL, /* Attribut obiligatoire */
nom VARCHAR(10),
prix NUMERIC(9,2),
dateAchat DATE DEFAULT sysdate); /* valeur par
defaut*/
)

16
 Contraintes d’intégrité

• Une contrainte d’intégrité est une contrainte que

doivent vérifier les données d’une table

• Une commande est annulée par le SGBD si son

exécution viole une des contraintes

17
 Types de contraintes d’intégrité

• PRIMARY KEY : clé primaire

• FOREIGN KEY … REFERENCES : clé étrangère
• UNIQUE : 2 lignes ne peuvent avoir la même valeur
pour les colonnes spécifiées
• CHECK : contrainte de domaine, ou autre ; porte sur
une seule ligne

• 2 types de contraintes :
– contrainte de colonne (concerne une seule colonne)
– contrainte de table

18
 Définition des contraintes

• Les contraintes sont définies dans les commandes

CREATE (ou ALTER) TABLE
– à l'intérieur des définitions de colonnes pour les contraintes de
colonne
– au même niveau que les définitions de colonnes pour les
contraintes de table

CONSTRAINT nomContrainte définitionContrainte

19
 Clé primaire
• Si la clé primaire n’est formée que d’une seule colonne, le plus
simple est d’ajouter une contrainte de colonne :
CREATE TABLE Client (
nclt INTEGER CONSTRAINT PK_Client PRIMARY KEY,
…

• Sinon, il faut ajouter une contrainte de table:

CREATE TABLE Cmd (
Erreur si on déclare
nclt INTEGER,
nclt et nprod en cle
nprod INTEGER,
primaire
….,
CONSTRAINT PK_Cmd PRIMARY KEY (nclt, nprod) )

20
 Contrainte UNIQUE

• 2 lignes de la table ne pourront avoir la même valeur

(sauf NULL)
• Correspond à un identificateur (clé candidate si
minimal), si on ajoute une contrainte NOT NULL

Remarque :
• Aucune des colonnes de la clé primaire ne peut avoir la
valeur NULL

21
 Clé étrangère
• Si une seule colonne forme la clé étrangère, le plus simple
est d’utiliser une contrainte de colonne :
CREATE TABLE Cmd (
nclt INTEGER CONSTRAINT FK_Cmd_Client REFERENCES Client(nclt),
nprod INTEGER CONSTRAINT FK_Cmd_Prod REFERENCES Prod(nprod),
…,
CONSTRAINT PK_Cmd PRIMARY KEY (nclt, nprod) )

• Peut être une contrainte de table :

FOREIGN KEY (colonne1, colonne2,…)
REFERENCES table-ref [(col1, col2,…)]
CREATE TABLE Cmd (
nclt INTEGER,
nprod INTEGER,
…,
CONSTRAINT FK_Cmd_Client FOREIGN KEY (nclt) REFERENCES
Client(nclt)

22
 Contraintes sur les Clés étrangères

• Les colonnes de l’autre table référencées (col1,col2,…)

doivent avoir la contrainte PRIMARY KEY ou
UNIQUE
• Option: ON DELETE CASCADE
– Avec : La suppression d'un client entraîne automatiquement la
suppression de toutes les lignes de la table Cmd qui
référencent ce client.
– Sans : On ne peut supprimer un client s'il est référencé par une
ligne de la table Cmd

23
 Contraintes sur les Clés étrangères

• Option: ON DELETE SET NULL

La suppression d’un client entraîne automatiquement la mise a
jour de l’attribut nclt des lignes de la table cmd qui référencent
ce client avec une valeur NULL
• Option: ON DELETE SET DEFAULT
La suppression d’un client entraîne automatiquement la mise a
jour de l’attribut nclt des lignes de la table cmd qui référencent
ce client avec une valeur NULL
• Option: ON UPATE CASCADE
La modification d’un nclt d’une ligne de la table client entraîne
automatiquement la mise a jour de l’attribut nclt des lignes de la
table cmd qui référencent ce client avec la nouvelle valeur
24
 Contraintes sur les Clés étrangères

• Option: ON UPDATE SET NULL

La modification d’un nclt d’une ligne de la table client
entraîne automatiquement la mise a jour de l’attribut
nclt des lignes de la table cmd qui référencent ce client
avec la valeur NULL
• Option: ON UPDATE SET DEFAULT
La modification d’un nclt d’une ligne de la table client
entraîne automatiquement la mise a jour de l’attribut
nclt des lignes de la table cmd qui référencent ce client
avec la valeur DEFAULT de la table CMd

25
 Modification des contraintes

ALTER TABLE client

DROP CONSTRAINT nom_unique
ADD (CONSTRAINT age_min
check(coalesce(age, 0) > 5000))
RENAME CONSTRAINT CTR1 TO CTRA;

• On ne peut ajouter que des contraintes de table

26
 Vérification des contraintes

• En fonctionnement normal les contraintes sont vérifiées

à chaque requête SQL
• Cette vérification peut être gênante, en particulier lors
de l’ajout de plusieurs lignes de données
• Exemple : Les contraintes de clé étrangère dans Cmd
nous obligent à insérer les clients et les produits en
premiers

27
 Contraintes « différables »

• Pour pallier ce problème, la vérification d’une

contrainte peut être différée à la fin de la transaction
CONSTRAINT nom-contrainte def-contrainte
[NOT] DEFERRABLE
[INITIALLY {DEFERRED | IMMEDIATE}]

• La valeur par défaut est NOT DEFERRABLE

28
 Différer une contrainte

• Indiquer qu’une contrainte est différable ne suffit pas

pour la différer si elle n’a pas été déclarée «
INITIALLY DEFERRED »
• Par défaut, une contrainte différable ne l’est que si on la
diffère par la commande
SET CONSTRAINT nom-contrainte DEFERRED;
• Elle ne sera différée que pour la durée d’une transaction

29
 Invalider des contraintes (Oracle)

• Oracle permet aussi d’invalider des contraintes

• Utile pour, par exemple, améliorer les performances lors
de l’ajout d’une grande quantité de données dans la
base:

ALTER TABLE table

{DISABLE | ENABLE} CONSTRAINT nom-contrainte

30
 SQL

TCL
SET TRANSACTION, COMMIT, ROLLBACK

DML DCL
DDL
SELECT, CONNECT,
CREATE,
INSERT, GRANT,
ALTER, UPDATE, REVOKE
DROP DELETE

Programmation SQL
DECLARE, FETCH, PREPARE,
DESCRIBE, EXECUTE
31
 Ordre SELECT

• Syntaxe :
SELECT [DISTINCT | ALL] { * | liste de colonnes}
[ FROM table ]
[ WHERE prédicat ]
[ GROUP BY liste des colonnes du groupage ]
[ HAVING prédicat ]
[ ORDER BY liste de colonnes de tri ]
;

32
 Résultat du SELECT

• Un résultat vide
• Un résultat NULL
• Une seule donnée
• Une table d’une seule ligne
• Une table de plusieurs lignes.

33
 Exemples

CLIENT(nclt, nom, prenom, age)

Rechercher le nom et le prenom de tous les

clients

SELECT nom, prenom

FROM client;

34
 Exemples

Rechercher toutes les informations des clients les

clients.
SELECT *
FROM client;

35
 Exemples

• Afficher les noms de clients qui ont Alain comme

prénom.
SELECT nom
FROM client
WHERE prenom = ‘Alain’;

36
 Exemples

• Liste des prénoms des clients ayant un numéro < 5.

SELECT prenom
FROM client
Where nclt < 5;

37
 Exemples

Éliminer les prénoms en double.

SELECT DISTINCT prenom

FROM client
WHERE nclt <5;

Le ALL est par défaut

38
 L’opérateur AS

SELECT nom as nom_client, ‘homme’ as sexe

FROM client
WHERE nclt >13;

39
 Surnom de la table

SELECT nom, prenom

FROM client
WHERE client.nclt < 5;

SELECT nom, prenom

FROM client clt
WHERE clt.nclt < 5 ;

40
 Clause where

• Opérateurs de comparaisons :
=, <>, <, <=, >, >=.
• Connecteurs logiques :
OR, AND
• Opérateur de négation :
NOT
• Priorité :
1. Opérateurs de comparaison
2. NOT
3. AND
4. OR
41
 Opérateurs de comparaisons

• Syntaxe :
WHERE [NOT] valeur1 {= | < | > | <= | >= | <>} valeur2
[ { OR | AND } …]

42
 Les mots réservés

SELECT select, date

FROM join
WHERE not = ’F’

SELECT "select", "date"

FROM "join"
WHERE "not" = ’F’

43
 ORDER BY

• Syntaxe:
ORDER BY colonne1 {[ASC] | DESC} , colonne2 {[ASC] |
DESC}, …

ORDER BY 1 {[ASC] | DESC} , 2 {[ASC] | DESC}, …

Les chiffres 1, 2, … sont relatifs à l’ordre de colonnes exprimées

dans la clause SELECT

44
 Traitement des chaînes de
caractères
• Opérateur de concaténation || (+sql server et sybase,
concat mysql,…)
• Recherche partielle LIKE :
– LIKE ‘{chaine} { % | _ } {chaine}’ [ESCAPE ‘car’]
• LOWER
• UPPER
• SUBSTRING(s, i, j)
• TRIM( {LEADING | TRAILING | BOTH } [chaine de
caractère] FROM nom de colonne

45
 Traitement des chaînes de
caractères
• POSITION( chaine IN chaine)
• CHARACTER_LENGTH : LENGTH pour mysql,
sql server
• SOUNDEX
• REVERSE

46
 Fonctions mathématiques

• +, -, *, /
• abs(n), log(), sqrt(n), sign(n), round(n, m), power(n),
mod(n, m), floor(n), pi()…

47
 Données temporelles

• current_date, current_time, current_timestamp.

• extract( {year | month | day | hour | minute | second }
from donnée_temporelle)

48
 Conversion de Type de Données

1. Conversion Implicite
Pour les affectations, les conversions suivantes sont
automatiques :
– VARCHAR ou CHAR è NUMBER
– VARCHAR ou CHAR è DATE
– NUMBER è VARCHAR
– DATE è VARCHAR
Pour les évaluation d’expression, les conversions suivantes sont
automatiques :
– VARCHAR ou CHAR è NUMBER
– VARCHAR ou CHAR è DATE

49
 Conversion de Type de Données

2. Conversion Explicite
– TO_DATE
– TO_CHAR
– TO_NUMBER

50
 Fonctions d’agrégations

• AVG, MAX, MIN, SUM, COUNT

• *, DISTINCT, ALL.

Exemples :

51
 Group by

• Regroupement.
• Group by et fonction d’agrégations.

52
 Opérateur IN et BETWEEN

• Valeur IN (valeur1 [,valeur2 …])

• Valeur BETWEEN valeur1 AND valeur2

• Exemples

53
 Valeurs booléennes

• Logique à 3 états : TRUE, FALSE, NULL.

• NOT
• Valeur NULL :
– SELECT * FROM t_client WHERE cli_enseigne=NULL; faux
– SELECT * FROM t_client WHERE cli_enseigne IS NULL;

• Expression IS {TRUE | FALSE | NULL}

• NULLIF(colonne, valeur)
• COALESCE(valeur1 [,valeur2, ….]) (non supportée par oracle9i)

54
 Le CASE

• CASE WHEN condition THEN valeur1

WHEN condition THEN valeur2
…….
ELSE valeur
END

55
 Requête multi-tabulaires
• Syntaxe :
SELECT [DISTINCT | ALL] { * | liste de colonnes}
[ FROM liste de tables ]
[ WHERE prédicat ]
[ GROUP BY liste des colonnes du groupage ]
[ HAVING prédicat ]
[ ORDER BY liste de colonnes de tri ]
;

56
Réda DEHAK [email protected]
 Plan
• Requêtes multi-tabulaires
• Jointure ( Interne et externe)
• Opérateurs ensemblistes (UNION, INTERSECT, EXCEPT)
• Requêtes imbriquées :
– Opérateur EXISTS
– Opérateur IN
– Opérateur ANY
– Opérateur ALL
• Valeurs Booléennes
• GROUP BY
• Modification de BDD
• Applications
58
 Requêtes multi-tabulaires

• Dans la majorité des cas, on doit combiner des

informations venant de plusieurs schémas relationnels
(plusieurs tables).
• On peut utiliser plusieurs relations en même temps en le
précisant dans la clause From.
• On utilise la notation relation.attribut pour différencier
les attributs qui porte le même nom

59
 Syntaxe

SELECT [DISTINCT | ALL] { * | liste de colonnes}

[ FROM liste de tables ]
[ WHERE prédicat ]
[ GROUP BY liste des colonnes du groupage ]
[ HAVING prédicat ]
[ ORDER BY liste de colonnes de tri ]

60
 Exemple

En utilisant les relations CLIENT(nclt, nom, age, adresse) et

CMD(nclt, nprod, qte, date), trouver la liste des nprods
commandés par Dehak?

SELECT nprod
FROM Client, Cmd
WHERE nom = ‘Dehak’ AND
Client.nclt = Cmd.nclt

61
 Sémantique

1. Commencer avec le produit cartésien de toutes

les tables citées dans la clause FROM.
2. Appliquer une sélection sur le résultat avec la
condition de la clause WHERE.
3. Projeter le résultat du 2 sur les attributs de la
clause SELECT.

62
 Exemple

En utilisant les relations CLIENT(nclt, nom, age, adresse) et

CMD(nclt, nprod, qte, date), trouver la liste des nprods
commandés par Dehak?
nprod

SELECT nprod
Client.nprod = Cmd.nprod
FROM Client, Cmd AND
nom = ‘dehak’
WHERE nom = ‘Dehak’ AND
Client.nclt = Cmd.nclt

Client Cmd
63
 Sémantique opérationnel

• Une variable tuple pour parcourir chaque relation

de la clause FROM :
– Ces variables tuples visitent toutes les combinaisons
possibles de tuples.
• Si les variables tuples pointent sur des tuples qui
vérifient la condition du WHERE, les tuples sont
envoyés à la clause SELECT

64
 Exemple
Client Nclt NOM adresse CMD NCLT NPROD QTE
…………………. ……….
VT1 123 Dehak Paris VT2 123 145 20

…………… …………….

Égalité Résultat

65
 Variables Tuples

1. Quand la requête utilise plusieurs fois la

même relation.
2. Permet de différencier les tables de la clause
FROM.
3. Reste optionnel dans les cas non ambigus.
4. Utilisation de [ AS ] pour définir des
variables tuples.

66
 Exemple

Trouver les nclts qui ont commandés le même produit.

SELECT c1.nclt, c2.nclt

FROM Cmd c1, Cmd c2
WHERE c1.nprod = c2.nprod

Problème :
Réponse (42, 42) (42, 57) (57,42) …

67
 Exemple

Trouver les nclts qui ont commandés le même produit.

Problème :
Réponse (42, 42) (42, 57) (57,42) …

SELECT c1.nclt, c2.nclt

FROM Cmd c1, Cmd c2
WHERE c1.nprod = c2.nprod AND
c1.nclt < c2.nclt

68
 Les Jointures

1. Jointure :

SELECT [DISTINCT | ALL] * | liste_de_colonnes

FROM table1 { [INNER] |
{LEFT | RIGHT | FULL} OUTER
} JOIN table2 ON <Cond>

69
 Les Jointures
Pays Nom Capitale Population surface
JO Année Lieu Pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaume Suisse Berne 7 41
Uni
USA Washington 189 441

SELECT année, lieu, pays, capitale

FROM JO, PAYS
WHERE JO.pays = PAYS.nom

70
 Les Jointures
Pays Nom Capitale Population surface
JO Année Lieu Pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaume Suisse Berne 7 41
Uni
USA Washington 189 441

Résultat Année Lieu Pays Capitale

1904 St Louis USA Washington

1908 Londre Royaume Uni Londre

SELECT année, lieu, pays, capitale

FROM JO, PAYS
WHERE JO.pays = PAYS.nom

71
 Les Jointures
Pays Nom Capitale Population surface
JO Année Lieu Pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaume Suisse Berne 7 41
Uni
USA Washington 189 441

Résultat Année Lieu Pays Capitale

1904 St Louis USA Washington

1908 Londre Royaume Uni Londre

SELECT année, lieu, pays, capitale

FROM JO JOIN PAYS ON JO.pays = PAYS.nom

72
 Les Jointures
Pays Nom Capitale Population surface
JO Année Lieu Pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaume Suisse Berne 7 41
Uni
USA Washington 189 441

Résultat Année Lieu Pays Capitale

1904
1896 St
Athène
Louis Grèce
USA Washington
NULL

1908
1900 Londre
Paris Royaume
FranceUni Londre
NULL

1904 St Louis USA Washington

SELECT année,1908
lieu, pays,Londre
capitaleRoyaume Uni Londre
FROM JO LEFT OUTER JOIN PAYS ON JO.pays =
PAYS.nom
73
 Les Jointures
Pays Nom Capitale Population surface
JO Année Lieu Pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaume Suisse Berne 7 41
Uni
USA Washington 189 441

Résultat Année Lieu Pays Capitale

1904 St Louis USA Washington

1908 Londre Royaume Uni Londre

NULL NULL Irlande Dublin

SELECT année, lieu, pays,NULL
NULL
capitale Autriche Vienne
FROM JO RIGHT
NULL
OUTER NULL
JOIN PAYSSuisse
ON JO.pays
Berne
=
PAYS.nom
74
 Les Jointures
Pays Nom Capitale Population surface
JO Année Lieu Pays
Irlande Dublin 3 70
1896 Athène Grèce
Autriche Vienne 8 83
1900 Paris France
Royaume Londre 36 244
1904 St Louis USA Uni
1908 Londre Royaume Suisse Berne 7 41
Uni
USA Washington 189 441

Résultat Année Lieu Pays Capitale

1904 St Louis USA Washington

1908 Londre Royaume Uni Londre

1896 Athène Grèce NULL

SELECT année,1900
lieu, pays, Paris
capitale France NULL

FROM JO FULLNULLOUTERNULL JOIN PAYSIrlande Dublin=

ON JO.pays
PAYS.nom NULL NULL Autriche Vienne

NULL NULL Suisse Berne

75
 Exemples

1. Jointure classique.
2. Jointure externe.

76
 Les Jointures

2. Jointure Naturelle:

SELECT [DISTINCT | ALL] * | liste_de_colonnes

FROM table1 NATURAL
{ [INNER] |
{LEFT | RIGHT | FULL} OUTER
} JOIN table2
[ USING (col1, …) ]

77
 exemples

• Oracle, MS-SQL Serveur PostGres

78
 Les Jointures

3. Produit cartésien:

SELECT [DISTINCT | ALL] * | liste_de_colonnes

FROM table1 CROSS JOIN table2

SELECT [DISTINCT | ALL] * | liste_de_colonnes

FROM table1, table2

79
 Opérateurs ensemblistes

Syntaxe :

SELECT ….
{ UNION | INTERSECT | EXCEPT } [ALL]
SELECT …
[ORDER BY …]

Attention : Elimination des doublons par défaut

80
 Exemples

• Union
• Intersection
• Différence

81
 Sous rêquetes

• Le résultat d’une requête peut être utilisé pour

répondre à une autre requête
• Le résultat d’une requête peut être utilisé
n’importe où on attend une table (ex : FROM
…)
• Le résultat d’une requête peut être utilisé
n’importe où on attend une constante si la
requête retourne une seule valeur.
82
 Une seule valeur

Trouver le nom des clients qui habitent à la même adresse

que ‘Dehak’?

SELECT nom
FROM Client
WHERE adresse = ( SELECT adresse
FROM Client
WHERE nom = ‘Dehak’ )

83
 Une seule valeur

Trouver le nom des clients plus âgés que ‘Dehak’?

SELECT nom
FROM Client
WHERE age > ( SELECT age
FROM Client
WHERE nom = ‘Dehak’ )

84
 Une seule valeur

Trouver le nom des clients les plus âgés de la table client?

SELECT nom
FROM Client
WHERE age = ( SELECT max(age)
FROM Client )

85
 Une seule valeur

Trouver le nom des clients et la somme des quantités

commandées pour les clients parisiens?

SELECT nom, ( SELECT SUM(QTE)

FROM Cmd
WHERE Cmd.nclt = Client.nclt )
FROM Client
WHERE adresse = ‘Paris’

86
 Plusieurs tuples : Opérateur EXISTS

• EXISTS( <relation> ) : est vrai si et seulement si la relation

contient au moins un tuple.
• Trouver les noms de client qui apparaissent plusieurs fois dans la
table ?

SELECT nom
FROM Client l1
WHERE EXISTS ( SELECT *
FROM Client l2
WHERE l2.nom = l1.nom AND
l2.nclt <> l2.nclt )

87
 Plusieurs tuples

Opérateur IN
• <tuple> IN <relation> : est vrai si et seulement
si le <tuple> est un membre de la table
<relation>.
• <tuple> NOT IN <relation> représente la
négation.
• La <relation> représente souvent une sous
requête
88
 Exemple

Trouver la liste nprods commandés par des clients dont le

nom commence par ‘D’

SELECT nprod
FROM Cmd
WHERE nclt IN ( SELECT nclt
FROM Client
WHERE nom LIKE ‘D%’)

89
 L’opérateur ANY

• x = ANY ( <relation> ) : est vrai si et seulement

si le tuple x est égale à au moins un tuple de la
relation.
• L’opérateur = peut être remplacé par n’importe
quel autre opérateur de comparaison
• x >= ANY ( <relation> ) : x n’est pas inférieure
à tous les tuples de la relation.

90
 L’opérateur ALL

• x <> ALL ( <relation> ) : est vrai si et

seulement si pour chaque tuple t de <relation>, le
tuple x est différent de t.
• L’opérateur <> peut être remplacé par n’importe
quel autre opérateur de comparaison
• x >= ALL ( <relation> ) : il n’existe pas une
valeur plus grande que x dans la table <relation>

91
 Exemple

Trouver le(s) nprod(s) de la plus importante commande

de la table cmd.

SELECT nprod
FROM Cmd
WHERE qte >= ALL ( SELECT qte
FROM Cmd)

92
 Valeurs booléennes

• Logique à 3 états : TRUE, FALSE, NULL.

• NOT
• Valeur NULL :
– SELECT * FROM clt WHERE age = NULL; faux
– SELECT * FROM clt WHERE age IS NULL;
• Expression IS {TRUE | FALSE | NULL}

AND T F N OR T F N

T T F N T T T T

F F F F F T F N

N N F N N T N N
93
 Group By

TABLE TABLE
WHERE
Création de groupe
suivant les attributs
du GROUP BY

TABLE
TABLE
Groupe
filtré Groupe
HAVING
SELECT
Résultat
94
 Exemple

La liste des clients avec le total des quantités

commandées?

SELECT nclt, SUM(Qte)

FROM Cmd
GROUP BY nclt

95
 Exemple

La liste des clients ayant commandés 5 produits

différents avec le total des quantités commandées?

SELECT nclt, SUM(Qte)

FROM Cmd
GROUP BY nclt
HAVING COUNT(DISTINCT nprod) = 5

96
 Exemple

La liste des clients ayant commandés 5 produits rouges

différents avec le total des quantités commandées?

SELECT nclt, SUM(Qte)

FROM Cmd
WHERE couleur = ‘rouge’
GROUP BY nclt
HAVING COUNT(DISTINCT nprod) = 5

97
 Exemple

La liste des noms et ages de clients classés par ordre

croissant sur l’age?

SELECT nom, age

FROM Client
ORDER BY age

98
 Exemple

La liste des noms et ages de clients classés par ordre

croissant sur l’age? Rajouter une colonne classement

SELECT count(*) as classement, c1.nom, c1.age

FROM Client c1 JOIN Client c2
ON c1.age <= c2.age
GROUP BY c1.nom, c1.age
ORDER BY age

99
 Exemple

La liste des noms et ages de clients classés 5ieme ou

bien 10ieme par ordre croissant sur l’age?

SELECT c1.nom, c1.age

FROM Client c1 JOIN Client c2
ON c1.age <= c2.age
GROUP BY c1.nom, c1.age
HAVING count(*) IN (5, 10)

100
 Modification d’une BDD

• Les requêtes de modifications ne retournent pas

un résultat, elles effectuent un changement de la
BDD.
• Trois types de modifications :
– INSERT : Insertion d’un ou plusieurs tuples.
– UPDATE : modification d’un ou plusieurs tuples.
– DELETE : suppression d’un ou plusieurs tuples.

101
 Insertion
• Insertion d’un tuple :
INSERT INTO <relation> [( liste_attr )]
VALUES (liste_valeur)

• Exemple :
INSERT INTO Client
VALUES (42, ‘Dehak’, ‘Reda’, 29, ‘Paris’)

INSERT INTO Client(nclt, nom)

VALUES (48, ‘Daniel’)

102
 Spécifier la liste des attributs

• On ne connaît pas l’ordre des attributs.

• On ne connaît pas la valeur de certains
attributs, Le système doit leur attribuer la
valeur par défaut ou bien NULL)

103
 Insertion de plusieurs tuples

• À l’aide de sous requêtes :

INSERT INTO <relation>
(<sous requête>)

• Exemple :
INSERT INTO N_Client_Parisien
( SELECT nclt, nom
FROM Client
WHERE adresse = ‘Paris’ )

104
 Suppression

Supprimer des tuples qui vérifient une condition :

DELETE FROM <relation>

[ WHERE <Cond> ]

Exemples :
DELETE FROM Client
WHERE nom = ‘Dehak’

105
 Absence du WHERE

• Supprimer tous les tuples d’une relation :

DELETE FROM cmd

Attention : Si vous avez oublié le WHERE, le

DELETE supprime tous les tuples.

106
 Suppression de plusieurs tuples
• Élimination des tuples en doubles :

Exemple :

DELETE FROM Client c1

WHERE EXISTS ( SELECT *
FROM Client c2
WHERE c2.age = c1.age AND
c2.nclt <> c1.nclt)

Résultat : ?

107
 UPDATE
• Modification de la valeur des attributs d’un ou plusieurs tuples

UPDATE <relation>
SET <list_attr_affectation>
[ WHERE <Cond> ]

Exemple : modifier le nom du client n°42

UPDATE Client
SET nom = ‘Barbier’
WHERE nclt = 42

108
 Exemple

• Augmenter l’age des parisiens de 2 ans

UPDATE Client
SET age = age + 2
WHERE adresse = ‘Paris’

109
Réda DEHAK [email protected]
 Requête multi-tabulaires
• Syntaxe :
SELECT [DISTINCT | ALL] { * | liste de colonnes}
[ FROM liste de tables ]
[ WHERE prédicat ]
[ GROUP BY liste des colonnes du groupage ]
[ HAVING prédicat ]
[ ORDER BY liste de colonnes de tri ]
;

111
 Exemples
• Soit les trois tables suivantes :
– CLIENT(nclt, nom, age, adresse)
– PROD(nprod, design, couleur, volume)
– CMD(nclt, nprod, qte, date).
1. La liste des noms de clients qui ont un age > 20.
2. La liste des noms de clients ayant commandés le produit numéro 13.
3. La liste des noms de clients ayant commandés un produit de couleur rouge.
4. La couleur des produits commandés par monsieur Dupont.
5. La liste des noms de clients ayant commandés au moins un produit.
6. La liste des noms de clients ayant commandés un produit vert ou rouge.
7. La liste des noms de clients ayant commandés un produit vert ou bien rouge.
8. La liste des noms de clients ayant commandés au moins deux produits.
9. La liste des clients qui ont un age > 50 et qui n’ont pas commandé un
produit vert.
10. La liste des noms de clients qui ont commandé tous nos produits.
11. La liste des noms de clients qui ont commandé tous nos types de pince.
12. Le Nombre de clients habitant à paris.
13. Le nclt et le nom du ou des clients les plus jeunes de la table client.
112
Réda DEHAK [email protected]
 Plan

• Vues
• Gestion des droits
• Triggers

114
 Les vues

Une vue est une table virtuelle = une relation définie à

partir des données contenues d’autres tables et vues.

Syntaxe :

CREATE VIEW nom_vue [(nomcol1,…)]

AS requete_SELECT
[WITH CHECK OPTION]

115
 Les vues

Objectifs :
– Masquer la complexité d’un schéma
relationnel.
– Simplifier les requêtes SELECT complexes
– Préserver la confidentialité des données.
– Contribuer à la non redondance des données.

116
 Exemples

Les clients parisiens :

CREATE VIEW client_parisien

AS SELECT *
FROM client
WHERE adresse = ‘PARIS’

117
 Exemples

Les commandes + produits des clients ‘DUPONT’ :

CREATE VIEW cmd_dupont
AS SELECT nprod, design, couleur, volume, qte, date
FROM cmd NATURAL JOIN prod
WHERE nclt IN ( SELECT nclt
FROM client
WHERE nom = ‘DUPONT’
)

118
 Exemples

Facture (attributs calculés)

CREATE VIEW Facture (nprod, nclt, qte, vol_tot, date)

AS SELECT nprod, nclt, qte, qte * volume,
date
FROM cmd NATURAL JOIN prod

119
 Utilisation d’une vue

Exemples

120
 Utilisation de la vue

• Le SGBD interpréte la requête en

considérant la vue comme une table
Les SGBDs traduisent la requête en arbre
algébrique.
• Il remplace la Vue par son arbre algébrique.

121
 Question

Peut on modifier le contenu d’une vue ?

122
 Vues modifiables
Les vues crées à partir de requête SELECT contenant :

1. Une seule table ou une seule vue, elle-même

modifiable.
2. Ni intersection, ni union, ni différence, ni
jointure.
3. Ni mot DISTINCT, ni expression de calcul dans la
clause SELECT
4. Ni clause group by ni HAVING
5. Aucune sous-requête faisant référence à la même
table que la table externe.
6. Présence obligatoire d’une clé primaire
123
 Exemples
CREATE VIEW client_parisien
AS SELECT *
FROM client
WHERE adresse = ’PARIS’
CREATE VIEW client_parisien
AS SELECT nclt, nom, prenom
FROM client
WHERE adresse = ‘PARIS’

CREATE VIEW client_parisien

AS SELECT *
FROM client
WHERE adresse = ’PARIS’
WITH CHECK OPTION
124
 Exemples

CREATE VIEW client_parisien

AS SELECT *
FROM client T
WHERE adresse IN ( SELECT adresse
FROM fournisseur
WHERE fournisseur.nom = T.nom )

CREATE VIEW client_parisien

AS SELECT nom, prenom
FROM client
WHERE adresse = ‘PARIS’

125
 Supprimer une vue

DROP VIEW <nom vue> : permet de supprimer

une vue existante

126
 Contrôle d’accès
Attribution des privilèges :

GRANT { ALL PRIVILEGES | liste_privilèges } ON objet

TO { PUBLIC | liste_user }
[ WITH GRANT OPTION ]

Privilèges :
– SELECT : lecture de toutes les colonnes d’une table ou d’une vue.
– INSERT[liste_colonne] : insertion dans une table ou dans une vue,
éventuellement limitée à certaines colonnes.
– UPDATE[liste_colonne] : mise à jour dans une table ou dans une vue,
éventuellement limitée à certaines colonnes.
– DELETE : suppression dans une table ou dans une vue.
– REFERENCES[liste_colonne] : Faire référence aux colonnes d’une
table ou d’une vue lors de la pose d’une contrainte référentielle,
éventuellement limitée à certaines colonnes.
127
 Exemples

GRANT SELECT ON client TO PUBLIC

GRANT SELECT ON produit TO PUBLIC

GRANT ALL PRIVILEGES ON cmd TO User_Service_Cmd

WITH GRANT OPTION

GRANT SELECT ON cmd TO User_Facturation

128
 Contrôle d’accès

• Suppression des droits d’accès :

REVOKE [ GRANT OPTION FOR] {ALL PRIVILEGES |
liste_privilèges}
ON object FROM { PUBLIC | liste_utilisateurs}

• Exemple :
REVOKE UPDATE ON cmd FROM User_Service_Cmd

REVOKE GRANT OPTION FOR DELETE ON cmd

FROM User_Service_Cmd

129
 TRIGGER
Syntaxe :
CREATE TRIGGER <nom trigger>
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF liste_colonnes]}
ON <relation>
[ ORDER <liste colonne> ]
[ REFERENCING OLD [AS] anc_val
| NEW [AS] nouv_val
| OLD_TABLE [AS] anc_tab
| NEW_TABLE [AS] nouv_tab ]
[FOR EACH {ROW | STATEMENT}]
Code_trigger

130
 SQL DDL

1. Création d’une base de donnée

CREATE DATABASE nom_base

2. Suppression d’une base de donnée

DROP DATABASE nom_base
3. Création d’une table
CREATE [{LOCAL | GLOBAL} TEMPORARY] TABLE nom_table
(…définition des colonnes et des contraintes…)
4. Modification d’une table
ALTER TABLE nom_table { [ADD CONSTRAINT contrainte]
DROP CONSTRAINT nom_contrainte]
ADD COLUMN def_colonne
DROP COLUMN nom_colonne
ALTER COLUMN { SET DEFAULT val_default]
| DROP DEFAULT}

131
 FOREIGN KEY

FOREIGN KEY REFERENCES table(col) [ [NOT]

DEFERRABLE [INITIALLY [DEFERRED |
IMMEDIATE] ]

SET CONSTRAINT <cont> DEFERRED

132
 Transaction

BEGIN
COMMIT
ROLLBACK

133