Module Base de données Licence 2

Le langage de requêtes SQL:

Définition des données (1)
H.BELLEILI
Université Badji Mokhtar Annaba
Département Informatique
2019-2020

1
 Plan
1. La commande CREATE TABLE
2. Les types de Données
3. Les contraintes
4. La commande ALTER TABLE
5. La commande DROP TABLE
6. Les procédures Stockées

2
 CREATE TABLE
Permet de créer une table en définissant le nom de la table, les colonnes
(attributs) et le type de chacune des colonnes.
La forme la plus simple:

CREATE TABLE <nom de table>(colonne1 type1, colonne2 type 2,…,colonnen typen);

<nom de table>: est une chaine de caractère commençant impérativement par un

caractère alphabétique (les chiffres et les caractères spéciaux ne doivent pas être au
début du nom.
SQL est insensible à la casse
<colonnei>: est un nom d’attribut
<typei> : est le type de l’attribut

3
TYPES DE DONNEES SUPPORTÉS

4
 Types Numériques

– INT ou INTEGER: entier (un sous-ensemble fini d'entiers

qui dépend de l'implémentation)
– SMALLINT : entier court (un sous-ensemble du type de
domaine entier qui dépend de l'implémentation)
– DECIMAL(p, d), DEC(p, d) ou NUMERIC(p, d): nombre
réel à point fixe , avec une précision (nombre total de
chiffres décimaux) de p chiffres et une échelle (nombre de
chiffres après la virgule) de d chiffres.
– REAL, DOUBLE PRECISION: nombres réels à virgule
flottante avec simple et double précision dépendant de
l'implémentation.
– FLOAT(n) : nombre réel avec une précision optionnelle
d'au moins n chiffres décimaux, la valeur par défaut de n
étant 0, et si n=0 on FLOAT ÛREAL
5
 Types catactères
– CHAR(n) ou CHARACTER(n): les chaînes de caractères de
longueurs fixes, la valeur par défaut de la longueur étant 1.

– VARCHAR(n): les chaînes de caractères de longueurs variables, avec

au maximum n caractères. Les chaînes de caractères constantes sont
placées entre simples quotes (guillemets) (ex., 'Bonjour tout le monde ')

6
 Types Temporels
• DATE : réserve 2 chiffres pour le mois et le jour et 4 pour l
’année : Sur 3 octets. L’affichage est au format ‘YYYY-MM-DD’.
• TIME : pour les heures, minutes et secondes l’heure au
format ‘HHH:MM:SS’ sur 3 octets
• TIMESTAMP : indique un moment précis par une date avec
heures, minutes et secondes (6 chiffres après la virgule)
‘YYYY-MM-DD HH:MM:SS’
• Les format de DATE, TIME et TIMESTAMP peuvent être
considérés comme un type spécial de chaîne de caractères. Par
conséquent, ils peuvent généralement être utilisés, après les
avoir converties en chaînes de caractères équivalentes, dans
les comparaisons de chaînes de caractères

7
 Types Binaires
• Les types BLOB (Binary Large OBject)
permettent de stocker des données non
structurées comme le multimédia (images,
sons, vidéo, etc.).
• la longueur maximum d'un BLOB peut être
spécifiée en kilo-octets (K), méga-octets (M)
ou en giga-octets (G). Par exemple, BLOB(20G)
spécifie une chaine de bits de longueur
maximum de 20 gigabits.

8
 Types Enumération
• Le type ENUM définit une liste de valeurs
permises (chaînes de caractères).

9
 Contraintes d’intégrité
• I. Les contraintes de domaine
• II. Les contraintes d’intégrité d’entité
• III. Les contraintes d ’intégrité référentielle

10
 Contraintes de domaine
• Ensemble des valeurs que peut prendre un attribut.
• Ces contraintes sont décrites dans la définition d ’un attribut, directement après
son type .
• NOT NULL : on impose que l ’attribut possède une valeur
• DEFAULT : on spécifie une valeur par défaut dont le type doit correspondre au type
de l ’attribut
• UNIQUE : interdit qu’une colonne contienne deux valeurs identiques
– Pour les colonnes UNIQUE le SGBD crée un index
CREATE TABLE Client (NumCli….
nomCli VARCHAR(25) NOT NULL,
CaCli INTEGER DEFAULT 0,
TypeCli VARCHAR(16) DEFAULT 'Particulier ’ …);

CREATE TABLE fournisseurs (... NomFour CHAR(25) NOT NULL UNIQUE, ..);

NomFour et NumFour sont deux clés candidates. NumFour a été choisi comme clé
primaire
11
 Contrainte de domaine
• CHECK(condition)
• La contrainte CHECK impose un domaine de
valeurs ou une condition simple ou complexe
entre colonnes
• Exemple : CHECK (note BETWEEN 0 AND 20),
• CHECK (grade=‘MCA' OR grade=‘MCB')
• CHECK grade IN(‘MCA’,’MCB’)
• CHECK date_depart<date_arrivee

12
 Contrainte de clé primaire
• La clause PRIMARY KEY.
• Une clé primaire doit toujours avoir une valeur déterminée
et unique pour la table.
• 2 cas possibles:
– Si elle porte sur 1 seul attribut la clause PRIMARY KEY est situé
après le type de l’attribut. Et l’attribut est NOT NULL
implicitement
– Si elle porte sur plusieurs attributs (clé composée) la clause
PRIMARY KEY est placée avant les attributs qui font partie de la
clé
– Ils sont entre parenthèse séparés par des virgules.
– Dans ce cas tous les attributs de la clé composée doivent être
NOT NULL
• Le SGBD crée automatiquement un INDEX sur la clé
primaire
13
 Exemple Clé primaire
• Clé primaire sur un seul attribut:
NumClient Number PRIMARY KEY ou bien

NumClient Number NOT NULL,

……
PRIMARY KEY NumClient
• Clé primaire composée:
NP CHAR(4) NOT NULL,
NF CHAR(2) NOT NULL,
NU CHAR(3) NOT NULL
PRIMARY KEY (NP,NF,NU)

14
 Contraintes d’intégrité référentielles
• L’intégrité référentielle forme le coeur de la cohérence d’une base de
données relationnelle
• C’est une contrainte de clé étrangère
• Elle permet de lier une table « fils »(enfant) à une table « père » (maitre
ou parent) possédant un ou plusieurs attributs en commun
• La clé étrangère de la table « fils » Référence la clé primaire (ou
candidate) de la table « père »
• On dit que table « fils » est la table qui référence et
• La table « père » est la table référencée
• Deux syntaxes possibles:

<Attribut_clé_étrangère> <type> REFERENCES <Table père> (<Attribut_clé_candidate>)

FOREIGN KEY <Attribut_clé_étrangère> REFERENCES <Table père> (<Attribut_clé_candidate>)

• Le SGBD crée un index pour les clés étrangères

15
 Exemple

CREATE TABLE Compagnie (comp VARCHAR(4) PRIMARY KEY…….)

CREATE TABLE Pilote(….. Compa VARCHAR(4) NOT NULL REFERENCES Compagnie(comp)…)

CREATE TABLE Pilote(….. Compa VARCHAR(4) NOT NULL
FOREIGN KEY (Compa) REFERENCES Compagnie(comp)…)
16
 Intégrité référentielle
problèmes résolus automatiquement par le
SGBG
• La cohérence du « fils » vers le « père » :
On ne doit pas pouvoir insérer un enregistrement « fils » (ou
modifier sa clé étrangère) rattaché à un enregistrement « père »
inexistant.
• La cohérence du « père » vers le « fils » :
on ne doit pas pouvoir supprimer un enregistrement « père » si
un enregistrement « fils » y est encore rattaché.

mysql> DELETE FROM Compagnie WHERE comp = 'SING';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`bdsoutou/pilote`, FOREIGN KEY (`compa`) REFERENCES
`compagnie` (`comp`))
17
 Cohérence du père vers le fils
•Il est possible:
üde supprimer les « fils » associés (ON DELETE CASCADE),
ü d’affecter la valeur nulle aux clés étrangères des « fils » associés (ON
DELETE SET NULL) à ce moment là la clé étrangère ne doit pas être NOT
NULL ou
üde répercuter une modification de la clé primaire du père sur la clé
étrangère des fils associés (ON UPDATE CASCADE)

18
CREATE TABLE Compagnie (comp VARCHAR(4) PRIMARY KEY…….)

CREATE TABLE Pilote(….. Compa VARCHAR(4) NOT NULL REFERENCES Compagnie(comp) ON

DELETE CASCADE…)
CREATE TABLE Pilote(….. Compa VARCHAR(4) NOT NULL
FOREIGN KEY (Compa) REFERENCES Compagnie(comp) ON DELETE
CASCADE …)

CREATE TABLE Pilote(….. Compa VARCHAR(4) NOT NULL REFERENCES Compagnie(comp) ON

DELETE SET NULL…)
CREATE TABLE Pilote(….. Compa VARCHAR(4) NOT NULL
FOREIGN KEY (Compa) REFERENCES Compagnie(comp) ON UPDATE
CASCADE19 …)
 Contraintes d’intégrité référentielles
• Il faut ordonner les créations:
– Les tables référencées (père) doivent être créées
avant les tables qui référencent (fils)
• Dans le cas de suppression de table:
– Les tables fils sont supprimer en premier
• Il faut ordonner les MAJ :
– il faut insérer dans la relation Maitre (la relation
référencée) avant d’insérer dans la relation qui la
référence

20
 Expression des contraintes d'intégrité
Contraintes colonnes

• Les contraintes de colonnes appelées aussi contraintes en ligne ne

peuvent porter que sur un seul attribut
• Elles sont placées juste après le type de la colonne
• Les différentes variantes sont :
– Valeur nulle impossible (….NOT NULL),
– Attribut clé primaire (…..PRIMARY KEY)
– Unicité de l'attribut (….UNIQUE ),
– Contrainte référentielle ( ….REFERENCES <table référencée> (clé
candidate)
– Contrainte générale (de contrôle) (….CHECK <condition>);

21
 Expression des contraintes d'intégrité
Contraintes Tables
• Les contraintes Table peuvent porter sur un ou plusieurs attributs.

• Elles sont obligatoires lorsque la contrainte porte sur plusieurs attributs

• Elles sont placée avant l’attribut

• Elle sont exprimées à l'aide des syntaxes suivantes:

– PRIMARY KEY ( <attribut>+) ,UNIQUE (<attribut>+)

– Contrainte référentielle, permettant de spécifier quelles colonnes

référencent celles d'une autre table,
FOREIGN KEY (<Attribut clé étrangère>+) REFERENCES <table référencée>
(<Attribut clé candidate>+),

– Contrainte générale, CHECK <condition>.

22
.
 Nommage des Contraintes
• Il est conseillé de nommer la contrainte, sinon
MySQL s’en charge.

23
Conventions recommandées

24
25
26
 Contrôle des Contraintes d’intégrité
• Un SGBD doit garantir la cohérence des Données lors des
MAJ
• Les Contraintes sont déclarées explicitement et sont
mémorisées dans le catalogue (dictionnaire de Données)
• Les MAJ portant sur des données dépendantes doivent faire
passer la base d’un état cohérent à un autre état cohérent,
• Pour garder la base de données cohérente, le SGBD doit
vérifier que les CI sont satisfaites à chaque fin d’opération de
MAJ,

27
 Méthodes de vérification de
cohérences
• Méthode de détection:
– Après une opération de modification de données
(INSERT, DELETE ou UPDATE) un test appelé post-
test est lancé par le SGBD qui consiste à vérifier si
une CI a été violée,
• Méthode de prévention
– Elle consiste à empêcher les modifications de la base
qui violeraient une quelconque CI. Pour cela un test
avant MAJ appelé pré-test permet de garantir la non
violation d’une CI. Ce test est lancé par le SGBD

28
 Contrôle sur les contraintes simples
• Unicité de la clé
– Tout SGBD gère en général un index (table dans laquelle il y a toutes les clés primaires ) sur les
clés primaires. Un pré-test simple consiste à vérifier que la nouvelle clé ne figure pas dans
l’index. Ce pré-test est effectué lors de l’insertion d’un tuple ou de la modification d’une clé
dans la table.
• Contrainte d’intégrité référentielle (clé étrangère)
– Deux tables, la table référencée et la table dépendante sont mises en jeu par une contrainte
référentielle. 4 types de modification nécessitent des vérifications:
• 1) Insertion dans la table fils: pré-test simple qui consiste à vérifier l’existence de la
valeur de la colonne clé étrangère dans l’index (table dans laquelle il y a toutes les clés)
• 2) MAJ de la colonne clé étrangère dans la table fils. Le pré-test est identique au
précédent
• 3) suppression dans la table maitre: le pré-test consiste à vérifier qu’il n’existe pas de
tuple contenant la valeur de clé à supprimer dans la colonne de clé étrangère. Il y a
plusieures possibilités:
– soit rejeter la MAJ,
– Soit supprimer les tuples de la table dépendantes (ON DELETE CASCADE)

29
Référence

30
FIN Partie 1 LDD

31
Module Base de données Licence 2

Le langage de requêtes SQL:

Définition des données (2)
H.BELLEILI
Université Badji Mokhtar Annaba
Département Informatique
2019-2020

32
 Plan
• ALTER TABLE
• DROP TABLE
• Procédures stockées

33
 Commande ALTER TABLE
• Permet des:
– Modifications structurelles
– Modifications comportementales

34
 Modification structurelles (ADD)
• Ajout de colonne (ADD)
• ALTER TABLE <nom_table> ADD (<colonne>
type_col [DEFAULT valeur [NOT NULL]]

35
Modifications structurelles (CHANGE)
• Permet de renommer une colonne existante.
• Le type doit être reprécisé. (sans le changer)
• La position de la colonne peut aussi être modifiée en même
temps.

36
Modifications structurelles (MODIFY)
• L’option MODIFY de l’instruction ALTER TABLE modifie
le type d’une colonne existante sans la renommer.
• On peut augmenter la taille des types numériques/
chaine
• La diminution de la taille est possible (sauf pour les
colonnes indexées) à condition que la taille des valeurs
déjà existantes entre dans la nouvelle taille.
• Les contraintes en ligne peuvent être aussi modifiées
par cette instruction. Les données présentes devront
toutes vérifier cette nouvelle contrainte.

37
MODIFY (exemple)

38
 Modifications structurelles
valeurs par défaut
L’option ALTER COLUMN de l’instruction ALTER TABLE modifie la
valeur par défaut d’une colonne existante

39
 Modifications structurelles (DROP)
• Permet de supprimer une colonne
• Il n’est pas possible de supprimer avec cette instruction :
• toutes les colonnes d’une table ;
• les colonnes qui sont clés primaires (ou candidates par
UNIQUE) référencées par des clés étrangères.

40
 Modifications comportementales
• Ce sont les mécanismes d’ajout, de suppression,
d’activation et de désactivation de contraintes..
– ADD CONSTRAINT
– Suppression de contrainte

41
ADD CONSTRAINT

42
unique

INDEX NOT NULL

43
 Suppression de CONTRAINTE
• NOT NULL: on supprime une contrainte NOT NULL en la
modifiant (via MODIFY) à NULL
• UNIQUE:
– DROP INDEX <nom de la contrainte UNIQUE>
• FOREIGN KEY:
– DROP FOREIGN KEY <le nom de la contrainte>
• PRIMARY KEY:
– DROP PRIMARY KEY
• Si la colonne clé primaire à supprimer contient des clés
étrangères, il faut d’abord retirer les contraintes de clé étrangère.
• Si la clé primaire à supprimer est référencée par des clés
étrangères d’autres tables, il faut d’abord retirer les contraintes
de clé étrangère de ces autres tables.

44
 Suppression de CONTRAINTE
Contrainte NOT NULL

Contrainte UNIQUE

Contrainte Clé Etrangère

Contrainte clé primaire

45
 Désactivation/réactivation des
contraintes d’intégrité référentielles

46
47
 DROP Table
• Cette commande permet de supprimer une
table de la base de données.
• Les lignes de la table et la définition elle-
même sont détruites.
• L ’espace occupé par la table est libéré.

• DROP Table <nom_table>

48
 Procédures stockées (PS)
• Une procédure stockée est un ensemble nommé d’instructions SQL,
précompilée et stockée sur le serveur. Elle permet d’encapsuler les tâches
répétitives afin de les exécuter efficacement.
• Avantages des procédures
– performance : les PS sont plus performantes car le serveur vérifie la syntaxe
à la création et compile la procédure une fois pour toute. Les exécutions
ultérieures de ces procédures seront plus rapides ;
– réutilisabilité : la PS peut être appelée à n’importe quel moment. Ceci
permet une modularité et encourage la réutilisation du code ;
– simplification : les règles et politiques de fonctionnement encapsulées dans
les PS peuvent être modifiées au même endroit. Tous les clients peuvent
utiliser les mêmes PS afin de garantir la cohérence des accès aux données et
de leurs modifications (sécurité) ;
– Accès réseau : elles contribuent à la réduction du trafic sur le réseau. Au lieu
d’envoyer des centaines d’instructions SQL sur le réseau, les utilisateurs
peuvent effectuer une opération complexe à l’aide d’une seule instruction,
réduisant ainsi le nombre de demandes échangées entre le client et le serveur.
49
 Gestion des procédures stockées
Création:
L’instruction CREATE PROCEDURE permet de créer une procédure stockée

CREATE PROCEDURE nom_procedure

([ [IN/OUT/INOUT] Parametre type_donnees [,..]])
Begin Instructions ; End

Exécution: CALL nom_procedure ( [{value| @variable}]);

Modification :
La seule façon de modifier une procédure existante en MySQL est de la supprimer
puis de la recréer avec les modifications (à la différence des autres SGBD).

Suppression :
L’instruction DROP PROCEDURE permet de supprimer une procédure stockée :

DROP PROCEDURE psInfoDuClient

50
Référence

51
 FIN
SQL LDD