LANGAGE SQL : PRÉSENTATION GÉNÉRALE,
DÉFINITION, INSERTION, MODIFICATION
INTERROGATION DE BD – PARTIE I
Cheikh KACFAH
[email protected]
INTRODUCTION
• Le langage SQL (Structured Query Language) peut être considéré comme le langage
d'accès normalisé aux bases de données. Il est aujourd'hui supporté par la plupart des
SGB
• Caractéristiques en bref: simplicité non procédural
• Le langage SQL propose un langage de requêtes ensembliste et assertionnel. Néanmoins, le
langage SQL ne possède pas la puissance d'un langage de programmation : entrées/sorties,
instructions conditionnelles, boucles et affectations. Pour certains traitements il est donc
nécessaire de coupler le langage SQL avec un langage de programmation plus complet
• De manière synthétique, on peut dire que SQL est un langage relationnel, il manipule donc
des tables (i.e. des relations, c'est-à-dire des ensembles) par l'intermédiaire de requêtes qui
produisent également des tables.
D
CATÉGORIES
D'INSTRUCTIONS
• Les instructions SQL sont regroupées en catégories en fonction de leur utilité et des
entités manipulées. Ces instructions permettent
1. La dé nition des éléments d'une BD (tables, colonnes, clés, index, contraintes…)
2. La manipulation des données (insertion, suppression, modi cation, extraction…)
3. La gestion des droits d'accès aux données (acquisition et révocation des droits)
4. La gestion des transactions
5. L’intégration aux langages de 3e génération
3
fi
,
fi
CATÉGORIES D’INSTRUCTIONS:
LANGAGE DE DÉFINITION DE
DONNÉES (LDD)
• En anglais Data De nition Language (DDL
• Intervient au niveau de la structure de la BD et permet de
• Créer, modi er, supprimer des objet
• Dé nir le domaine des données (nombre, chaîne de caractères, date, booléen…)
et d'ajouter des contraintes de valeur sur les donnée
• Autoriser ou d'interdire l'accès aux données et d'activer ou de désactiver l'audit
pour un utilisateur donn
Les instructions du LDD sont : CREATE, ALTER, DROP, TRUNCATE, AUDIT,
NOAUDIT, RENAME, ANALYZE.
4
fi
fi
fi
é
CATÉGORIES D’INSTRUCTIONS:
LANGAGE DE MANIPULATION DE
DONNÉES (LMD)
• En anglais Data Manipulation Language (DML
• Permet la manipulation des données dans une base de
donnée
• Ajout, Suppression et modi cation de lignes, Lecture du
contenu des tables et leur verrouillag
Les instructions du LMD sont : INSERT, UPDATE, DELETE,
SELECT, EXPLAIN, LOCK TABLE.
5
s
fi
e
CATÉGORIES D’INSTRUCTIONS:
LANGAGE DE PROTECTIONS D'ACCÈS
• En anglais Data Control Language (DCL
• Permet de gérer les droits d'accès aux table
Les instructions du DCL sont : GRANT, REVOKE.
CATÉGORIES D’INSTRUCTIONS:
LANGAGE DE CONTRÔLE DE
TRANSACTION
• En anglais Transaction Control Language (TCL
• Permet de gérer les modi cations faites par le LMD,
c'est-à-dire les caractéristiques des transactions et
la validation et l'annulation des modi cation
Les instructions du TCL sont : COMMIT,
SAVEPOINT, ROLLBACK, etc.
7
fi
fi
s
CATÉGORIES D’INSTRUCTIONS:
SQL INTÉGRÉ
• Le SQL intégré (Embedded SQL) permet d'utiliser SQL dans un langage de
troisième génération (C, Java, Cobol, etc.). Il perme
• La déclaration d'objets ou d'instructions
• L’exécution d'instructions
• La gestion des variables et des curseurs
• Le traitement des erreurs
Les instructions du SQL intégré sont : DECLARE, TYPE, DESCRIBE, VAR,
CONNECT, PREPARE, EXECUTE, OPEN, FETCH, CLOSE, etc.
8
.
AVANT-PROPOS: CONSEILS ET
CONVENTIONS - I
• Conseils: Noms de tables et de colonne
• Pas d’espace dans les nom
• À la place de « date de naissance » préférer
« dateNaissance » ou « date_de_naissance
• Éviter les accents: « prenom » au lieu de « prénom
• Éviter les mots réservés du langage comme nom de tables ou
colonnes ou base de données*
9
s
AVANT-PROPOS: CONSEILS ET
CONVENTIONS - II
• Conseils: Soyez cohérent
• Noms de tables/colonnes au singulier ou au
pluriel
• Pour les objets à plusieurs mots, Underscore ou
Camel Case?
10
?
AVANT-PROPOS: CONSEILS ET
CONVENTIONS - II
• Conventions
• Mots clés SQL complètement en majuscule
• Noms de tables commençant par majuscule
• Noms de bases et colonnes en minuscul
• Dans les dé nitions des requêtes SQL un terme entre
[ ] est facultatif
11
fi
:
LDD: DATABASE
• CREATE DATABASE nom_bd; - - les commandes SQL se terminent par ;
• CREATE DATABASE videotheque CHARACTER SET 'utf8mb4';
• RENAME DATABASE nom_bd TO nouveau_nom_bd [, nom_bd2 TO
nouveau_nom_bd2, …]
• DROP DATABASE nom_bd; - - La bd doit exister sinon erreur
• DROP DATABSE IF EXISTS videotheque
• USE videotheque; /* Sélectionner la BD videotheque */
12
;
LDD: TABLE - I
• Les objets de type table (TABLE) se gèrent principalement par :
• CREATE TABLE … ;
• ALTER TABLE …
• RENAME TABLE nom_table TO nouveau_nom_table [,
nom_table2 TO nouveau_nom_table2, ...]
• DROP TABLE … ;
13
;
LDD: TABLE - II
• L’instruction CREATE TABLE contient une très grande variété d’options et sa
structure contient plusieurs éléments importants.
• Outre le nom de la table, les paramètres importants à spéci er sont :
• le nom et le type de chaque colonne,
• les contraintes liées aux colonnes
CREATE TABLE nom_table (
nom_col1 type_col1 [contrainte_col1]
[, nom_col2 type_col2 [contrainte_col2], …]
[CONSTRAINT nom_contrainte_sup NOM_CONTRAINTE option_contrainte, ...]
[ENGINE=moteur];
14
fi
LDD: TABLE - III > TYPES DE DONNÉES - I
• Important de connaître les usages et particularités de chaque
type a n de choisir le meilleur type possible pour chaque
colonne. Un mauvais type pourrait entraîner
• Un gaspillage de mémoir
• Des problèmes de performanc
• Un comportement contraire à celui attend
• L’impossibilité d'utiliser des fonctionnalités propres à un type
de données
15
fi
e
LDD: TABLE - III > TYPES DE DONNÉES - I
• Nombres entiers
Type Octets Minimum Maximum
⚠ Si vous essayez de stocker
TINYINT 1 -128 127 une valeur en dehors de
l'intervalle permis par le type
SMALLINT 2 -32768 32767 de votre champ, MySQL
stockera la valeur la plus
MEDIUMINT 3 -8388608 8388607 proche.
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807
• L’attribut UNSIGNED peut servir si nos entiers sont tous positif
• On peut préciser le nombre minimum de chiffres à l’af chage. ZEROFILL
servant à remplacer les vides par 0. Exemple INT(5) ZEROFILL
16
fi
s
LDD: TABLE - III > TYPES DE DONNÉES - II
• Nombres décimau
• NUMERIC et DECIMAL sont équivalents et acceptent
deux paramètres : la précision et l’échelle. DECIMAL(5,3) :
5 chiffres signi catifs dont 3 après la virgules. Sont stockés
sous forme de chaînes de caractères donc préci
• FLOAT avec ou sans paramètres (4 octects), REAL et
DOUBLE sans paramètres (8 octets). Sont stockés sous
forme de nombres donc possibles arrondis
17
fi
x
LDD: TABLE - III > TYPES DE DONNÉES - IV
• Types alpha numérique
• CHAR[(n)] : chaîne de caractères de longueur xe n
• VARCHAR[(n)] chaîne de caractères de longueur maximale n
• CHAR et VARCHAR ont une longueur maximale de 255 caractères. Pour stocker
plus, on a le type TEXT et ses dérivé
• TINYTEXT: jusqu’à 28 octet
📝 Pour aller plus loin, voir
• TEXT: jusqu’à 216 octet les chaînes de type binaire
• MEDIUMTEXT: jusqu’à 224 octet
• LONGTEXT: jusqu’à 232 octets
18
s
fi
LDD: TABLE - III > TYPES DE DONNÉES - V
• ENUM
• Pour stocker des valeurs de type chaîne de caractères mais parmi une
shortlist de valeurs autorisées (maximum 65535 valeurs possibles
• ENUM(‘Action’, ‘Horreur’, ‘Comédie’, ‘Policier’) pour stocker
uniquement ces 4 types de lms - - ou la valeur NULL
• Au moment de l’insertion, on peut préciser la valeur (ex:
‘Horreur’) ou son index (ex: 2)
• SET semblable à ENUM mais on peut y stocker une liste, par exemple
‘Action,Horreur’. Pour un SET on a au plus 64 valeurs dé nies
19
fi
fi
)
LDD: TABLE - III > TYPES DE DONNÉES - VI
• Dates et heure
• TIME heure de format : HH:MM:SS. On peut stocker ‘HH:MM:SS’,
‘HHH:MM:SS’, ‘MM:SS’, ‘HHMMSS’, HHMMSS (nombre), etc
• DATE date de format : YYYY-MM-JJ de 1000-01-01 à 9999-12-31.
On peut stocker ‘AAAA-MM-JJ’, ‘AAMMJJ’, ‘AAAA/MM/JJ’,
AAAAMMJJ (nombre), AAMMJJ (nombre), etc
• DATETIME date et heure de format : YYYY-MM-JJ HH:MM:SS de
1000-01-01 00:00:00 à 9999-12-31 23:59:59
• TIMESTAMP nombre de secondes depuis 1970-01-01 00:00:01
20
s
LDD: TABLE - IV > CONTRAINTES - I
• Nous savons déjà écrire une instruction de cette forme. Quid des
contraintes?
CREATE TABLE Pays
idPays CHAR(2)
nomPays VARCHAR(30)
languePays VARCHAR(30)
ENGINE=INNODB;
• MySQL utilise des moteurs de stockage (transactionnels et non transactionnels
• MyISAM (moteur par défaut avant la v5.5): rapide pour insertion et sélection, mais ne gère
ni les clés étrangères ni les transactions; moins gourmand en ressource
• InnoDB (moteur par défaut après la v5.5): gère les transactions et les clés étrangères; plus
gourmand en ressources
21
,
LDD: TABLE - IV > CONTRAINTES - II
• Il est possible d’ajouter 6 types de contraintes différentes
• Valeur nulle permise ou interdite (NULL par défaut) > NULL ou NOT NULL
• Unicité > UNIQUE
• Clé primaire > PRIMARY KEY
• Clé étrangère > FOREIGN KEY
• Validation > CHECK
• Valeur par défaut > DEFAULT
Toutes les contraintes qui seront spéci ées seront automatiquement gérées par le SGBD et
toutes les manipulations violant ces contraintes seront interdites.
22
fi
:
LDD: TABLE - IV > CONTRAINTES - III
• On peut ajouter les contraintes de deux façons
• À la n de la déclaration d’une colonne
• Avantage: écriture plus compact
• À la n de la déclaration de toutes les colonne
• Avantage: on peut nommer une contrainte et y faire
référence plus tar
On privilégie généralement la deuxième approche lorsqu’elle est
possible.
23
fi
fi
d
LDD: TABLE - IV > CONTRAINTES - III
• Contrainte de valeur nulle permise ou interdite (NULL or NOT NULL?
• Cette contrainte doit être mise sur la déclaration de la colonn
• Valeur par défaut (rien n’est indiqué) NUL
L'exemple suivant indique qu’un lm doit avoir un id, un titre, mais que son
résumé et sa langue d’origine sont facultatifs
CREATE TABLE Film
idFilm CHAR(30) NOT NULL
titre VARCHAR(100) NOT NULL
resume TEXT NULL,
langueOriginale VARCHAR(30)
ENGINE=INNODB;
24
(
fi
,
LDD: TABLE - IV > CONTRAINTES - IV
• Contrainte d’unicité - UNIQUE
• Stipule que deux tuples ne peuvent avoir la même valeur pour cette colonne ou cet
ensemble de colonne
• Peut être déclarée sur une ligne ou à la n. Dans ce second cas, permet de mettre la
contrainte sur un ensemble de colonne
L'exemple suivant indique qu’un lm doit avoir un id unique, et que pris ensemble titre et
réalisateur ne peuvent pas être répétés dans la table Film.
CREATE TABLE Film
idFilm CHAR(30) UNIQUE ⚠ UNIQUE et NULL
titre VARCHAR(100) NOT NULL
realisateur VARCHAR(100) NOT NULL,
CONSTRAINT uc_TitreRealisateur UNIQUE (titre, realisateur)
ENGINE=INNODB;
25
s
fi
,
fi
,
LDD: TABLE - IV > CONTRAINTES - V
• Contrainte de clé primaire - PRIMARY KEY
• Peut être déclarée sur une ligne ou à la n. Dans ce second cas, permet de
mettre la contrainte sur un ensemble de colonne
• Implicitement, une clé primaire inclue les contraintes UNIQUE et NOT NULL
L'exemple suivant indique qu’un lm doit avoir un id unique, et que pris ensemble
titre et réalisateur ne peuvent pas être répétés dans la table Film.
CREATE TABLE Film CREATE TABLE Artiste
idFilm CHAR(30) PRIMARY KEY nomA VARCHAR(30) NOT NULL
titre VARCHAR(100) NOT NULL prenomA VARCHAR(30) NOT NULL
realisateur VARCHAR(100) NOT NULL); CONSTRAINT pk_nomPrebom
PRIMARY KEY (nomA, prenomA));
26
(
fi
,
fi
s
LDD: TABLE - IV > CONTRAINTES - VI
• Contrainte de clé étrangère - FOREIGN KEY
• Est déclarée à la n, avec ou sans nom
• Uniquement sur une colonne ou groupe de colonnes indexées
CREATE TABLE Artiste
idArtiste VARCHAR(30) PRIMARY KEY
nomArtiste VARCHAR(100) NOT NULL
paysArtiste CHAR(2)
FOREIGN KEY (paysArtiste) REFERENCES Pays(idPays));
CREATE TABLE Film
titre VARCHAR(100) NOT NULL
nomReal VARCHAR(30) NOT NULL
prenomReal VARCHAR(30) NOT NULL
CONSTRAINT fk_nomReal_PrenomReal_Fil
FOREIGN KEY (nomReal , prenomReal
REFERENCES Artiste(nomArtiste, prenomArtiste));
27
fi
(
LDD: TABLE - IV > CONTRAINTES - IV
• Contrainte de clé étrangère (suite
• Que faire en cas de suppression ou mise à jour de l’objet référencé
• Syntaxe: ON { UPDATE | DELETE } { RESTRICT |NO ACTION| CASCADE | SET NULL }
• En cas de suppression/mise à jour d’un objet qui est référencé par des tuples, l’option
• RESTRICT ou NO ACTION : Fera avorter la suppression ou la mise à jour /* Défaut */
• CASCADE: Supprimera toutes les lignes de toutes les tables utilisant l’objet référencé
• SET NULL: Mettra à NULL toutes les références vers l’objet référencé
CREATE TABLE Dvd
idDvd TINYINT AUTO_INCREMENT PRIMARY KEY
idFilm CHAR(2)
FOREIGN KEY (idFilm) REFERENCES Film(idFilm)
ON DELETE CASCADE ON UPDATE CASCADE );
28
,
LDD: TABLE - IV > CONTRAINTES - V
• Contrainte de validation - CHECK
• Permet de véri er qu’une colonne respecte une condition donné
• Peut être déclarée sur une ligne ou à la n
CREATE TABLE Personne
nom VARCHAR(30) NOT NULL
sexe CHAR(1) NOT NULL CHECK (sexe in ('F', 'M', 'A'))
age SMALLINT
CONSTRAINT cc_age CHECK (age>=15 AND age<40
);
29
,
fi
(
fi
,
LDD: TABLE - IV > CONTRAINTES - VI
• Contrainte* de valeur par défaut - DEFAULT
• S’applique à une colonne donnée et est déclarée à la n de celle-c
• Si aucune valeur n’est spéci ée pour cette colonne lors de l’insertion, la
valeur dé nie par défaut sera insérée
CREATE TABLE Personne
nom VARCHAR(30) NOT NULL
nationalite CHAR(2) DEFAULT ‘CM’
sexe CHAR(1) NOT NULL CHECK (sexe in ('F', 'M', 'A'))
age SMALLINT
CONSTRAINT cc_age CHECK (age>=15 AND age<40)
CONSTRAINT fk_nationalite_Personne FOREIGN KEY (nationalite) REFERENCES Pays(idPays
);
30
fi
,
fi
,
fi
i
LDD: TABLE - V > MODIFICATION/
SUPPRESSION
• Il est possible de modi er (la structure d’) une table (colonne, contrainte, index, etc.)
Syntaxe:
ALTER TABLE nom_table
| ADD COLUMN nom_col type_col [contrainte_col] [, ...]
| ADD CONSTRAINT [nom_contrainte] {PRIMARY KEY | UNIQUE} paramètres [, ...]
| CHANGE [COLUMN] nom_col nouveau_nom_col type_col [contrainte_col] [, ...]
| DROP [COLUMN] nom_col [, ...]
| DROP PRIMARY KEY
| DROP FOREIGN KEY nom_contrainte [, ...]
| RENAME [TO | AS] nouv_nom_table ... ;
| DROP TABLE nom_table
CREATE TABLE Personne
nom VARCHAR(30) NOT NULL
sexe CHAR(1) NOT NULL CHECK (sexe in ('F', 'M', 'A'))
age SMALLINT )
ALTER TABLE Personne ADD COLUMN ville VARCHAR(30) DEFAULT ‘Yaoundé’
ALTER TABLE Personne DROP COLUMN age
DROP TABLE Personne
31
;
fi
,
RÉFÉRENCES
• Chantal Gribaumont, Administrez vos bases de données avec MySQL.
https://openclassrooms.com/
• Stéphane Crozat, Algèbre relationnelle - librecours.net, Costech UT
• Laurent Audibert, Bases de données : de la modélisation au
SQL, Ellipses, 200
• M. Chériet et J-C Demers, GPA-775 Bases de données, Université du
Quebec - ETS, 201
• MySQL Documentation, https://dev.mysql.com/doc/
9
