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)
页:
[1]