JEE Study|JAVA EE|企业级开发学习网

标题: MySQL8.0大师之路：第9章：MySQL教程-9.9 MySQL常用查询 [打印本页]

作者: JeeStudy 时间: 2020-4-4 20:49
标题: MySQL8.0大师之路：第9章：MySQL教程-9.9 MySQL常用查询
1.准备数据
CREATE TABLE shop (
article INT UNSIGNED DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DECIMAL(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);

2.根据article排序
mysql> SELECT * FROM shop ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    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 |
+---------+--------+-------+
7 rows in set (0.00 sec)

3.查询article最大值
mysql> SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|    4 |
+---------+
1 row in set (0.00 sec)

4.查询价格最高的数据
mysql> SELECT article, dealer, price
-> FROM shop
-> WHERE price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    4 | D    | 19.95 |
+---------+--------+-------+
1 row in set (0.01 sec)

另外两种写法：
mysql> SELECT s1.article, s1.dealer, s1.price
-> FROM shop s1
-> LEFT JOIN shop s2 ON s1.price < s2.price
-> WHERE s2.article IS NULL;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    4 | D    | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> SELECT article, dealer, price
-> FROM shop
-> ORDER BY price DESC
-> LIMIT 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    4 | D    | 19.95 |
+---------+--------+-------+
1 row in set (0.01 sec)

5.查询相同的article，不同的dealer，价格最高的数据
mysql> SELECT article, MAX(price) AS price
-> FROM shop
-> GROUP BY article
-> ORDER BY article;
+---------+-------+
| article | price |
+---------+-------+
|    1 |  3.99 |
|    2 | 10.99 |
|    3 |  1.69 |
|    4 | 19.95 |
+---------+-------+
4 rows in set (0.01 sec)

或者
mysql> SELECT article, dealer, price
-> FROM shop s1
-> WHERE price=(SELECT MAX(s2.price)
-> FROM shop s2
-> WHERE s1.article = s2.article)
-> ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    1 | B    |  3.99 |
|    2 | A    | 10.99 |
|    3 | C    |  1.69 |
|    4 | D    | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

6.使用用户自定义临时变量存储值
查询价格最高和最低的商品信息
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                1.25 |                19.95 |
+------------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    3 | D    |  1.25 |
|    4 | D    | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)

欢迎光临 JEE Study|JAVA EE|企业级开发学习网 (http://jeestudy.com/)