Vues (VIEW) — Définition et cas d'usage

Données utilisées dans cet article — employee / department / sales

Une vue (VIEW) est une instruction SELECT nommée et stockée que tu peux référencer comme une table.

Contrairement à une table, une vue ne contient pas ses propres données — chaque fois que tu la références, le SELECT stocké est ré-exécuté.

Avant de plonger dans les exercices, jette un œil aux définitions de colonnes et aux données d'exemple des trois tables utilisées dans cet article — employee / department / sales.

① Exécute PRAGMA table_info(employee); / PRAGMA table_info(department); / PRAGMA table_info(sales); pour vérifier les définitions de colonnes des trois tables.

② Exécute SELECT * FROM employee LIMIT 5; / SELECT * FROM department LIMIT 5; / SELECT * FROM sales LIMIT 5; pour prévisualiser les cinq premières lignes de chaque table.

CREATE VIEW — Donner un nom à une requête complexe

Définis une vue avec CREATE VIEW nom_vue AS SELECT ...;.

Une fois définie, écrire SELECT * FROM nom_vue exécute le SELECT stocké et renvoie son résultat.

Comme une vue est une requête stockée sans données propres, toute mise à jour des tables sous-jacentes apparaît la prochaine fois que tu référeras à la vue.

Quand tu n'en as plus besoin, supprime-la avec DROP VIEW nom_vue;.

Si tu veux recréer une vue avec le même nom, exécute DROP VIEW IF EXISTS nom_vue; avant CREATE VIEW pour que le script reste réexécutable.

-- Nommer une requête de comptage par département en tant que vue
DROP VIEW IF EXISTS dept_headcount;
CREATE VIEW dept_headcount AS
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- Après la définition, on la référence comme une table
SELECT dept_name, headcount FROM dept_headcount
ORDER BY headcount DESC;

-- La supprimer une fois qu'on a fini
DROP VIEW dept_headcount;

Imagine l'exigence : « Je veux voir une liste d'employés avec le nom et le lieu du département à côté de chaque ligne, sans écrire le JOIN à chaque fois. » À l'intérieur d'une seule exécution, prends la vue de la définition à la référence puis à la suppression comme un script autonome. (Quand il s'exécute correctement, l'explication apparaît.)

① Définis une vue nommée emp_dept qui fait un LEFT JOIN d'employee et department sur dept_id et renvoie emp_id, name, salary, dept_name et location. Mets avant la définition une instruction qui supprime toute vue existante du même nom, pour que le script ne plante jamais à la réexécution.

② Extrais name, dept_name et salary de la vue et affiche les 5 premières lignes triées par salary décroissant.

③ Pour finir, supprime la vue pour que sa définition ne traîne pas sur la page.

Trois cas d'usage — réutilisation, autorisation, abstraction

Il y a trois raisons principales d'utiliser une vue.

Réutilisation : regroupe un JOIN ou une agrégation souvent utilisé sous un seul nom pour ne pas avoir à réécrire la même requête encore et encore.

Autorisation : crée une vue qui n'expose qu'un sous-ensemble de colonnes ou de lignes, et donne accès à la vue plutôt qu'à la table sous-jacente (masquer les colonnes de salaire élevé, n'afficher que les lignes du département de l'utilisateur, etc.).

Abstraction : les appelants n'ont pas besoin de connaître la structure interne de la vue (quelles tables elle joint, comment) — connaître le nom de la vue et les colonnes qu'elle renvoie suffit.

-- (1) Abstraction : une vue qui étiquette les employés par cohorte d'année d'embauche (les appelants ne voient pas l'expression)
DROP VIEW IF EXISTS emp_cohort;
CREATE VIEW emp_cohort AS
SELECT name, hired_on,
  CASE
    WHEN hired_on < '2018-01-01' THEN 'Veteran'
    WHEN hired_on < '2021-01-01' THEN 'Mid'
    ELSE 'Recent'
  END AS cohort
FROM employee;

SELECT cohort, COUNT(*) AS cnt
FROM emp_cohort
GROUP BY cohort
ORDER BY cohort;

DROP VIEW emp_cohort;

-- (2) Autorisation : une vue qui exclut le salaire et n'expose que les colonnes publiables
DROP VIEW IF EXISTS emp_public;
CREATE VIEW emp_public AS
SELECT emp_id, name, dept_id FROM employee;
DROP VIEW emp_public;

Imagine cette exigence : « Je veux regrouper en un seul endroit la logique qui classe les employés en tranches de salaire (High / Mid / Low), et confirmer que mettre à jour la table employee sous-jacente fait se mettre à jour automatiquement le résultat agrégé de la vue. » Comme une vue est un SELECT stocké sans données propres, elle ré-évalue la table sous-jacente chaque fois que tu la référeras.

① Supprime toute vue existante du même nom, puis définis une vue emp_tier avec une colonne tier qui renvoie High quand salary vaut 6 500 000 ou plus, Mid quand il est entre 5 000 000 et 6 500 000, et Low en dessous.

② Agrège l'effectif par tier sous l'alias cnt, et vérifie les comptes avant la mise à jour.

③ INSÈRE un nouvel employé dans employee (emp_id 999 / name 'Zoe' / dept_id 1 / manager_id NULL / city 'Tokyo' / salary 8000000 / hired_on '2024-01-01'), qui atterrit dans la tranche High.

④ Exécute la même requête d'agrégation qu'au ②, et confirme que le compte High a augmenté de 1 (preuve qu'une mise à jour de la table sous-jacente apparaît immédiatement à travers la vue).

⑤ Nettoie en supprimant la ligne avec emp_id = 999 et en supprimant la vue.

Les vues d'agrégation raccourcissent la requête de l'appelant

Les vues peuvent stocker des agrégations GROUP BY, pas seulement des JOIN.

Si tu regroupes une agrégation souvent utilisée — comme le total des ventes par employé — dans une seule vue, les appelants peuvent sauter JOIN et GROUP BY et simplement faire un SELECT sur des lignes déjà agrégées.

L'exemple ci-dessous agrège sales par employé, le joint à employee pour attacher le nom de l'employé, et définit ça comme une vue.

Les appelants n'ont qu'à référencer la vue pour voir « qui a vendu combien » — ils n'ont pas à penser au join et à l'agrégation internes.

-- Une vue qui étiquette les totaux de ventes par département avec le nom du département
DROP VIEW IF EXISTS dept_sales;
CREATE VIEW dept_sales AS
SELECT d.dept_name, SUM(s.amount) AS total
FROM sales s
JOIN employee e   ON s.emp_id = e.emp_id
JOIN department d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

-- Les appelants font juste un SELECT sur la vue agrégée
SELECT dept_name, total FROM dept_sales
ORDER BY total DESC;

DROP VIEW dept_sales;

Imagine l'exigence : « Pour le tableau de bord de performance commerciale, je veux afficher les meilleurs employés par total des ventes sans écrire le JOIN et l'agrégation à chaque fois. » Prends la vue de la définition à la référence puis à la suppression comme un script autonome dans une seule exécution.

① Joins employee et sales sur emp_id, agrège les totaux de ventes par employé sous l'alias total, et définis le résultat comme une vue nommée emp_sales. Les colonnes de la vue doivent être emp_id, name et total — trois colonnes au total. Mets une instruction « supprimer si existe » pour le même nom de vue avant la définition.

② Extrais name et total de la vue et affiche les 5 premières lignes triées par total décroissant, puis par name croissant comme départage.

③ Pour finir, supprime la vue.