1 / 74

IFT2821 Base de données Chapitre 6 SQL

IFT2821 Base de données Chapitre 6 SQL. Plan du cours. Introduction Architecture Modèles de données Modèle relationnel Algèbre relationnelle SQL Conception Fonctions avancées Concepts avancées PL/SQL. Introduction. Permet de retrouver et de manipuler les données

crevan
Télécharger la présentation

IFT2821 Base de données Chapitre 6 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. IFT2821Base de donnéesChapitre 6SQL

  2. Plan du cours • Introduction • Architecture • Modèles de données • Modèle relationnel • Algèbre relationnelle • SQL • Conception • Fonctions avancées • Concepts avancées • PL/SQL

  3. Introduction • Permet de retrouver et de manipuler les données • Dérivé de SEQUEL 2 (76) lui-même dérivé de SQUARE (75) • Proposé par IBM (82 puis 87) • Première version normalisée SQL1 (ISO89) • Deuxième version normalisée SQL2 (ISO92) • SQL3 en cours de normalisation • Sert de couche basse aux L4G (par exemple Access)

  4. Langage SQL • Partie LDD • Conceptuel : CREATE SCHEMA, TABLE,… • Externe : CREATE VIEW, GRANT,… • Interne : CREATE INDEX, CLUSTER,… • Partie LMD • SELECT, INSERT, DELETE, UPDATE

  5. Environnement SQL Catalogue Ventes Schéma Ventes.Exportation Table Schéma Ventes.s2 Ventes.Exportation.Article Table View c1.s2.t1 Ventes.Exportation.ArticleSport Catalogue c2 Schéma c2.s1 Environnement SQL

  6. Environnement SQL (utilisateur) • Identificateur d ’utilisateur • authorizationID • Mécanisme d’authentification • e.g. mot de passe • Utilisateur possède privilèges • Exemple • CREATE user joe IDENTIFIED BY jpw • CONNECT joe/jpw

  7. Environnement SQL (Schéma) • DATABASE ~ catalogue • une instance Oracle monte une DATABASE à la fois • Nom du SCHEMA • = authorizationID du propriétaire (Oracle) • CREATE SCHEMA AUTHORIZATION blair • CONNECT balir/cmoi@blair [AS sysDBA]

  8. Environnement SQL (interprétation de requêtes) • Création d ’une table ou schéma • Transmise à l ’interprète du LDD • vérification • création de la table • schéma stocké dans dictionnaire de données • allocation des structures physiques

  9. Environnement SQL (Dictionnaire) • BD relationnelle : contient les méta-données d ’un CATALOG • DEFINITION_SCHEMA • tables • INFORMATION_SCHEMA • VIEWS sur les tables du DEFINITION_SCHEMA

  10. Environnement SQL (Dictionnaire) • SCHEMATA • les SCHEMA créés par CURRENT_USER • DOMAINS • les DOMAIN accessibles par CURRENT_USER ou PUBLIC • TABLES • les noms des tables accessibles par CURRENT_USER ou PUBLIC • VIEWS • les vues accessibles par CURRENT_USER ou PUBLIC • COLUMNS • les colonnes des TABLE accessibles par CURRENT_USER ou PUBLIC • TABLE_CONSTRAINTS • contraintes des TABLE créées par CURRENT_USER • CHECK_CONSTRAINTS • contraintes CHECK des TABLE créées par CURRENT_USER • TABLE_PRIVILEGES • privilèges accordés par CURRENT_USER, à CURRENT_USER, ou à PUBLIC

  11. Environnement SQL (Dictionnaire)

  12. Type SQL • Numérique exact • INTEGER (ou INT) :2,3,5,..,299,.. • SMALLINT 2, 3, 459,… • NUMERIC(p, c) (ou DECIMAL(p, c) ou DEC(p, c)) • Nombre décimal avec p chiffres significatifs et c chiffres après le point • Exemples : 2.5, 456.342, 6

  13. Type SQL • Numérique approximatif • REAL : Point flottant • Exemples : 3.27E-4, 24E5 • DOUBLE PRECISION : Point flottant à double précision • Exemples : 3.27265378426E-4, 24E12 • FLOAT(n) :Point flottant (précision minimale est de n chiffres pour la mantisse) • Exemples : 3.27E-4, 24E5

  14. Type SQL • Chaîne de caractères • CHARACTER(n) (ou CHAR(n)) :Chaîne de caractère de taille fixe égale à n • Exemples :  ’Adresse', 'Paul LeGrand ’ • CHARACTER VARYING (n) (ou VARCHAR(n)) : Taille variable (max de n caractères) • NATIONAL CHARACTER(n) :Ensemble de caractères alternatif spécifique à l'implémentation • NATIONAL CHARACTER VARYING(n) :Taille variable

  15. Type SQL • Date et temps • DATE : année (quatre chiffres), mois (2 chiffres) et jour (2 chiffres) • Exemple : DATE '1998-08-25 ’ • ALTER SESSION SET NLS_DATE_FORMAT = ‘ DD/MM/YYYY ’ • TIME[(p)] : heure (2 chiffres), minutes (2 chiffres), secondes (2 +p chiffres) • Exemple : TIME '14:04:32.25 ’ • TIMESTAMP[(p)] : DATE + TIME • Exemple : TIMESTAMP '1998-08-25 14:04:32.25 ’ • INTERVAL : Représente un intervalle de temps • Exemple : INTERVAL '2' DAY

  16. Type SQL • Booléen • BIT (n) : Vecteur de n bits. • Exemples : B'00100110', X'9F ’ • BIT VARYING (n) : taille variable (max = n) • Données de grande taille • BINARY LARGE OBJECT (n) (BLOB(n)) n : taille en octets (ex: 1024, 5K, 3M, 2G) • Exemple : X ’52CF4 ’ (hexadecimal) • CHARACTER LARGE OBJECT (n) (CLOB(n))

  17. Type SQL (Oracle) • NUMBER(p,[c]) • numérique exact; p entre 1 et 38, c doit être entre -84 et +127 (défaut, c =0) • VARCHAR2(n) : n£ 4000 • RAW(n) : • Binaire de taille n octets (n£ 2000) • LONG(n) : • Chaîne de caractères de taille variable (n£ 2G). Maximum une colonne LONG par table • LONG RAW(n) • Binaire de taille variable (n£ 2G). Maximum une colonne de type LONG RAW par table

  18. Type SQL (Oracle) • ROWID : identifiant de ligne composé de • identificateur de fichier • identificateur de bloc relatif au fichier • identificateur de ligne relatif au bloc • Conversions implicites

  19. Définition des données • Domaine • Exemple CREATE DOMAIN SSS_TYPE AS CHAR(9) (sql2) CREATE TYPE COULEUR AS OBJECT (Oracle) nomCouleur CHAR(6) DEFAULT 'vert' CONSTRAINTCOULEUR_VALIDECHECK (VALUE IN 'rouge', 'blanc', 'vert', 'bleu', 'noir'); • Destruction d’un domaine DROP TYPEdomaine[RESTRICT | CASCADE]

  20. Définition des données • Définition d’une table (1) CREATETABLE<table name> (<column name> <column type> [<attribute constraint>] {, <column name> <column type> [<attribute constraints>] } [<table constraint> {,<table constraint>}]) • Contraintes d’intégrité sur une table • NOT NULL • UNIQUE ou PRIMARY KEY • FOREIGN KEY • REFERENCES • CHECK

  21. CREATE TABLE nomDeLaTable ( spécificationDeColonne [, spécificationDeColonne]... [, spécificationDeContrainte]...) nomColonne [ type|domaine] [DEFAULT valeurDeDéfaut] [NULL | NOT NULL] [UNIQUE | PRIMARY KEY] [REFERENCES nomTable[ listeColonnes]] [[CONSTRAINT nomContrainte] CHECK ( conditionSQL)] [CONSTRAINT nomContrainte] {PRIMARY KEY listeColonnes| [ON DELETE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}] [ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT}]| CHECK ( conditionSQL) } [[NOT] DEFERRABLE INITIALLY {DEFERRED|IMMEDIATE}] Définition des données • Définition d’une table (2)

  22. Définition des données • Exemple CREATE TABLEDEPARTMENT (DNAME VARCHAR(15) NOT NULL, DNUMBERINT, MGRSSNCHAR(9) DEFAULT '888665555', MGRSTARTDATEDATE, CONSTRAINTDEPTPKPRIMARY KEY(DNUMBER), CONSTRAINTDEPTSKUNIQUE (DNAME) CONSTRAINTDEPTMGRFKFOREIGN KEY(MGRSSN) REFERENCESEMPLOYE(SSN)ON DELETE CASCADE);

  23. Définition des données • Table • Avec contraintes d ’intégrité • Exemple (SQL 86) • CREATE TABLE COMMANDE ( • NC NUMBER UNIQUE NOT NULL, • NV NUMBER NOT NULL • QUANTITE NUMBER(6)) • Exemple (SQL 89) • CREATE TABLE COMMANDE ( • NC NUMBER PRIMARY KEY, • NV NUMBER NOT NULL REFERENCES VIN, • QUANTITE NUMBER(6) CHECK(QUANTITE > 0)) • -------------------------------------------------------------------------------------------------- • NC NUMBER, • PRIMARY KEY (NC), • NV NUMBER NOT NULL, • FOREIGN KEY (NV) REFERENCES VIN, SQL 92

  24. Définition des données • Modification du schéma d’une table (1) ALTER TABLE <table> ADD <attribut> <type>, <attribut> <type>, ... • Exemple : ALTER TABLE DEPARTEMENT ADD DATE_CREATION DATE ; ALTER TABLE EMPLOYE DROP ADDRESS CASCADE; ALTER TABLE EMPLOYE DROP CONSTRAINT EMPSUPERFK CASCADE; • Suppression d’une table DROP TABLE <table> • Exemple : DROP TABLE EMPLOYE ; DROP TABLE DEPENDENT CASCADE;

  25. Définition des données • Modification du schéma d’une table (2) ALTER TABLE nomTable {ADD COLUMN spécificationColonne DROP COLUMN nomColonne[RESTRICT|CASCADE]| ADD spécificationContrainte| DROP nomContrainte [RESTRICT|CASCADE]| ALTER nomColonne SET DEFAULT valeurDéfaut| ALTER nomColonne DROP DEFAULT}

  26. Accès aux données • Clause SELECT SELECT <attributs> FROM <liste de tables> [WHERE <condition>] [GROUP BY <groupe(s) attribut(s) >] [HAVING <condition de groupe>] [ORDER BY <liste attributs>]; Exemple : SELECT BDATE, ADRESS FROM EMPLOYE WHERE FNAME='Jhon' AND LNAME='Smith'; BDATE ADRESS --------- ------------------------------------------------- 01-SEP-65 731Fondren Huston, TX

  27. Accès aux données • Exemple • Donner les noms et adresses des employés? SELECT BDATE, ADRESS FROM EMPLOYE BDATE ADRESS --------- ------------------------------------------------ 10-OCT-37 450 Stone, Huston, TX 08-DEC-55 638 Voss, Huston , TX 20-JUN-41 291 Berry, Bellaire, TX 15-SEP-62 975FireOak, Humble,TX 31-JUL-72 5631Rice, Humble, TX 19-JUL-68 3321 Castle,Spring, TX 29-MAR-69 980, Dallas, Huston, TX 01-SEP-65 731Fondren Huston, TX

  28. Accès aux données • Clause Where non spécifiée • Que fait la requête suivante ? SELECT * FROM EMPLOYE, DEPARTMENT ; • Clause Where mal spécifiée !!! • Que fait la requête suivante ? SELECT LNAME, FNAME, DNUMDER FROM EMPLOYE, DEPARTMENT WHERE DNUMBER = 5; Produit cartésien des tables EMPLOYE et DEPARTMENT ? ? ? ? ? ? ? ?

  29. Accès aux données • Exemple • Donner les noms et adresses des employés du département de recherche ? SELECT FNAME, LNAME, ADRESS FROM EMPLOYE, DEPARTMENT WHERE DNAME='Research' AND DNUMBER=DNO; FNAME LNAME ADRESS --------------- --------------- ------------------------------ Franklin Wong 638 Voss, Huston , TX Ramesh Narayan 975FireOak, Humble,TX Joyce English 5631Rice, Humble, TX Jhon Smith 731Fondren Huston, TX

  30. Accès aux données • Attributs ambiguës Exemple : • Donner les noms et adresses des employés qui travaillent au département de recherche ? (Supposons que l’attribut DNO de la table EMPLOYE s’appelle DNUMBER) SELECT FNAME, EMPLOYE.LNAME, ADRESS FROM EMPLOYE, DEPARTMENT WHERE DEPARTMENT.DNAME = 'Research' AND DEPARTMENT.DNUMBER=EMPLOYE.DNUMBER;

  31. Accès aux données • Attributs ambiguës Exemple : • Donner le nom et prénom de chaque employé ainsi que le nom et le prénom de son superviseur immédiat ? SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYE E, EMPLOYE S WHERE E.SUPERSSN=S.SSN; FNAME LNAME FNAME LNAME --------------- --------------- --------------- --------------- Franklin Wong James Borg Jennifer Wallace James Borg Ramesh Narayan Franklin Wong Joyce English Franklin Wong Alicia Zelaya Jennifer Wallace Ahmad Jabbar Jennifer Wallace Jhon Smith Franklin Wong

  32. Accès aux données • Tables & Ensembles Duplication des uplets : • Duplication permise des uplets SELECT ALL SALARY FROM EMPLOYE ; • Duplication non permise des uplets SELECT DISTINCT SALARY FROM EMPLOYE ; SALARY ---------- 55000 40000 43000 38000 25000 25000 25000 30000 SALARY --------- 25000 30000 38000 40000 43000 55000

  33. Accès aux données • Tables & Ensembles Union: Donner la liste des projets dont Smith est implique comme employé ou comme directeur du département qui contrôle ces projets ? (SELECT DISTINCT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYE WHERE DNUM = DNUMBER AND MGRSSN = SSN AND LNAME= 'Smith') UNION (SELECT DISTINCT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYE WHERE PNUMBER = PNO AND ESSN = SSN AND LNAME = 'Smith'); PNUMBER ------------- 1 2

  34. Accès aux données • Fonctions de calcul • SUM, AVG, MAX, MIN SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYE; SUM(SALARY) MAX(SALARY) MIN(SALARY) AVG(SALARY) ------------------- ------------------- -------------------- -------------------- 281000 55000 25000 35125 • Quel est le nombre des employés ? SELECT COUNT (*) FROM EMPLOYE; COUNT(*) ---------- 8

  35. Accès aux données • Fonctions de calcul • Que fait la requête suivante ? SELECT LNAME, FNAME FROM EMPLOYE WHERE (SELECT COUNT (*) FROM DEPENDENT WHERE SSN=ESSN) >= 2; Retourne nom et prénom des employés qui ont deux dépendants et plus. LNAME FNAME --------------- --------------- Smith Jhon

  36. Accès aux données • Fonctions d’agrégation • GROUP BY, HAVING Pour chaque projet retrouver son nom, son numéro et le nombre des employés qui y ont participé ? SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME; PNUMBER PNAME COUNT(*) --------------- ----------- ---------- 1 ProductX 2 2 ProductY 3 3 ProductZ 2 10 Computerization 3 20 Reorganization 3 30 Newbenefits 3

  37. Accès aux données • Fonctions d’agrégation • GROUP BY, HAVING Pour chaque projet qui implique plus de deux employés retrouver son nom, son numéro et le nombre d’employés qui y participent ? SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER = PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2; PNUMBER PNAME COUNT(*) --------------- ----------- ---------- 2 ProductY 3 10 Computerization 3 20 Reorganization 3 30 Newbenefits 3

  38. Accès aux données • Chaînes de caractères, opérateurs arithmétiques et ordonnancement • LIKE, BETWEEN, ORDER BY Retrouver tous les employés qui ont une adresse a Huston, Texas? SELECT FNAME, LNAME FROM EMPLOYE WHERE ADRESS LIKE '%Huston, TX%'; Retrouver tous les employés qui sont nés durant les années 1950 ? SELECT FNAME, LNAME FROM EMPLOYE WHERE BDATE LIKE'________5_';

  39. Accès aux données • Chaînes de caractères, opérateurs arithmétiques et ordonnancement Retrouver le salaire de chaque employé si on augmente de 10% les employés qui ont participé dans le projet ‘ProjectX’? SELECT FNAME, LNAME, 1.1*SALARY FROM EMPLOYE, WORKS_ON, PROJECT WHERE SSN=ESSN AND PNO=PNUMBER AND PNAME='ProjectX'; • Que fait la requête suivante ? SELECT * FROM EMPLOYE WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO = 5; FNAME LNAME 1.1*SALARY --------------- --------------- ---------- Jhon Smith 33000 Joyce English 27500

  40. Accès aux données • Chaînes de caractères, opérateurs arithmétiques et ordonnancement Retrouver la liste des employés qui sont impliqués dans des projets ; Cette liste doit triée par département (descendant), nom et prénom(ascendant). SELECT DNAME, LNAME, FNAME, PNAME FROM DEPARTMENT, EMPLOYE, WORKS_ON, PROJECT WHERE DNUMBER=DNO AND SSN=ESSN AND PNO=PNUMBER ORDER BY DNAME DESC, LNAME ASC, FNAME ASC;

  41. Accès aux données • Chaînes de caractères, opérateurs arithmétiques et ordonnancement DNAME LNAME FNAME PNAME --------------- --------------- --------------- --------------- Research English Joyce ProductX Research English Joyce ProductY Research Narayan Ramesh ProductZ Research Smith Jhon ProductX Research Smith Jhon ProductY Research Wong Franklin ProductY Research Wong Franklin ProductZ Research Wong Franklin Computerization Research Wong Franklin Reorganization Headquarters Borg James Reorganization Administration Jabbar Ahmad Computerization Administration Jabbar Ahmad Newbenefits Administration Wallace Jennifer Newbenefits Administration Wallace Jennifer Reorganization Administration Zelaya Alicia Newbenefits Administration Zelaya Alicia Computerization

  42. Accès aux données • Sous requêtes et comparaison d’ensembles • IN, SOME, ANY, ALL, EXIST, UNIQUE, NOT Retrouver le NAS des employé qui ont travaillé dans les mêmes projets et les mêmes nombres d ’heures que John Smith (NAS : 123456789) ? SELECT DISTINCT ESSN FROM WORKS_ON WHERE (PNO, HOURS) IN (SELECT PNO, HOURS FROM WORKS_ON WHERE ESSN='123456789'); Qu’obtient-on si on remplacer IN par =SOME puis par =ANY ?

  43. Accès aux données • Sous requêtes et comparaison d’ensembles • (IN, NOT) SOME, ANY, ALL, (EXIST, NOT) Retrouver les noms des employé qui gagnent un salaire supérieur a tous ceux qui travaillent dans le département de recherche? SELECT LNAME, FNAME FROM EMPLOYEE WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DNO=5); LNAME FNAME --------------- --------------- Borg James Wallace Jennifer

  44. Accès aux données • Sous requêtes et comparaison d’ensembles • IN, SOME, ANY, ALL, EXIST, NOT Retrouver les noms des employé qui n’ont pas de dépendant? SELECT FNAME, LNAME FROM EMPLOYE WHERE NOT EXISTS (SELECT * FROM DEPENDENT, EMPLOYE WHERE SSN=ESSN); Retrouver les noms des managers qui ont plus d’un dépendant? FNAME LNAME --------------- -------- James Borg Ramesh Narayan Joyce English Alicia Zelaya Ahmad Jabbar

  45. Accès aux données • Jointure de tables • JOIN, NATURAL JOIN Retrouver les noms et les adresses des employé qui travaillent dans le département de recherche ? SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYEJOIN DEPARTMENTON DNO=DNUMBER) WHERE NAME=‘Research’; SELECT FNAME, LNAME, ADDRESS FROM (EMPLOYENATURAL JOIN (DEPARTMENTAS DEPT (DNAME, DNO, MSSN, MSDATE))) WHERE DNAME=‘Research;

  46. Manipulation des données • Insertion INSERT INTO <table> [ (<liste_colonnes>)] {VALUES (<liste_valeurs>) | <requete>} INSERT INTO EMPLOYEE VALUES ('Richard','Marini', '653298653', '30-dec-1962','98 Oak Forest,Katy,TX','M', 37000, '987654321', 4); INSERT INTO EMPLOYE (FNAME, LNAME, DNO, SSN) VALUES ('Richard', 'Marini', 4, '653298653'); INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT (*), SUM (SALARY) FROM (DEPARTMENT JOIN EMPLOYE ON DNUMBER=DNO) GROUP BY DNAME;

  47. Manipulation des données • Mise à jour UPDATE <table> SET {<nom_colonne> = <expression>}+ WHERE {<condition>} UPDATE PROJECT SET PLOCATION = ‘Bellaire’, DNUM = 5 WHERE PNUMBER=10; UPDATE EMPLOYE SET SALARY = SALARY *1.1 WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME = 'Research');

  48. Manipulation des données • Suppression DELETE FROM <table> [WHERE {<condition> ] DELETE FROM EMPLOYE WHERE SSN=‘123456789’; DELETE FROM EMPLOYE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research'); • Que fait la requête suivante ? DELETE FROM EMPLOYE;

  49. Opérations ensemblistes • Différence Les employés qui n ’ont pas de dépendants. select ssn as Nass from employe minus select essn from dependent; Nass --------- 453453453 666884444 888665555 987987987 999887777 - Que fait la requête suivante ? select essn from dependent; minus select ssn as Nass from employe

  50. Opérations ensemblistes • Différence Les noms et prénoms des employés qui n’ont pas de dépendants. select lname as prenom from employe where ssn in (select ssn as Nass from employe minus select essn from dependent); ou select lname as prenom from employe, (select ssn as Nass from employe minus select essn from dependent) where ssn = Nass; • PRENOM • ----------- • English • Narayan

More Related