M1104 : Introduction aux bases de données - Deptinfo

M1104 : Introduction aux bases de
données
Dut Alternance
2015-2016
Faten ATIGUI, Maître de Conférences
[email protected]
1
Plan du cours
 Chapitre 1 : Introduction aux bases de données
 Chapitre 2 : Le modèle Entité/Association
 Chapitre 3 : L’algèbre relationnelle
 Chapitre 4 : Le langage SQL
2
Chapitre 4
Le langage SQL
3
Standard SQL
 SQL : Structured Query Language
 SQL est un standard ANSI/ISO depuis 1986
 Version SQL-92 ou SQL2 (standard bien supporté)
 Version SQL-99 ou SQL3 (dernier standard, peu supporté)
 Dans ce cours : commandes SQL2 pour
a) Langage de Définition de Données
– Définition du schéma
– Contraintes d’intégrité, contraintes d’intégrité référentielle
– Mise à jour du schéma
b) Langage de Manipulation de données
– Définition de données
– Interrogation de données
– Mise à jour de données
Définition de données
1. Création d'un schéma de relation

CREATE TABLE
2. Suppression d'un schéma de relation

DROP TABLE
3. Modification d'un schéma de relation

Alter TABLE
Description d’un attribut
nomAttribut
type [DEFAULT expression]
CHAR(n)
VARCHAR(n)
NUMBER(n)
NUMBER(n,m)
DATE
TIME
…..
[Contrainte]
constante
NOT NULL
SYSDATE
UNIQUE
LN(valeur)
PRIMARY KEY
…….
REFERENCES nomtable
…….
Création de tables
 Création d’une table
CREATE TABLE nomTable
( descriptionAttribut1 ,
descriptionAttribut2 ,
……
descriptionAttributn ,
[description contraintes portant sur +ieurs
+ieurs
attributs]
);
Contrainte portant sur plusieurs attributs d’une relation
UNIQUE (attribut1, attribut2, …)
Liste des attributs constituants la clé candidate
PRIMARY KEY (attribut1, attribut2, …)
Liste des attributs constituant la clé primaire
Les attributs
constituant une
clé primaire (ou
candidate) sont
forcement non
nuls
FOREIGN KEY (attribut1, attribut2, …) REFERENCES nomtable (attribut1’, attribut2’, …)
Liste des attributs
constituant la clé
étrangère
Liste des attributs
constituant la référence (clé
primaire ou candidate)
Si pas de liste , la référence
est la clé primaire
Contrainte portant sur plusieurs attributs d’une relation
CONSTRAINT NomContrainte UNIQUE (attribut1, attribut2, …)
CONSTRAINT NomContrainte PRIMARY KEY (attribut1, attribut2, …)
CONSTRAINT NomContrainte FOREIGN KEY (attribut1, attribut2, …)
REFERENCES nomtable(attribut1’, attribut2’, …)
Définition de données
Création d'un schéma de relation
Créer les schémas des tables pièce, fournisseur,vente
pièce (nop, nomp, prix)
fournisseur (nof,nomf,ville)
vente (nop,nof )
CREATE TABLE pièce
(nop integer,
nomp varchar(50) NOT NULL,
prix float,
PRIMARY KEY (nop))
CREATE TABLE fournisseur
(nof integer,
nomf varchar(80) NOT NULL,
ville varchar(60),
PRIMARY KEY (nof))
Définition de données
Création d'un schéma de relation
Créer les schémas des tables pièce, fournisseur, vente
pièce (nop, nomp, prix)
fournisseur (nof,nomf,ville)
vente (nop,nof )
CREATE TABLE pièce
(nop integer,
nomp varchar(50) NOT NULL,
prix float,
Constraint Macléprimaire PRIMARY KEY (nop))
CREATE TABLE fournisseur
(nof integer,
nomf varchar(80) NOT NULL,
ville varchar(60),
Constraint Macléprimaire PRIMARY KEY (nof))
Définition de données
Création d'un schéma de relation
CREATE TABLE
(nop integer
nof integer
PRIMARY KEY
vente
REFERENCES pièce(nop),
REFERENCES fournisseur(nof),
(nop,nof))
– L'attribut nop dans la table vente fait référence à l'attribut nop de la
table pièce
– L'attribut nof dans la table vente fait référence à l'attribut nof de la
table fournisseur
Définition de données
Types de données
Type de données d'un attribut = domaine des valeurs
 NUMBER (n,m): nombre de n chiffres avant la
virgule et m chiffres après la virgule
 INTEGER : nombres entiers
 FLOAT : nombres réels
 CHAR(n) : chaînes de caractères de longueur fixe n
 VARCHAR(n) : chaînes de caractères de longueur
variable et de longueur maximale n
 DATE : date selon un format interne au système
 TIME : combine la date avec l’heure
13
Définition de données
Suppression [d'un schéma] de relation
DROP TABLE
nom-relation
 suppression du schéma de la relation
 suppression des tuples de la relation
Supprimer le schéma de la table vente
DROP TABLE vente;

Suppression de tous les tuples de la table vente

Suppression du schéma de la table vente
Contraintes d’intégrité
CHECK, CONSTRAINT
Contrainte nommée :
Avantage ? Manipulation direct de la contrainte lors de la suppression par exemple
Deux syntaxes permis :
CHECK (prix>=0)
CONSTRAINT prix_positif CHECK (prix>=0)
Exemple
CREATE TABLE pièce
(nop integer,
nomp varchar(50) NOT NULL,
prix float,
PRIMARY KEY (nop),
CHECK (prix>=0);)
CREATE TABLE pièce
(nop integer,
nomp varchar(50) NOT NULL,
prix float,
PRIMARY KEY (nop),
CONSTRAINT chk_prix CHECK (prix>=0);)
Contraintes d’intégrité
FOREIGN KEY
Ecriture alternative (recommandé) :
nop FOREIGN KEY fk_piece REFERENCES pièce(nop)
Exemple
CREATE TABLE vente
(nop integer,
nof integer,
nop FOREIGN KEY fk_piece REFERENCES pièce(nop),
nof FOREIGN KEY fk_fournisseur REFERENCES fournisseur(nof),
PRIMARY KEY (nop,nof))
Contraintes d’intégrité référentielles
FOREIGN KEY
 ON DELETE NO ACTION / ON UPDATE NO ACTION : aucun traitement
particulier n'est entrepris en cas de mise à jour ou suppression
d'informations référencées. Autrement dit, il y a blocage du traitement car le
lien d'intégrité ne doit pas être brisé. Même effets que RESTRICT,.
 ON DELETE CASCADE / ON UPDATE CASCADE : en cas de suppression
d'un élément, les éléments qui lui font référence sont eux aussi supprimés.
En cas de modification de la valeur de la clé, les valeurs des clés étrangères
qui lui font référence sont elles aussi modifiées afin de maintenir l'intégrité.
Par exemple en cas de suppression d'un client, les factures et les
commandes de ce client sont elles aussi supprimées.
Contraintes d’intégrité référentielles
FOREIGN KEY
 ON DELETE SET NULL / ON UPDATE SET NULL : en cas de suppression
d'un élément, les valeurs des clés étrangères qui lui font référence sont
modifiées en « NULL », de même en cas de modification. Le lien d'intégrité
est alors brisé.
Ceci n’est pas possible, si on a défini une contrainte « NOT NULL » sur
l’attribut clé étrangère.
 ON DELETE SET DEFAULT / ON UPDATE SET DEFAULT : en cas de
suppression comme en cas de mise à jour de la clé référencée, la référence
passe à la valeur par défaut définie lors de la création de la table. Ce mode
permet l'insertion d'un client générique, possédant un identifiant particulier
(par exemple 0 ou -1) afin de ne jamais briser le lien d'intégrité
référentielle. Bien entendu on veillera ensuite à rectifier la vrai valeur du lien
au moment opportun si besoin.
 ON DELETE RESTRICT / ON UPDATE RESTRICT : mêmes effets que
NO ACTION.
Contraintes d’intégrité référentielles
FOREIGN KEY
 Exemple
CREATE TABLE vente
(nop integer,
nof integer,
nop FOREIGN KEY fk_piece REFERENCES pièce(nop) ON UPDATE CASCADE,
PRIMARY KEY (nop,nof))
Remarque
 Toute contrainte est vérifiée à l’insertion ou à
la modification d’un tuple
Modification de schéma
ALTER TABLE
 L'ordre ALTER sur une table permet de :
–
–
–
–
–
–
–
supprimer une colonne
supprimer une contrainte
ajouter une colonne
ajouter une contrainte
ajouter une contrainte de ligne DEFAULT
modifier le type de données accepté par une colonne
…
Modification de schéma
ALTER TABLE
 Syntaxe de l'ordre ALTER sur table :
ALTER TABLE nom_table {
ADD definition_colonne |
ADD CONSTRAINT nom_contrainte FOREIGN KEY (nom_colonne)
REFRENCES nom_table_référencée(colonne_référencée)|
Modify nom_colonne définition_colonne (type, null, etc.) |
DROP nom_colonne [ CASCADE | RESTRICT ] |
DROP CONSTRAINT nom_contrainte [ CASCADE | RESTRICT ]|
Modification de schéma
ALTER TABLE
 Pour ajouter une nouvelle colonne
ALTER TABLE table_name ADD column_name datatype [FIRST | AFTER col_name ];
];
 Pour supprimer une colonne
ALTER TABLE table_name DROP COLUMN column_name;
 Pour changer le type de données d’une colonne
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
 Pour ajouter la contrainte NOT NULL à une colonne
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
 Pour ajouter la contrainte CHECK à une colonne
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
Modification de schéma
ALTER TABLE
 Pour ajouter une clé primaire
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
 Pour supprimer une contrainte CHECK
ALTER TABLE table_name
DROP CONSTRAINT MyCheckConstraint;
 Pour supprimer une contrainte PRIMARY KEY
ALTER TABLE table_name DROP PRIMARY KEY;
 Pour supprimer une contrainte FOREIGN KEY
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
 Pour Renommer une table
ALTER TABLE table_name RENAME TO new_table_name;
Modification de schéma
ALTER TABLE
 Pour supprimer une contrainte nommée (PrimaryKey, ForeignKey,
Unique, Check)
ALTER TABLE table_name
DROP CONSTRAINT NomContraint;
NomContraint;
Mise à jour des données
Ajout de tuples (INSERT)
INSERT INTO nom-relation [(attr1, attr2…)]
VALUES (val-attr1, val-attr2, … )
Ajout de deux pièces dans la relation pièce
INSERT INTO pièce VALUES (4,'clou',1.99);
INSERT INTO pièce (nop,nomp) VALUES (5,'cheville');
nop
nomp
prix
1
vis
1.5
2
écrou
2
3
boulon
2.5
4
clou
1.99
5
cheville
NULL
N.B. Les attributs non insérés
sont positionnés à NULL
Mise à jour des données
Modification de tuples (UPDATE)
UPDATE nom_relation
SET attr1=exp1 [,attr2=exp2 … ]
[WHERE condition ]
Changer à 0.99 le prix de la pièce numéro 4
UPDATE
pièce
SET
prix= 0.99 WHERE nop=4;
Augmenter de 5% le prix des pièces dont le nom commence
par c
UPDATE pièce
SET prix= prix*1.05
WHERE nomp LIKE 'c%';
Mise à jour de données
Suppression de tuples (DELETE)
DELETE
[FROM]
relation [WHERE condition]
Supprimer les fournisseurs de Lyon:
DELETE
FROM fournisseur
WHERE
ville =
‘Lyon’;
Supprimer tous les tuples de la table vente :
DELETE
FROM vente;
 suppression de tous les tuples de la table vente
 le schéma de la table vente existe toujours
 On peut insérer des nouveaux tuples dans la table vente
Interrogation de données
SQL : langage pour les BDR basé sur l’algèbre
relationnelle
 Traduction des opérateurs de l’algèbre en SQL (projection,
restriction, jointure …).
commande SELECT
 Opérateurs supplémentaires pour trier ou agréger des
données.
Forme générale de la commande SELECT
SELECT {* | [ALL|DISTINCT] expr1 [AS nomCol1]
[,expr2 [AS nomCol2]]…}
FROM
relation1 [alias1] [,relation2 [alias2] …]
[WHERE condition]
[GROUP BY nomAttribut1 [,nomAttribut2]…]
[HAVING
condition]
[ORDER BY nomAttr1 [ASC|DESC][,nomAttr2 [ASC|DESC]…]
Notations pour la syntaxe des commandes
Notation
Signification
[élément]
élément est optionnel
élément1|élément2
on a le choix entre élément1
et élément2
{ élément1|élément2}
idem
{élément1}|{élément2}
idem
31
vente
fournisseur
nof
nomf
ville
nop
nof
1
Girard
Lyon
1
1
2
Blanc
Paris
1
2
3
Merlin
Nancy
2
2
2
3
3
1
3
2
3
3
pièce
nop
nomp
prix
1
vis
1.5
2
écrou
2
3
boulon
2.5
Base de données exemple
32
Traduction de la projection (1/2)
Trouver le nom et la ville de tous les fournisseurs
SELECT
FROM
nomf, ville
fournisseur
SELECT
FROM
ALL nomf, ville
ou
fournisseur
nomf
ville
Girard
Lyon
Blanc
Paris
Merlin
Nancy
Attention : les doublons ne sont pas supprimés !
Traduction de la projection (2/2)
Trouver les numéros de fournisseur vendant au moins une
pièce (sans les doublons)
SELECT
DISTINCT nof FROM
vente
nof
1
2
3
π nof (vente)
Expression
algébrique
équivalente
Traduction de la restriction (1/2)
Trouver les fournisseurs de Lyon
SELECT
FROM
WHERE
*
fournisseur
ville = ‘Lyon’
nof
nomf
ville
1
Girard
Lyon
Exp. algéb.
équivalente
σ ville = ‘Lyon’ (fournisseur)
Restriction : forme de la condition
SELECT
WHERE
*
FROM
nom-relation
predicat
 Syntaxe de predicat
predicatSimple | (predicat) | NOT (predicat) |
predicat {AND|OR} predicat
 Syntaxe de predicatSimple
expression {= | <|>|<=|>=|<>|!=} expression |
expression [NOT]BETWEEN expression AND expression|
expression IS [NOT] NULL
expression [NOT]LIKE patron
Restriction : Exemples
 Pièces dont le numéro est compris entre 1 et 100
ou
SELECT
FROM
WHERE
*
pièce
nop >= 1 AND
SELECT
FROM
WHERE
*
pièce
nop BETWEEN 1 AND 100
nop <= 100
 Pièces dont le numéro est inférieur à 10 ou supérieur à 20
SELECT
FROM
*
pièce
WHERE
nop < 10 OR nop > 20
Restriction : Opérateurs IS NULL et IS NOT NULL
IS NULL : Teste si la valeur d'un attribut est absente
IS NOT NULL : Teste si la valeur d'un attribut est présente (ou
définie)
 Pièces dont le prix n'est pas fixé (null)
SELECT
FROM
WHERE
*
pièce
prix IS NULL
Restriction : Opérateur LIKE
LIKE teste si une chaîne de caractères correspondant à un patron où :
% : correspond à zéro à n caractères quelconques
_ : correspond un caractère quelconque
 Fournisseurs dont le nom commence par B, se termine par B
et contient au moins 3 caractères
SELECT *
FROM
fournisseur
WHERE nomf LIKE ‘B_%B’
Traduction de restriction et projection
 Nom et prix des pièces dont le numéro est supérieur ou égal à 2
SELECT
FROM
WHERE
nomp, prix
pièce
nop >= 2
nomp
prix
écrou
2
boulon
2.5
π nomp,prix (σnop >=2 pièce)
Exp. Algébr.
équivalente
Traduction du produit cartésien
SELECT
FROM
*
relation1, relation2
Produire toutes les combinaisons possibles de fournisseur et de
pièce
SELECT *
FROM
fournisseur, pièce
Exp. Algébr.
équivalente
fournisseur x pièce
Traduction de la jointure
SELECT
FROM
WHERE
attribut1 [,attribut2, …]
relation1,relation2 [,relation3,…]
condition
 Cette commande SELECT combine
– produit cartésien entre relation1,relation2, relation3…
– restriction sur condition
– Projection sur attribut1,attribut2…
N.B. Il faut préfixer par un nom de relation chaque attribut commun à
plusieurs relations
Jointure : exemple de requête
Liste des ventes avec le nom du
fournisseur
SELECT nop,vente.nof,nomf
FROM
vente, fournisseur
WHERE vente.nof = fournisseur.nof
nop,vente.nof…
vente.nof =
founisseur.nof
x
vente
fournisseur
Arbre algébrique correspondant à
l’expression SQL (SGBDR)
Jointure : utilisation d'alias
Utilisation d’alias pour alléger l’écriture d’une requête
incluant des jointures
Liste des ventes avec le nom du fournisseur
SELECT
FROM
WHERE


v.nop,v.nof,f.nomf
vente v, fournisseur f
v.nof = f.nof
vente alias v
fournisseur alias f
Opérations ensemblistes
(UNION, INTERSECT, MINUS ou EXCEPT)
 Noms et prénoms des employés qui sont aussi passagers
Employé
Passager
noEmp
nomEmp
prénomEmp
noPass
nomPass
prénomPass
10
Henry
John
4
Harry
Peter
15
Conrad
James
78
Conrad
James
35
Jenqua
Jessica
9
Land
Robert
46
Leconte
Jean
466
Leconte
Jean
(SELECT nomEmp as nom, prénomEmp as prénom
FROM Employé)
INTERSECT
(SELECT nomPass as nom, prénomPass as prénom
FROM Passager)
nom
prénom
Conrad
James
Leconte
Jean
Les opérateurs Intersect, Minus (Except) ne sont pas supportés par tous les SGBD.
Opérations ensemblistes
(UNION, INTERSECT, MINUS ou EXCEPT)
 Noms et prénoms des employés qui ne sont pas passagers
Employé
Passager
noEmp
nomEmp
prénomEmp
noPass
nomPass
prénomPass
10
Henry
John
4
Harry
Peter
15
Conrad
James
78
Conrad
James
35
Jenqua
Jessica
9
Land
Robert
46
Leconte
Jean
466
Leconte
Jean
(SELECT nomEmp as nom, prénomEmp as prénom
FROM Employé)
MINUS
(SELECT nomPass as nom, prénomPass as prénom
FROM Passager)
nom
prénom
Henry
John
Jenqua
Jessica
Les opérateurs Intersect, Minus (Except) ne sont pas supportés par tous les SGBD.
Expression de calcul dans la liste de
projection (partie SELECT)
 Liste des numéros de pièce avec le prix avant et après
inclusion d’une taxe de 10%
SELECT
FROM
nop, prix, prix*1.1 as prixTTC
pièce
nop
prix
prixTTC
1
1.5
1.65
2
2
2.2
3
2.5
2.75
Expression de calcul dans la liste de
projection (partie SELECT)
 UPPER : convertir en majuscules
– Cette fonction convertit le texte d'un champ en majuscules
 LOWER : convertir en minuscules
– Cette fonction a l'effet inverse : le contenu sera entièrement écrit en
minuscules.
 LENGTH : compter le nombre de caractères
– Vous pouvez obtenir la longueur d'un champ avec la fonction LENGTH()
 ROUND : arrondir un nombre décimal
– La fonction ROUND() s'utilise sur des champs comportant des valeurs
décimales
 Liste complète
– http://dev.mysql.com/doc/refman/4.1/en/functions.html
Expression de calcul dans la condition
(partie WHERE ou partie SELECT)
Une condition peut comporter une expression de calcul
Liste des numéros de pièce dont le prix TTC dépasse 2€
SELECT
FROM
WHERE
nop
pièce
prix*1.1 > 2
nop
2
3
Expression de calcul dans la condition
(partie WHERE)
Une expression peut aussi faire appel à des fonctions
Numéro et nom des pièces dont le nom comporte 4 caractères ou
plus
SELECT
FROM
WHERE
nop, nomp
pièce
CHARACTER_LENGTH(nomp) >= 4
nop
nomp
2
écrou
3
boulon
CHARACTER_LENGTH ( c ) : fonction retournant le nombre de caractères
de la chaîne c
Fonctions d’agrégation (ou de groupe)
Elles opèrent sur un groupe de valeurs d’attributs et produisent
une valeur résultat (extension de l’algèbre relationelle)
Nombre total de pièces dans la relation pièce
SELECT COUNT(*) AS nb_pieces
FROM
pièce
nb_pieces
3
Prix moyen des pièces
SELECT AVG (prix) AS prix_moyen
FROM
pièce
prix_moyen
2
Prix maximal des pièces (maximum de la colonne prix)
SELECT MAX(prix) AS prix_maxi
FROM
pièce
prix_maxi
2.5
Fonctions d’agrégation (ou de groupe)
Prix minimal des pièces (minimum de la colonne prix)
prix_mini
SELECT MIN(prix) AS prix_mini
FROM
pièce
1.5
Somme des prix des pièces
SELECT SUM(prix) AS somme_totale
FROM
pièce
somme_totale
6
Somme des prix des pièces dont le numéro est supérieur ou égal à 2
SELECT SUM (prix) AS somme
FROM
pièce
WHERE nop >= 2
somme
4.5
GROUP BY : grouper des données
 On utilise cette clause en combinaison d'une fonction d'agrégat (comme
AVG) pour obtenir des informations intéressantes sur des groupes de
données.
SELECT AGREGAT(colonne) as alias, colonne (s)
FROM relation(s) [WHERE condition]
GROUP BY colonne(s)
 Il faut utiliser GROUP BY en même temps qu'une fonction d'agrégat,
sinon il ne sert à rien.
Nombre de ventes par pièces
SELECT nop, COUNT(nof) as nb_ventes,
From vente
GROUP BY nop
nop
Nb_ventes
1
2
2
2
3
3
HAVING : filtrer les données regroupées
 HAVING montre une condition sur les données une fois qu'elles ont été
regroupées. C'est donc une façon de filtrer les données après avoir
composé les groupes et fait les calculs des agrégats.
SELECT AGREGAT(colonne) as alias, colonne (s)
FROM relation(s) [WHERE condition]
GROUP BY colonne(s)
HAVING condition(s)
Nombre de ventes inférieures ou égales à 2 par pièces
SELECT COUNT(nof) as nb_ventes, nop
FROM
vente
GROUP BY nop
HAVING COUNT(nof) <= 2
Nb_ventes nop
2
1
2
2
Tri du résultat d’une requête (ORDER BY)
 Possibilité de trier les résultats d’une requête par rapport à une ou
plusieurs colonnes
SELECT colonne(s)
FROM relation(s) [WHERE condition]
ORDER BY colonne(s) [ASC|DESC]
Où
ASC : ordre ascendant (par défaut)
DESC : ordre descendant
liste des pièces par ordre décroissant du prix
SELECT *
FROM
pièce
ORDER BY prix DESC
nop
nomp
prix
3
boulon
2.5
2
écrou
2
1
vis
1.5
Requêtes imbriquées
Opérateur IN / NOT IN
 Le résultat d’une commande SELECT peut être utilisé dans la
condition d’une autre commande SELECT
SELECT
FROM
WHERE
attribut(s)
relation(s)
expression [NOT]IN (sous-requête)
On teste l’appartenance (ou non appartenance) de la valeur
de l'expression à l'ensemble retourné par la sous-requête
Requêtes imbriquées
Opérateur IN / NOT IN
Nom des pièces commandées par le fournisseur numéro 1
SELECT
FROM
WHERE
nomp
pièce
nop
IN
(SELECT nop
FROM
vente
WHERE
nof = 1)
Intersection : IN
Différence : NOT IN