《数据库管理与编程技术》第5章 数据操作

1、数据库管理与编程技术第第5章章 数据操作数据操作 第5章 数据操作v5.1 数据查询 v5.2 数据修改5.1 数据查询v5.1.1 连接查询v5.1.2 合并多个结果集 v5.1.3 将查询结果保存到新表中v5.1.4 使用 top限制结果集 v5.1.5 使用case表达式v5.1.6 汇总数据v5.1.7 子查询 5.1.1 连接查询v若一个查询同时涉及两个或两个以上的表，则称之为连接查询。v连接查询包括内连接、外连接和交叉连接等。连接基础知识v连接查询中用于连接两个表的条件称为连接条件或连接谓词。 v一般格式为： 必须是可比的必须是可比的内连接vsql-92格式： from 表1 in

2、ner join 表2 on vsql-89格式： from 表1，表2 where 执行连接操作的过程：v首先取表首先取表1 1中的第中的第1 1个元组，然后从头开始扫个元组，然后从头开始扫描表描表2 2，逐一查找满足连接条件的元组，逐一查找满足连接条件的元组，v找到后就将表找到后就将表1 1中的第中的第1 1个元组与该元组拼接个元组与该元组拼接起来，形成结果表中的一个元组。起来，形成结果表中的一个元组。v表表2 2全部查找完毕后，再取表全部查找完毕后，再取表1 1中的第中的第2 2个元个元组，然后再从头开始扫描表组，然后再从头开始扫描表2 2， v重复这个过程，直到表重复这个过程，直到表1

3、 1中的全部元组都处中的全部元组都处理完毕为止。理完毕为止。例2.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。select sname, cno, grade from student join sc on student.sno = sc.sno where sdept = 计算机系 例3. 查询信息系修了vb课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。select sname, cname, grade from student s join sc on s.sno = sc. sno join course c on c.cno = sc.cno wh

4、ere sdept = 信息系 and cname = vb 例4查询所有修了vb课程的学生的修课情况，要求列出学生姓名和所在的系。select sname, sdept from student s join sc on s.sno = sc. sno join course c on c.cno = sc.cno where cname = vb例5有分组的多表连接查询。查询每个系的学生的考试平均成绩。select sdept, avg(grade) as averagegradefrom student s join sc on s.sno = sc.sno group by sdept

5、例6有分组和行过滤的多表连接查询。查询计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩。vselect cno, count(*) as total, avg(grade) as avggrade, max(grade) as maxgrade, min(grade) as mingrade from student s join sc on s.sno = sc.sno where sdept = 计算机系 group by cno 自连接v为特殊的内连接v相互连接的表物理上为同一张表。v必须为两个表取别名，使之在逻辑上成为两个表。例7. 查询与刘晨在同一个系学习的学生的姓名和所在的

6、系。select s2.sname, s2.sdept from student s1 join student s2 on s1.sdept = s2.sdept where s1.sname = 刘晨 and s2.sname != 刘晨v例8假设有著书情况表记录了作者与其所写的图书的情况，其结构为：au_book（au_id, book_id） 查询哪些作者合写了一本书，要求列出合写书的作者号和书号。【见p98】select t1.au_id as 作者1, t2.au_id as 作者2, t1.book_id as 书号from au_book t1 join au_book t2o

7、n t1.book_id = t2.book_idwhere t1.au_id t2.au_id 外连接v只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。 v外连接的语法格式为：from 表1 left | right outer join 表2 on 例9. 查询学生的修课情况，包括修了课修了课程的学生和没有修课的学生。程的学生和没有修课的学生。select student.sno, sname, cno, gradefrom student left outer join scon student.sno = sc.sno 或：select student.sno

8、, sname, cno, gradefrom sc right outer join student on student.sno = sc.sno例10查询哪些课程没有人选，列出课程名。select cname from course c left join sc on c.cno = sc.cno where sc.cno is null 【用子查询如何实现？】例11查询每个学生的选课门数，包括没有选课的学生。select s.sno 学号, count(sc.cno) 选课门数 from student s left join sc on s.sno = sc.sno group by

9、 s.sno交叉连接v语法格式为：select from 表1 cross join 表2 v没有where子句的交叉连接将产生连接所涉及的表的一个笛卡尔乘积，也就是第一个表中的每一行数据与第二个表中的每一行数据进行连接的结果。 v交叉连接的结果一般没有什么实际的意义。v如果在交叉连接中有where子句，则交叉联接的作用将同内联接一样，例如：select * from student cross join sc where student.sno = sc.sno与内连接：select * from student inner join sc on student.sno = sc.sno的结

10、果一样。 5.1.2 合并多个结果集 将两个或更多查询的结果集组合为一个结果集，该结果集包含联合查询中的所有查询的全部行。 union语句形式语句形式 查询语句1 union all 查询语句2 union all union all 查询语句nall：在结果中包含所有的行，包括重复行。如果没有指定，则删除重复行。注意：列的个数、顺序必须相同；对应列的数据类型必须兼容！例12：将作者所在的城市与出版商所在的城市合并为一个显示结果select city as cities from authorsunion select city from publishersorder by city说明：标

11、题采用第一个的标题； 排序语句放最后select 语句。 5.1.3 将查询结果保存到新表中 vselect into 语句创建一个新表，并用 select 的结果集填充该表。新表的结构由选择列表中表达式的特性定义。 vselect into子句的大致格式为 ：select 选择列表into 新表名from 子句 被插入表的类型v局部临时表：#新表名局部于当前连接，生命期同连接期。v全局临时表： #新表名v可在所有连接中使用，生命期同用户连接期v永久表： 新表名存储在磁盘上例13将计算机系的学生信息存入#computer局部临时表中。 select sno, sname, ssex, sage

12、 into #computer from student where sdept = 计算机系例14将选修了vb课程的学生的学号及成绩存入全局临时表#vb中。select sno, grade into #vbfrom sc join course c on c.cno = sc.cno where cname = vb例1：将学生的姓名、修课的课程名和成绩存入永久表s_c_g中select sname, cname, grade into s_c_g from student s join sc on s.sno = sc.sno join course c on c.cno = sc.cn

13、o5.1.4 使用 top限制结果集 top 子句限制返回到结果集中的行数。格式： top n percent with ties top n：取前n个结果top n percent：取前n%个结果with ties：取前n个结果，包括并列的行。必须同order by一起使用 例16查询年龄最大的三个学生的姓名、年龄及所在的系。select top 3 sname, sage, sdept from student order by sage descv若要包括年龄并列第三名的学生，则此句可写为：select top 3 with ties sname, sage, sdept from st

14、udent order by sage desc例17查询vb课程考试成绩前三名的学生的姓名和成绩。select top 3 with ties sname, grade from student s join sc on s.sno = sc.sno join course c on c.cno = sc.cno where cname = vb order by grade desc例18查询选课门数最多的前三名的学生的学号和选课门数，包括并列的情况。select top 3 with ties sno, count(*) 选课门数 from sc group by sno order b

15、y count(*) desc5.1.5 使用case表达式 v可以在查询语句中使用case表达式，以达到分情况显示不同类型的数据的目的。v查询语句中的case表达式一般是出现在查询列表中。例19对pubs数据库中的titles表中的数据更改图书分类（category）的显示，以使其更易于理解。如果图书分类为“popular_comp”，则显示“流行计算类”；如果图书分类为“mod_cook”，则显示“现代烹饪类”；如果图书分类为“business”，则显示“商业类”；如果图书分类为“psychology”，则显示“心理学类”； 如果图书分类为“trad_cook”，则显示“传统烹饪类”；其他

16、情况，显示“未分类”。select category = case type when popular_comp then 流行计算类 when mod_cook then 现代烹饪类 when business then 商业类 when psychology then 心理学类 when trad_cook then 传统烹饪类 else 未分类 end, title , price from titleswhere price is not nullorder by type, price 例20对pubs数据库的titles表，如果出版日期早于1991年，则显示“旧书”；如果出版日期在

17、1992年到1998年之间，则显示“较旧”；如果晚于1998年则显示“新书”。列出图书书号，书名和处理后的出版日期和图书类型。select title_id,title, case when pubdate 1998/12/31 then 新书end, type from titles例21对pubs数据库的titles表，查询每类（type）图书的平均价格，如果平均价格高于30，则显示“比较贵”；如果平均价格在20到30之间，则显示“合适”；如果平均价格小于20，则显示“比较便宜”；如果平均价格为空，则显示“未定价”。select type, 平均价格 = case when avg(pri

18、ce) 30 then 比较贵 when avg(price) between 20 and 30 then 适中 when avg(price) 90 ) 例30查询选修了“vb”课程的这些学生的学号和选课门数。select sno, count(*) 选课门数 from sc where sno in ( select sno from sc where cno in ( select cno from course where cname = vb) group by sno例30的另一种实现方法select sno, count(*) 选课门数 from sc where sno in

19、 ( select sno from sc join course on sc.cno = co where cname = vb) group by sno例31查询选修了“vb”课程的学生的学号、姓名和成绩。select s.sno, sname, grade from student s join sc on s.sno = sc.sno join course c on o = sc.cno where cname = vb例31的另一种实现方法select s.sno, sname, grade from student s join sc on s.sno = sc.sno whe

20、re cno in ( select cno from course where cname = vb)使用子查询进行比较测试 v带比较运算符的子查询指父查询与子查询之间用比较运算符连接，v当用户能确切知道内层查询返回的是单值时，可用、=、=、运算符。例32查询选修了课程号为“c02”且成绩高于此门课程的平均成绩的学生的学号和成绩。select sno, grade from sc where cno = c02 and grade ( select avg(grade) from sc where cno = c02)例33查询计算机系年龄大于学生总平均年龄的学生的姓名和年龄。select

21、sname, sage from student where sdept = 计算机系 and sage ( select avg(sage) from student )例34查询“c02”号课程考试成绩最高的学生的姓名和所在系。select sname,sdept from student s join sc on s.sno = sc.sno where cno = c02 and grade = ( select max(grade) from sc where cno = c02)使用子查询进行存在性测试 v一般使用exists谓词，其形式为：where not exists（子查询

22、） v带exists谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。 例35.查询选修了c01号课程的学生姓名。select sname from student where exists (select * from sc where sno = student.sno and cno = c01) 注意v注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。v注2: 由于exists的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有exists的子查询中，其目标列表达式通常都用*。上句的处理过程1.找外层表student表的

23、第一行，根据其sno值处理内层查询2.由外层的值与内层的结果比较，由此决定外层条件的真、假3.顺序处理外层表student表中的第2、3、 行。例36.查询没有选修c01号课程的学生姓名和所在系。select sname, sdept from student where not exists ( select * from sc where sno = student.sno and cno = c01) 例36的另一种实现方法select sname, sdept from student where sno not in ( select sno from sc where cno =

24、c01 )例37查询计算机系没有选修“vb”课程的学生的姓名和性别。select sname, ssex from student where sno not in ( select sno from sc join course on sc.cno = co where cname = vb) and sdept = 计算机系5.2数据修改v5.2.1 添加数据v5.2.2 更新数据v5.2.3 删除数据5.2.1 添加数据v插入单行记录的insert语句的格式为：insert into () values （值表）v功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋

25、给对应列名。 说明： 1. 列名表与值表：列名必须在表中已定义，值可取常量或null。 2. 赋值规则：(1)值与列名按顺序对应，要求值类型与列数据类型一致。(2)对语句中无值对应的列名赋null。 3. 如果into子句中没有指明列名，则新插入记录的值的顺序必须与表中列的顺序一致，且每一列均有值（可为空）。 例1将新生记录（95020，陈冬，男，信息系，18岁）插入到student表中 insert into student values （95020，陈冬，男，is，18） 例2.在sc表中插入一新记录，成绩暂缺insert into sc(sno, cno) values(95020,1

26、) 注： 此时必须列出列名（因为有缺省） sc中的grade必须允许为null 实际插入的值为：(95020,1，null)多行数据插入v格式： insert into () select语句 v功能：将（结构与列名表相同的）子查询结果数据插入指示的表中，新行中列名表以外各列置null。 例3 统计每个系的学生的平均年龄，并把结果存入数据库中 create table deptage ( -先建表sdept char(15), avgage smallint) insert into deptage(sdept,avgage)-再插入select sdept, avg(sage) from s

27、tudent group by sdept 5.2.2 更新数据 v更新数据指的是修改表中的列的值。v格式：update set , where 2.有条件更新更改表中满足条件的各行中指定列值(1) 基于本表条件的更新 (2) 基于其它表的更新 (1) 基于本表条件的更新 例4将学生9512101的年龄改为21岁 update student set sage = 21 where sno = 9512101 (2)基于其它表的更新v用子查询实现v用多表连接查询实现例5将计算机系全体学生的成绩置0 1. 用子查询实现update sc set grade = 0 where sno in(se

28、lect sno from student where sdept=cs) 2. 用多表连接查询实现update sc set grade = 0 from sc join student on sc.sno = student.sno where sdept=cs 例6修改条件包含子查询。将学分最低的课程的学分加2分。update course set credit = credit + 2 where credit = ( select min(credit) from course)例7使用case表达式分情况修改数据。对pubs数据库的titles表，修改图书的价格（price）和版税

29、（royalty），修改规则如下：对于“business”类型的图书，其价格增加10，版税为8；对于“popular_comp”类型的图书，其价格增加15，版税为10；对于“psychology”类型的图书，其价格增加20，版税为12，其他类型的图书价格不变，版税也为12。update titles set price = price + price * case type when business then 0.1 when popular_comp then 0.15 when psychology then 0.2 else 0 end, royalty = case type when