1、第八章,高级查询,2,目标,掌握简单子查询的用法 掌握IN子查询的用法 掌握EXISTS子查询的用法 应用T-SQL进行综合查询,3,子查询,子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。,Select From Table,WHERE ,父查询,Select From WHERE = ,操作符,子查询,4,什么是子查询 3-1,学员信息表,问题： 编写T-SQL语句，查看年龄比“李斯文”大的学员，要求显示这些学员的信息 ？,分析： 第一步：求出“李斯文”的年龄； 第二步：利用WHERE语句，筛选年龄比“李斯文”大的学员；,5,什么是子查询 3-2,实现方法一：采用T-S
2、QL变量实现,DECLARE age INT -定义变量，存放李斯文的年龄 SELECT age=stuAge FROM stuInfoWHERE stuName=李斯文 -求出李斯文的年龄 -筛选比李斯文年龄大的学员 SELECT * FROM stuInfo WHERE stuAgeage GO,6,什么是子查询 3-3,实现方法二：采用子查询实现,SELECT * FROM stuInfo WHERE stuAge( SELECT stuAge FROM stuInfo where stuName=李斯文) GO,子查询,子查询在WHERE语句中的一般用法：SELECT FROM 表1
3、WHERE 字段1 (子查询)外面的查询称为父查询，括号中嵌入的查询称为子查询 UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个,7,使用子查询替换表连接3-1,问题：查询笔试刚好通过（60分）的学员。,学员信息表和成绩表,8,使用子查询替换表连接3-2,实现方法一：采用表连接,SELECT stuName FROM stuInfo INNER JOIN stuMarksON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60 GO,内连接（等值连接）,
4、9,使用子查询替换表连接3-3,实现方法二：采用子查询,SELECT stuName FROM stuInfo WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60) GO,子查询,一般来说，表连接都可以用子查询替换，但有的子查询却不能用表连接替换 子查询比较灵活、方便，常作为增删改查的筛选条件，适合于操纵一个表的数据 表连接更适合于查看多表的数据,10,相关子查询,许多查询都可以通过执行一次子查询并将结果值代入外部查询的 WHERE 子句进行评估。 在包括相关子查询（也称为重复子查询）的查询中，子查询依靠外部查询获得值。 示
5、例:查询工资高于本部门平均工资的员工,USE company SELECT name, id,salary FROM employee as eWHERE salary ( SELECT avg(salary) FROM employee WHERE dno=e.dno),11,子查询实例,返回第一步,USE northwind SELECT orderid, customeridFROM orders AS or1WHERE 20 (SELECT quantity FROM order details AS odWHERE or1.orderid = od.orderidAND od.pro
6、ductid = 23) GO,实例,12,IN子查询 4-1,问题：查询笔试刚好通过的学员名单。,如何解决？,13,IN子查询 4-2,解决方法：采用 IN 子查询,SELECT stuName FROM stuInfo WHERE stuNo IN(SELECT stuNo FROM stuMarks WHERE writtenExam=60) GO,将号改为IN,IN后面的子查询可以返回多条记录 常用IN替换等于（）的比较子查询,14,在子查询中使用IN 或 NOT IN,通过 IN（或 NOT IN）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这些结果。 例如
7、,USE pubs SELECT pub_name FROM publishers WHERE pub_id IN(SELECT pub_idFROM titlesWHERE type = business),15,IN子查询 4-3,问题：查询参加考试的学员名单,学员信息表和成绩表（重抓本图）,分析： 判断一个学员是否参加考试其实很简单，只需要查看该学员对应的学号是否在考试成绩表stuMarks中出现即可,16,IN子查询 4-4,/*-采用IN子查询参加考试的学员名单-*/ SELECT stuName FROM stuInfoWHERE stuNo IN (SELECT stuNo FR
8、OM stuMarks) GO,演示：使用IN子查询,参考语句,17,NOT IN子查询,问题：查询未参加考试的学员名单,分析：加上否定的NOT 即可,18,EXISTS子查询 4-1,例如：数据库的存在检测,IF EXISTS（SELECT * FROM sysDatabases WHERE name=stuDB）DROP DATABASE stuDB CREATE DATABASE stuDB .建库代码略,19,EXISTS子查询 4-2,IF EXISTS （子查询）语句,EXISTS子查询的语法：,如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true
9、），否则返回假(false) EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换,20,使用 EXISTS 和 NOT EXISTS,EXISTS 用于检查子查询返回的行是否存在 该子查询实际上并不返回任何数据，而是返回 TRUE 或 FALSE 示例,USE pubs SELECT au_lname, au_fname FROM authors WHERE exists(SELECT *FROM publishersWHERE authors.city = publishers.city),21,EXISTS子查询 4-3,问题： 检查本次考试，本班如果有人笔试成绩达
10、到80分以上，则每人提2分；否则，每人允许提5分,分析： 是否有人笔试成绩达到80分以上，可以采用EXISTS检测,22,EXISTS子查询 4-4,/*-采用EXISTS子查询，进行酌情加分-*/ IF EXISTS (SELECT * FROM stuMarks WHERE writtenExam80)BEGINprint 本班有人笔试成绩高于80分，每人加2分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+2SELECT * FROM stumarksEND ELSEBEGINprint 本班无人笔试成绩高于80分，每人可以加5分
11、，加分后的成绩：UPDATE stuMarks SET writtenExam=writtenExam+5SELECT * FROM stumarksEND GO,演示：使用EXISTS子查询,参考语句,23,NOT EXISTS子查询 2-1,问题： 检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都60分），则试题偏难，每人加3分，否则，每人只加1分,分析： 没有一人通过考试，即不存在“笔试和机试成绩都60分”，可以采用NOT EXISTS检测,24,NOT EXISTS子查询 2-2,IF NOT EXISTS (SELECT * FROM stuMarks WHERE writt
12、enExam60 AND labExam60)BEGINprint 本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+3,labExam=labExam+3SELECT * FROM stuMarksEND ELSEBEGINprint 本班考试成绩一般，每人只加1分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+1,labExam=labExam+1SELECT * FROM stuMarksEND GO,演示：使用NOT EXISTS子查询
13、,参考语句,25,在子查询中使用比较运算符,子查询可由一个比较运算符（=、 =、, ! 或 =）引入。 示例,SELECT name,sex,id,salaryFROM Employee AS eWHERE salary ( SELECT AVG(salary)FROM EmployeeWHERE dno=e.dno ),26,在子查询中使用 ANY、SOME 或 ALL,可以用 ALL 或 ANY 关键字修改引入子查询的比较运算符。SOME 是 SQL-92 标准的 ANY 的等效物。 示例,SELECT name,idFROM Employee eWHERE 200000 = ANY(SE
14、LECT budgetFROM Project pWHERE e.id=p.pmgrid),27,在子查询中使用 ANY、SOME 或 ALL,修改的比较运算符可以使其对一组值进行操作,而未修改的比较运算符则不行 =ANY 等价于 IN 运算符 ANY 不等价于 NOT IN 运算符 ALL 相当于大于最大值 ANY 相当于大于最小值,28,嵌套子查询,子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。,SELECT name,sex,id,salaryFROM EmployeeWHERE id IN (SELECT mgridFROM DepartmentWHERE d
15、number IN(SELECT dnoFROM EmployeeGROUP BY dnoHAVING AVG(salary)4000),示例:查询平均月薪高于4000的部门的部门经理,29,在UPDATE、DELETE 和 INSERT 语句中使用子查询,子查询可以嵌套在 UPDATE、DELETE 和 INSERT 语句以及 SELECT 语句中。 例如,UPDATE EmployeeSET salary = salary + 500WHERE dno IN(SELECT dnumberFROM DepartmentWHERE dname = 技术部),30,T-SQL语句的综合应用,学员
16、信息表和成绩表,应到人数：5人,实到人数4人，缺考1人,31,T-SQL语句的综合应用,如何实现？,本次考试的缺考情况,统计通过率,32,T-SQL参考语句,/*-本次考试的原始数据-*/ -SELECT * FROM stuInfo -SELECT * FROM stuMarks /*-统计考试缺考情况-*/ SELECT 应到人数=(SELECT count(*) FROM stuInfo) , -应到人数为子查询表达式的别名实到人数=(SELECT count(*) FROM stuMarks) ,缺考人数=(SELECT count(*) FROM stuInfo)-(SELECT c
17、ount(*) FROM stuMarks),33,T-SQL语句的综合应用,统计学员的通过率情况： 1）通过人数：因为通过用1表示，没通过用0表示，所以isPass列的累加和即是通过人数； 2）通过率：同理，isPass列的平均值*100即是通过率；,34,T-SQL参考语句,/*-显示通过率及通过人数-*/ SELECT 总人数=count(*) ,通过人数=SUM(isPass),通过率=(convert(varchar(5),AVG(isPass*100)+%) FROM newTable,35,总结,总结我们曾学习过的查询，合并多个表中的数据的方法有三种： 联合(Union)合并多个数据表中的行 子查询将一个查询包含到另一个查询中 联接合并多个数据表中的列 通过在子查询中使用EXISTS子句，可以对子查询中的行是否存在进行检查 IN子查询后面可跟随返回多条记录的子查询，用于检测某列的值是否在某个范围,
