Le langage SQL

Pr. [Link]

1
 Aspect générale du langage SQL
Le langage SQL ( Structured Query Longage : Langage
d’interrogation structuré) , lié à la structure relationnelle des BD,
est un langage non procédural, il comporte plusieurs commandes
qui se repartissent en trois familles fonctionnellement distinctes:
• Langage de manipulation des données (LMD): sélectionner,
insérer, modifier, ou supprimer des données dans une table.
• Langage de définition des données(LDD): créer des tables dans
une BDR, ainsi d’en modifier ou de supprimer leur structure.
• Langage de contrôle de données(LCD): gérer la sécurité et les
permissions au niveau des utilisateurs d’une BDR.

2
 Tables utilisées dans le Cours

Table Emp Table Dept

Table SALGRADE

3
 L’ordre Select

L’utilisation la plus fréquente de SQL s’effectue dans les requêtes

afin de rechercher les données dans une base de données.

Syntaxe
select [distinct] * | <liste des champs >
From <Liste des tables>
[where <critère de sélection >]
[group by <critères de regroupement>]
[having <conditions de filtrage sur les groupes>]
[order by <liste des champs > asc | desc ] ;

4
 Écriture des Ordres SQL

• Les ordres SQL peuvent être écrits indifféremment en

majuscules et/ou minuscules.
• Les ordres SQL peuvent être écrits sur plusieurs lignes.
• Les mots-clés ne doivent pas être abrégés ni scindés sur deux
lignes différentes.
• Les clauses sont généralement placées sur des lignes
distinctes.

5
 Exemple de SELECT

SELECT *
FROM dept;

DEPTNO DNAME LOC

--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SELECT deptno, loc

FROM dept;
DEPTNO LOC
--------- -------------
10 NEW YORK
20 DALLAS
30 CHICAGO
40 BOSTON

6
 Exemple de SELECT
SELECT *
FROM dept;

DEPTNO DNAME LOC

--------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SELECT
SELECT deptno
deptno SELECT DISTINCT deptno
FROM
FROM emp;
emp; FROM emp;

DEPTNO DEPTNO
--------- ---------
10
10
30
10 20
20 30
...
14 rows selected.

7
 L’Alias de Colonne

• Permet de renommer un en-tête de colonne (champs)

• Suit immédiatement le nom de la colonne ; le mot-clé AS peut
être placé entre le nom et l’alias est optionnel

• Doit obligatoirement être inclus entre guillemets (") s’il

contient des espaces, des caractères spéciaux ou si les

majuscules/minuscules doivent être différenciées

SELECT ename AS name, sal salary

FROM emp;

8
 Le littéral

• Un littéral est un caractère, une expression, ou un nombre inclus

dans la liste SELECT.
• Les valeurs littérales de type date et caractère doivent être
placées entre simples quotes (').

SELECT ename, job, deptno

FROM emp
WHERE job='CLERK';

ENAME JOB DEPTNO

---------- --------- ---------
JAMES CLERK 30
SMITH CLERK 20
ADAMS CLERK 20
MILLER CLERK 10

9
Les opérateurs

10
 Utilisation des Opérateurs
SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 1500;

ENAME SAL
---------- --------- Limite Limite
MARTIN 1250
TURNER 1500 inférieure supérieure
WARD 1250
ADAMS 1100
MILLER 1300

SELECT empno, ename, sal, mgr

FROM emp
WHERE mgr IN (7902, 7566, 7788);

EMPNO ENAME SAL MGR

--------- ---------- --------- ---------
7902 FORD 3000 7566
7369 SMITH 800 7902
7788 SCOTT 3000 7566
7876 ADAMS 1100 7788
11
Utilisation des Opérateurs de Comparaison

SELECT ename
FROM emp
WHERE ename LIKE 'S%';

SELECT ename
FROM emp
WHERE ename LIKE '_A%';

ENAME
----------
JAMES
WARD

12
 Utilisation des Opérateurs logiques

... WHERE ... NOT BETWEEN ... AND ...

... WHERE ... IS NOT IN ...
... WHERE ... NOT LIKE ...
... WHERE ... IS NOT NULL

SELECT ename, job

FROM emp
WHERE job NOT IN ('CLERK','MANAGER','ANALYST');

ENAME JOB
---------- ---------
KING PRESIDENT
MARTIN SALESMAN
ALLEN SALESMAN
TURNER SALESMAN
WARD SALESMAN

13
Utilisation de l’opérateurs de Concaténation

SELECT ename ||' is a '||job AS "Employee Details"

FROM emp;

Employee
Employee Details
Details
-------------------------
-------------------------
KING
KING is
is aa PRESIDENT
PRESIDENT
BLAKE
BLAKE is
is aa MANAGER
MANAGER
CLARK
CLARK is
is aa MANAGER
MANAGER
JONES
JONES is
is aa MANAGER
MANAGER
MARTIN
MARTIN is
is aa SALESMAN
SALESMAN
...
...
14
14 rows
rows selected.
selected.

14
 Exemple de tri des enregistrements

SELECT ename, deptno, sal

FROM emp
ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL

---------- --------- ---------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
FORD 20 3000
...
14 rows selected.

15
 Types de Jointures en SQL

Equijointure Non-équijointure

Jointure externe Autojointure

16
 Exemple d’équijointure

SELECT empo, ename, [Link], [Link] ,[Link]

FROM emp, dept
WHERE [Link]=[Link];

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.

17
 Exemple de non-équijointure

SELECT [Link], [Link], [Link]

FROM emp e, salgrade s
WHERE [Link]
BETWEEN [Link] AND [Link];

ENAME SAL GRADE

---------- --------- ---------
JAMES 950 1
SMITH 800 1
ADAMS 1100 1
...
14 rows selected.

18
 Exemple de Jointures Externes

SELECT [Link], [Link], [Link]

FROM emp e, dept d
WHERE [Link](+) = [Link]
ORDER BY [Link];

ENAME DEPTNO DNAME

---------- --------- -------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
...
40 OPERATIONS
15 rows selected.

19
 Exemple de l’autojointures

SELECT [Link]||' works for '||[Link]

FROM emp worker, emp manager
WHERE [Link] = [Link];

[Link]||'WORKSFOR'||MANAG
[Link]||'WORKSFOR'||MANAG
-------------------------------
-------------------------------
BLAKE
BLAKE works
works for
for KING
KING
CLARK
CLARK works
works for
for KING
KING
JONES
JONES works
works for
for KING
KING
MARTIN
MARTIN works
works for
for BLAKE
BLAKE
...
...
13
13 rows
rows selected.
selected.

20
 Fonction de regroupement de données

Les fonctions de groupe agissent sur des groupes de lignes et donnent

un résultat par groupe

• AVG ([DISTINCT|ALL]n)

• COUNT ({ *|[DISTINCT|ALL]expr})

• MAX ([DISTINCT|ALL]expr)

• MIN ([DISTINCT|ALL]expr)

• SUM ([DISTINCT|ALL]n)

21
Utilisation des fonctions de regroupement

SELECT AVG(sal), MAX(sal), MIN(sal), SUM(sal)

FROM emp
WHERE job LIKE 'SALES%';

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)

-------- --------- --------- ---------
1400 1600 1250 5600

SELECT COUNT(*)
FROM emp
WHERE deptno = 30;

COUNT(*)
---------
6

22
 Utilisation des fonctions de regroupement

SELECT AVG(comm)
FROM emp;

AVG(COMM)
---------
550

SELECT AVG(NVL(comm,0))
FROM emp;

AVG(NVL(COMM,0))
----------------
157.14286

23
 Création de Groupes de Données

EMP DEPTNO SAL

--------- --------

10 2450 2916.6667
10 5000
10 1300 "salaire DEPTNO AVG(SAL)
20 800 moyen pour ------- ---------
20 1100 2175 chaque 10 2916.6667
20 3000 département
20 3000 20 2175
de la table
20 2975 EMP" 30 1566.6667
30 1600
30 2850 1566.6667
30 1250
30 950
30 1500
30 1250
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;

24
Exemple de création de groupes de données

SELECT deptno, job, sum(sal)

FROM emp
GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)

--------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
...
9 rows selected.

25
 Imbrication des Fonctions de Groupe

SELECT max(avg(sal))
FROM emp
GROUP BY deptno;

MAX(AVG(SAL))
-------------
2916.6667

26
 Utilisation de la clause HAVING

La clause HAVING permet restreindre les groupes

• Les lignes sont regroupées.

• La fonction de groupe est appliquée.
• Les groupes qui correspondent à la clause HAVING sont

affichés.

27
 Exemple de l’utilisation de HAVING

SELECT deptno, max(sal)

FROM emp
GROUP BY deptno
HAVING max(sal)>2900;

DEPTNO MAX(SAL)
--------- ---------
10 5000
20 3000

28
 Exemple de l’utilisation de HAVING

SELECT job, SUM(sal) PAYROLL

FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY SUM(sal);

JOB PAYROLL
--------- ---------
ANALYST 6000
MANAGER 8275

29
 Les Sous-Interrogations

Syntaxe
SELECT select_list
FROM tables
WHERE expr operator (SELECT select_list FROM tables… )
…;
• La sous-interrogation (requête interne) est exécutée une fois
avant la requête principale (une sous-interrogation ne doit pas
contenir la clause ORDER BY).
• Le résultat de la sous-interrogation est utilisé par la requête
principale (externe).

30
 Types de Sous-Interrogations

Soous--iinterrogation Sous-interrogation
mono--lligne multi-ligne
Opérateurs mono-ligne Opérateurs multi-ligne

31
Exemple de Sous-Interrogations mono-ligne

SELECT ename, job

FROM emp
WHERE job = CLERK
(SELECT job
FROM emp
WHERE ename = 'ADAMS')
AND sal > 1100
(SELECT sal
FROM emp
WHERE ename = 'ADAMS');

ENAME
ENAME JOB
JOB
----------
---------- ---------
---------
MILLER
MILLER CLERK
CLERK

32
Exemple de Sous-Interrogations mono-ligne

SELECT ename, job, sal

800
FROM emp
WHERE sal =
(SELECT MIN(sal)
FROM emp);

ENAME
ENAME JOB
JOB SAL
SAL
----------
---------- ---------
--------- ---------
---------
SMITH
SMITH CLERK
CLERK 800
800

SELECT deptno, MIN(sal)

FROM emp
GROUP BY deptno
HAVING MIN(sal) > 800
(SELECT MIN(sal)
FROM emp
WHERE deptno = 20);

33
 Exemple de Sous-Interrogations multi-ligne

SELECT empno, ename, job 1300

FROM emp 1100
800
WHERE sal < ANY 950
(SELECT sal
FROM emp
WHERE job = 'CLERK‘)
AND job <> ''CLERK';

EMPNO
EMPNO ENAME
ENAME JOB
JOB
---------
--------- ----------
---------- ---------
---------
7654
7654 MARTIN
MARTIN SALESMAN
SALESMAN
7521
7521 WARD
WARD SALESMAN
SALESMAN

34
Exemple de Sous-Interrogations multi-ligne

SELECT empno, ename, job

1566.6667
FROM emp 2175

WHERE sal > ALL 2916.6667

(SELECT avg(sal)
FROM emp
GROUP BY deptno)

EMPNO
EMPNO ENAME
ENAME JOB
JOB
---------
--------- ----------
---------- ---------
---------
7839
7839 KING
KING PRESIDENT
PRESIDENT
7566
7566 JONES
JONES MANAGER
MANAGER
7902
7902 FORD
FORD ANALYST
ANALYST
7788
7788 SCOTT
SCOTT ANALYST
ANALYST

35
 Les Sous-Interrogations Synchronisées

Syntaxe: SELECT outer1, outer2, ...

FROM table1 alias1
WHERE outer1 operator (SELECT inner1
FROM table2 alias2
WHERE alias1.outer2 = alias2.inner1);
La sous-interrogation est exécutée pour chaque enregistrement de la
requête principale.

Nouvelle ligne candidate (requête externe)

Exécuter la requête interne utilisant une valeur de la ligne

candidate

Utiliser la valeur(s) de la requête interne qualifiant la ligne

candidate

36
Exemple de Sous-Interrogations Synchronisées

Recherchez tous les employés dont le salaire est supérieur au

salaire moyen de leur département
SELECT empno, sal, deptno Chaque fois que la requête
FROM emp outer externe est traitée,
WHERE sal > (SELECT AVG(sal) la requête interne
FROM emp inner est exécutée.
WHERE [Link]= [Link]);

EMPNO
EMPNO SAL
SAL DEPTNO
DEPTNO
--------
-------- ---------
--------- ---------
--------- Opérateur [Link]
7839
7839 5000
5000 10
10 ou muti-ligne
7698
7698 2850
2850 30
30
7566
7566 2975
2975 20
20
...
...
66 rows
rows selected.
selected.

37
 Les Sous-Interrogations Synchronisées et l'Opérateur EXISTS

• Pour chaque enregistrement de la requête principale, une

recherche d’enregistrement est effectuée dans la sous-
interrogation.
• La recherche dans la sous-interrogation est interrompue dès une
ligne a été trouvée, la condition de la requête principale est
vraie.
• La condition de la requête principale est fausse si aucune ligne
n’a été trouvée dans la sous-interrogation.

38
Exemple de l’utilisation de l’opérateur EXISTS

Recherchez les employés ayant au moins une personne sous leur

responsabilité.
SELECT empno, ename, job, deptno
FROM emp outer
WHERE EXISTS (SELECT empno
FROM emp inner
WHERE [Link] = [Link]);

EMPNO ENAME JOB DEPTNO

--------- ---------- --------- ---------
7839 KING PRESIDENT 10
7698 BLAKE MANAGER 30
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
...
6 rows selected.

39
 Les Opérateurs Ensemblistes

• Intersect
• Union / Union All
• Minus
Table Emp Table EMPHISTORY

40
 Exemple de l'Opérateur UNION

Affichez le nom, le poste et le département de tous les

employés
SELECT ename, job, deptno
FROM emp
UNION
SELECT name, title, deptid
FROM emp_history;

ENAME JOB DEPTNO

---------- --------- ---------
ADAMS CLERK 30
ALLEN SALESMAN 30
ALLEN SALESMAN 20
BALFORD CLERK 20
BLAKE MANAGER 30
...
20 rows selected.

41
 Exemple de l'Opérateur INTERSECT

Affichez les différents noms, numéros et postes des

employés présents dans les tables EMP et EMPHISTORY.

SELECT ename, empno, job

FROM emp
INTERSECT
SELECT name, empid, title
FROM emp_history;

ENAME
ENAME EMPNO
EMPNO JOB
JOB
----------
---------- ---------
--------- ---------
---------
ALLEN
ALLEN 7499
7499 SALESMAN
SALESMAN
CLARK
CLARK 7782
7782 MANAGER
MANAGER
SCOTT
SCOTT 7788
7788 ANALYST
ANALYST

42
 Exemple de l'Opérateur MINUS

Affichez le nom,le numéro et le poste de tous les employés

ayant quitté la société.
SELECT name, empid, title
FROM emp_history
MINUS
SELECT ename, empno, job
FROM emp;

NAME
NAME EMPID
EMPID TITLE
TITLE
----------
---------- ---------
--------- ---------
---------
BALFORD
BALFORD 6235 CLERK
6235 CLERK
BRIGGS
BRIGGS 7225
7225 PAY
PAY CLERK
CLERK
JEWELL
JEWELL 7001 ANALYST
7001 ANALYST
SPENCER
SPENCER 6087
6087 OPERATOR
OPERATOR
...
...
66 rows
rows selected.
selected.

43
 Ajout d‘enregistrement dans une Table

L'ordre INSERT permet d'ajouter un nouveau enregistrement

dans une table.

Syntaxe 1:
INSERT
INSERTINTO
INTO Nomtable
Nomtable [(champs
[(champs [,[, champs
champs ...])]
...])]
VALUES (valeur
VALUES (valeur [,[, valeur...]
valeur...] ););

• Indiquez les valeurs dans l'ordre par défaut des colonnes dans
la table.
• Placez les valeurs de type caractère et date entre simples
quotes.

44
 Ajout d‘enregistrement dans une Table

INSERT INTO dept (deptno, dname, loc)

VALUES (50, 'FINANCES', 'PARIS');

INSERT
INSERTINTO
INTO emp
emp (empno,
(empno, ename,
ename, job,
job,
mgr,
mgr, hiredate,
hiredate, sal,
sal, deptno)
deptno)
VALUES
VALUES (8000,
(8000, ‘SAAD',
‘SAAD', 'ANALYST',
'ANALYST',
7839,
7839, SYSDATE,
SYSDATE, 4000,
4000, 20);
20);

45
 Ajout d‘enregistrement dans une Table

Syntaxe 2:
INSERT
INSERTINTO
INTO Nomtable
Nomtable[(champs
[(champs[,[,champs
champs...])]
...])]
(select …);
(select …);

Les champs de la clause INSERT doit correspondre à ceux de la

sous-interrogation
Exemple:
INSERT
INSERT INTO
INTO SALSES
SALSES (mat,
(mat, nom,
nom, sal,
sal, hiredate,
hiredate, deptno)
deptno)
SELECT
SELECT empno,
empno, ename,
ename, hiredate,
hiredate, sal+
sal+ comm,
comm, deptno
deptno
FROM
FROM emp
emp
WHERE
WHERE job
job like
like 'SALSES%';
'SALSES%';

46
 Modification des données d’une Table

L'ordre UPDATE permet de modifier les enregistrements d’une

table.
Syntaxe :
UPDATE
UPDATE Nomtable
Nomtable
SET Champs = valeur [, champs = valeur]
SET Champs = valeur [, champs = valeur]
[WHERE condition];
[WHERE condition];
• La clause WHERE permet de modifier une ou plusieurs
lignes spécifiques; en cas d’absence de cette clause toutes les
lignes seront modifiées.
• Chaque opération de modification doit respecter les
contraintes d’intégrité référentielle de la base de données.

47
 Modification des données d’une Table

UPDATE
UPDATE emp
emp
SET
SET com =0
com
WHERE
WHERE com is
com is NULL;
NULL;

UPDATE
UPDATE emp
emp
SET
SET mgr
mgr == (SELECT
(SELECT empno
empno
FROM
FROM emp
emp
WHERE
WHERE (deptno
(deptno == 30)
30)AND
AND (job
(job like
like 'MANAGER')
'MANAGER') )) ,,
job
job ='SALSESMEN'
='SALSESMEN'
WHERE
WHERE deptno
deptno == 30;
30;

48
Suppression des enregistrements d’une Table

L'ordre DELETE permet de supprimer des enregistrements a

partir d’une table.
Syntaxe :
DELETE
DELETE [FROM]
[FROM] Nomtable
Nomtable
[WHERE condition];
[WHERE condition];

• La clause WHERE permet de supprimer une ou plusieurs

lignes spécifiques en l’absence de cette clause, toutes les
lignes sont supprimées.
• Chaque opération de modification doit respecter les
contraintes d’intégrité référentielle de la base de données.

49
Suppression des enregistrements d’une Table

DELETE
DELETE dept
dept
WHERE dname
WHERE dname == 'FINANCES';
'FINANCES';

DELETE
DELETE FROM
FROM emp
emp
WHERE
WHERE deptno
deptno==
(SELECT
(SELECT deptno
deptno
FROM
FROM dept
dept
WHERE
WHERE dname
dname ='SALES');
='SALES');

50
 Langage de définition des Données

Un ordre du LDD permet de créer , de supprimer et de modifier la

structure d’une base de données relationnelle; les objets que le
LDD peut gérer sont:

• Table
• Vue
• Séquence
• Index
• Synonyme

51
 Création de Tables

Syntaxe :
CREATE TABLE nomtable
(champs typechamps [DEFAULT valeur], ...);
CREATE TABLE nomtable
[(champs1, champs2...)] as sousinterrogation;

Les identificateurs utilisés doivent respecter les règles suivantes:

• Commencer par une lettre et contenir que les caractères A à
Z, 0 à 9, _, $, et # (30 caractères maximum).
• Être différent des autres objets appartenant au même
utilisateur et des mots réservé à Oracle

52
 Création de Tables

Types de données Description

VARCHAR2(taille) Données caractères de longueur variable
(2000 caractères maximum)
CHAR(taille) Données caractères de longueur fixe
(255 caractères maximum)
NUMBER(taille,décimale) Numérique de longueur variable
DATE Valeurs de date et d'heure
LONG Données caractères de longueur variable,
jusqu'à 2 giga-octets
LONG RAW Binaire(image)

53
 Création de Tables

CREATE
CREATE TABLE
TABLE dept
dept
(( deptno
deptno NUMBER(2),
NUMBER(2),
dname
dname VARCHAR2(15),
VARCHAR2(15),
loc
loc VARCHAR2(12));
VARCHAR2(12));

CREATE
CREATE TABLE
TABLE Empclerk
Empclerk
AS
AS SELECT
SELECT empno
empno mat,
mat, ename,
ename, nom,
nom, sal
sal ++ comm
comm as
as salaire,
salaire,
hiredate,
hiredate, deptno
deptno
FROM
FROM emp
emp
WHERE
WHERE job
job like
like 'clerk');
'clerk');

54
 Modification de la structure d’une Tables

L'ordre ALTER TABLE permet d’ajouter de nouveau champs ou

de modifier des champs existants.

Syntaxe :
ALTER TABLE Nomtable
ADD (champs typechamps [DEFAULT valeur]
[, champs typechamps]...);
ALTER TABLE Nomtable
MODIFY (champs typechamps [DEFAULT valeur]
[, champs typechamps]...);
ALTERTABLE Nomtable
DROP COLUMN champs ;
ALTER TABLE table
RENAME COLUMN ancien_nom TO nouveau_nom

55
 Gestion des tables

Supprimer une Table

DROP
DROPTABLE
TABLE Nomtable;
Nomtable;

Renommer une Table

RENAME
RENAMEAncienNomTable
AncienNomTable TO
TO NouveauNomTable;
NouveauNomTable;

Vider une Table

TRUNCATE
TRUNCATE TABLE
TABLE NomTable;
NomTable;

56
 Les Contraintes d’intégrités

Les types de contraintes reconnues dans SQL sont:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

57
 Les Contraintes d’intégrités

Syntaxe :
CREATE
CREATE TABLE
TABLE [Schema.]NomTable
[Schema.]NomTable
(Champs
(Champs TypeChamps
TypeChamps [DEFAULT
[DEFAULTValeur]
Valeur]
[[CONSTRAINT
[[CONSTRAINT NomContrainte]
NomContrainte] TypeContrainte],
TypeContrainte],
…
…,,
[[CONSTRAINT
[[CONSTRAINT NomContrainte]
NomContrainte] TypeContrainte(Champs,
TypeContrainte(Champs, ...),]
...),]
… );
…);

ALTER
ALTER TABLE
TABLE NomTable
NomTable
ADD
ADD [CONSTRAINT
[CONSTRAINT NomContrainte]
NomContrainte] TypeContrainte(Champs);
TypeContrainte(Champs);

58
 Les Contraintes d’intégrités

CREATE
CREATE TABLE
TABLE Employes(
Employes(
empno
empno NUMBER(4)
NUMBER(4) PRIMARY
PRIMARY KEY,
KEY, ename
ename VARCHAR2(15)
VARCHAR2(15) NOT
NOT NULL,
NULL,
job
job VARCHAR2(10),
VARCHAR2(10), mgr
mgr NUMBER(4),
NUMBER(4), hiredate
hiredate DATE,
DATE, sal
sal NUMBER(8,2),
NUMBER(8,2),
comm
comm NUMBER(8,2),
NUMBER(8,2), deptno
deptno NUMBER(2)
NUMBER(2) NOT
NOT NULL
NULL ,,
CONSTRAINT
CONSTRAINT un_dept
un_dept UNIQUE
UNIQUE (ename)
(ename) ););

ALTER
ALTER TABLE
TABLE Employes
Employes
ADD CONSTRAINT
ADD CONSTRAINT Emp_Dept_Fk
Emp_Dept_Fk FOREIGN
FOREIGN KEY
KEY (deptno)
(deptno)
REFERENCES
REFERENCES dept
dept (deptno)
(deptno) ON
ON DELETE
DELETE CASCADE
CASCADE;;

ALTER
ALTER TABLE
TABLE Employes
Employes
ADD
ADD CONSTRAINT
CONSTRAINT CONSTRAINT
CONSTRAINT CHECK
CHECK (Comm
(Comm >> 0);
0);

59
 Gestion des contraintes

Supprimer une contrainte

ALTER
ALTER TABLE
TABLE NomTable
NomTable
DROP
DROPCONSTRAINT
CONSTRAINT NomContrainte
NomContrainte [CASCADE];
[CASCADE];

Désactiver une Contraintes

ALTER
ALTER TABLE
TABLE NomTable
NomTable
DISABLE
DISABLE CONSTRAINT
CONSTRAINT NomContrainte
NomContrainte [CASCADE];
[CASCADE];

Activer une Contraintes

ALTER
ALTER TABLE
TABLE NomTable
NomTable
ENABLE CONSTRAINT NomContrainte
ENABLE CONSTRAINT NomContrainte[CASCADE];
[CASCADE];

60
 Notion de vue

 Une vue est une table virtuelle basée sur une ou plusieurs table

(ou sur d’autres vues). C’est une fenêtre par laquelle il est

possible de visualiser ou de modifier des données venant de ces

tables (appelés tables de base).

 Les utilisateurs consultent la base, ou modifier la base (avec

certaines restrictions) à travers les vues (Limitation d'accès )

61
 Création d'une Vue
Syntaxe
CREATE
CREATE VIEW
VIEW NomVue [(alias[, alias]...)]
NomVue [(alias[, alias]...)]
AS
AS SousInterogation
SousInterogation
[WITH
[WITH CHECK
CHECK OPTION]
OPTION]
[WITH
[WITH READ
READ ONLY]
ONLY]

La SousInterogation(ordre select) ne doit pas comporter la

clause ORDER BY.
« WITH CHECK OPTION » permet de garantir que les
ordres LMD reste dans le domaine de la vue.
« WITH READ ONLY » assure qu'aucune opération LMD ne
sera exécutée dans la vue.

62
 Exemple de création d'une Vue

CREATE
CREATE VIEW
VIEW manager
manager
(matricule,
(matricule, nom,
nom, fonction,
fonction, dateembouche,
dateembouche, salaire,
salaire, Departement
Departement ))
AS
AS SELECT
SELECT empno,
empno, ename,
ename, job,hiredate,
job,hiredate, sal
sal ++ nvl(comm,0),
nvl(comm,0), dname
dname
FROM
FROM emp, emp, dept
dept
WHERE
WHERE (([Link]
(([Link] == [Link])
[Link]) and
and
empno
empno in
in (select
(select mgr
mgr from
from emp
emp where
where mgr
mgr isis not
not NULL))
NULL))
WITH
WITH READ
READ ONLYONLY

Select
Select **
from
from manager
manager ;;

63
 Suppression d’une vue

L'ordre DROP VIEW permet supprimer une vue (les tables de

base correspondantes ne sont pas supprimées).

Syntaxe

DROP
DROP VIEW
VIEW NomVue;
NomVue;

64
 Contrôle des accès utilisateur

Dans un environnement multi-utilisateur, l'accès et l'utilisation

d’une base de données doit être sécurisé; une telle sécurité peut
être classée en deux catégories :
• La sécurité du système couvre l'accès à la base de données et
son utilisation au niveau du système (nom de l'utilisateur et
mot de passe, espace disque alloué aux utilisateurs et
opérations système autorisées par l'utilisateur).
• La sécurité de la base de données couvre l'accès aux objets
de la base de données et leur utilisation, ainsi que les actions
exécutées sur ces objets par les utilisateurs.

65
 Privilèges

Un privilège donne le droit d'exécuter certains opérations sur la

base de données(ordres SQL):

 Privilèges système : autorisent l'accès à la base de données

 Privilèges objet : autorisent la manipulation du contenu des

objets de la base de données

66
 Compte utilisateur

L'ordre CREATE USER permet à l'administrateur de base de

données de créer de nouveau utilisateurs.
Syntaxe
CREATE
CREATE USER
USER login
login
IDENTIFIED
IDENTIFIED BY
BY motpasse;
motpasse;

L'ordre ALTER USER permet de modifier le mot de passe d’un

utilisateur.
Syntaxe
ALTER
ALTER USER
USER login
login
IDENTIFIED
IDENTIFIED BY
BY nouveau_motpasse;
nouveau_motpasse;
67
 Création et Attribution d'un Rôle

 Un rôle est un groupe d’utilisateur qu’ont les même privilèges.

Un utilisateur peut avoir accès à plusieurs rôles, et le même rôle
peut être attribué à plusieurs utilisateurs.
 L'ordre CREATE ROLE permet à l'administrateur de base de
données de créer de nouveau utilisateurs.
 L'ordre GRANT permet d’attribue un rôle à des utilisateurs

Syntaxe

CREATE
CREATE ROLE
ROLE NomRole;
NomRole;
GRANT
GRANT NomRole
NomRole to
to Login1[,
Login1[, Login2,
Login2, …];
…];

68
 Retrait d'un Rôle

L'ordre REVOKE permet de retirer un rôle à un utilisateur

Syntaxe
REVOKE
REVOKE RoleX
RoleX
FROM
FROM utilisateur1[,
utilisateur1[, utilisateur2...];
utilisateur2...];

69
 Privilèges systèmes de l’utilisateur

L'administrateur de base de données peut accorder certaines

privilèges à un utilisateur.

Exemple de privilèges

Privilège Système Opérations autorisées

CREATE SESSION Connexion à la base de données

CREATE TABLE Création de tables dans le schéma de l’utilisateur

CREATE VIEW Création de tables dans le schéma de l’utilisateur

Des rôles sont préfinis par Oracle: Exemple CONNECT permet

à un utilisateur de se connecter et de créer des tables.

70
 Octroi de privilèges système

L'ordre GRANT permet à l'administrateur d’accorder à un

utilisateur certains privilèges systèmes.

Syntaxe
GRANT
GRANT Privilege1
Privilege1 [,[, Privilege2,
Privilege2, …]
…]
TO
TO {utilisateur1|Role1[,utilisateur2,…];
{utilisateur1|Role1[,utilisateur2,…];

71
 Retrait des privilèges systèmes

L’administrateur peut retirer à un utilisateur ou à un rôle certains

privilèges systèmes qui il leurs a accordés.

Syntaxe
REVOKE
REVOKE {privilege1
{privilege1 [,[, privilege2...]}
privilege2...]}
FROM
FROM {utilisateur1[,
{utilisateur1[, utilisateur2...]|role}
utilisateur2...]|role}

72
 Privilèges objets

Le propriétaire d’un objet d’une base de données peut accorder

certaines privilèges autorisant la manipulation du contenu de cet

objet.

Exemple de privilèges

SELECT, ALTER , DELETE , INSERT, REFERENCES,

UPDATE

73
 Octroi de privilèges objets

L'ordre GRANT permet d’accorder à un utilisateur certains

privilèges objets.

Syntaxe
GRANT
GRANT {privilege_objet [(champs)]…|ALL}
{privilege_objet[(champs)]…|ALL}
ON
ON nom_objet
nom_objet
TO
TO {utilisateur|nom_role|PUBLIC}
{utilisateur|nom_role|PUBLIC}
[WITH
[WITH GRANT
GRANT OPTION];
OPTION];

WITH GRANT OPTION autorise le bénéficiaire à accorder les

privilèges objet à d'autres utilisateurs et à des rôles

74
 Retrait des privilèges objets

L'ordre REVOKE permet de retirer à un utilisateur ou à un rôle

certains privilèges objets qui leurs étaient accordés.
Syntaxe

REVOKE
REVOKE {privilege1
{privilege1 [,[, privilege2...]|ALL}
privilege2...]|ALL}
ON
ON Objet
Objet
FROM
FROM {utilisateur1[,
{utilisateur1[, utilisateur2...]|role|PUBLIC}
utilisateur2...]|role|PUBLIC}

Les privilèges accordés avec WITH GRANT OPTION seront

automatiquement retirés.

75
 Transactions de Base de Données
• Une transaction se compose des éléments suivants :
 Ensemble d'ordres du LMD effectuant une opération cohérente des
données
 Un ordre du LDD ou LCD
• Une transaction commence à l'exécution d’un ordre SQL et se termine
par l'un des événements suivants :
 COMMIT ou ROLLBACK
 Exécution d'un ordre LDD ou LCD (validation automatique)
 Fin de session utilisateur
 Panne du système

76
 Transactions de Base de Données

• Avant toute opération de sauvegarde ou de restauration (COMMIT ou

ROLLBACK) :
 Les résultats des ordres du LMD exécutés par l'utilisateur courant ne
peuvent pas être affichés par d'autres utilisateurs.
 Les lignes concernées sont verrouillées. Aucun autre utilisateur ne
peut les modifier
•Aprés toute opération de sauvegarde ou de retoration (COMMIT
ou ROLLBACK) :
 Les modifications des données dans la base sont définitives (perte de
l'état précédent de la base)
 Tous les utilisateurs peuvent voir le résultat des modifications.
 Les lignes verrouillées sont libérées et peuvent de nouveau être
manipulées par d'autres utilisateurs

77
 Transactions de Base de Données

COMMIT;
COMMIT;
Commit
Commit complete.
complete.

ROLLBACK;
ROLLBACK;
Rollback
Rollback complete.
complete.

UPDATE
UPDATE ...
SAVEPOINT
SAVEPOINT point1;
Savepoint
Savepoint created.
created.
DELETE
DELETE … …
ROLLBACK
ROLLBACK TO
TO point1;
point1;
Rollback
Rollback complete.
complete.

78