8.4 Créer et utiliser une base de données
Si l'administrateur vous a créé une base de données pour vous, alors vous pouvez directement commencer à l'utiliser. Sinon, il vous faut la créer vous même :
mysql> CREATE DATABASE menagerie;
Sous Unix, les noms de base de données sont sensibles à la casse (contrairement aux mots clés SQL), donc il faudra faire référence à votre base de données sous le nom menagerie, et non pas Menagerie, MENAGERIE ou tout autre variante. Sous Windows, cette restriction ne s'applique pas, même si vous devez faire référence à vos bases et tables de la même manière tout au long d'une même commande).
Créer une base de données ne la selectionne pas automatiquement. Il faut le faire explicitement. Pour faire de menagerie votre base courante, il faut utiliser la commande:
mysql> USE menagerie Database changed
La base n'a besoin d'être crée qu'une seule fois, mais il faudra la sélectionner à chaque fois que vous commencerez une session mysql. Il suffira alors d'utiliser la même commande que ci-dessus. Alternativement, vous pouvez sélectionner une base dès la connexion, en passant le nom de la base après tous les paramètres de connexion : .
shell> mysql -h host -u user -p menagerie Enter password: ********
Remarquez bien que menagerie n'est pas dans votre mot de passe. Si vous voulez transmettre votre mot de passe après l'option –p, vous devez le faire sans espace entre le mot de passe et l'option : (e.g., tel que -pmypassword, mais pas -p mypassword). Cependant, mettre votre mot de passe dans la ligne de connexion n'est pas très recommandé, car cela vous rend vulnérable à tous les mouchards qui pourraient être sur votre machine.
8.4.1 Creéer une table
Créer une base de données est facile, mais, jusqu'à présent, c'est vide. La commande SHOW TABLES vous dira :
mysql> SHOW TABLES; Empty set (0.00 sec)
La partie la plus difficile est le choix de la structure de votre base de données, et des tables dont vous aurez besoin, et quelles colonnes seront nécessaires.
Vous pouvez envisager de créer une table qui créera un enregistrement pour chacun de vos animaux. Cette table portera le nom de animaux et devrait contenir au minimum le nom de l'animal. Etant donné que le nom seul n'est pas vraiment intéressant, il faudra qu'il contienne aussi d'autres informations. Par exemple, si plusieurs personnes de votre famille ont des animaux domestiques, vous voudrez garder la liste de chaque maître. Vous voudrez peut être aussi conserver des informations basiques telles que le genre ou la race.
Et l'age ? Cela pourrait être intéressant à conserver, mais ce n'est pas une bonne chose à conserver dans une base de données. En effet, l'age change tous les jours, et il faudrait changer constamment la base de données. Au contraire, il est bien mieux de conserver la date de naissance. Alors, à chaque fois que vous aurez besoins de l'age, il suffira de faire la différence entre la date du jour et la date de naissance. MySQL disposent de puissantes fonctions de calculs sur les dates. Enregistrer la date de naissance plutôt quel l'age a d'autres atouts :
Vous pourrez utiliser la base de données pour garder en mémoire les dates d'anniversaires de vos animaux (Si cela vous semble un peu idiot, remarquez bien que c'est exactement la même chose que de conserver la date d'anniversaire de vos clients, et de leur envoyer cette carte d'anniversaire à la spontanéité toute informatique).
Vous pourrez faire des calculs d'age en relation avec d'autres dates. Par exemple, si vous enregistrer la date de mort, vous pourrez facilement calculer à quel age est mort votre compagnon.
Votre imagination fertile vous permettra sûrement d'imaginer une foule d'informations utiles pour garnir la table animaux , mais les champs que nous venons d'identifier seront suffisant pour l'instant : le nom, le propriétaire, la race, le genre, la date de naissance et celle de mort.
Utilisez maintenant la fonction de création de table pour créer la votre :
mysql> CREATE TABLE animaux (nom VARCHAR(20), proprietaire VARCHAR(20),
-> espece VARCHAR(20), genre CHAR(1), naissance DATE, mort DATE);
VARCHAR est un bon choix pour le nom, le propriétaire et la race, car ces valeurs auront des longueurs variables. Les longueurs de ces colonnes n'ont pas besoin d'être toutes identiques, ni de valoir 20. Vous pouvez choisir n'importe quelle longueur entre 1 et 255, du moment que cela vous semble approprié (si vous vous trompez , vous pourrez toujours agrandir le champs avec la fonction MySQL : ALTER TABLE ).
Le genre des animaux peu prendre de nombreuses formes, comme par exemple "m" et "f", ou peut être "male" et "femelle". Le plus simple sera d'utiliser les caractères "m" et "f".
L'utilisation du type DATE pour représenter les dates de naissance naissance et de mort mort est un choix évident.
Maintenant que vous avez créer une table, , SHOW TABLES devrait être plus loquace :
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | animaux | +---------------------+
Pour vérifier que la table a été créée comme vous le désiriez, utilisez la commande DESCRIBE :
mysql> DESCRIBE animaux; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | nom | varchar(20) | YES | | NULL | | | proprietaire | varchar(20) | YES | | NULL | | | espece | varchar(20) | YES | | NULL | | | genre | char(1) | YES | | NULL | | | naissance | date | YES | | NULL | | | mort | date | YES | | NULL | | +----------------+-------------+------+-----+---------+-------+
Vous pouvez utiliser DESCRIBE à tout moment, par exemple, si vous oubliez les noms de colonnes ou leur type.
8.4.2 Charger des données dans une table
Après avoir créé votre table, il faut la remplir. La fonction LOAD DATA et INSERT remplissent cette fonction.
Supposons que les informations sur vos animaux soient décrites comme dans le tableau ci-dessous : Remaquez bien que MySQL utilise un format de date de type AAAA-MM-JJ ; qui n'est pas le format standard.)
Etant donné que vous commencez avec une table vide, le meilleur moyen de remplir cette table est de créer un fichier texte, chaque ligne contenant les informations d'un animal, puis de le charger directement dans la table avec une seule commande.
Vous créez ainsi un fichier animaux.txt' contenant un enregistrement par ligne, avec des valeurs séparées par des tabulation, et dans le même ordre que l'ordre dans lequel les colonnes ont été listées dans la commande CREATE TABLE. Pour les valeurs manquantes (comme par exemple, les genres inconnues, ou les dates de mort des animaux vivants), vous pouvez utiliser la valeur NULL . Vous la représenterez dans le texte avec \N. Par exemple, l'enregistrement de l'oiseau Whistler ressemblera à ceci :
Pour charger ce fichier `animaux.txt' dans la table animaux , utilisez la commande suivante :
mysql> LOAD DATA LOCAL INFILE "animaux.txt" INTO TABLE animaux;
Vous pourriez spécifier le type de chaque colonne et le marqueur de fin de ligne dans la commande LOAD DATA si vous le désiriez, mais les valeurs par défaut (tabulations et retour chariot) fonctionnent très bien ici.
Pour n'ajouter qu'un seul enregistrement à la fois, la fonction INSERT est plus pratique : Dans sa forme la plus simple, vous fournissez les valeurs dans l'ordre des colonnes. Par exemple, si Diane recoit un hamster du nom de Puffball, vous pourriez ajouter un nouvel enregistrement avec la commande suivante :
mysql> INSERT INTO animaux
-> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Notez bien que les chaînes et les dates sont spécifiées avec des guillemets. De la même façon, vous pouvez insérer la valeur NULL directement pour représenter une valeur manquante. N'utilisez pas \N comme pour LOAD DATA.
A partir de cet exemple, vous voyez que la commande INSERT requiert nettement plus de frappe au clavier que la fonction LOAD DATA
8.4.3 Lire des informations dans une table
La commande SELECT sert à lire des informations d'une table.
La forme générale est la suivante :
SELECT what_to_select FROM which_table WHERE conditions_to_satisfy
what_to_select indique ce que vous voulez affichier. Cela peut être une liste de champs, ou bien le joker * qui signifie ``toutes les colonnes'' which_table indique dans quelle table lire les informations. La clause WHERE est optionnelle. Si elle est présente, conditions_to_satisfy spécifie les conditions qu'une ligne doit remplir pour être retenue, et retournée.
8.4.3.1 Selection toutes les données
La forme la plus simple de SELECT permet d'obtenir la liste complète des ligne d'une table :
mysql> SELECT * FROM animaux; +----------+---------------+---------+--------+----------------+------------+ | nom | proprietaire | espece | genre | 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 pour passer en revue une table, comme par exemple, une table que vous viendriez de charger. Dans cet exemple, la table ci-dessus montre qu'il y a eu une erreur dans le fichier. Bowser semble être né après être mort ! En consultant son dossier, vous vous apercevez que sa date correcte de naissance est 1989, et non pas 1998.
Il y a au moins deux façons de corriger cette erreur :
Editez le fichier `animaux.txt' pour corriger l'erreur, puis effacer la table ,et la recharger avec la DELETE et LOAD DATA:
mysql> DELETE FROM animaux; mysql> LOAD DATA LOCAL INFILE "animaux.txt" INTO TABLE animaux;
Cependant, en faisant cela, il vous faudra aussi insérer de nouveau la fiche de Puffball.
Ou bien, corriger seulement la fiche erronée avec une commande UPDATE :
mysql> UPDATE animaux SET naissance = "1989-08-31" WHERE nom = "Bowser";
Dans cet exemple, on voit qu'il est facile de sélectionner toute la table. Mais généralement, ce n'est pas très pratique, surtout quand la table devient trop grande. En général, il s'agit de réponse à une question plus spécifique, pour laquelle il va falloir ajouter des contraintes sur les informations à retourner. Voyons maintenant quelques exemples de requêtes.
8.4.3.2 Selectioner une partie des lignes
Il est bien sûr possible de ne sélectionner quelques lignes dans une table. Mettons que vous souhaitiez vérifier que la nouvelle date de naissance de Bowser's a bien été prise en compte. Il suffit de sélectionner l'enregistrement de Bowser comme ceci :
mysql> SELECT * FROM animaux WHERE nom = "Bowser"; +--------+--------------+---------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +--------+--------------+---------+--------+----------------+------------+ | Bowser | Diane | chien | m | 1989-08-31 | 1995-07-29 | +--------+--------------+---------+--------+----------------+------------+
Le résultat confirme bien que l'année de naissance est 1989, et non plus 1998.
Les comparaisons de chaîne sont généralement insensible à la casse : on aurait plus préciser le nom "bowser", "BOWSER", etc. Le résultat aurait été le même.
Vous pouvez faire des recherches sur d'autres colonnes que nom. Par exemple, si vous voulez savoir quels animaux sont nés 1998, faites un test sur la colonne naissance :
mysql> SELECT * FROM animaux WHERE naissance >= "1998-1-1"; +----------+--------------+---------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +----------+--------------+---------+--------+----------------+------------+ | Chirpy | Gwen | oiseau | f | 1998-09-11 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------------+---------+--------+----------------+------------+
Vous pouvez aussi combiner les conditions : par exemple, pour rechercher les chiennes
mysql> SELECT * FROM animaux WHERE espece = "chien" AND genre = "f"; +----------+--------------+---------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +----------+--------------+---------+--------+----------------+------------+ | Buffy | Harold | chien | f | 1989-05-13 | NULL | +----------+--------------+---------+--------+----------------+------------+
La requête précédente utilisait l'opérateur logique AND (ET) Il y a aussi un opérateur OR (OU) :
mysql> SELECT * FROM animaux WHERE espece = "serpent" OR espece = "oiseau"; +----------+--------------+---------+--------+----------------+------------+ | nom | proprietaire | espece | genre | 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 peut être utilisés dans la même requête. C'est alors une bonne idée d'utiliser des parenthèses pour préciser les regroupements :
mysql> SELECT * FROM animaux WHERE (espece = "chat" AND genre = "m")
-> OR (espece = "chien" AND genre = "f");
+----------+--------------+---------+--------+----------------+------------+
| nom | proprietaire | espece | genre | naissance | mort |
+----------+--------------+---------+--------+----------------+------------+
| Claws | Gwen | chat | m | 1994-03-17 | NULL |
| Buffy | Harold | chien | f | 1989-05-13 | NULL |
+----------+--------------+---------+--------+----------------+------------+
8.4.3.3 Selectionner une colonne spécifique
Il se peut que vous n'ayez pas besoin de toutes les colonnes de votre table, mais juste de quelques colonnes. Il suffit alors de citer les colonnes qui vous intéressent. Par exemple, si vous ne voulez voir que les noms des animaux, avec leur date de naissance, il suffit de ne sélectionner que les colonnes nom et naissance:
mysql> SELECT nom, naissance FROM animaux; +----------+------------+ | 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 lister les propriétaires d'animaux, utilisez la requête suivante :
mysql> SELECT proprietaire FROM animaux; +---------------+ | proprietaire | +---------------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +---------------+
Cependant, vous pouvez remarquer que cette requête simple affiche le champs proprietaire de chaque ligne, ce qui conduit à avoir des redondances (comme Gwen). Pour ne les voir apparaître qu'une seule fois, il faut utiliser le mot clé DISTINCT:
mysql> SELECT DISTINCT proprietaire FROM animaux; +---------------+ | proprietaire | +---------------+ | Benny | | Diane | | Gwen | | Harold | +---------------+
Vous pouvez encore combiner une clause WHERE lors de la selection de lignes et de colonnes Par exemple, pour obtenir les dates de naissances des chiens et des chats, utilisez la requête suivante :
mysql> SELECT nom, espece, naissance FROM animaux
-> 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 |
+--------+---------+------------+
8.4.3.4 Trier les lignes
Vous avez pu remarquer que les lignes précédentes ont été affichées dans un ordre aléatoire. Comme il est plus facile d'analyser une requête dont les lignes ont été triées, il vaut mieux trier ces lignes avec la clause : ORDER BY :
Voici la liste des dates de naissances des animaux, classées par date :
mysql> SELECT nom, naissance FROM animaux 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 | +----------+------------+
Pour inverser l'ordre de tri, ajoutez le mot clé DESC (descendant) après le nom de la colonne que vous classez.
mysql> SELECT nom, naissance FROM animaux 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 faire des classements avec plusieurs critères de tri. Par exemple, pour trier les animaux pas espèce, puis par naissance pour chaque type d'animaux, utilisez la requête suivante :
mysql> SELECT nom, espece, naissance FROM animaux 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 bien que le mot clé DESC ne s'applique qu'à la colonne le précédent immédiatement (naissance); espece étant trié dans l'ordre ascendant.
8.4.3.5 Calculs sur les dates
MySQL possède de puissantes fonctions pour effectuer des calculs sur les dates, comme par exemple, calculer un age, ou extraire des parties de date.
Pour déterminer l'age de chacun des animaux, il faut calculer la différence entre la naissance et la date courante. Puis, convertir ces deux dates en jours, et diviser le tout par 365, pour avoir le nombre d'année.
mysql> SELECT nom, (TO_DAYS(NOW())-TO_DAYS(naissance))/365 FROM animaux; +----------+-----------------------------------------+ | nom | (TO_DAYS(NOW())-TO_DAYS(naissance))/365 | +----------+-----------------------------------------+ | Fluffy | 6.15 | | Claws | 5.04 | | Buffy | 9.88 | | Fang | 8.59 | | Bowser | 9.58 | | Chirpy | 0.55 | | Whistler | 1.30 | | Slim | 2.92 | | Puffball | 0.00 | +----------+-----------------------------------------+
Bien que cette requête fasse bien ce qu'on lui demande, il y a de la place pour quelques améliorations. En premier lieu, les résultats gagneraient à être classés. De plus, le titre de la colonne n'est pas très explicite.
Le premier problème peut être résolu avec une clause ORDER BY nom qui va classer par ordre alphabétique. Pour régler le problème du titre, nous allons utiliser un alias.
mysql> SELECT nom, (TO_DAYS(NOW())-TO_DAYS(naissance))/365 AS age
-> FROM animaux ORDER BY nom;
+----------+------+
| nom | age |
+----------+------+
| Bowser | 9.58 |
| Buffy | 9.88 |
| Chirpy | 0.55 |
| Claws | 5.04 |
| Fang | 8.59 |
| Fluffy | 6.15 |
| Puffball | 0.00 |
| Slim | 2.92 |
| Whistler | 1.30 |
+----------+------+
Pour trier les résultats par age plutôt que par nom nom, il suffit de le mettre dans la clause ORDER BY :
mysql> SELECT nom, (TO_DAYS(NOW())-TO_DAYS(naissance))/365 AS age
-> FROM animaux ORDER BY age;
+----------+------+
| nom | age |
+----------+------+
| Puffball | 0.00 |
| Chirpy | 0.55 |
| Whistler | 1.30 |
| Slim | 2.92 |
| Claws | 5.04 |
| Fluffy | 6.15 |
| Fang | 8.59 |
| Bowser | 9.58 |
| Buffy | 9.88 |
+----------+------+
Une requête similaire pourrait calculer l'age de mort des animaux morts. Pour cela, vous allez déterminer les animaux morts, en testant la colonne mort à NULL. Puis, pour les valeurs non-NULL, calculez l'age avec les colonnes mort et naissance:
mysql> SELECT nom, naissance, mort, (TO_DAYS(mort)-TO_DAYS(naissance))/365 AS age
-> FROM animaux WHERE mort IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| nom | naissance | mort | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5.91 |
+--------+------------+------------+------+
La requête utilise mort IS NOT NULL plutôt que mort != NULL car NULL est une valeur spéciale. Cela est expliqué plus loin. Allez 8.4.3.6 Travailler avec la valeur NULL.
Et comment rechercher les animaux dont l'anniversaire sera le mois prochain ? Pour ce genre de calculs, year et day sont inutiles, il suffit d'extraire le mois de la colonne naissance . MySQL fournit plusieurs fonctions d'extraction comme par exemple YEAR(), MONTH() et DAY(). MONTH() est le plus approprié ici. Pour voir comment cela fonction, exécutez la commande suivante, qui naissance et MONTH(naissance):
mysql> SELECT nom, naissance, MONTH(naissance) FROM animaux; +----------+------------+------------------+ | 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 dont la date de naissance est le mois prochain est facile. En supposant que nous soyons au mois d'avril. Alors, le mois est le 4, et il suffit de rechercher les animaux nés au mois de May (5), comme ceci :
mysql> SELECT nom, naissance FROM animaux WHERE MONTH(naissance) = 5; +-------+----------------+ | nom | naissance | +-------+----------------+ | Buffy | 1989-05-13 | +-------+----------------+
Il y a bien sur un cas particulier: décembre. Il ne suffit pas seulement d'ajouter 1 à numéro du mois courant et de chercher les dates d'anniversaires correspondantes, car personne ne nait au mois 13. A la place, il faut chercher les animaux qui sont nés au mois de janvier.
Vous pourriez écrire une requête qui fonctionne, quelque soit le mois courant. De cette façon, vous n'aurez pas à utiliser un numéro particulier de mois dans la requête. DATE_ADD() vous permettra d'ajouter une durée de temps à une date. Si vous ajoutez un mois à la date de NOW(), puis vous en sortez le mois avec MONTH(), le résultat sera bien le mois suivant.
mysql> SELECT nom, naissance FROM animaux
-> WHERE MONTH(naissance) = MONTH(DATE_ADD(NOW(), INTERVAL 1 MONTH));
Une autre manière de faire serait d'ajouter 1 au mois courant, puis d'utiliser la (MOD) pour ``boucler'' à la fin de l'année, et faire correspondre janvier et décembre :
mysql> SELECT nom, naissance FROM animaux
-> WHERE MONTH(naissance) = MOD(MONTH(NOW()),12) + 1;
8.4.3.6 Travailler avec la valeur NULL
La valeur NULL peut se comporter de manière surprenante si vous l'utilisez. Conceptuellement, NULL signifie ``valeur manquante '' ou `` valeur inconnue'' et il est traité de manière légèrement différente des autres valeurs. Pour tester une valeur à NULL, vous ne pouvez pas utiliser les opérateurs de comparaison habituels, tels que =, < or !=. Pour vous en convaincre, essayez la requête suivante :
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 significatif de ces comparaisons. Utilisez les opérateurs IS NULL et IS NOT NULL:
mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+
Avec MySQL, 0 signifie faux et 1 signifie vrai.
Cette gestion spéciale de NULL explique pourquoi, dans la section précédente, il était nécessaire de savoir quels animaux étaient encore vivant, en utilisant mort IS NOT NULL à la place de mort != NULL.
8.4.3.7 Recherche de valeurs
MySQL propose les méthodes de recherche standard du SQL, mais aussi les recherches à base d'expression régulière, similaire à celle utilisées dans les utilitaires Unix, tels que vi, grep et sed.
Les méthodes de recherche SQL vous permettent d'utiliser le caractère ``_'' pour remplacer n'importe quel caractère unique, et ``%'' pour remplacer n'importe quel nombre de caractères (y compris le caractère 0). Les recherches SQL sont insensibles à la casse. Reportez vous aux exemples ci-dessous. Remarquez bien que l'on n'utilise pas = ou != mais plutôt LIKE ou NOT LIKE.
Recherche des noms commençant par ``b'':
mysql> SELECT * FROM animaux WHERE nom LIKE "b%"; +--------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +--------+---------------+--------+--------+----------------+------------+ | Buffy | Harold | chien | f | 1989-05-13 | NULL | | Bowser | Diane | chien | m | 1989-08-31 | 1995-07-29 | +--------+---------------+--------+--------+----------------+------------+
Recherche des noms finissant par :``fy'':
mysql> SELECT * FROM animaux WHERE nom LIKE "%fy"; +--------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +--------+---------------+--------+--------+----------------+------------+ | Fluffy | Harold | chat | f | 1993-02-04 | NULL | | Buffy | Harold | chien | f | 1989-05-13 | NULL | +--------+---------------+--------+--------+----------------+------------+
Recherche des noms contenant ``w'':
mysql> SELECT * FROM animaux WHERE nom LIKE "%w%"; +----------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +----------+---------------+--------+--------+----------------+------------+ | Claws | Gwen | chat | m | 1994-03-17 | NULL | | Bowser | Diane | chien | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | oiseau | NULL | 1997-12-09 | NULL | +----------+---------------+--------+--------+----------------+------------+
Recherche des noms contenant exactement 5 caractères, utilisez le caractère ``_'' :
mysql> SELECT * FROM animaux WHERE nom LIKE "_____"; +----------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +----------+---------------+--------+--------+----------------+------------+ | Claws | Gwen | chat | m | 1994-03-17 | NULL | | Buffy | Harold | chien | f | 1989-05-13 | NULL | +----------+---------------+--------+--------+----------------+------------+
L'autre type de recherche disponible avec MySQL est les expression régulières. Pour utiliser ce type de recherche, il faut ajouter les mots clé REGEXP et NOT REGEXP (ou RLIKE t NOT RLIKE, qui sont des synonymes).
Les caractéristiques des expressions régulières sont :
- ``.'' remplace n'importe quel caractère qui n'apparaît qu'une fois.
- Une classe de caractères ``[...]'' remplace n'importe quel caractère qui apparaît dans les crochets. Par exemple, ``[abc]'' peut remplacer ``a'', ``b'' ou ``c''. Pour un intervalle de caractères, utilisez le tiret :. ``[a-z]'' remplace n'importe quelle lettre minuscule, et ``[0-9]'' remplace n'importe quel nombre.
- ``*'' remplace zéro ou plus occurrences du caractère le précédent immédiatement. Par exemple, ``x*'' remplace n'importe quelle nombre de ``x''. ``[0-9]*'' `'' remplace n'importe quelle nombre de chiffres, et ``.*'' remplace n'importe quelle nombre de caractères.
- Les expression régulières sont sensibles à la casse, mais vous pouvez utiliser une classe de caractères pour les rendre insensible à la casse. Par exemple, ``[aA]'' remplace n'importe quel ``a'', minuscule ou majuscule, et ``[a-zA-Z]'' remplace n'importe quelle lettre, minuscule ou majuscule.
- La recherche est positive, si elle est vérifiée à n'importe quel endroit de la valeur (en SQL, ce n'est vrai que sur la valeur entière).
- Pour contraindre une expression au début ou à la fin de la valeur, utilisez les caractères spéciaux ``^'' pour le début ou ``$'' pour la fin.
Pour illustrer le fonctionnement des expressions régulières, les requêtes précédentes ont été réécrites en utilisant les expressions régulières.
Recherche des noms commençant par ``b'': on utilise ``^'' pour indiquer le début de la valeur, et ``[bB]'' pour rechercher indifféremment, ``b'' minuscule ou majuscule.
mysql> SELECT * FROM animaux WHERE nom REGEXP "^[bB]"; +--------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +--------+---------------+--------+--------+----------------+------------+ | Buffy | Harold | chien | f | 1989-05-13 | NULL | | Bowser | Diane | chien | m | 1989-08-31 | 1995-07-29 | +--------+---------------+--------+--------+----------------+------------+
Recherche des noms finissant par :``fy'': on utilise ``$'' pour indiquer la fin de la valeur
+--------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +--------+---------------+--------+--------+----------------+------------+ | Fluffy | Harold | chat | f | 1993-02-04 | NULL | | Buffy | Harold | chien | f | 1989-05-13 | NULL | +--------+---------------+--------+--------+----------------+------------+
Recherche des noms contenant ``w'':, on utilise ``[wW]'' pour rechercher les ``w', 'minuscule ou majuscule :
mysql> SELECT * FROM animaux WHERE nom REGEXP "[wW]"; +----------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +----------+---------------+--------+--------+----------------+------------+ | Claws | Gwen | chat | m | 1994-03-17 | NULL | | Bowser | Diane | chien | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | oiseau | NULL | 1997-12-09 | NULL | +----------+---------------+--------+--------+----------------+------------+
Etant donné qu'une expression régulière est vrai si elle est vrai sur une partie d'une valeur, il n'est pas besoin de caractères spéciaux.
Recherche des noms contenant exactement 5 caractères, utilisez ``^'' et ``$'' pour indiquer le début et la fin de la chaîne, et 5 fois ``.'' pour les 5 caractères.
mysql> SELECT * FROM animaux WHERE nom REGEXP "^.....$"; +----------+---------------+--------+--------+----------------+------------+ | nom | proprietaire | espece | genre | naissance | mort | +----------+---------------+--------+--------+----------------+------------+ | Claws | Gwen | chat | m | 1994-03-17 | NULL | | Buffy | Harold | chien | f | 1989-05-13 | NULL | +----------+---------------+--------+--------+----------------+------------+
Vous auriez pu aussi utiliser l'opérateur ``{n''} `` n-fois'':
mysql> SELECT * FROM animaux WHERE nom REGEXP "^.{5}$";
+----------+---------------+--------+--------+----------------+------------+
| nom | proprietaire | espece | genre | naissance | mort |
+----------+---------------+--------+--------+----------------+------------+
| Claws | Gwen | chat | m | 1994-03-17 | NULL |
| Buffy | Harold | chien | f | 1989-05-13 | NULL |
+----------+---------------+--------+--------+----------------+------------+
8.4.3.8 Compter les lignes
Les bases de données sont souvent utilisées pour répondre aux questions du type : ``combien de fois une information est-elle enregistrée dans une table?''. Par exemple, vous pouvez souhaiter connaître le nombre d'animaux que vous avez, ou le nombre d'animaux de chaque propriétaire, ou encore toutes sortes de statistiques sur les animaux.
Pour compter le nombre total d'animaux que vous avez, il suffit de compter le nombre de ligne dans la table animaux , puisqu'il y a un enregistrement par animal. La fonction COUNT() compte le nombre de ligne non-NULL. Votre requête ressemblera alors à :
mysql> SELECT COUNT(*) FROM animaux; +----------+ | COUNT(*) | +----------+ | 9 | +----------+
Précédemment, vous avez recherché les noms des propriétaires d'animaux. Vous pouvez utiliser la fonction COUNT() pour connaître le nombre d'animaux que chaque propriétaire a :
mysql> SELECT proprietaire, COUNT(*) FROM animaux GROUP BY proprietaire; +---------------+----------+ | proprietaire | COUNT(*) | +---------------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +---------------+----------+
Remarques: l'utilisation de la clause GROUP BY qui rassemble les lignes par proprietaire. Sans cette clause, vous obtenez le message d'erreur suivant :
mysql> SELECT proprietaire, COUNT(proprietaire) FROM animaux; 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 informations dans de nombreuses situations : Les exemples suivant effectuent des statistiques sur vos animaux :
Nombre d'animaux par espèce
mysql> SELECT espece, COUNT(*) FROM animaux GROUP BY espece; +----------+----------+ | espece | COUNT(*) | +----------+----------+ | oiseau | 2 | | chat | 2 | | chien | 3 | | hamster | 1 | | serpent | 1 | +----------+----------+
Nombre d'animaux par genre:
mysql> SELECT genre, COUNT(*) FROM animaux GROUP BY genre; +-------+----------+ | genre | COUNT(*) | +-------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +-------+----------+
(Dans cette réponse, NULL indique ``genre inconnu.'')
Nombre d'animaux par espece et genre:
mysql> SELECT espece, genre, COUNT(*) FROM animaux GROUP BY espece, genre; +---------+-------+----------+ | espece | genre | 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 | +---------+-------+----------+
Il n'y a pas besoin d'utiliser la table entière avec la fonction COUNT(). Par exemple, la requête précédente effectuée sur la population de chien et de chat devient:
mysql> SELECT espece, genre, COUNT(*) FROM animaux
-> WHERE espece = "chien" OR espece = "chat"
-> GROUP BY espece, genre;
+---------+-------+----------+
| espece | genre | COUNT(*) |
+---------+-------+----------+
| chat | f | 1 |
| chat | m | 1 |
| chien | f | 1 |
| chien | m | 2 |
+---------+-------+----------+
Ou, pour avoir le nombre d'animaux par genre, et pour les espèces connues :
mysql> SELECT espece, genre, COUNT(*) FROM animaux
-> WHERE genre IS NOT NULL
-> GROUP BY espece, genre;
+---------+-------+----------+
| espece | genre | COUNT(*) |
+---------+-------+----------+
| oiseau | f | 1 |
| chat | f | 1 |
| chat | m | 1 |
| chien | f | 1 |
| chien | m | 2 |
| hamster | f | 1 |
| serpent | m | 1 |
+---------+-------+----------+
8.4.4 Utiliser plus d'une table
La table animaux contient la liste des animaux que vous avez. Vous pourriez vouloir enregistrer d'autres informations à leur sujet, telles que des évènements de leur vie, comme les visites chez le vétérinaire, ou les dates des portées de petits : vous avez besoin d'une autre table. A quoi va t elle ressembler ?
- Elle va devoir contenir les noms des animaux, pour savoir à qui c'est arrivé.
- Il faut une date pour l'événement.
- Il faut un champs de description des événements
- Il faut aussi un champs de catégorie d'événement.
Avec ces indications, la requête de CREATE TABLE va ressembler à ceci :
mysql> CREATE TABLE event (nom VARCHAR(20), date DATE,
-> type VARCHAR(15), remark VARCHAR(255));
Comme pour la table animaux table, il est plus facile de charger les premières valeurs à partir d'un fichier, dont les champs sont délimités avec des tabulations :
Chargez les informations comme ceci :
mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;
Etant donné ce que vous avez appris avec les requêtes sur la table animaux table, vous devriez être capable d'exécuter des requêtes sur la table event; les principes sont les mêmes. Mais la table event pourrait se révéler insuffisante pour répondre à vos questions.
Supposons que vous voulez avoir l'age des animaux lorsqu'ils ont eu leur portée. La table event indique quand ils ont eu leur portée, mais pour calculer l'age de la mère, il faut aussi sa date de naissance. Etant donné que cette date est stockée dans la table animaux, vous avez besoin des deux tables dans la même requête :
mysql> SELECT animaux.nom, (TO_DAYS(date) - TO_DAYS(naissance))/365 AS age, remarque
-> FROM animaux, event
-> WHERE animaux.nom = event.nom AND type = "portée";
+--------+------+-------------------------------+
| nom | age | remarque |
+--------+------+-------------------------------+
| Fluffy | 2.27 | 4 chatons, 3 femelles, 1 male |
| Buffy | 4.12 | 5 chiots, 2 femelles, 3 male |
| Buffy | 5.10 | 3 chiots, 3 femelles |
+--------+------+-------------------------------+
Il faut remarquer plusieurs choses à propos de cette requête :
- La clause
FROMest une liste contenant les noms des deux tables, car la requête clause va chercher des informations dans ces deux tables. - Lorsque vous combinez des informations entre plusieurs tables, il faut spécifier comment les lignes vont correspondre. Ici, la correspondance est simple, et basée sur la colonne
nom. La requête utilise une clauseWHEREpour rechercher et assortir les valeurs des deux tables, avec la colonnenom. - Etant donné que les deux tables ont une colonne
nomil faut préciser la table d'appartenance de ces colonnes à chaque référence. C'est facilement faisable en ajoutant simplement le nom de la table devant le nom de la colonne.
Les regroupements sont aussi possibles sur une même table. Cela revient à comparer des valeurs d'une table avec d'autres valeurs de la même table. Par exemple, pour marier vos animaux entre eux, vous pouvez faire un regroupement de la table animaux avec elle-même pour rechercher les males et femelles de la même espèce :
mysql> SELECT p1.nom, p1.genre, p2.nom, p2.genre, p1.espece
-> FROM animaux AS p1, animaux AS p2
-> WHERE p1.espece = p2.espece AND p1.genre = "f" AND p2.genre = "m";
+--------+-------+--------+-------+---------+
| nom | genre | nom | genre | espece |
+--------+-------+--------+-------+---------+
| Fluffy | f | Claws | m | chat |
| Buffy | f | Fang | m | chien |
| Buffy | f | Bowser | m | chien |
+--------+-------+--------+-------+---------+
Dans cette requête, plusieurs alias sont définis pour pouvoir faire référence à chaque instance de la table, et à la bonne colonne.
