Upload
others
View
5
Download
0
Embed Size (px)
Citation preview
Réda DEHAK [email protected]
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
DDL
CREATE, ALTER, DROP
DML
SELECT, INSERT, UPDATE, DELETE
DCL
CONNECT, GRANT, REVOKE
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 caractères
Temporels Nombres Chaînes de bits
CHAR
VARCHAR
ASCII
UNICODE
NCHAR
NCHAR VARYING
DATE TIME
TIMESTAMP REELS ENTIERS
EXACT
APPROCHES DECIMAL
REAL FLOAT DOUBLE PRECISION
INTEGER SMALLINT
BIT
BIT VARYING
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 ( nclt INTEGER, nprod INTEGER, …., CONSTRAINT PK_Cmd PRIMARY KEY (nclt, nprod) )
Erreur si on déclare nclt et nprod en cle
primaire
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
DDL
CREATE, ALTER, DROP
DML
SELECT, INSERT, UPDATE, DELETE
DCL
CONNECT, GRANT, REVOKE
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
SELECT nom, prenom FROM client;
CLIENT(nclt, nom, prenom, age)
Rechercher le nom et le prenom de tous les clients
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? SELECT nprod FROM Client, Cmd WHERE nom = ‘Dehak’ AND
Client.nclt = Cmd.nclt
63 Client
Client.nprod = Cmd.nprod AND
nom = ‘dehak’
nprod
Cmd
x
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
65
Client Nclt NOM adresse
………………….
123 Dehak Paris
……………
CMD NCLT NPROD QTE
……….
123 145 20
…………….
VT1 VT2
Égalité Résultat
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
SELECT année, lieu, pays, capitale FROM JO, PAYS WHERE JO.pays = PAYS.nom
70
JO Année Lieu Pays
1896 Athène Grèce
1900 Paris France
1904 St Louis USA
1908 Londre Royaume Uni
Pays Nom Capitale Population surface
Irlande Dublin 3 70
Autriche Vienne 8 83
Royaume Uni
Londre 36 244
Suisse Berne 7 41
USA Washington 189 441
Les Jointures
SELECT année, lieu, pays, capitale FROM JO, PAYS WHERE JO.pays = PAYS.nom
71
JO Année Lieu Pays
1896 Athène Grèce
1900 Paris France
1904 St Louis USA
1908 Londre Royaume Uni
Pays Nom Capitale Population surface
Irlande Dublin 3 70
Autriche Vienne 8 83
Royaume Uni
Londre 36 244
Suisse Berne 7 41
USA Washington 189 441
Résultat Année Lieu Pays Capitale
1904 St Louis USA Washington
1908 Londre Royaume Uni Londre
Les Jointures
SELECT année, lieu, pays, capitale FROM JO JOIN PAYS ON JO.pays = PAYS.nom
72
JO Année Lieu Pays
1896 Athène Grèce
1900 Paris France
1904 St Louis USA
1908 Londre Royaume Uni
Pays Nom Capitale Population surface
Irlande Dublin 3 70
Autriche Vienne 8 83
Royaume Uni
Londre 36 244
Suisse Berne 7 41
USA Washington 189 441
Résultat Année Lieu Pays Capitale
1904 St Louis USA Washington
1908 Londre Royaume Uni Londre
Les Jointures
SELECT année, lieu, pays, capitale FROM JO LEFT OUTER JOIN PAYS ON JO.pays =
PAYS.nom
73
JO Année Lieu Pays
1896 Athène Grèce
1900 Paris France
1904 St Louis USA
1908 Londre Royaume Uni
Pays Nom Capitale Population surface
Irlande Dublin 3 70
Autriche Vienne 8 83
Royaume Uni
Londre 36 244
Suisse Berne 7 41
USA Washington 189 441
Résultat Année Lieu Pays Capitale
1904 St Louis USA Washington
1908 Londre Royaume Uni Londre
Résultat Année Lieu Pays Capitale
1896 Athène Grèce NULL
1900 Paris France NULL
1904 St Louis USA Washington
1908 Londre Royaume Uni Londre
Les Jointures
SELECT année, lieu, pays, capitale FROM JO RIGHT OUTER JOIN PAYS ON JO.pays =
PAYS.nom
74
JO Année Lieu Pays
1896 Athène Grèce
1900 Paris France
1904 St Louis USA
1908 Londre Royaume Uni
Pays Nom Capitale Population surface
Irlande Dublin 3 70
Autriche Vienne 8 83
Royaume Uni
Londre 36 244
Suisse Berne 7 41
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
NULL NULL Autriche Vienne
NULL NULL Suisse Berne
Les Jointures
SELECT année, lieu, pays, capitale FROM JO FULL OUTER JOIN PAYS ON JO.pays =
PAYS.nom
75
JO Année Lieu Pays
1896 Athène Grèce
1900 Paris France
1904 St Louis USA
1908 Londre Royaume Uni
Pays Nom Capitale Population surface
Irlande Dublin 3 70
Autriche Vienne 8 83
Royaume Uni
Londre 36 244
Suisse Berne 7 41
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
1900 Paris France NULL
NULL NULL Irlande Dublin
NULL NULL Autriche Vienne
NULL NULL Suisse Berne
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}
93
AND T F N
T T F N
F F F F
N N F N
OR T F N
T T T T
F T F N
N T N N
Group By
94
TABLE WHERE
TABLE
Création de groupe suivant les attributs
du GROUP BY
TABLE Groupe
TABLE Groupe
filtré HAVING
SELECT
Résultat
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 :
117
CREATE VIEW client_parisien
AS SELECT *
FROM client
WHERE adresse = ‘PARIS’
Exemples
Les commandes + produits des clients ‘DUPONT’ :
118
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’
)
Exemples
Facture (attributs calculés)
119
CREATE VIEW Facture (nprod, nclt, qte, vol_tot, date)
AS SELECT nprod, nclt, qte, qte * volume, date
FROM cmd NATURAL JOIN prod
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
124
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
Exemples
125
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’
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