Chapitre : définition des données :
le Langage de Définition de Données (LDD)
1
Instructions SQL
Interrogation de données :
SELECT : Extraction de données
Langage de manipulation de (LMD) :
INSERT : insertion
UPDATE : mise à jour
DELET : suppression de lignes
Langage de définition de données (LDD) :
CREATE : création d'un objet (table, …)
ALTER : modification d'un objet
DROP : supprimer un objet
RENAME : : renommer un objet
2
CREATE Langage de définition de
ALTER données (LDD)
DROP
RENAME
TRUNCATE
3
Principaux objets d'une base de
données
Objet Description
Table Unité de stockage élémentaire,
composée de lignes et de
colonnes
Vue Représente de manière logique des sous-
groupes de données
Index Améliore les performances de certaines
requêtes
4
Création d’une base de données
CREATE DATABASE ma_base
ma_base : nom de la base
CREATE DATABASE IF NOT permet de ne pas retourner
EXISTS ma_base d’erreur
5
En mysql
Afficher la liste des bases existantes sur votre compte.
Show databases ;
Pour accédez à une base de donnée et travailler
Use nomBase;
6
supprimer une base de données
DROP DATABASE ma_base
Attention : va supprimer toutes les tables et toutes les
données de cette base
Ne pas afficher d’erreur si la base n’existe pas :
DROP DATABASE IF EXISTS ma_base
7
Créer une table
CREATE TABLE nomTable
colonne1 type1 [DEFAULT val1] [NOT NULL],
colonne2 type2 [DEFAULT val2] [NOT NULL], …
nomTable : le nom de la table
Colonne<i> et type<i> : nom de la colonne avec son type (CHAR,
VARCHAR, NUMBER,DATE, …)
.Exemple :
CREATE TABLE utilisateur
(
id INT,
nom VARCHAR(100),
prenom VARCHAR(100),
email VARCHAR(255),
date_naissance DATE date_naissance : date de naissance enregistre au format
pays VARCHAR(255), AAAA-MM-JJ (exemple : 1973-11-17)
ville VARCHAR(255),
code_postal VARCHAR(5), Pas de virgule à la fin de de la dernière instruction
nombre_achat INT
); 8
Types de données des colonnes
Type de données Syntaxe Description
Alphanumérique CHAR(n) Chaîne de caractères de longueur fixe n
(n<16383)
le type CHAR pour les colonnes qui contiennent des
chaînes de longueur constante.
VARCHAR(n) Chaîne de caractères de n caractères
maximum (n<16383)
Numérique NUMBER(n [,d]) Nombre de n chiffres [optionnellement d
-NUMBER
-NUMBER(taille_maxi)
après la virgule]
-NUMBER(taille_maxi,
décimales)
utilisé par Oracle
INT Entier signé de 32 bits (-2E31 à 2E31-1)
mysql
FLOAT Nombre à virgule flottante
Horaire DATE Date sous la forme 1999-12-13
TIME Heure sous la forme 12:54:24.85
TIMESTAMP Date et Heure 9
CHAR
Exemple
nom CHAR(3),
3 est la longueur maximale (en nombre de caractères) qu'il
sera possible de stocker dans le champ ;
-L'insertion d'une chaîne dont la longueur est supérieure à 3
sera refusée.
-Une chaîne plus courte que 3 sera complétée par
des espaces
-nom char()
par défaut, longueur est égale à 1.
10
varchar
le type VARCHAR pour les colonnes qui contiennent des
chaînes de longueurs variables.
On déclare ces colonnes par :
VARCHAR(longueur )
longueur indique la longueur maximale des chaînes
contenues dans la colonne.
Les types Oracle sont les types SQL2 mais le type
VARCHAR s'appelle VARCHAR2 dans Oracle
11
Création d’une table
Syntaxe complete:
CREATE TABLE nomTable(
colonne1 type1 [DEFAULT val1] [NOT NULL],
colonne2 type2 [DEFAULT val2] [NOT NULL],
…
CONSTRAINT nomContrainte TypeContrainte
);
nomContrainte : nom de la contrainte
typeContrainte : exemple de type de la contrainte :clé primaire, clé
étrangère,etc..
12
L’option DEFAULT
Possibilité de donner une valeur par défaut pour
une colonne si la colonne n'est pas renseignée en
utilisant l'option DEFAULT
Cette option empêche l'insertion de valeurs NULL
dans une colonne lors de l'ajout d'une ligne
Exemples :
datemaj DATE DEFAULT CURRENT_DATE,
ville Varchar(30) DEFAULT ‘Paris’,
13
Contraintes
NOT NULL
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
Objectif : programmer des règles de gestion au niveau des
colonnes des tables
alléger les programme client
Chaque contrainte doit être nommée
CONSTRAINT nomContrainte TypeContrainte
14
NOT NULL
La contrainte NOT NULL interdit la présence de
valeurs NULL dans la colonne à laquelle elle s'applique
Par défaut, les colonnes peuvent contenir des valeurs
NULL
Exemple :
CREATE TABLE groupe
(codeg int, nomg varchar(30) NOT NULL)
15
clé primaire
Toute définition de table doit comporter au moins une
contrainte de type PRIMARY KEY.
Une contrainte PRIMARY KEY crée une clé primaire pour la
table
La contrainte PRIMARY KEY est une colonne qui identifie
de manière unique chaque ligne d'une table
garantit qu'aucune colonne faisant partie de la clé primaire
ne contient de valeur NULL
Exemple :
CREATE TABLE groupe Prefixe ≪ PK_ ≫
(codeg varchar(6), nomg varchar(30) NOT NULL,
CONSTRAINT pk_groupe PRIMARY KEY(codeg) )
16
clé étrangère
La contrainte FOREIGN KEY, ou contrainte d'intégrité référentielle,
désigne une colonne ou une combinaison de colonnes comme étant
une clé étrangère établit une relation avec une clé primaire
Exemple :
CREATE TABLE stagiaire
Prefixe ≪ FK_ ≫
(cin varchar(12), nom varchar(30), … ,
codeg varchar(6),
CONSTRAINT pk_stagiaire PRIMARY KEY (cin),
CONSTRAINT fk_groupe FOREIGN KEY (codeg)
REFERENCES groupe(codeg)
Table groupe
17
Création d’une table
Pilote
brevet nom nbrHvol prime embauche typeAvion compa
compagnie
CREATE TABLE compagnie ( compa nrue rue ville nomcompa
compa VARCHAR(10),
nomComp VARCHAR(30) NOT NULL,
nrue NUMBER(3),
rue VARCHAR(20),
ville VARCHAR(15) DEFAULT ‘Paris’,
CONSTRAINT pk_Comppagnie PRIMARY KEY(comp),
);
Rq : Ordre important
CREATE TABLE pilote (
brevet varchar(6), nom varchar(15) IS NOT NULL, nbHvol NUMBER(7),
compa varchar(4),
CONSTRAINT pk_pilote PRIMARY KEY(brevet),
CONSTRAINT un_nom UNIQUE(nom),
CONSTRAINT fk_pilote_compa FOREIGN KEY (compa) REFERENCES
compagnie(compa)
); 18
UNIQUE
Une contrainte d'intégrité de type clé UNIQUE exige que
chaque valeur dans une colonne ou dans un ensemble de
colonnes soit unique
Exemple :
CREATE TABLE stagiaire
(cin varchar(12), numSt int, nom varchar(30), … ,
codeg varchar(6),
CONSTRAINT pk_stagiaire PRIMARY KEY (cin),
CONSTRAINT fk_groupe FOREIGN KEY (codeg)
REFERENCES groupe(codeg),
CONSTRAINT uk_ni UNIQUE (numSt) )
Interdit qu'une colonne contient deux valeurs identiques.
19
CHECK
La contrainte CHECK définit une condition que
chaque ligne doit obligatoirement satisfaire
Exemple :
CREATE TABLE stagiaire
(cin varchar(12), nom varchar(30),
sexe CHAR(1),note int(2)… ,
CHECK (sexe=‘F’ or sexe=‘M’),
;
20
Ajouter, supprimer ou
renommer une contrainte
Des contraintes d'intégrité peuvent être ajoutées ou
supprimées par la commande ALTER TABLE.
21
Suppression d’un contrainte
ALTER TABLE <nomTAble> DROP CONSTRAINT
nomContrainte [CASCADE];
Utilisez l'ordre ALTER TABLE avec la clause DROP
L'option CASCADE provoque également la
suppression de toutes les contraintes associées
Exemple :
ALTER TABLE stagiaire
DROP CONSTRAINT fk_groupe
22
Ajouter ou renommer une contrainte
Ajout de contrainte
Utilisé après création de la table sans contrainte
:
ALTER TABLE <nomTable> ADD CONSTRAINT nom_contrainte
typeContrainte;
alter table etudiant
add (constraint notStudnt CHECK (note between 0 and
20) )
23
Modifier ou renommer contraintes
On peut aussi modifier l'état de contraintes par MODIFY
alter table personne modify (
constraint personne_sexe_ck check(sexe in ('m', 'f')))
renommer
alter table personne
RENAME CONSTRAINT Nmctr TO NOMP
24
Activer désactiver des contraintes
Désactiver des contraintes
Les contraintes d'intégrité sont parfois gênantes. On peut
vouloir les suspendre pour améliorer les performances
durant le chargement d'une grande quantité de données
dans la base.
Oracle fournit la commande
ALTER TABLE ... DISABLE/ENABLE.
Exemple
ALTER TABLE EMP
DISABLE CONSTRAINT NOM_UNIQUE
25
Supprimer une table
Syntaxe :
DROP TABLE <nomTable>;
Exemple DROP TABLE client_2009
A savoir : s’il y a une dépendance avec une autre
table, il est recommande de les supprimer avant de
supprimer la table.
C’est le cas par exemple s’il y a des clés étrangères.
Possiblité d’utiliser l’option CASCADE CONSTRAINTS par Oracle :
DROP TABLE <nomTable>
[CASCADE CONSTRAINTS];
L'option CASCADE CONSTRAINTS est requise s'il s'agit de la table
parent d'une relation de clé étrangère.
26
Attention : il faut utiliser cette commande avec attention
car une fois supprimée, les données sont
perdues.
Avant de l’utiliser sur une base importante il peut être judicieux
d’effectuer un backup (une sauvegarde) pour éviter les
mauvaises surprises
27
Intérêts
Il arrive qu’une table soit créer temporairement pour stoker des
données qui n’ont pas de vocation a etre re-utiliser.
La suppression d’une table non utilisée est avantageux sur
plusieurs aspects :
• Libérer de la memoire et alleger le poids des backups
• Eviter des erreurs dans le futur si une table porte un nom
similaire ou qui porte a confusion
• Lorsqu’un développeur ou administrateur de base de données
découvre une application, il est plus rapide de comprendre le
système s’il n’y a que les tables utilisées qui sont présente
28
Renommer une table
Syntaxe :
ALTER TABLE <nomTable> RENAME To <newname>
29
Modifier la définition d’une table
Il est possible de modifier la structure d’une table pour
ajouter, par exemple, une colonne oubliée ou changer une
définition de colonne existante
Cela est possible grâce à la commande ALTER TABLE
Cette commande permet aussi de gérer les colonnes d'une
table : ajout d'une colonne (après toutes les autres
colonnes), suppression et modification d'une colonne
existante
Exemple :
ALTER TABLE resultat
ADD appreciation VARCHAR(60)
30
Ajout d'une colonne - ADD
permet d'ajouter une ou plusieurs colonnes à une table
existante.
ALTER TABLE <nomTable> ADD (col1 type1, col2 type2 …);
Exemple :
ALTER TABLE Pilote ADD (tel VARCHAR(10));
Les types possibles sont les mêmes que ceux décrits avec la
commande CREATE TABLE.
Les parenthèses ne sont pas nécessaires si on n'ajoute
qu'une seule colonne.
31
Ajout d'une colonne - suite
Il est possible de définir des contraintes de colonne.
Exemple
alter table personne
add (email_valide char(1)
CONSTRAINT uk_ni UNIQUE (email_valide))
32
● Suppression de colonne :
Syntaxe :
ALTER TABLE <nomTable> DROP COLUMN <nomColonne>;
Exemple :
ALTER TABLE Pilote DROP COLUMN adresse;
PS : La colonne supprimée ne doit pas être référencée par une clé
étrangère
● Renommer une colonne :
ALTER TABLE <nomTable> RENAME COLUMN <nomColonne> To
<newColumnName>;
Exemple :
ALTER TABLE Pilote RENAME COLUMN ville To adresse;
33
Modification d'une colonne
ALTER TABLE table
MODIFY (col1 type1, col2 type2, ...)
col1, col2... sont les noms des colonnes que l'on veut modifier.
Elles doivent bien sûr déjà exister dans la table. type1, type2,...
sont les nouveaux types que l'on désire attribuer aux colonnes.
Exemple
alter table personne
modify (
prenoms null,
nom varchar(50))
34
On peut donner une contrainte de colonne dans la nouvelle
définition de la colonne.
Exemple 2
alter table personne modify (
sexe char(1),
constraint personne_sexe_ck check(sexe in ('m', 'f')))
35
Modification d'une colonne (suite)
Il est possible de modifier la définition d'une colonne, à
condition que la colonne ne contienne que des valeurs
NULL ou que la nouvelle définition soit compatible avec
le contenu de la colonne :
-on ne peut pas diminuer la taille maximale d'une
colonne.
-on ne peut spécifier 'NOT NULL' que si la colonne ne
contient pas de valeur nulle.
-Il est toujours possible d'augmenter la taille maximale
d'une colonne, tant qu'on ne dépasse pas les limites
propres à SQL.
on peut dans tous les cas spécifier 'NULL' pour
autoriser les valeurs nulles.
36
Table Pilote
brevet nom nbrHvol prime embauche typeAvion compa
PL-1 Gratien Viel 450 500 05/02/1965 A320 AF
PL-2 Didier Donsez 0 null 13/05/1965 A320 AF
PL-3 Richard Grin 1000 null 11/09/2001 A320 SING
PL-4 Placide Fresnais 2450 500 21/09/2001 1330 SING
PL-5 Daniel Vielle 400 600 16/01/1965 A340 AF
PL-6 Françoise Tort 0 24/12/2000 A340 CAST
37
Exemple :
- changement de la taille de la colonne compa et change de la contrainte par
défaut.
ALTER TABLE Pilote MODIFY compa VARCHAR (6) DEFAULT ‘SING’ ;
- Rendre possible l’insertion de valeur nulle dans la colonne compa :
ALTER TABLE Pilote MODIFY compa ;
38