SQL : définition de données

Troisième partie sur le langage SQL : les requêtes de définition et gestion des tables d'une base de données.

I. Présentation

1) Définition

Le langage de définition de données permet de définir la structure des tables. Trois actions sont possibles :

• Création de tables, vues et contraintes.
• Modification des tables, vues et contraintes.
• Suppression des tables, vues et contraintes.

2) Modèle relationnel pour les exemples

Service (IdServ, NomServ, LieuServ)
- Clé primaire : Id_Serv

Employe (IdEmp, NomEmp, FonctionEmp, DateEmbaucheEmp, SalaireEmp, CommissionEmp, ServiceEmp, SuperieurEmp)
- Clé primaire : IdEmp
- Clé étrangère : ServiceEmp en référence à IdServ de Service

II. La création de table

1) L'instruction CREATE TABLE

a) Présentation

CREATE TABLE Nomtable (
  Champ1 TypeDonnées1,
  Champ2 TypeDonnées2,
  …
);

b) Les types de donnée

c) Exemple

CREATE TABLE Service (
  NumServ INT,
  NomServ VARCHAR(25),
  LieuServ VARCHAR(20)
);

2) La création des clés

a) Rappel

• Clé primaire : Champ ou groupe de champs dont chaque valeur est associée à un et un seul enregistrement de la table, unique, et ne peut donc pas être de valeur NULL.
• Clé étrangère : Champ ou groupe de champs (mais plus rare) permettant d'associer des enregistrements, le plus souvent de tables différentes (fait généralement référence à la clé primaire d'une autre table).

b) Définition

Une clé se définit généralement en même temps que la table (lors de l'utilisation de CREATE TABLE). Mais il est également possible de définir les clés après la création de tous les champs de la table. Elles peuvent aussi être également modifiées plus tard.

CREATE TABLE Nomtable (
chp1 TypeDonnées1,
chp2 TypeDonnées2,
…
PRIMARY KEY (chp1 [, chp2,...]),
FOREIGN KEY (chp3 [, chp4] REFERENCES TableRef1(ChpRef1)[,
FOREIGN KEY chp5 [, chp6] REFERENCES TableRef2(ChpRef2),...]
);

c) Exemple

CREATE TABLE Employe (
  IdEmp INT,
  NomEmp VARCHAR(15),
  FonctionEmp VARCHAR(15),
  DateEmbaucheEmp DATE,
  SalaireEmp DECIMAL(7,2),
  CommissionEmp DECIMAL(7,2),
  ServiceEmp INT,
  SuperieurEmp INT,
  PRIMARY KEY (IdEmp),
  FOREIGN KEY (ServiceEmp) REFERENCES Service(NumServ),
  FOREIGN KEY (SuperieurEmp) REFERENCES Employe(IdEmp)
);

3) Les autres caractéristiques de champs

a) Caractéristiques possibles

Des caractéristiques peuvent être indiquées lors de la définition des champs, à la suite du type de données :

• NULL et NOT NULL : indique si le champ doit être obligatoirement défini ou non.
• UNIQUE : impose que tous les enregistrements aient une valeur différente pour un champ (sans qu'il soit obligatoirement une clé primaire).
• DEFAULT : indique la valeur par défaut prise par le champ à la création d'un nouvel enregistrement ; il peut d'agir d'une constante numérique ou alphanumérique (sous forme de chaîne de caractères), ou du mot clé Null.
• CHECK : précise une condition que doit remplir toute valeur saisie du champ (cette condition peut être définie à l'aide d'une sous-requête d'interrogation)
• AUTO_INCREMENT (sous MySQL) : permet d'auto-incrémenter un champ numérique entier.

b) Exemple

CREATE TABLE Employe (
  IdEmp INT NOT NULL AUTO_INCREMENT,
  NomEmp VARCHAR(15) NOT NULL,
  FonctionEmp VARCHAR(15),
  DateEmbaucheEmp DATE,
  SalaireEmp DECIMAL(7,2) CHECK(salaire >0),
  CommissionEmp DECIMAL(7,2) DEFAULT NULL,
  ServiceEmp INT,
  SuperieurEmp INT,
  PRIMARY KEY (IdEmp),
  FOREIGN KEY (ServiceEmp) REFERENCES Service(NumServ),
  FOREIGN KEY (SuperieurEmp) REFERENCES Employe(IdEmp)
);

4) Les contraintes

a) Présentation

Il est possible d’attribuer un nom par défaut aux contraintes PRIMARY KEY, UNIQUE, CHECK et FOREIGN par le SGBD. Ces noms vont s'afficher en cas d'erreur sur ces contraintes. On définit ces noms de contrainte en plaçant CONSTRAINT NomContrainte devant la définition de la contrainte.

b) Exemple

CREATE TABLE Employe (
  IdEmp integer NOT NULL AUTO_INCREMENT,
  NomEmp varchar(15) NOT NULL,
  FonctionEmp VARCHAR(15),
  DateEmbaucheEmp DATE,
  SalaireEmp DECIMAL(7,2) CHECK(salaire >0),
  CommissionEmp DECIMAL(7,2) DEFAULT NULL,
  ServiceEmp INTEGER,
  SuperieurEmp INTEGER,
  CONSTRAINT ClePrimEmp PRIMARY KEY (IdEmp),
  CONSTRAINT CleServ FOREIGN KEY (ServiceEmp) REFERENCES Service(NumServ),
  CONSTRAINT CleSuperieur FOREIGN KEY (SuperieurEmp) REFERENCES Employe(IdEmp)
);

5) Insertion de lignes à la création

Il est possible de créer une table en insérant directement des lignes lors de la création. Ces lignes à insérer peuvent être récupérées d'une table existante grâce aux instructions AS SELECT.

CREATE TABLE Nomtable (
  Champ1 TypeDonnées1,
  Champ2 TypeDonnées2,
  …
) AS SELECT...
     FROM...
     [WHERE... GROUP BY... HAVING... ORDER BY...];

III. Les vues

1) Définition

Une vue est une table virtuelle qui stocke les enregistrements résultat d'une requête d'interrogation afin qu'ils soient en permanence disponibles. Pour ces tables, on a la possibilité de renommer les colonnes. Une fois la vue créée, elle est utilisable comme n'importe quelle table.

CREATE VIEW NomVue [(NomChp1, NomChp2,...)]
AS SELECT...
FROM...
[WHERE... GROUP BY... HAVING... ORDER BY...];

2) Utilité des vues

L'utilisation des vues permet de fournir aux utilisateurs des données correspondant à leurs besoins :

• Sans qu'ils aient à réaliser des requêtes parfois complexes ;
• En leur délivrant les données, y compris calculées, qui leur sont nécessaires ;
• Sans leur donner accès à des données qui leur sont inutiles, voir confidentielles.

3) Exemple

CREATE VIEW SubordonnésDe27047
AS SELECT Employe.*
FROM Employe
WHERE SuperieurEmp = 27047;

IV. Suppression d'une table ou d'une vue

DROP TABLE NomTable;

DROP VIEW NomVue;

V. Modification de tables

1) Ajout de champs ou de contraintes à une table

a) ALTER... ADD

Pour ajouter un ou plusieurs champs :

ALTER TABLE NomTable
ADD Chp1 TypeChp1 [,...];

Pour ajouter une contrainte :

ALTER TABLE NomTable
ADD FOREIGN KEY chp1 REFERENCES TableRef(ChpRef);

b) Exemple

ALTER TABLE Service
ADD BudgetServ DECIMAL(7,2) CHECK(BudgetServ>0);

2) Suppression de champs ou de contraintes d'une table

a) ALTER... DROP

Pour supprimer un ou plusieurs champs (possible seulement si le champ ne fait partie d'une vue ou d'un index et qu'il n'est pas l'objet d'une contrainte d'intégrité) :

ALTER TABLE NomTable
DROP Chp1 [,...];

Pour supprimer une contrainte :

ALTER TABLE NomTable
DROP PRIMARY KEY NomContrainte;

ALTER TABLE NomTable
DROP FOREIGN KEY NomContrainte;

b) Exemple

ALTER TABLE Service
DROP BudgetServ;

3) Modification de champs d'une table

a) ALTER... CHANGE / ALTER... MODIFY

ALTER associé à CHANGE (avec renommage du champ) ou MODIFY (sans renommage du champ) modifie le type ou autres caractéristiques éventuelles du champ.

ALTER TABLE NomTable
CHANGE AncienNomChp NouveauNomChp TypeDonnées,...;

ALTER TABLE NomTable
MODIFY Champ TypeDonnées,...;

Attention : en modifiant des champs, il peut y avoir des incompatibilités pour les données déjà saisies.

b) Exemples

ALTER TABLE Service
CHANGE LieuServ CPServ integer;

ALTER TABLE Employe
MODIFY CommissionEmp float;