Apprendre SQL : Structurer sa BDD (CREATE, ALTER et DROP)

Base de données

Création d’une base de données

Avant toute chose, il faut créer une base de données. Pour cela, SQL nous propose la commande CREATE DATABASE suivi du nom de la base de données à créer.

CREATE DATABASE nom_base;

On écrira généralement une ligne de commande plus aboutie, qui vérifiera d’abord si la base n’existe pas déjà et qui spécifiera par la même occasion l’encodage et la collation voulus.

CREATE DATABASE IF NOT EXISTS spotifi CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

On définit ici :

• utf8mb4 : il s’agit l’encodage le plus répandu sur le web. Il encode un très large jeu de caractères (notamment les caractères de la plupart des langues du monde) pour stocker des données dans la base de données.
• utf8mb4_general_ci : il s’agit d’une collation très répandue définissant comment les données sont triées et comparées dans une base de données.

Suppression d’une base de données

Il est également possible de supprimer une base de données avec la commande DROP DATABASE suivi du nom de la base de données à supprimer.

DROP DATABASE nom_base;

Tables

Création d’une table

Une fois la base de données créée, vous aurez la possibilité de lui ajouter votre première table.

Pour cela on utilisera la commande CREATE TABLE, suivie du nom de la table à créer. Il est alors possible de spécifier entres parenthèses () quelles colonnes on souhaite.

CREATE TABLE nom_de_la_table
(
	colonne_1 type_donnees (options),
	colonne_2 type_donnees (options),
	colonne_3 type_donnees (options),
	colonne_4 type_donnees (options)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Intitulé de colonne

L’intitulé d’une colonne représente l’information qui va y être stockée. (exemple : id, titre, etc.)

Type de colonne

Chaque colonne est en mesure de stocker une information d’une nature spécifique (INT, DECIMAL, DATE, VARCHAR, TEXT, etc.). On parle de type.

Options

Pour chaque colonne, il est également possible de définir des options, dont les principales sont :

• NOT NULL : spécifie qu’une valeur définie est obligatoire pour une colonne.
• DEFAULT : attribuer une valeur par défaut si aucune donnée n’est indiquée pour cette colonne.
• PRIMARY KEY : indiquer si cette colonne est considérée comme clé primaire pour un index.
• AUTO_INCREMENT : indiquer qu’une colonne numérique avec une clé primaire sera incrémentée automatiquement à chaque nouvel enregistrement.

Encodage

On précise l’encodage comme étant utf8mb4 et la collation comme étant utf8mb4_general_ci.

CREATE TABLE articles (
	id INT NOT NULL AUTO_INCREMENT,
	title VARCHAR(255) NOT NULL,
	description VARCHAR(255) NOT NULL,
	content TEXT NOT NULL,
	PRIMARY KEY (id)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Modification d’une table

Pour modifier la structure d’une table, on utilise la commande ALTER TABLE suivie d’une instruction d’ajout, de modification ou encore de suppression de colonne.

ALTER TABLE nom_table ...;

Ajouter une colonne

Pour ajouter une colonne, on utilise le mot-clé ADD :

ALTER TABLE nom_table ADD nouvelle_colonne VARCHAR(255);

Modifier une colonne

Pour modifier une colonne, on utilise le mot-clé MODIFY :

ALTER TABLE nom_table MODIFY colonne_a_modifier VARCHAR(255) NOT NULL;

Supprimer une colonne

Pour supprimer une colonne, on utilise le mot-clé DROP :

ALTER TABLE nom_table DROP colonne_a_supprimer;

Suppression d’une table

Pour modifier la structure d’une table, on utilise la commande DROP TABLE suivie du nom de la table à supprimer.

DROP TABLE nom_table;

Relations entre tables

On l’a vu au chapitre précédent, sans relation, une base de données manque la plupart du temps d’intérêt et nous souhaiterons bien souvent associer des données entre elles.

En SQL, une relation entre deux tables s’implémente de deux manières, selon la multiplicité de la relation.

Relation One To One / Many To One et One To Many

Une relation est dite simple si elle ne met en relation un élément qu’avec un seul autre dans au moins un côté de la relation. Ce type de relation s’implémente en ajoutant une clé étrangère dans l’une des tables.

Une clé étrangère est une colonne contenant un identifiant dont le rôle est de faire référence à la clé primaire d’une autre table.

La table contenant la clé étrangère doit être définie durant les étapes de modélisation du MLD.

CREATE TABLE nom_table (
	cle_primaire INT NOT NULL AUTO_INCREMENT,
	colonne_1 INT,
	colonne_2 VARCHAR(255),
	cle_etrangere INT NOT NULL,
	PRIMARY KEY(cle_primaire),
	CONSTRAINT nom_contrainte FOREIGN KEY(cle_etrangere) REFERENCES table_liee(cle_primaire)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

• cle_etrangere INT NOT NULL permet d’ajouter une colonne de type numérique dont la vocation est de devenir notre clé étrangère.
• CONSTRAINT nom_contrainte FOREIGN KEY(cle_etrangere) REFERENCES table_liee(cle_primaire) permet d’ajouter une contrainte relationnelle transformant réellement notre colonne numérique en clé étrangère.

Relation Many To Many

Une relation multiple s’effectue en créant une table d’association.

Une table d’association est une table constituée d’au moins deux clés étrangères, et possiblement d’autres colonnes. La clé primaire est formée d’un ensemble de colonnes contenant au moins les 2 clés étrangères.

CREATE TABLE nom_table (
	cle_etrangere_1 INT NOT NULL,
	cle_etrangere_2 INT NOT NULL,
	colonne_1 DATE NOT NULL,
	PRIMARY KEY(cle_etrangere_1, cle_etrangere_2, colonne_1),
	CONSTRAINT nom_contrainte_1 FOREIGN KEY(cle_etrangere_1) REFERENCES table_liee_1(cle_primaire),
	CONSTRAINT nom_contrainte_2 FOREIGN KEY(cle_etrangere_2) REFERENCES table_liee_2(cle_primaire)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Nous avons ici deux contraintes relationnelles (pour référencer chacune des deux tables) ainsi qu’une clé primaire formée de l’ensemble des 3 colonnes.

ALTER TABLE nom_table ENGINE = InnoDB;

Moteur de stockage

Qu’est-ce qu’un moteur de stockage ?

Un moteur de stockage, parfois appelé « moteur de table », est le composant d’un SGBD responsable du stockage et de l’accès aux données. En règle générale, les SGBD utilisent un unique moteur de stockage, optimisé au mieux pour leur écosystème.

Moteur transactionnel VS non transactionnel

Un moteur de stockage peut être transactionnel ou non transactionnel.

Principaux moteurs de stockage

MySQL et MariaDB se démarquent des autres SGBD en proposant de sélectionner un moteur parmi une liste conséquente. En voici les principaux :

• MyISAM
• InnoDB
• SEQUENCE
• CSV
• Aria
• MEMORY
• Etc.

MyISAM et InnoDB deux moteurs de stockage répandus ayant chacun des fonctions et spécificités différentes.