Tutoriel : Administrez vos bases de données avec 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 :

• un système permettant de gérer cette base ;

• un langage pour transmettre des instructions à la base de données (par l'intermédiaire du système de gestion).

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 :

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" :

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

• la sélection (ou restriction) : obtenir les lignes de A répondant à certains critères ;

• la projection : obtenir une partie des attributs des lignes de A ;

• l'union - A \cup B : obtenir tout ce qui se trouve dans la relation A ou dans la relation B ;

• l'intersection - A \cap B : obtenir tout ce qui se trouve à la fois dans la relation A et dans la relation B ;

• la différence - A - B : obtenir ce qui se trouve dans la relation A mais pas dans la relation B ;

• la jointure - A \bowtie B : obtenir l'ensemble des lignes provenant de la liaison de la relation A et de la relation B à l'aide d'une information commune.

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 !

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é.

Présentation succincte de MySQL...

MySQL 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

David 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.

... 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

Oracle, é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

Comme 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

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

La 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).

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

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é

• MySQL est un Système de Gestion de Bases de Données Relationnelles (SGBDR) basé sur le modèle client-serveur.

• Le langage SQL est utilisé pour communiquer entre le client et le serveur.

• Dans une base de données relationnelle, les données sont représentées sous forme de tables.

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

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 :

• primo, parce que je veux que vous maîtrisiez vraiment les commandes. En effet, les interfaces graphiques permettent de faire pas mal de choses, mais une fois que vous serez bien lancés, vous vous mettrez à faire des choses subtiles et compliquées, et il ne serait pas étonnant qu'il vous soit obligatoire d'écrire vous-mêmes vos requêtes ;

• ensuite, parce qu'il est fort probable que vous désiriez utiliser MySQL en combinaison avec un autre langage de programmation (si ce n'est pas votre but immédiat, ça viendra probablement un jour). Or, dans du code PHP (ou Java, ou Python, etc.), on ne va pas écrire "Ouvre PhpMyAdmin et clique sur le bon bouton pour que je puisse insérer une donnée dans la base". On va devoir écrire en dur les requêtes. Il faut donc que vous sachiez comment faire.

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

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.

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.

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

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

• Entrez votre mot de passe si nécessaire

• Tapez Ctrl + Z

bg

• Tapez Ctrl + D

• Quittez le terminal

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>'

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 :

• un serveur de base de données MySQL ;

• plusieurs logiciels clients qui permettent d'interagir avec le serveur.

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 : c'est-à-dire l'endroit où est localisé le serveur ;

• le nom d'utilisateur ;

• et le mot de passe de l'utilisateur.

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.

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 !"

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 :

• d'écrire une instruction sur plusieurs lignes ;

• d'écrire plusieurs instructions sur une seule ligne.

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 :

• # : tout ce qui suit ce caractère sera considéré comme commentaire

• -- : la syntaxe normale est acceptée uniquement si les deux tirets sont suivis d'une espace au moins

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 :

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 :

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 :

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 :

• GRANT ALL PRIVILEGES : Cette commande permet d'attribuer tous les droits (c'est-à-dire insertions de données, sélections, modifications, suppressions…)

• ON elevage.* : définit les bases de données et les tables sur lesquelles ces droits sont acquis. Donc ici, on donne les droits sur la base "elevage" (qui n'existe pas encore, mais ce n'est pas grave, nous la créerons plus tard), pour toutes les tables de cette base (grâce à *).

• TO 'sdz' : définit l'utilisateur auquel on accorde ces droits. Si l'utilisateur n'existe pas, il est créé.

• @'localhost' : définit à partir d'où l'utilisateur peut exercer ces droits. Dans notre cas, 'localhost', donc il devra être connecté à partir de cet ordinateur.

• IDENTIFIED BY 'mot_de_passe': définit le mot de passe de l'utilisateur.

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é

• MySQL peut s'utiliser en ligne de commande ou avec une interface graphique.

• Pour se connecter à MySQL en ligne de commande, on utilise : mysql -u utilisateur [-h hôte] -p.

• Pour terminer une instruction SQL, on utilise le caractère ;.

• En SQL, les chaînes de caractères doivent être entourées de guillemets simples '.

• Lorsque l'on se connecte à MySQL, il faut définir l'encodage utilisé, soit directement dans la connexion avec l'option --default-character-set, soit avec la commande SET NAMES.

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.

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 :

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.

• La précision définit le nombre de chiffres significatifs stockés, donc les 0 à gauche ne comptent pas. En effet 0024 est équivalent à 24. Il n'y a donc que deux chiffres significatifs dans 0024.

• L'échelle définit le nombre de chiffres après la virgule.

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

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 :

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

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 :

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.

• Une chaîne binaire traite directement l'octet, et pas le caractère que l'octet représente. Donc par exemple, une recherche sur une chaîne binaire sera toujours sensible à la casse, puisque "A" (code binaire : 01000001) sera toujours différent de "a" (code binaire : 01100001).

• Tous les caractères sont utilisables, y compris les fameux caractères de contrôle non-affichables définis dans la table ASCII.

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 :

• si vous essayez d'introduire une chaîne non-autorisée, MySQL stockera une chaîne vide '' dans le champ ;

• si vous autorisez le champ à ne pas contenir de valeur (vous verrez comment faire ça dans le chapitre sur la création des tables), le champ contiendra NULL, qui correspond à "pas de valeur" en SQL (et dans beaucoup de langages informatiques).

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

• soit remplir directement avec la valeur choisie ("chat", "chien" ou "tortue" dans notre exemple) ;

• soit utiliser l'index de la valeur, c'est-à-dire le nombre associé par MySQL à la valeur. Ce nombre est compris entre 1 et le nombre de valeurs définies. L'index est attribué selon l'ordre dans lequel les valeurs ont été données lors de la création du champ. De plus, la chaîne vide (stockée en cas de valeur non-autorisée) correspond à l'index 0. Le tableau suivant reprend les valeurs d'index pour notre exemple précédent : le champ espece.

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 :

• '' (chaîne vide ) ;

• 'chat' ;

• 'chat,tortue' ;

• 'chat,chien,tortue' ;

• 'chien,tortue' ;

• ...

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".

• 000 signifie qu'aucune valeur n'est présente.

• 001 signifie que 'chat' est présent.

• 100 signifie que 'tortue' est présent.

• 110 signifie que 'chien' et 'tortue' sont présents.

• ...

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.

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 !

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.

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

• Vous pouvez faire de la colonne categorie une simple colonne VARCHAR(100). Le désavantage est que vous ne pouvez pas limiter les valeurs entrées dans cette colonne. Cette vérification pourra éventuellement se faire à un autre niveau (par exemple au niveau du PHP si vous faites un site web avec PHP et MySQL).

• Vous pouvez aussi ajouter une table Categorie qui reprendra toutes les catégories possibles. Dans la table des éléments, il suffira alors de stocker une référence vers la catégorie de l'élément.

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.

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

• 'AAAA-MM-JJ' (c'est sous ce format-ci qu'une DATE est stockée dans MySQL)

• 'AAMMJJ'

• 'AAAA/MM/JJ'

• 'AA+MM+JJ'

• 'AAAA%MM%JJ'

• AAAAMMJJ (nombre)

• AAMMJJ (nombre)

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 :

• si l'année donnée est entre 00 et 69, on utilisera le 21e siècle, on ira donc de 2000 à 2069 ;

• par contre, si l'année est comprise entre 70 et 99, on utilisera le 20e siècle, donc entre 1970 et 1999.

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

• 'AAAA-MM-JJ HH:MM:SS' (c'est sous ce format-ci qu'un DATETIME est stocké dans MySQL)

• 'AA*MM*JJ HH+MM+SS'

• AAAAMMJJHHMMSS (nombre)

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 :

• 'HH:MM:SS'

• 'HHH:MM:SS'

• 'MM:SS'

• 'J HH:MM:SS'

• 'HHMMSS'

• HHMMSS (nombre)

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

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é

• MySQL définit plusieurs types de données : des numériques entiers, des numériques décimaux, des textes alphanumériques, des chaînes binaires alphanumériques et des données temporelles.

• Il est important de toujours utiliser le type de données adapté à la situation.

• SET et ENUM sont des types de données qui n'existent que chez MySQL. Il vaut donc mieux éviter de les utiliser.

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.

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 :

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é

• Pour créer une base de données, on utilise la commande CREATE DATABASE nom_base.

• Pour supprimer une base de données : DROP DATABASE nom_base.

• À chaque connexion à MySQL, il faut préciser avec quelle base on va travailler, avec USE nom_base.

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.

• Espèce : on a des chats, des chiens et des tortues pour l'instant. On peut donc caractériser l'espèce par un ou plusieurs mots. Ce sera donc un champ de type alphanumérique.
  Les noms d'espèces sont relativement courts, mais n'ont pas tous la même longueur. On choisira donc un VARCHAR. Mais quelle longueur lui donner ? Beaucoup de noms d'espèces ne contiennent qu'un mot, mais "harfang des neiges", par exemple, en contient trois, et 18 caractères. Histoire de ne prendre aucun risque, autant autoriser jusqu'à 40 caractères pour l'espèce.

• Sexe : ici, deux choix possibles (mâle ou femelle). Le risque de voir un troisième sexe apparaître est extrêmement faible. Par conséquent, il serait possible d'utiliser un ENUM. Cependant, ENUM reste un type non standard. Pour cette raison, nous utiliserons plutôt une colonne CHAR(1), contenant soit 'M' (mâle), soit 'F' (femelle).

• Date de naissance : pas besoin de réfléchir beaucoup ici. Il s'agit d'une date, donc soit un DATETIME, soit une DATE. L'heure de la naissance est-elle importante ? Disons que oui, du moins pour les soins lors des premiers jours. DATETIME donc !

• Commentaires : de nouveau un type alphanumérique évidemment, mais on a ici aucune idée de la longueur. Ce sera sans doute succinct mais il faut prévoir un minimum de place quand même. Ce sera donc un champ TEXT.

• Nom : plutôt facile à déterminer. On prendra simplement un VARCHAR(30). On ne pourra pas appeler nos tortues "Petite maison dans la prairie verdoyante", mais c'est amplement suffisant pour "Rox" ou "Roucky".

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

• Espèce : un éleveur digne de ce nom connaît l'espèce des animaux qu'il élève. On n'autorisera donc pas la colonne espece à être NULL.

• Sexe : le sexe de certains animaux est très difficile à déterminer à la naissance. Il n'est donc pas impossible qu'on doive attendre plusieurs semaines pour savoir si "Rox" est en réalité "Roxa". Par conséquent, la colonne sexe peut contenir NULL.

• Date de naissance : pour garantir la pureté des races, on ne travaille qu'avec des individus dont on connaît la provenance (en cas d'apport extérieur), les parents, la date de naissance. Cette colonne ne peut donc pas être NULL.

• Commentaires : ce champ peut très bien ne rien contenir, si la bestiole concernée ne présente absolument aucune particularité.

• Nom : en cas de panne d'inspiration (ça a l'air facile comme ça mais, une chatte pouvant avoir entre 1 et 8 petits d'un coup, il est parfois difficile d'inventer 8 noms originaux comme ça !), il vaut mieux autoriser cette colonne à être NULL.

Récapitulatif

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

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.

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;

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.

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

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é

• Avant de créer une table, il faut définir ses colonnes. Pour cela, il faut donc déterminer le type de chacune des colonnes et décider si elles peuvent ou non contenir NULL (c'est-à-dire ne contenir aucune donnée).

• Chaque table créée doit définir une clé primaire, donc une colonne qui permettra d'identifier chaque ligne de manière unique.

• Le moteur d'une table définit la manière dont elle est gérée. Nous utiliserons le moteur InnoDB, qui permet notamment de définir des relations entre plusieurs tables.

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

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

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é

• La commande ALTER TABLE permet de modifier une table

• Lorsque l'on ajoute ou modifie une colonne, il faut toujours préciser sa (nouvelle) description complète (type, valeur par défaut, auto-incrément éventuel)

Syntaxe de INSERT

Deux possibilités s'offrent à nous lorsque l'on veut insérer une ligne dans une table : soit donner une valeur pour chaque colonne de la ligne, soit ne donner les valeurs que de certaines colonnes, auquel cas il faut bien sûr préciser de quelles colonnes il s'agit.

Insertion sans préciser les colonnes

Je rappelle pour les distraits que notre table Animal est composée de six colonnes : id, espece, sexe, date_naissance, nom et commentaires.

Voici donc la syntaxe à utiliser pour insérer une ligne dans Animal, sans renseigner les colonnes pour lesquelles on donne une valeur (implicitement, MySQL considère que l'on donne une valeur pour chaque colonne de la table).

INSERT INTO Animal 
VALUES (1, 'chien', 'M', '2010-04-05 13:43:00', 'Rox', 'Mordille beaucoup');

Deuxième exemple : cette fois-ci, on ne connaît pas le sexe et on n'a aucun commentaire à faire sur la bestiole :

INSERT INTO Animal 
VALUES (2, 'chat', NULL, '2010-03-24 02:23:00', 'Roucky', NULL);

Troisième et dernier exemple : on donne NULL comme valeur d'id, ce qui en principe est impossible puisque id est défini comme NOT NULL et comme clé primaire. Cependant, l'auto-incrémentation fait que MySQL va calculer tout seul comme un grand quel id il faut donner à la ligne (ici : 3).

INSERT INTO Animal 
VALUES (NULL , 'chat', 'F', '2010-09-13 15:02:00', 'Schtroumpfette', NULL);

Vous avez maintenant trois animaux dans votre table :

Pour vérifier, vous pouvez utiliser la requête suivante :

SELECT * FROM Animal;

Deux choses importantes à retenir ici.

• id est un nombre, on ne met donc pas de guillemets autour. Par contre, l'espèce, le nom, la date de naissance et le sexe sont donnés sous forme de chaînes de caractères. Les guillemets sont donc indispensables. Quant à NULL, il s'agit d'un marqueur SQL qui, je rappelle, signifie "pas de valeur". Pas de guillemets donc.

• Les valeurs des colonnes sont données dans le bon ordre (donc dans l'ordre donné lors de la création de la table). C'est indispensable évidemment. Si vous échangez le nom et l'espèce par exemple, comment MySQL pourrait-il le savoir ?

Insertion en précisant les colonnes

Dans la requête, nous allons donc écrire explicitement à quelle(s) colonne(s) nous donnons une valeur. Ceci va permettre deux choses.

• On ne doit plus donner les valeurs dans l'ordre de création des colonnes, mais dans l'ordre précisé par la requête.

• On n'est plus obligé de donner une valeur à chaque colonne ; plus besoin de NULL lorsqu'on n'a pas de valeur à mettre.

Quelques exemples :

INSERT INTO Animal (espece, sexe, date_naissance) 
    VALUES ('tortue', 'F', '2009-08-03 05:12:00');
INSERT INTO Animal (nom, commentaires, date_naissance, espece) 
    VALUES ('Choupi', 'Né sans oreille gauche', '2010-10-03 16:44:00', 'chat');
INSERT INTO Animal (espece, date_naissance, commentaires, nom, sexe) 
    VALUES ('tortue', '2009-06-13 08:17:00', 'Carapace bizarre', 'Bobosse', 'F');

Ce qui vous donne trois animaux supplémentaires (donc six en tout, il faut suivre !)

Insertion multiple

Si vous avez plusieurs lignes à introduire, il est possible de le faire en une seule requête de la manière suivante :

INSERT INTO Animal (espece, sexe, date_naissance, nom) 
VALUES ('chien', 'F', '2008-12-06 05:18:00', 'Caroline'),
        ('chat', 'M', '2008-09-11 15:38:00', 'Bagherra'),
        ('tortue', NULL, '2010-08-23 05:18:00', NULL);

Bien entendu, vous êtes alors obligés de préciser les mêmes colonnes pour chaque entrée, quitte à mettre NULL pour certaines. Mais avouez que ça fait quand même moins à écrire !

Syntaxe alternative de MySQL

MySQL propose une syntaxe alternative à INSERT INTO ... VALUES ... pour insérer des données dans une table.

INSERT INTO Animal 
SET nom='Bobo', espece='chien', sexe='M', date_naissance='2010-07-21 15:41:00';

Cette syntaxe présente deux avantages.

• Le fait d'avoir l'un à côté de l'autre la colonne et la valeur qu'on lui attribue (nom = 'Bobo') rend la syntaxe plus lisible et plus facile à manipuler. En effet, ici il n'y a que six colonnes, mais imaginez une table avec 20, voire 100 colonnes. Difficile d'être sûrs que l'ordre dans lequel on a déclaré les colonnes est bien le même que l'ordre des valeurs qu'on leur donne…

• Elle est très semblable à la syntaxe de UPDATE, que nous verrons plus tard et qui permet de modifier des données existantes. C'est donc moins de choses à retenir (mais bon, une requête de plus ou de moins, ce n'est pas non plus énorme…)

En effet, cette syntaxe présente deux défauts majeurs.

• Elle est propre à MySQL. Ce n'est pas du SQL pur. De ce fait, si vous décidez un jour de migrer votre base vers un autre SGBDR, vous devrez réécrire toutes les requêtes INSERT utilisant cette syntaxe.

• Elle ne permet pas l'insertion multiple.

Utilisation de fichiers externes

Maintenant que vous savez insérer des données, je vous propose de remplir un peu cette table, histoire qu'on puisse s'amuser par la suite.
Rassurez-vous, je ne vais pas vous demander d'inventer cinquante bestioles et d'écrire une à une les requêtes permettant de les insérer. Je vous ai prémâché le boulot. De plus, ça nous permettra d'avoir, vous et moi, la même chose dans notre base. Ce sera ainsi plus facile de vérifier que vos requêtes font bien ce qu'elles doivent faire.
Et pour éviter d'écrire vous-mêmes toutes les requêtes d'insertion, nous allons donc voir comment on peut utiliser un fichier texte pour interagir avec notre base de données.

Exécuter des commandes SQL à partir d'un fichier

Écrire toutes les commandes à la main dans la console, ça peut vite devenir pénible. Quand c'est une petite requête, pas de problème. Mais quand vous avez une longue requête, ou beaucoup de requêtes à faire, ça peut être assez long.

Une solution sympathique est d'écrire les requêtes dans un fichier texte, puis de dire à MySQL d'exécuter les requêtes contenues dans ce fichier. Et pour lui dire ça, c'est facile :

SOURCE monFichier.sql;

Ou

\. monFichier.sql;

Ces deux commandes sont équivalentes et vont exécuter le fichier monFichier.sql. Il n'est pas indispensable de lui donner l'extension .sql, mais je préfère le faire pour repérer mes fichiers SQL directement. De plus, si vous utilisez un éditeur de texte un peu plus évolué que le bloc-note (ou textEdit sur Mac), cela colorera votre code SQL, ce qui vous facilitera aussi les choses.

Attention : si vous ne lui indiquez pas le chemin, MySQL va aller chercher votre fichier dans le dossier où vous étiez lors de votre connexion.

Exemple : on donne le chemin complet vers le fichier

SOURCE C:\Document and Settings\dossierX\monFichier.sql;

Insérer des données à partir d'un fichier formaté

Par fichier formaté, j'entends un fichier qui suit certaines règles de format. Un exemple typique serait les fichiers .csv. Ces fichiers contiennent un certain nombre de données et sont organisés en tables. Chaque ligne correspond à une entrée, et les colonnes de la table sont séparées par un caractère défini (souvent une virgule ou un point-virgule). Ceci par exemple, est un format csv :

nom;prenom;date_naissance
Charles;Myeur;1994-12-30
Bruno;Debor;1978-05-12
Mireille;Franelli;1990-08-23

Ce type de fichier est facile à produire (et à lire) avec un logiciel de type tableur (Microsoft Excel, ExcelViewer, Numbers…). La bonne nouvelle est qu'il est aussi possible de lire ce type de fichier avec MySQL, afin de remplir une table avec les données contenues dans le fichier.
La commande SQL permettant cela est LOAD DATA INFILE, dont voici la syntaxe :

LOAD DATA [LOCAL] INFILE 'nom_fichier'
INTO TABLE nom_table
[FIELDS
    [TERMINATED BY '\t']
    [ENCLOSED BY '']
    [ESCAPED BY '\\' ]
]
[LINES 
    [STARTING BY '']    
    [TERMINATED BY '\n']
]
[IGNORE nombre LINES]
[(nom_colonne,...)];

Le mot-clé LOCAL sert à spécifier si le fichier se trouve côté client (dans ce cas, on utilise LOCAL) ou côté serveur (auquel cas, on ne met pas LOCAL dans la commande). Si le fichier se trouve du côté serveur, il est obligatoire, pour des raisons de sécurité, qu'il soit dans le répertoire de la base de données, c'est-à-dire dans le répertoire créé par MySQL à la création de la base de données, et qui contient les fichiers dans lesquels sont stockées les données de la base. Pour ma part, j'utiliserai toujours LOCAL, afin de pouvoir mettre simplement mes fichiers dans mon dossier de travail.

Les clauses FIELDS et LINES permettent de définir le format de fichier utilisé. FIELDS se rapporte aux colonnes, et LINES aux lignes (si si ^^ ). Ces deux clauses sont facultatives. Les valeurs que j'ai mises ci-dessus sont les valeurs par défaut.

Si vous précisez une clause FIELDS, il faut lui donner au moins une des trois "sous-clauses".

• TERMINATED BY, qui définit le caractère séparant les colonnes, entre guillemets bien sûr. ' ' correspond à une tabulation. C'est le caractère par défaut.

• ENCLOSED BY, qui définit le caractère entourant les valeurs dans chaque colonne (vide par défaut).

• ESCAPED BY, qui définit le caractère d'échappement pour les caractères spéciaux. Si par exemple vous définissez vos valeurs comme entourées d'apostrophes, mais que certaines valeurs contiennent des apostrophes, il faut échapper ces apostrophes "internes" afin qu'elles ne soient pas considérées comme un début ou une fin de valeur. Par défaut, il s'agit du \ habituel. Remarquez qu'il faut lui-même l'échapper dans la clause.

De même pour LINES, si vous l'utilisez, il faut lui donner une ou deux sous-clauses.

• STARTING BY, qui définit le caractère de début de ligne (vide par défaut).

• TERMINATED BY, qui définit le caractère de fin de ligne (' ' par défaut, mais attention : les fichiers générés sous Windows ont souvent ' ' comme caractère de fin de ligne).

La clause IGNORE nombre LINES permet… d'ignorer un certain nombre de lignes. Par exemple, si la première ligne de votre fichier contient les noms des colonnes, vous ne voulez pas l'insérer dans votre table. Il suffit alors d'utiliser IGNORE 1 LINES.

Enfin, vous pouvez préciser le nom des colonnes présentes dans votre fichier. Attention évidemment à ce que les colonnes absentes acceptent NULL ou soient auto-incrémentées.

Si je reprends mon exemple, en imaginant que nous ayons une table Personne contenant les colonnes id (clé primaire auto-incrémentée), nom, prenom, date_naissance et adresse (qui peut être NULL).

nom;prenom;date_naissance
Charles;Myeur;1994-12-30
Bruno;Debor;1978-05-12
Mireille;Franelli;1990-08-23

Si ce fichier est enregistré sous le nom personne.csv, il vous suffit d'exécuter la commande suivante pour enregistrer ces trois lignes dans la table Personne, en spécifiant si nécessaire le chemin complet vers personne.csv :

LOAD DATA LOCAL INFILE 'personne.csv'
INTO TABLE Personne
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n' -- ou '\r\n' selon l'ordinateur et le programme utilisés pour créer le fichier
IGNORE 1 LINES
(nom,prenom,date_naissance);

Remplissage de la base

Nous allons utiliser les deux techniques que je viens de vous montrer pour remplir un peu notre base. N'oubliez pas de modifier les commandes données pour ajouter le chemin vers vos fichiers,

Exécution de commandes SQL

Voici donc le code que je vous demande de copier-coller dans votre éditeur de texte préféré, puis de le sauver sous le nom remplissageAnimal.sql (ou un autre nom de votre choix).

INSERT INTO Animal (espece, sexe, date_naissance, nom, commentaires) VALUES 
('chien', 'F', '2008-02-20 15:45:00' , 'Canaille', NULL),
('chien', 'F','2009-05-26 08:54:00'  , 'Cali', NULL),
('chien', 'F','2007-04-24 12:54:00' , 'Rouquine', NULL),
('chien', 'F','2009-05-26 08:56:00' , 'Fila', NULL),
('chien', 'F','2008-02-20 15:47:00' , 'Anya', NULL),
('chien', 'F','2009-05-26 08:50:00' ,'Louya' , NULL),
('chien', 'F', '2008-03-10 13:45:00','Welva' , NULL),
('chien', 'F','2007-04-24 12:59:00' ,'Zira' , NULL),
('chien', 'F', '2009-05-26 09:02:00','Java' , NULL),
('chien', 'M','2007-04-24 12:45:00' ,'Balou' , NULL),
('chien', 'M','2008-03-10 13:43:00' ,'Pataud' , NULL),
('chien', 'M','2007-04-24 12:42:00' , 'Bouli', NULL),
('chien', 'M', '2009-03-05 13:54:00','Zoulou' , NULL),
('chien', 'M','2007-04-12 05:23:00' ,'Cartouche' , NULL),
('chien', 'M', '2006-05-14 15:50:00', 'Zambo', NULL),
('chien', 'M','2006-05-14 15:48:00' ,'Samba' , NULL),
('chien', 'M', '2008-03-10 13:40:00','Moka' , NULL),
('chien', 'M', '2006-05-14 15:40:00','Pilou' , NULL),
('chat', 'M','2009-05-14 06:30:00' , 'Fiero', NULL),
('chat', 'M','2007-03-12 12:05:00' ,'Zonko', NULL),
('chat', 'M','2008-02-20 15:45:00' , 'Filou', NULL),
('chat', 'M','2007-03-12 12:07:00' , 'Farceur', NULL),
('chat', 'M','2006-05-19 16:17:00' ,'Caribou' , NULL),
('chat', 'M','2008-04-20 03:22:00' , 'Capou', NULL),
('chat', 'M','2006-05-19 16:56:00' , 'Raccou', 'Pas de queue depuis la naissance');

Vous n'avez alors qu'à taper :

SOURCE remplissageAnimal.sql;

LOAD DATA INFILE

À nouveau, copiez-collez le texte ci-dessous dans votre éditeur de texte, et enregistrez le fichier. Cette fois, sous le nom animal.csv.

"chat";"M";"2009-05-14 06:42:00";"Boucan";NULL
"chat";"F";"2006-05-19 16:06:00";"Callune";NULL
"chat";"F";"2009-05-14 06:45:00";"Boule";NULL
"chat";"F";"2008-04-20 03:26:00";"Zara";NULL
"chat";"F";"2007-03-12 12:00:00";"Milla";NULL
"chat";"F";"2006-05-19 15:59:00";"Feta";NULL
"chat";"F";"2008-04-20 03:20:00";"Bilba";"Sourde de l'oreille droite à 80%"
"chat";"F";"2007-03-12 11:54:00";"Cracotte";NULL
"chat";"F";"2006-05-19 16:16:00";"Cawette";NULL
"tortue";"F";"2007-04-01 18:17:00";"Nikki";NULL
"tortue";"F";"2009-03-24 08:23:00";"Tortilla";NULL
"tortue";"F";"2009-03-26 01:24:00";"Scroupy";NULL
"tortue";"F";"2006-03-15 14:56:00";"Lulla";NULL
"tortue";"F";"2008-03-15 12:02:00";"Dana";NULL
"tortue";"F";"2009-05-25 19:57:00";"Cheli";NULL
"tortue";"F";"2007-04-01 03:54:00";"Chicaca";NULL
"tortue";"F";"2006-03-15 14:26:00";"Redbul";"Insomniaque"
"tortue";"M";"2007-04-02 01:45:00";"Spoutnik";NULL
"tortue";"M";"2008-03-16 08:20:00";"Bubulle";NULL
"tortue";"M";"2008-03-15 18:45:00";"Relou";"Surpoids"
"tortue";"M";"2009-05-25 18:54:00";"Bulbizard";NULL
"perroquet";"M";"2007-03-04 19:36:00";"Safran";NULL
"perroquet";"M";"2008-02-20 02:50:00";"Gingko";NULL
"perroquet";"M";"2009-03-26 08:28:00";"Bavard";NULL
"perroquet";"F";"2009-03-26 07:55:00";"Parlotte";NULL

Exécutez ensuite la commande suivante :

LOAD DATA LOCAL INFILE 'animal.csv'
INTO TABLE Animal
FIELDS TERMINATED BY ';' ENCLOSED BY '"'
LINES TERMINATED BY '\n' -- ou '\r\n' selon l'ordinateur et le programme utilisés pour créer le fichier
(espece, sexe, date_naissance, nom, commentaires);

Et hop ! Vous avez plus d'une cinquantaine d'animaux dans votre table.

Si vous voulez vérifier, je rappelle que vous pouvez utiliser la commande suivante, qui vous affichera toutes les données contenues dans la table Animal.

SELECT * FROM Animal;

Nous pouvons maintenant passer au chapitre suivant !

En résumé

• Pour insérer des lignes dans une table, on utilise la commande

  INSERT INTO nom_table [(colonne1, colonne2, ...)] VALUES (valeur1, valeur2, ...);

• Si l'on ne précise pas à quelles colonnes on donne une valeur, il faut donner une valeur à toutes les colonnes, et dans le bon ordre.

• Il est possible d'insérer plusieurs lignes en une fois, en séparant les listes de valeurs par une virgule.

• Si l'on a un fichier texte contenant des requêtes SQL, on peut l'exécuter en utilisant SOURCE nom_fichier; ou \. nom_fichier;.

• La commande LOAD DATA [LOCAL] INFILE permet de charger des données dans une table à partir d'un fichier formaté (.csv par exemple).

Syntaxe de SELECT

La requête qui permet de sélectionner et afficher des données s'appelle SELECT. Nous l'avons déjà un peu utilisée dans le chapitre d'installation, ainsi que pour afficher tout le contenu de la table Animal.

SELECT permet donc d'afficher des données directement. Des chaînes de caractères, des résultats de calculs, etc.

Exemple

SELECT 'Hello World !';
SELECT 3+2;

SELECT

permet également de sélectionner des données à partir d'une table. Pour cela, il faut ajouter une clause à la commande SELECT : la clause FROM, qui définit de quelle structure (dans notre cas, une table) viennent les données.

SELECT colonne1, colonne2, ... 
FROM nom_table;

Par exemple, si l'on veut sélectionner l'espèce, le nom et le sexe des animaux présents dans la table Animal, on utilisera :

SELECT espece, nom, sexe 
FROM Animal;

Sélectionner toutes les colonnes

Si vous désirez sélectionner toutes les colonnes, vous pouvez utiliser le caractère * dans votre requête :

SELECT * 
FROM Animal;

Il est cependant déconseillé d'utiliser SELECT * trop souvent. Donner explicitement le nom des colonnes dont vous avez besoin présente deux avantages :

• d'une part, vous êtes certains de ce que vous récupérez ;

• d'autre part, vous récupérez uniquement ce dont vous avez vraiment besoin, ce qui permet d'économiser des ressources.

Le désavantage est bien sûr que vous avez plus à écrire, mais le jeu en vaut la chandelle.

Comme vous avez pu le constater, les requêtes SELECT faites jusqu'à présent sélectionnent toutes les lignes de la table. Or, bien souvent, on ne veut qu'une partie des données. Dans la suite de ce chapitre, nous allons voir ce que nous pouvons ajouter à cette requête SELECT pour faire des sélections à l'aide de critères.

La clause WHERE

La clause WHERE ("où" en anglais) permet de restreindre les résultats selon des critères de recherche. On peut par exemple vouloir ne sélectionner que les chiens :

SELECT * 
FROM Animal 
WHERE espece='chien';

Les opérateurs de comparaison

Les opérateurs de comparaison sont les symboles que l'ont utilise pour définir les critères de recherche (le = dans notre exemple précédent). Huit opérateurs simples peuvent être utilisés.

Exemples :

SELECT * 
FROM Animal 
WHERE date_naissance < '2008-01-01'; -- Animaux nés avant 2008

SELECT * 
FROM Animal 
WHERE espece <> 'chat'; -- Tous les animaux sauf les chats

Combinaisons de critères

Tout ça c'est bien beau, mais comment faire si on veut les chats et les chiens par exemple ? Faut-il faire deux requêtes ?
Non bien sûr, il suffit de combiner les critères. Pour cela, il faut des opérateurs logiques, qui sont au nombre de quatre :

Voici quelques exemples, sûrement plus efficaces qu'un long discours.

AND

Je veux sélectionner toutes les chattes. Je veux donc sélectionner les animaux qui sont à la fois des chats ET des femelles. J'utilise l'opérateur AND :

SELECT * 
FROM Animal 
WHERE espece='chat' 
    AND sexe='F';
-- OU
SELECT * 
FROM Animal 
WHERE espece='chat' 
    && sexe='F';

OR

Sélection des tortues et des perroquets. Je désire donc obtenir les animaux qui sont des tortues OU des perroquets :

SELECT * 
FROM Animal 
WHERE espece='tortue' 
    OR espece='perroquet';
-- OU
SELECT * 
FROM Animal 
WHERE espece='tortue' 
    || espece='perroquet';

NOT

Sélection de tous les animaux femelles sauf les chiennes.

SELECT * 
FROM Animal 
WHERE sexe='F' 
    AND NOT espece='chien';
-- OU
SELECT * 
FROM Animal 
WHERE sexe='F' 
    AND ! espece='chien';

XOR

Sélection des animaux qui sont soit des mâles, soit des perroquets (mais pas les deux) :

SELECT * 
FROM Animal 
WHERE sexe='M' 
    XOR espece='perroquet';

Et voilà pour les opérateurs logiques. Rien de bien compliqué, et pourtant, c'est souvent source d'erreur. Pourquoi ? Tout simplement parce que tant que vous n'utilisez qu'un seul opérateur logique, tout va très bien. Mais on a souvent besoin de combiner plus de deux critères, et c'est là que ça se corse.

Sélection complexe

Lorsque vous utilisez plusieurs critères, et que vous devez donc combiner plusieurs opérateurs logiques, il est extrêmement important de bien structurer la requête. En particulier, il faut placer des parenthèses au bon endroit. En effet, cela n'a pas de sens de mettre plusieurs opérateurs logiques différents sur un même niveau.
Petit exemple simple :
Critères : rougeANDvertORbleu

Qu'accepte-t-on ?

• Ce qui est rouge et vert, et ce qui est bleu ?

• Ou ce qui est rouge et, soit vert soit bleu ?

Dans le premier cas, [rouge, vert] et [bleu] seraient acceptés. Dans le deuxième, c'est [rouge, vert] et [rouge, bleu] qui seront acceptés, et non [bleu].

En fait, le premier cas correspond à (rougeANDvert) ORbleu, et le deuxième cas à rougeAND (vertORbleu).

Avec des parenthèses, pas moyen de se tromper sur ce qu'on désire sélectionner !

Exercice/Exemple

Alors, imaginons une requête bien tordue…

Je voudrais les animaux qui sont, soit nés après 2009, soit des chats mâles ou femelles, mais dans le cas des femelles, elles doivent être nées avant juin 2007.

Je vous conseille d'essayer d'écrire cette requête tout seuls. Si vous n'y arrivez pas, voici une petite aide : l'astuce, c'est de penser en niveaux. Je vais donc découper ma requête.

Je cherche :

• les animaux nés après 2009 ;

• les chats mâles et femelles (uniquement nées avant juin 2007 pour les femelles).

C'est mon premier niveau. L'opérateur logique sera OR puisqu'il faut que les animaux répondent à un seul des deux critères pour être sélectionnés.

On continue à découper. Le premier critère ne peut plus être subdivisé, contrairement au deuxième. Je cherche :

• les animaux nés après 2009 ;

• les chats :

  • mâles ;

  • et femelles nées avant juin 2007.

Et voilà, vous avez bien défini les différents niveaux, il n'y a plus qu'à écrire la requête avec les bons opérateurs logiques !

SELECT * 
FROM Animal 
WHERE date_naissance > '2009-12-31'
    OR
    ( espece='chat'
         AND
        ( sexe='M'
            OR
            ( sexe='F' AND date_naissance < '2007-06-01' )
        )
    );

Le cas de NULL

Vous vous souvenez sans doute de la liste des opérateurs de comparaison que je vous ai présentée (sinon, retournez au début de la partie sur la clause WHERE). Vous avez probablement été un peu étonnés de voir dans cette liste l'opérateur <=> : égal (valable aussi pour NULL). D'autant plus que j'ai fait un peu semblant de rien et ne vous ai pas donné d'explication sur cette mystérieuse précision "aussi valable pour NULL" :-° . Mais je vais me rattraper maintenant !
En fait, c'est très simple, le marqueur NULL (qui représente donc "pas de valeur") est un peu particulier. En effet, vous ne pouvez pas tester directement colonne = NULL. Essayons donc :

SELECT * 
FROM Animal 
WHERE nom = NULL; -- sélection des animaux sans nom

SELECT * 
FROM Animal  
WHERE commentaires <> NULL; -- sélection des animaux pour lesquels un commentaire existe

Comme vous pouvez vous en douter après ma petite introduction, ces deux requêtes ne renvoient pas les résultats que l'on pourrait espérer. En fait, elles ne renvoient aucun résultat. C'est donc ici qu'intervient notre opérateur de comparaison un peu spécial <=> qui permet de reconnaître NULL. Une autre possibilité est d'utiliser les mots-clés IS NULL, et si l'on veut exclure les NULL : IS NOT NULL. Nous pouvons donc réécrire nos requêtes, correctement cette fois-ci :

SELECT * 
FROM Animal 
WHERE nom <=> NULL; -- sélection des animaux sans nom
-- OU
SELECT * 
FROM Animal 
WHERE nom IS NULL;

SELECT * 
FROM Animal 
WHERE commentaires IS NOT NULL; -- sélection des animaux pour lesquels un commentaire existe

Cette fois-ci, ça fonctionne parfaitement !

Tri des données

Lorsque vous faites un SELECT, les données sont récupérées dans un ordre défini par MySQL, mais qui n'a aucun sens pour vous. Vous avez sans doute l'impression que MySQL renvoie tout simplement les lignes dans l'ordre dans lequel elles ont été insérées, mais ce n'est pas exactement le cas. En effet, si vous supprimez des lignes, puis en ajoutez de nouvelles, les nouvelles lignes viendront remplacer les anciennes dans l'ordre de MySQL. Or, bien souvent, vous voudrez trier à votre manière. Par date de naissance par exemple, ou bien par espèce, ou par sexe, etc.

Pour trier vos données, c'est très simple, il suffit d'ajouter ORDER BY tri à votre requête (après les critères de sélection de WHERE s'il y en a) et de remplacer "tri" par la colonne sur laquelle vous voulez trier vos données bien sûr.

Par exemple, pour trier par date de naissance :

SELECT * 
FROM Animal 
WHERE espece='chien' 
ORDER BY date_naissance;

Et hop ! Vos données sont triées, les plus vieux chiens sont récupérés en premier, les jeunes à la fin.

Tri ascendant ou descendant

Tout ça c'est bien beau, j'ai mes chiens triés du plus vieux au plus jeune. Et si je veux le contraire ?
Pour déterminer le sens du tri effectué, SQL possède deux mots-clés : ASC pour ascendant, et DESC pour descendant. Par défaut, si vous ne précisez rien, c'est un tri ascendant qui est effectué : du plus petit nombre au plus grand, de la date la plus ancienne à la plus récente, et pour les chaînes de caractères et les textes, c'est l'ordre alphabétique normal qui est utilisé.
Si par contre vous utilisez le mot DESC, l'ordre est inversé : plus grand nombre d'abord, date la plus récente d'abord, et ordre anti-alphabétique pour les caractères.

SELECT * 
FROM Animal 
WHERE espece='chien' 
    AND nom IS NOT NULL 
ORDER BY nom DESC;

Trier sur plusieurs colonnes

Il est également possible de trier sur plusieurs colonnes. Par exemple, si vous voulez que les résultats soient triés par espèce et, dans chaque espèce, triés par date de naissance, il suffit de donner les deux colonnes correspondantes à ORDER BY :

SELECT * 
FROM Animal 
ORDER BY espece, date_naissance;

Vous pouvez trier sur autant de colonnes que vous voulez.

Éliminer les doublons

Il peut arriver que MySQL vous donne plusieurs fois le même résultat. Non pas parce que MySQL fait des bêtises, mais tout simplement parce que certaines informations sont présentes plusieurs fois dans la table.

Petit exemple très parlant : vous voulez savoir quelles sont les espèces que vous possédez dans votre élevage. Facile, une petite requête :

SELECT espece 
FROM Animal;

En effet, vous allez bien récupérer toutes les espèces que vous possédez, mais si vous avez 500 chiens, vous allez récupérer 500 lignes 'chien'.
Un peu embêtant lorsque la table devient bien remplie.

Heureusement, il y a une solution : le mot-clé DISTINCT.
Ce mot-clé se place juste après SELECT et permet d'éliminer les doublons.

SELECT DISTINCT espece 
FROM Animal;

Ceci devrait gentiment vous ramener quatre lignes avec les quatre espèces qui se trouvent dans la table. C'est quand même plus clair non ?

Attention cependant, pour éliminer un doublon, il faut que toute la ligne sélectionnée soit égale à une autre ligne du jeu de résultats. Ça peut paraître logique, mais cela en perd plus d'un. Ne seront donc prises en compte que les colonnes que vous avez précisées dans votre SELECT. Uniquement espece donc, dans notre exemple.

Restreindre les résultats

En plus de restreindre une recherche en lui donnant des critères grâce à la clause WHERE, il est possible de restreindre le nombre de lignes récupérées.
Cela se fait grâce à la clause LIMIT.

Syntaxe

LIMIT s'utilise avec deux paramètres.

• Le nombre de lignes que l'on veut récupérer.

• Le décalage, introduit par le mot-clé OFFSET et qui indique à partir de quelle ligne on récupère les résultats. Ce paramètre est facultatif. S'il n'est pas précisé, il est mis à 0.

LIMIT nombre_de_lignes [OFFSET decalage];

Exemple :

SELECT * 
FROM Animal 
ORDER BY id 
LIMIT 6 OFFSET 0;

SELECT * 
FROM Animal 
ORDER BY id 
LIMIT 6 OFFSET 3;

Avec la première requête, vous devriez obtenir six lignes, les six plus petits id puisque nous n'avons demandé aucun décalage (OFFSET 0).

Par contre, dans la deuxième, vous récupérez toujours six lignes, mais vous devriez commencer au quatrième plus petit id, puisqu'on a demandé un décalage de trois lignes.

Exemple avec un seul paramètre :

SELECT * 
FROM Animal 
ORDER BY id 
LIMIT 10;

Cette requête est donc équivalente à :

SELECT * 
FROM Animal 
ORDER BY id 
LIMIT 10 OFFSET 0;

Syntaxe alternative

MySQL accepte une autre syntaxe pour la clause LIMIT. Ce n'est cependant pas la norme SQL donc idéalement vous devriez toujours utiliser la syntaxe officielle. Vous vous apercevrez toutefois que cette syntaxe est énormément usitée, je ne pouvais donc pas ne pas la mentionner

SELECT * 
FROM Animal 
ORDER BY id 
LIMIT [decalage, ]nombre_de_lignes;

Tout comme pour la syntaxe officielle, le décalage n'est pas obligatoire, et vaudra 0 par défaut. Si vous le précisez, n'oubliez pas la virgule entre le décalage et le nombre de lignes désirées.

En résumé

• La commande SELECT permet d'afficher des données.

• La clause WHERE permet de préciser des critères de sélection.

• Il est possible de trier les données grâce à ORDER BY, selon un ordre ascendant (ASC) ou descendant (DESC).

• Pour éliminer les doublons, on utilise le mot-clé DISTINCT, juste après SELECT.

• LIMIT nb_lignes OFFSET decalage permet de sélectionner uniquement nb_lignes de résultats, avec un certain décalage.

Recherche approximative

Pour l'instant, nous avons vu huit opérateurs de comparaison :

À l'exception de <=> qui est un peu particulier, ce sont les opérateurs classiques, que vous retrouverez dans tous les langages informatiques. Cependant, il arrive que ces opérateurs ne soient pas suffisants. En particulier pour des recherches sur des chaînes de caractères. En effet, comment faire lorsqu'on ne sait pas si le mot que l'on recherche est au singulier ou au pluriel par exemple ? Ou si l'on cherche toutes les lignes dont le champ "commentaires" contient un mot particulier ?

Pour ce genre de recherches, l'opérateur LIKE est très utile, car il permet de faire des recherches en utilisant des "jokers", c'est-à-dire des caractères qui représentent n'importe quel caractère.

Deux jokers existent pour LIKE :

• '%' : qui représente n'importe quelle chaîne de caractères, quelle que soit sa longueur (y compris une chaîne de longueur 0) ;

• '_' : qui représente un seul caractère (ou aucun).

Quelques exemples :

• 'b%' cherchera toutes les chaînes de caractères commençant par 'b' ('brocoli', 'bouli', 'b')

• 'B_' cherchera toutes les chaînes de caractères contenant une ou deux lettres dont la première est 'b' ('ba', 'bf', 'b')

• '%ch%ne' cherchera toutes les chaînes de caractères contenant 'ch' et finissant par 'ne' ('chne', 'chine', 'échine', 'le pays le plus peuplé du monde est la Chine')

• '_ch_ne' cherchera toutes les chaînes de caractères commençant par 'ch', éventuellement précédées d'une seule lettre, suivies de zéro ou d'un caractère au choix et enfin se terminant par 'ne' ('chine', 'chne', 'echine')

Rechercher '%' ou '_'

Comment faire si vous cherchez une chaîne de caractères contenant '%' ou '_' ? Évidemment, si vous écrivez LIKE '%' ou LIKE '_', MySQL vous donnera absolument toutes les chaînes de caractères dans le premier cas, et toutes les chaînes de 0 ou 1 caractère dans le deuxième.
Il faut donc signaler à MySQL que vous ne désirez pas utiliser % ou _ en tant que joker, mais bien en tant que caractère de recherche. Pour ça, il suffit de mettre le caractère d'échappement \, dont je vous ai déjà parlé, devant le '%' ou le '_'.

Exemple :

SELECT * 
FROM Animal 
WHERE commentaires LIKE '%\%%';

Résultat :

Exclure une chaîne de caractères

C'est logique, mais je précise quand même (et puis ça fait un petit rappel) : l'opérateur logique NOT est utilisable avec LIKE. Si l'on veut rechercher les animaux dont le nom ne contient pas la lettre a, on peut donc écrire :

SELECT * 
FROM Animal 
WHERE nom NOT LIKE '%a%';

Sensibilité à la casse

Vous l'aurez peut-être remarqué en faisant des essais, LIKE 'chaîne de caractères' n'est pas sensible à la casse (donc aux différences majuscules-minuscules). Pour rappel, ceci est dû à l'interclassement. Nous avons gardé l'interclassement par défaut du jeu de caractère UTF-8, qui n'est pas sensible à la casse.
Si vous désirez faire une recherche sensible à la casse, vous pouvez définir votre chaîne de recherche comme une chaîne de type binaire, et non plus comme une simple chaîne de caractères :

SELECT * 
FROM Animal 
WHERE nom LIKE '%Lu%'; -- insensible à la casse

SELECT * 
FROM Animal 
WHERE nom LIKE BINARY '%Lu%'; -- sensible à la casse

Recherche dans les numériques

Vous pouvez bien entendu utiliser des chiffres dans une chaîne de caractères. Après tout, ce sont des caractères comme les autres. Par contre, utiliser LIKE sur un type numérique (INT par exemple), c'est déjà plus étonnant.
Et pourtant, MySQL le permet. Attention cependant, il s'agit bien d'une particularité MySQL, qui prend souvent un malin plaisir à étendre la norme SQL pure.

LIKE '1%' sur une colonne de type numérique trouvera donc des nombres comme 10, 1000, 153

SELECT * 
FROM Animal 
WHERE id LIKE '1%';

Recherche dans un intervalle

Il est possible de faire une recherche sur un intervalle à l'aide uniquement des opérateurs de comparaison >= et <=. Par exemple, on peut rechercher les animaux qui sont nés entre le 5 janvier 2008 et le 23 mars 2009 de la manière suivante :

SELECT * 
FROM Animal 
WHERE date_naissance <= '2009-03-23' 
    AND date_naissance >= '2008-01-05';

Ça fonctionne très bien. Cependant, SQL dispose d'un opérateur spécifique pour les intervalles, qui pourrait vous éviter les erreurs d'inattention classiques (< au lieu de > par exemple) en plus de rendre votre requête plus lisible et plus performante : BETWEEN minimum AND maximum (between signifie "entre" en anglais). La requête précédente peut donc s'écrire :

SELECT * 
FROM Animal 
WHERE date_naissance BETWEEN '2008-01-05' AND '2009-03-23';

BETWEEN

peut s'utiliser avec des dates, mais aussi avec des nombres (BETWEEN 0 AND 100) ou avec des chaînes de caractères (BETWEEN 'a' AND 'd') auquel cas c'est l'ordre alphabétique qui sera utilisé (toujours insensible à la casse sauf si l'on utilise des chaînes binaires : BETWEEN BINARY 'a' AND BINARY 'd').
Bien évidemment, on peut aussi exclure un intervalle avec NOT BETWEEN.

Set de critères

Le dernier opérateur à utiliser dans la clause WHERE que nous verrons dans ce chapitre est IN. Ce petit mot de deux lettres, bien souvent méconnu des débutants, va probablement vous permettre d'économiser du temps et des lignes.

Imaginons que vous vouliez récupérer les informations des animaux répondant aux doux noms de Moka, Bilba, Tortilla, Balou, Dana, Redbul et Gingko. Jusqu'à maintenant, vous auriez sans doute fait quelque chose comme ça :

SELECT * 
FROM Animal 
WHERE nom = 'Moka' 
    OR nom = 'Bilba' 
    OR nom = 'Tortilla' 
    OR nom = 'Balou' 
    OR nom = 'Dana' 
    OR nom = 'Redbul' 
    OR nom = 'Gingko';

Un peu fastidieux non :( ? Eh bien réjouissez-vous, car IN est dans la place ! Cet opérateur vous permet de faire des recherches parmi une liste de valeurs. Parfait pour nous donc, qui voulons rechercher les animaux correspondant à une liste de noms. Voici la manière d'utiliser IN :

SELECT * 
FROM Animal 
WHERE nom IN ('Moka', 'Bilba', 'Tortilla', 'Balou', 'Dana', 'Redbul', 'Gingko');

C'est quand même plus agréable à écrire ! :soleil:

En résumé

• L'opérateur LIKE permet de faire des recherches approximatives, grâce aux deux caractères "joker" : '%' (qui représente 0 ou plusieurs caractères) et '_' (qui représente 0 ou 1 caractère).

• L'opérateur BETWEEN permet de faire une recherche sur un intervalle. WHERE colonne BETWEEN a AND b étant équivalent à WHERE colonne >= a AND colonne <= b.

• Enfin, l'opérateur IN permet de faire une recherche sur une liste de valeurs.

Sauvegarde d'une base de données

Il est bien utile de pouvoir sauvegarder facilement sa base de données, et très important de la sauvegarder régulièrement. Une mauvaise manipulation (ou un méchant pirate :pirate: s'il s'agit d'un site web) et toutes les données peuvent disparaître. MySQL dispose donc d'un outil spécialement dédié à la sauvegarde des données sous forme de fichiers texte : mysqldump.

Cette fonction de sauvegarde s'utilise à partir de la console. Vous devez donc être déconnectés de MySQL pour la lancer. Si c'est votre cas, tapez simplement exit

Vous êtes maintenant dans la console Windows (ou Mac, ou Linux).
La manière classique de faire une sauvegarde d'une base de données est de taper la commande suivante :

mysqldump -u user -p --opt nom_de_la_base > sauvegarde.sql

Décortiquons cette commande.

• mysqldump : il s'agit du client permettant de sauvegarder les bases. Rien de spécial à signaler.

• --opt : c'est une option de mysqldump qui lance la commande avec une série de paramètres qui font que la commande s'effectue très rapidement.

• nom_de_la_base : vous l'avez sans doute deviné, c'est ici qu'il faut indiquer le nom de la base qu'on veut sauvegarder.

• > sauvegarde.sql : le signe > indique que l'on va donner la destination de ce qui va être généré par la commande : sauvegarde.sql. Il s'agit du nom du fichier qui contiendra la sauvegarde de notre base. Vous pouvez bien sûr l'appeler comme bon vous semble.

Lancez la commande suivante pour sauvegarder elevage dans votre dossier courant (c'est-à-dire le dossier dans lequel vous étiez au moment de la connexion) :

mysqldump -u sdz -p --opt elevage > elevage_sauvegarde.sql

Puis, allez voir dans le dossier. Vous devriez y trouver un fichier elevage_sauvegarde.sql. Ouvrez-le avec un éditeur de texte.
Vous pouvez voir nombre de commandes SQL qui servent à la création des tables de la base de données, ainsi qu'à l'insertion des données. S'ajoutent à cela quelques commandes qui vont sélectionner le bon encodage, etc.

La base de données est donc sauvegardée. Notez que la commande pour créer la base elle-même n'est pas sauvée. Donc, si vous effacez votre base par mégarde, il vous faut d'abord recréer la base de données (avec CREATE DATABASE nom_base), puis exécuter la commande suivante (dans la console) :

mysql nom_base < chemin_fichier_de_sauvegarde.sql

Concrètement, dans notre cas :

mysql elevage < elevage_sauvegarde.sql

Ou, directement à partir de MySQL :

USE nom_base;
source fichier_de_sauvegarde.sql;

Donc :

USE elevage;
source elevage_sauvegarde.sql;

Suppression

La commande utilisée pour supprimer des données est DELETE. Cette opération est irréversible, soyez très prudents !
On utilise la clause WHERE de la même manière qu'avec la commande SELECT pour préciser quelles lignes doivent être supprimées.

DELETE FROM nom_table 
WHERE critères;

Par exemple : Zoulou est mort, paix à son âme :ange: ... Nous allons donc le retirer de la base de données.

DELETE FROM Animal 
WHERE nom = 'Zoulou';

Et voilà, plus de Zoulou :'( !

Si vous désirez supprimer toutes les lignes d'une table, il suffit de ne pas préciser de clause WHERE.

DELETE FROM Animal;

Modification

La modification des données se fait grâce à la commande UPDATE, dont la syntaxe est la suivante :

UPDATE nom_table 
SET col1 = val1 [, col2 = val2, ...] 
[WHERE ...];

Par exemple, vous étiez persuadés que ce petit Pataud était un mâle mais, quelques semaines plus tard, vous vous rendez compte de votre erreur. Il vous faut donc modifier son sexe, mais aussi son nom. Voici la requête qui va vous le permettre :

UPDATE Animal 
SET sexe='F', nom='Pataude' 
WHERE id=21;

Vérifiez d'abord chez vous que l'animal portant le numéro d'identification 21 est bien Pataud. J'utilise ici la clé primaire (donc id) pour identifier la ligne à modifier, car c'est la seule manière d'être sûr que je ne modifierai que la ligne que je désire. En effet, il est possible que plusieurs animaux aient pour nom "Pataud". Ce n'est a priori pas notre cas, mais prenons tout de suite de bonnes habitudes.

La requête suivante changerait donc le commentaire de tous les animaux stockés dans la table Animal (ne l'exécutez pas).

UPDATE Animal 
SET commentaires='modification de toutes les lignes';

En résumé

• Le client mysqldump est un programme qui permet de sauvegarder facilement ses bases de données.

• La commande DELETE permet de supprimer des données.

• La commande UPDATE permet de modifier des données.

Qu'est-ce qu'un index ?

Revoyons la définition d'un index.

Citation : Définition

Structure de données qui reprend la liste ordonnée des valeurs auxquelles il se rapporte.

Lorsque vous créez un index sur une table, MySQL stocke cet index sous forme d'une structure particulière, contenant les valeurs des colonnes impliquées dans l'index. Cette structure stocke les valeurs triées et permet d'accéder à chacune de manière efficace et rapide. Petit schéma explicatif dans le cas d'un index sur l'id de la table Animal (je ne prends que les neuf premières lignes pour ne pas surcharger).

Les données d'Animal ne sont pas stockées suivant un ordre intelligible pour nous. Par contre, l'index sur l'id est trié simplement par ordre croissant. Cela permet de grandement accélérer toute recherche faite sur cet id.
Imaginons en effet, que nous voulions récupérer toutes les lignes dont l'id est inférieur ou égal à 5. Sans index, MyQSL doit parcourir toutes les lignes une à une. Par contre, grâce à l'index, dès qu'il tombe sur la ligne dont l'id est 6, il sait qu'il peut s'arrêter, puisque toutes les lignes suivantes auront un id supérieur ou égal à 6. Dans cet exemple, on ne gagne que quelques lignes, mais imaginez une table contenant des milliers de lignes. Le gain de temps peut être assez considérable.
Par ailleurs, avec les id triés par ordre croissant, pour rechercher un id particulier, MySQL n'est pas obligé de simplement parcourir les données ligne par ligne. Il peut utiliser des algorithmes de recherche puissants (comme la recherche dichotomique), toujours afin d’accélérer la recherche.

Mais pourquoi ne pas simplement trier la table complète sur la base de la colonne id ? Pourquoi créer et stocker une structure spécialement pour l'index ? Tout simplement parce qu'il peut y avoir plusieurs index sur une même table, et que l'ordre des lignes pour chacun de ces index n'est pas nécessairement le même. Par exemple, nous pouvons créer un second index pour notre table Animal, sur la colonne date_naissance.

Comme vous pouvez le voir, l'ordre n'est pas du tout le même.

Intérêt des index

Vous devriez avoir compris maintenant que tout l'intérêt des index est d'accélérer les requêtes qui utilisent des colonnes indexées comme critères de recherche. Par conséquent, si vous savez que dans votre application, vous ferez énormément de recherches sur la colonne X, ajoutez donc un index sur cette colonne, vous ne vous en porterez que mieux.
Les index permettent aussi d'assurer l'intégrité des données de la base. Pour cela, il existe en fait plusieurs types d'index différents, et deux types de "clés". Lorsque je parle de garantir l'intégrité de vos données, cela signifie en gros garantir la qualité de vos données, vous assurer que vos données ont du sens. Par exemple, soyez sûrs que vous ne faites pas référence à un client dans la table Commande, alors qu'en réalité, ce client n'existe absolument pas dans la table Client.

Désavantages

Si tout ce que fait un index, c'est accélérer les requêtes utilisant des critères de recherche correspondants, autant en mettre partout, et en profiter à chaque requête ! Sauf qu'évidemment, ce n'est pas si simple, les index ont deux inconvénients.

• Ils prennent de la place en mémoire

• Ils ralentissent les requêtes d'insertion, modification et suppression, puisqu'à chaque fois, il faut remettre l'index à jour en plus de la table.

Par conséquent, n'ajoutez pas d'index lorsque ce n'est pas vraiment utile.

Index sur plusieurs colonnes

Reprenons l'exemple d'une table appelée Client, qui reprend les informations des clients d'une société. Elle se présente comme suit :

Vous avez bien sûr un index sur la colonne id, mais vous constatez que vous faites énormément de recherches par nom, prénom et initiale du second prénom. Vous pourriez donc faire trois index, un pour chacune de ces colonnes. Mais, si vous faites souvent des recherches sur les trois colonnes à la fois, il vaut encore mieux faire un seul index, sur les trois colonnes (nom, prenom, init_2e_prenom). L'index contiendra donc les valeurs des trois colonnes et sera trié par nom, ensuite par prénom, et enfin par initiale (l'ordre des colonnes a donc de l'importance !).

Du coup, lorsque vous cherchez "Dupont Valérie C.", grâce à l'index MySQL trouvera rapidement tous les "Dupont", parmi lesquels il trouvera toutes les "Valérie" (toujours en se servant du même index), parmi lesquelles il trouvera celle (ou celles) dont le second prénom commence par "C".

Tirer parti des "index par la gauche"

Tout ça c'est bien beau si on fait souvent des recherches à la fois sur le nom, le prénom et l'initiale. Mais comment fait-on si l'on fait aussi souvent des recherches uniquement sur le nom, ou uniquement sur le prénom, ou sur le nom et le prénom en même temps mais sans l'initiale ? Faut-il créer un nouvel index pour chaque type de recherche ?
Hé bien non ! MySQl est beaucoup trop fort : il est capable de tirer parti de votre index sur (nom, prenom, init_2e_prenom) pour certaines autres recherches. En effet, si l'on représente les index sous forme de tables, voici ce que cela donnerait pour les quatre index (prenom), (nom), (nom, prenom) et (nom, prenom, init_2e_prenom).

Oui ! Bien vu ! L'index (nom, prenom) correspond exactement aux deux premières colonnes de l'index (nom, prenom, init_2e_prenom) ; pas seulement au niveau des valeurs mais surtout au niveau de l'ordre. Et l'index (nom) correspond à la première colonne de l'index (nom, prenom, init_2e_prenom).
Or, je vous ai dit que lorsque vous faites une recherche sur le nom, le prénom et l'initiale avec l'index (nom, prenom, init_2e_prenom), MySQL regarde d'abord le nom, puis le prénom, et pour finir l'initiale. Donc, si vous ne faites une recherche que sur le nom et le prénom, MySQL va intelligemment utiliser l'index (nom, prenom, init_2e_prenom) : il va simplement laisser tomber l'étape de l'initiale du second prénom. Idem si vous faites une recherche sur le nom : MySQL se basera uniquement sur la première colonne de l'index existant.
Par conséquent, pas besoin de définir un index (nom, prenom) ou un index (nom). Ils sont en quelque sorte déjà présents.

Mais qu'en est-il des index (prenom), ou (prenom, init_2e_prenom) ? Vous pouvez voir que la table contenant l'index (prenom) ne correspond à aucune colonne d'un index existant (au niveau de l'ordre des lignes). Par conséquent, si vous voulez un index sur (prenom), il vous faut le créer. Même chose pour (prenom, init_2e_prenom) ou (nom, init_2e_prenom).

On parle d'index "par la gauche". Donc si l'on prend des sous-parties d'index existant en prenant des colonnes "par la gauche", ces index existent. Mais si l'on commence par la droite ou que l'on "saute" une colonne, ils n'existent pas et doivent éventuellement être créés.

Index sur des colonnes de type alphanumérique

Types CHAR et VARCHAR

Lorsque l'on indexe une colonne de type VARCHAR ou CHAR (comme la colonne nom de la table Client par exemple), on peut décomposer l'index de la manière suivante :

C'est très simple. Ici, nous n'avons que des noms assez courts. La colonne nom peut par exemple être de type VARCHAR(30). Mais imaginez une colonne de type VARCHAR(150), qui contient des titres de livres par exemple. Si l'on met un index dessus, MySQL va indexer jusqu'à 150 caractères. Or, il est fort probable que les 25-30 premiers caractères du titre suffisent à trier ceux-ci. Au pire, un ou deux ne seront pas exactement à la bonne place, mais les requêtes en seraient déjà grandement accélérées.
Il serait donc plutôt pratique de dire à MySQL : "Indexe cette colonne, mais base-toi seulement sur les x premiers caractères". Et c'est possible évidemment (sinon je ne vous en parlerais pas :p ), et c'est même très simple. Lorsque l'on créera l'index sur la colonne titre_livre, il suffira d'indiquer un nombre entre parenthèses : titre_livre(25) par exemple. Ce nombre étant bien sûr le nombre de caractères (dans le sens de la lecture, donc à partir de la gauche) à prendre en compte pour l'index.

Le fait d'utiliser ces index partiels sur des champs alphanumériques permet de gagner de la place (un index sur 150 lettres prend évidemment plus de place qu'un index sur 20 lettres), et si la longueur est intelligemment définie, l'accélération permise par l'index sera la même que si l'on avait pris la colonne entière.

Types BLOB et TEXT (et dérivés)

Si vous mettez un index sur une colonne de type BLOB ou TEXT (ou un de leurs dérivés), MySQL exige que vous précisiez un nombre de caractères à prendre en compte. Et heureusement… Vu la longueur potentielle de ce que l'on stocke dans de telles colonnes.

Les différents types d'index

En plus des index "simples", que je viens de vous décrire, il existe trois types d'index qui ont des propriétés particulières. Les index UNIQUE, les index FULLTEXT, et enfin les index SPATIAL.
Je ne détaillerai pas les propriétés et utilisations des index SPATIAL. Sachez simplement qu'il s'agit d'un type d'index utilisé dans des bases de données recensant des données spatiales (tiens donc ! :p ), donc des points, des lignes, des polygones…

Sur ce, c'est parti !

Index UNIQUE

Avoir un index UNIQUE sur une colonne (ou plusieurs) permet de s'assurer que jamais vous n’insérerez deux fois la même valeur (ou combinaison de valeurs) dans la table.

Par exemple, vous créez un site internet, et vous voulez le doter d'un espace membre. Chaque membre devra se connecter grâce à un pseudo et un mot de passe. Vous avez donc une table Membre, qui contient 4 colonnes : id, pseudo, mot_de_passe et date_inscription.
Deux membres peuvent avoir le même mot de passe, pas de problème. Par contre, que se passerait-il si deux membres avaient le même pseudo ? Lors de la connexion, il serait impossible de savoir quel mot de passe utiliser et sur quel compte connecter le membre.
Il faut donc absolument éviter que deux membres utilisent le même pseudo. Pour cela, on va utiliser un index UNIQUE sur la colonne pseudo de la table Membre.

Autre exemple, dans notre table Animal cette fois. Histoire de ne pas confondre les animaux, vous prenez la décision de ne pas nommer de la même manière deux animaux de la même espèce. Il peut donc n'y avoir qu'une seule tortue nommée Toto. Un chien peut aussi se nommer Toto, mais un seul. La combinaison (espèce, nom) doit n'exister qu'une et une seule fois dans la table. Pour s'en assurer, il suffit de créer un index unique sur les colonnes espece et nom (un seul index, sur les deux colonnes).

Contraintes

Lorsque vous mettez un index UNIQUE sur une table, vous ne mettez pas seulement un index, vous ajoutez surtout une contrainte.
Les contraintes sont une notion importante en SQL. Sans le savoir, ou sans savoir que c'était appelé comme ça, vous en avez déjà utilisé. En effet, lorsque vous empêchez une colonne d'accepter NULL, vous lui mettez une contrainte NOT NULL.
De même, les valeurs par défaut que vous pouvez donner aux colonnes sont des contraintes. Vous contraignez la colonne à prendre une certaine valeur si aucune autre n'est précisée.

Index FULLTEXT

Un index FULLTEXT est utilisé pour faire des recherches de manière puissante et rapide sur un texte. Seules les tables utilisant le moteur de stockage MyISAM peuvent avoir un index FULLTEXT, et cela uniquement sur les colonnes de type CHAR, VARCHAR ou TEXT (ce qui est plutôt logique).

Une différence importante (très importante !!! :diable: ) entre les index FULLTEXT et les index classiques (et UNIQUE) est que l'on ne peut plus utiliser les fameux "index par la gauche" dont je vous ai parlé précédemment. Donc, si vous voulez faire des recherches "fulltext" sur deux colonnes (parfois l'une, parfois l'autre, parfois les deux ensemble), il vous faudra créer trois index FULLTEXT : (colonne1), (colonne2) et (colonne1, colonne2).

Création et suppression des index

Les index sont représentés par le mot-clé INDEX (surprise ! o_O ) ou KEY et peuvent être créés de deux manières :

• soit directement lors de la création de la table ;

• soit en les ajoutant par la suite.

Ajout des index lors de la création de la table

Ici aussi, deux possibilités : vous pouvez préciser dans la description de la colonne qu'il s'agit d'un index, ou lister les index par la suite.

Index dans la description de la colonne

Je rappelle que la description de la colonne se rapporte à l'endroit où vous indiquez le type de données, si la colonne peut contenir NULL, etc. Il est donc possible, à ce même endroit, de préciser si la colonne est un index.

CREATE TABLE nom_table (
    colonne1 INT KEY,                  -- Crée un index simple sur colonne1
    colonne2 VARCHAR(40) UNIQUE,       -- Crée un index unique sur colonne2
);

Quelques petites remarques ici :

• Avec cette syntaxe, seul le mot KEY peut être utilisé pour définir un index simple. Ailleurs, vous pourrez utiliser KEY ou INDEX.

• Pour définir un index UNIQUE de cette manière, on n'utilise que le mot-clé UNIQUE, sans le faire précéder de INDEX ou de KEY (comme ce sera le cas avec d'autres syntaxes).

• Il n'est pas possible de définir des index composites (sur plusieurs colonnes) de cette manière.

• Il n'est pas non plus possible de créer un index sur une partie de la colonne (les x premiers caractères).

Liste d'index

L'autre possibilité est d'ajouter les index à la suite des colonnes, en séparant chaque élément par une virgule :

CREATE TABLE nom_table (
    colonne1 description_colonne1,
    [colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...,]
    [PRIMARY KEY (colonne_clé_primaire)],
    [INDEX [nom_index] (colonne1_index [, colonne2_index, ...]]
)
[ENGINE=moteur];

Exemple : si l'on avait voulu créer la table Animal avec un index sur la date de naissance, et un autre sur les 10 premières lettres du nom, on aurait pu utiliser la commande suivante :

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),
    INDEX ind_date_naissance (date_naissance),  -- index sur la date de naissance
    INDEX ind_nom (nom(10))                     -- index sur le nom (le chiffre entre parenthèses étant le nombre de caractères pris en compte)
)
ENGINE=INNODB;

Vous n'êtes pas obligés de préciser un nom pour votre index. Si vous ne le faites pas, MySQL en créera un automatiquement pour vous.
Je préfère nommer mes index moi-même plutôt que de laisser MySQL créer un nom par défaut, et respecter certaines conventions personnelles. Ainsi, mes index auront toujours le préfixe "ind" suivi du ou des nom(s) des colonnes concernées, le tout séparé par des "_". Il vous appartient de suivre vos propres conventions bien sûr. L'important étant de vous y retrouver.

Et pour ajouter des index UNIQUE ou FULLTEXT, c'est le même principe :

CREATE TABLE nom_table (
    colonne1 INT NOT NULL,   
    colonne2 VARCHAR(40), 
    colonne3 TEXT,
    UNIQUE [INDEX] ind_uni_col2 (colonne2),     -- Crée un index UNIQUE sur la colonne2, INDEX est facultatif
    FULLTEXT [INDEX] ind_full_col3 (colonne3)   -- Crée un index FULLTEXT sur la colonne3, INDEX est facultatif
)
ENGINE=MyISAM;

Exemple : création de la table Animal comme précédemment, en ajoutant un index UNIQUE sur (nom, espece).

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),
    INDEX ind_date_naissance (date_naissance),  
    INDEX ind_nom (nom(10)),                    
    UNIQUE INDEX ind_uni_nom_espece (nom, espece)  -- Index sur le nom et l'espece
)
ENGINE=INNODB;

Ajout des index après création de la table

En dehors du fait que parfois vous ne penserez pas à tout au moment de la création de votre table, il peut parfois être intéressant de créer les index après la table.
En effet, je vous ai dit que l'ajout d'index sur une table ralentissait l'exécution des requêtes d'écriture (insertion, suppression, modification de données). Par conséquent, si vous créez une table, que vous comptez remplir avec un grand nombre de données immédiatement, grâce à la commande LOAD DATA INFILE par exemple, il vaut bien mieux créer la table, la remplir, et ensuite seulement créer les index voulus sur cette table.

Il existe deux commandes permettant de créer des index sur une table existante : ALTER TABLE, que vous connaissez déjà un peu, et CREATE INDEX. Ces deux commandes sont équivalentes, utilisez celle qui vous parle le plus.

Ajout avec ALTER TABLE

ALTER TABLE nom_table
ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]); --Ajout d'un index simple

ALTER TABLE nom_table
ADD UNIQUE [nom_index] (colonne_index [, colonne2_index ...]); --Ajout d'un index UNIQUE

ALTER TABLE nom_table
ADD FULLTEXT [nom_index] (colonne_index [, colonne2_index ...]); --Ajout d'un index FULLTEXT

Contrairement à ce qui se passait pour l'ajout d'une colonne, il est ici obligatoire de préciser INDEX (ou UNIQUE, ou FULLTEXT) après ADD.
Dans le cas d'un index multi-colonnes, il suffit comme d'habitude de toutes les indiquer entre parenthèses, séparées par des virgules.

Reprenons la table Test_tuto, utilisée pour tester ALTER TABLE, et ajoutons-lui un index sur la colonne nom :

ALTER TABLE Test_tuto 
ADD INDEX ind_nom (nom);

Si vous affichez maintenant la description de votre table Test_tuto, vous verrez que dans la colonne "Key", il est indiqué MUL pour la colonne nom. L'index a donc bien été créé.

Ajout avec CREATE INDEX

La syntaxe de CREATE INDEX est très simple :

CREATE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);  -- Crée un index simple

CREATE UNIQUE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);  -- Crée un index UNIQUE


CREATE FULLTEXT INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);  -- Crée un index FULLTEXT

Exemple : l'équivalent de la commande ALTER TABLE que nous avons utilisée pour ajouter un index sur la colonne nom est donc :

CREATE INDEX ind_nom 
ON Test_tuto (nom);

Complément pour la création d'un index UNIQUE - le cas des contraintes

Vous vous rappelez, j'espère, que les index UNIQUE sont ce qu'on appelle des contraintes.

Or, lorsque vous créez un index UNIQUE, vous pouvez explicitement créer une contrainte. C'est fait automatiquement bien sûr si vous ne le faites pas, mais ne soyez donc pas surpris de voir apparaître le mot CONSTRAINT, c'est à ça qu'il se réfère.

Pour pouvoir créer explicitement une contrainte lors de la création d'un index UNIQUE, vous devez créer cet index soit lors de la création de la table, en listant l'index (et la contrainte) à la suite des colonnes, soit après la création de la table, avec la commande ALTER TABLE.

CREATE TABLE nom_table (
    colonne1 INT NOT NULL,   
    colonne2 VARCHAR(40), 
    colonne3 TEXT,
    CONSTRAINT [symbole_contrainte] UNIQUE [INDEX] ind_uni_col2 (colonne2)
);

ALTER TABLE nom_table
ADD CONSTRAINT [symbole_contrainte] UNIQUE ind_uni_col2 (colonne2);

Il n'est pas obligatoire de donner un symbole (un nom en fait) à la contrainte. D'autant plus que dans le cas des index, vous pouvez donner un nom à l'index (ici : ind_uni_col).

Suppression d'un index

Rien de bien compliqué :

ALTER TABLE nom_table 
DROP INDEX nom_index;

Notez qu'il n'existe pas de commande permettant de modifier un index. Le cas échéant, il vous faudra supprimer, puis recréer votre index avec vos modifications.

Recherches avec FULLTEXT

Nous allons maintenant voir comment utiliser la recherche FULLTEXT, qui est un outil très puissant, et qui peut se révéler très utile.

Quelques rappels d'abord :

• un index FULLTEXT ne peut être défini que pour une table utilisant le moteur MyISAM ;

• un index FULLTEXT ne peut être défini que sur une colonne de type CHAR, VARCHAR ou TEXT

• les index "par la gauche" ne sont pas pris en compte par les index FULLTEXT

Ça, c'est fait ! Nous allons maintenant passer à la recherche proprement dite, mais avant, je vais vous demander d'exécuter les instructions SQL suivantes, qui servent à créer la table que nous utiliserons pour illustrer ce chapitre. Nous sortons ici du contexte de l'élevage d'animaux. En effet, toutes les tables que nous créerons pour l'élevage utiliseront le moteur de stockage InnoDB.

Pour illustrer la recherche FULLTEXT, je vous propose de créer la table Livre, contenant les colonnes id (clé primaire), titre et auteur. Les recherches se feront sur les colonnes auteur et titre, séparément ou ensemble. Il faut donc créer trois index FULLTEXT : (auteur), (titre) et (auteur, titre).

CREATE TABLE Livre (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	auteur VARCHAR(50),
	titre VARCHAR(200)
) ENGINE = MyISAM;

INSERT INTO Livre (auteur, titre)
VALUES ('Daniel Pennac', 'Au bonheur des ogres'),
('Daniel Pennac', 'La Fée Carabine'),
('Daniel Pennac', 'Comme un roman'),
('Daniel Pennac', 'La Petite marchande de prose'),
('Jacqueline Harpman', 'Le Bonheur est dans le crime'),
('Jacqueline Harpman', 'La Dormition des amants'),
('Jacqueline Harpman', 'La Plage d''Ostende'),
('Jacqueline Harpman', 'Histoire de Jenny'),
('Terry Pratchett', 'Les Petits Dieux'),
('Terry Pratchett', 'Le Cinquième éléphant'),
('Terry Pratchett', 'La Vérité'),
('Terry Pratchett', 'Le Dernier héros'),
('Terry Goodkind', 'Le Temple des vents'),
('Jules Verne', 'De la Terre à la Lune'),
('Jules Verne', 'Voyage au centre de la Terre'),
('Henri-Pierre Roché', 'Jules et Jim');

CREATE FULLTEXT INDEX ind_full_titre
ON Livre (titre);

CREATE FULLTEXT INDEX ind_full_aut
ON Livre (auteur);

CREATE FULLTEXT INDEX ind_full_titre_aut
ON Livre (titre, auteur);

Comment fonctionne la recherche FULLTEXT ?

Lorsque vous faites une recherche FULLTEXT sur une chaîne de caractères, cette chaîne est découpée en mots. Est considéré comme un mot : toute suite de caractères composée de lettres, chiffres, tirets bas _ et apostrophes '. Par conséquent, un mot composé, comme "porte-clés" par exemple, sera considéré comme deux mots : "porte" et "clés".
Chacun de ces mots sera ensuite comparé avec les valeurs des colonnes sur lesquelles se fait la recherche. Si la colonne contient un des mots recherchés, on considère alors qu'elle correspond à la recherche.

Lorsque MySQL compare la chaîne de caractères que vous lui avez donnée, et les valeurs dans votre table, il ne tient pas compte de tous les mots qu'il rencontre. Les règles sont les suivantes :

• les mots rencontrés dans au moins la moitié des lignes sont ignorés (règle des 50 %) ;

• les mots trop courts (moins de quatre lettres) sont ignorés ;

• et les mots trop communs (en anglais, about, after, once, under, the…) ne sont également pas pris en compte.

Par conséquent, si vous voulez faire des recherches sur une table, il est nécessaire que cette table comporte au moins trois lignes, sinon chacun des mots sera présent dans au moins la moitié des lignes et aucun ne sera pris en compte.

Il est possible de redéfinir la longueur minimale des mots pris en compte, ainsi que la liste des mots trop communs. Je n'entrerai pas dans ces détails ici, vous trouverez ces informations dans la documentation officielle.

Les types de recherche

Il existe trois types de recherche FULLTEXT : la recherche naturelle, la recherche avec booléen, et enfin la recherche avec extension de requête.

Recherche naturelle

Lorsque l'on fait une recherche naturelle, il suffit qu'un seul mot de la chaîne de caractères recherchée se retrouve dans une ligne pour que celle-ci apparaisse dans les résultats. Attention cependant au fait que le mot exact doit se retrouver dans la valeur des colonnes de l'index FULLTEXT examiné.

Voici la syntaxe utilisée pour faire une recherche FULLTEXT :

SELECT *                                  -- Vous mettez évidemment les colonnes que vous voulez.
FROM nom_table
WHERE MATCH(colonne1[, colonne2, ...])   -- La (ou les) colonne(s) dans laquelle (ou lesquelles) on veut faire la recherche (index FULLTEXT correspondant nécessaire).
AGAINST ('chaîne recherchée');            -- La chaîne de caractères recherchée, entre guillemets bien sûr.

Si l'on veut préciser qu'on fait une recherche naturelle, on peut ajouter IN NATURAL LANGUAGE MODE. Ce n'est cependant pas obligatoire puisque la recherche naturelle est le mode de recherche par défaut.

SELECT *                                  
FROM nom_table
WHERE MATCH(colonne1[, colonne2, ...])  
AGAINST ('chaîne recherchée' IN NATURAL LANGUAGE MODE);

Premier exemple : on recherche "Terry" dans la colonne auteur de la table Livre .

SELECT *
FROM Livre
WHERE MATCH(auteur)
AGAINST ('Terry');

Deuxième exemple : On recherche d'abord "Petite", puis "Petit" dans la colonne titre.

SELECT *
FROM Livre
WHERE MATCH(titre)
AGAINST ('Petite');

SELECT *
FROM Livre
WHERE MATCH(titre)
AGAINST ('Petit');

Résultat de la première requête :

La deuxième requête (avec "Petit") ne renvoie aucun résultat. En effet, bien que "Petit" se retrouve deux fois dans la table (dans "La Petite marchande de prose" et "Les Petits Dieux"), il s'agit chaque fois d'une partie d'un mot, pas du mot exact.

Troisième exemple : on recherche "Henri" dans la colonne auteur.

SELECT *
FROM Livre
WHERE MATCH(auteur)
AGAINST ('Henri');

Ici par contre, on retrouve bien Henri-Pierre Roché en faisant une recherche sur "Henri", puisque Henri et Pierre sont considérés comme deux mots.

Quatrième exemple : on recherche "Jules", puis "Jules Verne" dans les colonnes titre et auteur.

SELECT *
FROM Livre
WHERE MATCH(auteur, titre)
AGAINST ('Jules');

SELECT *
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('Jules Verne');

Ces deux requêtes retournent les mêmes lignes. Vous pouvez donc voir que l'ordre des colonnes dans MATCH n'a aucune importance, du moment qu'un index FULLTEXT existe sur ces deux colonnes. Par ailleurs, la recherche se fait bien sur les deux colonnes, et sur chaque mot séparément, puisque les premières et troisièmes lignes contiennent 'Jules Verne' dans l'auteur, tandis que la deuxième contient uniquement 'Jules' dans le titre.

Par contre, l'ordre des lignes renvoyées par ces deux requêtes n'est pas le même. Lorsque vous utilisez MATCH... AGAINST dans une clause WHERE, les résultats sont par défaut triés par pertinence.

La pertinence est une valeur supérieure ou égale à 0 qui qualifie le résultat d'une recherche FULLTEXT sur une ligne. Si la ligne ne correspond pas du tout à la recherche, sa pertinence sera de 0. Si par contre elle correspond à la recherche, sa pertinence sera supérieure à 0. Ensuite, plus la ligne correspond bien à la recherche (nombre de mots trouvés par exemple), plus la pertinence sera élevée.
Vous pouvez voir la pertinence attribuée à une ligne en mettant l'expression MATCH... AGAINST dans le SELECT.

Cinquième exemple : affichage de la pertinence de la recherche

SELECT *, MATCH(titre, auteur) AGAINST ('Jules Verne Lune')
FROM Livre;

En fait, écrire :

WHERE MATCH(colonne(s)) AGAINST (mot(s) recherché(s))

Cela revient à écrire :

WHERE MATCH(colonne(s)) AGAINST (mot(s) recherché(s)) > 0

Donc seules les lignes ayant une pertinence supérieure à 0 (donc correspondant à la recherche) seront sélectionnées.

Recherche avec booléens

La recherche avec booléens possède les caractéristiques suivantes :

• elle ne tient pas compte de la règle des 50 % qui veut qu'un mot présent dans 50 % des lignes au moins soit ignoré ;

• elle peut se faire sur une ou des colonne(s) sur laquelle (lesquelles) aucun index FULLTEXT n'est défini (ce sera cependant beaucoup plus lent que si un index est présent) ;

• les résultats ne seront pas triés par pertinence par défaut.

Pour faire une recherche avec booléens, il suffit d'ajouter IN BOOLEAN MODE après la chaîne recherchée.

SELECT * 
FROM nom_table
WHERE MATCH(colonne) 
AGAINST('chaîne recherchée' IN BOOLEAN MODE);  -- IN BOOLEAN MODE à l'intérieur des parenthèses !

La recherche avec booléens permet d'être à la fois plus précis et plus approximatif dans ses recherches.

• Plus précis, car on peut exiger que certains mots se trouvent ou soient absents dans la ligne pour la sélectionner. On peut même exiger la présence de groupes de mots, plutôt que de rechercher chaque mot séparément.

• Plus approximatif, car on peut utiliser un astérisque * en fin de mot, pour préciser que le mot peut finir de n'importe quelle manière.

Pour exiger la présence ou l'absence de certains mots, on utilise les caractères + et -. Un mot précédé par +devra être présent dans la ligne et inversement, précédé par - il ne pourra pas être présent.

Exemple : Recherche sur le titre, qui doit contenir "bonheur" mais ne peut pas contenir "ogres".

SELECT * 
FROM Livre
WHERE MATCH(titre)
AGAINST ('+bonheur -ogres' IN BOOLEAN MODE);

Seule une ligne est ici sélectionnée, bien que "bonheur" soit présent dans deux. En effet, le second livre dont le titre contient "bonheur" est "Le Bonheur des ogres", qui contient le mot interdit "ogres".

Pour spécifier un groupe de mots exigés, on utilise les doubles guillemets. Tous les mots entre doubles guillemets devront non seulement être présents mais également apparaître dans l'ordre donné, et sans rien entre eux. Il faudra donc que l'on retrouve exactement ces mots pour avoir un résultat.

Exemple : recherche sur titre, qui doit contenir tout le groupe de mot entre guillemets doubles.

SELECT * 
FROM Livre 
WHERE MATCH(titre) 
AGAINST ('"Terre à la Lune"' IN BOOLEAN MODE);

SELECT * 
FROM Livre 
WHERE MATCH(titre) 
AGAINST ('"Lune à la Terre"' IN BOOLEAN MODE);

SELECT * 
FROM Livre 
WHERE MATCH(titre) 
AGAINST ('"Terre la Lune"' IN BOOLEAN MODE);

Résultat première requête :

La première requête renverra bien un résultat, contrairement à la seconde (car les mots ne sont pas dans le bon ordre) et à la troisième (car il manque le "à" dans la recherche - ou il y a un "à" en trop dans la ligne, ça dépend du point de vue).
"Voyage au centre de la Terre" n'est pas un résultat puisque seul le mot "Terre" est présent.

Pour utiliser l'astérisque, il suffit d'écrire le début du mot dont on est sûr, et de compléter avec un astérisque.

Exemple : recherche sur titre, sur tous les mots commençant par "petit".

SELECT * 
FROM Livre
WHERE MATCH(titre)
AGAINST ('petit*' IN BOOLEAN MODE);

"Petite" et "Petits" sont trouvés.

Exemple : recherche sur titre et auteur, de tous les mots commençant par "d".

SELECT * 
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('d*' IN BOOLEAN MODE);

Chacun des résultats contient au moins un mot commençant par "d" dans son titre ou son auteur ("Daniel", "Dormition"...).
Mais qu'en est-il de "Voyage au centre de la Terre" par exemple ? Avec le "de", il aurait dû être sélectionné. Mais c'est sans compter la règle qui dit que les mots de moins de quatre lettres sont ignorés. "De" n'ayant que deux lettres, ce résultat est ignoré.

Pour en finir avec la recherche avec booléens, sachez qu'il est bien sûr possible de mixer ces différentes possibilités. Les combinaisons sont nombreuses.

Exemple : recherche sur titre, qui doit contenir un mot commençant par "petit", mais ne peut pas contenir le mot "prose".

SELECT * 
FROM Livre
WHERE MATCH(titre)
AGAINST ('+petit* -prose' IN BOOLEAN MODE); -- mix d'un astérisque avec les + et -

Recherche avec extension de requête

Le dernier type de recherche est un peu particulier. En effet la recherche avec extension de requête se déroule en deux étapes.

1. Une simple recherche naturelle est effectuée.

2. Les résultats de cette recherche sont utilisés pour faire une seconde recherche naturelle.

Un exemple étant souvent plus clair qu'un long discours, passons-y tout de suite.

Une recherche naturelle effectuée avec la chaîne "Daniel" sur les colonnes auteur et titre donnerait ceci :

SELECT *
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('Daniel');

Par contre, si l'on utilise l'extension de requête, en ajoutant WITH QUERY EXPANSION, on obtient ceci :

SELECT *
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('Daniel' WITH QUERY EXPANSION);

En effet, dans la seconde étape, une recherche naturelle a été faite avec les chaînes "Daniel Pennac", "La Petite marchande de prose", "Comme un roman", "Au bonheur des ogres" et "La Fée Carabine", puisque ce sont les résultats de la première étape (une recherche naturelle sur "Daniel").
"Le Bonheur est dans le crime" a donc été ajouté aux résultats, à cause de la présence du mot "bonheur" dans son titre ("Bonheur" étant également présent dans "Au bonheur des ogres")

Ainsi s'achève la présentation des requêtes FULLTEXT, ainsi que le chapitre sur les index.

En résumé

• Un index est une structure de données qui reprend la liste ordonnée des valeurs auxquelles il se rapporte.

• Un index peut se faire sur une ou plusieurs colonnes ; et dans les cas d'une colonne de type alphanumérique (CHAR, VARCHAR, TEXT, etc.), il peut ne prendre en compte qu'une partie de la colonne (les x premiers caractères).

• Un index permet d'accélérer les recherches faites sur les colonnes constituant celui-ci.

• Un index UNIQUE ne peut contenir qu'une seule fois chaque valeur (ou combinaison de valeurs si l'index est composite, c'est-à-dire sur plusieurs colonnes).

• Un index FULLTEXT (réservé aux tables MyISAM) permet de faire des recherches complexes sur le contenu des colonnes le constituant.

Clés primaires, le retour

Les clés primaires ont déjà été introduites dans le chapitre de création des tables. Je vous avais alors donné la définition suivante :

La clé primaire d'une table est une contrainte d'unicité, composée d'une ou plusieurs colonnes, et qui permet d'identifier de manière unique chaque ligne de la table.

Examinons plus en détail cette définition.

• Contrainte d'unicité : ceci ressemble fort à un index UNIQUE.

• Composée d'une ou plusieurs colonnes : comme les index, les clés peuvent donc être composites.

• Permet d'identifier chaque ligne de manière unique : dans ce cas, une clé primaire ne peut pas être NULL.

Ces quelques considérations résument très bien l'essence des clés primaires. En gros, une clé primaire est un index UNIQUE sur une colonne qui ne peut pas être NULL.
D'ailleurs, vous savez déjà que l'on définit une clé primaire grâce aux mots-clés PRIMARY KEY. Or, nous avons vu dans le précédent chapitre que KEY s'utilise pour définir un index. Par conséquent, lorsque vous définissez une clé primaire, pas besoin de définir en plus un index sur la (les) colonne(s) qui compose(nt) celle-ci, c'est déjà fait ! Et pas besoin non plus de rajouter une contrainte NOT NULL
Pour le dire différemment, une contrainte de clé primaire est donc une combinaison de deux des contraintes que nous avons vues jusqu'à présent : UNIQUE et NOT NULL.

Choix de la clé primaire

Le choix d'une clé primaire est une étape importante dans la conception d'une table. Ce n'est pas parce que vous avez l'impression qu'une colonne, ou un groupe de colonnes, pourrait faire une bonne clé primaire que c'est le cas. Reprenons l'exemple d'une table Client, qui contient le nom, le prénom, la date de naissance et l'email des clients d'une société.

Chaque client a bien sûr un nom et un prénom. Est-ce que (nom, prenom) ferait une bonne clé primaire ? Non bien sûr : il est évident ici que vous risquez des doublons.
Et si on ajoute la date de naissance ? Les chances de doublons sont alors quasi nulles. Mais quasi nul, ce n'est pas nul… Qu'arrivera-t-il le jour où vous voyez débarquer un client qui a les mêmes nom et prénom qu'un autre, et qui est né le même jour ? On refait toute la base de données ? Non, bien sûr.
Et l'email alors ? Il est impossible que deux personnes aient la même adresse email, donc la contrainte d'unicité est respectée. Par contre, tout le monde n'est pas obligé d'avoir une adresse email. Difficile donc de mettre une contrainte NOT NULL sur cette colonne.

Par conséquent, on est bien souvent obligé d'ajouter une colonne pour jouer le rôle de la clé primaire. C'est cette fameuse colonne id, auto-incrémentée que nous avons déjà vue pour la table Animal.

Il y a une autre raison d'utiliser une colonne spéciale auto-incrémentée, de type INT (ou un de ses dérivés) pour la clé primaire. En effet, si l'on définit une clé primaire, c'est en partie dans le but d'utiliser au maximum cette clé pour faire des recherches dans la table. Bien sûr, parfois ce n'est pas possible, parfois vous ne connaissez pas l'id du client, et vous êtes obligés de faire une recherche par nom. Cependant, vous verrez bientôt que les clés primaires peuvent servir à faire des recherches de manière indirecte sur la table. Du coup, comme les recherches sont beaucoup plus rapides sur des nombres que sur des textes, il est souvent intéressant d'avoir une clé primaire composée de colonnes de type INT.

Enfin, il y a également l'argument de l'auto-incrémentation. Si vous devez remplir vous-mêmes la colonne de la clé primaire, étant donné que vous êtes humains (comment ça pas du tout ? :waw: ), vous risquez de faire une erreur. Avec une clé primaire auto-incrémentée, vous ne risquez rien : MySQL fait tout pour vous. De plus, on ne peut définir une colonne comme auto-incrémentée que si elle est de type INT et qu'il existe un index dessus. Dans le cas d'une clé primaire auto-incrémentée, on définit généralement la colonne comme un entier UNSIGNED, comme on l'a fait pour la table Animal.

PRIMARY KEY or not PRIMARY KEY

Je me dois de vous dire que d'un point de vue technique, avoir une clé primaire sur chaque table n'est pas obligatoire. Vous pourriez travailler toute votre vie sur une base de données sans aucune clé primaire, et ne jamais voir un message d'erreur à ce propos.
Cependant, d'un point de vue conceptuel, ce serait une grave erreur. Ce n'est pas le propos de ce tutoriel que de vous enseigner les étapes de conception d'une base de données mais, s'il vous plaît, pensez à mettre une clé primaire sur chacune de vos tables. Si l'utilité n'en est pas complètement évidente pour vous pour le moment, elle devrait le devenir au fur et à mesure de votre lecture.

Création d'une clé primaire

La création des clés primaires étant extrêmement semblable à la création d'index simples, j'espère que vous me pardonnerez si je ne détaille pas trop mes explications. :ange:

Donc, à nouveau, la clé primaire peut être créée en même temps que la table, ou par la suite.

Lors de la création de la table

On peut donc préciser PRIMARY KEY dans la description de la colonne qui doit devenir la clé primaire (pas de clé composite dans ce cas) :

CREATE TABLE [IF NOT EXISTS] Nom_table (
    colonne1 description_colonne1 PRIMARY KEY [,
    colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...,]
)
[ENGINE=moteur];

Exemple : création de la table Animal en donnant la clé primaire dans la description de la colonne.

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

Ou bien, on ajoute la clé à la suite des colonnes.

CREATE TABLE [IF NOT EXISTS] Nom_table (
    colonne1 description_colonne1 [,
    colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...],
    [CONSTRAINT [symbole_contrainte]] PRIMARY KEY (colonne_pk1 [, colonne_pk2, ...])  -- comme pour les index UNIQUE, CONSTRAINT est facultatif
)
[ENGINE=moteur];

C'est ce que nous avions fait d'ailleurs pour la table Animal. Cette méthode permet bien sûr la création d'une clé composite (avec plusieurs colonnes).

Exemple : création de Animal.

CREATE TABLE Animal (
    id SMALLINT 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;

Après création de la table

On peut toujours utiliser ALTER TABLE. Par contre, CREATE INDEX n'est pas utilisable pour les clés primaires.

ALTER TABLE nom_table
ADD [CONSTRAINT [symbole_contrainte]] PRIMARY KEY (colonne_pk1 [, colonne_pk2, ...]);

Suppression de la clé primaire

ALTER TABLE nom_table
DROP PRIMARY KEY

Pas besoin de préciser de quelle clé il s'agit, puisqu'il ne peut y en avoir qu'une seule par table !

Clés étrangères

Les clés étrangères ont pour fonction principale la vérification de l'intégrité de votre base. Elles permettent de s'assurer que vous n'insérez pas de bêtises…

Reprenons l'exemple dans lequel on a une table Client et une table Commande. Dans la table Commande, on a une colonne qui contient une référence au client. Ici, 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.

C'est bien joli, mais que se passe-t-il si M. Hadrien Piroux passe une commande de 15 tubes de colle, et qu'à l'insertion dans la table Commande, votre doigt dérape et met 45 comme numéro de client ? C'est l'horreur ! Vous avez dans votre base de données une commande passée par un client inexistant, et vous passez votre après-midi du lendemain à vérifier tous vos bons de commande de la veille pour retrouver qui a commandé ces 15 tubes de colle.
Magnifique perte de temps !

Ce serait quand même sympathique si, à l'insertion d'une ligne dans la table Commande, un gentil petit lutin allait vérifier que le numéro de client indiqué correspond bien à quelque chose dans la table Client, non ?
Ce lutin, ou plutôt cette lutine, existe ! Elle s'appelle "clé étrangère".

Par conséquent, si vous créez une clé étrangère sur la colonne client de la table Commande, en lui donnant comme référence la colonne numero de la table Client, MySQL ne vous laissera plus jamais insérer un numéro de client inexistant dans la table Commande. Il s'agit bien d'une contrainte !

Avant d'entamer une danse de joie, parce que quand même le SQL c'est génial, restez concentrés cinq minutes, le temps de lire et retenir quelques points importants.

• Comme pour les index et les clés primaires, il est possible de créer des clés étrangères composites.

• Lorsque vous créez une clé étrangère sur une colonne (ou un groupe de colonnes) – la colonne client de Commande dans notre exemple –, un index est automatiquement ajouté sur celle-ci (ou sur le groupe).

• Par contre, la colonne (le groupe de colonnes) qui sert de référence - la colonne numero de Client - doit déjà posséder un index (où être clé primaire bien sûr).

• La colonne (ou le groupe de colonnes) sur laquelle (lequel) la clé est créée doit être exactement du même type que la colonne (le groupe de colonnes) qu'elle (il) référence. Cela implique qu'en cas de clé composite, il faut le même nombre de colonnes dans la clé et la référence. Donc, si numero (dans Client) est un INT UNSIGNED, client (dans Commande) doit être de type INT UNSIGNED aussi.

• Tous les moteurs de table ne permettent pas l'utilisation des clés étrangères. Par exemple, MyISAM ne le permet pas, contrairement à InnoDB.

Création

Une clé étrangère est un peu plus complexe à créer qu'un index ou une clé primaire, puisqu'il faut deux éléments :

• la ou les colonnes sur laquelle (lesquelles) on crée la clé - on utilise FOREIGN KEY ;

• la ou les colonnes qui va (vont) servir de référence - on utilise REFERENCES.

Lors de la création de la table

Du fait de la présence de deux paramètres, une clé étrangère ne peut que s'ajouter à la suite des colonnes, et pas directement dans la description d'une colonne. Par ailleurs, je vous conseille ici de créer explicitement une contrainte (grâce au mot-clé CONSTRAINT) et de lui donner un symbole. En effet, pour les index, on pouvait utiliser leur nom pour les identifier ; pour les clés primaires, le nom de la table suffisait puisqu'il n'y en a qu'une par table. Par contre, pour différencier facilement les clés étrangères d'une table, il est utile de leur donner un nom, à travers la contrainte associée.
À nouveau, je respecte certaines conventions de nommage : mes clés étrangères ont des noms commençant par fk (pour FOREIGN KEY), suivi du nom de la colonne dans la table puis (si elle s'appelle différemment) du nom de la colonne de référence, le tout séparé par des _ (fk_client_numero par exemple).

CREATE TABLE [IF NOT EXISTS] Nom_table (
    colonne1 description_colonne1,
    [colonne2 description_colonne2,
    colonne3 description_colonne3,
    ...,]
    [ [CONSTRAINT [symbole_contrainte]]  FOREIGN KEY (colonne(s)_clé_étrangère) REFERENCES table_référence (colonne(s)_référence)]
)
[ENGINE=moteur];

Donc si on imagine les tables Client et Commande, pour créer la table Commande avec une clé étrangère ayant pour référence la colonne numero de la table Client, on utilisera :

CREATE TABLE Commande (
    numero INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    client INT UNSIGNED NOT NULL,
    produit VARCHAR(40),
    quantite SMALLINT DEFAULT 1,
    CONSTRAINT fk_client_numero          -- On donne un nom à notre clé
        FOREIGN KEY (client)             -- Colonne sur laquelle on crée la clé
        REFERENCES Client(numero)        -- Colonne de référence
)
ENGINE=InnoDB;                          -- MyISAM interdit, je le rappelle encore une fois !

Après création de la table

Tout comme pour les clés primaires, pour créer une clé étrangère après création de la table, il faut utiliser ALTER TABLE.

ALTER TABLE Commande
ADD CONSTRAINT fk_client_numero FOREIGN KEY client REFERENCES Client(numero);

Suppression d'une clé étrangère

Il peut y avoir plusieurs clés étrangères par table. Par conséquent, lors d'une suppression il faut identifier la clé à détruire. Cela se fait grâce au symbole de la contrainte.

ALTER TABLE nom_table
DROP FOREIGN KEY symbole_contrainte

Modification de notre base

Maintenant que vous connaissez les clés étrangères, nous allons en profiter pour modifier notre base et ajouter quelques tables afin de préparer le prochain chapitre, qui portera sur les jointures.

Jusqu'à maintenant, la seule information sur l'espèce des animaux de notre élevage était son nom courant. Nous voudrions maintenant stocker aussi son nom latin, ainsi qu'une petite description. Que faire ? Ajouter deux colonnes à notre table ? nom_latin_espece et description_espece ?
J'espère que vous n'avez envisagé cette possibilité qu'une demi-seconde, car il est assez évident que c'est une très mauvaise solution. En effet, ça obligerait à stocker la même description pour chaque chien, chaque chat, etc. Ainsi que le même nom latin. Nous le faisions déjà avec le nom courant, et ça aurait déjà pu poser problème. Imaginez que pour un animal vous fassiez une faute d'orthographe au nom de l'espèce, "chein" ou lieu de "chien" par exemple. L'animal en question n'apparaîtrait jamais lorsque vous feriez une recherche par espèce.
Il faudrait donc un système qui nous permette de ne pas répéter la même information plusieurs fois, et qui limite les erreurs que l'on pourrait faire.

La bonne solution est de créer une seconde table : la table Espece. Cette table aura 4 colonnes : le nom courant, le nom latin, une description, et un numéro d'identification (qui sera la clé primaire de cette table).

La table Espece

Voici donc la commande que je vous propose d'exécuter pour créer la table Espece :

CREATE TABLE Espece (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    nom_courant VARCHAR(40) NOT NULL,
    nom_latin VARCHAR(40) NOT NULL UNIQUE,
    description TEXT,
    PRIMARY KEY(id)
)
ENGINE=InnoDB;

On met un index UNIQUE sur la colonne nom_latin pour être sûr que l'on ne rentrera pas deux fois la même espèce. Pourquoi sur le nom latin et pas sur le nom courant ? Tout simplement parce que le nom latin est beaucoup plus rigoureux et réglementé que le nom courant. On peut avoir plusieurs dénominations courantes pour une même espèce ; ce n'est pas le cas avec le nom latin.

Bien, remplissons donc cette table avec les espèces déjà présentes dans la base :

INSERT INTO Espece (nom_courant, nom_latin, description) VALUES
    ('Chien', 'Canis canis', 'Bestiole à quatre pattes qui aime les caresses et tire souvent la langue'),
    ('Chat', 'Felis silvestris', 'Bestiole à quatre pattes qui saute très haut et grimpe aux arbres'),
    ('Tortue d''Hermann', 'Testudo hermanni', 'Bestiole avec une carapace très dure'),
    ('Perroquet amazone', 'Alipiopsitta xanthops', 'Joli oiseau parleur vert et jaune');

Ce qui nous donne la table suivante :

Vous aurez remarqué que j'ai légèrement modifié les noms des espèces "perroquet" et "tortue". En effet, et j'espère que les biologistes pourront me pardonner, "perroquet" et "tortue" ne sont pas des espèces, mais des ordres. J'ai donc précisé un peu (si je donne juste l'ordre, c'est comme si je mettais "carnivore" au lieu de "chat" - ou "chien" d'ailleurs).

Bien, mais cela ne suffit pas ! Il faut également modifier notre table Animal. Nous allons ajouter une colonne espece_id, qui contiendra l'id de l'espèce à laquelle appartient l'animal, et remplir cette colonne. Ensuite nous pourrons supprimer la colonne espece, qui n'aura plus de raison d'être.
La colonne espece_id sera une clé étrangère ayant pour référence la colonne id de la table Espece. Je rappelle donc que ça signifie qu'il ne sera pas possible d'insérer dans espece_id un nombre qui n'existe pas dans la colonne id de la table Espece.

La table Animal

Je vous conseille d'essayer d'écrire vous-même les requêtes avant de regarder comment je fais.

-- Ajout d'une colonne espece_id
ALTER TABLE Animal ADD COLUMN espece_id SMALLINT UNSIGNED; -- même type que la colonne id de Espece

-- Remplissage de espece_id
UPDATE Animal SET espece_id = 1 WHERE espece = 'chien';
UPDATE Animal SET espece_id = 2 WHERE espece = 'chat';
UPDATE Animal SET espece_id = 3 WHERE espece = 'tortue';
UPDATE Animal SET espece_id = 4 WHERE espece = 'perroquet';

-- Suppression de la colonne espece
ALTER TABLE Animal DROP COLUMN espece;

-- Ajout de la clé étrangère
ALTER TABLE Animal
ADD CONSTRAINT fk_espece_id FOREIGN KEY (espece_id) REFERENCES Espece(id);

Pour tester l'efficacité de la clé étrangère, essayons d'ajouter un animal dont l'espece_id est 5 (qui n'existe donc pas dans la table Espece) :

INSERT INTO Animal (nom, espece_id, date_naissance)
VALUES ('Caouette', 5, '2009-02-15 12:45:00');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`elevage`.`animal`, CONSTRAINT `fk_espece_id` FOREIGN KEY (`espece_id`) REFERENCES `espece` (`id`))

Elle est pas belle la vie ?

J'en profite également pour ajouter une contrainte NOT NULL sur la colonne espece_id. Après tout, si espece ne pouvait pas être NULL, pas de raison qu'espece_id puisse l'être ! Ajoutons également l'index UNIQUE sur (nom, espece_id) dont on a déjà discuté.

ALTER TABLE Animal MODIFY espece_id SMALLINT UNSIGNED NOT NULL;

CREATE UNIQUE INDEX ind_uni_nom_espece_id ON Animal (nom, espece_id);

Notez qu'il n'était pas possible de mettre la contrainte NOT NULL à la création de la colonne, puisque tant que l'on n'avait pas rempli espece_id, elle contenait NULL pour toutes les lignes.

Voilà, nos deux tables sont maintenant prêtes. Mais avant de vous lâcher dans l'univers merveilleux des jointures et des sous-requêtes, nous allons encore compliquer un peu les choses. Parce que c'est bien joli pour un éleveur de pouvoir reconnaître un chien d'un chat, mais il serait de bon ton de reconnaître également un berger allemand d'un teckel, non ?

Par conséquent, nous allons encore ajouter deux choses à notre base. D'une part, nous allons ajouter une table Race, basée sur le même schéma que la table Espece. Il faudra également ajouter une colonne à la table Animal, qui contiendra l'id de la race de l'animal. Contrairement à la colonne espece_id, celle-ci pourra être NULL. Il n'est pas impossible que nous accueillions des bâtards, ou que certaines espèces que nous élevons ne soient pas classées en plusieurs races différentes.
Ensuite, nous allons garder une trace du pedigree de nos animaux. Pour ce faire, il faut pouvoir connaître ses parents. Donc, nous ajouterons deux colonnes à la table Animal : pere_id et mere_id, qui contiendront respectivement l'id du père et l'id de la mère de l'animal.

Ce sont toutes des commandes que vous connaissez, donc je ne détaille pas plus.

-- --------------------------
-- CREATION DE  LA TABLE Race
-- --------------------------
CREATE TABLE Race (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    nom VARCHAR(40) NOT NULL,
    espece_id SMALLINT UNSIGNED NOT NULL,     -- pas de nom latin, mais une référence vers l'espèce
    description TEXT,
    PRIMARY KEY(id),
    CONSTRAINT fk_race_espece_id FOREIGN KEY (espece_id) REFERENCES Espece(id)  -- pour assurer l'intégrité de la référence
)
ENGINE = InnoDB;

-- -----------------------
-- REMPLISSAGE DE LA TABLE
-- -----------------------
INSERT INTO Race (nom, espece_id, description)
VALUES ('Berger allemand', 1, 'Chien sportif et élégant au pelage dense, noir-marron-fauve, noir ou gris.'),
('Berger blanc suisse', 1, 'Petit chien au corps compact, avec des pattes courtes mais bien proportionnées et au pelage tricolore ou bicolore.'),
('Boxer', 1, 'Chien de taille moyenne, au poil ras de couleur fauve ou bringé avec quelques marques blanches.'),
('Bleu russe', 2, 'Chat aux yeux verts et à la robe épaisse et argentée.'),
('Maine coon', 2, 'Chat de grande taille, à poils mi-longs.'),
('Singapura', 2, 'Chat de petite taille aux grands yeux en amandes.'),
('Sphynx', 2, 'Chat sans poils.');

-- ---------------------------------------------
-- AJOUT DE LA COLONNE race_id A LA TABLE Animal
-- ---------------------------------------------
ALTER TABLE Animal ADD COLUMN race_id SMALLINT UNSIGNED;

ALTER TABLE Animal
ADD CONSTRAINT fk_race_id FOREIGN KEY (race_id) REFERENCES Race(id);

-- -------------------------
-- REMPLISSAGE DE LA COLONNE
-- -------------------------
UPDATE Animal SET race_id = 1 WHERE id IN (1, 13, 20, 18, 22, 25, 26, 28);
UPDATE Animal SET race_id = 2 WHERE id IN (12, 14, 19, 7);
UPDATE Animal SET race_id = 3 WHERE id IN (23, 17, 21, 27);
UPDATE Animal SET race_id = 4 WHERE id IN (33, 35, 37, 41, 44, 31, 3);
UPDATE Animal SET race_id = 5 WHERE id IN (43, 40, 30, 32, 42, 34, 39, 8);
UPDATE Animal SET race_id = 6 WHERE id IN (29, 36, 38);

-- -------------------------------------------------------
-- AJOUT DES COLONNES mere_id ET pere_id A LA TABLE Animal
-- -------------------------------------------------------
ALTER TABLE Animal ADD COLUMN mere_id SMALLINT UNSIGNED;

ALTER TABLE Animal
ADD CONSTRAINT fk_mere_id FOREIGN KEY (mere_id) REFERENCES Animal(id);

ALTER TABLE Animal ADD COLUMN pere_id SMALLINT UNSIGNED;

ALTER TABLE Animal
ADD CONSTRAINT fk_pere_id FOREIGN KEY (pere_id) REFERENCES Animal(id);

-- -------------------------------------------
-- REMPLISSAGE DES COLONNES mere_id ET pere_id
-- -------------------------------------------
UPDATE Animal SET mere_id = 18, pere_id = 22 WHERE id = 1;
UPDATE Animal SET mere_id = 7, pere_id = 21 WHERE id = 10;
UPDATE Animal SET mere_id = 41, pere_id = 31 WHERE id = 3;
UPDATE Animal SET mere_id = 40, pere_id = 30 WHERE id = 2;

A priori, la seule chose qui pourrait vous avoir surpris dans ces requêtes ce sont les clés étrangères sur mere_id et pere_id qui référencent toutes deux une autre colonne de la même table.

Bien, maintenant que nous avons trois tables et des données sympathiques à exploiter, nous allons passer aux choses sérieuses avec les jointures d'abord, puis les sous-requêtes.

En résumé

• Une clé primaire permet d'identifier chaque ligne de la table de manière unique : c'est à la fois une contrainte d'unicité et une contrainte NOT NULL.

• Chaque table doit définir une clé primaire, et une table ne peut avoir qu'une seule clé primaire.

• Une clé étrangère permet de définir une relation entre deux tables, et d'assurer la cohérence des données.

Principe des jointures et notion d'alias

Principe des jointures

Sans surprise, le principe des jointures est de joindre plusieurs tables. Pour ce faire, on utilise les informations communes des tables.

Par exemple, lorsque nous avons ajouté dans notre base les informations sur les espèces (leur nom latin et leur description), je vous ai dit que ce serait une très mauvaise idée de tout mettre dans la table Animal, car il nous faudrait alors répéter la même description pour tous les chiens, la même pour toutes les tortues, etc.
Cependant, vous avez sans doute remarqué que du coup, si vous voulez afficher la description de l'espèce de Cartouche (votre petit préféré), vous avez besoin de deux requêtes.

Étape 1 : on trouve l'id de l'espèce de Cartouche grâce à la table Animal.

SELECT espece_id FROM Animal WHERE nom = 'Cartouche';

Étape 2 : on trouve la description de l'espèce grâce à son id.

SELECT description FROM Espece WHERE id = 1;

Ne serait-ce pas merveilleux de pouvoir faire tout ça (et plus encore) en une seule requête ?
C'est là que les jointures entrent en jeu ; on va utiliser l'information commune entre les deux tables : l'id de l'espèce, qui est présente dans Animal avec la colonne espece_id, et dans Espece avec la colonne id.

SELECT Espece.description 
FROM Espece 
INNER JOIN Animal 
    ON Espece.id = Animal.espece_id 
WHERE Animal.nom = 'Cartouche';

Et voilà le travail !

En fait, lorsque l'on fait une jointure, on crée une table virtuelle et temporaire qui reprend les colonnes des tables liées. Le schéma ci-dessous illustre ce principe.

Au départ, on a deux tables : Animal (id, sexe, nom, race_id, espece_id) et Espece (id, nom_courant, nom_latin). Les deux premières lignes d'Animal correspondent à la première ligne d'Espece, et la troisième ligne d'Animal à la deuxième ligne d'Espece. Une fois les deux tables jointes, on obtient une table possédant toutes les colonnes d'Animal et toutes les colonnes d'Espece, avec les valeurs correspondantes de chaque table. On peut voir que les cinquième et sixième colonnes de la table de jointure ont les mêmes valeurs.
Ensuite, de cette table virtuelle, on peut extraire ce que l'on veut. La colonne nom_latin pour la ligne ayant "Caribou" dans la colonne nom, par exemple.

Notion d'alias

Je fais ici une petite parenthèse avant de vous expliquer en détail le fonctionnement des jointures pour vous parler d'un petit truc bien utile : les alias.

Les alias sont des noms de remplacement, que l'on donne de manière temporaire (le temps d'une requête en fait) à une colonne, une table, une donnée. Les alias sont introduits par le mot-clé AS. Ce mot-clé est facultatif, vous pouvez très bien définir un alias sans utiliser AS, mais je préfère personnellement toujours le mettre. Je trouve qu'on y voit plus clair.

Comment ça marche ?

Prenons cette requête toute simple :

SELECT 5+3;

Imaginons que ce calcul savant représente en fait le nombre de chiots de Cartouche, qui a eu une première portée de 5 chiots, et une seconde de seulement 3 chiots. Nous voudrions donc indiquer qu'il s'agit bien de ça, et non pas d'un calcul inutile destiné simplement à illustrer une notion obscure.

Facile ! Il suffit d'utiliser les alias :

SELECT 5+3 AS Chiots_Cartouche;

-- OU, sans utiliser AS

SELECT 5+3 Chiots_Cartouche;

Bon, tout ça c'est bien joli, mais pour l'instant ça n'a pas l'air très utile…
Prenons un exemple plus parlant : retournez voir le schéma qui explique le principe des jointures un peu plus haut. La table virtuelle résultant de la jointure des tables Espece et Animal possède plusieurs colonnes :
id sexe nom race_id espece_id id nom_courant nom_latin
Mais que vois-je ? J'ai deux colonnes id ! Comment faire pour les différencier ? Comment être sûr de savoir à quoi elles se rapportent ?
Avec les alias pardi ! Il suffit de donner l'alias espece_id à la colonne id de la table Espece, et animal_id à la colonne id de la table Animal.
Tout à coup, ça vous semble plus intéressant non ?

• id

• sexe

• nom

• race_id

• espece_id

• id

• nom_courant

• nom_latin

Je vais vous laisser sur ce sentiment. Il n'y a pas grand-chose de plus à dire sur les alias, vous en comprendrez toute l'utilité à travers les nombreux exemples dans la suite de ce cours. L'important pour l'instant est que vous sachiez que ça existe et comment les définir.

Jointure interne

L'air de rien, dans l'introduction, je vous ai déjà montré comment faire une jointure. La première émotion passée, vous devriez vous être dit "Tiens, mais ça n'a pas l'air bien compliqué en fait, les jointures".
En effet, une fois que vous aurez compris comment réfléchir aux jointures, tout se fera tout seul. Personnellement, ça m'aide vraiment d'imaginer la table virtuelle créée par la jointure, et de travailler sur cette table pour tout ce qui est conditions, tris, etc.

Revoici la jointure que je vous ai fait faire, et qui est en fait une jointure interne.

SELECT Espece.description 
FROM Espece 
INNER JOIN Animal 
    ON Espece.id = Animal.espece_id 
WHERE Animal.nom = 'Cartouche';

Décomposons !

• SELECT Espece.description : je sélectionne la colonne description de la table Espece.

• FROM Espece: je travaille sur la table Espece.

• INNER JOIN Animal : je la joins (avec une jointure interne) à la table Animal.

• ON Espece.id = Animal.espece_id : la jointure se fait sur les colonnes id de la table Espece et espece_id de la table Animal, qui doivent donc correspondre.

• WHERE Animal.nom = 'Cartouche': dans la table résultant de la jointure, je sélectionne les lignes qui ont la valeur "Cartouche" dans la colonne nom venant de la table Animal.

Si vous avez compris ça, vous avez tout compris !

Syntaxe

Comme d'habitude, voici donc la syntaxe à utiliser pour faire des requêtes avec jointure(s) interne(s).

SELECT *                                   -- comme d'habitude, vous sélectionnez les colonnes que vous voulez
FROM nom_table1   
[INNER] JOIN nom_table2                    -- INNER explicite le fait qu'il s'agit d'une jointure interne, mais c'est facultatif
    ON colonne_table1 = colonne_table2     -- sur quelles colonnes se fait la jointure
                                           -- vous pouvez mettre colonne_table2 = colonne_table1, l'ordre n'a pas d'importance

[WHERE ...]                               
[ORDER BY ...]                            -- les clauses habituelles sont bien sûr utilisables !
[LIMIT ...]

Condition de jointure

La clause ON sert à préciser la condition de la jointure. C'est-à-dire sur quel(s) critère(s) les deux tables doivent être jointes. Dans la plupart des cas, il s'agira d'une condition d'égalité simple, comme ON Animal.espece_id = Espece.id. Il est cependant tout à fait possible d'avoir plusieurs conditions à remplir pour lier les deux tables. On utilise alors les opérateurs logiques habituels. Par exemple, une jointure peut très bien se faire sur plusieurs colonnes :

SELECT *
FROM nom_table1
INNER JOIN nom_table2
   ON colonne1_table1 = colonne1_table2
      AND colonne2_table1 = colonne2_table2
      [AND ...];

Expliciter le nom des colonnes

Il peut arriver que vous ayez dans vos deux tables des colonnes portant le même nom. C'est le cas dans notre exemple, puisque la table Animal comporte une colonne id, tout comme la table Espece. Il est donc important de préciser de quelle colonne on parle dans ce cas-là.
Vous l'avez vu dans notre requête, on utilise pour ça l'opérateur . (nom_table.nom_colonne). Pour les colonnes ayant un nom non-ambigu (qui n'existe dans aucune autre table de la jointure), il n'est pas obligatoire de préciser la table.
En général, je précise la table quand il s'agit de grosses requêtes avec plusieurs jointures. En revanche, pour les petites jointures courantes, il est vrai que c'est moins long à écrire si on ne précise pas la table.

Exemple : sélection du nom des animaux commençant par "Ch", ainsi que de l'id et la description de leur espèce.

SELECT Espece.id,                   -- ici, pas le choix, il faut préciser
       Espece.description,          -- ici, on pourrait mettre juste description
       Animal.nom                   -- idem, la précision n'est pas obligatoire. C'est cependant plus clair puisque les espèces ont un nom aussi
FROM Espece   
INNER JOIN Animal
     ON Espece.id = Animal.espece_id
WHERE Animal.nom LIKE 'Ch%';

Utiliser les alias

Les alias sont souvent utilisés avec les jointures. Ils permettent notamment de renommer les tables, et ainsi d'écrire moins de code.

Exemple : on renomme la table Espece "e", et la table Animal "a".

SELECT e.id,                  
       e.description,          
       a.nom                   
FROM Espece AS e          -- On donne l'alias "e" à Espece
INNER JOIN Animal AS a    -- et l'alias "a" à Animal.
     ON e.id = a.espece_id
WHERE a.nom LIKE 'Ch%';

Comme vous le voyez, le code est plus compact. Ici encore, c'est quelque chose que j'utilise souvent pour de petites requêtes ponctuelles. Par contre, pour de grosses requêtes, je préfère les noms explicites ; c'est ainsi plus facile de s'y retrouver.

Une autre utilité des alias est de renommer les colonnes pour que le résultat soit plus clair. Observez le résultat de la requête précédente. Vous avez trois colonnes : id, description et nom. Le nom de la table dont provient la colonne n'est indiqué nulle part. A priori, vous savez ce que vous avez demandé, surtout qu'il n'y a pas encore trop de colonnes, mais imaginez que vous sélectionniez une vingtaine de colonnes. Ce serait quand même mieux de savoir de quel id on parle, s'il s'agit du nom de l'animal, de son maître, du père, du fils ou du Saint-Esprit !
Il est intéressant là aussi d'utiliser les alias.

Exemple : on donne des alias aux colonnes (id_espece pour id de la table Espece, description_espece pour Espece.description et nom_bestiole pour Animal.nom).

SELECT Espece.id AS id_espece,                  
       Espece.description AS description_espece,          
       Animal.nom AS nom_bestiole                   
FROM Espece   
INNER JOIN Animal
     ON Espece.id = Animal.espece_id
WHERE Animal.nom LIKE 'Ch%';

C'est tout de suite plus clair !

Pourquoi "interne" ?

INNER JOIN permet donc de faire une jointure interne sur deux tables. Mais que signifie donc ce "interne" ?

C'est très simple ! Lorsque l'on fait une jointure interne, cela veut dire qu'on exige qu'il y ait des données de part et d'autre de la jointure. Donc, si l'on fait une jointure sur la colonne a de la table A et la colonne b de la table B :

SELECT * 
FROM A
INNER JOIN B 
    ON A.a = B.b

Ceci retournera uniquement les lignes pour lesquelles A.a et B.b correspondent.

Exemple : on veut connaître la race des chats :

SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Animal
INNER JOIN Race
    ON Animal.race_id = Race.id
WHERE Animal.espece_id = 2             -- ceci correspond aux chats
ORDER BY Race.nom, Animal.nom;

On peut voir ici que les chats Choupi et Roucky pour lesquels je n'ai pas d'information sur la race (race_id est NULL), ne sont pas repris dans les résultats. De même, aucun des chats n'est de la race "Sphynx", celle-ci n'est donc pas reprise. Si je veux les inclure, je dois utiliser une jointure externe.

Jointure externe

Comme je viens de vous le dire, une jointure externe permet de sélectionner également les lignes pour lesquelles il n'y a pas de correspondance dans une des tables jointes. MySQL permet deux types de jointures externes : les jointures par la gauche et les jointures par la droite.

Jointures par la gauche

Lorsque l'on fait une jointure par la gauche (grâce aux mots-clés LEFT JOIN ou LEFT OUTER JOIN), cela signifie que l'on veut toutes les lignes de la table de gauche (sauf restrictions dans une clause WHERE bien sûr), même si certaines n'ont pas de correspondance avec une ligne de la table de droite.

Alors, table de gauche, table de droite, laquelle est laquelle ? C'est très simple, nous lisons de gauche à droite, donc la table de gauche est la première table mentionnée dans la requête, c'est-à-dire, en général, la table donnée dans la clause FROM.

Si l'on veut de nouveau connaître la race des chats, mais que cette fois-ci nous voulons également afficher les chats qui n'ont pas de race, on peut utiliser la jointure suivante (je ne prends que les chats dont le nom commence par "C" afin de réduire le nombre de lignes, mais vous pouvez choisir les conditions que vous voulez) :

SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Animal                                               -- Table de gauche
LEFT JOIN Race                                            -- Table de droite
    ON Animal.race_id = Race.id
WHERE Animal.espece_id = 2 
    AND Animal.nom LIKE 'C%'
ORDER BY Race.nom, Animal.nom;

-- OU 

SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Animal                                               -- Table de gauche
LEFT OUTER JOIN Race                                      -- Table de droite
    ON Animal.race_id = Race.id
WHERE Animal.espece_id = 2
    AND Animal.nom LIKE 'C%'
ORDER BY Race.nom, Animal.nom;

On ne connaît pas la race de Choupi, et pourtant il fait bien partie des lignes sélectionnées alors qu'avec la jointure interne il n'apparaissait pas. Simplement, les colonnes qui viennent de la table Race (la colonne Race.nom AS race dans ce cas-ci) sont NULL pour les lignes qui n'ont pas de correspondance (la ligne de Choupi ici).

Jointures par la droite

Les jointures par la droite (RIGHT JOIN ou RIGHT OUTER JOIN), c'est évidemment le même principe, sauf que ce sont toutes les lignes de la table de droite qui sont sélectionnées même s'il n'y a pas de correspondance dans la table de gauche.

Exemple : toujours avec les races de chats.

SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Animal                                                -- Table de gauche
RIGHT JOIN Race                                            -- Table de droite
    ON Animal.race_id = Race.id
WHERE Race.espece_id = 2
ORDER BY Race.nom, Animal.nom;

- OU

SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Animal                                              -- Table de gauche
RIGHT OUTER JOIN Race                                    -- Table de droite
    ON Animal.race_id = Race.id
WHERE Race.espece_id = 2
ORDER BY Race.nom, Animal.nom;

On a bien une ligne avec la race "Sphynx", bien que nous n'ayons aucun sphynx dans notre table Animal.

À noter que toutes les jointures par la droite peuvent être faites grâce à une jointure par la gauche (et vice versa). Voici l'équivalent avec une jointure par la gauche de la requête que nous venons d'écrire :

SELECT Animal.nom AS nom_animal, Race.nom AS race
FROM Race                                               -- Table de gauche
LEFT JOIN Animal                                        -- Table de droite
    ON Animal.race_id = Race.id
WHERE Race.espece_id = 2
ORDER BY Race.nom, Animal.nom;

Syntaxes alternatives

Les syntaxes que je vous ai montrées jusqu'ici, avec [INNER] JOIN et LEFT|RIGHT [OUTER] JOIN, sont les syntaxes classiques que vous retrouverez le plus souvent. Il existe cependant d'autres manières de faire des jointures.

Jointures avec USING

Lorsque les colonnes qui servent à joindre les deux tables ont le même nom, vous pouvez utiliser la clause USING au lieu de la clause ON.

SELECT *
FROM table1
[INNER | LEFT | RIGHT] JOIN table2 USING (colonneJ);  -- colonneJ est présente dans les deux tables

-- équivalent à 

SELECT *
FROM table1
[INNER | LEFT | RIGHT] JOIN table2 ON Table1.colonneJ = table2.colonneJ;

Jointures naturelles

Comme pour les jointures avec USING, il est possible d'utiliser les jointures naturelles dans le cas où les colonnes servant à la jointure ont le même nom dans les deux tables.
Simplement, dans le cas d'une jointure naturelle, on ne donne pas la (les) colonne(s) sur laquelle (lesquelles) joindre les tables : c'est déterminé automatiquement. Donc si on a les trois tables suivantes :

• table1 : colonnes A, B, C

• table2 : colonnes B, E, F

• table3 : colonnes A, C, E

Exemple 1 : jointure de table1 et table2 (une colonne ayant le même nom : B).

SELECT * 
FROM table1
NATURAL JOIN table2;

-- EST ÉQUIVALENT À

SELECT *
FROM table1
INNER JOIN table2 
    ON table1.B = table2.B;

Exemple 2 : jointure de table1 et table3 (deux colonnes ayant le même nom : A et C).

SELECT * 
FROM table1
NATURAL JOIN table3;

-- EST ÉQUIVALENT À

SELECT *
FROM table1
INNER JOIN table3
    ON table1.A = table3.A AND table1.C = table3.C;

Pour utiliser ce type de jointure, il faut donc être certain que toutes les colonnes nécessaires à la jointure ont le même nom dans les deux tables, mais aussi que les colonnes ayant le même nom sont uniquement celles qui servent à la jointure.

Notez que vous pouvez également réaliser une jointure externe par la gauche avec les jointures naturelles à l'aide de NATURAL LEFT JOIN.

Jointures sans JOIN

Cela peut paraître absurde, mais il est tout à fait possible de faire une jointure sans utiliser le mot JOIN. Ce n'est cependant possible que pour les jointures internes.
Il suffit de mentionner les tables que l'on veut joindre dans la clause FROM (séparées par des virgules), et de mettre la condition de jointure dans la clause WHERE (sans clause ON).

SELECT *
FROM table1, table2
WHERE table1.colonne1 = table2.colonne2;

-- équivalent à 

SELECT *
FROM table1
[INNER] JOIN table2
    ON table1.colonne1 = table2.colonne2;

Je vous déconseille cependant d'utiliser cette syntaxe. En effet, lorsque vous ferez des grosses jointures, avec plusieurs conditions dans la clause WHERE, vous serez bien contents de pouvoir différencier au premier coup d’œil les conditions de jointures des conditions "normales".

Exemples d'application et exercices

Maintenant que vous savez comment faire une jointure, on va un peu s'amuser. Cette partie sera en fait un mini-TP. Je vous dis à quel résultat vous devez parvenir en utilisant des jointures, vous essayez, et ensuite, vous allez voir la réponse et les explications.

Techniquement, vous avez vu toute la théorie nécessaire pour réaliser toutes les requêtes que je vous demanderai ici. Cependant, il y aura des choses que je ne vous ai pas "montrées" explicitement, comme les jointures avec plus de deux tables, ou les auto-jointures (joindre une table avec elle-même).
C'est voulu !

Je ne m'attends pas à ce que vous réussissiez à construire toutes les requêtes sans jeter un œil à la solution. Le but ici est de vous faire réfléchir, et surtout de vous faire prendre conscience qu'on peut faire pas mal de chose en SQL, en combinant plusieurs techniques par exemple.
Si un jour vous vous dites "Tiens, ce serait bien si en SQL on pouvait…", arrêtez de vous le dire et faites-le, simplement ! Vous serez probablement plus souvent limités par votre imagination que par SQL.

Bien, ça c'est dit, donc allons-y !

A/ Commençons par des choses faciles

Des jointures sur deux tables, avec différentes conditions à respecter.

1. Moi, j'aime bien les chiens de berger

Vous devez obtenir la liste des races de chiens qui sont des chiens de berger.

SELECT Race.nom AS Race 
FROM Race
INNER JOIN Espece ON Espece.id = Race.espece_id
WHERE Espece.nom_courant = 'chien' AND Race.nom LIKE '%berger%';

Bon, c'était juste un échauffement. Normalement vous ne devriez pas avoir eu de difficultés avec cette requête. Peut-être avez-vous oublié la condition Espece.nom_courant = 'chien' ? On ne sait jamais, une race de chat (ou autre) pourrait très bien contenir "berger", or j'ai explicitement demandé les chiens.

2. Mais de quelle couleur peut bien être son pelage ?

Vous devez obtenir la liste des animaux (leur nom, date de naissance et race) pour lesquels nous n'avons aucune information sur la couleur que devrait avoir leur pelage.

SELECT Animal.nom AS nom_animal, Animal.date_naissance, Race.nom AS race
FROM Animal
LEFT JOIN Race
    ON Animal.race_id = Race.id
WHERE (Race.description NOT LIKE '%poil%' 
        AND Race.description NOT LIKE '%robe%'
        AND Race.description NOT LIKE '%pelage%'
      )
      OR Race.id IS NULL;

Il faut donc :

• soit qu'on ne connaisse pas la race (on n'a alors aucune information sur le pelage de la race, a fortiori) ;

• soit qu'on connaisse la race, mais que dans la description de celle-ci il n'y ait pas d'information sur le pelage.

1/ On ne connaît pas la race

Les animaux dont on ne connaît pas la race ont NULL dans la colonne race_id. Mais vu que l'on fait une jointure sur cette colonne, il ne faut pas oublier de faire une jointure externe, sinon tous ces animaux sans race seront éliminés.
Une fois que c'est fait, pour les sélectionner, il suffit de mettre la condition Animaux.race_id IS NULL par exemple, ou simplement Race.#n'importe quelle colonne# IS NULL vu qu'il n'y a pas de correspondance avec Race.

2/ Pas d'information sur le pelage dans la description de la race

Je vous ai donné comme indice le fait que j'utilisais les mots "pelage", "poil" ou "robe" dans les descriptions des espèces. Il fallait donc sélectionner les races pour lesquelles la description ne contient pas ces mots. D'où l'utilisation de NOT LIKE.

Si on met tout ça ensemble : il fallait faire une jointure externe des tables Animal et Race, et ensuite sélectionner les animaux qui répondaient à l'une ou l'autre des conditions (opérateur logique OR).

B/ Compliquons un peu les choses

Jointures sur deux tables, ou plus !

1. La race ET l'espèce

Vous devez obtenir la liste des chats et des perroquets amazones, avec leur sexe, leur espèce (nom latin) et leur race s'ils en ont une. Regroupez les chats ensemble, les perroquets ensemble et, au sein de l'espèce, regroupez les races.

SELECT Animal.nom as nom_animal, Animal.sexe, Espece.nom_latin as espece, Race.nom as race
FROM Animal
INNER JOIN Espece
    ON Animal.espece_id = Espece.id
LEFT JOIN Race
    ON Animal.race_id = Race.id
WHERE Espece.nom_courant IN ('Perroquet amazone', 'Chat')
ORDER BY Espece.nom_latin, Race.nom;

Comme vous voyez, c'est non seulement très simple de faire des jointures sur plus d'une table, mais c'est également possible de mélanger jointures internes et externes. Si ça vous pose problème, essayez vraiment de vous imaginer les étapes.
D'abord, on fait la jointure d'Animal et d'Espece. On se retrouve alors avec une grosse table qui possède toutes les colonnes d'Animal et toutes les colonnes d'Espece. Ensuite, à cette grosse table (à la fois virtuelle et intermédiaire), on joint la table Race, grâce à la colonne Animal.race_id.
Notez que l'ordre dans lequel vous faites les jointures n'est pas important.

En ce qui concerne la clause ORDER BY, j'ai choisi de trier par ordre alphabétique, mais il est évident que vous pouviez également trier sur les id de l'espèce et de la race. L'important ici était de trier d'abord sur une colonne d'Espece, ensuite sur une colonne de Race.

2. Futures génitrices

Vous devez obtenir la liste des chiennes dont on connaît la race, et qui sont en âge de procréer (c'est-à-dire nées avant juillet 2010). Affichez leur nom, date de naissance et race.

SELECT Animal.nom AS nom_chienne, Animal.date_naissance, Race.nom AS race
FROM Animal
INNER JOIN Espece
    ON Animal.espece_id = Espece.id
INNER JOIN Race
    ON Animal.race_id = Race.id
WHERE Espece.nom_courant = 'chien'
    AND Animal.date_naissance < '2010-07-01'
    AND Animal.sexe = 'F';

Cette fois, il fallait faire une jointure interne avec Race puisqu'on voulait que la race soit connue. Le reste de la requête ne présentait pas de difficulté majeure.

C/ Et maintenant, le test ultime !

Jointures sur deux tables ou plus, avec éventuelles auto-jointures.

Je vous ai fait rajouter, à la fin du chapitre précédent, deux jolies petites colonnes dans la table Animal : mere_id et pere_id. Le moment est venu de les utiliser !

1. Mon père, ma mère, mes frères et mes sœurs (Wohooooo)

Vous devez obtenir la liste des chats dont on connaît les parents, ainsi que le nom de ces parents.

SELECT Animal.nom, Pere.nom AS Papa, Mere.nom AS Maman
FROM Animal
INNER JOIN Animal AS Pere
    ON Animal.pere_id = Pere.id
INNER JOIN Animal AS Mere
    ON Animal.mere_id = Mere.id
INNER JOIN Espece
    ON Animal.espece_id = Espece.id
WHERE Espece.nom_courant = 'chat';

Si celle-là, vous l'avez trouvée tout seuls, je vous félicite ! Sinon, c'est un peu normal. Vous voici face au premier cas dans lequel les alias sont obligatoires. En effet, vous aviez sans doute compris que vous pouviez faire FROM Animal INNER JOIN Animal, puisque j'avais mentionné les auto-jointures, mais vous avez probablement bloqué sur la clause ON. Comment différencier les colonnes d'Animal dans FROM des colonnes d'Animal dans JOIN ? Vous savez maintenant qu'il suffit d'utiliser des alias.
Il faut faire une jointure sur trois tables puisqu'au final, vous avez besoin des noms de trois animaux. Or en liant deux tables Animal ensemble, vous avez deux colonnes nom. Pour pouvoir en avoir trois, il faut lier trois tables.

Prenez le temps de bien comprendre les auto-jointures, le pourquoi du comment et le comment du pourquoi. Faites des schémas si besoin, imaginez les tables intermédiaires.

2. Je suis ton père

Histoire de se détendre un peu, vous devez maintenant obtenir la liste des enfants de Bouli (nom, sexe et date de naissance).

SELECT Animal.nom, Animal.sexe, Animal.date_naissance
FROM Animal
INNER JOIN Animal AS Pere
    ON Animal.pere_id = Pere.id
WHERE Pere.nom = 'Bouli';

Après la requête précédente, celle-ci devrait vous sembler plutôt facile ! Notez qu'il y a plusieurs manières de faire bien sûr. En voici une autre :

SELECT Enfant.nom, Enfant.sexe, Enfant.date_naissance
FROM Animal
INNER JOIN Animal AS Enfant
    ON Enfant.pere_id = Animal.id
WHERE Animal.nom = 'Bouli';

L'important, c'est le résultat ! Évidemment, si vous avez utilisé 45 jointures et 74 conditions, alors ce n'est pas bon non plus. Du moment que vous n'avez joint que deux tables, ça devrait être bon.

3. C'est un pure race ?

Courage, c'est la dernière (et la plus thrash :ninja: )!

Vous devez obtenir la liste des animaux dont on connaît le père, la mère, la race, la race du père, la race de la mère. Affichez le nom et la race de l'animal et de ses parents, ainsi que l'espèce de l'animal (pas des parents).

SELECT Espece.nom_courant AS espece, Animal.nom AS nom_animal, Race.nom AS race_animal,
    Pere.nom AS papa, Race_pere.nom AS race_papa,
    Mere.nom AS maman, Race_mere.nom AS race_maman
FROM Animal
INNER JOIN Espece
    ON Animal.espece_id = Espece.id
INNER JOIN Race
    ON Animal.race_id = Race.id
INNER JOIN Animal AS Pere
    ON Animal.pere_id = Pere.id
INNER JOIN Race AS Race_pere
    ON Pere.race_id = Race_pere.id
INNER JOIN Animal AS Mere
    ON Animal.mere_id = Mere.id
INNER JOIN Race AS Race_mere
    ON Mere.race_id = Race_mere.id;

Pfiou :euh: ! Le principe est exactement le même que pour avoir le nom des parents. Il suffit de rajouter une jointure avec Race pour le père, pour la mère, et pour l'enfant, en n'oubliant pas de bien utiliser les alias bien sûr.
C'est avec ce genre de requête que l'on se rend compte à quel point il est important de bien structurer et indenter sa requête, et à quel point un choix d'alias intelligent peut clarifier les choses.

Si vous avez survécu jusqu'ici, vous devriez maintenant avoir compris en profondeur le principe des jointures, et être capables de manipuler de nombreuses tables sans faire tout tomber par terre.

En résumé

• Une jointure est une opération qui consiste à joindre plusieurs tables ensemble, sur la base d'informations communes.

• Lorsque l'on fait une jointure interne, on ne prend que les données pour lesquelles il existe une correspondance entre la table 1 et la table 2.

• Par contre, dans une jointure externe, on récupère toutes les données d'une des deux tables (table 1), plus les données de la seconde table (table 2) pour lesquelles il existe une correspondance dans la table 1.

• On peut joindre une table à elle-même. Cela s'appelle une auto-jointure.

Sous-requêtes dans le FROM

Lorsque l'on fait une requête de type SELECT, le résultat de la requête nous est envoyé sous forme de table. Et grâce aux sous-requêtes, il est tout à fait possible d'utiliser cette table et de refaire une recherche uniquement sur les lignes de celle-ci.

Exemple : on sélectionne toutes les femelles parmi les perroquets et les tortues .

SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.espece_id
FROM Animal
INNER JOIN Espece
    ON Espece.id = Animal.espece_id
WHERE sexe = 'F' 
AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone');

Parmi ces femelles perroquets et tortues, on veut connaître la date de naissance de la plus âgée. On va donc faire une sélection dans la table des résultats de la requête.

SELECT MIN(date_naissance)
FROM (
    SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.espece_id
    FROM Animal
    INNER JOIN Espece
        ON Espece.id = Animal.espece_id
    WHERE sexe = 'F'
    AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
) AS tortues_perroquets_F;

Les règles à respecter

Parenthèses

Une sous-requête doit toujours se trouver dans des parenthèses, afin de définir clairement ses limites.

Alias

Dans le cas des sous-requêtes dans le FROM, il est également obligatoire de préciser un alias pour la table intermédiaire (le résultat de notre sous-requête). Si vous ne le faites pas, MySQL déclenchera une erreur. Ici, on l'a appelée tortues_perroquets_F.
Nommer votre table intermédiaire permet de plus de vous y référer si vous faites une jointure dessus, ou si certains noms de colonnes sont ambigus et que le nom de la table doit être précisé. Attention au fait qu'il ne s'agit pas de la table Animal, mais bien d'une table tirée d'Animal.
Par conséquent, si vous voulez préciser le nom de la table dans le SELECT principal, vous devez écrire SELECT MIN(tortues_perroquets_F.date_naissance), et non pas SELECT MIN(Animal.date_naissance).

Cohérence des colonnes

Les colonnes sélectionnées dans le SELECT "principal" doivent bien sûr être présentes dans la table intermédiaire. La requête suivante, par exemple, ne fonctionnera pas :

SELECT MIN(date_naissance)
FROM (
    SELECT Animal.id, Animal.nom
    FROM Animal
    INNER JOIN Espece
        ON Espece.id = Animal.espece_id
    WHERE sexe = 'F'
    AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
) AS tortues_perroquets_F;

En effet, tortues_perroquets_F n'a que deux colonnes : id et nom. Il est donc impossible de sélectionner la colonne date_naissance de cette table.

Noms ambigus

Pour finir, attention aux noms de colonnes ambigus. Une table, même intermédiaire, ne peut pas avoir deux colonnes ayant le même nom. Si deux colonnes ont le même nom, il est nécessaire de renommer explicitement au moins l'une des deux.

Donc, si on veut sélectionner la colonne Espece.id en plus dans la sous-requête, on peut procéder ainsi :

SELECT MIN(date_naissance)
FROM (
    SELECT Animal.id, Animal.sexe, Animal.date_naissance, Animal.nom, Animal.espece_id, 
            Espece.id AS espece_espece_id         -- On renomme la colonne id de Espece, donc il n'y a plus de doublons.
    FROM Animal                                   -- Attention de ne pas la renommer espece_id, puisqu'on sélectionne aussi la colonne espece_id dans Animal !
    INNER JOIN Espece
        ON Espece.id = Animal.espece_id
    WHERE sexe = 'F'
    AND Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
) AS tortues_perroquets_F;

Sous-requêtes dans les conditions

Je vous ai donc dit que lorsque vous faites une requête SELECT, le résultat est sous forme de table. Ces tables de résultats peuvent avoir :

• plusieurs colonnes et plusieurs lignes ;

• plusieurs colonnes mais une seule ligne ;

• plusieurs lignes mais une seule colonne ;

• ou encore une seule ligne et une seule colonne (c'est-à-dire juste une valeur).

Les sous-requêtes renvoyant plusieurs lignes et plusieurs colonnes ne sont utilisées que dans les clauses FROM. Nous allons ici nous intéresser aux trois autres possibilités uniquement.

Comparaisons

Pour rappel, voici un tableau des opérateurs de comparaison.

On peut utiliser des comparaisons de ce type avec des sous-requêtes qui donnent comme résultat soit une valeur (c'est-à-dire une seule ligne et une seule colonne), soit une ligne (plusieurs colonnes mais une seule ligne).

Sous-requête renvoyant une valeur

Le cas le plus simple est évidemment d'utiliser une sous-requête qui renvoie une valeur.

SELECT id, sexe, nom, commentaires, espece_id, race_id
FROM Animal
WHERE race_id = 
    (SELECT id FROM Race WHERE nom = 'Berger Allemand');  -- la sous-requête renvoie simplement 1

Remarquez que cette requête peut également s'écrire avec une jointure plutôt qu'une sous-requête :

SELECT Animal.id, sexe, Animal.nom, commentaires, Animal.espece_id, race_id
FROM Animal
INNER JOIN Race ON Race.id = Animal.race_id
WHERE Race.nom = 'Berger Allemand';

Voici un exemple de requête avec sous-requête qu'il est impossible de faire avec une simple jointure :

SELECT id, nom, espece_id
FROM Race
WHERE espece_id = (
    SELECT MIN(id)     -- Je rappelle que MIN() permet de récupérer la plus petite valeur de la colonne parmi les lignes sélectionnées
    FROM Espece);

En ce qui concerne les autres opérateurs de comparaison, le principe est exactement le même :

SELECT id, nom, espece_id
FROM Race
WHERE espece_id < (
    SELECT id    
    FROM Espece
    WHERE nom_courant = 'Tortue d''Hermann');

Ici la sous-requête renvoie 3, donc nous avons bien les races dont l'espèce a un id inférieur à 3 (donc 1 et 2 :p ).

Sous-requête renvoyant une ligne

Dans le cas d'une sous-requête dont le résultat est une ligne, la syntaxe est la suivante :

SELECT *
FROM nom_table1
WHERE [ROW](colonne1, colonne2) = (    -- le ROW n'est pas obligatoire
    SELECT colonneX, colonneY
    FROM nom_table2
    WHERE...);                         -- Condition qui ne retourne qu'UNE SEULE LIGNE

Cette requête va donc renvoyer toutes les lignes de la table1 dont la colonne1 = la colonneX de la ligne résultat de la sous-requête ET la colonne2 = la colonneY de la ligne résultat de la sous-requête.

Vous voulez un exemple peut-être ? Allons-y !

SELECT id, sexe, nom, espece_id, race_id 
FROM Animal
WHERE (id, race_id) = (
    SELECT id, espece_id
    FROM Race
    WHERE id = 7);

Décomposons calmement. Voyons d'abord ce que la sous-requête donne comme résultat.

SELECT id, espece_id
FROM Race
WHERE id = 7;

Et comme condition, on a WHERE (id, race_id) = #le résultat de la sous-requête#.
Donc la requête renverra les lignes de la table Animal pour lesquelles id vaut 7 et race_id vaut 2.

Conditions avec IN et NOT IN

IN

Vous connaissez déjà l'opérateur IN, qui compare une colonne avec une liste de valeurs.

Exemple

SELECT Animal.id, Animal.nom, Animal.espece_id
FROM Animal
INNER JOIN Espece
    ON Espece.id = Animal.espece_id
WHERE Espece.nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone');

Cet opérateur peut également s'utiliser avec une sous-requête dont le résultat est une colonne ou une valeur. On peut donc réécrire la requête ci-dessus en utilisant une sous-requête plutôt qu'une jointure :

SELECT id, nom, espece_id
FROM Animal
WHERE espece_id IN (
    SELECT id 
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

Le fonctionnement est plutôt facile à comprendre. La sous-requête donne les résultats suivants :

SELECT id            -- On ne sélectionne bien qu'UNE SEULE COLONNE.
FROM Espece
WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone');

Ainsi, la requête principale sélectionnera les lignes qui ont un espece_id parmi ceux renvoyés par la sous-requête, donc 3 ou 4.

NOT IN

Si l'on utilise NOT IN, c'est bien sûr le contraire, on exclut les lignes qui correspondent au résultat de la sous-requête. La requête suivante nous renverra donc les animaux dont l'espece_id n'est pas 3 ou 4.

SELECT id, nom, espece_id
FROM Animal
WHERE espece_id NOT IN (
    SELECT id 
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

Conditions avec ANY, SOME et ALL

Les conditions avec IN et NOT IN sont un peu limitées, puisqu'elles ne permettent que des comparaisons de type "est égal" ou "est différent". Avec ANY et ALL, on va pouvoir utiliser les autres comparateurs (plus grand, plus petit, etc.).

• ANY : veut dire "au moins une des valeurs".

• SOME : est un synonyme de ANY.

• ALL : signifie "toutes les valeurs".

ANY (ou SOME)

La requête suivante signifie donc "Sélectionne les lignes de la table Animal, dont l'espece_id est inférieur à au moins une des valeurs sélectionnées dans la sous-requête". C'est-à-dire inférieur à 3 ou à 4. Vous aurez donc dans les résultats toutes les lignes dont l'espece_id vaut 1, 2 ou 3 (puisque 3 est inférieur à 4).

SELECT *
FROM Animal
WHERE espece_id < ANY (
    SELECT id
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

ALL

Par contre, si vous utilisez ALL plutôt que ANY, cela signifiera "Sélectionne les lignes de la table Animal, dont l'espece_id est inférieur à toutes les valeurs sélectionnées dans la sous-requête". Donc inférieur à 3 et à 4. Vous n'aurez donc plus que les lignes dont l'espece_id vaut 1 ou 2.

SELECT *
FROM Animal
WHERE espece_id < ALL (
    SELECT id
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

Remarque : lien avec IN

Remarquez que = ANY est l'équivalent de IN, tandis que <> ALL est l'équivalent de NOT IN. Attention cependant que ANY et ALL (et SOME) ne peuvent s'utiliser qu'avec des sous-requêtes, et non avec des valeurs comme on peut le faire avec IN.
On ne peut donc pas faire ceci :

SELECT id
FROM Espece
WHERE nom_courant = ANY ('Tortue d''Hermann', 'Perroquet amazone');

#1064 - You have an error in your SQL syntax;