立即学习:https://edu.csdn.net/course/play/27328/370702?utm_source=blogtoedu
子查询:
子查询In:
where子句使用in语法:
select column_name from table_name where column_name in(value1,value2,...)
如果in后面的值是来源于某个查询结果而并非是指定的几个值,需要用到子查询,在查询的where子句中嵌入查询语句
子查询in语法:
select column_name from table_name where column_name in(select column_name from table_name [where]);
子查询Exists:
exists是子查询中用于测试内部查询是否返回任何行的布尔运算符。将主查询的数据放到子查询中做条件验证,根据验证结果来决定主查询的数据结果是否保留。
where子句使用exists语法:
select column_name1 from table_name1 where exists (select * from table_name2 where condition);
例:
score表:
+----+----------+---------+-------+
| id | course | stu_no | score |
+----+----------+---------+-------+
| 1 | 计算机 | 2016001 | 99 |
| 2 | 离散数学 | 2016001 | 85 |
| 3 | 计算机 | 2016002 | 78 |
+----+----------+---------+-------+
student表:
+---------+--------+----------+
| stu_no | name | address |
+---------+--------+----------+
| 2016001 | 张三 | 贵州贵阳 |
| 2016002 | 李芳 | 陕西兴平 |
| 2016003 | 张晓燕 | 江西南昌 |
+---------+--------+----------+
查询所有选修了课程的学生:
select A.*
from student A
where A.stu_no in (select B.stu_no from score B);
+---------+------+----------+
| stu_no | name | address |
+---------+------+----------+
| 2016001 | 张三 | 贵州贵阳 |
| 2016002 | 李芳 | 陕西兴平 |
+---------+------+----------+
查询所有选修了离散数学的学生:
select A.*
from student A
where A.stu_no in (select B.stu_no from score B where B.course='离散数学');
+---------+------+----------+
| stu_no | name | address |
+---------+------+----------+
| 2016001 | 张三 | 贵州贵阳 |
+---------+------+----------+
查询所有选修了课程的学生:
select A.*
from student A
where exists (select * from score B where A.stu_no = B.stu_no);
+---------+------+----------+
| stu_no | name | address |
+---------+------+----------+
| 2016001 | 张三 | 贵州贵阳 |
| 2016002 | 李芳 | 陕西兴平 |
+---------+------+----------+
查询所有未选修课程的学生:
select A.*
from student A
where not exists (select * from score B where A.stu_no = B.stu_no);
+---------+--------+----------+
| stu_no | name | address |
+---------+--------+----------+
| 2016003 | 张晓燕 | 江西南昌 |
+---------+--------+----------+