Universit Virtuelle de Tunis - Institut Sup rieur dInformatique
Module 2 Section 2 :
Le langage de d finition
des donn es (LDD)
de SQL
Riadh ZAAFRANI
Octobre 2020
1 re ann e MP2L
Page 1
09/10/2020
Riadh Zaafrani
Plan du cours
q Cr ation des tables avec SQL
q Modification de la structure
dune table avec SQL
q Les contraintes dint grit sous
SQL
Page 2
09/10/2020
Riadh Zaafrani
1
Les tables
q Les tables repr sentent
le m canisme de stockage des
donn es dans une base Oracle.
q Une table contient un ensemble fixe de colonnes.
q Chaque
colonne
caract ristiques sp cifiques.
poss de
un
nom ainsi
que
des
q Les op rations de cr ation, de suppression et de modification
des tables mettent jour le dictionnaire de donn es du SGBD.
On rappelle que le dictionnaire de donn es est une structure
propre au SGBD qui contient la description des objets du
SGBD (base de donn es, tables, colonnes, droits, etc.).
Page 3
09/10/2020
Riadh Zaafrani
Cr ation des tables
q La cr ation dune table est une op ration importante quil
faut
entreprendre avec soin. Cest lors de cette tape que lon d finit le type
de donn es, la cl , les index ventuels et quil convient dimposer des
contraintes de validation garantissant la bonne qualit des informations
entr es dans la table.
q La forme g n rale de linstruction de cr ation de table est la suivante :
q CREATE TABLE <Nom de la table> (
liste des colonnes avec leur type s par es par ,) ;
q CREATE TABLE voiture (NumVoit INT,
Marque CHAR(40),
Type CHAR(30),
Couleur CHAR(20)) ;
Page 4
09/10/2020
Riadh Zaafrani
2
Cr ation des tables
q Le nom de la table ou dune colonne ne doit pas d passer 128
caract res. Il commence par une lettre, contient des chiffres, des lettres
et le caract re _ .
q Attention de m me ne pas utiliser un mot cl SQL.
q Les tables peuvent tre cr es de mani re temporaire : elles seront
donc effac es la fin de la session de lutilisateur laide du mot cl
TEMPORARY.
q CREATE TEMPORARY TABLE temporaire (
Identifiant INT,
Jour DATE,
Valide BOOLEAN
) ;
Page 5
09/10/2020
Riadh Zaafrani
Cr ation des tables
q Les tables peuvent tre issues directement du r sultat
dune requ te en utilisant le mot cl AS :
q cest particuli rement commode pour pouvoir disposer de
lors dune
r sultats interm diaires en fin de v rification,
s rie de manipulations sur une table.
q CREATE TEMPORARY TABLE resultat
AS
(SELECT Vo.Marque, Vo.Couleur
FROM voiture AS Vo);
Page 6
09/10/2020
Riadh Zaafrani
3
Type de donn es
q Le type de donn es est choisi essentiellement en
la
fonction des op rations qui sont effectu es sur
colonne.
q Le choix du type permet galement de mettre en place
un premier niveau de restriction sur le contenu des
donn es : une colonne de type num rique ne pourra pas
contenir de caract res. Des restrictions plus fines seront
d finies la section Contraintes dint grit .
q Voici une liste (non exhaustive) des types de donn es
SQL.
Page 7
09/10/2020
Riadh Zaafrani
Type de donn es
INT
SMALLINT
REAL
FLOAT(n)
NUMBER
(n, )
CHAR(n)
Entier standard (32 bits)
Entier petit (16 bits)
R el (taille sp cifique au SGBD)
R el (repr sent sur n bits)
Champ de longueur variable de n chiffres dont d apr s
Publicité
la virgule, acceptant des nombres n gatifs.
Cha ne de caract res de longueur n (codage
ASCII 1 octet)
VARCHAR(n) Cha ne de caract res de longueur maximale n
NCHAR(b)
NVARCHAR(
b)
(codage ASCII 1 octet)
Cha ne de caract res de longueur n (codage
Unicode sur 2 octets)
Cha ne de caract res de longueur maximale n
(codage Unicode sur 2 octets)
Page 8
09/10/2020
Riadh Zaafrani
4
Type de donn es
DATE
TIME[(n)]
Date
Heure, n (optionnel) est le nombre de decimals
repr sentant la fraction de secondes
BOOLEAN
BLOB
Bool en
Binary Large Object : permet de stocker tout
type binaire (photo, fichier traitement de
texte&)
Page 9
09/10/2020
Riadh Zaafrani
Suppression
q La commande DROP TABLE permet de supprimer une table.
q DROP TABLE voiture
q Si
la table est r f renc e dans une autre table (par exemple,
contrainte dint grit r f rentielle), le SGBD refuse en g n ral de la
supprimer : il utilise loption RESTRICT par d faut. Si lon d sire tout
de m me la supprimer ainsi que tous les objets qui lui sont li s, il
faut alors utiliser loption CASCADE. Dans lexemple, si
la table
vente utilise la table voiture comme table de r f rence pour le
contenu de la colonne NumVoit, on ne peut supprimer la table
voiture avant davoir supprim la table vente.
q DROP TABLE voiture CASCADE
Page 10
09/10/2020
Riadh Zaafrani
5
Plan du cours
q Cr ation des tables avec SQL
q Modification de la structure
dune table avec SQL
q Les contraintes dint grit sous
SQL
Page 11
09/10/2020
Riadh Zaafrani
Modification
q La commande ALTER TABLE permet de modifier la structure de la
table, cest- -dire dajouter, de supprimer ou modifier des colonnes.
q ALTER TABLE voiture
ADD COLUMN enplus INT ;
q SELECT * FROM voiture ;
NumVoit Marque
Peugeot
1
Citroen
2
Opel
3
Peugeot
4
Renault
5
Renault
6
Type
404
SM
GT
403
Alpine A310 Rose
Bleue
Floride
enplus
Couleur
NULL
Rouge
Noire
NULL
Blanche NULL
Blanche NULL
NULL
NULL
Page 12
09/10/2020
Riadh Zaafrani
6
Modification
q ALTER TABLE voiture
DROP COLUMN Couleur;
q SELECT * FROM voiture ;
enplus
Type
NumVoit Marque
NULL
404
Peugeot
NULL
SM
Citroen
NULL
GT
Opel
Peugeot
NULL
403
Renault Alpine A310 NULL
Publicité
NULL
Floride
Renault
1
2
3
4
5
6
Page 13
09/10/2020
Riadh Zaafrani
Modification
q La commande ALTER permet de modifier galement les
contraintes associ es aux colonnes. Cette partie est trait e
ult rieurement. Le mot cl COLUMN est optionnel.
q Il nest pas possible de modifier directement le nom dune
colonne ou son type.
faut pour cela crire une s rie
dop rations, en utilisant par exemple des colonnes
temporaires.
Il
q Voici
la suite dinstructions permettant la modification du
nom de la colonne de nom Couleur de la table voiture en
Teinte en changeant son type.
Page 14
09/10/2020
Riadh Zaafrani
7
Modification
q ALTER TABLE voiture
ADD COLUMN Teinte CHAR(60);
q SELECT * FROM voiture ;
NumVoit Marque Type
1
2
3
4
5
6
Peugeot 404
SM
Citroen
Opel
GT
Peugeot 403
Renault Alpine A310 Rose
Bleue
Floride
Renault
Couleur Teinte
NULL
Rouge
Noire
NULL
Blanche NULL
Blanche NULL
NULL
NULL
Page 15
09/10/2020
Riadh Zaafrani
Modification
q UPDATE voiture
SET Teinte=Couleur ;
q SELECT * FROM voiture ;
NumVoit Marque Type
Peugeot 404
Citroen SM
GT
Opel
Peugeot 403
1
2
3
4
5
6
Couleur Teinte
Rouge
Rouge
Noire
Blanche Blanche
Noire
Blanche Blanche
Renault Alpine A310 Rose
Bleue
Renault Floride
Rose
Bleue
Page 16
09/10/2020
Riadh Zaafrani
8
Modification
q ALTER TABLE voiture
DROP COLUMN Couleur;
q SELECT * FROM voiture ;
NumVoit Marque Type
1
2
3
4
5
6
Peugeot 404
Citroen SM
Opel
GT
Peugeot 403
Renault Alpine A310 Rose
Bleue
Renault Floride
Teinte
Rouge
Noire
Blanche
Blanche
Publicité
Page 17
09/10/2020
Riadh Zaafrani
Plan du cours
q Cr ation des tables avec SQL
q Modification de la structure
dune table avec SQL
q Les contraintes dint grit
sous SQL
Page 18
09/10/2020
Riadh Zaafrani
9
CONTRAINTES DINT GRIT
q Lors de l tape de conceptualisation, on a d fini
la notion de domaine , qui d crira lensemble
des valeurs que peut prendre un attribut.
q Au niveau de SQL, une premi re approche du
domaine est tablie par le choix du type de la
colonne, mais cela nest pas assez restrictif en
g n ral.
Page 19
09/10/2020
Riadh Zaafrani
CONTRAINTES DINT GRIT
q SQL vous permet de d finir des conditions de validit plus
fines lors de la cr ation de la table, que lon nomme
contraintes dint grit .
q Cest le SGBD qui applique ces conditions au moment de
linsertion, de la modification ou m me de la suppression
li es
de donn es dans le cas ou ces derni res sont
dautres tables. Cette tape est parfois fastidieuse, mais
elle garantit
la coh rence des donn es et vite de se
retrouver avec des bases de donn es, conceptuellement
correctes, mais inutilisables faute de donn es valides.
Page 20
09/10/2020
Riadh Zaafrani
10
CONTRAINTES DINT GRIT
q On peut distinguer diff rents types de contraintes
sur les colonnes :
q les propri t s g n rales comme lunicit et
lobligation;
q les restrictions dappartenance un ensemble ;
q les d pendances entre plusieurs colonnes.
Page 21
09/10/2020
Riadh Zaafrani
CONTRAINTES DINT GRIT :
Propri t s g n rales
1) La valeur de la colonne doit tre renseign e
absolument (NOT NULL).
2) La valeur doit tre unique compar e toutes les
valeurs de la colonne de la table (UNIQUE).
q Lorsque les deux conditions pr c dentes sont
r unies,
la colonne peut servir identifier un
enregistrement et constitue donc une cl
candidate .
Page 22
09/10/2020
Riadh Zaafrani
11
CONTRAINTES DINT GRIT :
Propri t s g n rales
q On rappelle quil ne peut y avoir quune seule cl que lon
d signera en SQL par le mot cl PRIMARY KEY. Ici, on
indique que la colonne NumAch est choisie comme cl de
la table (donc implicitement unique et non nulle) et que la
colonne Nom doit toujours tre renseign e.
q CREATE TABLE personne (
NumAch INT PRIMARY KEY,
Nom CHAR(20) NOT NULL,
Age INT
) ;
Page 23
09/10/2020
Riadh Zaafrani
CONTRAINTES DINT GRIT :
Propri t s g n rales
q Si aucune mention nest pr cis e comme pour la colonne
Age, elle peut tre renseign e ou non.
q Si la cl est constitu e de plusieurs colonnes (elle est dite
composite) ; on indique la liste des colonnes constitutives
de la cl la suite du mot cl PRIMARY KEY.
q CREATE TABLE vente (
DateVente DATE,
PRIX INT,
NumAch INT,
NumVoit INT,
PRIMARY KEY (NumAch, NumVoit)) ;
Page 24
09/10/2020
Riadh Zaafrani
12
CONTRAINTES DINT GRIT :
Condition dappartenance un ensemble
q Il sagit de d crire le domaine dans lequel la colonne pourra prendre
ses valeurs. Un ensemble peut tre d crit :
q En donnant la liste de tous ses l ments constitutifs (IN). Lensemble
des jours de la semaine ne peut tre exprim que de cette mani re :
lundi , mardi , etc. On v rifie que la colonne couleur ne peut
prendre que des valeurs normalis es : Rouge Vert ou Bleu.
q CREATE TABLE voiture(
NumVoit INT PRIMARY KEY,
Marque VARCHAR(30) NOT NULL,
Type VARCHAR(20),
Couleur VARCHAR(40) CHECK Couleur in (Rouge,Vert,Bleu));
Page 25
09/10/2020
Riadh Zaafrani
CONTRAINTES DINT GRIT :
Condition dappartenance un ensemble
q Par une expression (>, < , BETWEEN&).
q Par exemple, le prix doit tre sup rieur 1 000.
q On v rifie que l ge est compris entre 1 et 99.
Publicité
q CREATE TABLE personne
(NumAch INT PRIMARY KEY,
Nom CHAR(20) NOT NULL,
Ville CHAR(40),
AGE INT NOT NULL CHECK (Age BETWEEN 1 AND 99)
);
Page 26
09/10/2020
Riadh Zaafrani
13
CONTRAINTES DINT GRIT :
Condition dappartenance un ensemble
q Par une r f rence aux valeurs dune colonne dune autre table
(REFERENCES). Les colonnes doivent tre de m me type et lon ne
peut plus d truire par d faut une table qui appara t comme r f rence.
On v rifie que les valeurs identifiantes des personnes NumAch et des
voitures NumVoit de la table vente existent bien dans les tables de
r f rence personne et voiture.
q CREATE TABLE vente (
DateAch DATE,
PRIX INT,
NumAch INT NOT NULL REFERENCES personne(NumAch),
NumVoit INT NOT NULL REFERENCES voiture(NumVoit),
PRIMARY KEY (NumAch, NumVoit)) ;
Page 27
09/10/2020
Riadh Zaafrani
Condition sur plusieurs colonnes
(contrainte de table)
q Lorsque lon d sire exprimer des contraintes plus labor es impliquant
plusieurs colonnes, on peut d finir une contrainte de table en utilisant le
mot cl CONSTRAINT. On v rifie que la colonne Age et la colonne
Ville doivent tre renseign es ou vides en meme temps.
q CREATE TABLE personne
(NumAch INT PRIMARY KEY,
Nom CHAR(20) NOT NULL,
Ville CHAR(40),
AGE INT,
CONSTRAINT la_contrainte CHECK ( (Age IS NOT NULL AND Ville
IS NOT NULL) OR (Age IS NULL AND Ville IS NULL) );
Page 28
09/10/2020
Riadh Zaafrani
14
D nomination des contraintes
q Les contraintes peuvent tre nomm es afin d tre plus
facilement manipul es ult rieurement.
q Dans le cas o aucun nom nest affect explicitement
une contrainte, Oracle g n re automatiquement un nom
de la forme SYS_CXXXXXX(XXXXXX est un nombre
entier unique).
q De tels noms ne sont pas parlants. Il est donc pr f rable de
la fournir vous-m me. Lemploi dune strat gie pour affecter
des noms permet de mieux les identifier et les g rer.
Page 29
09/10/2020
Riadh Zaafrani
D nomination des contraintes
q Lors de laffectation explicite dun nom une contrainte, il est
la convention de d nomination suivante :
pratique dutiliser
TABLE_COLONNE_TYPEDECONTRAINTE.
q O TYPEDECONTRAINTE est
associ au type de contrainte :
labr viation mn monique
q NN NOT NULL
CK CHECK UQ UNIQUE
q PK PRIMARY KEY
FK FOREIGN KEY
q Cela facilite la compr hension des messages, et permet de modifier ou
de d truire une contrainte :
q ALTER TABLE CINEMA DROP CONSTRAINT CINEMA_SALLE_NN
Page 30
09/10/2020
Riadh Zaafrani
15
D nomination des contraintes
q Exemple :
q CREATE TABLE UTILISATEUR (
NO_UTILISATEUR NUMBER(6)
CONSTRAINT UTILISATEUR_PK PRIMARY KEY,
NOM_PRENOM VARCHAR2(20)
CONSTRAINT
UTILISATEUR_NOM_PRENOM_NN NOT NULL,
Page 31
09/10/2020
Riadh Zaafrani
D nomination des contraintes
DATE_CREATION DATE DEFAULT SYSDATE NOT NULL,
DATE_MISEAJOUR DATE DEFAULT SYSDATE NOT
NULL,
CONNECTIONS NUMBER(6)
CONSTRAINT
UTILISATEUR_CONNECTIONS_CK
CHECK (CONNECTIONS BETWEEN 1 AND 10),
CONSTRAINT UTILISATEUR_DATE_MISEAJOUR_CK
CHECK (DATE_CREATION <=
DATE_MISEAJOUR));
Page 32
09/10/2020
Riadh Zaafrani
16
Universit Tunis El Manar - Institut Sup rieur dInformatique
Module 2 Section 2 :
Le langage de d finition
des donn es (LDD)
de SQL
Riadh ZAAFRANI
1 re ann e MP2L
Merci pour votre attention
Page 33
09/10/2020
Riadh Zaafrani
17