第五章 高级查询.docx

第五章 高级查询.docx

《第五章 高级查询.docx》由会员分享，可在线阅读，更多相关《第五章 高级查询.docx（19页珍藏版）》请在冰豆网上搜索。

第五章高级查询

第五章高级查询

5.1子查询概述

5.2无关子查询

5.3相关子查询

5.4子查询在INSERT、UPDATE、DELETE中的应用

5.5集合操作

5.1子查询概述

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块，如将一个查询块嵌套在另一个查询块的子句中，这种查询称为嵌套查询，被嵌套的查询块称为子查询，外面的查询块称为主查询。

子查询一般嵌套在WHERE子句和FROM子句中使用，使用时要用括号括起来，嵌套层数最多为32层。

5.1.1WHERE子句中嵌套子查询

WHERE子句中嵌套子查询时，子查询放在条件表达式里，一般返回单一数值或数值列表。

子查询嵌套在WHERE子句中的语法格式：

SELECT<目标列名>

FROM<表名>

WHERE[<列名>]<操作运算符>

（SELECT<目标列名>

FROM<表名>

WHERE<条件表达式>）

说明：

Ø“<操作运算符>”可以是关键字IN、NOTIN、EXISTS、NOTEXISTS，或是比较运算符、ALL+比较符、ANY+比较符。

Ø当使用关键字EXISTS和NOTEXISTS时，“<操作运算符>”前不需要“<列名>”。

例（加）：

查询成绩小于88的学生的学号、姓名。

SELECTStu_ID'学号',Stu_Name'姓名'

FROMStudent

WHEREStu_IDIN

（SELECTStu_IDFROMStu_CourseWHERE

Score<88）

✓操作运算符IN判断运算符左面的值是否属于右面的集合，是则返回值为TRUE，否则返回值为FALSE。

例5-1：

查询学分大于3的所有课程的课程编号、课程名称和学分信息。

不用嵌套子查询：

selectCourse_ID,Course_Name,Course_Credit

fromCourse

whereCourse_Credit>3

用嵌套子查询：

selectCourse_ID,Course_name,Course_Credit

fromCourse

whereCourse_IDin

（SelectCourse_IDfromCoursewhere

Course_Credit>3）

例5-2：

查询成绩超过平均值的所有学生的学号、姓名、课程名称和成绩信息，并按由高到低的成绩顺序排列。

SELECTa.Stu_ID'学号',a.Stu_Name'姓名',b.Course_Name'课程名称',c.Score'成绩'

FROMStudentASaJOINStu_CourseASc

ONa.Stu_ID=c.Stu_IDJOINCourseASb

ONb.Course_ID=c.Course_ID

WHEREc.Score>（SELECTAVG（Score）FROM

Stu_Course）

ORDERBYc.ScoreDESC

思考：

查询所有选修了3号课程的学生的姓名

selectStu_name

fromStudent

whereStu_IDin（selectStu_IDfromStu_CoursewhereCourse_ID='3'）

5.1.2FROM子句中嵌套子查询

在FROM子句中嵌套子查询时将子查询的结果作为主查询的查询数据源，即将子查询的结果看作一个虚拟的表或视图。

子查询嵌套在FROM子句中的语法格式：

SELECT<目标列名>

FROM（SELECT<目标列名>

FROM<表名>

WHERE<条件表达式>）AS<别名>

WHERE<条件表达式>

Ø子查询后的“AS<别名>”用来为子查询的结果指定别名，以便于主查询使用子查询的结果。

实际应用中，嵌套在FROM子句中的情况比较少。

例5-3：

查询选修了课程“数据结构”和“操作系统”的学生的姓名和学号信息。

不用嵌套子查询：

SELECTc.Stu_ID,c.Stu_Name,d.Course_Name

FROMStudentAScJOINStu_CourseASe

ONc.Stu_ID=e.Stu_IDJOINCourseASdONd.Course_ID=e.Course_ID

WHEREd.Course_Name='数据结构'or

d.Course_Name='操作系统'

SELECTdistincta.Stu_Name'姓名',a.Stu_ID'学号'

FROM

（SELECTc.Stu_ID,c.Stu_Name,d.Course_Name

FROMStudentAScJOINStu_CourseASeON

c.Stu_ID=e.Stu_IDJOINCourseASdON

d.Course_ID=e.Course_ID）

ASa

WHEREa.Course_Name='数据结构'ora.Course_Name='操作系统'

5.2无关子查询

无关子查询是指在主查询之前运行，并返回结果供主查询使用的子查询。

即子查询的运行与主查询没有关系，而主查询的运行要使用子查询的结果。

在WHERE子句中，无关子查询的操作运算符采用IN、NOTIN和比较运算符。

5.2.1单行子查询

单行子查询即单独采用比较运算符的子查询，子查询返回的结果只有一行数据。

用于单行子查询的比较运算符有等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、不等于（<>）。

例5-4：

刘振江今年22岁，查询和他同属一个系且年龄比他小的学生的信息。

SELECTStu_ID,Stu_Name,Sage,Sdept

FROMStudent

WHERESdept=（SELECTSdeptFROMStudent

WHEREStu_Name='刘振江'）

ANDSage<22

等同于：

SELECTStu_ID,Stu_Name,Sage,Sdept

FROMStudent

WHERESdeptin（selectSdeptfromStudentwhereStu_Name='刘振江'）

andSage<22

思考：

查询比马宁年纪大的教师编号、姓名

selectTea_ID,Tea_Name

fromTeacher

whereTea_Age>（selectTea_AgefromTeacher

whereTea_Name='马宁'）

5.2.2多行子查询

多行子查询是指查询结果返回的是一行或多行数据的子查询。

当主查询语句的WHERE子句引用子查询结果时，必须采用多行比较符号IN、NOTIN或ALL+比较符、ANY+比较符来进行比较。

ØIN的含义是匹配子查询结果中的任一个值。

ØNOTIN是与子查询结果中的任一个值都不匹配，

ØALL则必须要符合子查询的所有值，

ØANY要符合子查询结果的任何一个值。

ALL和ANY操作符不能单独使用，只能与比较运算符配合使用，它们的具体含义如下：

ALL，表示所有的。

Ø

Ø>ALL比子查询返回的所有结果都大，即大于返回结果中的最大值；

Ø=ALL无意义，逻辑上不成立。

ANY，表示任意的。

Ø

Ø>ANY比子查询返回的任意一个结果大即可，即大于返回结果中的最小值；

Ø=ANY和子查询返回的任意一个结果相等即可，相当于IN。

例5-5：

查询“物理系”学生的学号、选修课程编号和成绩信息。

SELECTStu_ID,Course_ID,Score

FROMStu_Course

WHEREStu_IDIN

（SELECTStu_IDFROMStudent

WHERESdept='物理系'）

例5-6：

查询其他系年龄大于计算机科学与技术系学生年龄的学生信息。

SELECTStu_ID,Stu_Name,Sage,Sdept

FROMStudent

WHERESage>ALL（SELECTSageFROMStudentWHERESdept='计算机科学与技术系'）

AndSdept<>'计算机科学与技术系'

例5-7：

查询其他系年龄大于计算机科学与技术系中任意一个学生年龄的学生信息。

SELECTStu_ID,Stu_Name,Sage,Sdept

FROMStudent

WHERESage>ANY（SELECTSageFROMStudentWHERESdept='计算机科学与技术系'）

andSdept<>'计算机科学与技术系'

思考题：

查询其他系中比物理系所有学生年龄都小的学生的学号、姓名、年龄，并按学号降序排序。

SELECTStu_ID学号,Stu_Name姓名,Sage年龄

FROMStudent

WHERESage

WHERESdept='物理系'）

/*andSdept<>'物理系'*/

OrderbyStu_ID

5.3相关子查询

相关子查询是指每次执行查询都需要依赖主查询结果的子查询。

由于相关子查询会对主查询的每一个可能结果都执行一次查询，因而又称重复子查询。

相关子查询不能单独执行，这一点与无关子查询不同。

例5-8：

查询年龄大于各系年龄平均值的所有学生的学号、姓名、年龄和所在系信息。

分析：

SELECTStu_ID,Stu_Name,Sage,Sdept

FROMStudent

WHERESage>（SELECTAVG（Sage）FROMStudent）

SELECTSdept系部,avg（Sage）年龄

FromStudent

groupbySdept

SQL编写：

SELECTStu_ID,Stu_Name,Sage,Sdept

FROMStudenta

WHERESage>

（SELECTAVG（Sage）

FROMStudent

WHERESdept=a.Sdept）

说明：

Ø主查询首先查询学生表（Student）的第一行，并将第一行的所在系（Sdept）的列值交给子查询，子查询根据该列值查询所对应系的学生年龄平均值，并返回给主查询；主查询将该行的年龄（Sage）的列值与平均值比较，如果大于平均值，则将该数据行放入查询结果中；依次类推，直到主查询查询到学生表（Student）的最后一行为止。

思考：

对Stu_Course表，查询成绩大于各门课程平均分的所有学生的学号、所修课程号、成绩

selectStu_ID,Course_ID,Score

fromStu_Courseasa

whereScore>（selectAVG（Score）

fromStu_Course

whereCourse_ID=a.Course_ID）

5.3.1EXISTS子查询

将EXISTS关键字引入子查询后，子查询的作用就相当于进行存在测试。

主查询的WHERE子句测试子查询返回的行是否存在，子查询不产生任何数据。

使用EXISTS的子查询的语法格式：

WHEREEXISTS（SELECT<目标列名>

FROM<表名>

WHERE<条件表达式>）

由于子查询并不返回具体的值，所以子查询的“<目标列名>”常使用符号“*”。

例5-9：

查询选修了课程的学生的学号和姓名信息。

SELECTStu_ID,Stu_Name

FROMStudenta

WHEREEXISTS

（SELECT*

FROMStu_Course

WHEREStu_ID=a.Stu_ID）

等同于：

SELECTStu_ID,Stu_Name

FROMStudenta

WHEREStu_IDin

（SELECTStu_ID

FROMStu_Course

WHEREStu_ID=a.Stu_ID）

5.3.2NOTEXISTS子查询

NOTEXISTS与EXISTS的使用方法类似，用于查找在某数据表中不存在的数据行。

使用NOTEXISTS的子查询的语法：

WHERENOTEXISTS（SELECT*

FROM<表名>

WHERE<条件表达式>）

例5-10：

查询未选修课程学生的学号和姓名信息。

SELECTStu_ID,Stu_Name

FROMStudenta

WHERENOTEXISTS

（SELECT*

FROMStu_Course

WHEREStu_ID=a.Stu_ID）

思考题：

查询所有选修了2号课程的学生的学号；查询所有未选2号课程的学生的学号。

selectStu_ID

fromStudentasa

whereexists

（select*fromStu_Course

whereStu_ID=a.Stu_IDandCourse_ID='2'）

selectStu_ID

fromStudentasa

wherenotexists

（select*fromStu_Course

whereStu_ID=a.Stu_IDandCourse_ID='2'）

5.4子查询在INSERT、UPDATE、DELETE中的应用

5.4.1在INSERT中嵌套子查询

在INSERT语句中嵌套子查询可以将其它表的数据行添加到当前表中。

在INSERT语句中嵌套子查询比使用多个单行的INSERT语句效率要高得多。

嵌套子查询的INSERT语句的语法格式：

INSERTINTO<表名1>（<列名1>）

SELECT<列名2>

FROM<表名2>

WHERE<条件表达式>

Ø“<表名1>”是接受数据的表；

Ø“<表名2>”是提供数据的源表；

Ø“<列名1>”是接受数据的表的列，它应与“<列名2>”中的各列相对应。

例5-11：

表Stu_Sdept结构与学生表（Student）相同，要求将学生表（Student）中的所有物理系学生的数据复制到Stu_Sdept表中。

Usetestdb

Go

CreatetableStu_Sdept（

Stu_IDchar（12）primarykey,

Stu_Namevarchar（50）,

Stu_Passwdvarchar（50）,

Ssexchar

（2）default（'男'）check（Ssex='男'orSsex='女'）,

Sageint,

Sdeptvarchar（50）

）

go

INSERTINTOStu_Sdept（Stu_ID,Stu_Name,Stu_Passwd,Ssex,Sage,Sdept）

SELECTStu_ID,Stu_Name,Stu_Passwd,Ssex,Sage,SdeptFROMStudentWHERESdept='物理系'

5.4.2在UPDATE中嵌套子查询

在UPDATE语句中嵌套子查询实现根据其它表的信息更新当前表中的数据。

在UPDATE语句中嵌套子查询的语法格式：

UPDATE<表名1>SET<列名1>=<表达式>

WHERE[<列名2>]<操作运算符>

（SELECT<列名3>

FROM<表名2>

WHERE<条件表达式>）

说明：

Ø“<表名1>”是被更新数据的表，“<表名2>”是提供更新条件的表；

Ø“<列名1>=<表达式>”是为更新列赋值；

Ø“<列名2>”是更新表的列；“<列名3>”是提供更新条件表的列；

Ø“<操作运算符>”可以是关键字IN、NOTIN、EXISTS、NOTEXISTS，或是比较运算符、ALL+比较符、ANY+比较符。

例5-12：

由于某种原因，所有学生的“高等数学”成绩都少计算了5分，要求采用UPDATE语句对学生的成绩进行修正。

UPDATEStu_courseSETScore=Score+5

WHERECourse_IDIN

（SELECTCourse_ID

FROMCourse

WHERECourse_Name='高等数学'）

思考题：

将所有学生的“数据库系统概论”课程的成绩置0

UPDATEStu_courseSETScore=0

WhereCourse_IDin

（selectCourse_IDfromCourse

whereCourse_Name='数据库系统概论'）

5.4.3在DELETE中嵌套子查询

如果删除数据的条件涉及多个数据表，或删除操作的条件不只一个，则需要借助嵌套子查询来实现删除。

嵌套子查询的DELETE语句的语法格式：

DELETEFROM<表名1>

WHERE[<列名1>]<操作运算符>

（SELECT<列名2>

FROM<表名2>

WHERE<条件表达式>）

说明：

Ø“<表名1>”是要删除数据的表，“<表名2>”是提供删除数据条件的表；

Ø“<列名1>”是删除数据表的列；“<列名2>”是提供删除数据条件表的列；

Ø“<操作运算符>”可以是关键字IN、NOTIN、EXISTS、NOTEXISTS，或是比较运算符、ALL+比较符、ANY+比较符。

例5-13：

删除所有学生“数据结构”课程的成绩。

DELETEFROMStu_course

WHERECourse_IDIN

（SELECTCourse_IDFROMCourse

WHERECourse_Name='数据结构'）

select*fromStu_Course

5.5集合操作

5.5.1UNION运算符

UNION运算符可以将两个或两个以上SELECT语句的查询结果集合并成一个结果集。

UNION运算不同于使用连接查询方式合并两个表中列的运算。

UNION的语法格式：

SELECT语句1

UNION[ALL]

SELECT语句2

UNION[ALL]

…

UNION[ALL]

SELECT语句n

说明：

Ø关键字ALL表示将所有行（包括重复行）合并到结果集合中。

如果缺省该关键字，则结果集合中重复行将只保留一行。

Ø涉及两个以上的SELECT语句的合并，执行顺序是自左至右，可以使用括号改变执行顺序。

Ø所有SELECT语句中的列的数目必须相同；

Ø所有SELECT语句中的列都必须具有相同的数据类型，或是可以自动转换的数据类型（对于数值类型，低精度的数据类型自动转换为高精度的数据类型）；

Ø所有SELECT语句中的列的顺序必须相同，因为UNION运算符在合并时将按照各个SELECT语句给定的列顺序一对一地比较各列。

例5-14：

将在课程表（Course）中查询得到的课程号1至4与课程号3至6的结果合并，要求合并的结果中包含课程编号、课程名称和学分信息。

SELECTCourse_ID'课程编号',Course_Name'课程名称',Course_Creidt'学分'

FROMCourse

WHERECourse_IDBETWEEN1AND4

UNIONALL

SELECTCourse_ID'课程编号',Course_Name'课程名称',Course_Creidt'学分'

FROMCourse

WHERECourse_IDBETWEEN3AND6

例5-15：

将在课程表（Course）中查询得到的课程号1至4与课程号3至6的结果合并，要求合并的结果中不能出现重复信息，结果包含课程编号、课程名称和学分信息。

SELECTCourse_ID'课程编号',Course_Name'课程名称',Course_Creidt'学分'

FROMCourse

WHERECourse_IDBETWEEN1AND4

UNION

SELECTCourse_ID'课程编号',Course_Name'课程名称',Course_Creidt'学分'

FROMCourse

WHERECourse_IDBETWEEN3AND6

思考题：

在Stu_Course表中，查询在4号和6号课程中，至少选修了一门课程的学生学号，要求不要出现重复行。

SELECTStu_IDFROMStu_CourseWHERECourse_ID='4'

UNION

SELECTStu_IDFROMStu_CourseWHERECourse_ID='6'

比较：

SELECTdistinctStu_IDFROMStu_Course

WHERECourse_ID='4'orCourse_ID='6'

5.5.2EXCEPT运算符

EXCEPT运算符实现两个SELECT语句的查询结果集的集合差操作，即从左查询中返回右查询没有找到的所有非重复数据行。

EXCEPT的语法格式：

SELECT语句1

EXCEPT

SELECT语句2

“SELECT语句1”与“SELECT语句2”的查询结果集必须具有相同的结构，即它们的列数必须相同，相应的结果集列的数据类型必须相同或是可以自动转换的数据类型。

说明：

Ø如果两个SELECT语句的查询返回的可比较列的