cours sql ldd
TRANSCRIPT
-
8/18/2019 Cours SQL Ldd
1/87
SAGARSamya
SGBD : Le langage SQL(LDD : Langage de Définition des Données)
SAGARSamya
-
8/18/2019 Cours SQL Ldd
2/87
IntroductionIntroduction
2 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
3/87
Présentation de SQL
SQL = Structured Query LanguageSQL = Structured Query Language
Langage de gestion de bases de données relationnelles pour;
Définir les données (LDD ; Langage de Définition des Données gérées)
ordres CREATE, ALTER,DROP (Création des objets).Manipuler les données (LMD ; Langage de Manipulation des Données)Manipuler les données (LMD ; Langage de Manipulation des Données)
ordres UPDATE, INSERT, DELETE (mise à jour des données);
ordre SELECT (Interrogation des données).
aux Données)ordres GRANT, REVOKE (gestion des droit);ordres GRANT, REVOKE (gestion des droit);
ordres COMMIT, ROLLBACK (gestion des transactions).
3 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
4/87
Norme SQL92 (ou SQL2)
International
Organisation for Standardization)
Presque complètement implémentée par les principauxPresque complètement implémentée par les principaux
SGBD : Oracle, DB2, Informix, MySQL, PostsgreSQL,
La norme SQL99 (ou SQL3) est déjà là depuis longtemps
4 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
5/87
Utilisation de SQL
Interactif (SQL*Plus)
Intégré dans un programme (PLSQL)
SAGAR Samya5
-
8/18/2019 Cours SQL Ldd
6/87
Oracle
SGBDR (Relationnel) qui utilise le langage SQLSGBDR (Relationnel) qui utilise le langage SQL
Langage procédural PL/SQL (dit L 4G) propriétaire
Nombreux programmes utilitairesNombreux programmes utilitaires
SQL*PLUS : SQL interactif, avec quelques ajouts
SQL*FORMS : saisir/voir des données avec des formulaires
SQL*REPORTWRITER : rapports imprimés
SAGAR Samya6
.. .
-
8/18/2019 Cours SQL Ldd
7/87
Oracle
Pour chaque utilisateur reconnu de la base dePour chaque utilisateur reconnu de la base dedonnées, un schéma , aussi appelé compte , est crée
Le nom du schéma est le même que celui deLe nom du schéma est le même que celui de
vues, séquences, synonymes, index, clusters,
SAGAR Samya7
vues, séquences, synonymes, index, clusters,
procédures, fonctions, packages et liens)
-
8/18/2019 Cours SQL Ldd
8/87
Oracle
Par défaut, chaque utilisateur a accès à toutes lesPar défaut, chaque utilisateur a accès à toutes lesobjets de même nom
La protection des objets de la base est décentralisée;
tout objet a un utilisateur créateur, qui possède toustout objet a un utilisateur créateur, qui possède tous
les droits (consultation, modification et suppression)
sur cet objet
SAGAR Samya8
à moins que le créateur ne le leur en accorde
explicitement
-
8/18/2019 Cours SQL Ldd
9/87
Oracle_Exemple
Samya crée la table Client . La table Client estdans le schéma . Pour désigner sa table
Samya crée la table Client . La table Client estdans le schéma Samya . Pour désigner sa table ClientSamya utilise le mot ClientSamya.Client mais elle peut le faire si elle veut
Mohamed ne peut accéder à la table Client deSamya
Samya donne le droit de consultation sur sa tableSamya donne le droit de consultation sur sa tableClient Mohamed .
Mohamed peut désormais accéder à la table Client
de Samya (en consultation). Pour désigner la table Client deSamya Mohamed doit employer le mot
Samya.ClientMohamed Client .
SAGAR Samya9
Mohamed Client .
Mohamed désignera sa table Client par Client (ouMohamed.Client ) et celle de Samya par Samya.Client
-
8/18/2019 Cours SQL Ldd
10/87
Objets manipulés par SQL
IdentificateursIdentificateurs
Identificateurs pour les objets manipulés
30 caractères au plus
lettres, chiffres, _, $ ou # (commence par une lettre)
pas un mot clefpas un mot clef
Quelques mots clefs : ASSERT, ASSIGN, AUDIT,
COMMENT, DATE, DECIMAL, DEFINITION,
FILE, FORMAT, INDEX, LIST, MODE, OPTION,
PARTITION, PRIVILEGES, PUBLIC, SELECT,
SAGAR Samya10
PARTITION, PRIVILEGES, PUBLIC, SELECT,
-
8/18/2019 Cours SQL Ldd
11/87
Objets manipulés par SQL
TablesTables
Relations stockées sous forme de tables composées de
lignes et de colonnes
schéma (pour réunir tous les objets liés à un mêmeschéma (pour réunir tous les objets liés à un même
thème)
Sous Oracle, le schéma est remplacé par le nom de
SAGAR Samya11
-
8/18/2019 Cours SQL Ldd
12/87
Objets manipulés par SQL
TablesTables
Exemple de table ; DEPT
dept nomD Lieu
10 Finances Gabès10 Finances Gabès
20 Recherche Sousse
30 Ventes Kairouan
SAGAR Samya12
-
8/18/2019 Cours SQL Ldd
13/87
Objets manipulés par SQL
TablesTables
Exemple de table ; EMP
matr nomE sal com sup dept
1200 Med 2500 300 2200 101200 Med 2500 300 2200 10
2200 Mahmoud 3000 500 10
1780 Samya 2500 2200 20
SAGAR Samya13
-
8/18/2019 Cours SQL Ldd
14/87
Objets manipulés par SQL
ColonnesColonnes
2 colonnes dans 2 tables différentes peuvent avoir le
même nommême nom
table à laquelle elle appartient (obligatoire en cas
DEPT.dept,
SAGAR.DEPT.dept
SAGAR Samya14
SAGAR.DEPT.dept
-
8/18/2019 Cours SQL Ldd
15/87
Types de donnéesTypes de données
15 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
16/87
Types de données SQL2
Types numériquesTypes numériquesTypes pour les chaînes de caractères
telles que les images et les sonstelles que les images et les sons
type
SAGAR Samya16
-
8/18/2019 Cours SQL Ldd
17/87
Types numérique (1)
Nombres entiers :Nombres entiers :SMALLINT sur 2 octets
INTEGER sur 4 octets
À virgule flottante :
REAL(ou )DOUBLE PRECISION (ou FLOAT )
Constantes : 253.8, -10, 1.3E -5
SAGAR Samya17
-
8/18/2019 Cours SQL Ldd
18/87
Types numériques (2)
Nombres décimaux à nombre fixe de décimales :Nombres décimaux à nombre fixe de décimales :DECIMAL(nbChiffres, nbDécimales)
NUMERIC(nbChiffres, nbDécimales)
Précision imposée
pour les calculs
NUMERIC(8, 2) ou DECIMAL(8, 2) :
6 chiffres avant la virgule et 2 après
Constantes : 253.8, -10
SAGAR Samya18
-
8/18/2019 Cours SQL Ldd
19/87
Types chaînes de caractères
CHAR( longueur)CHAR( longueur)chaînes de caractères avec un nombre fixe de caractères
VARCHAR(longueurMaximum)
chaînes de caractères avec un nombre variable de caractères
(mais un nombre maximum de caractères)CHAR(5) : chaîne de 5 caractèresCHAR(5) : chaîne de 5 caractères
VARCHAR(20) : chaîne de 20 caractères au plus
Constante : 'Comptabilité', 'Aujourd''hui'
SAGAR Samya19
' doublé
-
8/18/2019 Cours SQL Ldd
20/87
Types temporels
DATE pour les datesDATE pour les datesTIME pour les heures, minutes et secondes
TIMESTAMP pour un moment précis :
la microseconde (un millionième de seconde)permet d'indiquer un intervalle de temps.INTERVAL permet d'indiquer un intervalle de temps.
SAGAR Samya20
-
8/18/2019 Cours SQL Ldd
21/87
Type binaires
Ce type permet d'enregistrer des données telles que lesCe type permet d'enregistrer des données telles que lesimages et les sons, de très grande taille et avec divers
formats.
BITExemples :Exemples :
BIT(1)
BIT(4)
Note :Pas supporté par Oracle
SAGAR Samya21
Pas supporté par Oracle
-
8/18/2019 Cours SQL Ldd
22/87
Oracle accepte les types numériques SQL2 mais il lesOracle accepte les types numériques SQL2 mais il lestraduit dans ses propres types
NUMBER
nombre à virgule flottante avec jusqu'à 38 chiffres significatifs
NUMBER(nbChiffres [, nb -décimales])nombre décimal d'au plus nbC hiffres chiffres dont nb décima lesnombre décimal d'au plus nbC hiffres chiffres dont nb décima les
après la virgule
SAGAR Samya22
-
8/18/2019 Cours SQL Ldd
23/87
CHAR , comme la norme SQL2
VARCHAR est accepté mais Oracle conseille d'utiliser
VARCHAR2 qui a les mêmes propriétésVARCHAR2 qui a les mêmes propriétésNote : (la taille maximum est de 2000 caractères)
SAGAR Samya23
-
8/18/2019 Cours SQL Ldd
24/87
Le type DATE remplace les types DATE et TIME deLe type DATE remplace les types DATE et TIME deSQL2
DATE correspond à une date avec une précision jusqu'à la
seconde
Note : Ne pas oublier de donner 4 chiffres pour l'année,
sinon la date risque d'être mal interprétée par Oracle
le format dépend de lalocalisation de la base
SAGAR Samya24
-
8/18/2019 Cours SQL Ldd
25/87
Interrogations et Mise à jour
des données
(LMD)(LMD)
25 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
26/87
Remarque
Voir cours Base de données ; semestre 1 _ 2INFO
SAGAR Samya26
-
8/18/2019 Cours SQL Ldd
27/87
27 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
28/87
Syntaxe simplfiée:
Syntaxe simplfiée:
CREATE TABLE t a b l e (
colonne1 type1,
colonne2 type2,
. . .
. . .)
Exemple :
create table article (
ref char(5) not null ,
nom varchar (20), Option not null
SAGAR Samya28
prix numeric (9,2),
dateMAJ date);
Option not nullsi la colonne
doit obligatoirementêtre renseignée
-
8/18/2019 Cours SQL Ldd
29/87
Valeur par défaut
On peut ajouter après la description d'une colonne l'optionNOT NULL qui interdira que cette colonne ne contienne pasOn peut ajouter après la description d'une colonne l'optionNOT NULL qui interdira que cette colonne ne contienne pasla valeur NULL.
On peut donner une valeur par défaut pour une colonne :
On peut aussi donner une fonction comme valeur par défaut ;par exemple,
create table dept (
numDept integer not null,
nomDept varchar (20),
ville varchar (30) default
SAGAR Samya29
On peut aussi donner une fonction comme valeur par défaut ;par exemple, default sysdate
-
8/18/2019 Cours SQL Ldd
30/87
DESCRIBE
Cette (pas SQL) afficheCette (pas SQL) affiche
SQL > describe article;Name Null ? Type
REF Not null CHAR(5)NOM VARCHAR(20)NOM VARCHAR(20)PRIX NUMBER(9,2)
DATEMAJ DATE
Même si le typedonné à la
SAGAR Samya30
donné à la
création estDECIMAL
-
8/18/2019 Cours SQL Ldd
31/87
Une commande est annulée par le SGBD siUne commande est annulée par le SGBD si
son exécution viole une des contraintes
SAGAR Samya31
-
8/18/2019 Cours SQL Ldd
32/87
Les contraintes possibles sont :Les contraintes possibles sont :PRIMARY KEY : clé primaire
: clé étrangère
UNIQUE : 2 lignes ne peuvent avoir la même valeur pour les colonnes
spécifiées
CHECK : contrainte de domaine, ou autre ; porte sur une seule ligne2 types de contraintes :2 types de contraintes :
contrainte de colonne
concerne une seule colonne
elle suit la définition de la colonne dans un ordre CREATE TABLE
contrainte de tableconcerne une ou plusieurs colonnes
SAGAR Samya32
concerne une ou plusieurs colonnes
elles se place au même niveau que les définition des colonnes dans un ordre
CREATE TABLE ou ALTER TABLE.
-
8/18/2019 Cours SQL Ldd
33/87
Définition des contraintes
CONSTRAINT nom -contrainte définition -contrainte
Les contraintes sont définies dans les commandesCREATE (ou ALTER) TABLE
à l'intérieur des définitions de colonnes pour les contraintesde colonne
CONSTRAINT nom -contrainte définition -contrainte
de colonne
au même niveau que les définitions de colonnes pour les
contraintes de table
Chaque contrainte doit être nommée :
Ceci permettra de la désigner par un ordre ALTER TABLE
SAGAR Samya33
Ceci permettra de la désigner par un ordre ALTER TABLE
Le nom d'une contrainte doit être unique parmi toutes les
contraintes de toutes les tables de la base de données.
-
8/18/2019 Cours SQL Ldd
34/87
Clé primaire
Sinon, il faut ajouter une contrainte de table :
create table emp (
matr integer constraint pkemp primary key ,
. . .
Sinon, il faut ajouter une contrainte de table :
Remarque : Contrainte sur les clés primaires
create table participation (
matr integer ,
codeP integer ,
. . .,
constraint pkpar primary key( matr , codeP ))
SAGAR Samya34
Remarque : Contrainte sur les clés primaires
Aucune des colonnes de la clé primaire ne peut avoir la valeur null
-
8/18/2019 Cours SQL Ldd
35/87
Contrainte d'unicité : UNIQUE
(pour une contrainte sur une table :)
Interdit qu'une colonne (ou la concaténation de plusieurscolonnes) contienne deux valeurs identiques.
(pour une contrainte sur une table :)UNIQUE (colonne1, colonne2,...)
(pour une contrainte sur une colonne :)UNIQUE
colonnes) contienne deux valeurs identiques.
Remarques :
2 lignes de la table ne pourront avoir la même valeur (sauf NULL)
Correspond à un identificateur (clé candidate si minimal), si on
ajoute une contrainte NOT NULL
SAGAR Samya35
-
8/18/2019 Cours SQL Ldd
36/87
Attention: Erreur à ne pas faire
Si une table a une clé primaire formée de 2 colonnes, il ne
faut pas déclarer 2 contraintes de colonne
Mais , il faut déclarer une seule contrainte de table
portant sur les 2 colonnes :
constraint pkpar primary key(matr,
codeP)
SAGAR Samya36
-
8/18/2019 Cours SQL Ldd
37/87
Clé étrangère
Si une seule colonne forme la clé étrangère, le plus simple estSi une seule colonne forme la clé étrangère, le plus simple est
REFERENCES table -ref [(col1)]
create table emp (
. . .,
dept integer constraint r_dept references
Elle peut être une contrainte de table :
REFERENCES table -
dept integer constraint r_dept references
dept (dept )) Optionnel sicolonne référencée
est clé primaire
create table emp (Il faut ajouter
SAGAR Samya37
create table emp (
. . .,
dept integer ,
constraint r_dept foreign key (dept )
references dept (dept ))
Il faut ajouterforeign key
-
8/18/2019 Cours SQL Ldd
38/87
Clés étrangères : Remarques
Cette contrainte ne permettra pas d'insérer une ligne deCette contrainte ne permettra pas d'insérer une ligne dela table si la table table - ref ne contient aucune ligne dont
la concaténation des valeurs de col1, col2,... est égale à la
concaténation des valeurs de colonne1, colonne2,...
doivent avoir la contrainte :doivent avoir la contrainte :
PRIMARY KEY ou UNIQUE
constraint r_dept references dept(dept)
dept doit être clé
SAGAR Samya38
dept doit être cléprimaire, ou
unique
-
8/18/2019 Cours SQL Ldd
39/87
Clés étrangères : Options
Option ON DELETE CASCADEOption ON DELETE CASCADESans
On ne peut supprimer un département s'il est référencé parune ligne de la table emp
create table emp (
. . .
dept integer
constraint r_dept references dept )
une ligne de la table emp
Avec
La suppression d'un département entraîne automatiquement la
create table emp (
. . .
dept number (2)
constraint r_dept references dept
on delete cascade)
SAGAR Samya39
La suppression d'un département entraîne automatiquement la
suppression de toutes les lignes de la table emp quiréférencent ce département
-
8/18/2019 Cours SQL Ldd
40/87
Clés étrangères : Options
Option ON DELETE SET NULLOption ON DELETE SET NULLDans ce cas, la clé étrangère sera mise à NULL si la lignequ'elle référence dans table - ref est supprimée.
Autres options : La norme SQL2 offre 4 autres options quine sont pas implémentée dans Oracle :ne sont pas implémentée dans Oracle :
ON DELETE SET DEFAULT met une valeur par défaut dans la cléétrangère quand la clé primaire référencée est supprimée.
ON UPDATE CASCADE modifie la clé étrangère si on modifie la cléprimaire (ce qui est à éviter).
ON UPDATE SET NULL met NULL dans la clé étrangère quand laclé primaire référencée est modifiée.
SAGAR Samya40
clé primaire référencée est modifiée.
ON UPDATE SET DEFAULT met une valeur par défaut dans la cléétrangère quand la clé primaire référencée est modifiée.
-
8/18/2019 Cours SQL Ldd
41/87
Contrainte CHECK
CHECK(condition )CHECK(condition )
Donne une condition que les colonnes de chaque ligne
devront vérifier.
On peut ainsi indiquer des contraintes d'intégrité dedomaines.domaines.
Cette contrainte peut être une contrainte de colonne oude table.
Si c'est une contrainte de colonne, elle ne doit porter que
sur la colonne en question.
SAGAR Samya41
sur la colonne en question.
-
8/18/2019 Cours SQL Ldd
42/87
Exemples de contraintes
CREATE TABLE EMP (
MATR INTEGER CONSTRAINT KEMP PRIMARY KEY ,
NOME VARCHAR(10) CONSTRAINT NOM_UNIQUE UNIQUE
CONSTRAINT MAJ CHECK (NOME = UPPER(NOME) ,
..................
DEPT INTEGER CONSTRAINT R_DEPT REFERENCES
DEPT(DEPT)
CONSTRAINT NDEPT CHECK (DEPT IN (10,
20, 30, 35, 40)))
SAGAR Samya42
-
8/18/2019 Cours SQL Ldd
43/87
Modification des contraintes
Des contraintes d'intégrité peuvent être ajoutées,Des contraintes d'intégrité peuvent être ajoutées,supprimées ou renommées, par la commande ALTER
TABLE .
On peut aussi modifier l' état de contraintes par MODIFYCONSTRAINT .
On ne peut ajouter que des contraintes de table.On ne peut ajouter que des contraintes de table.
Si on veut ajouter (ou modifier) une contrainte decolonne, il faut modifier la colonne
SAGAR Samya43
-
8/18/2019 Cours SQL Ldd
44/87
Exemples de modification de contraintes
ALTER TABLE emp
DROP CONSTRAINT nom_unique
ADD CONSTRAINT sal_min CHECK(sal + coalesce( comm,0)>50)
RENAME CONSTRAINT nom1 TO nom2
MODIFY CONSTRAINT sal_min DISABLE
SAGAR Samya44
-
8/18/2019 Cours SQL Ldd
45/87
Vérification des contraintes
En fonctionnement normal les contraintes sont vérifiées àEn fonctionnement normal les contraintes sont vérifiées àchaque requête SQL
Cette vérification peut être gênante, en particulier lors de
Exemple : si on a cette contrainte sur la colonne SUP dela table EMP :la table EMP :
constraint sup_ref_emp references EMP
La contrainte oblige à ajouter les supérieurs en premier, avant
les employés.
Tout se passera bien si tous les employés sont ajoutés en une
SAGAR Samya45
Tout se passera bien si tous les employés sont ajoutés en une
seule transaction et si cette contrainte n'est vérifiée qu'à la fin
de la transaction.
-
8/18/2019 Cours SQL Ldd
46/87
Contraintes « différables »
contrainte peut être différée à la fin de latransaction.
Si une contrainte est différée et si elle n'est pasSi une contrainte est différée et si elle n'est pasvérifiée au moment du commit de la transaction,
toute la transaction est invalidée (rollback)
automatiquement par le SGBD.
SAGAR Samya46
-
8/18/2019 Cours SQL Ldd
47/87
Différer une contrainte
2 étapes :2 étapes :1. Contrainte différale : Au moment de la déclaration de la
contrainte, on indique qu'on pourra différer sa vérification:CONSTRAINT truc def -contrainte [NOT] DEFERRABLE
[INITIALLY DEFERRED | IMMEDIATE]
La valeur par défaut est NOT DEFERRABLE.
Si une contrainte peut être différée, elle ne le sera qu'après la
deuxième étape. On peut changer ça en donnant la clausedeuxième étape. On peut changer ça en donnant la clauseINITIALLY DEFERRED qui diffère tout de suite la
contrainte pour toutes les transactions.
SAGAR Samya47
-
8/18/2019 Cours SQL Ldd
48/87
Différer une contrainte
2. On diffère la vérification de contraintes juste pour la2. On diffère la vérification de contraintes juste pour latransaction en cours, avec la commande :
Si elle est INITIALLY DEFERRED, elle sera différée par défautau début de chaque transaction. On peut ne plus la différer
set CONSTRAINTS {liste de contraintes | ALL} DEFERRED
au début de chaque transaction. On peut ne plus la différer
pour le temps d'une transaction en lançant la commande :
set CONSTRAINTS {liste de contraintes | ALL} IMMEDIATE
SAGAR Samya48
-
8/18/2019 Cours SQL Ldd
49/87
Différer une contrainte : Exemple
create table emp (create table emp (matr integer constraint emp_pk primary key,
. . .,
sup integer CONSTRAINT EMP_REF_SUP REFERENCES EMP DEFERRABLE ,
. . .);
SET CONSTRAINTS EMP_REF_SUP DEFERRED ;
insert into emp valuesinsert into emp values
(7499, 'Biraud', 'commercial', 7698, '20/2/1981',
12800.00, 2400.00, 30);
. . .
insert into emp values
(7698, 'Leroy', 'directeur', null, '19/3/1988',15000.00, null, 30);
SAGAR Samya49
COMMIT SET CONSTRAINTS EMP_REF_SUP IMMEDIATE ;
-
8/18/2019 Cours SQL Ldd
50/87
Différer une contrainte
On peut modifier l'état d'une contrainte différable par laOn peut modifier l'état d'une contrainte différable par lacommande ALTER TABLE
Exemple :
ALTER TABLE emp
MODIFY CONSTRAINT ref_dept_emp INITIALLY DEFERRED
Si on veut rendre une contrainte différable (ou l'inverse),
on doit supprimer la contrainte et la recréer ensuite.
MODIFY CONSTRAINT ref_dept_emp INITIALLY DEFERRED
SAGAR Samya50
-
8/18/2019 Cours SQL Ldd
51/87
Invalider des contraintes avec Oracle
OracleOracleUtile pour, par exemple, améliorer les performances lors
ALTER TABLE table {DISABLE | ENABLE} constraint n o m - contrainte
Exemple :
La clause ENABLE a une option qui permet de conserver dansune table les lignes qui ne respectent pas la contrainte
ALTER TABLE EMP DISABLE CONSTRAINT NOM_UNIQUE
une table les lignes qui ne respectent pas la contrainte
réactivée.
SAGAR Samya51
-
8/18/2019 Cours SQL Ldd
52/87
Création et descriptions
52 SAGAR Samya
-
8/18/2019 Cours SQL Ldd
53/87
Schéma
CREATE SCHEMA AUTHORIZATION
Un schéma est un ensemble d'objets (tables, vues, index, autorisations,etc...) gérés ensemble.
On pourra ainsi avoir un schéma lié à la gestion du personnel et un autre
lié à la gestion des clients.
CREATE SCHEMA AUTHORIZATION
lié à la gestion des clients.
Cette notion introduite par la norme SQL2 n'est pas vraiment prise encompte par Oracle qui identifie pour le moment un nom de schéma avecun nom d'utilisateur.
Le catalogue , est un ensemble de schémas.
Un catalogue doit nécessairement comprendre un schéma particulier quicorrespond au dictionnaire des données .correspond au dictionnaire des données .
SAGAR Samya53
-
8/18/2019 Cours SQL Ldd
54/87
CREATE TABLE table
permet d'insérer pendant la création de la table des lignes venant d'autrestables.
On peut aussi spécifier des contraintes d'intégrité de colonne ou de table.
Il faut évidemment que les définitions des colonnes de la table créée et durésultat de la sélection soient compatibles en type et en taille.
CREATE TABLE table
Il faut évidemment que les définitions des colonnes de la table créée et durésultat de la sélection soient compatibles en type et en taille.
On peut également ne pas donner les noms et type des colonnes de latable créée.
create table dept2 ( cle integer , nom varchar (20))
as select dept, nomd from dept;
SAGAR Samya54
create table dept10 as select * from emp
where dept = 10;
-
8/18/2019 Cours SQL Ldd
55/87
Modifier la définition d'une tableALTER
Ajout d'une colonne - ADDAjout d'une colonne - ADD
Exemple :
ALTER TABLE table
ADD (col 1 type1, col 2 type2
Exemple :
alter table personne
add (email_valide char(1)
constraint personne_email_valide
check( email_valide in ('o', 'n')));
SAGAR Samya55
-
8/18/2019 Cours SQL Ldd
56/87
Modifier la définition d'une tableALTER
Modification d'une colonne MODIFYModification d'une colonne MODIFY
Note : On ne peut modifier une colonne que si la colonne ne contientque des valeurs null ou si la nouvelle définition est compatible avec les
ALTER TABLE table
que des valeurs null ou si la nouvelle définition est compatible avec les
valeurs déjà entrées dans cette colonne
on ne peut pas diminuer la taille maximale d'une colonne.
on ne peut spécifier 'NOT NULL' que si la colonne ne contient pas de valeurnulle.
Il est toujours possible d'augmenter la taille maximale d'une colonne.On peut donner une contrainte de colonne dans la nouvelle définition de laOn peut donner une contrainte de colonne dans la nouvelle définition de lacolonne.
et on peut dans tous les cas spécier 'NULL' pour autoriser les valeurs nulles.
SAGAR Samya56
-
8/18/2019 Cours SQL Ldd
57/87
Modifier la définition d'une tableALTER
Modification d'une colonne MODIFYModification d'une colonne MODIFY
Exemples
alter table personne
modify (
prenoms null,nom varchar(50));nom varchar(50));
alter table personne
modify (
sexe char(1)
constraint personne_sexe_ck check(sexe
in ('m', 'f')));
SAGAR Samya57
-
8/18/2019 Cours SQL Ldd
58/87
Modifier la définition d'une tableALTER
Suppression d'une colonne - DROP COLUMNSuppression d'une colonne - DROP COLUMN
Important
ALTER TABLE table
DROP COLUMN colonne;
Importantréférencée par une clé étrangère
sur laquelle un index a été construit
Exemple :
ALTER TABLE EMP
SAGAR Samya58
ALTER TABLE EMP
DROP COLUMN sal;
-
8/18/2019 Cours SQL Ldd
59/87
Modifier la définition d'une tableALTER
Renommer une colonne RENAME COLUMNRenommer une colonne RENAME COLUMN
ALTER TABLE table
RENAME COLUMN ancien_nom TO nouveau_nom ;
SAGAR Samya59
-
8/18/2019 Cours SQL Ldd
60/87
Modifier la définition d'une tableALTER
Renommer une table RENAME TORenommer une table RENAME TOOn peut renommer une table :
ALTER TABLE ancienNom
RENAME TO nouveauNom
Oracle offre une commande équivalente pour renommerune table
RENAME ancienNom TO nouveauNom
SAGAR Samya60
-
8/18/2019 Cours SQL Ldd
61/87
Supprimer une table
DROP TABLE table
permet de supprimer une table :
les lignes de la table et la définition elle -même de la table sont
détruites.L'espace occupé par la table est libéré.
DROP TABLE table
L'espace occupé par la table est libéré.
Il est impossible de supprimer une table si la table est
référencée par une contrainte d'intégrité référentielle.
Une variante Oracle (pas SQL2 ) de la commande permet de
supprimer les contraintes d'intégrité et la table :supprimer les contraintes d'intégrité et la table :
SAGAR Samya61
DROP TABLE table CASCADE CONSTRAINTS
-
8/18/2019 Cours SQL Ldd
62/87
Si une table doit être utilisée par plusieurs utilisateurs, il peut
être intéressant de lui donner un synonyme public pour queSi une table doit être utilisée par plusieurs utilisateurs, il peut
être intéressant de lui donner un synonyme public pour queles utilisateurs ne soient pas obligés de préfixer le nom de la
table par le nom de son créateur.
CREATE [ PUBLIC | PRIVATE ] SYNONYM employe FOR
toto.emp
Pour chercher des synonymes dans le dictionnaire des
données :
toto.emp
select synonym_name , table_name , owner
from all_synonyms
where table_owner
Remarque : on peut créer un synonyme à une vue.
SAGAR Samya62
where table_owner
-
8/18/2019 Cours SQL Ldd
63/87
Vue
Une vue est une vision partielle ou particulière desUne vue est une vision partielle ou particulière des
select :
les données de la vue sont celles retournées par le select
Les utilisateurs peuvent consulter ou modifier la base àtravers la vue comme si c'était une table réelletravers la vue comme si c'était une table réelle
Seule la définition de la vue est enregistrée dans la base,
et pas les données de la vue.
table virtuelle .
SAGAR Samya63
-
8/18/2019 Cours SQL Ldd
64/87
Création et suppression d'une vue
CREATE VIEW
Le select peut contenir toutes les clauses d'un select sauf « order by »
La spécification des noms des colonnes de la vue est facultative : par
défaut, sont ceux des colonnes résultats du SELECT.
Si certaines colonnes résultats du SELECT sont des expressions sans
CREATE VIEW
[WITH CHECK OPTION]
Si certaines colonnes résultats du SELECT sont des expressions sansnom, il faut alors obligatoirement spécifier les noms de colonnes de lavue.
Exemple :create view emp10 as
select * from emp
where dept = 10;
SAGAR Samya64
DROP VIEW vue
-
8/18/2019 Cours SQL Ldd
65/87
Exemple de création de vues
create view deptStat (nom, inf, moy, max, total)
as
select nomd, min(sal), avg(sal), max(sal), sum(sal)
from emp natural join deptfrom emp natural join dept
group by dept.nomd
SAGAR Samya65
-
8/18/2019 Cours SQL Ldd
66/87
Utilisation des vues dans un select
Dans un select on peut utiliser une vue à la place d'uneDans un select on peut utiliser une vue à la place d'unetable
select * from emp10 ;
select nom, totalselect nom, total
from deptStat
where total > 100000;
SAGAR Samya66
-
8/18/2019 Cours SQL Ldd
67/87
Suppression avec une vue
On peut effectuer des delete à travers une vue, sousOn peut effectuer des delete à travers une vue, sousles conditions suivantes sur le select qui définit la vue :
une seule table (pas de jointure)
pas de group by
pas de distinct
pas de fonction de groupepas de fonction de groupe
SAGAR Samya67
-
8/18/2019 Cours SQL Ldd
68/87
Modification avec une vue
On peut effectuer des update à travers une vue, sousOn peut effectuer des update à travers une vue, sousles conditions du delete , et en plus :
les colonnes modifiées sont des colonnes réelles de la table
(pas des expressions)
Exemple :Exemple :
Ainsi, il est possible de modifier les salaires du département 10
à travers la vue EMP10. Toutes les lignes de la table EMP avec
update emp10
set sal = sal * 1.1;
à travers la vue EMP10. Toutes les lignes de la table EMP avec
DEPT = 10 seront modifiées.
SAGAR Samya68
-
8/18/2019 Cours SQL Ldd
69/87
Insertion avec une vue
On peut effectuer des insert à travers une vue, sousOn peut effectuer des insert à travers une vue, sousles conditions du update , et en plus :
toute colonne « not null » de la table représentée par la vue
est présente dans la vue
ExempleExemple
insert into emp10 ( matr, nome
SAGAR Samya69
-
8/18/2019 Cours SQL Ldd
70/87
Les Vues et Oracle
Les restrictions données ci -dessus ( suppression, modification,Les restrictions données ci -dessus ( suppression, modification,insertion avec une vue ) sont les restrictions de la normeSQL2.
Oracle permet de modifier les données d'une table sous - jacente par l'interm édiaire d'une vue qui comporte une
jointure lorsque la vue préserve la clé de la table.
Exemple:Exemple:
L'instruction suivante est permise sous Oracle :
CREATE VIEW EMP2 AS
SELECT matr, nomE, nomD
FROM EMP NATURAL JOIN DEPT
SAGAR Samya70
UPDATE EMP 2
SET nomE
where nomE
-
8/18/2019 Cours SQL Ldd
71/87
Option CHECK
Une vue peut créer des données qu'elle ne pourra pasUne vue peut créer des données qu'elle ne pourra pasvisualiser.
On peut ainsi ajouter un employé du département 20 avec la vue
EMP10.
Si la vue a été créée avec " WITH CHECK OPTION ", toute
modification à travers de la vue ne peut donner des donnéesmodification à travers de la vue ne peut donner des donnéesqui ne seraient pas affichées par la vue
Exemple :create view emp10 AS
select * from emp
where dept = 10
with check option
SAGAR Samya71
update emp10
set dept = 20interdit
-
8/18/2019 Cours SQL Ldd
72/87
Utilité des vues
Les vues permettent de dissocierLes vues permettent de dissocierla façon dont les utilisateurs voient les données
du découpage en tables
On favorise ainsi l'indépendance entre les programmes et
les données
Si un programme utilise des vues, on peut, par exemple,Si un programme utilise des vues, on peut, par exemple,
remplacer une table par 2 tables sans modifier leprogramme de consultation des données ; il suffit de
modifier la définition des vues
SAGAR Samya72
-
8/18/2019 Cours SQL Ldd
73/87
Utilité des vues
Peuvent simplifier la consultation de la base enPeuvent simplifier la consultation de la base enenregistrant des select complexes
Participent à la protection des données :
on peut donner accès à une vue, sans donner accès à la table
sous - jacente
lignes d'une table
les modifications des données peuvent être restreintes avec la
clause WITH CHECK OPTION
SAGAR Samya73
-
8/18/2019 Cours SQL Ldd
74/87
Index
Un index utilise des techniques informatiques pour
rendre très rapides les accès aux valeurs d'une colonnerendre très rapides les accès aux valeurs d'une colonne
Exemple :
est très long si la table emp contient des millions de lignes
select * from emp
where nomE
est très long si la table emp contient des millions de lignes
Un index bien construit permet d'obtenir l'emplacement desinformations sur Mahmoud en quelques accès disques.
Un index peut porter sur plusieurs colonnes : la clé
d'accès sera la concaténation des différentes colonnes.d'accès sera la concaténation des différentes colonnes.
On peut créer plusieurs index indépendants sur une
même table.
SAGAR Samya74
-
8/18/2019 Cours SQL Ldd
75/87
Création et suppression d'un index
Exemple :
Le nom choisi doit être unique parmi tous les index de
CREATE [UNIQUE] INDEX nomIndex
ON table (col 1, col 2
create index nomE on emp(nomE);
Le nom choisi doit être unique parmi tous les index detoutes les tables
Oracle crée automatiquement un index sur les colonnes
qui ont des contraintes Primary key et Unique
Suppression :Suppression :
SAGAR Samya75
DROP INDEX nomIndex
-
8/18/2019 Cours SQL Ldd
76/87
Utilisation d'un index
Après sa création un index est géré automatiquement parAprès sa création un index est géré automatiquement parle SGBD
Il est transparent pour l'utilisateur : celui -ci interroge labase de la même façon que si l'index n'existait pas
Le SGBD peut utiliser un index s'il pense que la requête
sera accéléréesera accélérée
Les index ralentissent les modifications des données
SAGAR Samya76
-
8/18/2019 Cours SQL Ldd
77/87
Séquences et Génération de clés primaire
Utilité
Les identifiants de lignes non significatifs sont préférables
entiers
Le problème : générer des entiers sans que 2 lignes puissent
avoir le même identifiant, même en situation de concurrenceavoir le même identifiant, même en situation de concurrenceentre plusieurs transactions
Solution 1 (Une mauvaise solution)
Prendre le plus grand nombre déjà utilisé dans la table comme
identifiant et ajouter 1:
Exemple :lock table;
val = select max(cle) from table;Exemple :
SAGAR Samya77
val = select max(cle) from table;
insert into table
commit;
-
8/18/2019 Cours SQL Ldd
78/87
Séquences et Génération de clés primaire
Inconvénients de la solution 1Inconvénients de la solution 1
nécessite un accès à la base
il faut trouver le plus grand
nécessite un blocage de la table pour éviter que 2 transactions
Si on veut garder un historique, on peut se retrouver avec
SAGAR Samya78
-
8/18/2019 Cours SQL Ldd
79/87
Séquences et Génération de clés primaire
Solution 2Solution 2
Une table contient la prochaine clé à attribuer
La valeur est incrémentée à chaque nouvelle clé
Variantes
Variante 1 :Variante 1 :Une seule table contient une seule valeur utilisée pour les identifiantsde toutes les tables
Variante 2 :
Une table par clé
Variante 3 :Variante 3 :
Quelle colonnes dans la table des clés ?
SAGAR Samya79
-
8/18/2019 Cours SQL Ldd
80/87
Séquences et Génération de clés primaire
ExempleExemplelock table_cle;
update table_cle
set cle = cle + 1;set cle = cle + 1;val = select cle from table_cle;
commit;
insert into table
SAGAR Samya80
commit;
-
8/18/2019 Cours SQL Ldd
81/87
Séquences et Génération de clés primaire
Inconvénients de l'utilisation d'une tableInconvénients de l'utilisation d'une table
nécessite un accès à la base (mais cette petite table est
conservée en mémoire centrale dans le cache du SGBD)
SAGAR Samya81
-
8/18/2019 Cours SQL Ldd
82/87
Séquences et Génération de clés primaire
SéquenceSéquenceLes versions actuelles des SGBD offrent des solutions qui
Inconvénient : pas les mêmes solutions dans tous les
SGBDsSGBDsLes séquences sont disponibles avec Oracle , DB2 etPostgreSQL
SAGAR Samya82
-
8/18/2019 Cours SQL Ldd
83/87
Créer une séquence
Exemple
CREATE SEQUENCE nom_séquence
[INCREMENT BY entier1]
[START WITH entier2]
Exemple
create sequence seqdept
increment by 10
start with 10
SAGAR Samya83
-
8/18/2019 Cours SQL Ldd
84/87
Utilisation des séquences
Deux pseudo -colonnes permettent d'utiliser les séquences :Deux pseudo -colonnes permettent d'utiliser les séquences :CURRVAL retourne la valeur courante
NEXTVAL incrémente la séquence et retourne la nouvelle valeur
Remarques :
insert into dept(dept, nomd)
values ( seqdept.nextval , 'Finances')
Remarques :
On ne peut utiliser CURRVAL NEXTVAL au moinsune fois dans la session de travail
NEXTVAL modifie immédiatement la valeur future pour les autres
La valeur de CURRVAL ne dépend que des NEXTVAL lancés dans la mêmeLa valeur de CURRVAL ne dépend que des NEXTVAL lancés dans la même
transaction
SAGAR Samya84
-
8/18/2019 Cours SQL Ldd
85/87
Modification des séquences
ALTER SEQUENCE nom_séquence
Exemple :
ALTER SEQUENCE nom_séquenceINCREMENT BY entier 1
alter sequence seqdept
increment by 5
Remarque : On ne peut modifier la valeur de départ
Informations sur les séquences
select seqdept.currval from dual
increment by 5
Tables du dictionnaire des données :
USER_SEQUENCESet ALL_SEQUENCES
SAGAR Samya85
-
8/18/2019 Cours SQL Ldd
86/87
Récupérer plusieurs identificateurs
Mettre un incrément supérieur à 1 permet d'obtenirMettre un incrément supérieur à 1 permet d'obtenirplusieurs identificateurs en un seul appel pour obtenir de
meilleures performances
Par exemple, si on veut des identificateurs pour des lignes
de factures, on en a souvent besoin de plusieurs en même
tempstemps
Remarque :
DB2 et SQL Server ont une clause « IDENTITY »DB2 et SQL Server ont une clause « IDENTITY »
SAGAR Samya86
-
8/18/2019 Cours SQL Ldd
87/87
Conclusion
Les procédures stockées, les fonctions, ou encore les triggers
(voir cours PL/SQL).
Cluster (voir cour SGBD)
Les index et les vues sont fortement utilisés au niveau deLes index et les vues sont fortement utilisés au niveau de
la performance de base de données et aux plans
SAGAR Samya87