Réda DEHAK reda@lrde.epita SIBD - Système... · sql tcl set transaction, commit, rollback ddl...

Réda DEHAK reda@lrde.epita.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

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 reda@lrde.epita.fr

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 reda@lrde.epita.fr

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 reda@lrde.epita.fr

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