Version en ligne

Tutoriel : Administrez vos bases de données avec MySQL

Table des matières

Administrez vos bases de données avec MySQL
Introduction
Concepts de base
Présentation succincte de MySQL...
... et de ses concurrents
Organisation d'une base de données
Installation de MySQL
Avant-propos
Installation du logiciel
Connexion à MySQL
Syntaxe SQL et premières commandes
Les types de données
Types numériques
Types alphanumériques
Types temporels
Création d'une base de données
Avant-propos : conseils et conventions
Création et suppression d'une base de données
Création de tables
Définition des colonnes
Introduction aux clés primaires
Les moteurs de tables
Syntaxe de CREATE TABLE
Suppression d'une table
Modification d'une table
Syntaxe de la requête
Ajout et suppression d'une colonne
Modification de colonne
Insertion de données
Syntaxe de INSERT
Syntaxe alternative de MySQL
Utilisation de fichiers externes
Remplissage de la base
Sélection de données
Syntaxe de SELECT
La clause WHERE
Tri des données
Éliminer les doublons
Restreindre les résultats
Élargir les possibilités de la clause WHERE
Recherche approximative
Recherche dans un intervalle
Set de critères
Suppression et modification de données
Sauvegarde d'une base de données
Suppression
Modification
Index
Qu'est-ce qu'un index ?
Les différents types d'index
Création et suppression des index
Recherches avec FULLTEXT
Clés primaires et étrangères
Clés primaires, le retour
Clés étrangères
Modification de notre base
Jointures
Principe des jointures et notion d'alias
Jointure interne
Jointure externe
Syntaxes alternatives
Exemples d'application et exercices
Sous-requêtes
Sous-requêtes dans le FROM
Sous-requêtes dans les conditions
Sous-requêtes corrélées
Jointures et sous-requêtes : modification de données
Insertion
Modification
Suppression
Union de plusieurs requêtes
Syntaxe
UNION ALL
LIMIT et ORDER BY
Options des clés étrangères
Option sur suppression des clés étrangères
Option sur modification des clés étrangères
Utilisation de ces options dans notre base
Violation de contrainte d'unicité
Ignorer les erreurs
Remplacer l'ancienne ligne
Modifier l'ancienne ligne
Rappels et introduction
Rappels et manipulation simple de nombres
Définition d'une fonction
Quelques fonctions générales
Fonctions scalaires
Manipulation de nombres
Manipulation de chaînes de caractères
Exemples d'application et exercices
Fonctions d'agrégation
Fonctions statistiques
Concaténation
Regroupement
Regroupement sur un critère
Regroupement sur plusieurs critères
Super-agrégats
Conditions sur les fonctions d'agrégation
Exercices sur les agrégats
Du simple…
…Vers le complexe
Obtenir la date/l'heure actuelle
Rappels
Date actuelle
Heure actuelle
Date et heure actuelles
Formater une donnée temporelle
Extraire une information précise
Formater une date facilement
Créer une date à partir d'une chaîne de caractères
Calculs sur les données temporelles
Différence entre deux dates/heures
Ajout et retrait d'un intervalle de temps
Divers
Exercices
Commençons par le format
Passons aux calculs
Et pour finir, mélangeons le tout
Transactions
Principe
Syntaxe et utilisation
Validation implicite et commandes non-annulables
ACID
Verrous
Principe
Syntaxe et utilisation : verrous de table
Syntaxe et utilisation : verrous de ligne
Niveaux d'isolation
Requêtes préparées
Variables utilisateur
Principe et syntaxe des requêtes préparées
Usage et utilité
Procédures stockées
Création et utilisation d'une procédure
Les paramètres d'une procédure stockée
Suppression d'une procédure
Avantages, inconvénients et usage des procédures stockées
Structurer ses instructions
Blocs d'instructions et variables locales
Structures conditionnelles
Boucles
Gestionnaires d'erreurs, curseurs et utilisation avancée
Gestion des erreurs
Curseurs
Utilisation avancée des blocs d'instructions
Triggers
Principe et usage
Création des triggers
Suppression des triggers
Exemples
Restrictions
Vues
Création d'une vue
Sélection des données d'une vue
Modification et suppression d'une vue
Utilité des vues
Algorithmes
Modification des données d'une vue
Tables temporaires
Principe, règles et comportement
Méthodes alternatives de création des tables
Utilité des tables temporaires
Vues matérialisées
Principe
Mise à jour des vues matérialisées
Gain de performance
Gestion des utilisateurs
Introduction
Création, modification et suppression des utilisateurs
Les privilèges - introduction
Ajout et révocation de privilèges
Privilèges particuliers
Options supplémentaires
Informations sur la base de données et les requêtes
Commandes de description
La base de données information_schema
Déroulement d'une requête de sélection
Configuration et options
Variables système
Modification des variables système avec SET
Options au démarrage du client mysql
Options au démarrage du serveur mysqld
Fichiers de configuration

Administrez vos bases de données avec MySQL

Vous avez de nombreuses données à traiter et vous voulez les organiser correctement, avec un outil adapté ?
Les bases de données ont été créées pour vous !

Ce tutoriel porte sur MySQL, qui est un Système de Gestion de Bases de Données Relationnelles (abrégé SGBDR). C'est-à-dire un logiciel qui permet de gérer des bases de données, et donc de gérer de grosses quantités d'informations. Il utilise pour cela le langage SQL.
Il s'agit d'un des SGBDR les plus connus et les plus utilisés (Wikipédia et Adobe utilisent par exemple MySQL). Et c'est certainement le SGBDR le plus utilisé à ce jour pour réaliser des sites web dynamiques. C'est d'ailleurs MySQL qui est présenté dans le tutoriel Concevez votre site web avec PHP et MySQL écrit par Mathieu Nebra, fondateur de ce site.

MySQL peut donc s'utiliser seul, mais est la plupart du temps combiné à un autre langage de programmation : PHP par exemple pour de nombreux sites web, mais aussi Java, Python, C++, et beaucoup, beaucoup d'autres.

MySQL avec l'interface PHPMyAdminMySQL avec l'interface PHPMyAdmin

MySQL avec une console windowsMySQL avec une console windows

Différentes façons d'utiliser MySQL

Quelques exemples d'applications

Vous gérez une boîte de location de matériel audiovisuel, et afin de toujours savoir où vous en êtes dans votre stock, vous voudriez un système informatique vous permettant de gérer les entrées et sorties de matériel, mais aussi éventuellement les données de vos clients. MySQL est une des solutions possibles pour gérer tout ça.

Vous voulez créer un site web dynamique en HTML/CSS/PHP avec un espace membre, un forum, un système de news ou même un simple livre d'or. Une base de données vous sera presque indispensable.

Vous créez un super logiciel en Java qui va vous permettre de gérer vos dépenses afin de ne plus jamais être à découvert, ou devoir vous affamer pendant trois semaines pour pouvoir payer le cadeau d'anniversaire du petit frère. Vous pouvez utiliser une base de données pour stocker les dépenses déjà effectuées, les dépenses à venir, les rentrées régulières, ...

Votre tantine éleveuse d'animaux voudrait un logiciel simple pour gérer ses bestioles, vous savez programmer en python et lui proposez vos services dans l'espoir d'avoir un top cadeau à Noël. Une base de données vous aidera à retenir que Poupouche le Caniche est né le 13 décembre 2007, que Sami le Persan a des poils blancs et que Igor la tortue est le dernier représentant d'une race super rare !

Points abordés dans ce tutoriel

La conception et l'utilisation de bases de données est un vaste sujet, il a fallu faire des choix sur les thèmes à aborder. Voici les compétences que ce tutoriel vise à vous faire acquérir :

Introduction

Concepts de base

Avant de pouvoir joyeusement jouer avec des données, il vous faut connaître quelques concepts de base.

À la fin de ce chapitre, vous devriez :

Concepts de base

Introduction Présentation succincte de MySQL...

Concepts de base

Base de données

Une base de données informatique est un ensemble de données qui ont été stockées sur un support informatique, et organisées et structurées de manière à pouvoir facilement consulter et modifier leur contenu.

Prenons l'exemple d'un site web avec un système de news et de membres. On va utiliser une base de données MySQL pour stocker toutes les données du site : les news (avec la date de publication, le titre, le contenu, éventuellement l'auteur,…) et les membres (leurs noms, leurs emails,…).
Tout ceci va constituer notre base de données pour le site. Mais il ne suffit pas que la base de données existe. Il faut aussi pouvoir la gérer, interagir avec cette base. Il faut pouvoir envoyer des message à MySQL (messages qu'on appellera "requêtes"), afin de pouvoir ajouter des news, modifier des membres, supprimer, et tout simplement afficher des éléments de la base.

Une base de données seule ne suffit donc pas, il est nécessaire d'avoir également :

SGBD

Un Système de Gestion de Base de Données (SGBD) est un logiciel (ou un ensemble de logiciels) permettant de manipuler les données d'une base de données. Manipuler, c'est-à-dire sélectionner et afficher des informations tirées de cette base, modifier des données, en ajouter ou en supprimer (ce groupe de quatre opérations étant souvent appelé "CRUD", pour Create, Read, Update, Delete).
MySQL est un système de gestion de bases de données.

Le paradigme client - serveur

La plupart des SGBD sont basés sur un modèle Client - Serveur. C'est-à-dire que la base de données se trouve sur un serveur qui ne sert qu'à ça, et pour interagir avec cette base de données, il faut utiliser un logiciel "client" qui va interroger le serveur et transmettre la réponse que le serveur lui aura donnée. Le serveur peut être installé sur une machine différente du client ; c'est souvent le cas lorsque les bases de données sont importantes. Ce n'est cependant pas obligatoire, ne sautez pas sur votre petit frère pour lui emprunter son ordinateur. Dans ce tutoriel, nous installerons les logiciels serveur et client sur un seul et même ordinateur.
Par conséquent, lorsque vous installez un SGBD basé sur ce modèle (c'est le cas de MySQL), vous installez en réalité deux choses (au moins) : le serveur, et le client. Chaque requête (insertion/modification/lecture de données) est faite par l'intermédiaire du client. Jamais vous ne discuterez directement avec le serveur (d'ailleurs, il ne comprendrait rien à ce que vous diriez).
Vous avez donc besoin d'un langage pour discuter avec le client, pour lui donner les requêtes que vous souhaitez effectuer. Dans le cas de MySQL, ce langage est le SQL.

SGBDR

Le R de SGBDR signifie "relationnel". Un SGBDR est un SGBD qui implémente la théorie relationnelle. MySQL implémente la théorie relationnelle ; c'est donc un SGBDR.

La théorie relationnelle dépasse le cadre de ce tutoriel, mais ne vous inquiétez pas, il n'est pas nécessaire de la maîtriser pour être capable d'utiliser convenablement un SGBDR. Il vous suffit de savoir que dans un SGBDR, les données sont contenues dans ce qu'on appelle des relations, qui sont représentées sous forme de tables. Une relation est composée de deux parties, l'en-tête et le corps. L'en-tête est lui-même composé de plusieurs attributs. Par exemple, pour la relation "Client", on peut avoir l'en-tête suivant :

Numéro

Nom

Prénom

Email

Quant au corps, il s'agit d'un ensemble de lignes (ou n-uplets) composées d'autant d'éléments qu'il y a d'attributs dans le corps. Voici donc quatre lignes pour la relation "Client" :

Numéro

Nom

Prénom

Email

1

Jean

Dupont

[email protected]

2

Marie

Malherbe

[email protected]

3

Nicolas

Jacques

[email protected]

4

Hadrien

Piroux

[email protected]

Différentes opérations peuvent alors être appliquées à ces relations, ce qui permet d'en tirer des informations. Parmi les opérations les plus utilisées, on peut citer (soient A et B deux relations) :

Un petit exemple pour illustrer la jointure : si l'on veut stocker des informations sur les clients d'une société, ainsi que les commandes passées par ces clients, on utilisera deux relations : client et commande, la relation commande étant liée à la relation client par une référence au client ayant passé commande.
Un petit schéma clarifiera tout ça !

Schéma bdd relationnelle

Le client numéro 3, M. Nicolas Jacques, a donc passé une commande de trois tubes de colle, tandis que Mme Marie Malherbe (cliente numéro 2) a passé deux commandes, pour du papier et des ciseaux.

Le langage SQL

Le SQL (Structured Query Language) est un langage informatique qui permet d'interagir avec des bases de données relationnelles. C'est le langage pour base de données le plus répandu, et c'est bien sûr celui utilisé par MySQL. C'est donc le langage que nous allons utiliser pour dire au client MySQL d'effectuer des opérations sur la base de données stockée sur le serveur MySQL

Il a été créé dans les années 1970 et c'est devenu standard en 1986 (pour la norme ANSI - 1987 en ce qui concerne la norme ISO). Il est encore régulièrement amélioré.

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Introduction Présentation succincte de MySQL...

Présentation succincte de MySQL...

Concepts de base ... et de ses concurrents

Présentation succincte de MySQL...

Image utilisateurMySQL est donc un Système de Gestion de Bases de Données Relationnelles, qui utilise le langage SQL. C'est un des SGBDR les plus utilisés. Sa popularité est due en grande partie au fait qu'il s'agit d'un logiciel Open Source, ce qui signifie que son code source est librement disponible et que quiconque qui en ressent l'envie et/ou le besoin peut modifier MySQL pour l'améliorer ou l'adapter à ses besoins. Une version gratuite de MySQL est par conséquent disponible. À noter qu'une version commerciale payante existe également.

Le logo de MySQL est un dauphin, nommé Sakila suite au concours Name the dolphin ("Nommez le dauphin").

Un peu d'histoire

Image utilisateurDavid Axmark, fondateur de MySQLLe développement de MySQL commence en 1994 par David Axmark et Michael Widenius. EN 1995, la société MySQL AB est fondée par ces deux développeurs, et Allan Larsson. C'est la même année que sort la première version officielle de MySQL.
En 2008, MySQL AB est rachetée par la société Sun Microsystems, qui est elle-même rachetée par Oracle Corporation en 2010.
On craint alors la fin de la gratuité de MySQL, étant donné qu'Oracle Corporation édite un des grands concurrents de MySQL : Oracle Database, qui est payant (et très cher). Oracle a cependant promis de continuer à développer MySQL et de conserver la double licence GPL (libre) et commerciale jusqu'en 2015 au moins.

Mise en garde

MySQL est très utilisé, surtout par les débutants. Vous pourrez faire de nombreuses choses avec ce logiciel, et il convient tout à fait pour découvrir la gestion de bases de données. Sachez cependant que MySQL est loin d'être parfait. En effet, il ne suit pas toujours la norme officielle. Certaines syntaxes peuvent donc être propres à MySQL et ne pas fonctionner sous d'autres SGBDR. J'essayerai de le signaler lorsque le cas se présentera, mais soyez conscients de ce problème.
Par ailleurs, il n'implémente pas certaines fonctionnalités avancées, qui pourraient vous être utiles pour un projet un tant soit peu ambitieux. Enfin, il est très permissif, et acceptera donc des requêtes qui généreraient une erreur sous d'autres SGBDR.

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Concepts de base ... et de ses concurrents

... et de ses concurrents

Présentation succincte de MySQL... Organisation d'une base de données

... et de ses concurrents

Il existe des dizaines de SGBDR, chacun ayant ses avantages et ses inconvénients. Je présente ici succinctement quatre d'entre eux, parmi les plus connus. Je m'excuse tout de suite auprès des fans (et même simples utilisateurs) des nombreux SGBDR que j'ai omis.

Oracle database

Logo d'OracleOracle, édité par Oracle Corporation (qui, je rappelle, édite également MySQL) est un SGBDR payant. Son coût élevé fait qu'il est principalement utilisé par des entreprises.
Oracle gère très bien de grands volumes de données. Il est inutile d'acheter une licence oracle pour un projet de petite taille, car les performances ne seront pas bien différentes de celles de MySQL ou d'un autre SGBDR. Par contre, pour des projets conséquents (plusieurs centaines de Go de données), Oracle sera bien plus performant.
Par ailleurs, Oracle dispose d'un langage procédural très puissant (du moins plus puissant que le langage procédural de MySQL) : le PL/SQL.

PostgreSQL

Logo PostgreSQLComme MySQL, PostgreSQL est un logiciel Open Source. Il est cependant moins utilisé, notamment par les débutants, car moins connu. La raison de cette méconnaissance réside sans doute en partie dans le fait que PostgreSQL a longtemps été disponible uniquement sous Unix. La première version Windows n'est apparue qu'à la sortie de la version 8.0 du logiciel, en 2005.
PostgreSQL a longtemps été plus performant que MySQL, mais ces différences tendent à diminuer. MySQL semble être aujourd'hui équivalent à PostgreSQL en terme de performances sauf pour quelques opérations telles que l'insertion de données et la création d'index.
Le langage procédural utilisé par PostgreSQL s'appelle le PL/pgSQL.

MS Access

Logo MS Access

MS Access ou Microsoft Access est un logiciel édité par Microsoft (comme son nom l'indique…) Par conséquent, c'est un logiciel payant qui ne fonctionne que sous Windows. Il n'est pas du tout adapté pour gérer un grand volume de données et a beaucoup moins de fonctionnalités que les autres SGBDR. Son avantage principal est l'interface graphique intuitive qui vient avec le logiciel.

SQLite

Logo SQLiteLa particularité de SQLite est de ne pas utiliser le schéma client-serveur utilisé par la majorité des SGBDR. SQLite stocke toutes les données dans de simples fichiers. Par conséquent, il ne faut pas installer de serveur de base de données, ce qui n'est pas toujours possible (certains hébergeurs web ne le permettent pas).
Pour de très petits volumes de données, SQLite est très performant. Cependant, le fait que les informations soient simplement stockées dans des fichiers rend le système difficile à sécuriser (autant au niveau des accès, qu'au niveau de la gestion de plusieurs utilisateurs utilisant la base simultanément).

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Présentation succincte de MySQL... Organisation d'une base de données

Organisation d'une base de données

... et de ses concurrents Installation de MySQL

Organisation d'une base de données

Bon, vous savez qu'une base de données sert à gérer les données. Très bien. Mais comment ?? Facile ! Comment organisez-vous vos données dans la "vie réelle" ?? Vos papiers par exemple ? Chacun son organisation bien sûr, mais je suppose que vous les classez d'une manière ou d'une autre.

Toutes les factures ensemble, tous les contrats ensemble, etc. Ensuite on subdivise : les factures d’électricité, les factures pour la voiture. Ou bien dans l'autre sens : tous les papiers concernant la voiture ensemble, puis subdivision en taxes, communication avec l'assureur, avec le garagiste, ...

Une base de données, c'est pareil ! On classe les informations. MySQL étant un SGBDR, je ne parlerai que de l'organisation des bases de données relationnelles.

Comme je vous l'ai dit précédemment, on représente les données sous forme de tables. Une base va donc contenir plusieurs tables (elle peut n'en contenir qu'une bien sûr, mais c'est rarement le cas). Si je reprends mon exemple précédent, on a donc une table représentant des clients (donc des personnes).
Chaque table définit un certain nombre de colonnes, qui sont les caractéristiques de l'objet représenté par la table (les attributs de l'en-tête dans la théorie relationnelle). On a donc ici une colonne "Nom", une colonne "Prénom", une colonne "Email" et une colonne "Numéro" qui nous permettent d'identifier les clients individuellement (les noms et prénoms ne suffisent pas toujours).

Numéro

Nom

Prénom

Email

1

Jean

Dupont

[email protected]

2

Marie

Malherbe

[email protected]

3

Nicolas

Jacques

[email protected]

4

Hadrien

Piroux

[email protected]

Si je récapitule, dans une base nous avons donc des tables, et dans ces tables, on a des colonnes. Dans ces tables, vous introduisez vos données. Chaque donnée introduite le sera sous forme de ligne dans une table, définissant la valeur de chaque colonne pour cette donnée.

En résumé
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

... et de ses concurrents Installation de MySQL

Installation de MySQL

Organisation d'une base de données Avant-propos

Maintenant qu'on sait à peu près de quoi on parle, il est temps d'installer MySQL sur l'ordinateur, et de commencer à l'utiliser.
Au programme de ce chapitre :

Avant-propos

Installation de MySQL Installation du logiciel

Avant-propos

Il existe plusieurs manières d'utiliser MySQL. La première, que je vais utiliser tout au long du tutoriel, est l'utilisation en ligne de commande.

Ligne de commande

Mais qu'est-ce donc ? o_O

Eh bien il s'agit d'une fenêtre toute simple, dans laquelle toutes les instructions sont tapées à la main. Pas de bouton, pas de zone de saisie. Juste votre clavier.

Les utilisateurs de Linux connaissent très certainement. Pour Mac, il faut utiliser l'application "Terminal" que vous trouverez dans Applications > Utilitaires. Quant aux utilisateurs de Windows, c'est le "Command Prompt" que vous devez trouver (Démarrer > Tous les programmes > Accessoires).

Commande prompt (Windows)
Interface graphique

Si l'on ne veut pas utiliser la ligne de commande (il faut bien avouer que ce n'est pas très sympathique cette fenêtre monochrome), on peut utiliser une interface graphique, qui permet d'exécuter pas mal de choses simples de manière intuitive sur une base de données.

Comme interface graphique pour MySQL, on peut citer MySQL Workbench, PhpMyAdmin (souvent utilisé pour créer un site web en combinant MySQL et PHP) ou MySQL Front par exemple.

Pourquoi utiliser la ligne de commande ?

C'est vrai ça, pourquoi ? Si c'est plus simple et plus convivial avec une interface graphique ? :euh:

Deux raisons :

Bien sûr, si vous voulez utiliser une interface graphique, je ne peux guère vous en empêcher. Mais je vous encourage vivement à commencer par utiliser la ligne de commande, ou au minimum à faire l'effort de décortiquer les requêtes que vous laisserez l'interface graphique construire pour vous. Ceci afin de pouvoir les écrire vous-mêmes le jour où vous en aurez besoin (ce jour viendra, je vous le prédis).

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Installation de MySQL Installation du logiciel

Installation du logiciel

Avant-propos Connexion à MySQL

Installation du logiciel

Pour télécharger MySQL, vous pouvez vous rendre sur le site suivant :

http://dev.mysql.com/downloads/mysql/#downloads

Sélectionnez l'OS sur lequel vous travaillez (Windows, Mac OS ou Linux).

Windows

Téléchargez MySQL avec l'installeur (MSI Installer), puis exécutez le fichier téléchargé. L'installeur démarre et vous guide lors de l'installation.
Lorsqu'il vous demande de choisir entre trois types d'installation, choisissez "Typical". Cela installera tout ce dont nous pourrions avoir besoin.

Choix du type d'installation

L'installation se lance. Une fois qu'elle est terminée, cliquez sur "Terminer" après vous être assurés que la case "lancer l'outil de configuration MySQL" est cochée.

Lancement outil de configuration

Dans cet outil de configuration, choisissez la configuration standard, et à l'étape suivante, cochez l'option "Include Bin Directory in Windows PATH"

Options configurations

On vous propose alors de définir un nouveau mot de passe pour l'utilisateur "root". Choisissez un mot de passe et confirmez-le. Ne cochez aucune autre option à cette étape. Cliquez ensuite sur "Execute" pour lancer la configuration.

Mac OS

Téléchargez l'archive DMG qui vous convient (32 ou 64 bits), double-cliquez ensuite sur ce .dmg pour ouvrir l'image disque.
Vous devriez y trouver 4 fichiers dont deux .pkg. Celui qui nous intéresse s'appelle mysql-5.5.9-osx10.6-x86_64.pkg (les chiffres peuvent changer selon la version de MySQL téléchargée et votre ordinateur). Ouvrez ce fichier qui est en fait l'installateur de MySQL, et suivez les instructions.

Une fois le programme installé, vous pouvez ouvrir votre terminal (pour rappel, il se trouve dans Applications -> Utilitaires).

Tapez les commandes et exécutez les instructions suivantes :

cd /usr/local/mysql
sudo ./bin/mysqld_safe
bg

MySQL est prêt à être utilisé !

Configuration

Par défaut, aucun mot de passe n'est demandé pour se connecter, même avec l'utilisateur root (qui a tous les droits). Je vous propose donc de définir un mot de passe pour cet utilisateur :

/usr/local/mysql/bin/mysqladmin -u root password <votre_mot_de_passe>

Ensuite, pour pouvoir accéder directement au logiciel client depuis la console, sans devoir aller dans le dossier où est installé le client, il vous faut ajouter ce dossier à votre variable d'environnement PATH. Pour cela, tapez la commande suivante dans le terminal :

echo 'export PATH=/usr/local/mysql/bin:$PATH' >> ~/.profile
/usr/local/mysql/bin

est donc le dossier dans lequel se trouve le logiciel client (plusieurs logiciels clients en fait). Redémarrez votre terminal pour que le changement prenne effet.

Linux
Sous Debian ou Ubuntu

Exécuter la commande suivante pour installer MySQL :

sudo apt-get install mysql-server mysql-client

Une fois votre mot de passe introduit, MySQL va être installé.

Sous RedHat

Exécuter la commande suivante pour installer MySQL :

sudo yum install mysql mysql-server

Une fois votre mot de passe introduit, MySQL va être installé.

Dans tous les cas, après installation

Pensez ensuite à modifier le mot de passe de l'utilisateur root (administrateur ayant tous les droits) avec la commande suivante :

sudo mysqladmin -u root -h localhost password '<votre mot de passe>'
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Avant-propos Connexion à MySQL

Connexion à MySQL

Installation du logiciel Syntaxe SQL et premières commandes

Connexion à MySQL

Je vous ai dit que MySQL était basé sur un modèle client - serveur, comme la plupart des SGBD. Cela implique donc que votre base de données se trouve sur un serveur auquel vous n'avez pas accès directement, il faut passer par un client qui fera la liaison entre vous et le serveur.
Lorsque vous installez MySQL, plusieurs choses sont donc installées sur votre ordinateur :

Connexion au client

Parmi ces clients, celui dont nous allons parler à présent est mysql (original comme nom o_O ). C'est celui que vous utiliserez tout au long de ce cours pour vous connecter à votre base de données et y insérer, consulter et modifier des données. La commande pour lancer le client est tout simplement son nom :

mysql

Cependant cela ne suffit pas. Il vous faut également préciser un certain nombre de paramètres. Le client mysql a besoin d'au minimum trois paramètres :

L'hôte et l'utilisateur ont des valeurs par défaut, et ne sont donc pas toujours indispensables. La valeur par défaut de l'hôte est "localhost", ce qui signifie que le serveur est sur le même ordinateur que le client. C'est bien notre cas, donc nous n'aurons pas à préciser ce paramètre. Pour le nom d'utilisateur, la valeur par défaut dépend de votre système. Sous Windows, l'utilisateur courant est "ODBC", tandis que pour les systèmes Unix (Mac et Linux), il s'agit de votre nom d'utilisateur (le nom qui apparaît dans l'invite de commande).
Pour votre première connexion à MySQL, il faudra vous connecter avec l'utilisateur "root", pour lequel vous avez normalement défini un mot de passe (si vous ne l'avez pas fait, inutile d'utiliser ce paramètre, mais ce n'est pas très sécurisé). Par la suite, nous créerons un nouvel utilisateur.
Pour chacun des trois paramètres, deux syntaxes sont possibles :

########
# Hôte #
########

--hote=nom_hote
 
# ou 

-h nom_hote

########
# User #
########

--user=nom_utilisateur

# ou 

-u nom_utilisateur

################
# Mot de passe #
################

--password=password

# ou 

-ppassword

Remarquez l'absence d'espace entre -p et le mot de passe. C'est voulu (mais uniquement pour ce paramètre-là), et souvent source d'erreurs.
La commande complète pour se connecter est donc :

mysql -h localhost -u root -pmotdepassetopsecret

# ou

mysql --host=localhost --user=root --password=motdepassetopsecret

# ou un mélange des paramètres courts et longs si ça vous amuse

mysql -h localhost --user=root -pmotdepassetopsecret

J'utiliserai uniquement les paramètres courts à partir de maintenant. Choisissez ce qui vous convient le mieux.
Notez que pour le mot de passe, il est possible (et c'est même très conseillé) de préciser uniquement que vous utilisez le paramètre, sans lui donner de valeur :

mysql -h localhost -u root -p

Apparaissent alors dans la console les mots suivants :

Enter password:

Tapez donc votre mot de passe, et là, vous pouvez constater que les lettres que vous tapez ne s'affichent pas. C'est normal, cessez donc de martyriser votre clavier, il n'y peut rien le pauvre :colere: . Cela permet simplement de cacher votre mot de passe à d'éventuels curieux qui regarderaient par-dessus votre épaule.

Donc pour résumer, pour me connecter à mysql, je tape la commande suivante :

mysql -u root -p

J'ai omis l'hôte, puisque mon serveur est sur mon ordinateur. Je n'ai plus qu'à taper mon mot de passe et je suis connecté.

Déconnexion

Pour se déconnecter du client, il suffit d'utiliser la commande quit ou exit.

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Installation du logiciel Syntaxe SQL et premières commandes

Syntaxe SQL et premières commandes

Connexion à MySQL Les types de données

Syntaxe SQL et premières commandes

Maintenant que vous savez vous connecter, vous allez enfin pouvoir discuter avec le serveur MySQL (en langage SQL évidemment). Donc, reconnectez-vous si vous êtes déconnectés.

Vous pouvez constater que vous êtes connectés grâce au joli (quoiqu'un peu formel) message de bienvenue, ainsi qu'au changement de l'invite de commande. On voit maintenant mysql>.

"Hello World !"

Traditionnellement, lorsque l'on apprend un langage informatique, la première chose que l'on fait, c'est afficher le célèbre message "Hello World !". Pour ne pas déroger à la règle, je vous propose de taper la commande suivante (sans oublier le ; à la fin) :

SELECT 'Hello World !';
SELECT

est la commande qui permet la sélection de données, mais aussi l'affichage. Vous devriez donc voir s'afficher "Hello World !"

Hello World !

Hello World !

Comme vous le voyez, "Hello World !" s'affiche en réalité deux fois. C'est parce que MySQL représente les données sous forme de table. Il affiche donc une table avec une colonne, qu'il appelle "Hello World !" faute de meilleure information. Et dans cette table nous avons une ligne de données, le "Hello World !" que nous avons demandé.

Syntaxe

Avant d'aller plus loin, voici quelques règles générales à retenir concernant le SQL qui, comme tout langage informatique, obéit à des règles syntaxiques très strictes.

Fin d'une instruction

Pour signifier à MySQL qu'une instruction est terminée, il faut mettre le caractère ;. Tant qu'il ne rencontre pas ce caractère, le client MySQL pense que vous n'avez pas fini d'écrire votre commande et attend gentiment que vous continuiez.

Par exemple, la commande suivante devrait afficher 100. Mais tant que MySQL ne recevra pas de ;, il attendra simplement la suite.

SELECT 100

En appuyant sur la touche Entrée vous passez à la ligne suivante, mais la commande ne s'effectue pas. Remarquez au passage le changement dans l'invite de commande. mysql> signifie que vous allez entrer une commande, tandis que -> signifie que vous allez entrer la suite d'une commande commencée précédemment.

Tapez maintenant ; puis appuyer sur Entrée. Ca y est, la commande est envoyée, l'affichage se fait !

Ce caractère de fin d'instruction obligatoire va vous permettre :

Commentaires

Les commentaires sont des parties de code qui ne sont pas interprétées. Ils servent principalement à vous repérer dans votre code. En SQL, les commentaires sont introduits par -- (deux tirets). Cependant, MySQL déroge un peu à la règle SQL et accepte deux syntaxes :

Afin de suivre au maximum la norme SQL, ce sont les -- qui seront utilisés tout au long de ce tutoriel.

Chaînes de caractères

Lorsque vous écrivez une chaîne de caractères dans une commande SQL, il faut absolument l'entourer de guillemets simples (donc des apostrophes).

Exemple : la commande suivante sert à afficher "Bonjour petit Zéro !"

SELECT 'Bonjour petit Zéro !';

Par ailleurs, si vous désirez utiliser un caractère spécial dans une chaîne, il vous faudra l'échapper avec \. Par exemple, si vous entourez votre chaîne de caractères de guillemets simples mais voulez utiliser un tel guillemet à l'intérieur de votre chaîne :

SELECT 'Salut l'ami';  -- Pas bien !
SELECT 'Salut l\'ami'; -- Bien !

Quelques autres caractères spéciaux :

retour à la ligne

tabulation

\

antislash (eh oui, il faut échapper le caractère d'échappement…)

%

pourcent (vous verrez pourquoi plus tard)

_

souligné (vous verrez pourquoi plus tard aussi)

Notez que pour échapper un guillemet simple (et uniquement ce caractère), vous pouvez également l'écrire deux fois. Cette façon d'échapper les guillemets correspond d'ailleurs à la norme SQL. Je vous encourage par conséquent à essayer de l'utiliser au maximum.

SELECT 'Salut l'ami';  -- ne fonctionne pas !
SELECT 'Salut l\'ami'; -- fonctionne !
SELECT 'Salut l''ami'; -- fonctionne aussi et correspond à la norme !
Un peu de math

MySQL est également doué en calcul :

SELECT (5+3)*2;

Pas de guillemets cette fois puisqu'il s'agit de nombres. MySQL calcule pour nous et nous affiche :

(5+3)*2

16

MySQL est sensible à la priorité des opérations, comme vous pourrez le constater en tapant cette commande :

SELECT (5+3)*2, 5+3*2;

Résultat :

(5+3)*2

5+3*2

16

11

Utilisateur

Il n'est pas très conseillé de travailler en tant que "root" dans MySQL, à moins d'en avoir spécifiquement besoin. En effet, "root" a tous les droits. Ce qui signifie que vous pouvez faire n'importe quelle bêtise dans n'importe quelle base de données pendant que j'ai le dos tourné. Pour éviter ça, nous allons créer un nouvel utilisateur, qui aura des droits très restreints. Je l’appellerai "sdz", mais libre à vous de lui donner le nom que vous préférez. Pour ceux qui sont sous Unix, notez que si vous créez un utilisateur du même nom que votre utilisateur Unix, vous pourrez dès lors omettre ce paramètre lors de votre connexion à mysql.

Je vous demande ici de me suivre aveuglément, car je ne vous donnerai que très peu d'explications. En effet, la gestion des droits et des utilisateurs fera l'objet d'un chapitre entier dans une prochaine partie du cours. Tapez donc cette commande dans mysql, en remplaçant sdz par le nom d'utilisateur que vous avez choisi, et mot_de_passe par le mot de passe que vous voulez lui attribuer :

GRANT ALL PRIVILEGES ON elevage.* TO 'sdz'@'localhost' IDENTIFIED BY 'mot_de_passe';

Je décortique donc rapidement :

Pour vous connecter à mysql avec ce nouvel utilisateur, il faut donc taper la commande suivante (après s'être déconnecté bien sûr) :

mysql -u sdz -p
En résumé
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Connexion à MySQL Les types de données

Les types de données

Syntaxe SQL et premières commandes Types numériques

Nous avons vu dans l'introduction qu'une base de données contenait des tables qui, elles-mêmes sont organisées en colonnes, dans lesquelles sont stockées des données.
En SQL (et dans la plupart des langages informatiques), les données sont séparées en plusieurs types (par exemple : texte, nombre entier, date…). Lorsque l'on définit une colonne dans une table de la base, il faut donc lui donner un type, et toutes les données stockées dans cette colonne devront correspondre au type de la colonne. Nous allons donc voir les différents types de données existant dans MySQL.

Types numériques

Les types de données Types alphanumériques

Types numériques

On peut subdiviser les types numériques en deux sous-catégories : les nombres entiers, et les nombres décimaux.

Nombres entiers

Les types de données qui acceptent des nombres entiers comme valeur sont désignés par le mot-clé INT, et ses déclinaisons TINYINT, SMALLINT, MEDIUMINT et BIGINT. La différence entre ces types est le nombre d'octets (donc la place en mémoire) réservés à la valeur du champ. Voici un tableau reprenant ces informations, ainsi que l'intervalle dans lequel la valeur peut être comprise pour chaque type.

Type

Nombre d'octets

Minimum

Maximum

TINYINT

1

-128

127

SMALLINT

2

-32768

32767

MEDIUMINT

3

-8388608

8388607

INT

4

-2147483648

2147483647

BIGINT

8

-9223372036854775808

9223372036854775807

L'attribut UNSIGNED

Vous pouvez également préciser que vos colonnes sont UNSIGNED, c'est-à-dire qu'on ne précise pas s'il s'agit d'une valeur positive ou négative (on aura donc toujours une valeur positive). Dans ce cas, la longueur de l'intervalle reste la même, mais les valeurs possibles sont décalées, le minimum valant 0. Pour les TINYINT, on pourra par exemple aller de 0 à 255.

Limiter la taille d'affichage et l'attribut ZEROFILL

Il est possible de préciser le nombre de chiffres minimum à l'affichage d'une colonne de type INT (ou un de ses dérivés). Il suffit alors de préciser ce nombre entre parenthèses : INT(x). Notez bien que cela ne change pas les capacités de stockage dans la colonne. Si vous déclarez un INT(2), vous pourrez toujours y stocker 45282 par exemple. Simplement, si vous stockez un nombre avec un nombre de chiffres inférieur au nombre défini, le caractère par défaut sera ajouté à gauche du chiffre, pour qu'il prenne la bonne taille. Sans précision, le caractère par défaut est l'espace.

Cette taille d'affichage est généralement utilisée en combinaison avec l'attribut ZEROFILL. Cet attribut ajoute des zéros à gauche du nombre lors de son affichage, il change donc le caractère par défaut par '0'. Donc, si vous déclarez une colonne comme étant

INT(4) ZEROFILL

Vous aurez l'affichage suivant :

Nombre stocké

Nombre affiché

45

0045

4156

4156

785164

785164

Nombres décimaux

Cinq mots-clés permettent de stocker des nombres décimaux dans une colonne : DECIMAL, NUMERIC, FLOAT, REAL et DOUBLE.

NUMERIC et DECIMAL

NUMERIC et DECIMAL sont équivalents et acceptent deux paramètres : la précision et l'échelle.

Dans un champ DECIMAL(5,3), on peut donc stocker des nombres de 5 chiffres significatifs maximum, dont 3 chiffres sont après la virgule. Par exemple : 12.354, -54.258, 89.2 ou -56.
DECIMAL(4) équivaut à écrire DECIMAL(4, 0).

Comme pour les nombres entiers, si l'on entre un nombre qui n'est pas dans l'intervalle supporté par la colonne, MySQL le remplacera par le plus proche supporté. Donc si la colonne est définie comme un DECIMAL(5,3) et que le nombre est trop loin dans les positifs (1012,43 par exemple), 999.999 sera stocké, et -99.999 si le nombre est trop loin dans les négatifs.
S'il y a trop de chiffres après la virgule, MySQL arrondira à l'échelle définie.

FLOAT, DOUBLE et REAL

Le mot-clé FLOAT peut s'utiliser sans paramètre, auquel cas quatre octets sont utilisés pour stocker les valeurs de la colonne. Il est cependant possible de spécifier une précision et une échelle, de la même manière que pour DECIMAL et NUMERIC.

Quant à REAL et DOUBLE, ils ne supportent pas de paramètres. DOUBLE est normalement plus précis que REAL (stockage dans 8 octets contre stockage dans 4 octets), mais ce n'est pas le cas avec MySQL qui utilise 8 octets dans les deux cas. Je vous conseille donc d'utiliser DOUBLE pour éviter les surprises en cas de changement de SGBDR.

Valeurs exactes vs. valeurs approchées

Les nombres stockés en tant que NUMERIC ou DECIMAL sont stockés sous forme de chaînes de caractères. Par conséquent, c'est la valeur exacte qui est stockée. Par contre, les types FLOAT, DOUBLE et REAL sont stockés sous forme de nombres, et c'est une valeur approchée qui est stockée.
Cela signifie que si vous stockez par exemple 56,6789 dans une colonne de type FLOAT, en réalité, MySQL stockera une valeur qui se rapproche de 56,6789 (par exemple, 56,678900000000000001). Cela peut poser problème pour des comparaison notamment (56,678900000000000001 n'étant pas égal à 56,6789). S'il est nécessaire de conserver la précision exacte de vos données (l'exemple type est celui des données bancaires), il est donc conseillé d'utiliser un type numérique à valeur exacte (NUMERIC ou DECIMAL donc).

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Les types de données Types alphanumériques

Types alphanumériques

Types numériques Types temporels

Types alphanumériques

Chaînes de type texte
CHAR et VARCHAR

Pour stocker un texte relativement court (moins de 255 octets), vous pouvez utiliser les types CHAR et VARCHAR. Ces deux types s'utilisent avec un paramètre qui précise la taille que peut prendre votre texte (entre 1 et 255). La différence entre CHAR et VARCHAR est la manière dont ils sont stockés en mémoire. Un CHAR(x) stockera toujours x octets, en remplissant si nécessaire le texte avec des espaces vides pour le compléter, tandis qu'un VARCHAR(x) stockera jusqu'à x octets (entre 0 et x), et stockera en plus en mémoire la taille du texte stocké.
Si vous entrez un texte plus long que la taille maximale définie pour le champ, celui-ci sera tronqué.

Petit tableau explicatif, en prenant l'exemple d'un CHAR ou d'un VARCHAR de 5 octets maximum :

Texte

CHAR(5)

Mémoire requise

VARCHAR(5)

Mémoire requise

''

'   '

5 octets

''

1 octet

'tex'

'tex  '

5 octets

'tex'

4 octets

'texte'

'texte'

5 octets

'texte'

6 octets

'texte trop long'

'texte'

5 octets

'texte'

6 octets

Vous voyez donc que dans le cas où le texte fait la longueur maximale autorisée, un CHAR(x) prend moins de place en mémoire qu'un VARCHAR(x). Préférez donc le CHAR(x) dans le cas où vous savez que vous aurez toujours x octets (par exemple si vous stockez un code postal). Par contre, si la longueur de votre texte risque de varier d'une ligne à l'autre, définissez votre colonne comme un VARCHAR(x).

TEXT

Et si je veux pouvoir stocker des textes de plus de 255 octets ?

Il suffit alors d'utiliser le type TEXT, ou un de ses dérivés TINYTEXT, MEDIUMTEXT ou LONGTEXT. La différence entre ceux-ci étant la place qu'ils permettent d'occuper en mémoire. Petit tableau habituel :

Type

Longueur maximale

Mémoire occupée

TINYTEXT

2^8 octets

Longueur de la chaîne + 1 octet

TEXT

2^16 octets

Longueur de la chaîne + 2 octets

MEDIUMTEXT

2^24 octets

Longueur de la chaîne + 3 octets

LONGTEXT

2^32 octets

Longueur de la chaîne + 4 octets

Chaînes de type binaire

Comme les chaînes de type texte que l'on vient de voir, une chaîne binaire n'est rien d'autre qu'une suite de caractères.
Cependant, si les textes sont affectés par l'encodage et l'interclassement, ce n'est pas le cas des chaînes binaires. Une chaîne binaire n'est rien d'autre qu'une suite d'octets. Aucune interprétation n'est faite sur ces octets. Ceci a deux conséquences principales.

Par conséquent, les types binaires sont parfaits pour stocker des données "brutes" comme des images par exemple, tandis que les chaînes de texte sont parfaites pour stocker...du texte ! :D

Les types binaires sont définis de la même façon que les types de chaînes de texte. VARBINARY(x) et BINARY(x) permettent de stocker des chaînes binaires de x caractères maximum (avec une gestion de la mémoire identique à VARCHAR(x) et CHAR(x)). Pour les chaînes plus longues, il existe les types TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB, également avec les mêmes limites de stockage que les types TEXT.

SET et ENUM
ENUM

Une colonne de type ENUM est une colonne pour laquelle on définit un certain nombre de valeurs autorisées, de type "chaîne de caractère". Par exemple, si l'on définit une colonne espece (pour une espèce animale) de la manière suivante :

espece ENUM('chat', 'chien', 'tortue')

La colonne espece pourra alors contenir les chaînes "chat", "chien" ou "tortue", mais pas les chaînes "lapin" ou "cheval".

En plus de "chat", "chien" et "tortue", la colonne espece pourrait prendre deux autres valeurs :

Pour remplir un champ de type ENUM, deux possibilités s'offrent à vous :

Valeur

Index

NULL

NULL

''

0

'chat'

1

'chien'

2

'tortue'

3

Afin que tout soit bien clair : si vous voulez stocker "chien" dans votre champ, vous pouvez donc y insérer "chien" ou insérer 2 (sans guillemets, il s'agit d'un nombre, pas d'un caractère).

SET

SET est fort semblable à ENUM. Une colonne SET est en effet une colonne qui permet de stocker une chaîne de caractères dont les valeurs possibles sont prédéfinies par l'utilisateur. La différence avec ENUM, c'est qu'on peut stocker dans la colonne entre 0 et x valeur(s), x étant le nombre de valeurs autorisées.

Donc, si l'on définit une colonne de type SET de la manière suivante :

espece SET('chat', 'chien', 'tortue')

On pourra stocker dans cette colonne :

Vous remarquerez que lorsqu'on stocke plusieurs valeurs, il faut les séparer par une virgule, sans espace et entourer la totalité des valeurs par des guillemets (non pas chaque valeur séparément). Par conséquent, les valeurs autorisées d'une colonne SET ne peuvent pas contenir de virgule elles-mêmes.

Les colonnes SET utilisent également un système d'index, quoiqu'un peu plus complexe que pour le type ENUM. SET utilise en effet un système d'index binaire. Concrètement, la présence/absence des valeurs autorisées va être enregistrée sous forme de bits, mis à 1 si la valeur correspondante est présente, à 0 si la valeur correspondante est absente.
Si l'on reprend notre exemple, on a donc :

espece SET('chat', 'chien', 'tortue')

Trois valeurs sont autorisées. Il nous faut donc trois bits pour savoir quelles valeurs sont stockées dans le champ. Le premier, à droite, correspondra à "chat", le second (au milieu) à "chien" et le dernier (à gauche) à "tortue".

Par ailleurs, ces suites de bits représentent des nombres en binaire convertibles en décimal. Ainsi 000 en binaire correspond à 0 en nombre décimal, 001 correspond à 1, 010 correspond à 2, 011 à 3...

Puisque j'aime bien les tableaux, je vous en fais un, ce sera peut-être plus clair.

Valeur

Binaire

Décimal

'chat'

001

1

'chien'

010

2

'tortue'

100

4

Pour stocker 'chat' et 'tortue' dans un champ, on peut donc utiliser 'chat,tortue' ou 101 (addition des nombres binaires correspondants) ou 5 (addition des nombres décimaux correspondants).
Notez que cette utilisation des binaires a pour conséquence que l'ordre dans lequel vous rentrez vos valeurs n'a pas d'importance. Que vous écriviez 'chat,tortue' ou 'tortue,chat' ne fait aucune différence. Lorsque vous récupérerez votre champ, vous aurez 'chat,tortue' (dans le même ordre que lors de la définition du champ).

Avertissement

SET et ENUM sont des types propres à MySQL. Ils sont donc à utiliser avec une grande prudence !

Pourquoi avoir inventé ces types propres à MySQL ?

La plupart des SGBD implémentent ce qu'on appelle des contraintes d'assertions, qui permettent de définir les valeurs que peuvent prendre une colonne (par exemple, on pourrait définir une contrainte pour une colonne contenant un âge, devant être compris entre 0 et 130).
MySQL n'implémente pas ce type de contrainte et a par conséquent créé deux types de données spécifiques (SET et ENUM), pour pallier en partie ce manque.

Dans quelles situations faut-il utiliser ENUM ou SET ?

La meilleure réponse à cette question est : jamais ! Je déconseille fortement l'utilisation des SET et des ENUM. Je vous ai présenté ces deux types par souci d'exhaustivité, mais il faut toujours éviter autant que possible les fonctionnalités propres à un seul SGBD. Ceci afin d'éviter les problèmes si un jour vous voulez en utiliser un autre.

Mais ce n'est pas la seule raison. Imaginez que vous vouliez utiliser un ENUM ou un SET pour un système de catégories. Vous avez donc des éléments qui peuvent appartenir à une catégorie (dans ce cas, vous utilisez une colonne ENUM pour la catégorie) ou appartenir à plusieurs catégories (et vous utilisez SET).

categorie ENUM("Soupes", "Viandes", "Tarte", "Dessert")
categorie SET("Soupes", "Viandes", "Tarte", "Dessert")

Tout se passe plutôt bien tant que vos éléments appartiennent aux catégories que vous avez définies au départ. Et puis tout à coup, vous vous retrouvez avec un élément qui ne correspond à aucune de vos catégories, mais qui devrait plutôt se trouver dans la catégorie "Entrées". Avec SET ou ENUM, il vous faut modifier la colonne categorie pour ajouter "Entrées" aux valeurs possibles. Or, une des règles de base à respecter lorsque l'on conçoit une base de données, est que la structure de la base (donc les tables, les colonnes) ne doit pas changer lorsque l'on ajoute des données. Par conséquent, tout ce qui est susceptible de changer doit être une donnée, et non faire partie de la structure de la base.

Il existe deux solutions pour éviter les ENUM, et une solution pour éviter les SET.

Pour éviter ENUM

Pour éviter SET
La solution consiste en la création de deux tables : une table Categorie, qui reprend les catégories possibles, et une table qui lie les éléments aux catégories auxquels ils appartiennent.

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Types numériques Types temporels

Types temporels

Types alphanumériques Création d'une base de données

Types temporels

Pour les données temporelles, MySQL dispose de cinq types qui permettent, lorsqu'ils sont bien utilisés, de faire énormément de choses.

Avant d'entrer dans le vif du sujet, une petite remarque importante : lorsque vous stockez une date dans MySQL, certaines vérifications sont faites sur la validité de la date entrée. Cependant, ce sont des vérifications de base : le jour doit être compris entre 1 et 31 et le mois entre 1 et 12. Il vous est tout à fait possible d'entrer une date telle que le 31 février 2011. Soyez donc prudents avec les dates que vous entrez et récupérez.

Les cinq types temporels de MySQL sont DATE, DATETIME, TIME, TIMESTAMP et YEAR.

DATE, TIME et DATETIME

Comme son nom l'indique, DATE sert à stocker une date. TIME sert quant à lui à stocker une heure, et DATETIME stocke...une date ET une heure ! :D

DATE

Pour entrer une date, l'ordre des données est la seule contrainte. Il faut donner d'abord l'année (deux ou quatre chiffres), ensuite le mois (deux chiffres) et pour finir, le jour (deux chiffres), sous forme de nombre ou de chaîne de caractères. S'il s'agit d'une chaîne de caractères, n'importe quelle ponctuation peut être utilisée pour délimiter les parties (ou aucune). Voici quelques exemples d'expressions correctes (A représente les années, M les mois et J les jours) :

L'année peut donc être donnée avec deux ou quatre chiffres. Dans ce cas, le siècle n'est pas précisé, et c'est MySQL qui va décider de ce qu'il utilisera, selon ces critères :

DATETIME

Très proche de DATE, ce type permet de stocker une heure, en plus d'une date. Pour entrer un DATETIME, c'est le même principe que pour DATE : pour la date, année-mois-jour, et pour l'heure, il faut donner d'abord l'heure, ensuite les minutes, puis les secondes. Si on utilise une chaîne de caractères, il faut séparer la date et l'heure par une espace. Quelques exemples corrects (H représente les heures, M les minutes et S les secondes) :

TIME

Le type TIME est un peu plus compliqué, puisqu'il permet non seulement de stocker une heure précise, mais aussi un intervalle de temps. On n'est donc pas limité à 24 heures, et il est même possible de stocker un nombre de jours ou un intervalle négatif. Comme dans DATETIME, il faut d'abord donner l'heure, puis les minutes, puis les secondes, chaque partie pouvant être séparée des autres par le caractère :. Dans le cas où l'on précise également un nombre de jours, alors les jours sont en premier et séparés du reste par une espace. Exemples :

YEAR

Si vous n'avez besoin de retenir que l'année, YEAR est un type intéressant car il ne prend qu'un seul octet en mémoire. Cependant, un octet ne pouvant contenir que 256 valeurs différentes, YEAR est fortement limité : on ne peut y stocker que des années entre 1901 et 2155. Ceci dit, ça devrait suffire à la majorité d'entre vous pour au moins les cent prochaines années.

On peut entrer une donnée de type YEAR sous forme de chaîne de caractères ou d'entiers, avec 2 ou 4 chiffres. Si l'on ne précise que deux chiffres, le siècle est ajouté par MySQL selon les mêmes critères que pour DATE et DATETIME, à une exception près : si l'on entre 00 (un entier donc), il sera interprété comme la valeur par défaut de YEAR 0000. Par contre, si l'on entre '00' (une chaîne de caractères), elle sera bien interprétée comme l'année 2000.
Plus de précisions sur les valeurs par défaut des types temporels dans quelques instants !

TIMESTAMP

Par définition, le timestamp d'une date est le nombre de secondes écoulées depuis le 1er janvier 1970, 0h0min0s (TUC) et la date en question.
Les timestamps étant stockés sur 4 octets, il existe une limite supérieure : le 19 janvier 2038 à 3h14min7s. Par conséquent, vérifiez bien que vous êtes dans l'intervalle de validité avant d'utiliser un timestamp.

Le type TIMESTAMP de MySQL est cependant un peu particulier. Prenons par exemple le 4 octobre 2011, à 21h05min51s.
Entre cette date et le 1er janvier 1970, 0h0min0s, il s'est écoulé exactement 1317755151 secondes. Le nombre 1317755151 est donc, par définition, le timestamp de cette date du 4 octobre 2011, 21h05min51s.
Pourtant, pour stocker cette date dans un TIMESTAMP SQL, ce n'est pas 1317755151 qu'on utilisera, mais 20111004210551. C'est-à-dire l'équivalent, au format numérique, du DATETIME '2011-10-04 21:05:51'.
Le TIMESTAMP SQL n'a donc de timestamp que le nom. Il ne sert pas à stocker un nombre de secondes, mais bien une date sous format numérique AAAAMMJJHHMMSS (alors qu'un DATETIME est donc stocké sous forme de chaîne de caractères).

Il n'est donc pas possible de stocker un "vrai" timestamp dans une colonne de type TIMESTAMP. C'est évidemment contre-intuitif, et source d'erreur.
Notez que malgré cela, le TIMESTAMP SQL a les même limites qu'un vrai timestamp : il n'acceptera que des date entre le 1e janvier 1970 à 00h00min00s et le 19 janvier 2038 à 3h14min7s.

La date par défaut

Lorsque MySQL rencontre une date/heure incorrecte, ou qui n'est pas dans l'intervalle de validité du champ, la valeur par défaut est stockée à la place. Il s'agit de la valeur "zéro" du type. On peut se référer à cette valeur par défaut en utilisant '0' (caractère), 0 (nombre) ou la représentation du "zéro" correspondant au type de la colonne (voir tableau ci-dessous).

Type

Date par défaut ("zéro")

DATE

'0000-00-00'

DATETIME

'0000-00-00 00:00:00'

TIME

'00:00:00'

YEAR

0000

TIMESTAMP

00000000000000

Une exception toutefois, si vous insérez un TIME qui dépasse l'intervalle de validité, MySQL ne le remplacera pas par le "zéro", mais par la plus proche valeur appartenant à l'intervalle de validité (-838:59:59 ou 838:59:59).

En résumé
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Types alphanumériques Création d'une base de données

Création d'une base de données

Types temporels Avant-propos : conseils et conventions

Ça y est, le temps est venu d'écrire vos premières lignes de commande.
Dans ce chapitre plutôt court, je vous donnerai pour commencer quelques conseils indispensables. Ensuite, je vous présenterai la problématique sur laquelle nous allons travailler tout au long de ce tutoriel : la base de données d'un élevage d'animaux.
Pour finir, nous verrons comment créer et supprimer une base de données.

La partie purement théorique est donc bientôt finie. Gardez la tête et les mains à l'intérieur du véhicule. C'est parti !

Avant-propos : conseils et conventions

Création d'une base de données Création et suppression d'une base de données

Avant-propos : conseils et conventions

Conseils
Noms de tables et de colonnes

N'utilisez jamais, au grand jamais, d'espaces ou d'accents dans vos noms de bases, tables ou colonnes. Au lieu d'avoir une colonne "date de naissance", préférez "date_de_naissance" ou "date_naissance". Et au lieu d'avoir une colonne "prénom", utilisez "prenom". Avouez que ça reste lisible, et ça vous évitera pas mal d'ennuis.

Évitez également d'utiliser des mots réservés comme nom de colonnes/tables/bases. Par "mot réservé", j'entends un mot-clé SQL, donc un mot qui sert à définir quelque chose dans le langage SQL. Vous trouverez une liste exhaustive des mots réservés dans la documentation officielle. Parmi les plus fréquents : date, text, type. Ajoutez donc une précision à vos noms dans ces cas-là (date_naissance, text_article ou type_personnage par exemple).
Notez que MySQL permet l'utilisation de mots-clés comme noms de tables ou de colonnes, à condition que ce nom soit entouré de ` (accent grave/backquote). Cependant, ceci est propre à MySQL et ne devrait pas être utilisé.

Soyez cohérents

Vous vous y retrouverez bien mieux si vous restez cohérents dans votre base. Par exemple, mettez tous vos noms de tables au singulier, ou au contraire au pluriel. Choisissez, mais tenez-vous-y. Même chose pour les noms de colonnes. Et lorsqu'un nom de table ou de colonne nécessite plusieurs mots, séparez les toujours avec '_' (ex : date_naissance) ou bien toujours avec une majuscule (ex : dateNaissance).
Ce ne sont que quelques exemples de situations dans lesquelles vous devez décider d'une marche à suivre, et la garder tout au long de votre projet (voire pour tous vos projets futurs). Vous gagnerez énormément de temps en prenant de telles habitudes.

Conventions
Mots-clés

Une convention largement répandue veut que les commandes et mots-clés SQL soient écrits complètement en majuscules. Je respecterai cette convention et vous encourage à le faire également. Il est plus facile de relire une commande de 5 lignes lorsqu'on peut différencier au premier coup d’œil les commandes SQL des noms de tables et de colonnes.

Noms de bases, de tables et de colonnes

Je viens de vous dire que les mots-clés SQL seront écrits en majuscule pour les différencier du reste, donc évidemment, les noms de bases, tables et colonnes seront écrits en minuscule.
Toutefois, par habitude et parce que je trouve cela plus clair, je mettrai une majuscule à la première lettre de mes noms de tables (et uniquement pour les tables : ni pour la base de données ni pour les colonnes). Notez que MySQL n'est pas nécessairement sensible à la casse en ce qui concerne les noms de tables et de colonnes. En fait, il est très probable que si vous travaillez sous Windows, MySQL ne soit pas sensible à la casse pour les noms de tables et de colonnes. Sous Mac et Linux par contre, c'est le contraire qui est le plus probable.
Quoi qu'il en soit, j'utiliserai des majuscules pour la première lettre de mes noms de tables. Libre à vous de me suivre ou non.

Options facultatives

Lorsque je commencerai à vous montrer les commandes SQL à utiliser pour interagir avec votre base de données, vous verrez que certaines commandes ont des options facultatives. Dans ces cas-là, j'utiliserai des crochets [ ] pour indiquer ce qui est facultatif. La même convention est utilisée dans la documentation officielle MySQL (et dans beaucoup d'autres documentations d'ailleurs). La requête suivante signifie donc que vous pouvez commander votre glace vanille toute seule, ou avec du chocolat, ou avec de la chantilly, ou avec du chocolat ET de la chantilly.

COMMANDE glace vanille [avec chocolat] [avec chantilly]
Mise en situation

Histoire que nous soyons sur la même longueur d'onde, je vous propose de baser le cours sur une problématique bien précise. Nous allons créer une base de données qui permettra de gérer un élevage d'animaux. Pourquoi un élevage ? Tout simplement parce que j'ai dû moi-même créer une telle base pour le laboratoire de biologie pour lequel je travaillais. Par conséquent, j'ai une assez bonne idée des problèmes qu'on peut rencontrer avec ce type de bases, et je pourrai donc appuyer mes explications sur des problèmes réalistes, plutôt que d'essayer d'en inventer.

Nous nous occupons donc d'un élevage d'animaux. On travaille avec plusieurs espèces : chats, chiens, tortues entre autres (tiens, ça me rappelle quelque chose :-° ). Dans la suite de cette partie, nous nous contenterons de créer une table Animal qui contiendra les caractéristiques principales des animaux présents dans l'élevage, mais dès le début de la deuxième partie, d'autres tables seront créées afin de pouvoir gérer un grand nombre de données complexes.

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Création d'une base de données Création et suppression d'une base de données

Création et suppression d'une base de données

Avant-propos : conseils et conventions Création de tables

Création et suppression d'une base de données

Création

Nous allons donc créer notre base de données, que nous appellerons elevage. Rappelez-vous, lors de la création de votre utilisateur MySQL, vous lui avez donné tous les droits sur la base elevage, qui n'existait pas encore. Si vous choisissez un autre nom de base, vous n'aurez aucun droit dessus.

La commande SQL pour créer une base de données est la suivante :

CREATE DATABASE nom_base;

Avouez que je ne vous surmène pas le cerveau pour commencer…

Cependant, attendez avant de créer votre base de données elevage. Je vous rappelle qu'il faut également définir l'encodage utilisé (l'UTF-8 dans notre cas). Voici donc la commande complète à taper pour créer votre base :

CREATE DATABASE elevage CHARACTER SET 'utf8';

Lorsque nous créerons nos tables dans la base de données, automatiquement elles seront encodées également en UTF-8.

Suppression

Si vous avez envie d'essayer cette commande, faites-le maintenant, tant qu'il n'y a rien dans votre base de données. Soyez très prudents, car vous effacez tous les fichiers créés par MySQL qui servent à stocker les informations de votre base.

DROP DATABASE elevage;

Si vous essayez cette commande alors que la base de données elevage n'existe pas, MySQL vous affichera une erreur :

mysql> DROP DATABASE elevage;
ERROR 1008 (HY000) : Can't drop database 'elevage'; database doesn't exist
mysql>

Pour éviter ce message d'erreur, si vous n'êtes pas sûrs que la base de données existe, vous pouvez utiliser l'option IF EXISTS, de la manière suivante :

DROP DATABASE IF EXISTS elevage;

Si la base de données existe, vous devriez alors avoir un message du type :

Query OK, 0 rows affected (0.00 sec)

Si elle n'existe pas, vous aurez :

Query OK, 0 rows affected, 1 warning (0.00 sec)

Pour afficher les warnings de MySQL, il faut utiliser la commande

SHOW WARNINGS;

Cette commande affiche un tableau :

Level

Code

Message

Note

1008

Can't drop database 'elevage'; database doesn't exist

Utilisation d'une base de données

Vous avez maintenant créé une base de données (si vous l'avez effacée avec DROP DATABASE, recréez-la). Mais pour pouvoir agir sur cette base, vous devez encore avertir MySQL que c'est bien sûr cette base-là que vous voulez travailler. Une fois de plus, la commande est très simple :

USE elevage

C'est tout ! À partir de maintenant, toutes les actions effectuées le seront sur la base de données elevage (création et modification de tables par exemple).

Notez que vous pouvez spécifier la base de données sur laquelle vous allez travailler lors de la connexion à MySQL. Il suffit d'ajouter le nom de la base à la fin de la commande de connexion :

mysql -u sdz -p elevage
En résumé
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Avant-propos : conseils et conventions Création de tables

Création de tables

Création et suppression d'une base de données Définition des colonnes

Dans ce chapitre, nous allons créer, étape par étape, une table Animal, qui servira à stocker les animaux présents dans notre élevage.
Soyez gentils avec cette table, car c'est elle qui vous accompagnera tout au long de la première partie (on apprendra à jongler avec plusieurs tables dans la deuxième partie).

Pour commencer, il faudra définir de quelles colonnes (et leur type) la table sera composée. Ne négligez pas cette étape, c'est la plus importante. Une base de données mal conçue est un cauchemar à utiliser.
Ensuite, petit passage obligé par de la théorie : vous apprendrez ce qu'est une clé primaire et à quoi ça sert, et découvrirez cette fonctionnalité exclusive de MySQL que sont les moteurs de table.
Enfin, la table Animal sera créée, et la requête de création des tables décortiquée. Et dans la foulée, nous verrons également comment supprimer une table.

Définition des colonnes

Création de tables Introduction aux clés primaires

Définition des colonnes

Type de colonne

Avant de choisir le type des colonnes, il faut choisir les colonnes que l'on va définir. On va donc créer une table Animal. Qu'est-ce qui caractérise un animal ? Son espèce, son sexe, sa date de naissance. Quoi d'autre ? Une éventuelle colonne commentaires qui peut servir de fourre-tout. Dans le cas d'un élevage sentimental, on peut avoir donné un nom à nos bestioles.
Disons que c'est tout pour le moment. Examinons donc les colonnes afin d'en choisir le type au mieux.

NULL or NOT NULL ?

Il faut maintenant déterminer si l'on autorise les colonnes à ne pas stocker de valeur (ce qui est donc représenté par NULL).

Récapitulatif

Comme d'habitude, un petit tableau pour récapituler tout ça :

Caractéristique

Nom de la colonne

Type

NULL?

Espèce

espece

VARCHAR(40)

Non

Sexe

sexe

CHAR(1)

Oui

Date de naissance

date_naissance

DATETIME

Non

Commentaires

commentaires

TEXT

Oui

Nom

nom

VARCHAR(30)

Oui

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Création de tables Introduction aux clés primaires

Introduction aux clés primaires

Définition des colonnes Les moteurs de tables

Introduction aux clés primaires

On va donc définir cinq colonnes : espece, sexe, date_naissance, commentaires et nom. Ces colonnes permettront de caractériser nos animaux. Mais que se passe-t-il si deux animaux sont de la même espèce, du même sexe, sont nés exactement le même jour, et ont exactement les mêmes commentaires et le même nom ? Comment les différencier ? Évidemment, on pourrait s'arranger pour que deux animaux n'aient jamais le même nom. Mais imaginez la situation suivante : une chatte vient de donner naissance à sept petits. On ne peut pas encore définir leur sexe, on n'a pas encore trouvé de nom pour certains d'entre eux et il n'y a encore aucun commentaire à faire à leur propos. Ils auront donc exactement les mêmes caractéristiques. Pourtant, ce ne sont pas les mêmes individus. Il faut donc les différencier. Pour cela, on va ajouter une colonne à notre table.

Identité

Imaginez que quelqu'un ait le même nom de famille que vous, le même prénom, soit né dans la même ville et ait la même taille. En dehors de la photo et de la signature, quelle sera la différence entre vos deux cartes d'identité ? Son numéro !
Suivant le même principe, on va donner à chaque animal un numéro d'identité. La colonne qu'on ajoutera s’appellera donc id, et il s'agira d'un INT, toujours positif donc UNSIGNED. Selon la taille de l'élevage (la taille actuelle mais aussi la taille qu'on imagine qu'il pourrait avoir dans le futur !), il peut être plus intéressant d'utiliser un SMALLINT, voire un MEDIUMINT. Comme il est peu probable que l'on dépasse les 65000 animaux, on utilisera SMALLINT. Attention, il faut bien considérer tous les animaux qui entreront un jour dans la base, pas uniquement le nombre d'animaux présents en même temps dans l'élevage. En effet, si l'on supprime pour une raison ou une autre un animal de la base, il n'est pas question de réutiliser son numéro d'identité.

Ce champ ne pourra bien sûr pas être NULL, sinon il perdrait toute son utilité.

Clé primaire

La clé primaire d'une table est une contrainte d'unicité, composée d'une ou plusieurs colonnes. La clé primaire d'une ligne permet d'identifier de manière unique cette ligne dans la table. Si l'on parle de la ligne dont la clé primaire vaut x, il ne doit y avoir aucun doute quant à la ligne dont on parle. Lorsqu'une table possède une clé primaire (et il est extrêmement conseillé de définir une clé primaire pour chaque table créée), celle-ci doit être définie.
Cette définition correspond exactement au numéro d'identité dont nous venons de parler. Nous définirons donc id comme la clé primaire de la table Animal, en utilisant les mots-clés PRIMARY KEY(id).
Lorsque vous insérerez une nouvelle ligne dans la table, MySQL vérifiera que vous insérez bien un id, et que cet id n'existe pas encore dans la table. Si vous ne respectez pas ces deux contraintes, MySQL n’insérera pas la ligne et vous renverra une erreur.

Par exemple, dans le cas où vous essayez d'insérer un id qui existe déjà, vous obtiendrez l'erreur suivante :

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Je n'en dirai pas plus pour l'instant sur les clés primaires mais j'y reviendrai de manière détaillée dans la seconde partie de ce cours.

Auto-incrémentation

Il faut donc, pour chaque animal, décider d'une valeur pour id. Le plus simple, et le plus logique, est de donner le numéro 1 au premier individu enregistré, puis le numéro 2 au second, etc.
Mais si vous ne vous souvenez pas quel numéro vous avez utilisé en dernier, pour insérer un nouvel animal il faudra récupérer cette information dans la base, ensuite seulement vous pourrez ajouter une ligne en lui donnant comme id le dernierid utilisé + 1.
C'est bien sûr faisable, mais c'est fastidieux… Heureusement, il est possible de demander à MySQL de faire tout ça pour nous !
Comment ? En utilisant l'auto-incrémentation des colonnes. Incrémenter veut dire "ajouter une valeur fixée". Donc, si l'on déclare qu'une colonne doit s'auto-incrémenter (grâce au mot-clé AUTO_INCREMENT), plus besoin de chercher quelle valeur on va mettre dedans lors de la prochaine insertion. MySQL va chercher ça tout seul comme un grand en prenant la dernière valeur insérée et en l'incrémentant de 1.

Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Définition des colonnes Les moteurs de tables

Les moteurs de tables

Introduction aux clés primaires Syntaxe de CREATE TABLE

Les moteurs de tables

Les moteurs de tables sont une spécificité de MySQL. Ce sont des moteurs de stockage. Cela permet de gérer différemment les tables selon l'utilité qu'on en a. Je ne vais pas vous détailler tous les moteurs de tables existant. Si vous voulez plus d'informations, je vous renvoie à la documentation officielle.
Les deux moteurs les plus connus sont MyISAM et InnoDB.

MyISAM

C'est le moteur par défaut. Les commandes d'insertion et sélection de données sont particulièrement rapides sur les tables utilisant ce moteur. Cependant, il ne gère pas certaines fonctionnalités importantes comme les clés étrangères, qui permettent de vérifier l'intégrité d'une référence d'une table à une autre table (voir la deuxième partie du cours) ou les transactions, qui permettent de réaliser des séries de modifications "en bloc" ou au contraire d'annuler ces modifications (voir la cinquième partie du cours).

InnoDB

Plus lent et plus gourmand en ressources que MyISAM, ce moteur gère les clés étrangères et les transactions. Étant donné que nous nous servirons des clés étrangères dès la deuxième partie, c'est celui-là que nous allons utiliser.
De plus, en cas de crash du serveur, il possède un système de récupération automatique des données.

Préciser un moteur lors de la création de la table

Pour qu'une table utilise le moteur de notre choix, il suffit d'ajouter ceci à la fin de la commande de création :

ENGINE = moteur;

En remplaçant bien sûr "moteur" par le nom du moteur que nous voulons utiliser, ici InnoDB :

ENGINE = INNODB;
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Introduction aux clés primaires Syntaxe de CREATE TABLE

Syntaxe de CREATE TABLE

Les moteurs de tables Suppression d'une table

Syntaxe de CREATE TABLE

Avant de voir la syntaxe permettant de créer une table, résumons un peu. Nous voulons donc créer une table Animal avec six colonnes telles que décrites dans le tableau suivant.

Caractéristique

Nom du champ

Type

NULL?

Divers

Numéro d'identité

id

SMALLINT

Non

Clé primaire + auto-incrément + UNSIGNED

Espèce

espece

VARCHAR(40)

Non

-

Sexe

sexe

CHAR(1)

Oui

-

Date de naissance

date_naissance

DATETIME

Non

-

Commentaires

commentaires

TEXT

Oui

-

Nom

nom

VARCHAR(30)

Oui

-

Syntaxe

Par souci de clarté, je vais diviser l'explication de la syntaxe de CREATE TABLE en deux. La première partie vous donne la syntaxe globale de la commande, et la deuxième partie s'attarde sur la description des colonnes créées dans la table.

Création de la table
CREATE TABLE [IF NOT EXISTS] Nom_table (
    colonne1 description_colonne1,
    [colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...,]
    [PRIMARY KEY (colonne_clé_primaire)]
)
[ENGINE=moteur];

Le IF NOT EXISTS est facultatif (d'où l'utilisation de crochets [ ]), et a le même rôle que dans la commande CREATE DATABASE : si une table de ce nom existe déjà dans la base de données, la requête renverra un warning plutôt qu'une erreur si IF NOT EXISTS est spécifié.
Ce n'est pas non plus une erreur de ne pas préciser la clé primaire directement à la création de la table. Il est tout à fait possible de l'ajouter par la suite. Nous verrons comment un peu plus tard.

Définition des colonnes

Pour définir une colonne, il faut donc donner son nom en premier, puis sa description. La description est constituée au minimum du type de la colonne. Exemple :

nom VARCHAR(30),
sexe CHAR(1)

C'est aussi dans la description que l'on précise si la colonne peut contenir NULL ou pas (par défaut, NULL est autorisé). Exemple :

espece VARCHAR(40) NOT NULL,
date_naissance DATETIME NOT NULL

L'auto-incrémentation se définit également à cet endroit. Notez qu'il est également possible de définir une colonne comme étant la clé primaire dans sa description. Il ne faut alors plus l'indiquer après la définition de toutes les colonnes. Je vous conseille néanmoins de ne pas l'indiquer à cet endroit, nous verrons plus tard pourquoi.

id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT [PRIMARY KEY]

Enfin, on peut donner une valeur par défaut au champ. Si lorsque l'on insère une ligne, aucune valeur n'est précisée pour le champ, c'est la valeur par défaut qui sera utilisée. Notez que si une colonne est autorisée à contenir NULL et qu'on ne précise pas de valeur par défaut, alors NULL est implicitement considéré comme valeur par défaut.

Exemple :

espece VARCHAR(40) NOT NULL DEFAULT 'chien'
Application : création de Animal

Si l'on met tout cela ensemble pour créer la table Animal (je rappelle que nous utiliserons le moteur InnoDB), on a donc :

CREATE TABLE Animal (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    espece VARCHAR(40) NOT NULL,
    sexe CHAR(1),
    date_naissance DATETIME NOT NULL,
    nom VARCHAR(30),
    commentaires TEXT,
    PRIMARY KEY (id)
)
ENGINE=INNODB;
Vérifications

Au cas où vous ne me croiriez pas (et aussi un peu parce que cela pourrait vous être utile un jour), voici deux commandes vous permettant de vérifier que vous avez bien créé une jolie table Animal avec les six colonnes que vous vouliez.

SHOW TABLES;      -- liste les tables de la base de données

DESCRIBE Animal;  -- liste les colonnes de la table avec leurs caractéristiques
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Les moteurs de tables Suppression d'une table

Suppression d'une table

Syntaxe de CREATE TABLE Modification d'une table

Suppression d'une table

La commande pour supprimer une table est la même que celle pour supprimer une base de données. Elle est, bien sûr, à utiliser avec prudence, car irréversible.

DROP TABLE Animal;
En résumé
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Syntaxe de CREATE TABLE Modification d'une table

Modification d'une table

Suppression d'une table Syntaxe de la requête

La création et la suppression de tables étant acquises, parlons maintenant des requêtes permettant de modifier une table.
Plus précisément, ce chapitre portera sur la modification des colonnes d'une table (ajout d'une colonne, modification, suppression de colonnes).
Il est possible de modifier d'autres éléments (des contraintes, ou des index par exemple), mais cela nécessite des notions que vous ne possédez pas encore, aussi n'en parlerai-je pas ici.

Notez qu'idéalement, il faut penser à l'avance à la structure de votre base et créer toutes vos tables directement et proprement, de manière à ne les modifier qu'exceptionnellement.

Syntaxe de la requête

Modification d'une table Ajout et suppression d'une colonne

Syntaxe de la requête

Lorsque l'on modifie une table, on peut vouloir lui ajouter, retirer ou modifier quelque chose. Dans les trois cas, c'est la commande ALTER TABLE qui sera utilisée, une variante existant pour chacune des opérations :

ALTER TABLE nom_table ADD ... -- permet d'ajouter quelque chose (une colonne par exemple)

ALTER TABLE nom_table DROP ... -- permet de retirer quelque chose 

ALTER TABLE nom_table CHANGE ...
ALTER TABLE nom_table MODIFY ... -- permettent de modifier une colonne
Créons une table pour faire joujou

Dans la seconde partie de ce tutoriel, nous devrons faire quelques modifications sur notre table Animal, mais en attendant, je vous propose d'utiliser la table suivante, si vous avez envie de tester les différentes possibilités d'ALTER TABLE :

CREATE TABLE Test_tuto (
	id INT NOT NULL,
	nom VARCHAR(10) NOT NULL,
        PRIMARY KEY(id)
);
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Modification d'une table Ajout et suppression d'une colonne

Ajout et suppression d'une colonne

Syntaxe de la requête Modification de colonne

Ajout et suppression d'une colonne

Ajout

On utilise la syntaxe suivante :

ALTER TABLE nom_table 
ADD [COLUMN] nom_colonne description_colonne;

Le [COLUMN] est facultatif, donc si à la suite de ADD vous ne précisez pas ce que vous voulez ajouter, MySQL considérera qu'il s'agit d'une colonne.
description_colonne correspond à la même chose que lorsque l'on crée une table. Il contient le type de donnée et éventuellement NULL ou NOT NULL, etc.

Ajoutons une colonne date_insertion à notre table de test. Il s'agit d'une date, donc une colonne de type DATE convient parfaitement. Disons que cette colonne ne peut pas être NULL (si c'est dans la table, ça a forcément été inséré). Cela nous donne :

ALTER TABLE Test_tuto 
ADD COLUMN date_insertion DATE NOT NULL;

Un petit DESCRIBE Test_tuto; vous permettra de vérifier les changements apportés.

Suppression

La syntaxe de ALTER TABLE ... DROP ... est très simple :

ALTER TABLE nom_table 
DROP [COLUMN] nom_colonne;

Comme pour les ajouts, le mot COLUMN est facultatif. Par défaut, MySQL considérera que vous parlez d'une colonne.

Exemple : nous allons supprimer la colonne date_insertion, que nous remercions pour son passage éclair dans le cours.

ALTER TABLE Test_tuto 
DROP COLUMN date_insertion; -- Suppression de la colonne date_insertion
Fatigué(e) de lire sur un écran ? Découvrez ce cours en livre.

Syntaxe de la requête Modification de colonne

Modification de colonne

Ajout et suppression d'une colonne Insertion de données

Modification de colonne

Changement du nom de la colonne

Vous pouvez utiliser la commande suivante pour changer le nom d'une colonne :

ALTER TABLE nom_table 
CHANGE ancien_nom nouveau_nom description_colonne;

Par exemple, pour renommer la colonne nom en prenom, vous pouvez écrire

ALTER TABLE Test_tuto 
CHANGE nom prenom VARCHAR(10) NOT NULL;

Attention, la description de la colonne doit être complète, sinon elle sera également modifiée. Si vous ne précisez pas NOT NULL dans la commande précédente, prenom pourra contenir NULL, alors que du temps où elle s'appelait nom, cela lui était interdit.

Changement du type de données

Les mots-clés CHANGE et MODIFY peuvent être utilisés pour changer le type de donnée de la colonne, mais aussi changer la valeur par défaut ou ajouter/supprimer une propriété AUTO_INCREMENT. Si vous utilisez CHANGE, vous pouvez, comme on vient de le voir, renommer la colonne en même temps. Si vous ne désirez pas la renommer, il suffit d'indiquer deux fois le même nom.
Voici les syntaxes possibles :

ALTER TABLE nom_table 
CHANGE ancien_nom nouveau_nom nouvelle_description;

ALTER TABLE nom_table 
MODIFY nom_colonne nouvelle_description;

Des exemples pour illustrer :

ALTER TABLE Test_tuto 
CHANGE prenom nom VARCHAR(30) NOT NULL; -- Changement du type + changement du nom

ALTER TABLE Test_tuto 
CHANGE id id BIGINT NOT NULL; -- Changement du type sans renommer

ALTER TABLE Test_tuto
MODIFY id BIGINT NOT NULL AUTO_INCREMENT; -- Ajout de l'auto-incrémentation

ALTER TABLE Test_tuto 
MODIFY nom VARCHAR(30) NOT NULL DEFAULT 'Blabla'; -- Changement de la description (même type mais ajout d'une valeur par défaut)

Il existe pas mal d'autres possibilités et combinaisons pour la commande ALTER TABLE mais en faire la liste complète ne rentre pas dans le cadre de ce cours. Si vous ne trouvez pas votre bonheur ici, je vous conseille de le chercher dans la documentation officielle.

En résumé