MySQL中的常见子查询语句

技术交流QQ群:1027579432，欢迎你的加入！

欢迎关注我的微信公众号：CurryCoder的程序人生

一.子查询语句格式

基本语法：SELECT 字段列表/* + FROM + 表名 +[WHERE 条件];
完整语法：SELECT [SELECT 选项] + 字段列表[字段别名] /* + FROM + 数据源 + [WHERE 条件] + [1] + [2] + [3] + [4];
- [1] = group by 子句
- [2] = having 子句
- [3] = order by 子句
- [4] = limit 子句

1.SELECT选项

SELECT选项：SELECT对查出来的结果的处理方式

ALL：默认，保留所有的查询结果

DISTINCT：去重，将查出来的结果中所有字段都相同的记录去掉

mysql> SELECT * FROM my_copy;
+-------+
| name  |
+-------+
| curry |
| curry |
| curry |
| A     |
| a     |
| A     |
| a     |
| A     |
| a     |
| A     |
| a     |
| A     |
| B     |
| b     |
| a     |
| A     |
| B     |
| b     |
| a     |
| A     |
| B     |
| b     |
+-------+
22 rows in set (0.65 sec)

mysql> SELECT ALL * FROM my_
+-------+
| name  |
+-------+
| curry |
| curry |
| curry |
| A     |
| a     |
| A     |
| a     |
| A     |
| a     |
| A     |
| a     |
| A     |
| B     |
| b     |
| a     |
| A     |
| B     |
| b     |
| a     |
| A     |
| B     |
| b     |
+-------+
22 rows in set (0.00 sec)

mysql> SELECT DISTINCT * FROM my_copy;
+-------+
| name  |
+-------+
| curry |
| A     |
| B     |
+-------+
3 rows in set (0.09 sec)

2.字段别名

字段别名：当数据进行查询的时候，有时候字段的名字并不一定满足需求（特别地，在多表查询的时候，很可能会有同名字段），这时就需要对字段进行重命名、取别名。

基本语法：字段名 + [AS] + 别名;

mysql> SELECT id, name AS 姓名, age AS 年龄, grade AS 年级
    -> FROM student;
+----+--------+------+------+
| id | 姓名   | 年龄 | 年级 |
+----+--------+------+------+
|  1 | curry  |   30 |  3.1 |
|  2 | durant |   29 |  3.4 |
|  3 | Riuo   |   27 |  3.6 |
|  4 | harden |   29 |  3.2 |
+----+--------+------+------+
4 rows in set (0.13 sec)

3.数据源

数据源：数据的来源，关系型数据库的数据源都是数据表，本质上只要保证数据类似二维表，最终就可以作为数据源。数据源分为 3 种，分别为：单表数据源，多表数据源和查询语句。

单表数据源：SELECT * FROM + 表名;

mysql> SELECT * FROM student;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  1 | curry  |   30 |   3.1 | 男   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
|  4 | harden |   29 |   3.2 | 男   |    1 |
+----+--------+------+-------+------+------+
4 rows in set (0.00 sec)

多表数据源：SELECT * FROM + 表1名,表2名,...;

mysql> SELECT * FROM student, my_class;
+----+--------+------+-------+------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id | grade | room | id   |
+----+--------+------+-------+------+------+-------+------+------+
|  1 | curry  |   30 |   3.1 | 男   |    1 | 101   | S    |    2 |
|  1 | curry  |   30 |   3.1 | 男   |    1 | 102   | B    |    2 |
|  1 | curry  |   30 |   3.1 | 男   |    1 | 103   | D    |    2 |
|  2 | durant |   29 |   3.4 | 男   |    1 | 101   | S    |    2 |
|  2 | durant |   29 |   3.4 | 男   |    1 | 102   | B    |    2 |
|  2 | durant |   29 |   3.4 | 男   |    1 | 103   | D    |    2 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 | 101   | S    |    2 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 | 102   | B    |    2 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 | 103   | D    |    2 |
|  4 | harden |   29 |   3.2 | 男   |    1 | 101   | S    |    2 |
|  4 | harden |   29 |   3.2 | 男   |    1 | 102   | B    |    2 |
|  4 | harden |   29 |   3.2 | 男   |    1 | 103   | D    |    2 |
+----+--------+------+-------+------+------+-------+------+------+
12 rows in set (0.00 sec)

使用多表数据源时，默认从一张表中取出一条记录去另一张表中匹配所有记录，而且全部保留，比较浪费存储空间，应该尽量避免！

查询语句(子查询)：SELECT * FROM + (SELECT * FROM 表名) + [AS] 别名;

mysql> SELECT * FROM (SELECT * FROM student) AS stu;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  1 | curry  |   30 |   3.1 | 男   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
|  4 | harden |   29 |   3.2 | 男   |    1 |
+----+--------+------+-------+------+------+
4 rows in set (0.05 sec)

4.WHERE子句

WHERE子句：用来判断和筛选数据，返回的结果为0或者1，其中0代表false，1代表true，where是唯一一个直接从磁盘获取数据的时候就开始判断的条件，从磁盘中读取一条数据，就开始进行where判断，如果判断的结果为真，则保持；反之，不保存。

判断条件：

比较运算符：> < >= <= <> = like between and in not in
逻辑运算符：&& || ！

实例1如下：

mysql> SELECT * FROM student
    -> WHERE id = 2 || id = 3;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
+----+--------+------+-------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student
    -> WHERE id IN(2,3);
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
+----+--------+------+-------+------+------+
2 rows in set (0.00 sec)

实例2：查询student中id在1到3之间的记录。注意：在使用between and的时候，其选择的区间为闭区间，即包含端点值。此外，and前面的数值必须大于等于and后面的数值，否则会出现空判断！

mysql> SELECT * FROM student
    -> WHERE id BETWEEN 1 AND 3;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  1 | curry  |   30 |   3.1 | 男   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
+----+--------+------+-------+------+------+
3 rows in set (0.03 sec)

5.group by子句

group by子句：根据表中的某个字段进行分组，即把含有相同字段值的记录放在一组，不同的放在不同组中。
基本语法：group by + 字段名;

实例：将表student中的数据按字段sex进行分组

mysql> SELECT * FROM student
    -> GROUP BY sex;
+----+-------+------+-------+------+------+
| id | name  | age  | grade | sex  | c_id |
+----+-------+------+-------+------+------+
|  3 | Riuo  |   27 |   3.6 | 女   |    1 |
|  1 | curry |   30 |   3.1 | 男   |    1 |
+----+-------+------+-------+------+------+
2 rows in set (0.02 sec)

表student在分组过后，数据“丢失”啦！实际上并非如此，产生这样现象原因为：group by分组的目的是为了（按分组字段）统计数据，并不是为了单纯的进行分组而分组。为了方便统计数据，SQL 提供了一系列的统计函数，例如：
- count()：统计分组后，每组的总记录数；
  - count()函数里面可以使用两种参数，分别为：
    - *：统计组内全部记录的数量
    - 字段名：统计对应字段的非null（如果某条记录中该字段的值为null，则不统计）记录的总数
- max()：统计每组中的最大值；
- min()：统计每组中的最小值；
- avg()：统计每组中的平均值；
- sum()：统计每组中的数据总和。

实例：将表student中的数据按字段sex进行分组，并进行统计

mysql> SELECT sex, COUNT(*), MAX(age), MIN(age), SUM(age)
    -> FROM student
    -> GROUP BY sex;
+------+----------+----------+----------+----------+
| sex  | COUNT(*) | MAX(age) | MIN(age) | SUM(age) |
+------+----------+----------+----------+----------+
| 女   |        1 |       27 |       27 |       27 |
| 男   |        3 |       30 |       29 |       88 |
+------+----------+----------+----------+----------+
2 rows in set (0.09 sec)

使用group by进行分组之后，展示的记录会根据分组的字段值进行排序，默认为升序，也可以人为的设置升序和降序。基本语法：group by + 字段名 + [desc/asc];

mysql> SELECT sex, COUNT(*)
    -> FROM student
    -> GROUP BY sex DESC;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| 男   |        3 |
| 女   |        1 |
+------+----------+
2 rows in set (0.00 sec)

上面的测试样例中，都是单个字段进行分组。实际上，也可以使用多字段分组，即：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段（前提是分组后的结果中包含此字段）进行分组。

实例：将表 student 中的数据先按字段 grade 进行分组，再按字段 sex 进行分组

mysql> SELECT *, COUNT(*) FROM student
    -> GROUP BY grade, sex;
+----+--------+------+-------+------+------+----------+
| id | name   | age  | grade | sex  | c_id | COUNT(*) |
+----+--------+------+-------+------+------+----------+
|  1 | curry  |   30 |   3.1 | 男   |    1 |        1 |
|  4 | harden |   29 |   3.2 | 男   |    1 |        1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |        1 |
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |        1 |
+----+--------+------+-------+------+------+----------+
4 rows in set (0.00 sec)

函数group_concat(字段名)可以对分组的结果中的某个字段值进行字符串连接，即保留该组某个字段的所有值。实例：将表 student 中的数据按字段 sex进行分组，并保留字段 name 的值

mysql> SELECT sex, age, count(*), group_concat(name)
    -> FROM student
    -> group by sex;
+------+------+----------+---------------------+
| sex  | age  | count(*) | group_concat(name)  |
+------+------+----------+---------------------+
| 女   |   27 |        1 | Riuo                |
| 男   |   30 |        3 | curry,durant,harden |
+------+------+----------+---------------------+
2 rows in set (0.06 sec)

回溯统计：利用with rollup关键字（书写在 SQL 语句末尾），可以在每次分组过后，根据当前分组的字段进行统计，并向上一级分组进行汇报。在进行回溯统计的时候，会将分组字段置空。例如：
```
mysql> SELECT sex, count(*) FROM student
    -> group by sex with rollup;
+------+----------+
| sex  | count(*) |
+------+----------+
| 女   |        1 |
| 男   |        3 |
| NULL |        4 |
+------+----------+
3 rows in set (0.00 sec)
```

6.having子句

having子句：与where子句一样，都是进行条件判断的。但是，where子句是针对磁盘数据进行判断，数据进入内存之后，会进行group by分组操作，分组结果就需要having子句来处理。因此，having能做where能做的几乎所有事情，但是where却不能做having能做的很多事情。
6.1：组统计的结果或者说统计函数只有having能够使用

实例：求出表 student 中所有班级人数等于 1 的班级

mysql> SELECT grade, COUNT(*)
    -> FROM student
    -> GROUP BY grade
    -> HAVING COUNT(*) = 1;
+-------+----------+
| grade | COUNT(*) |
+-------+----------+
|   3.1 |        1 |
|   3.2 |        1 |
|   3.4 |        1 |
|   3.6 |        1 |
+-------+----------+
4 rows in set (0.00 sec)

6.2：having能够使用字段别名，where则不能，原因是where是从磁盘读取数据，而磁盘中数据的名字只能是字段名，别名是数据（字段）进入到内存后才产生的。

实例：求出表 student 中所有班级人数等于 1 的班级

mysql> SELECT grade, COUNT(*) AS total
    -> FROM student
    -> GROUP BY grade
    -> HAVING total = 1;
+-------+-------+
| grade | total |
+-------+-------+
|   3.1 |     1 |
|   3.2 |     1 |
|   3.4 |     1 |
|   3.6 |     1 |
+-------+-------+
4 rows in set (0.00 sec)

7.order by子句

order by子句：根据某个字段进行升序或者降序排序，基本语法：order by + [asc/desc];

mysql> SELECT *
    -> FROM student
    -> ORDER BY age;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  4 | harden |   29 |   3.2 | 男   |    1 |
|  1 | curry  |   30 |   3.1 | 男   |    1 |
+----+--------+------+-------+------+------+
4 rows in set (0.00 sec)

多字段排序：先根据某个字段进行排序，然后在排序后的结果中，再根据某个字段进行排序。将表student中的数据先按年龄age升序排列，再按班级grade降序排列。

mysql> SELECT *
    -> FROM student
    -> ORDER BY age,grade DESC;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  3 | Riuo   |   27 |   3.6 | 女   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
|  4 | harden |   29 |   3.2 | 男   |    1 |
|  1 | curry  |   30 |   3.1 | 男   |    1 |
+----+--------+------+-------+------+------+
4 rows in set (0.00 sec)

8.Limit子句

limit子句：是一种限制结果的语句，通常来限制结果的数量。
语法：LIMIT + [offset,] + length;

8.1:只用来限制长度（数据量）

mysql> SELECT * FROM student LIMIT 2;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  1 | curry  |   30 |   3.1 | 男   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
+----+--------+------+-------+------+------+
2 rows in set (0.00 sec)

8.2:限制起始值，限制长度

mysql> SELECT * FROM student LIMIT 0, 2;
+----+--------+------+-------+------+------+
| id | name   | age  | grade | sex  | c_id |
+----+--------+------+-------+------+------+
|  1 | curry  |   30 |   3.1 | 男   |    1 |
|  2 | durant |   29 |   3.4 | 男   |    1 |
+----+--------+------+-------+------+------+
2 rows in set (0.00 sec)

8.3:主要用来实现数据的分页，目的是为用户节省时间，提高服务器的响应效率，减少资源的浪费。

最后编辑于：2020.06.06 16:11:50

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 212,080评论 6赞 493
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 90,422评论 3赞 385
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 157,630评论 0赞 348
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 56,554评论 1赞 284
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 65,662评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,856评论 1赞 290
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,014评论 3赞 408
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 37,752评论 0赞 268
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,212评论 1赞 303
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,541评论 2赞 327
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,687评论 1赞 341
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,347评论 4赞 331
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,973评论 3赞 315
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,777评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,006评论 1赞 266
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 46,406评论 2赞 360
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 43,576评论 2赞 349