SQL : Langage de Définition de Données (DDL)

Le Langage SQL : Langage de Définition de Données

• Le langage SQL permet la définition de données, la manipulation de données, l'interrogation de données, et le contrôle de données.
• DDL (Data Definition Language) est un langage de définition de données.
• DDL permet la création, la modification, et la suppression de structures de données.

Table

• La création, la modification, la suppression et le renommage de tables sont couverts.

Syntaxe de Création de Table

• La commande CREATE TABLE est utilisée pour créer une nouvelle table.
• Syntaxe : CREATE TABLE [schema.]<nom_table> (<nom_colonne> type [DEFAULT expr], ...);
• Exemple : CREATE TABLE Etudiants (Netudiant number, nom varchar2(10), prenom varchar2(10));
• La commande DESCRIBE (ou DESC) affiche la description de la table. Par exemple: DESCRIBE Etudiants;

Types de Données

• Les types de données incluent CHAR, NCHAR, VARCHAR2, NVARCHAR2, NUMBER, BINARY_FLOAT, BINARY_DOUBLE, LONG, DATE, et TIMESTAMP.
• CHAR et NCHAR ont une taille fixe entre 1 et 2000.
• VARCHAR2 et NVARCHAR2 ont une taille variable entre 1 et 4000.
• NUMBER a une précision comprise entre 1 et 38, et une échelle variant de -84 à 127.
• BINARY_FLOAT est un nombre 32 bits à virgule flottante nécessitant 5 octets.
• BINARY_DOUBLE est un nombre 64 bits à virgule flottante nécessitant 9 octets.
• LONG permet des données caractères ayant une taille inférieure ou égale à 2GO.
• DATE comprend des dates entre le 1/1/4712 AJC et le 31/12/999 APJC.
• TIMESTAMP inclut l'année, le mois, le jour, l'heure, la minute, la seconde et la fraction de seconde.

Création de Table à partir d'une Sous-Interrogation

• Syntaxe: CREATE TABLE [schema.]<nom_table> (<nom_colonne> type [DEFAULT expr], ...) AS sous_interrogation;
• Exemple: CREATE TABLE emp AS SELECT * FROM employees WHERE department_id = 20;

Les Contraintes

• Les cinq types de contraintes sont: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, et FOREIGN KEY.
• Il existe deux niveaux de définition de contraintes: au niveau de la colonne et au niveau de la table.
• Syntaxe: CREATE TABLE [schema.]table (nom_colonne type [DEFAULT expr] [contrainte_niveau_colonne], [contrainte_niveau_colonne][,...]);
• Les contraintes peuvent être définies lors de la création de la table ou après.

Contrainte NOT NULL

• La contrainte NOT NULL ne peut être définie qu'au niveau de la colonne.
• Exemple: CREATE TABLE Fournisseurs (fournisseur_id number(10) NOT NULL, nom varchar2(20) NOT NULL, contact varchar2(50));

Contrainte UNIQUE

• Une contrainte d'intégrité de type unique exige que chaque valeur dans une colonne ou un ensemble de colonnes soit unique.
• Une contrainte unique autorise la valeur NULL, sauf indication contraire via des contraintes NOT NULL.
• 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

• La contrainte CHECK définit une condition que chaque ligne de la table doit vérifier.
• Les appels de fonction telle que SYSDATE et les interrogations qui font référence à d'autres valeurs dans d'autres lignes ne sont pas autorisés.
• Exemple au niveau colonne: 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

• Une contrainte clé primaire crée une clé primaire pour la table.
• Une seule clé primaire peut être créée par table.
• La clé primaire peut être constituée d'une ou plusieurs colonnes et aucune des colonnes faisant partie de la clé primaire ne peut être NULL.
• Exemple au niveau colonne: CREATE TABLE Fournisseurs (fournisseur_id number(10) PRIMARY KEY, nom varchar2(20) NOT NULL, contact varchar2(50));
• Exemple au niveau table: CREATE TABLE Etudiants(nom varchar2(30), prenom varchar2(30), date_naiss date, CONSTRAINT pk_etudiants PRIMARY KEY (nom, prenom));

Contrainte 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.

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

• Syntaxe 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,
      ...
  );
  

• Il est important de créer les tables parentes avant les tables filles et de supprimer les tables filles avant les tables parentes.

Options du mot-clé ON DELETE pour FOREIGN KEY

• 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. Applicable seulement si toutes les colonnes de clé étrangère de la table cible acceptent les valeurs NULL.
• SET DEFAULT: Place la valeur par défaut dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé. Cette option n'est pas prise en charge dans Oracle.
• RESTRICT: Indique une erreur en cas d'effacement d'une valeur correspondant à la clé. Cette option n'est pas prise en charge dans Oracle.

Options du mot-clé ON UPDATE pour FOREIGN KEY

• 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 option n'est applicable 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 dans la ligne de la table étrangère en cas de mise à jour d'une valeur correspondant à la clé.
• RESTRICT: Indique une erreur en cas de mise à jour d'une valeur correspondant à la clé.

Modification de Table

• L'instruction ALTER TABLE permet de modifier la structure de la table.
• Elle permet de faire ce qui suit :
  • ajouter une colonne (ADD)
  • modifier une colonne existante (MODIFY)
  • supprimer une colonne (DROP)
  • ajouter une contrainte (ADD CONSTRAINT)
  • supprimer une contrainte (DROP CONSTRAINT)

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

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

Supprimer une Colonne

• Syntaxe : ALTER TABLE <nom_table> DROP (column1, column2, ...);
• Exemple : ALTER TABLE Fournisseurs DROP (adresse, telephone);

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);
  • Pour ajouter une contrainte NULL/NOT NULL, on utilise l'option MODIFY.
  • Exemple : ALTER TABLE Fournisseurs MODIFY contact CONSTRAINT nn_fournisseurs_contact NOT NULL;

Supprimer une Contrainte

• Syntaxe : ALTER TABLE <nom_table> DROP CONSTRAINT <nom_contrainte>;
• Exemples :
  • ALTER TABLE Fournisseurs DROP CONSTRAINT ck_fournisseurs;
  • ALTER TABLE Fournisseurs DROP CONSTRAINT pk_fournisseurs;

Activer/Désactiver une Contrainte

• Syntaxe : ALTER TABLE <nom_table> ENABLE | DISABLE CONSTRAINT <nom_contrainte>;
• Permet d'activer ou de désactiver une contrainte existante.
• Après l'insertion de lignes, la contrainte peut être réactivée. La vérification des valeurs insérées se fait lors de l'activation de la contrainte.

Suppression de Table

• Syntaxe : DROP TABLE <nom_table>;
• La commande TRUNCATE TABLE nom_table; permet de vider la table.

Renommage de Table

• Syntaxe : RENAME <ancien_nom> TO <nouveau_nom>;
• Exemple : RENAME Fournisseurs TO LesFournisseurs;

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, pas les données.

• Elle permet de :

  • limiter l'accès à la base de données,
  • faciliter la création de requêtes complexes,
  • présenter les mêmes données sous différentes formes

• 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 d'instructions LMD (UPDATE, DELETE, INSERT).

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 même si les tables n'existent pas.
• alias indique les noms des expressions sélectionnées par la requête de la vue.
• WITH CHECK OPTION n'autorise l'insertion et la mise à jour des lignes que pour les lignes auxquelles la vue peut accéder (vérification des conditions de la clause WHERE).
• CONSTRAINT donne un nom de contrainte aux restrictions WITH CHECK OPTION et WITH READ ONLY.
• WITH READ ONLY interdit toute opération LMD.

Suppression de Vue

• Syntaxe : DROP VIEW <nom_vue>;

Séquence

• Génére automatiquement les numéros uniques.
• Est partageable entre plusieurs utilisateurs et éventuellement entre plusieurs tables.
• Permet de créer une valeur clé primaire.

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 est le premier numéro de la séquence.
• MAXVALUE | NOMAXVALUE définit la valeur maximale.
• MINVALUE | NOMINVALUE définit la valeur minimale.
• CYCLE | NOCYCLE détermine si la séquence continue ou non à générer des valeurs si la valeur maximale ou minimale est atteinte.
• CACHE | NOCACHE spécifie le nombre de valeurs pré-allouées et conservées en mémoire.
• La valeur du cache doit être inférieure ou égale au nombre de valeurs d'un cycle.

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

Utilisation de Séquence

• L'utilisation d'une séquence se fait par les pseudo-colonnes CURRVAL et NEXTVAL.
• CURRVAL retourne la valeur courante de la séquence.
• NEXTVAL incrémente la séquence et retourne la nouvelle valeur.

Suppression de Séquence

• Syntaxe : DROP SEQUENCE <nom_sequence>;

Index

• Un index est un objet de la base de données qui permet d'accélérer la recherche des lignes et contient deux champs: la clé d'index et l'adresse du bloc de données contenant la clé.
• Il peut être créé juste après la création d'une table ou sur une table existante.
• Un index peut porter sur plusieurs colonnes.
• Les index sont indépendants logiquement et physiquement des tables qu'ils indexent.

Création d'Index

• La création d'index peut être automatique (lors de la définition d'une contrainte PRIMARY KEY ou UNIQUE) ou manuelle.
• Syntaxe :

  CREATE [UNIQUE] INDEX <nom_index>
  ON <nom_table>(col1, col2,...);
  

• Un index unique ne permet pas de valeurs dupliquées dans la colonne indexée.
• Il est possible de renommer un index avec ALTER INDEX <ancien_nom> RENAME TO <nouveau_nom>;.
• Créer un index si :
  • la colonne est 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éer d'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.

Suppression d'Index

• Syntaxe : DROP INDEX <nom_index>;

Synonyme

• Un synonyme est un alias sur un objet de la base ou un schéma (sorte de raccourci).
• L'objet peut être une table, une vue, une séquence, une procédure, une fonction, un package, etc.
• Le synonyme peut être public (accessible à tous les schémas et utilisateurs) ou privé (accessible uniquement à partir du schéma dans lequel il a été créé).
• Les synonymes sont créés pour masquer le vrai nom des objets, simplifier les noms des objets, et éviter le préfixage dans les requêtes avec le nom de son propriétaire.

Création et Suppression de Synonyme

• Syntaxes:
  • Création : CREATE [OR REPLACE] [PUBLIC] SYNONYM <nom_synonyme> FOR [schéma.]<nom_objet>;
  • Suppression : DROP SYNONYM <nom_synonyme>;