MySQL查询语句使用总结（三）——子查询

语句使用总结（三）——子查询"/>

MySQL查询语句使用总结（三）——子查询

MySQL查询语句使用总结（三）——子查询

子查询是指在一个查询中嵌套另一个select语句。子查询的用法如下：
（1）子查询放在小括号内；
（2）子查询如果在where后面一般放在条件的右侧；
（3）如果子查询的结果集为一行一列，称为标量子查询，一般搭配【=、>、<、>=、<=、!=】操作符使用；
（4）如果子查询的结果集为一列多行，称为列子查询，一般搭配【in、=any/some、=all】操作符使用；
（5）如果子查询的结果集为多行多列，称为表子查询，一般放在from后面；
（6）子查询的执行优先外部查询执行，因为外部查询的条件需要用到子查询的结果。

一、数据准备： 创建三张表，student（学生）、course（课程）、score（成绩），命令如下：

create table student(s_id char(5) primary key comment '学生学号',s_name char(20) comment '学生姓名',age int comment '学生年龄',phone char(20) comment '电话',addr varchar(200) comment '家庭地址',class char(20) comment '班级'
);create table course(c_id char(4) primary key comment '课程编号',c_name char(20) comment '课程名称',credits int comment '学分'
);create table score(s_id char(5) comment '学生学号',c_id char(4) comment '课程编号',score int comment '成绩',primary key(s_id,c_id),foreign key(s_id) references student(s_id),foreign key(c_id) references course(c_id)
);

为以上三张表插入必要的数据，命令如下：

insert into student
values('S1001','张平',20,'15537302558','新乡市','物流1班'),('S1002','刘鹏',21,'15537306666','新乡市','物流1班'),
('S1003','张静静',18,'15937304444','新乡市','电商1班'),('S1004','刘宏伟',19,'15637306688','郑州市','电商1班'),
('S1005','张莹莹',19,'13937307888','开封市','电商1班'),('S1006','赵晶晶',18,'13503735522','安阳市','国贸1班');insert into course
values('C001','数据库',4),('C002','数据结构',4),('C003','管理学',3),('C004','电子商务',3);insert into score 
values('S1001','C001',80),('S1001','C002',90),('S1001','C004',87),
('S1002','C001',87),('S1002','C002',67),('S1002','C003',78),
('S1003','C001',66),('S1003','C002',77),('S1003','C003',88),('S1003','C004',99),
('S1004','C001',81),('S1004','C003',62),('S1004','C004',68);

二、标量子查询举例

--查询和张静静家庭住址在同一个城市的学生信息
mysql> select * from student where addr=(select addr from student where s_name='张静静');
+-------+-----------+------+-------------+-----------+------------+
| s_id  | s_name    | age  | phone       | addr      | class      |
+-------+-----------+------+-------------+-----------+------------+
| S1001 | 张平      |   20 | 15537302558 | 新乡市    | 物流1班    |
| S1002 | 刘鹏      |   21 | 15537306666 | 新乡市    | 物流1班    |
| S1003 | 张静静    |   18 | 15937304444 | 新乡市    | 电商1班    |
+-------+-----------+------+-------------+-----------+------------+
3 rows in set (0.00 sec)--查询张静静同学的数据结构课程的考试成绩
mysql> select score from scorewhere s_id=(select s_id from student where s_name='张静静') andc_id=(select c_id from course where c_name='数据结构');
+-------+
| score |
+-------+
|    77 |
+-------+
1 row in set (0.00 sec)--查询年龄最小的同学的姓名、年龄、家庭地址和班级
mysql> select s_name,age,addr,class from student where s_id in (select s_id from student where age=(select min(age) from student) );
+-----------+------+-----------+------------+
| s_name    | age  | addr      | class      |
+-----------+------+-----------+------------+
| 张静静    |   18 | 新乡市    | 电商1班    |
| 赵晶晶    |   18 | 安阳市    | 国贸1班    |
+-----------+------+-----------+------------+
2 rows in set (0.00 sec)

三、列子查询举例

mysql> select * from student where age in (select age from student where class='电商1班');
--查询和电商1班某个学生年龄相同的学生信息
+-------+-----------+------+-------------+-----------+------------+
| s_id  | s_name    | age  | phone       | addr      | class      |
+-------+-----------+------+-------------+-----------+------------+
| S1003 | 张静静    |   18 | 15937304444 | 新乡市    | 电商1班    |
| S1006 | 赵晶晶    |   18 | 13503735522 | 安阳市    | 国贸1班    |
| S1004 | 刘宏伟    |   19 | 15637306688 | 郑州市    | 电商1班    |
| S1005 | 张莹莹    |   19 | 13937307888 | 开封市    | 电商1班    |
+-------+-----------+------+-------------+-----------+------------+
4 rows in set (0.00 sec)mysql> select * from student where age>=all(select age from student where class='电商1班');
--查询比电商1班所有人年龄都大的学生信息
+-------+-----------+------+-------------+-----------+------------+
| s_id  | s_name    | age  | phone       | addr      | class      |
+-------+-----------+------+-------------+-----------+------------+
| S1001 | 张平      |   20 | 15537302558 | 新乡市    | 物流1班    |
| S1002 | 刘鹏      |   21 | 15537306666 | 新乡市    | 物流1班    |
| S1004 | 刘宏伟    |   19 | 15637306688 | 郑州市    | 电商1班    |
| S1005 | 张莹莹    |   19 | 13937307888 | 开封市    | 电商1班    |
+-------+-----------+------+-------------+-----------+------------+
4 rows in set (0.01 sec)mysql> select * from student where age>=any(select age from student where class='电商1班');
--查询比电商1班中的任意一个学生年龄大的学生信息
+-------+-----------+------+-------------+-----------+------------+
| s_id  | s_name    | age  | phone       | addr      | class      |
+-------+-----------+------+-------------+-----------+------------+
| S1001 | 张平      |   20 | 15537302558 | 新乡市    | 物流1班    |
| S1002 | 刘鹏      |   21 | 15537306666 | 新乡市    | 物流1班    |
| S1003 | 张静静    |   18 | 15937304444 | 新乡市    | 电商1班    |
| S1004 | 刘宏伟    |   19 | 15637306688 | 郑州市    | 电商1班    |
| S1005 | 张莹莹    |   19 | 13937307888 | 开封市    | 电商1班    |
| S1006 | 赵晶晶    |   18 | 13503735522 | 安阳市    | 国贸1班    |
+-------+-----------+------+-------------+-----------+------------+
6 rows in set (0.00 sec)

说明：
（1）>=all(子查询)：表示比子查询结果中所有的数据值都要大；
（2）>=any或some(子查询)：表示比子查询结果中的某一个数据值大即可；

四、表子查询

表子查询一般放在外部查询的from后面，此时子查询必须带别名。

mysql> select s.s_name,sc.course_count,sc.score_avg from(select s_id,count(*) as course_count,avg(score) as score_avg from score group by s_id) sc inner join student s on sc.s_id=s.s_idwhere sc.score_avg>80;
+-----------+--------------+-----------+
| s_name    | course_count | score_avg |
+-----------+--------------+-----------+
| 张平      |            3 |   85.6667 |
| 张静静    |            4 |   82.5000 |
+-----------+--------------+-----------+
2 rows in set (0.00 sec)

五、exists查询

创建表并插入数据：

create table dept5(d_id int primary key,d_name char(20));
insert into dept5 values(1,'财务部'),(2,'销售部'),(3,'人事部'),(4,'信息部'),(5,'生产部');
create table emp5(e_id int primary key,e_name char(20),salary int,d_id int);
insert into emp5 values(101,'Tom',5000,1),(102,'Jack',5500,1),(103,'Jerry',8500,2),
(104,'John',4500,2),(105,'Kate',5100,2),(106,'Rose',5800,3),(107,'Mark',7500,3);

举例：

--查询有员工的部门，用 in 查询
mysql> select * from dept5 where d_id in (select d_id from emp5);
+------+-----------+
| d_id | d_name    |
+------+-----------+
|    1 | 财务部    |
|    2 | 销售部    |
|    3 | 人事部    |
+------+-----------+
3 rows in set (0.00 sec)--查询有员工的部门，用 exists 查询
mysql> select * from dept5 where exists(select * from emp5 where emp5.d_id=dept5.d_id);
+------+-----------+
| d_id | d_name    |
+------+-----------+
|    1 | 财务部    |
|    2 | 销售部    |
|    3 | 人事部    |
+------+-----------+
3 rows in set (0.00 sec)--查询没有员工的部门，用 not exists 查询
mysql> select * from dept5 where not exists(select * from emp5 where emp5.d_id=dept5.d_id);
+------+-----------+
| d_id | d_name    |
+------+-----------+
|    4 | 信息部    |
|    5 | 生产部    |
+------+-----------+
2 rows in set (0.00 sec)