Le langage SQL

Ce document présente une synthèse complète du langage SQL, en particulier les commandes de définition de données (DDL). Il s’adresse aux étudiants et professionnels souhaitant maîtriser la création, la modification, la suppression et la gestion des objets de base de données tels que les tables, vues, séquences, index et synonymes.

Tables

Création de table

La création d’une table se fait avec la commande CREATE TABLE :

CREATE TABLE [schema.]<nom table>
(<nom colonne> type [DEFAULT expr],
<nom colonne> type [DEFAULT expr],
...
) ;

Exemple :

CREATE TABLE Etudiants (Netudiant number, nom varchar2(10), prenom varchar2(10)) ;

Pour afficher la description d’une table :

DESCRIBE Etudiants ;

Création de table à partir d’une sous-interrogation

Il est possible de créer une table en copiant la structure et les données d’une requête :

CREATE TABLE [schema.]<nom table> AS sous interrogation ;

Exemple :

CREATE TABLE emp AS SELECT * FROM employees WHERE department_id = 20 ;
SELECT * FROM emp ;

Les contraintes

Les contraintes assurent l’intégrité des données. Il existe cinq types :

• NOT NULL
• UNIQUE
• CHECK
• PRIMARY KEY
• FOREIGN KEY

Ces contraintes peuvent être définies au niveau colonne ou au niveau table.

Contrainte NOT NULL

Ne peut être définie qu’au niveau colonne. Exemple :

CREATE TABLE Fournisseurs (
  fournisseur_id number(10) NOT NULL,
  nom varchar2(20) NOT NULL,
  contact varchar2(50)
) ;

Contrainte UNIQUE

Garantit l’unicité des valeurs dans une colonne ou un ensemble de colonnes. Exemple au niveau colonne :

CREATE TABLE Fournisseurs (
  fournisseur_id number(10) UNIQUE,
  nom varchar2(20) NOT NULL,
  contact varchar2(50)
) ;

Exemple au niveau table :

CREATE TABLE Fournisseurs (
  fournisseur_id number(10),
  nom varchar2(20) NOT NULL,
  contact varchar2(50),
  CONSTRAINT uq_fournisseurs UNIQUE(fournisseur_id)
) ;

Contrainte CHECK

Définit une condition que chaque ligne doit vérifier. Exemple :

CREATE TABLE Fournisseurs (
  fournisseur_id number(10) CHECK (fournisseur_id BETWEEN 10 AND 1000),
  nom varchar2(20) NOT NULL,
  contact varchar2(50)
) ;

Contrainte PRIMARY KEY

Définit la clé primaire d’une table. Une seule clé primaire par table, composée d’une ou plusieurs colonnes, aucune ne pouvant être NULL. Exemple :

CREATE TABLE Fournisseurs (
  fournisseur_id number(10) PRIMARY KEY,
  nom varchar2(20) NOT NULL,
  contact varchar2(50)
) ;

Exemple avec clé primaire multiple :

CREATE TABLE Etudiants(
  nom varchar2(30),
  prenom varchar2(30),
  date_naiss date,
  CONSTRAINT pk_etudiants PRIMARY KEY (nom, prenom)
) ;

Contrainte FOREIGN KEY

Définit une clé étrangère reliant une table fille à une table parente. Syntaxe au niveau table :

CREATE TABLE <nom table> (
  <col1> 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}
) ;

Exemple :

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
) ;

Note importante : Créer d'abord les tables parentes avant les tables filles, et supprimer les tables filles avant les tables parentes.

Modification de table

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

• Ajouter une colonne (ADD)
• Modifier une colonne (MODIFY)
• Supprimer une colonne (DROP)
• Ajouter une contrainte (ADD CONSTRAINT)
• Supprimer une contrainte (DROP CONSTRAINT)

Ajouter une colonne

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
) ;

Modifier une colonne

On peut modifier le type, la taille ou la valeur par défaut :

ALTER TABLE <nom table> MODIFY (
  <column> type [DEFAULT expr],
  ...
) ;

Exemple :

ALTER TABLE Fournisseurs MODIFY (
  adresse varchar2(100),
  telephone number(13)
) ;

Supprimer une colonne

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

Exemple :

ALTER TABLE Fournisseurs DROP (adresse, telephone) ;

Ajouter une contrainte

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 modifier une contrainte NULL/NOT NULL :

ALTER TABLE Fournisseurs MODIFY contact CONSTRAINT nn_fournisseurs_contact NOT NULL ;

Supprimer une contrainte

ALTER TABLE <nom table> DROP CONSTRAINT <nom contrainte> ;

Exemple :

ALTER TABLE Fournisseurs DROP CONSTRAINT ck_fournisseurs ;
ALTER TABLE Fournisseurs DROP CONSTRAINT pk_fournisseurs ;

Activer ou désactiver une contrainte

ALTER TABLE <nom table> ENABLE | DISABLE CONSTRAINT <nom contrainte> ;

Exemple d’usage pour gérer des contraintes circulaires :

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) ;

-- Désactivation temporaire
ALTER TABLE T1 DISABLE CONSTRAINT fk_T1 ;

-- Insertion des données
INSERT INTO T1 VALUES(1, 'a') ;
INSERT INTO T1 VALUES(2, 'b') ;
INSERT INTO T2 VALUES('b', 1) ;

-- Réactivation de la contrainte
ALTER TABLE T1 ENABLE CONSTRAINT fk_T1 ;

Suppression de table

DROP TABLE <nom table> ;

Remarque : La commande TRUNCATE TABLE <nom table> permet de vider une table sans la supprimer.

Renommage de table

RENAME <ancien nom> TO <nouveau nom> ;

Exemple :

RENAME Fournisseurs TO LesFournisseurs ;

Vues

Une vue est une table logique définie par une requête sur une ou plusieurs tables ou vues. Elle ne stocke pas les données mais seulement la définition (requête). Les vues facilitent la gestion des accès, la simplification des requêtes complexes et la présentation des données sous différentes formes.

Il existe deux types de vues :

• Vue simple : utilise une seule table, ne contient ni fonction ni groupe, permet les opérations LMD (UPDATE, DELETE, INSERT).
• Vue complexe : utilise plusieurs tables, contient des fonctions ou groupes, ne permet pas les opérations LMD.

Création et modification de vue

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>]] ;

Options importantes :

• FORCE : crée la vue même si les tables n’existent pas.
• alias : noms des colonnes de la vue.
• WITH CHECK OPTION : interdit l’insertion ou la mise à jour de lignes qui ne respecteraient pas la condition de la vue.
• WITH READ ONLY : interdit toute modification via la vue.

Note : Pour insérer dans une vue, celle-ci doit contenir les colonnes avec contrainte NOT NULL.

Exemple de création de vues

CREATE TABLE Fournisseurs (
  fournisseur_id number(10) PRIMARY KEY,
  nom varchar2(20) NOT NULL,
  contact varchar2(50),
  code_region number(3)
) ;

-- Vue simple avec filtre
CREATE OR REPLACE VIEW vue1_fournisseurs_10 (numero, nom, region) AS
SELECT fournisseur_id, nom, code_region FROM Fournisseurs WHERE code_region = 10 ;

-- Vue avec contrôle d’insertion
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 ;

-- Insertion interdite (hors condition WHERE)
INSERT INTO vue2_fournisseurs_10 VALUES (100, 'Daniel', 100) ; -- ERREUR ORA-01402

-- Insertion autorisée (respecte la condition)
INSERT INTO vue2_fournisseurs_10 VALUES (100, 'Daniel', 10) ; -- OK

-- Vue en lecture seule
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 ;

Suppression de vue

DROP VIEW <nom vue> ;

Exemple :

DROP VIEW vue3_fournisseurs_10 ;

Séquences

Une séquence génère automatiquement des numéros uniques, souvent utilisés pour les clés primaires. Elle peut être partagée entre plusieurs utilisateurs ou tables.

Création de séquence

CREATE SEQUENCE <nom sequence>
[INCREMENT BY <pas>]
[START WITH <valeur>]
[{MAXVALUE <valeur max> | NOMAXVALUE}]
[{MINVALUE <valeur min> | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE <cache> | NOCACHE}]

Paramètres :

• INCREMENT BY : intervalle entre les numéros.
• START WITH : valeur de départ.
• MAXVALUE / MINVALUE : bornes de la séquence.
• CYCLE / NOCYCLE : la séquence recommence ou non après avoir atteint la limite.
• CACHE / NOCACHE : nombre de valeurs préallouées en mémoire.

Exemples de création

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 ;

Note : La valeur du cache doit être inférieure ou égale au nombre de valeurs d’un cycle.

Modification de séquence

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 ;

Utilisation de séquence

Les pseudo-colonnes NEXTVAL et CURRVAL permettent d’utiliser une séquence :

• NEXTVAL : incrémente la séquence et retourne la nouvelle valeur.
• CURRVAL : retourne la valeur courante de la séquence.

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 ;

Suppression de séquence

DROP SEQUENCE <nom sequence> ;

Exemple :

DROP SEQUENCE seq ;

Index

Un index est un objet de base de données qui accélère la recherche des lignes. Il contient :

• La clé d’index
• L’adresse du bloc de données contenant la clé

Un index peut être créé automatiquement lors de la définition d’une contrainte PRIMARY KEY ou UNIQUE, ou manuellement.

Création d’index

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

Exemple :

CREATE UNIQUE INDEX idx_fournisseurs ON Fournisseurs (contact) ;

Remarque : Il est possible de renommer un index :

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

Quand créer un index ?

• Colonne souvent utilisée dans une clause WHERE ou condition de jointure
• Colonne contenant beaucoup de valeurs NULL
• Plusieurs colonnes souvent utilisées conjointement dans une clause WHERE
• Table de grande taille avec requêtes extrayant peu de lignes (2 à 4%)

Quand ne pas créer d’index ?

• Table de petite taille
• Table souvent mise à jour
• Colonnes rarement utilisées dans des conditions de requête
• Requêtes extrayant un très grand pourcentage de lignes

Suppression d’index

DROP INDEX <nom index> ;

Exemple :

DROP INDEX idx_fournisseurs ;

Synonymes

Un synonyme est un alias pour un objet de base de données (table, vue, séquence, procédure, fonction, package, etc.). Il sert de raccourci pour simplifier l’accès aux objets, masquer leur nom réel ou éviter le préfixage avec le nom du propriétaire.

Un synonyme peut être :

• Public : accessible depuis tous les schémas et utilisateurs
• Privé : accessible uniquement dans le schéma où il a été créé

Création de synonyme

CREATE [OR REPLACE] [PUBLIC] SYNONYM <nom synonyme> FOR [schema.]<nom objet> ;

Suppression de synonyme

DROP SYNONYM <nom synonyme> ;

Glossaire des termes clés

• Table : structure de données stockant des lignes et colonnes.
• Vue : table logique définie par une requête, ne stocke pas les données.
• Séquence : générateur automatique de nombres uniques.
• Index : structure accélérant la recherche dans une table.
• Synonyme : alias pour un objet de base de données.
• Contrainte : règle assurant l’intégrité des données (NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY).
• PRIMARY KEY : clé primaire identifiant de façon unique chaque ligne.
• FOREIGN KEY : clé étrangère reliant une table à une autre.
• WITH CHECK OPTION : option de vue empêchant l’insertion/modification hors condition.
• WITH READ ONLY : option de vue interdisant toute modification.
• NEXTVAL : pseudo-colonne pour obtenir la prochaine valeur d’une séquence.
• CURRVAL : pseudo-colonne pour obtenir la valeur courante d’une séquence.

Points clés à retenir

• SQL comprend plusieurs langages : définition (DDL), manipulation (DML), interrogation (LID) et contrôle (LCD).
• Les tables sont créées avec CREATE TABLE et peuvent être modifiées avec ALTER TABLE.
• Les contraintes garantissent l’intégrité des données et peuvent être définies au niveau colonne ou table.
• Les vues permettent de simplifier l’accès aux données et de restreindre les modifications.
• Les séquences génèrent des valeurs uniques, utiles pour les clés primaires.
• Les index améliorent les performances des requêtes, mais doivent être utilisés judicieusement.
• Les synonymes facilitent l’accès aux objets en masquant leur nom ou leur schéma.