1 / 66

Le langage SQL

Le langage SQL. Histoire langages pour les BD relationnelless incluant toutes les possibilités de l'algèbre relationnelle : QUEL, SEQUEL, SQL (79, sur Oracle) Normalisation en 87 par ANSI de SQL plus d'une centaine de produits supportant SQL aujourd'hui

hewitt
Télécharger la présentation

Le langage SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Le langage SQL • Histoire • langages pour les BD relationnelless incluant toutes les possibilités de l'algèbre relationnelle : QUEL, SEQUEL, SQL (79, sur Oracle) • Normalisation en 87 par ANSI de SQL • plus d'une centaine de produits supportant SQL aujourd'hui • SQL n'est pas un langage de programmation • 2 douzaines d'instructions environ • peut trouver sa place dans un langage de programmation Maria Berger - Maîtrise d'AES 2003-2004

  2. Caractéristiques • SQL assure l'indépendance des données • SQL est un langage déclaratif (par opposition à procédural) • Mode interactif ou programmé • interactif : instruction SQL tapée directement, instantanément traitée • à l'intérieur d'un pgm : appel de SQL dans un pgm en C, ... • instruction SQL = requête • résultat d'une requête = table Maria Berger - Maîtrise d'AES 2003-2004

  3. Caractéristiques • SQL est un langage à dimension triple : • le DDL (Data Definition Language) : commandes de SQL permettant de créer, modifier ou effacer la définition (schéma) d'une BD ou d'une table. • le DML (Data Manipulation Language) : interrogation et modification de l'information contenue dans les tables • le DCL (Data Control Language) : sécurité et confidentialité de la BD. Maria Berger - Maîtrise d'AES 2003-2004

  4. SQL1 - 86 • LANGAGE DE DEFINITIONS DE DONNEES • CREATE TABLE • CREATE VIEW • LANGAGE DE MANIPULATION DE DONNEES • SELECT OPEN • INSERT FETCH • UPDATE CLOSE • DELETE • LANGAGE DE CONTROLE DE DONNEES • GRANT et REVOKE • BEGIN et END TRANSACTION • COMMIT et ROLLBACK Maria Berger - Maîtrise d'AES 2003-2004

  5. Le Language de Manipulation des Données : la consultation de données • Instruction SELECT : 3 parties principales : • clause (obligatoire) Select : précise les valeurs qui constituent chaque ligne du résultat • clause (obligatoire) from : indique les tables desquelles le résultat tire ses valeurs • clause where : donne la condition de sélection que doivent satisfaire les lignes qui fournissent le résultat SELECT schéma FROM table WHERE condition ; Maria Berger - Maîtrise d'AES 2003-2004

  6. Interrogation en SQL SELECT <liste de colonnes> FROM <liste de tables> [WHERE <critère de jointure> AND <critère de sélection>] [GROUP BY <attributs de partitionnement>] [HAVING <citère de restriction>] • Exemple : SELECT NomCoureur FROM Coureurs WHERE CodePays = ‘SUI’; Maria Berger - Maîtrise d'AES 2003-2004

  7. Interrogation d’une seule table CRU(Nom_Cru,Commune,Région,Coul) VINS(Région,Coul,Millésime,Qualité) CEPAGE_REGION(Cepage,R_PROD,Coul) OU EMP(Num,Nom,Fonction,N_Sup,Embauche, Salaire,Comm,N_Dep) DEPT(N_Dept,Nom,Lieu) Maria Berger - Maîtrise d'AES 2003-2004

  8. PROJECTION • Extraction simple : afficher une table entièrement SELECT * FROM Nom_table; • Exemple : • Tous les crus ? • Expression algébrique : cru • SQL : SELECT * FROM CRU; Maria Berger - Maîtrise d'AES 2003-2004

  9. PROJECTION SELECT Nom_Col1, ..., Nom_ColN FROM Nom_table; • Exemple : Requête : Liste des noms de crus Algèbre : Nom_Cru (CRU) SQL : SELECT Nom_Cru FROM CRU La clause DISTINCT permet d'éliminer les doublons. Maria Berger - Maîtrise d'AES 2003-2004

  10. SELECTION SELECT Nom_Colonne FROM Nom_Table WHERE critère ; • Exemple : • Requête : La liste des noms de crus rouges • Algèbre : Nom_Cru ( Coul = rouge(CRU)) • SQL : SELECT Nom_Cru FROM CRU WHERE Coul=‘Rouge’ Maria Berger - Maîtrise d'AES 2003-2004

  11. Opérateurs de comparaison • =,>,<,>=,<=,<> (ou != ou ^=) • In et not in • Requête : quels sont les bons millésimes de Bordeaux ? • SQL : SELECT Millésime FROM VINS WHERE Région = ‘Bordeaux’ AND Qualité =‘Bonne’ OR Qualité =‘Très Bonne’ OR Qualité =‘Excellente’ OU SELECT Millésime FROM VINS WHERE Région = ‘Bordeaux’ AND Qualité IN (‘Bonne’, ‘Très bonne’, Excellente’ ) Maria Berger - Maîtrise d'AES 2003-2004

  12. Opérateurs de comparaison • LIKE : appartenance à une chaîne de caractères • ‘_’ remplace n'importe quel caractère • ‘%’ remplace n'importe quelle chaîne de caractères • Exemple : • Requête : Quels sont les noms de cru qui commencent par Ch ? • SQL : SELECT Nom_Cru FROM CRU WHERE Nom_Cru like ‘Ch%’ • Requête : Quels sont les noms de cru possédant un ‘a’ en seconde position ? • SQL : SELECT Nom_Cru FROM CRU WHERE Nom_Cru like ‘_a%’ Maria Berger - Maîtrise d'AES 2003-2004

  13. Opérateurs de comparaison • BETWEEN : appartenance à un intervalle • Exemple : • Requête : Quels sont les salariés gagnant entre 20000 et 25000 ? • SQL : SELECT Nom, Salaire FROM EMP WHERE Salaire BETWEEN 20000 AND 25000 Maria Berger - Maîtrise d'AES 2003-2004

  14. Nom de colonne • Les colonnes constituant le résultat d’un SELECT peuvent être renommées dans le SELECT • Requête : salaire de chaque employé • SQL : SELECT Nom,Salaire « SALAIRE MENSUEL » FROM EMP; Maria Berger - Maîtrise d'AES 2003-2004

  15. Opérateurs de comparaison • IS NULL et IS NOT NULL • Exemple : • Requête : le nom des crus dont la commune n’a pas été renseignée • SQL : SELECT Nom_Cru FROM CRU WHERE Commune IS NULL Maria Berger - Maîtrise d'AES 2003-2004

  16. Tri des résultats • ORDER BY SELECT attribut1, attribut2, ... FROM Nom_table ORDER BY attribut1 [ASC], attribut2 [DESC], ... ; • Exemple : • Requête : Les bordeaux classés par millésimes et par couleur • SQL : SELECT * FROM VINS WHERE Région = ‘Bordeaux’ ORDER BY Millésime, Coul Maria Berger - Maîtrise d'AES 2003-2004

  17. Fonctions • La relation résultat • ne comportera qu'une ligne • ou pourra simplement être considérée comme un nombre • Fonctions numériques : • AVG : moyenne • Exemple : Age moyen des professeurs • SQL : SELECT ‘2001-10-10’-AVG(Date_Naissance) FROM PROFESSEUR; • SUM : somme • Exemple : CA généré par les vendeurs de PACA: • SQL : SELECT SUM(CA) FROM Vendeur WHERE Region = 'PACA'’; Maria Berger - Maîtrise d'AES 2003-2004

  18. Fonctions • COUNT : nombre d'éléments sélectionnés • Requête : Nombre de cépages bordelais ? • SQL : SELECT COUNT ( *) FROM CEPAGE_REGION WHERE R_Prod = ‘Bordeaux’; • MIN, MAX, ... • Expressions et Fonctions sur les chaînes de caractères • || : seul opérateur sur les chaînes de caractères : la concaténation. • Résultat : chaîne de caractères obtenue en écrivant d'abord la chaîne à gauche de || puis celle à droite de ||. • LOWER(chaîne) : Renvoie chaîne en ayant mis toutes ses lettres en minuscules. • ... Maria Berger - Maîtrise d'AES 2003-2004

  19. Fonctions • Expressions et fonctions sur les dates • Opérateurs sur les dates : + et - • date +/- nombre : le resultat est une date obtenue en ajoutant le nombre de jours nombre à la date date. • date2 - date1 : le resultat est le nombre de jours entre les deux dates. Maria Berger - Maîtrise d'AES 2003-2004

  20. Regroupements • Il est possible de subdiviser la table en groupes • Permet d’appliquer les fonctions d’aggrégation à des sous-groupes • Requête : Combien de crus rouges et de crus blancs sont produits dans chaque commune ? • SQL : SELECT Commune, Count(*) FROM CRU GROUP BY Commune, Coul; Maria Berger - Maîtrise d'AES 2003-2004

  21. Résultat Maria Berger - Maîtrise d'AES 2003-2004

  22. Selection des groupes • HAVING : conditions imposées aux groupes (de lignes) à sélectionner • pour éviter la confusion avec la clause WHERE (qui ne s'applique qu'à des lignes seules) • Exemple : • Requête : Donner les communes où au moins 2 crus rouges ou crus blancs sont produits? • SQL : SELECT Commune, Count(*) FROM CRU GROUP BY Commune, Coul HAVING Count(*) >2; Maria Berger - Maîtrise d'AES 2003-2004

  23. Quantificateurs • ALL + opérateur de comparaison : teste si une expression est vérifiée dans tous les cas de figure • Requête : Tous les salariés ont-ils été recrutés avant le 1er janvier 2001 ? • SQL : ‘2001-01-01’ > ALL (SELECT Embauche FROM EMP) • SOME ou ANY : expression vraie si la comparaison est vérifiée pour au moins une valeur • Requête : vérifiez si au moins un salarié a été recruté depuis un an • SQL : ‘2000-10-01’ > ANY (SELECT Embauche FROM EMP) Maria Berger - Maîtrise d'AES 2003-2004

  24. Quantificateurs • EXISTS, NOT EXISTS Maria Berger - Maîtrise d'AES 2003-2004

  25. Comment interpréter une requête complexe monotable ? • on considère la table spécifiée dans la clause FROM • on sélectionne les lignes sur la base de la clause WHERE • on classe ces lignes en groupes comme spécifié dans la clause GROUP BY • on ne retient que les groupes qui vérifient la clause HAVING • de chacun de ces groupes, on extrait les valeurs demandées dans la clause SELECT • les valeurs demandées sont ordonnées selon la clause ORDER BY éventuelle. Maria Berger - Maîtrise d'AES 2003-2004

  26. Interrogations sur plusieurs tables : • SQL permet la liaison de plusieurs tables via 3 possibilités : • Les opérations de jointure entre 2 tables en se basant sur l'égalité entre l'un des attributs de chaque table • Le principe des requêtes imbriquées qui repose sur le fait que le résultat d'une requête est une table • L'utilisation d'opérations ensemblistes pour combiner le résultat de plusieurs requêtes. Maria Berger - Maîtrise d'AES 2003-2004

  27. Jointures SELECT ... FROM nom_table1, nom_table2... WHERE critère; • pas de condition de sélection : résultat obtenu = produit cartésien des tables présentes derrière le FROM. • Requête : à partir de quel cépage principal est produit le Meursault ? • SQL :SELECT Cépage FROM CEPAGE_REGION, CRU WHERE région = R_Prod AND Commune = Meursault’ Si l’attribut Région avait le même nom dans CRU que dans CEPAGE_REGION on aurait écrit : CRU.Région = CEPAGE_REGION.Région Maria Berger - Maîtrise d'AES 2003-2004

  28. Auto-jointure : • Requête : Donner pour chaque employé le nom de son supérieur hiérarchique. • SQL : SELECT EMP.Nom, chef.Mom FROM EMP, EMP chef WHERE EMP.N_sup= chef.Num; Maria Berger - Maîtrise d'AES 2003-2004

  29. Autres jointures • Le critère d'égalité est le critère de jointure le plus naturel. Mais on peut utiliser d'autres types de comparaisons comme critères de jointures. • Requête : Quels sont les employés gagnant plus que SIMON ? • SQL : SELECT EMP.nom, EMP.salaire, EMP.fonction FROM EMP, EMP empbis WHERE EMP.salaire > empbis.salaire AND empbis.nom = 'SIMON'; Maria Berger - Maîtrise d'AES 2003-2004

  30. Requêtes imbriquées • une clause WHERE est elle-même le résultat d'un SELECT • renvoient une ou plusieurs valeurs Maria Berger - Maîtrise d'AES 2003-2004

  31. Requêtes imbriquées • Sous-interrogation ramenant une seule valeur • Requête : Quels sont les vins qui sont produits dans la même commune que La Grappe d’Or ? • SQL : SELECT Nom_Cru FROM VINS WHERE Commune = (SELECT Commune FROM VINS WHERE Nom_Cru = ‘La Grappe d’Or’) • Remarque : auto-jointure possible pour répondre à cette question Maria Berger - Maîtrise d'AES 2003-2004

  32. Requêtes imbriquées • une sous-interrogation qui ne ramène aucune ligne se termine avec un code d'erreur. • une sous-interrogation ramenant plusieurs lignes provoquera aussi, dans ce cas, une erreur Maria Berger - Maîtrise d'AES 2003-2004

  33. Requêtes imbriquées • Sous-interrogation ramenant plusieurs lignes • Avec des opérateurs de comparaison admettant à leur droite un ensemble de valeurs comme : • l'opérateur IN • les opérateurs obtenus en ajoutant ANY ou ALL à la suite d'un opérateur de comparaison classique (=, <>, >, >=, <, <=) Maria Berger - Maîtrise d'AES 2003-2004

  34. Requêtes imbriquées • Requête : Quels sont les employés gagnant plus que tous les employés du département 30 ? • SQL : SELECT Nom, Salaire FROM Emp WHERE salaire > ALL(SELECT Salaire FROM Emp WHERE N_Dep=30); Maria Berger - Maîtrise d'AES 2003-2004

  35. Requêtes imbriquées • Sous-interrogation ramenant plusieurs colonnes • Exemple : Quels sont les employés ayant la même fonction et le même supérieur que CODD ? • SQL : SELECT Nom, Fonction, N_Sup FROM Emp WHERE (Fonction,N_Sup) = (SELECT Fonction,N_Sup FROM Emp WHERE Nom = 'CODD'); Maria Berger - Maîtrise d'AES 2003-2004

  36. Requêtes imbriquées • Sous-interrogation ramenant au moins une ligne • L'opérateur EXISTS permet de construire un prédicat vrai si la sous-interrogation qui suit ramène au moins une ligne. • Requête : Quels sont les employés travaillant dans un département qui a procédé à des embauches depuis le début de l'année 2001 ? • SQL : SELECT * FROM Emp Empbis WHERE EXISTS (SELECT * FROM EMP WHERE Embauche >= '01-jan-01' AND N_Dept = Empbis.N_Dept); Maria Berger - Maîtrise d'AES 2003-2004

  37. Les opérateurs ensemblistes • permettent de "joindre" des tables verticalement c'est-à-dire de combiner dans un résultat unique des lignes provenant de deux interrogations. Les opérateurs ensemblistes sont les suivants : • l'union : UNION • l'intersection : INTERSECT • la différence relationnelle : MINUS • La syntaxe d'utilisation est la même pour ces trois opérateurs : • SELECT ... {UNION | INTERSECT | MINUS } SELECT ... Maria Berger - Maîtrise d'AES 2003-2004

  38. Les opérateurs ensemblistes • Dans une requête utilisant des opérateurs ensemblistes : • Tous les SELECT doivent avoir le même nombre de colonnes sélectionnées, et leur types doivent être un à un identiques. • Les doubles sont éliminés (DISTINCT implicite). • Les noms de colonnes sont ceux du premier SELECT. • On peut combiner le résultat de plus de deux SELECT au moyen des opérateurs UNION, INTERSECT, MINUS. SELECT ... UNION SELECT ... MINUS SELECT ... • Expresion évaluée de gauche à droite. Modification de l'ordre d'évaluation par des parenthèses. SELECT ... UNION (SELECT ... MINUS SELECT ...) Maria Berger - Maîtrise d'AES 2003-2004

  39. Les opérateurs ensemblistes • Exemple : Lister tous les enseignants SELECT Nom, Prénom FROM MdC UNION SELECT Nom, Prénom FROM Professeur ; Maria Berger - Maîtrise d'AES 2003-2004

  40. Coment interpréter une requête complexe multitable ? • on considère les tables spécifiées dans la clause FROM • on effectue la jointure de ces tables selon le critère de jointure de la clause WHERE • on sélectionne les lignes de la jointure sur la base des autres conditions de la clause WHERE • on classe ces lignes en groupes comme spécifié dans la clause GROUP BY • on ne retient que les groupes qui vérifient la clause HAVING • de chacun de ces groupes, on extrait les valeurs demandées dans la clause SELECT • les valeurs demandées sont ordonnées selon la clause ORDER BY éventuelle. Maria Berger - Maîtrise d'AES 2003-2004

  41. Coment interpréter une requête complexe multitable ? • Exemple : SELECT N°Client, COUNT(*), SUM(QtéCom) FROM Commande C, LigneCom L WHERE C.N°Com = L.N°Com AND N°Pro = ‘PA 60’ GROUP BY N°Client HAVING COUNT(*) >= 2 ORDER BY N°Client Maria Berger - Maîtrise d'AES 2003-2004

  42. Le Language de Manipulation des Données : la modification de données • Insertion de nouveaux n-uplets INSERT INTO nom_table(nom_col1, nom_col2,...) VALUES (val1, val2...) • Exemple : INSERT INTO Etudiant (n°Et, Nom, Prénom) VALUES(96035, ‘Diego’, ‘Berger’) Maria Berger - Maîtrise d'AES 2003-2004

  43. La modification de données • Il est possible d'insérer dans une table des lignes provenant d'une autre table. La syntaxe est la suivante : INSERT INTO nom_table(nom_col1, nom_col2, ...) SELECT ... • Exemple : Insérer dans la table Bonus les noms et salaires des directeurs. INSERT INTO bonus SELECT nom, salaire FROM emp WHERE fonction = 'directeur'; Maria Berger - Maîtrise d'AES 2003-2004

  44. La modification de données • Modification de lignes • La commande UPDATE permet de modifier les valeurs d'une ou plusieurs colonnes, dans une ou plusieurs lignes existantes d'une table. La syntaxe est la suivante : UPDATE nom_table SET nom_col1 = {expression1 | ( SELECT ...) }, nom_col2 = {expression2 | ( SELECT ...) } [WHERE critère]; • Exemple : Augmenter de 10% les salaires [des ingénieurs]. UPDATE emp SET salaire = salaire * 1.1 [WHERE fonction = 'ingenieur' ]; Maria Berger - Maîtrise d'AES 2003-2004

  45. La modification de données • Suppression de lignes • La commande DELETE permet de supprimer des lignes d'une table. La syntaxe est la suivante : DELETE FROM nom_table WHERE critère; • Toutes les lignes pour lesquelles le critère est évalué à vrai sont supprimées. En l'absence de clause WHERE, toutes les lignes de la table sont supprimées. • Exemple : DELETE FROM emp WHERE fonction = ‘retraité‘; Maria Berger - Maîtrise d'AES 2003-2004

  46. Le language de définition de données • Création d'une table CREATE TABLE nom_table (nom_col1 TYPE1,[NOTNULL/ PRIMARY KEY/FOREIGN KEY] nom_col2 TYPE2,[.../.../...] ...); • Types acceptés : • CHAR(longueur), VARCHAR(longueur) • SMALLINT, INTEGER, DECIMAL(m,n), FLOAT, SERIAL(n) • DATE Maria Berger - Maîtrise d'AES 2003-2004

  47. Création d'une table CREATE TABLE Departement (NDep SERIAL(20),NOTNULL,PRIMARY KEY, NomDep CHAR(20), Directeur CHAR(20), Budget DECIMAL(6,0)); CREATE TABLE Etudiant (NEtud SERIAL(20),NOTNULL,PRIMARY KEY, Nom CHAR(20), Prénom CHAR(20), DateNaissance DATE, Rue CHAR(80), CodePostal DECIMAL(5,0), Ville CHAR(20),DEFAULT ‘‘PARIS’’, NDep SERIAL(20),NOTNULL,FOREIGN KEY); Maria Berger - Maîtrise d'AES 2003-2004

  48. Suppresion et modification d'une table • DROP TABLE nom_table ; • Exemple : DROP TABLE Etudiant ; • Modification d'une table • Ajoût d'une ou plusieurs colonnes : ALTER TABLE nom_table ADD(nom_col1 TYPE1, nom_col2 TYPE2, ...); • option : [BEFORE nom_col_before] • Exemple : On aimerait connaître le téléphone des étudiants ALTER TABLE Etudiant ADD(Téléphone DECIMAL(10,0) BEFORE NDep); Maria Berger - Maîtrise d'AES 2003-2004

  49. Modification d'une table • Suppression d'une colonne : ALTER TABLE nom_table DROP nom_col; • Attention aux problèmes d'intégrité ! • Modification d’une table : ALTER TABLE nom_table MODIFY(nom_col1 TYPE1,nom_col2 TYPE2,...); • Exemple : Un nom peut dépasser 20 caractères ALTER TABLE Etudiant MODIFY(Nom Char(25)); Maria Berger - Maîtrise d'AES 2003-2004

  50. Modification d'une table • Changement de nom de tables ou de colonnes : RENAME TABLE ancien_nom TO nouveau_nom ; RENAME COLUMN nom_relation.ancien_nom_col TO nouveau_nom_col ; • Exemple : RENAME COLUMN Etudiant.DateNaissance TO BirthDay; Maria Berger - Maîtrise d'AES 2003-2004

More Related