MySQL Manuel de Référence pour 3.23.5-alpha.

8.3 Exemples de requêtes

Voici quelques exemples de requêtes classiques avec MySQL.

Certains des exemples utilisent la table 'shop' qui contient les prix de chaque article (numéro d'objet). Supposons que chaque objet a un prix unique, et que le couple (item, trader) et une clé prmiaire pour ces lignes.

Vous pouvez créer cet exemple avec la table suivante :

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);

Les données pour l'exemple sont :

SELECT * FROM shop

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

8.3.1 Valeur maximale d'une colone

"Quel est le plus grand numéro d'objet?"

SELECT MAX(article) AS article FROM shop

+---------+
| article |
+---------+
|       4 |
+---------+

8.3.2 La ligne qui contient le maximum d'une colonne

"Retrouver le prix, le vendeur et le numéro de l'objet le plus cher du magasin"

En ANSI-SQL cela est très facilement fait avec un sous selection :

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop)

Avec MySQL (et donc, sans les sous selections), il faut le faire en deux étapes :

Retrouver la valeur maximale de la table, avec la commande SELECT.

Avec la valeur lue, créer la requêt suivante :

SELECT article, dealer, price
FROM   shop
WHERE  price=19.95

Une autre solution est de trier les objets par prix, et de lire la première ligne, avec la clause MySQL LIMIT:

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1

Note: Avec cette méthode, on ne verra qu'ne seul objet, même si il y a plusieurs objets de meme prix.

8.3.3 Maximum d'une colonne : par groupement

"Quel est le prix maximal d'un article?"

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

8.3.4 La ligne contenant le maximum d'une colonne d'un groupe

"Pour chaque article, trouver le vendeur le plus cher."

En ANSI SQL on pourrai le faire avec une sous selection, comme ceci :

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)

Avec MySQL il vaut mieux le faire en deux étapes :

Retrouver la liste des (article,prix_maxima). 8.3.4 La ligne contenant le maximum d'une colonne d'un groupe.
pour chaque article trouvé, retrouver la ligne correspondante, pour lire le nom du vendeur. price.

Cela peut se faire facilement avec une table temporaire:

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES article read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT article, dealer, price FROM shop, tmp
WHERE shop.article=tmp.articel AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

Si vous n'utislisez pas de table temporaire, il vous faut verrouiller la table.

"Est ce qu'il est impossible de faire cela avec une seule requête?"

Oui, mais en utilisant une astuce qui s'appelle : "MAX-CONCAT trick":

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

Le dernier exemple peut être fait de manière plus efficace, en effectuant la scission de la colonne au niveau du client; The last example can of course be made a bit more efficient by doing the

8.3.5 Utiliser des clés étrangères

Il n'y a pas besoin de clé étrangère pour joindre deux tables.

La seule chose que MySQL ne fait pas est de CHECK (vérifier) que les clés que vous utilisez existent vraiment dans la table que vous réféencez, et qu'il n'efface par de lignes dnas une table avec une définition de clé étrangère. Si vous utilisez vos clés de manière habituelle, cela fonctionnera parfaitement.

CREATE TABLE persons (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirts (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
    PRIMARY KEY (id)
);

INSERT INTO persons VALUES (NULL, 'Antonio Paz');

INSERT INTO shirts VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirts VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());

SELECT * FROM persons;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirts;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+

SELECT s.* FROM persons p, shirts s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+