Tutoriel de démarrage MySQL 7 - Exemples de requêtes de base de données courantes

Voici quelques exemples d'utilisation de MySQL pour résoudre certains problèmes courants.

Dans certains exemples, la table de base de données "shop" est utilisée pour stocker le prix de chaque article (numéro d'article) d'un certain commerçant (revendeur). Supposons que chaque commerçant a un prix fixe pour chaque article, alors (article, commerçant) est la clé primaire de l'enregistrement.

Démarrez l'outil de ligne de commande mysql et sélectionnez la base de données :

shell> mysql your-database-name

(dans la plupart des MySQL, vous pouvez utiliser la base de données de test).

Vous pouvez créer un exemple de tableau en utilisant l'instruction suivante :

mysql> 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)); mysql> 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);

Après avoir exécuté l'instruction, le tableau doit contenir les éléments suivants :

mysql> 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 |
+---------+--------+-------+

1. valeur de la colonne

« Quel est le plus grand numéro d'élément ? »

SELECT MAX(article) AS article FROM shop;

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

2. La ligne avec la valeur maximale d'une colonne

Tâche : Rechercher le numéro d'article le plus cher, le vendeur et le prix. C'est facile à faire avec une sous-requête :

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

Une autre solution consiste à trier toutes les lignes par prix par ordre décroissant et à n'obtenir que la première ligne avec un MySQLspécifique Clause LIMIT :

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

Remarque : S'il y a plusieurs articles les plus chers (par exemple, le prix de chacun est de 19,95), la solution LIMIT n'en affichera qu'un seul !

3. Valeur maximale de la colonne : par groupe

Tâche : Quel est le prix maximum de chaque 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 |
+---------+-------+

4. La ligne avec la valeur inter-groupe maximale d'un certain champ

Tâche : Pour chaque article, trouver le distributeur de l'article le plus cher entreprise.

Vous pouvez résoudre ce problème avec une sous-requête comme celle-ci :

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

5. Utilisation des variables utilisateur

Vous pouvez effacer les variables utilisateur MySQL pour enregistrer le. résultats sans avoir à les enregistrer dans des variables temporaires côté client.

Par exemple, pour connaître l'article avec le prix le plus élevé ou le plus bas, la méthode est la suivante :

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; +---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

Utilisation de clés étrangères

Dans MySQL, table InnoDB. prend en charge les vérifications des contraintes de mots clés externes.

Lors de la jointure de deux tables, aucun mot-clé externe n'est nécessaire. Pour les tables autres que les types InnoDB, des mots-clés externes peuvent être utilisés lors de la définition de colonnes à l'aide de la clause REFERENCES tbl_name(col_name) Cette clause n'a aucun effet réel et est uniquement utilisée. sous forme de mémo ou de commentaire pour vous rappeler que la colonne que vous êtes en train de définir pointe vers une colonne d'un autre tableau. Lors de l'exécution de cette instruction, il est important d'implémenter ce qui suit :

· MySQL n'effectue pas d'actions dans la table tbl_name, par exemple en réponse à des actions sur les lignes de la table que vous sont en train de définir. Supprime la ligne ; en d'autres termes, cette syntaxe ne provoque pas de comportement ON DELETE ou ON UPDATE (si vous écrivez une clause ON DELETE ou ON UPDATE dans la clause REFERENCES, elle sera ignorée).

· Cette syntaxe crée une colonne mais ne crée aucun index ni mot-clé.

· Si vous utilisez cette syntaxe pour définir une table InnoDB, une erreur se produira.

Vous pouvez utiliser des colonnes créées comme colonnes de jointure comme ceci :

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

CREATE TABLE shirt (
    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 person(id),
    PRIMARY KEY (id)
);

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

SELECT @last := LAST_INSERT_ID();

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

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

SELECT @last := LAST_INSERT_ID();

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

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

SELECT * FROM shirt;

+----+---------+--------+-------+| 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 person p, shirt sWHERE p.name LIKE &#39;Lilliana%&#39;AND s.owner = p.idAND s.color <> &#39;white&#39;;
+----+-------+--------+-------+| id | style | color  | owner |+----+-------+--------+-------+|  4 | dress | orange |     2 ||  5 | polo  | red    |     2 ||  6 | dress | blue   |     2 |+----+-------+--------+-------+

Utilisée de cette façon, la clause REFERENCES n'apparaîtra pas dans SHOW CREATE TABLE ou DESCRIBE dans la sortie :

SHOW CREATE TABLE shirt\G
*************************** 1. row ***************************
Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL auto_increment,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Dans les définitions de colonnes, l'utilisation de REFERENCES comme commentaire ou "indice" de cette manière s'applique aux tables MyISAM et BerkeleyDB.

7. La recherche basée sur deux clés

peut utiliser pleinement la clause OR en utilisant un seul mot-clé, tout comme AND.

Un exemple plus flexible consiste à trouver deux mots-clés combinés par OU :

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

Cette situation a été optimisée.

Vous pouvez également utiliser UNION pour combiner la sortie de deux instructions SELECT distinctes afin de résoudre ce problème plus efficacement.

Chaque SELECT ne recherche qu'un seul mot-clé et peut être optimisé :

SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

8. Calculer les visites en fonction des jours

L'exemple suivant montre comment utiliser la fonction des groupes de bits. pour calculer le nombre de jours de chaque mois pendant lesquels un utilisateur visite une page Web.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);

Le tableau d'exemple contient les valeurs année-mois-jour qui représentent la visite de l'utilisateur sur la page Web. La requête suivante peut être utilisée pour déterminer le nombre de jours visités par mois :

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

retournera :

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

Cette requête compte les différents jours combinés par année/mois dans le table, les requêtes en double peuvent être automatiquement supprimées.

9. Utilisez AUTO_INCREMENT

pour générer un identifiant unique pour la nouvelle ligne via l'attribut AUTO_INCREMENT :

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

renverra :

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Vous pouvez utiliser la fonction SQL LAST_INSERT_ID() ou la fonction API C mysql_insert_id() pour interroger la dernière valeur AUTO_INCREMENT. Ces fonctions sont spécifiques à la connexion, leurs valeurs de retour ne sont donc pas affectées par d'autres connexions exécutant des fonctions d'insertion.

Remarque : Pour les insertions multi-lignes, LAST_INSERT_ID() et mysql_insert_id() renvoient en fait le mot-clé AUTO_INCREMENT de la première ligne insérée. Dans une configuration de réplication, cette fonction permet de répliquer correctement les insertions multi-lignes sur d'autres serveurs.

Pour les tables MyISAM et BDB, vous pouvez spécifier AUTO_INCREMENT et les index multi-colonnes dans la deuxième colonne. À l'heure actuelle, la méthode de calcul de la valeur générée par la colonne AUTO_INCREMENT est : MAX(auto_increment_column) 1 WHERE prefix=given-prefix. Utilisez cette méthode si vous souhaitez placer les données dans des groupes triés.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

reviendra :

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

请注意在这种情况下（AUTO_INCREMENT列是多列索引的一部分），如果你在任何组中删除有最大AUTO_INCREMENT值的行，将会重新用到AUTO_INCREMENT值。对于MyISAM表也如此,对于该表一般不重复使用AUTO_INCREMENT值。

如果AUTO_INCREMENT列是多索引的一部分，MySQL将使用该索引生成以AUTO_INCREMENT列开始的序列值。。例如，如果animals表含有索引PRIMARY KEY (grp, id)和INDEX(id)，MySQL生成序列值时将忽略PRIMARY KEY。结果是，该表包含一个单个的序列，而不是符合grp值的序列。

要想以AUTO_INCREMENT值开始而不是1，你可以通过CREATE TABLE或ALTER TABLE来设置该值，如下所示:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

 以上就是MySQL入门教程7 —— 常用数据库查询的示例的内容，更多相关内容请关注PHP中文网（www.php.cn）！