mysql中子查询语句怎么写_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:主要用来实现数据的分页，目的是为用户节省时间，提高服务器的响应效率，减少资源的浪费。