TP3 Atelier syst me dinformation
AU 2015-2016
Langage SQL
Partie th orique
I.
1. Pr sentation g n rale
SQL (Structured Query Language) est un langage qui permet lutilisateur de sp cifier ses
interrogations en terme de r sultats sans avoir d crire les proc dures suivre pour obtenir
ces r sultats.
SQL fournit un ensemble de commandes pour une vari t de t ches, dont:
linterrogation de la base de donn es
linsertion, la mise jour et la suppression des donn es dans la base de donn es
la cr ation et la modification du sch ma de la BD
la d finition de vues
le contr le de lacc s aux donn es
la cr ation d'index pour acc l rer les interrogations
Une commande SQL est aussi appel e instruction SQL ou requ te SQL.
2. Structure du langage
En tant que langage dacc s aux donn es, SQL se compose de trois sous ensembles
dinstructions :
SQL est un
langage de d finition de donn es
(LDD), c'est- -dire qu'il
tables dans une base de donn es relationnelle, ainsi
permet de cr er des
que d'en modifier ou en supprimer.
SQL est un langage de manipulation de donn es (LMD), cela signifie qu'il permet de
s lectionner, ins rer, modifier ou supprimer des donn es dans une table d'une base de
donn es relationnelle.
SQL est un langage de protections dacc s (LCD). Il est possible avec SQL de d finir
au niveau des utilisateurs d'une base de donn es.
des permissions
On parle de DCL (Data Control Language).
3. Le langage de d finition de donn es
3.1. Type de donn es
Les types de donn es autoris s par SQL :
Publicité
Char(n) : chaine de caract res sur n positions (peut atteindre 255).
Varchar(n) ou Varchar2(n) : chaine de caract res sur n positions (peut atteindre
2000).
Long : chaine de caract re pouvant m moriser de longs textes allant jusqu 2Go.
Mme Im ne Jemmali
1
TP3 Atelier syst me dinformation
AU 2015-2016
Number(n,m) : n est le nombre total de positions , m est le nombre de positions apr s
la virgule.
Small Int : entier sur 15 bits.
Integer : entier sur 31 bits sign s.
Float : r el.
Date : Donn es de type date.
3.2.Les contraintes dint grit d finis par SQL
Une contrainte dint grit est une r gle qui permet de contr ler la validit et la coh rence des
valeurs entr es dans les diff rentes tables de la base. Elle peut tre d finie en deux moments,
lors de la cr ation dune table ou bien par une commande de modification dune table
existante. Une contrainte dint grit est d finie soit sur un attribut, soit sur une table.
a. Sur un attribut :
Ces contraintes suivent la d finition des attributs correspondant.
Not Null : Sp cifie que pour tout tuple, lattribut doit tre renseign .
Unique : Sp cifie que toutes les valeurs de lattribut seront distinctes.
Primary Key : Sp cifie que lattribut est une cl primaire pour la table, cette
contrainte peut tre remplac e par Not Null et Unique.
References table (attribut) : Cest la contrainte dint grit r f rentielle, elle se d finit
par rapport une cl trang re, et v rifie que chaque valeur de lattribut existe au
pr alable dans la table dont lattribut est r f renc .
Check : Cest une contrainte associ e une condition qui doit tre v rifi e par toutes
les valeurs de lattribut.
b. Sur une table :
Ces contraintes portent sur un ensemble dattributs dune m me table, une virgule s pare
la d finition des contraintes des d finitions des attributs.
Unique (attribut1, attribut2, &) : Lunicit porte sur la combinaison des valeurs de
tous les attributs cit s entre les parenth ses.
Primary Key (attribut1, attribut2, &) : la cl primaire de la table est une cl
compos .
Frreign Key (attribut1, attribut2, &) References table (attributi, attributj, &) : D signe une cl trang re compose de plusieurs attributs.
Loption On Delete Cascade indique que la suppression dune ligne de la table de
r f rence va entrainer la suppression automatique des lignes r f renc es.
3.3. Cr ation de table
Au moment de la cr ation de la table, le no dune table doit tre unique dans la base de
Publicité
donn es et le nom dun attribut doit tre unique dans une table.
Mme Im ne Jemmali
2
TP3 Atelier syst me dinformation
AU 2015-2016
Syntaxe :
Create
nom_colonne2 type_colonne2 contrainte_colonne2, ...) ;
(nom_colonne1
nom_table
table
type_colonne1
contrainte_colonne1,
Exemple :
Soit les quatre tables Comp tition, R sultat, Membre et Cat gorie et les lignes de commande
suivantes :
Base de donn es "Club de voile"
Create table competition
(ncomptb number(4),
inticompt varchar2(30) not null,
datecompt date,
lieucompt varchar2((30),
constraint pk_compt primary key (ncomptb) );
Create table Membre
(nmembre number(6),
&
Ncateg number(4),
constraint pk_memb primary key( nmembre),
constraint fk_membforeign key ncateg references Cat gorie(ncateg));
3.4. Affichage de la structure dune table
describe nom_table ;
3.5.Modification de la structure dune table
a. Suppression dune table
drop table nom_table
b. Ajout dun attribut
Alter table nom_table
Mme Im ne Jemmali
3
TP3 Atelier syst me dinformation
AU 2015-2016
Add attribut type ;
Publicité
Exemple :
Alter table comp tition
Add dur ecompt number(15) ;
c. Suppression dun attribut
Alter table nom_table
Drop column attribut ;
Exemple :
Alter table comp tition
Drop column dur ecompt ;
d. Agrandissement dun attribut existant
Alter table nom_table
Modify attribut nouveau_type ;
Exemple :
Alter table competition
Modify initcompt varchar2(40) ;
e. Ajout dune contrainte
Alter table nom_table
Add constraint nom_contrainte d finition_contrainte ;
Exemple :
Alter table Membre
Add constraint datenaissance check(age>= 18) ;
f. Suppression dune contrainte
Alter table nom_table
Drop constraint nom_contrainte ;
Exemple :
Mme Im ne Jemmali
4
AU 2015-2016
TP3 Atelier syst me dinformation
Alter table Membre
Drop constraint datenaissance ;
g. D sactivation dune contrainte
Alter table nom_table
Disable constraint nom_contrainte ;
Exemple :
Alter table Membre
Disable constraint datenaissance ;
h. Activation dune contrainte
Alter table nom_table
Enable constraint nom_contrainte ;
Exemple :
Alter table Membre
Publicité
Enable constraint datenaissance ;
II.
Partie pratique
Soit le sch ma relationnel suivant :
Fournisseur (NF , NomF , Ville , Statut)
Pi ces ( NP, NomP, Ville, Prix)
Projets ( NJ, NomJ, Ville, Debut)
R alisation (#NF, #NP, #NJ, Qte)
Les types des diff rents attributs est comme suit :
NF, NP, NJ et Qte number(3)
NomF, NomP et NomJ varchar2(20)
Ville varchar2(15),
Statut number(2)
Prix number(6,3)
Debut Date.
Mme Im ne Jemmali
5
TP3 Atelier syst me dinformation
AU 2015-2016
1. Cr er la table Fournisseur et attribuer les propri t s NOT NULL et UNIQUE sa cl
NF.
2. Cr er la table Pi ces et d finir la contrainte pkNP de la cl primaire quivalente
lattribution des propri t s NOT NULL et UNIQUE.
3. Cr er la table Projets sans d finir de contrainte ni de propri t s sur la cl , ceci sera
d fini ult rieurement par la commande ALTER TABLE.
4. Cr er la table R alisation en sp cifiant la contrainte de cl primaire pkFPJ(#NF,
#NP,#NJ) et lune des contraintes de cl trang re fkNF.
5. Ajouter lattribut Fin la table Projets.
6. Ajouter des attributs Couleur et Poids la table Pi ces.
7. Eliminer lattribut Prix de la table Pi ces.
8. Modifier la taille de lattribut Qte NUMBER(5).
9. Ajouter la contraint pkPJ de PRIMARY KEY sur lattribut NJ de la table Projets.
10. Retraire la contrainte pkFPJ.
11. Ajouter la contrainte pkFPJ.
12. Ajouter les contraintes de cl s trang res fkNP et fkNJ sur la table R alisation.
13. Ajouter la contrainte ckQt CHECK sur lattribut Qte (Qte>0) de la table R alisation.
Mme Im ne Jemmali
6