MySQL的常见查询示例(针对小白3)
前言
内容紧接上一篇博客。这一篇内容较少,如果各位看了上一篇的话,这一篇很快就可以掌握。(写完吐槽:就是单纯用SELECT啊)
这次学习和分享的部分是MySQL8.0官方文档的第三章里的创建和使用数据库和常见查询示例,地址是:
https://dev.mysql.com/doc/refman/8.0/en/tutorial.html
上一篇博客《MySQL的基本操作(针对小白2)》,强烈推荐:https://blog.csdn.net/qq_37924224/article/details/105510809
OK,直接正文吧。(示例里用到的表格可以通过执行前的准备工作里来生成。)
可以直接通过目录进行跳转哦。
执行前的准备工作
输入
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);
创建的表格如图
列的最大值
查找查找article列的最大值
(“ 最高的商品编号是多少?”)
SELECT MAX(article) AS article FROM shop;
mysql> SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+
1 row in set (0.01 sec)
保留某列最大值的行
(“查找价格最高的商品的编号,经销商和价格。”)
下面这个输入,可以看作(SELECT MAX(price) FROM shop)=19.95
因为
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
和
SELECT article, dealer, price
FROM shop
WHERE price=19.95;
的结果一样。
结果如下
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.00 sec)
mysql> SELECT article, dealer, price
-> FROM shop
-> WHERE price=19.95;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 4 | D | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)
每组最大列数
(“查找每件商品的最高价格。”)
显示article列中相同的元素的在price中的最大值
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
ORDER BY article;
结果如下
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.00 sec)
保持某一列的分组最大值的行
(“对于每件商品,找到价格最昂贵的经销商。”)
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
ORDER BY article;
结果如下
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)
使用用户定义的变量
(“要查找价格最高和最低的商品”)
使用两句
SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
结果如下
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)
结束语
这篇很短,经过三篇博客,我们应该算是掌握MySQL的基础了。因为我也不是专门搞数据库的,自己对数据库的学习先打住,满足以后可能的基本需求就行。所以之后我会去学其他的东西吧。
学习是一个持之以恒的事情。我并不爱学习,但我爱钱💰。当这两个事情绑定在一起的时候。哎~
果然还是不想学啊。。。
。
。
。
“你还要买房啊!!!”
“扶我起来,我能学。”