SQL

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

➡️ MySQL 8.4 Reference Manual

Convention de nommage

Les noms des bases de données, des tables et des attributs s'écrivent uniquement :
- En anglais
- En minuscule
- En utilisant le "snake_case", donc un _ (underscore) pour la séparation des termes.
Les noms des tables et des attributs doivent clairement indiquer la nature et la fonction de l'élément.
Les noms des tables s'écrivent au singulier.
Utiliser des noms courts mais descriptifs pour les attributs.
Éviter d'utiliser les mots réservés de SQL dans les noms des tables et des attributs :
- MySQL - Keywords and Reserved Words
Les clés primaires sont souvent nommées id.

Type de données dans MySQL

MySQL prend en charge les types de données SQL dans plusieurs catégories : les types numériques, les types de date et d'heure, les types de chaînes (caractères et octets), les types spatiaux et le type de données JSON.

➡️ MySQL - Data Types

Données numériques

Type de données	Taille	Description
TINYINT	1 octet	Entier très petit. Signé : -128 à 127. Non signé : 0 à 255.
SMALLINT	2 octets	Petit entier. Signé : -32'768 à 32'767. Non signé : 0 à 65'535.
MEDIUMINT	3 octets	Entier moyen. Signé : -8'388'608 à 8'388'607. Non signé : 0 à 16'777'215.
INT ou INTEGER	4 octets	Entier standard. Signé : -2'147'483'648 à 2'147'483'647. Non signé : 0 à 4'294'967'295.
BIGINT	8 octets	Grand entier. Signé : -2^63 à 2^63-1. Non signé : 0 à 2^64-1.
FLOAT	4 octets	Nombre à virgule flottante. Taille variable.
DOUBLE	8 octets	Nombre à double précision. Taille variable.
DECIMAL ou NUMERIC	Taille variable	Nombre à virgule fixe avec précision et échelle.

Données numériques binaires

Type de données	Taille	Description
BIT	1 à 64 bits	Type de données utilisé pour stocker des valeurs binaires. La taille peut être spécifiée pour indiquer le nombre de bits que chaque valeur peut contenir.

Date et heure

Type de données	Format	Description
DATE	'YYYY-MM-DD'	Date.
TIME	'HH:MM:SS'	Heure.
DATETIME	'YYYY-MM-DD HH:MM:SS'	Date et heure.
TIMESTAMP	'YYYY-MM-DD HH:MM:SS'	Date et heure, avec fuseau horaire.
YEAR	Année sur 2 ou 4 chiffres	Année.

Chaîne de caractères

Type de données	Taille	Description
CHAR	0 à 255 caractères	Chaîne de taille fixe.
VARCHAR	0 à 65'535 caractères	Chaîne de taille variable.
BLOB ou TEXT	0 à 65'535 octets	Objet binaire ou texte de taille variable.
MEDIUMBLOB ou MEDIUMTEXT	0 à 16'777'215 octets	Objet binaire ou texte de taille moyenne.
LONGBLOB ou LONGTEXT	0 à 4'294'967'295 octets	Objet binaire ou texte de grande taille.

Note

Le nombre maximal effectif de caractères pouvant être stockés dans une colonne VARCHAR est soumis à la taille de ligne maximale de 65'535 octets, qui est partagée entre toutes les colonnes. Pour une colonne VARCHAR qui stocke des caractères multi-octets (UTF-8), le nombre maximal effectif de caractères est inférieur.

Données binaires

Type de données	Taille	Description
BINARY	0 à 255 octets	Chaîne de taille fixe, contenant des données binaires brutes.
VARBINARY	0 à 65'535 octets	Chaîne binaire de taille variable.

Données énumérées et définies

Type de données	Description
ENUM	Une liste d'options parmi lesquelles une valeur est choisie.
SET	Un ensemble de zéro ou plusieurs valeurs choisies parmi une liste.

NULL

Dans MySQL, et plus généralement dans le contexte des bases de données relationnelles, NULL est une valeur spéciale qui représente l'absence de valeur. En d'autres termes, lorsque vous rencontrez NULL dans une base de données, cela signifie qu'aucune valeur réelle n'est attribuée dans cette cellule.

Création de tables

➡️ MySQL - CREATE TABLE Statement

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 sur les contraintes d'intégrité référentielle dans les bases de données relationnelles sont des règles appliquées pour définir ce qui doit se produire lorsqu'une clé étrangère référencée est modifiée ou supprimée. Ces restrictions sont essentielles pour maintenir des liens cohérents entre les tables.

CASCADE

Lorsqu'une opération de mise à jour ou de suppression est effectuée sur la clé primaire d'une table, la même opération est automatiquement appliquée à toutes les entrées correspondantes dans la table liée par la clé étrangère.
Exemple : Si un enregistrement dans la table parent est supprimé, tous les enregistrements dans la table enfant qui référencent la clé primaire supprimée seront également supprimés.

SET NULL

Lors de la suppression ou de la mise à jour d'un enregistrement dans la table parent, toutes les valeurs de clé étrangère correspondantes dans la table enfant sont définies sur NULL.
Cela est souvent utilisé lorsque les enregistrements de la table enfant ne doivent pas être supprimés, mais plutôt marqués comme n'ayant plus de relation valide.

SET DEFAULT

Similaire à SET NULL, mais au lieu de définir la clé étrangère sur NULL, elle est définie sur une valeur par défaut définie lors de la création de la contrainte.
Cela nécessite qu'une valeur par défaut soit définie pour cette colonne.

NO ACTION

Indique qu'aucune action spéciale ne doit être effectuée lors de la mise à jour ou de la suppression d'un enregistrement lié.
En pratique, cela signifie qu'une erreur est générée si une opération de mise à jour ou de suppression violerait l'intégrité référentielle.

RESTRICT

Semblable à NO ACTION en ce sens qu'elle empêche la modification ou la suppression d'un enregistrement dans la table parent si des références existent encore dans la table enfant.
La différence est que RESTRICT est immédiatement vérifiée, contrairement à NO ACTION qui est vérifiée à la fin de la transaction.

Note

Dans MySQL, lorsque vous créez des clés étrangères, le comportement par défaut (si non spécifié) pour les actions ON UPDATE et ON DELETE est RESTRICT.

Dans la pratique, les valeurs les plus couramment utilisées pour les comportements ON UPDATE et ON DELETE sont :

ON UPDATE CASCADE : Ce comportement est logique dans des situations où les modifications apportées aux clés primaires dans la table parent doivent se répercuter automatiquement dans les tables enfant. Cependant, dans la pratique, la mise à jour des clés primaires est assez rare car elles sont généralement conçues pour être des identifiants immuables. Si les clés primaires changent fréquemment, cela pourrait indiquer un problème de conception de la base de données.
ON DELETE RESTRICT : RESTRICT est le comportement par défaut et il est très courant, car il protège l'intégrité des données en empêchant la suppression de données dans une table parent si des références existent dans une table enfant. Cette approche conservatrice est souvent préférable pour éviter des suppressions accidentelles qui pourraient avoir des effets en cascade indésirables.

Cardinalité

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

Pour créer une relation de cardinalité Un-à-Un (1:1) entre deux tables, vous pouvez définir une clé primaire dans chaque table et une clé étrangère dans l'une des tables qui fait référence à la clé primaire de l'autre table.

Exemple : Supposons que nous ayons un système de gestion d'employés où chaque employé a un unique badge d'accès. Chaque badge est attribué à un seul employé. Cela crée une relation 1‑1 entre les employés et les badges d'accès.

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

La table employee a une clé primaire id.
La table badge a une clé primaire id et une clé étrangère employee_id qui fait référence à id dans la table employee.
La contrainte UNIQUE sur employee_id dans la table badge garantit que chaque employé est associé à au plus un badge, préservant ainsi la relation 1:1.

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

Pour créer une relation de cardinalité Un-à-Plusieurs (1:N) entre deux tables, vous pouvez définir une clé primaire dans chaque table et une clé étrangère dans l'une des tables qui fait référence à la clé primaire de l'autre table.

Exemple : Imaginons un système de gestion d'une bibliothèque où chaque livre appartient à une catégorie spécifique. Chaque catégorie peut avoir plusieurs livres, mais chaque livre n'appartient qu'à une seule catégorie. Cela crée une relation 1‑N entre les catégories et les livres.

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

La table category a une clé primaire id et stocke les différentes catégories de livres.
La table book a une clé primaire id et une clé étrangère category_id qui fait référence à id dans la table category.
Chaque livre dans la table book peut être associé à une seule catégorie, mais chaque catégorie peut être associée à plusieurs livres, réalisant ainsi une relation de cardinalité 1:N.

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

Pour créer une relation de cardinalité Plusieurs-à-Plusieurs (N:N) entre deux tables, vous devez utiliser une table d'association. Cette table d'association contient des clés étrangères qui font référence aux clés primaires des deux tables initiales. Chaque paire de clés étrangères dans la table d'association forme une clé primaire composite pour cette table.

Exemple : Prenons l'exemple d'une université où les étudiants peuvent s'inscrire à plusieurs cours, et chaque cours peut être suivi par plusieurs étudiants. Cela crée une relation N‑N entre les étudiants et les cours.

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

La table student contient des informations sur les étudiants, avec une clé primaire id.
La table course contient des informations sur les cours, avec une clé primaire id.
La table d'association enrollment a une clé primaire composite constituée des clés étrangères student_id et course_id, qui font respectivement référence aux clés primaires des tables student et course.
Cette structure permet à chaque étudiant de s'inscrire à plusieurs cours, et chaque cours d'être suivi par plusieurs étudiants, établissant ainsi une relation N:N entre les étudiants et les cours.

Cardinalité optionnelle vs obligatoire

Les cardinalités optionnelle et obligatoire dans les relations de base de données déterminent si une entité doit obligatoirement avoir une relation avec une autre entité ou si cette relation est facultative.

Contexte : Chaque employé appartient à un département, mais certains départements peuvent ne pas avoir d'employés pour le moment.

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

Dans une cardinalité obligatoire, chaque employé doit appartenir à un département. Cela signifie que dans la table employee, la colonne qui fait référence au département ne peut pas être 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

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

Dans une cardinalité optionnelle, un employé peut ne pas être assigné à un département. Ainsi, la colonne de clé étrangère dans la table employee peut avoir 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 identifiant numérique qui est automatiquement généré et incrémenté à chaque fois qu'un nouvel enregistrement est ajouté à la table. Cette fonctionnalité est souvent utilisée pour les clés primaires.

Avantages des ID auto-incrémentés

Unicité : Assure que chaque enregistrement a un identifiant unique.
Simplicité : Simplifie l'ajout de données car vous n'avez pas besoin de vous soucier de la génération des ID.
Performance : Généralement plus efficace pour les opérations de recherche et d'indexation.

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

Dans cet exemple, id est la clé primaire de la table user et est auto-incrémentée. Chaque fois qu'un nouvel utilisateur est ajouté sans spécifier id, MySQL assigne automatiquement le prochain numéro d'ID.