Apprenez les structures de tables MySQL et les types de données | Laboratoire interactif

Introduction

Dans ce laboratoire (lab), nous explorerons les bases des structures de tables MySQL et des types de données. Comprendre comment créer, modifier et gérer des tables est essentiel pour quiconque travaille avec des bases de données. Nous apprendrons les différents types de données dans MySQL, comment choisir le bon type de données pour vos besoins et comment effectuer des opérations de base sur les tables. À la fin de ce laboratoire, vous aurez acquis une expérience pratique en matière de création de tables, de définition de colonnes avec des types de données appropriés et de modification des structures de tables.

Objectifs

En terminant ce laboratoire, vous serez en mesure de :

Comprendre les types de données de base de MySQL et quand les utiliser
Créer des tables avec des définitions de colonnes appropriées
Modifier les structures de tables existantes
Supprimer les tables lorsqu'elles ne sont plus nécessaires
Afficher et comprendre les métadonnées des tables

Comprendre les types de données MySQL

Dans cette étape, nous explorerons les types de données MySQL les plus couramment utilisés. Comprendre les types de données est crucial car le choix du bon type de données pour vos colonnes affecte à la fois l'intégrité des données et les performances de la base de données.

Étant donné que de nombreuses commandes SQL sont impliquées dans cette étape, nous recommandons d'utiliser le terminal web. Cliquez sur l'onglet "Terminal" pour l'ouvrir ; il fonctionne comme le terminal de bureau.

Commençons par nous connecter à MySQL :

sudo mysql -u root

Maintenant que nous sommes connectés, créons une nouvelle base de données pour nos expériences :

CREATE DATABASE store;
USE store;

Examinons les principales catégories de types de données MySQL :

Types numériques :
- INT : Pour les nombres entiers
- DECIMAL : Pour les nombres décimaux précis
- FLOAT/DOUBLE : Pour les nombres décimaux approximatifs
Types de chaînes de caractères :
- VARCHAR : Pour les chaînes de caractères de longueur variable
- CHAR : Pour les chaînes de caractères de longueur fixe
- TEXT : Pour les textes longs
Types de date et d'heure :
- DATE : Pour les dates (AAAA-MM-JJ)
- TIME : Pour les heures (HH:MM:SS)
- DATETIME : Pour les dates et les heures

Créons une table simple qui illustre ces différents types de données :

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    weight FLOAT,
    in_stock BOOLEAN,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Analysons cette structure de table :

id : Un entier auto-incrémenté qui sert de clé primaire
name : Une chaîne de caractères de longueur variable qui ne peut pas être NULL
price : Un nombre décimal précis avec 10 chiffres au total et 2 décimales
description : Un champ texte pour les descriptions plus longues
weight : Un nombre à virgule flottante pour les valeurs décimales approximatives
in_stock : Un champ booléen (VRAI/FAUX)
created_at : Stocke automatiquement l'horodatage de création
last_updated : Se met à jour automatiquement lorsque l'enregistrement change

Pour voir la structure de notre table :

DESCRIBE products;

Vous devriez voir une sortie comme celle-ci :

+--------------+---------------+------+-----+---------------------+-------------------------------+
| Field        | Type          | Null | Key | Default             | Extra                         |
+--------------+---------------+------+-----+---------------------+-------------------------------+
| id           | int(11)       | NO   | PRI | NULL                | auto_increment                |
| name         | varchar(100)  | NO   |     | NULL                |                               |
| price        | decimal(10,2) | NO   |     | NULL                |                               |
| description  | text          | YES  |     | NULL                |                               |
| weight       | float         | YES  |     | NULL                |                               |
| in_stock     | tinyint(1)    | YES  |     | NULL                |                               |
| created_at   | datetime      | YES  |     | current_timestamp() |                               |
| last_updated | timestamp     | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+---------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.001 sec)

Création de tables avec contraintes

Dans cette étape, nous apprendrons les contraintes de table et comment elles contribuent à maintenir l'intégrité des données. Nous allons créer une structure de table plus complexe qui illustre différents types de contraintes.

Créons deux tables liées pour comprendre les relations et les contraintes :

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(200),
    active BOOLEAN DEFAULT TRUE
);

CREATE TABLE inventory_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    sku VARCHAR(20) NOT NULL UNIQUE,
    item_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL CHECK (quantity >= 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

Examinons les contraintes que nous avons utilisées :

PRIMARY KEY (clé primaire) : Garantit l'identification unique de chaque enregistrement
UNIQUE (unicité) : Empêche les valeurs dupliquées dans une colonne
NOT NULL (non nulle) : Garantit qu'une colonne ne peut pas contenir de valeurs NULL
CHECK (vérification) : Valide les données avant leur insertion
FOREIGN KEY (clé étrangère) : Garantit l'intégrité référentielle entre les tables
DEFAULT (valeur par défaut) : Fournit une valeur par défaut si aucune n'est spécifiée

Pour voir la structure de la table avec les contraintes :

SHOW CREATE TABLE inventory_items;

Cela affichera l'instruction complète CREATE TABLE, y compris toutes les contraintes :

MariaDB [store]> SHOW CREATE TABLE inventory_items;

<!-- Exemple de sortie -->

CREATE TABLE `inventory_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) DEFAULT NULL,
  `sku` varchar(20) NOT NULL,
  `item_name` varchar(100) NOT NULL,
  `quantity` int(11) NOT NULL CHECK (`quantity` >= 0),
  `unit_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `inventory_items_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci

Modification de la structure de table

Dans cette étape, nous apprendrons à modifier les tables existantes en utilisant les commandes ALTER TABLE. C'est une tâche courante lorsque vos besoins en matière de données évoluent avec le temps.

Modifions notre table products pour ajouter de nouvelles colonnes et modifier celles existantes :

Ajouter une nouvelle colonne :

ALTER TABLE products
ADD COLUMN manufacturer VARCHAR(100) AFTER name;

Cette commande ajoute une nouvelle colonne nommée manufacturer après la colonne name.

Modifier une colonne existante :

ALTER TABLE products
MODIFY COLUMN description VARCHAR(500) NOT NULL DEFAULT 'No description available';

Cette commande change la colonne description en une chaîne de caractères de longueur variable avec une longueur maximale de 500 caractères. Elle définit également une valeur par défaut de 'No description available' pour les nouvelles lignes.

Renommer une colonne :

ALTER TABLE products
CHANGE COLUMN weight product_weight DECIMAL(8,2);

Cette commande renomme la colonne weight en product_weight et change son type de données en un nombre décimal avec 8 chiffres au total et 2 décimales.

Supprimer une colonne :

ALTER TABLE products
DROP COLUMN in_stock;

Cette commande supprime la colonne in_stock de la table.

Ajoutons un index composite pour améliorer les performances des requêtes :

ALTER TABLE products
ADD INDEX idx_name_manufacturer (name, manufacturer);

Cette commande crée un index composite sur les colonnes name et manufacturer.

Pour voir toutes les modifications que nous avons apportées :

DESCRIBE products;
SHOW INDEX FROM products;

Vous devriez voir la structure mise à jour de la table et les index :

MariaDB [store]> DESCRIBE products;
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| Field          | Type          | Null | Key | Default                  | Extra                         |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
| id             | int(11)       | NO   | PRI | NULL                     | auto_increment                |
| name           | varchar(100)  | NO   | MUL | NULL                     |                               |
| manufacturer   | varchar(100)  | YES  |     | NULL                     |                               |
| price          | decimal(10,2) | NO   |     | NULL                     |                               |
| description    | varchar(500)  | NO   |     | No description available |                               |
| product_weight | decimal(8,2)  | YES  |     | NULL                     |                               |
| created_at     | datetime      | YES  |     | current_timestamp()      |                               |
| last_updated   | timestamp     | NO   |     | current_timestamp()      | on update current_timestamp() |
+----------------+---------------+------+-----+--------------------------+-------------------------------+
8 rows in set (0.001 sec)

MariaDB [store]> SHOW INDEX FROM products;
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table    | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| products |          0 | PRIMARY               |            1 | id           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            1 | name         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| products |          1 | idx_name_manufacturer |            2 | manufacturer | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
+----------+------------+-----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.000 sec)

Travailler avec les informations de table

Dans cette étape, nous explorerons diverses méthodes pour récupérer des informations sur nos tables et leur structure.

Tout d'abord, examinons les informations sur l'état des tables :

SHOW TABLE STATUS FROM store;

Cette commande affiche des informations sur chaque table de la base de données, notamment :

Moteur de stockage (storage engine)
Format de ligne (row format)
Nombre de lignes
Longueur moyenne d'une ligne
Taille des données
Taille des index

Pour afficher toutes les tables de notre base de données :

SHOW TABLES;

Pour afficher des informations détaillées sur une colonne spécifique :

SHOW FULL COLUMNS FROM products;

Cela fournit des informations supplémentaires sur chaque colonne, notamment :

Type de colonne
Collation
Privilèges
Commentaires

Pour afficher tous les index d'une table :

SHOW INDEX FROM products;

Nous pouvons également obtenir des informations sur nos tables à partir de la base de données INFORMATION_SCHEMA :

SELECT
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH,
    INDEX_LENGTH
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    TABLE_SCHEMA = 'store';

Suppression de tables et nettoyage

Dans cette étape finale, nous apprendrons à supprimer en toute sécurité les tables lorsqu'elles ne sont plus nécessaires. Il s'agit d'une compétence importante, mais elle doit être utilisée avec prudence car la suppression d'une table supprime définitivement toutes ses données.

Avant de supprimer une table, il est recommandé de :

Vérifier que vous êtes dans la bonne base de données
Vérifier si la table existe
Vérifier que vous avez une sauvegarde si nécessaire

Commençons par vérifier notre base de données et nos tables actuelles :

SELECT DATABASE();
SHOW TABLES;

Pour supprimer en toute sécurité une table, nous pouvons utiliser la clause IF EXISTS :

DROP TABLE IF EXISTS inventory_items;

Notez que nous devons d'abord supprimer les tables avec des contraintes de clé étrangère. Si nous essayions de supprimer d'abord la table categories, nous obtiendrions une erreur car inventory_items la référence.

Maintenant, nous pouvons supprimer les tables restantes :

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;

Pour vérifier que les tables ont été supprimées :

SHOW TABLES;

Vous devriez voir un ensemble vide, indiquant que toutes les tables ont été supprimées.

Résumé

Dans ce laboratoire (lab), nous avons couvert les aspects essentiels de la manipulation des tables MySQL et des types de données :

Comprendre et utiliser différents types de données MySQL
Créer des tables avec des contraintes appropriées
Modifier les structures de table en utilisant ALTER TABLE
Récupérer les métadonnées et les informations sur les tables
Supprimer en toute sécurité les tables lorsqu'elles ne sont plus nécessaires

Ces compétences constituent la base pour travailler efficacement avec les bases de données MySQL.

Structure des tables MySQL et types de données