数据库第三章

SQL概述数据定义查询更新视图数据控制关系数据库标准语言SQL第3章1

SQL语言的特点掌握SQL语言的关系表定义及更新操作掌握SQL语言强大的查询功能掌握SQL语言的关系表更新操作第3章重点用SQL语言正确完成复杂查询第3章难点2●

SQL的发展及现状

1974年，由Boyce和Chamberlin提出。1975－1979，IBM公司SanJoseResearchLab研制了关系数据库管理系统原型SystemR并实现了这种语言。1986年美国国家标准局(ANSI)的数据库委员会X3H2批准SQL作为关系数据库语言的美国标准（SQL-86）。ANSI不断修改和完善SQL标准，公布了SQL-89、SQL-92(SQL2)、SQL-99(SQL3)大部分DBMS产品都支持SQL，成为操作数据库的标准语言33.1SQL概述3.1.1SQL的特点SQL是一种介于关系代数和关系演算之间的结构化查询语言。综合统一

集DDL、DML、DCL的功能于一体，可以独立完成数据库生命周期中的全部活动。

高度非过程化

无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。

面向集合的操作方式

操作对象、查找结果、插入、删除、更新操作的对象可以是元组集合。以同一种语法结构提供两种使用方式

可独立的用于联机交互的使用方式，用户可在终端键入SQL命令对数据库进行操作；SQL语句可嵌入到高级语言程序中，供程序员设计程序时使用。

语言简单，易学易用

核心功能只有9个动词。4数据查询(DataQuery)SQL语言

数据操纵(DataManipulation)数据定义(DataDefinition)数据控制(DataControl)SQL功能数据查询数据定义数据操纵数据控制动词SELECTCREATE,DROP,ALTERINSERT,UPDATE,DELETEGRANT,REVOKE表3.1SQL语言的动词5SQL用户基本表1视图1视图2基本表2基本表3基本表4存储文件1存储文件2外模式模式内模式图3.1SQL对关系数据库模型的支持3.1.2SQL语言的基本概念6基本概念：用户可以用SQL语言对视图(View)和基本表(BaseTable)进行查询等操作，在用户观点里，视图和表一样，都是关系。

视图是从一个或多个基本表中导出的表，本身不存储在数据库中，只有其定义，可以将其理解为一个虚表。

基本表是本身独立存在的表，每个基本表对应一个存储文件，一个表可以带若干索引，存储文件及索引组成了关系数据库的内模式。7SQL

Server数据库创建方法83.2数据定义操作对象表视图索引操作方式创建CREATETABLE删除DROPTABLE修改ALTERTABLECREATEINDEXDROPINDEXCREATEVIEWDROPVIEW表3.2SQL的数据定义语句视图是基于基本表的虚表，索引是依附于基本表的，因此，SQL通常不提供修改视图定义和索引定义的操作。（删除后重建；Oracle允许直接修改视图定义）操作对象表视图索引操作方式创建CREATETABLE删除DROPTABLE修改ALTERTABLECREATEINDEXDROPINDEXCREATEVIEWDROPVIEW表3.2SQL的数据定义语句93.2.1定义、删除与修改基本表定义基本表格式：CREATETABLE<表名>

(<列名><数据类型>[列级完整性约束条件] [,<列名><数据类型>[列级完整性约束条件]]……

[,<表级完整性约束条件>])；建立一个新表，表中无记录10

CREATETABLE

Student

(

Sno

CHAR(5)NOTNULL

UNIQUE,/*列级完整性约束条件*/

Sname

CHAR(20)UNIQUE,

Ssex

CHAR(1),

Sage

INT,

Sdept

CHAR(15))；例1：建立学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性构成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。11

CREATETABLES (S#CHAR(6)NOTNULL,/*学号*/

SNAMECHAR(8)NOTNULL,/*姓名*/

SAGESMALLINT,/*年龄*/

SDCHAR(10),/*系名*/

SEXCHAR(2)DEFAULT

'男'

CHECK((SEX='男')OR(SEX='女')),/*性别*/

PRIMARYKEY(S#))；例2：建立学生S、课程C、选课SC三个表S表：12

CREATETABLEC (C#CHAR(6)NOTNULL,/*课程号*/

CNAMECHAR(30)NOTNULL,/*课程名*/

TNAMECHAR(8),/*教师姓名*/

PC#CHAR(6),/*先行课*/

PRIMARYKEY(C#))；例2：建立学生S、课程C、选课SC三个表C表：13CREATETABLESC(S#CHAR(6)NOTNULL,/*学号*/

C#CHAR(6)NOTNULL,/*课程号*/

GRSMALLINT,/*成绩*/PRIMARYKEY(S#,C#),FOREIGNKEY(S#)REFERENCESS(S#),FOREIGNKEY(C#)REFERENCESC(C#),CHECK((GRISNULL) OR(GRBETWEENOAND100)))；例2：建立学生S、课程C、选课SC三个表SC表：14例3：设关系S_G(S#,AVG_G),把平均成绩大于80的男生的学号及平均成绩存入S_G中。CREATETABLES_G(S#CHAR(6)NOTNULL,

AVG_GSMALLINTDEFAULTNULL)AS(SELECTS#，AVG(ALLGRADE)FROMSCWHERES#IN

(SELECTS#FROMSWHERESEX=‘男’)

GROUPBYS#

HAVING

AVG(ALLGRADE)>80)15修改基本表改变表名增加列改变列的数据类型删除列的约束删除列改变列名基本表的修改操作：16格式：ALTERTABLE<表名>

[ADD

<新列名><数据类型>[完整性约束]][ADD

完整性约束][DROP<列名>

][DROP<完整性约束名>] [ALTERCOLUMN<列名><数据类型>] [RENAME<旧表名>TO<新表名>];

增加新列增加新的完整性约束条件删除指定列删除指定的完整性约束条件修改列的定义（列名和数据类型）改变表名17例4：在Student表中增加“入学时间”列，数据类型为日期型。

ALTERTABLE

Student

ADD

Scome

DATETIME；例5：把年龄的数据类型改为半字长整数.ALTERTABLEStudentALTERCOLUMNSageSMALLINT；例6：删除学生姓名必须取唯一值的约束。

ALTERTABLEStudentDROPCONSTRAINTNAME；18删除基本表格式：DROPTABLE<表名>；删除一个表，及与该表相关的索引、视图、码和外部码。例7：删除Student表。

DROPTABLEStudent;193.2.2建立与删除索引建立索引是加快查询速度的有效手段，一个基本表上可建立一个或多个索引，以提供多种存取路径，加快查找速度。建立与删除索引由DBA或表的属主负责完成，用户不必也不能选择索引。20建立索引格式：CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名>[<次序>],[,<列名>[<次序>]]…)；

UNIQUE

表示索引值唯一。

CLUSTER

表示索引是聚簇索引,指索引项的顺序与表中记录的物理顺序一致。索引一旦建立，交由系统使用和维护。对指定的表的列建立索引。21例8：为学生－课程数据库中的S,C,SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXSIDXONS(S#);CREATEUNIQUEINDEXCIDXONC(C#);CREATEUNIQUEINDEXSCIDXONS(S#ASC,C#DESC);22例9：为学生Student表建立聚簇索引（按学生姓名升序建索引）。CREATECLUSTERINDEXSNAMEIDXONStudent(Sname);一个基本表最多只能建立一个聚簇索引。建立索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。23删除索引格式：

DROPINDEX<表名.索引名>；例10：删除Student表的SNAMEIDX索引。

DROPINDEX

Student.SNAMEIDX

；243.3查询

基本结构

SelectA1,A2,...,An

FromR1,R2,...,Rm

WherePSelectWhereFrom

πA1,A2,...,An(σp(R1×R2×...×Rm))数据查询是数据库应用的核心功能。25格式

SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]……

FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；26SELECT…FROM常用语句执行过程

SELECT…⑤

投影

FROM…①TABLE→内存

WHERE…②

选取元组

GROUP…③

分组

HAVING…④

选择分组

[{UNION|…}⑥

查徇结果的集合运算

SELECT…]①～⑤

ORDERBY……⑦

排序输出273.3.1单表查询

选择表中的若干列

选择表中的若干元组

对查询结果排序

使用集函数

对查询结果分组281.查询指定列例1.查询全体学生的学号及姓名。学生表：Student(Sno,Sname,Ssex,Sage,Sdept)查询各列的先后顺序可以与表中的顺序不一致。例2.查询全体学生的姓名、学号、所在系。SELECTSno,SnameFROMStudent；SELECTSname,Sno,Sdept FROMStudent；29302.查询全部列学生表：Student(Sno,Sname,Ssex,Sage,Sdept)例3.查询全体学生的全部信息。SELECTSno,Sname,Ssex,Sage,Sdept FROMStudent；

等价于SELECT*FROMStudent;31323.查询经过计算的值学生表：Student(Sno,Sname,Ssex,Sage,Sdept)例4.查询全体学生的姓名及其出生年份。SELECTSname,2006-Sage FROMStudent；33例5.查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth:',2006-Sage,LOWER(Sdept)FROMStudent；SELECTSnameNAME,'YearofBirth:'

BIRTH,2006-Sage

BIRTHDAY,LOWER(Sdept)DEPARTMENT

FROMStudent；34351.消除取值重复的行例6.查询选修了课程的学生学号。学生课程表：SC(Sno,Cno,Grade)消除重复的行SELECTSnoFROMSC；SELECTDISTINCTSno FROMSC；36372.查询满足条件的元组Where子句——运算符比较：<、<=、>、>=、=、<>、!=、!>、!<not+上述比较运算符确定范围：BetweenAandB、NotBetweenAandB确定集合：IN、NOTIN字符匹配：LIKE、NOTLIKE空值：ISNULL、ISNOTNULL多重条件：AND、OR38例7.查询计算机系全体学生的名单。(1)比较大小SELECTSnameFROMStudentWHERESdept='CS'；3940例8.查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname,Sage FROMStudentWHERESage<

20；SELECTSname,Sage FROMStudentWHERENOTSage>=

20；或4142例9.查询考试成绩有不及格的学生的学号。SELECTDISTINCTSno FROMSCWHEREGrade<60；4344例10.查询年龄在20～23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。(2)确定范围SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23；SELECTSname,Sdept,SageFROMStudentWHERESage>=20ANDSage<=

23；4546SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23；例11.查询年龄不在20～23岁之间的学生姓名、系别和年龄。SELECTSname,Sdept,SageFROMStudentWHERESage<20ORSage>

23；4748例12.查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。(3)确定集合SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS')；SELECTSname,SsexFROMStudentWHERESdept=

'IS'

ORSdept=

'MA'ORSdept=

'CS'；4950例13.查询既不是信息系(IS)、数学系(MA)也不是计算机科学系(CS)学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','MA','CS')；SELECTSname,SsexFROMStudentWHERESdept!=

'IS'ANDSdept!=

'MA'

ANDSdept!=

'CS'；5152(4)字符匹配Where子句——Like格式:[NOT]LIKE

'<匹配串>'[ESCAPE'<换码字符>']%:表示任意长度(长度≥0）的字符串。_:表示单个的任意字符ESCAPE'\':表示\为换码字符，匹配串中紧跟在\后面的字符‘%’或‘_’，被定义为普通字符(不作通配符用)53例14.查询学号为95001的学生的详细情况。如果LIKE后面的匹配串中不含通配符，则可以用＝运算符取代LIKE谓词；用!=或<>取代NOTLIKE谓词。SELECT*FROMStudentWHERESnoLIKE

'95001'；SELECT*FROMStudentWHERESno=

'95001'；等价于5455例15.查询所有姓王的学生的姓名、学号和性别。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE

'王％'；5657例16.查询姓李，且姓名为两个汉字的学生姓名。SELECTSnameFROMStudentWHERESnameLIKE'李_'

；5859例17.查询名字中第二个字为“晨”字的学生姓名和学号。SELECTSname,SnoFROMStudentWHERESnameLIKE

'_晨%

'

；6061例18.查询所有不姓刘的学生姓名。SELECTSnameFROMStudentWHERESnameNOTLIKE

'刘%

'

；6263例19.查询DB_Design课程的课程号和学分。SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE

'\'

；64例20.查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKE

'DB\_%i__'

ESCAPE'\'；65(5)涉及空值的查询例21.某些学生选修课后没有参加考试，所以有选课录，但没有成绩。查询缺少成绩的学生的学号和相应课程号。SELECTSno,CnoFROMSCWHEREGradeISNULL；6667例22.查询所有有成绩的学生学号和课程号。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL；6869(6)多重条件查询例23.查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept='CS'

ANDSage<20；7071例24.查询信息系(IS)、数学系(MA)和计算机科学系(CS)的男学生的姓名和年龄。逻辑运算符AND的优先级比OR高，可通过括号改变优先级。SELECTSname,SageFROMStudentWHERE(Sdept='IS'

ORSdept='MA'

OR

Sdept='CS')ANDSsex='男'；72733.对查询结果的排序例25.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。SELECTSno,GradeFROMSCWHERECno='3'

ORDERBYGradeDESC；7475例26.查询全体学生情况，查询结果按所在系的系号升序排列，同在一个系的按年龄降序排列。SELECT*FROMStudent

ORDERBYSdeptASC,SageDESC；SELECT*FROMStudent

ORDERBYSdept,SageDESC；76774.使用集函数主要有：

COUNT([DISTINCT|ALL]*)统计元组个数

COUNT([DISTINCT|ALL]<列名>)统计一列中值的个数

SUM([DISTINCT|ALL]<列名>)

计算一列值的总和（此列必须是数值型）

AVG([DISTINCT|ALL]<列名>)计算一列值的平均值（此列必须是数值型）

MAX([DISTINCT|ALL]<列名>)求一列值中的最大值

MIN([DISTINCT|ALL]<列名>)求一列值中的最小值78例27.查询学生总人数。SELECTCOUNT

(*)FROMStudent;例28.查询选修了课程的学生人数。SELECTCOUNT

(DISTINCTSno)FROMSC;79例29.计算2号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno='2';例30.查询选修2号课程的学生最高成绩。SELECTMAX(Grade)FROMSCWHERECno='2';80815.对查询结果分组例31.求各个课程号及相应的选课人数。SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;82例32.查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVING

COUNT(*)>=3;HAVING用于分组后按一定条件对这些分组进行筛选。

WHERE子句作用于基本表或视图，从中选择满足条件的元组

HAVING短句作用于组，从中选择满足条件的组8384SQL

Server数据库查询演示853.3.2连接查询

等值与非等值连接查询

自身连接

外连接

复合条件连接861.等值与非等值连接查询连接条件一：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>连接条件二：

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

比较运算符主要有：＝、>、<、>=、<=、!=。连接查询中用来连接两个表的条件称为连接条件或连接谓词。连接谓词中的列名称为连接字段，其各字段应是可比的。87例33.查询每个学生及其选修课程的情况。

等值连接SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=

SC.Sno;自然连接：在等值连接中把目标列中重复的属性列去掉。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=

SC.Sno;88892.自身连接例34.查询每门课的间接先修课。一个表与其自身进行连接SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=

SECOND.Cno;90913.外连接在连接条件的某侧加上(＊)或(+)，表示该侧所对应的表中可形成一个各数据项均为空值的万能替代行，用来与另一侧对应的表中所有不满足条件的元组进行连接。外连接符(＊)或（+）出现在左侧称为右外连接、出现在右侧称为左连接、两侧都出现的称为全外连接。92例35.查询每个学生及其选修课的情况。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMSCRIGHTJOINStudentONStudent.Sno=SC.Sno;93944.复合条件连接例36.查询选修了2号课程且成绩在90分以上的所有学生。WHERE子句中有多个连接条件SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='2'

ANDSC.Grade>=90;95例37.查询每个学生的学号、姓名、选修课程名及成绩。多表连接SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;96973.3.3嵌套查询在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。例如：SELECTSnameFROMStudentWHERESnoINSELECTSnoFROMSCWHERECno='2';外层查询内层查询不能使用ORDERBY子句98

带有IN谓词的子查询

带有比较运算符的子查询

带有ANY或ALL谓词的子查询

带有EXISTS谓词的子查询3.3.3嵌套查询99例38.查询与“刘晨”在同一个系学习的学生。

SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='刘晨');(1)首先在Student关系中找出刘晨所在的系，结果为'IS'。(2)其次在Student关系中找出'IS'系的学生1.带有IN谓词的子查询或SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨'；100101例39.查询选修了课程名为“信息系统”的学生学号和姓名。

SELECTSno,Sname FROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='信息系统'));(1)首先在Course关系中找出“信息系统的课程号”，结果为3。(2)其次在SC关系中找出选修3号课程的学生(3)最后在Student关系中取出学号和姓名。或SELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='信息系统'

；1021032.带有比较运算符的子查询例40.查询与“刘晨”在同一个系学习的学生。内存查询返回的是单值时，可以用比较运算符；子查询要跟在比较符之后。SELECTSno,Sname,SdeptFROMStudentWHERESdept＝(SELECTSdeptFROMStudentWHERESname='刘晨');1041053.带有ANY或ALL谓词的子查询子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时必须同时使用比较运算符。其语义为：>(>=、=)ANY

大于（大于等于、等于）子查询结果中的某个值>(>=、=)ALL

大于（大于等于、等于）子查询结果中的所有值<(<=)ANY

小于（小于等于）子查询结果中的某个值<(<=)ALL

小于（小于等于）子查询结果中的所有值!=(或<>)ANY

不等于子查询结果中的某个值!=(或<>)ALL

不等于子查询结果中的任何一个值106例41.查询其他系中比信息系某一学生年龄小的学生姓名和年龄。

SELECTSname,Sage FROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';或

SELECTSname,Sage FROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';107108例42.查询其他系中比信息系所有学生年龄小的学生姓名和年龄。

SELECTSname,Sage FROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';或

SELECTSname,SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';109＝<>或!=<<=>>=ANYIN__<MAX<=MAX>MIN>=MINALL__NOTIN<MIX<=MIN>MAX>MAX表3-4ANY,ALL谓词与集函数及IN谓词的等价转换关系集函数实现子查询比直接用ANY或ALL查询效率更高。1104.带有EXISTS谓词的子查询不相关子查询：子查询的查询条件不依赖于父查询的子查询。相关子查询(CorrelatedSubquery)：子查询的查询条件依赖于外层父查询的某个属性值的子查询。带EXISTS的子查询就是相关子查询

EXISTS表示存在量词带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值

'True'

或'False'111例43-1.查询所有选修了1号课程的学生姓名。

SELECTSnameFROMStudentWHERESnoIN(SELECTSNOFROMSCWHERESC.Cno='1');不相关子查询112例43-2.查询所有选修了1号课程的学生姓名。

SELECTSname FROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');执行过程：

先在外层查询中取Student表的第一个元组，用该元组的相关的属性值（在内层WHERE子句中给定的）处理内层查询，若外层的WHERE子句返回'TRUE'值，则此元组送入结果的表中。然后再取下一个元组；重复上述过程直到外层表的元组全部遍历一次为止。相关子查询113114说明：不关心子查询的具体内容，因此用SELECT*

Exists+子查询用来判断该子查询是否返回元组当子查询的结果集非空时，Exists

为'True'当子查询的结果集为空时，Exists

为'False'

NOTEXISTS

：若子查询结果为空，返回'TRUE'值，否则返回'FALSE'115例44.查询没有选修1号课程的学生姓名。

SELECTSname FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');或

SELECTSnameFROMStudentWHERESnoNOTIN(SELECTSNOFROMSCWHERESC.Cno='1');不相关子查询相关子查询116117例45.查询与刘晨在同一个系学习的学生。

SELECTSno,Sname,Sdept FROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='刘晨');或

SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='刘晨');118119SQL无全称量词，但可以把全称量词转化为等价的存在量词形式。例46：“查询选修全部课程的学生姓名”(∀x)P≡!(∃x(!P))原命题等价于：“查询这样的学生a，不存在某个课程x，这名学生没有选修”

120例46.查询选修了全部课程的学生姓名。

SELECTSname FROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));这门课他没选这样的课是不存在的121122例47.查询至少选修了学生95002选修的全部课程的学生号。SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS

(SELECT*FROMSCSCY

WHERESCY.Sno='95002' ANDNOTEXISTS

(SELECT* FROMSCSCZ WHERESCZ.Sno=SCX.Sno ANDSCZ.Cno=SCY.Cno))； 1233.3.4集合查询例48.查询计算机系的学生或者年龄不大于19岁的学生。SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19124125例49.查询选修了课程1或选修课程2的学生。SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2';126127例50.查询计算机系的学生与年龄不大于19岁的学生的交集。SELECT*FROMStudentWHERESdept='CS'ANDSage<=19;例51.查询选修了课程1的学生集合与选修课程2的学生集合的交集。SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2');128例52.查询计算机系的学生与年龄不大于19岁的学生的差集。SELECT*FROMStudentWHERESdept='CS'ANDSage>19;例53.查询选修课程1但没有选修课程2的学生。SELECTSnoFROMSCWHERECno='1'ANDSnoNOTIN(SELECTSnoFROMSCWHERECno='2');1293.3.5SELECT语句的一般格式SELECT[ALL|DISTINCT]<目标列表达式>[别名][,<目标列表达式>[别名]]……FROM<表名或视图名>[别名][,<表名或视图名>[别名]]……[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]];1303.4数据更新

插入操作

INSERT

修改操作

UPDATE

删除操作

DELETE1313.4.1插入数据1.插入单个元组格式：INSERTINTO<表名>[(<列名1>[,<列名2>]…)]

VALUES(<常量1>[,<常量2>]…)；插入一已知元组的全部列的常量

插入一已知元组的部分列的常量表定义时说明了NOTNULL的属性列不能取空值如果INTO子句没有指明任何列名，则新插入的记录必须在每个属性列上均有值。132插入一已知元组的全部列常量

INSERTINTOStudent

VALUES('95005','陈冬','男',18,'CS')；例1：将一个新生记录插入学生表。

INSERTINTOSC(Sno，Cno)

VALUES('95005','1')；插入一已知元组的部分列常量例2：新增一条选课记录1331342.插入子查询结果格式：INSERTINTO<表名>[(<列名1>[,<列名2>]…)]

子查询；CREATETABLEDeptage(SdeptCHAR(15)AvgageSMALLINT);INSERTINTODeptage(Sdept,Avgage)SELECTSdept,AVG(Sage)

FROMStudentGROUPBYSdept;例3：对每个系，求学生的平均年龄，并把结果存入数据库。1351363.4.2修改数据格式：

UPDATE<表名>

SET<列名>=<表达式>[,<列名>=<表达式>

]…[WHERE<条件>]；1371.修改某一个元组的值UPDATEStudentSETSage=22WHERESno='95001';例4：将学生95001的年龄改为22。2.修改多个元组的值UPDATEStudentSETSage=Sage+1；例5：将所有学生的年龄都增加1。1381393.带子查询的修改语句UPDATESCSETGrade=0WHERESnoIN

(SELECTSno

FROMStudent

WHERESdept='CS')；例6：将计算机科学系学生的成绩都置零。1401413.4.3删除数据格式：DELETEFROM<表名>

[WHERE<条件>]；只能对整个元组操作，不能只删除某些属性上的值。只能对一个关系起作用，若要从多个关系中删除元组，则必须对每个关系分别执行删除命令。

从关系R中删除满足P的元组，只是删除数据，而不是定义。1421.删除某一个元组的值DELETE

FROMStudentWHERESno='95005'；例7：删除学号为95005的学生记录。2.删除多个元组的值DELETE

FROMSC；例8：删除所有的学生选课记录。1433.带子查询的删除语句DELETE

FROMSCWHERESnoIN

(SELECTSno

FROMStudent

WHERESdept='CS')；例9：删除计算机科学系所有学生的选课记录。1444.更新操作与数据库的一致性首先DELETE

FROMSCWHERESno='95005'；其次DELETE

FROMStudentWHERESno='95005'；例10：删除学号为95005的学生记录。事务(Transaction)：保证语句要么都做，要么都不做，以保证关系的完整性。删除表中元组的策略：(1)自动删除参照表中相应的元组；(2)检查参照表中是否存在相应的元组，如果存在相应的元组，如果存在，则操作失败。1453.5视图视图是从一个或几个表(或视图)导出的表。视图是一个虚表数据库中只存放视图的定义视图对应的数据仍存放在原来的表中随着表中数据的变化，视图的数据随之改变。对视图的查询与基本表一样对视图的更新将受到一定的限制1463.5视图

定义视图

查询视图

更新视图

视图的作用147视图概念视图是一个虚表数据库中只存放视图的定义视图对应的数据仍存放在原来的表中随着表中数据的变化，视图的数据随之改变。对视图的查询与基本表一样对视图的更新将受到一定的限制基本表1基本表2视图视图概念示意图3.5.1定义视图148建立视图格式：CREATEVIEW<视图名>[(<列名>[,<列名>]…)]

AS

子查询[WITHCHECKOPTION]；下述必须指定全部列名：某个目标列是集函数或表达式多表连接时，目标列中出现同名列需在视图中为某列用新的名字在定义视图时要么指定全部视图列，要么全部省略不写；如果省略了视图的属性列名，则视图的列名与子查询列名相同。子查询中通常不包含ORDERBY和DISTINCT子句。

WITHCHECKOPTION：对视图进行UPDATE、INSERT、DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件。149

行列子集视图：从单个基本表导出，保留基本表的码，但去掉其它的某些列和部分行的视图。

表达式视图：带虚拟列（经过各种计算派生出的数据所设置的派生属性列）的视图。

分组视图：子查询目标表带有组函数或子查询带有GROUPBY子句的视图。视图分类

150例1：建立信息系学生视图

CREATEVIEWIS_Student

AS

SELECTSno，Sname，Ssex，Sage FROMStudent WHERESdept='IS'；(行列子集视图)建立视图的结果是把视图定义存入数据字典，并不执行SELECT语句；只在对视图查询时，才按其定义从基本表中将数据查出。151例2：建立计算机系学生视图，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生。

CREATEVIEWCS_Student

AS

SELECTSno，Sname，Ssex，Sage FROMStudent WHERESdept='CS'

WITHCHECKOPTION；由于加上了WITHCHECKOPTION子句，以后对该视图进行插入、修改、删除操作时，DBMS会自动加上Sdept=‘CS’的条件。152例3：建立信息系选修了1号课程的学生的视图。

CREATEVIEWIS_S1(Sno，Sname，Grade)

AS

SELECTStudent.Sno，Sname，Grade FROMStudent,SC WHERESdept='IS'ANDSC.Cno='1'

ANDStudent.Sno=SC.Sno；建立在多个基本表上153例4：建立信息系选修了1号课程且成绩在90分以上的学生的视图。

CREATEVIEWIS_S2

AS

SELECTSno，Sname，Grade FROMIS_S1 WHEREGrade>=90；建立在已定义的视图上154例5：定义一个反映学生出生年份的视图。

CREATEVIEWBT_S(Sno,Sname,Sbirth)

AS

SELECTSno，Sname，2006-Sage FROMStudent；

(表达式视图)虚拟列155例6：将学生的学号及他的平均成绩定义为一个视图。

CREATEVIEWS_G(Sno,Gavg)

AS

SELECTSno，AVG(Grade) FROMSC

GROUPBYSno；

(分组视图)156例7：将Student表中所有女生记录定义为一个视图。CREATEVIEWF_Student(stdnum,name,sex,age,dept)

AS

SELECT* FROMStudent

WHERESsex='女'；如果修改了基本表Student的结构，则视图与基本表之间的映象关系被破坏，视图就不能正确工作。为避免出现该类问题，最好在修改基本表之后删除有该基本表导出的视图，然后重建（同名）视图。157删除视图格式：DROPVIEW<视图名>；例8：删除视图IS_S1。DROPVIEWIS_S2；

（IS_S2由IS_S1导出）

DROPVIEWIS_S1；1583.5.2查询视图视图消解(ViewResolution)在对视图查询时，DBMS将进行有效性检查（表及视图）。若存在，则从数据字典中取出视图定义，并把定义中的子查询与用户查询结合起来转换为等价的对基本表的查询，然后再执行。159例1：在信息系学生的视图中查找年龄小于20的学生。

SELECTSno，Sname FROMIS_Student WHERESage<20；视图消解

SELECTSno，Sname FROMStudent WHERESage<20ANDSdept='IS'；160例2：查询信息系选修了1号课程的学生。

SELECTSno，Sname FROMIS_Student,SC WHERESC.Sno=IS_Student.Sno

ANDSC.Cno='1'；视图消解

SELECTStudent.Sno，Sname FROMStudent,SC WHERESC.Cno='1'ANDSdept='IS'

Student.Sno=SC.Sno；161例3：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。SELECT*FROMS_GWHEREGavg>=90；(系统转换后)

SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；SELECTSno，AVG(Grade)FROMSCGROUPBYSno；162例3：在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。SELECT*FROMS_GWHEREGavg>=90；(系统转换后)SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；1633.5.3更新视图更新视图即通过视图插入(INSERT)、删除(DELETE)和修改(UPDATE)数据，实质上转换为对基本表的更新。为了防止用户对超出视图范围的基本表的数据进行操作，在定义视图时，应加上WITHCHECKOPTION子句，则在视图上更新数据时，DBMS将检查视图定义中的条件，不满足将拒绝执行。164例1：将信息系学生视图IS_Student中学号为95002的学生的姓名改为“刘辰”。UPDATEIS_StudentSETSname='刘辰'WHERESno='95002'；

(系统转换后)UPDATEStudentSETSname='刘辰'WHERESno='95002'ANDSdept='IS'；165例2：向信息系学生视图IS_Student中插入一名新生，学号为95006，姓名为赵新，年龄为20岁的学生。INSERTINTOIS_StudentVALUES('95006','赵新',20)；

(系统转换后)INSERTINTOStudent(Sno,Sname,Sage,Sdept)VALUES('95006','赵新',20,'IS')；166例3：删除信息系学生视图IS_Student中学号为95006的记录。DELETEFROMIS_StudentWHERESno='95006'；

(系统转换后)DELETEFROMStudentWHERESno='95006'ANDSdept='IS'；167一般情况下，行列子集视图是可更新的。不可更新的视图（各系统不太一致）由多个表导出的视图，不可更新视图的列来自表达式或常数，不可插入、修改、可删除视图列是来自集函数，不可更新视图定义中含有GROUPBY子句，不可更新视图定义中含有DISTINCT短语，不可更新视图定义中内层嵌套的表与导出该视图表相同，不可更新在不允许更新的视图上定义的视图，不可更新168例：将SC中成绩在平均成绩之上的元组定义成一个视图GOOD_SC。

CREATEVIEWGOOD_SCASSELECTSno,Cno,GradeFROMSCWHEREGrade>(SELECTAVG(Grade)FROMSC);1693.5.4视图的作用视图能够简化用户的操作

使数据库看起来结构简单、清晰、可简化用户的数据查询操作视图使用户能以多种角度看待同一数据

使不同的用户以不同的方式看待同一数据视图对重构数据库提供了一定程度的逻辑独立性视图能够对机密数据提供安全保护

对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户的视图上。1703.6数据控制数据控制功能数据库恢复、并发控制数据库的安全性控制数据库的完整性控制定义码、取值唯一的列、不为空值的列、外码及其他一些约束条件。支持事物、提交、回滚等。某用户对某类数据具有何种操作权力。把