• Facile

Mis à jour le 21/11/2013

Et si on faisait un peu de SQL ? ...

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Un petit chapitre qui commencera par faire le point sur la norme SQL dont je vous parle souvent mais que je ne vous ai jamais vraiment expliquée, et qui passera ensuite à nos premières requêtes MySQL armées de la clause FROM qui permet de récupérer (enfin !) des données qui proviennent de quelque part et non pas qu'on crée nous-mêmes dans la requête. :)

La norme SQL ?

Je vous en parle souvent, en vous disant généralement "Il faut savoir que ce n'est pas la norme, à éviter donc ...", mais au fait c'est quoi ?

Une sous-partie un peu théorique, pas forcément nécessaire mais c'est toujours bon à savoir, ce ne sera pas très détaillé, le but est juste de vous faire une idée de ce qu'est cette fameuse norme. :)

Tout d'abord il faut vous rappeler que MySQL est un programme, ou un SGBDR qui a donc été développé par des gens comme vous et moi.

Ces développeurs ne sont pas partis de rien, une étape importante lorsqu'on programme est de bien établir ce à quoi on veut arriver au final, et ce n'est franchement pas simple !

La norme SQL est en fait un document, rien de plus, qui contient toutes les fonctions qu'un SGBDR devrait implémenter, autrement dit la liste des fonctionnalités d'un SGBDR.

Bien sûr les développeurs ne sont pas obligés de suivre à la lettre ce document, c'est pourquoi plusieurs programmes faisant du SQL sont apparus. Vous connaissez déjà MySQL qui respecte une partie conséquente de cette norme (on va dire 60%), mais il en existe d'autres. Le plus connu étant certainement Oracle, très ancien et prévu pour les très gros volumes de données (plusieurs teras, c'est à dire plusieurs milliers de gigas !).

Mais on pourrait également citer PostGreSQL, que j'affecte particulièrement et qui respecte plus de 95% de cette norme !

Une norme, plusieurs documents...

Je vous ai dit que la norme est un document, ce qui est vrai mais il faut encore savoir que plusieurs versions de SQL existent, les 2 dont on parle actuellement sont les normes SQL-92 (ou SQL2) et SQL-99 (SQL3).

On parle encore beaucoup de SQL-92 puisque c'est la norme que respecte la plupart des SGBDR actuellement (ils n'ont pas encore eu le temps de s'adapter à la dernière version, tout de même relativement récente).

Vous vous demandez sans doute ce qu'apporte SQL-99, vous répondre ici serait trop long, mais en deux mots, tous les concepts de triggers, UDF, UDT, vues, schémas et autres facettes de SQL que nous n'avons pas encore abordées, mais nous le ferons en temps voulu :) (sauf pour les UDT ou User Defined Types qui ne sont pas (encore ?) gérés par MySQL).

Si vous voulez voir le document en question, je suis disposé à vous l'envoyer, contactez moi par MP, après tout vous me supportez depuis 5 chapitres, je vous dois bien ça. :)

Quelques données de test

Normalement cette sous-partie n'est pas trop dure à suivre. :p

Copiez-collez le code ci-dessous dans une console MySQL, c'est tout ! :)

-- Données de test pour le tutoriel du SdZ
-- Base de données "news_test"
-- 2 tables
CREATE DATABASE news_test;
USE news_test;
CREATE TABLE t_categorie (
cat_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
cat_nom VARCHAR(30) NOT NULL,
PRIMARY KEY ( cat_id )
) MAX_ROWS = 30;
CREATE TABLE t_news (
nws_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
nws_catid TINYINT(3) UNSIGNED NOT NULL,
nws_titre VARCHAR(100) NOT NULL,
nws_auteur VARCHAR(30) NOT NULL,
nws_date DATETIME NOT NULL,
nws_nb_vues INT(10) NOT NULL DEFAULT 0,
nws_texte TEXT NOT NULL,
PRIMARY KEY ( nws_id ),
INDEX i_nws_catid ( nws_catid )
);
CREATE TABLE t_commentaire (
cmt_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
cmt_nwsid SMALLINT(5) UNSIGNED NOT NULL,
cmt_auteur VARCHAR(30) DEFAULT NULL,
cmt_date DATETIME NOT NULL,
cmt_texte TEXT NOT NULL,
PRIMARY KEY ( cmt_id ),
INDEX i_cmt_nwsid ( cmt_nwsid )
);
INSERT INTO t_categorie ( cat_id, cat_nom ) VALUES
( 1, 'Site du Zér0' ),
( 2, 'Musique' ),
( 3, '(My)SQL' );
INSERT INTO t_news ( nws_id, nws_catid, nws_titre, nws_auteur, nws_date, nws_nb_vues, nws_texte ) VALUES
( 1, 2, 'Vive la guitare', 'Alexi Laiho', '2007-06-02 02:18:32', 837, 'Ya pas à dire, la guitare c''est vraiment le pied !!' ),
( 2, 1, 'test', 'karamilo', '2007-06-05 20:58:23', 139, 'test' ),
( 3, 1, 'test', 'karamilo', '2007-06-05 21:19:08', 42, 'test' ),
( 4, 2, 'Bientôt le Graspop', 'Alexi Laiho', '2007-06-08 18:51:26', 1235, 'Salut tout le monde, je suppose que vous êtes tous au courant du fait que cette année le Graspop accueille Children of Bodom, Dimmu Borgir, AeroSmith, Blind Guardian, Iron Maiden, Amon Amarth, ... En bref que du bon !!' ),
( 5, 1, '50 000 visiteurs', 'karamilo', '2007-06-18 23:15:18', 2154, 'Grand jour pour le SdZ: la barre des 50 000 visiteurs a été franchie aujourd''hui ! Félicitations à toute la team !' );
INSERT INTO t_news ( nws_catid, nws_titre, nws_auteur, nws_texte ) VALUES ( 1, 'Insertion des données de test', 'visiteur avide de savoir', 'tadaaaaam ! ^^' );
INSERT INTO t_commentaire ( cmt_nwsid, cmt_auteur, cmt_date, cmt_texte ) VALUES
( 1, 'Shepard', '2007-06-02 10:18:20', 'Tout à fait d''accord !! Mais comment tu fais pour te réveiller si tôt ?' ),
( 1, 'Alexi Laiho', '2007-06-02 12:11:52', 'Facile: je ne dors pas ^^' ),
( 1, 'Shepard', '2007-06-02 13:11:18', 'Et tu ne manges pas non plus ? Je savais que t''étais pas humain' ),
( 1, 'Alexi Laiho', '2007-06-02 15:18:22', ':p' ),
( 2, 'karamilo', '2007-06-05 20:59:59', 'test' ),
( 2, 'karamilo', '2007-06-05 21:13:15', 'test' ),
( 2, 'karamilo', '2007-06-05 21:18:32', 'test' ),
( 3, 'karamilo', '2007-06-05 21:19:23', 'test' ),
( 1, 'Janne Warman', '2007-06-06 23:19:08', 'Bah tu pourras le voir en chair et en os ( ou en métal et en électrons ) au Graspop le 24 :p :)' ),
( 1, 'Shepard', '2007-06-07 10:17:55', 'Non je n''y vais pas ... :( ( pas trouvé les sous :s )' ),
( 1, 'Janne Warman', '2007-06-07 18:48:15', 'Ah flûte, désolé ...' ),
( 4, 'Janne Warman', '2007-06-08 20:18:32', 'Vas t''entrainer plutôt que de te vanter et d''emmerder Shep :p' ),
( 1, 'Shepard', '2007-06-08 23:11:26', 'T''inquiète ;)' ),
( 4, 'Shepard', '2007-06-08 23:13:51', 'Il n''en a pas besoin, enfin seulement la disquette ^^' );
INSERT INTO t_commentaire ( cmt_nwsid, cmt_date, cmt_texte ) VALUES ( 5, '2007-06-19 10:18:21', 'Chapeau bas ! ^^' );
INSERT INTO t_commentaire ( cmt_nwsid, cmt_auteur, cmt_date, cmt_texte ) VALUES ( 5, 'Shepard', '2007-06-19 10:23:21', 'Je confirme :p ( comment ça moi floodeur ? )');
-- End of File ^^

N'essayez pas de comprendre le code, ce n'est pas l'objet de ce chapitre, ni même de cette partie du cours, nous verrons les requêtes de création / d'insertion plus tard. :)

Enfin une requête conforme !

Derrière ce titre un peu racoleur se cache une vérité assez frustrante : toutes les requêtes effectuées dans les chapitres précédents n'étaient pas des requêtes SQL...

[Là ça devrait être le moment où vous vous énervez sur moi, m'attachez, me versez du miel dans les oreilles et lachez les fourmis rouges...]

Pas de bol vous ne savez pas où j'habite. :p

Bon je vous explique quand même : la norme SQL dont je vous ai parlé plus tôt définit l'ordre SELECT comme ceci :

SELECT [DISTINCT|ALL] { * | liste }
FROM TABLE
[WHERE conditions]
[GROUP BY liste_groupage]
[HAVING conditions]
[ORDER BY liste_ordre]

Les éléments entre crochets sont facultatifs; les éléments entre accolades et séparés par des | sont au choix.

La chose que je voulais vous montrer, c'est que la clause FROM n'est pas facultative. :p D'où la non-validité de toutes les requêtes que nous avons exécutées jusqu'à présent. ^^

Enfin finalement ce n'est qu'un détail...

Bon je propose qu'on s'amuse un peu à faire des requêtes conformes. :p Pour voir si vous n'avez pas tout oublié du cours de M@teo21, je vous propose d'essayer de trouver les requêtes demandées par vous-mêmes puis de regarder les solutions, c'est vraiment basique ne vous en faites pas. ;)

Il n'y a jamais de WHERE ou de ORDER BY ou quoi que ce soit d'autre dans la requête, juste SELECT ... FROM ... :p

  • Récupérer toutes les colonnes de la table t_categorie

  • Récupérer les colonnes nws_id et nws_auteur de la table t_news

  • Récupérer les colonnes cmt_id et cmt_auteur de la table t_commentaire et vérifier que tous les id sont bien présents (il suffit de regarder qu'il n'y ait pas de trou, normalement il y en aura un, sauf si vous connaissez l'astuce :p )

  • Même chose qu'au dessus mais faites disparaître ce NULL et remplacez-le par "Visiteur" si vous y arrivez :)

Et maintenant les solutions :

-- Tout d'abord, se connecter à news_test
USE test_news;
-- toutes les colonnes de t_categorie
SELECT * FROM t_categorie;
-- nws_id et nws_auteur de t_news
SELECT nws_id, nws_auteur FROM t_news;
-- cmt_id et cmt_auteur de t_commentaire
SELECT cmt_id, cmt_auteur FROM t_commentaire;
-- même chose mais NULL => "Visiteur"
SELECT cmt_id, COALESCE(cmt_auteur, 'Visiteur') AS cmt_auteur FROM t_commentaire;
-- Ou, si on veut tout afficher en une requête ...
SELECT cmt_id, cmt_auteur, COALESCE(cmt_auteur, 'Visiteur') AS cmt_auteur2 FROM t_commentaire;

Le résultat de cette dernière requête en image :

Les 2 dernières requêtes demandées en une :p

(Regardez la ligne 15)

La dernière requête que je vous demandais, c'était juste pour voir si vous aviez compris le principe des fonctions et retenu l'utilité de COALESCE, j'avoue que c'était peut-être un peu sadique de ma part. :-°

Tiens, tant qu'on parle de requêtes valides, je vous ai déniché un petit validateur de requêtes (à la manière du validateur (x)HTML du W3C :p , vous tapez votre requête et... ça passe ou ça casse ^^ ).

http://developer.mimer.se/validator/

Choisissez votre version de SQL, entrez votre requête et "Test SQL" !

SELECT ... FROM ( SELECT ... FROM ... )

On va avoir besoin ici d'une petite subtilité du langage SQL :

Tout ordre SELECT renvoie une table (généralement temporaire).

Maintenant vous comprenez certainement le sens du titre de cette sous-partie. :p On peut faire un SELECT ... sur un SELECT ^^

Un exemple :

SELECT cmt_auteur FROM ( SELECT * FROM t_commentaire ) AS t

Notez que le AS t est obligatoire, sinon le client MySQL vous dira joyeusement ERROR 1248 (42000): Every derived table must have its own alias.

SELECT ... FROM ( SELECT ... FROM ) AS ...

Vous avez sans doute envie de dire "ça sert à rien" et vous avez probablement raison. Non seulement cette technique a la réputation d'être très gourmande au niveau temps d'exécution, mais en plus il y a toujours moyen de faire autrement, nous verrons cela plus tard... Si ça peut vous rassurer, je ne me suis jamais servi de cette fonctionnalité, par conséquent je ne vous en voudrai pas si lorsque je vous croise sur le forum (je vous poserai évidemment 30 questions sur l'ensemble du cours comme d'habitude), vous ne savez pas répondre à Peut-on utiliser une requête SELECT comme argument d'une clause FROM ?

Nous verrons plus tard que ce que nous avons fait s'appelle en fait une sous-requête, mais en attendant, je propose qu'on passe à quelque chose de plus intéressant. :)

DISTINCT

Plus haut je vous ai montré ceci :

SELECT [DISTINCT|ALL] { * | liste }
FROM TABLE
[WHERE conditions]
[GROUP BY liste_groupage]
[HAVING conditions]
[ORDER BY liste_ordre]

Vous vous êtes probablement posé des questions auxquelles je ne peux pas encore répondre, du moins pas à toutes, mais je peux éclaircir les deux mots-clefs juste après le SELECT: DISTINCT et ALL.

Prenons cette requête :

SELECT cmt_auteur FROM t_commentaire;

MySQL vous renvoie une liste de 16 pseudos, la plupart apparaissent au moins 2 fois, ce n'est pratique... Imaginez que votre boss vous demande une liste de tous les pseudos des membres qui ont laissé au moins un commentaire sur les news du site, vous ne pouvez pas lui remettre ça...

C'est ici, vous vous en doutiez, que DISTINCT intervient ! Il permet simplement de supprimer ce que dans le jargon, on appelle vulgairement "doublons". Ainsi, la requête suivante vous donnera une liste non-redondante :

SELECT DISTINCT cmt_auteur FROM t_commentaire;
utilisation du mot-clef DISTINCT

Voilà. :)

Et le mot-clef ALL alors ??

C'est très simple : ce mot-clef est en fait utilisé par défaut, le mettre ou ne rien mettre revient donc au même : récupérer toutes les données, même redondantes !

Voilà, un chapitre un peu plus théorique c'est certain, j'ai fait de mon mieux pour qu'il soit un minimum agréable à lire quand même, bon amusement jusqu'au prochain chapitre, on se servira des mêmes données de test pendant un certain temps, inutile donc de supprimer tout de suite la base de données "news_test". ^^ :p

Exemple de certificat de réussite
Exemple de certificat de réussite