Système de Gestion de Base de Données

Systme de gestion de base de donnes

Plan Introduction aux bases de donnesAlgbre relationnelLangage SQL

Plusieurs tapes sont ncessaires la mise en place d'une base de donnes, ds lors que l'on a prcisment dfini ses besoins: la cration de la structure de la base sous forme de tables (tableaux de donnes) relies entre elles par des donnes cls, la conception des requtes qui permettront d'extraire ou de mettre jour les informations qu'elle contient, la conception de l'interface homme-machine (crans et tats) qui rendra plus conviviale la saisie et la restitution des informations.

Une bonne matrise de l'algbre relationnelle permet de concevoir n'importe quelle requte aussi complexe soit elle avant de la mettre en uvre l'aide du langage QBE ou SQL.

Cycle de vie dune base de donnes

SGBD(dfinition)Dfinition 1 : Une Base de donnes (BD) est une collections de donnes structures et cohrentes.Dfinition 2 : Un Systme de Gestion de Bases de Donnes (SGBD) est un logiciel permettant de manipuler les donnes dune BD.

Objectifs des SGBDFaciliter la manipulation de donnes. Faciliter ladministration des donnes: Un SGBD doit fournir les outils facilitant les taches dadministration de la BD. Efficacit daccs:Accs rapide, bons temps de rponse Contrle de la redondance: Diminution du volume de stockage. Partage de donnes:Plusieurs utilisateurs simultans. Scurit des donns:Gestion des droits daccs.

Modle de donnesPremires gnration : modle hirarchique et rseau. Modle relationnel: SGBD relationnel (Oracle, SQL Server, DB2, MySQL,Access) Modle objet: SGBD OO Modle relationnel-objet: intgrer les concepts de OO au modle relationnel (SGBD RO)

SGBD hirarchiqueCaractristiquesAccs vertical (haut vers bas et bas vers haut)Les chemins hirarchiques sont implments par des pointeursProblmesRedondance des donnes : une modification du solde dun seul compte ncessite plusieurs mises jourCoteux en espace mmoireAccs aux informations parfois difficiles et/ou coteuses : clients possdant un compte donn

SGBD rseauCompos denregistrements et de liensSaku231508 450 00024140175KoivuHannaKoivuPierreDelisle

SGBD rseauImplmentation avec des pointeursAvantagesRedondance limine, moins despace mmoireManipulations plus facilesInconvnientsAccs squentiel, donc lentDifficile programmer et maintenir

Les SGBD hirarchiques et rseaux sont aujourdhui considrs comme obsoltes

SGBD relationnel (SGBDR)Pas de pointeursUne table est compose deColonnesAnalogue un champ dun enregistrement dans un fichier de donnesPortent un nom unique lintrieur dune tablePeuvent faire partie de plusieurs tables (pour les lier)RangesAnalogue un enregistrement dans un fichier de donnesIdentifies par une cl unique (cl primaire) compose dune ou plusieurs colonnes

Modle relationnel: dfinition et vocabulaire Modle fond sur la thorie mathmatique des ensembles.Le modle de donnes le plus utilisModle simple : une seule structure : la relation Une relation dfini une table; une BD est une collection de tablesle modle relationnel est un modle d'organisation des donnes sous forme de Tables (Tableaux de valeurs) ou chaque Table reprsente une Relation, au sens mathmatique d'Ensemble.

Modle relationnel: dfinition et vocabulaireUne relation est un ensemble dattributs : A1, A2,.., AnExemple: la relation FILM est lensemble des attributs (idFilm, titre, anne, genre,codePays)Chaque attribut Aj prend ses valeurs dans un domaine DjEx. Domaine de genre est lensemble {Science Fiction, Suspense, Drame} Un domaine peut tre un type lmentaire comme lensemble des entiers, des chanes de caractres On peut ajouter des contraintes sur le domaine (ex. age >=0)

Modle relationnel: dfinition et vocabulaireUn tuple est un ensemble de valeurs T = o Vi appartient Di ou Vi est la valeur nulle (NULL).Cl primaire dune relation : est le plus petit sous ensemble des attributs qui permet didentifier chaque tuple de manire unique.Cl trangre : Attribut qui est la cl primaire dune autre relation

Modle relationnel: dfinition et vocabulaire Un schma dune relation est sa description : un nom suivi de la liste des attributs, chaque attribut tant associ son domaine.Notation : R (A1 : D1, A2 : D2,..An:Dn)Le schma dune base de donnes et lensemble de schmas des relations de cette BD.

Modle relationnel: dfinition et vocabulaire

Algbre relationnelleUn langage manipulant les relations. Il propose un ensemble doprateurs qui sappliquent aux relations et dont le rsultat est une relation. Oprateurs ensemblistes: union, diffrence, produit, cartsien Oprateurs spcifiques : projection, restriction, jointureIl permet dexprimer des requtes sur une base de donnes relationnelle.Matrise de l'algbre relationnelle : essentiel pour comprhension SQL et SGBDR.

Requte

Une requte est destine obtenir des informations prcises et ordonnes sur la base de donnes. On peut galement par le biais des requtes:effectuer des calculs,obtenir des statistiques,modifier les tables,crer de nouvelles tablesgrer des utilisateursPrincipe d'criture d'une requte:La plupart des requtes (ou interrogations) portant sur une base de donnes relationnelle ne peuvent pas tre ralises partir d'une seule opration mais en enchanant successivement plusieurs oprations

Oprateurs ensemblistesUnionOpration portant sur deux relations de mme schma RELATION1, et RELATION2 consistant construire une relation de mme schma RELATION3 ayant pour tuples ceux appartenant RELATION1 ou RELATION2 ou aux deux relationsL'union est une opration commutative. Notations : R1 R2, UNION(R1, R2)

Oprateurs ensemblistes: Union

Oprateurs ensemblistes: IntersectionLintersection consiste combiner deux relations (compatibles) pour crer une troisime relation qui contient toutes les occurrences appartenant lune et lautre des relations de dpart.Notation :R3 = R1 R2 OuR3 = INTERSECTION (R1, R2)

Oprateurs ensemblistes: Intersection

Oprateurs ensemblistes: DiffrenceDiffrenceOpration portant sur deux relations de mme schma RELATION1, et RELATION2 consistant construire une relation de mme schma RELATION3 ayant pour tuples ceux appartenant RELATION1 et nappartenant pas RELATION2Notation : R3 = R1 - R2 OuR3 = DIFFERENCE (R1, R2)

Oprateurs ensemblistes: Diffrence

Oprateurs ensemblistes: Produit cartsienDef.: Le produit cartsien entre les relations R1 et R2 de schmas quelconques, consiste construire une relation R3 qui a pour schma la concatnation de ceux de R1 et R2, et donc pour extension l'ensemble de toutes les combinaisons possibles entre les tuples de R1 et ceux de R2. Le produit cartsien de deux relations est une opration commutative (si pas d'ordre sur les colonnes).Opration trs courante Notations :

Oprateurs ensemblistes: Produit cartsienProduit cartsien: exemple 1

Oprateurs ensemblistes: Produit cartsienProduit cartsien: exemple 2

Oprateurs de base:Projectionprojection : Permet de ne retenir que quelques attributs d'une relationDef.: Soit la relation R1(a1, , an). La projection de la relation R1 sur les attributs (a1, , am), m

Oprateurs de base:ProjectionProjection : exemple

Oprateurs de base:ProjectionRemarque: La projection ne porte que sur 1 relation. Il permet de ne retenir que certains attributs spcifis d'une relation. On obtient tous les n-uplets de la relation l'exception des doublons.

Oprateurs de base: Restriction ou selectionRestriction: Opration sur une relation RELATION1 produisant une relation RELATION2 de mme schma, mais comportant les seuls tuples qui vrifient la condition prcise en argument.Notations : critre(R1), (R1)[critre], RESTRICT(R1, critre), R : critreAvec Critre de restriction :

Oprateurs de base: Restriction ou selectionRestriction: exemple 1

Oprateurs de base: Restriction ou slectionRestriction: exemple 2

Oprateurs de base: Restriction ou slectionSelection: exemple 3

Donner les rsultats de :- LesEmployes : (nom = Marie ) ;- LesEmployes : (nom = Marie ou(adresse = St-Egrve et salaire < 3000)) ;- (LesEmployes : (nom = Marie ou(non adresse = St-Egrve et salaire < 3000)))[nom] ;

Oprateurs de base: Restriction ou slectionCet oprateur porte sur 1 relation. Il permet de ne retenir que les n-uplets rpondant une condition exprime l'aide des oprateurs arithmtiques ( =, >, =,

JointureDfinition 1 : la jointure des deux relations R1 et R2 sous une condition est une relation ayant comme schma lunion des schmas et contenant comme tuples toutes les combinaisons des tuples de R1 et R2 satisfaisant cette condition.

Notation : R3 = R1 R2 (Expression conditionnelle),R3 = JOINTURE R1.R2 (Expression conditionnelle), R3=R1 (Expression conditionnelle) * R2Avec R1 et R2 sont deux relations, entre parenthses figure le critre de jointure

Jointure : dfinition Oprandes : une relation R de schma X ;une relation S de schma Y ;une condition PNotation : R (P) * SRsultat : relation T dont le schma est la concatnation de X et de Y ; et contenant tous les couples d'lments de R et S tels que P est vraie

Jointure:dfinition Opration majeure : Thoriquement, la jointure de deux relations est un produit cartsien entre ces deux relations,suivi de l'limination de certains tuples ne satisfaisant pas un critre de comparaison entre deux colonnes du rsultat du produit cartsien.C'est le seul oprateur exploitant les attributs rfrentiels inter-relations.Pas vraiment une opration de base : peut tre dfinie partir du produit cartsien et d'une restriction: R (P) * S = (R x S) : P Grande importance

Jointure: exempleJointure: exemple 1

Jointure: exempleJointure: exemple1(suite)

Jointure : exempleLa jointure = mise en correspondance de 2 tables selon un critre.

Jointure : typePlusieurs types de Jointures existent : Equijointure (ou jointure naturelle), Jointure externe gauche ou droiteEquijointure : le pivot utilise loprateur = (jointure naturelle)Thtajointure : le pivot utilise les oprateurs =, != ou

Jointure: Jointure naturelleLa jointure A JOIN B de deux tables A (X, Y) et B (Z, Y) est la table C avec les attributs : C (X, Y, Z)et les tuples (X:x, Y:y, Z:z ) tels que (x, y) est dans A et (y, z) est dans BJointure naturelle : jointure entre 2 relations avec critre d'galit (equi-jointure) entre 2 attributs de mme noms et fusion des colonnes de mme nom(s).

Jointure naturelle: exemple

Jointure naturelle: exempleClient(numro, nom, adresse, tlphone)Vente(numro, ref_produit, no_client, date)Produit(rfrence, marque, prix)

Jointure naturelle: exempleAfficher le nom des clients avec les dates de leurs achats

ExercicesAfficher, pour le client numro 125, le numro de vente et la marque des produits achetsSolution:

Phi (ou thta) Jointure

Semi - JointureDfinition : la semi-jointure des deux relations R1 et R2 est une relation ayant le schma de la relation R1 et contenant toutes les tuples participant la jointure.quivalent une jointure interne suivie par une projection sur les attributs de la relation R1.Notation:

Jointure externe(Outer Join)Quelque fois il est utile de conserver dans le jointure les tuples des deux relations qui ne peuvent pas tre fusionns.Exemple: donner la liste des employs avec leurs dpartements et les responsable, sil y a un responsableLe outer join tend par des valeurs NULL les tuples qui seraient enlevs par un join Le outer join existe dans trois formes: left (gauche), right (droite) , full (complte)

Jointure externe(Outer Join)

Jointure externe (full) outer join Dfinition : la jointure externe des deux relations R1 et R2 sous une condition est une relation ayant comme schma lunion des schmas et contenant comme tuples les tuples produites par une jointure interne et les tuples des relations R1 et R2 avec des valeurs NULL pour les attributs de lautre relation

Jointure externe: externe

Jointures externes gauche et droite: left join / right joinDfinition : la jointure externe gauche des deux relations R1 etR2 sous une condition est une relation ayant comme schma lunion des schmas et contenant comme tuples les tuples produites par une jointure interne et les tuples de la relation R1 avec des valeurs NULL pour les attributs de la relation R2Notation :LEFT-JOIN (R1, R2, CONDITION)Dfinition de la jointure externe droite de faon similaire

Jointure externe gauche : exemple

Division: dfinition Dfinition 1 : la division de la relation R1 par la relation R2 est la relation contenant des tuples qui,concatns tout tuple de R2, font partie des tuples de R1Condition :Schma(R1) =Schma(DIVISION(R1,R2)) + Schma(R2)

Division: dfinition

Oprandes : une relation R de schma X ; une relation S de schma Y inclus dans XNotation : R / S ou DIVISION(R1,R2)Rsultat : relation T de schma X - Y ne contenant que les n-uplets (v1, v2, ..., vn) tels que pour chaque n-uplet (w1, w2, ..., wn) de S, R contient le n-uplet (v1, v2, ..., vn, w1, w2, ..., wn)

Division: Exemple

Division : utilisationSoient les tables suivantes:LesEtudiants(NoEt, nom, prenom, ville)LesInscriptions(NoEt, matiere)LesRsultats(NoEt, matiere, dateEpreuve, note)RequtesQuels sont les lves inscrits dans toutes les matires ?Quels sont les lves ayant une note chacune des preuves ?

Exercice 1 RequtesNumros, noms et prnoms des tudiants qui ne sont pas inscrits en gographie ?Numros des tudiants ayant obtenu la meilleure note lpreuve de gographie du 01/04 ?

Correction

Exercice 2:

Exprimer les requtes suivantes : Numro, nom et prnom des tudiants inscrits en gographie ? Ensemble des couples (x, y) dtudiants tels quil existe aumoins une preuve laquelle x a obtenu une meilleure note que y ?

Correction

fonctionsLes fonctions permettent de se servir dexpressions arithmtiques pour affiner les restrictions, les jointures et les projections.Exemple: R1 = RESTRICT (Employes, Salaire/echelon >1000)R1 = JOIN (Employes, Echelon, (Salaire-1000)/200 =echelon)R1 = PROJECT (Film, Titre, 2004-Annee);

Agrgations: dfinition Les agrgations permettent de combiner les attributs de plusieurs tuples afin dobtenir un seul rsultat final agrg, ou plusieurs rsultats groups.

Agrgations:exemple

Langages algbriques et expressionsdrivesUn langage algbrique peut tre driv de l'algbre relationnel pour interroger les bases de donnes Une requte se reprsente par un arbre d'opration. Cet arbre peut tre traduit en SQL, donc un langage oprationnel.

Expressions algbriques : exemplesConsidrons la base constitue des relations:Film(numfilm, titre, ralisateur, anne, dure)Acteur(numacteur, nom, prnom, dateNaissance)Casting(numfilm, numacteur, personnage)Quel sont les films (titre, ralisateur) qui durent plus de deux heures ?Solution:R1 = RESTRICT(Film, duree >= 120 )RESULTAT = PROJECT(R1, titre, ralisateur)

Expressions algbriques : exemplesQuels sont les acteurs (nom, prnom) n en 1952 ?Solution R1 = RESTRICT(Acteur, YEAR(dateNaissance) = TO_DATE('1952', 'YYYY'))RESULTAT = PROJECT(R1, nom, prnom) Quels sont les acteurs ayant jou dans Astrix ?Solution :R1 = JOIN(Acteur, Casting) (ou explicitement: R1 = JOIN(Acteur, Casting, Acteur.numacteur = Casting.numacteur) )R2 = RESTRICT(R1, personnage='Astrix')RESULTAT = PROJECT(R2, nom, prnom)

Expressions algbriques : exemplesQuels sont les acteurs (nom, prnom et mois de naissance) ns la mme anne ?Solution:R1 = JOIN(Acteur A1, Acteur A2, YEAR(A1.dateNaissance) = YEAR(A2.dateNaissance))RESULTAT = PROJECT(R1, A1.nom, A1.prnom, A2.nom, A2.prnom, MONTH(A1.dateNaissance))

Expressions algbriques : exemples

SQL: Prsentation(Structured Query Language)

Le langage SQLLangage standard - 4me gnration (SQL89, SQL2, SQL3)3 langages pour BdD relationnellesLangage de Description de Donnes (LDD)Cration de tables, Langage de Manipulation de Donnes (LMD)Interrogation/Modification de la BdDLangage de Contrle de donnes (LCD)Gestion des droits daccsSQL est un langage utilisable en mode interactifassoci une interface graphiqueassoci des langages de programmation

Plan de la prsentationLangage de Description de DonnesInstructions CREATE, DROP, ALTER TABLELangage de Manipulation de DonnesInstructions SELECT, INSERT, DELETE, UPDATESQL sous oracle

Introduction SQLDfinition des donnes

Ecriture des Ordres SQLLes ordres SQL peuvent tre crits indiffremment en majuscules et/ou minuscules. Les ordres SQL peuvent tre crits sur plusieurs lignes. Les mots-cls ne doivent pas tre abrgs ni scinds sur deux lignes diffrentes.Les clauses sont gnralement places sur des lignes distinctes.Les tabulations et indentations permettent une meilleure lisibilit.

Cration de tableUne table (relation en algbre relationnelle) se cr par linstruction CREATE TABLECREATE TABLE nom_table(nom_col1 type_col1 [contrainte1] [, nom_col2 type_col2 [contrainte2]]);

Types classiques de donnes

Particularit des SGBD : le cas dOracle

Types de donnes: exempleSalaire NUMBER(8,2): Dfinit une colonne numrique SALAIRE, les valeurs auront au maximum 2 dcimales et 8 chiffres au plus au total( donc 6 chiffre avant le point dcimal)

Contraintes sur les colonnesDEFAULT value: Value est la valeur par dfaut si aucune valeur nest donne un enregistrementPRIMARY KEY: Cl primaire de la table. Pour dsigner plus dune colonne, on lutilise comme une clause PRIMARY KEY(col1, col2, ) ct des dfinitions de colonnesNOT NULL: Doit avoir une valeur pour chaque enregistrementUNIQUE: Chaque enregistrement doit avoir une valeur diffrenteCHECK condition: La valeur doit respecter une condition donneREFERENCES et FOREIGN KEY: permettent de dfinir des contraintes d'intgrit rfrentielle deux formes syntaxiques

Exemple de cration de tableCREATE TABLE article(ref CHAR(10) NOT NULL,prix NUMBER(9,2),datemaj DATE);

Exemple de cration de tableExemple1CREATE TABLE Client(num_client NUMBER(5) PRIMARY KEY,nom VARCHAR(30) NOT NULL,solde NUMBER,type VARCHAR(10) CHECK (type IN (particulier, pme,autre));

cration de table: PRIMARY KEY

Deux formes:dans la dfinition d'un attribut, s'il forme une cl tout seulcomme contrainte spar, si nonExemple1CREATE TABLE Employee (Maricule CHAR(6) PRIMARY KEY,Prenom CHAR(20) NOT NULL,Nom CHAR(20) NOT NULL,.....)Exemple2 CREATE TABLE Employee (Matricule CHAR(6)Prenom CHAR(20) NOT NULL,Nom CHAR(20) NOT NULL,......PRIMARY KEY(Matricule))

Cration de table: PRIMARY KEY

Exemple CREATE TABLE Employee (Matricule CHAR(6)Prenom CHAR(20) NOT NULL,Nom CHAR(20) NOT NULL,......Constraint PK_Employee PRIMARY KEY(Matricule))Remarque: chaque contrainte doit tre nomme (ce qui permettra de la dsign par un ordre SQL ALTER en la faisant prcder par Constraint nom_contrainte contrainte

Primary Key avec plusieurs attributsWORK_ON(PCODE, NSS, DateDeb)Exemple 1 create table WORK_ON(PCODE int,NSS char(6),DateDep date,primary key (PCODE, NSS))Exemple 2create table WORK_ON(PCODE int,NSS char(6),DateDep date,constraint WORK_ON_PK primary key (PCODE, NSS))

Contraintes Inter-relationnelsREFERENCES et FOREIGN KEY: permettent de dfinir des contraintes d'intgrit rfrentielle deux formes syntaxiques:pour attributs seuls pour plusieurs attributs

Contraintes Inter-relationnels

Remarque: loption ON DELETE CASCADE indique que la suppression dune ligne de la table tableref va entraner automatiquement la suppression des lignes qui la rfrencent dans la table

cration de table: Foreign KeyExemple create table Department(DeptName char(15) primary key,Site char(2),)create table Employee(Matricule char(6) primary key,FamName char(20) not null,FirstName char(20) not null,Dept char(15) references Department(DeptName),Salary numeric(9) default 0,unique(FamName,FirstName))Remarque:Il faut que les types des attributs rfrencs soient les mmes!

Cration de table: Foreign Keycreate table Employee(Matricule char(6) primary key,FamName char(20) not null,FirstName char(20) not null,Dept char(15),Office numeric(3),Salary numeric(9) default 0,unique(FamName,FirstName)Foreign Key(Dept) references Department(DeptName))

Cration de table: Foreign Keycreate table Employee(Matricule char(6) primary key,FamName char(20) not null,FirstName char(20) not null,Dept char(15),Office numeric(3),Salary numeric(9) default 0,unique(FamName,FirstName)Constraint FK_EmployeeDeptForeign Key(Dept) references Department(DeptName))

Exemple de contraintesContraintes sur des colonnes

Contraintes au niveau de la table

Exemple de contraintesCertaines contraintes portent sur plusieurs colonnes et ne peuvent etre indiques que comme contraintes de table

Gestion des cls trangres

Quelques remarques

Suppression & renommage dune tableUne table se supprime par linstruction DROP TABLEDROP TABLE nom_table;Remarque: la suppression est autorise seulement si la table n'est pas referenceUne table se renomme par linstruction RENAME TABLERENAME TABLE ancien_nom TO nouveau_nom;

Suppression & renommage dune tableDROP TABLE DROP TABLE nom_de_tableDROP TABLE employeela suppression est autorise seulement si latable n'est pas reference

Modification dune tableLe contenu dune table peut tre modifi par linstruction ALTER TABLEALTER TABLE nom_tablemodification1,modification 2;Les modifications portent sur des ajouts, suppression ou remplacement de colonnes avec pour syntaxe :ADD (nom_col type_col [contrainte])DROP nom_colMODIFY nom_col type_col [contrainte]

Modification dune tableADD dfinition_de_colonneADD CONSTRAINT contrainte_de_tableDROP COLUMN nom_de_colonneDROP CONSTRAINT nom_de_contrainte

Exemple de modification de tableExemple 1ALTER TABLE ClientADD (prenom VARCHAR(30)),MODIFY nom VARCHAR(50) NOT NULL,DROP solde,ADD (naissance DATE);

ALTER TABLE employeeADD Address char(20) default nullALTER TABLE DepartmentADD supervisor char(6) default nullALTER TABLE DepartementADD constraint FKey_EMP_SUP foreign key(supervisor) references EMPLOYEE(NSS)

Modification dune tableDrop, exemples:ALTER TABLE employeeDROP column addressALTER TABLE employeeDROP constraint FKey_EMP_SUP

Vues ORACLELister l'ensemble des tables cres : select table_name from user_tables;Lister les colonnes d'une table : select column_name from user_tab_columns;Lister les contraintes : select constraint_name from user_constraints;Commande SQL+ : descdesc personne

SQL : commandes

Suppression d'une table :DROP TABLE [IF EXISTS] nom_table [, nom_table ] ... Modification d'une table :ALTER TABLE nom_table ADD/MODIFY/CHANGE/DROP nom_champ Exemple :

ALTER TABLE client ADD email VARCHAR(30)ALTER TABLE client MODIFY email VARCHAR(50)ALTER TABLE client CHANGE email courriel VARCHAR(50)ALTER TABLE client DROP courriel

Introduction SQLManipulation des donnes

INSERT: Insertion de lignes dans une table UPDATE: Modification de lignes dans une table DELETE: Suppression de lignes dans une tableSelect: slection des informations

Modification des donnes

Insertion denregistrementUn enregistrement (tuple en algbre relationnelle) sinsre dans une table par linstruction INSERT INTOINSERT INTO nom_table[(nom_col1, nom_col2, )]VALUES (val1, val2, )[(val3, val4, )];Exemple :INSERT INTO ClientVALUES (1, Durand, 0, particulier),(2, Martin, 50, pme);

Insertion utilisant une slectionUne requte imbrique peut-tre utilise pour raliser des insertions.Par exemple, pour insrer dans la table 2A tous les lves de la table 1A dont lattribut note est suprieur 10, on peut crire :INSERT INTO 2A SELECT * FROM 1AWHERE note > 10;Les instructions UPDATE et DELETE peuvent aussi utiliser une requte imbrique

Cration utilisant une slectionUne requte imbrique peut-tre utilise pour crer et remplir une table.Par exemple, pour une table 2A comprenant les colonnes nom et prenom et remplie avec tous les noms et prenoms des enregistrements dune rable 1A, on peut crire :CREATE TABLE 2A AS SELECT nom, prenom FROM 1A;

Modification de lignes : UPDATEUPDATE permet de modifier les valeurs dune ou plusieurs colonnes dans une ou plusieurs lignes dj existantes Les nouvelles valeurs peuvent tre nonces par lutilisateur ou provenir dun ou plusieurs SELECTUPDATE nom_tableSET nom_col1 = {expression1 | ( SELECT ...) },nom_col2 = {expression2 | ( SELECT ...) }WHERE predicatEx : augmenter de 10% les salaires des ingnieurs.UPDATE emp SET salaire = salaire * 1.1WHERE fonction = ingenieur ;Remarque: Sans clause WHERE : toutes les lignes sont mises jour

Suppression de lignes : DELETEDELETE permet de supprimer une ou plusieurs lignes dune table.DELETE FROM nom_tableWHERE predicat ;Exemple: dtruit les lignes des employs travaillant Paris.DELETEFROM empWHERE emp.deptno = (SELECT dept.deptnoFROM deptWHERE lieu=Paris');Sans clause WHERE : toutes les lignes sont supprimes

Les Possibilits de l'Ordre SQL SELECTSlectionProjectionTable 1Table 2Table 1Table 1Jointure

Requte SELECTUne requte dinterrogation de la BdD scrit laide de linstruction SELECTSELECT [DISTINCT] table1.attr1 [, table2.attr2] | exprFROM table1 [, table2][WHERE condition][ORDER BY expr [DESC]][GROUP BY expr][HAVING expr]

Ordre SELECT ElmentaireSELECT[DISTINCT] {*, column [alias],...}FROMtable;SELECT indique quelles colonnes rapporterFROM indique dans quelle table rechercher

Slection de Toutes les Colonnes DEPTNO DNAME LOC--------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL> SELECT * 2 FROM dept;

ProjectionLa commande SELECT:SELECT [ALL|DISTINCT] FROM Exemple. Projection partir de la table client

SELECT nom, prenom FROM client;

Slection dUne ou Plusieurs Colonnes Spcifiques DEPTNO LOC--------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTONSQL> SELECT deptno, loc 2 FROM dept;

Projection d'une table et la clause DISTINCTProduire les noClient et dateCommande de toutes les Commandes

Multi-ensemble !

**Clause DISTINCTProduire les noClient et dateCommande de toutes les Commandes

noClient, dateCommande (Commande)

Expressions ArithmtiquesPossibilit de crer des expressions avec des donnes de type NUMBER et DATE au moyen doprateurs arithmtiques

Utilisation des Oprateurs Arithmtiques

SQL> SELECT ename, sal, sal+300 2 FROMemp;ENAME SAL SAL+300---------- --------- ---------KING 5000 5300BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275MARTIN 1250 1550ALLEN 1600 1900...14 rows selected.

Priorit des OprateursLa multiplication et la division ont priorit sur laddition et la soustraction.A niveau de priorit identique, les oprateurs sont valus de gauche droite.Les parenthses forcent la priorit dvaluation et permettent de clarifier les ordres.

Priorit des OprateursSQL> SELECT ename, sal, 12*sal+100 2 FROM emp;ENAME SAL 12*SAL+100---------- --------- ----------KING 5000 60100BLAKE 2850 34300CLARK 2450 29500JONES 2975 35800MARTIN 1250 15100ALLEN 1600 19300...14 rows selected.

Utilisation des ParenthsesSQL> SELECT ename, sal, 12*(sal+100) 2 FROM emp;ENAME SAL 12*(SAL+100)---------- --------- -----------KING 5000 61200BLAKE 2850 35400CLARK 2450 30600JONES 2975 36900MARTIN 1250 16200...14 rows selected.

La Valeur NULLNULL reprsente une valeur non disponible, non affecte, inconnue ou inapplicable.La valeur NULL est diffrente du zro ou de lespace.SQL> SELECT ename, job, comm 2 FROMemp;ENAME JOB COMM---------- --------- ---------KING PRESIDENTBLAKE MANAGER...TURNER SALESMAN 0...14 rows selected.

Valeurs NULL dans les Expressions ArithmtiquesLes expressions arithmtiques comportant une valeur NULL sont values NULLSQL> select ename , 12*sal+comm 2 from emp 3 WHERE ename='KING';ENAME 12*SAL+COMM ---------- -----------KING

LAlias de ColonneRenomme un en-tte de colonneEst utile dans les calculsSuit immdiatement le nom de la colonne ; le mot-cl AS plac entre le nom et lalias est optionnelDoit obligatoirement tre inclus entre guillemets sil contient des espaces, des caractres spciaux ou si les majuscules/minuscules doivent tre diffrencies

Utilisation des Alias de Colonnes

SQL> SELECT ename AS name, sal salary 2 FROM emp;NAME SALARY------------- --------- ...SQL> SELECT ename "Name", 2 sal*12 "Annual Salary" 3 FROM emp;

Slectionner les Lignes

**Slection sur une table (WHERE)Slectionner les Articles dont le prix est infrieur $20.00 et le numro est suprieur 30 prixUnitaire < 20.00 ET noArticle > 30 (Article)

SlectionLa clause WHERE

**Syntaxe de conditionSQL

Syntaxe de la conditionSimple :

Chanes de Caractres et DatesLes constantes chanes de caractres et dates doivent tre places entre simples quotes. La recherche tient compte des majuscules et minuscules (pour les chanes de caractre) et du format (pour les dates.)Le format de date par dfaut est 'DD-MON-YY'.SQL> SELECTename, job, deptno 2 FROM emp 3 WHEREename = 'JAMES';

Expression des conditionsOprateurs simples de comparaison=, !=, , =Autres oprateursexpr1 BETWEEN expr2 AND expr3Ex : solde BETWEEN 50 AND 100expr1 IN (expr2, expr3, )Ex : nom IN (Martin, Durand)expr1 LIKE chaneEx : nom LIKE D%pon_

Utilisation des Oprateurs de ComparaisonSQL> SELECT ename, sal, comm 2 FROM emp 3 WHERE sal

ConditionSQL - BETWEENENAME SAL---------- ---------MARTIN 1250TURNER 1500WARD 1250ADAMS 1100MILLER 1300

SQL> SELECTename, sal 2 FROM emp 3 WHEREsal BETWEEN 1000 AND 1500;BETWEEN permet de tester l'appartenance une fourchette de valeurs.

**ConditionSQL - BETWEENSlectionner les Commandes du mois de juin de l'anne 2000

**ConditionSQL - ININ permet de comparer une expression avec une liste de valeurs.Slectionner les Commandes du Client dont le noClient est 10 ou 40 ou 80

ConditionSQL - LIKELIKE permet de rechercher des chanes de caractres l'aide de caractres gnriquesLes conditions de recherche peuvent contenir des caractres ou des nombres littraux.(%) reprsente zro ou plusieurs caractres( _ ) reprsente un caractreSQL> SELECTename 2 FROM emp 3 WHEREename LIKE 'S%';

**ConditionSQL - LIKESlectionner les Clients dont le nomClient contient le mot Le

2ime lettre du nomClient = o et dernire lettre est un k

**ConditionSQL - IS NULLRecherche de valeurs NULL avec loprateur IS NULLSlectionner les Articles dont la description est une valeur nulle

LOprateur ANDAvec AND, les deux conditions doivent tre VRAIES.SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal>=1100 4 AND job='CLERK'; EMPNO ENAME JOB SAL--------- ---------- --------- --------- 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300

Utilisation de lOprateur OR Avec OR, l'une ou l'autre des deux conditions doit tre VRAIE.SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal>=1100 4 OR job='CLERK'; EMPNO ENAME JOB SAL--------- ---------- --------- --------- 7839 KING PRESIDENT 5000 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7566 JONES MANAGER 2975 7654 MARTIN SALESMAN 1250...14 rows selected.

lOprateur NOTSQL> SELECT ename, job 2 FROM emp 3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');ENAME JOB---------- ---------KING PRESIDENTMARTIN SALESMANALLEN SALESMANTURNER SALESMANWARD SALESMAN

... WHERE sal NOT BETWEEN 1000 AND 1500... WHERE ename NOT LIKE %A%... WHERE comm IS NOT NULL

Rgles de PrioritLes parenthses permettent de modifier les rgles de prioritOrdre de priorit Oprateur 1Tous les oprateurs de comparaison2NOT3AND4OR

Rgles de PrioritENAME JOB SAL---------- --------- ---------KING PRESIDENT 5000MARTIN SALESMAN 1250ALLEN SALESMAN 1600TURNER SALESMAN 1500WARD SALESMAN 1250SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE job='SALESMAN' 4 OR job='PRESIDENT' 5 AND sal>1500;

Rgles de PrioritENAME JOB SAL---------- --------- ---------KING PRESIDENT 5000ALLEN SALESMAN 1600Utilisation de parenthses pour forcer la priorit.SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE (job='SALESMAN' 4 OR job='PRESIDENT') 5 AND sal>1500;

Ordre du rsultatLe rsultat dune requte peut tre tri selon les valeurs dune ou plusieurs colonne(s).SELECT * FROM Client ORDER BY solde;Par ordre croissant :SELECT * FROM Client ORDER BY nom, prenom DESC;Par ordre dcroissant (ici le tri est dabord fait par le nom puis par le prenom pour les enregistrements de mme nom) :

Clause ORDER BYTri des lignes avec la clause ORDER BYASC : ordre croissant (par dfaut)DESC : ordre dcroissantLa clause ORDER BY se place la fin de lordre SELECT

SQL> SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate;

ENAME JOB DEPTNO HIREDATE---------- --------- --------- ---------SMITH CLERK 20 17-DEC-80ALLEN SALESMAN 30 20-FEB-81...14 rows selected.

Tri par Ordre DcroissantSQL> SELECT ename, job, deptno, hiredate 2 FROM emp 3 ORDER BY hiredate DESC;ENAME JOB DEPTNO HIREDATE---------- --------- --------- ---------ADAMS CLERK 20 12-JAN-83SCOTT ANALYST 20 09-DEC-82MILLER CLERK 10 23-JAN-82JAMES CLERK 30 03-DEC-81FORD ANALYST 20 03-DEC-81KING PRESIDENT 10 17-NOV-81MARTIN SALESMAN 30 28-SEP-81...14 rows selected.

Tri sur lAlias de ColonneSQL> SELECT empno, ename, sal*12 annsal 2 FROM emp 3 ORDER BY annsal; EMPNO ENAME ANNSAL--------- ---------- --------- 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7654 MARTIN 15000 7521 WARD 15000 7934 MILLER 15600 7844 TURNER 18000...14 rows selected.

Tri sur Plusieurs ColonnesLordre des lments de la liste ORDER BY donne lordre du tri.Vous pouvez effectuer un tri sur une colonne ne figurant pas dans la liste SELECT.SQL> SELECTename, deptno, sal 2 FROM emp 3 ORDER BY deptno, sal DESC;ENAME DEPTNO SAL---------- --------- ---------KING 10 5000CLARK 10 2450MILLER 10 1300FORD 20 3000...14 rows selected.

RsumSELECT[DISTINCT] {*, column [alias], ...}FROM table[WHEREcondition(s)][ORDER BY{column, expr, alias} [ASC|DESC]];

**Slection-projection sur une tableProduire les noClient et dateCommande des Commandes dont la date est suprieure au 05/07/2000

* Robert Godin. Tous droits rservs.*Produit cartsien avec SELECT-FROMProduire toutes les combinaisons possibles de lignes de Client et de CommandeClient Commande

EMPNO DEPTNO LOC----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO...14 rows selected.Afficher des Donnes Issues de Plusieurs TablesEMP DEPT EMPNOENAME...DEPTNO -----------...------ 7839KING... 10 7698BLAKE... 30 ... 7934MILLER... 10DEPTNO DNAME LOC ------ ------------------ 10ACCOUNTINGNEW YORK 20RESEARCHDALLAS 30SALESCHICAGO 40OPERATIONSBOSTON

la JointureUne jointure sert extraire des donnes de plusieurs tables.

Ecrivez la condition de jointure dans la clause WHERE.Placez le nom de la table avant le nom de la colonne lorsque celui-ci figure dans plusieurs tables.SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;

Jointure

Exemple:

SELECT * FROM Vehicule, ProprietaireWHERE Vehicule.proprietaire = Proprietaire.numero;

**Jointure naturelle avec SELECT-FROM-WHEREProduire les informations au sujet des Clients et de leurs Commandes

Client.noClient, nomClient, noTlphone, noCommande, dateCommande ( Client.noCliente = Commande.noClient (Client Commande))

**Jointure avec JOIN (SQL2)Produire les informations au sujet des Clients et de leurs Commandes

Jointure- (si noms de colonnes de jointure sont diffrents)Client Commande

Types de JointuresEquijointureNon-quijointureJointure externeAutojointure

L EquijointureEMP DEPT EMPNO ENAME DEPTNO------ ------- ------- 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7566 JONES 20 7654 MARTIN 30 7499 ALLEN 30 7844 TURNER 30 7900 JAMES 30 7521 WARD 30 7902 FORD 20 7369 SMITH 20...14 rows selected. DEPTNO DNAME LOC ------- ---------- -------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 10 ACCOUNTINGNEW YORK 20 RESEARCHDALLAS 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 30 SALES CHICAGO 20 RESEARCHDALLAS 20 RESEARCHDALLAS...14 rows selected.

Extraction d'Enregistrements avec les EquijointuresSQL> SELECT emp.empno, emp.ename, emp.deptno, 2dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;EMPNO ENAME DEPTNO DEPTNO LOC----- ------ ------ ------ --------- 7839 KING 10 10 NEW YORK 7698 BLAKE 30 30 CHICAGO 7782 CLARK 10 10 NEW YORK 7566 JONES 20 20 DALLAS...14 rows selected.

Diffrencier les Noms de Colonne AmbigusPrfixer avec le nom de la table pour diffrencier les noms de colonnes appartenant plusieurs tables.Ces prfixes de table amliorent les performances.Diffrencier des colonnes de mme nom appartenant plusieurs tables en utilisant des alias de colonne.

**Dfinition d'un alias (clause AS)~ renommer ()

Utilisation d'Alias de TableSimplifiez les requtes avec les alias de table.SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno;SQL> SELECT e.empno, e.ename, e.deptno, 2 d.deptno, d.loc 3 FROM emp e, dept d 4 WHERE e.deptno=d.deptno;

**Auto-jointureQuels sont les Clients qui ont le mme numro de tlphone? Client.noClient, Client2.noClient, ( Client.noTlphone = Client2.noTlphone (Client Client2 (Client))) noClient, noClient2 (Client Client2(noClient2, nomClient2, noTlphone) (Client))

Non-EquijointuresEMPSALGRADE EMPNO ENAME SAL------ ------- ------ 7839 KING 5000 7698 BLAKE 2850 7782 CLARK 2450 7566 JONES 2975 7654 MARTIN 1250 7499 ALLEN 1600 7844 TURNER 1500 7900 JAMES 950...14 rows selected.GRADE LOSAL HISAL----- ----- ------1 70012002 120114003 140120004200130005 30019999

Extraction d'Enregistrements avec les Non-EquijointuresENAME SAL GRADE---------- --------- ---------JAMES 950 1SMITH 800 1ADAMS 1100 1...14 rows selected.SQL> SELECT e.ename, e.sal, s.grade 2FROMemp e, salgrade s 3WHERE e.sal 4BETWEEN s.losal AND s.hisal;

Jointures ExternesEMP DEPT ENAMEDEPTNO ----------- KING10BLAKE30CLARK10JONES20...

DEPTNO DNAME------ ----------10 ACCOUNTING30 SALES10 ACCOUNTING20RESEARCH...40OPERATIONS

Jointures ExternesLes jointures externes permettent de visualiser des lignes qui ne rpondent pas la condition de jointure.L'oprateur de jointure externe est le signe (+).

SELECTtable.column, table.columnFROMtable1, table2WHEREtable1.column(+) = table2.column;SELECTtable.column, table.columnFROMtable1, table2WHEREtable1.column = table2.column(+);

Utilisation des Jointures ExternesSQL> SELECTe.ename, d.deptno, d.dname 2 FROMemp e, dept d 3 WHEREe.deptno(+) = d.deptno 4 ORDER BYe.deptno;ENAME DEPTNO DNAME---------- --------- -------------KING 10 ACCOUNTINGCLARK 10 ACCOUNTING... 40 OPERATIONS15 rows selected.

* Robert Godin. Tous droits rservs.*Jointure externe (OUTER JOIN)Produire les informations au sujet des Clients et de leurs Commandes incluant les informations sur les Clients qui nont pas plac de Commande

Oracle + aprs colonne pour inclure la valeur NULLClient = Commande

**Oprations ensemblistes (UNION, INTERSECT, EXCEPT)Produire les noms et numros de tlphone des Employs qui sont aussi des Clients de la ppinire

Fonctions de GroupeLes fonctions de groupe agissent sur des groupes de lignes et donnent un rsultat par groupe.EMP"salaire maximumde la table EMP" 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 MAX(SAL)--------- 5000

Exemples doprateurs de groupeSELECT COUNT(*) FROM Vehicule WHERE marque=BMW;SELECT MAX(solde) FROM Client;SELECT SUM(solde) FROM Client WHERE nom = Durand;

Fonctions AVG et SUMAVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)-------- --------- --------- --------- 1400 1600 1250 5600AVG et SUM s'utilisent avec des donnes numriques.SQL> SELECTAVG(sal), MAX(sal), 2MIN(sal), SUM(sal) 3FROMemp 4WHEREjob LIKE 'SALES%';

Fonctions MIN et MAXMIN et MAX s'utilisent avec tous types de donnes.SQL> SELECTMIN(hiredate), MAX(hiredate) 2 FROMemp;MIN(HIRED MAX(HIRED--------- ---------17-DEC-80 12-JAN-83

Utilisation de la Fonction COUNT COUNT(*)--------- 6SQL> SELECTCOUNT(*) 2 FROMemp 3 WHEREdeptno = 30;COUNT(*) ramne le nombre de lignes d'une table.

Utilisation de la Fonction COUNTCOUNT(expr) ramne le nombre de lignes non NULL.SQL> SELECTCOUNT(comm) 2 FROMemp 3 WHEREdeptno = 30;COUNT(COMM)----------- 4

Fonctions de Groupe et Valeurs NULLLes fonctions de groupe ignorent les valeurs NULL des colonnes.SQL> SELECT AVG(comm) 2 FROM emp; AVG(COMM)--------- 550

Utilisation de la Fonction NVL avec les Fonctions de Groupe

La fonction NVL force la prise en compte des valeurs NULL dans les fonctions de groupe.SQL> SELECT AVG(NVL(comm,0)) 2 FROM emp;AVG(NVL(COMM,0))---------------- 157.14286

**Fonctions de groupeLe nombre d'Articles diffrents vendre ainsi que le prixUnitaire moyen des Articles

**Fonctions de groupe:suite

Groupement des rsultatsPar dfaut les oprateurs de groupe sappliquent sur lensemble des enregistrements slectionnsLinstruction GROUP BY permet dappliquer un oprateur des groupes spars denregistrementsPar exemple, pour obtenir le nombre de vehicule de chaque marque on peut crire :SELECT COUNT(*) FROM Vehicule GROUP BY marque;

Cration de Groupes de DonnesEMP"salaire moyen pour chaque dpartement de la table EMP" 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

Cration de Groupes de Donnes : la Clause GROUP BYSELECTcolumn, group_functionFROMtable[WHEREcondition][GROUP BYgroup_by_expression][ORDER BYcolumn];

Utilisation de la Clause GROUP BYLa clause GROUP BY doit inclure toutes les colonnes de la liste SELECT qui ne figurent pas dans des fonctions de groupe.SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno; DEPTNO AVG(SAL)--------- --------- 10 2916.6667 20 2175 30 1566.6667

**Partition d'une table avec la clause GROUP BYProduire le nombre de Commandes passes par chacun des Clients qui ont pass au moins une Commande

Regroupement sur Plusieurs Colonnes

EMP'"somme des salaires de la table EMP pour chaque poste, regroups par dpartement" DEPTNO JOB SAL--------- --------- --------- 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250JOB SUM(SAL)--------- ---------CLERK 1300MANAGER 2450PRESIDENT 5000ANALYST 6000CLERK 1900MANAGER 2975CLERK 950MANAGER 2850SALESMAN 5600DEPTNO--------101010202020303030

Utilisation de la Clause GROUP BY sur Plusieurs ColonnesSQL> SELECT deptno, job, sum(sal) 2 FROM emp 3 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.

Erreurs d'Utilisation des Fonctions de Groupe dans une Requte

Toute colonne ou expression de la liste SELECT autre qu'une fonction de groupe, doit tre incluse dans la clause GROUP BY.SQL> SELECTdeptno, COUNT(ename) 2 FROMemp;SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group functionColonne manquante dans la clause GROUP BY

Erreurs d'Utilisation des Fonctions de Groupe dans une Requte

Vous ne pouvez utiliser la clause WHERE pour limiter les groupes.Utilisez la clause HAVING.SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;WHERE AVG(sal) > 2000 *ERROR at line 3:ORA-00934: group function is not allowed hereN'utilisez pas la clause WHERE pour limiter les groupes

Requtes de groupement : GROUP BY

Rgles: Tous les attributs Ai, , Andans la clause SELECT qui ne sont pas impliqus dans une opration d'agrgation doivent tre inclus dans la clause GROUP BYGROUP BY peut avoir dautres attributs en plus

Clause HAVINGLa clause HAVING ralise une slection ( la manire du WHERE) sur les groupes retenir.

Par exemple, si on souhaite connatre le nombre de vhicules de chaque marque en ayant plus de 5, on peut crire :SELECT COUNT(*) FROM Vehicule GROUP BY marque HAVING COUNT(*) > 5;SELECTcolumn, group_functionFROMtable[WHEREcondition][GROUP BYgroup_by_expression][HAVINGgroup_condition][ORDER BYcolumn];

Utilisation de la clause HAVINGSQL> SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)>2900; DEPTNO MAX(SAL)--------- --------- 10 5000 20 3000

Clause HAVINGProduire le nombre de Commandes passes par chacun des Clients qui ont pass deux Commandes ou plus

Produire le nombre de Commandes passes par chacun des Clients qui ont pass deux Commandes ou plus aprs le 02/06/2000

Imbrication des Fonctions de GroupeSQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;MAX(AVG(SAL))------------- 2916.6667Afficher le salaire moyen maximum.

RsumSELECTcolumn, group_functionFROMtable[WHEREcondition][GROUP BYgroup_by_expression][HAVINGgroup_condition][ORDER BYcolumn];

**Tri du rsultat (ORDER BY)Les Clients en ordre alphabtique du nomLaboratoireExercices 1 q) s) t) v)

SELECT *

FROM

Client

ORDER BYnomClient DESC, noTlphone ASC

Requtes imbriquesPermet le calcul d'un sous-rsultat sans crer une nouvelle relation ; Peut simplifier la lecture du rsultat (p. ex. requte imbrique en remplacement d'une jointure).

Sous-interrogationsUn critre de recherche employ dans une clause WHERE peut tre lui-mme le rsultat dun SELECTPar exemple, pour connatre le nom des personnes nes le mme jour que le client Martin on peut crire :SELECT nom FROM ClientWHERE naissance = (SELECT naissance FROM Client WHERE nom = Martin);

Exemple

Exemple de requte imbriqueRequte : Etudiants de Durand ? Algbre relationnelle :R1 = (LesEnseignants : (nomEnseignant = Durand))[matiere]R2 = (LesInscriptions : (matiere = R1))[NoEt] SQL : SELECT NoEt FROM LesInscriptions WHERE matiere =(SELECT matiere FROM LesEnseignants WHERE nomEnseignant = Durand)

Exemple de requte imbriqueRsultat de la requte

Sous-InterrogationsLa sous-interrogation (requte interne) est excute une fois avant la requte principale.Le rsultat de la sous-interrogation est utilis par la requte principale (externe).SELECTselect_listFROMtableWHEREexpr operator (SELECTselect_list FROMtable);

Conventions d'Utilisation des Sous-InterrogationsPlacez les sous-interrogations entre parenthses. Placez les sous-interrogations droite de l'oprateur de comparaison.N'ajoutez jamais de clause ORDER BY une sous-interrogation.Utilisez les oprateurs mono-ligne avec les sous-interrogations mono-ligne.Utilisez les oprateurs multi-ligne avec les sous-interrogations multi-ligne.

SQL> SELECT ename 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno=7566);

Utilisation d'une Sous-InterrogationENAME----------KINGFORDSCOTT

Types de Sous-InterrogationsSous-interrogation mono-ligneRequte principalesous-interrogation ramneCLERKSous-interrogation multi-ligneCLERKMANAGERRequte principale ramneSous-interrogation multi-colonneCLERK 7900 MANAGER 7698Requte principale ramnesous-interrogationsous-interrogation

Sous-interrogationsDans le cas o plusieurs enregistrements peuvent tre renvoys on peut utiliser :IN : pour tester une appartenance lensemble de ce qui est renvoyUn oprateur de comparaison (, ) suivi de ANY ou de ALL pour tester si la comparaison est vraie au moins une fois ou pour tous les enregistrements.Par exemple pour connatre le client le plus jeune on peut crire :SELECT nom FROM Client WHERE naissance

Sous-Interrogations Mono-ligneNe ramnent qu'une seule ligneUtilisent des oprateurs de comparaison mono-ligneOprateur=> >=<

Sous-Interrogations Mono-ligneSQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4(SELECT job 5 FROM emp 6 WHERE empno = 7369) 7 AND sal > 8(SELECT sal 9FROMemp 10WHEREempno = 7876); Exemple: Afficher les employs occupant le mme poste quel'employ 7369 et gagnent plus que l'employ 7876

Clause HAVING avec Sous-InterrogationsOracle Server excute les sous-interrogations en premier.Oracle Server ramne les rsultats dans la clause HAVING de la requte principale.SQL> SELECTdeptno, MIN(sal) 2 FROMemp 3 GROUP BYdeptno 4 HAVINGMIN(sal) > 5(SELECTMIN(sal) 6FROMemp 7WHEREdeptno = 20);

Qu'est-ce Qui ne Va pas dans cet Ordre ?ERROR:ORA-01427: single-row sub-query returns more than one row

no rows selectedSQL> SELECT empno, ename 2 FROM emp 3 WHERE sal = 4(SELECT MIN(sal) 5FROM emp 6GROUP BY deptno);Oprateur mono-ligne avec sous-interrogation multi-ligne

Cet Ordre Va-t-il Fonctionner ?no rows selectedLa sous-interrogation ne ramne aucune valeurSQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4(SELECTjob 5FROMemp 6WHEREename='SMYTHE');

Sous-Interrogation Multi-ligneRamne plusieurs lignesUtilise des oprateurs de comparaison multi-ligne

Oprateur in

L'exemple prcdent ne fonctionne que si la requteimbrique ne renvoie qu'un seul n-uplet ; Si une sous-requte renvoie un nombre quelconqued'lments, il faut utiliser les oprateurs in et not in ,traductions SQL de

Exemple d'utilisation de in Requte : Enseignants de l'tudiant '12'? SQL : SELECT nomEnseignant FROM LesEnseignants WHERE matiere IN (SELECT matiere FROM LesInscriptions WHERE NoEt = 12)

Oprateur exists Permet de tester si le rsultat d'une requte imbrique est vide ; Apparat comme lment d'une condition.

Exemple d'utilisation de exists Requte : Enseignants ayant au moins un lve ? SQL : SELECT R1.nomEnseignant FROM LesEnseignants R1 WHERE EXISTS (SELECT * FROM LesInscriptions R2 WHERE R2.matiere = R1.matiere)

Sous-Interrogations Multi-ligneSQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal < ANY 4(SELECTsal 5 FROMemp 6WHEREjob = 'CLERK');Afficher les employs dont le salaire est infrieur celui den'importe quel employ CLERK'

Sous-Interrogations Multi-ligneSQL> SELECT empno, ename, job 2 FROM emp 3 WHERE sal > ALL 4 (SELECTavg(sal) 5 FROMemp 6GROUP BYdeptno)Afficher les employs dont le salaire est suprieur ausalaire moyen de tous les dpartements

Sous-Interrogations Multi-colonne

Sous-interrogation ramenant plusieurs colonnesSous-interrogation ramenant plusieurs colonnes: On compare une liste de colonnes un SELECT ramenant une liste de colonnes :SELECT nom,fonction, n_supFROM empWHERE (fonction,n_sup) = (SELECT fonction, n_supFROM emp WHERE nom = Martin)Renvoie les employs ayant mme fonction et mme suprieur que Martin

Sous-interrogation synchroniseDans les exemples prcdents, la sous-interrogation tait value d'abord, puis le rsultat pouvait tre utilis pour excuter l'interrogation principale. SQL sait galement traiter une sous-interrogation faisant rfrence une colonne de la table de l'interrogation principale. Le traitement dans ce cas est plus complexe car il faut valuer la sous interrogation pour chaque ligne de l'interrogation principale.

Sous-interrogation synchronise

Smantique :pour chaque tuple x de la requte externe Q, excuter la requte interne Q; s'il existe au moins un tuple y dans le rsultat de la requte interne, alors slectionner x. Les deux requtes sont gnralement corrles: Condition dans la requte interne Q exprime une jointure entre les tables de Q et les tables de la requte externe Q.

Sous-interrogation synchroniseSous-interrogation synchronise avec linterrogation principale : pour chaque ligne de linterrogation principale on teste toute la 2ndExemple: Renvoie les employs ne travaillant pas dans le mme dpartement que leur suprieurSELECT nombFROM emp eWHERE n_dept != (SELECT n_dept FROM empWHERE e.n_sup = num)AND n_sup IS NOT NULL;Il faut renommer la table emp de linterrogation principale pour pouvoir la rfrencer dans la sous-interrogation

Sous-interrogation synchroniseSous-interrogation (synchronise) ramenant au moins 1 ligne : Loprateur EXISTS permet de construire un prdicat vrai si la sous-interrogation qui suit ramne au moins une ligne Exemple: Renvoie les employs travaillant dans un dpartement qui a procd des embauches depuis le dbut de lanne 94.

Sous-interrogation multiplesSous-interrogation multiples: On peut utiliser plusieurs sous-interrogations dans un mme SELECT : par imbrication, ou par combinaison (AND ou OR)Exemple: Renvoie la liste des employs du dpartement 10 ayant mme fonction que quelquun du dpartement de DUPONT.

RsumLes sous-interrogations sont utiles lorsqu'une requte fait appel des valeurs inconnues.SELECTselect_listFROMtableWHEREexpr operator (SELECT select_list FROM table);

Vues et Relations temporaires : dfinition Une vue est une relation non stocke dans la base de donnes mais recalcule chaque utilisation.

Vue: rles

Constitution dune vue

Cration dune vue

Options de la syntaxe

Cration exemplediteur de ParisCreate view editeur_paris AS select numediteur, nom from editeur where adresseediteur=Paris;

Create view emp10et20 as select * from emp where ndept in(10,20);

create view deptStat (nom, inf, moy, max, total) as select nomd,min(sal), avg(sal), max(sal), sum(sal) from emp natural join dept group by dept.nomd

Extraction des donnes dune vueSelect * from editeur_paris;select * from emp10et20 ;select nom, total from deptStat where total > 100000;

Rgles dexcutions des ordres du LMD dans une vue

Suppression avec une vueOn peut effectuer des delete travers une vue, sous les conditions suivantes sur le select qui dfinit la vue :une seule tablepas de group bypas de distinct pas de fonction de groupe

Modification avec une vueOn peut effectuer des update travers une vue, sous les conditions du delete, et en plus : les colonnes modifies sont des colonnes relles de la table (pas des expressions)Exemple:update emp10et20 set sal = sal * 1.1;

Insertion dun vueOn peut effectuer des insert travers une vue, sous les conditions du update, et en plus : toute colonne not null de la table reprsente par la vue est prsente dans la vueExemple:insert into emp10 (matr, nome, ) values (1200, 'DUBOIS', );

CHECK OPTIONWITH CHECK OPTION protge contre les disparitions de tuples de vues:UPDATE emp10et20 SET dept = 30 WHERE dept = 10' ; serait alors rejet.e1...10e2...20e5...10e7...30e1...30e2...20e5...30e7...30Sans CHECK OPTION

CHECK OPTIONWITH CHECK OPTION protge contre les disparitions de tuples de vues:UPDATE emp10et20 SET dept = 30 WHERE dept = 10' ; serait alors rejet.e1...10e2...20e5...10e7...30e2...20e7...30Avec CHECK OPTIONAborte1...10e5...10

Synthse de mise jour travers les vues

Suppression dune vueDROP VIEW [view {RESTRICT |CASCADE};L'effacement d'une vue n'efface pas les donnesOption CASCADE et RESTRICT RESTRICT : Si la vue intervient dans la dfinition d'une autre vue, la suppression est refuse CASCADE : Si la vue intervient dans la dfinition d'une autre vue, le systme les efface toutes

INDEXUn index utilise des techniques informatiques pour rendre trs rapides les accs aux valeurs d'une colonneselect * from emp where nomE = 'Dupond'est trs long si la table emp contient des millions de lignesUn index bien construit permet d'obtenir l'emplacement des informations sur Dupond en quelques accs disques (moins de 5, mme s'il y a des millions de lignes dans la table EMP)

Index Un index sera matrialis par la cration de blocs disque contenant des couples (valeurs d'index, numro de bloc) donnant le numro de bloc disque dans lequel se trouvent les lignes correspondant chaque valeur d'index.

Structure d'un index

Un index contient la liste trie des valeurs des colonnes indexes avec les adresses des lignes (numro de bloc dans la partition et numro de ligne dans le bloc) correspondantes )Tous les index sont stocks sous forme d'arbres: une structure arborescente permet de retrouver rapidement dans l'index la valeur de cl cherche, et donc l'adresse de la ligne correspondante dans la table. Dans un tel arbre, toutes les feuilles sont la mme profondeur, et donc la recherche prend approximativement le mme temps quelle que soit la valeur de la cl.

Choix des index

Indexer en priorit : 1.les cls primaires2.les colonnes servant de critre de jointure 3.les colonnes servant souvent de critre de recherche Ne pas indexer : 1.les colonnes contenant peu de valeurs distinctes (index alors peu efficace) 2.les colonnes frquemment modifies

Utilisation d'un indexAprs sa cration un index est gr automatiquement par le SGBDIl est transparent pour l'utilisateur : celui-ci interroge la base de la mme faon que si l'index n'existait pasLe SGBD peut utiliser un index s'il pense que la requte sera acclreLes index ralentissent les modifications des donnes: la cration d'index utilise de l'espace mmoire dans la base de donnes, et, tant donn qu'il est mis jour chaque modification de la table laquelle il est rattach, peut alourdir le temps de traitement du SGBDR lors de la saisie de donnes

Cration dun indexCREATE [UNIQUE] INDEX nomIndex ON table (col1, col2,)create index nomE on emp(nomE);Le nom choisi doit tre unique parmi tous les index de toutes les tables

Oracle cre automatiquement un index sur les colonnes qui ont des contraintes Primary key et Unique

Cration dun indexCREATE [UNIQUE] INDEX Nom_de_l_index ON Nom_de_la_table (Nom_de_champ [ASC/DESC], ...)Exemple: Create index nomE on emp(nomE);Le nom choisi doit tre unique parmi tous les index de toutes les tablesL'option UNIQUE permet de dfinir la prsence ou non de doublons pour les valeurs de la colonne Les options ASC/DESC permettent de dfinir un ordre de classement des valeurs prsentes dans la colonne Remarque: Oracle cre automatiquement un index sur les colonnes qui ont des contraintes Primary key et Unique

Suppression d'un index

DROP INDEX nomIndex

Les squences

Les squences

La cration de squences permet de gnrer par exemple des valeurs squentielles de cl primaire.CREATE SEQUENCE nom_squence[INCREMENT BY 1 ou autre_valeur][START WITH valeur][MAX VALUE valeur / NOMAXVALUE][MIN VALUE valeur / NOMINVALUE][CYCLE / NOCYCLE][CACHE valeur / NOCACHE];

Les squences

CYCLE / NOCYCLE permet de recommencer la valeur de dpart ou de sarrter au maximum (ou au minimum en cas de dcrmentation)CACHE permet de gnrer lavance des valeur (par dfaut 20 valeurs sont stockes en mmoire).

Les squences

Exemple :CREATE SEQUENCE compteurauto START WITH 1000INCREMENT BY 10NOMAXVALUENOCYCLE;Exemple : create sequence seqdeptincrement by 10start with 10

Utilisation des squences

Deux pseudo-colonnes permettent d'utiliser les squences :CURRVAL retourne la valeur couranteNEXTVAL incrmente la squence et retourne la nouvelle valeur insert into dept(dept, nomd) values (seqdept.nextval, 'Finances')

CURRVAL et NEXTVAL

On ne peut utiliser CURRVAL quaprs avoir utilis NEXTVAL au moins une fois dans la session de travailNEXTVAL modifie immdiatement la valeur future pour les autres transactions, mme sil est lanc dans une transaction non valideLa valeur de CURRVAL ne dpend que des NEXTVAL lancs dans la mme transaction

Modification des squences

ALTER SEQUENCE nom_squence INCREMENT BY entier1alter sequence seqdept increment by 5 On ne peut modifier la valeur de dpart

Les squences

Exemple :INSERT INTO MACHINE (numeleve, nomeleve, prenomeleve) VALUES (compteurauto.NEXTVAL, Pamalin, Antony);insert into dept(dept, nomd) values (seqdept.nextval, 'Finances')Supprimer une squence :DROP SEQUENCE nom_squence;Modifier une squence (mmes paramtres que lors de la cration) :ALTER SEQUENCE

Informations sur les squencesAfficher la valeur dune squence :select seqdept.currval from dualTables du dictionnaire des donnes :USER_SEQUENCES et ALL_SEQUENCESExemple: SELECT * FROM user_sequence WHERE sequence_name = nom_squence;select seqdept.currval from dual

ORACLE:PL/SQL labor par: EL ALAMI Nama *Gestion des transactionsObjectif:Pour permettre diffrents utilisateur de travailler simultanment et partager les meme ressource Tous autre utilisateur ne peut intervenir sur les mmes donnes tant quelles ne sont pas dverrouilles

ORACLE:PL/SQL labor par: EL ALAMI Nama

Gestion des transactionsUne transaction est un ensemble de modifications de la base qui forme un tout indivisibleIl faut effectuer ces modifications entirement ou pas du tout, sous peine de laisser la base dans un tat incohrentLes SGBDs permettent aux utilisateurs de grer leurs transactions :COMMIT : les modifications de la transaction en cours deviennent dfinitives et visibles de tous,ROLLBACK : les modifications de la transaction en cours sont toutes annules

Transaction: Concept fondamental

Principe dutilisation dune transaction

ORACLE:PL/SQL labor par: EL ALAMI Nama *Proprits des transactionsLatomicit: Une transaction est un ensemble de mises jour de la base dpendantes. soit elles sont toutes valides soit aucune ne lestCohernce: lexecution dune transaction fait pass la base de donnes dun tat consistant un autre etat consistantIsolation:Les rsultats d'une transaction ne sont visibles aux autres transactions qu'une fois la transaction valide.Durabilit: aprs validation, mise jour prennes mme si panne


Transactions Dbut de transaction implicite Dbut de toute commande SQL en dbut de session, Fin de la transaction prcdente ensuite.Fin de transaction explicite Commandes de validation (COMMIT) ou annulation (ROLLBACK),Fin de transaction implicite Commande de dfinition de donnes (CREATE, ALTER, DROP etc.) Fin de session, dtection de problme par le SGBD ou arrt anormal du SGBD

Gestion des transactionsLes principaux ordres SQL de gestion des transactions sont les suivants:COMMIT COMMIT ;Termine une transaction par la validation des actions effectuesAnnulation des verrous ventuels et publication dfinitive des modifications effectues aux autres utilisateursROLLBACK ROLLBACK ; Termine une transaction en annulant toutes les actions effectues. Sous-transaction : SAVEPOINT positionnement dun point intermdiaire dans la transactionSAVEPOINT ; annulation des actions depuis ce point de repre (et non depuis le dbut) ROLLBACK TO ;LOCK TABLE ordre de verrouillage explicite

gestion des transactions

Gestion des transactions En cours de transaction : mmorisation des actions ralises dans un journal (fichier de log ou de redolog)

ORACLE:PL/SQL labor par: EL ALAMI Nama *Transaction : exempleEXEMPLE--- debut de sessionConnect username/pwq...UpdateInsert into ...Commit--- fin transaction 1


ORACLE:PL/SQL labor par: EL ALAMI Nama *Gestion des transactions--debut de la 2eme tansactionInsert into...---debut sous transaction-2Savepoint SV1;Delete fromROLLBACK to SV1;---annulation sous-transaction2COMMIT;--valide tous le reste de la transaction2--fin transaction 2


Transaction : exemple

DELETE FROM emp WHERE .... ;INSERT INTO TABLE emp VALUES ... ;COMMIT ;ALTER TABLE dept ADD . ;UPDATE emp ... SET ;SAVEPOINT a ;DELETE FROM emp WHERE .... ;UPDATE personne SET .... ;ROLLBACK to a ;ROLLBACK;

ORACLE:PL/SQL labor par: EL ALAMI Nama *Gestion des transactionsUtilisation de la commande COMMITLa commande commit valide dfinitivement les mises jour opres dans la base lors dune transaction.jusqu la validation dfintive, les autres utilisateurs ne peuvent pas voir les donnes modifies mais telles quelles taient avant les changements.


Dbut dune transaction

Fin dune transaction

ORACLE:PL/SQL labor par: EL ALAMI Nama *Gestion des transactionsUpdate e_service set nom=achat WHERE no=23;-- pose dun verrou sur les donnes manipules dans cet order SQLCOMMIT;-- verification dfinitive de la transaction


ORACLE:PL/SQL labor par: EL ALAMI Nama *Utilisation de la commande ROLLBACK cette commande annule dfinitivement les mises jour opres dans la base lors dune transactionGestion des transactions


ORACLE:PL/SQL labor par: EL ALAMI Nama *Utilisation de savepointlordre Savepoint dcoupe une transaction en sous-transactions.lordre SAVEPOINT est utiliss conjointement lordre ROLLBACK TOGestion des transactions


ORACLE:PL/SQL labor par: EL ALAMI Nama *

Update e_emp set salaire=salaire*02 where titre=magazinier;Savepoint inserer;Insert into e_emp values(num_emp,...).ROLLBACK TO inserer;Gestion des transactions


TransactionsEn gnral il existe deux modes transactionnels:aprs un SET AUTOCOMMIT ON toute requte SQL est considre comme une transaction en elle-mmeaprs un SET AUTOCOMMIT OFF il faut ajouter explicitement un COMMIT (ou un ROLLBACK) pour marquer la fin dune suite de requtes SQL

ORDRE SELECTOprateurs et fonctionsTypes numriquesNon typsType dateType alphanumrique

DECODECASEGREATESTLEASTNVL

SYSDATEADD_MONTHSNEXT_DAYLAST_DAYMONTHS_BETWEEN

SUBSTRINSTRUPPERLOWERLTRIMLPADABSCEILFLOORMODPOWERROUNDSQRTCOS

Fonctions non types

DECODEDECODE( crit, val1, result1 [, val2, result2] ... [, default] )

Elle renvoie result1 si critre =val1, result2 si critre=val2, ou dfault dans tous les autres cas.

Ex : SELECT DECODE (deptno, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 40, 'OPERATION', 'NONE') FROM emp;

CASE CASE WHEN cond1 THEN expr1 [cond2 THEN expr2 ] [ELSE expr] END

En fonction de la condition vrifie, l'expression expr est xecute.

GREATESTGREATEST(expr1,expr2,)Renvoie la plus grande valeur parmi expr1,expr2,

LEASTLEAST(expr1,expr2,

Renvoie la plus petite valeur parmi expr1,expr2,

NVL NVL(expr1,expr2)Renvoie la valeur de expr1 si elle est NOT NULL, sinon expr2.

Fonctions non types

Exemple:Liste des employs avec leur catgorie (prsident = 1, directeur = 2, autre = 3) :SELECT NOME, DECODE(POSTE, 'PRESIDENT', 1, 'DIRECTEUR', 2, 3) FROM EMP

Liste des employs avec leur catgorie (prsident = 1, directeur = 2, autre = 3), en appelant la colonne Niveau :Solution 1:SELECT nomeCASEWHEN (poste = 'Prsident') THEN 1WHEN (poste = 'Directeur') THEN 2ELSE 3END NiveauFROM emp;

Solution 2:SELECT nome,CASE posteWHEN 'Prsident' THEN 1WHEN 'Directeur' THEN 2ELSE 3END NiveauFROM emp;

Fonctions numriquesCertaines des fonctions numriques utilises sous SQL

FonctionSyntaxeRleABSABS(n NUMBER) : NUMBERValeur absolue de nCEILCEIL(n NUMBER) : NUMBEREntier immdiatement >= nFLOORFLOOR((n NUMBER) : NUMBEREntier immdiatement

Fonctions numriques

Exemple SELECT FLOOR(10.8) AS VAL1, POWER(2,4) AS VAL3, ROUND(123.456,2) AS VAL4, SIGN(123) AS VAL5,SIGN(-2) AS VAL6 From table;Rsultat

Fonctions numriques

Exemple : Donner pour chaque employ son salaire journalier. Solution :SELECT nom, ROUND(salaire/22,2) FROM emp WHERE N_dept = 20;

Fonctions alphanumriques

FonctionSyntaxeRleLENGTHLENGTH(str VARCHAR2) : numriqueLongueur d'une chaneSUBSTRSUBSTR(str VARCHAR2,pos,length) : chaneRenvoie une sous-chaneINSTRINSTR(str1 VARCHAR2,str2 VARCHAR2,pos,nocc)Pos et nocc sont facultativesPosition d'une ss-chane dans une chaneUPPERUPPER(str VARCHAR2) : chaneRenvoie en MAJUSCULELOWERLOWER(str VARCHAR2) : chaneRenvoie en minusculeLPADLPAD(chane, long [,car])Car est facultatifcomplte (ou tronque) chane la longueur long. La chane est complte gauche par le caractre (ou la chane de caractres) carRPADRPAD(chane, long [,car]) a une fonction analogue, chane tant compl te droite LTRIMLTRIM(VARCHAR2[,set]) Supprime les sets gaucheRTRIMLTRIM(VARCHAR2[,set])Supprime les sets droiteREPLACEREPLACE(char, search_string [,replacement_string]) Remplacement d'une ss-chane dans une chaneASCIIASCII(ch CHAR)Code ASCII d'un caractreINITCAPINITCAP(str VARCHAR2) : chaneRenvoie premire lettre en CAPITAL CHRCHR(n NUMBER) : NUMBERCaractre du code ASCII n


ConcatnationSELECT emp.nom||' travaille a ||dept.nom as employe FROM emp inner join dept on emp.n_dept=dept.n_dept and salaire >=3000;


INSTR: Position du deuxime 'A' dans les postes : SELECT INSTR (POSTE, 'A', 1, 2) FROM EMPLPAD: SELECT LPAD (NOME, 10, '.') FROM EMP.


TRANSLATE(chane, car_source, car_cible)La fonction TRANSLATE remplace chaque caractre de la chane chane prsent dans l'ensemble de caractres car_source par le caractre correspondant (de mme position) de l'ensemble car_cible.Exemple : remplacer les A et les M par des * dans les noms des employs :SELECT TRANSLATE (NOME, 'AM', '**') FROM EMPREPLACE(chane, ch1, ch2 ) remplace ch1 par ch2 dans chane.

Fonction TO_CHARLa fonction TO_CHAR permet de convertir un nombre ou une date en chane de caractre en fonction d'un format :Pour les nombres :TO_CHAR (nombre, format) avec 9 reprsente un chiffre (non reprsente si non significatif)0 reprsente un chiffre (prsent mme si non significatif). point dcimal apparentExemple: Affichage des salaires avec au moins trois chiffres (dont deux dcimales)SELECT TO_CHAR(SAL, '9990.00') FROM EMP

Fonctions DATE

FonctionSyntaxeRleSYSDATESYSDATE Retourne la date OSADD_MONTHSADD_MONTHS(date1,n) Rajoute n mois la date date1MONTHS_BETWEENMONTHS_BETWEEN(date1,date2)Nombre de mois entre date1 et date2LAST_DAYLAST_DAY(date1)Date du dernier du mois de date1NEXT_DAYNEXT_DAY(date1,str)Date du prochain jour indiqu par strTO_DATETO_DATE(char [, fmt [, 'nlsparams'] ]) Conversion d'une chane de caractre en date selon le format fmtROUNDROUND(date1[,fmt]) Arrondit date1 selon le format fmt

Affichage d'une colonne de datesTO_CHAR(date,format)Avec paramtres date : la date convertir, format : le format de conversion rsultat une date sous forme de chane

Affichage d'une colonne de datesFormat: YYYY anneYY deux derniers chiffres de l'anneWW numro de la semaine dans l'anneMM numro du moisDDD numro du jour dans l'anneDD numro du jour dans le moisD numro du jour dans la semaine (1 pour lundi, 7 pour dimanche)HH ou HH12 heure (sur 12 heures)HH24 heure (sur 24 heures)MI minutes

Exemple: select sysdate from dual;Avec dual C'est une table particulire dfinie sous Oracle et qui permet l'utilisation de la commande SELECT lorsqu'on n'a pas de colonne de table afficherselect to_char(sysdate,'DD MONTH YYYY') from dual;select to_char(sysdate,'Day DD MONTH YYYY') from dual

select sysdate, user, round(3676 / 7) from dual

LCD: langage de contrle de donnes

LCD: langage de contrle de donnes Langage de dfinition du contrle d'accs aux donnes Dfinit les droits en lecture/criture des utilisateurs Contrle l'attribution de privilges Commandes GRANT et REVOKE

Les diffrents privilgesPrivilges Privilge systme : droit dexcuter un ordre SQL sur nimporte quel type dobjet. Par dfaut, droit rserv uniquement DBA.Privilge objet : droit d accs un objet prcis dans un autre schma que le sien.Par dfaut, un utilisateur qui cre un nouvel objet tous les droits sur lui, les autres aucun (sauf DBA)

Privilges objetSELECT: droit de lecture INSERT: droit d'insertion DELETE: droit de suppression UPDATE: droit de mise jour ALTER: droit de modification de la dfinition de table INDEX: droit de crer un index EXECUTE: programmes PL/SQLALL: tous les droits prcdents GRANT OPTION: droit de donner les droits (possds)

GRANT et REVOKE GRANT attribut des privilges sur des tables (ou des vues) certains utilisateurs ou tous. Possibilit de restreindre les privilges sur certaines colonnes seulement Syntaxe: GRANT { privilge_objet [, privilge objet ...] | ALL } ON table1, ..., tablen, vue1, ..., vuen TO user1, ..., usern|PUBLIC [WITH GRANT OPTION];

GRANT et REVOKEAvec GRANT OPTION: droit au bnficiaire daccorder ce privilge dautres utilisateursPrivilge et type dobjet:Table : ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATEVue : ALTER, DELETE, INSERT, SELECT, UPDATESquence : ALTER, SELECTPrivilge sur attributsExemple GRANT SELECT ON ma_table(col1, col2) TO PUBLIC;

GRANT et REVOKEREVOKE annule les privilges des utilisateurs Syntaxe: REVOKE privilge [GRANT OPTION FOR] ON table1, ..., tablen, vue1, ..., vuen FROM user1, ..., usern|PUBLIC

GRANT et REVOKE: exemple

GRANT SELECT ON emp TO u1 ;GRANT SELECT, UPDATE (salaire, adresse) ON emp S TO u1, u2 ;GRANT ALL ON emp, dept TO u1,u2,u3;GRANT SELECT ON emp TO PUBLIC ;GRANT INDEX ON emp TO u1 ;GRANT SELECT ON emp TO u2 WITH GRANT OPTION ;

Vues et scurit des donnesVues moyen ais davoir un contrle direct sur les accsExemple :GRANT sur attributs GRANT SELECT ON Table1 (Att1) TO PUBLIC;GRANT sur vue CREATE VIEW S_ATT1 AS SELECT Attr1 FROM Table1;GRANT SELECT ON S_ATT1 TO PUBLIC;

SGBD Oracle : utilisateursDroit de cration limit au DBASyntaxe: CREATE USER nom IDENTIFIED BY passwd GRANT [CONNECT|RESOURCE|DBA] TO nom INDENTIFIED BY passwdREVOKE [CONNECT|RESOURCE|DBA] FROM nom Suppression: DROP USER nom

SGBD Oracle : utilisateursModifier son mot de passe :ALTER USER IDENTIFIED BY GRANT CONNECT TO user IDENTIFIED BY passwdUTILISATEURS PREDEFINISSYS propritaire du dictionnaire de donnesSYSTEM administrateur ORACLE par dfaut

SGBD Oracle : rlesRegroupement dutilisateurs qui partageront les mmes privilgesUn rle peut tre attribu ou retir un ou plusieurs utilisateursRle prdfinis ORACLECONNECT connexion, CREATE et SELECT sur les objets courants RESOURCE droits en cration plus avancs (index, cluster, trigger) DBA

SGBD Oracle : rlesCREATE ROLE nomRole; Les rles sutilisent indiffremment comme identifiants dutilisateur ou de privilgeGRANT privileges ON table TO nomRole;GRANT nomRole TO user;

SGBD Oracle : rlesRles prdfinisCONNECT Utilisateur de base : CREATE SEQUENCE, CREATE TABLE, CREATE VIEWRESOURCE Complment pour utilisateur un peu plus avanc : CREATE PROCEDURE, CREATE TRIGGERDBA Tous les privilges avec WITH ADMIN OPTIONEXP_FULL_DATABASE Privilges requis pour lexportationIMP_FULL_DATABASE Privilges requis pour limportationSELECT_CATALOG_ROLE SELECT sur les objets du dictionnaireEXECUTE_CATALOG_ROLE EXECUTE sur les objets du dictionnaire

Exercices dapplicationAccorder le droit de SELECT sur la table DEPT un autre user. Accorder les droits de INSERT et UPDATE sur DEPT un autre user. Accorder tous les droits sur la table DEPT un autre user. Crer une vue relative au numro, nom, titre, numro de dpartement des employs. Puis accorder les droits de SELECT sur la vue un autre user Crer une vue relative toutes les informations des employs du service 50. Puis accorder tous les droits sur la vue un autre user. Retirer le droit INSERT l'autre user, sur la table DEPT.

Correction GRANT SELECT ON dept TO nom de l'autre user ; GRANT INSERT,UPDATE ON dept TO nom de l'autre user ; GRANT ALL ON dept TO nom de l'autre user ; CREATE VIEW empvue AS SELECT noemp, nom, titre, nodept FROM emp ; GRANT SELECT ON empvue TO nom de l'autre user ; CREATE VIEW emp50 AS SELECT * FROM emp WHERE nodept = 50 ; GRANT ALL ON emp50 TO nom de l'autre user ; REVOKE INSERT ON dept FROM nom de l'autre user ;

*Ecriture des Ordres SQLEn suivant les rgles et indications simples ci-dessous, vous pourrez crer des ordres corrects, simples lire et diter. Sauf indication contraire, les ordres SQL peuvent tre crits indiffremment en majuscules ou en minuscules.Les ordres SQL peuvent tre saisis sur plusieurs lignes.Les mots-cls ne doivent pas tre scinds sur deux lignes diffrentes, ni abrgs.Les clauses se placent gnralement sur des lignes distinctes pour en faciliter la lecture et l'dition.L'utilisation de tabulations et d'indentations permet une meilleure lisibilit.Gnralement, les mots-cls sont saisis en majuscules, et tous les autres termes, tels que les noms de tables et de colonnes, sont saisis en minuscules.Dans le SQL*Plus, un ordre SQL est saisi au prompt SQL, et les lignes qui suivent sont numrotes. C'est ce qu'on appelle le buffer SQL. Il ne peut y avoir qu'un seul ordre courant la fois dans le buffer.Excution d'Ordres SQLPlacer un point-virgule (;) la fin de la dernire clause.Placer un slash (/) seul sur la dernire ligne du buffer.Placer un slash (/) au prompt SQL.Entrer la commande RUN SQL*Plus au prompt SQL.**Possibilits de l'ordre SQL SELECTUn ordre SELECT permet d'extraire des informations d'une base de donnes. L'utilisation d'un ordre SELECT offre les possibilits suivantes :Slection : SQL permet de choisir dans une table, les lignes que l'on souhaite ramener au moyen d'une requte. Divers critres de slection sont disponibles cet effet.Projection : SQL permet de choisir dans une table, les colonnes que l'on souhaite ramener au moyen d'une requte. Vous pouvez dterminer autant de colonnes que vous le souhaitez. Jointure : SQL permet de joindre des donnes stockes dans diffrentes tables, en crant un lien par le biais d'une colonne commune chacune des tables. Les jointures seront dcrites en dtail dans la suite de ce cours.

*Ordre SELECT ElmentaireDans sa forme la plus simple, l'ordre SELECT comprend :Une clause SELECT prcisant les colonnes afficherUne clause FROM spcifiant la table qui contient les colonnes indiques dans la clause SELECTSyntaxe :SELECTliste d'une ou plusieurs colonnesDISTINCTsuppression des doublons* slection de toutes les colonnescolumnslection de la colonne dsignealiasattribue des en-ttes diffrents aux colonnes slectionnesFROM table spcifie la table qui contient les colonnesRemarque : nous utiliserons les termes mot-cl, clause et ordre tout au long de ce cours.Un mot-cl est un lment SQL individuel. Par exemple, SELECT et FROM sont des mots-cls.Une clause est une partie d'un ordre SQL. Par exemple, SELECT empno, ename, ... est une clause.Un ordre est la combinaison de deux clauses ou plus. Par exemple, SELECT * FROM emp est un ordre SQL.*Slection de Toutes les Colonnes et de Toutes les LignesPour afficher toutes les colonnes d'une table, placez un astrisque la suite du mot-cl SELECT (*). Dans l'exemple de la diapositive, la table des dpartements (DEPT) comporte trois colonnes : DEPTNO, DNAME et LOC. La table comporte galement quatre lignes, une pour chaque dpartement. Vous pouvez aussi afficher toutes les colonnes de la table en les numrant toutes la suite du mot-cl SELECT. Par exemple, l'ordre SQL suivant affiche toutes les colonnes et toutes les lignes de la table DEPT :

SQL> SELECTdeptno, dname, loc 2 FROM dept;*Slection de Colonnes Spcifiques et de Toutes les LignesL'ordre SELECT peut tre utilis pour afficher des colonnes spcifiques de la table. Pour cela, indiquez les noms de colonnes spars par des virgules. L'exemple ci-dessus affiche tous les numros de dpartement de la table DEPT, ainsi que leur localisation. Dans la clause SELECT, indiquez les colonnes dans l'ordre o vous souhaitez qu'elles vous soient rapportes. Par exemple, si vous voulez que la colonne "LOC" soit place avant la colonne "DEPTNO", utilisez l'ordre suivant :

.SQL> SELECTloc, deptno 2 FROM dept;LOC DEPTNO ------------- ---------NEW YORK 10DALLAS 20CHICAGO 30BOSTON40*Expressions ArithmtiquesSi ncessaire, vous pouvez modifier l'affichage des donnes, effectuer des calculs ou tudier diffrentes hypothses au moyen d'expressions arithmtiques. Une expression arithmtique peut contenir des noms de colonnes, des valeurs numriques constantes et des oprateurs arithmtiques.Oprateurs ArithmtiquesCette diapositive prsente les oprateurs arithmtiques disponibles dans SQL. Vous pouvez les utiliser dans n'importe quelle clause d'un ordre SQL, except dans la clause FROM. *Utilisation des Oprateurs ArithmtiquesL'exemple de la diapositive utilise l'oprateur d'addition pour calculer l'augmentation de salaire de $300 pour tous les employs, puis affiche une nouvelle colonne SAL+300. A noter que la colonne SAL+300 qui rsulte de ce calcul n'est pas une nouvelle colonne de la table EMP ; elle n'est que l'affichage d'un rsultat. Par dfaut, le nom d'une nouvelle colonne est issu du calcul dont elle provient : dans ce cas prcis, sal+300.Remarque : SQL*Plus ignore les espaces situs avant et aprs l'oprateur arithmtique. *Priorit des OprateursLorsqu'une expression comporte plusieurs oprateurs, les oprateurs de multiplication et de division sont valus en premier. Lorsque les oprateurs d'une expression sont de priorit identique, ils sont valus de la gauche vers la droite. Pour forcer la priorit d'valuation d'une expression, placez-la entre parenthses. *Priorit des Oprateurs (suite)L'exemple de la diapositive prsente le nom, le salaire et le revenu annuel des employs. Le revenu annuel est calcul en multipliant le salaire mensuel par 12, puis en ajoutant un bonus exceptionnel de $100. Remarquez que la multiplication a t effectue avant l'addition. Remarque : l'utilisation des parenthses renforce l'ordre normal de priorit des oprateurs et amliore la clart. L'expression ci-dessus, par exemple, peut tre crite comme suit : (12*sal)+100, sans que le rsultat en soit modifi.

*Utilisation des ParenthsesVous pouvez modifier les rgles de priorit en utilisant des parenthses pour prciser l'ordre dans lequel les oprateurs doivent tre valus.L'exemple de la diapositive prsente les noms, salaires et revenus annuels des employs. Le calcul du revenu annuel est effectu en multipliant le salaire mensuel ainsi que le bonus de $100 par 12. Les parenthses rendent l'addition prioritaire sur la multiplication.

*Les Valeurs NULLLorsqu'il manque une valeur dans une colonne sur une ligne, la valeur est dite NULL. Une valeur NULL est une valeur non disponible, non affecte, inconnue ou inapplicable. Une valeur NULL est diffrente du zro ou de l'espace. Le zro est un chiffre et l'espace est un caractre. Quel que soit le type de donnes d'une colonne, celle-ci peut contenir des valeurs NULL, except lorsque cette colonne a t dfinie comme NOT NULL ou comme CLE PRIMAIRE lors de sa cration. Dans la colonne COMM de la table EMP, v

Documents

Système de Gestion de Base de Données