Chapitre 3 Tutoriels d'introduction

Table des matières

3.1 Connexion et déconnexion au serveur
3.2 Entrer des requêtes
3.3 Création et utilisation d'une base de données
3.3.1 Créer et sélectionner une base de données
3.3.2 Création d'une table
3.3.3 Charger des données dans une table
3.3.4 Récupérer des informations à partir d'une table
3.4 Obtenir des informations à propos des bases de données et des tables
3.5 Utilisation de mysql en mode batch
3.6 Exemples de requêtes usuelles
3.6.1 La valeur maximale d'une colonne
3.6.2 La ligne contenant le maximum d'une certaine colonne
3.6.3 Maximum d'une colonne par groupe
3.6.4 La ligne contenant la plus grande valeur d'un certain champ par rapport à un groupe
3.6.5 Utiliser les variables utilisateur
3.6.6 Utiliser les clefs étrangères
3.6.7 Recherche sur deux clefs
3.6.8 Calcul du nombre de visites par jour
3.6.9 Utiliser AUTO_INCREMENT
3.7 Requêtes du projet Twin
3.7.1 Trouver tous les jumeaux répondant aux critères
3.7.2 Afficher une table avec l'état des paires de jumeaux
3.8 Utilisation de MySQL avec Apache

Ce chapitre fournit un tutoriel d'introduction à MySQL en montrant comment utiliser le client mysql pour créer et utiliser une simple base de données. mysql (quelques fois nommé ``moniteur terminal'' ou juste ``moniteur'') est un programme interactif qui vous permet de vous connecter à un serveur MySQL, exécuter des requêtes et voir les résultats. mysql peut aussi être utilisé en mode batch : vous placez vos requêtes dans un fichier, puis vous faites exécuter à mysql le contenu de ce fichier. Les deux manières d'utiliser mysql sont expliquées ici.

Pour voir une liste d'options fournies par mysql, invoquez-le avec l'option --help :

shell> mysql --help

Ce chapitre assume que mysql est installé sur votre machine et qu'un serveur MySQL est disponible pour que vous vous y connectiez. Si ce n'est pas le cas, contactez votre administrateur MySQL. (Si vous êtes l'administrateur, vous aurez besoin de consulter d'autres sections de ce manuel.)

Ce chapitre décrit le processus d'installation et d'utilisation d'une base de données en entier. Si vous n'êtes intéressés que par l'accès à une base de données existante, vous pouvez sauter les sections décrivant la création de la base et des tables.

Ce chapitre n'est qu'un tutoriel, beaucoup de détails ne sont pas approfondis. Consultez les sections appropriées du manuel pour plus d'informations sur les sujets abordés.

3.1 Connexion et déconnexion au serveur

Pour vous connecter au serveur, vous aurez dans la plupart des cas à fournir un nom d'utilisateur à MySQL, et, sûrement, un mot de passe. Si le serveur fonctionne sur une autre machine que la vôtre, vous devrez spécifier son adresse. Contactez votre administrateur pour connaître les paramètres à utiliser lors de la connexion (hôte, nom d'utilisateur, mot de passe à utiliser...). Une fois que vous aurez les bons paramètres, vous pourrez vous connecter de la fa¸on suivante :

shell> mysql -h hote -u utilisateur -p
Enter password: ********

******** représente votre mot de passe, entrez-le lorsque mysql affiche Enter password:.

Si tout fonctionne, vous devrez voir quelques informations d'introduction suivies d'une invite de commande mysql> :

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 459 to server version: 3.22.20a-log

Type 'help' for help.

mysql>

L'invite vous dit que mysql attend que vous entriez des commandes.

Quelques installations de MySQL autorisent les connexions anonymes au serveur tournant sur l'hôte local. Si c'est la cas sur votre machine, vous devriez arriver à vous connecter à ce serveur en invoquant la commande mysql sans aucune option :

shell> mysql

Après vous être connecté avec succès, vous pouvez vous déconnecter à tout moment en entrant QUIT dans l'invite mysql> :

mysql> QUIT
Bye

Vous pouvez aussi le faire en appuyant sur Ctrl-D.

La plupart des exemples dans les sections suivantes supposent que vous êtes connecté au serveur. Cela se voit à l'invite mysql>.

3.2 Entrer des requêtes

Assurez-vous d'être connecté au serveur, comme expliqué précédemment dans cette section. Faire ceci ne sélectionnera pas une base par lui même, mais c'est normal. A ce stade, il est important de découvrir la fa¸on dont sont publiées les requêtes, pour ensuite pouvoir créer des tables, y insérer et rechercher des données. Cette section décrit les principes de base pour entrer une commande, en utilisant plusieurs requêtes que vous pouvez essayer pour vous familiariser avec la fa¸on dont mysql fonctionne.

Voilà une commande simple qui demande au serveur de vous donner son numéro de version et la date courante. Entrez-la comme suit, juste après l'invite mysql> puis pressez Enter :

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| VERSION()    | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

La requête révèle plusieurs choses à propos de mysql :

  • Une commande consiste normalement en une commande SQL suivie d'un point-virgule. (Il y a quelques cas ou le point-virgule n'est pas requis. QUIT, mentionnée plus tôt, en fait partie. Nous verrons les autres plus tard.)

  • Lorsque vous entrez une commande, mysql l'envoi au serveur pour l'exécution et affiche le résultat, puis affiche un autre mysql> pour indiquer qu'il attend une autre commande.

  • mysql affiche le résultat des requêtes dans une table (lignes et colonnes). La première ligne contient le nom des colonnes. Les lignes suivantes constituent le résultat de la requête. Normalement, les titres des colonnes sont les noms des champs des tables de la base de données que vous avez récupérés. Si vous récupérez la valeur d'une expression au lieu d'une colonne (comme dans l'exemple précédent), mysql nomme la colonne en utilisant l'expression elle-même.

  • mysql vous indique combien de lignes ont été retournées et combien de temps d'exécution la requête a pris, ce qui vous donnera une approximation des performances du serveur. Ces valeurs sont imprécises car elles représentent le temps logiciel (et non le temps processeur ou matériel), et qu'elles sont affectées par des facteurs tels que la charge du serveur ou l'accessibilité du réseau. (Dans un soucis de brièveté, la ligne contenant ``rows in set'' n'est plus montrée dans les exemples suivants de ce chapitre.)

Les mots-clef peuvent être entrés sous n'importe quelle forme de casse. Les requêtes suivantes sont équivalentes :

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

Voilà une autre requête. Elle montre que vous pouvez utiliser mysql en tant que simple calculatrice :

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
|    0.707107 |      25 |
+-------------+---------+

Les commandes vues jusqu'à présent ont été relativement courtes, et tenaient sur une seule ligne. Vous pouvez même entrer plusieurs requêtes sur une seule ligne. Il suffit de terminer chacune d'elle par un point-virgule :

mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| VERSION()    |
+--------------+
| 3.22.20a-log |
+--------------+

+---------------------+
| NOW()               |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+

Une commande ne doit pas être obligatoirement sur une seule ligne ; les commandes qui exigent plusieurs lignes ne sont pas un problème. mysql détermine où se situe la fin de votre commande en recherchant le point-virgule de terminaison, et pas l'extrémité de la commande entrée. (Dans d'autres termes, mysql accepte des formats libres d'entrée : il collecte les lignes entrées mais ne les exécute qu'une fois le point-virgule trouvé.)

Voilà une seule requête sur plusieurs lignes :

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+--------------------+--------------+
| USER()             | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18   |
+--------------------+--------------+

Dans cet exemple, notez comment l'invite change de mysql> à -> après avoir entré la première ligne d'une requête multi-lignes. C'est la fa¸on dont mysql indique qu'il n'a pas vu de requête complète et qu'il attend la fin de celle-ci. L'invite est votre ami en vous fournissant la rétroactivité. Si vous utilisez cette rétroactivité, vous vous rendrez toujours compte de ce que mysql attend.

Si vous décidez d'annuler une commande que vous êtes en train de taper, faites-le en entrant \c :

mysql> SELECT
    -> USER()
    -> \c
mysql>

Ici aussi, portez votre attention sur l'invite. Elle se transforme à nouveau en mysql> après que vous ayez entré \c, vous informant que mysql est prêt pour une nouvelle requête.

Le tableau suivant montre les différentes invites que vous pourrez voir et résume leur signification quand à l'état dans lequel se trouve mysql :

InviteSignification
mysql>Prêt pour une nouvelle commande.
    ->En attente de la ou des lignes terminant la commande.
    '>En attente de la prochaine ligne collectant une chaîne commencée par un guillemet simple (‘'’).
    ">En attente de la prochaine ligne collectant une chaîne commencée par un guillemet double (‘"’).
    `>En attente de la prochaine ligne collectant une chaîne commencée par un guillemet oblique (‘`’).

Les commandes sur plusieurs lignes sont la plupart du temps des accidents, lorsque vous voulez faire une commande sur une seule ligne et que vous oubliez le point-virgule de fin. Dans ce cas, mysql attend la suite de votre saisie :

mysql> SELECT USER()
    ->

Si cela vous arrive (vous pensez que votre requête est complète mais la seule réponse est l'invite ->), il est fort probable que mysql attende le point-virgule. Si vous ne notez pas ce que l'invite vous indique, vous pourriez patienter pendant longtemps avant de réaliser ce que vous devez faire. Entrez un point-virgule pour compléter la requête, et mysql devrait l'exécuter :

mysql> SELECT USER()
    -> ;
+--------------------+
| USER()             |
+--------------------+
| joesmith@localhost |
+--------------------+

L'invite '> ainsi que "> apparaissent durant l'entrée de chaîne. Dans MySQL, vous pouvez écrire une chaîne entourée du caractère ‘'’ ou bien ‘"’ (par exemple, 'Bonjour' or "Au Revoir"), et mysql vous laisse entrer une chaîne qui peut être sur plusieurs lignes. Lorsque vous voyez une invite comme '> ou ">, cela signifie que vous avez entré une ligne contenant le caractère ‘'’ ou ‘"’, mais vous n'avez pas encore entré le caractère correspondant qui termine votre chaîne. C'est pratique si vous entrez réellement une chaîne à lignes multiples, mais est-ce probable ? Pas vraiment. Plus souvent, les invites '> et "> indiquent que vous avez, par inadvertance, oublié un caractère de fermeture. Par exemple :

mysql> SELECT * FROM ma_table WHERE nom = "Smith AND age < 30;
    ">

Si vous entrez cette requête SELECT, puis appuyez sur Enter et attendez le résultat, rien ne se passera. Au lieu de vous demander pourquoi la requête met si longtemps à s'exécuter, remarquez que l'invite de commande s'est transformée en ">. Cela indique que mysql attend de voir la fin d'une chaîne de caractères non-terminée. (Voyez-vous l'erreur dans cette requête ? Il manque le second guillemet à la suite de "Smith.)

Que faire ? Le plus simple est d'annuler la commande. Toutefois, vous ne pouvez vous contenter de taper \c dans ce cas-là, car mysql l'interprète comme une partie de la chaîne qu'il est en train de collecter ! A la place, entrez le second guillemet (pour que mysql sache que vous avez fini la chaîne), puis entrez \c :

mysql> SELECT * FROM my_table WHERE name = "Smith AND age < 30;
    "> "\c
mysql>

L'invite se change à nouveau en mysql>, indiquant que mysql est prêt pour une nouvelle requête.

Il est important de savoir ce que les invites '> et "> signifient, car si vous avez entré par erreur une chaîne non terminée, toutes les lignes suivantes que vous entrerez seront ignorées par mysql, même une ligne contenant QUIT ! Cela peut prêter à confusion, spécialement si vous ne savez pas que vous devez fournir le guillemet fermant avant de pouvoir annuler la commande courante.

3.3 Création et utilisation d'une base de données

Maintenant que vous savez entrer des commandes, il est temps d'accéder à une base.

Supposons que vous avec plusieurs animaux chez vous (dans votre ménagerie) et que vous voulez garder diverses informations les concernant. Vous pouvez le faire en créant des tables pour stocker vos données et y charger vos informations. Vous pourrez alors répondre à différentes sortes de questions à propos de vos animaux en récupérant les données à partir des tables. Cette section vous montre comment :

  • Créer une base de données

  • Créer une table

  • Charger des données dans vos tables

  • Récupérer des données à partir des tables de différentes fa¸ons

  • Utiliser plusieurs tables

La base de données de la ménagerie va être simple (délibérément), mais il n'est pas difficile de penser à des situations courantes de la vie où vous aurez à utiliser un tel type de base de données. Par exemple, une telle base pourrait être utilisée par un éleveur pour gérer sa boutique, ou par un vétérinaire pour garder des traces de ses patients. Une distribution de la ménagerie contenant quelques requêtes et des exemples de données utilisées dans la section suivante peuvent être trouvés sur le site web de MySQL. Ils sont disponibles au format compressé tar (http://downloads.mysql.com/docs/menagerie-db.tar.gz) ou au format Zip (http://downloads.mysql.com/docs/menagerie-db.zip).

Utilisez la commande SHOW pour trouver quelles bases existent déjà sur le serveur :

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

La liste des bases de données est probablement différente sur votre machine, mais les bases mysql et test y figurent sûrement. La base mysql est requise car elle gère les accès et les privilèges. La base test est souvent fournie pour que les utilisateurs y effectuent leurs tests.

Notez que vous ne pourrez voir toutes les bases de données si vous n'avez pas le privilège SHOW DATABASES. See Section 13.5.1.3, « Syntaxe de GRANT et REVOKE ».

Si la base de données test existe, essayez d'y accéder :

mysql> USE test
Database changed

Notez que USE, comme QUIT, ne requiert pas de point-virgule. (Vous pouvez terminer ces commandes avec un point-virgule ; cela ne posera pas de problèmes.) La commande USE est spéciale d'un autre point de vue : elle doit être donnée sur une seule ligne.

Vous pouvez utiliser la base de données test (si vous y avez accès) pour les exemples qui suivent, mais tout ce que vous créerez dans cette base pourra être effacé par quiconque y a accès. Pour cette raison, vous feriez mieux de demander à votre administrateur MySQL la permission d'utiliser une base de données rien que pour vous. Supposez que vous voulez nommer la votre menagerie. L'administrateur a besoin d'exécuter une commande telle que :

mysql> GRANT ALL ON menagerie.* TO votre_nom_mysql;

votre_nom_mysql est le nom d'utilisateur MySQL qui vous est assigné.

3.3.1 Créer et sélectionner une base de données

Si l'administrateur vous a créé une base de données lors du paramétrage de vos droits, vous pouvez commencer à l'utiliser. Sinon, vous aurez besoin de la créer par vous-même :

mysql> CREATE DATABASE menagerie;

Sous Unix, les noms des bases de données sont sensibles à la casse (ce qui diffère des mots réservés de SQL), ce qui fait que vous devez toujours vous référer à votre base de données avec menagerie, non avec Menagerie, MENAGERIE, ou d'autres variantes. Cela est aussi valable pour les noms de tables. (Sous Windows, cette restriction n'est pas appliquée, même si vous devez vous référer à une table ou une base de la même fa¸on dans une même requête).

La création d'une base de données ne la sélectionne pas pour l'utilisation ; vous devez le faire explicitement. Pour rendre menagerie la base courante, utilisez cette commande :

mysql> USE menagerie
Database changed

Votre base a besoin d'être créée juste une fois, mais vous devez la sélectionner pour l'utiliser, chaque fois que vous débutez une session mysql. Vous pouvez le faire en publiant une requête USE comme ci-dessus. Sinon, vous pouvez sélectionner la base directement dans la ligne de commande lorsque vous invoquez mysql. Vous devez juste spécifier son nom après les paramètres de connexion dont vous avez besoin. Par exemple :

shell> mysql -h hote -u utilisateur -p menagerie
Enter password: ********

Notez que menagerie n'est pas votre mot de passe dans la commande que nous venons de montrer. Si vous voulez le fournir dans la ligne de commande après l'option -p, vous devez le faire sans espace entre les deux (par exemple, tapez -pmonmotdepasse, et non -p monmotdepasse). Toutefois, mettre le mot de passe en ligne de commande n'est pas recommandé, car le faire permettrait à d'autres utilisateurs connectés sur votre machine de l'obtenir.

3.3.2 Création d'une table

Créer la base de données est la partie facile, mais jusque-là elle est vide, comme vous le montre SHOW TABLES :

mysql> SHOW TABLES;
Empty set (0.00 sec)

La partie la plus difficile est le choix de la structure de la base de données : de quelles tables aurez vous besoin et quelles colonnes devront figurer dans chacune d'elles.

Vous voudrez une table qui contient un enregistrement pour chaque animal. On peut l'appeler la table animal, et elle devra contenir, au minimum, le nom de chaque animal. Puisque le nom tout seul n'est pas intéressant, la table devra contenir d'autres informations. Par exemple, si plus d'une personne de votre famille possède un animal, vous voudrez lister le nom du maître de chaque animal. Vous voudrez peut-être aussi enregistrer une description basique comme l'espèce ou le sexe.

Et pour l'âge ? C'est intéressant, mais n'est pas bon pour un stockage en base de données. L'âge change chaque jour, vous devrez donc mettre à jour vos enregistrements assez souvent. Il est préférable de stocker une valeur fixe, comme la date de naissance. Dans ce cas-là, à chaque fois que vous aurez besoin de l'âge, vous pourrez l'obtenir en faisant la différence entre la date courante et la date enregistrée. MySQL fournit des fonctions de calcul sur les dates, cela ne sera donc pas difficile. Enregistrer la date de naissance, au lieu de l'âge a d'autres avantages :

  • Vous pouvez utiliser la base de données pour des tâches, comme la génération d'un rappel pour les prochains anniversaires d'animaux. (Si vous trouvez que ce type de requêtes est quelque peu idiot, notez que c'est la même question que vous vous poseriez dans le contexte d'une base de données d'affaires pour identifier les clients à qui vous aurez besoin d'envoyer un message de voeux, pour cette touche informatiquement assistée d'humanisme.)

  • Vous pouvez calculer l'âge à partir d'autres dates que la date du jour. Par exemple, si vous stockez la date de la mort dans la base de données, vous pourrez facilement calculer l'âge qu'avait un animal à sa mort.

Vous trouverez probablement d'autres informations qui pourront être utiles dans la table animal, mais celles identifiés jusqu'à maintenant sont largement suffisantes pour l'instant : nom, maître, espèce, sexe, naissance, et mort.

Utilisez une requête CREATE TABLE pour spécifier la structure de votre table :

mysql> CREATE TABLE animal (nom VARCHAR(20), maitre VARCHAR(20),
    -> espece VARCHAR(20), sexe CHAR(1), naissance DATE, mort DATE);

VARCHAR est un bon choix pour les colonnes nom, maitre, et espece car leurs valeurs varient en longueur. La longueur de ces colonnes ne doit pas nécessairement être la même, et n'a pas besoin d'être forcement 20. Vous pouvez choisir une taille entre 1 et 255, celle qui vous semblera la plus raisonnable. (Si vous faites un mauvais choix et que vous vous apercevez plus tard que vous avez besoin d'un champ plus long, MySQL fournit la commande ALTER TABLE.)

Le sexe des animaux peut être représenté de plusieurs fa¸ons, par exemple, "m" et "f", ou bien "male" et "femelle". Il est plus simple d'utiliser les caractères simples "m" et "f".

L'utilisation du type de données DATE pour les colonnes naissance et mort est un choix plutôt judicieux.

Maintenant que vous avez créé une table, SHOW TABLES devrait produire de l'affichage :

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| animal              |
+---------------------+

Pour vérifier que la table a été créée de la fa¸on que vous vouliez , utilisez la commande DESCRIBE :

mysql> DESCRIBE animal;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| nom       | varchar(20) | YES  |     | NULL    |       |
| maitre    | varchar(20) | YES  |     | NULL    |       |
| espece    | varchar(20) | YES  |     | NULL    |       |
| sexe      | char(1)     | YES  |     | NULL    |       |
| naissance | date        | YES  |     | NULL    |       |
| mort      | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Vous pouvez utiliser DESCRIBE quand vous voulez, par exemple, si vous avez oublié les noms des colonnes dans votre table ou leurs types.

3.3.3 Charger des données dans une table

Après la création de votre table, vous aurez besoin de la remplir. Les commandes LOAD DATA et INSERT sont utiles pour cela.

Supposons que les enregistrements de vos animaux peuvent être décrits comme suit. (Observez que MySQL attend les dates au format YYYY-MM-DD; cela peut différer de ce à quoi vous êtes habitué.)

nommaîtreracessexenaissancemort
FluffyHaroldchatf1993-02-04 
ClawsGwenchatm1994-03-17 
BuffyHaroldchienf1989-05-13 
FangBennychienm1990-08-27 
BowserDianechienm1998-08-311995-07-29
ChirpyGwenoiseauf1998-09-11 
WhistlerGwenoiseau 1997-12-09 
SlimBennyserpentm1996-04-29 

Puisque vous commencez avec une table vide, il est facile de la remplir en créant un fichier texte contenant une ligne pour chaque animal que vous avez, puis charger son contenu à l'aide d'une seule commande.

Vous pouvez créer un fichier animal.txt contenant un enregistrement par ligne, avec les valeurs séparés par des tabulations, et ordonnées comme les champs l'étaient dans la requête CREATE TABLE. Pour les données manquantes (comme un sexe inconnu ou la date de mort d'un animal toujours en vie), vous pouvez utiliser les valeurs NULL. Pour les représenter dans votre fichier texte, utilisez \N. Par exemple, l'enregistrement de Whistler l'oiseau ressemblera à ¸a (l'espace entre les valeurs est une tabulation) :

nommaîtreracesexenaissancemort
WhistlerGwenbird\N1997-12-09\N

Pour charger le fichier animal.txt dans la table animal, utilisez cette commande :

mysql> LOAD DATA LOCAL INFILE "animal.txt" INTO TABLE animal;

Notez que si vous créez un fichier sur Windows, avec un éditeur qui utilise des caractères de lignes comme \r\n, vous devez utiliser :

mysql> LOAD DATA LOCAL INFILE "animal.txt" INTO TABLE animal;

Vous pouvez spécifier la valeur du séparateur de colonnes et le marqueur de fin de lignes explicitement dans la commande LOAD DATA si vous le voulez, mais les valeurs par défaut sont la tabulation et le retour à la ligne. Ceux-là sont suffisants pour que la commande lise le fichier animal.txt correctement.

Si la commande échoue, il est probable que votre installation MySQL n'a pas la possibilité d'accéder aux fichiers. Voyez Section 5.4.4, « Problèmes de sécurité avec LOAD DATA LOCAL » pour plus d'informations sur comment modifier cela.

Lorsque vous voulez ajouter des enregistrements un par un, la commande INSERT est utile. Dans sa forme la plus simple, où vous spécifiez une valeur pour chaque colonne, dans l'ordre où les colonnes sont listées dans la requête CREATE TABLE. Supposons que Diane achète un nouvel hamster nommé Puffball. Vous pourriez ajouter ce nouvel enregistrement en utilisant un INSERT de la fa¸on suivante :

mysql> INSERT INTO animal
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Notez que les chaînes de caractères et les valeurs de dates sont spécifiées en tant que chaînes protégées par des guillemets. De plus, avec INSERT vous pouvez insérer la valeur NULL directement pour représenter une valeur manquante. Vous n'utilisez pas \N comme vous le faites avec LOAD DATA.

A partir de cet exemple, vous devriez être capable de voir qu'il y a beaucoup plus de commandes à taper lorsque vous utilisez la commande INSERT au lieu de LOAD DATA.

3.3.4 Récupérer des informations à partir d'une table

La commande SELECT est utilisée pour récupérer des informations à partir d'une table. La forme usuelle est :

SELECT quoi_selectionner
FROM quel_table
WHERE conditions_a_satisfaire

quoi_selectionner indique ce que vous voulez voir. Cela peut être une liste de colonnes, ou * pour indiquer ``toutes les colonnes''. quel_table indique la table à partir de laquelle récupérer les données. La clause WHERE est optionnelle. Si elle est présente, conditions_a_satisfaire spécifie les conditions que les lignes doivent satisfaire pour être séléctionnées.

3.3.4.1 Sélectionner toutes les données

La plus simple forme de SELECT récupère toutes les données d'une table :

mysql> SELECT * FROM animal;
+----------+--------+---------+------+------------+------------+
| nom      | maitre | espece  | sexe | naissance  | mort       |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | chat    | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | chat    | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | chien   | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | chien   | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | chien   | m    | 1998-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | oiseau  | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | oiseau  | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | serpent | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

Cette forme de SELECT est utile si vous voulez récupérer la table entière. Par exemple, après l'avoir juste remplie avec vos données d'origine. Il apparaît alors qu'une erreur s'était glissée dans votre fichier de données : Bowser a l'air d'être né après sa mort ! En consultant le papier original de son pedigree, vous trouvez que la date correcte est 1989 et non pas 1998.

Il y a au moins deux fa¸ons de corriger cela :

  • Corriger le fichier animal.txt pour corriger l'erreur, puis vider et recharger à nouveau la table en utilisant DELETE et LOAD DATA :

    mysql> SET AUTOCOMMIT=1;  # Utilisé pour une recréation rapide de la table
    mysql> DELETE FROM animal;
    mysql> LOAD DATA LOCAL INFILE "animal.txt" INTO TABLE animal;
    

    Toutefois, si vous choisissez cette méthode, vous devrez aussi rentrer à nouveau l'enregistrement de Puffball.

  • Corriger uniquement l'enregistrement erroné avec une requête UPDATE :

    mysql> UPDATE animal SET naissance = "1989-08-31" WHERE nom = "Bowser";
    

Comme nous l'avons montré, il est facile de récupérer toutes les données d'une table. Toutefois, vous ne voudrez sûrement pas le faire, surtout si la table devient imposante. A la place, vous serez plus intéressé par répondre à une question particulière, dans ce cas-là, vous spécifiez quelques contraintes pour les informations que vous voulez. Regardons quelques requêtes de sélection qui répondent à des questions à propos de vos animaux.

3.3.4.2 Sélectionner des lignes particulières

Vous pouvez sélectionner des lignes particulières de votre table. Par exemple, si vous voulez vérifier la modification que vous avez effectuée sur la date de naissance de Bowser, sélectionnez son enregistrement comme suit :

mysql> SELECT * FROM animal WHERE nom = "Bowser";
+--------+--------+--------+------+------------+------------+
| nom    | maitre | espece | sexe | naissance  | mort       |
+--------+--------+--------+------+------------+------------+
| Bowser | Diane  | chien  | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+--------+------+------------+------------+

L'affichage confirme que la date est correcte maintenant : 1989, et non 1998.

La comparaison des chaînes de caractères se fait normalement avec sensibilité à la casse, vous pouvez donc spécifier le nom "bowser", "BOWSER", etc. Le résultat de la requête sera le même.

Vous pouvez spécifier des conditions sur toutes les colonnes, pas seulement nom. Par exemple, si vous voulez savoir quels animaux sont nés après 1998, testez la colonne naissance :

mysql> SELECT * FROM animal WHERE naissance >= "1998-1-1";
+----------+--------+---------+------+------------+-------+
| nom      | maitre | espece  | sexe | naissance  | mort  |
+----------+--------+---------+------+------------+-------+
| Chirpy   | Gwen   | oiseau  | f    | 1998-09-11 | NULL  |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL  |
+----------+--------+---------+------+------------+-------+

Vous pouvez combiner plusieurs conditions, par exemple, pour trouver les chiennes :

mysql> SELECT * FROM animal WHERE espece = "chien" AND sexe = "f";
+-------+--------+--------+------+------------+-------+
| nom   | maitre | espece | sexe | naissance  | mort  |
+-------+--------+--------+------+------------+-------+
| Buffy | Harold | chien  | f    | 1989-05-13 | NULL  |
+-------+--------+--------+------+------------+-------+

La requête précédente utilise l'opérateur logique AND. L'opérateur OR existe aussi :

mysql> SELECT * FROM animal WHERE espece = "serpent" OR espece = "oiseau";
+----------+--------+---------+------+------------+-------+
| nom      | maitre | espece  | sexe | naissance  | mort  |
+----------+--------+---------+------+------------+-------+
| Chirpy   | Gwen   | oiseau  | f    | 1998-09-11 | NULL  |
| Whistler | Gwen   | oiseau  | NULL | 1997-12-09 | NULL  |
| Slim     | Benny  | serpent | m    | 1996-04-29 | NULL  |
+----------+--------+---------+------+------------+-------+

AND et OR peuvent être utilisés ensemble. Si vous le faites, une bonne idée est d'utiliser les parenthèses pour indiquer comment les conditions doivent être regroupées :

mysql> SELECT * FROM animal WHERE (espece = "chat" AND sexe = "m")
    -> OR (espece = "chien" AND sexe = "f");
+-------+--------+--------+------+------------+-------+
| nom   | maitre | espece | sexe | naissance  | mort  |
+-------+--------+--------+------+------------+-------+
| Claws | Gwen   | chat   | m    | 1994-03-17 | NULL  |
| Buffy | Harold | chien  | f    | 1989-05-13 | NULL  |
+-------+--------+--------+------+------------+-------+

3.3.4.3 Sélectionner des colonnes particulières

Si vous ne voulez pas voir les lignes entières de votre table, nommez les colonnes qui vous intéressent, en les séparant par des virgules. Par exemple, si vous voulez savoir quand vos animaux sont nés, sélectionnez les colonnes nom et naissance :

mysql> SELECT nom, naissance FROM animal;
+----------+------------+
| nom      | naissance  |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

Pour trouver qui possède les animaux, utilisez cette requête :

mysql> SELECT maitre FROM animal;
+--------+
| maitre |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

Toutefois, remarquez que la requête récupère le champ maitre de chaque enregistrement, et certains apparaissent plus d'une fois. Pour minimiser l'affichage, récupérez chaque résultat unique une seule fois en ajoutant le mot-clé DISTINCT :

mysql> SELECT DISTINCT maitre FROM animal;
+--------+
| maitre |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

Vous pouvez utiliser une clause WHERE pour combiner la sélection des lignes avec celle des colonnes. Par exemple, pour obtenir les dates de naissance des chiens et chats uniquement, utilisez cette requête :

mysql> SELECT nom, espece, naissance FROM animal
    -> WHERE espece = "chien" OR espece = "chat";
+--------+--------+------------+
| nom    | espece | naissance  |
+--------+--------+------------+
| Fluffy | chat   | 1993-02-04 |
| Claws  | chat   | 1994-03-17 |
| Buffy  | chien  | 1989-05-13 |
| Fang   | chien  | 1990-08-27 |
| Bowser | chien  | 1989-08-31 |
+--------+--------+------------+

3.3.4.4 Trier les enregistrements

Vous avez sûrement noté dans les exemples précédents que les lignes de résultat sont affichées sans ordre particulier. Cependant, il est souvent plus facile d'examiner les résultats lorsqu'ils sont triés d'une manière significative. Pour trier un résultat, vous devez utiliser une clause ORDER BY.

L'exemple suivant présente les dates d'anniversaire des animaux, triées par date :

mysql> SELECT nom, naissance FROM animal ORDER BY naissance;
+----------+------------+
| nom      | naissance  |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

Sur les noms de colonnes, le tri, comme toutes les opérations de comparaison, est normalement exécuté sans tenir compte de la casse. Cela signifie que l'ordre sera indéfini pour les colonnes qui sont identiques, excepté leur casse. Vous pouvez forcer le tri sensible à la casse en utilisant la clause BINARY : ORDER BY BINARY(champ).

Pour trier dans l'ordre inverse, ajoutez le mot-clé DESC (décroissant) au nom de la colonne à trier :

mysql> SELECT nom, naissance FROM animal ORDER BY naissance DESC;
+----------+------------+
| nom      | naissance  |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

Vous pouvez effectuer un tri sur plusieurs colonnes. Par exemple, pour trier par types d'animaux, puis par la date d'anniversaire des animaux, en pla¸ant les plus jeunes en premier, utilisez la requête suivante :

mysql> SELECT nom, espece, naissance FROM animal ORDER BY espece, naissance DESC;
+----------+---------+------------+
| nom      | espece  | naissance  |
+----------+---------+------------+
| Chirpy   | oiseau  | 1998-09-11 |
| Whistler | oiseau  | 1997-12-09 |
| Claws    | chat    | 1994-03-17 |
| Fluffy   | chat    | 1993-02-04 |
| Fang     | chien   | 1990-08-27 |
| Bowser   | chien   | 1989-08-31 |
| Buffy    | chien   | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | serpent | 1996-04-29 |
+----------+---------+------------+

Notez que le mot-clé DESC est appliqué uniquement au nom de la colonne qui le précède (naissance) ; les valeurs espece continuent à être triées dans l'ordre croissant.

3.3.4.5 Calcul sur les Dates

MySQL fournit plusieurs fonctions que vous pouvez utiliser pour effectuer des calculs sur les dates, par exemple, pour calculer l'âge ou pour extraire des parties de date.

Pour déterminer quel âge a chacun de vos animaux, vous devez calculer la différence entre l'année en cours et l'année de naissance, puis soustraire à la date courante si la date du jour se produit plus tôt dans l'année civile que la date de naissance. La requête suivante montre, pour chaque animal, la date de naissance, la date courante, ainsi que l'âge en années.

mysql> SELECT nom, naissance, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(naissance))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5))
    -> AS age
    -> FROM animal;
+----------+------------+--------------+------+
| nom      |naissance   | CURRENT_DATE | age  |
+----------+------------+--------------+------+
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
| Fang     | 1990-08-27 | 2001-08-29   |   11 |
| Bowser   | 1989-08-31 | 2001-08-29   |   11 |
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
+----------+------------+--------------+------+

Ici, YEAR() extrait l'année de la date et RIGHT() extrait les 5 caractères les plus à droite de la date qui représentent MM-DD (année civile). La partie de l'expression qui compare les valeurs de MM-DD évalue à 1 ou à 0, qui ajustent la différence d'année à la baisse, si CURRENT_DATE se produit plus au début de l'année que la naissance. L'expression complète est un peu plus fine en utilisant un alias (age) pour produire un nom de colonne un peu plus significatif.

La requête fonctionne, mais le résultat pourrait être lu plus facilement si les lignes étaient présentées dans le même ordre. Cela peut être obtenu en ajoutant une clause ORDER BY nom pour trier le résultat par nom :

mysql> SELECT nom, naissance, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(naissance))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5))
    -> AS age
    -> FROM animal ORDER BY nom;
+----------+------------+--------------+------+
| nom      |naissance   | CURRENT_DATE | age  |
+----------+------------+--------------+------+
| Bowser   | 1989-08-31 | 2001-08-29   |   11 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Fang     | 1990-08-27 | 2001-08-29   |   11 |
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
+----------+------------+--------------+------+

Pour trier le résultat par l'age plutôt que par le nom, utilisez simplement une clause ORDER BY différente :

mysql> SELECT nom, naissance, CURRENT_DATE,
    -> (YEAR(CURRENT_DATE)-YEAR(naissance))
    -> - (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5))
    -> AS age
    -> FROM animal ORDER BY age;
+----------+------------+--------------+------+
| nom      |naissance   | CURRENT_DATE | age  |
+----------+------------+--------------+------+
| Chirpy   | 1998-09-11 | 2001-08-29   |    2 |
| Puffball | 1999-03-30 | 2001-08-29   |    2 |
| Whistler | 1997-12-09 | 2001-08-29   |    3 |
| Slim     | 1996-04-29 | 2001-08-29   |    5 |
| Claws    | 1994-03-17 | 2001-08-29   |    7 |
| Fluffy   | 1993-02-04 | 2001-08-29   |    8 |
| Fang     | 1990-08-27 | 2001-08-29   |   11 |
| Bowser   | 1989-08-31 | 2001-08-29   |   11 |
| Buffy    | 1989-05-13 | 2001-08-29   |   12 |
+----------+------------+--------------+------+

Une requête similaire peut être utilisée pour déterminer l'âge qu'avait un animal à sa mort. Vous determinez les animaux qui le sont en regardant les valeurs mort qui ne valent pas NULL. Alors, pour ceux dont la valeur est non NULL, calculez la différence entre la mort et la naissance :

mysql> SELECT nom, naissance, mort,
    -> (YEAR(mort)-YEAR(naissance)) - (RIGHT(mort,5)<RIGHT(naissance,5))
    -> AS age
    -> FROM animal WHERE mort IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| nom    |naissance   | mort       | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

Cette requête utilise mort IS NOT NULL plutôt que mort <> NULL parce que NULL est une valeur spéciale. Cela sera expliqué plus tard. See Section 3.3.4.6, « Travailler avec la valeur NULL ».

Vous désirez savoir quels sont les animaux qui ont leur anniversaire le mois prochain ? Pour effectuer ce type de calculs, l'année et le jour ne sont pas utiles ; vous voulez simplement extraire le mois de la colonne naissance. MySQL fournit plusieurs fonctions d'extraction de parties de dates, comme YEAR(), MONTH(), et DAYOFMONTH(). MONTH() est la fonction appropriée dans notre cas. Pour voir comment cette fonction travaille, exécutez une requête simple qui retourne l'naissance et le MONTH(naissance) :

mysql> SELECT nom, naissance, MONTH(naissance) FROM animal;
+----------+------------+------------------+
| nom      | naissance  | MONTH(naissance) |
+----------+------------+------------------+
| Fluffy   | 1993-02-04 |                2 |
| Claws    | 1994-03-17 |                3 |
| Buffy    | 1989-05-13 |                5 |
| Fang     | 1990-08-27 |                8 |
| Bowser   | 1989-08-31 |                8 |
| Chirpy   | 1998-09-11 |                9 |
| Whistler | 1997-12-09 |               12 |
| Slim     | 1996-04-29 |                4 |
| Puffball | 1999-03-30 |                3 |
+----------+------------+------------------+

Trouver les animaux qui ont leur anniversaire dans le mois suivant est aisé. Supposez que le mois courant est Avril. Donc, la valeur du mois est 4 et vous cherchez les animaux nés en Mai (mois 5) comme ceci :

mysql> SELECT nom, naissance FROM animal WHERE MONTH(naissance) = 5;
+-------+------------+
| nom   |naissance   |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

Il y a une petite complication si le mois courant est Décembre, bien sûr. Vous ne pouvez pas uniquement ajouter 1 au numéro du mois courant (12) et chercher les animaux qui sont nés le mois numéro 13, parce qu'il n'existe pas. A la place, vous cherchez les animaux nés en Janvier (mois numéro 1).

Vous pouvez toujours écrire une requête qui fonctionne quelque soit le mois courant. Comme cela, vous n'avez pas à utiliser un numéro de mois particulier dans votre requête. DATE_ADD() vous permet d'ajouter un intervalle de temps à une date donnée. Si vous ajoutez un mois à la valeur de NOW(), et que vous extrayez le mois à l'aide de MONTH(), le résultat produit le mois dans lequel vous devez chercher un anniversaire :

mysql> SELECT nom, naissance FROM animal
    -> WHERE MONTH(naissance) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));

Une manière différente d'arriver au même résultat est d'ajouter 1 pour trouver le mois prochain après le mois courant (après l'usage de la fonction (MOD) pour ajouter à la valeur du mois la valeur 0 si il est de 12) :

mysql> SELECT nom, naissance FROM animal
    -> WHERE MONTH(naissance) = MOD(MONTH(NOW()), 12) + 1;

Notez que MONTH retourne un nombre entre 1 et 12. MOD(quelquechose,12) retourne un nombre entre 0 et 11. Donc, l'addition doit être faite après l'utilisation de la fonction MOD(), sinon, nous aurions un intervalle entre Novembre (11) et Janvier (1).

3.3.4.6 Travailler avec la valeur NULL

La valeur NULL peut être surprenante jusqu'à ce que vous vous y habituiez. Conceptuellement, NULL représente une valeur qui manque, ou une valeur inconnue, et elle est traitée différemment des autres valeurs. Pour tester la présence de la valeur NULL, vous ne pouvez pas utiliser les opérateurs arithmétiques habituels comme =, <, ou <>. Pour le voir, il suffit d'essayer ceci :

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Clairement, vous n'obtiendrez aucun résultat valable pour ces comparaisons. Utilisez les opérateurs IS NULL et IS NOT NULL à la place :

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

Notez que deux NULL sont considérés comme égaux lors que vous utilisez la clause GROUP BY.

Avec MySQL, 0 et NULL représentent le booléen faux, et tout le reste représente le booléen vrai. La valeur par défaut du booléen vrai issue d'une comparaison est 1.

Lorsque vous utilisez la clause ORDER BY, les valeurs NULL sont toujours triées en premier, même si vous utilisez l'attribut DESC.

Ce traitement particulier de NULL explique pourquoi, dans la section précédente, il était nécessaire de déterminer quel animal ne vivait plus en utilisant la fonction mort IS NOT NULL au lieu de mort <> NULL.

3.3.4.7 Recherche de modèles

MySQL fournit le standard SQL des recherches de modèles, basé sur une extension des expressions régulières similaires à celles utilisées par les utilitaires Unix comme vi, grep, et sed.

La recherche de modèles SQL vous permet d'utiliser le caractère ‘_’ pour trouver n'importe quel caractère et le caractère ‘%’ pour trouver un nombre arbitraire de caractères (y compris aucun caractère). Dans MySQL, la recherche de modèles est sensible à la casse par défaut. Quelques exemples vous sont présentés ici.

Notez que vous n'utilisez ni = ni <> lorsque vous utilisez la recherche de modèles SQL ; utilisez les opérateurs de comparaison LIKE ou NOT LIKE à la place.

Pour trouver les noms commen¸ant par la lettre ‘b’ :

mysql> SELECT * FROM animal WHERE nom LIKE "b%";
+--------+--------+--------+------+------------+------------+
| nom    | maitre | espece | sexe | naissance  | mort       |
+--------+--------+--------+------+------------+------------+
| Buffy  | Harold | chien  | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | chien  | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+--------+------+------------+------------+

Pour trouver les noms finissant par ‘fy’ :

mysql> SELECT * FROM animal WHERE nom LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| nom    | maitre | espece  | sexe | naissance  | mort  |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | chat    | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | chien   | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

Pour trouver les noms contenant le caractères ‘w’ :

mysql> SELECT * FROM animal WHERE nom LIKE "%w%";
+----------+--------+---------+------+------------+------------+
| nom      | maitre | espece  | sexe | naissance  | mort       |
+----------+--------+---------+------+------------+------------+
| Claws    | Gwen   | chat    | m    | 1994-03-17 | NULL       |
| Bowser   | Diane  | chien   | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen   | oiseaux | NULL | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+

Pour trouver les noms contenant exactement 5 caractères, utilisez le caractère de recherche ‘_’ :

mysql> SELECT * FROM animal WHERE nom LIKE "_____";
+-------+--------+--------+------+------------+------+
| nom   | maitre | espece | sexe | naissance  | mort |
+-------+--------+--------+------+------------+------+
| Claws | Gwen   | chat   | m    | 1994-03-17 | NULL |
| Buffy | Harold | chien  | f    | 1989-05-13 | NULL |
+-------+--------+--------+------+------------+------+

L'autre type de recherche de modèles fourni par MySQL utilise les expressions régulières étendues. Lorsque vous testez une recherche avec ce type de modèle, utilisez les opérateurs REGEXP et NOT REGEXP (ou RLIKE et NOT RLIKE qui sont des synonymes).

Quelques caractéristiques des expressions régulières étendues sont :

  • Le caractère ‘.’ trouve n'importe quel caractère.

  • Une classe de caractères ‘[...]’ trouve n'importe quel caractère contenu entre les crochets. Par exemple, la classe de caractères ‘[abc]’ trouve le caractère ‘a’, ‘b’, ou ‘c’. Pour définir un intervalle de caractères, utilisez un trait d'union. La classe de caractères ‘[a-z]’ trouvera n'importe quel caractère minuscule, tout comme la classe ‘[0-9]’ trouvera n'importe quel nombre.

  • Le caractère ‘*’ trouvera aucune ou plus d'instances du caractère qui le précède. Par exemple, ‘x*’ trouvera n'importe quel nombre de fois le caractère ‘x’, ‘[0-9]*’ trouvera n'importe quel nombre et ‘.*’ trouvera n'importe quel nombre de fois n'importe quel caractère.

  • Le modèle est trouvé s'il se produit n'importe où dans la valeur testée. (Les modèles SQL ne sont trouvés que s'ils sont présents en valeur entière.)

  • Pour ancrer un modèle de sorte qu'il soit trouvé au début ou à la fin de valeur testée, utilisez ‘^’ au début ou bien ‘$’ à la fin du modèle.

Pour démontrer comment les expressions régulières fonctionnent, les requêtes LIKE vues précédemment ont été réécrites pour utiliser REGEXP.

Pour trouver les noms qui commencent par la lettre ‘b’, utilisez ‘^’ pour trouver le début du nom :

mysql> SELECT * FROM animal WHERE nom REGEXP "^b";
+--------+--------+--------+------+------------+------------+
| nom    | maitre | espece | sexe | naissance  | mort       |
+--------+--------+--------+------+------------+------------+
| Buffy  | Harold | chien  | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | chien  | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+--------+------+------------+------------+

Avant la version 3.23.4 de MySQL, REGEXP était sensible à la casse, et la requête précédente ne retournait aucune ligne. Pour trouver la lettre ‘b’ minuscule ou majuscule, utilisez cette requête à la place :

mysql> SELECT * FROM animal WHERE nom REGEXP "^[bB]";

Depuis MySQL 3.23.4, pour forcer REGEXP à être sensible à la casse, utilisez le mot-clé BINARY pour faire de la chaîne, une chaîne binaire. Cette requête trouvera uniquement la lettre minuscule ‘b’ au début du nom :

mysql> SELECT * FROM animal WHERE nom REGEXP BINARY "^b";

Pour trouver les noms finissant par ‘fy’, utilisez ‘$’ pour trouver la fin du nom :

mysql> SELECT * FROM animal WHERE nom REGEXP "fy$";
+---------+--------+--------+-------+------------+------+
| nom     | maitre | espece | sexe  | naissance  | mort |
+---------+--------+--------+-------+------------+------+
| Fluffy  | Harold | chat   | f     | 1993-02-04 | NULL |
| Buffy   | Harold | chien  | f     | 1989-05-13 | NULL |
+---------+--------+--------+-------+------------+------+

Pour trouver les noms contenant la lettre ‘w’ minuscule ou majuscule, utilisez la requête suivante :

mysql> SELECT * FROM animal WHERE nom REGEXP "w";
+----------+--------+---------+------+------------+------------+
| nom      | maitre | espece  | sexe | naissance  | mort       |
+----------+--------+---------+------+------------+------------+
| Claws    | Gwen   | chat    | m    | 1994-03-17 | NULL       |
| Bowser   | Diane  | chien   | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen   | oiseaux | NULL | 1997-12-09 | NULL       |
+----------+--------+---------+------+------------+------------+

Parce qu'une expression régulière est trouvée si le modèle se trouve n'importe où dans la valeur, il n'est pas nécessaire dans la requête précédente de mettre un joker de chaque côté du modèle recherché pour trouver la valeur entière comme cela aurait été le cas en utilisant les modèles de recherche SQL.

Pour trouver les noms contenant exactement 5 caractères, utilisez ‘^’ et ‘$’ pour trouver le début et la fin du nom, et 5 instances de ‘.’ au milieu :

mysql> SELECT * FROM animal WHERE nom REGEXP "^.....$";
+-------+--------+--------+------+------------+------+
| nom   | maitre | espece | sexe | naissance  | mort |
+-------+--------+--------+------+------------+------+
| Claws | Gwen   | chat   | m    | 1994-03-17 | NULL |
| Buffy | Harold | chien  | f    | 1989-05-13 | NULL |
+-------+--------+--------+------+------------+------+

Vous pouvez aussi écrire la requête suivante en utilisant l'opérateur ‘{n}’ ``répéter-n-fois'' :

mysql> SELECT * FROM animal WHERE nom REGEXP "^.{5}$";
+-------+--------+--------+------+------------+------+
| nom   | maitre | espece | sexe | naissance  | mort |
+-------+--------+--------+------+------------+------+
| Claws | Gwen   | chat   | m    | 1994-03-17 | NULL |
| Buffy | Harold | chien  | f    | 1989-05-13 | NULL |
+-------+--------+--------+------+------------+------+

3.3.4.8 Compter les lignes

Les bases de données sont souvent employées pour répondre à la question : ``Combien de fois un certain type de données se trouve dans la table ?'' Par exemple, vous aimeriez savoir combien d'animaux vous avez, ou bien combien d'animaux chaque propriétaire possède, ou encore savoir différentes choses concernant vos animaux.

Savoir combien vous avez d'animaux revient à se poser la question : ``Combien de lignes y a-t-il dans la table animal ?'' parce qu'il y a un enregistrement par animal. La fonction COUNT() compte le nombre de résultats non NULL, donc, la requête pour compter les animaux ressemble à ceci :

mysql> SELECT COUNT(*) FROM animal;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

Vous pouvez trouver également les noms des propriétaires des animaux. Vous pouvez utiliser COUNT() si vous voulez trouver combien d'animaux possède chaque propriétaire :

mysql> SELECT maitre, COUNT(*) FROM animal GROUP BY maitre;
+--------+----------+
| maitre | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

Notez l'utilisation de la clause GROUP BY pour grouper tous les enregistrements par propriétaire. Sans cela, vous auriez le message d'erreur suivant :

mysql> SELECT maitre, COUNT(maitre) FROM animal;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT() et GROUP BY sont utiles pour caractériser vos données de diverses fa¸ons. Les exemples suivants montrent différentes manières pour obtenir des statistiques sur les animaux.

Nombre d'animaux par espèce :

mysql> SELECT espece, COUNT(*) FROM animal GROUP BY espece;
+---------+----------+
| espece  | COUNT(*) |
+---------+----------+
| oiseau  |        2 |
| chat    |        2 |
| chien   |        3 |
| hamster |        1 |
| serpent |        1 |
+---------+----------+

Nombre d'animaux par sexe :

mysql> SELECT sexe, COUNT(*) FROM animal GROUP BY sexe;
+------+----------+
| sexe | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

(Dans ce résultat, NULL indique les sexes inconnus.)

Nombre d'animaux par espèce et sexe :

mysql> SELECT espece, sexe, COUNT(*) FROM animal GROUP BY espece, sexe;
+---------+------+----------+
| espece  | sexe | COUNT(*) |
+---------+------+----------+
| oiseau  | NULL |        1 |
| oiseau  | f    |        1 |
| chat    | f    |        1 |
| chat    | m    |        1 |
| chien   | f    |        1 |
| chien   | m    |        2 |
| hamster | f    |        1 |
| serpent | m    |        1 |
+---------+------+----------+

Vous n'avez pas besoin de rechercher une table entière quand vous employez COUNT(). Par exemple, la requête précédente, si vous voulez trouver uniquement les chiens et les chats, ressemble à cela :

mysql> SELECT espece, sexe, COUNT(*) FROM animal
    -> WHERE espece = "chien" OR espece = "chat"
    -> GROUP BY espece, sexe;
+---------+------+----------+
| espece  | sexe | COUNT(*) |
+---------+------+----------+
| chat    | f    |        1 |
| chat    | m    |        1 |
| chien   | f    |        1 |
| chien   | m    |        2 |
+---------+------+----------+

Ou bien, si vous voulez trouver le nombre d'animaux par sexe, uniquement pour les animaux dont le sexe est connu :

mysql> SELECT espece, sexe, COUNT(*) FROM animal
    -> WHERE sexe IS NOT NULL
    -> GROUP BY espece, sexe;
+---------+------+----------+
| espece  | sexe | COUNT(*) |
+---------+------+----------+
| oiseau  | f    |        1 |
| chat    | f    |        1 |
| chat    | m    |        1 |
| chien   | f    |        1 |
| chien   | m    |        2 |
| hamster | f    |        1 |
| serpent | m    |        1 |
+---------+------+----------+

3.3.4.9 Utiliser plus d'une table

La table animal garde les enregistrements de vos animaux. Si vous voulez enregistrer d'autres informations concernant vos animaux, comme les événements de leurs vies, les visites chez le vétérinaire, ou encore lorsqu'ils ont mis bas, vous avez besoin d'une autre table. De quoi a besoin cette table ? Elle doit :

  • Contenir le nom de l'animal pour savoir à quel animal cet événement se rattache.

  • Une date pour savoir quand a eu lieu l'événement.

  • Un champ qui décrit l'événement.

  • Un champ de type événement, si vous voulez être capable de cataloguer les événements.

En prenant cela en considération, le code CREATE TABLE pour la table evenement doit ressembler à ceci :

mysql> CREATE TABLE evenement (nom VARCHAR(20), date DATE,
    -> type VARCHAR(15), remarque VARCHAR(255));

Tout comme la table animal, il est facile d'enregistrer les enregistrements initiaux en créant un fichier texte délimité par des tabulations, contenant l'information :

nomdatetyperemarque
Fluffy1995-05-15mise bas4 chatons, 3 femelles, 1 mâles
Buffy1993-06-23mise bas5 chiots, 2 femelles, 3 mâles
Buffy1994-06-19mise bas3 chiots, 3 femelles
Chirpy1999-03-21vétérinaireRedresser le bec
Slim1997-08-03vétérinaireCotes cassées
Bowser1991-10-12chenil 
Fang1991-10-12chenil 
Fang1998-08-28anniversaireDon d'un nouvel objet de mastication
Claws1998-03-17anniversaireDon d'un nouveau collier anti-puces
Whistler1998-12-09anniversairePremier anniversaire

Chargez ces enregistrements comme cela :

mysql> LOAD DATA LOCAL INFILE "evenement.txt" INTO TABLE evenement;

En se basant sur ce que vous avez appris des requêtes effectuées sur la table animal, vous devriez être capable de faire des recherches sur les enregistrements de la table evenement ; le principe est le même. Quand devez-vous vous demander si la table evenement est seule suffisante pour répondre à votre question ?

Supposez que vous voulez trouver l'âge de chaque animal lorsqu'il a mis bas. La table evenement indique quand cela s'est produit, mais pour le calcul de l'âge de la mère, vous avez besoin de sa date de naissance. Parce que ces informations sont stockées dans la table animal, vous avez besoin des deux tables pour cette requête :

mysql> SELECT animal.nom,
    -> (TO_DAYS(date) - TO_DAYS(naissance))/365 AS age,
    -> remarque
    -> FROM animal, evenement
    -> WHERE animal.nom = evenement.nom AND type = "mise bas";
+--------+------+--------------------------------+
| nom    | age  | remarque                       |
+--------+------+--------------------------------+
| Fluffy | 2.27 | 4 chatons, 3 femelles, 1 mâle  |
| Buffy  | 4.12 | 5 chiots, 2 femelles, 3 mâles  |
| Buffy  | 5.10 | 3 chiots, 3 femelles           |
+--------+------+--------------------------------+

Il y a plusieurs choses à noter concernant cette requête :

  • La clause FROM liste les deux tables parce que la requête a besoin d'informations contenues dans ces deux tables.

  • Lorsque vous combinez (joignez) des informations provenant de plusieurs tables, vous devez spécifier quels enregistrements d'une table peuvent être associés à quels enregistrements des autres tables. C'est aisé parce qu'elles ont toutes les deux une colonne nom. La requête utilise la clause WHERE pour faire correspondre les enregistrements des deux tables sur les valeurs de la colonne nom.

  • Parce que la colonne nom apparaît dans les deux tables, vous devez être explicite concernant la table que vous utilisez lorsque vous vous référez à cette colonne. C'est fait en faisant précéder le nom de la colonne par le nom de la table.

Vous n'avez pas besoin de deux tables différentes pour effectuer une jointure. Quelques fois, c'est plus facile de joindre une table sur elle-même, si vous voulez comparer des enregistrements dans une table avec d'autres enregistrements de la même table. Par exemple, pour trouver des paires multiples parmi vos animaux, vous pouvez joindre la table animal sur elle-même pour trouver les paires mâles / femelles par rapport à l'espèce :

mysql> SELECT p1.nom, p1.sexe, p2.nom, p2.sexe, p1.espece
    -> FROM animal AS p1, animal AS p2
    -> WHERE p1.espece = p2.espece AND p1.sexe = "f" AND p2.sexe = "m";
+--------+------+--------+------+---------+
| nom    | sexe | nom    | sexe | espece  |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | chat    |
| Buffy  | f    | Fang   | m    | chien   |
| Buffy  | f    | Bowser | m    | chien   |
+--------+------+--------+------+---------+

Dans cette requête, nous avons spécifié des alias pour les noms de tables dans l'ordre de référence des colonnes et ainsi maintenir directement à quelle instance de la table chaque colonne est associée.

3.4 Obtenir des informations à propos des bases de données et des tables

Que faire si vous oubliez le nom d'une base de données ou d'une table, ou bien encore la structure d'une table donnée (par exemple, comment se nomment ses colonnes) ?

MySQL répond à ce problème en fournissant plusieurs commandes qui renvoient des informations à propos des tables et des bases de données les contenant.

Vous avez déjà vu SHOW DATABASES qui liste les bases de données gérées par le serveur. Pour trouver quelle base de données est actuellement sélectionnée, utilisez la fonction DATABASE() :

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

Si vous n'avez encore sélectionné aucune base de données, le résultat est vide.

Pour trouver quelles sont les tables que la base contient (par exemple, quand vous n'êtes pas sûr du nom d'une table), utilisez cette commande :

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| evenement           |
| animal              |
+---------------------+

Si vous voulez en savoir d'avantage sur la structure d'une table, la commande DESCRIBE est utile ; elle fournit des informations sur chaque colonne de la table :

mysql> DESCRIBE animal;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| nom       | varchar(20) | YES  |     | NULL    |       |
| maitre    | varchar(20) | YES  |     | NULL    |       |
| espece    | varchar(20) | YES  |     | NULL    |       |
| sexe      | char(1)     | YES  |     | NULL    |       |
| naissance | date        | YES  |     | NULL    |       |
| mort      | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Field indique le nom de la colonne, Type est son type de données, NULL indique si la colonne peut contenir des valeurs NULL, Key indique si la colonne est indexée et Default spécifie la valeur par défaut de la colonne.

Si vous avez des index sur une table, SHOW INDEX FROM nom_de_table vous fournira des informations sur elles.

3.5 Utilisation de mysql en mode batch

Dans les sections précédentes, vous avez utilisé mysql inter activement pour entrer vos requêtes et voir les résultats. Vous pouvez aussi utiliser mysql en mode batch. Pour ce faire, placez les commandes que vous voulez exécuter dans un fichier, puis dites à mysql de lire les entrées à partir de celui-ci :

shell> mysql < fichier-batch

Si vous utilisez mysql sous Windows et que vous avez des caractères spéciaux dans le fichier qui posent problèmes, vous pouvez faire :

dos> mysql -e "source fichier-batch"

Si vous devez spécifier les paramètres de connexion en ligne de commande, la commande ressemblera à ca :

shell> mysql -h hôte -u utilisateur -p < fichier-batch
Enter password: ********

Lorsque vous utilisez mysql de cette fa¸on, vous créez un fichier de script, puis vous l'exécutez.

Si vous voulez que le script continue, même si il y a des erreurs, vous devez utiliser l'option --force de la ligne de commande.

Pourquoi utilisez un script ? Voici quelques raisons :

  • Si vous utilisez une requête de fa¸on répétitive (c'est à dire, chaque jour, ou chaque semaine), en faire un script vous évitera de la réécrire chaque fois.

  • Vous pouvez générer de nouvelles requêtes à partir de requêtes existantes et similaires en copiant et éditant des fichiers de scripts.

  • Ce mode peut aussi être utile lors du développement d'une requête, particulièrement pour les commandes sur plusieurs lignes ou plusieurs séquences de commandes. Si vous commettez une erreur, vous n'avez pas à tout récrire. Editez juste votre script pour corriger l'erreur et dites à mysql de l'exécuter à nouveau.

  • Si vous avez une requête qui produit beaucoup d'affichage, vous pouvez le rediriger vers un visualiseur plutôt que de le regarder défiler sur votre écran :

    shell> mysql < fichier-batch | more
    
  • Vous pouvez capturer l'affichage dans un fichier pour un traitement ultérieur :

    shell> mysql < fichier_batch > mysql.out
    
  • Vous pouvez distribuer votre script à d'autres personnes pour qu'elles l'exécutent.

  • Quelques situations ne permettent pas une utilisation interactive, par exemple, quand vous exécutez une requête à partir d'une tâche cron. Dans ce cas, vous devez utiliser le mode batch.

Le format d'affichage par défaut est différent (plus concis) lorsque vous exécutez mysql en mode batch de celui utilisé inter activement. Par exemple, le résultat de SELECT DISTINCT espece FROM animal ressemble à ¸a inter activement :

+---------+
| espece  |
+---------+
| oiseau  |
| chat    |
| chien   |
| hamster |
| serpent |
+---------+

Mais à ¸a en mode batch :

espece
oiseau
chat
chien
hamster
serpent

Si vous voulez le format d'affichage interactif en mode batch, utilisez mysql -t. Pour écrire les commandes exécutez dans la sortie, utilisez mysql -vvv.

Vous pouvez aussi utiliser un script à partir de l'invite mysql en utilisant la commande source :

mysql> source nom_fichier;

3.6 Exemples de requêtes usuelles

Voilà des exemples qui vous serviront à résoudre les problèmes communs avec MySQL.

Certains exemples utilisent la table shop pour sauvegarder le prix de chaque article (numéro de l'élément) pour certains vendeurs (dealers). En supposant que chaque vendeur à un prix fixe pour chaque article, le couple (article, dealer) est une clef primaire pour les enregistrements.

Démarrez le client en ligne de commande mysql et sélectionnez une base de données :

mysql nom-base-données

(Dans la plupart des installations de MySQL, vous pouvez utiliser la base de données test).

Vous pouvez créer la table d'exemple de la fa¸on suivante :

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);

Les données d'exemple sont :

mysql> SELECT * FROM shop;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.1 La valeur maximale d'une colonne

``Quel est le numéro du plus grand élément ?''

SELECT MAX(article) AS article FROM shop

+---------+
| article |
+---------+
|       4 |
+---------+

3.6.2 La ligne contenant le maximum d'une certaine colonne

``Trouvez le numéro, vendeur et prix de l'article le plus cher.''

En SQL-99 (et MySQL version 4.1), cela est facilement fait avec une sous-requête :

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

En MySQL 4.0 ou plus ancien, vous devez le faire en deux temps :

  1. Obtenir le plus grand prix de la table avec une requête SELECT.

    mysql> SELECT MAX(price) FROM shop;
    +------------+
    | MAX(price) |
    +------------+
    |      19.95 |
    +------------+
    
  2. Utiliser la valeur 19.95 avec la requête suivante :

    mysql> SELECT article, dealer, price
        -> FROM   shop
        -> WHERE  price=19.95;
    +---------+--------+-------+
    | article | dealer | price |
    +---------+--------+-------+
    |    0004 | D      | 19.95 |
    +---------+--------+-------+
    

Une autre solution est de trier toutes les lignes en ordre décroissant, et de ne lire que la première ligne avec la clause LIMIT :

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1;

Note : s'il y a beaucoup d'articles chers (par exemple, chaque 19.95) la solution avec LIMIT n'en montre qu'un !.

3.6.3 Maximum d'une colonne par groupe

``Quel est le plus grand prix par article ?''

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

3.6.4 La ligne contenant la plus grande valeur d'un certain champ par rapport à un groupe

``Pour chaque article, trouvez le ou les vendeurs ayant le plus haut prix.''

En ANSI SQL, je l'aurais fait de cette fa¸on avec une sous-requête :

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

En MySQL il vaut mieux le faire en plusieurs étapes :

  1. Récupérer la liste de couples article et plus grand prix.

  2. Pour chaque article, récupérer la ligne qui a le plus grand prix stocké.

Cela se fait facilement avec une table temporaire :

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

Si vous n'utilisez pas une table TEMPORARY, vous devez aussi verrouiller celle-ci.

``Peut-on le faire avec une seule requête ?''

Oui, mais en utilisant une astuce inefficace que j'appelle ``astuce du MAX-CONCAT'' :

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

Le dernier exemple peut, bien sûr, être amélioré en découpant les colonnes concaténées dans le client.

3.6.5 Utiliser les variables utilisateur

Vous pouvez utiliser les variables utilisateur de MySQL pour garder des résultats en mémoire sans avoir à les enregistrer dans des variables temporaires du client. See Section 9.3, « Variables utilisateur ».

Par exemple, pour trouver l'article avec le plus haut et le plus bas prix, vous pouvez faire :

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.6 Utiliser les clefs étrangères

Depuis la version 3.23.44 de MySQL, les tables InnoDB supportent les contraintes des clefs étrangères. See Chapitre 15, Le moteur de tables InnoDB. Consultez aussi Section 1.5.5.5, « Les clés étrangères ».

Actuellement, vous n'avez pas besoin de clefs étrangères pour réaliser des jointures entre les tables. La seule chose que MySQL ne fait pas encore (avec les types autres que InnoDB), est CHECK pour s'assurer que que la clef que vous utilisez existe bien dans la ou les tables que vous référencez et il n'efface pas automatiquement les lignes d'une table avec une définition de clef étrangère. Si vous utilisez vos clefs comme une clef normale, tout marchera parfaitement :

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);


INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());


SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

3.6.7 Recherche sur deux clefs

MySQL n'optimise pas encore quand vous effectuez des recherches sur deux clefs différentes combinées avec OR (la recherche sur une clef avec différentes parties OR est elle pas mal optimisée) :

SELECT champ1_index, champ2_index FROM test_table WHERE champ1_index = '1'
OR  champ2_index = '1'

La raison est que nous n'avons pas trouvé le temps suffisant pour parvenir à un moyen efficace de gérer cela dans un cas général. (En comparaison, la gestion de AND est maintenant complètement générale et fonctionne très bien.)

En MySQL 4.0, vous pouvez résoudre ce problème efficacement en utilisant une clause UNION qui combine le résultat de deux requêtes SELECT séparée.s See Section 13.1.7.2, « Syntaxe de UNION ». Chaque requête SELECT ne recherche qu'avec une seule clé, et peut être optimisée :

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';

Avant MYSQL 4.0, vous pouvez résoudre ce problème efficacement en utilisant une table temporaire (TEMPORARY). Ce type d'optimisation est très utile si vous utilisez des requêtes très complexes et que le serveur SQL fait une optimisation dans le mauvais ordre.

CREATE TEMPORARY TABLE tmp
SELECT champ1_index, champ2_index FROM test_table WHERE champ1_index = '1';
INSERT INTO tmp
SELECT champ1_index, champ2_index FROM test_table WHERE champ2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

La méthode ci-dessus pour résoudre cette requête est en effet une UNION de deux requêtes. See Section 13.1.7.2, « Syntaxe de UNION ».

3.6.8 Calcul du nombre de visites par jour

Ce qui suit donne une idée d'une utilisation des fonctions de bits pour calculer le nombre de jours par mois où un utilisateur a visité une page web.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

La table d'exemple contient des valeurs au format année-mois-jour, qui représentent des visites d'utilisateurs sur la page. Pour déterminer le nombre de jour entre deux visites, utilisez la requête suivante :

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

Qui retourne :

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

Ce qui précède calcule le nombre de jours différents qui a été utilisé pour une combinaison année/mois, avec suppression automatique des doublons.

3.6.9 Utiliser AUTO_INCREMENT

L'attribut AUTO_INCREMENT peut être utilisé pour générer un identifiant unique pour les nouvelles lignes :

CREATE TABLE animals (
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (id)
             );
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
                                  ("lax"),("whale"),("ostrich");
SELECT * FROM animals;

Qui retourne :

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Vous pouvez obtenir la valeur utilisée de la clef AUTO_INCREMENT avec la fonction SQL LAST_INSERT_ID() ou la fonction d'API mysql_insert_id().

Note@ : Pour une insertion multi-lignes, LAST_INSERT_ID()/mysql_insert_id() retourneront la clef AUTO_INCREMENT de la première ligne insérée. Cela permet de reproduire les insertions multi-lignes sur d'autres services.

Pour les tables MyISAM et BDB vous pouvez spécifier AUTO_INCREMENT sur une colonne secondaire d'une clef multi-colonnes. Dans ce cas, la valeur générée pour la colonne auto-incrémentée est calculée de la fa¸on suivante : MAX(auto_increment_column)+1) WHERE prefix=given-prefix. C'est utile lorsque vous voulez placer des données dans des groupes ordonnés.

CREATE TABLE animals (
             grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
             );
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
                  ("bird","penguin"),("fish","lax"),("mammal","whale"),
                  ("bird","ostrich");
SELECT * FROM animals ORDER BY grp,id;

Qui retourne :

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Notez que dans ce cas, la valeur d'AUTO_INCREMENT sera réutilisée si vous effacez la ligne avec la plus grande valeur d'AUTO_INCREMENT tous groupes confondus. Cela n'arrive jamais avec les tables MyISAM, dont les valeurs AUTO_INCREMENT ne sont jamais réutilisées.

3.7 Requêtes du projet Twin

A Analytikerna et Lentus, nous avons eu à mettre en place le partie système et base de données d'un grand projet de recherche. Ce projet est une collaboration entre l'institut de médecine environnementale de l'institut de Karolinska Stockholm et la section de recherche clinique d'âge et de psychologie à l'université de la Californie du sud.

Le projet nécessite une partie de récolte d'informations où tous les jumeaux en Suède de plus de 65 ans sont contactés par téléphone. Ceux qui répondent à certains critères sont admis à la seconde étape. Dans celle-ci, les jumeaux qui veulent participer rencontrent une équipe de médecins/infirmiers. Les examens incluent des examens physiques et neuropsychologiques, des tests en laboratoire, de la neuro-imagerie, des études psychologiques et de la collecte d'informations relatives à la famille. En plus de tout cela, les données à propos des facteurs de risques médicaux et environnementaux sont collectées.

Plus d'informations à propos de l'étude Twin peuvent être trouvées sur : http://www.imm.ki.se/TWIN/TWINUKW.HTM

La dernière partie de ce projet est administrée avec une interface web écrite en utilisant Perl et MySQL.

Chaque nuit, toutes les informations des interviews sont stockées dans une base de données MySQL.

3.7.1 Trouver tous les jumeaux répondant aux critères

La requête suivante a été utilisée pour déterminer qui participerait à la seconde partie du projet :

SELECT
        CONCAT(p1.id, p1.tvab) + 0 AS tvid,
        CONCAT(p1.christian_name, " ", p1.surname) AS Name,
        p1.postal_code AS Code,
        p1.city AS City,
        pg.abrev AS Area,
        IF(td.participation = "Aborted", "A", " ") AS A,
        p1.dead AS dead1,
        l.event AS event1,
        td.suspect AS tsuspect1,
        id.suspect AS isuspect1,
        td.severe AS tsevere1,
        id.severe AS isevere1,
        p2.dead AS dead2,
        l2.event AS event2,
        h2.nurse AS nurse2,
        h2.doctor AS doctor2,
        td2.suspect AS tsuspect2,
        id2.suspect AS isuspect2,
        td2.severe AS tsevere2,
        id2.severe AS isevere2,
        l.finish_date
FROM
        twin_project AS tp
        /* For Twin 1 */
        LEFT JOIN twin_data AS td ON tp.id = td.id
                  AND tp.tvab = td.tvab
        LEFT JOIN informant_data AS id ON tp.id = id.id
                  AND tp.tvab = id.tvab
        LEFT JOIN harmony AS h ON tp.id = h.id
                  AND tp.tvab = h.tvab
        LEFT JOIN lentus AS l ON tp.id = l.id
                  AND tp.tvab = l.tvab
        /* For Twin 2 */
        LEFT JOIN twin_data AS td2 ON p2.id = td2.id
                  AND p2.tvab = td2.tvab
        LEFT JOIN informant_data AS id2 ON p2.id = id2.id
                  AND p2.tvab = id2.tvab
        LEFT JOIN harmony AS h2 ON p2.id = h2.id
                  AND p2.tvab = h2.tvab
        LEFT JOIN lentus AS l2 ON p2.id = l2.id
                  AND p2.tvab = l2.tvab,
        person_data AS p1,
        person_data AS p2,
        postal_groups AS pg
WHERE
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id = tp.id AND p1.tvab = tp.tvab AND
        p2.id = p1.id AND p2.ptvab = p1.tvab AND
        /* Just the sceening survey */
        tp.survey_no = 5 AND
        /* Skip if partner died before 65 but allow emigration (dead=9) */
        (p2.dead = 0 OR p2.dead = 9 OR
         (p2.dead = 1 AND
          (p2.death_date = 0 OR
           (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
            >= 65))))
        AND
        (
        /* Twin is suspect */
        (td.future_contact = 'Yes' AND td.suspect = 2) OR
        /* Twin is suspect - Informant is Blessed */
        (td.future_contact = 'Yes' AND td.suspect = 1
                                   AND id.suspect = 1) OR
        /* No twin - Informant is Blessed */
        (ISNULL(td.suspect) AND id.suspect = 1
                            AND id.future_contact = 'Yes') OR
        /* Twin broken off - Informant is Blessed */
        (td.participation = 'Aborted'
         AND id.suspect = 1 AND id.future_contact = 'Yes') OR
        /* Twin broken off - No inform - Have partner */
        (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                      AND p2.dead = 0))
        AND
        l.event = 'Finished'
        /* Get at area code */
        AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
        /* Not already distributed */
        AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
        /* Has not refused or been aborted */
        AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
        OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
        tvid;

Quelques explications :

  • CONCAT(p1.id, p1.tvab) + 0 AS tvid

    Nous voulons trier la concaténation de id et tvab dans un ordre numérique. Ajouter 0 au résultat force MySQL à le considérer comme un nombre.

  • colonne id

    Identifie une paire de jumeaux. C'est un clef dans toutes les tables.

  • colonne tvab

    Identifie un jumeau dans une paire. Valeur 1 ou 2.

  • colonne ptvab

    Inverse de tvab. Si tvab est 1 c'est égal à 2, et vice-versa. Elle existe pour diminuer la frappe et faciliter la tâche à MySQL lors de l'optimisation de la requête.

Cette requête montre, entre autres, comment faire pour consulter une table depuis cette même table en utilisant une jointure (p1 et p2). Dans cet exemple, est utilisé pour chercher quel partenaire du projet est décédé avant l'âge de 65 ans. Si c'est le cas, la ligne n'est pas retournée.

Tout ce qui précède existe dans toutes les tables avec des informations relatives aux jumeaux. Nous avons une clé sur les champs id,tvab (toutes les tables), et sur les champs id,ptvab (person_data) pour accélérer les requêtes.

Sur notre machine de production (un 200MHz UltraSPARC), cette requête retourne près de 150-200 lignes et prend moins d'une seconde.

Le nombre d'enregistrements dans les tables utilisées plus haut :

TableLignes
person_data71074
lentus5291
twin_project5286
twin_data2012
informant_data663
harmony381
postal_groups100

3.7.2 Afficher une table avec l'état des paires de jumeaux

Chaque entrevue se finit avec un code d'état, appelé event. La requête ci-dessous montre comment afficher une table avec toutes les paires, rassemblées par code d'état. Elle indique combien de paires ont terminé, combien de paires ont à moitié terminé et combien on refusé, etc.

SELECT
        t1.event,
        t2.event,
        COUNT(*)
FROM
        lentus AS t1,
        lentus AS t2,
        twin_project AS tp
WHERE
        /* Nous recherchons une paire à la fois */
        t1.id = tp.id
        AND t1.tvab=tp.tvab
        AND t1.id = t2.id
        /* On étudie toutes les données */
        AND tp.survey_no = 5
        /* Cela évite qu'un paire deviennen un doublon */
        AND t1.tvab='1' AND t2.tvab='2'
GROUP BY
        t1.event, t2.event;

3.8 Utilisation de MySQL avec Apache

Il existe des programmes vous permettant d'identifier vos utilisateurs à l'aide d'une base MySQL et qui vous permettent aussi de créer des journaux de log dans vos tables MySQL.

Vous pouvez changer le format d'archivage d'Apache pour le rendre plus facilement lisible par MySQL en mettant ce qui suit dans le fichier de configuration d'Apache :

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

Avec MySQL, vous pouvez exécuter une requête de cette manière :

LOAD DATA INFILE '/local/access_log' INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'