Le langage SQL

Au niveau colonne :

CREATE TABLE Fournisseurs (fournisseur id number(10) PRIMARY KEY, nom

varchar2(20) not null, contact varchar2(50)) ;

CREATE TABLE Fournisseurs (fournisseur id number(10) CONSTRAINT

pk fournisseurs PRIMARY KEY, nom varchar2(20) not null, contact varchar2(50)) ;

Au niveau table :

CREATE TABLE Fournisseurs (fournisseur id number(10), nom varchar2(20) not null,

contact varchar2(50), CONSTRAINT pk fournisseurs PRIMARY

KEY(fournisseur id)) ;

CREATE TABLE Etudiants(nom varchar2(30), prenom varchar2(30), date naiss date,

CONSTRAINT pk etudiants PRIMARY KEY (nom,prenom)) ;

12/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Les contraintes

La contrainte FOREIGN KEY

Syntaxe

Au niveau table :

CREATE TABLE <nom table>

(<col1> type null/not null,

<col2> type null/not null,

...,

CONSTRAINT <fk table colonne> FOREIGN KEY (col1, col2, ... coln) REFERENCES

<table parente> (col1, col2, ... coln)

ON DELETE {CASCADE|SET NULL|SET DEFAULT|RESTRICT}

ON UPDATE {CASCADE|SET NULL|SET DEFAULT|RESTRICT}

) ;

Au niveau colonne :

CREATE TABLE <nom table>

(<col1> type null/not null CONSTRAINT <fk table colonne> REFERENCES <table parente>

(col) ON DELETE {CASCADE|SET NULL|SET DEFAULT|RESTRICT}

ON UPDATE {CASCADE|SET NULL|SET DEFAULT|RESTRICT},

<col2> type null/not null,

...,

) ;

13/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Les contraintes

La contrainte FOREIGN KEY

FOREIGN KEY : définit la colonne dans la table fille au niveau

contrainte de table

REFERENCES : identifie la table et la colonne dans la table parente

Notez Bien

Créer les tables parentes avant les tables filles

Supprimer les tables filles avant les tables parentes

14/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Les contraintes

La contrainte FOREIGN KEY : mot clé ON DELETE

Les options possibles avec le mot clé ON DELETE :

CASCADE : supprime les lignes dépendantes dans la table fille

lorsqu’une ligne de la table parente est supprimée

SET NULL : convertit les valeurs des clés étrangères dépendantes en

valeurs NULL. Cette contrainte ne peut être exécutée que si toutes

les colonnes de clé étrangère de la table cible acceptent des

valeurs NULL.

SET DEFAULT place la valeur par défaut (qui suit ce paramètre)

dans la ligne de la table étrangère en cas d’effacement d’une valeur

correspondant à la clé (option n’est pas prise en charge dans oracle)

RESTRICT indique une erreur en cas d’effacement d’une valeur

correspondant à la clé (option n’est pas prise en charge dans oracle)

15/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Les contraintes

La contrainte FOREIGN KEY : mot clé ON UPDATE

Les options possibles avec le mot clé ON UPDATE (option n’est pas prise

en charge dans oracle) :

CASCADE : met à jour les lignes dépendantes dans la table fille

lorsqu’une ligne de la table parente est mise à jour

SET NULL : convertit les valeurs des clés étrangères dépendantes en

valeurs NULL. Cette contrainte ne peut être exécutée que si toutes

les colonnes de clé étrangère de la table cible acceptent des

valeurs NULL.

SET DEFAULT place la valeur par défaut (qui suit ce paramètre)

dans la ligne de la table étrangere en cas de mise a jour d’une valeur

correspondant à la clé

RESTRICT indique une erreur en cas de mise à jour d’une valeur

correspondant à la clé

16/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Les contraintes

La contrainte FOREIGN KEY

Exemple 1

CREATE TABLE Fournisseurs ( fournisseur id number(10) not null PRIMARY KEY, nom

varchar2(50) not null, contact varchar2(50)) ;

CREATE TABLE Produits (produit id number(10) not null, fournisseur id number(10) not null,

CONSTRAINT fk produits FOREIGN KEY (fournisseur id ) REFERENCES Fournisseurs

(fournisseur id ) ON DELETE cascade) ;

Exemple 2

CREATE TABLE Fournisseurs ( fournisseur id number(10) not null, nom varchar2(50) not null,

contact varchar2(50),CONSTRAINT pk fournisseur PRIMARY KEY (fournisseur id ,nom)) ;

CREATE TABLE Produits (produit id number(10) not null, fournisseur id number(10) not null,

nom varchar2(50) not null, CONSTRAINT fk produits FOREIGN KEY (fournisseur id , nom)

REFERENCES Fournisseurs (fournisseur id ,nom)) ;

17/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

L’instruction ALTER TABLE : permet de modifier la structure de la table

colonne :

ajouter une colonne (ADD)

modifier une colonne existante (MODIFY) –propriété d’oracle

supprimer une colonne (DROP)

contrainte :

ajouter une contrainte (ADD CONSTRAINT)

supprimer une contrainte (DROP CONSTRAINT)

18/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

Ajouter une colonne

Syntaxe

ALTER TABLE <nom table> ADD (<column> type [DEFAULT expr] [,

<column> type][DEFAULT expr],...) ;

Exemple

ALTER TABLE Fournisseurs ADD( adresse varchar2(50), telephone

number(8) NOT NULL) ;

19/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

Modifier une colonne

La modification d’une colonne peut concerner :

le type de données,

la taille,

la valeur par défaut d’une colonne (affecte uniquement les nouvelles

insertions dans la table)

Syntaxe

ALTER TABLE <nom table> MODIFY (<column> type [DEFAULT

expr] [, <column> type][DEFAULT expr],...) ;

Exemple

ALTER TABLE Fournisseurs Modify(adresse varchar2(100), telephone

number(13)) ;

20/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

Supprimer une colonne

Syntaxe

ALTER TABLE <nom table> DROP (column1, column2, ...) ;

Exemple

ALTER TABLE Fournisseurs DROP (adresse, telephone) ;

21/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

Ajouter une contrainte

Syntaxe

ALTER TABLE <nom table> ADD [CONSTRAINT <nom contrainte>] type contrainte

(<nom colonne>) ;

Exemples

ALTER TABLE Fournisseurs ADD CONSTRAINT uq fournisseurs UNIQUE(fournisseur id) ;

ALTER TABLE Fournisseurs ADD CONSTRAINT ck fournisseurs CHECK (fournisseur id

between 10 and 1000) ;

ALTER TABLE Fournisseurs ADD CONSTRAINT pk fournisseurs PRIMARY KEY

(fournisseur id) ;

ALTER TABLE Produits add constraint fk produits FOREIGN KEY (fournisseur id, nom)

REFERENCES Fournisseurs(fournisseur id, nom) ;

Pour ajouter une contrainte NULL/NOT NULL on utilise l’option MODIFY

Exemple :

ALTER TABLE Fournisseurs MODIFY contact CONSTRAINT nn fournisseurs contact NOT

NULL ;

22/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

Supprimer une contrainte

Syntaxe

ALTER TABLE <nom table> DROP CONSTRAINT

<nom contrainte> ;

Exemple

ALTER TABLE Fournisseurs DROP CONSTRAINT ck fournisseurs ;

ALTER TABLE Fournisseurs DROP CONSTRAINT pk fournisseurs

23/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Modification de table

Activer/Désactiver une contrainte

Syntaxe

ALTER TABLE <nom table> ENABLE | DISABLE CONSTRAINT

<nom contrainte> ;

Exemple

CREATE TABLE T1(a1 number PRIMARY KEY, b1 varchar2(10)) ;

CREATE TABLE T2(a2 varchar2(10) PRIMARY KEY, b2 number CONSTRAINT fk T2 REFERENCES T1(a1)) ;

ALTER TABLE T1 ADD CONSTRAINT fk T1 FOREIGN KEY (b1) REFERENCES T2(a2) ;

=⇒Pour insérer des lignes dans ces deux tables il faut désactiver l’une des contraintes FOREIGN KEY :

ALTER TABLE T1 DISABLE CONSTRAINT fk T1 ;

=⇒ Insérer les lignes dans T1 et T2 :

INSERT INTO T1 VALUES(1,’a’) ;

INSERT INTO T1 VALUES(2,’b’) ;

INSERT INTO T2 VALUES(’b’,1) ;

=⇒ Activer la contrainte fk T1 :

ALTER TABLE T1 ENABLE CONSTRAINT fk T1 ;

La vérification des valeurs insérées se fait lors de l’activation de la contrainte fk T1

24/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Suppression de table

Syntaxe

DROP TABLE <nom table> ;

Remarque

Il existe aussi la commande TRUNCATE qui permet de vider la table.

Syntaxe :

TRUNCATE TABLE nom table ;

25/46

Table

Vue

Séquence

Index

Synonyme

Création de table

Modification de table

Suppression de table

Renommage de table

Renommage de table

Syntaxe

RENAME <ancien nom> TO <nouveau nom> ;

Exemple

RENAME Fournisseurs TO LesFournisseurs ;

26/46

Table

Vue

Séquence

Index

Synonyme

Création et modification de vue

Suppression de vue

Plan

1 Table

Création de table

Modification de table

Suppression de table

Renommage de table

2 Vue

Création et modification de vue

Suppression de vue

3 Séquence

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

4 Index

Création d’index

Suppression d’index

5 Synonyme

Création et supression de synonyme

27/46

Table

Vue

Séquence

Index

Synonyme

Création et modification de vue

Suppression de vue

Vue

Une vue est une table logique sur une ou plusieurs autres tables ou

vues,

Seule la définition de la vue (requête) est enregistrée dans la base, et

pas les données de la vue,

Limite l’acces a la base de données,

Facilite la création des requêtes complexes,

Présente les mêmes données sous différentes formes,

Il existe deux types de vues :

vue simple :

utilise une seule table

ne contient ni fonction ni groupe de données

permet d’exécuter des instructions LMD (UPDATE, DELETE,

INSERT)

vue complexe :

utilise plusieurs tables

contient des fonctions ou des groupes de données

ne permet pas des instructions LMD (UPDATE, DELETE, INSERT)

28/46

Table

Vue

Séquence

Index

Synonyme

Création et modification de vue

Suppression de vue

Création et modification de vue

Syntaxe

CREATE [OR REPLACE ] [FORCE|NOFORCE] VIEW <nom vue> [(alias [, alias], . . . )]

AS SELECT <requête> [WITH CHECK OPTION [CONSTRAINT <nom contrainte>]]

[WITH READ ONLY [CONSTRAINT <nom contrainte>]] ;

FORCE : crée la vue que les tables existent ou non

alias : indique les noms des expressions sélectionnées par la requête de la vue. Le nombre

d’alias doit être égal au nombre d’expressions sélectionnées

WITH CHECK OPTION : n’autorise l’insertion et la mise à jour des lignes que pour les

lignes auxquelles la vue peut accéder (vérifient les conditions de la clause WHERE)

CONSTRAINT : donne un nom de contrainte aux restrictions WITH CHECK OPTION et

WITH READ ONLY (messages d’erreur mieux compréhensibles)

WITH READ ONLY : aucune opération LMD ne peut être exécutée.

Notez Bien

Pour pouvoir insérer dans une vue, la vue doit comprendre au moins les colonnes sur lesquelles on

a une contrainte NOT NULL

29/46

Table

Vue

Séquence

Index

Synonyme

Création et modification de vue

Suppression de vue

Création et modification de vue

Exemple

CREATE TABLE Fournisseurs (fournisseur id number(10) PRIMARY KEY, nom

varchar2(20) not null, contact varchar2(50), code region number(3)) ;

1 CREATE OR REPLACE VIEW vue1 Fournisseurs 10 (numero,nom,region) AS SELECT

fournisseur id, nom, code region from fournisseurs where code region=10 ;

2 CREATE OR REPLACE VIEW vue2 Fournisseurs 10 (numero,nom,region) AS SELECT

fournisseur id, nom, code region from fournisseurs where code region=10 WITH CHECK

OPTION CONSTRAINT ck 10 ;

INSERT INTO vue2 Fournisseurs 10 VALUES (100, ’Daniel’,100) ;

=⇒ORA-01402 : vue WITH CHECK OPTION - violation de clause WHERE

INSERT INTO vue2 Fournisseurs 10 VALUES (100, ’Daniel’,10) ;

=⇒1 ligne(s) insérée(s).

3 CREATE OR REPLACE VIEW vue3 Fournisseurs 10 (numero,nom,region) AS SELECT

fournisseur id, nom, code region from fournisseurs where code region=10 WITH READ

ONLY ;

30/46

Table

Vue

Séquence

Index

Synonyme

Création et modification de vue

Suppression de vue

Suppression de vue

Syntaxe

DROP VIEW <nom vue> ;

Exemple

DROP VIEW vue3 Fournisseurs 10 ;

31/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Plan

1 Table

Création de table

Modification de table

Suppression de table

Renommage de table

2 Vue

Création et modification de vue

Suppression de vue

3 Séquence

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

4 Index

Création d’index

Suppression d’index

5 Synonyme

Création et supression de synonyme

32/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Séquence

Une séquence :

Génère automatiquement des numéros uniques

Est Partageable entre plusieurs utilisateurs et éventuellement entre

plusieurs tables

Permet de créer une valeur de clé primaire

33/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Création de séquence

Syntaxe

CREATE SEQUENCE <nom sequence>

[INCREMENT BY <pas>]

[START WITH <valeur>]

[{MAXVALUE <valeur max> | NOMAXVALUE}]

[{MINVALUE <valeur min> | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE <cache> | NOCACHE}]

INCREMENT BY : définit l’intervalle entre les numéros (pas d’incrémentation)

START WITH : premier numéro de la séquence

MAXVALUE | NOMAXVALUE : valeur maximale

MINVALUE | NOMINVALUE : valeur minimale

CYCLE | NOCYCLE : la séquence peut continuer à générer ou non des valeurs si la valeur

maximale (ou minimale) est atteinte (la valeur par défaut NOCYCLE)

CACHE | NOCACHE : nombre de valeurs pré-allouées et conservées en mémoire (la valeur

par défaut est cache=20)

34/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Création de séquence

Exemples

CREATE SEQUENCE sequence1 increment by 1 start with 1

maxvalue 5 ;

CREATE SEQUENCE sequence2 increment by 5 start with 10

maxvalue 100 NOCACHE NOCYCLE ;

CREATE SEQUENCE sequence3 increment by 1 start with 1

maxvalue 20 CACHE 10 CYCLE

CREATE SEQUENCE sequence4 increment by 5 start with -10

minvalue -20 maxvalue 5 cache 2 cycle ;

Notez Bien

La valeur du cache doit etre inférieur ou égale au nombre de valeurs d’un

cycle

35/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Modification de séquence

Syntaxe

ALTER SEQUENCE <nom sequence>

[INCREMENT BY <pas>]

[START WITH <valeur>]

[{MAXVALUE <valeur max> | NOMAXVALUE}]

[{MINVALUE <valeur min> | NOMINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE <cache> | NOCACHE}]

Exemple

ALTER SEQUENCE sequence1 maxvalue 10 cycle ;

36/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Utilisation de séquence

L’utilisation d’une séquence se fait par des ”pseudo-colonnes” CURRVAL et NEXTVAL. On

parle de pseudo-colonne car cela se manipule un peu comme une colonne de table, mais ce

n’est pas une colonne de table.

CURRVAL : retourne la valeur courante de la séquence

NEXTVAL : incrémente la séquence et retourne la nouvelle valeur

Exemple

CREATE TABLE Tseq(a number, b varchar2(5)) ;

CREATE SEQUENCE seq increment by 5 start with -10 minvalue -20 maxvalue 5 cache 2

cycle ;

INSERT INTO Tseq values (seq.nextval, ’a’)

SELECT * FROM Tseq ;

SELECT seq.currval FROM dual ;

37/46

Table

Vue

Séquence

Index

Synonyme

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

Suppression de séquence

Syntaxe

DROP SEQUENCE <nom sequence> ;

Exemple

DROP SEQUENCE seq ;

38/46

Table

Vue

Séquence

Index

Synonyme

Création d’index

Suppression d’index

Plan

1 Table

Création de table

Modification de table

Suppression de table

Renommage de table

2 Vue

Création et modification de vue

Suppression de vue

3 Séquence

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

4 Index

Création d’index

Suppression d’index

5 Synonyme

Création et supression de synonyme

39/46

Table

Vue

Séquence

Index

Synonyme

Création d’index

Suppression d’index

Index

Un index est un objet de la base de données qui permet d’accélérer

la recherche des lignes. Il contient deux champs :

la clé d’index

l’adresse du bloc de données contenant la clé

Un index peut être créé juste après la création d’une table ou sur

une table contenant déjà des lignes

Un index peut porter sur plusieurs colonnes : la clé d’accès sera la

concaténation des différentes colonnes

Les index sont indépendants logiquement et physiquement des tables

qu’ils indexent

40/46

Table

Vue

Séquence

Index

Synonyme

Création d’index

Suppression d’index

Création d’index

La création d’index peut être :

Automatique : un index unique est crée automatiquement lors de la définition d’une

contrainte PRIMARY KEY ou contrainte UNIQUE

Manuelle : un index non unique peut être crée manuellement (clés d’index peuvent

être dupliquées)

Syntaxe

CREATE [UNIQUE] INDEX <nom index> ON <nom table>(col1, col2,...)

Exemple

CREATE UNIQUE INDEX idx fournisseurs ON Fournisseurs (contact) ;

=⇒des valeurs dupliquées ne sont pas permise dans la colonne contact même si il y a pas une

contrainte UNIQUE sur cette colonne

Remarque

Il est possible de renommer un index

Syntaxe :

ALTER INDEX <ancien nom> RENAME TO <nouveau nom> ;

41/46

Table

Vue

Séquence

Index

Synonyme

Création d’index

Suppression d’index

Création d’index

Créez index si :

La colonne doit être souvent utilisée dans la clause WHERE ou une

condition de jointure

La colonne contient un grand nombre de valeurs NULL

Deux ou plusieurs colonnes sont souvent utilisées conjointement dans une

clause WHERE ou une condition de jointure

La table est de grande taille et la plupart des requêtes doivent extraire

moins de 2 à 4% des lignes

Ne pas créez index si :

La table est de petite taille

La table est souvent mise à jour

Les colonnes ne sont pas souvent utilisées comme condition dans une

requête

La plupart des requêtes sont prévues pour extraire un très grand

pourcentage de lignes

42/46

Table

Vue

Séquence

Index

Synonyme

Création d’index

Suppression d’index

Supression d’index

Syntaxe

DROP INDEX <nom index> ;

Exemple

DROP INDEX idx fournisseurs

43/46

Table

Vue

Séquence

Index

Synonyme

Création et supression de synonyme

Plan

1 Table

Création de table

Modification de table

Suppression de table

Renommage de table

2 Vue

Création et modification de vue

Suppression de vue

3 Séquence

Création de séquence

Modification de séquence

Utilisation de séquence

Suppression de séquence

4 Index

Création d’index

Suppression d’index

5 Synonyme

Création et supression de synonyme

44/46

Table

Vue

Séquence

Index

Synonyme

Création et supression de synonyme

Synonyme

Un synonyme est un alias sur un Objet de la base ou Schéma, une

sorte de raccourcis.

L’objet peut être une table, une vue, une séquence, une procédure,

une fonction, un package, etc

Le synonyme peut être publique ou privé.

publique : accessible à partir de tous schéma et utilisateurs

privé il sera accessible uniquement à partir du schéma dans lequel il a

été créé

Créer des synonymes pour :

masquer le vrai nom des objets et leur localisations

simplifier les noms des objets

éviter le pré-fixage dans les requêtes avec le nom de son propriétaire

45/46

Table

Vue

Séquence

Index

Synonyme

Création et supression de synonyme

Création et supression de synonyme

Création :

Syntaxe

CREATE [OR REPLACE] [PUBLIC] SYNONYM

<nom synonyme> FOR [schéma.]<nom objet> ;

Supression

Syntaxe

DROP SYNONYM <nom synonyme> ;

46/46