Cours MariaDB / Créer et utiliser une vue avec CREATE VIEW

Une vue MariaDB, créée avec CREATE VIEW, enregistre une définition SELECT sous un nom que d’autres requêtes peuvent utiliser. Une vue ordinaire ne conserve pas une copie indépendante de ses lignes : vérifiez ses dépendances, les privilèges utilisés et les possibilités de modification avant de la traiter comme une table.

Distinguer une vue persistante d’une CTE

Si plusieurs rapports calculent les mêmes montants de produits par commande payée, une vue permet de partager la définition de ce calcul. Une expression de table commune, ou CTE (Common Table Expression), donne un nom à une étape dans une seule instruction ; une vue créée reste utilisable dans les instructions suivantes. Choisissez aussi les colonnes et leurs noms en fonction des applications qui les liront.

Objet Ce qu’il conserve Durée
Vue ordinaire Une définition SELECT Jusqu’à sa suppression avec DROP VIEW
CTE Un nom de requête intermédiaire Dans l’instruction qui la contient
Table Des lignes enregistrées Selon les opérations et la politique de conservation

Pour un nom utile seulement à l’intérieur d’une instruction, comparez les étapes de requête avec WITH et les CTE. La référence CREATE VIEW de MariaDB précise la syntaxe, les droits nécessaires et le contexte de sécurité.

Préparer les tables et vérifier le nom de la vue

Connectez-vous au serveur d’apprentissage avec course_user et sélectionnez shopcourse. Le compte de cet exercice doit disposer des droits nécessaires pour créer, remplir, lire et nettoyer les tables, ainsi que des privilèges CREATE VIEW et SHOW VIEW pour créer et examiner la vue. Sa suppression demande le privilège DROP sur la vue. Dans un système réel, adaptez ces droits au rôle de chaque compte.

Le guide du client MariaDB et des fichiers SQL UTF-8 décrit la connexion et l’exécution de fichiers. Créez un dossier d’exercice et enregistrez le bloc complet suivant en UTF-8, par exemple dans C:/mariadb-lab/c34-vues.sql. Dans le client MariaDB, lancez SOURCE C:/mariadb-lab/c34-vues.sql; avec le chemin réel. Exécutez le SQL dans la connexion au serveur, pas directement dans l’invite du système.

Contrôlez d’abord le serveur et la base avec SELECT VERSION(), DATABASE();, puis les objets avec SHOW FULL TABLES;. Les tables c34_customers, c34_products, c34_orders, c34_items et la vue c34_paid_order_totals utilisent des noms réservés à cet exercice. Tables et vues partagent le même espace de noms dans la base : vérifiez donc aussi qu’une table ne porte pas le nom prévu pour la vue.

Si un objet existe déjà, examinez sa définition et ses données avant de le modifier. Pour une vue existante, conservez sa définition avec SHOW CREATE VIEW. Cet exercice crée une nouvelle vue ; il n’utilise pas CREATE OR REPLACE pour écraser automatiquement une définition non examinée. Ne supprimez que des objets d’exercice dont aucune donnée n’est à conserver.

USE shopcourse;
CREATE TABLE c34_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c34_order_customer FOREIGN KEY (customer_id)
  REFERENCES c34_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c34_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c34_item_order FOREIGN KEY (order_id)
  REFERENCES c34_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c34_item_product FOREIGN KEY (product_id)
  REFERENCES c34_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c34_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c34_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c34_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c34_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Vous obtenez quatre clients, quatre produits, quatre commandes et six lignes de détail. Les noms et villes coréens sont conservés pour reproduire les mêmes résultats entre langues. Les produits 1 à 4 représentent un carnet, un stylo, une tasse et une lampe ; les clients 1 et 3 ont la ville Séoul, les clients 2 et 4 Busan et Daejeon. Gardez les littéraux UTF-8 et suivez les identifiants.

Créer et interroger une vue SQL SECURITY INVOKER

La définition ci-dessous joint les commandes et leurs détails, conserve les commandes paid, puis additionne la quantité multipliée par le prix unitaire enregistré à l’achat. Elle produit une ligne par commande. Le montant exclut les frais de livraison et la commande annulée 104.

CREATE SQL SECURITY INVOKER VIEW c34_paid_order_totals AS
SELECT o.order_id,o.customer_id,o.ordered_at,
 SUM(i.quantity*i.unit_price) AS goods_total
FROM c34_orders o JOIN c34_items i ON i.order_id=o.order_id
WHERE o.status='paid' GROUP BY o.order_id,o.customer_id,o.ordered_at;

Interrogez ensuite la vue et affichez sa définition enregistrée :

SELECT order_id,goods_total FROM c34_paid_order_totals ORDER BY order_id;
SHOW CREATE VIEW c34_paid_order_totals;
order_id goods_total
101 11500.00
102 8000.00
103 19500.00

Le SELECT renvoie ces trois lignes ; SHOW CREATE VIEW affiche ensuite la définition, avec ses attributs et son contexte de sécurité. La représentation affichée peut normaliser la requête, qualifier les noms et montrer un compte définisseur. Vérifiez surtout la définition attendue et SQL SECURITY INVOKER. Spécifiez ORDER BY dans la requête qui utilise la vue : ne supposez pas que la vue impose à elle seule l’ordre des résultats.

Avec INVOKER, les privilèges sur les objets sous-jacents sont contrôlés selon le compte qui appelle la vue. Autoriser seulement SELECT sur la vue ne dispense donc pas cet utilisateur des droits nécessaires sur ses sources. La vue reste un objet nommé, mais ce choix ne crée pas une voie d’accès privilégiée aux tables.

Observer une modification de source et son annulation

Dans la même connexion et avec les tables InnoDB créées plus haut, exécutez tout le bloc suivant. La modification porte précisément sur le produit 1 de la commande 101, dont la quantité passe de deux à trois.

START TRANSACTION;
UPDATE c34_items SET quantity=3 WHERE order_id=101 AND product_id=1;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;
ROLLBACK;
SELECT goods_total FROM c34_paid_order_totals WHERE order_id=101;

Le premier SELECT donne 15000.00 : un carnet supplémentaire à 3500 s’ajoute au montant initial de 11500. Après ROLLBACK, le second donne de nouveau 11500.00. La vue relit les données de base visibles dans la transaction ; vous n’avez pas modifié une copie historique stockée dans la vue. Vérifiez ces deux valeurs et la quantité initiale avant de répéter l’essai.

Ce résultat décrit une seule connexion. Ce qu’une autre connexion voit dépend notamment du niveau d’isolation, de sa transaction et de la validation des changements. Ne généralisez pas cet essai à une lecture simultanée sans tenir compte de ces conditions. Gardez aussi les créations et suppressions d’objets en dehors de cet essai d’annulation des données.

Examiner la sécurité, les modifications et les performances

Cette vue contient SUM et GROUP BY : elle ne permet pas de mettre à jour les lignes sources par un UPDATE de la vue. Certaines vues simples sont modifiables, sous conditions ; cela ne rend pas toutes les vues modifiables. La documentation sur les insertions et modifications par une vue détaille ces limites. Pour cet exemple, séparez la mise à jour des tables de base du rapport agrégé.

Le contexte SQL SECURITY DEFINER utilise au contraire les droits du définisseur pour les accès concernés. Ce choix doit être volontaire : le compte, ses droits et sa présence dans l’environnement de restauration deviennent des dépendances. Une définition restaurée qui attend un compte absent peut échouer à l’utilisation. Examinez les attributs de la vue lors des sauvegardes et restaurations, plutôt que de supposer qu’un nom de vue suffit à préserver les accès.

Une vue ordinaire ne met pas automatiquement son résultat en cache et ne garantit pas une accélération. Les jointures et agrégats restent à évaluer selon les données et le plan d’exécution. Réutiliser une définition améliore la cohérence des requêtes ; pour juger le coût d’un rapport, examinez son plan et mesurez-le dans les conditions pertinentes.

Préserver les dépendances et supprimer la vue avant les tables

Renommer ou supprimer une colonne exposée peut casser les requêtes qui utilisent la vue. Préférez une liste explicite de colonnes à SELECT *, conservez la définition actuelle et examinez ses consommateurs avant un changement. MariaDB fixe la définition lors de la création : une colonne ajoutée ensuite à une table ne devient pas automatiquement une nouvelle colonne d’une vue créée auparavant avec SELECT *.

Lors du nettoyage, commencez par la vue pour ne pas la laisser référencer des tables supprimées. Selon la référence DROP VIEW, cette instruction supprime la vue. Dans cet exercice, elle ne supprime pas les lignes des tables de base :

DROP VIEW shopcourse.c34_paid_order_totals;

Si vous conservez les tables, vous pouvez vérifier leurs lignes avec des SELECT après cette opération. Pour les supprimer aussi, contrôlez la base, les définitions et les données réelles, et sauvegardez ce qui doit être conservé. Ne nettoyez que les objets jetables de cet exercice, en supprimant les enfants avant les tables référencées. DROP TABLE enlève la structure et les données et n’est pas annulé par un ROLLBACK ordinaire.

DROP TABLE shopcourse.c34_items;
DROP TABLE shopcourse.c34_orders;
DROP TABLE shopcourse.c34_products;
DROP TABLE shopcourse.c34_customers;