formations / mysql / mysql-les-requetes-de-tables

Débutant

cgoupil1970 | 18-11-2023

MySql - Les requêtes de tables

Les requêtes de table sont un élément fondamental de la programmation SQL. Elles permettent d'extraire des données d'une base de données.

Dans ce tutoriel, nous allons voir les bases des requêtes de table, ainsi que quelques exemples concrets.

Créer une table dans une base de données

Pour créer une table dans une base de données, nous allons utiliser la commande SQL "CREATE", comme nous l'avons fait pour la base de données, à la différence prêt que nous allons la faire suivre du mot-clé "TABLE". Ensuite, entre parenthèses, nous pouvons énumérer les colonnes, leurs types et leurs réglages comme le fait qu'elles puissent être NULL.

Le code ci-dessous illustre la création d'une table nommées "animaux" dans la base de données à l'aide de la commande SQL vu dans le cours sur les requêtes de base de données "USE".

USE base_demo;
CREATE TABLE animaux (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(255),
  image VARCHAR(255) NULL
);

Cette requête crée une table appelée "animaux" qui contient les colonnes suivantes :

id : un entier qui est utilisé comme clé primaire. La valeur de cette colonne est automatiquement incrémentée à chaque fois qu'une nouvelle ligne est insérée dans la table.
nom : une chaîne de caractères de longueur maximale 255.
image : une chaîne de caractères de longueur maximale 255.

La clause AUTO_INCREMENT de la colonne id indique que la valeur de cette colonne est automatiquement incrémentée à chaque fois qu'une nouvelle ligne est insérée dans la table. Cela permet de garantir que chaque ligne de la table a une valeur unique pour la colonne id.

La clause PRIMARY KEY de la colonne id indique que cette colonne est la clé primaire de la table. Cela signifie que la valeur de cette colonne est unique pour chaque ligne de la table et qu'elle est utilisée pour identifier chaque ligne de la table.

Néanmoins si nous relançons cette requête, dans la mesure ou la table existera déjà suite à la première execution de la la requête, nous obtiendrons une erreur.

Il va falloir comme nous l'avons vu pour les bases de données modifier ce script afin de sécuriser cette erreur en utilisant une condition comme le code ci-dessous l'illustre

USE base_demo;
CREATE TABLE IF NOT EXISTS animaux (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(255),
  image VARCHAR(255) NULL
);

Pour les besoins de ce tutoriel, nous allons créer une seconde table nommée "type", elle permettra d'associer un type d'animal à chaque animal renseigné dans la table des animaux.

Cette table contiendra un id en auto-incrément et clé primaire, un nom de type en chaîne de caractères ainsi q'un nom d'image également en chaîne de caractères. Aucune de ces deux colonnes ne pourra être null.

USE base_demo;
CREATE TABLE IF NOT EXISTS animal_type (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(255),
  image VARCHAR(255)
);

Après avoir exécuté la seconde requête et rafraîchit le menu de gauche (1) on peut voir les tables correctement créées et présentes dans la base de données.

Modifier une table

Imaginons que nous voulions mettre en place un principe de suppression "logique" plutôt que de suppression "physique" sur les entrées de la table des animaux. Pour mette en place cela, nous avons besoin d'ajouter dans la table une colonne nommée "is_deleted" qui contiendra une valeur booléene, c'est à dire, soit 1 (la ligne est active en tant que ligne de données dans la table), soit 1 (la lignée de données est supprimées).

Nous n'avons pas ajouté cette colonne lors de la création de la table, il va donc nous falloir modifier sa structure à l'aide d'une requête faisant appel à la commande SQL "ALTER TABLE" comme dans le code qui suit.

USE base_demo;
ALTER TABLE animaux ADD is_deleted TINYINT DEFAULT 0;

On peut noter que pour ajouter on utilise la commande "ADD" avec "ALTER TABLE".
Il existe d'autres commandes que ADD :

MODIFY : Modifie le type de données, la taille, la valeur par défaut.
DROP : Supprime une colonne existante.
CHANGE : Modifie le nom et/ou le type de données d'une colonne existante.
ADD COLUMN : Ajoute une colonne à la fin de la table.
ADD COLUMN AFTER : Ajoute une colonne à un emplacement spécifique dans la table.
ADD COLUMN FIRST : Ajoute une colonne au début de la table.
MODIFY COLUMN : Modifie les propriétés d'une colonne existante, telles que son type de données, sa taille, sa valeur par défaut ou sa contrainte.
DROP COLUMN : Supprime une colonne existante.
CHANGE COLUMN : Modifie le nom et/ou le type de données d'une colonne existante.
ADD CONSTRAINT : Ajoute une contrainte à une colonne ou à une table.
DROP CONSTRAINT : Supprime une contrainte d'une colonne ou d'une table.

Lorsqu'on a lancé la requête et que l'on regarde la structure de la table modifiée dans PhpMyAdmin comme le montre la capture ci-dessous, on peut voir :

La colonne "is_deleted" a bien été créée.
Son type est "tinyint", en effet, il n'est pas à proprement parlé de type booléen.
Qu'une valeur par défaut est affectée à 0

Ajouter une clé étrangère

Comme nous venons de le voir, nous pouvons avec la commande SQL "ALTER TABLE" ajouter des colonnes et ainsi modifier la structure de la table. Nous allons maintenant nous intéresser à l'ajout de colonne en gérant une clé étrangère.

Qu'est-ce qu'une clé étrangère ?

Une clé étrangère est une colonne d'une table qui est liée à une colonne d'une autre table. La table contenant la clé étrangère est appelée la table dépendante, et la table contenant la colonne référencée est appelée la table référente. Il s'agit souvent de l'id, la clé primaire d'une table devenant une clé étrangère dans l'autre table.

La principale fonction d'une clé étrangère est de garantir l'intégrité référentielle des données. Cela signifie que les données des deux tables sont cohérentes. En effet, si la valeur d'une clé étrangère dans la table dépendante n'existe pas dans la table référente, la ligne de la table dépendante contenant cette valeur est considérée comme invalide.

Mettons cela en œuvre maintenant.

Avec le code ci-dessous, nous pouvons ajouter une colonne nommée "type_id" dans la table des animaux qui correspondra à l'id d'un type d'animal dans la table "animal_type".

USE base_demo;
ALTER TABLE animaux
ADD COLUMN type_id INT,
ADD CONSTRAINT fk_animal_type
FOREIGN KEY (type_id)
REFERENCES animal_type(id);

Dans cette requête, on utilise "ALTER TABLE" pour modifier la structure de la table. Avec "ADD COLUMN" on ajoute une colonne nommée "type_id" de type integer.

Avec "ADD CONSTRAINT" on ajoute une contrainte à la table qui est définie avec "FOREIGN KEY", commande avec laquelle on détermine que la contrainte est de type clé étrangère dans la nouvelle colonne nommée "type_id". Enfin, avec "REFERENCES" on précise qu'il s'agit d'un valeur de type clé étrangère provenant de la table "animal_type" et plus précisément de sa colonne "id".

On peut s'assurer que la requête s'est bien exécutée en vérifiant dans l'interface phpMyAdmin.

On va sur l'onglet structure de la table.
On active la vue relationelle.
On voit que la contrainte de clé étrangère à bien été créée.
On voit que la colonne concernée dans la table (table dépendante) est la colonne nommée "type_id".
On voit que la table référente est bien la table "animal-type".
On voit que la colonne concernée dans la table référente et la colonne "id".
On voit le réglage des cascades.

Lorsque vous définissez une clé étrangère, vous pouvez spécifier des actions de cascade qui déterminent le comportement des lignes enfants lorsque des modifications sont apportées à la ligne parente. Il existe deux actions principales : ON DELETE et ON UPDATE.

ON DELETE : L'action de cascade ON DELETE spécifie le comportement à adopter lorsque la ligne parente est supprimée. Voici quelques options courantes :

CASCADE : Supprime automatiquement toutes les lignes enfants lorsque la ligne parente est supprimée.
SET NULL : Met à jour les valeurs de la colonne de clé étrangère à NULL lorsque la ligne parente est supprimée.
SET DEFAULT : Met à jour les valeurs de la colonne de clé étrangère à la valeur par défaut lors de la suppression de la ligne parente.
RESTRICT : Empêche la suppression de la ligne parente si des lignes enfants existent.

ON UPDATE : L'action de cascade ON UPDATE spécifie le comportement à adopter lorsque la valeur de la clé primaire de la ligne parente est mise à jour. Les options sont similaires à celles de ON DELETE.

Supprimer une table

Bien évidemment, comme on peut créer ou modifier une table, il est tout à fait possible d'en supprimer une. Pour faite cela on fait appel à la commande SQL "DROP". Il suffira alors d'utiliser un script comme le suivant.

USE base_demo;
DROP TABLE animaux;

Attention, si vous supprimez une table référente et que les cascades de la clé étrangère dans la table dépendante ne sont pas bien configurées cette requête renverra une erreur.

Nous venons de faire un tour d'horizon des principales commandes SQL quant aux tables, création modification et relation, suppression. Nous pouvons maintenant nous intéresser aux différentes commandes SQL relative au CRUD et pour commencer la création de données dans une table.

Tutos du jour

Crédits

Entités & relations

Les entités et les différents types de relation OneToOne, OneToMany, ManyToOne, ManyToMany

Voir le tuto

Photoshop, créer un fichier

Démarrer sur photoshop