MySQL 8 的学习——7常见查询示例

首先我们来创建一个可供测试的表

mysql> use menagerie
Database changed
mysql> create table shop(
    ->  article int(4) unsigned zerofill default '0000' not null,
    ->  dealer char(20)                  default '0000' not null,
    ->  dealer char(20)                  default '0000' not null,\c
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));
Query OK, 0 rows affected (0.10 sec)

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);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

这样我们查询一下 shop 表

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 |
+---------+--------+-------+
7 rows in set (0.00 sec)

1.列的最大值

查询最高的 article  编号

mysql> select max(article) as article from shop;
+---------+
| article |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

2.查询某列最大值的行

任务：查找最昂贵文章的编号，经销商和价格。

这可以通过子查询轻松完成：

mysql> select article,dealer,price
    -> from shop
    -> where price = (select max(price) from shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

其他解决方案是使用a LEFT JOIN或者按价格降序排序所有行，并使用特定于MySQL的LIMIT子句获取第一行：

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
## 注意：如果有几个最昂贵的文章，每个价格为19.95，LIMIT解决方案只显示其中一个。

3.每组最大列数

任务：找到每篇文章的最高价格。

mysql> 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 rows in set (0.00 sec)

4.保持某一列的分组最大值的行

任务：对于每篇文章，找到价格最贵的经销商或经销商。

这个问题可以通过像这样的子查询来解决：

mysql> select article,dealer,price
    -> from shop s1
    -> where price=(select max(s2.price)
    ->              from shop s2
    ->              where s1.article = s2.article);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)