SLAM / Développement / SQL : LMD et LDD

SQL : LMD et LDD

Après le LID (interrogation) vu en B1, les deux autres familles : manipuler les lignes, et définir la structure.

1. Les familles du SQL

Les contraintes d'intégrité (clé primaire, clé étrangère) assurent la cohérence des données. À chaque manipulation, le SGBD contrôle leur respect — et refuse l'opération si elle les viole.

2. INSERT INTO

INSERT INTO <table> [(liste_des_champs)]
VALUES (<liste_des_valeurs>);

• La liste des champs est facultative. Si elle est omise, ce sont toutes les colonnes, dans l'ordre de création de la table.
• Le nombre de valeurs doit être égal au nombre de champs.
• La valeur null est attribuée aux champs non renseignés.
• Les valeurs doivent respecter les types et l'ordre des champs.

INSERT INTO candidat
VALUES (1207, 'MARTIN', 'Lola', '02/05/2004', 03);

INSERT INTO candidat (numCandidat, nomCandidat, codeEtab)
VALUES (1208, 'MUBO', 01);

INSERT INTO candidat
VALUES (1209, 'PLOC', 'Bill', null, 03);

Omettre la liste des champs rend la requête fragile : le jour où une colonne est ajoutée par ALTER TABLE, toutes les insertions sans liste explicite cassent. On la précise donc systématiquement en production.

3. UPDATE

UPDATE <table>
SET <champ1> = <expression1> [, <champ2> = <expression2>, ...]
[WHERE <condition>];

L'expression peut être une valeur fixe, un calcul, une référence à l'ancienne valeur du champ, ou le résultat d'une requête.

-- Augmente toutes les notes de 1 point
UPDATE obtenir
SET note = note + 1;

-- Deux champs, une seule ligne ciblée
UPDATE candidat
SET nomCandidat = 'MARTINEZ', naissCandidat = '02/05/2003'
WHERE numCandidat = 1207;

-- La valeur provient d'une sous-requête
UPDATE candidat
SET codeEtab = (SELECT codeEtab FROM candidat WHERE numCandidat = 1206)
WHERE numCandidat = 1209;

Sans WHERE, toutes les lignes de la table sont mises à jour. Réflexe de sécurité : écrire d'abord la requête en SELECT avec la même clause WHERE, vérifier les lignes retournées, puis seulement transformer en UPDATE.

4. DELETE

DELETE FROM <table>
[WHERE <condition>];

DELETE FROM obtenir;                        -- vide entièrement la table
DELETE FROM obtenir WHERE numCandidat = 1206;   -- les notes d'un seul candidat

Même avertissement que pour UPDATE : en l'absence de WHERE, toutes les lignes disparaissent. Et une ligne référencée par une clé étrangère ne peut pas être supprimée tant que les lignes « enfants » existent — c'est l'intégrité référentielle qui joue son rôle.

5. CREATE TABLE

Créer une table, c'est définir ses colonnes (nom, type, valeur par défaut) et ses contraintes d'intégrité.

CREATE TABLE <nom_table>
(
    <colonne1> <type> [<contraintes de colonne>],
    <colonne2> <type> [<contraintes de colonne>],
    ...
    [contraintes de table]
);

CREATE TABLE etablissement
(
    codeEtab  VARCHAR(2),
    nomEtab   VARCHAR(40) NOT NULL,
    villeEtab VARCHAR(30),
    CONSTRAINT pk_etab PRIMARY KEY(codeEtab)
);

CREATE TABLE epreuve
(
    codeEpreuve     VARCHAR(2),
    intituleEpreuve VARCHAR(25) UNIQUE NOT NULL,
    coeffEpreuve    INTEGER,
    CONSTRAINT chk_epreuve CHECK(coeffEpreuve >= 1),
    CONSTRAINT pk_epreuve  PRIMARY KEY(codeEpreuve)
);

CREATE TABLE candidat
(
    numCandidat    NUMBER(4),
    nomCandidat    VARCHAR(30),
    prenomCandidat VARCHAR(20),
    naissCandidat  DATE,
    codeEtab       VARCHAR(2) NOT NULL,
    CONSTRAINT pk_candidat PRIMARY KEY(numCandidat),
    CONSTRAINT fk_candidat FOREIGN KEY(codeEtab) REFERENCES etablissement(codeEtab)
);

CREATE TABLE obtenir
(
    numCandidat NUMBER(4),
    codeEpreuve VARCHAR(2),
    note        DECIMAL(4,2) CHECK(note BETWEEN 0 AND 20),
    CONSTRAINT pk_obtenir    PRIMARY KEY(numCandidat, codeEpreuve),
    CONSTRAINT fk_obtenir_C  FOREIGN KEY(numCandidat) REFERENCES candidat(numCandidat),
    CONSTRAINT fk_obtenir_E  FOREIGN KEY(codeEpreuve) REFERENCES epreuve(codeEpreuve)
);

L'ordre de création compte : une table référencée doit exister avant celle qui la référence. Ici etablissement et epreuve avant candidat, puis obtenir.

6. Types de données

Les types varient d'un SGBD à l'autre.

CHAR(10) occupe toujours 10 caractères, complétés par des espaces : adapté à une donnée de longueur constante (code pays, code postal). VARCHAR(10) n'occupe que la place utile : à préférer dès que la longueur varie.

7. Contraintes d'intégrité

Nommer explicitement les contraintes avec CONSTRAINT <nom> n'est pas cosmétique : c'est ce qui permet ensuite de les supprimer ou de les modifier, et cela rend les messages d'erreur du SGBD lisibles. Une clé primaire composée de plusieurs colonnes ne peut d'ailleurs s'écrire qu'en contrainte de table.

8. ALTER TABLE et DROP TABLE

ALTER TABLE permet d'ajouter ou modifier une colonne, de la renommer, d'ajouter ou de supprimer une contrainte de table.

ALTER TABLE candidat ADD mail VARCHAR(50) UNIQUE;
ALTER TABLE candidat ALTER COLUMN mail TYPE VARCHAR(60);
ALTER TABLE candidat CHANGE mail TO email;
ALTER TABLE epreuve DROP CONSTRAINT chk_epreuve;

DROP TABLE supprime la table et toutes les données qu'elle contient.

DROP TABLE <nom_table> [CASCADE CONSTRAINTS];

DROP TABLE obtenir;
DROP TABLE etablissement CASCADE CONSTRAINTS;

Si la clé primaire de la table est référencée ailleurs, CASCADE CONSTRAINTS supprime ces contraintes dans les tables enfants. Sans cette clause, il est impossible de supprimer une table référencée par d'autres — ce qui est une protection, pas un obstacle.