SQL

Le langage SQL (Structured Query Language) est un langage de programmation conçu pour gérer et manipuler les bases de données relationnelles.

➡️ MySQL 8.4 Reference Manual

Convention de nommage

Les noms des bases, tables et attributs s'écrivent :
- En anglais
- En minuscules
- En snake_case (séparation par _)
Les noms doivent être clairs, descriptifs et représenter correctement leur fonction.
Les noms de tables s'écrivent au singulier.
Les attributs doivent utiliser des noms courts, mais explicites.
Il faut éviter d'utiliser des mots clés et réservés SQL.
- MySQL - Keywords and Reserved Words
Les clés primaires portent généralement le nom id.

Types de données (MySQL)

MySQL prend en charge plusieurs familles de types de données : numériques, chaînes de caractères, date/heure, JSON, binaires et spatiaux.

➡️ MySQL - Data Types

NULL

NULL représente l'absence de valeur.

NULL n'est pas égal à 0.
NULL n'est pas une chaîne vide.
NULL signifie : "aucune valeur n'a été définie".

Création de tables

➡️ MySQL - CREATE TABLE Statement

La commande CREATE TABLE permet de créer une table dans une base de données MySQL.

Elle définit :

Le nom de la table
Les colonnes
Le type de données de chaque colonne
Les contraintes (clé primaire, clé étrangère, unicité, etc.)

create table employee
(
    id int primary key,
    first_name  varchar(50),
    last_name   varchar(50),
    department  varchar(50)
);

create table project
(
    id   varchar(10) primary key,
    name varchar(50)
);

create table skill
(
    id   int primary key,
    name varchar(50)
);

create table employee_skill
(
    employee_id int,
    skill_id    int,
    primary key (employee_id, skill_id)
);

create table employee_project
(
    employee_id int,
    project_id  varchar(10),
    primary key (employee_id, project_id)
);

Création des clés étrangères

➡️ MySQL - Using Foreign Keys

alter table employee_project
    add constraint employee_project_employee_id_fk
        foreign key (employee_id) references employee (id),
    add constraint employee_project_project_id_fk
        foreign key (project_id) references project (id);

alter table employee_skill
    add constraint employee_skill_employee_id_fk
        foreign key (employee_id) references employee (id),
    add constraint employee_skill_skill_id_fk
        foreign key (skill_id) references skill (id);

Restrictions sur les contraintes d'intégrité référentielle

Les restrictions appliquées aux clés étrangères définissent le comportement de MySQL lorsqu'un enregistrement référencé dans la table parent est modifié ou supprimé. Elles permettent de protéger la cohérence des données.

CASCADE

Si une ligne de la table parent est modifiée ou supprimée, la modification est répercutée automatiquement dans la table enfant.
Exemple : supprimer un projet supprime automatiquement toutes les lignes correspondantes dans employee_project.

SET NULL

Si la ligne référencée est supprimée ou modifiée, la clé étrangère dans la table enfant est remplacée par NULL.
Utile lorsque la relation devient simplement inexistante mais que l'enregistrement enfant doit être conservé.

SET DEFAULT

Fonctionne comme SET NULL, mais la clé étrangère est remplacée par une valeur par défaut.
Nécessite qu'une valeur DEFAULT soit définie sur la colonne.

NO ACTION / RESTRICT

Empêche immédiatement la suppression ou modification d'une ligne parent si elle est encore référencée dans une table enfant.

Note

Dans MySQL, si aucune action n'est explicitement définie lors de la création d'une clé étrangère, les options par défaut sont :

ON UPDATE RESTRICT
ON DELETE RESTRICT

En pratique :

ON UPDATE CASCADE est utilisé lorsque la clé primaire d'une table peut exceptionnellement être modifiée. Cela reste rare car une clé primaire devrait normalement être immuable.
ON DELETE RESTRICT est le comportement le plus courant : il empêche la suppression accidentelle d'un enregistrement parent encore utilisé dans des tables enfants.

Cardinalité

Cardinalité Un-à-Un (1:1)

Pour créer une relation de cardinalité Un-à-Un (1:1) entre deux tables, il suffit de définir une clé primaire dans chaque table et d'ajouter une clé étrangère dans l'une des deux tables. Cette clé étrangère doit être unique, afin d'empêcher plusieurs lignes d'établir la même relation.

Exemple : Dans un système où chaque employé possède un seul et unique badge, et où chaque badge appartient à un seul employé, la relation entre employee et badge est de type 1:1.

Table : employee

create table employee (
    id int primary key,
    name varchar(50),
    position varchar(50)
);

Table : badge

create table badge (
    id varchar(10) primary key,
    employee_id int unique,
    access_code varchar(50),
    foreign key (employee_id) references employee(id)
);

Explications

La table employee possède une clé primaire id.
La table badge possède :
- Sa propre clé primaire id
- Une colonne employee_id marquée UNIQUE
- Une clé étrangère reliant employee_id → employee(id)

La contrainte UNIQUE sur employee_id garantit que :

Un employé ne peut être lié qu'à un seul badge
Un badge ne peut référencer qu'un seul employé
La relation est donc bien de cardinalité 1:1

Cardinalité Un-à-Plusieurs (1:N)

Pour mettre en place une relation de cardinalité Un-à-Plusieurs (1:N), il suffit de définir une clé primaire dans la table parent et d'ajouter une clé étrangère dans la table enfant, qui fait référence à cette clé primaire.

Exemple : Dans une bibliothèque, chaque livre appartient à une seule catégorie, tandis qu'une catégorie peut contenir plusieurs livres. La relation entre category et book est donc de type 1:N.

Table : category

create table category (
    id int primary key,
    name varchar(50)
);

Table : book

create table book (
    id varchar(10) primary key,
    title varchar(100),
    category_id int,
    foreign key (category_id) references category(id)
);

Explications

La table category possède une clé primaire id et stocke les différentes catégories.
La table book possède :
- Une clé primaire id
- Une colonne category_id utilisée comme clé étrangère
- Une contrainte foreign key (category_id) references category(id)

Ainsi :

Chaque livre appartient à une seule catégorie
Chaque catégorie peut contenir plusieurs livres
Ce qui correspond exactement à une relation 1:N.

Cardinalité Plusieurs-à-Plusieurs (N:N)

Pour créer une relation de cardinalité Plusieurs-à-Plusieurs (N:N) entre deux tables, il est nécessaire d'utiliser une table d'association. Cette table intermédiaire contient une clé étrangère vers chacune des deux tables et utilise généralement une clé primaire composite formée de ces deux clés étrangères.

Exemple : Dans une université, un étudiant peut suivre plusieurs cours, et chaque cours peut être suivi par plusieurs étudiants. Cette situation correspond à une relation N:N entre student et course.

Table : student

create table student (
    id int primary key,
    name varchar(50)
);

Table : course

create table course (
    id varchar(10) primary key,
    name varchar(50)
);

Table d'association : enrollment

create table enrollment (
    student_id int,
    course_id varchar(10),
    primary key (student_id, course_id),
    foreign key (student_id) references student(id),
    foreign key (course_id) references course(id)
);

Explications

La table student définit la liste des étudiants.
La table course définit la liste des cours proposés.
La table enrollment :
- Contient deux colonnes (student_id, course_id) servant de clé primaire composite
- Etablit deux clés étrangères pointant respectivement vers student(id) et course(id)

Grâce à cette structure :

Un étudiant peut être lié à plusieurs cours
Un cours peut être lié à plusieurs étudiants
La relation est donc correctement modélisée en N:N.

Cardinalité optionnelle vs obligatoire

Les cardinalités optionnelle et obligatoire précisent si une relation entre deux tables doit impérativement exister ou si elle peut être absente.

Contexte : Chaque employé appartient à un département, mais certains départements peuvent ne pas avoir d'employés. On souhaite donc distinguer deux situations possibles dans la conception de la base de données.

Cardinalité obligatoire (1:1 ou 1:N)

Dans une relation obligatoire, chaque employé doit appartenir à un département. Cela implique que la colonne de clé étrangère dans la table employee ne peut pas être nulle (NOT NULL).

Table : department

create table department (
    id int primary key,
    name varchar(50)
);

id	name
1	Ressources Humaines
2	IT
3	Marketing

Table : employee

create table employee (
    id int primary key,
    name varchar(50),
    department_id int not null,
    foreign key (department_id) references department(id)
);

id	name	department_id
101	Alice Dupont	2
102	Bob Martin	1
103	Claire Lune	3

→ Dans ce modèle, aucun employé ne peut être ajouté sans département.

Cardinalité optionnelle (0:1 ou 0:N)

Dans une relation optionnelle, un employé peut exister sans être affecté à un département. La colonne de clé étrangère peut donc contenir la valeur NULL.

Table : department

create table department (
    id int primary key,
    name varchar(50)
);

id	name
1	Ressources Humaines
2	IT
3	Marketing

Table : employee

create table employee (
    id int primary key,
    name varchar(50),
    department_id int,
    foreign key (department_id) references department(id)
);

id	name	department_id
101	Alice Dupont	2
102	Bob Martin	NULL
103	Claire Lune	1

ID auto-incrémentés

Un ID auto-incrémenté est un entier généré automatiquement par MySQL lors de l'insertion d'une nouvelle ligne. Il est couramment utilisé comme clé primaire.

Avantages

Unicité garantie

Chaque enregistrement reçoit automatiquement un identifiant unique.
Simplicité

Pas besoin de gérer manuellement les ID lors des insertions.
Performance

Très efficace pour les index et opérations de recherche.

create table user (
    id int auto_increment primary key,
    name varchar(50),
    email varchar(254)
);

Dans cet exemple :

id est la clé primaire
Il s'incrémente automatiquement à chaque nouvel enregistrement
Si l'on insère un utilisateur sans préciser id, MySQL génère la valeur suivante.