Cours: Ingénierie de bases de données - 2024-2025 PDF

Cours: Ingénierie de bases de données Chapitre 3: SQL-LDD Pr. Houda Benhar Année universitaire 2024-2025  Objectifs pédagogiques UTILISER LA BASES DE DONNÉES CONSTRUIRE DES APPLIQUER D'AUTR...

Cours: Ingénierie de bases de données Chapitre 3: SQL-LDD Pr. Houda Benhar Année universitaire 2024-2025  Objectifs pédagogiques UTILISER LA BASES DE DONNÉES CONSTRUIRE DES APPLIQUER D'AUTRES MODÉLISATION RELATIONNELLES ET REQUÊTES SQL FONCTIONNALITÉS À RELATIONNELLE. SGBDR SIMPLES. VOS REQUÊTES SQL.  Plan Objectifs A la fin de ce chapitre, vous saurez: Créer des tables Décrire les différents types de données utilisables pour les définitions de colonne Modifier la définition des tables Supprimer, renommer et tronquer une table Définir les contraintes Créer des contraintes et les maintenir  Introduction Le langage SQL, acronyme de Structured Query Language, est un langage spécialement conçu pour gérer et manipuler des bases de données relationnelles. Il est composé de cinq sous ensembles: SQL Commands DDL DML DQL DCL TCL CREATE INSERT ALTER SELECT GRANT COMMIT UPDATE REVOKE DROP REOLLBACK DELETE Pour la recherche RENAME SAVEPOINT MERGE des informations Pour gérer les TRUNCATE CALL droits sur les Pour la gestion des Permet la définition et utiles en interrogeant LOCK TABLE objets de la base transactions la mise à jour de la la base de données … (création des (validation ou structure de la BD annulation de (tables, vues, champs, Pour l'insertion, la mise utilisateurs et à jour et la suppression affectation de modifications de index…) données dans la BD). de données… leurs droits).  Plande Données (DDL) Langage de Définition L’ordre CREATE TABLE L’ordre CREATE TABLE permet de créer une table en définissant le nom, le type de chacune des colonnes de la table. CREATE TABLE table (𝑐𝑜𝑙𝑜𝑛𝑛𝑒1 𝑡𝑦𝑝𝑒1 [DEFAULT expr], 𝑐𝑜𝑙𝑜𝑛𝑛𝑒2 𝑡𝑦𝑝𝑒2 ,…, 𝑐𝑜𝑙𝑜𝑛𝑛𝑒𝑛 𝑡𝑦𝑝𝑒𝑛 ); table est le nom que l’on donne à la table. 𝑐𝑜𝑙𝑜𝑛𝑛𝑒𝑖 est le nom d’une colonne. 𝑡𝑦𝑝𝑒𝑖 est le type des données contenues dans 𝑐𝑜𝑙𝑜𝑛𝑛𝑒𝑖 Exemple: SQL> CREATE TABLE etudiant 2 (cne NUMBER(2), 3 ename VARCHAR2(14)); Table created.  Plande Données (DDL) Langage de Définition Conventions de dénomination Un nom : Doit commencer par une lettre Peut comporter de 1 à 30 caractères Ne peut contenir que les caractères A à Z, a à z, 0 à 9, _, $, et # Ne doit pas porter le nom d’un autre objet appartenant au même utilisateur Ne doit pas être un mot réservé d’Oracle: ALL, AND, ANY, AS, ASC, BETWEEN, BY, CONNECT, CREATE, DATE, DELETE, DESC, DISTINCT, DROP, ELSE, EXISTS, FROM, GROUP, HAVING, IN, INSERT, INTERSECT, INTO, IS, JOIN, LIKE, NOT, NULL, OR, ORDER, SELECT, SET, TABLE, THEN, TRUNCATE, UNION….  Plande Données (DDL) Langage de Définition Types de données Type de données Description Entiers INT ou INTEGER Entier (jusqu'à 38 chiffres) SMALLINT Entier court (généralement de -32768 à 32767, soit 5 chiffres) NUMBER Entier long (jusqu'à 38 chiffres) Nombres décimaux DECIMAL(p, s) ou Nombre décimal précis avec p (precision) chiffres au total, dont NUMERIC(p, s) s (scale) chiffres après la virgule. Chaînes de CHAR(n) Chaîne de caractères de longueur fixe n. caractères VARCHAR(n) ou Chaîne de caractères de longueur variable avec une longueur VARCHAR2(n) maximale de n. Date et heure DATE Date au format 'YYYY-MM-DD'. Booléens BOOLEAN Valeurs TRUE, FALSE ou NULL Grands objets CLOB Pour stocker des chaînes de caractères de grande taille (up to 4 Go) BLOB pour stocker des données binaires de grande taille, comme des images ou des fichiers (jusqu’à 4 Go)  Plande Données (DDL) Langage de Définition L’ordre CREATE TABLE Créez une table et insérez des lignes en associant l'ordre CREATE TABLE et l'option AS subquery. CREATE TABLE table AS subquery; Les noms de colonnes de la table créée auront les mêmes noms, types et tailles que celles de l ’interrogation Exemple: SQL> CREATE TABLE student_copy 2 AS 3 SELECT * FROM student; Table created.  Plande Données (DDL) Langage de Définition L’ordre ALTER TABLE Utilisez l'ordre ALTER TABLE pour: ALTER TABLE table Ajouter un champ ADD (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table Modifier un champ MODIFY (column datatype [DEFAULT expr] [, column datatype]...); ALTER TABLE table Supprimer un champ DROP COLUMN nom_colonne;  Plande Données (DDL) Langage de Définition L’ordre ALTER TABLE Exemples: SQL> ALTER TABLE student 2 ADD (address VARCHAR2(50)); Table altered. ALTER TABLE student MODIFY (address VARCHAR2(60)); Table altered. ALTER TABLE student DROP COLUMN address; Table altered.  Plande Données (DDL) Langage de Définition L’ordre DROP TABLE Cette commande permet de supprimer une table de la base de données. Les lignes de la table et la définition elle-même sont détruites. L’espace occupé par la table est libéré DROP TABLE table; Exemple: SQL> DROP TABLE student; Table dropped. Une fois exécuté, l'ordre DROP TABLE est irréversible  Plande Données (DDL) Langage de Définition L’ordre RENAME L'ordre RENAME est un ordre du LDD qui permet de renommer une table ou d’autres objets SQL RENAME old_name TO new_name; Exemple: SQL> RENAME student TO etudiant; Table renamed.  Plande Données (DDL) Langage de Définition L’ordre TRUNCATE TABLE L'ordre TRUNCATE TABLE: Supprime toutes les lignes d'une table Libère l'espace de stockage utilisé par la table SQL> TRUNCATE TABLE etudiant; Table truncated. Vous ne pouvez pas annuler un ordre TRUNCATE  Plande Données (DDL) Langage de Définition Les Contraintes Les contraintes contrôlent des règles de gestion au niveau d'une table. Les contraintes empêchent la suppression d'une table lorsqu'il existe des dépendances. Types de contraintes valides dans Oracle : PRIMARY KEY: spécifie la clé primaire d ’une table FOREIGN KEY: est un attribut d'une table qui fait référence à la clé primaire d'une autre table. NOT NULL: on impose que l ’attribut possède une valeur UNIQUE: interdit qu’une colonne contienne deux valeurs identiques CHECK(condition): permet de spécifier une contrainte qui doit être vérifiée à tout moment par les tuples de la table Si vous ne nommez pas une contrainte, Oracle8 Server créera un nom au format SYS_Cn. Vous pouvez créer une contrainte : - En même temps que la création de la table: au niveau table ou colonne - Une fois que la table est créée  Plande Données (DDL) Langage de Définition Les Contraintes CREATE TABLE table (column datatype [DEFAULT expr] [column_constraint], … [table_constraint]); Contrainte au niveau colonne column [CONSTRAINT constraint_name] constraint_type, Contrainte au niveau table column,... [CONSTRAINT constraint_name] constraint_type (column,...),  Plande Données (DDL) Langage de Définition Les Contraintes Exemple: CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), … deptno NUMBER(2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO));  Plande Données (DDL) Langage de Définition La Contrainte NOT NULL EMP La contrainte NOT NULL ne peut être EMPNO ENAME JOB... COMM DEPTNO définie qu'au niveau colonne, pas au niveau table. 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 SQL> CREATE TABLE emp( 7782 CLARK MANAGER 10 2 empno NUMBER(4), 7566 JONES MANAGER 20 3 ename VARCHAR2(10) NOT NULL,... 4 job VARCHAR2(9), 6 hiredate DATE, Contrainte NOT NULL Absence de contrainte Contrainte 7 sal NUMBER(7,2), (aucune ligne ne peut NOT NULL NOT NULL 8 comm NUMBER(7,2), avoir de valeur NULL (toute ligne peut avoir dans cette colonne) une valeur NULL dans 9 deptno NUMBER(2) NOT NULL); cette colonne)  Plande Données (DDL) Langage de Définition La Contrainte de clé UNIQUE contrainte de clé UNIQUE DEPT DEPTNO DNAME LOC ------ ---------- -------- Les contraintes de clé UNIQUE se 10 ACCOUNTING NEW YORK définissent au niveau colonne ou table. 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), A insérer Interdit 4 loc VARCHAR2(13), 50 SALES DETROIT (DNAME⎯SALES 5 CONSTRAINT dept_dname_uk existe déjà) UNIQUE(dname)); 60 BOSTON Autorisé  Plande Données (DDL) Langage de Définition La Contrainte CHECK Définit une condition que chaque ligne doit obligatoirement satisfaire Expressions interdites : Références aux pseudo-colonnes CURRVAL, NEXTVAL, LEVEL et ROWNUM Appels aux fonctions SYSDATE, UID, USER et USERENV Requêtes faisant référence à d'autres valeurs dans d'autres lignes..., deptno NUMBER(2), CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),...  Plande Données (DDL) Langage de Définition La Contrainte PRIMARY KEY clé PRIMAIRE DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON A insérer interdit (DEPTNO−20 20 MARKETING DALLAS existe déjà) FINANCE NEW YORK interdit (DEPTNO est NULL)  Plande Données (DDL) Langage de Définition La Contrainte PRIMARY KEY Une seule clé primaire peut être créée par table Les contraintes PRIMARY KEY peuvent être définies au niveau colonne ou table. La contrainte PRIMARY KEY est une colonne ou un ensemble de colonnes qui identifie de manière unique chaque ligne d'une table. SQL> CREATE TABLE dept( 2 deptno NUMBER(2), 3 dname VARCHAR2(14), 4 loc VARCHAR2(13), 5 CONSTRAINT dept_dname_uk UNIQUE (dname), 6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));  Plande Données (DDL) Langage de Définition La Contrainte FOREIGN KEY DEPT PRIMARY DEPTNO DNAME LOC KEY ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS... EMP EMPNO ENAME JOB... COMM DEPTNO FOREIGN ----- ----- ---------- ----- ------ KEY 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30... Interdit (DEPTNO⎯ A insérer n'existe pas dans la table DEPT 7571 FORD MANAGER... 200 9 7571 FORD MANAGER... 200 Autorisé  Plande Données (DDL) Langage de Définition La Contrainte FOREIGN KEY Les contraintes FOREIGN KEY peuvent être définies au niveau table ou colonne. SQL> CREATE TABLE emp( 2 empno NUMBER(4), 3 ename VARCHAR2(10) NOT NULL, 4 job VARCHAR2(9), 5 mgr NUMBER(4), 6 hiredate DATE, 7 sal NUMBER(7,2), 8 comm NUMBER(7,2), 9 deptno NUMBER(2) NOT NULL, 10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) 11 REFERENCES dept (deptno));  Plande Données (DDL) Langage de Définition Ajout d'une Contrainte Vous pouvez ajouter ou supprimer une contrainte, mais pas la modifier Vous pouvez activer ou désactiver des contraintes Pour ajouter une contrainte NOT NULL, utilisez la clause MODIFY ALTER TABLE table ADD [CONSTRAINT constraint] type (column); Exemple: SQL> ALTER TABLE emp 2 ADD CONSTRAINT emp_mgr_fk 3 FOREIGN KEY(mgr) REFERENCES emp(empno); Table altered.  Plande Données (DDL) Langage de Définition Suppression d'une Contrainte Supprimer de la table EMP la contrainte concernant le manager. SQL> ALTER TABLE emp 2 DROP CONSTRAINT emp_mgr_fk; Table altered. Supprimer la contrainte PRIMARY KEY de la table DEPT, ainsi que la contrainte FOREIGN KEY associée définie sur la colonne EMP.DEPTNO. SQL> ALTER TABLE dept 2 DROP PRIMARY KEY CASCADE; Table altered.  Plande Données (DDL) Langage de Définition Activation/Désactivation de Contraintes Pour désactiver une contrainte d'intégrité, utiliser la clause DISABLE de l'ordre ALTER TABLE. Pour désactiver les contraintes d'intégrité dépendantes, ajouter l'option CASCADE. SQL> ALTER TABLE emp 2 DISABLE CONSTRAINT emp_empno_pk CASCADE; Table altered. Pour activer une contrainte d'intégrité actuellement désactivée dans la définition de la table, utiliser la clause ENABLE. SQL> ALTER TABLE emp 2 ENABLE CONSTRAINT emp_empno_pk; Table altered.  Plande Données (DDL) Langage de Définition Vérification des Contraintes Pour afficher les définitions et noms de toutes les contraintes, interrogez la table USER_CONSTRAINTS. SQL> SELECT constraint_name, constraint_type, 2 search_condition 3 FROM user_constraints 4 WHERE table_name = 'EMP'; CONSTRAINT_NAME C SEARCH_CONDITION ------------------------ - ------------------------- SYS_C00674 C EMPNO IS NOT NULL SYS_C00675 C DEPTNO IS NOT NULL EMP_EMPNO_PK P...  Plande Données (DDL) Langage de Définition Vérification des Contraintes Affichez les colonnes associées aux noms de contraintes au moyen de la vue USER_CONS_COLUMNS SQL> SELECT constraint_name, column_name 2 FROM user_cons_columns 3 WHERE table_name = 'EMP'; CONSTRAINT_NAME COLUMN_NAME ------------------------- ---------------------- EMP_DEPTNO_FK DEPTNO EMP_EMPNO_PK EMPNO EMP_MGR_FK MGR SYS_C00674 EMPNO SYS_C00675 DEPTNO