Documentation technique

B04 - SQL - créer la structure (DDL)

4 min de lecture

B04 - SQL : créer la structure (DDL)

Objectif

Créer une base, des tables avec les bons types et les contraintes, les modifier, ajouter des index et des vues. Dialecte principal : T-SQL (SQL Server), avec les différences MariaDB/MySQL. Cours d’origine : Principe de base de la création de BDD.

1. Les familles de commandes SQL

FamilleRôleCommandes
DDL (Data Definition)StructureCREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation)DonnéesSELECT, INSERT, UPDATE, DELETE → B05 - SQL - interroger et modifier les données
DCL (Data Control)DroitsGRANT, REVOKE, DENY
TCL (Transaction Control)TransactionsBEGIN TRAN, COMMIT, ROLLBACK

2. Base de données

CREATE DATABASE GestionParc;
GO                       -- (T-SQL) sépare les lots d'instructions dans SSMS / sqlcmd / VS Code
USE GestionParc;
GO
DROP DATABASE IF EXISTS GestionParc;   -- supprimer (⚠ irréversible)

3. Les types de données (SQL Server)

CatégorieTypeUsage / remarque
EntiersTINYINT (0-255), SMALLINT, INT, BIGINTIdentifiants, quantités
Décimaux exactsDECIMAL(p, s) / NUMERICArgent, mesures précises : DECIMAL(10,2) = 8 chiffres avant la virgule, 2 après
Décimaux approchésFLOAT, REALCalcul scientifique (jamais pour l’argent)
MonétaireMONEYPréférer DECIMAL
BooléenBIT0 / 1
Texte Unicode (recommandé)NCHAR(n), NVARCHAR(n), NVARCHAR(MAX)Accents, toutes langues - préfixer les littéraux par N'…'
Texte non UnicodeCHAR(n), VARCHAR(n), VARCHAR(MAX)Codes, références ASCII
DatesDATE, TIME, DATETIME2, DATETIMEOFFSETDATETIME2 remplace DATETIME ; DATETIMEOFFSET garde le fuseau
BinaireVARBINARY(MAX)Fichiers (mieux : stocker le chemin du fichier)
Identifiant globalUNIQUEIDENTIFIER + NEWID()GUID
ObsolètesTEXT, NTEXT, IMAGERemplacés par (N)VARCHAR(MAX), VARBINARY(MAX)
PiègeTIMESTAMP / ROWVERSIONCe n’est pas une date : un compteur binaire de version de ligne
SQL ServerMariaDB / MySQL
INT IDENTITY(1,1)INT AUTO_INCREMENT
NVARCHAR(100)VARCHAR(100) (avec CHARSET utf8mb4)
BITBOOLEAN (= TINYINT(1))
DATETIME2DATETIME
NVARCHAR(MAX)TEXT / LONGTEXT
TOP 10LIMIT 10
GETDATE()NOW()
ISNULL(x, 0)IFNULL(x, 0) (ou COALESCE, standard, dans les deux)

4. CREATE TABLE avec toutes les contraintes

CREATE TABLE Salles (
    id_salle   INT IDENTITY(1,1) CONSTRAINT PK_Salles PRIMARY KEY,
    nom_salle  NVARCHAR(50) NOT NULL CONSTRAINT UQ_Salles_Nom UNIQUE,
    etage      TINYINT NOT NULL CONSTRAINT CK_Salles_Etage CHECK (etage BETWEEN 0 AND 5)
);
 
CREATE TABLE Postes (
    id_poste      INT IDENTITY(1,1) CONSTRAINT PK_Postes PRIMARY KEY,
    nom_poste     NVARCHAR(50)  NOT NULL,
    adresse_ip    VARCHAR(15)   NULL CONSTRAINT UQ_Postes_IP UNIQUE,
    prix_achat    DECIMAL(8,2)  NULL CONSTRAINT CK_Postes_Prix CHECK (prix_achat > 0),
    date_achat    DATE          NOT NULL CONSTRAINT DF_Postes_Date DEFAULT (CAST(GETDATE() AS DATE)),
    est_portable  BIT           NOT NULL CONSTRAINT DF_Postes_Portable DEFAULT 0,
    id_salle      INT           NOT NULL,
    CONSTRAINT FK_Postes_Salles FOREIGN KEY (id_salle)
        REFERENCES Salles(id_salle)
        ON DELETE NO ACTION           -- refuse de supprimer une salle qui contient des postes
        ON UPDATE CASCADE
);

Nomme tes contraintes (PK_…, FK_…, CK_…, UQ_…, DF_…) : les messages d'erreur deviennent lisibles et tu pourras les supprimer facilement (ALTER TABLE … DROP CONSTRAINT CK_Postes_Prix).

Contraintes CHECK plus riches (exercice Zoo)

CONSTRAINT CK_Animal_Id      CHECK (id_animal LIKE REPLICATE('[0-9]', 14)),          -- exactement 14 chiffres
CONSTRAINT CK_Animal_Dates   CHECK (date_naissance IS NULL OR date_naissance <= date_arrivee),
CONSTRAINT CK_Animal_Depart  CHECK (date_depart IS NULL OR date_depart > date_arrivee),
CONSTRAINT CK_Animal_Steril  CHECK (sterilise = 0 OR sexe IN ('M', 'F'))              -- pas stérilisé si sexe inconnu

Actions sur les clés étrangères

ActionQuand on supprime/modifie la ligne PARENTE…
NO ACTION (défaut)…c’est refusé s’il existe des lignes filles
CASCADE…les lignes filles sont supprimées / mises à jour aussi
SET NULL…la FK des filles passe à NULL (FK doit accepter NULL)
SET DEFAULT…la FK des filles prend sa valeur par défaut

5. Modifier une table : ALTER

ALTER TABLE Postes ADD numero_serie NVARCHAR(50) NULL;                   -- ajouter une colonne
ALTER TABLE Postes ALTER COLUMN nom_poste NVARCHAR(100) NOT NULL;        -- changer le type (T-SQL)
-- MariaDB : ALTER TABLE Postes MODIFY nom_poste VARCHAR(100) NOT NULL;
ALTER TABLE Postes DROP COLUMN numero_serie;                             -- supprimer
ALTER TABLE Postes ADD CONSTRAINT CK_Postes_Nom CHECK (LEN(nom_poste) >= 3);
ALTER TABLE Postes DROP CONSTRAINT CK_Postes_Nom;
EXEC sp_rename 'Postes.nom_poste', 'libelle_poste', 'COLUMN';            -- renommer (T-SQL)

6. Supprimer

CommandeEffet
DROP TABLE IF EXISTS Postes;Supprime la table et ses données
TRUNCATE TABLE Postes;Vide la table (rapide, remet l’IDENTITY à 1) - impossible si référencée par une FK
DELETE FROM Postes;Vide ligne par ligne (journalisé, respecte les FK)

Ordre : supprimer d’abord les tables filles (qui ont les FK), puis les parentes.

7. Index et vues

-- Index : accélère les recherches et jointures sur une colonne
CREATE INDEX IX_Postes_Salle ON Postes(id_salle);
CREATE UNIQUE INDEX UX_Postes_Serie ON Postes(numero_serie) WHERE numero_serie IS NOT NULL;  -- index filtré
 
-- Vue : une requête enregistrée, utilisable comme une table
CREATE OR ALTER VIEW v_PostesParSalle AS
SELECT s.nom_salle, COUNT(p.id_poste) AS nb_postes
FROM Salles s LEFT JOIN Postes p ON p.id_salle = s.id_salle
GROUP BY s.nom_salle;
GO
SELECT * FROM v_PostesParSalle;

Les clés primaires et contraintes UNIQUE créent automatiquement un index. Les clés étrangères non : indexe-les toi-même.

8. Droits (DCL) - principe

-- SQL Server
CREATE LOGIN app_web WITH PASSWORD = 'MotDePasse#Fort1';
CREATE USER app_web FOR LOGIN app_web;
GRANT SELECT, INSERT, UPDATE ON dbo.Postes TO app_web;
DENY DELETE ON dbo.Postes TO app_web;

MariaDB : B07 - MariaDB et MySQL.

Voir aussi

Vue Graphique

Liens retour