SQL数据操作功能.ppt

《SQL数据操作功能.ppt》由会员分享，可在线阅读，更多相关《SQL数据操作功能.ppt（106页珍藏版）》请在三一办公上搜索。

1、第5章 SQL语言数据操作功能,5.1 数据查询功能 5.2 数据更改功能 5.3 视图,5.1 数据查询功能,5.1.1 查询语句的基本结构5.1.2 简单查询5.1.3 多表连接查询5.1.4 使用TOP限制结果集5.1.5 子查询,5.1.1 查询语句的基本结构SELECT ALL|DISTINCT,-需要哪些列FROM（或视图）-来自哪些表WHERE-根据什么条件GROUP BY HAVING 内部函数表达式 ORDER BY ASC|DESC；语句含义：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，按SELECT子句中的目标列表达式，选出元组中

2、的属性值形成结果表。,如果有ORDER子句，则结果表要根据指定的列名2按升序或降序排序；如果有GROUP子句，则将结果按的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。,5.1.2 简单查询,1.选择表中若干列（1）查询表中用户感兴趣的部分属性列。例1：查询全体学生的学号与姓名。SELECT Sno，Sname FROM Student例2：查询全体学生的姓名、学号和所在系。SELECT Sname，Sno，Sdept FROM Student,（2）查询全部列 例3查询全

3、体学生的记录 SELECT Sno，Sname，Ssex,Sage,Sdept FROM Student等价于：SELECT*FROM Student,（3）查询经过计算的列 例4查询全体学生的姓名及其出生年份。SELECT Sname，2010-Sage FROM Student结果为如下形式：Sname 2010-Sage-S1 1984 S2 1983 S3 1985 S4 1985例5含字符串常量的列：查询全体学生的姓名和出生年份，并在出生年份列前加一列，此列的每行数据均为“出生年份”常量值。SELECT Sname，出生年份,2010-Sage FROM Student,（4）改变列

4、标题 语法：列名|表达式 AS 列标题或：列标题 列名|表达式例：SELECT Sname 姓名,2008-Sage 年份 FROM Student,（5）消除取值相同的记录例6在修课表中查询有哪些学生修了课程，要求列出学生的学号。SELECT Sno FROM SC结果中有重复的行。用DISTINCT关键字可以去掉结果中的重复行。DISTINCT关键字放在SELECT词的后边、目标列名序列的前边。SELECT DISTINCT Sno FROM SC,2.查询满足条件的元组,（1）比较大小,例7查询计算机系全体学生的姓名。SELECT Sname FROM Student WHERE Sde

5、pt=计算机系例8查询年龄在20岁以下的学生的姓名及年龄。SELECT Sname,Sage FROM Student WHERE Sage 20例9查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade 60,（2）确定范围,用BETWEENAND和NOT BETWEENAND是逻辑运算符，可以用来查找属性值在或不在指定范围内的元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限。BETWEENAND的格式为：列名|表达式 NOT BETWEEN 下限值 AND 上限值如果列或表达式的值在（或不在）下限值和上限值范围内

6、，则结果为True，表明此记录符合查询条件。BETWEENAND包括边界值。,例10查询年龄在2023岁之间的学生的姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23等价于：SELECT Sname,Sdept,Sage FROM Student WHERE Sage=20 AND Sage=23,例11查询年龄不在2023之间的学生姓名、所在系和年龄。SELECT Sname,Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23等价于

7、：SELECT Sname,Sdept,Sage FROM Student WHERE Sage 23,例12 对于日期类型的数据也可以使用基于范围的查找。查询1991年6月出版的图书信息：SELECT title_id,type,price,pubdate FROM titles WHERE pubdate BETWEEN 1991/6/1 AND 1991/6/30,（3）确定集合,用来查找属性值属于指定集合的元组。格式为：列名 NOT IN（常量1,常量2,常量n）IN：当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录。NOT IN：当列中的值与某个

8、常量值相等时，结果为False，表明此记录为不符合查询条件的记录。,例13 查询信息管理系、通信工程系和计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept IN(信息管理系,通信工程系,计算机系)等价于：SELECT Sname,Ssex FROM Student WHERE Sdept=信息管理系 OR Sdept=通信工程系 OR Sdept=计算机系,例14 查询既不是信息管理系、通信工程系，也不是计算机系学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN(信息管

9、理系,通信工程系,计算机系)等价于：SELECT Sname,Ssex FROM Student WHERE Sdept!=信息管理系 AND Sdept!=通信工程系 AND Sdept!=计算机系,（4）字符匹配,一般形式为：列名 NOT LIKE 匹配串中可包含如下四种通配符：_：匹配任意一个字符；%：匹配0个或多个字符；：匹配 中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表达)；：不匹配 中的任意一个字符。,例15查询学生表中姓张的学生的详细信息。SELECT*FROM Student WHERE Sname LIKE 张%例16查询学生表中姓张、姓李和姓刘的学生的情

10、况。SELECT*FROM Student WHERE Sname LIKE 张李刘%例17 查询Student表中名字的第2个字为“小”或“大”的学生的姓名和学号。SELECT Sname,Sno FROM Student WHERE Sname LIKE _小大%,例18 查询Student表中所有不姓“刘”的学生。SELECT Sname FROM Student WHERE Sname NOT LIKE 刘%例19 从Student表中查询学号的最后一位不是2、3、5的学生信息。SELECT*FROM Student WHERE Sno LIKE%235,转义字符,若要查找的字符串正好

11、含有通配符，比如下划线或百分号，就需要使用一个特殊子句来告诉系统这里的下划线或百分号是一个普通的字符，而不是一个通配符，这个特殊的子句就是ESCAPE。ESCAPE的语法格式：ESCAPE转义字符其中“转义字符”可以是任何有效的字符。,例如，为查找field1字段中包含字符串“30%”的记录，可在WHERE子句中指定：WHERE field1 LIKE%30!%ESCAPE!又如，为查找field1字段中包含下划线（_）的记录，可在WHERE子句中指定：WHERE field1 LIKE%!_%ESCAPE!,（5）涉及空值的查询,空值（NULL）在数据库中表示不确定的值。例如，学生选修课程后

12、还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。判断某个值是否为NULL，不能使用普通的比较运算符。判断取值为空的语句格式为：列名 IS NULL判断取值不为空的语句格式为：列名 IS NOT NULL,例20 查询还没有考试的学生的学号和相应的课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NULL查询结果如图4-20所示。例21 查询所有已经考试了的学生的学号和课程号。SELECT Sno,Cno FROM SC WHERE Grade IS NOT NULL,（6）多重条件查询,在WHERE子句中可使用AND和OR来组成多条件查询

13、。使用AND谓词的语法格式如下：布尔表达式1 AND布尔表达式2 AND AND 布尔表达式n只有当全部的布尔表达式均为真时，整个表达式的结果才为真，只要有一个布尔表达式的结果为假，则整个表达式结果即为假。使用OR谓词的语法格式如下。布尔表达式1 OR布尔表达式2 OR OR 布尔表达式n表示只要其中一个布尔表达式为真，则整个表达式的结果即为真；只有当全部布尔表达式的结果均为假时，整个表达式结果才为假。,例22 查询计算机系年龄在20岁以下的学生姓名SELECT Sname FROM Student WHERE Sdept=计算机系 AND Sage 20例23 查询计算机系和信息管理系学生中

14、年龄在1820的学生的学号、姓名、所在系和年龄。SELECT Sno,Sname,Sdept,Sage FROM Student WHERE(Sdept=计算机系 OR Sdept=信息管理系)AND Sage between 18 and 20也可写为：SELECT Sno,Sname,Sdept,Sage FROM Student WHERE Sdept in(计算机系,信息管理系)AND Sage between 18 and 20,3.对查询结果集进行排序,可对查询结果进行排序。排序子句为：ORDER BY ASC|DESC,说明：按进行升序（ASC）或降序（DESC）排序。,例24

15、将学生按年龄的升序排序。SELECT*FROM Student ORDER BY Sage例25 查询选修了“C002”号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECT Sno,Grade FROM SC WHERE Cno=C002 ORDER BY Grade DESC例26 查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROM Student ORDER BY Sdept,Sage DESC,4.使用统计函数汇总数据,SQL提供的统计函数有：COUNT(DISTINCT|ALL*)统计元组个数 COUNT(DISTINCT

16、|ALL)统计一列中值的个数 SUM(DISTINCT|ALL)计算一列值的总和 AVG(DISTINCT|ALL)计算一列值的平均值MAX(DISTINCT|ALL)求一列值中的最大值 MIN(DISTINCT|ALL)求一列值中的最小值使用DISTINCT则计算时要取消指定列中的重复值。若不指定DISTINCT或指定ALL（缺省），则表示不取消重复值。上述函数中除COUNT(*)外，其他函数在计算过程中均忽略NULL值。,例27 统计学生总人数。SELECT COUNT(*)FROM Student 例28 统计选修了课程的学生的人数。SELECT COUNT(DISTINCT Sno)F

17、ROM SC例29计算学号为“0611101”的学生的考试总成绩之和。SELECT SUM(Grade)FROM SC WHERE Sno=0611101,例30 计算“C001”课程的学生的考试平均成绩。图4-25 例31查询结果SELECT AVG(Grade)FROM SC WHERE Cno=C001例31 查询选修了“C001”号课程的学生的最高分和最低分。SELECT MAX(Grade)最高分,MIN(Grade)最低分 FROM SC WHERE Cno=C001,注意,统计函数不能出现在WHERE子句中。例如，查询年龄最大的学生的姓名，如下写法是错误的：SELECT Snam

18、e FROM Student WHERE Sage=MAX(Sage),5.对查询结果进行分组计算,GROUP BY子句可将查询结果表的各行按一列或多列取值相等的原则进行分组。分组语句的一般形式：GROUP BY HAVING 目的：细化集函数的作用对象。作用：可以控制计算的级别，对全表还是对一组。如果未对查询结果分组，集函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则，集函数将作用于每一个组，即每一组都有一个函数值。,例32 统计每门课程的选课人数，列出课程号和人数。SELECT Cno as 课程号,COUNT(Sno)as 选课人数 FROM SC GROUP BY Cno

19、该语句首先对查询结果按Cno的值分组，所有具有相同Cno值的元组归为一组，然后再对每一组使用COUNT函数进行计算，求得每组的学生人数。,例33 查询每名学生的选课门数和平均成绩。SELECT Sno 学号,COUNT(*)选课门数,AVG(Grade)平均成绩 FROM SC GROUP BY Sno注意：GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。,例34 统计每个系的学生人数和平均年龄。SELECT Sdept,COUNT

20、(*)AS 人数,AVG(Sage)AS 平均年龄 FROM Student GROUP BY Sdept例35 带WHERE子句的分组，统计每个系的女生人数。SELECT Sdept,Count(*)女生人数 FROM Student WHERE Ssex=女 GROUP BY Sdept,例36 按多列分组。统计每个系的男生人数和女生人数以及男生的最大年龄和女生的最大年龄。结果按系名升序排序SELECT Sdept,Ssex,Count(*)人数,Max(Sage)最大年龄 FROM Student GROUP BY Sdept,Ssex ORDER BY Sdept,使用HAVING,H

21、AVING子句用于对分组后的结果再进行过滤，它的功能有点像WHERE子句，但它用于组而不是单个记录。在HAVING子句中可以使用统计函数，但在WHERE子句中则不能。HAVING通常与GROUP BY子句一起使用。例37 查询选修3门以上课程的学生的学号和选课门数。SELECT Sno,count(*)选课门数 FROM SC GROUP BY Sno HAVING COUNT(*)3,例38 查询选课门数大于等于4门的学生的平均成绩和选课门数。SELECT Sno,AVG(Grade)平均成绩,COUNT(*)选课门数 FROM SC GROUP BY Sno HAVING COUNT(*)

22、=4,说明,WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。GROUP BY子句用来对经WHERE子句筛选后的结果数据进行分组。HAVING子句用来对分组后的结果数据再进行筛选。对于可在分组操作之前应用的搜索条件，在WHERE子句中指定它们更有效，这样可减少参与分组的数据行。应当在HAVING子句中指定的搜索条件应该是那些必须在执行分组操作之后应用的搜索条件。建议将所有行搜索条件放在WHERE子句中而不是HAVING子句中。,例如，查询计算机系和信息管理系的学生人数：SELECT Sdept,COUNT(*)FROM Student GROUP BY Sdept HAVING

23、Sdept in(计算机系,信息管理系)或：SELECT sdept,COUNT(*)FROM Student WHERE Sdept in(计算机系,信息管理系)GROUP BY Sdept第二种写法比第一种写法效率要高，因为参与分组的数据会比较少。,5.1.3 多表连接查询,若一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括内连接、外连接和交叉连接等。连接查询中用于连接两个表的条件称为连接条件或连接谓词。一般格式为：=,必须是可比的,1.内连接,SQL-92 内连接语法如下：SELECT FROM 表名 INNER JOIN 被连接表 ON 连

24、接条件执行过程如下：首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2，重复该过程，直到表1中的全部元组都处理完为止。,例39 查询每个学生及其选课的详细信息。SELECT*FROM Student INNER JOIN SC ON Student.Sno=SC.Sno结果中有重复的列。例40 去掉例39中的重复列。SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Stude

25、nt JOIN SC ON Student.Sno=SC.Sno,例41 查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECT Sname,Cno,Grade FROM Student JOIN SC ON Student.Sno=SC.Sno WHERE Sdept=计算机系 可为表提供别名，其格式如下：AS 例如，使用别名时例41可写为如下形式：SELECT Sname,Cno,Grade FROM Student S JOIN SC ON S.Sno=SC.Sno WHERE Sdept=计算机系,例42 查询“信息管理系”选修了“计算机文化学”课程的学生的

26、成绩，要求列出学生姓名、课程名和成绩。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=计算机文化学,例43 查询所有选修了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,例44 有分组的多表连

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

28、E Sdept=计算机系 GROUP BY Cno,2、自连接,是一种特殊的内连接。相互连接的表物理上为同一张表。必须为两个表取别名，使之在逻辑上成为两个表。例46 查询与刘晨在同一个系学习的学生的姓名和所在系。SELECT S2.Sname,S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept=S2.Sdept WHERE S1.Sname=刘晨 AND S2.Sname!=刘晨,3、外连接,在通常的连接操作中，只有满足连接条件的元组才能作为结果输出，但是有时我们想以S表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出

29、其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（Outer Join）。外连接的运算符通常为*。有的关系数据库中也用+。,ANSI方式的外连接的语法格式为：FROM 表1 LEFT|RIGHT OUTER JOIN 表2 ON theta方式的外连接的语法格式为：左外连接：FROM 表1,表2 WHERE 表1.列名(+)表2.列名右外连接：FROM 表1,表2 WHERE 表1.列名 表2.列名(+),例47 查询学生的选课情况，包括选修了课程的学生和没有选修课程的学生。SELECT Student.Sno,Sname,Cno,Grade FROM Student LEFT OU

30、TER JOIN SC ON Student.Sno=SC.Sno例48 查询哪些课程没有人选，列出课程名。SELECT Cname FROM Course C LEFT JOIN SC ON C.Cno=SC.Cno WHERE SC.Cno IS NULL,5.1.4 使用TOP限制结果集,在进行查询时有时只希望列出结果集中的前几个结果，而不是全部结果。例如，竞赛时可能只取成绩最高的前三名.可以使用TOP谓词限制输出的结果。格式如下:TOP n percent WITH TIES n为非负整数。TOP n：表示取查询结果的前n行；TOP n percnet：表示取查询结果前n%行；WITH

31、 TIES：表示包括并列的结果。,例49 查询年龄最大的三个学生的姓名、年龄及所在的系。SELECT TOP 3 Sname,Sage,Sdept FROM Student ORDER BY Sage DESC若包括年龄并列第3名的学生，则：SELECT TOP 3 WITH TIES Sname,Sage,Sdept FROM Student ORDER BY Sage DESC 例50 查询VB课程考试成绩前三名的学生的姓名和成绩。SELECT TOP 3 WITH TIES Sname,Grade FROM Student S JOIN SC on S.Sno=SC.Sno JOIN C

32、ourse C ON C.Cno=SC.Cno WHERE Cname=VB ORDER BY Grade DESC,4.1.5 子查询,一个SELECT 语句称为一个查询块。子查询是一个SELECT查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句的WHERE或HAVING子句内，或其它子查询中。子查询的SELECT查询使用圆括号括起来。子查询语句可以出现在任何能够使用表达式的地方，通常情况下，子查询语句用在外层查询的WHERE子句或HAVING子句中。,1.使用子查询进行基于集合的测试,使用子查询进行基于集合的测试的语句的一般格式为：列名 NOT IN(子查询),

33、例51.查询与刘晨在同一个系的学生。SELECT Sno,Sname,Sdept FROM StudentWHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname=刘晨)AND Sname!=刘晨,例52.查询成绩为大于90分的学生的学号、姓名。SELECT Sno,Sname FROM StudentWHERE Sno IN(SELECT Sno FROM SCWHERE Grade 90),例53.查询选修了“VB”课程的学生的学号、姓名。SELECT Sno,Sname FROM Student WHERE Sno IN(SELECT Sn

34、o FROM SC WHERE Cno IN(SELECT Cno FROM Course WHERE Cname=VB),例54 查询选修了VB课程的学生的选课门数和平均成绩。SELECT Sno 学号,COUNT(*)选课门数,AVG(Grade)平均成绩 FROM SC WHERE Sno IN(SELECT Sno FROM SC JOIN Course C ON C.Cno=SC.Cno WHERE Cname=VB)GROUP BY Sno,2使用子查询进行比较测试,使用子查询进行比较测试时，通过比较运算符（=、=、=），将一个表达式的值与子查询返回的值进行比较。如果比较运算的结果

35、为真，则比较测试返回True。使用子查询进行比较测试的形式如下：WHERE 表达式 比较运算符（子查询）要求子查询语句必须返回的是单值。,例55 查询选了C004课程且成绩高于此课程的平均成绩的学生的学号和成绩。SELECT Sno,Grade FROM SC WHERE Cno=C004 AND Grade(SELECT AVG(Grade)FROM SC WHERE Cno=C004),例56 查询计算机系学生中年龄大于计算机系学生平均年龄的学生的姓名和年龄。SELECT Sname,Sage FROM Student WHERE Sdept=计算机系 AND Sage(SELECT AV

36、G(Sage)FROM Student WHERE Sdept=计算机系),3.使用子查询进行存在性测试,通常用EXISTS谓词，其形式如下：WHERE NOT EXISTS（子查询）带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值和逻辑假值。EXISTS：当子查询中有满足条件的数据时，返回真值，否则返回假值。NOT EXISTS：当子查询中有满足条件的数据时，返回假值；否则返回真值。,例57 查询选修了C002课程的学生姓名。SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND

37、 Cno=C002),注意,注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。注2:由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。,例57的处理过程,1.找外层表Student表的第一行，根据其Sno值处理内层查询2.由外层的值与内层的结果比较，由此决定外层条件的真、假3.顺序处理外层表Student表中的第2、3、行。,例58 查询没有选修C001课程的学生姓名和所在系。用多表连接实现（数据有误）SELECT DISTINCT Sname,Sdept FROM Student S

38、 JOIN SC ON S.Sno=SC.Sno WHERE Cno!=C001,例58示例（续）,用嵌套子查询实现在子查询中否定（数据有误）SELECT Sname,Sdept FROM Student WHERE Sno IN(SELECT Sno FROM SC WHERE Cno!=C001)在外层查询中否定SELECT Sname,Sdept FROM Student WHERE Sno NOT IN(SELECT Sno FROM SC WHERE Cno=C001),例58示例（续）,用相关子查询实现在子查询中否定（数据有误）SELECT Sname,Sdept FROM Stu

39、dent WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno!=C001)在外层查询中否定SELECT Sname,Sdept FROM Student WHERE NOT EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AND Cno=C001),例59 查询计算机系没有选修VB课程的学生的姓名和性别。SELECT sname,ssex FROM Student WHERE sno NOT IN(SELECT sno FROM SC JOIN Course ON SC.cno=Co WHE

40、RE cname=VB)AND sdept=计算机系,5.2 数据更改功能,5.2.1 插入数据 5.2.2 更新数据 5.2.3 删除数据,5.2.1 插入数据,插入单行记录的INSERT语句的格式为：INSERT INTO()VALUES（值表）功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序或列名表顺序赋给对应列名。,注意,值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。如果后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。,例1 将一个新生插入到Student表中，学号：0621105，姓名：陈冬，性别：男，

41、年龄18岁，信息管理系学生。INSERT INTO Student VALUES(0621105,陈冬,男,信息管理系,18)例2 在SC表中插入一条新记录，学号为“0621105”，选修的课程号为“C001”，成绩暂缺。INSERT INTO SC(Sno,Cno)VALUES(0621105,C001),5.2.2 更新数据,用UPDATE语句实现。格式：UPDATE SET,n WHERE 需要修改数据的表的名称。SET子句指定要修改的列，表达式指定要修改后的新值。WHERE子句用于指定只修改表中满足条件的记录的相应列值。,无条件更新,例1.将所有学生的年龄加1。UPDATE Stude

42、nt SET Sage=Sage+1,有条件更新,1.基于本表条件的更新例2.将学号为“0611104”学生的年龄改为18岁。UPDATE Student SET Sage=18WHERE Sno=0611104,2.基于其他表条件的更新,例3：将计算机系全体学生的成绩加5分。（1）用子查询实现UPDATE SC SET Grade=Grade+5 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept=计算机系)（2）用多表连接实现UPDATE SC SET Grade=Grade+5 FROM SC JOIN Student ON SC.Sno=S

43、tudent.Sno WHERE Sdept=计算机系,基于其他表条件的更新（续）,例4 将学分最低的课程的学分加2分。UPDATE Course SET Ccredit=Ccredit+2 WHERE Ccredit=(SELECT MIN(Ccredit)FROM Course),4.2.3 删除数据,用DELETE语句实现。格式：DELETE FROM WHERE 说明了要删除哪个表中的数据。WHERE子句说明只删除表中满足条件的记录。,无条件删除,例1.删除所有学生的选课记录。DELETE FROM SC,有条件删除,（1）基于本表条件的删除。例2删除所有不及格学生的修课记录。DELE

44、TE FROM SC WHERE Grade 60,基于其他表条件的删除,例3删除计算机系不及格学生的修课记录。（1）用子查询实现DELETE FROM SC WHERE Grade 60 AND Sno IN(SELECT Sno FROM Student WHERE Sdept=计算机系)（2）用多表连接实现DELETE FROM SC FROM SC JOIN Student ON SC.Sno=Student.SnoWHERE Sdept=计算机系AND Grade 60,5.3 视图,5.3.1 基本概念5.3.2 定义视图5.3.3 通过视图查询数据5.3.4 修改和删除视图 5.

45、3.5 视图的作用,5.3.1 基本概念,视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口，是基本表的部分行和列数据的组合。视图是一个虚表。数据库中只存放视图的定义，而不存放视图包含的数据，这些数据仍存放在原来的基本表中。基本表中的数据如果发生变化，从视图中查询出的数据也会随之变化。,视图可以从一个基本表中提取数据，也可以从多个基本表中提取数据，甚至还可以从其他视图中提取数据，构成新的视图。对视图数据的操作最终都会转换为对基本表的操作。,5.3.2 定义视图,定义视图的格式如下：CREATE VIEW(视图列名表)AS 查询语句注意：查询语句中通常不包含ORDER BY和DISTINCT

46、子句。在定义视图时要么指定视图的全部列名，要么全部省略不写，不能只写视图的部分列名。,必须明确指定视图所有列名的情况,某个目标列不是简单的列名，而是函数或表达式。多表连接时选出了几个同名列作为视图的字段。需要在视图中为某个列选用新的更合适的列名。,1定义单源表视图,视图的数据取自一个基本表的部分行和列例1 建立查询信息管理系学生的学号、姓名、性别和年龄的视图。CREATE VIEW IS_StudentAS SELECT Sno,Sname,Ssex,Sage FROM Student WHERE Sdept=信息管理系,例3,2定义多源表视图,指定义视图的查询语句涉及多张表，这样定义的视图一

47、般只用于查询，不用于修改数据。例2 建立信息管理系选修了C001课程的学生的学号、姓名和成绩的视图。CREATE VIEW V_IS_S1(Sno,Sname,Grade)AS SELECT Student.Sno,Sname,Grade FROM Student JOIN SC ON Student.Sno=SC.Sno WHERE Sdept=信息管理系 AND SC.Cno=C001,例8,3在已有视图上定义新视图,可以在视图上再建立视图，这时作为数据源的视图必须是已经建立好的视图。例3 利用例1建立的视图，建立查询信息管理系年龄小于20的学生的学号、姓名和年龄的视图。CREATE VI

48、EW IS_Student_SageAS SELECT Sno,Sname,Sage FROM IS_Student WHERE Sage 20,IS_Student,视图的来源是视图和基本表的组合,例4 在例1所建的视图基础上，例2的视图定义可改为：CREATE VIEW V_IS_S2(Sno,Sname,Grade)AS SELECT SC.Sno,Sname,Grade FROM IS_Student JOIN SC ON IS_Student.Sno=SC.Sno WHERE Cno=C001,4定义带表达式的视图,由于视图中的数据并不实际存储，所以定义视图时可以根据需要设置一些派生

49、属性列，在这些派生属性列中保存经过计算的值。这些派生属性被称为虚拟列。包含虚拟列的视图也称为带表达式的视图。例5 定义一个反映学生出生年份的视图。CREATE VIEW BT_S(Sno,Sname,Sbirth)AS SELECT Sno,Sname,2008-Sage FROM Student,5含分组统计信息的视图,定义视图的查询语句中含有GROUP BY子句，这样的视图只能用于查询，不能用于修改数据。例6 定义一个反映每个学生的学号及平均成绩的视图。CREATE VIEW S_G(Sno,AverageGrade)AS SELECT Sno,AVG(Grade)FROM SC GROU

50、P BY Sno,例10,5.3.3 通过视图查询数据,通过视图查询数据同基本表一样。例7 利用例1建立的视图，查询信息管理系男生的信息。SELECT*FROM IS_Student WHERE Ssex=男 最终转换成的实际查询：SELECT Sno,Sname,Ssex,Sage FROM Student WHERE Sdept=信息管理系 AND Ssex=男,通过视图查询数据（续）,例8 查询信息管理系选修了C001课程且成绩大于等于60的学生的学号、姓名和成绩。SELECT*FROM V_IS_S1 WHERE Grade=60转换成的对最终基本表的查询：SELECT S.Sno,S