单元4查询数据表中的数据教材

1、单元单元4 查询数据表中的数据查询数据表中的数据SQL Server 2008数据库技术及应用数据库技术及应用 单元单元4 教学目标教学目标SQL Server 2008数据库技术及应用数据库技术及应用单元单元4 在本书中位置在本书中位置单元单元4 结构图结构图单元单元4 查询数据表中的数据查询数据表中的数据学习型工作任务学习型工作任务【任务任务4.1 】创建基本查询创建基本查询【任务任务4.2 】创建连接查询创建连接查询【任务任务4.3 】创建子查询创建子查询【任务任务4.4 】创建联合查询创建联合查询知识知识链接链接【L1.2.1】Select 语句的语句的语法格式语法格式 1. SELE

2、CT 语句的语法格式2. SELECT语句说明3. SELECT语句的执行过程4. 使用使用SELECT语句的注意语句的注意事项事项单元训练单元训练单元训练单元训练SELECT子句中谓词的使用方法WHERE子句的使用方法分组查询GROUP BY、HAVING子句的使用方法集合函数的使用方法多表连接查询的使用方法灵活使用SELECT子句进行数据处理单元单元1 知识结构框架图知识结构框架图 多表查询学习型工作任务学习型工作任务查询数据表中数据4.1 创建基本查询4.2 创建连接查询4.3 创建子查询4.4 创建联合查询 多多表表查查询询单表单表查询查询4.1 创建基本查询创建基本查询-单表查询单表

3、查询【任务4.1.1】SELECT 子句查询【任务4.1.2 】WHERE 子句查询【任务4.1.3 】使用聚合函数查询【任务4.1.4 】使用GROUP BY子句分组统计查询【任务4.1.5 】使用HAVING子句限定分组统计查询【任务4.1.6 】使用ORDER BY子句排序【任务4.1.7 】使用COMPUTE BY子句汇总查询【任务4.1.8 】使用INTO子句保存查询【操作准备】【操作准备】uSELECT语句的语法格式及功能；语句的语法格式及功能；学生成绩管理系统数据库学生成绩管理系统数据库StudentScore_DBu根据成绩管理根据成绩管理E-R图，了解成绩管理数据库中表之间的

4、关系。图，了解成绩管理数据库中表之间的关系。图图4-4 成绩管理数据库关系图成绩管理数据库关系图【任务【任务4.1.1】SELECT 子句查询子句查询（1）利用SQL 编辑器查询“班级表”中所有信息。（2）查询班级表ClassInfo中班级号ClassID，班级名ClassNamer的信息。（3）查询班级表ClassInfo中ClassID和ClassName，为便于用户阅读，列名显示为中文“班级号”和“班级名”。（4）查询成绩表ScorInfo中学号StudID,课程号CourseID,成绩Score及成绩Score加5分后结果。（5）查询学生表StudentInfo中前5条记录。（6）查询

5、学生表中班级编号ClassID，要求相同的班级编号只显示一次。【任务描述任务描述】【任务【任务4.1.1】SELECT 子句查询子句查询1.启动SSMS，连接数据库服务器，打开SQL编辑器。打开【对象资源管理器】窗口，单击工具栏中按钮，打开【SQL 编辑器】。2. 设置当前数据库为StudentSocre_DB在【SQL编辑器】工具栏中的数据库下拉列表中选择“StudentScore_DB”为当前数据库，或使用“use StudScore_DB”命令打开数据库。如图4-5所示。3. 根据任务描述，依次输入SELECT 语句，并执行。单击【SQL编辑器】工具栏中的【执行】按钮 或者选择菜单命令【

6、查询】【执行】或者直接按F5键，在当前数据库中执行SQL查询语句。【任务实施任务实施】【任务【任务4.1.1】SELECT 子句查询子句查询（1）使用（*）查询表的所有列。使用*表示选取表的全部列，from 子名表示要查询的表。【任务4-1】 利用SQL 编辑器查询“班级表”中所有信息。select * from classInfo（2）查询指定的列【任务4-2】查询班级表ClassInfo中班级号ClassID，班级名ClassNamer的信息。Select classID,className from ClassInfo（3）改变查询结果的列标题。格式1：指定的列标题=列名表达式格式2：列

7、名 as 指定的列标题。【任务实施任务实施】【任务【任务4.1.1】SELECT 子句查询子句查询【任务4-3】查询班级表ClassInfo中ClassID和ClassName，为便于用户阅读，列名显示为中文“班级号”和“班级名”。Select classID 班级号,className 班级名 from ClassInfo也可写为：Select 班级号=ClassID,班级名=ClassName from ClassInfo（4）查询时除使用字段名称外，还可使用常量、函数或它们的组合。【任务4-4】查询成绩表ScoreInfo中学号StudID,课程号CourseID,成绩Score及成绩S

8、core加5分后结果。查询结果如图4-8所示。Select studID 学号,courseID 课程号,Score 成绩,Score+5 加分后 from ScoreInfo（5）使用top关键字限制返回的行数。格式：top n Percent如果未指定关键字percent，则返回查询结果集的前n条记录。如果指定了关键字percent，n返回结果集行的百分数。【任务4-5】查询学生表StudentInfo中前5条记录。Select top 5 * from StudentInfo【任务实施任务实施】【任务【任务4.1.1】SELECT 子句查询子句查询（6）使用 distinct 关键字可以

9、消除查询结果中重复的数据行。【任务4-6】查询学生表中班级编号ClassID，要求相同的班级编号只显示一次。select distinct ClassID from studentinfo4. 保存SQL语句。单击工具栏中的【保存】按钮 或者选择菜单命令【文件】【保存】，打开【另存文件为】对话框，在该对话框中定位到保存SQL语句的文件夹，输入文件名“sele_class.sql”，然后单击【保存】按钮即可。【任务实施任务实施】【任务【任务4.1.2 】WHERE 子句查询子句查询格式 ：SELECT ALL|DISTINCT|TOP n| 表达式1，表达式2，。表达式n FROM 表名 WHE

10、RE 。WHERE 子句称为选择操作，其作用是查询符合WHERE条件的记录。此外，WHERE子句还用在DELETE和UPDATE语句中，定义表中被删除和修改的行。T-SQL 语言通常用逻辑表达式描述条件。当查询的表中记录使得WHERE子句逻辑表达式为真时，才向结果集提供数据。 逻辑表达式中可以使用比较运算符（=、 =、=）、逻辑运算符（and、or、not）、范围运算符(betweenand)、模糊匹配(Like、not Like )、空值(IS NULL、IS NOT NULL)等。【功能描述功能描述】【任务【任务4.1.2 】WHERE 子句查询子句查询（7）利用SQL 编辑器查询学生表S

11、tudInfo中女同学的信息。（8）查询1993年1月1日以前出生的女同学的信息。（9）查询成绩表中成绩在70至85之间信息。（10）查询名子中有“丽”的同学的信息。（11）查询姓“张”，名子中仅有一个字的，备注为空的学生信息。（12）查询学号为“kj1331001、wl1331003、wl1332008”的学生信息。【任务描述任务描述】【任务【任务4.1.2 】WHERE 子句查询子句查询（1）关系运算：）关系运算：关系运算符： （等于）、（不等于）、（大于）、=（大于等于）、=（小于等于） 关系表达式：常量、列名（变量）、函数、关系运算符。【任务4-7】利用SQL 编辑器查询学生表Stud

12、Info中女同学的信息。在查询分析器中运行如下命令：Select * from StudentInfo where studsex=女（2）逻辑运算符：）逻辑运算符： NOT（非）、AND（与）、OR（或）逻辑表达式：常量、列名（变量）、函数、逻辑运算符。【任务4-8】查询1993年1月1日以前出生的女同学的信息。在查询分析器中运行如下命令：Select * from StudentInfo where StudBirthday=开始值 AND 列名=结束值）NOT BETWEEN 开始值 AND 结束值 。等价于（列名结束值）【任务4-9】查询成绩表中成绩在70至85之间信息在查询分析器中运

13、行如下命令：Select * from ScoreInfo where Score between 70 and 85（4）模式匹配运算符）模式匹配运算符模糊查询就是查找数据表中与用户输入关键字相近或相似的记录信息。模糊匹配通常与通配符一起使用，且必须将字符串和通配符都用单引号引起来。语法：列名 NOT LIKE字符串（含通配符）通配符“_”代表一个任意字符，通配符“%”代表任意多个字符。模式匹配运算符LIKE可以实现对表的模糊查询。 【任务4-10】查询名子中有“丽”的同学的信息。Select * from StudentInfo where StudName like %丽%【任务实施任务

14、实施】【任务【任务4.1.2 】WHERE 子句查询子句查询（5）空值查询）空值查询空值判断查询条件常用来查询某一字段为空值的记录，使用 IS NULL 或 IS NOT NULL指定查询条件。NULL值表示字段的数据值未知或不可用，它并用表示零、零长度的字符串或空白字符串。格式：列名 IS NOT NULL【任务4-11】查询姓“张”，名子中仅有一个字的，备注为空的学生信息。Select * from studentInfo where studName like 张_ and remarks is null（6）IN关键字关键字包含列表查询条件的查询将返回所有与列表中的任意一个值匹配的记录

15、，通常用IN关键字来指定查询条件。IN关键字格式：IN（列表值1，列表值2，）【任务4-12】查询学号为“kj1331001、wl1331003、wl1332008”的学生信息Select * from studentinfo where StudID in(kj1331001,wl1331001,wl1332008)【任务实施任务实施】【任务【任务4.1.3 】使用聚合函数查询】使用聚合函数查询聚合函数功能COUNT ( ALL|DISTINCT expression |* )计算符合条件的记录数，COUNT(*)返回记录数，包括含有空值的记录，不能与DISTINCT一起使用SUM( ALL

16、|DISTINCT expression )计算一组数据的和AVG( ALL|DISTINCT expression )计算一组数据的平均值MAX ( ALL|DISTINCT expression )计算一组数据的最大值MIN( ALL|DISTINCT expression )计算一组数据的最小值聚合函数也称统计函数，其对一组值进行计算并返回一个数值。表4-1 SQL Server聚合函数及其功能ALL为默认选项，指计算所有的值；DISTINCT则去掉重复值。表4-1 常用聚合函数。【任务【任务4.1.3 】使用聚合函数查询】使用聚合函数查询【任务描述】【任务描述】（13）从“成绩表”中统

17、计成绩在“70至80（包含）”之间的记录数。（14）从“成绩表”中计算所有成绩的平均值。（15）从“成绩表”中计算所有成绩的总分。（16）从“成绩表”查询所有成绩的最高分、最低分。（17） 查询学号为“wl1133001”学生所选课程门数、最高分、最低分、总分、平均分，并用相应的中文名称表示相应的值 。【任务【任务4.1.3 】使用聚合函数查询】使用聚合函数查询【任务4-13】从“成绩表”中统计成绩在“70至80（包含）”之间的记录数。在查询分析器中运行如下命令：select COUNT(*) from ScoreInfo where Score between 70 and 80【任务4-1

18、4】从“成绩表”中计算所有成绩的平均值。在查询分析器中运行如下命令：select AVG(score) from ScoreInfo【任务4-15】从“成绩表”中计算所有成绩的总分。在查询分析器中运行如下命令：select sum(score) from ScoreInfo【任务4-16】从“成绩表”查询所有成绩的最高分、最低分。在查询分析器中运行如下命令：select max(score),MIN(score) from ScoreInfo【任务4-17】 查询学号为“wl1133001”学生所选课程门数、最高分、最低分、总分、平均分，并用相应的中文名称表示相应的值 。在查询分析器中运行如下

19、命令：运行结果如图4-10所示。select count(*) 课程门数,max(score) 最高分 ,MIN(score) 最低分, sum(score) 总分, avg (score) 平均分 from ScoreInfo where StudID =wl1331001【任务【任务4.1.4 】使用】使用GROUP BY子句分组统计查询子句分组统计查询GROUP BY 与列名或聚合函数配合实现分组统计。聚合函数是对整个表中的数据进行计算统计，但实际应用中往往需要根据某列的值进行分组统计与汇总，如从成绩表中统计每位学生所选课程门数、总分等，统计时需要用“GROUP BY StudID”,即

20、按“学号”进行分组统计，然后再计算机聚合函数的值。在SELECT 子句中投影的列，必须出现在GROUP BY 列名中。语法格式：GROUP BY 列名表【任务描述】【任务描述】（18）从“成绩表”中查询每位学生所选的课程门数、最高分、最低分、总分、平均分，并用相应的中文名称表示相应的值。（19）从“成绩表”中查询选修每门课的学生人数、最高分、最低分、总分、平均分。并用相应的中文名称表示相应的值，并用相应的中文名称表示相应的值。【任务【任务4.1.4 】使用】使用GROUP BY子句分组统计查询子句分组统计查询【任务4-18】从“成绩表”中查询每位学生所选的课程门数、最高分、最低分、总分、平均分

21、，并用相应的中文名称表示相应的值。在查询分析器中运行如下命令：select StudID , count(*) 课程门数,max(score) 最高分 ,MIN(score) 最低分, sum(score) 总分, avg (score) 平均分 from ScoreInfo group by StudID【任务4-19】从“成绩表”中查询选修每门课程的学生人数、最高分、最低分、总分、平均分，并用相应的中文名称表示相应的值。在查询分析器中运行如下命令：select CourseID , count(*) 课程门数,max(score) 最高分 ,MIN(score) 最低分, sum(scor

22、e) 总分, avg (score) 平均分 from ScoreInfo group by CourseID 【任务【任务4.1.5 】使用】使用HAVING子句限定分组统计查询子句限定分组统计查询HAVING子名与GROUP BY参数配合筛选统计结果。HAVING子句的逻辑表达式通常包含聚合函数。注意聚合函数不能放在WHERE子句的逻辑表达式中。语法格式：HAVING逻辑表达式【任务描述】【任务描述】（20）从“成绩表”中查询选修课程门数大于2门的每位学生所选的课程门数、最高分、最低分、总分、平均分，并用相应的中文名称表示相应的值select StudID , count(*) 课程门数,

23、max(score) 最高分 ,MIN(score) 最低分, sum(score) 总分, avg (score) 平均分 from ScoreInfo group by StudID having COUNT(*)2SQL Server 2008 数据库技术及应用数据库技术及应用【任务【任务4.1.6 】使用】使用ORDER BY子句排序查询子句排序查询【任务【任务4-21】从】从“成绩表成绩表”中查询信息，将结果按成绩由高到低排序。中查询信息，将结果按成绩由高到低排序。 SELECT * FROM ScoreInfo ORDER BY Score DESC【任务描述任务描述】 按一列名多

24、列（最多按一列名多列（最多8060个字节）对查询结果进行升序（个字节）对查询结果进行升序（ASC，默认）或降序，默认）或降序(DESC)排序。如果排序。如果ORDER BY子句后是一个列名表达式表，则系统将根据各列的次子句后是一个列名表达式表，则系统将根据各列的次序决定排序的优先级，然后排序。序决定排序的优先级，然后排序。ORDER BY对数据类型为对数据类型为varchar(max)、nvarchar(max)、varbinary(max)或或xml的的 列不使用，并且只能在外查询中使用。列不使用，并且只能在外查询中使用。语法格式：语法格式：ORDER BY 列名表达式表列名表达式表 ASC

25、|DESC如果指定了如果指定了SELECT DISTINCT ，那么，那么ORDER BY 子句中的列名就必须出现在子句中的列名就必须出现在SELECT子句【功能介绍功能介绍】【任务【任务4.1.7 】使用】使用COMPUTE BY子句汇总查询子句汇总查询COMPUTE子句包括COMPUTE和COMPUTE BY两种。COMPUTE BY子句使用户可以用同一SELECT 语句既查看明细行，又查看分类汇总行；而COMPUTE子句使用户可以用同一SELECT语句既可查看明细行，又查看总计行。可以计算机整个结果集汇总值，也可以计算子组的汇总值。COMPUTE子句需要下列信息：（1）可选的BY关键字，

26、该关键字可对一列计算指定的行统计；（2）行统计函数名称；例如，SUM、AVG、MIN、MAX、COUNT；（3）对其执行行统计函数的列。语法格式：（1）COMPUTE 聚合函数（数值型列名） （2）ORDER BY 分组字段名 COMPUTE 聚合函数（数值型列名） BY 分组字段名【功能描述功能描述】【任务【任务4.1.7 】使用】使用COMPUTE BY子句汇总查询子句汇总查询（22）从“成绩表”中计算机结果集中成绩的汇总值。 SELECT StudID,CourseID,Score FROM ScoreInfo COMPUTE SUM(Score)（23）按学号进行分组，计算结果集中成绩

27、的汇总值。SELECT StudID,CourseID,Score FROM ScoreInfo ORDER BY StudidCOMPUTE SUM(score),AVG(Score) BY StudID【任务实施任务实施】【任务【任务4.1.8 】使用】使用INTO子句保存查询子句保存查询INTO子句指定使用结果集创建新表。这种方式常用于创建表的副本。INTO 子名位于FROM子句前。语法格式：INTO 新表名【任务描述】【任务描述】（24）从“成绩表”中查询选修课程门数大于2门的每位学生所选的课程门数、最高分、最低分、总分、平均分，用相应的中文名称表示相应的值，并将查询结果保存至表Sco

28、re_gr_ha中。select StudID , count(*) 课程门数,max(score) 最高分 ,MIN(score) 最低分, sum(score) 总分, avg (score) 平均分 into Score_gr_ha from ScoreInfo group by StudID having COUNT(*)2【任务描述任务描述】4.2创建连接查询创建连接查询【任务任务4.2.1】创建基本连接查询创建基本连接查询【任务任务4.2.2 】创建内连接查询创建内连接查询【任务任务4.2.3 】创建外连接查询创建外连接查询【任务任务4.2.4 】创建交叉连接查询创建交叉连接查询【

29、任务任务4.2.1】创建基本连接查询创建基本连接查询前面主要介绍了在一个数据表中查询数据的方法。在实际应用中，往往需要在多个数据表之间查询，才能满足用户查询需求。 实现从两个或两个以上数据表中查询数据且结果集中出现的更来自于两个或两个以上数据表的检索操作称为连接查询。其实质是通过各个数据表之间的共同列的相关性来查询数据，首先在数据表之间建立连接，然后再从中查询数据。 连接的类型分为内连接、外连接和交叉连接。其中外连接包括左外连接、右外连接和全外连接。 连接查询根据语法分类分为：基本连接查询和ANSI（American National Standards Institute，美国标准学会）连接

30、查询两种类型。【任务任务4.2.1】创建基本连接查询创建基本连接查询1. 基本连接查询基本连接查询是早期的SQL SERVER连接的语法形式。连接条件写在WHERE子句的逻辑表达式中，实现连接。语法格式：语法格式：FROM ， WHERE 为书写方便，也可给表1和表2起别名，因此上述格式还可写为：FROM 别名1， 别名2 WHERE 连接操作符可以是：=、=、= 等，其中“=”表示等值连接，使用情况也最广泛。对于Select 子句中，当字段来源于多个数据表，且同名时，必须注明字段所在的数据表名称，例如：表1.字段。【任务任务4.2.1】创建基本连接查询创建基本连接查询2. ANSI连接查询连

31、接查询ANSI连接查询的语法形式，在FROM子句中使用JOINON关键字，连接条件写在ON后面，从而实现连接。SQL SERVER 2008 R2 推荐使用ANSI连接查询。语法格式：语法格式：FROM JOIN ON 连接类型包括：内连接（INNER）、外连接（LEFT、RIGHT或FULL）、交叉连接（CROSS JOIN）。【任务任务4.2.1】创建基本连接查询创建基本连接查询交叉连接又称笛卡尔积，返回的结果记录数等于第1个数据表的记录数乘以第2个表的记录数。例如：学生信息表StudentInfo中有10条记录，成绩表ScoreInfo中有8条记录，其结果有(10*8=80)条记录，交叉

32、连接方式的关键字是：Cross Join，一般用于测试数据库的执行效率，实际应用中使用较少。其语法格式： FROM 表1 CROSS JOIN 表2内连接是用比较运算符比较要连接列的值的连接，当至少有一个同属于两个表的记录符合连接条件时才返回记录，其可消除与另一个表中的任何不匹配的记录，连接条件通常采用“主键=外键”的形式。 【任务任务4.2.1】创建基本连接查询创建基本连接查询【任务描述任务描述】（25）在学生成绩管理数据库）在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要中，查询学生的成绩，要求有学生学号、姓名、所在班级号，课程号，成绩等。求有学生学号、姓名、所在班

33、级号，课程号，成绩等。（26）在学生成绩管理数据库）在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要中，查询学生的成绩，要求有学生学号、姓名、班级号、班级名称，课程号，课程名、成绩等。求有学生学号、姓名、班级号、班级名称，课程号，课程名、成绩等。（27）在学生成绩管理数据库）在学生成绩管理数据库StudentScore_DB中，查询男同学的成绩，中，查询男同学的成绩，要求有学生学号、姓名、性别、班级号、班级名称，课程号，课程名、成要求有学生学号、姓名、性别、班级号、班级名称，课程号，课程名、成绩等。绩等。分析任务25可以发现，其查询涉及到两个表：学生信息表(Studen

34、tInfo)、成绩表（ScoreInfo），这两个表通过学号(StudID)关联，故其连接条件为：StudentInfo.StudID=ScoreInfo.StudID，该连接条件位于WHERE子句中，且对于两个表中的共同字段要注明哪个表中的字段，如：StudentInfo.StudID。为书写方便，SQL 支持使用别名，用于简化。1两个数据表之间的连接查询两个数据表之间的连接查询【任务【任务4-25】在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求有学生学号、姓名、所在班级号，课程号，成绩等。在【SQL编辑器】中运行如下命令：Select StudentInfo .

35、StudID ,StudName,ClassID,CourseID,Score From StudentInfo ,ScoreInfo Where StudentInfo .StudID =ScoreInfo .StudID 上面语句也可简化为：Select S1.StudID ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 ,ScoreInfo S2 Where S1.StudID =S2.StudID 【任务实施任务实施】【任务任务4.2.1】创建基本连接查询创建基本连接查询2多个数据表之间的连接查询多个数据表之间的连接查询【任务4-

36、26】在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求有学生学号、姓名、所在班级名称，课程号，课程名、成绩等。分析任务26可以发现，其查询涉及四个数据表：班级名、学生表、课程表和成绩表的连接。连接条件为：班级表（ClassInfo）与学生表（StudentInfo）之间共同字段为班级号(ClassID)，课程表(CourseInfo)与成绩表（ScoreInfo）共字段为课程号（CourseID），学生表（StudentInfo）与成绩表（ScoreInfo）之间共同字段为学号（StudID），因此连接条件在WHERE子句中用AND 连接即可。若有其它筛选条件，在WH

37、ERE子句中使用AND连接，如任务27。语法格式：语法格式：FROM 表1，表2，表3 WHERE 表1.列名1=表2.列名1 AND 表2.列名2=表3.列名1Select S1.StudID ,S1.StudName,S1.StudSex ,S1.ClassID,C1.ClassName, C2.CourseID,C2.CourseName ,S2.Score From ClassInfo C1,StudentInfo S1 ,ScoreInfo S2,CourseInfo C2where S1.StudID =S2.StudID and C1.ClassID =S1.ClassID an

38、d C2.CourseID =S2.CourseID【任务实施任务实施】【任务任务4.2.1】创建基本连接查询创建基本连接查询【任务4-27】在学生成绩管理数据库StudentScore_DB中，查询男同学的成绩，要求有学生学号、姓名、性别、班级号、班级名称，课程号，课程名、成绩等。在【SQL编辑器】中执行如下命令： Select s1.StudID ,s1.StudName,s1.StudSex ,s1.ClassID,c1.ClassName, c2.CourseID,c2.CourseName ,s2.Score From ClassInfo c1,StudentInfo s1 ,Sco

39、reInfo s2,CourseInfo c2 Where S1.StudID =S2.StudID and c1.ClassID =s1.ClassID and c2.CourseID =s2.CourseID and s1.StudSex =男【任务实施任务实施】【任务【任务4.2.2】创建内连接查询】创建内连接查询 内连接是组合两个表的常用方法。内连接使用比较运算符进行多个源表之间数据的比较，并返回这些源表中与连接条件相匹配的数据行。使用JOIN或INNER JOIN关键字实现内连接。其执行连接查询后，结果仅显示匹配行的所有记录，其它不匹配的记录将被过滤掉。内连接分为等值连接、非等值连接

40、和自然连接。在连接条件中使用“=”时，称为等值连接。 连接字段相同，且在SELECT 语句中除去重复字段的等值连接称为自然连接，其查询结果与基本连接中使用“=”连接运算符时相同。在连接条件中使用其它运算符（如：、=、=、等）称为非等值连接。 用比较运算符比较要连接列的值的连接，当至少有一个同属于两个表的记录符合连接条件时才返回记录，其可消除与另一个表中的任何不匹配的记录，连接条件通常采用“主键=外键”的形式。 【任务【任务4.2.2】创建内连接查询】创建内连接查询两个表内连接时语法格式：两个表内连接时语法格式：FROM 表1 INNER JOIN 表2 ON 表1与表2 的连接表达式多表内连接

41、时语法格式：多表内连接时语法格式：FROM 表1 INNER JOIN 表2 ON 表1与表2的连接表达式 INNER JOIN 表3 ON 表2与表3的连接表达式 【任务【任务4.2.2】创建内连接查询】创建内连接查询（25）在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求有学生学号、姓名、所在班级号，课程号，成绩等。（26）在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求有学生学号、姓名、班级号、班级名称，课程号，课程名、成绩等。（27）在学生成绩管理数据库StudentScore_DB中，查询男同学的成绩，要求有学生学号、姓名、性别、班

42、级号、班级名称，课程号，课程名、成绩等。【任务描述任务描述】【任务【任务4.2.2】创建内连接查询】创建内连接查询1两个数据表之间的连接查询两个数据表之间的连接查询【任务【任务4-25】在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求有学生学号、姓名、所在班级号，课程号，成绩等。Select S1.StudID ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 INNER JOIN ScoreInfo S2 ON S1.StudID =S2.StudID【任务4-26】使用内连接查询时，命令如下： Select S

43、1.StudID ,S1.StudName,S1.StudSex ,S1.ClassID,C1.ClassName, C2.CourseID,C2.CourseName ,S2.Score From ClassInfo C1 INNER JOIN StudentInfo S1 ON C1.ClassID =S1.ClassID INNER JOIN ScoreInfo S2ON S1.StudID =S2.StudID INNER JOIN CourseInfo C2ON C2.CourseID =S2.CourseID【任务描述任务描述】【任务【任务4.2.2】创建内连接查询】创建内连接查询

44、【任务4-27】在学生成绩管理数据库StudentScore_DB中，查询男同学的成绩，要求有学生学号、姓名、性别、班级号、班级名称，课程号，课程名、成绩等。Select S1.StudID ,S1.StudName,S1.StudSex ,S1.ClassID,C1.ClassName, C2.CourseID,C2.CourseName ,S2.Score From ClassInfo C1 INNER JOIN StudentInfo S1 ON C1.ClassID =S1.ClassID INNER JOIN ScoreInfo S2ON S1.StudID =S2.StudID I

45、NNER JOIN CourseInfo C2ON C2.CourseID =S2.CourseIDWhere S1.StudSex =男【任务描述任务描述】【任务【任务4.2.3】创建外连接查询】创建外连接查询外连接返回FROM子句中指定的至少一个表或视图中的所有行，只要这些行符合任何WHERE选择（不包括ON之后的连接条件）或HAVING限定条件。外连接又分为左外连接、右外连接和全外连接。FROM子句表称为左表，JOIN子句表称为右表。左外连接对连接中左边的表不加限制；右外连接中对连接中右边的表不加限制；全外连接对两个表都不加限制，两个表的所有行都包括在结果集中。语法格式： FROM 表1

46、 LEFT|RIGHT|FULL JOIN 表2 ON 表1与表2的连接表达式。【功能描述功能描述】【任务【任务4.2.3】创建外连接查询】创建外连接查询（28）在学生成绩管理数据库StudentScore_DB中，查询学生的信息，要求有学生学号、姓名、性别、所在班级号、班级名，目前暂无学生的班级也要显示出来。1. 使用左外连接查询使用左外连接查询如前所述，左外连接对连接中左边的表不加限制，即左边的数据表记录会全部显示在结果集中。语法格式 ：FROM 表1 LEFT JOIN 表2 ON 表1与表2的连接表达式【任务4-28】命令如下：Select S1.StudID ,StudName,C1

47、.ClassID,C1.ClassName From ClassInfo C1 LEFT JOIN StudentInfo S1 ON C1.ClassID =S1.ClassID【任务实施任务实施】【任务【任务4.2.3】创建外连接查询】创建外连接查询（29）在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求显示：学生表中的学号、成绩表中的学号、学生姓名、所在班级号、课程号、成绩。2. 使用右外连接查询使用右外连接查询右外连接中对连接中右边的表不加限制， RIGHT JOIN子句表称为右表。语法格式 ：FROM 表1 RIGHT JOIN 表2 ON 表1与表2的连接

48、表达式有时当参照完整性约束时不完善时，会出现在成绩表中有某学号学生成绩，而在学生表中不存在该学号信息，即出现异常成绩。【任务4-29】命令如下：Select S1.StudID 学生表中学号 ,S2.StudID 成绩表中学号 ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 right JOIN ScoreInfo S2ON S1.StudID =S2.StudID【任务描述任务描述】【任务【任务4.2.3】创建外连接查询】创建外连接查询（30）在学生成绩管理数据库StudentScore_DB中，查询学生的成绩，要求显示：学生表中的学号

49、、成绩表中的学号、学生姓名、所在班级号、课程表中的课程号、课程名、成绩。两个数据表的记录都要显示出来。3. 全连接查询全连接查询全外连接对两个表都不加限制，两个表的所有行都包括在结果集中。语法格式 ：FROM 表1 FULL JOIN 表2 ON 表1与表2的连接表达式【任务4-30】命令如下：Select S1.StudID 学生表中学号 ,S2.StudID 成绩表中学号 ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 FULL JOIN ScoreInfo S2ON S1.StudID =S2.StudID FULL JOIN Co

50、urseInfo C1ON S1.CourseID=C1.CourseID【任务描述任务描述】【任务【任务4.2.3】创建外连接查询】创建外连接查询4. 自连接查询自连接查询表可以通过自连接实现自身的连接运算。自连接可以看作是一张表的两个副本之间进行的连接，在自连接中，必须为表指定两个不同的别名，使之在逻辑上成为两张表。语法格式： FROM 表1 别名1 JOIN 表1 别名2 ON 连接表达式【任务4-31】在学生成绩管理数据库StudentScore_DB中，查询选修了“A01001”课程的学生的成绩排名，要求查询结果中包含学号，名次字段。Select S1.Studid,count(*)

51、as 名次FROM ScoreInfo S1 INNER JOIN ScoreInfo S2on S1.Score76)【任务实施任务实施】4.3.1创建子查询创建子查询（31）查询每位学生的平均成绩，包括：学号，姓名，平均成绩。（32）查询选修了课程的学生学号，姓名，班级号。（33）查询平均分大于76分的学生信息。【任务描述任务描述】4.3.1 创建子查询创建子查询（31）查询每位学生的平均成绩，包括：学号，姓名，平均成绩。（32）查询选修了课程的学生学号，姓名，班级号。（33）查询平均分大于76分的学生信息。【任务描述任务描述】【任务【任务4.3.2】子查询在数据更新中的运用】子查询在数据

52、更新中的运用子查询除可以嵌套在外部SELECT以外，还可以嵌套在INSERT、UPDATE、DELETE语句的WHERE或HAVING子句内，或者其他子查询中。任何可以使用表达式的地方都可以使用子查询，只要它返回的是单个值。1. 子查询在INSERT语句中的应用在INSERT语句中使用SELECT子句可以将一个或多个表或视图中的值添加到另一个表中。使用SELECT 子句可以同时插入多行。语法格式： INSERT INTO 表1 SELECT 字段列表 FROM 表2说明：表2结构要与表1结构相匹配。2. 子查询在UPDATE语句中的应用。UPDATE语句中SET、WHERE子句中均可使用SEL

53、ECT子查询。3. 子查询在DELETE语句中的应用。DELETE语句中，WHERE子句中可以使用SELECT子查询。【功能描述功能描述】【任务【任务4.3.2】子查询在数据更新中的运用】子查询在数据更新中的运用（36）新建一个与ScoreInfo结构相同的表，New_Score，然后将ScoreInfo表中成绩小于等于60的记录添加到表New_Score中。（37）将学生表StudentInfo备份，产生一个新表，表名为：New_Student；然后再根据成绩表中分数不及格的学生的学号，将新表New_Student中相应学生的备注字段：Remarks的值修改为“成绩不合格，请参加补考”。【任

54、务描述任务描述】【任务4-36】实施步骤（1）创建一个结构与ScoreInfo相同的空表New_Score，请读者自行完成。（2）将ScoreInfo表中成绩小于等于70的记录添加到表New_Score中。命令如下：insert into new_score select * from scoreinfo where score70【任务4-37】实施步骤（1）将学生表StudentInfo备份，产生一个新表，表名为：New_Student（2）将新表New_Student中相应学生的备注字段：Remarks的值修改为“成绩不合格，请参加补考”。命令如下：select * into New_student from studentinfogo【任务实施任务实施】【任务【任务4.3.2】子查询在数据更新中的运用】子查询在数据更新中的运用【任务【任务4.3.2】子查询在数据更新中的运用】子查询在数据更新中的运用update new_student set remarks=成绩不合格，请参加补考 where exists(select studid from scoreinfo where score60 and studid=new_student.studid)goselec