工作任务之： 1、数据表的创建及配置管理 2、数据表数据的录入管理 3、数据查询管理 4、数据更新管理

Presentation on theme: "工作任务之： 1、数据表的创建及配置管理 2、数据表数据的录入管理 3、数据查询管理 4、数据更新管理"— Presentation transcript:

1 工作任务之： 1、数据表的创建及配置管理 2、数据表数据的录入管理 3、数据查询管理 4、数据更新管理
教学情境3 “网上考试系统”数据管理 工作任务之： 1、数据表的创建及配置管理 2、数据表数据的录入管理 3、数据查询管理 4、数据更新管理

2 教学目标 通过本课程的学习，要求达到如下的教学目标： 掌握SQL Server 2000的常用数据类型 熟练掌握表结构的创建、数据录入
熟练掌握表数据的查询管理及设计 掌握表数据的更新管理及设计 九江职业技术学院计算机教研室

3 工作任务1 数据表的创建及配置管理 九江职业技术学院计算机教研室

4 任务背景描述 在前面的课题中我们已经完成了“exam”数据库的创建与配置，但是到目前为止，在该数据库中并没有真正存储数据。为此，在本章我们的主要工作就是要在“exam”数据库中创建并配置好用来存放所有数据的数据表对象。可以说，数据表是数据库中最主要的对象。 为了保证数据表中数据的完整性，在创建数据表时应该添加适当的约束。在SQL SERVER中有五种约束，分别是：主键约束、唯一约束、检查约束、默认约束和外键约束。 在数据管理过程中可能会发现数据表的结构不太科学的情况，进而需要对数据表结构进行修改。 九江职业技术学院计算机教研室

5 预备知识 表：是数据库中非常重要的对象，它用于存储用户的数据。在关系数据库中每一个关系都体现为一张表，表是用来存储数据和操作数据的逻辑结构，关系数据库中所有数据都表现为表格的形式。 行：是组织数据的单位，每一行都是一条独立的数据记录。 列：是用于描述数据的属性，每一列表示记录中的一个元素。 创建表：就是定义表所包含的列的结构，其中包括列的名称、数据类型、约束等。 九江职业技术学院计算机教研室

6 1-1 数据表的创建与配置管理 在exam数据库设计过程中，经过概念设计、逻辑设计、物理设计后，进入到数据库的实施阶段，就是要将数据库的关系模式集合用RDBMS的创建表格命令进行实现。根据我校在无纸化考试考务管理的需要，设计出以下七个数据表： Admin(userid,username,pwd) Test_user(test_no,test_name,test_class,test_sex,test_dept) Exam_database(question_id,subject_id,type,question,test1, test2, test3, test4,answer,mark) Exam_subject(subject_id,subject_name,credit,teacher_id) Exam_test(subject_id, subject_name,totalper,singlecount,blankcount,judgecount,testtime,starttime,endtime,settime) Exam_score(test_no,subject_id,score) Teacher(Teacher_id,Teacher_name,Sex,T_bank,T_field,Dept) 九江职业技术学院计算机教研室

7 以下是exam数据库中各数据表的中文含义
管理员表（管理员编号、姓名、密码） 考生信息表（考生学号、姓名、班级、性别、所在系） 试题库表（试题编号、科目编号、题型、题目、选项1、选项2、选项3、选项4、答案、标识 ） 考试科目表（科目编号、科目名称、学分、教师编号） 考试设置表（科目编号、科目名称、总分、选择题分值、填空题分值、判断题分值、考试时间、开始时间、结束时间、设置时间） 考试成绩表（考生学号、科目编号、分数 ） 教师表（教师编号、姓名、性别、职称、专业、所在系） 九江职业技术学院计算机教研室

8 以上exam数据库中数据表之间关系如下图所示
管理员表 试题库表 考试设置表 考试科目表 考生信息表 考试成绩表 九江职业技术学院计算机教研室

9 1-1 实现方法—企业管理器 任务1：请根据之前规划的数据表格，在“exam”数据库中设计并定义相应的六个数据表结构（各字段的名称、类型等），试着录入一些数据。在输入数据的过程中可能会出现“输入的值与数据类型不一致，或者此值与列的长度不一致”的错误提示，注意避免。 [注意]： 在表中输入列名必须惟一，并且选择正确的数据类型、数据长度和精度。 常用的数据类型有：int,float,char,varchar,datatime等。 在定义数据表的结构时，各列的命名应符合ANSI的命名规则，这将有利于日后的编程控制。 九江职业技术学院计算机教研室

10 1-1 实现方法—企业管理器 打开视频 任务2：在刚刚设计定义完成的“exam”数据库的六个数据表中，试着录入一些数据，我们发现了很多“不正常”的现象，虽然这些数据不应该出现在我们的数据表中，但事实上在数据表中却存在了这些“不正常”的数据。为了防止这种“不正常”的数据出现在数据库里，SQL Server提供了约束管理机制，请在任务1中定义的数据库表上进行修改，添加必要的约束来规范用户的数据录入过程。 [分析]： 约束是实现数据完整性的有效手段。 SQL Server可以实现的约束有： 九江职业技术学院计算机教研室

11 1-1 实现方法—企业管理器 非空（即该列中不允许出现空值）
主键约束（规定该列中的值不可以为空，也不可以重复，每个数据表有且只有一个主键约束） 唯一约束（规定该列中的值不可以重复，但可以为空，常常用来作为主键约束功能的一种扩充） 默认约束（设定某列中在缺省状态下的取值，这样的列在录入时空缺，将以默认值自动插入） 检查约束（用于规定某列中值的取值范围，这样的规定可以有效避免在数据表中出现没有意义的数据） 外键约束（用于说明多个数据表间的制约关系，这种约束相对比较抽象，但却非常有意义）。 九江职业技术学院计算机教研室

12 Admin表中的相关约束 主键约束 标识种子 九江职业技术学院计算机教研室

13 Exam_subject表中的相关约束 主键约束 标识种子 九江职业技术学院计算机教研室

14 Exam_database表中的相关约束
主键约束 参照exam_subject设置外键约束 标识种子 九江职业技术学院计算机教研室

15 参照exam_subject设置外键约束
Exam_test表中的相关约束 主键约束 参照exam_subject设置外键约束 标识种子 九江职业技术学院计算机教研室

16 Test_user表中的相关约束 主键约束 默认约束 默认值 九江职业技术学院计算机教研室

17 参照exam_subject设置外键约束
exam_score表中的相关约束 两个列组合的主键约束 参照exam_subject设置外键约束 参照test_user设置外键约束 九江职业技术学院计算机教研室

18 1-1 实现方法—企业管理器 任务3：修改数据表test_user的结构，向test_user表中添加一列Phone（char，15）来记录学生的电话号码信息。 [注意]： 在向已经输入记录的数据表中添加新列时，必须允许该新列为“空”，否则出错，为什么？ 任务4：删除“exam”数据库中的所有用户数据表。 删除表格的权力只属于表格拥有者，并且不能转移。 当数据表之间存在外键约束时，需要注意删除顺序，应该首先删除外键表，然后再删除主键表；或者先解除外键。 删除表格时，表的结构、数据、约束、索引等将一并全部删除。 九江职业技术学院计算机教研室

19 1-2 实现方法—查询分析器 任务5：在查询分析器中，用T-SQL语句完成任务1，命令如下： CREATE TABLE admin (
userid smallint IDENTITY (1, 1) NOT NULL , username char (10), pwd char (10)) ON PRIMARY CREATE TABLE exam_database ( question_id int IDENTITY (1, 1) NOT NULL , subject_id char(6), type char (10), question varchar (700), text1 varchar (400), text2 varchar (400), text3 varchar (400), text4 varchar (400), answer char (10), mark bit) ON PRIMARY 九江职业技术学院计算机教研室

20 CREATE TABLE exam_test ( subject_id char(6) , totalper int ,
singlecount int, blankcount int, judgecount int, singleper int, blankper int, judgeper int, testtime int, starttime datetime, endtime datetime, settime datetime ) ON PRIMARY 九江职业技术学院计算机教研室

21 CREATE TABLE exam_score ( test_no char (12), subject_id char(6),
score int) ON PRIMARY CREATE TABLE exam_subject ( subject_id char(6) NOT NULL , subject_name varchar (50), credit smallint, teacher_id char(8)) ON PRIMARY 九江职业技术学院计算机教研室

22 CREATE TABLE test_user ( test_no char (12) NOT NULL ,
test_name char (10) , test_class char (20) , test_sex char (2) , test_dept char (20)) ON PRIMARY CREATE TABLE teacher ( teacher_id char (8) NOT NULL , teacher_name char (10) , sex char (2) , t_bank char (12) , t_field char (20), dept char(12)) ON PRIMARY 以上七个create table命令分别完成了exam数据库中七个表的创建工作。 九江职业技术学院计算机教研室

23 1-2 实现方法—查询分析器 [提示]：Creat table命令格式 cteate table 表名
（<列定义> <列级完整性约束> [, …..n] [，表级完整性约束] [ on 文件组名 | default ] ） [注意]： 完整性约束是防止数据库用户在进行数据操作，特别是录入操作时出现不合语义的数据的有效手段，具有制约和依赖规则。 在创建表时可同时定义的约束有：[not] null,primary,unique,default,check及foreign key等，[not] null和default只能为列级，其余均可为列级或表级。 九江职业技术学院计算机教研室

24 1-2 实现方法—特例 [提示]：在进行数据表结构定义时，有些比较特殊的情况值得注意 例1：Create table s
(sno char(6) not null ,sname char(8) , ssex bit default 1, birth smalldatetime , address varchar(50) , sage as year( getdate())-year( birth ) ) [分析]： 某列设置默认值后就会自动获取列值； 可以由AS引导计算列表达式来定义新的数据列。 九江职业技术学院计算机教研室

25 ( col_1 int identity ( 1,2 ) , *标识种子值为1，递增量为2 col_2 int )
Create table test1 ( col_1 int identity ( 1,2 ) , *标识种子值为1，递增量为2 col_2 int ) [分析]：只能指定数据类型为tinyint , smallint , int , bigint , decimal (p,0) 或者numeric (p,0) 的列为标识列，指定为标识列后就不能再允许为（null）空属性，系统将自动指定为not null。 例3、指定uniqueidentity数据类型列 Create table test2 ( col_1 uniqueidentifier default newid() not null , [分析]： uniqueidentity数据类型为惟一标识符类型，是以一个32位的十六进制数据表示的全局惟一标识符（GUID），通常和函数newid() 一起使用，由它生成GUID作为列的默认值。 九江职业技术学院计算机教研室

26 1-2 实现方法—查询分析器 任务6：在上面的任务1中我们只是用T-SQL命令完成了基本表结构（列名、类型、宽度、是否为空）的定义，但是这样的基本定义不能有效避免“非正常数据”进入数据表，因此，应该修改数据表定义，添加有关的约束。 ALTER TABLE admin ADD CONSTRAINT PK_admin PRIMARY KEY ( userid ) 在此，为admin数据表添加了主键约束，这个操作通过企业管理器更加直观。 九江职业技术学院计算机教研室

27 1-2 实现方法—查询分析器 ALTER TABLE exam_test ADD
CONSTRAINT FK_exam_test_exam_subject FOREIGN KEY ( subject_id ) REFERENCES exam_subject ( ) [注意]： 在此，为exam_test和exam_subject两个表之间添加了外键约束，实际上为数据表添加相关约束，多通过企业管理器实现。 九江职业技术学院计算机教研室

28 这里的数据表删除有一定的顺序关系，为什么呢？
1-2 实现方法—查询分析器 任务7：用T-SQL命令完成前面的任务3T_SQL语句如下 Alter table test_user add phone char(15) 任务8：用T-SQL命令完成前面的任务4T_SQL语句如下 Drop table admin Drop table exam_score Drop table test_user Drop table exam_test Drop table exam_database Drop table exam_subject 这里的数据表删除有一定的顺序关系，为什么呢？ 九江职业技术学院计算机教研室

29 alter table add <列定义> <列约束> [,……n ]
| [with check | with nocheck ] add <列约束> [,……n ] | drop {column 列名 | [constraint ] 约束名 } [,……n] | alter column 列名 <新列定义> | [check | nocheck ] constraint {all | 约束名 [,….n] } 九江职业技术学院计算机教研室

30 3、 alter table命令一次只能修改一个字段的属性，如果要修改多个字段的属性，必须执行多个alter table命令，形成批处理。
[注意]： 1、使用alter table命令可以修改表的结构，增加或删除列，也能修改列的属性，还能增加、删除、启用和暂停约束。但是修改表时不能破坏表原有的数据完整性，如不能向已有主键的表再添加主键列等。 2、修改表结构不仅会影响要修改属性中的数据，访问该属性的相关程序代码也必须做相应地修改，而且用户对表的操作也要相应地变动。所以表的结构在系统运行初期应尽量确定下来，系统运行一段时间以后，表的结构就不宜再变动了。 3、 alter table命令一次只能修改一个字段的属性，如果要修改多个字段的属性，必须执行多个alter table命令，形成批处理。 九江职业技术学院计算机教研室

31 1-2 实现方法—查询分析器 [提示]：Drop table命令格式 drop table 表名 [，……n]
[注意]：如果要删除的表是其他表的参照表，删除将无法进行，这时需先取消其他表中的外键约束；或者先删除其他表，然后再删除该表。删除表时会将与该表有关的所有对象一起删除掉,包括表中的数据。 九江职业技术学院计算机教研室

32 小 结 创建数据表时，能够一次性定义好所有需要的数据当然是最有效的方式，不过实际工作中，常常需要对已经定义的表进行必要的修改，如添加约束、索引、规划及增加列等。 在创建数据表及相关对象前，应尽量确定以下几个方面的内容： 表中每个列的名称，数据类型及其长度； 需要设为空值的列； 主键列； 需要使用的约束。 九江职业技术学院计算机教研室

33 工作任务2 数据表数据的录入管理 九江职业技术学院计算机教研室

34 教学目标 通过本课程的学习，要求达到如下的教学目标： 掌握常规的数据录入方法 检测在数据录入过程中的常见问题 简单了解数据的导入方法
九江职业技术学院计算机教研室

35 任务背景描述 在网上考试系统的后台数据库“Exam”中，我们已经定义并创建了有关的6个数据表，现在这些数据表仅仅只有表结构而没有任何数据，也就是说我们在前一个工作任务中只创建了6个空的数据表。因此，现在面临的问题就是要把所有的数据填入数据库表中去，形成一个真正的“网上考试系统”数据世界。 九江职业技术学院计算机教研室

36 2-1 数据录入 [任务1]：向网上考试系统后台Exam数据库的“Admin”、“Exam_subject”、“Exam_test”等数据表进行数据录入。 注意： 在数据录入过程中很可能出现一些错误提示，如“所输入数据类型或长度与定义不一致”等； 所有录入的数据必须遵守在数据库定义阶段所设计的各种约束条件，否则数据录入会失败； 九江职业技术学院计算机教研室

37 2-2 数据导入 [任务2]：向网上考试系统后台的“Exam”数据库的“Test_user”、“Exam_database” 等数据表进行数据导入。 [分析]： 因为在“Exam”数据库中这两个数据表中的数据量特别巨大，而且在学校的教务管理系统中已经保存了所有“考生”的基本数据，在电脑中保存了大量的“题库”信息，所以这两个表中的数据可以在作适当处理后，直接导入，避免数据的大量重复性输入。 使用SQL Server的数据导入功能，可以方便地进行各种类型的数据文件导入导出转换服务。 九江职业技术学院计算机教研室

38 小 结 数据库是大量数据的集散地，数据按一定的结构存储在数据库中，所以科学高效地进行录入，是在数据库实施过程中必不可少的步骤，主要通过两种途径来完成数据的录入： 数据录入 数据导入 九江职业技术学院计算机教研室

39 工作任务3 数据查询管理 九江职业技术学院计算机教研室

40 教学目标 通过本课程的学习，要求达到如下的教学目标： 熟练掌握单表查询设计； 熟练掌握统计查询设计； 熟练掌握连接查询设计；
熟练掌握嵌套查询设计； 了解查询优化技术； 九江职业技术学院计算机教研室

41 任务背景描述 在“exam”数据库中我们已经将需要存储、管理的数据装入了系统，也就是说“图书”已经全部入库，接下来摆在我们面前的任务更多的是如何才能更好地为读者服务，即怎样才能快速准确地查询到用户所需要的数据。为此，我们将面临数据管理过程中的一个最为重要的数据操纵问题，而其中数据查询更是重中之重。 本课题所有任务都基于“exam”的七个数据表中的数据进行 九江职业技术学院计算机教研室

42 预备知识 1、SQL语言极强，设计巧妙，语言十分简捷，实现数据管理的核心只有10个命令动词： 数据定义：create,drop,alter
数据查询：select 数据更新：insert,update,delete 数据控制：grant,deny,revoke 2、数据查询是数据库系统应用的主要内容，保存数据就是为了使用，要使用首先要查找到需要的数据，查询就是根据用户提供的限定条件，从可用的数据表或视图中返回用户需要的数据集合。Select语句就是用于实现数据查询，可以完成简单的单表查询，也可以完成复杂的多表查询。 九江职业技术学院计算机教研室

43 3-1 SQL简单查询 任务1：选择表中若干列 查询考生表中的所有数据项 查询考生表中所有学生的学号、姓名及所在系
查询考试设置表中各科目的考试总天数和剩余天数 将上题中的查询重新设计，并将查询结果各列分别命名为中文的“科目编号、考试总天数、剩余天数” 查询考生表中的考生来自哪些系 [注意]：以上五个查询任务涉及到 用*表示查询某个表的所有列； 可以在表中进行计算列的查询； 可以对查询结果进行命名，以使查询结果更加可读； 使用distinct可以找出一个列中的所有值，并对每个值只显示一次。 九江职业技术学院计算机教研室

44 Select * from test_user
任务1的查询命令如下： Select * from test_user Select test_no,test_name,test_dept from test_user select subject_id,datediff(day,starttime,endtime),datediff(day,getdate(),endtime) from exam_test select subject_id as 科目编号,datediff(day,starttime,endtime)as 考试总天数,datediff(day,getdate(),endtime)as 剩余天数 from exam_test Select distinct test_dept from test_user Select命令的最基本格式： Select [all/distinct] 字段列表 from 数据源 九江职业技术学院计算机教研室

45 3-1 SQL简单查询 任务2：使用where子句的简单查询 九江职业技术学院计算机教研室

46 任务2的查询命令 九江职业技术学院计算机教研室

47 3-1 SQL简单查询 [注意]：数据库中可以有很多数据，但在特定的情况下，我们只需要用到满足一定条件的部分记录，这就需要对数据表中的记录进行过滤，where子句就是用来过滤记录的。 where子句常用的查询条件及运算符有 比较：=,>,<,>=,<=,<> 确定范围：between and,not between and 模式匹配：like,not like（注意几个通配符%,_，[]，[^]） 确定集合：in,not in 空值：is null,is not null 多重条件：and,or 九江职业技术学院计算机教研室

48 3-1 SQL简单查询 任务3：使用order by子句对查询结果进行排序 查询所有考生的学号、科目代号及分数，并按课程代号升序显示
查询所有考生的学号、科目代号及分数，并按课程代号升序、分数降序显示 任务3的查询命令 1、Select * from exam_score order by test_no 或者：Select * from exam_score order by 2 2、Select * from exam_score order by test_no,score desc [注意]： order by子句用于指定排序依据，可以是字段名也可以是字段编号，asc和desc分别指定排序方向为升序和降序，默认为升序排列，NULL值视为最小值。 九江职业技术学院计算机教研室

49 3-2 统计查询 任务4：完成以下统计查询任务 查询test_user表中各系女生的总人数，并按女生人数的降序排列查询结果
3-2 统计查询 任务4：完成以下统计查询任务 查询test_user表中各系女生的总人数，并按女生人数的降序排列查询结果 查询exam_score表中每个科目的考生人数，并按人数的降序排列查询结果 查询exam_score表中各个科目中的考试最高分、最低分及平均分 查询exam_score表中各科目的考生人数、总成绩及平均成绩 查询test_user表中，参加考试人数超过50人的所在系及考生人数 九江职业技术学院计算机教研室

50 任务4的查询命令 Select test_dept,count(*) from test_user where test_sex=‘女’ group by test_dept order by 2 desc Select test_no,count(*) from exam_score group by test_no order by 2 desc Select test_no,max(score),min(score),avg(score) from exam_score group by test_no Select test_no as 科目代码,count(*) as 考生人数,sum(score) as 总成绩,avg(score) as 平均成绩 from exam_score group by test_no select test_dept,count(*) from test_user group by test_dept having count(*)>=50 九江职业技术学院计算机教研室

51 3-2 统计查询 [说明]： 统计查询中广泛应用集合函数 Sum()：用于统计数值型字段的和； Avg()：用于统计数值型字段的平均值；
3-2 统计查询 [说明]： 统计查询中广泛应用集合函数 Sum()：用于统计数值型字段的和； Avg()：用于统计数值型字段的平均值； Max()：用于统计字段中的最大值，NULL被忽略； Min()：用于统计字段中的最小值，NULL被忽略； Count()：用于统计查询输出的记录行数，或者字段中选取的项目数，学用于统计人数等 分组统计查询是一种很常用的统计查询手段，所以集合函数往往与group by 子句一起使用；如果查询结果还要根据统计结果进行筛选，则还要使用having子句。 九江职业技术学院计算机教研室

52 3-2 统计查询 完整的select语句格式如下，且各子句相对顺序不能颠倒 Select 字段列表 [into 新表名]
3-2 统计查询 完整的select语句格式如下，且各子句相对顺序不能颠倒 Select 字段列表 [into 新表名] From 查询数据源 [Where 条件表达式] [Group by 分组依据] [Having 筛选条件] [Order by 排序表达式 [asc/desc]] 九江职业技术学院计算机教研室

53 3-3 多表连接查询 查询“电气工程系”学生的考试情况，并列出学生的姓名、所考试科目名称及成绩等
3-3 多表连接查询 任务5：通过连接查询完成以下数据查询任务 查询“电气工程系”学生的考试情况，并列出学生的姓名、所考试科目名称及成绩等 查询所有参加了“C语言”科目考试的学生信息，并列出学生的学号、姓名及所在系等 查询同时参加了“C语言”与“计算机文化”两科目考试的学生信息，并列出学生的学号 查询同时参加了“C语言”与“计算机文化”两科目考试的学生信息，并列出学生的学号、姓名及所在系等 九江职业技术学院计算机教研室

54 任务5的T-SQL命令： 1、Select test_name,subject_name,score from test_user t1,exam_subject t2,exam_score t3 where t1.test_no=t3.test_no and t2.subject_id=t3.subject_id and test_dept='电气工程系' 或者：select test_name,subject_name,score from test_user t1 inner join exam_score t2 on t1.test_no=t2.test_no inner join exam_subject t3 on t3.subject_id=t2.subject_id where test_dept='电气工程系' 2、Select t1.test_no,test_name,test_dept from test_user t1 join exam_score t2 on t1.test_no=t2.test_no join exam_subject t3 on t3.subject_id=t2.subject_id where subject_name=‘C语言’ 3、Select t11.test_no from exam_score t1 join exam_score t2 on t1.test_no=t2.test_no where t1.subject_name=‘C语言’ and t2.subject_name=‘计算机文化’ 4、Select test_no,test_name,test_dept from test_user where test_no in (Select sc1.sno from exam_score t1 join exam_score t2 on t1.test_no=t2.test_no where t1.subject_name='C语言' and t2.subject_name='计算机文化') 九江职业技术学院计算机教研室

55 1、内连接：是一种最常见的连接类型，使用内连接时，如果两个表的相关字段满足连接条件，则从这两个表中提取数据合并组成新的记录。
[说明]：关于多表连接查询的几点说明 1、内连接：是一种最常见的连接类型，使用内连接时，如果两个表的相关字段满足连接条件，则从这两个表中提取数据合并组成新的记录。 内连接格式为： from <表1> [inner] join <表2> on <连接条件> 2、自身连接：是一种特殊的连接，它是指相互连接的表在物理上为同一个表，但可以逻辑上看作两个表。使用自身连接时必须为两个表分别取不同的别名，使它们在逻辑上成为两个表。 3、外连接：from 表1 left/right [outer] join 表2 on 连接条件 4、交叉连接：相当于对被连接的两个数据表进行笛卡积。 九江职业技术学院计算机教研室

56 3-4 嵌套查询 任务6：用嵌套查询实现以下的查询任务 查询参加了“40101”科目考试的学生的学号和姓名
3-4 嵌套查询 任务6：用嵌套查询实现以下的查询任务 查询参加了“40101”科目考试的学生的学号和姓名 查询参加了“C语言”课程考试的学生的学号和姓名 查询没有参加“C语言”课程考试的学生的学号、姓名及所在系信息 查询参加了“C语言”课程考试的学生总人数、平均成绩及总成绩 九江职业技术学院计算机教研室

57 任务6的T-SQL命令： 1、Select test_no,test_name from test_user where test_no in (select test_no from exam_score where subject_id=‘40101’) 或者：Select test_no,test_name from test_user where exists (select * from exam_score where test_no= test_user.test_no and subject_id= ‘40101’) 2、Select test_no,test_name from test_user where test_no in (select test_no from exam_score where subject_id in (select subject_id from exam_subject where subject_name=‘C语言’)) 3、Select test_no,test_name ,test_dept from test_user where test_no not in (select test_no from exam_score where subject_id=(select subject_id from exam_subject where subject_name=‘C语言’)) 4、Select count(*),avg(score),sum(score) from exam_score where subject_id=(select subject_id from exam_subject where subject_name=‘C语言’) 九江职业技术学院计算机教研室

58 3-4 嵌套查询 [说明]：嵌套查询的几点说明 嵌套查询：也称为子查询，是在一个select语句中嵌套一个select、insert、update、delete语句的情况。子查询的嵌套层次最多可达255层，以层层嵌套的方式构造查询，充分体现了SQL“结构化”的特点。 使用IN或NOT IN运算符进行基于集合的测试。 使用EXIST 或NOT EXIST运算符进行存在性测试。 通过比较运算符（>、<、>=、<=、=、!=）进行比较测试 九江职业技术学院计算机教研室

59 3-5 其它几种查询 任务7：通过相应的查询完成以下任务
3-5 其它几种查询 任务7：通过相应的查询完成以下任务 筛选出“电气工程系”学生的学号、姓名、考试科目名、分数及所在系信息，并将查询结果存入新表“exam_score34”。 筛选出“电子工程系”学生的学号、姓名、考试科目名、分数及所在系信息，并将查询结果与“电气工程系”进行合并显示。 九江职业技术学院计算机教研室

60 3-5 其它几种查询 任务7的select命令： Select t1.test_no,test_name,subject_name,score,test_dept into exam_score34 from test_user t1,exam_subject t2,exam_score t3 where t1.test_no=t3.test_no and t2.subject_id=t3.subject_id and dept='电气工程系' Select * from exam_score34 union Select t1.test_no,test_name,subject_name,score,test_dept from test_user t1,exam_subject t2,exam_score t3 where t1.test_no=t3.test_no and t2.subject_id=t3.subject_id and dept='电子工程系' 九江职业技术学院计算机教研室

61 3-5 其它几种查询 [说明]： Select……into子句可以将查询结果存储到一个新建表或临时表中。但使用时要注意以下几点：新表不能存在；新表中的列和行是基于查询结果集的；使用该子句时用户必须具有数据库的creat table权限；如果新表名称前有“#”则生成临时表，仅存于内存，并不真正存储到数据库中。 Union运算符用于将多个检索结果合并成一个结果，但使用时也必须注意以下几点： 所有查询中的列数和列顺序必须相同； 所有查询中按顺序对应列的数据类型必须兼容。 九江职业技术学院计算机教研室

62 工作任务4 数据更新管理 九江职业技术学院计算机教研室

63 教学目标 通过本课程的学习，要求达到如下的教学目标： 熟练掌握数据插入操作； 熟练掌握数据删除操作； 熟练掌握数据更新操作；
了解数据在更新过程中的约束冲突问题。 九江职业技术学院计算机教研室

64 任务背景描述 “网上考试系统”的后台数据库EXAM中已经存放了大量的各类数据，我们可以通过相应的查询命令完成一系列的数据查询操作。但是，在现实的考试管理过程中，难免会出现因为教学内容的更新，而需要插入新的试题、更改有问题的试题、删除过时的试题等。这样，在数据库的管理过程中，我们必须面对数据更新问题。 九江职业技术学院计算机教研室

65 预备知识 SQL 语言提供的数据更新命令主要包括插入数据、修改数据和删除数据三种语句： 插入命令：用于向数据表插入一行数据记录或一个数据集合
修改命令：用于根据条件修改数据表中已经存在的相应列内容 删除命令：用于从数据表中删除一行或多行数据 九江职业技术学院计算机教研室

66 4-1 SQL数据更新 任务1：利用SQL数据更新命令完成以下任务 在“exam”数据库中新增两个考试科目：英语、心理学
在新生入学后，将所有新生的学生信息数据(newstudents表)插入到“exam”数据库的test_user表中 在“exam”中有一个从外校新转入的男生’刘海’，请将他已经考过科目的成绩信息添加到exam_score表中，其中，该转入学生的信息为： 、刘海、男、数控0608、机械工程系，已考科目信息为：40101、87，10101、75。 九江职业技术学院计算机教研室

67 4-1 SQL数据更新 在“exam”数据库中有一个学号为“200402010101”的学生退学，请删除该学生的全部个人信息
九江职业技术学院计算机教研室

68 4-1 SQL数据更新 任务1的SQL命令： 1. Insert into exam_subject values(‘70101’,‘英语’,4) Insert into exam_subject values(‘80101’,‘心理学’,2) 2. Insert into test_user (select * from newstudents) 3. Insert into test_user (test_no,test_name,test_sex,test_class,test_dept) values(‘ ’,’刘海’,’男’,’数控0608’,’机械工程系’) insert into exam_score values(‘ ’,’40101’,87) insert into exam_score values(‘ ’,’10101’,75) 九江职业技术学院计算机教研室

69 4-1 SQL数据更新 4. Delete from exam_score where sno=‘200402010101’
Delete from test_user where sno= ‘ ’ 5. Update test_user set sno=‘ ’ where sno=‘ ’ Update exam_score set sno=‘ ’ where sno=‘ ’ 九江职业技术学院计算机教研室

70 小 结 1、插入命令：用于向数据表插入一行数据记录或一个数据集合。相应的语法格式如下
小 结 1、插入命令：用于向数据表插入一行数据记录或一个数据集合。相应的语法格式如下 Insert [into] <表名> [( 字段列表 )] values (<相应值列表>) Insert [into] <表名> （select 子句） 2、修改命令：用于根据条件修改数据表中已经存在的相应列内容。 Update <表名> set <列名>=<表达式> [,<列名>=<表达式> …] where <条件> 3、删除命令：用于从数据表中删除一行或多行数据。 Delete [ from ] <表名> [ where <删除条件>] 九江职业技术学院计算机教研室

71 小 结 4、不可恢复的删除数据命令 truncate table 表名
小 结 4、不可恢复的删除数据命令 truncate table 表名 5、在对数据库进行数据更新操作时，要特别注意到具有外键关系的表之间的约束力，比如：  在向外键表插入数据时，需要先将主键表中相关的数据插入后再执行；  当数据表之间具有外键关系，且设置了数据更新的级联操作，在数据更新时管理更加方便，具有所谓的一改全改功能； 九江职业技术学院计算机教研室

72 小 结 6、Truncate table命令及delete命令都只是删除表中的数据，表的结构是不受影响的，删除全部数据后是一个空表；
小 结 6、Truncate table命令及delete命令都只是删除表中的数据，表的结构是不受影响的，删除全部数据后是一个空表； 7、为了保证数据的安全性，数据更新操作需要相应的权限。 九江职业技术学院计算机教研室

73 总 结 数据查询命令select：查询功能是SQL语言的核心功能，是数据库中使用得最多的操作，select语句也是SQL语句中比较复杂的一个语句。查询的数据源可以是一个表，也可以是多个表甚至视图，查询的结果是0到N行记录组成的记录集合。select语句还可以对查询的结果进行汇总、排序等。 数据更新命令insert,update,delete 在大型数据库中，为了保证数据的安全性，只有数据库及对象的所有者或被授权的用户才能对数据库进行更新操作。 九江职业技术学院计算机教研室