单元5查询和更新数据

1、单元5 查询和更新数据 引言引言 数据库、表创建好后，接下来的工作是对数据进行操作，数据库、表创建好后，接下来的工作是对数据进行操作， 包括查询数据、插入数据、修改数据和删除数据等。包括查询数据、插入数据、修改数据和删除数据等。 数据操作是数据库工程师和数据库相关岗位人员日常工数据操作是数据库工程师和数据库相关岗位人员日常工 作中必做的也是作中必做的也是最频繁最频繁的工作。的工作。 【学习任务学习任务】 任务任务1 单表查询单表查询 任务任务2 数据汇总统计数据汇总统计 任务任务3 多表连接查询多表连接查询 任务任务4 数据更新数据更新 任务任务5 使用图形工具进行数据操作使用图形工具进行数据

2、操作 【学习目标学习目标】 理解理解SELECTSELECT语句的语法格式；语句的语法格式； 能根据实际需求对单表或多表进行数据查询，熟能根据实际需求对单表或多表进行数据查询，熟 练编写练编写SELECTSELECT语句；语句； 能对数据进行汇总计算、分组统计；能对数据进行汇总计算、分组统计； 能熟练使用能熟练使用SQLSQL语句对数据进行更新；语句对数据进行更新； 能使用图形工具进行数据操作。能使用图形工具进行数据操作。 任务任务1 任务任务1 1 单表查询单表查询 【任务提出任务提出】 数据库中最常见的操作是数据查询，可以说数据查询是数数据库中最常见的操作是数据查询，可以说数据查询是数 据

3、库的核心操作。据库的核心操作。 查询可以对单表进行查询，也可以完成复杂的连接查询和查询可以对单表进行查询，也可以完成复杂的连接查询和 嵌套查询，其中对单表进行查询是数据查询操作中最简单嵌套查询，其中对单表进行查询是数据查询操作中最简单 的，所以我们先从的，所以我们先从单表查询单表查询入手学习数据查询操作。入手学习数据查询操作。 【任务分析任务分析】 实现数据查询操作必须使用实现数据查询操作必须使用SQL语言中的语言中的SELECT语句语句。 所以先学习和理解所以先学习和理解SELECT语句，然后针对实际需求对表语句，然后针对实际需求对表 进行查询。进行查询。 【相关知识与技能相关知识与技能】

4、1单表查询的单表查询的SELECT语句语句 SELECT ALL|DISTINCT 目标列表达式目标列表达式 FROM 表名表名 WHERE 行条件表达式行条件表达式 ORDER BY 排序列排序列 ASC|DESC 【相关知识与技能相关知识与技能】 2SELECT语句的含义语句的含义 根据根据WHERE子句的行条件表达式，子句的行条件表达式， 从从FROM子句指定的表中找出满足条件的行（记录），子句指定的表中找出满足条件的行（记录）， 再按再按SELECT子句中的列名或表达式选出记录中的字子句中的列名或表达式选出记录中的字 段值形成查询结果。段值形成查询结果。 如果有如果有ORDER BY子

5、句，则查询结果还要按照排序列子句，则查询结果还要按照排序列 的值进行升序或降序排列。的值进行升序或降序排列。 【任务实施任务实施】 1选择表中的若干列选择表中的若干列 （1）查询部分列）查询部分列 格式：格式：SELECT 列名列名，n FROM 表名表名 【例【例1】查询所有学生的学号和姓名。】查询所有学生的学号和姓名。 【练习【练习1】查询所有课程的课程编号、课程名称和课程学】查询所有课程的课程编号、课程名称和课程学 分。分。 【任务实施任务实施】 （2）查询全部列）查询全部列 格式：格式：SELECT * FROM 表名表名 【例【例2】查询全体学生的详细信息。】查询全体学生的详细信息。

6、 【练习【练习2】查询所有班级的详细信息。】查询所有班级的详细信息。 【任务实施任务实施】 （3）为查询结果集内的列指定别名）为查询结果集内的列指定别名 格式格式1：SELECT 原列名原列名 AS 列别名列别名,n FROM 表名表名 格式格式2：SELECT 原列名原列名 列别名列别名,n FROM 表名表名 格式格式3：SELECT 列别名列别名=原列名原列名,n FROM 表名表名 【例例3】查询所有学生的学号和姓名，并指定别名为学生学号、查询所有学生的学号和姓名，并指定别名为学生学号、 学生姓名。学生姓名。 【练习【练习3】查询所有班级的详细信息，并给查询结果各列指定】查询所有班级的

7、详细信息，并给查询结果各列指定 中文意义的别名。中文意义的别名。 【任务实施任务实施】 （4）查询经过计算的列）查询经过计算的列 格式：格式：SELECT 计算表达式或列名计算表达式或列名 FROM 表名表名 【例【例4】查询所有学生的学号、姓名和出生年份。】查询所有学生的学号、姓名和出生年份。 【练习【练习4】查询所有学生的学号、姓名和年龄。】查询所有学生的学号、姓名和年龄。 【任务实施任务实施】 2选择表中的若干行选择表中的若干行 （1）查询满足条件的记录）查询满足条件的记录 格式：格式：SELECT 目标列表达式目标列表达式 FROM 表名表名 WHERE 行条件表达式行条件表达式 【任

8、务实施任务实施】 1）比较大小）比较大小 【例【例5】查询所有女生的学号和姓名。】查询所有女生的学号和姓名。 【练习练习5】查询课程学时超过查询课程学时超过50学时的课程号和课程名称。学时的课程号和课程名称。 【练习练习6】查询所有在查询所有在1992年年5月月10日后（包含日后（包含1992年年5月月 10日）出生的学生的详细信息。日）出生的学生的详细信息。 【练习练习7】查询年龄小于查询年龄小于20岁的学生的学号、姓名和出生岁的学生的学号、姓名和出生 年月。查询结果应如图年月。查询结果应如图5-12所示。所示。 【任务实施任务实施】 2）确定范围）确定范围 列名或计算表达式列名或计算表达式

9、 NOT BETWEEN 下限值下限值 AND 上限值上限值 【例【例6】查询平时成绩在】查询平时成绩在90100之间（包含之间（包含90和和100）的）的 学号和课程编号。学号和课程编号。 【练习练习8】查询出生年月在查询出生年月在1991年年1月月1日至日至1991年年5月月30 日之间的学生的学号和姓名。日之间的学生的学号和姓名。 【任务实施任务实施】 3）确定集合）确定集合 列名或表达式列名或表达式 NOT IN (常量常量1,常量常量2,常量常量n） 【例例7】查询课程学时为查询课程学时为30或或60的课程的课程编号和课程的课程的课程编号和课程 名称。名称。 【练习练习9】查询所属专

10、业为查询所属专业为计算机应用技术计算机应用技术、软件软件 技术技术的班级的班级编号、班级名称及入学年份。的班级的班级编号、班级名称及入学年份。 【任务实施任务实施】 4）字符匹配）字符匹配 列名列名 NOT LIKE 【例例8】查询所有姓查询所有姓陈陈的学生的学号和姓名。的学生的学号和姓名。 【练习【练习10】查询所有姓陈且名为单个字的学生的学号和姓】查询所有姓陈且名为单个字的学生的学号和姓 名。名。 【练习练习11】查询所有课程名称中含有查询所有课程名称中含有数据库数据库的课程的课的课程的课 程编号、课程名称。程编号、课程名称。 【练习练习12】查询学生姓名中姓查询学生姓名中姓张张、林林、江

11、江的学生的学的学生的学 号和姓名。号和姓名。 【任务实施任务实施】 5）涉及空值）涉及空值 列名列名 IS NOT NULL 【例例9】查询期末成绩现为空的学生的学号和课程编号。查询期末成绩现为空的学生的学号和课程编号。 【任务实施任务实施】 （2）消除取值重复的行）消除取值重复的行 SELECT DISTINCT 目标列表达式目标列表达式 FROM 表名表名 【例例10】查询期末成绩有不及格的学生的学号。查询期末成绩有不及格的学生的学号。 【练习练习13】查询所有有选课记录的学生的学号。查询所有有选课记录的学生的学号。 【任务实施任务实施】 （3）限制返回行数）限制返回行数 SELECT T

12、OP 指定的行数指定的行数 PERCENT 目标列表达式目标列表达式 FROM 表名表名 【例例11】查询返回学生表中的最前面查询返回学生表中的最前面2条记录作为样本数据条记录作为样本数据 显示。显示。 【任务实施任务实施】 3对查询结果排序对查询结果排序 ORDER BY 排序列名排序列名 ASC|DESC 【例例12】查询所有学生的详细信息，查询结果按照出生年查询所有学生的详细信息，查询结果按照出生年 月降序排列。月降序排列。 【练习练习14】查询选修了课程编号为查询选修了课程编号为0901170的课程的学生的课程的学生 的学号及其平时成绩，查询结果按照平时成绩升序排列。的学号及其平时成绩

13、，查询结果按照平时成绩升序排列。 【练习练习15】查询所有学生的详细信息，查询结果按照班级查询所有学生的详细信息，查询结果按照班级 编号升序排列，对同一个班的学生按照学号升序排列。编号升序排列，对同一个班的学生按照学号升序排列。 【练习练习16】查询所有学生中年龄最大的那位学生的学号和查询所有学生中年龄最大的那位学生的学号和 姓名。姓名。 【任务实施任务实施】 4多重条件查询多重条件查询 【例例13】查询班级编号为查询班级编号为200901001的班中所有男生的的班中所有男生的 【练习练习17】查询课程的平时成绩或期末成绩超过查询课程的平时成绩或期末成绩超过90分的学分的学 生的学号和课程编号

14、，查询结果按照学号升序排列，学号生的学号和课程编号，查询结果按照学号升序排列，学号 相同的按照课程编号降序排列。相同的按照课程编号降序排列。 【练习练习18】查询出年龄超过查询出年龄超过20岁女生的详细信息。岁女生的详细信息。 【任务总结任务总结】 行条件表达式中的归纳注意点如下：行条件表达式中的归纳注意点如下： 表达式中的字符型常量必须用单引号括起来，但字段名表达式中的字符型常量必须用单引号括起来，但字段名 不能用单引号括起来；不能用单引号括起来； 日期时间型常量须用单引号括起来。如日期时间型常量须用单引号括起来。如1992年年5月月10日日 可使用以下任一格式表示：可使用以下任一格式表示：

15、1992-05-10、1992/05/10、 05/10/1992、19920510。 范围运算符范围运算符BETWEENAND的语法格式为：的语法格式为： 列名 BETWEEN 下限值 AND 上限值； 【任务总结任务总结】 列表运算符列表运算符IN的语法格式为：的语法格式为： 列名列名 IN (常量常量1,常量常量2,常量常量n)； 模式匹配符模式匹配符LIKE的语法格式为：的语法格式为： 列名列名 LIKE ； 空值判断符空值判断符IS NULL的语法格式为：的语法格式为： 列名列名 IS NULL，不要写成：列名，不要写成：列名=NULL； 如果有多个条件，须使用如果有多个条件，须使用

16、AND或或OR连接。切记出现如下连接。切记出现如下 表达式表达式90=Uscore=100。 【拓展练习拓展练习】 1从从Dorm表中查询所有宿舍的详细信息。表中查询所有宿舍的详细信息。 2从从Live表中查询学号为表中查询学号为200931010100101学生的住学生的住 宿信息，包含宿舍编号宿信息，包含宿舍编号DormNo、床位号、床位号BedNo和入住日和入住日 期期InDate。 3从从Dorm表中查询所有男生宿舍（宿舍类别表中查询所有男生宿舍（宿舍类别DormType 为为男男）的详细信息，结果按照楼栋）的详细信息，结果按照楼栋Build升序排列，楼栋升序排列，楼栋 相同的按照宿舍

17、编号相同的按照宿舍编号DormNo升序排列。升序排列。 4从从Live表中查询在表中查询在2010年年9月份入住宿舍的学生的学月份入住宿舍的学生的学 号号Sno、宿舍编号、宿舍编号DormNo和床位号和床位号BedNo。 【拓展练习拓展练习】 5从从CheckHealth表中查询宿舍编号表中查询宿舍编号DormNo为为 LCB04N101宿舍在宿舍在2010年年10月份的卫生检查情况，结月份的卫生检查情况，结 果包含检查时间果包含检查时间CheckDate、检查人员、检查人员CheckMan、成绩、成绩 Score和存在问题和存在问题Problem。 6从从CheckHealth表中查询在表中

18、查询在2010年年10月月1日至日至2010年年 11月月30日之间宿舍卫生检查成绩日之间宿舍卫生检查成绩Score在在7080分（包含分（包含 70、80分）之间的宿舍编号分）之间的宿舍编号DormNo、检查时间、检查时间 CheckDate和存在问题和存在问题Problem。 7从从Dorm表中查询在表中查询在龙川南苑龙川南苑的宿舍详细信息。（在的宿舍详细信息。（在 龙川南苑龙川南苑指楼栋指楼栋Build包含包含龙川南苑龙川南苑）。）。 【拓展练习拓展练习】 8从从Dorm表中查询宿舍电话表中查询宿舍电话Tel目前为空的宿舍的宿舍目前为空的宿舍的宿舍 编号编号DormNo、楼栋、楼栋Bui

19、ld、楼层、楼层Storey和房间号和房间号 RoomNo。 9从从Student表中查询所有学生的学号表中查询所有学生的学号Sno、姓名、姓名 Sname和年龄，查询结果按照年龄降序排列。和年龄，查询结果按照年龄降序排列。 10从从CheckHealth表中查询表中查询2010年年10月卫生检查成绩月卫生检查成绩 Score最高的宿舍编号最高的宿舍编号DormNo和检查时间和检查时间CheckDate。 任务任务2 任务任务2 2 数据汇总统计数据汇总统计 【任务提出任务提出】 在对表数据进行查询中，经常会对数据进行在对表数据进行查询中，经常会对数据进行统计计算统计计算，如，如 统计个数、平

20、均值、最大最小值、计算总和等操作。另外，统计个数、平均值、最大最小值、计算总和等操作。另外， 还会根据需要对数据进行分开统计汇总，如统计各个班级还会根据需要对数据进行分开统计汇总，如统计各个班级 的人数等操作。的人数等操作。 【任务分析任务分析】 SQL提供了许多提供了许多集函数集函数对数据进行各种统计计算。若需要对数据进行各种统计计算。若需要 对数据进行分组统计计算，对数据进行分组统计计算，GROUP BY子句子句就能够实现就能够实现 这种分组统计。这种分组统计。 【相关知识与技能相关知识与技能】 1集函数集函数 【任务实施任务实施】 1使用集函数汇总数据使用集函数汇总数据 【例例1】统计统

21、计Student表中学生的记录数。表中学生的记录数。 【例【例2】统计出信息工程学院的专业个数。】统计出信息工程学院的专业个数。 【任务实施任务实施】 【练习练习1】查询学号为查询学号为200931010100101学生的所有选学生的所有选 修课程的平时成绩的总分和平均分。修课程的平时成绩的总分和平均分。 【练习练习2】查询课程编号为查询课程编号为2003003课程的学生期末成绩课程的学生期末成绩 的最高分和最低分。的最高分和最低分。 【相关知识与技能相关知识与技能】 2分组统计分组统计 GROUP BY 分组列名分组列名 3对组筛选对组筛选 HAVING 组筛选条件表达式组筛选条件表达式 区

22、分区分HAVING子句和子句和WHERE子句子句: HAVING子句是对子句是对GROUP BY分组后的组进行筛选，选择出满足分组后的组进行筛选，选择出满足 条件的组；条件的组； 而而WHERE子句是对表中记录进行选择，选择出满足条件的行。子句是对表中记录进行选择，选择出满足条件的行。 HAVING子句中可以使用集函数，一般子句中可以使用集函数，一般HAVING子句中的组筛选条子句中的组筛选条 件就是集函数。而件就是集函数。而WHERE子句中绝对不能出现集函数。子句中绝对不能出现集函数。 【任务实施任务实施】 2进行分组统计进行分组统计 【例【例3】统计各班级学生人数。】统计各班级学生人数。

23、【练习练习3】统计各门课程的选课人数。统计各门课程的选课人数。 【练习【练习4】统计各门课程学生的平时成绩平均分、期末成】统计各门课程学生的平时成绩平均分、期末成 绩平均分。绩平均分。 【任务实施任务实施】 3对组进行筛选对组进行筛选 【例例4】查询出课程选课人数超过查询出课程选课人数超过2人的课程编号。人的课程编号。 【练习练习5】查询出所有选修课程的平均期末成绩小于查询出所有选修课程的平均期末成绩小于50分分 的学生学号。的学生学号。 【任务总结任务总结】 若要对数据库表中数据进行统计计算，可使用集函数。若若要对数据库表中数据进行统计计算，可使用集函数。若 要对数据进行分组统计计算，使用要

24、对数据进行分组统计计算，使用GROUP BY子句。若子句。若 在表中数据分组后还要对这些组按条件进行筛选，输出满在表中数据分组后还要对这些组按条件进行筛选，输出满 足条件的组，则使用足条件的组，则使用HAVING子句。子句。 SELECT ALL|DISTINCT 目标列表达式目标列表达式 FROM 表名表名 WHERE 行条件表达式行条件表达式 GROUP BY 分组列名分组列名 HAVING 组筛选条件表达式组筛选条件表达式 ORDER BY 排序列排序列 ASC|DESC 【拓展练习拓展练习】 1从从Dorm表中查询所有男宿舍的总床位数。男宿舍指宿表中查询所有男宿舍的总床位数。男宿舍指宿

25、 舍类别舍类别DormType值为值为男男。 2从从CheckHealth表中查询宿舍编号为表中查询宿舍编号为LCB04N101 宿舍的被检查人员检查的次数。宿舍的被检查人员检查的次数。 3从从CheckHealth表中查询表中查询2010年年11月份各宿舍的检查月份各宿舍的检查 成绩的平均值。成绩的平均值。 4从从Student表中查询目前男生的人数。表中查询目前男生的人数。 5从从Student表中查询目前男女生的人数。表中查询目前男女生的人数。 【拓展练习拓展练习】 6从从Dorm表中查询出各楼栋的房间数。表中查询出各楼栋的房间数。 7从从Live表中统计各个宿舍的现入住人数。表中统计各

26、个宿舍的现入住人数。 8从从CheckHealth表中统计各宿舍到目前为止的卫生检表中统计各宿舍到目前为止的卫生检 查的平均成绩。查的平均成绩。 9从从CheckHealth表中查询出到目前为止的卫生检查平表中查询出到目前为止的卫生检查平 均成绩超过均成绩超过90分的宿舍编号。分的宿舍编号。 10从从CheckHealth表中查询宿舍被检查次数超过表中查询宿舍被检查次数超过3次的次的 宿舍编号。宿舍编号。 任务任务3 任务任务3 3 多表连接查询多表连接查询 【任务提出任务提出】 前面任务前面任务1中完成的查询只涉及到一张表。而在实际使用中完成的查询只涉及到一张表。而在实际使用 中，查询往往是

27、中，查询往往是针对多个表针对多个表进行的，可能涉及二张或更多进行的，可能涉及二张或更多 张表。张表。 【任务分析任务分析】 在关系型数据库中，将这种涉及到两个或两个以上表的查在关系型数据库中，将这种涉及到两个或两个以上表的查 询，称为询，称为多表连接查询多表连接查询。 连接查询是关系数据库中最重要的查询。连接查询是关系数据库中最重要的查询。 连接查询根据返回的连接记录情况，分为连接查询根据返回的连接记录情况，分为“交叉连接交叉连接”、 “内连接内连接”和和“外连接外连接”查询。查询。 【相关知识与技能相关知识与技能】 1交叉连接交叉连接 将第一张表的所有记录分别与第二张表的每条记录形将第一张表

28、的所有记录分别与第二张表的每条记录形 成一条新的记录。成一条新的记录。 结果集中的记录数为两个表的记录数的乘积。结果集中的记录数为两个表的记录数的乘积。 SELECT 目标列表达式目标列表达式 FROM 表名表名1 CROSS JOIN 表名表名2 【例例1】交叉连接交叉连接Student和和Score表。表。 SELECT * FROM Student CROSS JOIN Score 【相关知识与技能相关知识与技能】 2内连接内连接 返回多个表中满足连接条件的记录。返回多个表中满足连接条件的记录。 连接条件：连接条件： FROM 表名表名1 INNER JOIN 表名表名2 ON 【例例2

29、】连接连接Student和和Score表，返回两张表中满足表，返回两张表中满足 Sno相同的记录。相同的记录。 SELECT * FROM Student JOIN Score ON Student.Sno=Score.Sno 【相关知识与技能相关知识与技能】 自然连接自然连接 ：按照两个表中的相同字段进行等值连接，且：按照两个表中的相同字段进行等值连接，且 目标列中去掉了重复的属性列，但保留了所有不重复的属目标列中去掉了重复的属性列，但保留了所有不重复的属 性列。性列。 【例例3】查询所有学生的详细信息及其选课信息，查询结查询所有学生的详细信息及其选课信息，查询结 果包含两张表中的所有列，但

30、去除重复列。果包含两张表中的所有列，但去除重复列。 SELECT Student.*,Cno,UScore,EndScore FROM Student JOIN Score ON Student.Sno=Score.Sno 【相关知识与技能相关知识与技能】 3外连接外连接 外连接查询是除返回内部连接的记录以外，还在查询结果外连接查询是除返回内部连接的记录以外，还在查询结果 中返回左表或右表或左右表中不符合条件的记录。中返回左表或右表或左右表中不符合条件的记录。 分为分为 “左外连接左外连接”、“右外连接右外连接”和和“全外连接全外连接”。 【相关知识与技能相关知识与技能】 （1）左外连接）左外

31、连接 FROM 表名表名1 LEFT OUTER JOIN 表名表名2 ON （2）右外连接）右外连接 FROM 表名表名1 RIGHT OUTER JOIN 表名表名2 ON （3）全外连接）全外连接 FROM 表名表名1 FULL OUTER JOIN 表名表名2 ON 【任务实施任务实施】 1两张表的连接两张表的连接 【例例5】查询所有学生相关信息，包含学号、姓名、班级查询所有学生相关信息，包含学号、姓名、班级 编号、班级姓名。编号、班级姓名。 步骤步骤1：分析查询涉及的表，包括查询条件和查询结果：分析查询涉及的表，包括查询条件和查询结果 涉及的表；涉及的表； 步骤步骤2：如果是涉及到多

32、张表，分析确定表与表之间的：如果是涉及到多张表，分析确定表与表之间的 连接条件；连接条件； 步骤步骤3：分析确定查询目标列表达式。：分析确定查询目标列表达式。 【任务实施任务实施】 【练习练习1】查询所有学生选修课程的详细信息，结果包含查询所有学生选修课程的详细信息，结果包含 学号、课程编号、课程名称、课程学分、平时成绩、期末学号、课程编号、课程名称、课程学分、平时成绩、期末 成绩。成绩。 【任务实施任务实施】 【例例6】查询计算机查询计算机093班学生的学号和姓名。班学生的学号和姓名。 步骤步骤1：分析查询涉及的表，包括查询条件和查询结果：分析查询涉及的表，包括查询条件和查询结果 涉及的表；

33、涉及的表； 步骤步骤2：如果是涉及到多张表，分析确定表与表之间的：如果是涉及到多张表，分析确定表与表之间的 连接条件；连接条件； 步骤步骤3：分析查询是否针对所有记录，还是选择部分行。：分析查询是否针对所有记录，还是选择部分行。 如果选择部分行，则确定行选择条件；如果选择部分行，则确定行选择条件； 步骤步骤4：分析确定查询目标列表达式。：分析确定查询目标列表达式。 【任务实施任务实施】 【练习练习2】查询课程名称中包含查询课程名称中包含数据库数据库的课程的学生成绩，的课程的学生成绩， 结果包含学号、课程编号、课程名称、平时成绩、期末成结果包含学号、课程编号、课程名称、平时成绩、期末成 绩。绩。

34、 【练习练习3】查询所有课程的课程号、课程名和学生平时成查询所有课程的课程号、课程名和学生平时成 绩，按照课程号升序排列，如果课程号相同，按照平时成绩，按照课程号升序排列，如果课程号相同，按照平时成 绩降序排列。绩降序排列。 【练习练习4】查询期末成绩有不及格课程的学生信息，结果查询期末成绩有不及格课程的学生信息，结果 包含学号、姓名、班级编号。包含学号、姓名、班级编号。 【练习练习5】查询选修了查询选修了数据库技术与应用数据库技术与应用1课程的学生课程的学生 的人数。的人数。 【练习练习6】统计各专业学生的人数，结果包含专业名称、统计各专业学生的人数，结果包含专业名称、 该专业人数。该专业人

35、数。 【任务实施任务实施】 2两张表以上的连接两张表以上的连接 连接实现的步骤是：先两张表进行连接形成虚表连接实现的步骤是：先两张表进行连接形成虚表1，然后，然后 虚表虚表1与第三张表进行连接形成虚表与第三张表进行连接形成虚表2，然后虚表，然后虚表2与第四与第四 张表进行连接，张表进行连接，形成虚表，形成虚表n，最后对虚表，最后对虚表n进行查进行查 询得出查询结果。询得出查询结果。 【任务实施任务实施】 【例例7】查询所有学生的学号、姓名、班级名称、选修的查询所有学生的学号、姓名、班级名称、选修的 课程编号及平时成绩。课程编号及平时成绩。 【练习练习7】查询班级编号为查询班级编号为200901

36、001班学生的基本信班学生的基本信 息及其选课信息，结果包含学号、姓名、性别、课程编号、息及其选课信息，结果包含学号、姓名、性别、课程编号、 课程名称。课程名称。 【练习练习8】查询查询计算机计算机092班学生的基本信息及其选课信班学生的基本信息及其选课信 息，结果包含学号、姓名、性别、课程编号、课程名称。息，结果包含学号、姓名、性别、课程编号、课程名称。 【任务总结任务总结】 SELECT语句的一般格式为：语句的一般格式为： SELECT ALL|DISTINCT 目标列表达式目标列表达式 FROM 表名表名1 JOIN 表名表名2 ON 表名表名1.列名列名1=表名表名2. 列名列名2 W

37、HERE 行条件表达式行条件表达式 GROUP BY 分组列名分组列名 HAVING 组筛选条件表达式组筛选条件表达式 ORDER BY 排序列名排序列名 ASC|DESC 【拓展知识拓展知识】 1合并结果集合并结果集 【例例1】合并女生的学号、姓名信息和男生的学号、姓名信息。合并女生的学号、姓名信息和男生的学号、姓名信息。 SELECT Sno,Sname FROM Student WHERE Sex=女女 UNION SELECT Sno,Sname FROM Student WHERE Sex=男男 【注意注意】当使用当使用UNION时，所有时，所有SELECT查询的目标列的列数查询的目

38、标列的列数 和列的顺序必须相同，同时，所有和列的顺序必须相同，同时，所有SELECT查询的目标列按查询的目标列按 顺序对应列的数据类型必须兼容。顺序对应列的数据类型必须兼容。 【拓展知识拓展知识】 2嵌套查询嵌套查询 在一个在一个SELECT查询中，在其查询中，在其WHERE子句中的行条件表子句中的行条件表 达式或达式或HAVING子句中的组筛选条件中，可含有另一个子句中的组筛选条件中，可含有另一个 SEELCT语句，这种查询称为嵌套查询。语句，这种查询称为嵌套查询。 【例例2】查询查询数据库技术与应用数据库技术与应用1课程学生的选课信息。课程学生的选课信息。 SELECT * FROM Sc

39、ore WHERE Cno=(SELECT Cno FROM Course WHERE Cname=数据库技术与应用数据库技术与应用1) 【拓展知识拓展知识】 3返回单值的子查询返回单值的子查询 【例例3】查询出与查询出与陈国成陈国成同班的学生详细信息。同班的学生详细信息。 SELECT * FROM Student WHERE ClassNo=(SELECT ClassNo FROM Student WHERE Sname=陈国成陈国成) 【例例4】查询出选修查询出选修2003003课程且平时成绩低于本课程课程且平时成绩低于本课程 平时成绩的学生学号。平时成绩的学生学号。 【拓展知识拓展知识

40、】 4返回多个值的子查询返回多个值的子查询 【例例5】查询出选修了课程编号为查询出选修了课程编号为0901169课程的学生姓课程的学生姓 名和班级编号。名和班级编号。 SELECT Sname,ClassNo FROM Student WHERE Sno IN(SELECT Sno FROM Score WHERE Cno=0901169) 【例例6】查询出选修了查询出选修了数据库技术与应用数据库技术与应用1课程的学生姓课程的学生姓 名和班级编号。名和班级编号。 【拓展练习拓展练习】 1从从Dorm和和Live表中查询所有学生的详细住宿信息，结果包含学号表中查询所有学生的详细住宿信息，结果包含

41、学号 Sno、宿舍编号、宿舍编号DormNo、楼栋、楼栋Build、房间号、房间号RoomNo、入住日期、入住日期 InDate。 2从从Dorm和和Live表中查询住在表中查询住在龙川北苑龙川北苑04南南楼栋（即字段楼栋（即字段Build 的值为的值为龙川北苑龙川北苑04南南）的学生的学号）的学生的学号Sno和宿舍编号和宿舍编号DormNo。 3从从Dorm、CheckHealth表中查询所有宿舍在表中查询所有宿舍在2010年年10月份的卫月份的卫 生检查情况，结果包含楼栋生检查情况，结果包含楼栋Build、宿舍编号、宿舍编号DormNo、房间号、房间号 RoomNo、检查时间、检查时间Ch

42、eckDate、检查人员、检查人员CheckMan、检查成绩、检查成绩 Score、存在问题、存在问题Problem。 4从从Dorm、CheckHealth表中查询表中查询龙川北苑龙川北苑04南南楼栋各宿舍的卫楼栋各宿舍的卫 生检查平均成绩，结果包含宿舍编号、平均成绩。生检查平均成绩，结果包含宿舍编号、平均成绩。 【拓展练习拓展练习】 5从从Dorm、CheckHealth表中查询表中查询龙川北苑龙川北苑04南南楼栋的宿舍在楼栋的宿舍在 2010年年10月份的卫生检查情况，结果包含宿舍编号月份的卫生检查情况，结果包含宿舍编号DormNo、房间、房间 号号RoomNo、检查时间、检查时间Che

43、ckDate、检查人员、检查人员CheckMan、检查成、检查成 绩绩Score、存在问题、存在问题Problem。 6从从Dorm、CheckHealth表中查询表中查询龙川北苑龙川北苑04南南楼栋的宿舍在楼栋的宿舍在 2010年年10月份的卫生检查成绩不及格的宿舍个数。月份的卫生检查成绩不及格的宿舍个数。 7从从Dorm、Live、Student表中查询所有学生的基本信息及其住宿表中查询所有学生的基本信息及其住宿 信息，结果包含学号信息，结果包含学号Sno、姓名、姓名Sname、性别、性别Sex、宿舍编号、宿舍编号 DormNo、楼栋、楼栋Build、房间号、房间号RoomNo、入住日期、

44、入住日期InDate。 【拓展练习拓展练习】 8从从Dorm、Live、Student表中查询姓名为表中查询姓名为王康俊王康俊学生的住宿信息，学生的住宿信息， 结果包含宿舍编号结果包含宿舍编号DormNo、房间号、房间号RoomNo、入住日期、入住日期InDate。 9从从Dorm、Live、Student、Class表中查询所有学生的详细信息及表中查询所有学生的详细信息及 其住宿信息，结果包含学号其住宿信息，结果包含学号Sno、姓名、姓名Sname、性别、性别Sex、班级编号、班级编号 ClassNo、班级名称、班级名称ClassName、宿舍编号、宿舍编号DormNo、楼栋、楼栋Build

45、、 房间号房间号RoomNo、入住日期、入住日期InDate。 10从从Dorm、Live、Student、Class表中查询表中查询计算机应用技术计算机应用技术专业专业 所有学生的入住信息，结果包含学号所有学生的入住信息，结果包含学号Sno、姓名、姓名Sname、性别、性别Sex、 班级编号班级编号ClassNo、班级名称、班级名称ClassName、宿舍编号、宿舍编号DormNo、楼栋、楼栋 Build、房间号、房间号RoomNo、入住日期、入住日期InDate。查询结果按照班级编号升。查询结果按照班级编号升 序排列，同班的按照学号升序排列。序排列，同班的按照学号升序排列。 任务任务4 任

46、务任务4 4 数据更新数据更新 【任务提出任务提出】 对数据的操作除了常用的查询操作外，还包括日常必做的对数据的操作除了常用的查询操作外，还包括日常必做的 插入数据、修改数据、删除数据等操作。插入数据、修改数据、删除数据等操作。插入数据、修改插入数据、修改 数据、删除数据数据、删除数据操作统称为数据更新。操作统称为数据更新。 【任务分析任务分析】 在数据操作中，操作的对象都是记录，而不是记录中的某在数据操作中，操作的对象都是记录，而不是记录中的某 个数据。个数据。 所以插入数据指往表中插入一条记录或多条记录，修改数所以插入数据指往表中插入一条记录或多条记录，修改数 据指对表中现有记录进行修改，

47、删除数据指删除指定的记据指对表中现有记录进行修改，删除数据指删除指定的记 录。录。 插入记录对应的插入记录对应的SQL语句是语句是INSERT语句，修改记录对应语句，修改记录对应 的的SQL语句是语句是UPDATE语句，删除记录对应的语句，删除记录对应的SQL语句是语句是 DELETE语句。语句。 【相关知识与技能相关知识与技能】 1插入一条记录插入一条记录 INSERT INTO 表名表名(列名列名1,列名列名2,列名列名n) VALUES (常量常量1,常量常量n) 2插入插入查询结果查询结果 INSERT INTO 表名表名(列名列名1,列名列名2,列名列名n) SELECT 查询语句查

48、询语句 3使用使用INTO子句生成表子句生成表 SELECT ALL|DISTINCT 目标列表达式目标列表达式 INTO 新表名新表名 FROM 表名表名 WHERE 行条件表达式行条件表达式 【相关知识与技能相关知识与技能】 4UPDATE语句语句 UPDATE 表名表名 SET 列名列名1=,列名列名2=, WHERE 行条件表达式行条件表达式 5DELETE语句语句 DELETE FROM 表名表名 WHERE 行条件表达式行条件表达式 【任务实施任务实施】 1插入一条记录插入一条记录 【例例1】往往Student表中插入一条新记录，其中学号为表中插入一条新记录，其中学号为 20093

49、1010190125、姓名为、姓名为陈红陈红，性别为，性别为女女，班级编，班级编 号为号为200901901。 INSERT INTO Student (Sno,Sname,Sex,ClassNo) VALUES (200931010190125,陈红陈红,女女,200901901) 【例例2】往往Student表中插入一条新记录，其中学号为表中插入一条新记录，其中学号为 200931010190120、姓名为、姓名为何园何园，性别为，性别为男男，出生年，出生年 月为月为1991/11/18，班级编号为，班级编号为200901901。 【任务实施任务实施】 【练习练习1】往往Class表中插入

50、所在班级的信息，往学生表表中插入所在班级的信息，往学生表 中插入本人的基本信息。中插入本人的基本信息。 【任务实施任务实施】 2插入查询结果插入查询结果 【例例3】假如已为班级编号为假如已为班级编号为200901001的班级学生单独的班级学生单独 建了一个空表建了一个空表JSJ，其中包含学号、姓名、性别和出生年，其中包含学号、姓名、性别和出生年 月四个字段，字段的数据类型和长度都与月四个字段，字段的数据类型和长度都与Student表相同，表相同， 现要从现要从Student表中查询出该班学生信息插入到表中查询出该班学生信息插入到C1表中。表中。 INSERT INTO JSJ (Sno,Sna

51、me,Sex,Birth） SELECT Sno,Sname,Sex,Birth FROM Student WHERE ClassNo=200901001 【任务实施任务实施】 3使用使用INTO子句创建新表并插入查询结果子句创建新表并插入查询结果 【例例4】创建班级编号为创建班级编号为200901002的班级学生信息表，的班级学生信息表， 表名为表名为JSJ2。 SELECT Sno,Sname,Sex,Birth INTO JSJ2 FROM Student WHERE ClassNo=200901002 【任务实施任务实施】 【练习练习2】创建创建数据库技术与应用数据库技术与应用1课程的

52、选课情况表，课程的选课情况表， 表中信息包含学生学号、平时成绩、期末成绩，表名为表中信息包含学生学号、平时成绩、期末成绩，表名为 SjkXk。 【任务实施任务实施】 【例例5】创建各门课程的平均期末成绩临时表，要求表中创建各门课程的平均期末成绩临时表，要求表中 列出各门课程的课程名称和平均期末成绩。列出各门课程的课程名称和平均期末成绩。 SELECT Cname,AVG(EndScore) 平均期末成绩平均期末成绩 INTO #平均成绩表平均成绩表 FROM Course join Score on Course.Cno=Score.Cno GROUP BY Cname 【任务实施任务实施】

53、【练习练习3】创建各门课程的选课情况临时表，要求表中列创建各门课程的选课情况临时表，要求表中列 出各门课程的课程编号、课程名称、学生的学号、平时成出各门课程的课程编号、课程名称、学生的学号、平时成 绩和期末成绩。绩和期末成绩。 【任务实施任务实施】 4修改一条记录的值修改一条记录的值 【例例6】将将Sno为为200931010100102、Cno为为0901170的期的期 末成绩修改为末成绩修改为60分。分。 UPDATE Score SET EndScore=60 WHERE Sno=200931010100102 AND Cno=0901170 【任务实施任务实施】 【练习练习4】增加增加

54、Sno为为200931010100207、Cno为为 0901170的期末成绩为的期末成绩为90分。分。 【练习练习5】增加增加Sno为为200931010100322、Cno为为 0901025的平时成绩为的平时成绩为80分，期末成绩为分，期末成绩为84分。分。 【任务实施任务实施】 5修改多条记录的值修改多条记录的值 【例例7】将修改了课程编号为将修改了课程编号为2003003且期末成绩小于且期末成绩小于90 分的学生的期末成绩统一加分的学生的期末成绩统一加10分。分。 UPDATE Score SET EndScore=EndScore+10 WHERE Cno=2003003 AND

55、EndScore90 【任务实施任务实施】 6级联修改级联修改 【练习练习6】使用使用UPDATE语句将学号语句将学号200931010100101 修改为修改为201031010100150。 设置好级联修改后，编写执行以下设置好级联修改后，编写执行以下UPDATE语句。语句。 UPDATE Student SET Sno=201031010100150 WHERE Sno=200931010100101 【任务实施任务实施】 【练习练习7】将课程编号将课程编号2003003修改为修改为2003180。 【思考题思考题】将课程将课程数据库技术与应用数据库技术与应用1的所有课程期末成的所有课程

56、期末成 绩置为绩置为0分。分。 【任务实施任务实施】 7删除一条或多条记录删除一条或多条记录 【例例8】删除删除Sno为为200931010100322的学生选修课程的学生选修课程 编号为编号为0901025的课程的选课记录。的课程的选课记录。 DELETE FROM Score WHERE Sno=200931010100322 AND Cno=0901025 【练习练习8】从课程表中删除课程名称为从课程表中删除课程名称为思政概论思政概论的记录。的记录。 【任务实施任务实施】 8级联删除级联删除 【练习练习9】因学号为因学号为200931010100102的学生退学，在的学生退学，在 数据库

57、中删除该学生的所有相关记录。数据库中删除该学生的所有相关记录。 设置好级联删除后，编写执行以下设置好级联删除后，编写执行以下DELETE语句。语句。 DELETE FROM Student WHERE Sno=200931010100102 【任务实施任务实施】 【练习练习10】从课程表中删除课程名称为从课程表中删除课程名称为数据库技术与应数据库技术与应 用用2的记录。的记录。 【思考题思考题】删除课程删除课程数据库技术与应用数据库技术与应用1的所有选课记录。的所有选课记录。 【任务总结任务总结】 数据更新包括插入记录、修改记录和删除记录。插入记录数据更新包括插入记录、修改记录和删除记录。插入

58、记录 的的SQL语句为语句为INSERT语句，修改记录的语句为语句，修改记录的语句为UPDATE 语句，删除记录的语句为语句，删除记录的语句为DELETE语句。语句。 在进行数据更新时要保证数据库中数据的一致性，可使用在进行数据更新时要保证数据库中数据的一致性，可使用 级联修改和级联删除。级联修改和级联删除。 【拓展知识拓展知识】 1带子查询的更新带子查询的更新 【例例1】将课程将课程数据库技术与应用数据库技术与应用1的所有课程期末成绩的所有课程期末成绩 置为置为0分。分。 UPDATE Score SET EndScore=0 WHERE Cno=(SELECT Cno FROM Cours

59、e WHERE Cname=数据库技术与应用数据库技术与应用) 【例例2】将课程将课程数据库技术与应用数据库技术与应用1的所有课程期末成绩的所有课程期末成绩 置为空（置为空（NULL）。）。 【拓展知识拓展知识】 2带子查询的删除带子查询的删除 【例例3】删除课程删除课程数据库技术与应用数据库技术与应用1的所有选课记录。的所有选课记录。 DELETE FROM Score WHERE Cno=(SELECT Cno FROM Course WHERE Cname=数据库技术与应用数据库技术与应用) 【拓展练习拓展练习】 1往宿舍表往宿舍表Dorm中添加你所在宿舍的信息。中添加你所在宿舍的信息。 2往入住表往入住表Live中添加你入住宿舍的信息。中添加你入住宿舍的信息。 3统计各个宿舍的现入住人数，将统计结果存放到临时统计各个宿舍的现入住人数，将统计结果存放到临时 表中，表名为表中，表名为#LiveNum。 4从从Dorm、Live、Student、Class表中查询表中查询计算机应计算机应 用技术用技术专业所有学生的入住信息，结果包含学号专业所有学生的入住信息，结果包含学号Sno、姓、姓 名名Sname、班级名称、班级名称ClassName、宿舍编号、宿舍编号DormNo、 楼栋楼栋Build、房间号、房间号RoomNo、入住日期、入住日期InDate，并将查，并