« MySQL/Requêtes » : différence entre les versions

== SELECT ==

La syntaxe de sélection est la suivante (chaque clause fera l'objet d'un paragraphe explicatif ensuite) :

SELECT *

FROM nom_table

ORDER BY champ

LIMIT limite, taille;

 

=== Liste de champs ===

Il faut spécifier les données à récupérer avec <code>SELECT</code> :

SELECT DATABASE(); -- renvoie le nom de la base courante

SELECT CURRENT_USER(); -- l'utilisateur courant

SELECT 1+1; -- 2

 

L'étoile permet d'obtenir tous les champs d'une table :

SELECT * FROM `wiki1_page`;

Mais il est plutôt conseillé de nommer chaque champ (projection) pour accélérer la requête.

 

=== Noms des tables ===

Pour récupérer les champs d'une table ou d'une vue, il faut la placer dans la clause <code>FROM</code> :

USE wiki1;

SELECT page_id FROM `wiki1_page`; -- renvoie les valeurs du champ "page_id" de la table "wiki1_page".

 

SELECT `wiki1`.`wiki1_page`.`page_id`; -- idem

 

Autres exemples :

SELECT MAX(page_id) FROM `wiki1_page`; -- le nombre le plus élevé

SELECT page_id*2 FROM `wiki1_page`; -- le double de chaque identifiant

 

=== WHERE ===

Cette clause permet de filtrer les enregistrements. Prenons pas exemple celui ou ceux dont le champ identifiant est égal à 42 :

SELECT * FROM `wiki1_page` WHERE `page_id`=42;

 

Ou bien ceux qui ne sont pas nuls :

SELECT * FROM `wiki1_page` WHERE page_id IS NOT NULL;

 

{{attention|Il est impossible d'utiliser le résultat d'une fonction calculée dans le <code>SELECT</code> dans le <code>WHERE</code>, car ce résultat n'est trouvé qu'à la fin de l'exécution, donc <code>WHERE</code> ne peut pas s'en servir au moment prévu.

 

Par exemple, en regroupant les enregistrements de la table utilisateurs sur le champ de date d'inscription au wiki, on peut obtenir pour chacune le nombre d'édition maximum, minimum et leurs moyennes :

SELECT user_registration, MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)

FROM wiki1_user

GROUP BY `user_registration`;

 

Idem mais classé par nom et prénom d'utilisateur :

SELECT user_registration, user_real_name, MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)

FROM wiki1_user

GROUP BY `user_registration`, `user_real_name`;

 

Cette instruction permet donc de réaliser des transpositions lignes en colonnes. Par exemple pour afficher les utilisateurs connus comme ayant le même e-mail :

SELECT user_email,

MAX(CASE WHEN user_id = 1 THEN user_name ELSE NULL END) AS User1,

FROM wiki1_user

GROUP BY `user_email`;

 

{{attention|Si on place le MAX dans le CASE la transposition ne s'effectue pas.}}

 

Voici un exemple d'erreur d'optimisation classique : l'ordonnancement des opérations ne filtre le gros des résultats (valeur ''admin'') qu'à la fin de la requête (utilisant plus de mémoire, donc plus de temps qu'avec un <code>WHERE</code>) :

SELECT MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)

FROM wiki1_user

GROUP BY user_real_name

HAVING user_real_name = 'admin';

 

Par contre, cet exemple ne peut pas être optimisé car le <code>HAVING</code> utilise le résultat du <code>MAX()</code> calculé après le <code>GROUP BY</code> :

SELECT MAX(user_editcount), MIN(user_editcount), AVG(user_editcount)

FROM wiki1_user

GROUP BY user_real_name

HAVING MAX(user_editcount) > 500;

 

=== ORDER BY ===

Il est possible de classer les résultat, par ordre croissant ou décroissant, des nombres ou des lettres.

SELECT * FROM `wiki1_page` ORDER BY `page_id`;

Par défaut, l'ordre est <code>ASCENDING</code> (croissant). Pour le décroissant il faut donc préciser <code>DESCENDING</code> :

SELECT * FROM `wiki1_page` ORDER BY `page_id` ASC; -- ASC est facultatif

SELECT * FROM `wiki1_page` ORDER BY `page_id` DESC; -- ordre inversé

Les valeurs NULL sont considérées comme inférieures aux autres.

 

Il est également possible de nommer la colonne à classer par son numéro :

SELECT `page_title`, `page_id` FROM `wiki1_page` ORDER BY 1; -- nom

SELECT `page_title`, `page_id` FROM `wiki1_page` ORDER BY 2; -- id

SELECT `page_title`, `page_id` FROM `wiki1_page` ORDER BY 1 DESC;

 

Les expressions SQL sont autorisées :

SELECT `page_title` FROM `wiki1_page` ORDER BY REVERSE(`page_title`)

 

La fonction <code>RAND()</code> classe de façon aléatoire :

SELECT `page_title` FROM `wiki1_page` ORDER BY RAND()

 

Quand un <code>GROUP BY</code> est spécifié, les résultats sont classés selon les champs qui y sont nommés, sauf avant un <code>ORDER BY</code>. Donc l'ordre décroissant peut aussi être précisé depuis le <code>GROUP BY</code> :

SELECT user_registration, user_real_name, MAX(user_editcount)

FROM wiki1_user

GROUP BY `user_registration` ASC, `user_real_name` DESC;

Pour éviter ce classement automatique du <code>GROUP BY</code>, utiliser <code>ORDER BY NULL</code> :

SELECT user_registration, user_real_name, MAX(user_editcount)

FROM wiki1_user

GROUP BY `user_registration`, `user_real_name` ORDER BY NULL;

 

=== LIMIT ===

Le nombre maximum d'enregistrements dans le résultat est facultatif, on l'indique avec le mot <code>LIMIT</code> :

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10;

Ce résultat retourne donc entre 0 et 10 lignes.

 

Généralement cela s'emploie après un <code>ORDER BY</code> pour avoir les maximums et minimums, mais voici un exemple pour en avoir trois au hasard :

SELECT * FROM `wiki1_page` ORDER BY rand() LIMIT 3;

 

Il est possible de définir une plage d’enregistrements, sachant que le premier est le numéro zéro :

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10;

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 0, 10; -- synonyme

On peut donc paginer les requêtes dont les résultats peuvent saturer le serveur :

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 0, 10; -- première page

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10, 10; -- seconde page

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 20, 10; -- troisième page

La seconde commande est équivalente à celle-ci :

SELECT * FROM `wiki1_page` ORDER BY page_id LIMIT 10 OFFSET 10

 

Une astuce consiste à déboguer la syntaxe de sa requête rapidement en lui demandant un résultat vide, et observer ainsi s'il y a des messages d'erreur sans attendre :

SELECT ... LIMIT 0

 

;Conseils d'optimisation :

=== DISTINCT ===

Le mot <code>DISTINCT</code> peut être utilisé pour supprimer les doublons des lignes du résultat :

SELECT DISTINCT * FROM `wiki1_page` -- aucun doublon

SELECT DISTINCTROW * FROM `wiki1_page` -- synonyme

SELECT ALL * FROM `wiki1_page` -- doublons (comportement par défaut)

Cela permet par exemple de récupérer la liste de toutes les valeurs différentes d'un champ :

SELECT DISTINCT `user_real_name` FROM `wiki1_page` ORDER BY `user_real_name`

On peut également en sortir les différentes combinaisons de valeurs :

SELECT DISTINCT `user_real_name`, `user_editcount` FROM `wiki1_page` ORDER BY `user_real_name`

 

{{remarque|si une clé primaire ou un index unique fait partie de la sélection, le <code>DISTINCT</code> devient inutile. C'est également le cas avec <code>GROUP BY</code>.}}

===IN and NOT IN===

Équivalent du signe <code>=</code>, qui ne nécessite pas d'être répété quand il concerne plusieurs valeurs :

SELECT page_id

FROM wiki1_page

SELECT page_id

FROM wiki1_page WHERE page_id NOT IN (SELECT pp_page FROM wiki1_page_props);

 

=== EXISTS ===

Fonction disponible depuis MySQL 4.

-- N'affiche la première sélection que si la seconde n'est pas nulle

SELECT page_title

FROM wiki1_page

WHERE EXISTS (SELECT * FROM wiki1_page_props WHERE pp_propname = 'noindex');

 

=== ALL ===

-- Ne renvoie que les pages dont le numéro est le seul de la seconde sélection

SELECT page_title

FROM wiki1_page

WHERE page_id = ALL (SELECT pp_page FROM wiki1_page_props WHERE pp_propname = 'defaultsort');

 

=== UNION et UNION All===

 

La requête suivante renvoie tous les enregistrements de deux tables :

SELECT page_title FROM wiki1_page

UNION ALL

SELECT user_name FROM wiki1_user;

 

<code>UNION</code> est équivalent à <code>UNION DISTINCT</code>, ce qui le distingue de <code>UNION ALL</code> qui ne filtre pas les doublons.

SELECT page_id FROM wiki1_page

UNION

(SELECT page_id FROM wiki1_page)

ORDER BY page_id;

 

== JOIN ==

 

L'exemple suivant compare les nombres en anglais et en hindi.

CREATE TABLE english (Tag int, Inenglish varchar(255));

CREATE TABLE hindi (Tag int, Inhindi varchar(255));

INSERT INTO hindi (Tag, Inhindi) VALUES (3, 'Teen');

INSERT INTO hindi (Tag, Inhindi) VALUES (4, 'Char');

 

<table>

 

=== INNER JOIN ===

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi

FROM english, hindi

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi

FROM english INNER JOIN hindi ON english.Tag = hindi.Tag

 

<table>

 

La jointure cartésienne décrit le cas où chaque ligne d'une table est jointe à toutes celles d'une autre.

SELECT * FROM english, hindi

-- égal

SELECT * FROM english CROSS JOIN hindi

3*3 = 9 lignes :

<table>

=== USING ===

Le mot <code>USING</code> est compatible MySQL 4, mais change avec MySQL 5. La requête suivante est équivalente à celles <code>INNER JOIN</code> ci-dessus :

SELECT hindi.tag, hindi.Inhindi, english.Inenglish

FROM hindi NATURAL JOIN english

USING (Tag)

 

=== OUTER JOIN ===

SELECT hindi.Tag, english.Inenglish, hindi.Inhindi

FROM english OUTER JOIN hindi ON english.Tag = hindi.Tag

 

<table>

 

=== LEFT JOIN / LEFT OUTER JOIN ===

SELECT field1, field2 FROM table1 LEFT JOIN table2 ON field1=field2

 

LEFT JOIN hindi AS h ON e.Tag=h.Tag

WHERE h.Inhindi IS NULL

 

English tag Hindi

 

=== RIGHT OUTER JOIN ===

SELECT e.Inenglish AS English, h.tag, h.Inhindi AS Hindi

FROM english AS e RIGHT JOIN hindi AS h

ON e.Tag=h.Tag

WHERE e.Inenglish IS NULL

 

English tag Hindi

=== FULL OUTER JOIN ===

MySQL n'a pas de jointure <code>FULL OUTER JOIN</code>. Voici comment l'émuler :

(SELECT a.*, b*

FROM tab1 a LEFT JOIN tab2 b

FROM tab1 a RIGHT JOIN tab2 b

ON a.id = b.id)

 

Cette jointure permet d'ailleurs de comparer deux tables :

SELECT *

FROM table1

FULL OUTER JOIN table2 ON table2.id = table1.id

WHERE table1.id IS NULL OR table2.id IS NULL

 

=== Jointures multiples ===

Il est possible de joindre plus de deux tables :

SELECT ... FROM a JOIN (b JOIN c on b.id=c.id) ON a.id=b.id

 

Exemple :

mysql> SELECT group_type.type_id, group_type.nom, COUNT(people_job.job_id) AS count

FROM group_type

+---------+--------------------------------------+-------+

4 rows in set (0.02 sec)

 

== Sous requêtes ==

 

Par exemple, la "table" ''RepOffice = OfficeNbr from Offices'', liste les bureaux où le quota de vente excède la somme des quotas des vendeurs individuels :

SELECT ville FROM Offices WHERE Target > ???

??? est la somme des quotas des vendeurs.

SELECT SUM(Quota)

FROM SalesReps

WHERE RepOffice = OfficeNbr

 

En combinant ces deux requêtes, les points d'interrogations disparaissent :

SELECT ville FROM Offices

WHERE Target > (SELECT SUM(Quota) FROM SalesReps

WHERE RepOffice = OfficeNbr)

 

Par exemple, tous les clients avec des commandes ou limites de crédits > 50000 €. En utilisant le mot <code>DISTINCT</code> pour ne lister les clients qu'une seule fois :

SELECT DISTINCT CustNbr

FROM Customers, Orders

WHERE CustNbr = Cust AND (CreditLimit > 50000 OR Amt > 50000);

 

{{remarque|il y a donc trois types de filtre.