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

《《数据库管理与编程技术》第5章 数据操作.ppt》由会员分享，可在线阅读，更多相关《《数据库管理与编程技术》第5章 数据操作.ppt（92页珍藏版）》请在三一办公上搜索。

1、数据库管理与编程技术,第5章 数据操作,第5章 数据操作,5.1 数据查询 5.2 数据修改,5.1 数据查询,5.1.1 连接查询5.1.2 合并多个结果集 5.1.3 将查询结果保存到新表中5.1.4 使用 TOP限制结果集 5.1.5 使用CASE表达式5.1.6 汇总数据5.1.7 子查询,5.1.1 连接查询,若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询包括内连接、外连接和交叉连接等。,连接基础知识,连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：,必须是可比的,内连接,SQL-92格式：FROM 表1 INNER JOIN 表2 ON SQL-

2、89格式：FROM 表1，表2 WHERE,执行连接操作的过程：,首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，重复这个过程，直到表1中的全部元组都处理完毕为止。,例2.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。,SELECT Sname,Cno,Grade FROM Student JOIN SC ON Student.Sno=SC.Sno WHERE Sdept=计算机系,例3.查询信息系修了VB

3、课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。,SELECT Sname,Cname,Grade FROM Student s JOIN SC ON s.Sno=SC.Sno JOIN Course c ON c.Cno=SC.Cno WHERE 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有分组的多表连接

4、查询。查询每个系的学生的考试平均成绩。,SELECT Sdept,AVG(grade)as AverageGradeFROM Student S JOIN SC ON S.Sno=SC.Sno GROUP BY Sdept,例6有分组和行过滤的多表连接查询。查询计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩。,SELECT 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

5、Sdept=计算机系 GROUP BY Cno,自连接,为特殊的内连接相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。,例7.查询与刘晨在同一个系学习的学生的姓名和所在的系。,SELECT S2.Sname,S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept=S2.Sdept WHERE S1.Sname=刘晨 AND S2.Sname!=刘晨,例8假设有著书情况表记录了作者与其所写的图书的情况，其结构为：au_book（au_id,book_id）查询哪些作者合写了一本书，要求列出合写书的作者号和书号。【见P98

6、】SELECT t1.au_id as 作者1,t2.au_id as 作者2,t1.book_id as 书号FROM au_book t1 JOIN au_book t2ON t1.book_id=t2.book_idWHERE t1.au_id t2.au_id,外连接,只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。外连接的语法格式为：FROM 表1 LEFT|RIGHT OUTER JOIN 表2 ON,例9.查询学生的修课情况，包括修了课程的学生和没有修课的学生。,SELECT Student.Sno,Sname,Cno,GradeFROM Student

7、 LEFT OUTER JOIN SCON Student.Sno=SC.Sno 或：SELECT Student.Sno,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)选课门数

8、 FROM Student s LEFT JOIN SC ON s.sno=SC.sno GROUP BY s.sno,交叉连接,语法格式为：SELECT FROM 表1 CROSS JOIN 表2 没有WHERE子句的交叉连接将产生连接所涉及的表的一个笛卡尔乘积，也就是第一个表中的每一行数据与第二个表中的每一行数据进行连接的结果。,交叉连接的结果一般没有什么实际的意义。如果在交叉连接中有WHERE子句，则交叉联接的作用将同内联接一样，例如：SELECT*FROM Student CROSS JOIN SC WHERE Student.sno=SC.sno与内连接：SELECT*FROM St

9、udent INNER JOIN SC ON Student.sno=SC.sno的结果一样。,5.1.2 合并多个结果集,将两个或更多查询的结果集组合为一个结果集，该结果集包含联合查询中的所有查询的全部行。UNION语句形式 查询语句1 UNION ALL 查询语句2 UNION ALL UNION ALL 查询语句n,ALL：在结果中包含所有的行，包括重复行。如果没有指定，则删除重复行。注意：列的个数、顺序必须相同；对应列的数据类型必须兼容！,例12：将作者所在的城市与出版商所在的城市合并为一个显示结果,SELECT city AS Cities FROM authorsUNION SEL

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

11、表中。,SELECT Sno,Sname,Ssex,Sage 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 Cour

12、se c ON c.Cno=SC.Cno,5.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 desc若要包括年龄并列第三名的学生，则此句可写为：SELECT TOP 3 with ties Sname,Sag

13、e,Sdept FROM Student 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 s

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

15、统烹饪类”；其他情况，显示“未分类”。,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年，则显示“旧书”；如果出版

16、日期在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(pr

17、ice)30 THEN 比较贵 WHEN avg(price)between 20 and 30 THEN 适中 WHEN avg(price)20 THEN 比较便宜 WHEN avg(price)IS NULL THEN 未定价 END FROM titlesGROUP BY type,5.1.6 汇总数据,有时需要对数据进行一些汇总，特别是在进行辅助决策支持时。简单的数据汇总可以直接使用Transact-SQL完成。Transact-SQL提供了的数据汇总运算符：CUBE或ROLLUP运算符。Group by 一部分！COMPUTE或COMPUTE BY运算符。支持COMPUTE和COM

18、PUTE BY是为了向后兼容。一般情况下应优先选用ROLLUP 而不是COMPUTE或COMPUTE BY。,用CUBE汇总数据,CUBE运算符在SELECT 语句的GROUP BY子句中指定。该语句的选择列表中应包含要分析的列（维度列）和聚合函数表达式（要分析的数据）。在GROUP BY子句中要指定维度列和关键字WITH CUBE。结果集将包含维度列中各值的所有可能的组合以及与这些维度值组合相匹配的基础行中的聚合值。,例22,SELECT sdept,ssex,count(*)as count_stud FROM Student GROUP BY sdept,ssex WITH CUBE,例

19、23从学号和课程号两个角度统计考试平均成绩的情况。,SELECT sno,cno,avg(grade)AS avg_grade FROM SC GROUP BY sno,cno WITH CUBE,只有一个维度的多维数据集可用于生成合计,SELECT sdept,count(*)AS count_sno FROM Student GROUP BY sdept WITH CUBE,说明,如果SELECT语句中包含多个分析的维度，则由CUBE生成的结果集可能会很大，因为这些语句会为所有维度中值的所有组合生成行。大的结果集由于包含的数据可能过多而不易于阅读和理解。解决这个问题的办法是将SELECT语

20、句放在视图中，然后再对所生成的视图进行条件查询。,例24对例23的例子，将其查询结果保存在视图中,CREATE VIEW avg_grade ASSELECT CASE WHEN GROUPING(sno)=1 THEN 全部 ELSE sno END AS SNO,CASE WHEN GROUPING(cno)=1 THEN 全部 ELSE cno END AS CNO,avg(grade)AS avg_grade FROM SC GROUP BY sno,cno WITH CUBE,例25利用例24的视图，查询学号为“9512102”的学生选课情况和平均成绩。,SELECT*from av

21、g_grade WHERE SNO=9512102,例26利用例24的视图，查询学号为“c02”课程的总平均成绩。,SELECT*from avg_grade WHERE CNO=c02 AND SNO=全部,用RULLUP汇总数据,在生成包含小计和合计的报表时，ROLLUP运算符很有用。ROLLUP运算符生成的结果集类似于CUBE运算符所生成的结果集，两者之间的主要区别在于：CUBE 生成的结果集显示了所选列的值的所有组合的聚合结果。ROLLUP 生成的结果集显示了所选列的值的某一层次结构的聚合结果。,例如，对例22的查询改为用ROLLUP,SELECT sdept,ssex,count(*

22、)as count_stud FROM Student GROUP BY sdept,ssex WITH ROLLUP,使用GROUPING区分空值,CUBE或ROLLUP操作所产生的汇总结果中，有一些列的值是空值。这些空值带来这样一个问题：如何区分CUBE或ROLLUP操作所生成的NULL值和从实际数据中返回的NULL值？这个问题可以使用GROUPING函数解决。GROUPING是一个聚合函数，它产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，附加的列的值为1；当所添加的行不是由CUBE 或ROLLUP产生时，附加的列的值为0。仅在与包含CUBE或ROLLUP运算符的GROUP

23、BY子句相联系的选择列表中才允许使用GROUPING。,GROUPING的语法,语法格式：GROUPING(column_name)“column_name”是GROUP BY子句中用于检查CUBE或ROLLUP空值的列。这个函数的返回值类型是：int。,说明,如果列中的NULL来自事实数据，则GROUPING函数返回0；如果列中的NULL是由CUBE或ROLLUP操作所生成的，则返回1。在CUBE或ROLLUP操作中，所生成的NULL代表全体值，因此在SELECT语句中，可以用GROUPING函数将CUBE或ROLLUP操作所产生的NULL替换为某个字符串，比如“全体”。又由于事实数据中的N

24、ULL表明数据值是未知的，因此，在SELECT语句中也可以将这些NULL值替换为字符串，比如“未知”。,例27在例22的查询中将CUBE生成的NULL值替换为“全体”，将事实数据中的NULL值替换为“未定”。,SELECT CASE WHEN GROUPING(sdept)=1 THEN 全体 ELSE ISNULL(sdept,未定)END as 系,CASE WHEN GROUPING(ssex)=1 THEN 全体 ELSE ISNULL(ssex,未定)END as 性别,count(*)as 人数 FROM Student GROUP BY sdept,ssex WITH CUBE,

25、5.1.7 子查询,在SQL语言中，一个SELECTFROMWHERE语句称为一个查询块。子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句的 WHERE 或 HAVING 子句内，或其它子查询中 子查询的 SELECT 查询总是使用圆括号括起来。,子查询的通常形式,WHERE 表达式 NOT IN（子查询）WHERE 表达式 比较运算符（子查询）WHERE NOT EXISTS（子查询）,使用子查询进行基于集合的测试,使用子查询进行基于集合的测试的语句的一般格式为：列名 NOT IN(子查询),例28.查询与刘晨在同一个系的学生。,SEL

26、ECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=刘晨)AND Sname!=刘晨,例29.查询成绩为大于90分的学生的学号、姓名。,SELECT Sno,Sname FROM StudentWHERE Sno IN(SELECT Sno FROM SCWHERE Grade 90),例30查询选修了“VB”课程的这些学生的学号和选课门数。,SELECT sno,count(*)选课门数 FROM SC WHERE sno IN(SELECT sno FROM SC WHER

27、E cno IN(SELECT cno FROM Course WHERE cname=VB)GROUP BY sno,例30的另一种实现方法,SELECT sno,count(*)选课门数 FROM SC WHERE sno IN(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=

28、SC.cno WHERE cname=VB,例31的另一种实现方法,SELECT s.sno,sname,grade FROM Student s JOIN SC ON s.sno=SC.sno WHERE cno IN(SELECT cno FROM Course WHERE cname=VB),使用子查询进行比较测试,带比较运算符的子查询指父查询与子查询之间用比较运算符连接，当用户能确切知道内层查询返回的是单值时，可用、=、运算符。,例32查询选修了课程号为“c02”且成绩高于此门课程的平均成绩的学生的学号和成绩。,SELECT Sno,Grade FROM SC WHERE Cno=c0

29、2 AND Grade(SELECT AVG(Grade)from SC WHERE Cno=c02),例33查询计算机系年龄大于学生总平均年龄的学生的姓名和年龄。,SELECT 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

30、)FROM SC WHERE Cno=c02),使用子查询进行存在性测试,一般使用EXISTS谓词，其形式为：WHERE NOT EXISTS（子查询）带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。,例35.查询选修了c01号课程的学生姓名。,SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=c01),注意,注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。注2:由于EXISTS的子查询只能返回真或假值

31、，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。,上句的处理过程,1.找外层表Student表的第一行，根据其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

32、 Student WHERE Sno NOT IN(SELECT Sno FROM SC WHERE Cno=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数据修改,5.2.1 添加数据5.2.2 更新数据5.2.3 删除数据,5.2.1 添加数据,插入单行记录的INSERT语句的格式为：INSERT INTO()VALUE

33、S（值表）功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。,说明：,1.列名表与值表：列名必须在表中已定义，值可取常量或NULL。2.赋值规则：(1)值与列名按顺序对应，要求值类型与列数据类型一致。(2)对语句中无值对应的列名赋NULL。3.如果INTO子句中没有指明列名，则新插入记录的值的顺序必须与表中列的顺序一致，且每一列均有值（可为空）。,例1将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中,INSERT INTO Student VALUES（95020，陈冬，男，IS，18）,例2.在SC表中插入一新记录，成绩暂缺,IN

34、SERT INTO SC(Sno,Cno)VALUES(95020,1),注：此时必须列出列名（因为有缺省）SC中的Grade必须允许为NULL 实际插入的值为：(95020,1，NULL),多行数据插入,格式：INSERT INTO()SELECT语句 功能：将（结构与列名表相同的）子查询结果数据插入指示的表中，新行中列名表以外各列置NULL。,例3 统计每个系的学生的平均年龄，并把结果存入数据库中,CREATE TABLE Deptage(-先建表Sdept CHAR(15),Avgage SMALLINT),INSERT INTO Deptage(Sdept,Avgage)-再插入SEL

35、ECT Sdept,AVG(Sage)FROM Student GROUP BY Sdept,5.2.2 更新数据,更新数据指的是修改表中的列的值。格式：UPDATE SET,WHERE,2.有条件更新,更改表中满足条件的各行中指定列值(1)基于本表条件的更新(2)基于其它表的更新,(1)基于本表条件的更新,例4将学生9512101的年龄改为21岁,UPDATE Student SET Sage=21 WHERE Sno=9512101,(2)基于其它表的更新,用子查询实现用多表连接查询实现,例5将计算机系全体学生的成绩置0,1.用子查询实现UPDATE SC SET Grade=0 WHER

36、E Sno IN(SELECT 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）和版税

37、（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 busine

38、ss THEN 8 WHEN popular_comp THEN 10 WHEN psychology THEN 12 ELSE 12 END,5.3.3 删除数据,格式：DELETE FROM WHERE,2.有条件删除,删除满足条件的数据行 基于本表条件的删除 基于其它表进行删除,基于本表条件的删除,例8基于本表条件的删除。删除所有不及格学生的选课记录。,DELETE FROM SC WHERE Grade 60,基于其它表进行删除,用子查询实现 DELETE FROM 删除表名 WHERE 列名 IN（子查询）用多表连接实现 DELETE FROM 删除表名 FROM 连接表名1 JOI

39、N 连接表名2 ON 连接条件 WHERE 删除条件,例6删除计算机系所有学生的选课记录,1.用子查询实现 DELETE FROM SC WHERE Sno IN（SELECT Sno FROM Student WHERE Sdept=CS)2.用多表连接实现 DELETE FROM SC FROM SC JOIN Student ON Student.Sno=SC.Sno WHERE Sdept=CS,例10删除条件包含子查询。删除VB考试成绩最低的两个学生的VB考试记录。,DELETE FROM SC FROM SC JOIN Course c ON SC.cno=o WHERE cnam

40、e=VB AND grade in(SELECT TOP 2 WITH TIES grade FROM SC JOIN Course c ON SC.cno=o WHERE cname=VB ORDER BY grade asc),5.3 小结,本章介绍了Transact-SQL中的数据操作功能：数据的添加、删除、修改和查询功能。对查询操作主要介绍了连接查询，包括内连接、自连接、外连接和交叉连接、可用于多个角度分析数据的汇总数据查询、子查询，比较了子查询和多表连接查询的异同，同时介绍了一些扩展的查询功能，包括限制结果集行数、合并多个结果集以及将CASE表达式应用在查询语句中。在数据修改部分，对插入操作介绍了单行和多行插入操作、对更新和删除操作介绍了复杂条件的数据更新和删除的实现。,