• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

course.header.alt.is_certifying

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 22/05/2019

Vues

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

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 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és par les vues ;

  • comment modifier les données à partir d'une vue.

État actuel de la base de données

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

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\", \"A\" 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'       
    AND NEW.sexe != 'A'
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : sexe doit valoir "M", "F", "A" 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' 
    AND NEW.sexe != 'A'
      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

Le principe

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

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 que l'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 termes de performances 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 :

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 :

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 :

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'agisse pas d'une table, mais d'une requête SELECT  stockée, j'insiste).

SHOW TABLES;

Tables_in_elevage

Adoption

Animal

Animal_histo

Client

Erreur

Espece

Race

V_Animal_details

Les colonnes de la vue

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

DESCRIBE V_Animal_details;

Field

Type

Null

Key

Default

Extra

id

smallint(6) unsigned

NO

 

0

 

sexe

char(1)

YES

 

NULL

 

date_naissance

datetime

NO

 

NULL

 

nom

varchar(30)

YES

 

NULL

 

commentaires

text

YES

 

NULL

 

espece_id

smallint(6) unsigned

NO

 

NULL

 

race_id

smallint(6) unsigned

YES

 

NULL

 

mere_id

smallint(6) unsigned

YES

 

NULL

 

pere_id

smallint(6) unsigned

YES

 

NULL

 

disponible

tinyint(1)

YES

 

1

 

espece_nom

varchar(40)

NO

 

NULL

 

race_nom

varchar(40)

YES

 

NULL

 

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 le recours aux 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.

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.

CREATE VIEW V_test 
AS SELECT Animal.id, Espece.id 
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id;
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

Que peut-on mettre dans la requête SELECT  qui sert à définir 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 cela, 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.

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.

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.

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.

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.

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 clauseSELECT. 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.

ALTER TABLE Client ADD COLUMN date_naissance DATE;

DESCRIBE V_Client;

Field

Type

Null

Key

Default

Extra

id

smallint(5) unsigned

NO

 

0

 

nom

varchar(100)

NO

 

NULL

 

prenom

varchar(60)

NO

 

NULL

 

adresse

varchar(200)

YES

 

NULL

 

code_postal

varchar(6)

YES

 

NULL

 

ville

varchar(60)

YES

 

NULL

 

pays

varchar(60)

YES

 

NULL

 

email

varbinary(100)

YES

 

NULL

 

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

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 seront 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 WHEREGROUP 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.

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

id

nom

espece_nom

date_naissance

commentaires

disponible

69

Baba

Rat brun

2012-02-13 15:45:00

NULL

0

70

Bibo

Rat brun

2012-02-13 15:48:00

Agressif

1

72

Momy

Rat brun

2008-02-01 02:25:00

NULL

1

73

Popi

Rat brun

2007-03-11 12:45:00

NULL

1

75

Mimi

Rat brun

2007-03-12 22:03:00

NULL

0

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.

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;

id

nom_courant

nb

1

Chien

21

2

Chat

20

3

Tortue d'Hermann

14

4

Perroquet amazone

4

5

Rat brun

5

Exemple 3 : on sélectionne le nombre de chiens que l'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.

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;

nom

COUNT(V_Chien_race.id)

Berger allemand

8

Berger blanc suisse

4

Rottweiller

1

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 :

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.

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.

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.

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

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

annee

total

2007

2315.00

2008

3565.00

2009

400.00

2010

340.00

2011

1755.00

2012

3045.00

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

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;

espece

total

Chat

6480.00

Chien

2400.00

Perroquet amazone

2100.00

Rat brun

20.00

Tortue d'Hermann

420.00

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

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;

espece

moyenne

Chat

720.000000

Chien

342.857143

Perroquet amazone

700.000000

Rat brun

10.000000

Tortue d'Hermann

140.000000

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 que l'on doive la modifier, il suffit de changer la requête définissant la vue :

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, de modification, de suppression et de 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 :

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.

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élection. 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 :

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 :

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 :

    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 :

    WHERE MONTH(date_naissance) = 6;

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

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é :

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

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

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 :

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.

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 :

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 l'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, d'insérer et de 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 que l'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

-- 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.

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.

UPDATE V_Nombre_espece
SET nb = 6
WHERE id = 4;
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

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).

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 NULLet n'a pas de valeur par défaut.

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

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.

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.

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.

INSERT INTO V_Espece_dollars (nom_courant, nom_latin, prix_dollars)
VALUES ('Perruche terrestre', 'Pezoporus wallicus', 30);
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.

CREATE VIEW V_Espece_2noms -- on référence nom_courant deux fois
AS SELECT id, nom_courant, nom_latin, description, prix, nom_courant AS nom2 
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);
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.

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.

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.

UPDATE V_Animal_stagiaire 
SET espece_id = 1
WHERE nom = 'Cracotte';
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.

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 l'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

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)   
VALUES ('M', '2012-02-28 03:05:00', 'Pumba', 'Prématuré, à surveiller', 1, 9);           
-- Respecte toutes les conditions => Pas de problème

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

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

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

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

Exemple 2 : CASCADED

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 WHEREGROUP 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 de restreindre finement l'accès en lecture des données aux utilisateurs.

  • Sous certaines conditions, il est possible d'insérer, de modifier et de supprimer des données à partir d'une vue.

Exemple de certificat de réussite
Exemple de certificat de réussite