Vous vous demandez sans doute ce que c'est
.
En fait il s'agit simplement de variables, exactement comme dans PHP, à quelques différences près :
- Une variable commence par @ et est composée de caractères alphanumériques et de tirets bas "_" (on peut également utiliser $ et . mais je vous déconseille d'utiliser ces deux signes car ils créent des confusions).
- Les variables ne doivent pas être déclarées : elles valent NULL par défaut.
- Les variables peuvent être définies grâce à SET ou à l'aide de l'opérateur :=
- Une variable peut contenir un entier, un réel ou une chaîne de caractères.
- Toute variable est automatiquement fermée à la fin d'une session.
Il y a plusieurs façons de définir une variable, il n'y en a pas une "meilleure" que l'autre, on utilise l'une dans certains cas, et l'autre dans d'autres.
Déclarer une variable avec SET
SET permet de déclarer une variable. Par exemple :
Code : SQL
On peut également utiliser une requête comme valeur de cette variable (en fait ce qu'on va faire s'appelle une sous-requête, mais on apprendra ça un peu plus tard
).
Ne vous préoccupez pas trop de ce code, essayez juste de le comprendre, nous apprendrons plus tard la signification de chacunes de ces lignes.
Code : SQL1
2
3
4
5
6 | CREATE DATABASE tests;
USE tests;
CREATE TABLE test ( a int );
INSERT INTO test VALUES (1), (2), (3), (4), (6), (9), (19), (34), (72);
SET @a := ( SELECT COUNT(*) FROM test ); -- Définition d'une variable grâce à une sous-requête
|
Sélectionner une variable avec SELECT
Pour sélectionner une variable, rien de plus simple, il suffit de faire :
Code : SQL
Nous venons de définir deux variables : @une_variable et @a, regardons maintenant ce qu'elles contiennent ...
Code : SQL1 | SELECT @une_variable, @a;
|
Mais que vaut une variable non définie, par exemple @aetuo (pour ceux qui se demandent d'où ça vient, c'est azertyuiop en tirant une lettre sur deux
) :
Code : SQL
Comme vous le voyez, ça ne renvoie pas d'erreur, ça ne renvoie tout simplement... rien.
En fait une valeur est bien renvoyée : la valeur NULL, cette valeur est l'objet du prochain chapitre, donc je ne détaillerai pas plus ce phénomène ici.
(re)Définir une variable avec SELECT
On peut également donner une valeur à une variable grâce à SELECT (ou UPDATE ou INSERT ou DELETE mais nous ne parlerons que de SELECT, sachez juste que c'est possible).
On définit une variable grâce à l'opérateur := (comme en Delphi pour ceux qui connaissent). La plupart du temps, les variables sont définies la première fois avec SET puis manipulées avec SELECT, mais il n'est pas rare que des exceptions enfreignent cette règle.
Définissons @a à 4:
Code : SQL
Comme vous le voyez, SELECT renvoie directement la valeur vu que @a := 4 est considéré comme une colonne. On aurait très bien pu rajouter un AS pour redéfinir le nom de cette colonne.
Tiens à votre avis, est-il possible de donner à une colonne un nom contenu dans une variable ? Faisons le test :
Code : SQL1
2 | SET @nom = "colonne";
SELECT 4 AS @nom;
|
Comme vous le voyez, ça ne fonctionne malheureusement pas. C'est dommage, car ça aurait ouvert des voies pour la linéarisation de données (lignes en colonnes ou colonnes en lignes). Si toutefois quelqu'un trouve une astuce pour contourner ce problème, je lui serais fortement reconnaissant de me dire comment faire.
Le problème ...
Enormément de choses sont possibles grâce aux variables utilisateur ! Cependant la plupart des applications possibles demandent plus de connaissances que ce que vous êtes censé savoir. Il y a cependant une chose que vous devriez être capable de faire en cherchant un peu : un système de classement !
Le but ? Je vous donne une table avec des pseudos associés à des points, vous devez réussir à donner une place à chacun de ces pseudos en fonction de son nombre de points. Chaque pseudo a un nombre de points différents (pas d'égalité donc), en effet la gestion des ex-aequos rendrait l'exercice bien trop difficile pour votre niveau actuel.
Quelques indices supplémentaires :
- Pensez à ORDER BY que vous avez appris à utiliser dans le cours de M@teo.
- SET et l'opérateur := vous seront normalement nécessaires.
Voici les requêtes pour la création de la table :
Code : SQL1
2 | CREATE TABLE joueurs ( pseudo VARCHAR(30), points INT );
INSERT INTO joueurs ( pseudo, points ) VALUES ( 'Shepard', 13854 ), ( 'Alexi', 4251 ), ( 'Janne', 11245 ), ( 'Shagrath', 1248 ), ( 'Fred', 42857 );
|
Vous devez obtenir ce tableau :
Il ne me reste plus qu'à vous souhaiter bonne chance !
Une solution
En SQL, il n'y a jamais qu'un seul moyen de faire, ce qu'il y a dans la case secrète représente donc une solution, peut-être que la vôtre est radicalement différente (ça m'étonnerait un peu quand même mais bon
), mais ce qui compte à ce niveau, c'est que ça
fonctionne ! Le principal c'est d'avoir cherché et d'être tombé sur quelque chose de potable, si vous n'y êtes pas arrivé, ne vous inquiétez pas : c'était le premier et il y en aura sans doute d'autres.
Bon, assez blablaté.
Secret (cliquez pour afficher)
Code : SQL1
2 | SET @place = 0;
SELECT @place := @place + 1 AS place, pseudo, points FROM joueurs ORDER BY points DESC;
|
Comme vous le voyez, les requêtes ne sont pas longues du tout (ce n'est pas pour rien que je disais que c'était de votre niveau
), néanmoins il fallait y penser ! Je félicite ceux qui ont trouvé, mine de rien ce n'était pas si facile !
J'ai hésité à ajouter cette sous-partie à ce chapitre. En effet, elle fait appel à des notions dont vous n'avez certainement jamais entendu parler, je l'ai ajoutée quand même, dites-vous qu'il ne s'agit que d'un exemple, si vous ne comprenez pas tout à fait, ne vous inquiétez pas, on reviendra plus tard dans le cours sur tout ce qui sera montré ici.
Le problème
On a une table nommée livreor (id, auteur, message) qui contient des messages contenus dans un livre d'or, on aimerait sélectionner un message au hasard. Chaque message a un identifiant unique.
On peut distinguer deux cas à ce stade :
- Les identifiants sont continus (1, 2, 3, 4,...). Dans ce cas il est très facile de résoudre notre problème, mais en fait il est très rare que les identifiants soient continus. La plupart du temps, des messages ont été supprimés par un administrateur (messages de test lors du développement du site, messages malvenus, etc).
- On voit donc apparaître le deuxième cas : les identifiants ne sont pas continus (2, 3, 6, 8,...) dans ce cas le problème devient plus compliqué à résoudre, et c'est là que les variables de MySQL entreront en jeu.
Cas 1 : les identifiants sont continus
Code : SQL1
2
3
4
5
6
7
8 | CREATE TABLE livreor ( id INT AUTO_INCREMENT, auteur VARCHAR(30), message TEXT, PRIMARY KEY(id) );
INSERT INTO livreor ( auteur, message )
VALUES
( 'Shepard', 'Tres bon site :p' ),
( 'Alexi', 'Vive Children of Bodom !' ),
( 'Shepard', 'Entierement d''acord, Alexi :)' ),
( 'Janne', 'Vive le synthe :-°' ),
( 'Alexi','N''importe quoi ! C''est la guitare qui est mieux :D' );
|
Rien que dans ce cas-là, les variables MySQL sont déjà très utiles, je vous conseille de bien comprendre ce cas-ci car sinon vous risquez de vraiment avoir du mal pour le second...
En fait, il suffit de trouver un nombre au hasard pris entre 1 et n, n étant le nombre de messages.
On peut récupérer très facilement n grâce à la requête suivante :
Code : SQL1 | SELECT COUNT(*) FROM livreor;
|
Ensuite on va utiliser la fonction RAND() pour prendre un nombre au hasard entre 0 et n - 0.000(...)01 :
Code : SQL1 | SELECT RAND() * COUNT(*) FROM livreor;
|
Utilisons maintenant la fonction FLOOR pour arrondir à l'entier inférieur. On aura alors un nombre entre 0 et n - 1. Il faudra donc ajouter un au résultat pour avoir un nombre entre 1 et n :
Code : SQL1 | SELECT FLOOR( RAND() * COUNT(*) ) + 1 FROM livreor;
|
Il suffit maintenant de prendre le message ayant pour identifiant le résultat de cette requête :
Code : SQL1 | SELECT auteur, message FROM livreor WHERE id = FLOOR( RAND() * ( SELECT COUNT(*) FROM livreor ) ) + 1;
|
Oui mais non ! Il y a un petit problème... RAND() est réévalué à chaque ligne, ce qui fait qu'avec cette requête, on peut avoir des bizarreries de ce genre là :
Comme vous le voyez, ça ne marche pas toujours, des fois on a 3 lignes, et des fois on n'en a pas du tout !
Pour y remédier, il suffit de stocker RAND() quelque part... Où ça ? Dans une variable pardi ! Sauf qu'au lieu de juste stocker le RAND(), on va y mettre tout le FLOOR(...) + 1. Ainsi on aura un nombre entier (plus rapide à stocker) et la requête sera simplifiée :
Code : SQL1
2 | SET @id_hasard = FLOOR( RAND() * ( SELECT COUNT(*) FROM livreor ) ) + 1;
SELECT auteur, message FROM livreor WHERE id = @id_hasard;
|
Bien sûr, il faut réexécuter le SET avant chaque SELECT, sinon @id_hasard garde la même valeur et on obtient toujours le même message.
Et le tour est joué ! (J'espère que jusque là vous avez suivi parce que les choses vont se compliquer à partir de maintenant
).
Cas 2 : les identifiants sont discontinus
Code : SQL1
2
3
4
5
6
7
8
9 | DROP TABLE livreor;
CREATE TABLE livreor ( id INT AUTO_INCREMENT, auteur VARCHAR(30), message TEXT, PRIMARY KEY(id) );
INSERT INTO livreor ( id, auteur, message )
VALUES
( 2, 'Shepard', 'Tres bon site :p' ),
( 3, 'Alexi', 'Vive Children of Bodom !' ),
( 6, 'Shepard', 'Entierement d''acord, Alexi :)' ),
( 9, 'Janne', 'Vive le synthe :-°' ),
( 12, 'Alexi','N''importe quoi ! C''est la guitare qui est mieux :D' );
|
Là ça se complique un peu : si on prend un nombre au hasard entre 1 et le nombre de lignes, toutes les dernières lignes ne seront pas prises en compte et les "trous" pourraient être sélectionnés, donc ça ne va vraiment pas.
Une autre solution serait de prendre un nombre au hasard entre 1 et le dernier id de la table, puis de prendre l'id en dessous de ce nombre qui se rapproche le plus de ce nombre, mais je n'aime pas cette technique, tout d'abord parce que s'il y a des "gros" trous (suppression de 2000 messages d'affilée par exemple), le message juste avant ce trou reviendra plus souvent que les autres, et ensuite parce que cette méthode est plus compliquée que celle que je vais vous montrer.
Il y a une astuce : créer une table temporaire (donc qui ne dure que le temps de la session) avec une colonne contenant des chiffres continus (donc on retombe dans le premier cas), plus une autre colonne contenant les "vrais" id.
Pour créer cette table, rien de plus simple : on se base sur le principe du classement, sauf qu'il n'est plus nécessaire de préciser le ORDER BY :
Code : SQL1
2 | SET @place = 0;
CREATE TEMPORARY TABLE temp_livreor AS SELECT @place := @place + 1 AS place, id FROM livreor;
|
Histoire de nous assurer que tout fonctionne bien ...
Code : SQL1 | SELECT * FROM temp_livreor;
|
Remarquez qu'en général, je n'utilise jamais l'étoile qui permet de sélectionner tous les champs d'un coup, si je le fait ici, c'est parce que je veux m'assurer qu'il n'y ait rien en trop.
A partir de maintenant c'est facile : il suffit d'utiliser la même technique que tout à l'heure pour choisir une entrée de la table temp_livreor, puis de prendre le "vrai" id qui lui correspond, et d'afficher les infos correspondant à cet id.
Par exemple :
Code : SQL1
2
3 | SET @place_hasard = ( SELECT FLOOR( RAND() * ( SELECT COUNT(*) FROM temp_livreor ) ) + 1 );
SET @id_hasard = ( SELECT id FROM temp_livreor WHERE place = @place_hasard );
SELECT auteur, message FROM livreor WHERE id = @id_hasard;
|
Ou encore :
Code : SQL1
2 | SET @place_hasard = ( SELECT FLOOR( RAND() * ( SELECT COUNT(*) FROM temp_livreor ) ) + 1 );
SELECT auteur, message FROM livreor WHERE id = ( SELECT id FROM temp_livreor WHERE place = @place_hasard );
|
Il est également possible de le faire avec une jointure :
Code : SQL1
2 | SET @place_hasard = ( SELECT FLOOR( RAND() * ( SELECT COUNT(*) FROM temp_livreor ) ) + 1 );
SELECT auteur, message FROM livreor NATURAL JOIN temp_livreor WHERE place = @place_hasard;
|
La meilleure solution est la dernière, mais les deux autres sont évidemment tout à fait valables
(ne vous inquiétez pas si vous ne comprenez pas les deux dernières requêtes : c'est normal
).
Les trois techniques fonctionnent et renvoient le même message si on ne modifie pas @place_hasard (évidemment). Voici ce que ça donne dans la console :
Voilà, j'admet que cette sous-partie était ardue et impossible à comprendre en entier si vous n'aviez lu que le cours de M@teo. Je ne peux que vous conseiller de venir le relire une fois que vous saurez ce que sont les jointures, les sous-requêtes et les tables temporaires.
Flux RSS News
Twitter
Facebook