Différences

Ci-dessous, les différences entre deux révisions de la page.

--- d4:a03 [2025/09/18 22:55] – [A quoi servent les vues ?] dthevenot
+++ d4:a03 [2025/09/24 22:56] (Version actuelle) – [Les vues en SQL] dthevenot
@@ Ligne 1: / Ligne 1: @@
 ====== Les vues en SQL ======
+Sur la base de données des Docks utilisée en 1ère année, nous allons illustrer les différents rôles des vues en SQL (vue à écrire/utilisation de la vue) :
+  - Contrôler l'intégrité en restreignant l'accès aux données pour améliorer la confidentialité.
+    - Ecrire la vue //vueClientsPublics// qui permet de ne pas exposer les numéros de téléphone/fax des clients à certains utilisateurs.
+    - afficher les clients publics français
+  - Partitionner verticalement et/ou horizontalement
+    - on montre uniquement les commandes livrées en France (partition horizontale)
+    - nombre de clients livrés par ville française
+  - Masquer la complexité du schéma.
+    - regrouper commande et client pour éviter à l’utilisateur de faire un JOIN.
+    - les commandes avec leur client livrées en Allemagne
+  - Gérer un indépendance logique des données
+    - les clients avec leur nombre total de commandes et le port payé au total
+    - lister les clients qui ont payé plus de 1000 € de frais de port cumulés :
+  - Modifier automatiquement des données sélectionnées (sum(), avg(), max(),…).
+    - vue qui donne le nombre de commandes par pays de livraison.
+    - affichage trié du nombre de commandes par pays de livraison
+    - les pays qui ont plus de x commandes
+  - Manipuler des valeurs calculées à partir d'autres valeurs du schéma.
+    - calcul du délai réel entre la commande et la livraison.
+    - afficher les commandes livrées en plus de 10 jours
+  - Conserver la structure d'une table si elle doit être modifiée.
+    - imaginons que la direction décide de changer la structure de la table client en supprimant clFax et en renommant clTel en clTelephone, écrire la vue sur la table client qui permet à un ancien programme de toujours exécuter une requête affichant les clients français en utilisant clFax et clTel;
+[[https://docs.google.com/document/d/1QomE0vYfTphWTtKaoL7eDuVgVMvpOZGfLPlBCLHJcAw/edit?tab=t.0#heading=h.6ebi0cf29bzi|Correction collaborative]]
+===== Vues plus complexes =====
+  - Vue avec une sous-requête corrélée :
+    - Lister les clients avec leur dernière commande passée
+    - afficher les clients triés selon leur dernière commande passée
+  - Vue matérialisant une valeur calculée :
+    - Calculer le délai moyen de livraison par pays (coALivrerAvant – coDate).
+    - afficher les pays dont le délai moyen de livraison est supérieur à 10 jours
+  - Vue de sécurité / confidentialité avancée
+    - On ne montre aux commerciaux que leurs propres commandes (exemple : employé n°4).
+    - montrer au commerciaux 4 ses commandes
-Les vues sont des tables virtuelles issues de l'assemblage d'autres tables en fonction de critères. Elles permettent de rendre accessible le retour d'une requête et de l'utiliser.
-Techniquement les vues sont créées à l'aide d'une requête SELECT. Elles ne stockent pas les données
-qu'elles contiennent mais conservent juste la requête permettant de les créer.
-La requête SELECT qui génère la vue référence une ou plusieurs tables. La vue peut donc être, par
-exemple, une jointure entre différentes tables, l'agrégation ou l'extraction de certaines colonnes d'une
-table. Elle peut également être créée à partir d'une autre vue.
-===== A quoi servent les vues ? =====
- Les vues peuvent être utilisées pour différentes raisons. Elles permettent de :
-  * Contrôler l'intégrité en restreignant l'accès aux données pour améliorer la confidentialité.
-  * Partitionner verticalement et/ou horizontalement pour cacher des champs aux utilisateurs, ce qui
-  * permet de personnaliser l'affichage des informations suivant le type d'utilisateur.
-  * Masquer la complexité du schéma.
-  * Gérer un indépendance logique des données, utile pour donner aux utilisateurs l’accès à un
-  * ensemble de relations représentées sous la forme d'une table. Les données de la vue sont
-  * alors des champs de différentes tables regroupées, ou des résultats d’opérations sur ces
-  * champs.
-  * Modifier automatiquement des données sélectionnées (sum(), avg(), max(),...).
-  * Manipuler des valeurs calculées à partir d'autres valeurs du schéma.
-  * Conserver la structure d'une table si elle doit être modifiée.
-  * Le schéma peut ainsi être modifié sans qu'il ne soit nécessaire de changer les requêtes du
-  * côté applicatif.
-===== Les avantages =====
-  * **Simplicité** : Les vues encapsulent des requêtes complexes sous forme de "tables virtuelles", rendant leur résultat accessible via une simple instructionSELECT. Cela permet de masquer des jointures et calculs complexes derrière une interface simple.
-  * **Réutilisabilité** : Une fois créées, les vues peuvent être appelées à plusieurs reprises sans avoir à réécrire la requête d'origine, ce qui réduit le risque d'erreurs et facilite la maintenance des requêtes fréquemment utilisées.
-  * **Sécurité** : Les vues peuvent restreindre l'accès aux données sensibles en affichant seulement les colonnes ou lignes nécessaires à certains utilisateurs. Sofia, par exemple, voit cela comme un excellent moyen de contrôler quelles informations sont visibles pour chaque utilisateur.
-  * **Optimisation des performances** : En transformant une vue en une vue matérialisée, vous pouvez stocker le résultat d’une requête complexe et éviter de recalculer les données à chaque nouvelle exécution, ce qui est particulièrement utile pour des données consultées fréquemment.
-===== Les droits nécessaires =====
- Pour créer une vue l'utilisateur doit avoir le droit CREATE VIEW. Il faut également avoir la permission
-de sélectionner toutes les colonnes qui apparaissent dans la commande SELECT spécifiant ce qu'est la
-vue.
- De plus si la clause REPLACE est utilisée, le droit DROP est également nécessaire.
-Le droit SHOW VIEW donne la possibilité d'exécuter la commande SHOW CREATE VIEW. Cette
-commande permet d'obtenir les informations de création d'une vue. Une autre façon d'obtenir ces
-informations est d'interroger la table view du schéma information_schema. Cette information sera
-exhaustive seulement pour les vues que vous avez créé.
- Accorder les droits sur une vue pour un utilisateur :
- <code>
-GRANT
-  SELECT,
-  DROP,
-  CREATE VIEW,
-  SHOW VIEW
- ON `projet`.* TO 'secretaire'@'localhost';
-</code>
-===== Syntaxe d'une vue =====
-==== CREATE VIEW ====
- La commande MySQL pour créer une vue est assez proche de la syntaxe du standard SQL.
- Syntaxe de création d'une vue sous MySQL
- <code>CREATE VIEW nom_de_la_vue AS requête_select</code>
-====  Exemple   ====
-Création d'une vue pour la relation "etudiant" :
-<code>
- CREATE TABLE etudiant
- (
-  id_etudiant INT UNSIGNED PRIMARY KEY,
-  nom CHAR(30),
-  prenom CHAR(30),
-  age TINYINT UNSIGNED,
-  cursus ENUM('Licence', 'Master', 'Doctorat')
- );
- CREATE VIEW vEtudiantListe AS SELECT nom, prenom FROM etudiant;
-</code>
- Après avoir créé la table etudiant, on crée la vue vEtudiantListe qui contient le nom et le prénom des
-étudiants.
- Il est possible d'ajouter d'autres informations lors de la création de la vue, vous consulterez la
-documentation officielle MySQL pour les découvrir.
-====  ALTER VIEW ====
- Une fois la vue créée, il est bien évidement possible de la modifier avec la commande ALTER VIEW.
-Editer une vue à l'ordre ALTER :
-<code>
- ALTER definer='secretaire'@'localhost'
- VIEW v_etudiant_liste AS SELECT nom, prenom, cursus FROM etudiant;</code>
- Cette commande modifie la clause DEFINER en lui assignant le compte secretaire@localhost et modifie
-la définition de la vue en rajoutant le champ cursus.
- ==== DROP VIEW ====
- L'ordre DROP VIEW permet d'effacer une vue.
-Supprimer des vues à partir de l'ordre DROP VIEW :
- <code>DROP VIEW v_etudiant_liste, v_prof_liste;</code>
- Supprime les vues v_etudiant_liste et v_prof_liste. Il est possible d'ajouter la clause IF EXISTS qui
-retourne un avertissement au lieu d'une erreur si la vue à effacer n'existe pas.
-==== Restrictions ====
-Lors de la création d'une vue, certaines contraintes doivent être prises en compte :
-  * Il n'est pas possible de créer un index sur une vue.
-  * La vue ne peut pas contenir de sous-requêtes dans la clause FROM du SELECT.
-  * Il n'est pas possible d'utiliser de variables dans une vue.
-  * Les objets (tables et vues) nécessaires à la création de la vue doivent exister avant de la créer.
-  * Si un objet référencé par la vue est effacé, la vue n'est alors plus accessible.
-  * Une vue ne peut référencer une table temporaire (TEMPORARY TABLE).
-  * Il n'est pas possible de créer des vues temporaires.
-  * Il n'est pas possible d'associer un trigger à une vue.
-  * La définition d'une vue est "gelée" dans une requête préparé