SQL : Langage de Définition des Données (LDD)
Le LDD est le sous langage SQL qui permet de définir les données dans un SGBDR.
1) Créer une base des données :
CREATE DataBase nom_BD ;
Exemple : CREATE DataBase GStagiaire;
Pour utiliser une base de données, on utilise la syntaxe : Use nombd ;
2) Création des tables :
a) Type de données :
• Chaine de caractères :
CHAR(N) : Déclare un champ chaine de caractères de taille fixe (taille=n caractères)
VARCHAR(N) : Déclare un champ chaine de caractères de taille variable (max=n caractères)
• ENTIER : SMALLINT INT LONG
• REEL : FLOAT
• NUMBER (N, M) :nombre de N chiffre, M chiffre après la virgule
• DATE : Date Time TimeStamp
Remarque : pour un champ auto incrémentale, on utilise la clause IDENTITY (grain, pas)
Grain : valeur de départ
Pas : valeur d’incrémentation
b) Créer une table :
Syntaxe : Pour créer une table, on fait recours à l’instruction suivante :
create table nom_table(
nom_champ type [liste de contraintes d’attribut],
nom_champ type [liste de contraintes d’attribut],
nom_champ type [liste de contraintes d’attribut],
…..
liste de contraintes de la table) ;
avec :
▪ Le type peut être : varchar(n), date, int, time ….
▪ Les contraintes d’attributs ou de la table (appelées aussi contraintes d’intégrités) : les règles à suivre (on va les
détailler dans la suite du cours)
c) Contraintes d’intégrité :
• Default : Permet de spécifier une valeur par défaut pour un attribut de sort qu’il ne sera jamais NULL ;
• Not Null : Cette contrainte oblige le SGBD de ne pas accepter NULL dans un attribut de la table ;
• Unique : Cette contrainte permet d’interdire la répétition des valeurs dans un attribut ;
• Check : si un attribut doit satisfaire une condition spécifique ;
• Primary key : crée une clé primaire pour la table ;
• Foreign key : cette contrainte permet d’ajouter une clé étrangère à la table.
• Contraintes d'attribut vs Contraintes de la table :
On peut ajouter une contrainte au niveau d’attribut, comme on peut l’ajouter au niveau de la table (dans des cas, on
n’aura pas le choix, et l’une des deux options est le plus correcte).
L’écriture d’une telle contrainte est liée au type de l’option (contrainte d’attribut ou de table). Par exemple :
La contrainte d’attribut : S’écrit comme contrainte de table :
Primary key [CONSTRAINT nom_contrainte ]PRIMARY KEY (attribut1, attribut2,
…)
La contrainte d’attribut : S’écrit comme contrainte de table :
FOREIGN KEY (attribut_clé_étrangère) CONSTRAINT nom_contrainte FOREIGN
REFERENCES nom_table (attribut_référence) KEY (attribut_clé_étrangère) REFERENCES
nom_table (attribut_référence)
La contrainte d’attribut : S’écrit comme contrainte de table : (cette écriture est obligatoire
dans le cas d’un couple qui doit être unique et non pas un seul
unique champ)
CONSTRAINT nom_contrainte UNIQUE (colonne1, colonne2,..)
La contrainte d’attribut : S’écrit comme contrainte de table :
check(condition) CONSTRAINT nom_contrainte check(condition)
Exercice 1 :
Trouver comment peut-on écrire la contrainte not null comme étant une contrainte de table :
Exemple :
CREATE TABLE TPERSONNE(
PRS_ID INTEGER,
PRS_NOM VARCHAR(32),
PRS_PRENOM VARCHAR(32),
PRS_SEXE CHAR(1) DEFAULT ‘M’,
PRS_DATE_NAISSANCE DATETIME DEFAULT GETDATE(),
CONSTRAINT pk_personne PRIMARY KEY (PRS_ID)
);
Exercice 2 :
Créer les tables :
Soit les tables : Module, Evaluation et Stagiaire
Module (NumM, Intitule, Coef)
Evaluation (#NumM, #CNE, DateN, Note)
Stagiaire (CNE, Nom, Filiere)
• Contraintes d'intégrité référentielle :
SQL2 permet de spécifier les actions à entreprendre pour le maintien de l’intégrité référentielle, lors d’une suppression
ou d’une modification d’un tuple référencé.
CASCADE : cascader les suppressions ou modifications
SET NULL : rendre nul les attributs référençant
RESTRICT : rejet de la mise à jour. C’est l’option par défaut
Au cours de déclaration des clés étrangères, On ajoute : on delete cascade /set null et on update cascade/set null
Remarque :
ON DELETE CASCADE : indique que si une ligne est supprimée de la table maître, les lignes dépendantes de la table
détail seront également supprimées.
Résumé :
CREATE TABLE table
( définition des colonnes :
colonne type [ NOT NULL [UNIQUE] ] [ DEFAULT valeur ] [ PRIMARY KEY ] [ REFERENCES table ] [ CHECK condition ]
…
contraintes de table :
[ PRIMARY KEY (liste de colonnes) ],
[ UNIQUE (liste de colonnes) ]
…
[ FOREIGN KEY (liste de colonnes) REFERENCES table
[ ON DELETE {RESTRICT | CASCADE | SET NULL} ]
[ ON UPDATE {RESTRICT | CASCADE | SET NULL} ] ,
…
[ CHECK condition ]
…)
3) Modifier le schéma d'une table :
L’ordre sql alter table est utilisé pour modifier le schéma d’une table : ajouter/supprimer une colonne,
ajouter/supprimer une contrainte, changer le type d’un champ.
Dans la suite, on va détailler la syntaxe sql à utiliser selon l’opération souhaitée :
a) Ajouter une colonne à une table :
Pour ajouter la colonne à une table :
Alter Table nom_table
ADD nom_colonne type contrainte_d’attribut ;
b) Supprimer une colonne d’une table :
Pour supprimer la colonne d’une table :
Alter Table nom_table
DROP column nom_colonne ;
c) Changer le type des données d’une colonne :
Pour changer le type de colonne :
Alter Table nom_table
Alter column nom_colonne type ;
d) Ajouter une contrainte à une table :
Pour ajouter une contrainte à une table :
Alter Table nom_table
ADD Constraint nomContrainte check(condition) ;
Alter Table nom_table
ADD Constraint nom_contrainte unique(colonne1,colonne2) ;
e) Supprimer une contrainte d’une table :
Pour supprimer une contrainte d’une table :
Alter Table nom_table
DROP Constraint nom_contrainte ;
f) Renommer le nom d’une colonne :
Pour changer le nom de la colonne :
Exec sp_rename ‘nom_table.ancien_colonne’, ’nouveau_nom’, ’Column’
4) Supprimer des tables et des bases de données :
Pour supprimer les tables et les bases de données, on utilise la clause DROP
DROP TABLE nom_table
DROP DataBase nom_base_de_données
Exemple :
Soit le Schéma relationnel GestAud suivant :
Auditeur (#IdAud, Nom)
Examen (#IdEx, Salle, DateEx)
Passe_Ex (IdAud, IdEx,Note)
1. Créer la base de données GestAud
2. Créer les tables Auditeur, Examen, Passe_Ex (la valeur par défaut pour la note est la valeur 0)
3. Ajouter le champ telAud avec le type char(10)
▪ Modifier le type de champ telAud en varchar
▪ Renommer le champ telAud en téléphone
▪ Supprimer le champ téléphone de la table Auditeur
▪ Supprimer la contrainte par défaut 0 pour le champ note
▪ Modifier le champ note de telles sortes qu’il ne sera jamais Null
▪ Ajouter la contrainte pour que la valeur de la note soit entre 0 et 20
▪ Modifier le champ IdEx de la table Examen de sorte qu’il sera Null
▪ Ajouter la contrainte (clé primaire) pour les différentes tables
▪ Supprimer la contrainte de clé primaire pour la table Examen
▪ Ajouter la contrainte (clé étagère) pour les différentes tables (ajouter la contrainte de clé primaire pour
la table examen)
4. Insérer des enregistrements pour les différentes tables de la base de données GestAud
