1 / 61

Bases de Données Avancées: Bases de Données Relationnelles

Dr. Rim Samia Kaabi. Bases de Données Avancées: Bases de Données Relationnelles. 4 septembre 2014. Plan. SQL. SQL. INTRODUCTION. Un SGBD relationnel fournit un ensemble de services pour : définir et maintenir les schémas (LDD : langage de définition de données),

zada
Download Presentation

Bases de Données Avancées: Bases de Données Relationnelles

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. Dr. Rim Samia Kaabi Bases de Données Avancées: Bases de Données Relationnelles 4 septembre 2014

  2. Plan SQL

  3. SQL INTRODUCTION • Un SGBD relationnel fournit un ensemble de services pour : • définir et maintenir les schémas (LDD : langage de définition de données), • créer, rechercher, mettre à jour les informations (LMD : langage de manipulation de données). SQL fournit aussi bien le LDD que le LMD

  4. SQL : langage de définition de données Création d'une relation (CREATE TABLE) Description des relations avec: • domaine de chaque attribut, • définition de certaines contraintes d'intégrité sur les attributs. Format général : CREATE TABLE <relation> ( <attribut> <domaine> [ <contrainte d’attribut> ] ,... [ <contrainte de relation> , ... ] )

  5. SQL : langage de définition de données Création d'une relation (CREATE TABLE) • Domaines : caractère(s) : CHARACTER[(n)], ou CHAR[(n)] numérique exact : INTEGER ou INT, SMALLINT, NUMERIC(l,d), DECIMAL(l,d) numérique non exact : FLOAT (p), REAL, DOUBLE PRECISION temporels : DATE, TIME, TIMESTAMP (= date-heure)

  6. SQL : langage de définition de données Création d'une relation (CREATE TABLE) • Contraintes d'attribut (pour spécifier des contraintes sur un seul attribut) : valeur obligatoire : NOT NULL unicité de l'attribut : UNIQUE , PRIMARY KEY contrainte référentielle : REFERENCES <relation> [(<clé référencée>)] contrainte générale : CHECK (<expression logique>)

  7. SQL : langage de définition de données Création d'une relation (CREATE TABLE) • Contrainte de relation (pour spécifier des contraintes sur plusieurs attributs): contrainte d'unicité : UNIQUE , PRIMARY KEY (<attributs>,...) contrainte référentielle : FOREIGN KEY ( <clé étrangère>,...) REFERENCES <relation > [(clé référencée)] contrainte générale : CHECK (<expression logique>)

  8. SQL : langage de définition de données Création d'une relation (CREATE TABLE) Exemples: création des relations suivantes : • EMPLOYE(NoEmp, Nom, Année, NoDep) • DEPART(NoDep, Intitulé, Taille, NoResp) • PROJET(NoProj, NoChef) • TRAVAILLE(NoEmp, NoProj, Temps) CREATE TABLE EMPLOYE ( NoEmp INT NOT NULL, Nom CHAR(20), Année NUMERIC(4), NoDep INT NOT NULL, PRIMARY KEY (NoEmp), FOREIGN KEY (NoDep) REFERENCES DEPART);

  9. SQL : langage de définition de données Création d'une relation (CREATE TABLE) (Exemples) CREATE TABLE DEPART ( NoDep INT NOT NULL, Intitulé CHAR(15), Taille INT, NoResp INT, PRIMARY KEY (NoDep), FOREIGN KEY (NoResp) REFERENCES EMPLOYE(NoEmp)); CREATE TABLE PROJET ( NoProj INT NOT NULL, NoChef INT, PRIMARY KEY (NoProj), FOREIGN KEY (NoChef) REFERENCES EMPLOYE(NoEmp));

  10. SQL : langage de définition de données Création d'une relation (CREATE TABLE) CREATE TABLE TRAVAILLE ( NoEmp INT NOT NULL, NoProj INT NOT NULL, Temps INT CHECK (Temps BETWEEN 1 AND 100), PRIMARY KEY (NoEmp, NoProj) ); Remarques : • La clause NOT NULL impose qu'une valeur soit donnée lors de la création de chaque tuple. • La clause CHECK indique que le temps doit être compris entre 1 et 100 (c'est un pourcentage).

  11. SQL : langage de définition de données Modification d'une relation (ALTER TABLE) Ajout, modification ou suppression d’attributs d'une table existante. Format général : ALTER TABLE <table> ADD < <attribut> <domaine> [<contrainte d'attribut>] > ,... MODIFY < <attribut> [<domaine>] [<contrainte d'attribut>] > ,... DROP <attribut>,...

  12. Modification d'une relation (ALTER TABLE) SQL : langage de définition de données Exemple : modification de la longueur de l'attribut Nom de la relation EMPLOYE. ALTER TABLE EMPLOYE MODIFY Nom CHAR(30); Remarque: Selon le type de modification, les valeurs existantes peuvent être conservées, modifiées (tronquées par exemple) ou perdues. Deux SGBD différents peuvent d'ailleurs avoir des actions différentes.

  13. Destruction d'une relation (DROP TABLE) SQL : langage de définition de données suppression d’une table: + des données qu’elle contient, + de sa définition dans le dictionnaire, + des index associés. Format général : DROP TABLE <table> Exemple : destruction de la table TRAVAILLE DROP TABLE TRAVAILLE

  14. Création d'un index (CREATE INDEX) SQL : langage de définition de données Avantages d'un index : accélère certaines opérations : sélections ou tris (clauses WHERE et ORDER BY en SQL) , ou jointure. Inconvénients d’un index : traitements alourdis pour une table très petite, ou sur un attribut dont les mêmes valeurs se répètent beaucoup. Certains SGBD créent eux-mêmes certains index, sur les clés primaires par exemple. Format général : CREATE INDEX <index> ON <table> (<attribut> [,<attribut]...) Exemple : CREATE INDEX NOMEMP ON EMPLOYE (Nom)

  15. Suppression d'un index (DROP INDEX) SQL : langage de définition de données Format général : DROP INDEX <nom d'index> Normalement, dans une base donnée, deux index distincts doivent avoir des noms différents. Exemple : DROP INDEX NOMEMP

  16. Insertion de données (INSERT) SQL : langage de manipulation de données Format simplifié : INSERT INTO <table> [ ( <attribut>,... ) ] VALUES ( <valeur>,...> ); Exemple : insertion de l'employé Dupont, en 1978, dans le département 03, avec le n° 1045. INSERT INTO EMPLOYE VALUES (1045,'Dupont',1978,03);

  17. Interrogation des données (SELECT) SQL : langage de manipulation de données Interrogation Þsynthèse de la base de donnée : • sur 1 table, • sur n tables, • possibilité de réaliser la plupart des opérations de l'algèbre relationnelle : sélection, projection, jointure (et produit cartésien), union, intersection, différence. résultat = un ensemble de tuples, éventuellement: • ordonnés selon une clé (précisée dans le SELECT), • regroupés selon certains critères ( " " ), • etc.

  18. Interrogation des données (SELECT) SQL : langage de manipulation de données Format général : SELECT [ALL | DISTINCT] <expression résultat>,... FROM < <table> [ <alias-select>] > ,... [ WHERE <condition>] [ GROUP BY <attribut>,...] [ HAVING <condition> ] [ ORDER BY < <attribut> [ ASC | DESC] >,... ]

  19. Interrogation des données (SELECT) SQL : langage de manipulation de données DISTINCT : élimination des tuples en double dans le résultat. ALL : valeur par défaut. <expression résultat>: expression où interviennent les attributs des tables utilisées. L'expression la plus simple, et la plus usuelle est la référence à un attribut. Problème de la qualification: Si deux attributs de deux tables référencées ont le même nom, il est nécessaire de les préfixer avec les alias-select s'il en est défini, sinon avec les noms des tables : EMPLOYE.NoDepet DEPART.NoDep par exemple, désignent respectivement l'attribut NoDep des tables EMPLOYE et DEPART.

  20. Interrogation des données (SELECT) SQL : langage de manipulation de données opérateurs courants dans les expressions de résultat : + , - , * , / Opérateurs arithmétiques < , > , = , <> , IS NULL Opérateurs relationnels AND, OR, NOT Opérateurs logiques || Opérateur de concaténation (=mise bout à bout de deux chaînes)

  21. Interrogation des données (SELECT) SQL : langage de manipulation de données fonctions utilisables dans les expressions de résultats. Seules les fonctions d'agrégat seront vues, avec l'option GROUP BY. nom des colonnes du résultat : nom de l'attribut ou nom généré par le SGBD, par exemple SOMME DE ... si on a utilisé la fonction SUM(...). On peut aussi définir le titre de colonne avec une sous-option AS.

  22. Interrogation des données (SELECT) SQL : langage de manipulation de données Exemple: SELECT Nom, NoEmp AS 'N° Employé', NoDep AS 'N° de son département' FROM EMPLOYE ORDER BY Nom; Remarque: Le caractère * représente l'ensemble des attributs intervenant dans le SELECT, par exemple SELECT * FROM EMPLOYE, donnera les valeurs de tous les attributs de tous les tuples de la table EMPLOYE.

  23. Interrogation des données (SELECT) SQL : langage de manipulation de données FROM : • introduit la liste des tables impliquées dans la commande SELECT. • Chaque nom de table peut être suivi d'un alias-select qui n'est défini que le temps de l'exécution du SELECT. • Lorsque FROM introduit plusieurs tables, la commande SELECT porte sur le produit cartésien de ces tables. C'est la condition introduite par la clause WHERE qui, éventuellement, transformera le SELECT en une jointure.

  24. Interrogation des données (SELECT) SQL : langage de manipulation de données Exemple : SELECT NoEmp, Nom FROM EMPLOYE; SELECT EM.NoEmp, EM.Nom FROM EMPLOYE EM; Exemple ou l'alias est plus utile (jointure) : liste des noms des employés, avec l'intitulé de leur département : SELECT EM.Nom, DE.Intitule FROM EMPLOYE EM, DEPART DE WHERE EM.NoDep = DE.NoDep;

  25. Interrogation des données (SELECT) SQL : langage de manipulation de données WHERE : introduit une condition de sélection, et/ou une condition de jointure selon les cas. Exemple : liste des noms des employés engagés avant 1990, avec leur n° de département : SELECT Nom, NoDep FROM EMPLOYE WHERE Année < 1990; Exemple : même question avec l'intitulé du département au lieu du n° : SELECT EM.Nom, DE.Intitulé FROM EMPLOYE EM, DEPART DE WHERE EM.NoDep = DE.NoDep and Année < 1990;

  26. Interrogation des données (SELECT) SQL : langage de manipulation de données Opérateurs utilisables dans les expressions suivant WHERE : - opérateurs relationnels habituels : < , > , = , <> , - opérateur relationnel «composé» : BETWEEN, - opérateurs logiques : AND, OR, NOT, - opérateurs ensemblistes : IN, NOT IN, ANY ou SOME, ALL, EXISTS, - opérateur de recherche de chaîne de caractères: LIKE, Opérateur BETWEEN: exemple : Temps BETWEEN 1 and 100

  27. Interrogation des données (SELECT) SQL : langage de manipulation de données Opérateur LIKE : permet de tester la présence de chaîne de caractères précise dans un attribut de type chaîne de caractères. _ (caractère "souligné") représente un seul caractère quelconque % représente n'importe quelle chaîne de caractères, le caractère. tout autre caractère se représente lui-même... Exemples : - Nom LIKE 'DU%' : tous les noms commençant par DU (DUPONT, DURAND, DUX, DU, DULAC,...). - Nom LIKE 'DUPON_' : noms de 6 c débutant par DUPON (DUPOND, DUPONT, DUPONS,...)

  28. Interrogation des données (SELECT) SQL : langage de manipulation de données Opérateurs IN et NOT IN : servent à introduire soit une liste de valeurs entre parenthèses, soit une sous-requête générant une liste de valeur. Exemples : NoDep IN (03, 05, 09) vrai si NoDep est un de ces numéros. SELECT NoEmp, Nom sélectionne les employés consacrant FROM EMPLOYE plus de la moitié de leur temps sur un WHERE NoEmp IN seul projet ( SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50 );

  29. Interrogation des données (SELECT) SQL : langage de manipulation de données Exemples  (suite): SELECT NoEmp, Nom (id. requête précédente) FROM EMPLOYE EM, TRAVAILLE TR WHERE EM.NoEmp = TR.NoEmp AND TR.Temps > 50; SELECT NoEmp, Nom les employés qui ne consacrent FROM EMPLOYE pas plus de la moitié de leur WHERE NoEmp NOT IN temps sur chacun des projets ( SELECT NoEmp auxquels ils participent FROM TRAVAILLE WHERE Temps > 50 );

  30. Interrogation des données (SELECT) SQL : langage de manipulation de données Opérateurs ALL et ANY: l'élément placé à gauche est comparé (<, >, <>, =, >=, <=) à la liste située à droite. Comme avec IN, la liste peut être explicite, ou le résultat d'une sous-requête. Avec ALL: la condition est vraie si la comparaison est vraie avec toutes les valeurs de la liste. Si la liste est vide, le résultat est vrai. Avec ANY ou SOME: la condition est vraie si elle l'est pour au moins une valeur de la liste. Si la liste est vide, le résultat est faux.

  31. Interrogation des données (SELECT) SQL : langage de manipulation de données Exemples (ALL, ANY): SELECT * les employés qui ont une FROM EMPLOYE anciennetésupérieure WHERE Année < ALLà chacun des employés ( SELECT Année du département n° 03. FROM EMPLOYE WHERE NoDep = 03 ); SELECT * les employés qui ont une FROM EMPLOYE ancienneté supérieure à au WHERE Année < ANY moins un des employés ( SELECT Année du département n° 03. FROM EMPLOYE WHERE NoDep = 03 );

  32. Interrogation des données (SELECT) SQL : langage de manipulation de données Opérateur EXISTS : introduit une sous-requête. Le résultat est vrai si le résultat de cette sous-requête n'est pas vide, il est faux dans le cas contraire. Exemple : SELECT * les départements non vides! FROM DEPART WHERE EXISTS ( SELECT * FROM EMPLOYE WHERE DEPART.NoDep = EMP.NoDep ); On peut utiliser aussi NOT EXITS. Bien souvent, EXISTS permet de faire la même requête qu'avec une jointure, mais de façon plus compliquée.

  33. Interrogation des données (SELECT) SQL : langage de manipulation de données reprendre toutes les requêtes SQL données en exemple pour l'instruction SELECT, jusqu'à ce point, pour les refaire dans le langage de requête algébrique. Exercice

  34. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT Nom, NoEmp AS 'N° Employé', NoDep AS 'N° de son département' FROM EMPLOYE ORDER BY Nom; Titres ? Ordre ?

  35. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT EM.Nom, DE.Intitule FROM EMPLOYE EM, DEPART DE WHERE EM.NoDep = DE.NoDep;

  36. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT Nom, NoDep FROM EMPLOYE WHERE Année < 1990;

  37. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT EM.Nom, DE.Intitulé FROM EMPLOYE EM, DEPART DE WHERE EM.NoDep = DE.NoDep and Année < 1990;

  38. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT NoEmp, Nom FROM EMPLOYE WHERE NoEmp IN ( SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50 );

  39. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT NoEmp, Nom FROM EMPLOYE WHERE NoEmp NOT IN ( SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50 );

  40. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT * les employés qui ont une FROM EMPLOYE anciennetésupérieure WHERE Année < ALLà chacun des employés ( SELECT Année du département n° 03. FROM EMPLOYE WHERE NoDep = 03 );

  41. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT * les employés qui ont une FROM EMPLOYE ancienneté supérieure à au WHERE Année < ANYmoins un des employés SELECT Année du département n° 03. FROM EMPLOYE WHERE NoDep = 03 );

  42. Interrogation des données (SELECT) SQL : langage de manipulation de données Autre solution SQL pour : «les employés qui ont une ancienneté supérieure à au moins un des employés du département n° 03. SELECT distinct E1.* FROM EMPLOYE E1, EMPLOYE E2 WHERE E2.NoDep = 03 and E1.Année < E2.Année;

  43. Interrogation des données (SELECT) SQL : langage de manipulation de données SELECT * les départements non vides! FROM DEPART WHERE EXISTS ( SELECT * FROM EMPLOYE WHERE DEPART.NoDep = EMP.NoDep ); SELECT D.* FROM DEPART D, EMPLOYE E WHERE D.NoDep = E.NoDep;

  44. Interrogation des données (SELECT) SQL : langage de manipulation de données GROUP BY : indique sur quel(s) attribut(s) les tuples sélectionnés doivent être agglomérés. Ces attributs doivent impérativement apparaître dans la liste de résultats du SELECT, en plus des fonctions statistiques d'agrégats qui vont de paire avec la clause GROUP BY. Les fonctions d’agrégat les plus courantes sont : • SUM() : c'est la somme des valeurs de l'attribut donné en paramètre, qui apparaîtra pour chaque agrégat. • AVG() : idem pour la moyenne. • MIN() : idem pour le minimum. • MAX() : idem pour le max. • COUNT(*) : donne le nombre de tuples.

  45. Interrogation des données (SELECT) SQL : langage de manipulation de données GROUP BY(Suite): Remarque : Ces fonctions, utilisées seules dans un SELECT, sans la clause GROUP BY, fonctionnent sur la totalité des tuples sélectionnés, comme s'il n'y avait qu'un groupe. Exemple: en supposant qu'il existe un attribut salaire dans la table EMPLOYE, donner pour chaque département sa masse salariale : SELECT NoDep, SUM(salaire) FROM EMPLOYE GROUP BY NoDep;

  46. Interrogation des données (SELECT) SQL : langage de manipulation de données HAVING : - introduit une condition sur les groupes, - lié à la clause GROUP BY, - seuls les groupes pour lesquels la condition de la clause HAVING est vraie sont conservés dans le résultat. Exemple : liste des projets ayant plus de 3 employés y travaillant, en indiquant le n° du projet, le n° du chef de projet, et le nombre d'employés qui y travaillent : SELECT pr.NoProj, pr.NoChef, count(*) FROM PROJET PR., TRAVAILLE TR WHERE pr.NoProj = TR.NoProj GROUP BY pr.NoProj HAVING count(*) > 3;

  47. Interrogation des données (SELECT) SQL : langage de manipulation de données ORDER BY : introduit la ou les colonnes du résultat sur lesquelles un tri doit être fait. Une colonne peut être désignée par son nom (attribut ou nom introduit par AS), ou par sa position dans la liste de résultats. Exemple : liste des employés, par département, dans l'ordre des départements, et par ordre alphabétique dans chaque département. SELECT NoDep, Nom, NoEmp, Année FROM EMPLOYE ORDER BY NoDep, Nom;

  48. Interrogation des données (SELECT) SQL : langage de manipulation de données Remarque : on peut utiliser une sous-requête (SELECT) pour insérer plusieurs tuples dans une seule table, en une seule fois. Exemple : insérer dans une table PRIMEPROJ, de même schéma que EMPLOYE, un tuple pour chaque chef de projet: INSERT INTO PRIMEPROJ SELECT DISTINCT PR.NoChef, EM.Nom, EM.Année, EM.NoDep FROM PROJET PR, EMPLOYE EM WHERE PR.NoChef = EM.NoDep;

  49. Interrogation des données (SELECT) SQL : langage de manipulation de données Combinaisons ensemblistes de requêtes : UNION : union ensembliste des résultats de 2 SELECT. MINUS ou EXCEPT: différence. INTERSECT : intersection. Exemple : liste des n° des employés qui travaillent dans un service de plus de 10 personnes ou qui occupent plus de 50% de leur temps sur un seul projet : SELECT NoEmp FROM EMPLOYE EM, DEPART DE WHERE EM.NoDep = DE.NoDep AND DE.Taille>10 UNION SELECT NoEmp FROM TRAVAILLE WHERE Temps > 50;

  50. SQL : langage de manipulation de données Réalisation des opérations de l’algèbre relationnelle en SQL Sélection: clause WHERE dans SELECT. SELECT * FROM TABLE_X WHERE Condition; Û Condition Table_X

More Related