1 / 94

Licence de Sciences et Techniques Unité d’enseignement BASES DE DONNEES Langage de manipulation

François Jacquenet Professeur d'Informatique Faculté des Sciences Laboratoire Hubert Curien – UMR CNRS 5516 18 rue Benoit Lauras 42023 Saint-Etienne cedex 2 Tél : 04 77 91 58 07 e-mail : Francois.Jacquenet@univ-st-etienne.fr Web : http://eurise.univ-st-etienne.fr/~fj.

tokala
Télécharger la présentation

Licence de Sciences et Techniques Unité d’enseignement BASES DE DONNEES Langage de manipulation

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. François Jacquenet Professeur d'Informatique Faculté des Sciences Laboratoire Hubert Curien – UMR CNRS 5516 18 rue Benoit Lauras 42023 Saint-Etienne cedex 2 Tél : 04 77 91 58 07 e-mail : Francois.Jacquenet@univ-st-etienne.fr Web : http://eurise.univ-st-etienne.fr/~fj Licence de Sciences et Techniques Unité d’enseignement BASES DE DONNEES Langage de manipulation SQL

  2. Information pour la semaine du 25 au 29 septembre Cours, TD et TP aux horaires prévus sur l’emploi du temps

  3. Aujourd’hui nous verrons • SQL = Langage de définition de données • CREATE TABLE • ALTER TABLE • DROP TABLE • SQL = Langage de manipulation de données • INSERT INTO • UPDATE • DELETE FROM • SQL = Langage de requêtes • SELECT … FROM … WHERE ... • Sélection • Projection • Jointure • Produit cartésien • Union • Intersection • Différence • Les agrégats

  4. Introduction • SQL : Structured Query Language • Inventé chez IBM (centre de recherche d'Almaden en Californie), en 1974 par Astrahan & Chamberlin dans le cadre de System R • Le langage SQL est normalisé • SQL2: adopté (SQL 92) • SQL3: adopté (SQL 99) • Standard d'accès aux bases de données relationnelles

  5. SQL : Trois langages en un • Langage de définition de données (LDD/DDL) • création de relations : CREATE TABLE • modification de relations: ALTER TABLE • suppression de relations: DROP TABLE • vues, index …. : CREATE VIEW ... • Langage de manipulation de données (LMD /DML) • insertion de tuples: INSERT • mise à jour des tuples: UPDATE • suppression de tuples: DELETE • Langage de requêtes (LMD/DML) • SELECT ….... FROM ....... WHERE ..........

  6. Terminologie • Relation Table • Tuple Ligne • Attribut Colonne

  7. SQL Un langage de définition de données

  8. Un langage de définition de données • Commandes pour créer, modifier et supprimer les éléments du schéma • CREATE TABLE : créer une table (une relation), • CREATE VIEW : créer une vue particulière sur les données à partir d’un SELECT, • DROP {TABLE ¦ VIEW } : supprimer une table ou une vue, • ALTER {TABLE ¦ VIEW } : modifier une table ou une vue.

  9. CREATE TABLE Commande créant une table en donnant son nom, ses attributs et ses contraintes CREATE TABLE nom_table { ( nom-col type-col [DEFAULT val] [ [CONSTRAINT] contrainte-col] )* [ [CONSTRAINT] contrainte-table] | AS requête-SQL }; Légende : • {a | b} : a ou b, • [option]; • * : applicable autant de fois que souhaité; • mots en capitale : mots-clé.

  10. CREATE TABLE CREATE TABLEnom_table { ( nom-col type-col [DEFAULT val] [ [CONSTRAINT] contrainte-col] )* [ [CONSTRAINT] contrainte-table] | AS requête-SQL }; Exemples: CREATE TABLEDoctorant (nom VARCHAR(20), prenom VARCHAR(15), annee_insc YEAR DEFAULT 2002) ; CREATE TABLEDoctorant AS SELECT nom, prenom, annee_inscr FROM Etudiant WHERE statut='Doctorant';

  11. Type des colonnes (en MySQL) • Numériques • NUMERIC : idem DECIMAL • DECIMAL. Possibilité DECIMAL(M,D) M chiffre au total • INTEGER • TINYINT 1 octet (de -128 à 127) • SMALLINT 2 octets (de -32768 à 32767 • MEDIUMINT 3 octets (de -8388608 à 8388607) • INT 4 octets (de -2147483648 à 2147483647) • BIGINT 8 octets (de -9223372036854775808 à 9223372036854775807) • Possibilité de donner la taille de l’affichage : INT(6) => 674 s’affiche 000674 • Possibilité de spécifier UNSIGNED • INT UNSIGNED => de 0 à 4294967296 • FLOAT : 4 octets par défaut. Possibilité d’écrire FLOAT(P) • REAL : 8 octets • DOUBLE PRECISION : 8 octets

  12. Type des colonnes (en MySQL) • Date et Heure • DATETIME • AAAA-MM-JJ HH:MM:SS • de 1000-01-01 00:00:00 à '9999-12-31 23:59:59 • DATE • AAAA-MM-JJ • de 1000-01-01 à 9999-12-31 • TIMESTAMP • Date sans séparateur AAAAMMJJHHMMSS • TIME • HH:MM:SS (ou HHH:MM:SS) • de -838:59:59 à 838:59:59 • YEAR • YYYY • de 1901 à 2155

  13. Type des colonnes (en MySQL) • Chaînes • CHAR(n) 1 ≤ n ≤ 255 • VARCHAR(n) 1 ≤ n ≤ 255 Exemple :

  14. Type des colonnes (en MySQL) • Chaînes • TINYBLOB Taille < 2^8 caractères • BLOB Taille < 2^8 caractères • MEDIUMBLOB Taille < 2^24 caractères • LONGBLOB Taille < 2^32 caractères • TINYTEXT Taille < 2^8 caractères • TEXT Taille < 2^8 caractères • MEDIUMTEXT Taille < 2^24 caractères • LONGTEXT Taille < 2^32 caractères Les tris faits sur les BLOB tiennent compte de la casse, contrairement aux tris faits sur les TEXT.

  15. Type des colonnes (en MySQL) • ENUM • Enumération • ENUM("un", "deux", "trois") • Valeurs possibles : "" , "un", "deux", "trois"   • Au plus 65535 éléments • SET • Ensemble • SET("un", "deux") • Valeurs possibles : "" , "un", "deux", "un,deux"   • Au plus 64 éléments

  16. Contraintes • Contrainte-col : contrainte sur une colonne • NOT NULL • UNIQUE • PRIMARY KEY • REFERENCES nom_table [(nom-col)] [action] • CHECK ( condition) • Contrainte-table : contraintes sur une table • UNIQUE (nom-col)* • PRIMARY KEY (nom-col)* • FOREIGN KEY (nom-col)* REFERENCES nom_table [(nom-col)*] [action] • CHECK ( condition)

  17. Contraintes NOT NULL / UNIQUE • NOT NULL • Après une colonne • CREATE TABLE Pays (nom VARCHAR(20) NOT NULL, … • UNIQUE • Clé secondaire, pas deux lignes avec la même valeur, éventuellement null • Exemples : CREATE TABLE Etudiant ( no_etudiant INT UNIQUE, … CREATE TABLE Etudiant ( no_etudiant INT CONSTRAINT no_etu_u UNIQUE, …

  18. Contraintes PRIMARY KEY • PRIMARY KEY : IDENTIFIANT de la relation • UNIQUE + NOT NULL • Exemple: • CREATE TABLE Dept (dpt_id INT(4) PRIMARY KEY, … • CREATE TABLE Employé ( nom VARCHAR(30), prénom VARCHAR(30), adresse VARCHAR(60), …, CONSTRAINT PK_emp PRIMARY KEY (nom, prénom)) ; • AUTO_INCREMENT • UNIQUE et PRIMARY KEY sont incompatibles

  19. Contraintes d'intégrité référentielle • Foreign key : att1 REFERENCES att2 • référence soit une primary key soit une unique key. • Exemple: • CREATE TABLE Dept (dpt_id INT(4) PRIMARY KEY, manager_id INT(4) CONSTRAINT fk_mgr REFERENCES EMPLOYEE(emp_id) , … ) • CREATE TABLE Dept (dpt_id INT(4) PRIMARY KEY, manager_id INT(4), …, CONSTRAINT fk_mgr FOREIGN KEY (manager_id) REFERENCES EMPLOYEE(emp_id), …

  20. Actions déclenchées • REFERENCES nom_table [(nom-col)] [action] • Qu'est ce qui se passe quand on détruit/MAJ une clé primaire ou unique qui est référencée par un tuple (foreign key) d'une autre table? • CREATE TABLE Dept (dpt_id INT(4) PRIMARY KEY, manager_id INT(4) CONSTRAINT fk_mgr REFERENCES EMPLOYEE(emp_id), … • Si on a le tuple (dpt_id=1, manager_id=21), que se passe-t-il si on détruit ou met à jour l'employé d'identifiant 21 dans la table Employé?

  21. Actions déclenchées • Deux circonstances • ON DELETE • ON UPDATE • Trois options • SET NULL • SET DEFAULT: valeur par défaut si elle existe, sinon NULL • CASCADE : on répercute la mise à jour • Exemple : • CREATE TABLE Dept (dpt_id INT(4) PRIMARY KEY, manager_id INT(4) CONSTRAINT fk_mgr REFERENCES EMPLOYEE(emp_id) ONDELETESETNULL ONUPDATECASCADE) ;

  22. Contrainte CHECK • Condition que chaque ligne de la table doit vérifier • Si contrainte de colonne, porte uniquement sur cette colonne sinon porte sur plusieurs colonnes. • Exemple: • CREATE TABLE divisions ( div_no INT CONSTRAINT check_divno CHECK (div_no BETWEEN 10 AND 99), div_name VARCHAR(9) CONSTRAINT check_divname CHECK (div_name = UPPER(div_name)), office VARCHAR(10) CONSTRAINT check_office CHECK (office IN ('BOSTON', 'PARIS','TOKYO')) );

  23. Contrainte CHECK • CREATE TABLE Employee ( employe_id INT(4) PRIMARY KEY, nom VARCHAR(10), job_id VARCHAR(9), manager_id INT(4), salaire DECIMAL(7,2), Commission DECIMAL(7,2), departement_id SMALLINT(2), CONSTRAINT check_sal CHECK (salaire * commission <= 5000)); • Si on cherche à entrer une valeur ne vérifiant pas la contrainte, elle est rejetée.

  24. Statut des contraintes • Comment et quand vérifier une contrainte? • DEFFERABLE / NOT DEFFERABLE • ENABLE / DISABLE • VALIDATE / NO VALIDATE • Peut être spécifié après chaque contrainte (de colonne ou de la table) • ENABLE / DISABLE • Activation/désactivation d'une contrainte • Si contrainte non vérifiée, on ne peut pas l'activer • On ne peut pas activer une contrainte FK qui référence une contrainte non active

  25. DROP TABLE • DROP TABLE : Supprimer une table • supprime la table et tout son contenu • DROP TABLE nom_table [CASCADE CONSTRAINTS]; • CASCADE CONSTRAINTS • Supprime toutes les contraintes référençant une clé primaire (primary key) ou une clé unique (UNIQUE) de cette table • Si on cherche à détruire une table dont certains attributs sont référencés sans spécifier CASCADE CONSTRAINT, on a un message d'erreur.

  26. ALTER TABLE • Modifier la définition d’une table: • Changer le nom de la table mot clé : RENAME • Ajouter une colonne ou une contrainte mot clé : ADD • Modifier une colonne ou une contrainte mot clé : MODIFY • Supprimer une colonne ou une contrainte mot clé : DROP • renommer une colonne ou une contrainte mot clé : RENAME

  27. ALTER TABLE Syntaxe : ALTER TABLE nom-table { RENAME TO nouveau-nom-table | ADD (( nom-col type-col [DEFAULT valeur] [contrainte-col])*) | MODIFY (nom-col [type-col] [DEFAULT valeur] [contrainte-col])* | DROPCOLUMN nom-col [CASCADECONSTRAINTS] | RENAMECOLUMN old-name TO new-name };

  28. Renommer une table • ... RENAME TO nouveau-nom-table • Exemple : ALTER TABLE country RENAMETO pays

  29. Ajouter une colonne • … ADD ( (nom-col type-col [DEFAULT valeur] [contrainte-col])* ) • Exemple : ALTER TABLE country ADD ( duty DECIMAL(2,2) DEFAULT 1 CHECK (duty < 10), visa_needed VARCHAR(3) );

  30. Modifier une colonne • …MODIFY ( (nom-col [type-col] [DEFAULT valeur] [contrainte-col])* ) • Exemple : ALTER TABLE pays MODIFY (duty DECIMAL(3,2)); • Tout ce qui n'est pas spécifié dans le MODIFY n'est pas modifié, par exemple ici default et check

  31. Supprimer une colonne • …DROP COLUMN nom-col [CASCADECONSTRAINTS] • Exemple : ALTERTABLE pays DROPCOLUMN visa_needed CASCADECONSTRAINTS;

  32. Renommer une colonne • … RENAME COLUMNancien_nom TOnouveau_nom • Exemple : ALTER TABLE pays RENAME COLUMN dutyTOtaxe;

  33. ALTER TABLE sur les contraintes ALTER TABLEnom_table { ADD contrainte_table | DROP { PRIMARY KEY | UNIQUE ( nom_colonne)* | CONSTRAINT nom_contrainte } [CASCADECONSTRAINTS] | RENAMECONSTRAINT ancien TO nouveau | MODIFY [CONSTRAINT] nom_contrainte statut_contrainte };

  34. Ajouter une contrainte • … ADD contrainte-table • Exemple : ALTER TABLE employes ADD CONSTRAINT check_comp CHECK (salaire +(commission*salaire) <= 5000); ALTER TABLE pays ADD CONSTRAINT pk_pays PRIMARY KEY (nom);

  35. Supprimer une contrainte • DROP { PRIMARY KEY | UNIQUE ( nom-colonne)* | CONSTRAINT nom-contrainte } [CASCADECONSTRAINTS] • Exemple : ALTERTABLE departements DROPPRIMARY KEY CASCADE CONSTRAINTS;

  36. Statut des contraintes • ENABLE • VALIDATE : la contrainte est activée et contrôle que les données de la table vérifient la contrainte • NOVALIDATE : la contrainte est activée, les nouvelles données entrées dans la BD devront vérifier la contrainte. • DISABLE • VALIDATE : on cherche à désactiver la contrainte, si les données ne sont pas valides, erreur. Après DISABLE, on ne peut plus entrer, modifier ou supprimer des données de la table. • NOVALIDATE : on peut faire n'importe quelle opération y compris entrer des données non conformes à la contrainte

  37. Exemples • … MODIFY [CONSTRAINT] nom-contrainte statut_contrainte • Exemple : ALTER TABLE pays MODIFYCONSTRAINT pkc DISABLE Équivalent à: ALTER TABLE pays DISABLEPRIMARYKEY • Exemple : ALTER TABLE employes ENABLE VALIDATE PRIMARY KEY ENABLE NOVALIDATE CONSTRAINT nom_emp;

  38. SQL Un langage de manipulation de données

  39. Manipulation des données • INSERTINTO : ajouter un tuple dans une table ou une vue • UPDATE : changer les tuples d’une table ou d'une vue • DELETEFROM : éliminer les tuples d’une table ou d'une vue

  40. JO Année Lieu Pays 1896 Athènes Grèce 1900 Paris France 1904 St Louis USA 1908 Londres UK 1996 Atlanta USA JO Année Lieu Pays 1896 Athènes Grèce 1900 Paris France 1904 St Louis USA 1908 Londres UK 1996 Atlanta NULL INSERT INTO JO Année Lieu Pays 1896 Athènes Grèce 1900 Paris France 1904 St Louis USA 1908 Londres UK INSERT INTO JO VALUES (1996, ‘Atlanta’, ‘U.S.A’) INSERT INTO JO (Année, Lieu) VALUES (1996, ‘Atlanta’)

  41. INSERT INTO • Syntaxe : INSERTINTO {nom_table ¦ nom_vue} [ (nom_col (, nom_col)*) ] { VALUES (valeur (, valeur)*) ¦ sous-requête };

  42. UPDATE • Exemples : • UPDATEPays SET capitale = ‘Londres’ WHERE nom = ‘Ireland’ • UPDATEInfos SET esp_vie = esp_vie+2 , poids = poids * 2 WHERE continent = ‘Amérique' AND esp_vie < 80 • Syntaxe : • UPDATE {nom_table ¦ nom_vue} SET { (nom_col)* = (sous-requête) ¦ nom_col = { valeur ¦ (sous-requête)} }* WHERE condition;

  43. pays Nom Capitale Population Surface Continent Irlande Dublin 5 70 Europe Autriche Vienne 10 83 Europe UK Londres 50 244 Europe Suisse Berne 7 41 Europe USA Washington 350 441 Amérique DELETE FROM • Exemple : • DELETEFROMPays WHERE population > 300 • Syntaxe : • DELETEFROM {nom_table ¦ nom_vue} WHERE condition;

  44. SQL Un langage de requêtes

  45. Structure générale d'une requête • Structure d'une requête formée de trois clauses: SELECT <liste_attributs> FROM <liste_tables> WHERE <condition> • SELECT définit le format du résultat cherché • FROM définit à partir de quelles tables le résultat est calculé • WHERE définit les prédicats de sélection du résultat

  46. Exemple de requête SELECT * FROM pays Afficher tous les attributs de tous les tuples dans la table “pays” pays Nom Capitale Population Surface Irlande Dublin 5 70 Autriche Vienne 10 83 Royaume-Uni Londres 50 244 Suisse Berne 7 41 USA Washington 350 441

  47. SQL et Algèbre relationnelle

  48. SQL / Algèbre : Identité • En algèbre : Id(R) • En SQL : SELECT*FROMR

  49. SQL / Algèbre : Sélection • En algèbre : s[condition] R • En SQL : SELECT * FROMRWHEREcondition

  50. SQL / Algèbre : Sélection • SELECT * FROMpaysWHEREpopulation < 20 pays Nom Capitale Population Surface Irlande Dublin 5 70 Autriche Vienne 10 83 Royaume-Uni Londres 50 244 Suisse Berne 7 41 USA Washington 350 441

More Related