数据库第3章456节

1、数据查询总结和数据更新操作数据查询总结和数据更新操作order by sno desc; select top 3 * from student-where sno in(select top 4 sno from student order by sno)-带数量的查询：带数量的查询：集合查询集合查询-Select * from studentWhere sdept=计算机UnionSelect * from studentWhere sage between 18 and 20-集合查询没有直接提供并差的关键词，一般对集合查询用条件的复合来查询1 Select的一般格式Select all

2、 | distinct 别名， 别名From 别名 , 别名where group by having order by asc|desc1 目标列表达式格式：目标列表达式格式：1）*2）.* ,. * count(all | distinct *) 集函数3) 表名或别名.属性列名表达式:属性名 sno，sname 常量 出生年份 函数 year(getdate() 运算公式 2005-sage2 集函数的一般格式集函数的一般格式 集函数（集函数（distinct | all ）Count：计数Max：最大值Min：最小值Avg：平均值Sum：求和Select cno,count(sno)

3、From scGroup by cno Having count(sno) 33 Where 的条件表达式：的条件表达式：1) any | all(select 语句)2) Not between and 3) not in () (select 语句语句)4) not like escape 5) Is not null6) not exists (select语句 ) 要注意全称量词的表达7) 多条件的连接3.4 数据更新数据更新（插入insert 修改update 删除delete）3.4.1 插入数据插入数据一、插入单个元组一、插入单个元组插入单个元组的INSERT语句的格式为：INS

4、ERT INTO (,.) VALUES ( ,.) 注意： 1）如果某些属性列在INTO子句中没有出现，则新记录在这些列上将取空值。但必须注意的是，在表定义时说明了NOT NULL的属性列不能取空值。 2）如果INTO子句中没有指明任何列名，则新插入的记录必须在每个属性列上均有值。例例 插入一条选课记录插入一条选课记录(95020，1)INSERT INTO SC(Sno, Cno) VALUES (95020, 1); -Insert into sc(sno,cno,grade) values(95005,1,88)Insert into sc values(95005,1,88)-很多系

5、统允许一次插入多条记录，如INSERT INTO SC(Sno, Cno) VALUES (95003 , 1),(95003 , 2); 新插入的记录在Grade列上取空值。 二、插入子查询结果二、插入子查询结果 子查询不仅可以嵌套在SELECT语句中，用以构造父查询的条件，也可以嵌套在INSERT语句中，用以生成要插入的数据。插入子查询结果的INSERT语句的格式为： -INSERT INTO ( ,.) 子查询子查询；-其功能是以批量插入，一次将子查询的结果全部插入指定表中。首先要在数据库中建立一个新表例例 对每一个系，求学生人数及平均年龄，并把结果存入数对每一个系，求学生人数及平均年龄

6、，并把结果存入数据库。据库。CREATE TABLE Deptage (Sdept CHAR(15)， Num INT, Avgage INT); INSERTINTO Deptage(Sdept,Num, Avgage) SELECT Sdept, COUNT(*),AVG(Sage) FROM Student GROUP BY Sdept;然后将查询结果存入新表中。3.4.2 修改数据修改数据修改操作又称为更新操作，其语句的一般格式为：UPDATE SET =,=. WHERE ;其功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用的值取代相应的属性列值。

7、如果省略WHERE子句，则表示要修改表中的所有元组。一、修改某一个元组的值一、修改某一个元组的值例4 将学生95001的年龄改为22岁UPDATE Student SET Sage=22 WHERE Sno=95001; 二、修改多个元组的值二、修改多个元组的值例5 将所有信息系学生的年龄增加1岁UPDATE Student SET Sage=Sage+1 where sdept=is; 例例6 将计算机科学系全体学生的成绩加将计算机科学系全体学生的成绩加10分。分。UPDATE SCSET grade=grade+10WHERE CS=(SELECT Sdept FROM Student W

8、HERE Student.Sno=SC.Sno);(处理过程类似于相关子查询)三、带子查询的修改语句三、带子查询的修改语句子查询也可以嵌套在UPDATE语句中，用以构造执行修改操作的条件。例例 将李强的数据库成绩加将李强的数据库成绩加10分。分。update SCset grade=grade+10where sno=(select sno from student where sname=李强) and cno=(select cno from course where cname=数据库);例如，学号为95007的学生因病休学一年，复学后需要将其学号改为96089，由于Student表和S

9、C表都有关于95007的信息，因此两个表都需要修改，这种修改只能通过两条UPDATE语句进行。第一条UPDATE语句修改Student表：UPDATE Student SET Sno=96089 WHERE Sno=95007;第二条UPDATE语句修改SC表：UPDATE SC SET Sno=96089 WHERE Sno=95007;四、修改操作与数据库的一致性四、修改操作与数据库的一致性UPDATE语句一次只能操作一个表。这会带来一些问题。UPDATE Student SET Sno=96089 WHERE Sno=95007;-UPDATE SC SET Sno=96089 WHER

10、E Sno=95007;机器突然出现故障机器突然出现故障数据将永远处于不一致状态参照完整性受到破坏在执行了第一条UPDATE语句之后，数据库中的数据已处于不一致状态，因为这时实际上已没有学号为95007的学生了，但SC表中仍然记录着关于95007学生的选课信息，即数据的参照完整性参照完整性受到破坏。只有执行了第二条UPDATE语句之后，数据才重新处于一致状态。但如果执行完一条语句之后，机器突然出现故障，无法再继续执行第二条UPDATE语句，则数据库中的数据将永远处于不一致状态。因此必须保证这两条UPDATE语句要么都做，要么都不做。为解决这一问题，数据库系统通常都引入了事务事务（Transac

11、tion）的概念，将在后面详细介绍.3.4.3 删除数据删除数据删除语句的一般格式为：DELETEFROM WHERE ; DELETE语句的功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在字典中。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。 一、删除某一个元组的值一、删除某一个元组的值例7 删除学号为95019的学生记录DELETEFROM StudentWHERE Sno=95019; 二、删除多个元组的值二、删除多个元组的值例8 删除所有的学生选课记录DELETE FROM SC; 这条DELETE语

12、句将使SC成为空表，它删除了SC的所有元组。 三、带子查询的删除语句三、带子查询的删除语句子查询同样也可以嵌套在DELETE语句中，用以构造执行删除操作的条件。例例9删除计算机科学系所有学生的选课记录删除计算机科学系所有学生的选课记录DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno);例例9 删除李强选修删除李强选修数据库的选课记录的选课记录Delete from scWhere sno= (select sno from student where sname=李强) and cno= (

13、select cno from course Where cname=数据库 ) Update student Set sname=sname+* Where sno in( select sno from student x where not exists ( select * from course Where not exists (select * from sc where sno=x.sno and cno=o) )例例10 将选了所有课程的学生的姓名后面加将选了所有课程的学生的姓名后面加*2 删除李强选修删除李强选修数据库的选课记录的选课记录3 将选了所有课程的学生的姓名后面加

14、将选了所有课程的学生的姓名后面加*1 有表有表qq（cno,cname,count）,要求将选课人数在要求将选课人数在100人以上的课号，课程名称，选课人数存放到表人以上的课号，课程名称，选课人数存放到表qq中。中。Select * from sc,(select * from student where sdept=计算计算机机) as RWhere sc.sno=R.sno ( select * from student x where not exists ( select * from course Where not exists (select * from sc where sn

15、o=x.sno and cno=o) )Select sno,sname,2012-sage from Where sdept=isCreate table temp(sno ,Sname,Ssex ,Sage,Sdept)导入导入3.5 视图1.从一个或多个基本表(或视图)导出的表，是虚表虚表；2. 使用户以不同方式看数据不同方式看数据；3. 数据库中只存只存视图的定义定义，不存数据不存数据；4. 对视图的更新有限制更新有限制。主要目的：提供数据库保护。学生教师教务管理学生管理系统教师管理系统教务管理系统基本表基本表基本表基本表视图视图用户3.5.1 定义视图一、创建视图 SQL语言用CRE

16、ATE VIEW命令建立视图，其一般格式为： CREATE VIEW (,.) AS WITH CHECK OPTION; 其中子查询可以是任意复杂的SELECT语句，但通常不允许不允许含有ORDER BY子句子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE、 INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中视图定义中的谓词条件的谓词条件（即子查询中的条件表达式）。例1 建立信息系学生的视图。-CREATE VIEW IS_Student (Sno, Sname, Sage ) AS SELECT Sno, Sname, Sag

17、e FROM Student WHERE Sdept=IS;-CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS;例2 建立信息系学生的视图，并要求进行修改和插入操作时仍须保证该视图只有信息系的学生-CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS WITHCHECKOPTION;- WITH CHECK OPTION保证更新时满足视图定义的谓词条件。行列子集视图行列子集视图：从一个基本

18、表中导出，只是去掉了某些行或列(保留原表主码)，这样的视图称行列子集视图行列子集视图。Create view st2 (sno,sage,sdept)As select sno,sge,sdept From student-带表达式的视图，即带虚拟列的视图带虚拟列的视图。Create view st3 (sno,sname,brithyear)As Select sno,sname,year(getdate()-sageFrom student分组视图分组视图，子查询带集函数和GROUP BY分组的视图。-Create view stu4(sdept,scount,maxsage,minsag

19、e,avgage)AsSelect sdept,count(sno),max(sage),min(sage),avg(sage)From studentGroup by sdept二、删除视图语句的格式为：DROP VIEW ;一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROP VIEW语句将他们一一删除。例8 删除视图IS_S1DROP VIEW IS_S1;3.5.2 查询视图查询视图DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转

20、换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视视图的消解图的消解（View Resolution）。例例1 在信息系学生的视图中找出年龄小于在信息系学生的视图中找出年龄小于20岁的学生岁的学生SELECT Sno, SageFROM IS_Student（信息系学生的视图信息系学生的视图） WHERE Sage20;DBMS执行此查询时，将其与执行此查询时，将其与IS_Student视图定义中的子查询视图定义中的子查询SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS ; 结合起来，转换成对基

21、本表结合起来，转换成对基本表Student的查询，修正后的查询语的查询，修正后的查询语句为：句为： SELECT Sno, Sage FROM Student WHERE Sdept=IS AND Sage100修正后的SQL表达： select sdept,count(sno) as scountFrom studentWhere count(sno)100有问题：where条件中出现集函数正确表示：Select sdept,count(sno) as scountFrom studentGroup by sdeptHaving count(sno)100对试图的查询要注意非行列子集视图的情

22、况，有可能出现错误。3.5.3 更新视图由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新最终要转换为对基本表的更新。为防止用户通过视图对数据进行增删改时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITH CHECK OPTION子句，这样在视图上增删改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。 向视图中插入数据向视图中插入数据例1 向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为95008，姓名为小新，年龄为20岁INSERTINTO IS_StudentVALUES(95008, 小新

23、, 20);DBMS将其转换为对基本表的更新： INSERT INTO Student(Sno,Sname,Sage,Sdept)VALUES(95008, 小新, 20, IS); 这里系统自动将系名IS放入VALUES子句中。 CREATE VIEW IS_Student AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept=IS WITHCHECKOPTION; 视图中修改数据视图中修改数据例2 向信息系学生视图IS_Student中姓名为小新改名为蜡笔小新，年龄为20岁Update IS_StudentSet sname=蜡笔小新蜡笔小

24、新，sage=20Where sname= 小新小新;-DBMS将其转换为对基本表的更新： Update IS_StudentSet sname=蜡笔小新蜡笔小新，sage=20Where sname= 小新小新 and sdept=is; 修改Update view1Set scount=200Where sdept=is象这种修改不能被执行，因为scount是虚列，也就是不能改元组的个数，这里计数值改了 也没用。从视图中删除：从视图中删除：-Delete From is_studentWhere sno=97008-Delete From studentWhere sno=97008And sdept=is通过视图更新基本表，从理论上来说有的是可以更新的（不能更新），有的是根本不可能更新的（不可