SQL (Structured Query

Language)

1
 Introduction
Pour interroger une base de données relationnelle, on dispose de
deux types de langages :

• les langages procéduraux comme le langage algébrique vu

précédemment.
Ce langage fondé sur une algèbre permet de décrire
comment obtenir une relation correspondant au résultat
d'une requête ;

• les langages déclaratifs, qui permettent d'exprimer la

requête sous forme d'une assertion sans expliquer comment
la trouver.
SQL est le langage déclaratif le plus utilise
actuellement.

2
 Le SQL ou algèbre relationnelle ?

– Un opérateur en plus : celui de division

Intérêt du SQL par rapport à l’algèbre relationnel

– Le SQL permet de créer les BD et de tester les

calculs.
– Le SQL est un langage normalisé (ANSI - ISO) et
implanté dans tous les SGBD-R
– Le formalisme du SQL est très proche de celui de
l’algèbre relationnel.
– L’opérateur de division est un opérateur complexe et
rarement utilisé et équivalent à la combinaison
d’autres opérateurs du SQL.
 Le langage SQL
 c'est un langage de définition et de manipulation
de bases de données relationnelles ;
 c'est un langage standardisé (norme ANSI) ;
 Il fonctionne selon deux modes :
 un mode interprété, i.e. dans lequel SQL est vu comme
un langage a part entière. Nous présenterons SQL
principalement selon ce mode ;
 un mode intégré, dans lequel SQL est intégré à un autre
langage de programmation (C, Java, Cobol etc.).
On peut distinguer dans SQL trois sous-langages
particuliers:
• le langage de manipulation de données (DML
pour Data Manipulation Language),

4
 Le langage SQL(suite)
qui permet d'obtenir des informations et de
les mettre à jour ;
• le langage de description de données
(DDL pour Data Description Language), qui
permet de créer des relations, de modifier
leur schéma etc.
• le langage de contrôle des données (DCL
pour Data Control Language) qui permet de
restreindre l'accès des données.

5
 Le langage SQL(suite)
Les principaux mots-clés de ces trois langages
sont les suivants :

6
SQL : CREATION DE LA BD :
DDL

7
 Présentation
Les trois opérations fondamentales de gestion d’une base de données
sont:
• La création
• La modification
• La suppression
Ces opérations correspondent au sigle : CMS On parle aussi de « SIUD
» pour Select, Insert, Update et Delete ou aussi « CRUD » pour
Create, Replace, Update, Delete.
La CMS s’applique :
 A la base de données elle-même (opérations d‟administration de
base)
 Aux tables : avec le Data Definition Language (DDL)
 Aux tuples : avec le Data Manipulation Language (DML)
 Aux utilisateurs : avec le Data Control Language (DCL)
 A tous les autres objets de la BD : les procédures, les triggers, les
vues, etc.

8
Gestion des bases de données

9
 Présentation
La manipulation de la BD ne relève pas de l’algèbre
relationnelle mais d’opérations d’administration qui
sont spécifiques à chaque SGBD.
Le principe est toutefois de pouvoir créer, modifier ou
supprimer une BD (CMS) en précisant au minimum son
nom, et ensuite de pouvoir la consulter, c’est-à-dire
consulter les objets qu’elle contient : les tables, les
utilisateurs, etc.
On peut ensuite paramétrer de nombreux éléments de la
BD : la taille de l’espace physique qui lui sera alloué,
son propriétaire, etc. Ces paramétrages sont spécifiques
à chaque SGBD.
La présentation ci-dessous concerne uniquement la
gestion des BD MySQL

10
Consultation des BD du SGBD
La BD est un ensemble de tables. C’est une sorte de dossier contenant des
tables.
Le SGBD permet d’accéder à la liste de toutes les BD qu’il contient : Show
databases;

Les bases de données pré-installées

 3 bases de données pré-installées :
• information_schema
• mysql
• test
 Dictionnaire des données
information_schema et mysql sont des bases systèmes qui correspondent à ce
qu’on appelle le dictionnaire des données.
Ces deux bases contiennent des métadonnées : données sur les données.
 La base de test
MySQL installe une base de test vide. On peut l’utiliser pour des tests. Ou peut
aussi la supprimer.

11
 Consultation d’une BD
La création d’une base de données consiste seulement à
créer une sorte de dossier qui contiendra les futures
tables de la BD.

Syntaxe: Create database NomBD ;

NomBD est le nom de la BD qu’on veut créer.

On peut aussi vérifier la non-existence de la BD avant de
la créer :

Syntaxe: Create database if not exists NomBD ;

cela évite un signal d’erreur si la BD existe.

12
 Destruction d’une BD
La destruction d’une base de données consiste à
détruire le dossier et toutes les tables de la BD.
C’est à manier avec prudence :

Syntaxe: Drop database NomBD ;

On peut aussi vérifier l’existence de la BD avant de

la détruire :

Syntaxe: Drop database if exists NomBD ;

Cela évite un signal d’erreur si la BD n’existe pas.

13
 Utilisation d’une BD
Pour pouvoir manipuler les tables d’une BD, il faut d’abord signaler qu’on l’utilise :
Syntaxe: Use NomBD ;
Remarque :
On peut accéder à toutes les tables en préfixant le nom de leur BD : [Link]
 Connaître la BD actuellement utilisée
Pour savoir dans quelle BD on se trouve :
Syntaxe: Select database() ;
 Consultation des tables d’une BD
On peut afficher la liste des tables contenues dans une BD :
syntaxe: Show tables from NomBD ;
Ou, si la BD est utilisée :
Syntaxe: Show tables ;
 Consultation du code de création des tables enregistrées par le SGBD
Principe
Quand on envoie une commande de création de table, le SGBD enregistre la structure de la
table en mémoire.

14
 Utilisation d’une BD
Il enregistre aussi le code de création de la table. On peut afficher ce
code.
Syntaxe MySQL: SHOW CREATE TABLE NomTable ;

 Consultation des attributs d’une table

On peut afficher la liste des attributs d’une table :
Syntaxe: Desc NomTable ;

 Consultation des tuples d’une table

Syntaxe SQL: SELECT liste d’attributs FROM NomTable;

Exemples : tous les attributs

SELECT * FROM employes ;
Exemples : certains les attributs
SELECT NE, nom FROM employes ;

15
Gestion des tables: DDL

16
 CMS des tables : Le DDL : Data
Definition Language
Une fois le MR réalisé, il reste à créer la base de
données. Créer d ’abord les tables, puis les
tuples.
• CREATE TABLE
• ALTER TABLE
• DROP TABLE

17
 Création d’une table
La commande a la syntaxe suivante :
CREATE table NomTable (
attribut_1 type [contrainte d’intégrité],
attribut_2 type [contrainte d’intégrité],
…,
attribut_n type [contrainte d’intégrité] ,
[contrainte d’intégrité]
);

Pour chaque attribut, on précise :

• Son type
• Une contrainte. La contrainte est facultative.
La table est une coquille vide : c'est une structure. Elle permettra ensuite de créer des tuples.

Création d'une table à partir d’un Select

On peut créer une table et la remplir à partir d’un select (possibilité MySQL) :
Syntaxe: Create table nomTable as select … ;
Cette table contiendra les attributs et les tuples du select. Elle ne contiendra aucunes contraintes
d’intégrité. Tous les attributs sont à NULL par défaut. Pour y ajouter des contraintes
d’intégrité, il faudra faire des ALTER TABLE.

18
 Modification des tables
Ajouter un attribut à la table :
ALTER TABLE NomTable ADD (
attribut_1 type [contrainte],
attribut_2 type [contrainte],
…,
attribut_n type [contrainte]
);

Modifier le type d’un attribut de la table

ALTER TABLE NomTable MODIFY (
attribut_1 type [contrainte],
attribut_2 type [contrainte],
…,
attribut_n type [contrainte]
);

19
 Suppression des tables
Suppression d'une table
Syntaxe: DROP TABLE NomTable ;

Version sans vérification des erreurs :

Syntaxe: DROP TABLE IF EXISTS NomTable CASCADE;

Le IF EXISTS permet de ne faire le DROP que si la table existe. Le CASCADE permet de

supprimer la table indépendamment des contraintes d’intégrité.

Suppression d’une colonne

Syntaxe: ALTER TABLE NomTable DROP attribut ;

Attention
La modification et la destruction des tables et des colonnes doivent être manipulées avec
prudence : une table peut contenir des milliers de données. Il ne faut pas les supprimer ou
modifier une table sans précaution.
En cas d'erreur de manipulation, la commande ROLLBACK permet de revenir à l'état
antérieur, tant qu’on n’a pas fait de commande COMMIT.

20
Les types

21
 Présentation globale des
différents types
Les types sont plus ou moins standards selon les SGBD. Globalement, on va trouver les catégories de types suivantes
(les détails sont à chercher pour chaque SGBD) :
 Les numériques
Ce sont les entiers et les flottants : number (précision, virgule), numeric (précision, virgule), int, real, float, etc.
 Les chaînes de caractères
 char(n) : taille fixe, varchar(n) : taille variable, varchar2(n) : ORACLE.
 Booléen
boolean, bool
 Date
date : de –beaucoup avant JC à + beaucoup après JC !
time : jusqu’à la microseconde
timestamp : date et heure, jusqu’à la microseconde.
 LOB
Long Object : CLOB (Char LOB), BLOB (Binary LOB) : pour stocker des mégas ou des gigas
de caractères ou d’octets.
 BFILE, FILESTREAM
Types pour gérer des données binaires stockées dans un fichier externe à la base.
BFILE : ORACLE. FILESTREAM : SQL Server.
 Autres types
Selon les SGBD, on trouvera différents autres types pour gérer des données particulières.
Notons particulièrement :
Un type XML (SQL Server) : pour gérer des données au format XML. Ce type est associé à des opérateurs spécifiques
pour pouvoir interroger les données XML à partir de la BD.
Des types géographiques (SQL Server) : pour gérer des données cartographiques et géographiques.

22
 Les types par SGBD
• ORACLE
[Link]
server.111/b28318/[Link]
• MySQL
[Link]
[Link]
• PostgreSQL
[Link]
[Link]
• SQL Server
[Link]
fr/library/ms172424%28SQL.90%[Link]

23
Gestion des contraintes: DDL

24
 Présentation
Les contraintes d’intégrité définissent des
règles qui seront vérifiées en permanence
par le SGBD.
Les contraintes d'intégrité vont permettre de :
 définir les clés primaires et les clés
étrangères.
 préciser les valeurs possibles pour les
attributs.

25
Les principales contraintes
 PRIMARY KEY : permet de définir les clés primaires. Cette contrainte garantit le
fait que la valeur est différente de NULL et qu’elle est unique dans la table.

 FOREIGN KEY : permet de définir les clés étrangères. Cette contrainte fait
référence à une clé primaire dans une autre table. C’est cette contrainte qu’on
appelle « contrainte d’intégrité référentielle ». Cette contrainte ne peut être créée
qu’à condition qu’elle fasse référence à une clé primaire déjà créée dans la BD.

 NOT NULL : impose le fait que la valeur de l’attribut doit être renseignée.

 UNIQUE : impose le fait que chaque tuple de la table doit, pour l’attribut
concerné, avoir une valeur différente de celle des autres ou NULL.

 CHECK : permet de définir un ensemble de valeurs possible pour l’attribut. Cette

contrainte garantit le fait que la valeur de l’attribut appartiendra à cet ensemble.

 DEFAULT : permet de proposer une valeur par défaut. Si rien n’est saisi à la
création du tuple, c’est la valeur par défaut qui sera fournie. A noter qu’on peut
saisir NULL s’il y a une valeur par défaut.

26
 FOREIGN KEY : contrainte
d’intégrité référentielle
La contrainte d’intégrité référentielle a plusieurs conséquences
pratiques :
 Création d’une table avec une clé étrangère
Il faut que la table à laquelle on fait référence ait déjà été créée.
Il y a donc un ordre logique de création des tables.
 Création d’un tuple avec clé étrangère
Il faut que le tuple auquel on fait référence ait déjà été créé. Il y
a donc un ordre logique de création des tuples.
 Suppression d’une table (1) dont la clé primaire est
référencée par d’autres tables (2)
On doit d’abord commencer par supprimer les tables (2) qui font
référence à la table (1).
 Suppression d’un tuple (1) dont la clé primaire est
référencée par d’autres tuples (2)

27
 FOREIGN KEY : contrainte
d’intégrité référentielle
Plusieurs cas peuvent se présenter :
 On interdit la destruction du tuple (1).
Il faudra commencer par détruire les tuples (2)
pour pouvoir supprimer le tuple (1). C’est la
situation par défaut.
 DELETE CASCADE
Le système supprime le tuple (1) et les tuples (2),
et donc par la même occasion tous les tuples
référencés par les tuples (2) : DELETE CASCADE.
 DELETE SET NULL
Le système supprime le tuple (1) et modifie les
tuples (2) en donnant la valeur NULL à leur clé
étrangère : DELETE SET NULL

28
 FOREIGN KEY : contrainte
d’intégrité référentielle
 Modification de la clé primaire d’un tuple (1)
référencée par d’autres tuples (2)
Plusieurs cas peuvent se présenter :
 On interdit la modification du tuple (1)
Il faudra commencer par supprimer toutes les références à
ce tuple pour pouvoir le modifier. C’est la situation par
défaut.
 UPDATE CASCADE
Le système modifie le tuple (1) et les tuples (2) en donnant la
nouvelle valeur de la clé primaire du tuple (1) aux clés
étrangères correspondantes dans les tuples (2) : UPDATE
CASCADE.
 UPDATE SET NULL
Le système modifie le tuple (1) et les tuples (2) en donnant la
valeur NULL aux clés étrangères correspondant des tuples
(2) correspondant au tuple (1) : UPDATE SET NULL.

29
 création de contraintes
Les contraintes peuvent se déclarer au choix :
– Directement à la suite de la déclaration de l’attribut
– Après avoir déclaré tous les attributs. Dans ce cas, la contrainte devra faire référence à l’attribut concerné.
 Création de la table des départements
CREATE table dept (
deptno number (2) PRIMARY KEY,
dname char(20),
loc char(20)
);
À noter qu’il faut créer la table des départements avant celle des employés car la table des
employés fait référence à la table des départements.
 Création de la table des employés
CREATE TABLE EMP (
empno number (4) PRIMARY KEY,
numeroSecu number (13) UNIQUE,
ename varchar2 (10) NOT NULL,
job char (9) CHECK (job IN ('SALESMAN', 'MANAGER',
'PRESIDENT')) ,
hiredate date,
sal number (7,2),
comm number (7,2),
deptno number (2), FOREIGN KEY (deptno) References DEPT

30
 création de contraintes
(deptno),
mgr number(4), FOREIGN KEY (mgr) References emp (empno)
);
On peut aussi déclarer les contraintes après la déclaration des attributs, auquel cas il faut faire
référence à l’’attribut quand ce n’est pas déjà fait.
CREATE TABLE EMP (
empno number (4),
numeroSecu number (13),
ename varchar2 (10) NOT NULL,
job varchar (9),
hiredate date,
sal number (7,2),
comm number (7,2),
deptno number (2),
mgr number(4),
PRIMARY KEY (empno),
UNIQUE (numeroSecu),
FOREIGN KEY (deptno) References DEPT (deptno),
FOREIGN KEY (mgr) References EMP (empno),
CHECK (job IN ('SALESMAN', 'MANAGER', 'PRESIDENT'))
);

31
 création de contraintes
 Cas des clés constituées de plusieurs attributs
PRIMARY KEY (attribut1, attribut2)
 Différents cas de CHECK
CHECK (condition de recherche)
La syntaxe des conditions de recherche est la même que
celle qu’on trouvera au moment de L’interrogation de la
base de données.
Exemples :
CHECK (attribut < 100),
CHECK (attribut <100 and attribut >50)
CHECK (attribut between 50 and 100)
CHECK (attribut not between 50 and 100)
CHECK (attribut1 > attribut2)

32
 Nommer les contraintes
• On peut nommer les contraintes, ce qui permettra ensuite de
désactiver et de réactiver les contraintes en y faisant référence par
leur nom.
• Pour cela, il suffit d’ajouter « CONSTRAINT nomContrainte » devant
la déclaration de la contrainte.
Exemples :
CREATE TABLE EMP (
empno number (4) CONSTRAINT cleEmp PRIMARY KEY,
deptno number (2),
sal number (7,2),
CONSTRAINT deptnoEmp FOREIGN KEY (deptno) References DEPT
(deptno));

33
 Modification des contraintes
d'intégrité
 Ajouter une contrainte: ALTER TABLE NomTable ADD CONSTRAINT [contrainte] ;
 Supprimer la clé primaire: ALTER TABLE NomTable DROP primary key ;
 Suppression d’une contrainte nommée: ALTER TABLE NomTable DROP CONSTRAINT
nom_de_contrainte;
 Désactiver une contrainte nommée: ALTER TABLE NomTable DISABLE CONSTRAINT
nom_de_contrainte;
 Activer une contrainte nommée: ALTER TABLE NomTable ENABLE CONSTRAINT
nom_de_contrainte;
 Autres possibilités
Selon les SGBD, d’autres possibilités de définition de contraintes d’intégrité seront possibles.
Elles sont à analyser au cas par cas en fonction du SGBD sur lequel on travaille.
MySQL : Date de création et date de modification
 Date de création automatique :
Create table test ( …
dateCrea timestamp default now( ),
…)
L’attribut sera affecté automatiquement à « now » à la création d’un tuple.
 Date de modification automatique :
Create table test ( …
dateModif timestamp default now( ) on update
current_timestamp default now( ),
…)
L’attribut sera affecté automatiquement à « now » à la création d’un tuple puis remis à now à
chaque update.
A noter que MySQL ne permet pas l’usage des deux possibilités dans une même table.
34
 Auto-Incrément
 Présentation
Les SGBD-R permettent de gérer des auto-incréments qui sont utiles pour une numérotation
automatique des clés primaires.
Le principe d’un auto-incrément est que la nouvelle valeur d’un attribut est égale à la dernière
valeur entrée + 1.
La syntaxe des auto-incréments est très différente selon les SGBD.
Dans MySQL, il suffit d’ajouter la contrainte « autoincrement » à un attribut.
Sous ORACLE, il faut créer un objet « Sequence » qu’on utilise ensuite pour gérer
l’autoincrémentation d’un attribut.
Sous SQL-Server, il faut utiliser la notion d’IDENTITY

 Insertion d’un tuple avec un auto-incrément

Quand on ajoute un tuple avec un auto-incrément, il n’est pas nécessaire de préciser la valeur
pour l’attribut.
La syntaxe correspondante est variable selon les SGBD-R

 Dernière « id » inséré : last insert id

La notion de « last insert id » est associée à celle d’auto-incrément.
En effet, une fois un tuple inséré avec un auto-incrément, si on veut insérer un autre tuple faisant
référence avec une clé étrangère à cet auto-incrément, il faut pouvoir récupérer la valeur
insérée avec l’auto-incrément.
Les SGBD-R proposent donc une fonction permettant de récupérer la valeur du dernier « id » inséré
(last_insertid() MySQL, nextval ORACLE, @@IDENTITY SQL Server. La syntaxe correspondante
est variable selon les SGBD-R.

35
 Auto-Incrément
 Création de l’auto-incrément :
CREATE TABLE nomTable (
attribut integer auto_increment,
…
 Insertion directe :
INSERT INTO departements (nom, ville) values ('VENTES', 'PARIS');
Dans ce cas, il faut préciser la liste des attributs qu’on veut affecter sans préciser le nom de l’attribut clé,
et ne pas mettre de valeur pour l’attribut clé.
Au minimum, on écrira donc :
INSERT INTO departements ( ) values ( );

 En passant une valeur NULL

En passant une valeur NULL ou ‘’, on fait jouer l’auto-incrément :INSERT INTO departements values
(NULL, 'VENTES', 'PARIS');
Ou bien INSERT INTO departements values ('', 'VENTES', 'PARIS');

 Gestion des clés étrangères : la fonction last_insert_id()

La fonction last_insert_id() permet de récupérer le dernier numéro de clé primaire donnée. C’est utile pour
faire un lien sur une clé étrangère :
INSERT INTO employes
values (NULL, 'DURAND', 'ANALYST', '1981-11-17', 2000, NULL, last_insert_id(),7839);
Cette instruction permet de créer l‟employé DURAND et de l‟affecter dans le département qu‟on vient de
créer précédemment. A noter que la fonction «last_insert_id() » est gérée au niveau de chaque client.

36
 Moteurs MyIsam et InnoDB
 Notion de « moteur »
MySQL offre plusieurs « moteurs » pour gérer les tables. Les deux principaux sont :
MyISAM et InnoDB.
Un moteur peut être vu comme un SGBD particulier pour gérer les tables.
 Moteur par défaut : « storage_engine » et « table_type »
Show variable like ‘%engine%’; Ou bien Show variable like ‘%type%’;
Les variables « storage_engine » et « table_type » sont équivalentes.
 Moteur d’une table
 Par défaut
Quand on fait un CREATE TABLE, le moteur associé est celui par défaut
 Explicitement
A la création de la table, on peut préciser le moteur :
CREATE TABLE NomTable (
…
) ENGINE MyISAM;
On peut écrire ENGINE ou TYPE.
 Modification du moteur par défaut
 Par modification du fichier de configuration
On peut préciser dans le fichier « [Link] » :
[mysqld]

37
 Moteurs MyIsam et InnoDB
storage_engine = „MyISAM‟ ;
Il faut alors relancer le serveur pour que la modification soit prise en
compte.
 Par modification dynamique des variables du serveur
Set @@table_type =’myisam’; Est équivalent à : Set @@local.table_type =’myisam’;
Est équivalent à :
Set @@session.table_type =’myisam’; Est équivalent à : Set local table_type
=’myisam’;
La modification est prise en compte par le client qui a lancé la commande.
En remplaçant « local » par « global » : Set global table_type =’myisam’; Est
équivalent à:
Set @@global.table_type =’myisam’;
La modification sera prise en compte par tout nouveau client : elle n’est donc pas
prise en compte par le client qui a envoyé la commande.
 Choix d’un moteur
Le moteur MyISAM est le moteur d’origine. Il est très permissif : il ne
gère pas les contraintes d’intégrité référentielle.
Le moteur InnoDB gère l’intégrité référentielle et les transactions. On
n’utilisera toujours ce moteur dans ce cours.

38