关系数据库设计与应用（工作手册式） 课件 第6、7章 SQL查询、SQL的高级功能

第6章SQL查询学习目标LEARNINGTARGET掌握查询语句的基本结构；掌握投影、选择及连接等基本查询操作。知识目标能力目标会用SQL语句进行基本查询；能进行聚集查询、连接查询、子查询和集合查询等高级查询。思政目标养成严谨认真的学习态度；培养团队协作精神；了解大数据时代的工匠精神，并将其融入学习和工作当中。主要内容MAINCONTENTS6.1SQL基本查询语句6.2聚集查询6.3连接查询6.4子查询6.5集合查询6.6关于引用AS指定名字的规则SQL基本查询语句6.1SQL基本查询语句6.1.1查询语句的基本结构6.1.2投影6.1.3选择6.1.4对查询结构进行排序SQL基本查询语句数据检索就是把数据库中存储的数据根据用户的需求提取出来的过程。SQL语言中查询语句使用SELECT语句来执行；查询语句是数据操作中最基本和最重要的语句之一，其功能是从数据库中检索满足条件的数据；查询的数据源可以是一张表，也可以是多张表，甚至是视图；查询的结果是由0行或多行记录组成的一个记录集合，并允许选择一个或多个字段作为输出字段；SELECT语句还可以对查询的结果进行排序，汇总等。6.1.1查询语句的基本结构SQL基本查询语句select语句具有数据查询、统计、分组和排序的功能。查询语句的基本格式：select<目标列组>

--需要输出哪些列from<数据源>--来自于哪些表[where<元组选择条件>]--根据什么条件[groupby<分组依据列>][having<组提取条件>][orderby<排序依据列>]6.1.1查询语句的基本结构SQL基本查询语句select子句：用于指明查询结果集的目标列。目标列可以是直接从数据源中投影得到的属性以及与属性相关的表达式或数据统计的函数表达式，目标列甚至还可以是常数。from子句：用于指明查询的数据源，数据源可以是基本表或视图。where子句：描述选择条件。groupby子句：将查询结果的各行按一列取值相等的原则进行分组，如果有having短语，则查询结果只是满足指定条件的组。orderby子句：查询结果按一定顺序排序。注意：以上子句中，select子句和from子句是必须的，其他子句都是可选的。在SQL语句中，关键字对大小写不敏感；“--”表示注释。6.1.1查询语句的基本结构SQL基本查询语句检索学生表中所有学生的全部信息检索每个学生的姓名和班级Select

from

Select

from

*学生表注意：可以用

*

代表关系中所有的属性；当选择某几个属性时，属性之间用逗号(英文状态下)分开；返回的关系中的属性顺序和Select子句列出的属性顺序相同。姓名,班级学生表1.选择列6.1.2投影SQL基本查询语句改变列标题有两种方法：使用“=”；使用AS关键字。①当使用“=”时，语法形式为：新标题=列名如：select

学号,姓名,所在班级=班级

from

学生表②当使用“AS”时，语法形式为：列名AS新标题如：select学号,姓名,班级AS所在班级from学生表2.改变列标题在默认情况下，数据检索结果集中所显示出来的列标题就是在创建表时所使用的列名。6.1.2投影SQL基本查询语句例如：要求出成绩提高20%后的成绩，则可用如下表达式描述：select

学号,课程号,成绩,成绩*1.2AS调整后的成绩成绩from

选课表3.带表达式的select子句select子句中可以包含算术表达式，允许+、-、*、/，当然也可以是常量表达式。在查询结果中，还可以增加一些常量，改变查询结果的显示格式，提高结果关系中数据的可读性。注意：比较增加一列与改列名的区别（AS关键字使用）如：增加一常量列：增加‘届别’列，并取名为“2012级学生”select

*,‘2012级学生’AS届别

from

学生表6.1.2投影SQL基本查询语句例如：检索出所有的班级信息select

班级from

学生表selectdistinct

班级from

学生表4.ALL与DISTINCTSQL允许关系和查询结果中出现重复行，要强制消除重复，可在select后使用关键字DISTINCT，而指定ALL则不消除重复。在SQLServer中，默认为ALL。注意：关键字DISTINCT针对的是select子句后面的所有属性不能出现重复，而不是针对select后面第一个属性不能重复。6.1.2投影SQL基本查询语句5.用TOP关键字返回部分数据如果SELECT查询的结果集非常大，可以使用TOP关键字限制其返回的行数。返回行数的方法有两种：一是可以指定返回记录的数量，二是可以指定返回记录的比例。（1）用“TOPn”表示返回最前面的n行记录；（2）用“TOPnpercent”表示返回最前面的n%行记录。6.1.2投影例：查询学生表前10行信息。SELECTTOP10*FROM学生表SQL基本查询语句5.用TOP关键字返回部分数据如果SELECT查询的结果集非常大，可以使用TOP关键字限制其返回的行数。返回行数的方法有两种：一是可以指定返回记录的数量，二是可以指定返回记录的比例。（1）用“TOPn”表示返回最前面的n行记录；（2）用“TOPnpercent”表示返回最前面的n%行记录。6.1.2投影例：查询学生表前10%的学生信息。SELECTTOP10percent*FROM学生表SQL基本查询语句根据要求完成查询语句：①

给学生表增加一列，标题为“系别”，内容为“计算机”。②将课程表中的“教师”列的列名改成“教师姓名”。（分别用两种方法）③查询所有已经被学生选过的课程号。课堂练习1：15分钟SQL基本查询语句根据要求完成查询语句：①给学生表增加一列，标题为“系别”，内容为“计算机”。select*,'计算机'as系别from学生表

将课程表中的“教师”列的列名改成“教师姓名”。（分别用两种方法）select课程号,课程名,教师as教师姓名,周课时数,备注from课程表select课程号,课程名,教师姓名=教师,周课时数,备注from课程表③查询所有已经被学生选过的课程号。selectdistinct课程号from选课表课堂练习1参考答案SQL基本查询语句在SELECT语句中，WHERE子句指定要检索的数据行，只有满足关键字WHERE中指定条件的元组才能出现在结果关系中。1.比较运算符WHERE子句的语法允许在表列的名称之后和列值之前使用比较运算符（=、>、<、>=、<=、<>），在比较运算条件的运算项中，可以使用常量或关系中的属性，还可以使用通用的算术运算符（+、-、*、/）。例6.6：要求检索出选课表中所有成绩在95分以上的同学的学号、课程号和成绩。select*from

选课表where

成绩>956.1.3选择SQL基本查询语句1.比较运算符在上面的查询语句中，指定的条件是数字。如果指定的条件是字符串类型的，那么需要把字符串用单引号引起来。例6.7：要求查询出班级为GZ02计5班的全部同学的信息。select*from

学生表where

班级='GZ02计5'6.1.3选择SQL基本查询语句在WHERE子句中，可使用逻辑运算符把若干查询条件连接起来，组成复合条件。这些逻辑运算符包括AND、OR、NOT。优先级从高到低依次是NOT、AND、OR。例6.8：检索选修了1号课程并且成绩在80分以上的同学的学号和成绩。SELECT

学号,成绩FROM

选课表

WHERE

课程号=’1’AND

成绩>802.逻辑运算符6.1.3选择SQL基本查询语句BETWEEN...AND...(NOTBETWEEN...AND...)可以指定搜索范围，可以用来查找值在（不在）指定范围内的元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限，表示在这个之间。例6.9：要求检索出学生表中年龄在20-23之间的所有男同学的信息。SELECT*FROM学生表WHERE年龄BETWEEN20AND23AND性别=‘M’3.BETWEEN...AND(NOTBETWEEN...AND)6.1.3选择SQL基本查询语句当然，对于

BETWEEN关键字，也可以使用比较运算符来代替。WHERE年龄>=20AND年龄<=23AND性别=‘M’注意：在用BETWEEN...AND...指定查询范围时，如果是针对char、varchar、text、datetime、smalldatetime等数据类型的数据，则一定要用单引号引起来。3.BETWEEN...AND(NOTBETWEEN...AND)6.1.3选择SQL基本查询语句IN或者NOTIN关键字允许指定要选择的取值表，意思是包含在由IN指定的列表之中或不包含在由NOTIN指定的列表中。select*from学生表where班级in（'GZ02计5','GZ02计6','GZ02计7'）4.IN(NOTIN)例6.10：要求检索出学生表中GZ02计5、GZ02计6、GZ02计7三个班级的同学信息。select*from

学生表where

班级='GZ02计5'or

班级='GZ02计6'or

班级='GZ02计7'提问：此题的选择条件如何用逻辑运算符实现？6.1.3选择SQL基本查询语句如果需要从数据库中检索出一批记录，但又不能给出精确的字符查询条件，这时就可以采用字符串的模糊匹配。使用

LIKE

关键字。LIKE关键字用于检索与特定字符串相匹配的记录行。LIKE关键字后面跟一个值段而不是一个完整的列值。列名【NOT】LIKE<匹配字符串>LIKE关键字使用形式如下：5.字符串模糊匹配(LIKE)6.1.3选择SQL基本查询语句匹配字符串是一种特殊的字符串，其特殊之处在于它不仅可以包含普通字符，还可以包含通配符。通配符用于表示任意字符或字符串。在LIKE关键字前面也可以使用NOT，表示对结果取反。LIKE关键字中，可以使用4种匹配符。%（百分号）：匹配0个或多个字符；_（下划线）：匹配任意一个字符；[]：匹配[]中的任意一个字符；[^]：不匹配[^]中的任意一个字符。5.字符串模糊匹配(LIKE)6.1.3选择SQL基本查询语句示例：LIKE'BR%'返回以“BR”开始的任意字符串。LIKE'%en'返回以“en”结束的任意字符串。LIKE'%en%'返回包含“en”的任意字符串。LIKE'_en'返回以“en”结束的3个字符串。LIKE'[CK]%'返回以“C”或者

“K”开始的任意字符串。LIKE'M[^C]%'返回以“M”开始且第2个字符不是“C”的任意字符串。5.字符串模糊匹配(LIKE)6.1.3选择SQL基本查询语句请思考以下查询的匹配字符串设置查询第一个字符是“A”、中间包含“BC”的字符串。查询以“AB”开始、第四个字符是“C”，并以“d”结束的字符串。查询第二个字符是“A”或“B”、第四个字符不是“C”的长度为6个字符的字符串。5.字符串模糊匹配(LIKE)想一想‘A%BC%’‘AB_C%d’‘_[AB]_[^C]__’6.1.3选择SQL基本查询语句例6.11：检索出学生表中所有姓“陈”的同学。select*from学生表where姓名like‘[陈李刘]%’注意：在使用关键字LIKE进行数据检索时，应注意，一个英文字母和一个汉字都被当做一个字符。5.字符串模糊匹配(LIKE)例6.12：要求查询学生表中姓陈或姓李或姓刘的学生情况。Selectfromwhere姓名like‘陈%'学生表*

6.1.3选择SQL基本查询语句空值（NULL）在数据库中有特殊的含义。空值特性：等价于没有任何值。与0、空字符串或空格不同。排序在其他数据前面。在计算过程和大多数函数中均可使用空值。6.涉及空值的查询6.1.3选择SQL基本查询语句判断是否为空值的语句格式为：列名IS[NOT]NULL例6.13：要求查询已经选课，但无考试成绩的学生的学号和相应的课程号。select学号，课程号from选课表where成绩ISNULL6.涉及空值的查询如果要查询所有有考试成绩的学生的学号和课程号select学号，课程号from选课表where成绩ISNOTNULL6.1.3选择SQL基本查询语句WHERE子句常用的查询条件如下（P136）：总结归纳序号查询条件谓词1比较运算=、>、<、>=、<=、<>（或！=）2逻辑运算AND、OR、NOT3确定范围BETWEEN...AND...、(NOTBETWEEN...AND...)4确定集合IN、NOTIN5字符匹配LIKE、NOTLIKE6空值ISNULL、ISNOTNULL6.1.3选择SQL基本查询语句课堂练习2：①查找出所有班级为“GZ02计6”和“GZ02计7”班年龄在18至20岁之间的女学生信息②查找班级中包含’计’的男学生的学号和姓名，并在最后插入一列，标题为“爱好”，内容为“运动”③查所有姓张的学生学号，姓名④查所有姓张的且为单名的学生学号，姓名⑤查询班级包含'计'或'财'字的学生信息⑥查询班级中不包含'计'和'财'字的学生信息20分钟SQL基本查询语句课堂练习2答案：①select*from学生表where年龄between18and20and性别='F'and班级in('GZ02计6','GZ02计7')②select学号,姓名,班级,'运动'as爱好from学生表where班级like'%计%'and性别='M’③select学号,姓名from学生表where姓名like‘张%'④select学号,姓名from学生表where姓名like‘张_'⑤select*from学生表where班级like'%计%'or班级like'%财%‘或者select*from学生表where班级like'%[计财]%'⑥select*from学生表where班级notlike'%计%'and班级notlike'%财%'或者select*from学生表where班级notlike'%[计财]%'或者select*from学生表where班级like'%[^计][^财]%'SQL基本查询语句在SQL语句中，排序就是指用

ORDERBY子句排列查询结果的顺序。ORDERBY只能在最终结果上进行操作。ORDERBY语句使用升序（ASC）或降序（DESC）指定一组列。系统默认的排列顺序是升序。6.1.4对查询结果进行排序如：ORDERBY学号ASCORDERBY学号DESCORDERBY学号SQL基本查询语句例1：将选课表中的所有学生成绩按降序排列6.1.4对查询结果进行排序例6.14：检索出选课表中所有选修课程号为1并且成绩在95分以上的同学的学号和成绩，并要求按成绩从高到低的顺序排列，若成绩相同再按学号升序排列。SELECT

*FROM

选课表ORDERBY成绩DESCSELECT

学号，成绩FROM

选课表WHERE

ORDERBY课程号=1AND成绩>=95成绩DESC，学号

注意：对于多个排序字段的情况，按照先后，定义为主次顺序。问：求学生的总人数？SQL基本查询语句课堂练习3：①查找选择的课程号为’1’或’4’，并且成绩在80至90之间的所有选课信息，并要求按成绩升序、学号降序排序。②查询学生表中姓“王”，且最后一个字为“丰”的学生信息，并将班级列改名为“所在班级”，且按照学号升序、性别降序排序。15分钟聚集查询6.2聚集查询6.2.1聚集函数6.2.2使用groupby子句6.2.3使用having子句筛选结果集6.2.4对where、groupby、having的思考聚集查询概念：也称为集函数、聚合函数或计算函数。作用：是对一组值进行计算并返回一个单值。用法：集函数放在select语句中列名的位置；常与groupby子句一同使用，按照给定的条件进行分组，计算一些合计值。6.2.1聚集函数聚集查询count函数count（*）——取行数（返回所选择行的数量）count（列名）——求某字段不为NULL(空)的行数count（distinct列名）——不计重复的值avg()——取平均值

max()——取最大值min()——取最小值sum()——求和注意：COUNT用于统计数量，而SUM用于值相加求和。6.2.1聚集函数聚集查询集函数的NULL忽略策略？讨论除count(*)外，其它函数在对某个字段计算时均忽略该字段的NULL值。6.2.1聚集函数聚集查询例6.15：要求查询出所有选修1号课程的学生的最高分、最低分及平均分。MAX(成绩)AS最高分,MIN(成绩)AS最低分,AVG(成绩)AS平均成绩选课表课程号=1SELECTFROMWHERE选课表条件统计值6.2.1聚集函数聚集查询COUNT函数的用法SELECTCOUNT(*)AS所有记录数,

COUNT(学号)AS所有学生数,

COUNT(班级)AS班级列数量,

COUNT(DISTINCT班级)AS班级数FROM学生表问题1：若表中无NULL值，则上面哪几个表达式值相同。问题2：比较SUM(DISTINCT列名)与SUM(列名)的区别。6.2.1聚集函数聚集查询提问SELECT

COUNT(*)AS所有记录数，

COUNT(课程名)AS所有课程数，

COUNT(教师)AS教师列数量，

COUNT(DISTINCT

教师)AS教师数FROM

课程表如果课程表中共有5条记录，其中有一条记录中的教师为NULL值，则查询结果中哪几列值相同(假设教师列的值有2项重复)？6.2.1聚集函数聚集查询①查询选课表2号课程的最高分、最低分、总分和平均分。②列出全校男同学的人数。课堂练习4：10分钟6.2.1聚集函数聚集查询1.作用：将表中的数据根据某一列进行分组。2.用法格式：groupby字段名常与集函数一起使用，对分组后的每组数据进行汇总集函数本身只能产生一个单个的汇总数据，使用groupby子句之后，就可以生成分组的汇总数据。6.2.2使用groupby子句聚集查询

select

<目标列组>

from<数据源>[where<元组选择条件>][groupby<分列组>][having<组选择条件>组][orderby<排序列1>asc|desc[，…]]3.语法格式6.2.2使用groupby子句分组字段聚集查询例1：求各门课的课程号，最高分，最低分，平均分。统计值SELECT

FROM

groupby

max(成绩)AS最高分,min(成绩)AS最低分,avg(成绩)AS平均分

选课表课程号课程号，注意：分组查询时一般先显示分组字段6.2.2使用groupby子句例2：如何求出每个班级的人数及平均年龄？分组字段聚集查询

统计值SELECT

FROM

groupby

count(学号)AS班级人数,avg(年龄)AS平均年龄学生表班级班级，注意：所有在groupby字句中指定的字段，都必须出现在select语句的选择列表中。6.2.2使用groupby子句分组字段聚集查询例3：要求计算出各门课程的选课人数以及平均分。统计值SELECT

FROM

groupby

count(学号)AS选课人数,avg(成绩)AS平均分选课表课程号课程号，小窍门：“每个”、“按”、“各”一般是提示分组字段。6.2.2使用groupby子句聚集查询分组小结每一个组计算得到一个汇总值，并把这个汇总值保存在一个字段中。最好不要对可能包含空值的字段使用groupby字句，因为空值也将被当做一组。所有在groupby字句中指定的字段，都必须出现在select语句的选择列表中。在Select子句中出现的列，要么存在于groupby子句中，要么存在于集函数中。错例：Select班级,年龄,Count(学号)As人数From学生表Groupby班级注：对应于所指定的每一个组，只生成一条记录，且不返回细节信息。（这里的年龄是细节信息）6.2.2使用groupby子句聚集查询分组小结如果使用了where子句，将只对满足where子句的记录进行分组和汇总。Select班级,Count(学号)As人数From学生表Where性别=‘M’Groupby班级统计每班男生人数如何表述该题目？6.2.2使用groupby子句聚集查询2.用法格式：Having条件在Having子句中可以引用任何允许出现在Select选择列表中的字段1.作用：对分组后的结果按某种条件再进行筛选。提问：若进行了分组，则Having子句中允许出现哪两类字段？6.2.3使用HAVING子句筛选结果集聚集查询

select

<目标列组>

from<数据源>[where<元组选择条件>][groupby<分列组>][having<组选择条件>组]][orderby<排序列1>asc|desc[，…]]6.2.3使用HAVING子句筛选结果集例4：要求找出人数在40人以上的那些班级？条件聚集查询分组字段select

from

groupbyhaving

学生表班级注意：①分组前的条件用Where，分组后的条件用Having②带集函数的条件用Havingcount（学号）>40count(学号)AS班级人数班级，6.2.3使用HAVING子句筛选结果集例5：列出选课表里同时选修了4门课的同学的学号？条件聚集查询分组字段select

from

groupbyhaving

选课表学号count（课程号）=4count(课程号)AS课程门数学号，6.2.3使用HAVING子句筛选结果集例6：若全校人数超过700，就显示全校人数条件聚集查询统计值select

from

having

学生表count(学号)>700count(学号)AS全校人数注意：当条件为集函数时，也可不分组，直接用Having子句6.2.3使用HAVING子句筛选结果集聚集查询where子句用来筛选from子句中指定的操作所产生的行。Groupby子句用来分组where子句的输出。having子句用来从分组的结果中筛选行。当条件中带有集函数时，要用having子句。6.2.4对where、groupby、having的思考聚集查询1、列出全校每个班的人数。2、列出全校每个班的男同学的人数（两种方法）。3、列出全校中班级人数超过30的班级名称及班级人数。4、列出全校中班级男生人数超过20的班级名称及男生人数。5、求各个学生的学号及相应的选课门数。6、查询选了一门以上课程的学生学号。课堂练习5：25分钟连接查询6.3连接查询允许同时从两个表或者两个以上的表中检索数据。连接类型关键字交叉连接CROSSJOIN内连接（自连接）INNERJOIN外连接左外连接LEFTOUTERJOIN右外连接RIGHTOUTERJOIN全外连接FULLOUTERJOIN连接查询6.3.1交叉连接6.3.2内连接查询6.3.3自连接查询6.3.4外连接查询连接查询学号姓名年龄所在系98001王平20计算机98002李丽21数学98003陈红20计算机学号课程号成绩98001C19598001C38098003C18598003C275学生.学号姓名年龄所在系选课.学号课程号成绩98001王平20计算机98001C19598001王平20计算机98001C38098001王平20计算机98003C18598001王平20计算机98003C27598002李丽21数学98001C19598002李丽21数学98001C38098002李丽21数学98003C19598002李丽21数学98003C27598003陈红20计算机98001C19598003陈红20计算机98001C38098003陈红20计算机98003C18598003陈红20计算机98003C275学生选课学生╳选课

连

接连接查询学号姓名年龄所在系98001王平20计算机98002李丽21数学98003陈红20计算机学号课程号成绩98001C19598001C38098003C18598003C275学生选课学生⋈选课学生.学号=选课.学号等值连接学生.学号姓名年龄所在系选课.学号课程号成绩98001王平20计算机98001C19598001王平20计算机98001C38098003陈红20计算机98003C18598003陈红20计算机98003C275

连

接连接查询学号姓名年龄所在系98001王平20计算机98002李丽21数学98003陈红20计算机学号课程号成绩98001C19598001C38098003C18598003C275学生选课学生⋈选课自然连接学号姓名年龄所在系课程号成绩98001王平20计算机C19598001王平20计算机C38098003陈红20计算机C18598003陈红20计算机C275

连

接连接查询交叉连接即笛卡尔乘积，是指两个表中所有记录的任意组合。选课表与课程表两个关系模式的乘积表示如下：Select*From选课表,课程表注意：一般情况下，交叉查询是没有实际意义的。6.3.1交叉连接1.笛卡尔积课程号课程名教师周课时数备注学号课程号成绩1软件工程吴老师420111284741软件工程吴老师420111291931软件工程吴老师420111301741软件工程吴老师420111312842ASP程序设计黄老师420111284742ASP程序设计黄老师420111291932ASP程序设计黄老师420111301742ASP程序设计黄老师420111312843COM技术黄老师220111284743COM技术黄老师220111291933COM技术黄老师220111301743COM技术黄老师220111312844Dephi涂老师620111284744Dephi涂老师620111291934Dephi涂老师620111301744Dephi涂老师62011131284课程号课程名教师周课时数1软件工程吴老师42ASP程序设计黄老师43COM技术黄老师24Dephi涂老师6RS学号课程号成绩2011128474201112919320111301742011131284R×S连接查询两种表示格式：格式1：FROM表1[INNER]JOIN表2ON

<连接条件>格式2：FROM表1，表2WHERE

<连接条件>在内连接查询中，只有满足条件的记录才能出现在结果关系中。目前选用方式6.3.2内连接查询选课表连接查询例1：要求查询每个已经选课的学生情况和选课情况学生表SELECT*FROM学生表,选课表WHERE学生表.学号=选课表.学号问题：查什么属性？查什么表？连接条件？SELECT*FROM学生表INNERJOIN选课表ON学生表.学号=选课表.学号

注意：若引用的列是多个表所共有的，需指明“表名.列名”提问：结果集中如何消除重复字段？如何消除重复记录？6.3.2内连接查询选课表连接查询例2：要求查询已经选修了D4课程的学生信息学生表SELECT学生表.*FROM学生表,选课表WHERE学生表.学号=选课表.学号and选课表.课程号='D4'条件可省略问题：查什么属性？查什么表？连接条件？注意：若引用的列是多个表所共有的，需指明“表名.列名”提问：将题目改为“选修了软件工程课程”?Select学生表.*From学生表INNERJOIN选课表On

学生表.学号=选课表.学号Where选课表.课程号=‘D4’6.3.2内连接查询连接查询例3：要求查询已经选修了软件工程课程的学生信息SELECT学生表.*FROM学生表,选课表,课程表WHERE学生表.学号=选课表.学号and选课表.课程号=课程表.课程号and课程表.课程名='软件工程'可省略6.3.2内连接查询连接查询例4：查询学生的学号，姓名及所选修的课程号、成绩例5：查询学生的学号，姓名及所选修的课程名及成绩select学生表.学号,姓名,课程名,成绩from学生表,课程表,选课表where学生表.学号=选课表.学号and课程表.课程号=选课表.课程号select学生表.学号,姓名,选课表.课程号,成绩from学生表,选课表where学生表.学号=选课表.学号可省略6.3.2内连接查询连接查询例6：查询考试成绩有不及格的学生的学号、姓名例7：查询选了三门课程的学生的学号、姓名select学生表.学号,姓名

from学生表,选课表where学生表.学号=选课表.学号and成绩<60select学生表.学号,姓名from学生表,选课表where学生表.学号=选课表.学号groupby学生表.学号,姓名havingcount(课程号)=3distinct6.3.2内连接查询连接查询课堂练习6学生表（学号，姓名，性别，班级，年龄，所在系）课程表（课程号，课程名，教师，周课时数，备注）选课表（学号，课程号，成绩）查询考试成绩大于95分的学生的学号、姓名查询计算机系学生的选课门数查询平均分大于60的学生的学号、姓名查询关系数据库应用考试缺考的学生学号、姓名问题：20分钟连接查询1：查询考试成绩大于95分的学生的学号、姓名2：查询计算机系学生的选课门数select学生表.学号,姓名

from学生表,选课表Where学生表.学号=选课表.学号and成绩>95课堂练习6答案select学生表.学号,count(课程号)as选课门数from学生表,选课表where学生表.学号=选课表.学号and所在系='计算机系'groupby学生表.学号连接查询3：查询平均分大于60的学生的学号、姓名select学生表.学号,姓名

from学生表,选课表where学生表.学号=选课表.学号groupby学生表.学号,姓名having

avg(成绩)>60课堂练习6答案连接查询4：查询关系数据库应用考试缺考的学生学号、姓名select学生表.学号,姓名

from学生表,选课表,课程表where学生表.学号=选课表.学号and课程表.课程号=选课表.课程号and课程名='关系数据库应用'and成绩isnull课堂练习6答案连接查询如果在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。为了区分同样的两张表，使用AS语句定义别名：FROM

表名AS

别名自连接把物理上同一张表在逻辑上区分为两张表或多张表。注意：当为表指定了别名以后，在查询语句中的其它地方用到该表名时，都应使用其别名，而不能使用其原表名。6.3.3自连接查询连接查询例6.18：要求检索出学号为‘2021509’的学生的同班同学的信息。分析：第1遍查什么信息？第2遍查什么信息？SelectB.*

from

学生表

AS

A,学生表AS

B

where

A.学号=‘2021509'

AND

B.班级=A.班级SelectB.*from学生表ASA

JOIN学生表ASBONB.班级=A.班级WHEREA.学号=‘2021509'提问：如果要求查询结果中不包括该同学本身，如何解决?6.3.3自连接查询连接查询例：要求检索出学号为‘2021509’的学生同班的其他同学的信息。SelectB.*

from

学生表

AS

A,学生表AS

B

where

A.学号=‘2021509'

AND

B.班级=A.班级AndB.学号<>‘2021509’SelectB.*from学生表ASA

JOIN学生表ASBONB.班级=A.班级WHEREA.学号=‘005‘ANDB.学号<>‘2021509’6.3.3自连接查询连接查询课堂练习7学生表（学号，姓名，性别，班级，年龄，所在系）课程表（课程号，课程名，教师，周课时数，备注）选课表（学号，课程号，成绩）要求检索出学号为’2021626’的学生的性别和年龄均相同的同学的信息。要求检索出学号为’2021626’的学生的性别和年龄均相同的其它同学的信息。要求检索出学号为’2021626’的学生同年龄的学生人数。问题：20分钟连接查询课堂练习7答案1.要求检索出学号为’2021626’的学生的性别和年龄均相同的同学的信息。SelectB.*from学生表ASA,学生表ASBwhereA.学号='2021626'and

B.性别=A.性别andB.年龄=A.年龄2.要求检索出学号为’2021626’的学生的性别和年龄均相同的其它同学的信息。SelectB.*from学生表ASA,学生表ASBwhereA.学号='2021626'and

B.性别=A.性别andB.年龄=A.年龄andB.学号<>'2021626'连接查询课堂练习7答案3.要求检索出学号为’2021626’的学生同年龄的学生人数。Selectcount(B.学号)as学生人数from学生表ASA,学生表ASBwhereA.学号='2021626'andB.年龄=A.年龄连接查询内连接的查询结果都是满足连接条件的元组。但有时我们也希望输出那些不满足连接条件的元组的信息。外连接查询的关键字是OUTERJOIN。外连接是只限制一张表中的数据必须满足连接条件，而另一张表中的数据可以不满足连接条件的连接方式。外连接的格式为：

FROM表1LEFT|RIGHT|FULLOUTERJOIN表2ON<连接条件>6.3.4外连接查询连接查询有三种方式的外连接：左外连接右外连接全外连接6.3.4外连接查询连接查询左外连接（LEFTOUTERJOIN）如果在连接查询中，连接关键字左端的表（表1）中所有元组都列出来，并且能在右端的表（表2）中找到匹配的元组，那么连接成功。如果在右端的表中没能找到匹配的元组，那么对应的元组是空值（NULL）。这时，查询语句使用关键字LEFTOUTERJOIN。也就是说，左外连接的含义是限制连接关键字右端的表（表2）中的数据必须满足连接条件，而不管左端的表（表1）中的数据是否满足连接条件，均输出左端表（表1）中的内容。6.3.4外连接查询连接查询左外连接（LEFTOUTERJOIN）6.3.4外连接查询【例6.26】要查询所有学生的选课情况，包括已经选课的和还没有选课的学生。SELECT学生表.学号,姓名,班级,课程号,成绩FROM学生表LEFTOUTERJOIN选课表ON学生表.学号=选课表.学号从图所示的查询结果可以看出，其中有5个学生的课程号和成绩为NULL，这说明他们没有选课。但是，因为进行的是左外连接查询，所以仍将他们显示出来，并在相应的列上放置NULL。连接查询6.3.4外连接查询右外连接（RIGHTOUTERJOIN）右外连接查询的含义与左外连接的含义非常类似。只是右端表中的所有元组都被列出，使用的关键字是RIGHTOUTERJOIN。此时，限制左端表的数据必须满足连接条件，而不管右端表中的数据是否满足连接条件，均输出右端表中的内容。连接查询6.3.4外连接查询全外连接（FULLOUTERJOIN）外连接查询的特点是左、右两端表中的元组都被输出，如果没能找到匹配的元组，就使用NULL来代替。这时使用的关键字是FULLOUTERJOIN。连接查询6.3.4外连接查询示例为了更好地说明外连接查询，这里用另外两张表（图书和作者）来举例，见表6.4。SELECT图书.图书号,书名,作者名,单价FROM图书LEFTOUTERJOIN作者ON图书.图书号=作者.图书号SELECT图书.图书号,书名,作者名,单价FROM图书RIGHTOUTERJOIN作者ON图书.图书号=作者.图书号SELECT图书.图书号,书名,作者名,单价FROM图书FULLOUTERJOIN作者ON图书.图书号=作者.图书号左外连接右外连接全外连接子查询6.4子查询6.4.1将子查询用做派生的表6.4.2将子查询用做表达式6.4.3相关子查询6.4.4使用EXISTS和NOTEXISTS操作符子查询子查询是一系列SELECT语句的嵌套使用，嵌套的SELECT语句就称为子查询。使用子查询时应注意：子查询要用括号（）括起来。子查询一般出现在SELECT子句、WHERE子句、FROM子句中。只要需要一个值或一系列的值，就可以用子查询代替一个表达式。子查询不应包含在集函数中。子查询中不能查询包含数据类型是text或image的字段。子查询中不能使用compute或forbrowse语句。ORDERBY子句不能用于子查询，但指定了TOP时则可以。子查询中也可以再包含子查询，但从兼容性考虑，建议嵌套不要超过32层。通常，子查询是可以和连接查询相互转换的。子查询可以用子查询产生一个派生的表，用于代替FROM子句中的表。FROM子句中的子查询将返回一个结果集，这个结果集所形成的表将被外层SELECT语句使用。派生表是FROM子句中子查询的一个特殊用法，用一个别名或用户自定义的名字来引用这个派生表。SELECTXS.*FROM(SELECT学号,姓名,年龄FROM学生表WHERE班级='GZ02计6')ASXS6.4.1将子查询用做派生的表子查询子查询可以作为一个标量值来使用，产生标量值的含义就是把子查询的结果作为一个常量来使用。所有使用表达式的地方，都可以用子查询来代替，此时子查询必须返回一个单个的值或某个字段的值。一个简单的示例是：如果要求检索出年龄高于所有学生平均年龄的学生信息，则可用如下语句完成。SELECT*FROM学生表WHERE年龄>（SELECTAVG（年龄）FROM学生表）6.4.2将子查询用做表达式子查询【例6.27】如果要求查询GZ02计7班同学的平均年龄以及每个同学年龄与平均年龄的差。SELECT学号,姓名,年龄,(SELECTAVG(年龄)FROM学生表WHERE班级='GZ02计7')AS平均年龄，年龄-(SELECTAVG(年龄)FROM学生表WHERE班级='GZ02计7')AS年龄差FROM学生表WHERE班级='GZ02计7'在该查询示例中，使用子查询：(SELECTAVG（年龄）FROM学生表WHERE班级='GZ02计7')AS平均年龄该计算结果作为选择列表中的一个输出列，并作为下面算术表达式的一部分参与计算：年龄-（SELECTAVG（年龄）FROM学生表WHERE班级='GZ02计7'）AS年龄差6.4.2将子查询用做表达式子查询在SQLServer中，当子查询返回一个值时，关键字IN、ALL、ANY可以用于关系的比较以便生成逻辑值，放在查询语句的WHERE子句中形成查询条件。假设R为子查询生成的一列值，S为一个标量值，那么这些涉及关系的运算符如下表所示。6.4.2将子查询用做表达式运算符描述SINR当且仅当S和R中的某一个值相等时，条件为真SNOTINR当且仅当S和R中的任意一个值都不相等时，条件为真S=ALLR当且仅当S和R中的每一个值都相等时，条件为真S>ALLRS>=ALLR当且仅当S比R中的每一个值都大时（大于或等于），条件为真S<ALLRS<=ALLR当且仅当S比R中的每一个值都小时（小于或等于），条件为真S<>ALLR当且仅当S和R中的任意一个值都不相等时，条件为真S=ANYR当且仅当S和R中的某一个值相等时，条件为真S>ANYRS>=ANYR当且仅当S比R中的某一个值大时（大于或等于），条件为真S<ANYRS<=ANYR当且仅当S比R中的某一个值小时（小于或等于），条件为真S<>ANYR当且仅当S和R中的任意一个值都不相等时，条件为真子查询还可以将子查询归纳为以下3种形式：1.带关键字IN的子查询2.带比较运算符的子查询3.带关键字ANY或ALL的子查询子查询1.带关键字IN的子查询由于子查询的结果是记录的集合，故常使用谓词IN来实现。谓词IN用于一个定是否在子查询的结果集中。当父查询表达式与子查询的结果集中的某个值相等时，返回TRUE，否则返回FALSE。同时，也可以在IN关字之前使用NOT，表示表达式的值不在查询结果集中。对于使用IN的子查询的连接条件，其语法格式如下：WHERE<表达式>[NOT]IN<子查询>

子查询（1）同一张表的查询例6.25：要求检索出学号为‘2021509’的学生的同班同学的信息自连接：SELECTB.*FROM学生表ASA，学生表ASBWHEREA.学号='2021509'ANDA.班级=B.班级提问：用连接查询怎么写？属于哪种连接？提问：用子查询又该怎么写？子查询：SELECT*FROM学生表WHERE班级

IN（SELECT班级FROM学生表WHERE学号=‘2021509’）注意：对来自同一张表的查询，共有的条件（如班级）作为子查询的桥梁。1.带关键字IN的子查询子查询例6.28：要求检索出学号为‘2021509’的学生的同班的其他同学的信息自连接：SELECTB.*FROM学生表ASA，学生表ASBWHEREA.学号='2021509'ANDA.班级=B.班级ANDB.学号<>'2021509'提问：用连接查询怎么写？属于哪种连接？提问：用子查询又该怎么写？子查询：SELECT*FROM学生表WHERE班级

IN（SELECT班级FROM学生表WHERE学号=‘2021509’）AND学号<>‘2021509’总结：对来自同一张表的查询，共有的条件作为子查询的桥梁。（1）同一张表的查询1.带关键字IN的子查询子查询（2）不同表的查询例6.28：要求检索出GZ02计7班中所选课程中有过不及格的同学信息。内连接：selectdistinct

学生表.*from学生表,选课表where学生表.学号=选课表.学号and班级='GZ02计7'and成绩<60提问：用连接查询怎么写？属于哪种连接？提问：用子查询又该怎么写？子查询：SELECT*

FROM学生表WHERE学号

IN(SELECT学号FROM选课表WHERE成绩<60)AND班级='GZ02计7'总结：对来自不同表的查询，多个表之间共有的属性作为子查询的桥梁。1.带关键字IN的子查询子查询例6.29：求没有选修4课程的学生学号，姓名内连接：selectdistinct学生表.学号,姓名from学生表,选课表where学生表.学号=选课表.学号and课程号<>'4'提问：用子查询怎么写？子查询：SELECT学号,姓名FROM学生表WHERE学号NOTIN(SELECT学号FROM选课表WHERE课程号='4')解释：只是课程号<>'4',而不能代表学生没选4号课程思考：该题能用内连接查询写吗？不能分析？注意：子查询和连接查询大部分情况下可以互相转换，但子查询更加强大，可读性更好。（2）不同表的查询1.带关键字IN的子查询子查询例6.30：求选修了“关系数据库应用”学生的学号，姓名。内连接：select学生表.学号,姓名from学生表,选课表,课程表where学生表.学号=选课表.学号and选课表.课程号=课程表.课程号and课程名='关系数据库应用'思考：用子查询怎么写？子查询：select学号,姓名from学生表where学号in(select学号from选课表where课程号in(select课程号from课程表where课程名='关系数据库应用'))注意：多表查询，请注意嵌套顺序，两表有关联的先写。（2）不同表的查询1.带关键字IN的子查询子查询带IN谓词的子查询课堂练习：学生表（学号，姓名，性别，班级，年龄）课程表（课程号，课程名，教师，周课时数，备注）选课表（学号，课程号，成绩）1.检索出与“关系数据库应用"课程周课时数相同的课程信息。2.检索出与“关系数据库应用"课程周课时数相同的其它课程信息。3.请查询4号课程成绩大于80分的学生信息。4.检索出“关系数据库应用”成绩大于80分的学生信息。5.求没有选修“关系数据库应用”的学生学号，姓名。问题：1.带关键字IN的子查询子查询当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或<>）。与ANY或ALL谓词配合使用。当子查询返回0行或比较运算符对于子查询返回的每一行都是True时，则返回True；若比较运算符对于子查询返回的至少一行是False，则返回False。2.带比较运算符的子查询select学号,成绩from选课表where课程号='1'and成绩

>(select成绩from选课表where课程号='1'and学号

=(select学号from学生表where姓名='包海明'))子查询例6.31：要求查询选修1号课程且成绩高于“包海明”的学生学号，成绩。注意：子查询一定要跟在比较运算符之后如果上述例子中的学生姓名换成“徐阳”，查询语句中是否还可以使用比较运算符“=”，如果不行，请说明理由，并做相应修改？想一想2.带比较运算符的子查询子查询ANY：任意一个值

ALL：所有值注意：需要配合使用比较运算符3.带ANY或ALL的子查询子查询>ANY大于子查询结果中的某个值>ALL大于子查询结果中的所有值<ANY小于子查询结果中的某个值<ALL小于子查询结果中的所有值>=ANY大于等于子查询结果中的某个值>=ALL大于等于子查询结果中的所有值<=ANY小于等于子查询结果中的某个值<=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值（通常没有实际意义）!=（或<>）ANY不等于子查询结果中的某个值!=（或<>）ALL不等于子查询结果中的任何一个值3.带ANY或ALL的子查询子查询例6.32：要求检索出比所有女同学年龄都大的男同学信息。SELECT*FROM学生表WHERE年龄>ALL(SELECT年龄FROM学生表WHERE性别='F')AND性别='M'思考：该题目用any或all？查询的表？还可以理解为：要求检索出大于最大年龄女同学的男同学信息。SELECT*FROM学生表WHERE年龄>(SELECTMAX(年龄)FROM学生表WHERE性别='F')AND性别='M'3.带ANY或ALL的子查询子查询例6.33：要求检索出选课门数最多的学生学号和姓名。select学号,姓名from学生表where学号in(select学号from选课表groupby学号havingcount(学号)>=all(selectcount(学号)from选课表groupby学号))提问：该题目该如何写？请同学们写下？思考：该题目用any或all？查询的表？注意：有集函数的与集函数的写法一致3.带ANY或ALL的子查询子查询例6.34：要求检索出有学生的4号课程成绩在95分以上的那些班级。SELECTDISTINCT班级FROM学生表WHERE学号=ANY(SELECT学号FROM选课表WHERE成绩>95AND课程号=4)思考：如何解读该题目？3.带ANY或ALL的子查询4.子查询例6.35：要求检索出其他班级中比‘GZ02计7’班某一学生年龄小的学生的信息。SELECT*FROM学生表WHERE班级<>'GZ02计7'AND年龄<ANY(SELECT年龄FROM学生表WHERE班级='GZ02计7')还可以理解为：求年龄小于‘GZ02计7’年龄最大者的其它班级的学生select*from学生表where班级<>'GZ02计7'and年龄<

(selectmax(年龄)from学生表where班级='GZ02计7')思考：如何解读该题目？即查的是其他班级年龄小于‘GZ02计7’任一个学生的年龄思考：还可以如何理解？3.带ANY或ALL的子查询子查询学生表（学号，姓名，性别，班级，年龄）课程表（课程号，课程名，教师，周课时数，备注）选课表（学号，课程号，成绩）查询平均成绩最好的学生的学号。查询2号课程成绩大于全校2号课程平均成绩的学生信息。列出课程号为1的最高分获得者的信息。检索出GZ02计6班年龄大于等于全校学生平均年龄的学生信息。问题：3.带ANY或ALL的子查询课堂练习：子查询相关子查询可被用做动态表达式，这个表达式的值相对于外层查询的每一行而变化。查询处理器为外层查询的每一个记录计算子查询的值，一次一行，而这个子查询每次都会被作为一个表达式而被计算并返回给外层查询。相关子查询是动态执行的子查询和外层查询间的一个非常有效的联合。使用相关子查询时，内层子查询被反复执行，外层查询有多少记录，内层查询就被执行多少次。6.4.3相关子查询子查询【例6.36】要求查询已选修课程号为1且成绩在90分以上的同学的学号及姓名。6.4.3相关子查询SELECT学号,姓名FROM学生表WHERE90<=(SELECT成绩FROM选课表WHERE学生表.学号=选课表.学号AND课程号=1)子查询【例6.37】如果要求查询同时选修了4门课程的同学的学号。6.4.3相关子查询SELECTDISTINCT学号FROM选课表ASS1WHERE4=(SELECTCOUNT(课程号)FROM选课表ASS2WHERES1.学号=S2.学号)子查询【例6.38】检索出每门课程的及格人数与不及格人数，要求按照：课程名、及格人数、不及格人数排列。6.4.3相关子查询SELECT课程名，(SELECTCOUNT(学号)FROM选课表WHERE课程号=课程表.课程号AND成绩>=60)AS及格人数，(SELECTCOUNT(学号)FROM选课表WHERE课程号=课程表.课程号AND成绩<60)AS不及格人数FROM课程表子查询在相关子查询中可以使用EXISTS和NOTEXISTS操作符来判断某个值是否存在于一系列的值中。SQLServer处理带有EXISTS和NOTEXISTS操作符的子查询时：外层查询测试子查询返回的记录是否存在。基于查询所指定的条件，子查询返回TRUE或FALSE。子查询不产生任何数据。6.4.4使用EXISTS和NOTEXISTS操作符子查询【例6.39】要求查询出同时选修了1号课程和2号课程的同学的信息。6.4.4使用EXISTS和NOTEXISTS操作符SELECT学号，姓名，班级FROM学生表WHEREEXISTS(SELECT*FROM选课表WHERE学号=学生表.学号AND课程号=1)ANDEXISTS(SELECT*F