当前位置:Gxlcms > mysql > 第四章高级查询

第四章高级查询

时间:2021-07-01 10:21:17 帮助过:27人阅读

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

人气教程排行