Laisser un commentaire

Maitriser MySQL en CLI

Vous n’êtes pas sans savoir que derrière chaque site web se cache une base de données. Si vous développez des sites ou applis web, que ce soit en php, Ruby ou Python, vous devriez savoir maitriser MySQL en ligne de commande. À la question, “à quoi ça sert ?”, je répondrai que phpMyAdmin n’est pas forcement installé sur le serveur que l’on doit administrer, et dans ce cas, il va falloir goûter à l’interface en ligne de commande. En outre, ce sont les mêmes commandes qui vous servent à faire les requêtes dans votre code, alors autant les apprendre ou les réviser !

Si vous n’êtes pas bien familier du SQL et que les différents types de colonnes de MySQL vous sont inconnus, vous devriez peut-être commencer par jeter un oeil à mon précédent article sur les types de champs MySQL. Sinon, on peut commencer !

Avant toute chose, on oublie assez souvent qu’un tutoriel ne peut en aucun cas se substituer à une documentation exhaustive de l’outil. Voici donc tout de suite le lien vers la doc officielle de MySQL. Nous pouvons maintenant vraiment commencer !

Note : dans les commandes SQL, beaucoup de paramètres sont facultatifs. Ceux-ci seront entourés de crochets comme ceci :

une_commande [param_facultatif [un_sous_param_facultatif]] [un_autre_param_facultatif];

C’est cette notation que vous retrouverez dans la doc officielle.

Connexion

La première chose à faire lorsque l’on veut utiliser MySQL en ligne de commande est déjà de s’y connecter. Pour ça, une fois dans votre shell, tapez, mysql -h hôte_mysql -u username -p. Dans l’exemple ci-dessous, MySQL demande ensuite le mot de passe, vous n’avez plus qu’à entrer le mot de passe avant de vous retrouver dans l’interpréteur de MySQL.

mysql -h localhost -u root -p
Enter password:

Gestion des bases

C’est super, on est connecté, mais jusque là, ça ne sert pas à grand chose. On va donc voir comment créer, modifier et supprimer des bases de données.

Créer une base

Pour créer une base, la syntaxe n’est pas bien complexe, CREATE DATABASE nom_de_la_base. Voilà, votre base est créée !

Il est possible de spécifier directement à la création de la base l’encodage de caractères que l’on veut utiliser. Pour cela, il faut ajouter des arguments à la commande de création de BDD. Par exemple, pour un encodage en UTF-8 :

CREATE DATABASE maBase DEFAULT CHARACTER SET utf8;

Il existe en plus du set de caractères, ce qu’on appelle l’interclassement, ou collation. L’interclassement précise au set de caractères les spécificités d’une langue. Par exemple, que le “E” est semblable au “e” ou au “é”. Pour en savoir plus sur les charsets et les interclassements, je vous conseille ce très bon article d’OpenClassrooms. Si on ne précise pas d’interclassement, MySQL choisi celui qui est par défaut avec le set de caractères choisi.

Voici un exemple de définition pour l’UTF-8 avec une collation unicode multilingue insensible à la casse :

CREATE DATABASE maBase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Note : sur les versions récentes de MySQL, on peut avantageusement [en] utiliser utf8mb4 comme charset et utf8mb4_unicode_ci comme collation. L’exemple précédent modifié ressemble ainsi à ça :

CREATE DATABASE maBase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

On a a peu près fait le tour de la commande de création de bases. La commande générique complète est donc la suivante :

CREATE DATABASE db_name [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]];

Modifier une base

En fait, il n’y a pas grand chose à modifier sur une base, à part le charset et la collation. Voici donc la commande :

ALTER DATABASE nom_de_la_base CHARACTER SET character_set_name COLLATE collation_name;

Supprimer une base

Maintenant, pour la supprimer, commande au combien complexe :

DROP DATABASE nom_de_la_base;

Ça va vous suivez toujours ? Non parce que sinon, faut vraiment arrêter la moquette…

Travailler dans une base

Il est possible d’avoir plusieurs bases de données avec MySQL, on doit donc lui préciser dans laquelle on va travailler. Pour cela, il faut tout simplement utiliser la commande USE nom_BDD. Et cette commande, contrairement aux autres, ne doit pas obligatoirement se terminer par un point-virgule.

Gestion des tables

On sait maintenant créer, modifier et supprimer une base, il serait temps de la peupler un peu non ?

Les moteurs de tables

MySQL permet de choisir entre différents moteurs de tables. Parmi ceux-ci, deux sont plus connus. INNODB, qui est le moteur par défaut, et MyISAM qui est un autre moteur très utilisé. Lequel choisir ? Il n’y en a pas un de meilleur que l’autre, tout dépendra de votre utilisation et de vos besoins en fonctionnalités. MyISAM permet l’index FULLTEXT (si vous ne savez pas de quoi il s’agit nous en parlerons plus tard), c’est très puissant pour effectuer des recherches dans la table, en revanche, elle ne gère pas les clefs étrangères ni les transactions (nous verrons tout ça plus tard aussi). À l’inverse, INNODB ne dispose pas du FULLTEXT mais elle sait gérer les clefs étrangères et les transactions.

Il faut aussi prendre en considération le fait que MyISAM utilise un verrou lors de l’ajout ou la modification de données, ce qui pose problème lorsque la table est très sollicitée en écriture puisque qu’il va se créer une file d’attente. De ce fait, si votre application requiert de nombreuses modifications parallèles sur la base de données – un blog avec des commentaires ou un forum par exemple – vous devrez utiliser une base INNODB.

Bien entendu, il existe d’autres moteurs de tables. Pour plus d’info sur les deux sus-cités ou sur les autres moteurs disponibles et leurs fonctionnalités, il y a toute une partie de la doc qui traite du sujet.

Créer une table

La syntaxe pour créer une table est assez courte :

CREATE TABLE nom_table;

Vous voilà avec une table toute neuve… et toute vide. Il est bien entendu possible de créer une table vide et d’ajouter des colonnes ensuite, cependant, on préférera généralement directement créer une table complète. Syntaxe :

CREATE TABLE nom_table (
    colonne_1 type de champs [NULL | NOT NULL] [AUTO_INCREMENT] [DEFAULT 'valeur'],
    [colonne_2 type de champs [NULL | NOT NULL] [AUTO_INCREMENT] [DEFAULT 'valeur']]
    …
    [PRIMARY KEY (nom_colonne)]
    [DEFAULT CHARACTER SET character_set_name [COLLATE collation_name]]
)
[ENGINE = MYISAM | INNODB];

Oui, je ne vous l’ai pas montré dans l’exemple, mais on peut aussi définir charset et collation au niveau des tables. Plus fort, on peut même les spécifier pour chaque colonne ! Au niveau de la table, l’utilisation est simple. C’est exactement la même que lorsqu’on définit au niveau de la base, en ce qui concerne les colonnes. Cette utilisation étant nettement moins fréquente, le mieux est que vous consultiez la doc au besoin.

Petit point sur la définition des colonnes : vous devez pour chaque colonne choisir parmi différents types de données. Si vous ne les connaissez pas, je vous invite à lire mon précédent article sur les types de champs MySQL. Ensuite, vous pouvez spécifier si vous autorisez les champs à avoir une valeur nulle (être vide). AUTO_INCREMENT vous permet, dans le cas d’un INT (et de ses dérivés), d’incrémenter pour chaque nouvel enregistremet la valeur de 1. Par conséquent, les valeurs des champs sont automatiquement définies. Enfin, vous pouvez précisez une valeur par défaut, c’est à dire que, si vous ne précisez aucune valeur lors de l’ajout de données la valeur du champs prendra alors comme valeur la valeur par défaut.

Vous vous demandez peut-être à quoi correspond PRIMARY KEY. Vous pouvez voir dans l’exemple ci-dessous que j’utilise une colonne id, et que je place une clef primaire sur cette colonne. En effet, si vous avez deux membres avec le même pseudo, la colonne id permettra de les différencier. Une clef primaire est une contrainte (nous verrons les contraintes un peu plus loin), elle doit être unique.

Prenons pour exemple une table dans laquelle nous allons stocker les pseudos des membres d’un site :

CREATE TABLE membres (
id SMALLINT NOT NULL AUTO_INCREMENT,
pseudo char(255) NOT NULL,
PRIMARY KEY (id)
)
ENGINE=INNODB;

Modifier une table

Pour modifier une table, on utilisera la commande ALTER TABLE avec les arguments ADD, DROP, CHANGE ou MODIFY.

-- ajouter une colonne
ALTER TABLE test
ADD nom_colonne desc [AFTER colonne];

-- supprimer une colonne
ALTER TABLE test DROP COLUMN nom;

Il est possible de préciser la position de l’insertion avec AFTER.

Pour modifier une colonne, c’est un tout petit peu plus complexe. Vous vous doutez bien qu’il faudra utiliser les commandes CHANGE et MODIFY pour modifier une colonne. Quelle est la différence entre les deux ? Pas bien grande à vrai dire. CHANGE permet de renommer la colonne tandis que MODIFY ne le permet pas. Exemples :

-- CHANGE permet de renommer une colonne
ALTER TABLE nom_table
CHANGE nom_colonne nouveau_nom_colonne TYPE [NULL|NOT NULL] [AUTO_INCREMENT];
-- ici on s'est donc contenté de changer le nom

-- MODIFY permet de faire tout le reste
MODIFY nom_colonne TYPE [NULL|NOT NULL] [AUTO_INCREMENT] [DEFAULT 'valeur par defaut']

-- CHANGE a les même fonctionnalités mais on peut renommer en même temps
CHANGE pseudo pseudo_forum VARCHAR(50) NOT NULL

Renommer une table

La commande est à un mot près, la même que pour renommer une base, de la même manière, on ne l’utilisera pas tous les jours – c’est d’ailleurs préférable – mais il peut arriver qu’on soit amené à modifier un nom de table. Voici donc la commande :

RENAME TABLE ancien_nom TO nouveau_nom;

Supprimer une table

Comme pour supprimer une base, supprimer une table s’avère assez facile. Finalement tellement facile que, comme pour une base, on a parfois trop vite fait de la supprimer. Ces commandes sont sans appel, une fois supprimées, vous ne pourrez plus récupérer vos données. Alors prenez gare !

DROP TABLE nom_table

Gestion des utilisateurs

Par défaut, lorsque l’on installe la base, il n’y qu’un seul utilisateur de créé : root – où des utilisateurs spécifiquement dédiés aux tâches de fonds. Bien qu’en développement on puisse connecter nos applications avec root, un utilisateur qui a tous les droits sur toutes les tables représente toujours un cetain danger… surtout s’il vient à être compromis !

Les bonnes pratiques en terme de sécurité consistent à créer un utilisateur par application et de ne lui attribuer que les droits dont il a strictement besoin.

Créer un utilisateur

Il suffit d’utiliser la commande suivante :

CREATE USER 'buzut'@'localhost' IDENTIFIED BY 'XXXX';

Vous devinez bien entendu que “buzut” est à remplacer par le nom d’utilisateur et que “XXXX” doit être remplacé par un mot de passe de votre choix. Néanmoins, que signifie “localhost” ? Chaque utilisateur est identifié par son nom et son mot de passe mais aussi l’hôte depuis lequel il se connecte. Ainsi, si vous voulez que l’utilisateur “buzut” puisse se connecter depuis une machine distante directement via le client MySQL (donc sans se connecter en ssh d’abord), il faudra que vous créiez un autre utilisateur avec l’ip adéquate après le “@”.

Il y a également une alternative moins sécurisée puisqu’elle permet d’autoriser tous les hôtes, il suffit pour cela de renseigner le signe pourcentage “%” à la place de l’hôte, comme ceci : ‘buzut‘@’%’.

Il peut aussi être bon de connaître la commande pour changer le mot de passe d’un utilisateur à posteriori. Notez que vous devez avoir des droits suffisants pour changer le mot de passe d’un autre utilisateur que celui avec lequel vous êtes connecté.

SET PASSWORD [FOR 'un_user'@'localhost'] = PASSWORD('nouveau_mdp');

Il est inutile de préciser FOR ‘un_user‘@’localhost’ si vous changez le mot de passe de l’utilisateur courant.

Accorder des droits

Il y a plusieurs grands types de droits, les droits d’administration, les droits de schémas et les droits de tables. Les droits d’administration s’appliquent sur toutes les bases, et, comme leurs noms l’indiquent, les droits de schémas et de tables s’appliquent respectivement à une base et à une table.

Juste avant de vous montrer comment on définit les droits, il serait peut-être bon de savoir comment on affiche les droits relatifs à un utilisateur. C’est très simple.

SHOW GRANTS FOR 'user'@'host';

Droits d’administration

Les droits d’administration s’appliquent sur toutes les bases de données :

GRANT ALL PRIVILEGES ON *.* TO 'superadmin'@'localhost';

Le tableau ci-dessous représente les différents droits d’administration.

DroitDescription
CREATE TEMPORARY TABLESCréer des tables temporaires
CREATE USERCréer, supprimer, modifier des comptes utilisateurs (CREATE, DROP, RENAME)
FILELire ou écrire des fichiers stockés sur la machine hôte du serveur MySQL (SELECT … INTO OUTFILE, LOAD DATA)
GRANT OPTIONPermet de transmettre des droits à d’autres comptes
LOCK TABLESVerrouiller les tables (LOCK TABLES)
PROCESSVoir les threads
RELOADRéinitialisation des journaux, des tables, des statistiques… (FLUSH ou RESET)
REPLICATION CLIENTSuperviser et gérer la réplication (SOW MASTER STATUS, SHOW SLAVE STATUS)
REPLICATION SLAVEUtilisé par les comptes de réplication pour récupérer les évènements du journal binaire du maitre
SHOW SCHEMAS/DATABASESVoir la liste des schémas (bases)
SHUTDOWNArrêter le serveur (mysqladmin shutdown)
SUPERExécuter diverses commandes d’administration (CHANGE MASTER TO, KILL (un autre thread que le sien), SET GLOBAL.

Droits au niveau des bases

GRANT SELECT, INSERT, UPDATE, DELETE ON buzut.* TO 'buzut_app'@'localhost';

Le tableau ci-dessous représente les différents droits s’appliquant aux bases.

DroitDescription
ALTERModifier des schémas et des tables (ALTER SCHEMA/DATABASE/TABLE)
CREATECréer des schémas et des tables (CREATE SCHEMA/DATABASE/TABLE)
CREATE TEMPORARY TABLECréer des tables temporaires (CREATE TEMPORARY TABLES)
CREATE VIEWCréer des vues (CREATE VIEW)
DELETEEffacer des enregistrements d’une table (DELETE)
DROPSupprimer des schémas ou des tables (DROP SCHEMA/DATABASE/TABLE)
EVENTProgrammer des éléments de l’event scheduler (CREATE EVENT)
GRANT OPTIONPermet de transmettre ses droits à d’autres comptes
INDEXCréer et supprimer des index (CREATE/DROP INDEX)
INSERTAjouter des enregistrements dans une table
LOCK TABLESVerrouiller les tables (LOCK TABLES)
SELECTAfficher les enregistrements d’une table et voir la structure des tables (SELECT/DESCRIBE/SHOW CREATE TABLE)
SHOW VIEWVoir le code SQL d’une vue (SHOW CREATE VIEW)
UPDATEModifier les enregistrements

Droits au niveau des tables

On descend encore ici d’un niveau. En effet, nous attribuons ici les droits au niveau des tables. Par exemple, dans la base “buzut”, nous pourrions décider que l’utilisateur “buzut_app” possède tous les droits sur users, mais seulement INSERT et SELECT sur la table “payments”, afin qu’in ne puisse pas supprimer des paiements passés.

GRANT SELECT, INSERT, UPDATE, DELETE ON buzut.users TO 'buzut_app'@'localhost';
GRANT SELECT, INSERT ON buzut.payments TO 'buzut_app'@'localhost';

Les droits sont dans l’ensemble les mêmes que ceux s’appliquant aux bases, nous retrouvons ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, UPDATE.

Le seul nouveau est TRIGGER, lequel permet de créer ou supprimer des déclencheurs (ou triggers).

Droits au niveau des colonnes

Nous arrivons à la plus faible granularité, il s’agit ici de donner les droits seulement sur certaines colonnes. Nous retrouverons seulement INSERT, SELECT et UPDATE. Les droits s’appliquent de la manière suivante :

# donne les droits en lecture et update pour nom et prénom de la table users mais seulement lecture pour le payments_id
GRANT SELECT(first_name, last_name, payments_id), UPDATE(first_name, last_name) ON buzut.users TO 'buzut_app'@'localhost';

Révoquer des droits

Donner c’est donner, reprendre c’est voler ! Mais c’est aussi plus sécurisé que de faire preuve de trop de générosité…

Pour enlever des droits précédemment GRANTed, on utilisera la commande REVOKE :

# on révoque DELETE à buzut_app sur toute la base buzut
REVOKE DELETE on buzut.* FROM 'buzut_app'@'localhost';

# on révoque SELECT et INSERT sur la table users de la base buzut
REVOKE SELECT, INSERT on buzut.users FROM 'buzut_app'@'localhost';

Gestion des données

On a une base de données, une ou plusieurs tables dans cette base, mais on voudrait maintenant travailler dans ces tables : ajouter, modifier et supprimer des données, c’est quand même le but d’une BDD !

Insertion de données

Il y a deux façons d’insérer des données :

Sans préciser les colonnes

INSERT INTO nom_table
VALUES('', '', '', …);

En précisant les colonnes

INSERT INTO nom_table (colonne1, colonne4, colonne2)
VALUES ('data_colonne1', 'data_colonne4', 'data_colonne2');

Cette manière de procéder paraît un peu plus longue à écrire, cependant, elle est bien plus claire dans la mesure où l’on voit immédiatement le nom des colonnes dans lesquelles on va insérer des données. En outre, elle est plus puissante car elle permet des insertions multiples. Voyez ci-dessous :

INSERT INTO nom_table (colonne1, colonne4, colonne2)
VALUES ('data_colonne1', 'data_colonne4', 'data_colonne2'),
       ('data2_colonne1', 'data2_colonne4', 'data2_colonne2');

Modification de données

On va utiliser la commande UPDATE pour modifier des données déjà enregistrée.

UPDATE nom_table
SET col1 = val1 [, col2 = val2, ...]
[WHERE ...];

Attention à bien préciser la clause WHERE qui permet de localiser la ligne dans laquelle vous voulez modifier une valeur. Sans elle, toutes les lignes de la colonne concernée seront modifiées ! Nous reparlerons de WHERE plus en détails juste après.

Prenons comme exemple une table qui représente notre collection de films. Cette table contient quatre colonnes : une colonne id, titre, description, format. Si on veut modifier le titre du film “avatar”, voici la requête qu’il faut :

UPDATE films
SET titre = 'Avatar 2'
WHERE titre = 'avatar';

Évidemment, on peut modifier plusieurs ligne à la fois. Mettons que l’on veuille préciser dans la description le format des films en mp4 :

UPDATE films
SET description = 'Ce film a été encodé en mp4'
WHERE format = 'mp4';

Automatiquement, toutes les descriptions des films ayant pour format mp4 seront changées. Vous comprenez donc surement l’utilité du WHERE, qui ajoute une condition. Sans cette condition, toutes les lignes sont concernées !

Suppression des données

La suppression de données est chose aisée. Pareil, utilisez bien le WHERE, aucune confirmation ne vous est demandée, et si vous avez oublié le WHERE, dites au-revoir à toutes les données de votre table !

DELETE FROM nom_table
[WHERE critères];

Je ne vous redonne pas d’exemple, le fonctionnement est exactement le même que pour UPDATE. Vous saurez vous débrouiller, j’en suis certain.

Sélection et affichage

Dans cette partie, nous allons seulement demander à MySQL de nous envoyer les données qui sont enregistrées dans la base, mais bien entendu, pas toutes les données, juste celle dont nous avons besoin. Et mine de rien, c’est là que nous allons passer le plus clair de notre temps.

La syntaxe de base est la suivante :

SELECT colonne1, [colonne2, …]
FROM nom_table
[WHERE arguments [ORDER BY DESC|ASC]] [LIMIT un_nombre [OFFSET un_nombre]]

Simple et complexe à la fois. On se rend bien compte que si on n’utilise pas trop les arguments facultatifs, finalement, c’est ultra-simple (mais pas très puissant), inversement, ça se complique avec les autres arguments (mais on grimpe en puissance). On va donc faire le tour de ces arguments, histoire d’être à l’aise avec eux.

Préciser les conditions

On a déjà brièvement vu à quoi servait WHERE et comment s’en servir. Petit rappel, WHERE permet d’ajouter une ou des conditions à la requête. Seulement jusque là, on l’a toujours utilisé avec l’opérateur =, alors que bien d’autres opérateurs existent.

OpérateurSignification
=égal
<inférieur
<=inférieur ou égal
>supérieur
>=supérieur ou égal
<> ou !=différent
<=>égal valable pour NULL

Je crois qu’une explication s’impose pour la dernière ligne du tableau. On ne peut en fait pas faire de comparaison directe avec une valeur qui est égale à NULL (absence de valeur en fait), cependant, avec l’opérateur “<=>”, on peut demander les colonnes qui n’ont pas de valeur.

Il est possible d’adjoindre des opérateurs logiques à ces opérateurs de comparaison. La combinaison des deux ajoute encore un peu de complexité (pour beaucoup de puissance supplémentaire).

OpérateurSymboleSignification
AND&&ET
OROU
XOROU exclusif
NOT!NON

Comme dans beaucoup de langages de programmation, vous pouvez utiliser indifféremment l’opérateur ou le symbole. Ainsi, WHERE id=2 OR id=6 est équivalent à WHERE id=2 || id=6.

Prenons un exemple concret, on va rester sur notre table de films, pour mémoire, elle contient les colonnes suivantes : id, titre, description, format.

-- sélection de toutes les colonnes des films au format mp4 et webm
SELECT *
FROM films
WHERE format = 'mp4' OR format = 'webm';

Pour ceux qui se demandent ce que signifie l’étoile *, elle veut dire tout. Ici, elle permet donc de sélectionner toutes les colonnes sans avoir besoin de les lister une par une. Cette notation est à utiliser avec parcimonie, et est à éviter lorsque vous n’avez pas besoin de sélectionner toutes les colonnes de votre base, surtout si celle-ci sont nombreuses. En effet, MySQL sélectionnera toutes les colonnes, ce qui ralentira potentiellement le traitement de la requête. Ainsi, lorsque vous le faites manuellement dans l’invite de commande MySQL, si la commande met une seconde à s’exécuter n’a pas grande importance, cependant, si vous écrivez ceci dans vos codes pour une application, cette dernière risque de se retrouver ralentie inutilement.

Ordonner la sélection

Il est souvent bien pratique de classer notre sélection par ordre croissant, décroissant ou alphabétique. On utilisera pour cela la commande ORDER BY. Elle n’est pas bien compliquée à utiliser, on lui dit quelle colonne servira au classement, et si on veut un ordre croissant ESC ou décroissant DESC. On reprend la requête des films pour l’exemple :

-- sélection de toutes les colonnes des films au format mp4 et webm
-- et tri alphabétique selon le titre du film
SELECT *
FROM films
WHERE format = 'mp4' OR format = 'webm'
ORDER BY titre;

-- on veut par exemple avoir en premier les derniers films ajoutés,
-- donc les ids les plus grands d'abord
SELECT *
FROM films
WHERE format = 'mp4' OR format = 'webm'
ORDER BY id DESC;

Vous remarquez que je n’ai pas précisé ESC dans le premier exemple, la valeur par défaut de ORDER BY étant l’ordonnancement alphabétique ou croissant, il est facultatif de le préciser.

Eliminer les doublons

Mettons que l’on veuille savoir tous les âges représentés par les personnes enregistrées sur notre site. Si on fait :

SELECT ages
FROM membres
ORDER BY ages;

Alors on aura 10 entrées pour l’age 14 ans, 150 pour 20 ans… Pas très pratique pour une vue d’ensemble. On peut donc utiliser DISTINCT qui, comme son nom le laisse à penser, ne sélectionne une valeur que si elle est différente d’une déjà sélectionnée.

SELECT DISTINCT ages
FROM membres
ORDER BY ages;

On aura donc la liste de tous les âges représentés par les membres de notre site, sans doublons, et ordonnée par ordre croissant (ORDER BY).

Grouper pour mieux régner

Ok normalement c’est l’inverse… Pour autant, en SQL, grouper les résultats est d’une extraordinaire puissance car c’est moins d’informations inutiles à traiter.

Admettons que nous ayons un blog avec un système de commentaire. Chaque commentaire possède notamment l’email de son auteur. Si on veut récupérer l’email de tous les auteurs, inutile d’afficher autant de lignes que de fois que chaque personne à commenté. On dira donc à MySQL de grouper le résultat par email comme ceci :

SELECT email
FROM comments
GROUP BY email;

Ça représente immédiatement un sacré gain en clarté ! Et puis si vous deviez traiter ces données avec un autre langage après, vous gagnez en concision dans votre code mais aussi en performance (MySQL n’envoie pas de données inutiles).

Pour la route, admettons que nous voulions compter le nombre de commentaires de chaque auteur ? Facile.

SELECT count(email), email
FROM comments
GROUP BY email;

COUNT() est une fonction native SQL dont nous ne parlons pas ici. MySQL propose de nombreuses fonctions très puissante et il n’est pas possible de toutes les lister. As usual, la doc est votre amie.

Prendre une petite partie de la sélection

Parfois, on ne voudra que les x premiers résultats de notre sélection. On va pour ça utiliser la commande LIMIT. Elle prend deux paramètres :

-- n'affiche que les 10 premiers résultats
SELECT pseudos
FROM table_membres
ORDER BY pseudos
LIMIT 10;

On peut aussi vouloir commencer l’affichage à partir de n ligne. On précisera donc l’OFFSET a n-1. En effet, l’OFFSET ignore les x premières lignes que vous lui passez en paramètre, et commence la sélection à la ligne suivante.

-- commence à la 5eme ligne et affiche 10 ligne, donc les ligne 5 à 15
SELECT pseudos
FROM table_membres
ORDER BY pseudos
LIMIT 10 OFFSET 4;

On peut aussi écrire la commande sans le mot-clef OFFSET, dans ce cas, on commence par préciser l’OFFSET puis, séparé par une virgule, le nombres de lignes à sélectionner :

SELECT pseudos
FROM table_membres
ORDER BY pseudos
LIMIT 4, 10;

, plus de puissance pour WHERE

LIKE permet l’utilisation de joker, comme ?, + ou * dans les expression régulières. Ces jokers sont _ et % :

-- sélectionne tous les titres qui contiennent l'expression "james bond"
SELECT titre
FROM films
WHERE titre LIKE '%james bond%';

Tous les titres contenant l’expression “james bond” seront sélectionnés car on a placé un signe % avant et après l’expression. En revanche, si on ne l’avait placé qu’après par exemple, la sélection aurait fonctionné pour un titre comme “james bond – golden eye” mais pas pour “golden eye – james bond”.

On peut aussi utiliser NOT LIKE. De plus, si on veut que la recherche soit sensible à la casse, il faut rechercher en binaire :

SELECT titre
FROM films
WHERE titre BINARY LIKE '%james bond%';

pour les intervalles

Pour sélectionner une intervalle, par exemple une date. On pourrait dire qu’il faut que ce soit plus grand que telle date, et moins que telle autre.

SELECT *
FROM membres
WHERE birthdate <= '2000' AND >= '1990';

On aurait donc les dates de naissances comprises entre 90 et 2000. Mais on peut le faire plus facilement et plus élégamment ! La commande BETWEEN est faite pour ça :

SELECT *
FROM membres
WHERE birthdate BETWEEN '2000' AND '1990';

BETWEEN marche aussi pour les lettres, dans ce cas c’est évidemment l’ordre alphabétique qui est pris en compte, en ne tenant pas compte de la casse, sauf à utiliser BINARY. Bien entendu, on peut aussi utiliser NOT BETWEEN.

en chaine c’est de l’or en barre

C’est un peu contraignant d’accumuler les OR pour préciser des critères. On a donc une écriture plus rapide pour cela :

SELECT *
FROM onsenfou
WHERE ville IN( 'madrid', 'paris', 'roma', 'kingston');

C’est plus rapide ET plus lisible. À utiliser sans modération !

Description des tables et des bases

Il peut être très utiles de décrire les tables et les bases. Par exemple, on ne se souvient plus de toutes les tables contenues dans une base, ou alors des colonnes et de leur types dans une table, il y a une solution à ça !

Pour voir toutes les tables contenues dans une base, on se place dans celle-ci (avec use database), puis :

SHOW TABLES;

# voir toutes les bases
SHOW DATABASES;

DESCRIBE Videos;
+------------------+-----------------------+------+-----+---------+----------------+
| Field            | Type                  | Null | Key | Default | Extra          |
+------------------+-----------------------+------+-----+---------+----------------+
| id               | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | char(23)              | NO   | MUL | NULL    |                |
| length           | varchar(10)           | YES  |     | NULL    |                |
| size             | smallint(5) unsigned  | YES  |     | NULL    |                |
+------------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Mais parfois, les descriptions de tables ne sont pas assez verbeuses… Si on veut par exemple connaitre le CHARSET et la collation. Voici donc la commande à utiliser :

SHOW CREATE TABLE Audios;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Audios | CREATE TABLE `Audios` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(23) COLLATE utf8_unicode_ci NOT NULL,
  `length` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `size` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=665 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

En dernier lieu, si l’on veut connaître le charset et la collation, non pas de la table, mais de la base, on peut faire une requête sur la base information_schema. Exemple :

SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "buzut";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)

SELECT default_collation_name FROM information_schema.SCHEMATA
WHERE schema_name = "buzut";
+----------------------------+
| default_collation_name     |
+----------------------------+
| utf8mb4_general_ci         |
+----------------------------+
1 row in set (0.00 sec)

Fonctions de base

Avant que nous n’entrions vraiement dans le vif du sujet, avec la création et la gestion de bases et de tables, MySQL possède quelques fonctions bien utiles.

Vous voulez connaître la version du moteur que vous utilisez ?

SELECT VERSION();
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.5.35-0ubuntu0.12.04.2-log |
+-----------------------------+
1 row in set (0.01 sec)

Connaître l’utilisateur avec lequel vous êtes connectés :

SELECT USER();
mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Récupérer le dernier ID généré avec auto_increment. Ceci est particulièrement intéressant puisqu’il arrive souvent que lorsque plusieurs tables sont liées (avec des clefs étrangères afin de faire des jointures etc), on doive récupérer l’id d’une requête pour l’insérer dans une seconde table. À cause de cela, on voit parfois des chose de ce genre :

## on insert une donnée dans la table A
INSERT INTO Table_a (ma_colonne) VALUES ('ma donnée');

## on récupère le dernier id
SELECT id FROM Table_a LIMIT 1;

## En général, dans un langage de programmation, ce résultat est stocké dans une variable, ou il peut s'agir d'une sous requête
## On insert enfin notre donnée dans la table B
INSERT INTO Table_b (ma_colonne, ma_reference_table_a) VALUES ('ma donnée', dernier_id);

Tout ceci, vous l’aurez compris n’est pas l’idéal, cela pour plusieurs raisons :

Il existe une fonction bien pratique permettant d’éviter ces problèmes : LAST_INSERT_ID(), elle retourne le dernier id inséré pour la connexion utilisée; donc si une autre donnée a été inséré entre temps, pas de problème ! Elle vous évite de faire une requête intermédiaire inutile et d’obtenir des résultats incohérents. Nos requêtes précédentes ressembleraient donc à ceci :

## on insert une donnée dans la table A
INSERT INTO Table_a (ma_colonne) VALUES ('ma donnée');

## On insert notre donnée dans la table B
INSERT INTO Table_b (ma_colonne, ma_reference_table_a) VALUES ('ma donnée', LAST_INSERT_ID());

Nous avons déjà vu pas mal de chose, on va donc s’arrêter là pour cet article. Ça vous laissera le temps de tout digérer. N’essayez pas de tout retenir d’un coup, les commandes viennent avec la pratique.

Pour aller plus loin, utiliser MySQL de manière encore plus poussée et découvrir des commandes plus complexes et puissantes, lisez la suite de cet article pour les utilisateurs avancés.

Commentaires

Rejoignez la discussion !

Vous pouvez utiliser Markdown pour les liens [ancre de lien](url), la mise en *italique* et en **gras**. Enfin pour le code, vous pouvez utiliser la syntaxe `inline` et la syntaxe bloc

```
ceci est un bloc
de code
```