第四章高级查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了第四章高级查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7700字，纯文字阅读大概需要12分钟。
内容图文
4.1 简单子查询 究竟什么是子查询？子查询有什么用？带着这样的疑问，我们不妨先来解决本章第一个问题。 学员信息表（stuInfo）和学员成绩表（stuMarks）的数据。 stuNamestuNostuSexstuAgestuSeatstuAddress 张秋丽s25301男181北京海淀 李思文s25303女222河
4.1 简单子查询
究竟什么是子查询？子查询有什么用？带着这样的疑问，我们不妨先来解决本章第一个问题。
学员信息表（stuInfo）和学员成绩表（stuMarks）的数据。
stuName stuNo stuSex stuAge stuSeat stuAddress
张秋丽 s25301 男 18 1 北京海淀
李思文 s25303 女 22 2 河南洛阳
李文才 s25302 男 85 3 地址不详
欧阳俊雄 s25304 男 28 4 新疆
梅超风 s25318 女 23 5 地址不详
ExamNo stuNo writtenExam labExam
s271811 s25303 90 58
s271813 s25302 60 90
s271816 s25301 87 82
问题：查看年龄比“李思文”大的学员，要求显示这些学员的信息。
实现方法一：采用T-SQL变量实现，SQL语句如示例1所示。
示例1
Declare @age int ---定义变量，用于存放“李思文”的年龄
Select @age = stuAge From stuInfo where stuName = '李思文' ---求出“李思文”的年龄
Select * from stuInfo where stuAge > @age ---筛选比“李思文”年龄大的学员
GO
实现方法二：采用子查询实现，SQL语句如示例2所示。
我们可以合并上述两步。
将语句 Select * from stuInfo where stuAge > @age
替换为如示例2所示的语句
示例2
Select * from stuInfo Where stuAge > ( select stuAge from stuInfo where stuName > '李思文')
Go
你一定明白了，上述查询中的“( select stuAge from stuInfo where stuName > '李思文')”部分，就是子查询，因为它嵌入到查询中作为Where条件的一部分。
所以它在Where语句中的一般用法如下。
Select ··· from 表1 Where 字段1 > (子查询)
除了“>” 号外，还可以使用其他运算符号，习惯上，外面的查询称为父查询，括号中嵌入的查询称为子查询。SQL Server 执行时，先执行子查询部分，求出子查询部分的值，然后再执行整个父查询。它的执行效率比采用SQL变量实现的方案要高，所以推荐采用子查询。因为子查询作为Where条件的一部分，所以还可以和Update、Insert、Delete一起使用，语法类似于Select语句。
注： 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。
上述子查询将多个结果集合并在一起，除此之外，还可以将多表间的数据组合在一起，从而替换连接（Join）查询。
问题：查询笔试刚好通过60分的学员名单。
实现方法一： 采用表连接。
示例3
Select stuName from stuInfo inner join stuMarks
On stuInfo.stuNo = stuMarks.stuNo where writtenExam = 60
GO
除了采用表连接以外，还可以采用子查询替换上述连接。
实现方法二：采用子查询
示例4
Select stuName From stuInfo where stuNo = (select stuNo from stuMarks where writtenExam = 60) GO
注：一般来说，表连接都可以用子查询替换，但反过来说却不一定，有的子查询不能用表连接来替换。子查询比较灵活、方便，形式多样，适合于作为查询的筛选条件。而表连接更适合于查看多表的数据。
4.2 IN 和 Not In 子查询
使用“=”、“>” 等比较运算符时，要求子查询只能返回一条或空的记录。在SQL Server中，香港虚拟主机，当子查询跟随在 = 、!= 、<、<=、>、>=之后，不允许子查询返回多条记录。例如上例查询笔试刚好及格的学员信息，成绩表中刚好只有一条记录满足条件：李文才（s25302）的笔试成绩刚好60分。如果有多条记录满足条件，既有多个学员的笔试成绩为60分，采用上述子查询将出现编译错误。
示例5
/*-- 采用in子查询查询参加考试的学员名单 --*/
Select stuName from stuInfo where stuNo in (select stuNo from stuMarks) GO
同理，如果希望查询未参加考试的学员名单呢？您一定想到了，加上否定的Not 即可。
/*-- 采用Not in 子查询，查看未参加考试的学员名单 --*/
Select stuName from stuInfo where stuNo Not in (select stuNo from stuMarks) GO
4.3 Exists 和 Not Exists 子查询
Exists 语句我们并不陌生，在学习创建库和创建表的语句时曾提前用过，它是一个存在检测的子查询语句。例如，如果存在数据库stuDB，则先删除它，然后重新创建。
IF Exists (select * from sysDatabases where name = 'stuDB')
Drop DataBase stuDB
Create DataBase stuDB
··· 创建的库代码略
从理论上讲，Exists 也可以作为Where语句的子查询，但一般用于IF语句的存在检测。其基本语法如下：
IF Exists(子查询)
语句
如果子查询的结果非空，则Exists(子查询)将返回真(true)，否则返回假(false)。
问题：检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分，否则，每人允许提5分。
示例8
/*-- 采用Exists子查询，进行酌情加分 --*/
IF Exists (select * from stuMarks where writtenExam > 80)
Begin
print '本班有人笔试成绩高于80分，每人只加2分，加分后的成绩为：'
Update stuMarks set writtenExam = writtenExam + 2
Select * from stuMarks
End
Else
Begin
print '本班无人笔试成绩高于80分，每人可以加5分，加分后的成绩为：'
Update stuMarks set writtenExam = writtenExam + 5
Select * from stuMarks
End
Go
Exists 和 in 一样，同样允许添加Not取反，表示不存在。
问题，检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都>60分），则试题偏难，每人加3分，否则，每人只加1分。
示例9
/*--- 采用Not Exists子查询，根据试题难度加分 ---*/
If Not Exists ( select * from stuMarks where writtenExam > 60 and LabExam > 60)
Begin
Print '本班无人通过考试，试题骗难，每人加3分，加分后成绩为：'
Update stuMarks set writtenExam = writtenExam + 3 , labExam = labExam + 3
Select * from stuMarks
End
Else
Begin
Print '本班考试成绩一般，每人只加1分，加分后的成绩为：'
Update stuMarks set writtenExam = writtenExam + 1, labExam = labExam + 1
Select * from stuMarks
End
Go
4.4 T-SQL 语句的综合应用
假定目前本次考试学员信息表(stuInfo)和学员成绩表(stuMarks)的原始数据如下：
stuName stuNo stuSex stuAge stuSeat stuAddress
张秋丽 s25301 男 18 1 北京海淀
李思文 s25303 女 22 2 河南洛阳
李文才 s25302 男 85 3 地址不详
欧阳俊雄 s25304 男 28 4 新疆
梅超风 s25318 女 23 5 地址不详
ExamNo stuNo writtenExam LabExam
s271811 s25303 93 59
s271813 s25302 63 91
s271816 s25301 90 83
s271817 s25318 63 53
问题：
（1）统计本次考试的缺考情况，结果如图第一个记录集所示。
应到人数 实到人数 缺考人数
5 4 1
姓名 学号 笔试成绩 机试成绩 是否通过
张秋丽 s25301 90 89 是
李思文 s25303 93 65 是
李文才 s25302 63 97 是
欧阳俊雄 s25304 缺考 缺考 否
梅超风 s25318 63 59 否
总人数 通过人数 通过率
5 3 60%
（2）提取学员的成绩信息并保存结果，香港服务器，包括学员姓名、学号、笔试成绩、机试成绩、是否通过。
（3）比较笔试平均分和机试平均分，较低者进行循环提分，但提分后最高分不能超过97分。
（4）提分后，香港空间，统计学员的成绩和通过情况，如上图第二个记录集所示。
（5）提分后统计学员的通过率情况，如上图第三个记录集所示。
示例10
/*--- 本次考试的原始数据 ---*/
select * from stuInfo
select * from stuMarks
/*--- 统计考试缺考情况 ---*/
select 应到人数=(select Count(*) from stuInfo),
实到人数=(select count(*) from stuMarks),
缺考人数=((select count(*) from stuInfo) - (select count(*) from stuMarks))
/*--- 统计考试通过情况，并将统计结果存放在新表newTable中 ---*/
IF Exists (select * from sysobjects where name = 'newTable')
Drop table newTable
Select stuName, stuInfo.stuNo, writtenExam, labExam,
isPass = Case
When writtenExam >= 60 and labExam >=60 Then 1
Else 0
End
Info newTable from stuInfo Left Join stuMarks
On stuInfo.stuNo = stuMarks.stuNo
/*--- 酌情加分：比较笔试和机试平均分，哪科偏低，就给哪科提分 ---*/
Declare @avgWritten numeric (4,1), @avgLab numeric(4,1) ---定义变量存放笔试和机试平均分
Select @avgWritten = Avg(writtenExam) from newTable where writtenExam IS Not null
Select @avgLab = Avg(labExam) from newTable where labExam IS Not null
IF @avgWritten<@avgLab ---比较笔试和机试平均分，看哪科偏低
While (1=1) ---循环给笔试加分，最高分不能超过97分
Begin
Update newTable set writtenExam = writtenExam + 1
If(select Max(writtenExam) From newTable ) >= 97
Break
End
Else
While(1=1) ---循环给机试加分，最高分不能超过97分
Begin
Update newTable set labExam = labExam + 1
If(select Max(labExam) from newTable) >= 97
Break
End
--- 因为提分，所以需要更新isPass（是否通过）列的数据
Update newTable
Set isPass = Case
When writtenExam >= 60 and labExam >= 60 Then 1
Else 0
End
/*--- 显示考试最终通过情况 ---*/
Select 姓名=stuName, 学号=stuNo
笔试成绩=Case
when writtenExam IS null then '缺考'
Else convert(varchar(5), writtenExam)
End
机试成绩=Case
When labExam IS null then '缺考'
Else convert(varchar(5),labExam)
End
是否通过=Case
When isPass = 1 then '是'
Else '否'
End
From newTable
/*--- 显示通过率及通过人数 ---*/
Select 总人数 = Count(*) , 通过人数 = Sum(isPass),
通过率 = (convert (varchar(5), avg(isPass*100)) + '%' ) From newTable
Go
内容总结
以上是互联网集市为您收集整理的第四章高级查询全部内容，希望文章能够帮你解决第四章高级查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。