Système de Gestion de Base de Données
Système de Gestion de Base de Données
Système de Gestion de Base de Données
de donnes
Plan
Introduction aux bases de donnes
Algbre relationnel
Langage SQL
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.
Modle de donnes
Premires 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 hirarchique
Caractristiques
Problmes
Redondance des donnes : une modification du
solde dun seul compte ncessite plusieurs mises
jour
Coteux en espace mmoire
Accs aux informations parfois difficiles et/ou
coteuses : clients possdant un compte donn
SGBD rseau
Compos denregistrements et de liens
Saku
Koivu
Hanna
Koivu
Pierre
Delisle
24140
175
SGBD rseau
Implmentation avec des pointeurs
Avantages
Redondance limine, moins despace mmoire
Manipulations plus faciles
Inconvnients
Accs squentiel, donc lent
Difficile programmer et maintenir
Ranges
Analogue un enregistrement dans un fichier de donnes
Identifies par une cl unique (cl primaire) compose
dune ou plusieurs colonnes
Algbre relationnelle
Un 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, jointure
Requte
Une requte est destine obtenir des informations
prcises et ordonnes sur la base de donnes.
On peut galement par le biais des requtes:
Oprateurs ensemblistes
Union
Opration 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 relations
L'union est une opration commutative.
Notations : R1 R2, UNION(R1, R2)
Oprateurs ensemblistes:
Intersection
Lintersection 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 Ou
R3 = INTERSECTION (R1, R2)
Oprateurs ensemblistes:
Intersection
Oprateurs ensemblistes:
Diffrence
Diffrence
Opration 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
RELATION2
Notation : R3 = R1 - R2 Ou
R3 = DIFFERENCE (R1, R2)
Oprateurs ensemblistes:
Diffrence
Oprateurs de base:Projection
projection : Permet de ne retenir que quelques
attributs d'une relation
Def.: Soit la relation R1(a1, , an). La projection
de la relation R1 sur les attributs (a1, , am),
m<n, consiste laborer une relation R2, qui aura
pour schma le mme que celui de R1 sauf les
attributs (am+1, , an). Ainsi, la relation R2 en
rsultat aura la mme extension que celle de R1,
mais ses tuples auront des attributs en moins.
Notations : (a1, , am)(R1), (R1)[ a1, , am],
PROJECT(R1 , a1, , am)
Oprateurs de base:Projection
Projection : exemple
Oprateurs de base:Projection
Remarque: 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.
exemple 2
Jointure
Dfinition 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) * R2
Avec 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 P
Notation : R (P) * S
Rsultat : 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: exemple
Jointure: exemple 1
Jointure: exemple
Jointure: exemple1(suite)
Jointure : exemple
La jointure = mise en correspondance
de 2 tables selon un critre.
Jointure : type
Plusieurs types de Jointures existent :
Equijointure (ou jointure naturelle),
Jointure externe gauche ou droite
Equijointure : le pivot utilise loprateur =
(jointure naturelle)
Thtajointure : le pivot utilise les
oprateurs <, <=, >, >=, != ou <>
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 B
Exercices
Afficher, pour le client numro 125, le
numro de vente et la marque des
produits achets
Solution:
Semi - Jointure
Dfinition : 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:
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
R1
Condition :
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 X
Notation : 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: Exemple
Division : utilisation
Soient les tables suivantes:
LesEtudiants(NoEt, nom, prenom, ville)
LesInscriptions(NoEt, matiere)
LesRsultats(NoEt, matiere, dateEpreuve, note)
Requtes
Quels sont les lves inscrits dans toutes les
matires ?
Quels sont les lves ayant une note chacune
des preuves ?
Exercice 1
Requtes
Numros, 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
au
moins une preuve laquelle x a obtenu une meilleure
note que y ?
Correction
fonctions
Les 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
expressions
drives
Expressions algbriques :
exemples
Considrons la base constitue des relations:
Film(numfilm, titre, ralisateur, anne, dure)
Acteur(numacteur, nom, prnom, dateNaissance)
Casting(numfilm, numacteur, personnage)
Expressions algbriques :
exemples
Expressions algbriques :
exemples
Expressions algbriques :
exemples
Expressions algbriques :
exemples
SQL: Prsentation(Structured
Query Language)
Le langage SQL
Langage standard - 4me gnration (SQL89,
SQL2, SQL3)
3 langages pour BdD relationnelles
Langage de Description de Donnes (LDD)
Cration de tables,
Langage de Manipulation de Donnes (LMD)
Interrogation/Modification de la BdD
Langage de Contrle de donnes (LCD)
Gestion des droits daccs
Plan de la prsentation
Langage de Description de Donnes
Instructions CREATE, DROP, ALTER TABLE
Introduction SQL
Dfinition des donnes
Cration de table
Une table (relation en algbre relationnelle) se
cr par linstruction CREATE TABLE
CREATE TABLE nom_table
(nom_col1 type_col1 [contrainte1]
[, nom_col2 type_col2 [contrainte2]]);
Exemple1
CREATE TABLE Client
(num_client NUMBER(5) PRIMARY KEY,
nom VARCHAR(30) NOT NULL,
solde NUMBER,
type VARCHAR(10) CHECK (type IN (particulier,
pme,autre));
Exemple1
CREATE 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)
)
Exemple 2
create table WORK_ON(
PCODE int,
NSS char(6),
DateDep date,
constraint WORK_ON_PK primary key (PCODE, NSS)
)
Contraintes Inter-relationnels
REFERENCES et FOREIGN KEY:
permettent de dfinir des contraintes
d'intgrit rfrentielle deux formes
syntaxiques:
pour attributs seuls
pour plusieurs attributs
Contraintes Inter-relationnels
Exemple de contraintes
Exemple de contraintes
Certaines contraintes portent sur plusieurs
colonnes et ne peuvent etre indiques que
comme contraintes de table
Quelques remarques
Quelques remarques
DROP TABLE
DROP TABLE nom_de_table
DROP TABLE employee
la suppression est autorise seulement si
la
table n'est pas reference
Exemple de modification de
table
Exemple 1
Vues ORACLE
Lister l'ensemble des tables cres :
select table_name from user_tables;
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
ALTER
ALTER
ALTER
TABLE
TABLE
TABLE
TABLE
client
client
client
client
Introduction SQL
Manipulation des donnes
Insertion denregistrement
Un enregistrement (tuple en algbre relationnelle)
sinsre dans une table par linstruction INSERT INTO
INSERT INTO nom_table[(nom_col1, nom_col2, )]
VALUES (val1, val2, )
[(val3, val4, )];
Exemple :
INSERT INTO Client
VALUES (1, Durand , 0, particulier ),
(2, Martin , 50, pme );
Projection
Table 1
Table 1
Jointure
Table 1
Table 2
Requte SELECT
Une requte dinterrogation de la BdD
scrit laide de linstruction SELECT
[DISTINCT]
[DISTINCT] {*,
{*, column
column [alias],...}
[alias],...}
table;
table;
DNAME
-------------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Projection
La commande SELECT:
SELECT [ALL|DISTINCT] <liste de
projection> FROM <liste de tables>
Exemple. Projection partir de la table
client
SELECT nom, prenom FROM client;
LOC
------------NEW YORK
DALLAS
CHICAGO
BOSTON
Commande
noClient
10
20
10
10
30
20
40
40
SELECT
FROM
dateCommande
01/ 06/ 2000
02/ 06/ 2000
02/ 06/ 2000
05/ 07/ 2000
09/ 07/ 2000
09/ 07/ 2000
15/ 07/ 2000
15/ 07/ 2000
Multi-ensemble !
Clause DISTINCT
Produire les noClient et dateCommande de toutes les
SELECT
DISTINCT noClient, dateCommande
Commandes
FROM
Commande
noClient
10
20
10
10
30
20
40
08/10/15
dateCommande
01/ 06/ 2000
02/ 06/ 2000
02/ 06/ 2000
05/ 07/ 2000
09/ 07/ 2000
09/ 07/ 2000
15/ 07/ 2000
noClient, dateCommande
(Commande)
135
Expressions Arithmtiques
Possibilit de crer des expressions
avec des donnes de type NUMBER et
DATE au moyen doprateurs
arithmtiques
Oprateur Description
+
Addition
Soustraction
Multiplication
Division
La Valeur NULL
ENAME
JOB
COMM
---------- --------- --------KING
PRESIDENT
BLAKE
MANAGER
...
TURNER
SALESMAN
0
...
14 rows selected.
ENAME
12*SAL+COMM
---------- ----------KING
LAlias de Colonne
Renomme un en-tte de colonne
Est utile dans les calculs
Suit immdiatement le nom de la
colonne ; le mot-cl AS plac entre le
nom et lalias est optionnel
Doit obligatoirement tre inclus entre
guillemets sil contient des espaces, des
caractres spciaux ou si les
majuscules/minuscules doivent tre
diffrencies
FROM
emp;
NAME
SALARY
------------- --------...
SQL> SELECT ename "Name",
2
sal*12 "Annual Salary"
3 FROM
emp;
Name
Annual Salary
------------- ------------...
KING
BLAKE
CLARK
JONES
JOB
...
DEPTNO
PRESIDENT
MANAGER
MANAGER
MANAGER
10
30
10
20
rechercher tous
les employs du
dpartement 10
EMP
EMPNO ENAME
JOB
7839 KING
PRESIDENT
7782 CLARK MANAGER
7934 MILLER CLERK
...
DEPTNO
10
10
10
*
Article
prixUnitaire < 20 AND noArticle > 30
noArticle
60
70
95
08/10/15
description
Erable argent
Herbe puce
Gnvrier
prixUnitaire
15.99
10.99
15.99
146
Slection
La clause WHERE
Syntaxe de conditionSQL
{conditionSimple
(conditionSQL)|
NOT(conditionSQL)|
conditionSQL AND conditionSQL |
conditionSQL OR conditionSQL}
Syntaxe de la conditionSimple :
{expression {=|<|>|<=|>=|<>} expression|
expression BETWEEN expression AND expression|
expression {IS NULL |IS NOT NULL}|
expression {IN |NOT IN} listeConstantes|
expression {LIKE |NOT LIKE} patron}
08/10/15
148
SQL> SELECT
2 FROM
3 WHERE
Autres oprateurs
expr1 BETWEEN expr2 AND expr3
Ex : solde BETWEEN 50 AND 100
ENAME
SAL
COMM
---------- --------- --------MARTIN
1250
1400
ConditionSQL - BETWEEN
BETWEEN permet de tester l'appartenance
une
fourchette
valeurs.
SQL>
SELECT
ename,de
sal
2
3
FROM
WHERE
emp
sal BETWEEN 1000 AND 1500;
ENAME
SAL
---------- --------MARTIN
1250
TURNER
1500
WARD
1250
ADAMS
1100
MILLER
1300
Limite
Limite
infrieure
suprieure
ConditionSQL - BETWEEN
Slectionner les Commandes du mois de
juin de l'anne 2000
SELECT
FROM
WHERE
SELECT
FROM
WHERE
08/10/15
*
Commande
dateCommande BETWEEN '01/06/2000' AND '30/06/2000'
*
Commande
dateCommande >= '01/06/2000' AND
dateCommande <='30/06/2000'
153
ConditionSQL - IN
IN permet de comparer une expression
avec une liste de valeurs.
Slectionner les Commandes du Client
dont le noClient est 10 ou 40 ou 80
SELECT
FROM
WHERE
SELECT
FROM
WHERE
08/10/15
*
Commande
noClient IN (10, 40, 80)
*
Commande
noClient = 10 OR noClient = 40 OR noClient = 80
154
ConditionSQL - LIKE
LIKE permet de rechercher des chanes de
caractres l'aide de caractres gnriques
Les conditions de recherche peuvent contenir
des caractres ou des nombres littraux.
(%) reprsente zro ou plusieurs caractres
( _ ) reprsente un caractre
SQL> SELECT
2 FROM
3 WHERE
ename
emp
ename LIKE 'S%';
ConditionSQL - LIKE
Slectionner les Clients dont le nomClient
contient le mot Le
SELECT *
FROM
WHERE
Client
nomClient LIKE '%Le%'
Client
nomClient LIKE '_o%k'
156
ConditionSQL - IS NULL
Recherche de valeurs NULL avec
loprateur IS NULL
Slectionner les Articles dont la
description est une valeur nulle
08/10/15
157
LOprateur AND
Avec AND, les deux conditions doivent tre
VRAIES.
SQL>
2
3
4
SELECT
FROM
WHERE
AND
EMPNO
--------7876
7934
ENAME
---------ADAMS
MILLER
JOB
SAL
--------- --------CLERK
1100
CLERK
1300
Utilisation de lOprateur
Avec OR, l'une ou l'autre
ORdes deux conditions doit
tre VRAIE.
SQL>
2
3
4
SELECT
FROM
WHERE
OR
EMPNO ENAME
JOB
SAL
--------- ---------- --------- --------7839
7698
7782
7566
7654
KING
BLAKE
CLARK
JONES
MARTIN
...
14 rows selected.
PRESIDENT
MANAGER
MANAGER
MANAGER
SALESMAN
5000
2850
2450
2975
1250
lOprateur NOT
SQL> SELECT ename, job
2 FROM
emp
3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
ENAME
---------KING
MARTIN
ALLEN
TURNER
WARD
JOB
--------PRESIDENT
SALESMAN
SALESMAN
SALESMAN
SALESMAN
... WHERE
... WHERE
... WHERE
1500
Rgles de Priorit
Ordre de priorit
1
2
3
4
Oprateur
Tous les oprateurs
de comparaison
NOT
AND
OR
Rgles de Priorit
SQL>
2
3
4
5
SELECT
FROM
WHERE
OR
AND
ENAME
ENAME
------------------KING
KING
MARTIN
MARTIN
ALLEN
ALLEN
TURNER
TURNER
WARD
WARD
JOB
SAL
JOB
SAL
----------------- ----------------PRESIDENT
5000
PRESIDENT
5000
SALESMAN
1250
SALESMAN
1250
SALESMAN
1600
SALESMAN
1600
SALESMAN
1500
SALESMAN
1500
SALESMAN
1250
SALESMAN
1250
Rgles de Priorit
SELECT
FROM
WHERE
OR
AND
ENAME
ENAME
------------------KING
KING
ALLEN
ALLEN
JOB
SAL
JOB
SAL
----------------- ----------------PRESIDENT
5000
PRESIDENT
5000
SALESMAN
1600
SALESMAN
1600
Ordre du rsultat
Le rsultat dune requte peut tre tri selon les
valeurs dune ou plusieurs colonne(s).
Par ordre
croissant :
SELECT * FROM Client ORDER BY solde;
Clause ORDER BY
Tri des lignes avec la clause ORDER BY
ASC : ordre croissant (par dfaut)
DESC : ordre dcroissant
Rsum
SELECT
FROM
[WHERE
[ORDER BY
Commande
dateCommande > '05/07/2000'
noClient
30
20
40
40
08/10/15
dateCommande
09/ 07/ 2000
09/ 07/ 2000
15/ 07/ 2000
15/ 07/ 2000
170
*
Client, Commande
Client
Commande
08/10/15
171
ENAME
----KING
BLAKE
... DEPTNO
... -----...
10
...
30
MILLER ...
10
DEPTNO
-----10
20
30
40
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
EMPNO
EMPNO DEPTNO
DEPTNO LOC
LOC
--------- ------------- --------------7839
10
NEW
7839
10 NEW YORK
YORK
7698
30
CHICAGO
7698
30 CHICAGO
7782
10
7782
10 NEW
NEW YORK
YORK
7566
20
DALLAS
7566
20 DALLAS
7654
30
7654
30 CHICAGO
CHICAGO
7499
30
CHICAGO
7499
30 CHICAGO
...
...
14
14 rows
rows selected.
selected.
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
la Jointure
Une jointure sert extraire des
donnes de plusieurs tables.
SELECT
SELECT
FROM
FROM
WHERE
WHERE
table1.column,
table1.column,
table1,
table1, table2
table2
table1.column1
table1.column1
table2.column
table2.column
== table2.column2;
table2.column2;
Jointure
Exemple:
SELECT * FROM Vehicule, Proprietaire
WHERE Vehicule.proprietaire = Proprietaire.numero;
dateCommande
Client, Commande
Client.noClient = Commande.noClient
Client.noClient
10
10
10
20
20
30
40
40
nomClient
Luc Sansom
Luc Sansom
Luc Sansom
DollarTremblay
DollarTremblay
Lin B
Jean Leconte
Jean Leconte
noTlphone
(999)999-9999
(999)999-9999
(999)999-9999
(888)888-8888
(888)888-8888
(777)777-7777
(666)666-6666
(666)666-6666
noCommande
1
3
4
2
6
5
7
8
dateCommande
01/ 06/ 2000
02/ 06/ 2000
05/ 07/ 2000
02/ 06/ 2000
09/ 07/ 2000
09/ 07/ 2000
15/ 07/ 2000
15/ 07/ 2000
dateCommande
08/10/15
Client.noCliente = Commande.noClient
(Client
175
Client
Commande
08/10/15
*
Client JOIN Commande ON
Client.noClient = Commande.numroCLient {SQL2}
176
Types de Jointures
Equijointure
Non-quijointure
Jointure externe
Autojointure
L Equijointure
EMP
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
------10
30
10
20
30
30
30
30
30
20
20
...
14 rows
DNAME
---------ACCOUNTING
SALES
ACCOUNTING
RESEARCH
SALES
SALES
SALES
SALES
SALES
RESEARCH
RESEARCH
selected.
Cl trangre Cl primaire
LOC
-------NEW YORK
CHICAGO
NEW YORK
DALLAS
CHICAGO
CHICAGO
CHICAGO
CHICAGO
CHICAGO
DALLAS
DALLAS
Extraction d'Enregistrements
avec les Equijointures
SQL> SELECT
2
3 FROM
4 WHERE
SELECT
FROM
WHERE
08/10/15
181
Auto-jointure
Quels sont les Clients qui ont le mme numro de tlphone?
SELECT
FROM
WHERE
Client.noClient, Client2.noClient,
Client2
Client.noTlphone = Client2.noTlphone
(Client
noClient, noClient2
Client NATURAL JOIN {SQL2}
Client AS Client2(noClient2, nomClient2, noTlphone)
(Client
(Client))
noClient, noClient2
noTlphone)
08/10/15
(Client)))
SELECT
FROM
Client.noClient, Client2.noClient
Client, Client AS Client2
Client.noTlphone = Client2.noTlphone
Client2(noClient2, nomClient2,
183
Non-Equijointures
EMP
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.
SALGRADE
GRADE LOSAL HISAL
----- ----- -----1
700
1200
2
1201
1400
3
1401
2000
4
2001
3000
5
3001
9999
Extraction d'Enregistrements
avec les Non-Equijointures
SQL>
2
3
4
SELECT
FROM
WHERE
BETWEEN
ENAME
SAL
GRADE
---------- --------- --------JAMES
950
1
SMITH
800
1
ADAMS
1100
1
...
14 rows selected.
Jointures Externes
EMP
ENAME
----KING
BLAKE
CLARK
JONES
...
DEPT
DEPTNO
-----10
30
10
20
DEPTNO
-----10
30
10
20
...
40
DNAME
---------ACCOUNTING
SALES
ACCOUNTING
RESEARCH
OPERATIONS
Jointures Externes
Les jointures externes permettent de visualiser
des lignes qui ne rpondent pas la condition
de jointure.
L'oprateur de jointure externe est le signe (+).
SELECT
SELECT
FROM
FROM
WHERE
WHERE
table.column,
table.column, table.column
table.column
table1,
table1, table2
table2
table1.column(+)
table1.column(+) == table2.column;
table2.column;
SELECT
SELECT
FROM
FROM
WHERE
WHERE
table.column,
table.column, table.column
table.column
table1,
table1, table2
table2
table1.column
table1.column == table2.column(+);
table2.column(+);
SELECT
FROM
WHERE
ORDER BY
ENAME
DEPTNO DNAME
---------- --------- ------------KING
10 ACCOUNTING
CLARK
10 ACCOUNTING
...
40 OPERATIONS
15 rows selected.
Client =
Commande
*
Client NATURAL LEFT OUTER JOIN Commande {SQL2}
Oracle
+ aprs colonne pour inclure la valeur NULL
08/10/15
189
Produire les noms et numros de tlphone des Employs qui sont aussi des Clients de la
ppinire
Table Client
noClient nomClient
10
Luc Sansom
20
Dollard Tremblay
30
Lin B
40
Jean Leconte
50
Hafedh Alaoui
60
Marie Leconte
70
Simon Lecoq
80
Dollard Tremblay
noTlphone
(999)999-9999
(888)888-8888
(777)777-7777
(666)666-6666
(555)555-5555
(666)666-6666
(444)444-4419
(333)333-3333
codeEmploy
CASD1
PIOY1
LAFH1
HASC1
Table Employ
nomEmploy
Dollard Tremblay
Yan Piochuneshot
Yvan Lafleur
Jean Leconte
noTlphone
(888)888-8888
911
(111)111-1111
(666)666-6666
(SELECT
nomClient as nomPersonne, noTlphone
FROM
Client)
INTERSECT
(SELECT
nomEmploy as nomPersonne, noTlphone
FROM
Employ)
nomPersonne
Dollard Tremblay
Jean Leconte
08/10/15
noTlphone
(888)888-8888
(666)666-6666
190
Fonctions de Groupe
Les fonctions de groupe agissent sur des groupes
de lignes et donnent un rsultat par groupe.
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
"salaire maximum
de la table EMP"
MAX(SAL)
--------5000
Fonctions de Groupe
Exemples doprateurs de
groupe
SELECT COUNT(*) FROM Vehicule
WHERE marque=BMW;
SELECT MAX(solde) FROM Client;
SELECT SUM(solde) FROM Client
WHERE nom = Durand;
AVG(sal), MAX(sal),
MIN(sal), SUM(sal)
emp
job LIKE 'SALES%';
FROM
emp;
MIN(HIRED MAX(HIRED
--------- --------17-DEC-80 12-JAN-83
FROM
WHERE
COUNT(*)
--------6
emp
deptno = 30;
FROM
WHERE
COUNT(COMM)
----------4
emp
deptno = 30;
FROM
AVG(COMM)
--------550
emp;
AVG(NVL(COMM,0))
---------------157.14286
Fonctions de groupe
Le nombre d'Articles diffrents vendre
ainsi que le prixUnitaire moyen des
Articles
SELECT
FROM
COUNT(*) AS nombreArticles,
AVG(prixUnitaire) AS prixMoyen
Article
nombreArticles
10
08/10/15
prixMoyen
19.49
200
Fonctions de groupe:suite
SELECT
FROM
nombrePrix
6
SELECT
FROM
Count(prixUnitaire) AS nombrePrixNonNull
Article
nombrePrixNonNull
10
08/10/15
201
EMP
Cration de Groupes de
Donnes
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
2916.6667
DEPTNO AVG(SAL)
"salaire
moyen pour ------- --------2175
chaque
10 2916.6667
dpartement
20
2175
de la table
30 1566.6667
EMP"
1566.6667
column, group_function
table
condition]
[GROUP BY
[ORDER BY
group_by_expression]
column];
DEPTNO AVG(SAL)
--------- --------10 2916.6667
20
2175
30 1566.6667
Table Commande
noCommande dateCommande
1
01/ 06/ 2000
3
02/ 06/ 2000
4
05/ 07/ 2000
2
02/ 06/ 2000
6
09/ 07/ 2000
5
09/ 07/ 2000
7
15/ 07/ 2000
8
15/ 07/ 2000
08/10/15
noClient
10
10
10
20
20
30
40
40
noClient
10
20
30
40
nombreCommandes
3
2
1
2
206
JOB
SAL
--------- --------MANAGER
2450
PRESIDENT
5000
CLERK
1300
CLERK
800
CLERK
1100
ANALYST
3000
ANALYST
3000
MANAGER
2975
SALESMAN
1600
MANAGER
2850
SALESMAN
1250
CLERK
950
SALESMAN
1500
SALESMAN
1250
'"somme des
salaires
de la table EMP
pour chaque
poste,
regroups par
dpartement"
DEPTNO
-------10
10
10
20
20
20
30
30
30
JOB
SUM(SAL)
--------- --------CLERK
1300
MANAGER
2450
PRESIDENT
5000
ANALYST
6000
CLERK
1900
MANAGER
2975
CLERK
950
MANAGER
2850
SALESMAN
5600
DEPTNO JOB
SUM(SAL)
--------- --------- --------10 CLERK
1300
10 MANAGER
2450
10 PRESIDENT
5000
20 ANALYST
6000
20 CLERK
1900
...
9 rows selected.
SELECT
SELECT
FROM
FROM
deptno,
deptno, COUNT(ename)
COUNT(ename) c l
a
l
emp;
emp;
s
n
a
d
e
t
n
SELECT
deptno,
COUNT(ename)
a
SELECT deptno, COUNT(ename)
u
q
**
n
a
ERROR
ERROR at
at line
line 1:
1: m
ORA-00937:
ORA-00937: not
not an
aesingle-group
single-group group
group function
function
n
lo
o
C
SELECT
SELECT
FROM
FROM
WHERE
WHERE
GROUP
GROUP BY
BY
E
R
HE
W
e
s
u
la
c
a
l
s
a
p
deptno,
deptno, AVG(sal)
AVG(sal)
emp
emp
AVG(sal)
AVG(sal) >> 2000
2000
deptno;
deptno;
li
r
u
o
p
WHERE
WHERE AVG(sal)
AVG(sal) >> 2000
2000
**
z
e
ERROR
ERROR at
at line
line 3:
3: lis
ti es is
ORA-00934:
ORA-00934: group
group'ufunction
function
is not
not allowed
allowed here
here
p
N ou
gr
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 BY
GROUP BY peut avoir dautres attributs en plus
Clause HAVING
La clause HAVING ralise une slection ( la manire du
WHERE) sur les groupes retenir.
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
SELECT
FROM
GROUP BY
HAVING
deptno, max(sal)
emp
deptno
max(sal)>2900;
DEPTNO MAX(SAL)
--------- --------10
5000
20
3000
Clause HAVING
Produire le nombre de Commandes passes par chacun
des Clients qui ont pass deux Commandes ou plus
SELECT
FROM
GROUP BY
HAVING
Table Commande
noCommande dateCommande
1
01/ 06/ 2000
3
02/ 06/ 2000
4
05/ 07/ 2000
2
02/ 06/ 2000
6
09/ 07/ 2000
5
09/ 07/ 2000
7
15/ 07/ 2000
8
15/ 07/ 2000
noClient
10
10
10
20
20
30
40
40
noClient
10
20
30
40
nombreCommandes
3
2
1
2
Commande
dateCommande > '02/06/2000'
noClient
COUNT(*) >= 2
Table Commande
noCommande dateCommande
1
01/ 06/ 2000
3
02/ 06/ 2000
4
05/ 07/ 2000
2
02/ 06/ 2000
6
09/ 07/ 2000
5
09/ 07/ 2000
7
15/ 07/ 2000
8
15/ 07/ 2000
noClient
10
10
10
20
20
30
40
40
noClient
10
20
30
40
nombreCommandes
1
1
1
2
Rsum
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
*
Client
nomClient
*
Client
nomClient DESC, noTlphone ASC
Laboratoire
Exercices 1 q) s) t) v)
08/10/15
218
Requtes imbriques
Permet 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-interrogations
Un critre de recherche employ dans une
clause WHERE peut tre lui-mme le rsultat
dun SELECT
Par exemple, pour connatre le nom des
personnes nes le mme jour que le client
Martin on peut crire :
SELECT nom FROM Client
WHERE naissance = (SELECT naissance FROM
Client WHERE nom = Martin);
Exemple
Sous-Interrogations
SELECT
FROM
WHERE
select_list
table
expr operator
(SELECT
FROM
select_list
table);
Conventions d'Utilisation
des Sous-Interrogations
Placez 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.
Types de Sous-Interrogations
Sous-interrogation mono-ligne
Requte principale
sous-interrogation
ramne
CLERK
Sous-interrogation multi-ligne
Requte principale
ramne
sous-interrogation
CLERK
MANAGER
Sous-interrogation multi-colonne
Requte principale
ramne
sous-interrogation
CLERK
7900
MANAGER 7698
Sous-interrogations
Dans le cas o plusieurs enregistrements
peuvent tre renvoys on peut utiliser :
IN : pour tester une appartenance lensemble de
ce qui est renvoy
Un 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.
Sous-Interrogations Mono-ligne
Ne ramnent qu'une seule ligne
Utilisent des oprateurs de comparaison
mono-ligne
Oprateur
Signification
Egal
>
Suprieur
>=
Suprieur ou gal
<
Infrieur
<=
Infrieur ou gal
<>
Diffrent de
Sous-Interrogations Mono-ligne
Exemple: Afficher les employs occupant le mme poste que
l'employ 7369 et gagnent plus que l'employ 7876
SQL>
2
3
4
5
6
7
8
9
10
SELECT
FROM
WHERE
AND
ename, job
emp
job =
(SELECT
FROM
WHERE
sal >
(SELECT
FROM
WHERE
CLERK
job
emp
empno = 7369)
1100
sal
emp
empno = 7876);
SELECT
FROM
GROUP BY
HAVING
deptno, MIN(sal)
emp
deptno
MIN(sal) >
(SELECT
FROM
WHERE
800
MIN(sal)
emp
deptno = 20);
e
n
ig
l
ti
l
u
m
io
t
a
SQL> SELECT empno, ename
g
o
2 FROM
emp
r
er
3 WHERE sal =
t
in
4
(SELECT
MIN(sal)
s
5
FROM
emp
u
o
6
GROUP BY s deptno);
c
e
av
e
n
ERROR:
ERROR:
l- ig
ORA-01427:
osub-query
ORA-01427: single-row
single-row
sub-query returns
returns more
more than
than
n
one
o
one row
row
m
r
u
no
e
no rows
rows selected
selected
t
a
r
p
O
job
e
n
m
a
r
(SELECT job
FROM
emp
ne
WHERE
ename='SMYTHE');
on
no
no rows
rows selected
selected
La
i
t
a
g
ro
r
e
t
in
s
u
so
e
n
u
c
au
e
l
va
Sous-Interrogation Multi-ligne
Ramne plusieurs lignes
Utilise des oprateurs de comparaison
multi-ligne
Oprateur in
L'exemple prcdent ne fonctionne que si
la requte
imbrique ne renvoie qu'un seul n-uplet ;
Si une sous-requte renvoie un nombre
quelconque
d'lments, il faut utiliser les oprateurs
in et not in ,
traductions SQL de
Exemple d'utilisation de in
Requte : Enseignants de l'tudiant '12'?
Oprateur exists
Permet de tester si le rsultat d'une
requte imbrique est vide ;
Apparat comme lment d'une condition.
Sous-Interrogations Multi-ligne
SQL> SELECT
2 FROM
3 WHERE
4
5
6
Sous-Interrogations Multi-ligne
SQL> SELECT
2 FROM
3 WHERE
4
5
6
Sous-Interrogations Multi-colonne
Sous-interrogation synchronise
Dans les exemples prcdents, la sousinterrogation tait value d'abord, puis le
rsultat pouvait tre utilis pour excuter
l'interrogation principale.
SQL sait galement traiter une sousinterrogation 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
Sous-interrogation synchronise
Sous-interrogation synchronise avec linterrogation
principale : pour chaque ligne de linterrogation
Sous-interrogation synchronise
Sous-interrogation (synchronise) ramenant
au moins 1 ligne : Loprateur EXISTS permet
de construire un prdicat vrai si la sousinterrogation 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 multiples
Sous-interrogation multiples: On peut utiliser
plusieurs sous-interrogations dans un mme SELECT :
par imbrication, ou par combinaison (AND ou OR)
Rsum
Les sous-interrogations sont utiles
lorsqu'une requte fait appel des valeurs
SELECT
select_list
inconnues.
FROM
WHERE
table
expr operator
(SELECT select_list
FROM
table);
Vue: rles
Options de la syntaxe
Options de la syntaxe
Cration exemple
diteur de Paris
Create 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
Exemple:
insert into emp10 (matr, nome, ) values
(1200, 'DUBOIS', );
CHECK OPTION
WITH CHECK OPTION protge contre les
disparitions de tuples de vues:
UPDATE emp10et20
SET dept = 30 WHERE dept = 10' ;
e1...30
e2...20
e5...30
e7...30
Sans
CHECK OPTION
CHECK OPTION
WITH CHECK OPTION protge contre les
disparitions de tuples de vues:
UPDATE emp10et20
SET dept = 30 WHERE dept = 10' ;
e1...10
Abort e2...20
e5...10
e7...30
Avec
CHECK OPTION
INDEX
INDEX
Un index utilise des techniques informatiques
pour rendre trs rapides les accs aux valeurs
d'une colonne
select * from emp where nomE = 'Dupond'
est trs long si la table emp contient des millions
de lignes
Un 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.
Ne pas indexer :
1.les colonnes contenant peu de valeurs
distinctes (index alors peu efficace)
2.les colonnes frquemment modifies
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 1000
INCREMENT BY 10
NOMAXVALUE
NOCYCLE;
Exemple :
create sequence seqdept
increment by 10
start with 10
CURRVAL et NEXTVAL
On ne peut utiliser CURRVAL quaprs
avoir utilis NEXTVAL au moins une fois
dans la session de travail
NEXTVAL modifie immdiatement la valeur
future pour les autres transactions, mme
sil est lanc dans une transaction non
valide
La valeur de CURRVAL ne dpend que des
NEXTVAL lancs dans la mme transaction
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
Exemple:
SELECT * FROM user_sequence WHERE
sequence_name = nom_squence;
select seqdept.currval from dual
283
prennes mme
si panne
ORACLE:PL/SQL
labor par: EL ALAMI Nama
287
Transactions
Dbut de transaction implicite
Dbut de toute commande SQL en dbut de session,
Fin de la transaction prcdente ensuite.
Les principaux ordres SQL de gestion des transactions sont les suivants:
COMMIT COMMIT ;
Termine une transaction par la validation des actions effectues
Annulation des verrous ventuels et publication dfinitive des
modifications effectues aux autres utilisateurs
ROLLBACK ROLLBACK ;
Termine une transaction en annulant toutes les actions
effectues.
Sous-transaction : SAVEPOINT
positionnement dun point intermdiaire dans la transaction
SAVEPOINT <point_repere> ;
annulation des actions depuis ce point de repre (et non depuis
le dbut)
ROLLBACK TO <point_repere> ;
LOCK TABLE ordre de verrouillage explicite
Transaction : exemple
EXEMPLE
--- debut de session
Connect username/pwq
...
Update
Insert into
...
Commit
--- fin transaction 1
ORACLE:PL/SQL
labor par: EL ALAMI Nama
292
Delete from
ROLLBACK to SV1;
---annulation sous-transaction2
COMMIT;--valide tous le reste de la transaction2
--fin transaction 2
ORACLE:PL/SQL
labor par: EL ALAMI Nama
293
Transaction : exemple
295
ORACLE:PL/SQL
labor par: EL ALAMI Nama
298
ORACLE:PL/SQL
labor par: EL ALAMI Nama
299
ORACLE:PL/SQL
labor par: EL ALAMI Nama
300
Transactions
En gnral il existe deux modes
transactionnels:
aprs un SET AUTOCOMMIT ON toute
requte SQL est considre comme une
transaction en elle-mme
aprs un SET AUTOCOMMIT OFF il faut
ajouter explicitement un COMMIT (ou un
ROLLBACK) pour marquer la fin dune
suite de requtes SQL
ORDRE SELECT
Oprateurs et fonctions
Non typs
DECODE
CASE
GREATEST
LEAST
NVL
Types
numriques
ABS
CEIL
FLOOR
MOD
POWER
ROUND
SQRT
COS
Type
alphanumrique
Type
date
SUBSTR
INSTR
UPPER
LOWER
LTRIM
LPAD
SYSDATE
ADD_MONTHS
NEXT_DAY
LAST_DAY
MONTHS_BETWEEN
DECODE
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.
GREATEST
GREATEST(expr1,expr2,)
LEAST(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.
Liste des employs avec leur catgorie (prsident = 1, directeur = 2, autre = 3), en
appelant la colonne Niveau :
Solution 1:
SELECT nome
CASE
WHEN (poste = 'Prsident') THEN 1
WHEN (poste = 'Directeur') THEN 2
ELSE 3
END Niveau
FROM emp;
Solution 2:
SELECT nome,
CASE poste
WHEN 'Prsident' THEN 1
WHEN 'Directeur' THEN 2
ELSE 3
END Niveau
FROM emp;
Fonctions numriques
Certaines des fonctions numriques utilises sous SQL
Fonction
Syntaxe
Rle
ABS
Valeur absolue de n
CEIL
FLOOR
MOD
POWER
Renvoie n1 puissance e
ROUND
ROUND(n[,m]):
SIGN
Renvoie un signe
SQRT
Racine carre de n
COS
Cosinus de n
SIN
Sinus de n
TAN
Tangente de n
LN
Logarithme nprien
EXP
Exponentielle de n
LOG
Logarithme dcimal de n
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
Fonction
Syntaxe
Rle
LENGTH
SUBSTR
INSTR
UPPER
Renvoie en MAJUSCULE
LOWER
Renvoie en minuscule
LPAD
RPAD
LTRIM
LTRIM(VARCHAR2[,set])
RTRIM
LTRIM(VARCHAR2[,set])
REPLACE
ASCII
ASCII(ch CHAR)
INITCAP
CHR
Fonctions alphanumriques
Concatnation
SELECT emp.nom||' travaille a ||dept.nom
as employe FROM emp inner join dept on
emp.n_dept=dept.n_dept and salaire
>=3000;
Fonctions alphanumriques
INSTR:
Position du deuxime 'A' dans les postes :
SELECT INSTR (POSTE, 'A', 1, 2) FROM
EMP
LPAD: SELECT LPAD (NOME, 10, '.')
FROM EMP.
Fonctions alphanumriques
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 EMP
REPLACE(chane, ch1, ch2 ) remplace ch1 par ch2
dans chane.
Fonction TO_CHAR
La 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 apparent
Fonctions DATE
Fonction
Syntaxe
Rle
SYSDATE
SYSDATE
Retourne la date OS
ADD_MONTHS
ADD_MONTHS(date1,n)
MONTHS_BETWEEN
MONTHS_BETWEEN(date1,d
ate2)
LAST_DAY
LAST_DAY(date1)
NEXT_DAY
NEXT_DAY(date1,str)
TO_DATE
TO_DATE(char [, fmt [,
'nlsparams'] ])
ROUND
ROUND(date1[,fmt])
Exemple:
1. 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
afficher
select to_char(sysdate,'DD MONTH YYYY') from
dual;
select to_char(sysdate,'Day DD MONTH YYYY')
from dual
Privilges objet
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 REVOKE
Avec GRANT OPTION: droit au bnficiaire
daccorder ce privilge dautres utilisateurs
Privilge et type dobjet:
Table : ALTER, DELETE, INDEX, INSERT, REFERENCES,
SELECT, UPDATE
Vue : ALTER, DELETE, INSERT, SELECT, UPDATE
Squence : ALTER, SELECT
GRANT et REVOKE
REVOKE annule les privilges des
utilisateurs
Syntaxe :
REVOKE privilge [GRANT OPTION
FOR] ON table1, ..., tablen, vue1, ..., vuen
FROM user1, ..., usern|PUBLIC
Rles prdfinis
Exercices dapplication
1.
2.
3.
4.
5.
6.
Correction
1. GRANT SELECT ON dept TO nom de l'autre user ;
2. GRANT INSERT,UPDATE ON dept TO nom de l'autre
user ;
3. GRANT ALL ON dept TO nom de l'autre user ;
4. CREATE VIEW empvue AS SELECT noemp, nom, titre,
nodept FROM emp ; GRANT SELECT ON empvue TO
nom de l'autre user ;
5. CREATE VIEW emp50 AS SELECT * FROM emp
WHERE nodept = 50 ; GRANT ALL ON emp50 TO nom
de l'autre user ;
6. REVOKE INSERT ON dept FROM nom de l'autre user ;