Réduire au minimum le nombre de tuples à ramener
et ce le plus vite possible, voilà la mission du développeur
Internet (entre autres). Quel que soit le langage de script côté
serveur utilisé, l'utilisation du SQL est inévitable
lorsque l'on souhaite interroger une base de données.
Cette étape ne doit pas être négligée,
inutile en effet d'optimiser son script Php, Asp ou JSP si par la
suite la base entière est rappatriée à des
fins de traitement par le serveur web (voir
la dernière erreur de la série : "10 erreurs
à ne pas commettre en Php").
Afin de limiter les données à extraire de la base,
plusieurs commandes SQL s'ajoutent à la classique syntaxe
"SELECT... FROM... WHERE".
La commande ORDER BY
Rappelez-vous, nous terminions le
premier volet de cette série consacrée au SQL
par la commande suivante...
SELECT pp_prenom, pp_mot_clef
FROM people
ORDER BY pp_mot_clef
... ce qui avait pour effet de ramener
les champs "pp_prenom" et "pp_mot_clef" de la
table "people", le tout classé selon le champ "pp_mot_clef".
Classé oui mais dans quel ordre ? Par défaut la commande
"ORDER BY" est exécutée en mode "ASC"
pour "ASCENDING", autrement dit le classement est effectué
en ordre croissant.
Si vous souhaitez exécuter les exemples qui vont suivre
nous vous conseillons de créer chez vous la table d'exemple
reproduite ci-dessous, un simple "copier / coller sous PhpMyAdmin
suffit. Nous vous rappelons que celui-ci s'installe très
facilement ainsi que Apache et Php4 via "easyphp"
par exemple.
Voici la table sur laquelle nous allons nous appuyer pour nos requêtes
:
(Ne vous réjouissez pas trop vite, les prix sont en euros...)
ID
|
Nom
|
Prix
|
Fabricant
|
1
|
Gameboy Color
|
76
|
Nintendo
|
2
|
PS2
|
305
|
Sony
|
3
|
Dreamcast
|
152
|
Sega
|
4
|
Gameboy Advance
|
122
|
Nintendo
|
5
|
Gameboy Advance
|
100
|
Nintendo
|
Voici le "CREATE TABLE" et les "INSERT" correspondants
:
CREATE TABLE consoles (
id tinyint(3) unsigned NOT NULL auto_increment,
nom varchar(30) NOT NULL default '',
prix smallint(5) unsigned NOT NULL default '0',
fabricant varchar(20) NOT NULL default '',
PRIMARY KEY (id)
)
INSERT INTO consoles VALUES (1,'Gameboy Color',76,'Nintendo');
INSERT INTO consoles VALUES (2,'PS2',305,'Sony');
INSERT INTO consoles VALUES (3,'Dreamcast',152,'Sega');
INSERT INTO consoles VALUES (4,'Gameboy Advance',122,'Nintendo');
INSERT INTO consoles VALUES (5,'Gameboy
Advance',100,'Nintendo');
Voyons maintenant ce que donne un "ORDER BY" en mode
décroissant, le tout en révisant quelques notions
déjà abordées dans le tutoriel précédent
:
SELECT nom, prix
FROM consoles
WHERE fabricant LIKE 'N%' AND prix > 50
ORDER BY nom DESC
On obtient :
Nom
|
Prix
|
Gameboy Color
|
76
|
Gameboy Advance
|
122
|
Gameboy Advance
|
100
|
Pourquoi ce résultat ?
Nous avons demandé le nom et le prix des consoles dont le
nom du fabricant commence par la lettre "N" (rappelez-vous
le rôle du "LIKE"), avec un prix supérieur
à 50 €, le tout classé par nom, en ordre décroissant.
Les consoles retenues ont toutes un nom composé, et le premier
mot étant identique ("Gameboy"), c'est sur le second
que le tri va s'opérer : un tri alphabétiquement inversé
puisque nous sommes en mode "DESC" ("DESCENDING").
Il est aussi possible de pratiquer un "ORDER BY" sur
plusieurs champs à la fois :
SELECT id, nom, prix
FROM consoles
WHERE fabricant = 'Nintendo'
ORDER BY nom DESC, prix
On obtient :
ID
|
Nom
|
Prix
|
1
|
Gameboy Color
|
76
|
5
|
Gameboy Advance
|
100
|
4
|
Gameboy Advance
|
122
|
Encore une fois, pourquoi ?
Nous avons demandé le nom, le prix, des consoles dont le
fabricant est "Nintendo", le tout classé par nom
décroissant (c'est sur lui que s'applique le "DESC")
et par prix (par défaut "ASC").
Sans préciser le prix dans le "ORDER BY"...
SELECT id, nom, prix
FROM consoles
WHERE fabricant = 'Nintendo'
ORDER BY nom DESC
... nous aurions obtenu :
ID
|
Nom
|
Prix
|
1
|
Gameboy Color
|
76
|
4
|
Gameboy Advance
|
122
|
5
|
Gameboy Advance
|
100
|
Fonctions de calcul et commande "GROUP
BY... HAVING"
Trouver un maximum ou un minimum, calculer une moyenne, une somme,
cela n'effraie pas le langage SQL.
Voyons comment calculer le prix à payer pour obtenir un lot
défini par une Dreamcast et une PS2 :
SELECT sum(prix) AS prix_a_payer
FROM consoles
WHERE fabricant LIKE 'S%'
Nous avons ici utilisé un alias "AS", ils sont
susceptibles d'apporter plus de clarté à la requête,
nous le verrons bientôt dans un prochain tutoriel.
On obtient :
Plus simple encore : calculer le prix moyen d'une console à
partir des informations contenues dans notre base.
SELECT avg(prix) as moyenne
FROM consoles
Nous obtenons :
Faire disparaître ces zéros disgracieux ? La fonction
"Truncate(x, y)" retourne le nombre "x" tronqué
à "y" décimales :
SELECT TRUNCATE(avg(prix), 0) as moyenne
FROM consoles
Le résultat :
Voyons maintenant comment très simplement isoler la console
la plus chère de notre table "consoles" :
SELECT max(prix) as la_plus_chere
FROM consoles
Le résultat est là...
... mais on ignore à quelle console il correspond !
Cela est fâcheux, corrigeons le tir :
SELECT nom, max(prix) as la_plus_chere
FROM consoles
GROUP BY nom
ORDER BY la_plus_chere DESC
On obtient :
Nom
|
Prix
|
PS2
|
305
|
Dreamcast
|
152
|
Gameboy Advance
|
122
|
Gameboy Color
|
76
|
La fonction "max()" est une fonction d'agrégat,
c'est à dire qu'elle retourne un résultat calculée
sur un groupe de lignes. Cela n'a pas posé de problème
dans notre premier essai mais nous ne savions pas à quelle
console ce résultat correspondait.
Le rajout du champ "nom" était nécessaire,
celui de la commande "GROUP BY" également : il
permet d'effectuer les regroupements demandés.
C'est grâce à lui que seule la plus chère des
deux "Gameboy Advance" apparaît dans notre tableau.
MySQL offre une option supplémentaire et bien pratique si
vous souhaitez limiter manuellement le nombre de tuples retournés
: LIMIT 0, 1
SELECT nom, max(prix) as la_plus_chere
FROM consoles
GROUP BY nom
ORDER BY la_plus_chere DESC LIMIT 0, 1
... Ne ramène que la console la plus chère.
On peut également adjoindre au "GROUP BY" la clause
having. Celle-ci permet d'effectuer une sélection supplémentaire
portant sur le résultat de fonctions d'agrégats :
SELECT nom, max(prix) as la_plus_chere, fabricant
FROM consoles
GROUP BY nom
HAVING fabricant = 'Nintendo'
ORDER BY la_plus_chere DESC
On obtient :
Nom
|
la_plus_chere
|
fabricant
|
Gameboy Advance
|
122
|
Nintendo
|
Gameboy Color
|
76
|
Nintendo
|
Seules les consoles "Nintendo" ont été
sélectionnées. De même seule la plus chère
des deux consoles "Advance" a été retenue.
Nous terminerons ce volet par le fameux "COUNT(*)" :
SELECT COUNT(*) as nb_tuples
FROM consoles
Nous obtenons :
Ou encore :
SELECT COUNT(nom) as nb_GBA
FROM consoles
WHERE nom = 'Gameboy Advance'
Et sans surprise :
Notez qu'il existe bien d'autres fonctions "mathématiques",
la
documentation de MySQL vous les présente.
Enfin, n'hésitez pas à tester ces requêtes par
vous-même, PhpMyAdmin offre une interface très simple
pour ce type d'exercices.
|