SQL高级子查询——0616

SQL高级子查询
简单子查询
子查询概念
SQL语句最强大的地方就是子查询。子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询。基本的子查询分为三类：

通过IN、ANY、ALL修改的比较运算符引入的列表上操作的子查询
通过未修改的比较运算符引入必须返回单个子值的子查询；
通过EXISTS的存在测试的子查询
子查询几乎可以放在任何SQL语句中，任何允许使用表达式的地方都可以使用子查询

如何编写子查询概述
分析：
首先查询出学生“章涵”的班级编号，然后在Studentinfo学生表中查询出和“章涵”的班级编号相同的学生编号、姓名和班级编号。

代码

select id,StudentName,ClassID
from StudentInfo where ClassID=
(SELECT ClassID from StudentInfo  Where   StudentName='章涵');

通过代码可以看出，where条件是另一个查询语句，习惯上，外面的查询称为父查询。括号中嵌入的查询称为子查询。SQL执行时，先执行子查询，后执行父查询。同时注意，将子查询和比较运算符联合起来，必须保证子查询返回的值不能多于一个。

除此之外，子查询还可以将多表的数据组合在一起，从而替换表连接查询

表查询和子查询的区别：一般来说，表连接可以用子查询替换，但反过来说，有的子查询不能使用表连接来替换。子链接比较灵活、方便、形式多样，适合作为查询的筛选条件，而表连接更适合查看多表的数据
在UPDATE、DELETE、INSERT语句中使用子查询
子查询除用在SELECT语句中完成复杂查询功能外，还可用在UPDATE、DELETE、INSERT语句中完成复杂的更新、删除、插入功能。在UPDATE、DELETE和INSERT语句使用子查询和在SELECT语句中使用子查询的原理是一样的，内层子查询的结果作为外层查询的WHERE条件的参考值来使用。

1：在UPDATE语句中使用子查询
更新“李启年”的“基于C语言理解软件编程”的成绩为55分

分析：

先查询出名为“李启年”的教师编号和“基于C语言理解软件编程”的课程编号，然后作为UPDATE语句的条件。

代码：

UPDATE Exam set Exam=Exam+5
where StudentID = (select ID from StudentInfo where StudentName='李启年')
AND SubjectID = (
SELECT ID FROM Subject where SubjectName = '基于C语言理解软件编程')

2：在DELETE中使用子查询
与其他语句一样，子查询主要是放在DELETE语句的WHERE条件中，作为删除的条件。

删除“李启年”的所有考试成绩

分析：

先查询出名为“李启年”的学生编号，然后作为DELETE语句的条件。

代码

SELECT FROM EXAM WHERE STUDENRID = (
SELECT ID FROM StudentInfo where StudentName='李启年')

3：在INSERT中使用子查询
在INSERT语句中使用子查询的主要作用是实现追加子查询，基本语法

INSERTINTO 表名 （字段列表） select字段列表 from  表名

高级子查询
使用IN和NOT IN的子查询
使用=、>等比较运算符时，子查询返回结果只能为一条或空记录，不允许字串返回多条记录。

如果查询《基于C语言理解软件编程》考试成绩有多个同学刚好通过90分的学生名单

将出现编译错误

代码：

select StudentName FROM  StudentInfo where ID=(
select StudentID from Exam where exam=90 and SubjectID =(select SubjectID from Subject where SubjectName='基于C语言理解软件编程'))；

如何解决？

可以通过IN或NOT IN关键字引入的子查询也返回一列零值或更多值。只需把“=”改为IN就可以了

查询《基于C语言理解软件编程》考试成绩刚好等于90分的学生名单

分析

先查询出“基于C语言理解软件编程”课程的课号，然后用子查询查出所有选修这门课程比你高成绩刚好90分的同学名字

代码

select StudentName FROM  StudentInfo where ID in(
select StudentID from Exam where exam=90 and SubjectID =(select SubjectID from Subject where SubjectName='基于C语言理解软件编程'))；

如果查询没有参加基于“C语言理解软件编程“考试的学生姓名？

查询没有参加”基于C语言理解软件编程“考试的学生名单

分析： 与上一个刚好相反只需要用NOT IN 来显现，同时去掉等于90分的条件

代码;

select StudentName FROM  StudentInfo where ID not in(
select StudentID from Exam where SubjectID =(
select SubjectID from Subject where SubjectName='基于C语言理解软件编程'))；

使用 EXISTS 和NOT EXIXTS
EXISTS 关键字后面的参数时一个任意的子查询，系统对子查询及逆行运算一判断它是否返回行，如果至少返回一行，那么EXISTS 的结果时TRUE ，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS 返回的结果时 false ，此时外层语句将不进行查询

示例：查询成绩表中科目编号为2的考试成绩中是否存在不及格的学生，如果存在不及格的学生就将会参加科目编号2考试的学生编号和成绩全部查询出来，

代码

select StudentID,EXAM FROM EXAM WHERE SubjectID=2 AND EXISTS(
SELECT StudentID FROM EXAM WHERE EXAM <60)

由结果可以看出，内层循环结果表明EXAM表中存在SubjectID=2 并且不及格的成绩，因此EXISTS表达式返回TRUE；外层查询语句接受true之后对表EXAM 进行查询，并返回相应的记录。

NOT EXISTS 与EXISTS 使用方法相同，返回的结果相反。子查询如果至少返回一行，那么 NOT EXISTS 的结果返回为FALSE ,对此外层查询语句将不进行查询；如果子查询没有返回任何行，那么 NOT EXISTS 返回的结果为true，此时外层查询语句将及逆行查询

示例： 查询成绩表中科目编号为4的考试成绩中是否存在不及格的学生，如果不存在不及格的学生就将将参加科目编号2考试的学生编号和成绩全部查询出来，

SELECT StudentID ，Exam from EXAM WHERE SubjectID = 4 AND NOT EXISTS( SELECT StudentID from EXAM WHERE EXAM < 60 AND SubjectID=4)

使用ALL、 ANY/SOME 的查询
ALL用在子查询前，通过比较运算符将一个表达式的值或列值与子查询返回的一列值中的每一个进行比较，只要有一次比较的结果为FALSE，则ALL测试返回FALSE. ALL要求子查询必须返回而且只能返回一个字段。

语法：

表达式或字段比较运算符ALL(子查询)
查询成绩比科目编号为”1“的这门课程的所有成绩都大的学生考试成绩

代码：

select*from Exam WHERE EXAM > ALL(select Exam from EXAM where SubjectID =1)

ANY与子查询在一起使用时，满足比较运算符、表达式或字段对子查询的结果的每一行进行一次计算和比较。只要有一次满足条件，那么ANY的结果就是真，当子查询每行的结果与ANY前面的表达式或字段比较结果全为假时，则结果为假，”ANY“与”IN“等效

语法：

表达式或字段比较运算符ANY/SOME（子查询）
查询成绩比科目编号为”1“的任意一个成绩都大的考试结果

分析：

先查询出科目编号为”4“的成绩，作为ANY/SOME子查询的条件

代码：

select*from Exam where Exam>ANY(select Exam from EXAM where SubjectID=1)

两个作用相同