Vues

• Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
• Sommaire
• Tables temporaires

Les vues sont des objets de la base de données, constitués d'un nom, et d'une requête de sélection.

Une fois qu'une vue est définie, on peut l'utiliser comme on le ferait avec une table ; table qui serait constituée des données sélectionnées par la requête définissant la vue.

Nous verrons dans ce chapitre :

• comment créer, modifier, supprimer une vue ;
• à quoi peut servir une vue ;
• deux algorithmes différents pouvant être utilisées par les vues ;
• comment modifier les données à partir d'une vue.

Etat actuel de la base de données

Note : les tables de test et les procédures stockées ne sont pas reprises.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313

SET NAMES utf8;

DROP TABLE IF EXISTS Erreur;
DROP TABLE IF EXISTS Animal_histo;

DROP TABLE IF EXISTS Adoption;
DROP TABLE IF EXISTS Animal;
DROP TABLE IF EXISTS Race;
DROP TABLE IF EXISTS Espece;
DROP TABLE IF EXISTS Client;


CREATE TABLE Client (
  id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  nom varchar(100) NOT NULL,
  prenom varchar(60) NOT NULL,
  adresse varchar(200) DEFAULT NULL,
  code_postal varchar(6) DEFAULT NULL,
  ville varchar(60) DEFAULT NULL,
  pays varchar(60) DEFAULT NULL,
  email varbinary(100) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY ind_uni_email (email)
) ENGINE=InnoDB AUTO_INCREMENT=17;

LOCK TABLES Client WRITE;
INSERT INTO Client VALUES (1,'Dupont','Jean','Rue du Centre, 5','45810','Houtsiplou','France','jean.dupont@email.com'),(2,'Boudur','Marie','Place de la Gare, 2','35840','Troudumonde','France','marie.boudur@email.com'),(3,'Trachon','Fleur','Rue haute, 54b','3250','Belville','Belgique','fleurtrachon@email.com'),
(4,'Van Piperseel','Julien',NULL,NULL,NULL,NULL,'jeanvp@email.com'),(5,'Nouvel','Johan',NULL,NULL,NULL,'Suisse','johanetpirlouit@email.com'),(6,'Germain','Frank',NULL,NULL,NULL,NULL,'francoisgermain@email.com'),
(7,'Antoine','Maximilien','Rue Moineau, 123','4580','Trocoul','Belgique','max.antoine@email.com'),(8,'Di Paolo','Hector',NULL,NULL,NULL,'Suisse','hectordipao@email.com'),(9,'Corduro','Anaelle',NULL,NULL,NULL,NULL,'ana.corduro@email.com'),
(10,'Faluche','Eline','Avenue circulaire, 7','45870','Garduche','France','elinefaluche@email.com'),(11,'Penni','Carine','Boulevard Haussman, 85','1514','Plasse','Suisse','cpenni@email.com'),(12,'Broussaille','Virginie','Rue du Fleuve, 18','45810','Houtsiplou','France','vibrousaille@email.com'),
(13,'Durant','Hannah','Rue des Pendus, 66','1514','Plasse','Suisse','hhdurant@email.com'),(14,'Delfour','Elodie','Rue de Flore, 1','3250','Belville','Belgique','e.delfour@email.com'),(15,'Kestau','Joel',NULL,NULL,NULL,NULL,'joel.kestau@email.com');
UNLOCK TABLES;


CREATE TABLE Espece (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  nom_courant varchar(40) NOT NULL,
  nom_latin varchar(40) NOT NULL,
  description text,
  prix decimal(7,2) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY nom_latin (nom_latin)
) ENGINE=InnoDB AUTO_INCREMENT=6;

LOCK TABLES Espece WRITE;
INSERT INTO Espece VALUES (1,'Chien','Canis canis','Bestiole à quatre pattes qui aime les caresses et tire souvent la langue',200.00),(2,'Chat','Felis silvestris','Bestiole à quatre pattes qui saute très haut et grimpe aux arbres',150.00),(3,'Tortue d''Hermann','Testudo hermanni','Bestiole avec une carapace très dure',140.00),
(4,'Perroquet amazone','Alipiopsitta xanthops','Joli oiseau parleur vert et jaune',700.00),(5,'Rat brun','Rattus norvegicus','Petite bestiole avec de longues moustaches et une longue queue sans poils',10.00);
UNLOCK TABLES;


CREATE TABLE Race (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  nom varchar(40) NOT NULL,
  espece_id smallint(6) unsigned NOT NULL,
  description text,
  prix decimal(7,2) unsigned DEFAULT NULL,
  date_insertion datetime DEFAULT NULL,
  utilisateur_insertion varchar(20) DEFAULT NULL,
  date_modification datetime DEFAULT NULL,
  utilisateur_modification varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11;

LOCK TABLES Race WRITE;
INSERT INTO Race VALUES (1,'Berger allemand',1,'Chien sportif et élégant au pelage dense, noir-marron-fauve, noir ou gris.',485.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(2,'Berger blanc suisse',1,'Petit chien au corps compact, avec des pattes courtes mais bien proportionnées et au pelage tricolore ou bicolore.',935.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(3,'Singapura',2,'Chat de petite taille aux grands yeux en amandes.',985.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),
(4,'Bleu russe',2,'Chat aux yeux verts et à la robe épaisse et argentée.',835.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(5,'Maine coon',2,'Chat de grande taille, à poils mi-longs.',735.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(7,'Sphynx',2,'Chat sans poils.',1235.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),
(8,'Nebelung',2,'Chat bleu russe, mais avec des poils longs...',985.00,'2012-05-21 00:53:36','Test','2012-05-21 00:53:36','Test'),(9,'Rottweiller',1,'Chien d''apparence solide, bien musclé, à la robe noire avec des taches feu bien délimitées.',630.00,'2012-05-21 00:53:36','Test','2012-05-22 00:54:13','sdz@localhost'),(10,'Yorkshire terrier',1,'Chien de petite taille au pelage long et soyeux de couleur bleu et feu.',700.00,'2012-05-22 00:58:25','sdz@localhost','2012-05-22 00:58:25','sdz@localhost');
UNLOCK TABLES;


CREATE TABLE Animal (
  id smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  sexe char(1) DEFAULT NULL,
  date_naissance datetime NOT NULL,
  nom varchar(30) DEFAULT NULL,
  commentaires text,
  espece_id smallint(6) unsigned NOT NULL,
  race_id smallint(6) unsigned DEFAULT NULL,
  mere_id smallint(6) unsigned DEFAULT NULL,
  pere_id smallint(6) unsigned DEFAULT NULL,
  disponible tinyint(1) DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY ind_uni_nom_espece_id (nom,espece_id)
) ENGINE=InnoDB AUTO_INCREMENT=76;

LOCK TABLES Animal WRITE;
INSERT INTO Animal VALUES (1,'M','2010-04-05 13:43:00','Rox','Mordille beaucoup',1,1,18,22,1),(2,NULL,'2010-03-24 02:23:00','Roucky',NULL,2,NULL,40,30,1),(3,'F','2010-09-13 15:02:00','Schtroumpfette',NULL,2,4,41,31,0),
(4,'F','2009-08-03 05:12:00',NULL,'Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(5,NULL,'2010-10-03 16:44:00','Choupi','Né sans oreille gauche',2,NULL,NULL,NULL,0),(6,'F','2009-06-13 08:17:00','Bobosse','Carapace bizarre',3,NULL,NULL,NULL,1),
(7,'F','2008-12-06 05:18:00','Caroline',NULL,1,2,NULL,NULL,1),(8,'M','2008-09-11 15:38:00','Bagherra',NULL,2,5,NULL,NULL,0),(9,NULL,'2010-08-23 05:18:00',NULL,'Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(10,'M','2010-07-21 15:41:00','Bobo','Petit pour son âge',1,NULL,7,21,1),(11,'F','2008-02-20 15:45:00','Canaille',NULL,1,NULL,NULL,NULL,1),(12,'F','2009-05-26 08:54:00','Cali',NULL,1,2,NULL,NULL,1),
(13,'F','2007-04-24 12:54:00','Rouquine',NULL,1,1,NULL,NULL,1),(14,'F','2009-05-26 08:56:00','Fila',NULL,1,2,NULL,NULL,1),(15,'F','2008-02-20 15:47:00','Anya',NULL,1,NULL,NULL,NULL,0),
(16,'F','2009-05-26 08:50:00','Louya',NULL,1,NULL,NULL,NULL,0),(17,'F','2008-03-10 13:45:00','Welva',NULL,1,NULL,NULL,NULL,1),(18,'F','2007-04-24 12:59:00','Zira',NULL,1,1,NULL,NULL,0),
(19,'F','2009-05-26 09:02:00','Java',NULL,1,2,NULL,NULL,1),(20,NULL,'2007-04-24 12:45:00','Balou',NULL,1,1,NULL,NULL,1),(21,'F','2008-03-10 13:43:00','Pataude',NULL,1,NULL,NULL,NULL,0),
(22,'M','2007-04-24 12:42:00','Bouli',NULL,1,1,NULL,NULL,1),(24,'M','2007-04-12 05:23:00','Cartouche',NULL,1,NULL,NULL,NULL,1),(25,'M','2006-05-14 15:50:00','Zambo',NULL,1,1,NULL,NULL,1),
(26,'M','2006-05-14 15:48:00','Samba',NULL,1,1,NULL,NULL,0),(27,'M','2008-03-10 13:40:00','Moka',NULL,1,NULL,NULL,NULL,0),(28,'M','2006-05-14 15:40:00','Pilou',NULL,1,1,NULL,NULL,1),
(29,'M','2009-05-14 06:30:00','Fiero',NULL,2,3,NULL,NULL,1),(30,'M','2007-03-12 12:05:00','Zonko',NULL,2,5,NULL,NULL,0),(31,'M','2008-02-20 15:45:00','Filou',NULL,2,4,NULL,NULL,1),
(32,'M','2009-07-26 11:52:00','Spoutnik',NULL,3,NULL,52,NULL,0),(33,'M','2006-05-19 16:17:00','Caribou',NULL,2,4,NULL,NULL,1),(34,'M','2008-04-20 03:22:00','Capou',NULL,2,5,NULL,NULL,1),
(35,'M','2006-05-19 16:56:00','Raccou','Pas de queue depuis la naissance',2,4,NULL,NULL,1),(36,'M','2009-05-14 06:42:00','Boucan',NULL,2,3,NULL,NULL,1),(37,'F','2006-05-19 16:06:00','Callune',NULL,2,8,NULL,NULL,1),
(38,'F','2009-05-14 06:45:00','Boule',NULL,2,3,NULL,NULL,0),(39,'F','2008-04-20 03:26:00','Zara',NULL,2,5,NULL,NULL,0),(40,'F','2007-03-12 12:00:00','Milla',NULL,2,5,NULL,NULL,0),
(41,'F','2006-05-19 15:59:00','Feta',NULL,2,4,NULL,NULL,0),(42,'F','2008-04-20 03:20:00','Bilba','Sourde de l''oreille droite à 80%',2,5,NULL,NULL,0),(43,'F','2007-03-12 11:54:00','Cracotte',NULL,2,5,NULL,NULL,1),
(44,'F','2006-05-19 16:16:00','Cawette',NULL,2,8,NULL,NULL,1),(45,'F','2007-04-01 18:17:00','Nikki','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(46,'F','2009-03-24 08:23:00','Tortilla','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(48,'F','2006-03-15 14:56:00','Lulla','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(49,'F','2008-03-15 12:02:00','Dana','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,0),(50,'F','2009-05-25 19:57:00','Cheli','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(51,'F','2007-04-01 03:54:00','Chicaca','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(52,'F','2006-03-15 14:26:00','Redbul','Insomniaque',3,NULL,NULL,NULL,1),(54,'M','2008-03-16 08:20:00','Bubulle','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),
(55,'M','2008-03-15 18:45:00','Relou','Surpoids',3,NULL,NULL,NULL,0),(56,'M','2009-05-25 18:54:00','Bulbizard','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1),(57,'M','2007-03-04 19:36:00','Safran','Coco veut un gâteau !',4,NULL,NULL,NULL,0),
(58,'M','2008-02-20 02:50:00','Gingko','Coco veut un gâteau !',4,NULL,NULL,NULL,0),(59,'M','2009-03-26 08:28:00','Bavard','Coco veut un gâteau !',4,NULL,NULL,NULL,0),(60,'F','2009-03-26 07:55:00','Parlotte','Coco veut un gâteau !',4,NULL,NULL,NULL,1),
(61,'M','2010-11-09 00:00:00','Yoda',NULL,2,5,NULL,NULL,1),(62,'M','2010-11-05 00:00:00','Pipo',NULL,1,9,NULL,NULL,0),(69,'F','2012-02-13 15:45:00','Baba',NULL,5,NULL,NULL,NULL,0),
(70,'M','2012-02-13 15:48:00','Bibo','Agressif',5,NULL,72,73,1),(72,'F','2008-02-01 02:25:00','Momy',NULL,5,NULL,NULL,NULL,1),(73,'M','2007-03-11 12:45:00','Popi',NULL,5,NULL,NULL,NULL,1),
(75,'F','2007-03-12 22:03:00','Mimi',NULL,5,NULL,NULL,NULL,0);
UNLOCK TABLES;


CREATE TABLE Adoption (
  client_id smallint(5) unsigned NOT NULL,
  animal_id smallint(5) unsigned NOT NULL,
  date_reservation date NOT NULL,
  date_adoption date DEFAULT NULL,
  prix decimal(7,2) unsigned NOT NULL,
  paye tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (client_id,animal_id),
  UNIQUE KEY ind_uni_animal_id (animal_id)
) ENGINE=InnoDB;


LOCK TABLES Adoption WRITE;
INSERT INTO Adoption VALUES (1,8,'2012-05-21',NULL,735.00,1),(1,39,'2008-08-17','2008-08-17',735.00,1),(1,40,'2008-08-17','2008-08-17',735.00,1),
(2,3,'2011-03-12','2011-03-12',835.00,1),(2,18,'2008-06-04','2008-06-04',485.00,1),(3,27,'2009-11-17','2009-11-17',200.00,1),
(4,26,'2007-02-21','2007-02-21',485.00,1),(4,41,'2007-02-21','2007-02-21',835.00,1),(5,21,'2009-03-08','2009-03-08',200.00,1),
(6,16,'2010-01-27','2010-01-27',200.00,1),(7,5,'2011-04-05','2011-04-05',150.00,1),(8,42,'2008-08-16','2008-08-16',735.00,1),
(9,38,'2007-02-11','2007-02-11',985.00,1),(9,55,'2011-02-13','2011-02-13',140.00,1),(9,59,'2012-05-22',NULL,700.00,0),
(10,49,'2010-08-17','2010-08-17',140.00,0),(11,32,'2008-08-17','2010-03-09',140.00,1),(11,62,'2011-03-01','2011-03-01',630.00,0),
(12,15,'2012-05-22',NULL,200.00,1),(12,69,'2007-09-20','2007-09-20',10.00,1),(12,75,'2012-05-21',NULL,10.00,0),
(13,57,'2012-01-10','2012-01-10',700.00,1),(14,58,'2012-02-25','2012-02-25',700.00,1),(15,30,'2008-08-17','2008-08-17',735.00,1);
UNLOCK TABLES;

ALTER TABLE Race ADD CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);

ALTER TABLE Animal ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race (id);
ALTER TABLE Animal ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal (id);
ALTER TABLE Animal ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal (id);
ALTER TABLE Animal ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES Espece (id);

ALTER TABLE Adoption ADD CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES Client (id);
ALTER TABLE Adoption ADD CONSTRAINT fk_adoption_animal_id FOREIGN KEY (animal_id) REFERENCES Animal (id);


CREATE TABLE Animal_histo (
  id smallint(6) unsigned NOT NULL,
  sexe char(1) DEFAULT NULL,
  date_naissance datetime NOT NULL,
  nom varchar(30) DEFAULT NULL,
  commentaires text,
  espece_id smallint(6) unsigned NOT NULL,
  race_id smallint(6) unsigned DEFAULT NULL,
  mere_id smallint(6) unsigned DEFAULT NULL,
  pere_id smallint(6) unsigned DEFAULT NULL,
  disponible tinyint(1) DEFAULT 1,
  date_histo datetime NOT NULL,
  utilisateur_histo varchar(20) NOT NULL,
  evenement_histo char(6) NOT NULL,
  PRIMARY KEY (id,date_histo)
) ENGINE=InnoDB;

LOCK TABLES Animal_histo WRITE;
INSERT INTO Animal_histo VALUES (10,'M','2010-07-21 15:41:00','Bobo',NULL,1,NULL,7,21,1,'2012-05-22 01:00:34','sdz@localhost','UPDATE'),(47,'F','2009-03-26 01:24:00','Scroupy','Bestiole avec une carapace très dure',3,NULL,NULL,NULL,1,'2012-05-22 01:00:34','sdz@localhost','DELETE');
UNLOCK TABLES;


CREATE TABLE Erreur (
  id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  erreur varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY erreur (erreur)
) ENGINE=InnoDB AUTO_INCREMENT=8;

LOCK TABLES Erreur WRITE;
INSERT INTO Erreur VALUES (5,'Erreur : date_adoption doit être >= à date_reservation.'),(3,'Erreur : paye doit valoir TRUE (1) ou FALSE (0).'),(1,'Erreur : sexe doit valoir \"M\", \"F\" ou NULL.');
UNLOCK TABLES;

-- -------- --
-- TRIGGERS --
-- -------- --
DELIMITER |
CREATE TRIGGER before_insert_adoption BEFORE INSERT
ON Adoption FOR EACH ROW
BEGIN
    IF NEW.paye != TRUE                                     
    AND NEW.paye != FALSE     
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou FALSE (0).');

    ELSEIF NEW.date_adoption < NEW.date_reservation THEN    
        INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à date_reservation.');
    END IF;
END |

CREATE TRIGGER after_insert_adoption AFTER INSERT
ON Adoption FOR EACH ROW
BEGIN
    UPDATE Animal
    SET disponible = FALSE
    WHERE id = NEW.animal_id;
END |

CREATE TRIGGER before_update_adoption BEFORE UPDATE
ON Adoption FOR EACH ROW
BEGIN
    IF NEW.paye != TRUE                                     
    AND NEW.paye != FALSE     
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou FALSE (0).');

    ELSEIF NEW.date_adoption < NEW.date_reservation THEN    
        INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à date_reservation.');
    END IF;
END |

CREATE TRIGGER after_update_adoption AFTER UPDATE
ON Adoption FOR EACH ROW
BEGIN
    IF OLD.animal_id <> NEW.animal_id THEN
        UPDATE Animal
        SET disponible = TRUE
        WHERE id = OLD.animal_id;

        UPDATE Animal
        SET disponible = FALSE
        WHERE id = NEW.animal_id;
    END IF;
END |

CREATE TRIGGER after_delete_adoption AFTER DELETE
ON Adoption FOR EACH ROW
BEGIN
    UPDATE Animal
    SET disponible = TRUE
    WHERE id = OLD.animal_id;
END |

CREATE TRIGGER before_insert_animal BEFORE INSERT
ON Animal FOR EACH ROW
BEGIN
    IF NEW.sexe IS NOT NULL   
    AND NEW.sexe != 'M'       
    AND NEW.sexe != 'F'       
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : sexe doit valoir "M", "F" ou NULL.');
    END IF;
END |

CREATE TRIGGER before_update_animal BEFORE UPDATE
ON Animal FOR EACH ROW
BEGIN
    IF NEW.sexe IS NOT NULL   
    AND NEW.sexe != 'M'       
    AND NEW.sexe != 'F'       
      THEN
        SET NEW.sexe = NULL;
    END IF;
END |

CREATE TRIGGER after_update_animal AFTER UPDATE
ON Animal FOR EACH ROW
BEGIN
    INSERT INTO Animal_histo (
        id, sexe, date_naissance, nom, commentaires, espece_id, race_id, 
        mere_id, pere_id, disponible, date_histo, utilisateur_histo, evenement_histo)
    VALUES (
        OLD.id, OLD.sexe, OLD.date_naissance, OLD.nom, OLD.commentaires, OLD.espece_id, OLD.race_id,
        OLD.mere_id, OLD.pere_id, OLD.disponible, NOW(), CURRENT_USER(), 'UPDATE');
END |

CREATE TRIGGER after_delete_animal AFTER DELETE
ON Animal FOR EACH ROW
BEGIN
    INSERT INTO Animal_histo (
        id, sexe, date_naissance, nom, commentaires, espece_id, race_id, 
        mere_id,  pere_id, disponible, date_histo, utilisateur_histo, evenement_histo)
    VALUES (
        OLD.id, OLD.sexe, OLD.date_naissance, OLD.nom, OLD.commentaires, OLD.espece_id, OLD.race_id,
        OLD.mere_id, OLD.pere_id, OLD.disponible, NOW(), CURRENT_USER(), 'DELETE');
END |

CREATE TRIGGER before_delete_espece BEFORE DELETE
ON Espece FOR EACH ROW
BEGIN
    DELETE FROM Race
    WHERE espece_id = OLD.id;
END |

CREATE TRIGGER before_insert_race BEFORE INSERT
ON Race FOR EACH ROW
BEGIN
    SET NEW.date_insertion = NOW();
    SET NEW.utilisateur_insertion = CURRENT_USER();
    SET NEW.date_modification = NOW();
    SET NEW.utilisateur_modification = CURRENT_USER();
END |

CREATE TRIGGER before_update_race BEFORE UPDATE
ON Race FOR EACH ROW
BEGIN
    SET NEW.date_modification = NOW();
    SET NEW.utilisateur_modification = CURRENT_USER();
END |

CREATE TRIGGER before_delete_race BEFORE DELETE
ON Race FOR EACH ROW
BEGIN
    UPDATE Animal 
    SET race_id = NULL
    WHERE race_id = OLD.id;
END |
DELIMITER ;

• Création d'une vue
• Sélection des données d'une vue
• Modification et suppression d'une vue
• Utilité des vues
• Algorithmes
• Modification des données d'une vue

Création d'une vue

Le principe

Pour notre élevage, la requête suivante est très utile.

1
2
3
4
5
6

SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant AS espece_nom, Race.nom AS race_nom
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id;

Avec ou sans clause WHERE, il arrive régulièrement qu'on veuille trouver des renseignements sur nos animaux, y compris leur race et leur espèce (et, seul, l'id contenu dans Animal n'est pas une information très explicite). Il serait donc bien pratique de pouvoir stocker cette requête plutôt que de devoir la retaper en entier à chaque fois.

C'est très exactement le principe d'une vue : on stocke une requête SELECT en lui donnant un nom, et on peut ensuite appeler directement la vue par son nom.

Quelques remarques importantes :

• Il s'agit bien d'objets de la base de données, stockés de manière durable, comme le sont les tables ou les procédures stockées.
• C'est donc bien différent des requêtes préparées, qui ne sont définies que le temps d'une session, et qui ont un tout autre but.
• Ce qui est stocké est la requête, et non pas les résultats de celle-ci. On ne gagne absolument rien en terme de performance en utilisant une vue plutôt qu'en faisant une requête directement sur les tables.

Création

Pour créer une vue, on utilise tout simplement la commande CREATE VIEW, dont voici la syntaxe :

1
2

CREATE [OR REPLACE] VIEW nom_vue
AS requete_select;

La clause OR REPLACE est facultative. Si elle est fournie, la vue nom_vue sera soit créée si elle n'existe pas, soit remplacée si elle existait déjà. Si OR REPLACE est omise et qu'une vue portant le même nom a été précédemment définie, cela déclenchera une erreur.

Donc, si l'on reprend la requête précédente, voici comment créer une vue pour stocker celle-ci :

1
2
3
4
5
6
7

CREATE VIEW V_Animal_details
AS SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant AS espece_nom, Race.nom AS race_nom
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id;

Dorénavant, plus besoin de retaper cette requête, il suffit de travailler à partir de la vue, comme s'il s'agissait d'une table :

1

SELECT * FROM V_Animal_details;

D'ailleurs, si l'on demande la liste des tables de la base de données, on peut voir que la vue est reprise (bien qu'il ne s'agit pas d'une table, mais d'une requête SELECT stockée, j'insiste).

1

SHOW TABLES;

Les colonnes de la vue

Comme pour les tables, on peut utiliser la commande DESCRIBE pour voir les différentes colonnes de notre vue.

1

DESCRIBE V_Animal_details;

Comme on pouvait s'y attendre, les noms de colonnes ont été déterminés par la clause SELECT de la requête définissant la vue. S'il n'y avait pas d'alias pour la colonne, c'est simplement le même nom que dans la table d'origine (date_naissance par exemple), et si un alias a été donné, il est utilisé (espece_nom par exemple).

Lister les colonnes dans CREATE VIEW

Il existe une autre possibilité que les alias dans la clause SELECT pour nommer les colonnes d'une vue. Il suffit de lister les colonnes juste après le nom de la vue, lors de la création de celle-ci. La commande suivante est l'équivalent de la précédente pour la création de V_Animal_details, mais cette fois sans alias dans la requête SELECT.

1
2
3
4
5
6
7

CREATE VIEW V_Animal_details  (id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible, espece_nom, race_nom)
AS SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant, Race.nom
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id;

Doublons interdits

Comme pour une table, il est impossible que deux colonnes ayant le même nom cohabitent dans une même vue.

1
2
3
4

CREATE VIEW V_test 
AS SELECT Animal.id, Espece.id 
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id;

1

ERROR 1060 (42S21): Duplicate column name 'id'

Pour pouvoir créer cette vue, il est nécessaire de renommer une des deux colonnes dans la vue (soit avec un alias, soit en listant les colonnes juste après CREATE VIEW nom_vue).

Requête SELECT stockée dans la vue

La requête définissant une vue peut être n'importe quelle requête SELECT, à quelques exceptions près.

• Il n'est pas possible d'utiliser une requête SELECT dont la clause FROM contient une sous-requête SELECT.
• La requête ne peut pas faire référence à des variables utilisateur, des variables système, ni même des variables locales (dans le cas d'une vue définie par une procédure stockée).
• Toutes les tables (ou vues) mentionnées dans la requête doivent exister (au moment de la création du moins).

En dehors de ça, carte blanche ! La requête peut contenir une clause WHERE, une clause GROUP BY, des fonctions (scalaires ou d’agrégation), des opérations mathématiques, une autre vue, des jointures, etc.

Exemple 1 : une vue pour les chiens.

L'employé de l'élevage préposé aux chiens peut créer une vue sur ceux-ci, afin de ne pas devoir ajouter une clause WHERE espece_id = 1 à chacune de ses requêtes.

1
2
3
4

CREATE VIEW V_Chien
AS SELECT id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible
FROM Animal
WHERE espece_id = 1;

Exemple 2 : combien de chats possédons-nous ?

Il est tout à fait possible de créer une vue définie par une requête qui compte le nombre d'animaux de chaque espèce que l'on possède.

1
2
3
4
5

CREATE OR REPLACE VIEW V_Nombre_espece
AS SELECT Espece.id, COUNT(Animal.id) AS nb
FROM Espece
LEFT JOIN Animal ON Animal.espece_id = Espece.id
GROUP BY Espece.id;

Exemple 3 : vue sur une vue.

La vue V_Chien sélectionne les chiens. Créons la vue V_Chien_race qui sélectionne les chiens dont on connaît la race. On peut bien sûr créer cette vue à partir de la table Animal, mais on peut aussi repartir simplement de la vue V_Chien.

1
2
3
4

CREATE OR REPLACE VIEW V_Chien_race
AS SELECT id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible
FROM V_Chien
WHERE race_id IS NOT NULL;

Exemple 4 : expression dans une vue.

Voici un exemple de vue définie par une requête contenant une expression, qui sélectionne les espèces, avec leur prix en dollars.

1
2
3

CREATE VIEW V_Espece_dollars
AS SELECT id, nom_courant, nom_latin, description, ROUND(prix*1.31564, 2) AS prix_dollars
FROM Espece;

La requête SELECT est "figée"

La requête SELECT définissant une vue est figée : les changements de structure faits par la suite sur la ou les tables sous-jacentes n'influent pas sur la vue.

Par exemple, si l'on crée une vue V_Client toute simple.

1
2
3

CREATE VIEW V_Client
AS SELECT * 
FROM Client;

Cette vue sélectionne les huit colonnes de Client (id, nom, prenom, adresse, code_postal, ville, pays, email). Une fois cette vue créée, elle est figée, et l'ajout d'une colonne dans la table Client ne changera pas les résultats d'une requête sur V_Client. Cette vue ne sélectionnera jamais que id, nom, prenom, adresse, code_postal, ville, pays et email, malgré le caractère * dans la clause SELECT. Pour que V_Client sélectionne la nouvelle colonne de Client, il faudrait recréer la vue pour l'inclure.

Exemple : ajout d'une colonne date_naissance à la table Client.

1
2
3

ALTER TABLE Client ADD COLUMN date_naissance DATE;

DESCRIBE V_Client;

Tri des données directement dans la vue

Si l'on met un ORDER BY dans la requête définissant une vue, celui-ci prendra effet, sauf si l'on fait une requête sur la vue avec un ORDER BY. Dans ce cas, c'est ce dernier qui prime, et l'ORDER BY originel est ignoré.

Exemple

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE OR REPLACE VIEW V_Race
AS SELECT Race.id, nom, Espece.nom_courant AS espece
FROM Race
INNER JOIN Espece ON Espece.id = Race.espece_id
ORDER BY nom;

SELECT * 
FROM V_Race;     -- Sélection sans ORDER BY, on prend l'ORDER BY de la définition

SELECT * 
FROM V_Race
ORDER BY espece; -- Sélection avec ORDER BY, c'est celui-là qui sera pris en compte

La première requête donnera bien les races par ordre alphabétique, les races de chiens et les races de chats sont mélangées. Par contre, dans la deuxième, on a d'abord toutes les races de chats, puis toutes les races de chiens.

Comportement d'autres clauses de SELECT

En ce qui concerne notamment les clauses LIMIT et DISTINCT (et son opposé, ALL), le comportement est indéfini. Dans le cas où l'on fait une requête avec un LIMIT sur une vue dont la requête possède aussi un LIMIT, on ne sait pas quelle clause, de la définition ou de la sélection, sera appliquée. Par conséquent, il est déconseillé d'inclure ces clauses dans la définition d'une vue.

Sélection des données d'une vue

Une fois la vue créée, on peut bien entendu faire plus qu'un simple SELECT * FROM la_vue; : on peut tout simplement traiter cette vue comme une table, et donc ajouter des clauses WHERE, GROUP BY, des fonctions, des jointures et tout ce que l'on veut !

Exemple 1 : on sélectionne les rats bruns à partir de la vue V_Animal_details.

1
2
3

SELECT id, nom, espece_nom, date_naissance, commentaires, disponible 
FROM V_Animal_details
WHERE espece_nom = 'Rat brun';

Exemple 2 : on sélectionne le nombre d'animaux par espèce avec la vue V_Nombre_espece, en ajoutant une jointure sur la table Espece pour avoir le nom des espèces en plus de leur id.

1
2
3

SELECT V_Nombre_espece.id, Espece.nom_courant, V_Nombre_espece.nb
FROM V_Nombre_espece
INNER JOIN Espece ON Espece.id = V_Nombre_espece.id;

Exemple 3 : on sélectionne le nombre de chiens qu'on possède pour chaque race, en utilisant un regroupement sur la vue V_Chien_race, avec jointure sur Race pour avoir le nom de la race.

1
2
3
4

SELECT Race.nom, COUNT(V_Chien_race.id)
FROM Race
INNER JOIN V_Chien_race ON Race.id = V_Chien_race.race_id
GROUP BY Race.nom;

Modification et suppression d'une vue

Modification

CREATE OR REPLACE

Comme déjà mentionné, pour modifier une vue, on peut tout simplement ajouter la clause OR REPLACE à la requête de création de la vue, et lui donner le nom de la vue à modifier. L'ancienne vue sera alors remplacée.

ALTER

Il existe aussi la commande ALTER VIEW, qui aura le même effet que CREATE OR REPLACE VIEW si la vue existe bel et bien. Dans le cas contraire, ALTER VIEW générera une erreur.

Syntaxe :

1
2

ALTER VIEW nom_vue [(liste_colonnes)]
AS requete_select

Exemples : les deux requêtes suivantes ont exactement le même effet : on modifie la vue V_espece_dollars pour mettre à jour le taux de change du dollar.

1
2
3
4
5
6
7

CREATE OR REPLACE VIEW V_Espece_dollars
AS SELECT id, nom_courant, nom_latin, description, ROUND(prix*1.30813, 2) AS prix_dollars
FROM Espece;

ALTER VIEW V_Espece_dollars
AS SELECT id, nom_courant, nom_latin, description, ROUND(prix*1.30813, 2) AS prix_dollars
FROM Espece;

Suppression

Pour supprimer une vue, on utilise simplement DROP VIEW [IF EXISTS] nom_vue;.

Exemple : suppression de V_Race.

1

DROP VIEW V_Race;

Utilité des vues

Au-delà de la légendaire paresse des informaticiens, bien contents de ne pas devoir retaper la même requête encore et encore, les vues sont utilisées pour différentes raisons, dont voici les principales.

Clarification et facilitation des requêtes

Lorsqu'une requête implique un grand nombre de tables, ou nécessite des fonctions, des regroupements, etc., même une bonne indentation ne suffit pas toujours à rendre la requête claire. Qui plus est, plus la requête est longue et complexe, plus l'ajout de la moindre condition peut se révéler pénible. Avoir une vue pour des requêtes complexes permet de simplifier et de clarifier les requêtes.

Exemple : on veut savoir quelles espèces rapportent le plus, année après année. Comme c'est une question importante pour le développement de l'élevage, et qu'elle reviendra souvent, on crée une vue, que l'on pourra interroger facilement.

1
2
3
4
5
6

CREATE OR REPLACE VIEW V_Revenus_annee_espece
AS SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id, SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
FROM Adoption
INNER JOIN Animal ON Animal.id = Adoption.animal_id
INNER JOIN Espece ON Animal.espece_id = Espece.id
GROUP BY annee, Espece.id;

Avec des requêtes toutes simples, on peut maintenant obtenir des informations résultant de requêtes complexes, par exemple :

1. Les revenus obtenus par année

1
2
3

SELECT annee, SUM(somme) AS total
FROM V_Revenus_annee_espece
GROUP BY annee;

2. Les revenus obtenus pour chaque espèce, toutes années confondues

1
2
3
4

SELECT Espece.nom_courant AS espece, SUM(somme) AS total
FROM V_Revenus_annee_espece
INNER JOIN Espece ON V_Revenus_annee_espece.espece_id = Espece.id
GROUP BY espece;

3. Les revenus moyens générés par la vente d'un individu de l'espèce

1
2
3
4

SELECT Espece.nom_courant AS espece, SUM(somme)/SUM(nb) AS moyenne
FROM V_Revenus_annee_espece
INNER JOIN Espece ON V_Revenus_annee_espece.espece_id = Espece.id
GROUP BY espece;

Création d'une interface entre l'application et la base de données

Lorsque l'on a une base de données exploitée par une application (écrite dans un langage de programmation comme Java, ou PHP par exemple), c'est souvent dans cette application que sont construites les requêtes qui vont insérer, modifier, et sélectionner les données de la base. Si pour une raison ou une autre (mauvaise conception de la base de données au départ, modèle de données qui s’étend fortement,…) la structure des tables de la base change, il faut réécrire également l'application pour prendre en compte les modifications nécessaires pour les requêtes.

Cependant, si l'on a utilisé des vues, on peut éviter de réécrire toutes ces requêtes, ou du moins limiter le nombre de requêtes à réécrire. Si les requêtes sont faites sur des vues, il suffit en effet de modifier la définition de ces vues pour qu'elles fonctionnent avec la nouvelle structure.

Exemple

On a créé une vue V_Client, qui permet de voir le contenu de notre table Client (sauf la date de naissance, ajoutée après la définition de la vue).

Si un beau jour on décidait de stocker les adresses postales dans une table à part, il faudrait modifier la structure de notre base. Au lieu d'une seule table Client, on en aurait deux : Client(id, nom, prenom, date_naissance, email, adresse_id) et Adresse(id, rue_numero, code_postal, ville, pays).

Pour que l'application puisse continuer à sélectionner les personnes et leur adresse sans qu'on doive la modifier, il suffirait de changer la requête définissant la vue :

1
2
3
4

CREATE OR REPLACE VIEW V_Client     -- le OR REPLACE indispensable (ou on utilise ALTER VIEW)
AS SELECT Client.id, nom, prenom, rue_numero AS adresse, code_postal, ville, pays, email, date_naissance
FROM Client
LEFT JOIN Adresse ON Client.adresse_id = Adresse.id -- LEFT JOIN au cas où certains clients n'auraient pas d'adresse définie

Le changement de structure de la base de données serait ainsi transparent pour l'application (du moins en ce qui concerne la sélection des clients) !

Restriction des données visibles par les utilisateurs

La gestion des utilisateurs et de leurs droits fera l'objet d'un prochain chapitre. Sachez simplement que pour chaque utilisateur, il est possible de définir des droits particuliers pour chaque table et chaque vue (entre autres choses). On peut par exemple autoriser l'utilisateur A à faire des requêtes d'insertion, modification, suppression et sélection (le fameux CRUD) sur la table T1, mais n'autoriser l'utilisateur B qu'à faire des sélections sur la table T1. Et imaginons qu'il existe aussi un utilisateur C, auquel on veut donner l'autorisation de faire des requêtes de sélection sur la table T1, mais auquel il faudrait cacher certaines colonnes qui contiennent des données sensibles, ou certaines lignes auxquelles il ne devrait pas accéder. Il suffit de créer la vue V_T1, n'ayant accès qu'aux colonnes/lignes "publiques" de la table T1, et de donner à C les droits sur la vue V_T1, mais pas sur la table T1.

Exemple

Le stagiaire travaillant dans notre élevage s'occupe exclusivement des chats, et ne doit pas avoir accès aux commentaires. On ne lui donne donc pas accès à la table Animal, mais à une vue V_Animal_stagiaire créée de la manière suivante :

1
2
3
4

CREATE VIEW V_Animal_stagiaire
AS SELECT id, nom, sexe, date_naissance, espece_id, race_id, mere_id, pere_id, disponible
FROM Animal
WHERE espece_id = 2;

Algorithmes

Lors de la création d'une vue, on peut définir quel algorithme sera utilisé par MySQL lors d'une sélection sur celle-ci.

1
2
3
4

CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW nom_vue
    AS requete_select

Il s'agit d'une clause non standard, donc valable uniquement pour MySQL. Deux algorithmes différents peuvent être utilisés : MERGE et TEMPTABLE.

Les algorithmes interviennent au moment où l'on sélectionne des données de la vue, et pas directement à la création de celle-ci.

MERGE

Si l'algorithme MERGE (fusion en anglais) a été choisi, lorsque l'on sélectionne des données de la vue, MySQL va fusionner la requête SELECT qui définit la vue avec les clauses de sélections. Faire une sélection sur une vue qui utilise cet algorithme revient donc à faire une requête directement sur les tables sous-jacentes.

Comme ce n'est pas nécessairement très clair, voici deux exemples.

Exemple 1

On a créé la vue V_Animal_details à partir de la requête suivante :

1
2
3
4
5
6

SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant AS espece_nom, Race.nom AS race_nom
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id;

Ensuite, on fait une sélection sur cette vue :

1
2
3

SELECT * 
FROM V_Animal_details
WHERE MONTH(date_naissance) = 6;

Si c'est l'algorithme MERGE qui est utilisé, MySQL va fusionner :

• la requête définissant la vue :

1
2
3
4
5
6

SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant AS espece_nom, Race.nom AS race_nom
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id;

• les clauses de la requête de sélection :

1

WHERE MONTH(date_naissance) = 6;

Au final, faire cette requête sur la vue provoquera l'exécution de la requête suivante :

1
2
3
4
5
6
7

SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant AS espece_nom, Race.nom AS race_nom
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id
WHERE MONTH(date_naissance) = 6;

Exemple 2

Si l'on exécute la requête suivante sur la vue V_Chien, et que l'algorithme MERGE est utilisé :

1
2
3

SELECT nom, date_naissance 
FROM V_Chien
WHERE pere_id IS NOT NULL;

MySQL va fusionner les deux requêtes, et exécuter ceci :

1
2
3
4

SELECT nom, date_naissance
FROM Animal
WHERE espece_id = 1
AND pere_id IS NOT NULL;

TEMPTABLE

L'algorithme TEMPTABLE, par contre, crée une table temporaire contenant les résultats de la requête définissant la vue puis, par la suite, exécute la requête de sélection sur cette table temporaire.

Donc, si l'on exécute la requête suivante sur la vue V_Chien :

1
2
3

SELECT nom, date_naissance 
FROM V_Chien
WHERE pere_id IS NOT NULL;

Avec l'algorithme TEMPTABLE, la requête définissant la vue va être exécutée et ses résultats stockés dans une table temporaire.

1
2
3

SELECT id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible
FROM Animal
WHERE espece_id = 1;

Ensuite, sur cette table temporaire, va être exécutée la requête finale :

1
2
3

SELECT nom, date_naissance 
FROM table_temporaire 
WHERE pere_id IS NOT NULL;

Algorithme par défaut et conditions

Il existe une troisième option possible pour la clause ALGORITHM dans la requête de création des vues : UNDEFINED.

Par défaut, si on ne précise pas d'algorithme pour la vue, c'est l'option UNDEFINED qui est utilisée. Cette option laisse MySQL décider lui-même de l'algorithme qu'il appliquera. Si c'est possible, MERGE sera utilisé, car cet algorithme est plus performant que TEMPTABLE. Cependant, toutes les vues ne peuvent pas utiliser l'algorithme MERGE. En effet, une vue utilisant un ou plusieurs des éléments suivants ne pourra pas utiliser MERGE :

• DISTINCT ;
• LIMIT ;
• une fonction d'agrégation (SUM(), COUNT(), MAX(), etc.) ;
• GROUP BY ;
• HAVING ;
• UNION ;
• une sous-requête dans la clause SELECT.

Modification des données d'une vue

On a tendance à penser que les vues ne servent que pour la sélection de données. En réalité, il est possible de modifier, insérer et supprimer des données par l'intermédiaire d'une vue. Les requêtes sont les mêmes que pour insérer, modifier et supprimer des données à partir d'une table (si ce n'est qu'on met le nom de la vue au lieu du nom de la table bien sûr).

Cependant, pour qu'une vue ne soit pas en "lecture seule", elle doit répondre à une série de conditions.

Conditions pour qu'une vue permette de modifier des données (requêtes UPDATE)

Jointures

Il est possible de modifier des données à partir d'une vue définie avec une jointure, à condition de ne modifier qu'une seule table.

Exemple

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Modifie Animal
UPDATE V_Animal_details
SET commentaires = 'Rhume chronique'
WHERE id = 21;

-- Modifie Race
UPDATE V_Animal_details 
SET race_nom = 'Maine Coon'
WHERE race_nom = 'Maine coon';

-- Erreur
UPDATE V_Animal_details 
SET commentaires = 'Vilain oiseau', espece_nom = 'Perroquet pas beau' -- commentaires vient de Animal, et espece_nom vient de Espece
WHERE espece_id = 4;

Les deux premières modifications ne posent aucun problème, mais la troisième échouera, car elle modifie des colonnes appartenant à deux tables différentes.

1

ERROR 1393 (HY000): Can not modify more than one base table through a join view 'elevage.v_animal_details'

Algorithme

La vue doit utiliser l'algorithme MERGE (que l'algorithme ait été spécifié à la création, ou choisi par MySQL si l'algorithme n'a pas été défini). Par conséquent, les mêmes conditions que pour utiliser l'algorithme MERGE s'appliquent. Les éléments suivants sont ainsi interdits dans une vue si l'on veut pouvoir modifier les données à partir de celle-ci :

• DISTINCT ;
• LIMIT ;
• une fonction d’agrégation (SUM(), COUNT(), MAX(), etc.) ;
• GROUP BY ;
• HAVING ;
• UNION ;
• une sous-requête dans la clause SELECT.

Exemple : la vue V_Nombre_espece, qui utilise une fonction d'agrégation, ne permet pas de modifier les données.

1
2
3

UPDATE V_Nombre_espece
SET nb = 6
WHERE id = 4;

1

1288 (HY000): The target table V_Nombre_espece of the UPDATE is not updatable

Autres conditions

• On ne peut pas modifier les données à partir d'une vue qui est elle-même définie à partir d'une vue qui ne permet pas la modification des données.
• Ce n'est pas non plus possible à partir d'une vue dont la clause WHERE contient une sous-requête faisant référence à une des tables de la clause FROM.

Conditions pour qu'une vue permette d'insérer des données (requêtes INSERT)

On peut insérer des données dans une vue si celle-ci respecte les mêmes conditions que pour la modification de données, ainsi que les conditions supplémentaires suivantes.

Valeurs par défaut

Toutes les colonnes n'ayant pas de valeur par défaut (et ne pouvant pas être NULL) de la table dans laquelle on veut faire l'insertion doivent être référencées par la vue.

Exemple

1
2

INSERT INTO V_Animal_stagiaire (nom, sexe, date_naissance, espece_id, race_id)
VALUES ('Rocco', 'M', '2012-03-12', 1, 9);

Ceci fonctionne. La colonne commentaires n'est pas référencée par V_Animal_stagiaire mais peut être NULL (qui est donc sa valeur par défaut).

1
2
3
4
5
6

CREATE VIEW V_Animal_mini
AS SELECT id, nom, sexe, espece_id
FROM Animal;

INSERT INTO V_Animal_mini(nom, sexe, espece_id)
VALUES ('Toxi', 'F', 1);

Par contre, l'insertion dans V_Animal_mini échoue puisque date_naissance n'est pas référencée, ne peut pas être NULL, et n'a pas de valeur par défaut.

1

ERROR 1423 (HY000): Field of view 'elevage.v_animal_mini' underlying table doesn't have a default value

Jointures

Les vues avec jointure peuvent supporter l'insertion si :

• il n'y a que des jointures internes ;
• l'insertion se fait sur une seule table (comme pour la modification).

Exemple

1
2

INSERT INTO V_Animal_details (espece_nom, espece_nom_latin)
VALUES ('Perruche terrestre', 'Pezoporus wallicus');

Il y a une jointure externe dans V_Animal_details, donc l'insertion ne fonctionnera pas.

1

ERROR 1471 (HY000): The target table V_Animal_details of the INSERT is not insertable-into

Par contre, si l'on crée une table V_Animal_espece, avec uniquement une jointure interne, il n'y a aucun problème.

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE VIEW V_Animal_espece
AS SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.commentaires, 
       Animal.espece_id, Animal.race_id, Animal.mere_id, Animal.pere_id, Animal.disponible,
       Espece.nom_courant AS espece_nom, Espece.nom_latin AS espece_nom_latin
FROM Animal
INNER JOIN Espece ON Espece.id = Animal.espece_id;

INSERT INTO V_Animal_espece (espece_nom, espece_nom_latin)
VALUES ('Perruche terrestre', 'Pezoporus wallicus');

Expressions

Les colonnes de la vue doivent être de simples références à des colonnes, et non pas des expressions.

Exemple

Dans la vue V_Espece_dollars, la colonne prix_dollars correspond à ROUND(prix*1.30813, 2). Il n'est donc pas possible d'insérer des données à partir de cette vue.

1
2

INSERT INTO V_Espece_dollars (nom_courant, nom_latin, prix_dollars)
VALUES ('Perruche terrestre', 'Pezoporus wallicus', 30);

1

ERROR 1471 (HY000): The target table V_Espece_dollars of the INSERT is not insertable-into

Colonnes dupliquées

La même colonne ne peut pas être référencée deux fois dans la vue.

Exemple

Si l'on crée une vue avec deux fois la même colonne référencée, il est possible de modifier des données à partir de celle-ci, mais pas d'en insérer.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE VIEW V_Espece_2noms
AS SELECT id, nom_courant, nom_latin, description, prix, nom_courant AS nom2 -- nom_courant est référencé deux fois
FROM Espece;

-- Modification, pas de problème
UPDATE V_Espece_2noms
SET description= 'Joli oiseau aux plumes majoritairement vert brillant', prix = 20.00
WHERE nom_courant = 'Perruche terrestre';

-- Insertion, impossible
INSERT INTO V_Espece_2noms (nom_courant, nom_latin, prix)
VALUES ('Perruche turquoisine', 'Neophema pulchella', 40);

1

ERROR 1471 (HY000): The target table V_Espece_2noms of the INSERT is not insertable-into

Conditions pour qu'une vue permette de supprimer des données (requêtes DELETE)

Il est possible de supprimer des données à partir d'une vue si et seulement si :

• il est possible de modifier des données à partir de cette vue ;
• cette vue est "mono-table" (une seule table sous-jacente).

Option de la vue pour la modification des données

Lors de la création d'une vue, on peut ajouter une option : WITH [LOCAL | CASCADED] CHECK OPTION.

1
2
3
4
5

CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW nom_vue [(liste_colonnes)]
    AS requete_select
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Lorsque cette option est spécifiée, les modifications et insertions ne sont acceptées que si les données répondent aux conditions de la vue (c'est-à-dire aux conditions données par la clause WHERE de la requête définissant la vue).

Exemples

Si la vue V_Animal_stagiaire (qui, pour rappel, sélectionne les chats uniquement) est définie avec WITH CHECK OPTION, on ne peut pas modifier l'espece_id à partir de cette vue.

1
2
3
4
5

CREATE OR REPLACE VIEW V_Animal_stagiaire
AS SELECT id, nom, sexe, date_naissance, espece_id, race_id, mere_id, pere_id, disponible
FROM Animal
WHERE espece_id = 2
WITH CHECK OPTION;

En effet, cette vue est définie avec la condition WHERE espece_id = 2. Les modifications faites sur les données de cette vue doivent respecter cette condition.

1
2
3

UPDATE V_Animal_stagiaire 
SET espece_id = 1
WHERE nom = 'Cracotte';

1

ERROR 1369 (HY000): CHECK OPTION failed 'elevage.v_animal_stagiaire'

De même, l'insertion d'un animal dont l'espece_id n'est pas 2 sera refusée aussi.

1
2
3
4
5

INSERT INTO V_Animal_stagiaire (sexe, date_naissance, espece_id, nom)
VALUES ('F', '2011-09-21 15:14:00', 2, 'Bambi');       -- c'est un chat, pas de problème

INSERT INTO V_Animal_stagiaire (sexe, date_naissance, espece_id, nom)
VALUES ('M', '2011-03-11 05:54:00', 6, 'Tiroli');       -- c'est une perruche, impossible

La première insertion ne pose aucun problème, mais la seconde échoue.

LOCAL ou CASCADED

• LOCAL : seules les conditions de la vue même sont vérifiées.
• CASCADED : les conditions des vues sous-jacentes éventuelles sont également vérifiées. C'est l'option par défaut.

La vue V_Chien_race, par exemple, est définie à partir de la vue V_Chien. Si on ajoute WITH LOCAL CHECK OPTION à V_Chien_race, les modifications et insertions dans cette vue devront vérifier uniquement les conditions de la vue elle-même, c'est-à-dire race_id IS NOT NULL. Si par contre, c'est WITH CASCADED CHECK OPTION qui est choisi, alors les modifications et insertions devront toujours vérifier les conditions de V_Chien_race, mais également celles de V_Chien (espece_id = 1).

Exemple 1 : LOCAL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE OR REPLACE VIEW V_Chien_race
AS SELECT id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible 
FROM V_Chien
WHERE race_id IS NOT NULL
WITH LOCAL CHECK OPTION;

-- Modification --
-- ------------ --
UPDATE V_Chien_race 
SET race_id = NULL             -- Ne respecte pas la condition de V_Chien_race 
WHERE nom = 'Zambo';           -- => Impossible

UPDATE V_Chien_race
SET espece_id = 2, race_id = 4 -- Ne respecte pas la condition de V_Chien 
WHERE nom = 'Java';           -- => possible puisque LOCAL CHECK OPTION

-- Insertion --
-- --------- --
INSERT INTO V_Chien_race (sexe, date_naissance, nom, commentaires, espece_id, race_id)   -- Respecte toutes les conditions
VALUES ('M', '2012-02-28 03:05:00', 'Pumba', 'Prématuré, à surveiller', 1, 9);           -- => Pas de problème

INSERT INTO V_Chien_race (sexe, date_naissance, nom, commentaires, espece_id, race_id)   -- La race n'est pas NULL, mais c'est un chat
VALUES ('M', '2011-05-24 23:51:00', 'Lion', NULL, 2, 5);                                 -- => pas de problème puisque LOCAL

INSERT INTO V_Chien_race (sexe, date_naissance, nom, commentaires, espece_id, race_id)   -- La colonne race_id est NULL
VALUES ('F', '2010-04-28 13:01:00', 'Mouchou', NULL, 1, NULL);                           -- => impossible

La première modification et la dernière insertion échouent donc avec l'erreur suivante :

1

ERROR 1369 (HY000): CHECK OPTION failed 'elevage.v_chien_race'

Exemple 2 : CASCADED

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE OR REPLACE VIEW V_Chien_race
AS SELECT id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible
FROM V_Chien
WHERE race_id IS NOT NULL
WITH CASCADED CHECK OPTION;

UPDATE V_Chien_race 
SET race_id = NULL              -- Ne respecte pas la condition de V_Chien_race 
WHERE nom = 'Zambo';            -- => impossible

UPDATE V_Chien_race
SET espece_id = 2, race_id = 4  -- Ne respecte pas la condition de V_Chien 
WHERE nom = 'Fila';             -- => impossible aussi puisque CASCADED

Cette fois, les deux modifications échouent.

En résumé

• Une vue est une requête SELECT que l'on stocke et à laquelle on donne un nom.
• La requête SELECT stockée dans une vue peut utiliser des jointures, des clauses WHERE, GROUP BY, des fonctions (scalaires ou d’agrégation), etc. L'utilisation de DISTINCT et LIMIT est cependant déconseillée.
• On peut sélectionner les données à partir d'une vue de la même manière qu'on le fait à partir d'une table. On peut donc utiliser des jointures, des fonctions, des GROUP BY, des LIMIT,…
• Les vues permettent de simplifier les requêtes, de créer une interface entre l'application et la base de données, et/ou restreindre finement l'accès en lecture des données aux utilisateurs.
• Sous certaines conditions, il est possible d'insérer, modifier et supprimer des données à partir d'une vue.

• Au-delà des tables classiques : vues, tables temporaires et vues matérialisées
• Sommaire
• Tables temporaires