bado cours partie2 sql v7
Post on 10-Feb-2018
243 Views
Preview:
TRANSCRIPT
-
7/22/2019 Bado Cours Partie2 SQL V7
1/86
1
Introduction aux Bases de donnes
Le langage SQL(Structured Query Language)
-
7/22/2019 Bado Cours Partie2 SQL V7
2/86
27/08/2013
Tous les SGBD relationnels utilisent le langage SQL pour accder la base
de donnes et la manipuler :
- crer, supprimer, modifier des tables
- insrer, supprimer, mettre our les donnes des tables
!m" : c#a"ue SGBD utilise des e$tensions du langage "ui lui sont propres%
Quel"ues a&antages :
- !e"u'tes simples dclarati&es (Q)*+, non *./T0
- primiti&es crites en SQL 12 normalises, donc portables d3un SGBD un
autre (si seule la norme est utilise0
-
7/22/2019 Bado Cours Partie2 SQL V7
3/86
Le SGBD PostgresSQLDocumentation, par exemple :
ttp:!!docs"postgres#l$r"org!%"&!
PostgresSQL est un SGBD Relationnel (les dernires versionspossdent des fonctionnalits SGBD-Relationnel-Objet)
PostgreSQL a une architecture Client/Serveur bas sur le modle : unprocessus par client
PostgreSQL possde un dictionnaire de donnes o sont dcrites :
- les tables, les colonnes, les index, les contraintes, - les types de donnes, les fonctions, les oprateurs,
Les tables du dictionnaire commencent par pg_.Pour obtenir la liste complte des tables, voir le site :
http://docs.postgresqlfr.org/8.0/catalogs.html
-
7/22/2019 Bado Cours Partie2 SQL V7
4/86
S4nta$e des commandes SQL
Les commandes SQL commencent par un mot cl ("ui sert nommer l5opration e$cuter% #a"ue commande SQL doitremplir deu$ e$igences :
+ndi"uer les donnes sur les"uelles elle op6re ("ui est un ensemble
de lignes stoc7es d3une ou plusieurs tables0+ndi"uer l5opration e$cuter sur ces donnes
Les commandes du langage SQL op6rent sur une BD relationnelle(un ensemble de tables0
SQL permet d3e$cuter une re"u'te et d3obtenir une rponse 12interactif% ais, il e$iste SQL intgr (dans un langage, SQLd4nami"ue, et utilisable sous forme de bibliot#6"ues de fonctions(*DB, 8DB0 : non normalis%
-
7/22/2019 Bado Cours Partie2 SQL V7
5/86
5
SQL (Structured Quer4 Langage0 : standards (SQL9, SQL;, SQL0
(ou SQL DDL(Data Description Langage0 ou LDD :? description des obets de la base (tables, 0
!.@T., @LT.!, D!*A
> D'L(anipulation0 ou LD :
? +nterrogation : S.L.T
? anipulation : +/S.!T, )AD@T., D.L.T.
> DL(ontrol0 ou LD : contrle d 5acc6s au$ donnes : G!@/T C !.*E.
> F ; instructions : *+T (&alider0 et !*LLB@E (dfaire0
-
7/22/2019 Bado Cours Partie2 SQL V7
6/86
*n utilise comme e$emple une BD! contenant ; tables .A et
D.AT et une table D)@L d3un seul attribut% .lle est &ide%
'P (empno, ename, *o+, mgr, iredate, sal, comm, deptno)? empno, mgr, deptno : entier ename, *o+ : ca-ne de caract.re / iredate : date
? sal, comm : real
DP0 (deptno, dname, loc)? deptno : entier
? dname, loc : ca-ne de caract.res
D12L ta+le 3ide 4 une colonne
-
7/22/2019 Bado Cours Partie2 SQL V7
7/86
7
0raitement d 5une re#u6te
? @nal4se s4nta$i"ue : &rification de la disposition des mots dans la
re"u'te (mots-cl et autres0
? *ptimisation : le SGBD gn6re des re"u'tes optimises partir de la
re"u'te de base% +l se sert pour cela de la connaissance de la structure
des donnes, des inde$ (s 5ils e$istent0 et l 5organisation p#4si"ue
des donnes, et de statisti"ues d 5acc6s au$ donnes%
-
7/22/2019 Bado Cours Partie2 SQL V7
8/86
8
0raitement d 5une re#u6te (suite)
? .$cution : le SGBD gn6re des plans d 5e$cutions% +l en c#oit un et
e$cute la re"u'te selon ce plan%
? 7ote: le SGBD tient compte du fait "ue d 5autres utilisateurs
peu&ent 'tre en train d 5accder au$ m'mes donnes au m'me
moment 12 mcanisme de contrleur de concurrence%
-
7/22/2019 Bado Cours Partie2 SQL V7
9/86
Les t4pes de donnes
Les t4pes numri"ues : integer, smallint, double, float,%%%
e$emple : ;=, -
-
7/22/2019 Bado Cours Partie2 SQL V7
10/86
Les types de donnes : Oracle et PostgreSQL
Type numrique :
INTEGER dans Oracle et PostgreSQL.
Oracle: NUMBER(p,s) PostgreSQL: NUMERIC(p,S)
Type caractre :
CHAR(n), pour les chanes de taille fixe, dans Oracle et PostgreSQL.
Oracle: VARCHAR2(n)PostgreSQL et Oracle : VARCHAR(N) : chane de caractres de longueur variable
-
7/22/2019 Bado Cours Partie2 SQL V7
11/86
Type date :Le type DATE Oracle contient une date prcise la seconde prs.Le type DATE postgreSQL contient une date prcise au jour prs.
Type date Oracle ~ Type TIMESTAMP de postgreSQL.Oprations autoriss sur les dates : soustraire ou d'ajouter des jours une date
Ex: PostgreSQL :
SELECT date '29-nov-2008' + 1;
->2008-11-30
Pour soustraire ou ajouter des intervalles de temps un attribut de type TIMESTAMP, ilfaut utiliser une valeur de type INTERVAL.ex:SELECT timestamp '29-nov-2008' + interval '1 day';
-> 2008-11-30 00:00:00
Current_date (PostegreSQL) sysdate (Oracle)
-
7/22/2019 Bado Cours Partie2 SQL V7
12/86
12
SQLD'L
Interrogation : select
-
7/22/2019 Bado Cours Partie2 SQL V7
13/86
27/08/2013
SL0
- .st une commande "ui poss6de beaucoup d 5options (unecentaine0
- Aermet d 5e$traire des informations partir d 5une ou plusieurs
tables "ui respectent &entuellement certaines conditions
.$emple S.L.T empno K!* emp #ere deptno 1
-
7/22/2019 Bado Cours Partie2 SQL V7
14/86
27/08/2013
S4nta$e gnrale
S.L.T nomNc O, nomNc O, nomNcP P >K!* tab< O, tab; O, tabiP P
R.!. conditions
*!D.! B crit6res de tri
G!*)A B colonne de regroupement
-
7/22/2019 Bado Cours Partie2 SQL V7
15/86
15
Select * from nom_table ;
nom_commande toutes les colonnes mot-cl nom de la table
8: from empM
Extraction de toutes les lignes d une table
xtraction d 5une ou plusieurs colonnes (opration de pro*ection)
Select nomNcol O, nomNcol %%%P from nomNtable M
8:
-
7/22/2019 Bado Cours Partie2 SQL V7
16/86
16
9enommer les colonnes ou les ta+les
EX: 1. Lister les noms des dpartements :
Select dname AS NOM D!A"#MN#$ %rom dept&
'( le nom est compos )* mettre entre +,illemets ('
2. Lister les noms et salaires des emplos :
select ename AS NOM/ sal AS SALA0" %rom emp&
-
7/22/2019 Bado Cours Partie2 SQL V7
17/86
17
Extraction de toutes les lignes d une tableliminer les doublons ventuels : DISTINCT
Select ODistinct2LL; OnomNcol O, nomNcolP %%%P > from nomNtable M
!ar d%a,t : ALL
EX: Lister les di%%rents mtiers o3s4 sans do,3lons :
? Select distinct 2o3 %rom emp&
-
7/22/2019 Bado Cours Partie2 SQL V7
18/86
18
ondition de recerce
Select % Krom R#ere Ucondition2
oV Ucondition2 est une suite de prdicats -
)n prdicat 1 comparaison de ; &aleurs
.W:
-
7/22/2019 Bado Cours Partie2 SQL V7
19/86
1
ondition de recerce
Les littrau$ alp#anumri"ues : entre apostrop#es (Y 0 et sont compars
caract6re par caract6re (codage interne0 :
Select Krom % R#ere O/*TP prdicat-< @/D*! O/*TPprdicat-; M
x :
-
7/22/2019 Bado Cours Partie2 SQL V7
20/86
2
-
7/22/2019 Bado Cours Partie2 SQL V7
21/86
21
-
7/22/2019 Bado Cours Partie2 SQL V7
22/86
22
from emp #ere nomNcol O/*TP B.TR../ &al< and &al;M
.W% Les emplo4s dont le salaire est compris entre
-
7/22/2019 Bado Cours Partie2 SQL V7
23/86
2
, [ : combins pour faire des e$pressions%
.W : @ffic#er les emplo4s a&ec leur salaire total (a&ec la commission "uand ellee$iste0%
Select ename# sal# comm# salAcomm $%ere comm is not null"
0ri des rsultats
Les rsultats d 5une slection peu&ent 'tre tris, sur une ou plusieurs colonnes, par
ordre croissant (par dfaut0 ou dcroissant%
S4nta$e :
Select > from nomNtab #ere %% *!D.! B nomNcol O@SD.SP M
-
7/22/2019 Bado Cours Partie2 SQL V7
24/86
29
-
7/22/2019 Bado Cours Partie2 SQL V7
25/86
25
La =ointure
@ssociation de lignes de plusieurs tables en fonction d 5un crit6re (de
ointure0%
Select > ?rom nom@ta+ Anom@alias; ere Ccrit.re de *ointureE
.W% @ffic#er les noms des emplo4s (table emp0 a&ec les noms de leur
dpartement (table dept0 :
Select ename# dname !rom emp# dept $%ere emp.deptno & dept.deptnoM
!m" :
-
7/22/2019 Bado Cours Partie2 SQL V7
26/86
26
La =ointure
Lister les emplo4s a&ec leur manager : le crit6re d 5auto-ointure est
ralis en associant .A/* et G! ("ui est aussi un numro
d 5emplo4, c--d du c#ef0:
Aour effectuer l 5auto-ointure, il con&ient de citer ; fois la m'me table
en utilisant un alias, car on traite ; tables identi"ues, les m'mes nomsde colonnes apparaissent dans les deu$ tables%
Select emp.empno, emp.ename, empbis.empno, empbis.ename
From emp, emp empbis
Where emp.mgr = empbis.empno;
-
7/22/2019 Bado Cours Partie2 SQL V7
27/86
Jointure externe
Pour effectuer une jointure externe entre table, il faut utiliser la syntaxe conforme austandard SQL (disponible aussi Oracle), LEFT OUTER JOIN et RIGHT OUTER JOIN:
Ex :
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.id=table2.id;
Tous les n-uplets de table1 apparatront dans l'ensemble des n-uplets rsultats. Les n-upletsde table1 pour lesquelles il n'est pas possible de faire une jointure avec des n-uplets de la
table2 auront NULL comme valeur pour les attributs de table2.
-
7/22/2019 Bado Cours Partie2 SQL V7
28/86
SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.id=table2.id;
Tous les n-uplets de table2 apparatront dans l'ensemble des n-uplets rsultats. Les n-upletsde table2 pour lesquelles il n'est pas possible de faire une jointure avec des n-uplets de latable1 auront NULL comme valeur pour les attributs de table1.
-
7/22/2019 Bado Cours Partie2 SQL V7
29/86
2
Sousinterrogation
Select Krom R#ere nomNcol Uoprateur2
(Select Krom R#ere0M
.W: @ffic#er les emplo4s "ui sont dans le m'me dpartement "ue 3@LL./3 :
d3abord rec#erc#e du dpartement de @LL./, puis connaissant son numro
(=J0, on c#erc#e les emplo4s "ui 4 tra&aillent%
Select Drom emp %ere
deptno & 9Select deptno !rom emp $%ere ename & )+,,E/>"
-
7/22/2019 Bado Cours Partie2 SQL V7
30/86
Sousinterrogation
Sousinterrogation rapportant plusieurs lignes : I7, 27, 2LL
Le rsultat d 5une sous-interrogation peut comporter plusieurs lignes% Dans ce casles oprateurs 1, U, 2 ne con&iennent plus% L 5galit sera traite par
l 5oprateur +/ (un Y1Y par rapport une suite de &aleurs0,
et Les ingalits seront traites par l 5oprateur @/ et @LL%
.$emple : Les emplo4s tra&aillant dans le m'me dpartement "ue l 5un desemplo4s dpendant du prsident%
Select !rom emp
%ere deptno in 9select deptno !rom emp
%ere m7r & 9Select empno !rom emp
%ere ob&) P4ES8*E/ > >"
-
7/22/2019 Bado Cours Partie2 SQL V7
31/86
1
Oprateur ANY : Le rsultat de la comparaison est VRAI, s il l est pour au
moins un lment de l ensemble (de la sous-interrogation)
EX: Afficher les employs ayant un salaire suprieur celui de l un des
employs travaillant dans le dpartement 10
Select * from emp where sal > ANY (select sal from emp
where deptno=10);
-
7/22/2019 Bado Cours Partie2 SQL V7
32/86
2
Oprateur ALL : Le rsultat de la comparaison est VRAI, s il l est pour tous
les lments de l ensemble (sous-interrogation)
EX: Afficher les employs ayant un salaire suprieur celui des employs
travaillant dans le dpartement 20.
Select * From emp where sal > ALL (select sal from emp
where deptno=20);
-
7/22/2019 Bado Cours Partie2 SQL V7
33/86
xtraction de lignes si le rsultat de la sousinterrogation
comporte au moins une ligne A7
-
7/22/2019 Bado Cours Partie2 SQL V7
34/86
9
Les traitements de groupe
Select O@GS)+/@W*)/TP ( OD+ST+/T@LLP nomNcol 0 R#ere conditionM
12 Le rsultat sera affic# sur une seule ligne%
.$:
!rom emp"
;% @ffic#er le total et la mo4enne des commissions :
Select sum9comm># av79comm> !rom emp"
-
7/22/2019 Bado Cours Partie2 SQL V7
35/86
5
Les traitements de groupe
=% @ffic#er le nombre de commissions /)LL : Select count9comm> !rom emp"
9m#H: la &aleur /)LL n 5entre pas dans les diffrents calculs%
.$ : la mo4enne de la colonne * est calcule en cumulant le nombre
de commissions di&ise par le nombre de &aleurs non /)LL%
9m#F: +l est impossible d 5utiliser la fois une proection et une fonction de
groupe : Select ename# sum9sal> !rom emp" S92 9=0"
9m#: *)/T (>0 : nombre de lignes non /)LL%
-
7/22/2019 Bado Cours Partie2 SQL V7
36/86
6
Regroupement du rsultat d une slection GROUP BY
Les lignes provenant d une slection peuvent tre regroupes en fonction
d une valeur commune dans une ou plusieurs colonnes :
Select [AVG| |COUNT] ([distint|all] from nom_table
where
GROUP BY [nom_col1 | nom_col2| .];
Rmq: Il n est pas ncessaire de raliser un tri avant le regroupement. Il est
effectu automatiquement par GROUP BY.
Ex: Calculer le total des salaires par dpartement :
Select deptno, sum(sal) from emp group by deptno;
-
7/22/2019 Bado Cours Partie2 SQL V7
37/86
7
Sousinterrogation de groupe
Select . From nom_tab [alias] Where
nom_col
-
7/22/2019 Bado Cours Partie2 SQL V7
38/86
-
7/22/2019 Bado Cours Partie2 SQL V7
39/86
ration d 5une ta+le 4 partir d 5une autre ta+le (
-
7/22/2019 Bado Cours Partie2 SQL V7
40/86
9
Slection de groupes de lignes en $onction d 5un crit.re
Select O+/ % *)/TP OdistinctallP O(nomNcol0>P Krom nomNtab
R#ere Ucondition2 G!*)A B Onom-c !rom emp
G43UP C deptno
H+I8/G sum9sal> ' 9Select M+F9SUM9sal>> !rom emp
G43UP C deptno>" --oracle
-
7/22/2019 Bado Cours Partie2 SQL V7
41/86
27/08/2013
-
7/22/2019 Bado Cours Partie2 SQL V7
42/86
92
-
7/22/2019 Bado Cours Partie2 SQL V7
43/86
9
Oprateurs sur plusieurs tablesOprateurs algbriques : union, inter ou intersect et minus
Exemple : 2 tables de mme structure et de contenus dffrents : depot1 et deptot2:
Structure : numprod, design, conditionnem, stock, rayon, pu, tva
Diffrence MINUS (oracle) ou EXCEPT (postgres) : lignes existant dans une table mais pasdans une autre
Ex : Lister les produits en stocks dans le dpt1 uniquement :
Select numpro, design, pu, from depot1
MINUS Select numpro, design, pu, from depot2;
DIERENCE
S
-
7/22/2019 Bado Cours Partie2 SQL V7
44/86
99
Lignes communes plusieurs tables
INTERSECT : les doublons sont limins.
Select . From . Where .
INTERSECT
Select . From . Where .;
Ex: Afficher les produits identiques qui sont en stock dans les 2 dpts :
Select numpro, design, pu From depot1
INTERSECT
Select numpro, design, pu from depot2;
Rmq: Les noms de colonnes du rsultats sont les noms de colonnes du 1er SELECT.
INTERSECTION
-
7/22/2019 Bado Cours Partie2 SQL V7
45/86
95
SQLD'L
mise 4 *our : I7S90, 1PD20, DL0
-
7/22/2019 Bado Cours Partie2 SQL V7
46/86
96
*n tra&aille sur les tables .A et D.AT%
es commandes permettent d 5insrer des &aleurs dans des tables, de
supprimer des &aleurs ou de mettre our des &aleurs%
J insertion d 5une ligne dans une ta+le :
+/S.!T +/T* nomNtab ( OnomNcol< O, nomNcol;P %%P 0
@L).S ( &al< O,&al;P %0 M
Insertion : I7S90
-
7/22/2019 Bado Cours Partie2 SQL V7
47/86
97
Ex1: Insrer un nouveau dpartement : 50, EDUCATION, MIAMI
:
Insert INTO dept VALUES (50, EDUCATION , MIAMI);
Rmq: On n a pas citer les colonnes quand on insre des valeurs
pour toutes les colonnes !
Ex2:Insrer un employ en ne connaissant que le numro, le nom, le
job et le numro de dpartement :
Insert into EMP (empno, ename, job, deptno)
VALUES (7950, JOHN , TRAINER , 50);
/* on doit citer les colonnes */
-
7/22/2019 Bado Cours Partie2 SQL V7
48/86
98
'ise 4 *our : 1PD20
)pdate nomNtab S.T nomNcol1 e$p Onom-col1e$p%%%PR#ere conditionM
.$: !emplacer dans le dpartement =J, la localit
Y+@G*5 par YL*S @/G.L.S5 :
)AD@T. dept S.T loc 1 YL*S @/G.L.S5 R#ere deptno1=JM
@ugmenter la commission de
-
7/22/2019 Bado Cours Partie2 SQL V7
49/86
9
Suppression : DL0
D.L.T. K!* nomNtab OR#ere conditionPM
.W
-
7/22/2019 Bado Cours Partie2 SQL V7
50/86
5
9emar#ues
- L 5utilisateur "ui lance la commande doit 'tre propritaire
de la table ou a&oir re]u le droit )AD@T. sur la table
- Le &erbe S.T indi"ue les colonnes modifier% Seules les
colonnes spcifies seront modifies%
- Si la condition R.!. est absente, toutes les lignes
spcifies dans S.T seront modifies%
- L 5effacement d 5une colonne est ralis en utilisant
l 5option /)LL : S.T nomNcol 1 /)LLM
-
7/22/2019 Bado Cours Partie2 SQL V7
51/86
51
!alidation " #nnulation destransactions
-
7/22/2019 Bado Cours Partie2 SQL V7
52/86
52
ali+ation +es mo+i#ications e##ect)es &
C$%%IT [$or release]4 /5 work releasepar compatibilit) a,ec les
,ersions ant)rieres por 6racle 5/
'nnlation +es e##ets + ne transaction &
R$&&'#C( [$or release]4 /5 work releasepar compatibilit) a,ec les
,ersions ant)rieres por 6racle 5/
Rmq& n *)n)ral a,ec S9 interacti# cha:e or+re est ne transaction"'tocommit initialiser 6;%. onc les mo+i#ications ),entelles se
r)perctent atomati:ement "=ommit implicite por l tilisater%
=6>>?@ et A699B'=C sont trDs tiles :an+ on tilise n lan*a*e
proc)+ral et/o ne inter#ace a,ec n lan*a*e +e pro*rammation.
-
7/22/2019 Bado Cours Partie2 SQL V7
53/86
5
Ex)ressions et onctions
-
7/22/2019 Bado Cours Partie2 SQL V7
54/86
59
)ne e$pression : combinaison de &ariables (contenu d 5une
colonne0, de constantes et d 5autres e$pressions l 5aide des
oprateurs : F, -, >, [
)ne fonction : routine a4ant des arguments et ramenant un
rsultat (un argument peut 'tre une e$pression0
+l 4 a = t4pes d 5e$pressions : arit#mti"ues, c#aXnes de
caract6res et date%
@ c#a"ue t4pe 112 il 4 a des oprateurs spcifi"ues%
-
7/22/2019 Bado Cours Partie2 SQL V7
55/86
55
%
.$emple de fonctions : /L : /)LL @L). (*racle0
permet de remplacer une &aleur /)LL par une &aleur significati&e% S4nta$e : /L( e$p
-
7/22/2019 Bado Cours Partie2 SQL V7
56/86
56
PostrgreSQL
*@L.S.(arg
-
7/22/2019 Bado Cours Partie2 SQL V7
57/86
299
-
7/22/2019 Bado Cours Partie2 SQL V7
58/86
p
@S. R./ cond< T./ arg as date !rom emp"
*/T , D@ : mois (our0 en lettres
Aar dfaut : la date : --DD
27/08/2013
2 t $ ti D20
-
7/22/2019 Bado Cours Partie2 SQL V7
61/86
2utres $onctions D20
)!!./TND@T. : date du our
27/08/2013
Insertion de lignes dans une ta+le 4 partir d 5une autre ta+le : I7S90
-
7/22/2019 Bado Cours Partie2 SQL V7
62/86
62
Insertion de lignes dans une ta+le 4 partir d une autre ta+le : I7S90
(, partir
de la table emp, les emplo4s "ui sont S@L.S@/ et dont la
commission est suprieure ;Z\ du salaire :
8nsert 8nto C3/US
Select ename# ob# sal# comm Drom emp
%ere ob&)S+,ESM+/ and 53MM ' 0.
-
7/22/2019 Bado Cours Partie2 SQL V7
63/86
6
SQLLDD (Description)
920 / 2L09 D9
-
7/22/2019 Bado Cours Partie2 SQL V7
64/86
Les contraintes d5intgrit sont des r6gles "ui doi&ent 'tre &rifies enpermanence par le SGBD, "uel"ue soit l3opration effectue sur la
base%
Aarmi ces contraintes d3intgrit :la dfinition des cls primaires et des cls trang6res%
l3indication des &aleurs possibles pour les attributs%
!emar"ue : on les appli"uera dans la commande !.@T.%
g
ontraintes dKintgrit
-
7/22/2019 Bado Cours Partie2 SQL V7
65/86
27/08/2013
Principales contraintes
P9I'29 :dfinit une cl primaire (la &aleur est diffrente de /)LL etuni"ue dans la table0 12 toutes les lignes sont diffrentes
?
-
7/22/2019 Bado Cours Partie2 SQL V7
66/86
P9I'29 , 7
-
7/22/2019 Bado Cours Partie2 SQL V7
67/86
?
-
7/22/2019 Bado Cours Partie2 SQL V7
68/86
D3autres cas :
DELETE CASCADE : suppression du tuple rfrenc et de tous ceu$"u3il rfrence en cascade%
DELETE SET NLL : suppression du tuple rfrenc etmodification des tuples "ui rfrencent en assignant la &aleur /)LL
leur cl trang6re : D.L.T. S.T /)LL%
'odi$ication de la cl primaire d5un tuple (cette cl tant
r$rence par d5autres tuples )
-
7/22/2019 Bado Cours Partie2 SQL V7
69/86
Alusieurs cas :
!n inter"it la mo"i#ication "$ t$ple : sit$ation par "%#a$t &+l fautsupprimer d3abord toutes les rfrences ce tuple pour pou&oir ensuitele modifier0%% 'DATE CASCADE : m du tuple et des tuples "ui le rfrencent
assignant la nou&elle &aleur de la cl primairedu tuple m au$ cls trang6res correspondantes dans les tuples "uirfrencent%
'DATE SET NLL : modification du tuple rfrenc% Les tuples"ui le rfrencent se &oient assigns la &aleur /)LL (pour leurs clstrang6res0%
920I
-
7/22/2019 Bado Cours Partie2 SQL V7
70/86
7
920I
-
7/22/2019 Bado Cours Partie2 SQL V7
71/86
xemple
!.@T. T@BL. li&re (
codeNl +/T.G.! A!+@! E.,titreNl @!@!(=J0, prenom @!@!(=J0
0 M
@&ec le nom de la contrainte : 12
!.@T. T@BL. auteur (numNa +/T.G.!,nom @!@!(=J0,
-
7/22/2019 Bado Cours Partie2 SQL V7
72/86
ontraintes :
7
-
7/22/2019 Bado Cours Partie2 SQL V7
73/86
.$emple : reate table fournisseurs
( nomNfou c#ar(;Z0 /*T /)LL )/+Q). 0M
M: spcifie une contrainte "ui doit 'tre &rifie tout moment
par les tuples concerns%
.$emple : (reate table %
(cliNt4pe c#ar(
-
7/22/2019 Bado Cours Partie2 SQL V7
74/86
La "uantit li&re doit 'tre infrieure ou gale la "uantit commande%
"teNli& integer default J .E ("teNli& U1 "t]Ncom%0
@&ec /*T /)LL : .E (nomNfou +S /*T /)LL0 %%
+nter&alle : % .E &alNpri$ B.TR../
-
7/22/2019 Bado Cours Partie2 SQL V7
75/86
I r$rentielle: 12 gestion correcte des modifications de la cl
trang6re dans la table "ui rfrence et de la cl primaire dans la table
rfrence%
Soient les relations :
client(numcl, %0 et commande(numcom, numcl,%%0
si on aoute une commande 12 il faut "ue le client associ e$iste%
SQL : 920 02BL / ontraintes (suite)
-
7/22/2019 Bado Cours Partie2 SQL V7
76/86
solutionspour grer les ontraintes d3intgrit rfrentielles :
-
7/22/2019 Bado Cours Partie2 SQL V7
77/86
Dans les SGBD, mise en u&re de la solution = (intgrit rfrentielle
dclarati&e0%
.$emple :
reate table client (numNcl c#ar(Z0 not null primar4 7e4, 0M
reate table commande(numcomm integer not null primar4 7e4, %
/umNcom c#ar(Z0 not null !.K.!./.S client, %0M
Si la cl trang6re 1 plusieurs attributs 12 utiliser K*!.+G/ E.%
K*!.+G/ E. (atr
-
7/22/2019 Bado Cours Partie2 SQL V7
78/86
Si on modifie la &aleur d 5une cl primaire rfrence, SQL permet de
raliser automati"uement certaines oprations :
7< 20I
-
7/22/2019 Bado Cours Partie2 SQL V7
79/86
S0 71LL: si une cl primaire rfrence est modifie 12 on les met /)LL
l oV elles sont rfrences (condition : les parties de la cl trang6res doi&entpermettre les &aleurs /)LL0%
S0 D?21L0: si la cl primaire est modifie 12 la &aleur de la cl trang6re
"ui rfrence est mise une &aleur par dfaut (dfinie au pralable0
.$emple :
>" deptno 9?97S dept
-
7/22/2019 Bado Cours Partie2 SQL V7
80/86
8
'odi$ication de la structure d 5une ta+le : 2L09
.W : @LT.! T@BL. emp @DD (diplme c#ar(;J00M
@LT.! T@BL. .A (sal numer (9,;00M
Suppression d 5une ta+le : D9
-
7/22/2019 Bado Cours Partie2 SQL V7
81/86
81
SQL LD
ontrRle des acc.s 4 la +ase et aux o+*ets
920I
-
7/22/2019 Bado Cours Partie2 SQL V7
82/86
82
G!@/T O*//.T!.S*)!.DB@P T* nom-user
+D./T+K+.D B mot-passeM
.W: Le DB@ cr l 5utilisateur )S.!< a&ec les pri&il6ges de conne$ion et de
cration d 5obets :
Grant connect, resource to )S.!< identified b4 lambdaM
9eprise de pri3il.ges
!.*E. O*//.T!.S*)!.DB@P K!* nom-user M
.W : !e&o7e resource from user
-
7/22/2019 Bado Cours Partie2 SQL V7
83/86
8
Les o3ets doi,tilisate,r o, ?, >il a re@, lesdroits ncessaires :
"AN# BSLC#0NS"#DL#;!DA#0NDEALLF
o, 3ien
"AN# ;!DA# nomGcol/ nomGcol/ H4
I ON nomGta31/ nomGta32/ H #O B;1/ ;2/ ...!;JL0CF
I K0# "AN# O!#0ON&
*V :
-
7/22/2019 Bado Cours Partie2 SQL V7
84/86
89
*V :
+/D.W : donne la possibilit de crer des inde$% .$emple :
!.@T. Ouni"ueP +/D.W ind */ nom-tab ( nom-col< OascdescP, nom-col;
OascdescP, 0M
[> pour amliorer les performances lors de la manipulation >[
- @LL : tous les droits
- L 5option : _R+T .E *AT+*/` signifie "ue celui a "ui l 5on accorde lesdroits spcifis peut lui-m'me les transmettre d 5autres%
- .W : Grant select, update on emp to user< it# c#ec7 optionM
2nnulation des droits!.*E. OS.L.T %%P */ nom-tab, nom-tab;, %%K!* O)
-
7/22/2019 Bado Cours Partie2 SQL V7
85/86
85
ration d un synonyme pour une ta+le
!.@T. OA)BL+P S/*/ nom-s4n K*! nom-tableM
.W: )n utilisateur se cre un s4non4me pour s4s%emp :
reate s4non4m l-emp Kor SS%empM
L 5utilisateur SST. (DB@0 cre un s4non4me accessible
tout le monde pour sa table emp :
.W: reate public s4non4m p-emp for SS%empM
-
7/22/2019 Bado Cours Partie2 SQL V7
86/86
top related