Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

15/12/2014 Partie I: langage de définition de données Pr: E. Saad 15/12/2014 La

15/12/2014 Partie I: langage de définition de données Pr: E. Saad 15/12/2014 Langage de définition des Données Un ordre du LDD permet de créer , de supprimer et de modifier la structure d’une base de données relationnelle; les objets que le LDD peut gérer sont: • Table • Vue • Séquence • Index • Synonyme 15/12/2014 Création de Tables Syntaxe : Les identificateurs utilisés doivent respecter les règles suivantes: •Commencer par une lettre et contenir que les caractères A à Z, 0 à 9, _, $, et # (30 caractères maximum). •Être différent des autres objets appartenant au même utilisateur et des mots réservé à Oracle CREATE TABLE nomtable (champs typechamps [DEFAULT valeur], ...); CREATE TABLE nomtable [(champs1, champs2...)] as sousinterrogation; 15/12/2014 Types de données Description VARCHAR2(taille) Données caractères de longueur variable (2000 caractères maximum) CHAR(taille) Données caractères de longueur fixe (255 caractères maximum) NUMBER(taille,décimale) Numérique de longueur variable DATE Valeurs de date et d'heure LONG Données caractères de longueur variable, jusqu'à 2 giga-octets LONG RAW Binaire(image) Création de Tables 15/12/2014 CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(15), loc VARCHAR2(12)); CREATE TABLE Empclerk AS SELECT empno mat, ename, nom, sal + comm as salaire, hiredate, deptno FROM emp WHERE job like 'clerk'); Création de Tables 15/12/2014 Modification de la structure d’une Tables Syntaxe : L'ordre ALTER TABLE permet d’ajouter de nouveau champs ou de modifier des champs existants. ALTER TABLE Nomtable ADD (champs typechamps [DEFAULT valeur] [, champs typechamps]...); ALTER TABLE Nomtable MODIFY (champs typechamps [DEFAULT valeur] [, champs typechamps]...); ALTER TABLE Nomtable DROP COLUMN champs ; ALTER TABLE table RENAME COLUMN ancien_nom TO nouveau_nom 15/12/2014 Gestion des tables TRUNCATE TABLE NomTable; Vider une Table RENAME AncienNomTable TO NouveauNomTable; Renommer une Table DROP TABLE Nomtable; Supprimer une Table 15/12/2014 Les Contraintes d’intégrités Les types de contraintes reconnues dans SQL sont: • NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK 15/12/2014 Les Contraintes d’intégrités CREATE TABLE [Schema.]NomTable (Champs TypeChamps [DEFAULT Valeur] [[CONSTRAINT NomContrainte] TypeContrainte], … , [[CONSTRAINT NomContrainte] TypeContrainte(Champs, ...),] … ); Syntaxe : ALTER TABLE NomTable ADD [CONSTRAINT NomContrainte] TypeContrainte(Champs); 15/12/2014 Les Contraintes d’intégrités CREATE TABLE Employes( empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(15) NOT NULL, job VARCHAR2(10), mgr NUMBER(4), hiredate DATE, sal NUMBER(8,2), comm NUMBER(8,2), deptno NUMBER(2) NOT NULL , CONSTRAINT un_dept UNIQUE (ename) ); ALTER TABLE Employes ADD CONSTRAINT Emp_Dept_Fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE CASCADE; ALTER TABLE Employes ADD CONSTRAINT CONSTRAINT CHECK (Comm > 0); 15/12/2014 Gestion des contraintes ALTER TABLE NomTable ENABLE CONSTRAINT NomContrainte [CASCADE]; Activer une Contraintes ALTER TABLE NomTable DISABLE CONSTRAINT NomContrainte [CASCADE]; Désactiver une Contraintes ALTER TABLE NomTable DROP CONSTRAINT NomContrainte [CASCADE]; Supprimer une contrainte 15/12/2014 Partie II: langage de manipulation de données Pr: E. Saad 15/12/2014 Le langage SQL ( Structured Query Longage : Langage d’interrogation structuré) , lié à la structure relationnelle des BD, est un langage non procédural, il comporte plusieurs commandes qui se repartissent en trois familles fonctionnellement distinctes: • Langage de manipulation des données (LMD): sélectionner, insérer, modifier, ou supprimer des données dans une table. • Langage de définition des données(LDD): créer des tables dans une BDR, ainsi d’en modifier ou de supprimer leur structure. • Langage de contrôle de données(LCD): gérer la sécurité et les permissions au niveau des utilisateurs d’une BDR. Aspect générale du langage SQL 15/12/2014 Tables utilisées dans le Cours Table Emp Table SALGRADE Table Dept 15/12/2014 L’ordre Select L’utilisation la plus fréquente de SQL s’effectue dans les requêtes afin de rechercher les données dans une base de données. Syntaxe select [distinct] * | <liste des champs > From <Liste des tables> [where <critère de sélection >] [group by <critères de regroupement>] [having <conditions de filtrage sur les groupes>] [order by <liste des champs > asc | desc ] ; 15/12/2014 Écriture des Ordres SQL • Les ordres SQL peuvent être écrits indifféremment en majuscules et/ou minuscules. • Les ordres SQL peuvent être écrits sur plusieurs lignes. • Les mots-clés ne doivent pas être abrégés ni scindés sur deux lignes différentes. • Les clauses sont généralement placées sur des lignes distinctes. 15/12/2014 Exemple de SELECT DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SELECT * FROM dept; DEPTNO LOC --------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON SELECT deptno, loc FROM dept; 15/12/2014 SELECT deptno FROM emp; DEPTNO --------- 10 30 10 20 ... 14 rows selected. DEPTNO --------- 10 20 30 SELECT DISTINCT deptno FROM emp; Exemple de SELECT DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SELECT * FROM dept; 15/12/2014 L’Alias de Colonne • Permet de renommer un en-tête de colonne (champs) • Suit immédiatement le nom de la colonne ; le mot-clé AS peut être placé entre le nom et l’alias est optionnel • Doit obligatoirement être inclus entre guillemets (") s’il contient des espaces, des caractères spéciaux ou si les majuscules/minuscules doivent être différenciées SELECT ename AS name, sal salary FROM emp; 15/12/2014 Le littéral • Un littéral est un caractère, une expression, ou un nombre inclus dans la liste SELECT. • Les valeurs littérales de type date et caractère doivent être placées entre simples quotes ('). SELECT ename, job, deptno FROM emp WHERE job='CLERK'; ENAME JOB DEPTNO ---------- --------- --------- JAMES CLERK 30 SMITH CLERK 20 ADAMS CLERK 20 MILLER CLERK 10 15/12/2014 Les opérateurs 15/12/2014 Utilisation des Opérateurs ENAME SAL ---------- --------- MARTIN 1250 TURNER 1500 WARD 1250 ADAMS 1100 MILLER 1300 SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 1500; Limite inférieure Limite supérieure SELECT empno, ename, sal, mgr FROM emp WHERE mgr IN (7902, 7566, 7788); EMPNO ENAME SAL MGR --------- ---------- --------- --------- 7902 FORD 3000 7566 7369 SMITH 800 7902 7788 SCOTT 3000 7566 7876 ADAMS 1100 7788 15/12/2014 Utilisation des Opérateurs de Comparaison SELECT ename FROM emp WHERE ename LIKE 'S%'; SELECT ename FROM emp WHERE ename LIKE '_A%'; ENAME ---------- JAMES WARD 15/12/2014 Utilisation des Opérateurs logiques SELECT ename, job FROM emp WHERE job NOT IN ('CLERK','MANAGER','ANALYST'); ENAME JOB ---------- --------- KING PRESIDENT MARTIN SALESMAN ALLEN SALESMAN TURNER SALESMAN WARD SALESMAN ... WHERE ... NOT BETWEEN ... AND ... ... WHERE ... IS NOT IN ... ... WHERE ... NOT LIKE ... ... WHERE ... IS NOT NULL 15/12/2014 Fonction de regroupement de données Les fonctions de groupe agissent sur des groupes de lignes et donnent un résultat par groupe • AVG ([DISTINCT|ALL]n) • COUNT ({ *|[DISTINCT|ALL]expr}) • MAX ([DISTINCT|ALL]expr) • MIN ([DISTINCT|ALL]expr) • SUM ([DISTINCT|ALL]n) 15/12/2014 Utilisation des fonctions de regroupement AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------- 1400 1600 1250 5600 SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal) FROM emp WHERE job LIKE 'SALES%'; COUNT(*) --------- 6 SELECT COUNT(*) FROM emp WHERE deptno = 30; 15/12/2014 Utilisation des fonctions de regroupement SELECT AVG(NVL(comm,0)) FROM emp; AVG(NVL(COMM,0)) ---------------- 157.14286 SELECT AVG(comm) FROM emp; AVG(COMM) --------- 550 15/12/2014 Création de Groupes de Données EMP EMP "salaire moyen pour chaque département de la table EMP" "salaire moyen pour chaque département de la table EMP" 2916.6667 2916.6667 2175 2175 1566.6667 1566.6667 DEPTNO SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTNO AVG(SAL) ------- --------- 10 2916.6667 20 2175 30 1566.6667 SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; 15/12/2014 Exemple de création de groupes de données SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job; DEPTNO JOB SUM(SAL) --------- --------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 ... 9 rows selected. 15/12/2014 SELECT max(avg(sal)) FROM emp GROUP BY deptno; MAX(AVG(SAL)) ------------- 2916.6667 Imbrication des Fonctions de Groupe 15/12/2014 Utilisation de la clause HAVING La clause HAVING permet restreindre les groupes • Les lignes sont regroupées. • La fonction de groupe est appliquée. • Les groupes qui correspondent à la clause HAVING sont affichés. 15/12/2014 Exemple de l’utilisation de HAVING SELECT deptno, max(sal) FROM emp GROUP BY deptno HAVING max(sal)>2900; DEPTNO MAX(SAL) --------- --------- 10 5000 20 3000 15/12/2014 Exemple de l’utilisation de HAVING SELECT job, SUM(sal) PAYROLL FROM emp WHERE job NOT LIKE 'SALES%' GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal); JOB PAYROLL --------- --------- ANALYST 6000 MANAGER 8275 15/12/2014 Les Sous-Interrogations Syntaxe SELECT select_list FROM tables WHERE expr operator (SELECT select_list FROM tables… ) …; • La sous-interrogation (requête interne) est exécutée une fois avant la requête principale (une sous-interrogation ne doit pas contenir la clause ORDER BY). • Le résultat de la sous-interrogation est utilisé par la requête principale (externe). 15/12/2014 Types de Sous-Interrogations Sous-interrogation mono-ligne Sous-interrogation multi-ligne Opérateurs mono-ligne Opérateurs multi-ligne 15/12/2014 Exemple de Sous-Interrogations mono- ligne CLERK 1100 ENAME JOB ---------- --------- MILLER CLERK SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'ADAMS') AND sal > (SELECT sal FROM emp WHERE ename = 'ADAMS'); 15/12/2014 Exemple de Sous-Interrogations mono- ligne 800 ENAME JOB SAL ---------- uploads/s3/ cours-oracle 1 .pdf