第九章高级查询。
2. 本次课程目标讲解：[5分钟] 3
3. 课程知识点讲解： 3
3.1. 简单子查询：[20分钟] 3
3.2. in和not in子查询：[1５分钟] 5
3.3. exists和not exists子查询：[1５分钟] 6
3.4. t-sql语句的综合应用：[30分钟] 7
4. 本章总结 [5分钟] 9
5. 考核点 9
6. 测试题 9
7. 扩展部分 9
8. 学生问题汇总 9
9. 作业 9
本章主要目标。
掌握简单子查询的用法。
掌握in子查询的用法。
掌握exists子查询的用法。
应用t-sql进行综合查询。
本章重点。
掌握简单子查询的用法。
掌握in子查询的用法。
掌握exists子查询的用法。
本章难点。
t-sql的综合应用。
如何定义变量并赋值。
分析如下实例，指出错误的地方并更正：
create table bank
cardno int identity(1,1),username varchar(10),balance money
insert into bank(cardno,username,balance)
values('张三',500)
insert into bank(cardno,username,balance)
values('李四',700)
declare mymoney int(4)
mymoney=0
select mymoney=balance from bank
if @mymoney<100
print '卡上目前余额不足100，请及时充值！'
print '卡上余额为：'+mymoney
print '您的年利息为：'
select 利息=case
when balance<100 then balance*0.01
when balance > 1000 then balance*0.20
when else balance*0.10
from bank where username='张三‘
go 知识点讲解：什么是子查询？
引例讲解：现有stuinfo,stumarks两个表，stuinfo有stuname、stuno、stusex、stuage、stuseat、stuaddress等字段，如图：
老师提问：问题1：要从表中查出年龄比“李斯文”大的学生，并要求显示这些信息，思考如何实现？
实现一：采用t-sql实现：
declare @age int
select * age =stuage from stuinfo where stuname=’李斯文’
select * from stuinfo where stuage >@age
go思考：有何更简单的方法没有？
实现二： 采用子查询。
select * from stuinfo where stuage>(select stuage from stuinfo where stuname=’李斯文’)
go上述查询where子句中的（select stuage from stuinfo where stuname=’李斯文’）叫子查询，外面的查询叫父查询。sql server执行时，先查询子查询部分，得到子查询部分的值，再执行整个父查询。
使用子查询简洁，效率比采用sql变量实现的方案要高，所以推荐使用子查询。
因为子查询作为where条件的一部分，所以还可以和update、insert、delete一起使用，语法类似于select语句。
提示：当子查询与比较运算符联合使用时，必须保证子查询返回的值不能多于一个。
我们还可以通过子查询将多个结果集合并在一起，还可以将多表间的数据组合在一起，从而替换连接（join）查询。
案例讲解：使用上面的两个表，查询笔试（60分）刚好通过的学生名单。
实现方法一（采用表连接）：
select stuname from stuinfo inner join stumarks on
where wittenexam=60
go实现方法二（采用子查询）：
select stuname from stuinfo
where stuno=(select stuno from stumarks where writtenexam=60)
go一般说来，表连接可以用子查询替换，反过来则不一定。
二者区别：子查询比较灵活、方便，形式多样，适合于作为查询的筛选条件。
表连接适合于查看多表的数据。
引入：我们开始将子查询和比较运算符联合使用时，要求查询返回的值是多少？
如上面的查询考试通过的同学时，如有多个学生的笔试成绩通过60分，采用上述子查询时将会怎么样？
案例讲解：：插入一条学生笔试成绩为60分的记录，运行看看结果。
结果：如图：
从运行结果我们可以看出：
当结果超过一个时，会出现编译错误 。此时如何解解决呢？
将“=”改为“in”即可。
老师提问：问题1：现在假如我们要查询参加考试的学生名单？该如何查呢？
思路：只要学生的学号在stumarks**现，说明该学生已参加了考试。
select stuname from stuinfo
where stuno (select stuno from stumarks where writtenexam=60)
go拓展学习：not in
如查询未能通过考试的学生名单，如何查询呢？（-在in前加not）
老师提问：请同学们回忆我们以前在查询分析器中是如何建数据库和表的？
应当先用if条件检查，用exists关键字。
从理论上讲，exists可作为where语句的子查询，但一般用于if语句的存在检测。其语法为：
if exists（子查询）
语句。说明：若子查询结果非空，则exists（子查询）将返回真（true），否则返回false。
案例讲解：检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分
思路分析：采用exists检测。
if exists (select * from stumarks where writtenexam>80)
beginprint '本班有人笔试成绩高于80分，每人加2分，加分后的成绩为：'
update stumarks set writtenexam=writtenexam+2
select * from stumarks
endelse
beginprint '本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩：'
update stumarks set writtenexam=writtenexam+5
select * from stumarks
go说明：exists和in一样，允许其前添加not取反，表示不存在。
练习：检查本次考试，本班如果没有一个考试合格（双科都在60分下），则试题难，每人加3分；否则，每人加1分。
我们学习知识的目的在于应用。下面我们结合例子来学习t-sql的综合应用。
案例讲解：在ppt上显示如下两张表的详细信息：
问题： 1． 统计本次考试的缺考情况，结果如下图中的第一个结果集所示；
2． 提取学生的成绩信息并保存结果，包括学生姓名、学号、笔试成绩、机试成绩、是否通过，比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分 。加分后重新统计通过情况，如下图中的第二个结果集所示；
3． 统计通过率 。如下图第三个结果集所示。
解题思路分析：
1、 使用子查询统计缺考情况：
两个方面：应到人数、实到人数。
2、 提取学生信息并保存结果，按结果集2方式显示。
a. 提取的成绩应当包括两表的数据，考虑连接；
b. 要求添加新列“是否通过（ispass）”，可采用case…end。分数大于60分为通过，用1表示，否则用0表示。
c. 保存提取结果，用select…into new/table生成新表并保存数据。生成新表前应检查新表是否存在。
3、 比较笔试平均分和机试平均分，较低者进行循环加分，但加分后的最高分不能超过97分。
a. 定义两个变量，存放笔试平均分和机试平均分，然后用**g（）函数从表中获取数据并赋值。
b. 使用if语句判断。
c. while循环给学生加分，缺考除外。
d. 更新ispass列。
4、 提分后，统计学生的成绩和通过情况。
a. 使用别名实现中文字段名。
b. 当学生成绩为null时，替换为‘“缺考”，否则不变。
c. ispass列的1替换为是，0替换为否。
5、 统计学生的通过率情况。
打开查询分析器，按照分析，叫学生参与配合，配合上面的**并得到正确的结果。
叫学生总结：
总结我们曾学习过的查询，合并多个表中的数据的方法有三种：
联合(union)－合并多个数据表中的行。
子查询－将一个查询包含到另一个查询中。
联接－合并多个数据表中的列。
高级数据查询
1.学生管理数据库。学生表 表名 student 属性 sno 字符型，最大7个字符表示学生学号。sname 字符型，最大8个字符表示学生姓名。sage 整型表示学生年龄。ssex 字符型，最大2个字符表示学生性别。sdate 日期型表示学生入学时间。sdept 字符型，长度为20 表示所在系。约束...
高级数据查询
第6章高级数据查询。6.1 一般数据查询功能扩展6.1.1使用top限制结果集。1 top n 取查询结果的前n行数据。2 top n percent 取查询结果的n 行数据。3 with ties 表示包括最后一行取值并列的结果。6.1.2 使用case函数1.简单的case 函数case函数语法...
MySQL数据库高级查询和多表查询 二
机试测试试卷。mysql数据库应用与开发 注意 考试结束试卷必须交回，不交回试卷者成绩无效。题目 mysql 数据库高级查询和多表查询。一 语言和环境 a 实现技术 sql 语句练习 b 环境要求 mysql5.7 n icat 二 实验要求。1 创建名为 s t 的数据库，参数全部使用 utf 8...