LIF4-Cours-5-SQL3

Emmanuel Coquery

Informations

• Publié par: Fuiv
• Nombre de lectures: 314
• Langue: Français

Extrait

LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance
Modification des donn´ees stock´ees dans une base
La modification s’e!ectue par ajout, suppression ou modification
LIF4 - Initiation aux Bases de donn´ees : de n-uplets (lignes) dans l’instance de la base.
SQL - 3
SQL sert ici de langage de manipulation de donn´ees.
E.Coquery Trois instructions SQL permettent ces modifications :
[e-mail]
DELETE
UPDATEhttp://liris.cnrs.fr/!ecoquery
Ces instructions de mise `a jour peuvent utiliser des (morceaux
de) requˆete afin d’e!ectuer des calculs pour s´electionner et/ou
g´en´erer des donn´ees.
LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Insertion Exemple
Instruction INSERT Sch´ema : Batiment(Num bat, Nom bat, Ent princ, Ent sec)
INSERT INTO nom table(att , ..., att )1 n
VALUES(val , ..., val )1 n
Num bat Nom bat Ent princ Ent sec
Ajoute le n-uplet (val , ..., val ) `a la relation nom table.1 n 1 Turing Nord Ouest
val correspond `a l’attribut att . 2 Einstein Ouest NULLi i
3 Newton Sud NordSi un attribut de la relation nom table n’apparaˆıt pas dans
4 Pointcarre Est NULLatt , . . . ,att , alors la valeur du n-uplet pour cet attribut est1 n
5 Curie Nord NULLNULL.
6 Bohr Sud Est
La sp´ecification des attributs att , ..., att est optionnelle1 n
Si on pr´ecise pas les attributs, il faut donner une valeur `a tous
INSERT INTO Batiment(Nom bat,Num bat,Ent princ)les attributs.
VALUES (’Curie’,5,’Nord’);L’ordre sur des valeurs (val , ..., val ) est celui des attributs1 n
dans la d´efinition de la relation nom table.
INSERT INTO Batiment VALUES (6,’Bohr’,’Sud’,’Est’);C’est un des cas ou` cet ordre est important.
LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Insertion utilisant une requˆete Exemple
Sch´ema :
INSERT INTO nom table(att , . . . ,att ) Departement(Num dept, Nom dept, Num bat, Num chef)1 n
SELECT e , . . . ,e Batiment(Num bat, Nom bat, Ent princ, Ent sec)1 n
FROM ... Dept important(Nom,Bat)
Insertion dans nom table des n-uplets calcul´es par la requˆete
Ajouter `a la table Dept important les d´epartements qui sont dansSELECT ... FROM ...
des batiments ayant une entr´ee secondaire :La requˆete ne peut pas contenir de ORDER BY
De toute fa¸con, c’est le SGBD qui d´etermine l’ordre dans
lequel les n-uplets sont stock´es. INSERT INTO Dept important(Bat,Nom)
SELECT Nom bat, Nom deptLe nom des colonnes dans le r´esultat de la requˆete n’est pas
FROM Batiment, Departementimportant : c’est l’ordre des expressions qui compte.
WHERE Departement.Num bat = Batiment.Num bat
AND Ent sec IS NOT NULL;LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Supression Exemple
DELETE FROM nom table
Num bat Nom bat Ent princ Ent secWHERE condition
1 Turing Nord Ouest
2 Einstein Ouest NULL
Supprime les n-uplets de la relation nom table qui v´erifient
3 Newton Sud Nord
condition.
4 Pointcarre Est NULL peut ˆetre aussi complexe qu’une condition exprim´ee 5 Curie Nord NULL
dans le WHERE d’un SELECT.
6 Bohr Sud Est
En particulier, condition peut contenir des requˆetes imbriqu´ees.
Les requˆetes imbriqu´ees ne peuvent pas faire r´ef´erence `a
nom table, car elle est en cours de modification.
Supprimer le bˆatiment num´ero 5 :
WHERE condition est optionnel.
Si le WHERE est omis, tous les n-uplets sont supprim´es (cela DELETE FROM Batiment
revient `a utiliser a condition TRUE).
WHERE Num bat = 5;
LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Exemple - 2 Modification de n-uplets
UPDATE nom table
Sch´ema : SET att = e ,1 1
Batiment(Num bat, Nom bat, Ent princ, Ent sec) att = e ,2 2
Departement(Num dept, Nom dept, Num bat, Num chef) ...
WHERE condition
Supprimer les bˆatiments qui ne correspondent `a aucun condition indique les lignes `a modifier.
d´epartement : att prend la valeur calcul´ee par l’expression e .i i
e peut utiliser att ,att , . . ., y compris att .i 1 2 i
Ce sont les anciennes valeurs de att ,att , . . . qui seront1 2
DELETE FROM Batiment utilis´ees pour le calcul.
WHERE Num bat NOT IN Les e peuvent ˆetre des requˆetes `a condition qu’elles renvoienti
(SELECT Departement.Num bat un unique r´esultat et que nom table n’apparaisse pas dans un
FROM Departement); FROM.
Similairement au DELETE, le WHERE est optionnel.
Si le WHERE est omis, tous les n-uplets sont modifi´es.
LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Exemple Exemple - 2
Sch´ema : Batiment(Num bat, Nom bat, Ent princ, Ent sec)
Sch´ema : Employe(Nom, Num, Fonction, Salaire, Num Dept)
Changer le nom du bˆatiment num´ero 3 en ’Copernic’ :
Augmenter de 10% le salaire des ing´enieurs :
UPDATE Batiment SET Nom bat = ’Copernic’;
Num bat Nom bat Ent princ Ent sec
UPDATE Employe1 Turing Nord Ouest
SET Salaire = Salaire * 1.12 Einstein Ouest NULL
WHERE Fonction = ’ingenieur’;3 Copernic Sud NordLIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Exemple - 3 Exemple - 3 - suite
UPDATE Departement
Sch´ema : SET Num chef =
Employe(Nom, Num, Fonction, Num sup, Embauche, (SELECT Num
Salaire, Commission, Num Dept) FROM Employe
Departement(Num dept, Nom dept, Num bat, Num chef) WHERE Fonction = ’directeur’
AND Employe.Num dept = Departement.Num dept
AND Embauche <= ALL
(SELECT EmbauchePour chaque d´epartement dont le chef n’est pas connu, sp´ecifier
FROM Employe Eque son chef est le plus ancien employ´e de ce d´epartement
WHERE Fonction = ’directeur’occupant la fonction de directeur.
AND E.Num dept = Departement.Num dept))
WHERE Num chef IS NULL;
LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance Modifications d’une instance
Transactions Transactions en SQL
Gestion des transactions en SQL :
Une transaction est un ensemble de modifications de la base
qui forme un tout indivisible. COMMIT;
Valide les modifications e!ectu´ees.Ces modifications doivent ˆetre e!ectu´ee enti`erement ou pas
Les modifications sont alors d´efinitives et visibles par tous.du tout, sous peine de laisser la base dans un ´etat incoh´erent.
Au cours d’une transaction, seul l’utilisateur ayant d´emarr´e
cette transaction voit les modifications e!ectu´ees.
ROLLBACK;
Annule les modifications e!ectu´ees depuis le d´ebut de la
transaction.
LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
Modifications d’une instance D´efinition et modification du sch´ema d’une base
Di!´erences de traitement des transactions entre SGBDs Gestion du sch´ema d’une base
Dans Oracle :
SQL est ´egalement un langage de d´efinition de donn´ees :Une nouvelle transaction est implicitement d´emarr´ee au d´ebut
de la connection et apr`es chaque COMMIT. Permet de cr´eer ou supprimer des tables.
Le syst`eme assure la coh´erence des donn´ees en cas de mise `a Permet de modifier la structure d’une table.
jour simultan´ee par deux utilisateurs, en utilisant un syst`eme Permet de sp´ecifier certaines contraintes d’int´egrit´e sur le
de verrouillage automatique. sch´ema.
Dans MySQL :
Les tables doivent ˆetre stock´ees en utilisant le moteur de
stockage InnoDB ou BDB pour que les transactions soient DESC nom table;
g´er´ees.
Par d´efaut, chaque mise `a jour est imm´ediatement valid´ee Permet d’obtenir des informations sur le sch´ema d’une table.
(COMMIT automatique). Les attributs et leur type.
Pour d´emarrer explicitement une transaction, on utilise Des informations sur certaines contraintes d’int´egrit´e.
l’instruction BEGIN;
Dans ce cas le COMMIT automatique est d´esactiv´e.LIF4 - Initiation aux Bases de donn´ees : SQL - 3 LIF4 - Initiation aux Bases de donn´ees : SQL - 3
D´efinition et modification du sch´ema d’une base D´efinition et modification du sch´ema d’une base
Cr´eation de table Cr´eation simple
CREATE TABLE nom table(att type , att type , ...);1 1 2 2
Lors de la cr´eation d’une table on indique :
Cr´ee une table nom table;
Le nom des attributs.
ayant pour attributs att ,att , . . . ;1 2
Le type de chaque attribut.
att ayant le type type .i i
De mani`ere optionnelle : Exemple :
Certaines contraintes d’int´egrit&