Manuel de référence MySQL 5.0

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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

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

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 insensibilité à 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  |
+-------+--------+--------+------+------------+-------+

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

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.

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 déterminez 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. Voir Section 3.3.4.6, « Travailler avec la valeur NULL »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 la 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. À la place, vous cherchez les animaux nés en janvier (mois numéro 1).

Vous pouvez toujours écrire une requête qui fonctionne quel que 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 s'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).

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.

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 insensible à 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ère '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 quelle lettre 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 |
+-------+--------+--------+------+------------+------+

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

La table animal garde les enregistrements de vos animaux. Si vous voulez enregistrer d'autres informations concernant vos animaux, comme les événements de leur vie, 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 :

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 vouliez 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. Quelquefois, 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.