第四章关系数据库标准语言SQL语言

单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,第四章 关系数据库标准语言,SQL,4.1 SQL,概述,4.2,数据定义,4.3,数据查询,4.4,数据更新,4.5,视图,第四章 关系数据库标准语言,SQL,4.1 SQL,概述,SQL,（,Structed,Query Language,），即结构化查询语言，是关系数据库的标准语言，,SQL,是一个通用的、功能极强的关系数据库语言。,4.1.1 SQL,的产生与发展,SQL,是在,1974,年由,Boycee,和,Chamberlin,提出的，并在,IBM,公司研制的关系数据库管理系统原型,System R,上实现。,由于,SQL,简单易学，功能丰富，深受用户及计算机工业界欢迎，因此被数据库厂商所采用。经各公司的不断修改、扩充和完善，,SQL,得到业界的认可。,1986,年,10,月美国国家标准局的数据库委员会,X3H2,批准了,SQL,作为关系数据库语言的美国标准。同年公布了,SQL,标准文本。,1987,年国际标准化组织也通过了这一标准。,SQL,标准从,1986,年公布以来随着数据库技术的发展不断发展，不断丰富。,4.1.2 SQL,的特点,一、综合统一,SQL,集数据定义语言,DDL,、数据操纵语言,DML,、数据控制语言,DCL,的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。,二、高度非过程化,SQL,进行数据操作，只要提出“做什么”,而无需指明“怎么做”,因此无需了解存取路径。存取路径的选择以及,SQL,的操作过程由系统自动完成。,4.1.2 SQL,的特点,三、面向集合的操作方式,SQL,采用面向集合的操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。,四、以同一种语法结构提供多种使用方式,作为独立语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入,SQL,命令对数据库进行操作；,作为嵌入式语言，,SQL,语句能够嵌入到高级语言（例如,C,，,C+,，,Java,）程序中，供程序员设计程序时使用。,4.1.2 SQL,的特点,五、语言简洁易学,SQL,功能极强，但由于设计巧妙，语言十分简洁，完成核心功能只用了,9,个动词。,SQL,接近英语口语，因此容易学习，容易使用。,SQL,功能,动词,数据查询,SELECT,数据定义,CREATE,DROP,ALTER,数据操纵,INSERT,UPDATE,DELETE,数据控制,GRANT,REVOKE,4.2,数据定义,4.2.1,基本表的定义、删除、与修改,一、定义基本表,CREATE TABLE ,（, ,列级完整性约束条件, ,列级完整性约束条件,）；,4.2,数据定义,例,:,建立一个“学生”表,Student,。,create table Student,(,sno,char(9) primary key,sname,char(20) unique,ssex,char(2),sage,smallint,sdept,char(20),);,4.2,数据定义,例,:,建立一个“课程”表,Course,。,create table Course,(,cno,char(4) primary key,cname,char(40),cpno,char(4),ccredit,smallint,foreign key (,cpno,) references,Course(cno,),);,4.2,数据定义,例：建立学生选课表,SC,。,create table SC,(,sno,char(9),cno,char(4),grade,smallint,foreign key (,sno,) references,student(sno,),foreign key (,cno,) references,course(cno,),),4.2,数据定义,4.2.2,修改基本表,ALTER TABLE , ADD ,完整性约束,DROP ,ALTER COLUMN ,；,4.2,数据定义,例：向,Student,表增加“入学时间”列，其数据类型为日期型。,alter table Student add,s_entrance,datetime,;,例：将年龄的数据类型由,smallint,改为,int,。,alter table Student alter column sage,int,;,4.2,数据定义,例：增加课程名必须取唯一值的约束。,alter table course add,unique(cname,);,4.2,数据定义,4.2.3,删除基本表,DROP TABLE RESTRICT|CASCADE,RESTRICT:,该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用,(,如,CHECK,FOREIGN KEY,等约束,),，不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。,CASCADE:,该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。,(SQL Server 2000,中不支持,RESTRICT|CASCADE),4.2,数据定义,4.2.3,索引的建立与删除,建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。,建立与删除索引有数据库管理员,DBA,或表的属主，负责完成。,4.2,数据定义,4.2.3,索引的建立与删除,一、建立索引,CREATE UNIQUECLUSTER INDEX ,ON ,（, ,）,索引的排列次序，可选,ASC,或,DESC,，缺省值为,ASC,。,UNIQUE,表明此索引的每一个索引值只对应唯一的数据记录。,CLUSTER,表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。,4.2,数据定义,二、删除索引,DROP INDEX ,例：索引示例。,create table,abc,(,a,int,b,int,);,create unique index,abcindex,on,abc(a,desc,);,drop index,abc.abcindex,;,create clustered index,abcindex,on,abc(a,desc,);,4.3,数据查询,SELECT ALL|DISTINCT,FROM ,WHERE ,GROUP BY HAVING,ORDER BY ASC|DESC;,4.3.1,单表查询,单表查询是指仅涉及一个表的查询。,一、选择表中的若干列,1.,查询指定列,例,1.,查询全体学生的学号和姓名。,select,sno,sname,from student;,例,2.,查询全体学生的姓名、学号、所在系。,select,sname,sno,sdept,from student;,4.3.1,单表查询,2.,查询全部列,例,3.,查询全体学生的详细记录。,select * from student;,3.,查询经过计算的值,例,4.,查询全体学生的姓名及其出生年份。,select sname,2012-sage from student;,例,5.,查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名。,select,sname,year,of birth:,2012-sage,lower(sdept) from student;,4.3.1,单表查询,二、选择表中的若干元组,1.,消除取值重复的行,例,6.,查询选修了课程的学生学号。,select,sno,from sc;,select distinct,sno,from sc;,2.,查询满足条件的元组,（,1,） 比较大小,例,7.,查询计算机科学系全体学生的名单。,select,sname,from student where,sdept,=CS;,4.3.1,单表查询,例,8.,查询所有年龄在,20,岁以下的学生姓名及其年龄。,select,sname,sage,from student where sage20;,例,9.,查询考试成绩有不及格的学生的学号。,select distinct,sno,from sc where grade60;,4.3.1,单表查询,（,2,）确定范围,例,10.,查询年龄在,20-23,岁（包括,20,岁和,23,岁）之间的学生的姓名、系别和年龄。,select,sname,sdept,sage,from student where sage between 20 and 23;,例,11.,查询年龄不在,20-23,岁之间的学生姓名、系别和年龄。,select,sname,sdept,sage,from student where sage not between 20 and 23;,4.3.1,单表查询,（,3,）确定集合,例,12.,查询计算机科学系（,CS,）、数学系（,MA,）和信息系（,IS,）学生的姓名和性别。,select,sname,ssex,from student where,sdept,in(CS,MA,IS,);,例,13.,查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。,select,sname,ssex,from student where,sdept,not,in(CS,MA,IS,);,4.3.1,单表查询,（,4,）字符匹配,谓词,LIKE,可以用来进行字符串的匹配。,NOT LIKE ESCAPE ,%,代表任意长度的字符串（长度可以为,0,）；,-,（下横线）代表任意单个字符。,4.3.1,单表查询,例,14.,查询学号为,200215121,的学生的详细情况。,select * from student where,sno,like 200215121;,例,15.,查询所有姓刘的学生的姓名、学号和性别。,select * from student where,sname,like ,刘,%;,例,16.,查询姓“欧阳”且全名为,3,个汉字的学生的姓名。,select,sname,from student where,sname,like ,欧阳,_;,4.3.1,单表查询,例,17.,查询名字中第,2,个字为“阳”字的学生的姓名和学号。,select,sname,sno,from student where,sname,like _,阳,%;,例,18.,查询所有不姓刘的学生姓名。,select,sname,from student where,sname,not like ,刘,%;,4.3.1,单表查询,例,19.,查询,DB_Design,课程的课程号和学分。,select,cno,ccredit,from course where,cname,like DB_Design escape ;,例,20.,查询以“,DB_”,开头，且倒数第,3,个字符为,i,的课程的详细情况。,select * from course where,cname,like DB_%i_ escape ;,4.3.1,单表查询,（,5,）涉及空值的查询,例,21.,查询缺少成绩的学生的学号和相应的课程号。,select,sno,cno,from sc where grade is null;,例,22.,查所有有成绩的学生学号和课程号。,select,sno,cno,from sc where grade is not null;,4.3.1,单表查询,（,6,）多重条件查询,例,23.,查询计算机科学系年龄在,20,岁以下的学生姓名。,select,sname,from student where,sdept,=CS and sage3;,4.3.2,连接查询,若一个查询同时涉及两个以上的表，则称之为连接查询。,一、等值与非等值连接查询,连接查询的,WHERE,子句用来连接两个表的条件称为连接条件或连接谓词。,.,其中比较运算符主要有：,=,、,、,=,、,=,、,!=(,或,),等。,此外连接谓词还可以使用下面形式：,.BETWEEN.AND,.,4.3.2,连接查询,当连接运算为,=,时，称为等值连接。使用其他运算符称为非等值连接。,连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。,例,33.,查询每个学生及其选修课程的情况。,select student.*,sc.*,from,student,sc,where,student.sno,=,sc.sno,;,4.3.2,连接查询,例,34.,对例,33,用自然连接完成。,select,student.sno,sname,ssex,sage,sdept,cno,grade,from,student,sc,where,student.sno,=,sc.sno,;,4.3.2,连接查询,二、自身连接,连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接，称为表的自身连接。,select,o,second.cpno,from course,first,course,second,where,first.cpno,=,o,;,4.3.2,连接查询,例,36.,以,student,为主体列出每个学生的基本情况及其选课情况。,select,student.sno,sname,ssex,sage,sdept,cno,grade,from student left outer join sc on,student.sno,=,sc.sno,4.3.2,连接查询,四、复合条件连接,例,37.,查询选修,2,号课程且成绩在,90,分以上的所有学生。,select,student.sno,sname,from,student,sc,where,student.sno,=,sc.sno,and,o,=2 and,sc.grade,90;,4.3.2,连接查询,四、复合条件连接,例,38.,查询每个学生的学号、姓名、选修的课程名及成绩。,select,student.sno,sname,cname,grade,from,student,sc,course,where,student.sno,=,sc.sno,and,o,=,o,;,4.3.3,嵌套查询,在,SQL,语言中，一个,SELECT-FROM-WHERE,语句称为一个查询块。将一个查询块嵌套在另一个查询块的,WHERE,子句或,HAVING,短语的条件中的查询称为嵌套查询。,select,sname,from student,where,sno,in (,select,sno,from sc,where,cno,=2,);,4.3.3,嵌套查询,一、带有,IN,谓词的子查询。,例,39.,查询与“刘晨”在同一个系学习的学生。,select,sno,sname,sdept,from student,where,sdept,in (,select,sdept,from student,where,sname,=,刘晨,);,4.3.3,嵌套查询,例,40.,查询选修了课程名为“信息系统”的学生学号和姓名。,select,sno,sname,from student,where,sno,in (,select,sno,from sc,where,cno,in (,select,cno,from course,where,cname,=,信息系统,),);,4.3.3,嵌套查询,子查询的查询条件不依赖于父查询，这类子查询叫作不相关子查询。,子查询的查询条件依赖于父查询，这类子查询称为相关子查询。,4.3.3,嵌套查询,二、带有比较运算符的子查询,例,41.,找出每个学生超过他选修课程平均成绩的课程号。,select,sno,cno,from sc x,where grade (,select,avg(grade,),from sc y,where,y.sno,=,x.sno,),4.3.3,嵌套查询,三、带有,any,或,all,谓词的子查询,子查询返回单值时可以用比较运算符，但返回多值时要用,ANY,或,ALL,谓词修饰符。而使用,ANY,或,ALL,谓词时则必须同时使用比较运算符。,例,42.,查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。,select,sname,sage,from student,where sage any (,select sage,from student,where,sdept,=CS,),and,sdept,CS,4.3.3,嵌套查询,例,43.,查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。,select,sname,sage,from student,where sage all (,select sage,from student,where,sdept,=CS,),and,sdept,CS,4.3.3,嵌套查询,例,45.,查询没有选修,1,号课程的学生姓名。,select,sname,from student,where not exists (,select *,from sc,where,sno,=,student.sno,and,cno,=1,),4.3.3,嵌套查询,例,46,查询选修了全部课程的学生姓名。,可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。,4.3.3,嵌套查询,select,sname,from student,where not exists (,select *,from course,where not exists(,select *,from sc,where,sc.sno,=,student.sno,and,o,=,o,),),4.3.3,嵌套查询,例,47.,查询至少选修了学生,200215122,选修的全部课程的学生号码。,本查询可以用逻辑蕴含来表达：查询学号为,x,的学生，对所有的课程,y,，只要,200215122,学生选修了课程,y,，则,x,也选修了,y,。,用,p,表示谓词“学生,200215122,选修了课程,y”,用,q,表示谓词“学生,x,选修了课程,y”,则上述查询为,4.3.3,嵌套查询,该查询可以转换为如下等价形式,它所表达的语义为：不存在这样的课程,y,学生,200215122,选修了,y,而学生,x,没有选。,4.3.3,嵌套查询,select distinct,sno,from sc x,where not exists (,select *,from sc y,where,y.sno,=200215122 and not exists(,select *,from sc z,where,z.sno,=,x.sno,and,o,=,o,),),4.3.4,集合查询,例,48.,查询计算机科学系的学生及年龄不大于,19,岁的学生。,select *,from student,where,sdept,=CS,union,select *,from student,where sage=19,4.3.4,集合查询,例,49.,查询选修了课程,1,或者选修了课程,2,的学生。,select,sno,from sc,where,cno,=1,union,select,sno,from sc,where,cno,=2,4.3.4,集合查询,例,50.,查询计算机科学系的学生与年龄不大于,19,岁的学生的交集。,select *,from student,where,sdept,=CS,intersect,select *,from student,where sage=19/sql server2000,不支持,intersect,4.3.4,集合查询,例,51,查询既选修了课程,1,又选修了课程,2,的学生。,select,sno,from sc,where,cno,=1,intersect,select,sno,from sc,where,cno,=2 /,sql,server2000,不支持,intersect,4.3.4,集合查询,例,52.,查询计算机科学系的学生与年龄不大于,19,岁的学生的差集。,select *,from student,where,sdept,=CS,except,select *,from student,where sage=19 /,sql,server2000,不支持,except,4.4,数据更新,数据更新操作有,3,种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。,4.4.1,插入数据,SQL,的数据插入语句,INSERT,通常有两种形式。,插入一个元组,插入子查询结果,4.4,数据更新,4.4.1,插入数据,一、插入元组,插入元组的,INSERT,语句的格式为,INSERT,INTO(,),VALUES(,);,4.4,数据更新,例,1.,将一个新学生元组（学号：,200215128,；姓名：陈冬；性别：男；所在系：,IS,；年龄：,18,岁）插入到,Student,表中。,insert,into,student(sno,sname,ssex,sdept,sage,),values(200215128,陈冬,男,IS,18),4.4,数据更新,例,2.,将学生张成民的信息插入到,Student,表中。,insert,into student,values(200215126,张成民,男,18,CS),4.4,数据更新,例,3.,插入一条选课记录（,200215128,，,1,）,insert,into,sc(sno,cno,),values(200215128,1),insert,into sc,values(200215128,1,null),4.4,数据更新,二、插入子查询结果,INSERT,INTO (,),子查询；,4.4,数据更新,例,4.,对每一个系，求学生的平均年龄，并把结果存入数据库。,首先在数据库中建立一个新表，其中一列存放系名，另一列 存放相应的学生平均年龄。,create table,Dept_age,(,sdept,char(5),Avg_age,smallint,);,4.4,数据更新,然后对,student,表按系分组求平均年龄，再把系名和平均年龄存入新表中。,insert,into,Dept_age(sdept,Avg_age,),select,sdept,avg(sage,),from student,group by,sdept,;,4.4,数据更新,4.4.2,修改数据,修改操作又称为更新操作，其语句一般格式为,UPDATE ,SET =,=,WHERE ;,4.4,数据更新,一、修改某一个元组的值,例,5.,将学生,200215121,的年龄改为,22,岁。,update student,set sage=22,where,sno,=200215121,4.4,数据更新,二、修改多个元组的值,例,6.,将所有学生的年龄增加,1,岁。,update student,set sage=sage+1;,4.4,数据更新,三、带子查询的修改语句,例,7.,将计算机科学系全体学生的成绩置零。,update sc,set grade=0,where CS=,(,select,sdept,from student,where,student.sno,=,sc.sno,),4.4,数据更新,4.4.3,删除数据,一般格式,DELETE,FROM ,WHERE ,；,4.4,数据更新,一、删除一个元组的值,例,8.,删除学号为,200215128,的学生记录,delete,from student,where,sno,=200215128;,4.4,数据更新,二、删除多个元组的值,delete,from sc;,4.4,数据更新,三、带有查询的删除语句,例,10.,删除计算机科学系所有学生的选课记录。,delete,from sc,where CS=,(,select,sdept,from student,where,student.sno,=,sc.sno,);,4.5,视图,视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。,视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一定的限制。,4.5.1,定义视图,一、建立视图,CREATE VIEW (,),AS ,WITH CHECK OPTION,子查询可以是任意复杂的,SELECT,语句。但通常不允许含有,ORDER BY,子句和,DISTINCT,短语。,WITH CHECK OPTION,表示对视图进行,UPDATE,INSERT,DELETE,操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）,4.5.1,定义视图,create view,is_student,as,select,sno,sname,ssex,from student,where,sdept,=IS,insert,into,is_student,values(200215129,王六,男,),4.5.1,定义视图,插入语句可以执行。,select * from,is_student,select * from student,4.5.1,定义视图,create view,is_student,as,select,sno,sname,ssex,from student,where,sdept,=IS,with check option,4.5.1,定义视图,insert,into,is_student,values(200215129,王六,男,),服务器,:,消息,550,，级别,16,，状态,1,，行,1,试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了,WITH CHECK OPTION,，而该操作的一个或多个结果行又不符合,CHECK OPTION,约束的条件。,语句已终止。,4.5.1,定义视图,子查询中带有,order by,子句,CREATE VIEW FSTUDENT,AS,SELECT * FROM STUDENT,WHERE SSEX=,女,order by sage,服务器,:,消息,1033,，级别,15,，状态,1,，过程,FSTUDENT,，行,5,除非同时指定了,TOP,，否则,ORDER BY,子句在视图、内嵌函数、派生表和子查询中无效。,4.5.1,定义视图,CREATE VIEW FSTUDENT,AS,SELECT top 10 * FROM STUDENT,WHERE SSEX=,女,order by sage,4.5.1,定义视图,子查询中含有,DISTINCT,短语,create view,abc,as,select distinct,sno,from sc,where grade is null,4.5.1,定义视图,组成视图的属性列名或者全部省略或者全部指定。如果省略了视图的各个属性列名，则隐含该视图由子查询中的,SELECT,子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名。,（,1,）某个目标列不是单纯的属性名，而是聚集函数或列表达式；,4.5.1,定义视图,create view test1,as,select,sno,count,(*),from sc,group by,sno,服务器,:,消息,4511,，级别,16,，状态,1,，过程,test1,，行,3,创建视图或函数失败，因为没有为第,2,列指定列名。,4.5.1,定义视图,create view test2,as,select sname,2012-sage,from student,服务器,:,消息,4511,，级别,16,，状态,1,，过程,test2,，行,3,创建视图或函数失败，因为没有为第,2,列指定列名。,4.5.1,定义视图,（,2,）多表连接时选出了几个同名列作为视图的字段,（,sql,server 2000,中不符合）,create view test3,as,select,student.sno,cno,grade,from,student,sc,where,student.sno,=,sc.sno,4.5.1,定义视图,（,3,）需要在视图中为某个列启用新的更适合的名字。,create view test4(,姓名,年龄,),as,select,sname,sage,from student,4.5.1,定义视图,例,1.,建立信息系学生的视图。,create view,is_student,as,select,sno,sname,sage,from student,where,sdept,=IS,4.5.1,定义视图,例,2.,建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。,create view IS_Student1,as,select,sno,sname,sage,from student,where,sdept,=IS,with check option,4.5.1,定义视图,例,3.,建立信息系选修了,1,号课程的学生的视图。,create view IS_S1(sno,sname,grade),as,select,student.sno,sname,grade,from,student,sc,where,sdept,=IS and,student.sno,=,sc.sno,and,o,=1,4.5.1,定义视图,例,4.,建立信息系选修了,1,号课程且成绩在,90,分以以上的学生的视图。,create view IS_S2,as,select,sno,sname,grade,from IS_S1,where grade=90,4.5.1,定义视图,例,5.,定义一个反映学生出生年份的视图。,create view,BT_S(sno,sname,sbirth,),as,select sno,sname,2012-sage,from student,4.5.1,定义视图,例,6.,将学生的学号及他的平均成绩定义为一个视图。,create view,S_G(Sno,Gavg,),as,select,sno,avg(grade,),from sc,group by,sno,4.5.1,定义视图,若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这视图为行列子集视图。,在基本表中并不实际存在的列称为虚拟列。,带虚拟列的视图也称为带表达式的视图,。,4.5.1,定义视图,二、删除视图,DROP VIEW CASCADE;,例,8. drop view,bt_s,4.5.2,查询视图,例,9.,在信息系学生的视图中找出年龄小于,20,岁的学生。,select,sno,sage,from,is_student,where sage=90,4.5.3,更新视图,例,12,将信息系学生视图,IS_Student,中学号为,200215122,的学生姓名改为“刘晨”。,update,is_student,set,sname,=,刘辰,where,sno,=200215122,4.5.3,更新视图,例,13,向信息系学生视图,is_student,中插入一个新的学生记录，其中学号为,200215129,，姓名为赵新，年龄为,20,岁。,insert,into,is_student,values(200215129,赵鑫,20),4.5.3,更新视图,例,14.,删除信息系学生视图,IS_Student,中学号为,200215129,的记录。,delete,from,is_student,where,sno,=200215125,4.5.3,更新视图,在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。,把视图,S_G,中学号为,200215122,的学生的平均成绩改成,90,分。,update,s_g,set,gavg,=90,where,sno,=200215121,服务器,:,消息,4403,，级别,16,，状态,1,，行,1,视图或函数,s_g,不可更新，因为它包含聚合。,4.5.3,更新视图,在视图中修改数据的几个问题,对于多表视图不能使用,DELETE,语句,create view,score_view(sname,cno,grade,),as,select,sname,cno,grade,from,student,sc,where,student.sno,=,sc.sno,4.5.3,更新视图,delete,from,score_view,服务器,:,消息,4405,，级别,16,，状态,1,，行,1,视图或函数,score_view,不可更新，因为修改会影响多个基表。,4.5.3,更新视图,除非底层表的所有非空列都已经在视图中出现否则不能使用,INSERT,语句。有这个限制的原因是,SQL,不知道应该将什么数据插入到,NOT NULL,限制列中。,create table c,(,a,int,not null,b,int,not null,),4.5.3,更新视图,create view,cview,as,select a,from c,insert into,cview,values(9),服务器,:,消息,515,，级别,16,，状态,2,，行,1,无法将,NULL,值插入列,b,，表,test.dbo.c,；该列不允许空值。,INSERT,失败。,语句已终止。,4.5.3,更新视图,如果对一个归并的表格插入或更新记录那么所有被更新的记录必须属于同一个物理表,CREATE TABLE STUDENT,(,SNO INT,SNAME CHAR(10),SAGE INT,SDEPART CHAR(10),),CREATE TABLE SCSC,(,SNO INT,CNO INT,GRADE INT,),4.5.3,更新视图,CREATE VIEW STUABC,AS,SELECT SNAME,CNO,GRADE,FROM STUDENT,SCSC,WHERE STUDENT.SNO=SCSC.SNO,INSERT INTO STUABC,VALUES(WANG,1,90),服务器,:,消息,4405,，级别,16,，状态,2,，行,1,视图或函数,STUABC,不可更新，因为修改会影响多个基表。,4.5.3,更新视图,如果在创建视图时使用了,DINTINCT,子句，那么就不能插入或更新这个视图中的记录,create view,scview,as,select distinct,sno,from sc,insert,into,scview,values(1),服务器,:,消息,4404,，级别,16,，状态,1,，行,1,视图或函数,scview,不可更新，因为其定义中包含,DISTINCT,子句。,4.5.3,更新视图,不能更新视图中的虚拟列,(,它是用计算字段得到的,),create view,stuage(st_id,age,),as,select sno,2012-sage as age,from student,update,stuage,set age=20,where,st_id,=1,服务器,:,消息,4406,，级别,16,，状态,2,，行,1,对视图或函数,stuage,的更新或插入失败，因其包含有派生域或常量域。,4.5.4,视图的作用,1,视图能够简化用户的操作,2,视图使用户能以多种角度看待同一数据,3,视图对重构数据库提供了一定程度的逻辑独立性,Student(Sno,Sname,Ssex,Sage,Sdept,),分为,SX(Sno,Sname,Sage,),SY(Sno,Ssex,Sdept,),create view,student(sno,sname,ssex,sage,sdept,),as,select,sx.sno,sx.sname,sy.ssex,sx.sage,sy.sdept,from,sx,sy,where,sx.sno,=,sy.sno,4.5.4,视图的作用,4,视图能够对机密数据提供安全保护,5,适当的利用视图可以更清晰的表达查询。,练习,（,1,）查询所有不姓刘的学生记录。,（,2,）查询所有有成绩的学生学号和课号。,（,3,）查询选修了课程的学生人数。,（,4,）计算,1,号课程的学生平均成绩。,（,5,）查询选修了,1,号课程，且成绩在,80,分以上的所有学生的学号、姓名。,（,6,）查询选修了,3,门以上课程的学生学号。,（,7,）找出每个学生超过他选修课程平均成绩的课程号。,（,8,）查询所有选修了,1,号课程的学生姓名。,练习,（,9,）将,1,号学生的年龄改为,22,岁。,（,10,）删除所有学生的选课记录。,（,11,）将学生的学号及他的平均成绩定义为一个视图。,