SQL avancé

1. SQL avancé Les sous-requêtes Les fonctions de groupes Gestion des transactions Les vues Indexation et Cluster

2. Les sous-requêtes • SQL permet d'utiliser des requêtes pour élaborer des conditions plus complexes et "dynamiques", c'est à dire qu'un critère de recherche employé dans la clause WHERE soit lui même le résultat d'un SELECT • Type de sous requête • Sous‑requêtes simples • Sous‑requêtes synchronisées ISI 204

3. Sous‑requêtes simples (1) • Dans ce cas la sous‑requête est d'abord évaluée, • puis le résultat est utilisé pour exécuter la requête principale. • Exemple • sélectionner la liste des employés ayant même poste que JONES • select ENAME from EMPwhere JOB = ( select JOB from EMP where ENAME = 'JONES' ); • Remarque • les sous‑requêtes sont plus lisibles que des jointures ISI 204

4. Sous‑requêtes simples (2) • Exercice • Les personnes ayant un salaire inférieur à JONES et qui ne travaillent pas dans le même département que ALLEN • Sélectionner les employés travaillant à CHICAGO et ayant le même job que ALLEN ISI 204

5. Sous‑requêtes synchronisées • Dans ce cas la sous‑requête est évaluée pour chaque n-uplet de la requête principale • Exemple • sélectionner des employés ne travaillant pas dans le même département que leur manager. • SQL> select ENAME from EMP ewhere DEPTNO != ( select DEPTNO from EMP where e.MGR = EMPNO); • Remarque • le synonyme emp de la requête principale est utilisé dans la sous‑requête. ISI 204

6. Les opérateurs associés aux sous‑requêtes (1) • L'opérateur EXISTS permet de construire un prédicat vrai si la sous‑requête qui suit ramène au moins une ligne. • Syntaxe • select ' from nom‑table where exists ( select ‑‑‑ ) • Exercice • Liste des employés travaillant dans un département qui contient au moins un ANALYSTE ? ISI 204

7. Les opérateurs associés aux sous‑requêtes (2) • Les opérateurs ensemblistes • Dans les exemples précédent, le SELECT de la sous‑requête ramenait un seul n-uplet, car à droite du signe "=" se trouvait une seule valeur. Cependant une sous‑requête peut ramener plusieurs n-uplets (une liste de valeur). • Les opérateurs permettant de comparer une valeur à un ensemble de valeurs sont les opérateurs obtenus en ajoutant ANY et ALL à la suite d'un opérateur de comparaison • ANY : la comparaison sera vraie si elle est vraie pour au moins un élément de l'ensemble. • ALL : la comparaison sera vraie si elle est vraie pour tous les éléments de l'ensemble. ISI 204

8. Les opérateurs associés aux sous‑requêtes (3) • Exercice • sélectionner les employés gagnant plus que tous les employés du département 30 • Remarque • L'opérateur IN est équivalent à l'opérateur = ANY • L'opérateur NOT IN est équivalent à l'opérateur != ALL • Exercice • Employés du département "RESEARCH" embauchés le même jour que quelqu'un du département "SALES": ISI 204

9. Les opérateurs associés aux sous‑requêtes (4) • Les sous‑requêtes ramenant plusieurs colonnes • II est possible de comparer le résultat d'un SELECT ramenant plusieurs colonnes à une liste de colonnes. • La liste de colonnes figurera entre parenthèse à gauche de l'opérateur de comparaison. • Syntaxe • select col1, ... from nom tablewhere (col2,col3) = (select col2, col3 ) • Exercice • sélectionner la liste des employés ayant même job et même salaire que FORD ISI 204

10. Les fonctions de groupe (1) • II est possible d'utiliser certaines fonctions en demandant de grouper les résultats selon une ou plusieurs colonnes. • Les fonctions de groupe sont les suivantes • avg (col) • moyenne des valeurs (les valeurs NULL sont ignorées) • count (col) • nombre de n-uplet satisfaisant à la condition WHERE. Les valeurs NULL sont ignorées. • max (col) • valeur maximale des valeurs de la colonne • min (col) • valeur minimale des valeurs de la colonne • sum (col) • Somme des valeurs de la colonne • variance (col) • variance des valeurs de la colonne ISI 204

11. Les fonctions de groupe (2) • En appelant une fonction de groupe dans la liste résultat d'un SELECT, on obtient un résultat comportant une seule ligne et prenant en compte l'ensemble des lignes satisfaisant à la condition WHERE. • Exemple • Total des salaires du département 10. • select SUM(SAL) from EMP where DEPTNO = 10; • Remarque • il est impossible de demander à la fois une colonne et une fonction de groupe. • Exercice • Nom, job, et salaire de l'employé ayant le salaire le plus élevé. • Liste des employés ayant le salaire le plus élevé dans chaque département. ISI 204

12. Calcul sur plusieurs groupes (1) • II est possible de subdiviser la table en groupes, • chaque groupe étant l'ensemble des lignes ayant une valeur commune. • Syntaxe • group by expr [,expr2 ...] • exemple • donner la somme des salaires pour chaque département • Select SUM(SAL) from EMPgroup by DEPTNO; ISI 204

13. Calcul sur plusieurs groupes (2) • Exercice • sélectionner par job, le nombre d'employés, la somme et la moyenne des salaires. • Donner le salaire moyen par département et par job. • Liste des employés ayant le salaire le plus élevé dans chaque département. • Attention • dans la liste des colonnes résultat d'un SELECT de groupe ne peuvent figurer que des caractéristiques de groupe, • c'est à dire : • soit des fonctions de groupe • soit des expressions figurant dans le GROUP BY ISI 204

14. La clause HAVING • La clause HAVING permet, de façon analogue à la clause WHERE , de poser des conditions sur le résultat du regroupement • select col1 [col2 ...] from table [, table2j [where prédicat] [group by expression [, expression] [having prédicat] ] • Le prédicat figurant dans la clause HAVING ne peut porter que sur des caractéristiques des fonctions de groupe figurant dans la clause GROUP BY. ISI 204

15. La clause HAVING (2) • Exercice • sélectionner les jobs dont la moyenne des salaires est supérieure à 1500. • Remarque : • quand les clauses WHERE et GROUP BY sont présentes, la clause WHERE sera d'abord appliquée pour sélectionner les lignes, puis les groupes seront constitués à partir des n-uplets sélectionnés, et les fonctions de groupes seront évaluées • Exercices difficiles • nombre de CLERK des départements ayant au moins deux employés CLERK • job ayant le salaire moyen le plus bas ISI 204

16. Gestion des transactions (1) • transaction • traduit l'atomicité d'une séquence d'opérations sur la base, • elle fait évoluer la base de données d'un état cohérent à un autre état cohérent. • Ce qui signifie que si une transaction ne va pas à son terme pour une raison ou pour une autre (cas de crash du système ou du disque), la base est restaurée dans l'état où elle se trouvait avant que la transaction ne démarre. • Une transaction qui s'exécute bien • est validée ; • une transaction qui ne se termine pas • est avortée. ISI 204

17. Gestion des transactions (2) • valider la transaction en cours par la commande COMMIT. • Les modifications deviennent définitives et visibles à tous les utilisateurs. • annuler la transaction en cours par la commande ROLLBACK. • Toutes les modifications depuis le début de la transaction sont alors annulées. ISI 204

18. Les vues • Definitions • Une vue est une table virtuelle • elle n'existe pas dans la base • elle est construite à partir du résultat d'un SELECT. • La vue sera vu par l'utilisateur comme une table réelle. • Les vues permettent • des accès simplifiés aux données • l'indépendance des données • la confidentialité des données : restreint les droits d'accès à certaines colonnes ou à certains n-uplets. ISI 204

19. Les vues (2) • création d'une vue ‑ CREATE VIEW • La commande CREATE VIEW permet de créer une vue en spécifiant le SELECT constituant la définition de la vue • Syntaxe • create view nom [(col1, ...) ] ,.as. select col1, col2, ... from tab where prédicat[with check option] • La spécification des noms de colonnes de la vue est facultative. • Par défaut, les colonnes de la vue ont pour nom les noms des colonnes résultat de SELECT. ISI 204

20. Les vues (3) • Exercice • créer une vue EMP10 sous‑ensemble de la table EMP comportant le numéro, le nom et le job des employés du département 10. • L'option CHECK OPTION • permet de vérifier que la mise à jour ou l'insertion faite à travers la vue ne produisent que des lignes qui feront partie de la sélection de la vue. • Par exemple, si la vue EMP10 a été créée avec "check option", on ne pourra à travers cette vue ni modifier, ni insérer des employés ne faisant pas partie du département 10. ISI 204

21. Les vues (4) • Création de vues à partir de plusieurs tables. • Exemple • créer une vue comportant le nom des employés, le nom du service et le lieu de travail. • create view EMPLOYES2 as select ENAME, DNAME, LOC from EMP emp, DEPT dept where emp. DEPTNO = dept.DEPTNO • Requêtes et vues • Pour récupérer les données de vues, on procédera comme si l'on était en face d'une table classique. • select * from EMPLOYES2 … • En réalité, cette table est virtuelle et est reconstruite à chaque appel de la vue EMPLOYES par exécution du SELECT constituant la définition de la vue. ISI 204

22. Les vues (5) • Suppression d'une vue ‑ • Une vue peut être détruite par la commande drop view nom vue • Renommer une vue • Une vue peut être renommée par la commande • rename ancien‑nom‑vue to nouveau‑nom‑vue • Remarques : • la prochaine version de MySQL gérera les vues ISI 204

23. Indexation • Lors d'une sélection • le système balaye toute la table pour trouver les n-uplets répondant aux prédicats • temps de réponse prohibitifs pour des tables dépassant quelques centaines de lignes. • La création d'un index • permettra au système de repérer de façon plus rapide des lignes spécifiques des tables et donc d'améliorer d'interrogations. Exemple d'index ISI 204

24. Indexation (2) • création d'un index • Syntaxe • create index nom_index on nom table (col1, col2, ...) • Un index peut être créé sur une table contenant déjà des n-uplets. • L'index sera mis à jour automatiquement lors de la modification de la table. • Remarques • Les requêtes SOL sont transparentes au fait qu'il existe un index ou non. C'est l'optimisateur du SGBD qui optimise • II est possible de créer un index en précisant que les valeurs de la colonne indexée devront être uniques (unicité de clé). Ainsi la création de l'index suivant empêchera l'insertion dans la table EMP d'un numéro d'employé existant • create unique index EMPNO on EMP (EMPNO) • suppression d'un index • Syntaxe • drop index nom_index ISI 204

25. Cluster • Définition • Le cluster est une organisation des tables qui consiste à rassembler physiquement (dans un même bloc disque) les lignes d'une ou plusieurs tables ayant une valeur commune dans un champs donné. Le clustering permet d'accélérer les jointures. • Pour notre exemple • on peut "rapprocher" les informations de DEPT et de EMP en utilisant les colonnes communes DEPTNO. • création d'un cluster • Syntaxe • create cluster nom_cluster (cle1 types1, ....) • II faut préciser le nom du CLUSTER, la colonne à utiliser et le type des éléments de cette colonne. ISI 204

26. Cluster (2) • Activation • A sa création, le cluster est vide. II ne contiendra des données que lorsqu'on y transportera une table par la commande ALTER CLUSTER • Syntaxe • alter cluster nom_cluster add table nom_table where nom_cluster.cle = nom_table.col • où l'on indique la colonne de la table à identifier à la clé du cluster. • Les données de la table sont alors déplacées. • Suppression d'une table d'un cluster • Syntaxe • alter cluster nom_cluster drop table nom_table • Suppression d'un cluster • Syntaxe • drop cluster nom cluster ISI 204

27. Cluster (3) • Exemple • création • create cluster DEPT‑EMP (NUM number); • on associe les deux tables EMP et DEPT au cluster • alter cluster DEPT.EMP add table DEPT where DEPT.DEPTNO = DEPT-EMP.NUM; • alter cluster DEPT‑EMP add table EMP where EMP.DEPTNO = DEPT-EMP.NUM; ISI 204

28. Contrôle des accès à la base : LCD (1) • Partage de droits • Le créateur d'une table en est le propriétaire et il est seul à pouvoir ajouter, modifier ou supprimer des données ou modifier la description des tables. • Le propriétaire de la table peut néanmoins accorder ou retirer les privilèges à d'autre utilisateurs • Exemples • Autoriser un utilisateur à ajouter des n-uplets • Autoriser un utilisateur à consulter une vue • Autoriser un utilisateur à modifier la définition de la table ISI 204

29. Contrôle des accès à la base : LCD (2) • l'ordre GRANT • L'ordre GRANT permet au propriétaire d'une table de donner à d'autres utilisateurs des droits d'accès à cette table ou vue. • Syntaxe • grant droiton table to nom_utilisateur [whith grant option] • grant droiton vue to nom_utilisateur [whith grant option] • l'ordre REVOKE • Un utilisateur ayant accordé un droit peut le reprendre à l'aide de l'ordre REVOKE • Syntaxe • revoke droit on table from nom_utilisateur • revoke droit on vue from nom_utilisateur ISI 204

30. Contrôle des accès à la base : LCD (3) • Les privilèges qui peuvent être accordés sont les suivants • ~ select : droit de lecture • ~ insert : droit d'insertion de n-uplets • ~ update(col,...) : droit de modification de lignes • ~ delete : droit de suppression de lignes • ~ alter : droit de modification de la définition de la table • ~ index : droit de création d'index • ~ cluster : droit de mise en cluster • ~ all : tous les droits ci‑dessus • Remarques • Les trois premiers privilèges s'appliquent aux tables et aux vues. • Les droits peuvent être accordés à tous les utilisateurs en utilisant le mot réservé PUBLIC à la place du nom de l'utilisateur. ISI 204