| Précédent | Table des matière | Suivant |
 |  |
| Précédent | Table des matière | Suivant |
| |
Ce chapitre est une introduction à MySQL qui montre comment utiliser le client mysql pour créer et utiliser une base de données simple. mysql est un client (parfois appelée ``terminal'' ou aussi ``moniteur'') qui permet à un utilisateur de se connecter à un serveur MySQL, de lancer quelques requêtes, et de voir les résultats. mysql peut aussi être lancé en mode automatique, en lui précisant un fichier qui contient les commandes à exécuter. Cette présentation de mysql couvre les deux aspects.
Pour avoir la liste des options disponibles sur mysql, il suffit d'utiliser l'option : --help.
shell> mysql --help
Ce chapitre supposera que mysql est installé sur votre machine, et qu'un serveur MySQL est accessible. Si ce n'est pas le cas, contactez votre administrateur MySQL (Si vous etes l'administrateur, vous aurez certainement besoin de consulter d'autres sections de ce manuel).
Ce chapitre couvre la constitution et l'utilisation d'une base de données. Si vous êtes simplement interessé par la lecture de bases de données déjà existantes, vous pouvez éviter les premières sections qui montre la création d'une base de données et de tables.
Etant donné que ce chapitre n'est qu'un exemple d'introduction, de nombreux détails sont laissés de coté. N'hésitez pas à vous reportez aux autres sections du manuel, pour toute information complémentaire.
Pour se connecter au serveur MySQL, il vous faut un nom d'utilisateur, et, le plus probablement, un mot de passe. Si le serveur tourne sur une autre machine, il vous faudra aussi un nom d'hote. Contactez l'administrateur de la machine pour connaître les paramètres de connexion (i.e. le nom d'hote, le nom d'utilisateur, et le mot de passe). Une fois que vous connaîtrez tous ces paramètres, vous pourrez vous connecter comme ceci :
shell> mysql -h host -u user -p Enter password: ********
Les ******** représentent votre mot de passe : saisissez le lorsque mysql affiche the Enter password (Entrez votre mot de passe): invite.
Si tout a bien fonctionné, vous devriez voir s'afficher des informations d'introduction, suivies d'une invite de commande : mysql> prompt:
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 de commande vous indique que mysql est prêt à recevoir des commandes.
Certaines installations de MySQL permettent l'accès anonyme au serveur. Si c'est le cas de votre machine, vous devriez pouvoir vous connecter sans fournir aucune information.
shell> mysql
Après s'être correctement connecté, vous pouvez vous déconnecter à tout moment, en tapant QUIT à l'invite de mysql.
mysql> QUIT Bye
Vous pouvez aussi vous déconnecter en tapant les touches contrôle-D.
Par la suite, nous supposerons que vous vous êtes correctement connecté au serveur.
Assurez vous que vous êtes correctement connecté. Dans le cas contraire, reportez vous à la section précédente. Ce faisant, vous ne vous êtes en fait connecté à aucune base de données, mais c'est bien comme ça. A ce stade ; il est important de savoir comment envoyer une requête, avant de savoir créer une table, charger des données, et interroger la base. Cette section décrit les principes de base de saisie des commandes, en utilisant des commandes qui vous familiariseront avec le fonctionnement de mysql.
Voici une commande simple qui demande au serveur la version et la date courante. Saisissez la comme ci-dessous, puis appuyez sur la touche entrée.
mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | version() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql>
Cette première requête montre beaucoup de caractéristiques de mysql
Une commande consiste généralement d'une commande SQL, suivie d'un point-virgule (Il y a quelques exceptions, ou les point-virgules ne sont pas nécessaires, comme la commande QUIT , vue précédement. Nous y reviendrons plus loin).
Quand une requête a été saisie, mysql l'envoie au serveur pour qu'il l'exécute, puis affiche le résultat, et repropose une nouvelle invite de commande : mysql>.
Mysql> affiche la réponse du serveur sous forme de table (lignes et colonnes). La première ligne contient les titres des colonnes. Les lignes suivantes présentent les résultats de la requête. Généralement, les noms de colonnes sont les noms des colonnes des tables utilisées. Si la valeur retournée est une expression plutôt qu'une table, (comme dans l'exemple ci-dessus), mysql crée une colonne avec comme titre l'expression évaluée.
mysql affiche le nombre de ligne retourné, et le temps de traitement de la requête, ce qui donne une idée de la performance globale du serveur. Ces valeurs sont imprécises, car elle représente le temps passé entre l'envoi de la commande et la réception de la réponse, et ne montre pas quelle quantité de processeur a été utilisée. Cela ne permet pas de connaître la charge du serveur, ou les retards du réseau.
Par un souci de concision, la ligne ``rows in set'' ne sera plus affichée dans les exemples ultérieurs.
Les mots clés du langage peuvent être en majuscule ou minuscule, au choix. Les lignes suivantes sont équivalentes :
mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE;
Voici une autre requête qui montre que mysql peut être utilisé comme une simple calculatrice.
mysql> SELECT SIN(PI()/4), (4+1)*5; +-------------+---------+ | SIN(PI()/4) | (4+1)*5 | +-------------+---------+ | 0.707107 | 25 | +-------------+---------+
Les commandes que nous venons de voir sont relativement courtes, et tiennent sur une seule ligne. Il est possible de saisir plusieurs commandes sur une seule ligne, il suffit de toujours les terminer par des points-virgules.
mysql> SELECT VERSION(); SELECT NOW(); +--------------+ | version() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 1999-03-19 00:15:33 | +---------------------+
Une commande n'est pas obligatoirement sur une seule ligne : les commandes les plus longues peuvent tenir sur plusieurs lignes. Ce n'est pas un problème, car mysql détermines la fin de la commandes grâce au point-virgule, et non pas en cherchant la fin de la ligne (en d'autres termes, mysql accepte n'importe quel format de colonne, mais ne les exécute que si il trouve un point-virgule à la fin de la commande).
Voici une commande simple, et multi-lignes :
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+--------------------+--------------+
| USER() | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18 |
+--------------------+--------------+
Dans cet exemples, vous avez pu remarquer que l'invite passe de mysql> à -> dès que la commande devient multi-lignes. C'est par ce biais que mysql indique qu'il n'a pas trouvé une commande complète, et qu'il attend un complément d'information. En observant bien l'invite de commande, vous saurez toujours ce que mysql attend de vous.
Pour annuler une commande qui est partiellement saisie, il suffit de taper '\c' (slash-c)
mysql> SELECT
-> USER()
-> \c
mysql>
Ici, l'invite de commande reprend son aspect initial. Cela indique que mysql est prêt pour une nouvelle commande.
La table suivante montre les différentes formes de l'invite de commande, et sa signification :
Une commande peut s'étendre sur plusieurs lignes si, par accident, vous oubliez de terminer votre ligne par un point-virgule. Dans ce cas, mysql attend plus d'informations :
mysql> SELECT USER()
->
Si cela vous arrive (vous pensez avoir entré une commande, mais la seule réponse est cette désespérante invite -> ) ; le plus souvent mysql attends le point-virgule. Si vous ne comprenez pas que mysql attend la suite de votre commande, vous risquez d'attendre un bon moment. Il suffit alors de compléter la commande avec un point-virgule, pour valider la commande.
mysql> SELECT USER()
-> ;
+--------------------+
| USER() |
+--------------------+
| joesmith@localhost |
+--------------------+
Les formes '> et "> d'invite de commande apparaissent lors de la saisie de chaînes. Avec MySQL, vous pouvez écrire des chaînes avec les guillemets simples et doubles : ``''' ou ``"' ; 'comme par exemple 'bonjour' ou "au revoir". '> et "> signifie donc que vous avez commencé à saisir une chaîne de caractères, mais que vous n'avez pas encore fini. Si vous saisissez une chaîne de plusieurs lignes, c'est une indication judicieuse, mais est-ce souvent le cas? En général, ces deux invites de commande indiquent que vous avez oublié de refermer les guillemets :
mysql> SELECT * FROM my_table WHERE nom = "Smith AND age < 30;
">
Si vous saisissez cette commande SELECT , puis tapez ENTREE, il ne va rien se passer. Plutôt que de se demander " mais qu'est ce qui prend tant de temps ", il vaut mieux remarquer que l'invite a pris la forme particulière de "> . Cela signifie que mysql s'attend ce que vous complétiez votre chaîne et la commande. En effet, la chaîne "Smith n'a pas de deuxième guillemet.
A ce moment, que faire ? La chose la plus simplet d'annuler la commande. Cependant, vous ne pouvez pas taper \c , car mysql l'interprétera comme un caractère de chaîne. A la place, il faut clore la chaîne, puis taper \c .
mysql> SELECT * FROM my_table WHERE nom = "Smith AND age < 30;
"> "\c
mysql>
L'invite de commande redevient mysql>, ce qui indique que mysql est prêt pour une nouvelle commande.
Il est important que les invites de commande de la forme signifie '> et "> que vous n'avez pas terminé une chaîne, et que toutes les lignes suivantes seront ignorées par l'analyseur de mysql – y compris la commande QUIT! Cela peut être déroutant, surtout si vous ne savez pas qu'il faut absolument fournir un guillemet de fin, même pour annuler la saisie
Voici quelques exemples de requêtes classiques avec MySQL.
Certains des exemples utilisent la table 'shop' qui contient les prix de chaque article (numéro d'objet). Supposons que chaque objet a un prix unique, et que le couple (item, trader) et une clé prmiaire pour ces lignes.
Vous pouvez créer cet exemple avec la table 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 pour l'exemple sont :
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 | +---------+--------+-------+
"Quel est le plus grand numéro d'objet?"
SELECT MAX(article) AS article FROM shop +---------+ | article | +---------+ | 4 | +---------+
"Retrouver le prix, le vendeur et le numéro de l'objet le plus cher du magasin"
En ANSI-SQL cela est très facilement fait avec un sous selection :
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop)
Avec MySQL (et donc, sans les sous selections), il faut le faire en deux étapes :
SELECT.
SELECT article, dealer, price FROM shop WHERE price=19.95
Une autre solution est de trier les objets par prix, et de lire la première ligne, avec la clause MySQL LIMIT:
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1
Note: Avec cette méthode, on ne verra qu'ne seul objet, même si il y a plusieurs objets de meme prix.
"Quel est le prix maximal d'un 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 | +---------+-------+
"Pour chaque article, trouver le vendeur le plus cher."
En ANSI SQL on pourrai le faire avec une sous selection, comme ceci :
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
Avec MySQL il vaut mieux le faire en deux étapes :
Cela peut se faire 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 article read;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT article, dealer, price FROM shop, tmp
WHERE shop.article=tmp.articel AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;
Si vous n'utislisez pas de table temporaire, il vous faut verrouiller la table.
"Est ce qu'il est impossible de faire cela avec une seule requête?"
Oui, mais en utilisant une astuce qui s'appelle : "MAX-CONCAT trick":
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 être fait de manière plus efficace, en effectuant la scission de la colonne au niveau du client; The last example can of course be made a bit more efficient by doing the
Il n'y a pas besoin de clé étrangère pour joindre deux tables.
La seule chose que MySQL ne fait pas est de CHECK (vérifier) que les clés que vous utilisez existent vraiment dans la table que vous réféencez, et qu'il n'efface par de lignes dnas une table avec une définition de clé étrangère. Si vous utilisez vos clés de manière habituelle, cela fonctionnera parfaitement.
CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirts (
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 persons,
PRIMARY KEY (id)
);
INSERT INTO persons VALUES (NULL, 'Antonio Paz');
INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirts 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 persons;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirts;
+----+---------+--------+-------+
| 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 persons p, shirts 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 |
+----+-------+--------+-------+
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.
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.
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
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.
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.
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 |
+----------+--------------+---------+--------+----------------+------------+
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 |
+--------+---------+------------+
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.
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 Confère section 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;
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.
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 :
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 |
+----------+---------------+--------+--------+----------------+------------+
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 |
+---------+-------+----------+
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 ?
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 :
FROM est une liste contenant les noms des deux tables, car la requête clause va chercher des informations dans ces deux tables.
nom. La requête utilise une clause WHERE pour rechercher et assortir les valeurs des deux tables, avec la colonne nom.
nom il 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.
Que se passe t il si vous oubliez le nom d'une base de données, d'une table ou la structure d'une table donnée ? MySQL résoud ce problème avec plusieurs commandes qui fournissent des informations sur les bases et les tables.
Vous avez déjà rencontré SHOW DATABASES, qui liste les bases gérés par le serveur. Pour connaître la base de données courante, utilisez DATABASE():
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | menagerie | +------------+
Si vous n'avez pas de base sélectionnée, le résultat est vide.
Pour connaître les tables de la base de données courante, (par exemple, si vous n'êtes pas sur du nom d'une table), utilisez la commande suivante :
mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | event | | animaux | +---------------------+
Pour connaître les colonnes d'une table, (par exemple, si vous n'êtes pas sur du nom d'une colonne), utilisez la commande suivante :
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 | | +----------------+-------------+------+-----+---------+-------+
Field donne le nom de la colonne, Type est le type de données, Null indique si la colonne accepte la valeur NULL ou pas, Key indique que la colonne est manual_tocée, et Default indique la valeur par défaut de la colonne.
Si vous avez des manual_toc sur une table, SHOW manual_toc FROM tbl_nom fournit la liste des informations les concernant.
mysql en mode batch
Dans les sections précédentes, vous avez utilisé mysql de manière interactive pour entrer des requête et voir les résultats.Vous pouvez aussi utiliser mysql en mode batch. Pour cela, il faut mettre les commandes que vous souhaitez exécuter dans un fichier, puis indiquez à mysql qu'il faut l'utiliser comme fichier d'entrée.
shell> mysql < batch-file
Si vous devez préciser des paramètres de connexions sur la ligne de commande, elle peut ressembler à ceci :
shell> mysql -h host -u user -p < batch-file Enter password: ********
Quand vous utilisez mysql de cette façon, vous créez un fichier de script, puis exécutez ce script.
Pourquoi utiliser un script ? Voici quelques réponses :
shell> mysql < batch-file | more
shell> mysql < batch-file > mysql.out
Le format de réponse en mode batch est plus concis qu'en mode intéractif. Par exemple SELECT DISTINCT espece FROM animaux ressemble à ceci ,en mode intéractif :
+---------+ | espece | +---------+ | oiseau | | chat | | chien | | hamster | | serpent | +---------+
Mais en mode batch, il ressemble à ceci :
espece oiseau chat chien hamster serpent
Pour obtenir un format de réponse " intéractif " à partir du mode batch, utilisez l'option mysql -t. Pour avoir aussi les commandes exécutées utilisez l'option mysql -vvv.
Chez Analytikerna and Lentus (NDT : société de l'auteur), nous avons pris en charge l'étude du système et l'architecture des données pour un grand projet de recherche. Ce projet est une collaboration entre l' Institut de médecine environnementale à Karolinska Institutet, Stockholm et la Section Recherche Clinique sur l'age et la psychologie, de l'université de Californie du Sud.
Ce projet avait une grosse partie d'enquête, où tous les jumeaux suédois de plus de 65 ans étaient interviewés par téléphone. Les jumeaux qui remplissaient certains critères étaient admis dans la phase suivante de l'enquête. Dans cette deuxième phase, les jumeaux qui souhaitaient participer, recevaient la visite d'un docteur et d'une infirmière. Une partie des questionnaires étaient des examens physiques et neuropsychologiques, des tests de laboratoire, des scanners du cerveau, des analyses psychologique, et des études généalogiques. De plus, des informations étaient rassemblées sur les risques médicaux et environnementaux.
Pour plus d'information a propos de ce projet, suivez le lien suivant (en anglais) :
http://www.imm.ki.se/TWIN/TWINUKW.HTM
La deuxième partie du projet est accessible par une interface web, écrite en Perl et MySQL.
Chaque nuit, les informations des réunions étaient inséréees dans les base MySQL.
Confère section 8.7.1 Trouver tous les jumeaux non distribués
Les requêtes suivantes sont utilisées pour sélectionner les couples de jumeaux qui accèderont à la deuxième phase :
select
concat(p1.id, p1.tvab) + 0 as tvid,
concat(p1.christian_nom, " ", p1.surnom) as Nom,
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.mort_date = 0 or
(((to_days(p2.mort_date) - to_days(p2.naissanceday)) / 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 s'imposent :
concat(p1.id, p1.tvab) + 0 as tvid
On veut trier les valeurs avec la concaténation de id et tvab dans un ordre numérique. Ajouter 0 au nombre force MySQL à traiter le résultat comme un nombre
column id
Ceci identifiera un couple de jumeaux. C'est une clé commune à toutes les tables.
column tvab
Cette colonne identifie un des jumeaux dans un couple. Il prend la valeur de 1 ou 2.
column ptvab
C'est le complémentaire de la colonne précédente. Quand tvab vaut 1 celle-ci vaut 2, et vice versa. Elle sert à éviter des saisie, et permet à MySQL d'optimiser la requête.
Cette requête démontre, entre autres choses, comment faire des recherches dans une table à partir de la même table, grâce à un regroupement ( (p1 et p2). Dans l'exemple ci-dessus, on s'en sert pour vérifier si le deuxième jumeau n'est pas mort avant l'age de 65 ans. Dans ce cas, la ligne n'est pas renvoyée.
Toutes les informations ci-dessus existent dans les tables sur les jumeaux. Il y a toujours une clé sur id,tvab (toutes tables) et id,ptvab (person_data) pour rendre les requêtes plus rapides.
Sur notre serveur de production (une station Sun UltraSPARC 200MHz), cettre requête retourne entre 150 et 200 lignes, et prend moins d'une seconde.
Le nombre courant de ligne dans les tables sont les suivants :
Chaque interview est conclu par un status appelé event. La requête ci-dessous est utilisée pour créer une table avec toutes les paires de jumeaux. Elle indique aussi le nombre de couples qui ont terminé les entretiens, les couples où un seul jumeau a été interrogé, les couples qui ont refusé, etc…
select
t1.event,
t2.event,
count(*)
from
lentus as t1,
lentus as t2,
twin_project as tp
where
/* We are looking at one pair at a time */
t1.id = tp.id
and t1.tvab=tp.tvab
and t1.id = t2.id
/* Just the sceening survey */
and tp.survey_no = 5
/* This makes each pair only appear once */
and t1.tvab='1' and t2.tvab='2'
group by
t1.event, t2.event;
 | Table des matières |  |
| Précédent |  | Suivant |