of 138 /138
1 SQL : Un Langage SQL : Un Langage Relationnel Relationnel Witold LITWIN Witold LITWIN

1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

Embed Size (px)

Citation preview

Page 1: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

1

SQL : Un Langage RelationnelSQL : Un Langage Relationnel

Witold LITWINWitold LITWIN

Page 2: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

2

Langage de base de donnéesLangage de base de données(Database Language)(Database Language)

Langage de base de donnéesLangage de base de données(Database Language)(Database Language)

Un sous-langage de programmationUn sous-langage de programmation Consiste traditionnellement de deux parties:Consiste traditionnellement de deux parties:

– langage de définition de donnéeslangage de définition de données

– langage de manipulation de donnéeslangage de manipulation de données» langage interactif (de requêtes)langage interactif (de requêtes)

» langage imbriqué (langage imbriqué (embeddedembedded))

En pratique, les deux parties sont imbriquéesEn pratique, les deux parties sont imbriquées– définition de vues et des attributs hérités en généraldéfinition de vues et des attributs hérités en général

Page 3: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

3

SQLSQLSQLSQL Inventé à IBM San Jose, 1974 (Boyce & Chamberlin) Inventé à IBM San Jose, 1974 (Boyce & Chamberlin)

pour System Rpour System R Basé sur le calcul de tuple & algèbre relationnelleBasé sur le calcul de tuple & algèbre relationnelle relationnellement complet (et plus)relationnellement complet (et plus) Le langageLe langage de SGBD relationnels de SGBD relationnels En évolution contrôlée par ANSI (SQL1, 2, 3...)En évolution contrôlée par ANSI (SQL1, 2, 3...) Il existe aussi plusieurs dialectesIl existe aussi plusieurs dialectes Les possibilités basiques sont simples Les possibilités basiques sont simples Celles avancées peuvent être fort complexesCelles avancées peuvent être fort complexes

– Signalées dans ce qui suit par Signalées dans ce qui suit par

Page 4: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

4

SQL: Définition de DonnéesSQL: Définition de DonnéesSQL: Définition de DonnéesSQL: Définition de Données

CREATE TABLECREATE TABLE CREATE VIEW CREATE VIEW CREATE CREATE INDEXINDEX

ALTER TABLEALTER TABLE

DROP TABLEDROP TABLE DROP VIEWDROP VIEW DROP INDEXDROP INDEX

Page 5: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

6

CREATE TABLECREATE TABLE(clauses essentielles)(clauses essentielles)

CREATE TABLECREATE TABLE(clauses essentielles)(clauses essentielles)

Définit la table réelle (de base)Définit la table réelle (de base)

CREATE TABLE tableCREATE TABLE table

(column [,column]...(column [,column]...

[, primary key] ;[, primary key] ;

column := name type [NOT NULL]column := name type [NOT NULL]

type :=type := INTEGER, CHAR (n), GRAPHIC, ICON, DATE, INTEGER, CHAR (n), GRAPHIC, ICON, DATE, TIME, TIMESTAMPTIME, TIMESTAMP

Page 6: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

7

EXAMPLEEXAMPLEEXAMPLEEXAMPLE

CREATE TABLE SCREATE TABLE S(S# (S# CHAR (5)CHAR (5) NOT NULL,NOT NULL,SNAME SNAME CHAR (20),CHAR (20),STATUSSTATUS INT,INT,CITYCITY CHAR (15),CHAR (15),

PRIMARY KEY (S#) ) ;PRIMARY KEY (S#) ) ;

Page 7: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

8

Clauses CONSTRAINT sont nouvelles dans SQLClauses CONSTRAINT sont nouvelles dans SQL Permettent de définir Permettent de définir

– les clés étrangères FOREIGN KEYles clés étrangères FOREIGN KEY– les contraintes d'intégrité CHECKles contraintes d'intégrité CHECK

» sur un attributsur un attribut» inter-attribut d’une tableinter-attribut d’une table

– AutresAutres

La puissance expressive varie entre les dialectesLa puissance expressive varie entre les dialectes– le standard est le plus puissantle standard est le plus puissant

» notamment permet tout SELECT dans CHECKnotamment permet tout SELECT dans CHECK

– les dialectes ne permettent queles dialectes ne permettent que» aucun CHECK (MsAccess)aucun CHECK (MsAccess)» contrainte sur les valeurs d’un même tuple (DB-2)contrainte sur les valeurs d’un même tuple (DB-2)» une par attribut (SQL-Server, DB2)une par attribut (SQL-Server, DB2)» pas de sous-requêtes (SQL-Server)pas de sous-requêtes (SQL-Server)

CREATE TABLECREATE TABLEClause CONSTRAINTClause CONSTRAINT

Page 8: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

9

On peut créer une table dans une autre base que celle On peut créer une table dans une autre base que celle courante (ouverte)courante (ouverte)– SQL Server, SQL (seulement) de MsAccess, SQL-2SQL Server, SQL (seulement) de MsAccess, SQL-2

CREATE TABLE CREATE TABLE AUTRE-BASEAUTRE-BASE.S.S(S# (S# CHAR (5)CHAR (5) NOT NULL,NOT NULL,SNAME SNAME CHAR (20),CHAR (20),STATUSSTATUS INT,INT,CITYCITY CHAR (15),CHAR (15),

PRIMARY KEY (S#) ) ;PRIMARY KEY (S#) ) ;

CREATE TABLECREATE TABLE(multibase)(multibase)

CREATE TABLECREATE TABLE(multibase)(multibase)

Basecourante

Autre-Base

Page 9: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

15

ALTER & DROP TABLEALTER & DROP TABLEALTER & DROP TABLEALTER & DROP TABLE

ALTER TABLE S ADD DISCOUNT SMALLINT ;ALTER TABLE S ADD DISCOUNT SMALLINT ;

certains systèmes:certains systèmes:ALTER TABLE S DROP DISCOUNT SMALLINT ;ALTER TABLE S DROP DISCOUNT SMALLINT ;

ALTER TABLE S RENAME SNAME NAME ;ALTER TABLE S RENAME SNAME NAME ;

..........

DROP TABLE P ;DROP TABLE P ;

Page 10: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

16

IndexesIndexesIndexesIndexes

CREATE [UNIQUE] INDEX indexCREATE [UNIQUE] INDEX index

ON table ( column [order] [, column...)ON table ( column [order] [, column...)

[CLUSTER] ;[CLUSTER] ;

CREATE UNIQUE INDEX XS ON S (S#) ; CREATE UNIQUE INDEX XS ON S (S#) ;

CREATE UNIQUE INDEX XSP ON SP (S# ASC, P# DESC) ;CREATE UNIQUE INDEX XSP ON SP (S# ASC, P# DESC) ;

UNIQUE = pas de duplicata de valeurs indexéesUNIQUE = pas de duplicata de valeurs indexées indexes uniques obligatoires pour les clés dans le DB2indexes uniques obligatoires pour les clés dans le DB2

Page 11: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

18

IndexesIndexesIndexesIndexes

Définition des indexes ne devait pas être à ce Définition des indexes ne devait pas être à ce niveau de SQL (c'est le schéma interne)niveau de SQL (c'est le schéma interne)

En principe, une table peut avoir un nombre En principe, une table peut avoir un nombre quelconque d'indexesquelconque d'indexes

Les indexes accélèrent les recherchesLes indexes accélèrent les recherches Mais, les indexes pénalisent les mises à jour !Mais, les indexes pénalisent les mises à jour !

Pourquoi ?Pourquoi ?

Page 12: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

19

Un dialecte de SQLUn dialecte de SQLSQL-MsAccessSQL-MsAccess

Un dialecte de SQLUn dialecte de SQLSQL-MsAccessSQL-MsAccess

Le dialecte le plus répandu aujourd'huiLe dialecte le plus répandu aujourd'hui Définition de données est considérablement Définition de données est considérablement

plus élaborée que dans le SQL Standardplus élaborée que dans le SQL Standard Certaines options du standard sont toutefoisCertaines options du standard sont toutefois

– sous restrictionsous restriction– s'expriment sous mots-clés différentss'expriment sous mots-clés différents

» voir MsAccess Aidevoir MsAccess Aide

– pas toujours nécessairespas toujours nécessaires

Page 13: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

Example: Table P de S-PExample: Table P de S-PExample: Table P de S-PExample: Table P de S-P

• Attention: Type Counter -> Autonumber in MsAccess-97

Page 14: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

21

MsAccess: Types de Données MsAccess: Types de Données MsAccess: Types de Données MsAccess: Types de Données TextText

– limité par défaut à 50 caractèreslimité par défaut à 50 caractères» clause FIELD SIZE permet 256 caractèresclause FIELD SIZE permet 256 caractères

– supporte les prédicats SQL & QBEsupporte les prédicats SQL & QBE MemoMemo

– taille < 64K caractèrestaille < 64K caractères– supporte seulement la sélection SQL & QBEsupporte seulement la sélection SQL & QBE

Date/TimeDate/Time– supporte l ’arithmétique de dates/tempssupporte l ’arithmétique de dates/temps

» 21/3 - 21/2 = 2821/3 - 21/2 = 28» 21/4 - 21/3 = 31 ?21/4 - 21/3 = 31 ?

– prévu pour 21-ème siècle (Access97)prévu pour 21-ème siècle (Access97)» 1/1/00 à 31/12/29 signifie 1/1/2000 à 31/12/20291/1/00 à 31/12/29 signifie 1/1/2000 à 31/12/2029» 1/1/30 à 31/12/99 signifie 1/1/1930 à 31/12/19991/1/30 à 31/12/99 signifie 1/1/1930 à 31/12/1999

AutonumberAutonumber– compteur automatique ou OID (option random dans NewValues)compteur automatique ou OID (option random dans NewValues)

Page 15: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

22

MsAccess: Types de DonnéesMsAccess: Types de Données

HyperlinkHyperlink– comme son l ’indiquecomme son l ’indique

» nom symbolique < 2048 octetsnom symbolique < 2048 octets

» URL ou UNC< 2048 octetsURL ou UNC< 2048 octets

» sous-adresse (dans le fichier ou la page)sous-adresse (dans le fichier ou la page)Cajun Delights#http://www.cajundelights.com#PriceCajun Delights#http://www.cajundelights.com#Price

» supporte seulement la sélection SQL & QBEsupporte seulement la sélection SQL & QBE

OLE objetOLE objet– tout objet Windowstout objet Windows

» multimédia ou programmemultimédia ou programme

– peut être copié ou référencépeut être copié ou référencé

– il faut double-cliquer sur sa description textuelle dans le tuple pour le il faut double-cliquer sur sa description textuelle dans le tuple pour le voirvoir

Page 16: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

23

MsAccess: Champ MsAccess: Champ Number & CurrencyNumber & Currency clause Field sizeclause Field size

MsAccess: Champ MsAccess: Champ Number & CurrencyNumber & Currency clause Field sizeclause Field size

Byte Byte 0 à 255 0 à 255 Integer Integer -32,768 à 32,767, 2 octets.-32,768 à 32,767, 2 octets. Long Integer Long Integer

-2,147,483,648 à 2,147,483,647. 4 octets.-2,147,483,648 à 2,147,483,647. 4 octets. Single Six digits de precision Single Six digits de precision

-3.402823E38 à 3.402823E38. 4 octets.-3.402823E38 à 3.402823E38. 4 octets. Double (Default) 10 digits de precision Double (Default) 10 digits de precision 1.79769313486232E308 1.79769313486232E308

à 1.79769313486232E308. 8 octetsà 1.79769313486232E308. 8 octets..

Replication IDReplication ID– Pour les bases dupliquées - 16 octetsPour les bases dupliquées - 16 octets– un OIDun OID– peut être aussi dans le type Autonumberpeut être aussi dans le type Autonumber

Page 17: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

24

MsAccess: Champ MsAccess: Champ Yes/NoYes/No

A utiliser comme son nom l ’indiqueA utiliser comme son nom l ’indique– Yes/No ou On/Off ou True/FalseYes/No ou On/Off ou True/False

» fixé par le champ Formatfixé par le champ Format

» visualisé par défaut par Check-boxvisualisé par défaut par Check-box

» mais, il y a d ’autres possibilitésmais, il y a d ’autres possibilités

taille: 1 octettaille: 1 octet

Page 18: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

25

MsAccess : domainesMsAccess : domainesMsAccess : domainesMsAccess : domaines On peut les simuler (en QBE) par :On peut les simuler (en QBE) par :

– une table D source de valeursune table D source de valeurs» table de la base ou une liste de valeurstable de la base ou une liste de valeurs

– une zone de texte ou zone de texte modifiable une zone de texte ou zone de texte modifiable ((combo-boxcombo-box) sur l’attribut A à valeurs dans D) sur l’attribut A à valeurs dans D

» déclaré dans la définition de A (partie Liste de choix déclaré dans la définition de A (partie Liste de choix //LookupLookup))

– une requête déclarée dans la définition de A une requête déclarée dans la définition de A (dans «  (dans «  contenu /contenu / rowrow sourcesource » »))

Page 19: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

26

MsAccess : surprisesMsAccess : surprisesMsAccess : surprisesMsAccess : surprises

Seules les valeurs apparaissant dans la Seules les valeurs apparaissant dans la 1-ère1-ère colonne du colonne du box et donc dans D peuvent être dans Abox et donc dans D peuvent être dans A

– Même si l’on indique une autre « colonne liée »Même si l’on indique une autre « colonne liée » Type de données Assistant Liste de choix / Type de données Assistant Liste de choix / Lookup Wizard Lookup Wizard

réalise cette manipulationréalise cette manipulation– Drôle de type de donnéesDrôle de type de données

– Attention aux bugs de cet assistant Attention aux bugs de cet assistant – Aussi à l ’option « Limiter à la liste / Aussi à l ’option « Limiter à la liste / Limit to ListLimit to List » »

On peut la faire aussi sans cet assistant (et mieux) On peut la faire aussi sans cet assistant (et mieux)

Page 20: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

27

MsAccess : surprisesMsAccess : surprisesMsAccess : surprisesMsAccess : surprises La table peut La table peut hériterhériter l’attribut A si l’on déclare: l’attribut A si l’on déclare:

– L’attribut héritant à le même nom que le 1èr attribut de L’attribut héritant à le même nom que le 1èr attribut de D déclaré dans SELECTD déclaré dans SELECT

– L’attribut A est le 2-ème dans SELECTL’attribut A est le 2-ème dans SELECT– La 1-ère longueur de colonne = 0cmLa 1-ère longueur de colonne = 0cm Dans notre exemple DB, SP peut ainsi hériter SNAMEDans notre exemple DB, SP peut ainsi hériter SNAME Qu’arrive t’il aux tuples existants si l’on sélectionne une Qu’arrive t’il aux tuples existants si l’on sélectionne une

autre colonne liéeautre colonne liée– Par ex. on utilise d’abord pour saisir les valeurs de Par ex. on utilise d’abord pour saisir les valeurs de

P.CITY la requêteP.CITY la requête» SELECT S.City, S.SName FROM S;SELECT S.City, S.SName FROM S;

– Puis, on la remplace par:Puis, on la remplace par:» SELECT S.Sname, S.City FROM S;SELECT S.Sname, S.City FROM S;

Page 21: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

28

MsAccess : CREATE TABLEMsAccess : CREATE TABLECONSTRAINT = INDEXCONSTRAINT = INDEX

MsAccess : CREATE TABLEMsAccess : CREATE TABLECONSTRAINT = INDEXCONSTRAINT = INDEX

CREATE TABLE CREATE TABLE [[FriendsFriends]] ([First Name] TEXT, [Last Name] TEXT); ([First Name] TEXT, [Last Name] TEXT);

CREATE TABLE Friends1 ([First Name] TEXT, [Last Name] TEXT, CREATE TABLE Friends1 ([First Name] TEXT, [Last Name] TEXT, [Date of Birth] DATETIME, [Date of Birth] DATETIME, CONSTRAINTCONSTRAINT MyTableConstraint UNIQUE ([First Name], [Last MyTableConstraint UNIQUE ([First Name], [Last Name], [Date of Birth]));Name], [Date of Birth]));

CREATE TABLE Friends2 ([First Name] TEXT, [Last Name] TEXT, CREATE TABLE Friends2 ([First Name] TEXT, [Last Name] TEXT, SSN INTEGER SSN INTEGER CONSTRAINTCONSTRAINT MyFieldConstraint PRIMARY KEY); MyFieldConstraint PRIMARY KEY);

SSN est la clé primaire. SSN est la clé primaire. On peut créer une table sans clé primaireOn peut créer une table sans clé primaire

– alors elle accepte des duplicataalors elle accepte des duplicata» contrairement à la théorie du relationnelcontrairement à la théorie du relationnel

Pas d'indexes CLUSTER sous MsAccessPas d'indexes CLUSTER sous MsAccess

Page 22: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

29

MsAccess : CREATE TABLEMsAccess : CREATE TABLECONSTRAINT = CONSTRAINT = Contraintes d'intégritéContraintes d'intégrité

Contrainte sur attribut unique:Contrainte sur attribut unique:CONSTRAINT nom {PRIMARY KEY | UNIQUE | NOT NULL | CONSTRAINT nom {PRIMARY KEY | UNIQUE | NOT NULL |

REFERENCES foreigntable [(foreignfield1, foreignfield2)]}REFERENCES foreigntable [(foreignfield1, foreignfield2)]} Contrainte sur plusieurs attributs: Contrainte sur plusieurs attributs:

CONSTRAINT nameCONSTRAINT name

{PRIMARY KEY (primary1[, primary2 [, ...]]) |{PRIMARY KEY (primary1[, primary2 [, ...]]) |

UNIQUE (unique1[, unique2 [, ...]]) |UNIQUE (unique1[, unique2 [, ...]]) |

NOT NULL (notnull1[, notnull2 [, ...]]) |NOT NULL (notnull1[, notnull2 [, ...]]) |

FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]}foreignfield2 [, ...]])]}

foreigntable = la table avec la clé primaire référencée foreigntable = la table avec la clé primaire référencée le langage de définition de QBE d ’MsAccess permet de définir

davantage de contraintes (comme on a vu en partie)– surtout les contraintes prédicatives d intégrité

» mono ou multi-attribut, mais mono-table.

Page 23: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

30

La table nommée P_1est en fait la table P

Les clauses CASCADE n ’existent qu’en QBE de MsAccess

Page 24: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

31

Exercice : que veulent dire ces contraintes ?

Les clauses «is Null> dans les Validation Rules

sont-elles utiles ?

Page 25: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

32

Sous-Tables en MsAccess Sous-Tables en MsAccess Sous-Tables en MsAccess Sous-Tables en MsAccess

Une table peut avoir Une table peut avoir uneune sous-table sous-table – dite sous-feuilledite sous-feuille

La sous-table « auto » contient la clé La sous-table « auto » contient la clé étrangère de sa table (feuille)étrangère de sa table (feuille)

Alternativement, la sous-table est choisie à Alternativement, la sous-table est choisie à travers un lien sémantique défini travers un lien sémantique défini manuellementmanuellement– Table ou requêteTable ou requête

Page 26: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

33

Sous-Tables en MsAccess Sous-Tables en MsAccess Sous-Tables en MsAccess Sous-Tables en MsAccess

Dans la base SPDans la base SP– Table SP est automatiquement la sous-table de Table SP est automatiquement la sous-table de

SS– Table S peut être choisie manuellement comme Table S peut être choisie manuellement comme

sous-table de SPsous-table de SP» Avec le champs père SP.S# et champs fils S.S#Avec le champs père SP.S# et champs fils S.S#

– Suggérés par MsAccessSuggérés par MsAccess

– Les liens S -> SP -> S sont alors transitifsLes liens S -> SP -> S sont alors transitifs

Page 27: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

34

Sous-Tables en MsAccess Sous-Tables en MsAccess Sous-Tables en MsAccess Sous-Tables en MsAccess

Dans la base SPDans la base SP– Soit la requête « Quantité / fournisseur » :Soit la requête « Quantité / fournisseur » :

» Select Sum (Qty) From SP Group by [S#] ;Select Sum (Qty) From SP Group by [S#] ;

– Cette requête peut être rendue sous-feuille de Cette requête peut être rendue sous-feuille de SPSP

– Elle matérialise alors le concept de l’attribut Elle matérialise alors le concept de l’attribut dérivé d’UML pour SPdérivé d’UML pour SP

– Le formulaire résultant apparaît comme entité Le formulaire résultant apparaît comme entité structuréestructurée

Page 28: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

35

Sous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccess On crée une sous-tableOn crée une sous-table

– Sur le menu propriétés d’une tableSur le menu propriétés d’une table» Auto / Aucune / Nom de la table / requêteAuto / Aucune / Nom de la table / requête

» On peut fixer la hauteur de la sous-fenêtre ou la laisser auto On peut fixer la hauteur de la sous-fenêtre ou la laisser auto (option 0 cm)(option 0 cm)

» La sous-feuille peut apparaître La sous-feuille peut apparaître in extensoin extenso (ligne « étendue » (ligne « étendue » oui) ou par « + » seulement oui) ou par « + » seulement

– à cliquer pour la voir éténdueà cliquer pour la voir éténdue

– Sur le menu Insertion de la vue de la table ouverteSur le menu Insertion de la vue de la table ouverte» La sous-feuille est signalée par « + » seulementLa sous-feuille est signalée par « + » seulement

Page 29: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

36

Sous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccess

Page 30: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

37

Sous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccess

Page 31: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

38

Sous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccessSous-Tables en MsAccess

Réalisation limitée d’une table à

attributs hérités. Litwin, W. Ketabchi M. Risch, T.

« Relations with Inherited Attributes »

HPL Tech Rep. HPL-DTD-92-45,

April. 1992), 30.

Page 32: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

39

CREATE INDEXCREATE INDEX(MSAccess)(MSAccess)

CREATE INDEXCREATE INDEX(MSAccess)(MSAccess)

Création d’un index qui n’a pas été fait par Création d’un index qui n’a pas été fait par la clause de contraintela clause de contrainte

Création d’un index qui ne peut pas être fait Création d’un index qui ne peut pas être fait par la clause de contraintepar la clause de contrainte– Options d’ordre…Options d’ordre…

Création alternative de la clCréation alternative de la clé primaire é primaire Création d’un pseudo-index sur la table liCréation d’un pseudo-index sur la table liéeée

Page 33: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

41

CREATE INDEXCREATE INDEX(MSAccess)(MSAccess)

CREATE INDEXCREATE INDEX(MSAccess)(MSAccess)

Il existe l’alternative QBEIl existe l’alternative QBE– Commande Index dans le Menu Affichage ou bouton droit Commande Index dans le Menu Affichage ou bouton droit

» Quand la table est ouverteQuand la table est ouverte

Page 34: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

45

SQL: manipulation de donnéesSQL: manipulation de donnéesSQL: manipulation de donnéesSQL: manipulation de données

expression générale de sélection:expression générale de sélection:SELECT [DISTINCT] attribut(s)SELECT [DISTINCT] attribut(s)

FROM table(s)FROM table(s)[WHERE condition][WHERE condition][GROUP BY field(s) ][GROUP BY field(s) ][HAVING condition ][HAVING condition ][ORDER BY attribute(s)][ORDER BY attribute(s)]

basée sur le calcul de tuplebasée sur le calcul de tuple produit produit uneune table table temporaire temporaire (en général avec des (en général avec des

duplicata)duplicata)

Page 35: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

46

ExamplesExamplesExamplesExamples

En pratique sur MsAccessEn pratique sur MsAccess Sauf ceux spécifiques àSauf ceux spécifiques à

– SQL-ServerSQL-Server– DB2DB2

Page 36: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

47

S# SNAME STATUS CITYS1 Smith 20 London

S2 Jones 10 Paris

S3 Blake 30 Paris

S4 Clark 20 London

S5 Adams 30 Athens

P# PNAME COLOR WEIGHT CITYP1 Nut Red 12 London

P2 Bolt Green 17 Paris

P3 Screw Blue 14 Rome

P4 Screw Red 12 London

P5 Cam Blue 19 Paris

P6 Cog Red 19 London

S# P# QTYS1 P1 300

S1 P2 200

S1 P3 400

S1 P4 200

S1 P5 100

S1 P6 100

S2 P1 300

S2 P2 400

S3 P2 200

S4 P2 200

S4 P4 300

S4 P5 400

Exemple canon

S

P

SP

Page 37: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

48

MsAccess MsAccess SELECTSELECTMsAccess MsAccess SELECTSELECT

SELECTSELECT [predicate] { * | table.* | [table.]field1 [, table.]field2.[, ...]]} [predicate] { * | table.* | [table.]field1 [, table.]field2.[, ...]]}

[AS alias1 [, alias2 [, ...]]][AS alias1 [, alias2 [, ...]]]FROM tableexpression [, ...]FROM tableexpression [, ...][IN externaldatabase][IN externaldatabase][WHERE... ][WHERE... ][GROUP BY... ][GROUP BY... ][HAVING... ][HAVING... ][ORDER BY... ][ORDER BY... ][WITH OWNERACCESS OPTION][WITH OWNERACCESS OPTION]

Predicat: ALL, DISTINCT, Predicat: ALL, DISTINCT, DISTINCTROW, TOP.DISTINCTROW, TOP.

Page 38: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

49

Interrogations (vraiment) simplesInterrogations (vraiment) simplesInterrogations (vraiment) simplesInterrogations (vraiment) simples

Projections d'une table sur certains attributs:Projections d'une table sur certains attributs:

SELECT SELECT [[S#S#]] FROM S FROM S

Combien de lignes de programmationfaudrait-il pour cette requête en Cobol ? 20 ? 50 ? 100 ?

MsAccessS#s1s2s3s4s5

L'ordre de présentation est fixé par le SGBD et peut L'ordre de présentation est fixé par le SGBD et peut changer dans le tempschanger dans le temps

Page 39: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

50

Selection avec duplicataSelection avec duplicataSelection avec duplicataSelection avec duplicata

SELECT CITY FROM S;SELECT CITY FROM S;

cityLondonParisParisLondonAthens

Le résultat peut avoir les duplicata Le résultat peut avoir les duplicata alors, il n'est pas une relation, mais un alors, il n'est pas une relation, mais un bagbag

Page 40: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

51

Elimination de Elimination de duplicataduplicataElimination de Elimination de duplicataduplicata

SELECT SELECT DISTINCTDISTINCT CITY FROM S; CITY FROM S;

CITYAthensLondon

Paris

DISTINCTDISTINCT est optionnel pour deux raisons: est optionnel pour deux raisons:–éliminer les duplicata coûte en temps de réponseéliminer les duplicata coûte en temps de réponse–les fonctions agrégats en ont besoin.les fonctions agrégats en ont besoin.

Combien de lignes de programmation

faudrait-il pour cette requête en C ?

20 ? 50 ? 100 ?

Page 41: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

52

Selections multiplesSelections multiplesSelections multiplesSelections multiples

Les attributs apparaissent dans l’ordre de leur Les attributs apparaissent dans l’ordre de leur énumération dans la clause SELECTénumération dans la clause SELECT

SELECT [S#], CITY, SNAME FROM S;SELECT [S#], CITY, SNAME FROM S;

S# City SNames1 Paris Smiths2 Paris Joness3 Paris Blakes4 London Clarks5 Athens Adam

Page 42: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

53

SELECT * SELECT * SELECT * SELECT *

Tout sur toutes les fournitures :Tout sur toutes les fournitures :SELECT S#, P#, QTY FROM SP;SELECT S#, P#, QTY FROM SP;

Formulation plus courante :Formulation plus courante :SELECT * FROM SP;SELECT * FROM SP;

Ordre d'attributs est celui de CREATE TABLEOrdre d'attributs est celui de CREATE TABLE Cette formulation est plus simple, mais deconseillée Cette formulation est plus simple, mais deconseillée

pour les programmes d'applicationpour les programmes d'application

pourquoi ? pourquoi ?

Page 43: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

54

ORDER BYORDER BYORDER BYORDER BY

SELECT * FROM SPSELECT * FROM SP

ORDER BY QTY DESC, [S#];ORDER BY QTY DESC, [S#];

Page 44: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

55

ORDER BYORDER BYORDER BYORDER BYSELECT * FROM SPSELECT * FROM SP

ORDER BY QTY DESC, [S#];ORDER BY QTY DESC, [S#];

S#S# p# Qtyp# Qtys1s1 p3p3 400400s2s2 p2p2 400400s4s4 p5p5 400400s1s1 p1p1 300300s2s2 p1p1 300300s4s4 p4p4 300300s1s1 p4p4 200200s1s1 p2p2 200200s3s3 p2p2 200200s4s4 p2p2 200200s1s1 p6p6 100100s1s1 p5p5 100100

Combien de lignes de programmation

faudrait-il pour cette requête en PL1 ? 20 ? 50 ? 100 ?

Page 45: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

56

ORDER BYORDER BYORDER BYORDER BYSELECT * FROM SPSELECT * FROM SP

ORDER BY QTY DESC, [S#];ORDER BY QTY DESC, [S#];

Et la quantité nulle serait où ?

S#S# p# Qtyp# Qtys1s1 p3p3 400400s2s2 p2p2 400400s4s4 p5p5 400400s1s1 p1p1 300300s2s2 p1p1 300300s4s4 p4p4 300300s1s1 p4p4 200200s1s1 p2p2 200200s3s3 p2p2 200200s4s4 p2p2 200200s1s1 p6p6 100100s1s1 p5p5 100100

Page 46: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

57

TOPTOPTOPTOP

SELECT top 3 [S#] AS [Les petits], [P#], QTY SELECT top 3 [S#] AS [Les petits], [P#], QTY

FROM SPFROM SP

ORDER BY QTY ASC, [S#] ;ORDER BY QTY ASC, [S#] ;

Page 47: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

58

TOPTOPTOPTOP

SELECT top 3 [S#] AS [Les petits], [P#], QTY SELECT top 3 [S#] AS [Les petits], [P#], QTY

FROM SPFROM SP

ORDER BY QTY ASC, [S#] ;ORDER BY QTY ASC, [S#] ;

Les petitsLes petits Product IDProduct ID QTYQTYs1s1 p6p6 100100s1s1 p5p5 100100s1s1 p4p4 200200s1s1 p2p2 200200

Page 48: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

59

TOPTOPTOPTOP

SELECT top 3 [S#] AS [Les petits], [P#], QTY SELECT top 3 [S#] AS [Les petits], [P#], QTY

FROM SPFROM SP

ORDER BY QTY ASC, [S#] ;ORDER BY QTY ASC, [S#] ;

Les petitsLes petits Product IDProduct ID QTYQTYs1s1 p6p6 100100s1s1 p5p5 100100s1s1 p4p4 200200s1s1 p2p2 200200

Mot-clé utile, mais pas dans SQL standard (MsAccess)Mot-clé utile, mais pas dans SQL standard (MsAccess) - essaye de formuler cette requête en SQL standard- essaye de formuler cette requête en SQL standard

Pas de distinction entre les duplicata par rapport au critère d'ordre Pas de distinction entre les duplicata par rapport au critère d'ordre QTY, S# (les 3 tops sont devenus 4 tuples)QTY, S# (les 3 tops sont devenus 4 tuples)

Product ID ?

Page 49: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

60

Restrictions simplesRestrictions simplesRestrictions simplesRestrictions simples

SELECT [P#], PNAME FROM P WHERE COLOR = SELECT [P#], PNAME FROM P WHERE COLOR = 'RED';'RED';

Page 50: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

61

Restrictions simplesRestrictions simplesRestrictions simplesRestrictions simples

SELECT [P#], PNAME FROM P WHERE COLOR = SELECT [P#], PNAME FROM P WHERE COLOR = 'RED';'RED';

Product IDProduct ID Product NameProduct Namep1p1 nutsnutsp4p4 screwscrewp6p6 cogcog

Les noms d'attributs sont les légendes créées à la création de PLes noms d'attributs sont les légendes créées à la création de P L'ordre de tuples délivrés est définit par le SGBD et peut changer L'ordre de tuples délivrés est définit par le SGBD et peut changer

d'une exécution à l'autre d'une exécution à l'autre Est-il possible de faire:Est-il possible de faire:

SELECT [Product ID], [Product Name]…SELECT [Product ID], [Product Name]…

Page 51: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

62

Restrictions composéesRestrictions composéesRestrictions composéesRestrictions composées

SELECT [P#], PNAME, CITY SELECT [P#], PNAME, CITY FROM P FROM P WHERE COLOR = 'RED' AND NOT CITY = 'PARIS';WHERE COLOR = 'RED' AND NOT CITY = 'PARIS';

Page 52: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

63

Restrictions Restrictions composéescomposéesRestrictions Restrictions composéescomposées

SELECT [P#], PNAME, CITY SELECT [P#], PNAME, CITY FROM P FROM P WHERE COLOR = 'RED' AND NOT CITY = 'PARIS';WHERE COLOR = 'RED' AND NOT CITY = 'PARIS';

Product IDProduct ID Product NameProduct Name citycityp1p1 nutsnuts londonlondonp4p4 screwscrew londonlondonp6p6 cogcog londonlondon

Page 53: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

64

Restrictions Restrictions composéescomposéesRestrictions Restrictions composéescomposées

Les formulations :Les formulations :SELECT [P#], PNAME, CITY SELECT [P#], PNAME, CITY FROM P FROM P WHERE WHERE COLOR = 'RED' AND CITY = ‘THIERS';COLOR = 'RED' AND CITY = ‘THIERS';

et :et :SELECT [P#], PNAME, CITY SELECT [P#], PNAME, CITY FROM P FROM P WHERE WHERE CITY = CITY = THIERS'THIERS' AND COLOR = 'RED' AND COLOR = 'RED';;

ne donnent pas toujours les mêmes temps d’exécutionne donnent pas toujours les mêmes temps d’exécution.. Bien qu’elles soient logiquement équivalentesBien qu’elles soient logiquement équivalentes Notamment si les deux attributs sont indexNotamment si les deux attributs sont indexééss Il vaut mieux mettre d’abordIl vaut mieux mettre d’abord la clause en général plus la clause en général plus

sélectivesélective– vraie pour moins de tuplesvraie pour moins de tuples (ou aucun si possible) (ou aucun si possible)

Page 54: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

65

Restrictions sur nulsRestrictions sur nulsRestrictions sur nulsRestrictions sur nuls

Un nul n’est pas une valeurUn nul n’est pas une valeur Donc on a une clause spécialeDonc on a une clause spéciale

– IS [NOT] NULLIS [NOT] NULL Ex. Deux fournisseurs n ’ont pas de ville connue:Ex. Deux fournisseurs n ’ont pas de ville connue:

– Requête : est-ce que il y a dans S des villes inconnues?Requête : est-ce que il y a dans S des villes inconnues?

SELECT S.CitySELECT S.CityFROM S where city is null;FROM S where city is null;

CITY

A noter:

DISTINCT s ’applique aux nuls (à tort, pourquoi ?)

Vous avez dit bizarre pour la table de nuls ?

Page 55: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

66

SELECT [s#], city FROM S where SELECT [s#], city FROM S where city IN ('paris', 'london'); city IN ('paris', 'london');

Restrictions par clause INRestrictions par clause INRestrictions par clause INRestrictions par clause IN

S# SName Status Citys1 John 2 Pariss2 smith 10 Pariss3 Blake 30s4 Clark 20 Londons5 Adam 30 Athenss6 Bull 20 Pariss7 Ibm 100 Paris

s# citys1 Pariss2 Pariss4 Londons6 Pariss7 Paris

? SELECT [s#], city FROM S where city ? SELECT [s#], city FROM S where city NOT IN ('paris', 'london'); NOT IN ('paris', 'london');

? SELECT [s#], city FROM S where city ? SELECT [s#], city FROM S where city IN ('paris', 'london', null); IN ('paris', 'london', null);

? SELECT [s#], city FROM S where city ? SELECT [s#], city FROM S where city IN ('paris', 'london') or city is null; IN ('paris', 'london') or city is null;

Page 56: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

67

Expressions de valeurExpressions de valeurExpressions de valeurExpressions de valeur

SELECT [P#], PNAME, 2.1* weight as [Poids SELECT [P#], PNAME, 2.1* weight as [Poids ££ ] ]FROM P FROM P order by 2.1*weight desc;order by 2.1*weight desc;

Product IDProduct ID Product Name Product Name Poids Poids ££p6p6 cogcog 39.9 39.9p3p3 screwscrew 35.7 35.7p2p2 boltbolt 35.7 35.7p4p4 screwscrew 29.4 29.4p5p5 camcam 25.2 25.2p1p1 nutsnuts 25.2 25.2

Page 57: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

68

Expressions de valeurExpressions de valeurExpressions de valeurExpressions de valeur

On peut utiliser + - * / ^ et ( )On peut utiliser + - * / ^ et ( ) On peut sélectionner tous les attributs et une On peut sélectionner tous les attributs et une

expression de valeurexpression de valeurSELECT *, 2.1*weight as [Poids en KG], weight SELECT *, 2.1*weight as [Poids en KG], weight

+ weight/5 - + weight/5 - (weight^2 - weight*2.1) as [un jeu](weight^2 - weight*2.1) as [un jeu]FROM P FROM P order by 2.1*weight desc;order by 2.1*weight desc;

On peut utiliser une expression de valeur On peut utiliser une expression de valeur comme argument d’une clause de restrictioncomme argument d’une clause de restriction……. WHERE WEIGTH = 200 *2,1. WHERE WEIGTH = 200 *2,1

Page 58: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

69

Expressions de valeurExpressions de valeurExpressions de valeurExpressions de valeur

On ne peut pas utiliser dans ORDER BY l’alias On ne peut pas utiliser dans ORDER BY l’alias défini pour une expression de valeur dan la défini pour une expression de valeur dan la clause SELECTclause SELECT– Essayez … ORDER BY Essayez … ORDER BY [Poids [Poids ££ ] ]

? ? Vrai / Faux que les résultats des expressions de valeur Vrai / Faux que les résultats des expressions de valeur suivantes sont toujours comme suit :suivantes sont toujours comme suit :

0 * QTY = 0 0 * QTY = 0

QTY – QTY = 0QTY – QTY = 0

QTY / QTY = 1 si QTY <> 0QTY / QTY = 1 si QTY <> 0 La 1ère équation est d’importance par exemple pour La 1ère équation est d’importance par exemple pour

les requêtes où un attribut X sélectionné pour SELECT les requêtes où un attribut X sélectionné pour SELECT … X*QTY… pourraient avoir la valeur 0. … X*QTY… pourraient avoir la valeur 0.

Page 59: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

70

JointuresJointuresJointuresJointures

RaphaëlBrancusi

Page 60: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

71

JointuresJointuresJointuresJointures

SELECT distinct S.[S#], SNAME, [P#], Qty, CitySELECT distinct S.[S#], SNAME, [P#], Qty, CityFROM S, SP FROM S, SP where s.[s#]=sp.[s#] and city <> 'London';where s.[s#]=sp.[s#] and city <> 'London';

Page 61: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

72

Equi-jointuresEqui-jointuresEqui-jointuresEqui-jointures

SELECT distinct S.[S#], SNAME, [P#], Qty, CitySELECT distinct S.[S#], SNAME, [P#], Qty, CityFROM S, SP FROM S, SP where s.[s#]=sp.[s#] and city <> 'London';where s.[s#]=sp.[s#] and city <> 'London';

S#S# SNAMESNAME Product IDProduct ID QtyQty CityCitys2s2 JonesJones p1p1 300300 ParisPariss2s2 JonesJones p2p2 400400 ParisPariss3s3 BlakeBlake p2p2 200200 ParisParis

Page 62: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

77

Equi-jointures m-airesEqui-jointures m-airesEqui-jointures m-airesEqui-jointures m-aires

SELECT s.[s#], p.[P#], Qty, PnameSELECT s.[s#], p.[P#], Qty, PnameFROM S, SP, P where FROM S, SP, P where s.[s#]=sp.[s#] and sp.[p#]=p.[p#] s.[s#]=sp.[s#] and sp.[p#]=p.[p#] and s.[s#] and s.[s#] betweenbetween 's1' and 's3' 's1' and 's3' order by s.[S#], qty desc;order by s.[S#], qty desc;

Page 63: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

78

Equi-jointures m-airesEqui-jointures m-airesEqui-jointures m-airesEqui-jointures m-aires

SELECT s.[s#], p.[P#], Qty, PnameSELECT s.[s#], p.[P#], Qty, PnameFROM S, SP, P where FROM S, SP, P where s.[s#]=sp.[s#] and sp.[p#]=p.[p#] s.[s#]=sp.[s#] and sp.[p#]=p.[p#] and s.[s#] and s.[s#] betweenbetween 's1' and 's3' 's1' and 's3' order by s.[S#], qty desc;order by s.[S#], qty desc;

s#s# Product IDProduct ID QtyQty Product NameProduct Name

s1s1 p3p3 400400 screwscrews1s1 p1p1 300300 nutsnutss1s1 p4p4 200200 screwscrews1s1 p2p2 200200 boltbolts1s1 p6p6 100100 cogcogs1s1 p5p5 100100 camcams2s2 p2p2 400400 boltbolts2s2 p1p1 300300 nutsnutss3s3 p2p2 200200 boltbolt

Page 64: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

79

Clause BETWEENClause BETWEEN Le type d ’attribut détermine l évaluation de la Le type d ’attribut détermine l évaluation de la

clause:clause:– 20 n ’est pas BETWEEN 1 and 3 pour Number20 n ’est pas BETWEEN 1 and 3 pour Number

– 20 est BETWEEN 1 and 3 pour Text20 est BETWEEN 1 and 3 pour Text

? Date/Time ou Currency ?Date/Time ou Currency ?

? Et les nulsEt les nuls? sont ils sélectionnes par les clauses ci-dessoussont ils sélectionnes par les clauses ci-dessous– SELECT * FROM P where weight between 0 and 19;SELECT * FROM P where weight between 0 and 19;

– SELECT * FROM P where weight between null and 19;SELECT * FROM P where weight between null and 19;

– SELECT * FROM P where weight between 0 and null;SELECT * FROM P where weight between 0 and null;

– peut-on faire encore autrement pour trouver les poids peut-on faire encore autrement pour trouver les poids entre 10 et 19 ou inconnus ?entre 10 et 19 ou inconnus ?

Page 65: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

80

Equi-jointures m-airesEqui-jointures m-aires(avec *)(avec *)

Tous les attributs de toutes les tables dans la clause FROMTous les attributs de toutes les tables dans la clause FROM

SELECT *SELECT *FROM S, SP, P FROM S, SP, P where s.[s#]=sp.[s#] and where s.[s#]=sp.[s#] and pp.[.[pp#]=sp.[#]=sp.[pp#] #] and s. and s.city <> 'London';city <> 'London';

On peut aussi SELECT S.*, SP.*, P.* FROM S,SP, P bien-sûrOn peut aussi SELECT S.*, SP.*, P.* FROM S,SP, P bien-sûr

On peut ajouter des attributs additionnels On peut ajouter des attributs additionnels

SELECT *, 'Mecs d’Eurostar' as [D'ou viennent t'ils ?]SELECT *, 'Mecs d’Eurostar' as [D'ou viennent t'ils ?]FROM S, SP, P FROM S, SP, P where s.[s#]=sp.[s#] and p.[p#]=sp.[p#] and s.city <> 'London';where s.[s#]=sp.[s#] and p.[p#]=sp.[p#] and s.city <> 'London';

Page 66: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

81

Equi-jointuresEqui-jointuresEqui-jointuresEqui-jointures Equi-jointures peuvent être formulées pour tout attribut:Equi-jointures peuvent être formulées pour tout attribut: Mais, les types de données à joindre doivent être = compatiblesMais, les types de données à joindre doivent être = compatibles

SELECT s.[s#], p.[P#], Qty, Pname, s.city, p.citySELECT s.[s#], p.[P#], Qty, Pname, s.city, p.cityFROM S, SP, PFROM S, SP, Pwhere s.[s#]=sp.[s#] and sp.[p#]=p.[p#] and where s.[s#]=sp.[s#] and sp.[p#]=p.[p#] and s.city=p.citys.city=p.cityorder by s.city, s.[s#];order by s.city, s.[s#];

s#s# Product IDProduct ID QtyQty Product NameProduct Name S.cityS.city P.cityP.citys1s1 p6p6 100100 cogcog LondonLondon londonlondons1s1 p4p4 200200 screwscrew LondonLondon londonlondons1s1 p1p1 300300 nutsnuts LondonLondon londonlondons4s4 p4p4 300300 screwscrew LondonLondon londonlondons2s2 p2p2 400400 boltbolt ParisParis parispariss3s3 p2p2 200200 boltbolt ParisParis parisparis

Page 67: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

82

Possibilité nouvelle dans SQL2 (et MsAccess)Possibilité nouvelle dans SQL2 (et MsAccess) Prévue dans le nouvel SQL standard Prévue dans le nouvel SQL standard

– SQL-2SQL-2 Permet de standardiser la formulation de jointures Permet de standardiser la formulation de jointures

externesexternes – On les verra plus tardOn les verra plus tard

Permet aussi de fixer explicitement l’ordre de Permet aussi de fixer explicitement l’ordre de jointures jointures – Pour optimiser la requêtePour optimiser la requête

Equi-jointures dans la clause FROMEqui-jointures dans la clause FROMEqui-jointures dans la clause FROMEqui-jointures dans la clause FROM

Page 68: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

83

SELECT S.[S#], P.[p#], SP.Qty, PName, S.City, P.City FROM S SELECT S.[S#], P.[p#], SP.Qty, PName, S.City, P.City FROM S INNER JOININNER JOIN (P (P INNER JOININNER JOIN SP SP ONON P.[P#] = SP.[p#]) P.[P#] = SP.[p#]) ONON (S.City = P.City) AND (S.[S#] = SP.[S#])(S.City = P.City) AND (S.[S#] = SP.[S#])ORDER BY S.City, S.[S#];ORDER BY S.City, S.[S#];

Equi-jointures dans la clause FROMEqui-jointures dans la clause FROMEqui-jointures dans la clause FROMEqui-jointures dans la clause FROM

s# Product IDs# Product ID QtyQty Product NameProduct Name S.cityS.city P.cityP.citys1s1 p6p6 100100 cogcog LondonLondon londonlondons1s1 p4p4 200200 screwscrew LondonLondon londonlondons1s1 p1p1 300300 nutsnuts LondonLondon londonlondons4s4 p4p4 300300 screwscrew LondonLondon londonlondons2s2 p2p2 400400 boltbolt ParisParis parispariss3s3 p2p2 200200 boltbolt ParisParis parisparis

Page 69: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

84

Theta-jointures & Self-jointuresTheta-jointures & Self-jointuresTheta-jointures & Self-jointuresTheta-jointures & Self-jointures

L'opérateur L'opérateur TT de comparaison dans une clause de jointure peut-être en de comparaison dans une clause de jointure peut-être en fait : fait : – TT=, <, <==, <, <=>, >=>, >=<>}<>}

Une table peut-être jointe avec elle-mêmeUne table peut-être jointe avec elle-même– On suppose que les noms On suppose que les noms de de fournisseurs fournisseurs ssont ont tos tos différentsdifférents

SELECT s1.[s#], s1.sname, s2.[s#], s2.sname, s1.citySELECT s1.[s#], s1.sname, s2.[s#], s2.sname, s1.cityFROM FROM s s1, s s2 s s1, s s2 /* /* s1, s2 sont dess1, s2 sont des aliases aliasesWHERE s1.city = s2.city and WHERE s1.city = s2.city and s1.ss1.snamename << s2.s s2.snamename;;

s1.s#s1.s# s1.snames1.sname s2.s#s2.s# s2.snames2.sname citycity

s4s4 ClarkClark s1s1 SmithSmith LondonLondonss3 3 BlakeBlake ss22 JonesJones ParisParis

Page 70: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

85

Jointures externesJointures externesJointures externesJointures externes

Conserve les tuples sans corresp. sur les attributs de jointureConserve les tuples sans corresp. sur les attributs de jointure- jointure gauche (LEFT) conserve - jointure gauche (LEFT) conserve

les tuples à gaucheles tuples à gauche- jointure droite (RIGHT) conserve - jointure droite (RIGHT) conserve

les tuples à droiteles tuples à droite

SELECT S.[S#], city, SP.QtySELECT S.[S#], city, SP.QtyFROM FROM S LEFT JOIN SP S LEFT JOIN SP ON S.[S#] = SP.[S#]ON S.[S#] = SP.[S#]where (qty > 200 or where (qty > 200 or qty is nullqty is null) and not city = 'london';) and not city = 'london';

S#S# citycity QtyQty

s2s2 ParisParis 300300s2s2 ParisParis 400400s5s5 AthensAthens

s5

s7 p6 100

Page 71: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

86

Jointures externesJointures externes(propriétés algébriques)(propriétés algébriques)

Les jointures classiques dites Les jointures classiques dites internesinternes sont associatives sont associatives

Celle externes ne sont pasCelle externes ne sont pas

A démontrerA démontrer

La notation dans la clause WHERE pourrait être La notation dans la clause WHERE pourrait être ambiguëambiguë Pourquoi ?Pourquoi ?

D’où la notation algébrique dans la clause FROMD’où la notation algébrique dans la clause FROM

Introduite par SQL-2Introduite par SQL-2

Elles s’appliquent aussi aux jointures classiques dites dès Elles s’appliquent aussi aux jointures classiques dites dès lors lors internesinternes

Page 72: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

87

Jointure Jointure externe complèteexterne complèteJointure Jointure externe complèteexterne complèteSELECT pname, S.SName, S.City, P.CitySELECT pname, S.SName, S.City, P.City

FROM P RIGHT JOIN S on P.City = S.CityFROM P RIGHT JOIN S on P.City = S.CityunionunionSELECT pname, S.SName, S.City, P.CitySELECT pname, S.SName, S.City, P.CityFROM P left JOIN S ON P.City = S.City ;FROM P left JOIN S ON P.City = S.City ;

pname SName S.City P.CityAdams Athens

bolt Blake Paris parisbolt Jones Paris parisbolt smith Paris pariscam Blake Paris pariscam Jones Paris pariscam smith Paris pariscog Clark London londonnuts Clark London londonscrew romescrew Clark London london

Page 73: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

88

SELECT pname, S.SName, S.City, P.CitySELECT pname, S.SName, S.City, P.CityFROM P RIGHT JOIN S ON P.City = S.CityFROM P RIGHT JOIN S ON P.City = S.Citywhere where p.city <> 'london'p.city <> 'london' or or p.city is nullp.city is nullUNION UNION SELECT pname, S.SName,SELECT pname, S.SName,S.City, P.CityS.City, P.CityFROM P left JOIN S ON P.City = S.CityFROM P left JOIN S ON P.City = S.Citywhere where s.city <> 'london's.city <> 'london' or or s.city is nulls.city is null;;

Jointure externe complèteJointure externe complèteavec une sélectionavec une sélection

Jointure externe complèteJointure externe complèteavec une sélectionavec une sélection

pname SName S.City P.CityAdams Athens

bolt Blake Paris parisbolt Jones Paris parisbolt smith Paris pariscam Blake Paris pariscam Jones Paris pariscam smith Paris parisscrew rome

Page 74: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

89

Jointure externe Jointure externe self ou avec theta-self ou avec theta-comparaisoncomparaison

Jointure externe Jointure externe self ou avec theta-self ou avec theta-comparaisoncomparaison

Self-jointure externe est possibleSelf-jointure externe est possible– p.e. SP left joint SP…p.e. SP left joint SP… N° de tout fournisseur N° de tout fournisseur qui qui fournit fournit une une pièce pièce en en quantité la même quantité la même

que celle d’un autre fournisseur ou est inconnueque celle d’un autre fournisseur ou est inconnue

Les opérateurs Les opérateurs T T s’appliquent aussi aux s’appliquent aussi aux jointures externes jointures externes TT=, <, <==, <, <=>, >=>, >=<>}<>} N°s de N°s de tout tout fournisseur qui fournit une pièce en quantité moindre fournisseur qui fournit une pièce en quantité moindre

qu’un autre fournisseur d’une même pièce ou en quantité qu’un autre fournisseur d’une même pièce ou en quantité inconnue.inconnue.

Page 75: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

90

Jointures externes Jointures externes DB2 & SQL-Server & SQL-2… DB2 & SQL-Server & SQL-2…

Jointures externes Jointures externes DB2 & SQL-Server & SQL-2… DB2 & SQL-Server & SQL-2…

On utilise les déclarationsOn utilise les déclarations– LEFT, RIGHT & FULL OUTER JOINLEFT, RIGHT & FULL OUTER JOIN

» note OUTERnote OUTER

SELECT pname, S.SName, S.City, P.City FROM P SELECT pname, S.SName, S.City, P.City FROM P

FULL OUTER JOINFULL OUTER JOIN S ON P.City = S.City S ON P.City = S.Citywhere where p.city <> 'london'p.city <> 'london' or or p.city is nullp.city is null

pname SName S.City P.CityAdams Athens

bolt Blake Paris parisbolt Jones Paris parisbolt smith Paris pariscam Blake Paris pariscam Jones Paris pariscam smith Paris parisscrew rome

SQL-2 a le verbe USING pour les attr. de jointure d ’un même nom (USING (CITY). Les mots FULL ou INNER sont optionnels

-certains dialectes remplacent, LEFT, RIGHT, FULL par :

P.City *= S.City P.City = S.City (+)+)

Page 76: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

91

Mélange de jointures externes et internesMélange de jointures externes et internesMélange de jointures externes et internesMélange de jointures externes et internes

Explosif (sous MsAccess surtout):Explosif (sous MsAccess surtout): OK: OK:

SELECT sP.Qty, s.[S#], s.City, sP.[p#]SELECT sP.Qty, s.[S#], s.City, sP.[p#]FROM s FROM s RIGHT RIGHT JOIN (p INNER JOIN sP ON p.[P#] = sP.[p#]) ON JOIN (p INNER JOIN sP ON p.[P#] = sP.[p#]) ON sP.[S#] = s.[S#];sP.[S#] = s.[S#];

interdit :interdit :

SELECT sP.Qty, s.[S#], s.City, sP.[p#]SELECT sP.Qty, s.[S#], s.City, sP.[p#]FROM s FROM s LEFTLEFT JOIN (p INNER JOIN sP ON p.[P#] = sP.[p#]) ON JOIN (p INNER JOIN sP ON p.[P#] = sP.[p#]) ON sP.[S#] = s.[S#];sP.[S#] = s.[S#];

Page 77: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

92

Mélange de jointuresMélange de jointures externes et internes externes et internesMélange de jointuresMélange de jointures externes et internes externes et internes

En deux requêtes c'est OK par contre:En deux requêtes c'est OK par contre: Query-scratch1:Query-scratch1:

SELECT *SELECT *

FROM p INNER JOIN sp ON p.[P#] = sp.[p#];FROM p INNER JOIN sp ON p.[P#] = sp.[p#]; ::

SELECT s.[s#], qty, [Query-scratch1].color SELECT s.[s#], qty, [Query-scratch1].color FROM s left JOIN [Query-scratch1] ON [QueryFROM s left JOIN [Query-scratch1] ON [Queryscratch1].[S#] = S.[S#];scratch1].[S#] = S.[S#];

Page 78: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

93

RésultatRésultatRésultatRésultat

s#s# qtyqty colorcolors1s1 100100 redreds1s1 100100 blueblues1s1 200200 redreds1s1 400400 blueblues1s1 200200 greengreens1s1 300300 redreds2s2 400400 greengreens2s2 300300 redreds3s3 200200 greengreens4s4 400400 blueblues4s4 300300 redreds4s4 200200 greengreens5s5

Page 79: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

94

Jointures implicitesJointures implicitesJointures implicitesJointures implicites

Simplifient la formulation de la requêteSimplifient la formulation de la requête Générées par MsAccess à partir de Générées par MsAccess à partir de

contraintes d'intégrité référentielles et les contraintes d'intégrité référentielles et les liens sémantiquesliens sémantiques– jointures naturelles (internes)jointures naturelles (internes)– jointures externesjointures externes

Générées dans SQL, mais uniquement Générées dans SQL, mais uniquement quand la requête est formulée en QBEquand la requête est formulée en QBE

Page 80: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

On a cliquéd'abord ici

On a cliquéensuite ici

Declaration de jointures implicites

Page 81: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

Puis, clique et...

Tires avec la souris,

Ecris la restriction

Formulation de la requête avec les jointures implicites en QBE

Page 82: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

97

Résultat SQLRésultat SQLRésultat SQLRésultat SQL

SELECT DISTINCTROW S.SName, P.CitySELECT DISTINCTROW S.SName, P.City

FROM PFROM P INNER JOIN (S INNER JOIN SP ON S.[S#] = INNER JOIN (S INNER JOIN SP ON S.[S#] = SP.[S#]) ON P.[P#] = SP.[p#]SP.[S#]) ON P.[P#] = SP.[p#]

WHERE ((P.City="paris"));WHERE ((P.City="paris")); Jointure impl.générée

automatiquement

Page 83: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

98

LimitationsLimitationsLimitationsLimitations

Si les tables choisies ne sont pas Si les tables choisies ne sont pas directement en relation, alors, il faut ajouter directement en relation, alors, il faut ajouter aussi sous QBE toutes les tables aussi sous QBE toutes les tables intermédiairesintermédiaires– Pour formuler SELECT sname, pname FROM Pour formuler SELECT sname, pname FROM

S,P… avec les jointure implicites, il faut aussi S,P… avec les jointure implicites, il faut aussi inclure sous QBE la table SPinclure sous QBE la table SP

» Bien que l’on ne sélectionne aucun attribut de cette Bien que l’on ne sélectionne aucun attribut de cette table table

Page 84: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

99

LimitationsLimitationsLimitationsLimitations

L’ordre de clauses résulte de celui de L’ordre de clauses résulte de celui de sélection de tables, mais seulement si l’on sélection de tables, mais seulement si l’on suit les relations directes. suit les relations directes. – Essayez ajouter les tables et regarder le résultat Essayez ajouter les tables et regarder le résultat

SQL, selon les permutations suivants: SQL, selon les permutations suivants: » P,SP,S puis S,SP,P, puis P, S, SP puis S,P, SPP,SP,S puis S,SP,P, puis P, S, SP puis S,P, SP

– Conclusion ?Conclusion ?

Page 85: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

100

LimitationsLimitationsLimitationsLimitations

Une correspondance déclarée entre les Une correspondance déclarée entre les attributs d'une même relation ne génère pas attributs d'une même relation ne génère pas de jointure implicitede jointure implicite– sous MsAccess 2sous MsAccess 2

Pourquoi cette limitation ?Pourquoi cette limitation ?– une bonne questionune bonne question– sans bonne réponse de ma partsans bonne réponse de ma part– à adresser à Microsoftà adresser à Microsoft

Page 86: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

101

Page 87: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

102

LimitationsLimitationsLimitationsLimitations

Une correspondance multiple entre deux tablesUne correspondance multiple entre deux tables– donne lieu à AND entre les clauses correspondantesdonne lieu à AND entre les clauses correspondantes

» c'est bienc'est bien

– mais, peut donner lieu à une génération erronéemais, peut donner lieu à une génération erronée» une jointure implicite invisible sur l'image QBEune jointure implicite invisible sur l'image QBE

C'est un "C'est un "bugbug""– MsAccess 2MsAccess 2

Pourquoi ?Pourquoi ?– bonne question à Microsoftbonne question à Microsoft

Pour en savoir + sur les jointures implicites en généralPour en savoir + sur les jointures implicites en général– Implicit Joins in the Structural Data Model. IEEE-COMPSAC, Kyoto, (Sep. Implicit Joins in the Structural Data Model. IEEE-COMPSAC, Kyoto, (Sep.

1991). With Suk Lee, B., Wiederhold, G.1991). With Suk Lee, B., Wiederhold, G.

Page 88: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

103

Page 89: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

104

Page 90: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

105

Page 91: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

106

Page 92: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

107

Page 93: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

108

Page 94: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

109

Jointure AutomatiqueJointure AutomatiqueJointure AutomatiqueJointure Automatique

Une jointure implicite entre deux attributs de deux Une jointure implicite entre deux attributs de deux tables différentes sélectionnées pour une requête : tables différentes sélectionnées pour une requête : – sans lien sémantique dans le schéma sans lien sémantique dans le schéma – de type compatiblede type compatible– d’un même nomd’un même nom– avec au moins étant la clé primaireavec au moins étant la clé primaire

A ne pas confondre avec une self-jointureA ne pas confondre avec une self-jointure Existent dans MsAccess2000Existent dans MsAccess2000

– en optionen option

Page 95: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

112

Fonctions agrégatsFonctions agrégatsFonctions agrégatsFonctions agrégats

Un nombre Un nombre trèstrès limité: limité:– COUNT, SUM, AVG, MAX, MIN,COUNT, SUM, AVG, MAX, MIN,

» MIN, MAX s’applique aux Nuls ( à tort)MIN, MAX s’applique aux Nuls ( à tort)

» MsAccess: StDev, Var, First, Last MsAccess: StDev, Var, First, Last » MsAccess97: VarP, StDevPMsAccess97: VarP, StDevP

– calcul sur la population, pendant que Var, StDev utilisent un échantillon calcul sur la population, pendant que Var, StDev utilisent un échantillon

A mettre dans SELECT A mettre dans SELECT

SELECT sum(P.Weight) AS PoidsCumuleSELECT sum(P.Weight) AS PoidsCumuleFROM P;FROM P;

PoidsCumule

91

Page 96: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

113

Fonctions agrégatsFonctions agrégatsFonctions agrégatsFonctions agrégats SELECT Count (*) FROM S WHERE… compte tous les tuplesSELECT Count (*) FROM S WHERE… compte tous les tuples

SELECT Count (CITY) FROM S ne compte pas de nullsSELECT Count (CITY) FROM S ne compte pas de nulls– mais compte les doubles mais compte les doubles

SELECT COUNT (DISTINCT (CITY)) FROM S;SELECT COUNT (DISTINCT (CITY)) FROM S;

– Possible avec SQL ANSI, mais pas MsAccessPossible avec SQL ANSI, mais pas MsAccess» Pourquoi ? Pourquoi ?

- Très bonne question à Microsoft- Très bonne question à Microsoft– Possible dans SQL-Server ou DB2 ?– Formulable autrement avec MsAccess ?

On peut compter sur plusieurs champs, pourvus qu'ils ne soient pas On peut compter sur plusieurs champs, pourvus qu'ils ne soient pas tous nuls dans le tuple (MsAccess)tous nuls dans le tuple (MsAccess)

SELECT Count ("City & Status") FROM S;SELECT Count ("City & Status") FROM S; Compte les fournisseurs sans ville connueCompte les fournisseurs sans ville connue Compte le nombre de villes avec un fournisseur (MsAccess)Compte le nombre de villes avec un fournisseur (MsAccess)

Page 97: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

114

Fonctions agrégatsFonctions agrégatsFonctions agrégatsFonctions agrégats

SELECT Varp(SP.Qty) AS Varp, Var(SP.Qty) AS Var, SELECT Varp(SP.Qty) AS Varp, Var(SP.Qty) AS Var, StDev(SP.Qty) AS StDev, StDevp(SP.Qty) AS StDevpStDev(SP.Qty) AS StDev, StDevp(SP.Qty) AS StDevpFROM SP;FROM SP;

Varp Var StDev StDevp

15644.6280991736 17209.0909090909131.183424673588 125.078487755383

Page 98: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

115

GROUP BYGROUP BYGROUP BYGROUP BY

Permet d'appliquer les fonctions agrégats aux Permet d'appliquer les fonctions agrégats aux sous-tables, dites sous-tables, dites groupesgroupes, définies par l'égalité de , définies par l'égalité de certains attributscertains attributs

Inexistant dans SQL originel (et le modèle Inexistant dans SQL originel (et le modèle relationnel)relationnel)

Est populaire mais redondanteEst populaire mais redondante– ce qui est peu connu (voir le cours sur les Subtilités de ce qui est peu connu (voir le cours sur les Subtilités de

SQL)SQL) A été introduite par Peter Gray d'Univ. d'Aberdeen A été introduite par Peter Gray d'Univ. d'Aberdeen

(je crois).(je crois).

Page 99: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

116

GROUP BYGROUP BYGROUP BYGROUP BY

SELECT SELECT top 50 percenttop 50 percent [p#], sum (qty) as [tot-qty] from sp [p#], sum (qty) as [tot-qty] from sp GROUP BY [p#]GROUP BY [p#]Order by Order by sum (qty)sum (qty) desc; desc;

p#p# tot-qtytot-qtyp2p2 10001000p1p1 600600p5p5 500500p4p4 500500

Page 100: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

117

GROUP BYGROUP BYattributsattributs multiples multiples

GROUP BYGROUP BYattributsattributs multiples multiples

Tous les attributs sélectionnés non-agrégés forment Tous les attributs sélectionnés non-agrégés forment le GROUP BYle GROUP BY

SELECT S.SName, Sum(SP.Qty) as Somme, S.[S#]SELECT S.SName, Sum(SP.Qty) as Somme, S.[S#]FROM S INNER JOIN SP ON S.[S#] = SP.[S#]FROM S INNER JOIN SP ON S.[S#] = SP.[S#]WHERE SP.Qty > 100WHERE SP.Qty > 100GROUP BY GROUP BY S.SName, S.[S#] S.SName, S.[S#]

Sname Somme S#Clark 900 s4 Jones 700 s2Jones 200 s3Smith 1100 s1

Page 101: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

118

HAVINGHAVINGHAVINGHAVING

Permet de spécifier les prédicats sur les groupes de GROUP Permet de spécifier les prédicats sur les groupes de GROUP BY BY – et sur les attributs non agrégés, et sur les attributs non agrégés,

» double emploi avec WHEREdouble emploi avec WHERE

SELECT [p#], sum (qty) as [tot-qty] from sp SELECT [p#], sum (qty) as [tot-qty] from sp GROUP BY [p#]GROUP BY [p#]HAVING SUM (QTY) > 200HAVING SUM (QTY) > 200ORDER BY ORDER BY SUM (QTY)SUM (QTY) DESC; DESC;

p#p# tot-qtytot-qty

p2p2 10001000p1p1 600600p5p5 500500p4p4 500500p3p3 400400

Page 102: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

119

Sous-requêtesSous-requêtesSous-requêtesSous-requêtes

Une expression alternative de jointuresUne expression alternative de jointures Permet une optimisation manuellePermet une optimisation manuelle

– la sous-requête est exécutée d'abordla sous-requête est exécutée d'abord

Permet d'appliquer les fonctions agrégats dans Permet d'appliquer les fonctions agrégats dans la clause WHEREla clause WHERE

Permet d'appliquer le quantificateur EXISTSPermet d'appliquer le quantificateur EXISTS– et donc, indirectement, le quantificateur et donc, indirectement, le quantificateur

FORALL (universel)FORALL (universel)

Page 103: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

120

Sous-requêtesSous-requêtesSous-requêtesSous-requêtes

SELECT [s#], snameSELECT [s#], sname from S where s.[s#] in (select [s#] from sp where qty > 200); from S where s.[s#] in (select [s#] from sp where qty > 200);

SELECT [s#], sname, statusSELECT [s#], sname, statusfrom S where s.status = (select max (status) from s as S1);from S where s.status = (select max (status) from s as S1);

s#s# snamesname statusstatuss1s1 SmithSmith 3030s3s3 BlakeBlake 3030s5s5 AdamsAdams 3030

Page 104: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

121

La La requêterequête à sous-requête à sous-requête ::SELECT [S#], SNAME SELECT [S#], SNAME FROM S FROM S WHERE WHERE STATUS > 100 AND CITY IN STATUS > 100 AND CITY IN

(SELECT CITY FROM S WHERE CITY = ‘PARIS’);(SELECT CITY FROM S WHERE CITY = ‘PARIS’);

est est en en généralgénéral préférable préférable àà celle plus naturelle celle plus naturelle à à restrictions restrictions composées:composées:SELECT [S#], SNAME SELECT [S#], SNAME FROM S FROM S WHERE WHERE STATUS > 100 AND CITY = ‘PARIS’;STATUS > 100 AND CITY = ‘PARIS’;

LeLe temps d’exécution temps d’exécution estest plus petit. plus petit.– Si les deux attributs sont indexSi les deux attributs sont indexéés s – LLa plupart de fournisseurs est a plupart de fournisseurs est àà ParisParis– IIl y a peu de fournisseurs de Statut > 100l y a peu de fournisseurs de Statut > 100

Bien quBien que les deux e les deux requêterequêtess soient logiquement équivalentessoient logiquement équivalentes

Sous-requêtesSous-requêtesSous-requêtesSous-requêtes

Page 105: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

122

EXISTSEXISTSEXISTSEXISTS

SELECT [s#], sname, statusSELECT [s#], sname, status from S where exists from S where exists (select * from sp where [s#]=sp.[s#](select * from sp where [s#]=sp.[s#]and sp.[p#]='p2');and sp.[p#]='p2');

s#s# snamesname statusstatuss1s1 SmithSmith 3030s2s2 JonesJones 1010s3s3 BlakeBlake 3030s4s4 ClarkClark 2020s5s5 AdamsAdams 3030

Page 106: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

123

FORALL <-> NOT (NOT EXISTS)FORALL <-> NOT (NOT EXISTS)FORALL <-> NOT (NOT EXISTS)FORALL <-> NOT (NOT EXISTS)

SELECT [s#], snameSELECT [s#], snamefrom S where from S where not exists not exists (select * from p where (select * from p where not exists not exists ( select * from sp ( select * from sp where [s#]=s.[s#] and [p#]=p.[p#] ));where [s#]=s.[s#] and [p#]=p.[p#] ));

s#s# snamesname

s1s1 SmithSmith

Page 107: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

124

NOT...NOT EXISTSNOT...NOT EXISTSNOT...NOT EXISTSNOT...NOT EXISTS

SELECT distinct [s#] SELECT distinct [s#] from SP X where not exists from SP X where not exists (select * from sp y (select * from sp y where [s#]='s2' and not exists where [s#]='s2' and not exists (select * from sp z (select * from sp z where z.[s#]=x.[s#] and z.[p#]=y.[p#] )); where z.[s#]=x.[s#] and z.[p#]=y.[p#] ));

s#s#s1s1s2s2

C'est quoi ?C'est quoi ? Tous les fournisseurs qui fournissent au moins les pièces du Tous les fournisseurs qui fournissent au moins les pièces du

fournisseur 'S2'.fournisseur 'S2'.

Page 108: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

125

NOT...NOT EXISTSNOT...NOT EXISTSNOT...NOT EXISTSNOT...NOT EXISTS

SELECT distinct [s#] SELECT distinct [s#] from SP X where not exists from SP X where not exists (select * from sp y (select * from sp y where [s#]='s2' and not exists where [s#]='s2' and not exists (select * from sp z (select * from sp z where z.[s#]=x.[s#] and z.[p#]=y.[p#] )); where z.[s#]=x.[s#] and z.[p#]=y.[p#] ));

s#s#s1s1s2s2

C'est quoi ?C'est quoi ? Tous les fournisseurs qui fournissent au moins les pièces du Tous les fournisseurs qui fournissent au moins les pièces du

fournisseur 'S2'.fournisseur 'S2'.

SQL c'est simplecar non-procedural:une intention = une requête

Page 109: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

126

UNIONUNIONUNIONUNION

SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]'SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]'UNION UNION SELECT [P#], CITY FROM SP, S WHERE SP.[S#]=S.[S#] AND SELECT [P#], CITY FROM SP, S WHERE SP.[S#]=S.[S#] AND CITY >= 'B';CITY >= 'B';

Page 110: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

127

UNIONUNIONUNIONUNION

SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]'SELECT [P#], CITY FROM P WHERE CITY LIKE '[L-S]'UNION UNION SELECT [P#], CITY FROM SP, S WHERE SP.[S#]=S.[S#] AND SELECT [P#], CITY FROM SP, S WHERE SP.[S#]=S.[S#] AND CITY >= 'B';CITY >= 'B';

P#P# citycityp1p1 LondonLondonp1p1 ParisParisp2p2 LondonLondonp2p2 ParisParisp3p3 LondonLondonp4p4 LondonLondonp5p5 LondonLondonp6p6 LondonLondon

Tous les duplicata sont éliminés

Comment fairealors pour lesagrégats ?

Page 111: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

128

UNIONUNION MsAccessMsAccess

Les tables ou vues entières union-compatibles peuvent Les tables ou vues entières union-compatibles peuvent être référencées explicitementêtre référencées explicitement

TABLE Customers UNION TABLE Suppliers

On ne peut pas sélectionner d’attributs de type MEMO ou On ne peut pas sélectionner d’attributs de type MEMO ou OLEOLE– Y compris par * Y compris par *

» Déjà déconseillé pour les programmes d’applicationDéjà déconseillé pour les programmes d’application

Pas d opérateurs INTERSECT, EXCEPTPas d opérateurs INTERSECT, EXCEPT Comment faire alors ?Comment faire alors ?

Page 112: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

129

UNION ALLUNION ALLUNION ALLUNION ALL Préserve les duplicataPréserve les duplicata Nécessaire pour appliquer des agrégationsNécessaire pour appliquer des agrégations Mais, souvent il faut néanmoins dans ce but au moins 2 Mais, souvent il faut néanmoins dans ce but au moins 2

requêtes SQLrequêtes SQL– Défaut de conception SQLDéfaut de conception SQL– Solutions pratiques Solutions pratiques

» clause FROM imbriquéeclause FROM imbriquée» Une autre (DB2) voir cours SQL2 Une autre (DB2) voir cours SQL2

Dans l exemple qui suit, sous MsAccess, on veut des Dans l exemple qui suit, sous MsAccess, on veut des agrégations sur WEIGHTagrégations sur WEIGHT– la 1ere requête définie une vue appelée UNION-ALLla 1ere requête définie une vue appelée UNION-ALL– la 2eme requête calcule les agrégations vouluesla 2eme requête calcule les agrégations voulues

Page 113: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

130

UNION ALLUNION ALLUNION ALLUNION ALL

SELECT weight, p.city FROM P WHERE City like 'l*'SELECT weight, p.city FROM P WHERE City like 'l*'UNION ALL SELECT weight, s.city FROM p, SP, S WHEREUNION ALL SELECT weight, s.city FROM p, SP, S WHEREp.[p#]=sp.[p#] and sp.[s#]=s.[s#] and s.City like 'p*';p.[p#]=sp.[p#] and sp.[s#]=s.[s#] and s.City like 'p*';

weightweight citycity1212 londonlondon1414 londonlondon1919 londonlondon1212 ParisParis1717 ParisParis1717 ParisParis

Page 114: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

131

UNION ALLUNION ALLUNION ALLUNION ALL

SELECT weight, p.city FROM P WHERE City like 'l*'SELECT weight, p.city FROM P WHERE City like 'l*'UNION ALL SELECT weight, s.city FROM p, SP, S WHEREUNION ALL SELECT weight, s.city FROM p, SP, S WHEREp.[p#]=sp.[p#] and sp.[s#]=s.[s#] and s.City like 'p*';p.[p#]=sp.[p#] and sp.[s#]=s.[s#] and s.City like 'p*';

weightweight citycity1212 londonlondon1414 londonlondon1919 londonlondon1212 ParisParis1717 ParisParis1717 ParisParis

SELECT SELECT AVG(WEIGHT) AVG(WEIGHT) AS [AVG POIDS], AS [AVG POIDS], VAR(WEIGHT) VAR(WEIGHT) AS [VAR-POIDS],AS [VAR-POIDS], MAX(WEIGHT)MAX(WEIGHT) AS [POIDS-MAX] AS [POIDS-MAX]FROM [UNION-ALL];FROM [UNION-ALL];

avg poidsavg poids var poidsvar poids poids-maxpoids-max

15.166666666666715.1666666666667 8.566666666666678.56666666666667 1919

Page 115: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

132

Clause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquée

Définit une table dans la clause FROM d’une Définit une table dans la clause FROM d’une expression de sélection SQL (SQL-Select) expression de sélection SQL (SQL-Select) – Cette dernière peut-être imbriquée à son tourCette dernière peut-être imbriquée à son tour

Select attrs…FROM [tbls], Select attrs…FROM [tbls], (SQL-Select) (SQL-Select) Where …. ;Where …. ;

Clause non-documentée sous MsAccessClause non-documentée sous MsAccess– La traduction SQL-QBE est boguéeLa traduction SQL-QBE est boguée

» À essayerÀ essayer

Page 116: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

133

Clause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquée

Possibilités:Possibilités:– Agrégations par-dessus UNION ou UNION ALLAgrégations par-dessus UNION ou UNION ALL– Imbrication des expressions de valeurImbrication des expressions de valeur– Calcul de COUNT (DISTINCT)Calcul de COUNT (DISTINCT)

» MsAccessMsAccess

– Récursivité limitée Récursivité limitée

Page 117: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

134

Clause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquée

SELECT sum(weight) AS [poids-total]SELECT sum(weight) AS [poids-total]

FROM (SELECT weight, p.city FROM P FROM (SELECT weight, p.city FROM P WHERE City like 'l*'WHERE City like 'l*'

UNION ALL SELECT weight, s.city FROM UNION ALL SELECT weight, s.city FROM p, SP, S WHERE p.[p#]=sp.[p#] and sp.p, SP, S WHERE p.[p#]=sp.[p#] and sp.[s#]=s.[s#] and s.City like 'p*');[s#]=s.[s#] and s.City like 'p*');

Page 118: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

135

Clause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquéeClause FROM imbriquée

select avg(moy1) as [moyenne-des-moyennes]select avg(moy1) as [moyenne-des-moyennes]

FROMFROM

(SELECT avg(weight) as moy1 FROM P (SELECT avg(weight) as moy1 FROM P WHERE City like 'l*'WHERE City like 'l*'

UNION ALL SELECT avg(weight) as moy1 UNION ALL SELECT avg(weight) as moy1 FROM p, SP, S WHERE p.[p#]=sp.[p#] and FROM p, SP, S WHERE p.[p#]=sp.[p#] and sp.[s#]=s.[s#] and s.City like 'p*');sp.[s#]=s.[s#] and s.City like 'p*');

Page 119: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

136

Requêtes à paramètresRequêtes à paramètresRequêtes à paramètresRequêtes à paramètres un paramètre : un un paramètre : un [texte visualisé][texte visualisé] dans la clause dans la clause

WHERE pour que l'usager indique une valeurWHERE pour que l'usager indique une valeur– le texte peut être sans [], s’il ne désigne pas d’attribut et le texte peut être sans [], s’il ne désigne pas d’attribut et

n ’a pas de blancs, # etc.n ’a pas de blancs, # etc.» Possibilité à éviter à cause de conflit de noms possiblePossibilité à éviter à cause de conflit de noms possible

– "Paris" est une constante Paris serait un paramètre"Paris" est une constante Paris serait un paramètre

Le type de données d'un paramètre par défaut est Le type de données d'un paramètre par défaut est texte. texte.

On peut-être déclarer un type différent par la On peut-être déclarer un type différent par la clause PARAMETERclause PARAMETER

» recommandée pour un paramètre dans une recommandée pour un paramètre dans une expression de valeur (et expression de valeur (et obligatoireobligatoire dans la requête dans la requête qui suit)qui suit)

Page 120: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

137

Requêtes à paramètresRequêtes à paramètresRequêtes à paramètresRequêtes à paramètres On peut utiliser plusieurs paramètresOn peut utiliser plusieurs paramètres

– pour une clause BETWEEN [Qty Min ?] AND pour une clause BETWEEN [Qty Min ?] AND [Max ?] [Max ?]

On peut utiliser la clause LIKE On peut utiliser la clause LIKE [City ?][City ?] Alors la réponse doit être selon la Alors la réponse doit être selon la

sémantique de la clause LIKE, sémantique de la clause LIKE, – P. e., [L-P]* signifiera « toutes les villes qui P. e., [L-P]* signifiera « toutes les villes qui

commencent par une lettre entre L et P, incluscommencent par une lettre entre L et P, inclus Alternativement on peut ajouter les Alternativement on peut ajouter les

caractères génériques à la réponse d'usagercaractères génériques à la réponse d'usager– P.e. LIKE P.e. LIKE [City ?] & "*"[City ?] & "*"

Page 121: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

138

Requêtes à paramètresRequêtes à paramètresRequêtes à paramètresRequêtes à paramètres Le nom dans le paramètre a la priorité sur le nom de Le nom dans le paramètre a la priorité sur le nom de

l'attribut, si on génère un l'attribut, si on génère un conflitconflit de noms de noms

PARAMETERS [weight] Long;PARAMETERS [weight] Long;SELECT P.Weight, p.Weight+3/2, P.ColorSELECT P.Weight, p.Weight+3/2, P.ColorFROM PFROM PWHERE p.Weight+3/2 > WHERE p.Weight+3/2 > weightweight and and weightweight + 6 > p.weight; + 6 > p.weight;

On évite le conflit par l’emploi de P.WeightOn évite le conflit par l’emploi de P.Weight Note: il n ’y a ci-dessus qu’un seul param. Note: il n ’y a ci-dessus qu’un seul param. weightweight? Est-ce que c’est la même requêteEst-ce que c’est la même requête

PARAMETERS [weight] Long;PARAMETERS [weight] Long;SELECT Weight, Weight+3/2, P.ColorSELECT Weight, Weight+3/2, P.ColorFROM PFROM PWHERE Weight+3/2 > [WHERE Weight+3/2 > [weightweight] and [] and [weight]weight] + 6 > weight; + 6 > weight;

Page 122: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

139

Requêtes à paramètresRequêtes à paramètresRequêtes à paramètresRequêtes à paramètres Expression de paramètre peut être celle de Expression de paramètre peut être celle de

valeurvaleur……WHERE ... Prix = [Prix HT svp] * 1,2WHERE ... Prix = [Prix HT svp] * 1,2

? Est-ce une requête à paramètre Est-ce une requête à paramètre

SELECT S.SName, Sum(SP.Qty) as somme, S.[S#]SELECT S.SName, Sum(SP.Qty) as somme, S.[S#]FROM S INNER JOIN SP ON S.[S#] = SP.[S#]FROM S INNER JOIN SP ON S.[S#] = SP.[S#]WHERE SP.Qty > [100] WHERE SP.Qty > [100] GROUP BY S.SName, S.[S#] GROUP BY S.SName, S.[S#]

Page 123: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

140

Fonctions scalairesFonctions scalairesFonctions scalairesFonctions scalaires

S ’appliquent aux valeurs individuellesS ’appliquent aux valeurs individuelles– d ’attributsd ’attributs

– d agrégations SQLd agrégations SQL

Il y a plusieurs catégoriesIl y a plusieurs catégories– mathématiquesmathématiques

– financièresfinancières

– chaînes de caractèreschaînes de caractères

– ……..

Varient entre les dialectesVarient entre les dialectes– MsAccess possède UCASE, pas DB2MsAccess possède UCASE, pas DB2– DB2 possède LOG10, pas MsAccessDB2 possède LOG10, pas MsAccess

Page 124: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

141

Fonctions scalairesFonctions scalairesFonctions scalairesFonctions scalaires

Peuvent s’imbriquerPeuvent s’imbriquer– contrairement aux agrégats SQLcontrairement aux agrégats SQL

SELECT log((sum([qty]^2)^(1/2))) as exempleSELECT log((sum([qty]^2)^(1/2))) as exempleFROM SP group by [p#]FROM SP group by [p#]having int(log(sum([qty]))) = 5having int(log(sum([qty]))) = 5

exemple5.708757640082795.99146454710798

Page 125: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

142

Tabulations CroiséesTabulations Croisées(Crosstab queries)(Crosstab queries)

Tabulations CroiséesTabulations Croisées(Crosstab queries)(Crosstab queries)

Présentent les résultat sous forme habituelle de feuilles Présentent les résultat sous forme habituelle de feuilles de calculsde calculs– Les agrégats SUM, AVG.. de GROUP BY et les valeurs Les agrégats SUM, AVG.. de GROUP BY et les valeurs

individuelles en même tempsindividuelles en même temps– Impossible avec SQL standardImpossible avec SQL standard

Transforment les valeurs d'attributs en attributsTransforment les valeurs d'attributs en attributs– Par exemplePar exemple

» les valeurs de P# trouvés pour un même S# deviennent les les valeurs de P# trouvés pour un même S# deviennent les attributs P1, P2,...attributs P1, P2,...

» les valeurs de P1, P2.. sont les QTY (par ex.) les valeurs de P1, P2.. sont les QTY (par ex.) correspondants correspondants

Page 126: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

143

TRANSFORM Sum(SP.Qty) TRANSFORM Sum(SP.Qty) SELECT SP.[S#], Sum(SP.Qty) AS [Total SELECT SP.[S#], Sum(SP.Qty) AS [Total

Qty]Qty]FROM SPFROM SPGROUP BY SP.[S#]GROUP BY SP.[S#]PIVOT SP.[p#];PIVOT SP.[p#];

Tabulations Croisées Tabulations Croisées Tabulations Croisées Tabulations Croisées

Nouvellescolonnes

Page 127: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

144

S# Total Qty p1 p2 p3 p4 p5 p6

s1s1 13001300 300300 200200 400400 200200 100100 100100

s2s2 700700 300300 400400

s3s3 200200 200200

s4s4 900900 200200 300300 400400

L'intitulé Total Qty est mis par défaut par MsAccess

Tabulations Croisées Tabulations Croisées Tabulations Croisées Tabulations Croisées

Page 128: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

145

La fonction agrégat dans la clause La fonction agrégat dans la clause TRANSFORM est obligatoireTRANSFORM est obligatoire– bien que bien que SUM(QTY) = AVG(QTY) = QTYSUM(QTY) = AVG(QTY) = QTY– mais, COUNT(QTY) = 1mais, COUNT(QTY) = 1

On peut générer une expression de valeur On peut générer une expression de valeur TRANSFORM SUM(0.5*QTY) AS [Q2]TRANSFORM SUM(0.5*QTY) AS [Q2]SELECT Sum(SP.[Q2]) AS [Qte tot. dans 1 mois], Avg(P.SELECT Sum(SP.[Q2]) AS [Qte tot. dans 1 mois], Avg(P.[Q2]) AS [Qte moy. dans 1 mois][Q2]) AS [Qte moy. dans 1 mois]FROM SPFROM SPGROUP BY SP.[S#]GROUP BY SP.[S#]PIVOT SP.[p#];PIVOT SP.[p#];

Tabulations Croisées Tabulations Croisées Tabulations Croisées Tabulations Croisées

Page 129: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

146

On peut utiliser la clause WHEREOn peut utiliser la clause WHEREWHERE P# IN ('P1', 'P2')WHERE P# IN ('P1', 'P2')

Alors les fonctions ne calculent les agrégats que sur P1 et Alors les fonctions ne calculent les agrégats que sur P1 et P2.P2.

On peut aussi restreindre la tabulation seulement On peut aussi restreindre la tabulation seulement PIVOT SP.[p#] IN ('P1', P2')PIVOT SP.[p#] IN ('P1', P2')

Mais, cette clause n'affecte pas les calculs des agrégatsMais, cette clause n'affecte pas les calculs des agrégats Peut-on appliquer la clause ORDER BY ?Peut-on appliquer la clause ORDER BY ?

Si oui, quel serait l’effet sur les valeurs pivotées ?Si oui, quel serait l’effet sur les valeurs pivotées ? Peut-on ordonner par rapport à une fonction agrégat ?Peut-on ordonner par rapport à une fonction agrégat ?

Comme on a fait pour les requêtes à GROUP BY ?Comme on a fait pour les requêtes à GROUP BY ? Peut-on appliquer la clause HAVING ?Peut-on appliquer la clause HAVING ?

Tabulations Croisées Tabulations Croisées Tabulations Croisées Tabulations Croisées

Page 130: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

177

MiseMise à jour à jourMiseMise à jour à jour

update P update P set color = 'green', set color = 'green', weight = weight+10, weight = weight+10, city = null city = null where [p#] < 'p3';where [p#] < 'p3';

inconsistance/ à la sémantique de nuls pour les l'interrogations

Page 131: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

178

Mise à jourMise à jourMise à jourMise à jour

update P update P set color = 'green', set color = 'green', weight = weight+10, weight = weight+10, city = null city = null where [p#] < 'p3';where [p#] < 'p3';

update SP update SP set qty = '10' set qty = '10' where 20 = where 20 = (select status from S where s.[s#]=sp.[s#]) ;(select status from S where s.[s#]=sp.[s#]) ;

inconsistance/ à la sémantique de nuls pour les l'interrogations

une sous-requêteest nécessaire

Page 132: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

179

Mise à jourMise à jourMise à jourMise à jour

Transfert de fondsTransfert de fonds

update Account1update Account1set balance = balance - 100set balance = balance - 100where [c#] = '123';where [c#] = '123';

update Account2update Account2set balance = balance + 100set balance = balance + 100where [c#] = '123';where [c#] = '123';

- et si une de requêtes se casse ?- et si une de requêtes se casse ?

– il faut des transactionsil faut des transactions

Page 133: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

180

DELETEDELETEDELETEDELETE

Comme pour UPDATE:Comme pour UPDATE:

DELETE [*]DELETE [*]FROM tableFROM table[ WHERE condition ] ;[ WHERE condition ] ;

On retrouve aussi le besoin de transactionsOn retrouve aussi le besoin de transactions

Notamment pour l'intégrité référentielleNotamment pour l'intégrité référentielle

+ et + souvent gérée par SGBD à partir de la + et + souvent gérée par SGBD à partir de la déclaration dans LDD (ex. MsAccess)déclaration dans LDD (ex. MsAccess)

Page 134: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

181

INSERTINSERTINSERTINSERT

INSERT INTO P INSERT INTO P VALUES ('P8', 'nut', 'pink', 15, 'Nice') ;VALUES ('P8', 'nut', 'pink', 15, 'Nice') ;

pas bonne idée d'utiliser cette forme d'INSERTpas bonne idée d'utiliser cette forme d'INSERT

INSERT INTO P (weight, [P#] )INSERT INTO P (weight, [P#] )VALUES ( 100, 'P8') ;VALUES ( 100, 'P8') ;

les valeurs non-sélectionnées ne doivent pas être non-nullesles valeurs non-sélectionnées ne doivent pas être non-nulles

INSERT INTO TEMP ([P#], TQTY)INSERT INTO TEMP ([P#], TQTY)

SELECT (P#, SUM (QTY)SELECT (P#, SUM (QTY)FROM SPFROM SPGROUP BY [P#]GROUP BY [P#]

TEMP doit être préalablement créeTEMP doit être préalablement crée? Avec ou sans clé primaireAvec ou sans clé primaire

? Quelle différence pour INSERTQuelle différence pour INSERT

Page 135: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

182

INSERTINSERTINSERTINSERT

SELECT...INTO de MsAccess est équivalent à simple SELECT...INTO de MsAccess est équivalent à simple INSERT précédé de CREATE TABLEINSERT précédé de CREATE TABLE

SELECT field1[, field2[, ...]] INTO newtableSELECT field1[, field2[, ...]] INTO newtable[IN externaldatabase] [IN externaldatabase] FROM sourceFROM source

SELECT S.[S#], S.SName INTO [db2.mdb].s1SELECT S.[S#], S.SName INTO [db2.mdb].s1FROM SFROM SWHERE ((S.SName<>'paris'));WHERE ((S.SName<>'paris'));

+ en + souvent le SGBD peut vérifier pour INSERT + en + souvent le SGBD peut vérifier pour INSERT l'intégrité référentielle (MsAccess)l'intégrité référentielle (MsAccess)

Page 136: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

183

ConclusionConclusionConclusionConclusion

SQL est un langage assertionnelSQL est un langage assertionnel– relationnellement completrelationnellement complet– + expressions de valeur et agrégats+ expressions de valeur et agrégats

– + mises à jour+ mises à jourMais ce n'est pas un langage de programmation completMais ce n'est pas un langage de programmation completIl y a des défauts de conception et inconsistancesIl y a des défauts de conception et inconsistancesNéanmoins c'est un MUST pour un informaticien aujourd'huiNéanmoins c'est un MUST pour un informaticien aujourd'huiOn voit, néanmoins aussi que SQL n'est pas le langage pour On voit, néanmoins aussi que SQL n'est pas le langage pour les usagers ad-hoc !les usagers ad-hoc !C'est n'est pas tout pour SQL, il y en a encore !C'est n'est pas tout pour SQL, il y en a encore !

Page 137: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

184

FIN

Page 138: 1 SQL : Un Langage Relationnel Witold LITWIN 2 Langage de base de données (Database Language) n Un sous-langage de programmation n Consiste traditionnellement

185