数据库系统概论6数据更新资料

1、授课主题第6讲计划学时2数据更新教学目的和要求1、带有exists的子查询和集合查询2、数据更新的三条命令：insert、update、delete教学重点和难点理解带exists的子查询教学内容1、带有EXISTS谓词的子查询2、集合查询3、数据更新教学过程见课件共二十三页例32：查询所有选修了”k01”号课程(kchng)的学生姓名。方法一：select 姓名(xngmng) from student where exists ( select * from sc where 学号= student.学号 and 课程代号=k01)四、带有EXISTS谓词的子查询(难) exists代表存

2、在量词。带有exists谓词的子查询不返回任何实际数据，只产生逻辑真值”true”或逻辑假值”false”。若内层查询结果非空，则外层的where子句返回真值；若内层查询结果为空，则外层的where子句返回假值。sc.学号= student.学号 共二十三页执行过程：首先用student中的第一条记录中的学号值“200215121”和sc表中的每一个元组进行比较，若有满足条件(学号=student.学号 and 课程代号=k01)的元组则返回true，那么将此元组放入结果集中。然后再把student表中的第二个元组”200215122”与sc表中每个元组进行比较，直到student表中的每一个

3、元组的学号，均与sc表中每一个元组比较后，最终返回所有为true的结果。说明：在这例中，子查询(chxn)的查询(chxn)条件依赖于外层父查询(chxn)的某个属性值，因此也被称为相关子查询。其求解过程是：先取外层查询中的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回值为真，则取此元组放入结果表；然后检查下一个元组，重复这一过程，直至外层查询的元组全部检索过。由于exists只返回逻辑值，所以在exists后的子查询一般使用”*”来代替列名，因为即使指定列名也无意义。共二十三页in和exists查询的区别：结果相同(xin tn)，但操作过程不同当是in时：先扫描

4、一遍sc表，得出(d ch)子查询的结果集合(200215121，200215122)，然后从结果中找出与student表中学号相同的记录。方法二：用in查询select 姓名 from student where 学号 in (select 学号 from sc where 课程代号=k01)可以用exists等价替换带in、比较运算符、any和all的子查询，但不能用其他形式的子查询等价替换exists子查询。由于带exists谓词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于不相关子查询，甚至有时是最高效的办法。not exists：若内层查询结果为空，

5、则外层where子句返回真值，否则返回假值。例：没有选修2号课程的学生姓名。共二十三页例33：查询选修了全部课程的学生姓名。方法(fngf)一的思路：由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的姓名，没有一门课是他不选的。select 学号,姓名 from student where not exists (select * from course where not exists (select * from sc where 学号=student.学号 and 课程代号=course.课程代号)查询出所有已经选择过课程的学生及相应课程 所有没有被选择的课程 选取

6、所有没有未选择课程的学生，即选择了所有课程的学员名称。 SQL语言中没有全称量词(For all)，因此(ync)必须利用谓词将一个带有全称量词的谓词转换为等价的存在量词的谓词。共二十三页b004b003李小白b005刘月b001agesnamesnostudentcoursek02k01cnamecnosck03b004k02b005k01b005k02b003k01b001gcnosnosnosc.课程号=course.课程号 and sc.学号=student.学号第2个not exists第1个not existsb001b001 k01 truefalsetrueb001 k02 f

7、alsetruefalseb005b005 k01 truefalsetrueb005 k02 truefalsetrue共二十三页方法二的思路：若要想知道(zh do)是否选修了全部课程，至少我们需要知道(zh do)一共有几门课程。 select 学号,姓名(xngmng) from student where 学号 in (select 学号 from sc group by 学号 having count(课程代号)= (select count(课程代号) from course)共二十三页3.4.4 集合(jh)查询 每一个select语句都能获得(hud)一个或一组元组。若要把多

8、个select语句的结果合并为一个结果，可用集合操作来完成。集合操作主要包括并操作(union)、交操作(intersect)、差操作(except)。命令格式为：select *from 表名unionintersectexceptselect *from 表名共二十三页例34：查询选修(xunxi)了k01课程或者选修了课程k02的学生的学号。SELECT DISTINCT 学号FROM scWHERE 课程(kchng)代号=k01 or 课程代号=k02说明：UNION：将多个查询结果合并起来时，系统自动去掉重复元组。UNION ALL：将多个查询结果合并起来时，保留重复元组 SELE

9、CT 学号FROM SCWHERE 课程代号=k01UNIONSELECT 学号FROM SCWHERE 课程代号= k02 ；共二十三页例35：查询(chxn)选修课程k01的学生集合与选修课程k02的学生集合的交集。即：即选修了k01课程又选修了k02课程的学生。select 学号into #sc1 from sc where 课程(kchng)代号=k01 select 学号into #sc2 from sc where 课程代号=k02select * from #sc1intersectselect * from #sc2SELECT 学号FROM SCWHERE 课程代号= k01

10、 AND 学号 IN (SELECT 学号 FROM SC WHERE 课程代号= k02 )；SELECT DISTINCT 学号FROM scWHERE 课程代号=k01 and 课程代号=k02共二十三页例36：查询(chxn)选修课程k01的学生集合与选修课程k02的学生集合的差集。即：即选修了k01课程但没选k02课程的学生。drop table #sc1drop table #sc2select 学号into #sc1 from sc where 课程(kchng)代号=k01 select 学号into #sc2 from sc where 课程代号=k02select * fr

11、om #sc1exceptselect * from #sc2 SELECT 学号FROM SCWHERE 课程代号= k01 AND 学号 not IN (SELECT 学号 FROM SC WHERE 课程代号= k02 )；共二十三页3.5 数据(shj)更新3.5.1 插入语句(yj) INSERT3.5.2 更新语句 UPDATE 3.5.3 删除语句 DELETE共二十三页要添加(tin ji)记录的数据表名数据列表中的数据个数和数据类型必须(bx)和字段列表中的字段个数和数据类型一致。无特殊作用，可以省略3.5.1 插入语句 INSERTInsert语句用来向表中插入数据。格式一

12、：插入元组 INSERT INTO table_namecolumn_list VALUES (data_values)要添加数据的字段名列表，各个字段名之间用英文逗号分开。如果没有指定字段，则表示数据表的所有字段。关键字共二十三页例1：给student表中插入(ch r)一条记录： 程程,17,女。例2：给sc表插入一条记录(jl)，学号为2009025001，课程号为1，成绩为98。 INSERT INTO sc(sno,cno,grade) VALUES(2009025001, 1,97)insert into student(sname,sage,ssex) values(程程,17,

13、女)包含所有字段名，可以省略注意：使用insert命令添加记录时，不能为计算字段、indentity字段、默认字段指定数据，因为这些字段的值由SQL Server自动产生。其他字段没有提供数据，其值为null。若不允许为null，则提示错误信息。共二十三页格式(g shi)二：插入子查询结果例3：建立一个新表savg，存放(cnfng)每个学生的学号和平均成绩。利用查询结果插入新表中。Savg表中的数据与下表savg1中的数据完全一样：SELECT grade.学号 as sno,AVG(成绩) as average into savg1FROM grade,STUDENTWHERE grad

14、e.学号 = STUDENT.学号GROUP BY grade.学号CREATE TABLE savg (sno CHAR(8),average REAL)INSERT INTO savg (sno,average) ( SELECT 学号,AVG(成绩) FROM sc GROUP BY 学号)Insert into ( ,)子查询共二十三页3.5.2 更新(gngxn)语句 UPDATE更新操作是指修改指定表中满足条件的一行或多行元组中的一个或多个列值。语句格式： UPDATE 表名 SET 列名=, 列名= WHERE WHERE子句(z j)：给出被修改元组应满足的条件。 SET子句：

15、指定要修改的列和修改后的值。注意：更新操作也是面向集合的共二十三页例1：在course中，将数据库的学分改为4。update course set credit=4 where cname=数据库 例2：将1号课程(kchng)的成绩提高10%。update sc set grade=grade*1.1 where cno=1带有子查询的修改(xigi)语句例3：将选修了数据库课程的所有同学的成绩加10分。update sc set 成绩=成绩+10 where 课程代号= (select 课程代号 from course where 课程名称=数据库)共二十三页3.5.3 删除(shnch)

16、语句DELETEDELETE 语句从表中删除一个或多个满足条件的元组。语句格式： DELETE FROM表名 WHERE 条件表达式说明：WHERE子句(z j)中的条件表达式给出被删除元组应满足的条件；若不写WHERE子句，表示删除表中的所有元组，但表的定义仍存在(即定义的约束条件、默认值、标识种子都存在)。共二十三页例1：只涉及单个元组，从student中删除“程程”的信息DELETE FROM STUDENT WHERE sname=程程例2：删除操作涉及多个(du )元组，删除sc表中所有记录。DELETE FROM sctruncate table sc 从一个表中删除所有行的速度要

17、快于delete。它是一种快速、无日志记录(jl)的方法。共二十三页带子(di zi)查询的删除例3：删除选修了数据库课程的学生成绩delete from sc where 课程代号= (select 课程代号 from course where 课程名称=数据库)？如何删除表中重复的记录？select distinct * into #temp from scdrop table scselect * into sc from #temp 共二十三页说明(shumng)：对某个基本表中数据的增、删、改操作有可能会破坏参照完整性，这会带来数据库的不一致性问题，所以在进行数据更新操作时一定要遵循

18、参照完整性规则。另外：例如学号为2010001的学生因病休学一年，复学后需要将其学号改为2011089，由于student表和sc表中都有关于2010001的信息，因此两个表都需要修改，而这种修改只能通过两条update语句进行。在执行了一条update语句后，student表中的数据修改了，但sc表中的相关记录没有修改，这时数据库中的数据处于不一致的状态，只有两条修改语句都执行了，数据库才会再次处于一致性状态。为了保证这两条语句要么都做，要么都不做，数据库系统引入了事务的概念。共二十三页小结(xioji)1、exists是存在量词，只返回真假值，一般情况下可以替换in运算符。注意(zh y)exists运算符的执行过程。2、了解集合查询命令：union、intersect、except3、insert、update、delete命令的用法共二十三页内容摘要授课主题。例32：查询(chxn)所有选修了”k01”号课程的学生姓名。带