SQL语言学习资料课件 141页

第三章关系数据库标准语言SQL赵海利2021/9/18\n主要内容3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结\n3.1SQL概述SQL(StructuredQueryLanguage)结构化查询语言，是关系数据库的标准语言特色通用的功能极强\n3.1.1SQL的产生与发展标准大致页数发布日期SQL/861986.10SQL/89(FIPS127-1)120页1989年SQL/92622页1992年SQL991700页1999年SQL20033600页2003年\n3.1.2SQL的特点1.综合统一集数据定义语言(DDL),数据操纵语言(DML),数据控制语言(DCL)功能于一体可以独立完成数据库生命周期中的全部活动定义关系模式,插入数据,建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等用户数据库投入运行后,可根据需要随时逐步修改模式,不影响数据的运行数据操作符统一\n3.1.2SQL的特点2.高度非过程化SQL只要提出做什么,而无须指明怎么做3.面向集合的操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合4.以同一种语法结构提供多种使用方式SQL能嵌入到高级语言(C/C++,VC,Java)程序中\n3.1.2SQL的特点SQL功能动词数据查询SELECT数据定义CREATE,DROP,ALTER数据操纵INSERT,UPDATE,DELETE数据控制GRANT,REVOKE5.语言简洁,易学易用\n3.1.3SQL的基本概念SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模式内模式SQL支持关系数据库三级模式结构\n主要内容3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结\n3.2学生-课程数据库学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept200815001李勇男20CS200815002刘晨女19IS200815003王敏女18MA200815004李白男18MA200815005张立男19IS200815006董夏女18EN200815007王毅男19PH200815008刘欣女20ELStudent(Sno,Sname,Ssex,Sage,Sdept)\n课程号Cno课程名Cname先行课Cpno学分Ccredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言643.2学生-课程数据库Course(Cno,Cname,Cpno,Ccredit)\n学号Sno课程号Cno成绩Grade2008150011922008150012852008150013882008150022902008150023802008150035752008150046802008150053902008150072952008150084823.2学生-课程数据库SC(Sno,Cno,Grade)\n主要内容3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结\n3.3数据定义操作对象操作方式创建删除修改模式CREATESHEMADROPSCHEMA×表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW×索引CREATEINDEXDROPINDEX×SQL数据定义功能模式定义,表定义,视图和索引的定义只能删除然后重建\n3.3.1模式的定义与删除定义模式CREATESCHEMA<模式名>AUTHORIZATION<用户名>例:CREATESCHEMA“S_T”AUTHORIZATIONLIU删除模式DROPSCHEMA<模式名>例:DROPSCHEMALIUCASCADE\n3.2.2基本表的定义,删除与修改定义基本表CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件],[<列名><数据类型>[列级完整性约束条件],]…[表级完整性约束条件]);如果完整性约束条件涉及该表的多个属性列,则必须定义在表级上;否则,可定义在列级或表级\n数据类型SQL中域的概念用数据类型来实现定义表的属性时需要指明其数据类型及长度选用哪种数据类型?取值范围要做哪些运算\n数据类型数据类型含义CHAR(n)长度为n的定长字符串,中文占两个字符VARCHAR(n)最大长度为n的变长字符串INT长整数(也可以写作INTEGER)SMALLINT短整数NUMERIC(p,d)定点数,由p位数字(不包括符号和小数点)组成,小数后面有d位数字REAL取决于机器精度的浮点数DoublePrecision取决于机器精度的双精度浮点数FLOAT(n)浮点数,精度至少为n位数字DATE日期,包含年月日,格式为YYYY-MM-DDTIME时间,包含时分秒,格式为HH:MM:SS\n创建学生表StudentCREATETABLEStudent(SnoCHAR(9)PRIMARYKEY,/*列约束,主码*/SnameCHAR(20)UNIQUE,/*不允许重名*/SsexCHAR(2)NOTNULL,SageSMALLINTNOTNULL,/*非空*/SdeptCHAR(20),CHECK(Sage>0ANDSage<=50)/*表级约束*/);\n创建课程表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY,/*列约束,主码*/CnameCHAR(20),CpnoCHAR(4),CcreditSMALLINT,FOREIGNKEY(Cpno)REFERENCESCourse(Cno)/*表级完整性约束,Cpno是外码,被参照表Course,被参照列是Cno*/);\n创建学生选课表SCCREATETABLESC(SnoCHAR(9),CnoCHAR(4),GradeSMALLINT,PRIMARYKEY(Sno,Cno),/*两属性,必须是表级约束*/FOREIGNKEY(Sno)REFERENCESStudent(Sno),/*表级完整性约束,Sno是外码,被参照表Student*/FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束,Cno是外码,被参照表Course*/);\n删除基本表DROPTABLE<表名>[RESTRICT|CASCADE]RESTRICT:若存在依赖该表的对象(外码,视图,函数等),则不能被删除CASCADE:无限制删除基本表及在该表上的依赖对象注:SQLServer2000无此选项例:DROPTABLEStudentDROPTABLECourse\n修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束>][ALTERCOLUMN<列名><数据类型>]例1ALTERTABLEStudentADDSdormCHAR(10);/*增加宿舍列*/ALTERTABLEStudentDROPCOLUMNSdorm;/*删除宿舍列*/\n修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束>][ALTERCOLUMN<列名><数据类型>]例2ALTERTABLEStudentADDUNIQUE(Sname);/*增加唯一性约束*/\n修改基本表ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束>][ALTERCOLUMN<列名><数据类型>]例3ALTERTABLEStudentALTERCOLUMNSageSMALLINT;/*修改类型*/\n3.3.3索引的建立与删除建立索引的目的:加快查询速度谁可以建立索引?DBA或表的属主(Owner)DBMS一般会在PrimaryKey及Unique列上自动建索引索引由DBMS自动维护使用索引DBMS自动选择是否使用所以及使用哪些索引\n建立索引CREATE[UNIQUE|CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序],[<列名>[次序],]…);次序:ASC(升序,默认),DESC(降序)UNIQUE:每个索引值只对应唯一的数据记录CLUSTER建在经常查询的列上,但不是经常更新的列(维护代价)一个基本表最多只能有一个CLUSTER例(注:SQLServer2000不支持CLUSTER)CREATEUNIQUEINDEXIdxONStudent(SnameDESC)\n删除索引DROPINDEX<索引名>删除时,系统会从数据字典中删去有关该索引的描述注:DROPINDEX<表名.索引名>/*SQLServer2000*/例DROPINDEXIdx;DROPINDEXStudent.Idx;/*SQLServer2000*/\n主要内容3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结\n数据更新的基本要求DBMS在执行数据更新时会检查该操作是否破坏表上已定义的完整性规则实体完整性主码必须非空且唯一,赋值后不能修改参照完整性用户定义的完整性NOTNULL约束UNIQUE约束值域约束\n3.5数据更新3.5.1插入数据(INSERTINTO…VALUES)3.5.2修改数据(UPDATE…SET…WHERE…)3.5.3删除数据(DELETE…FROM…WHERE…)\n3.5.1插入数据插入一个元组INSERTINTO<表名>[(<属性列1>[,<属性列2>]…)]VALUES(<常量1>[,<常量2>]…);插入子查询结果(即:多个元组)INSERTINTO<表名>[(<属性列1>[,<属性列2>]…)]子查询结果;\n说明INTO语句属性列的顺序可以与表定义中的顺序不一致指定部分属性列没有指出属性列(注:表示所有属性列,且顺序同表定义)VALUES语句和子查询结果提供的值必须与INTO语句保持匹配值的类型值的顺序值的个数\n例1将一个新学生插入到Student表中.INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES(‘200816012’,‘陈东’,‘男’,‘IS’,18);/*注:属性的顺序可任意排放,只需保持对应*/INSERTINTOStudentVALUES(‘200815126’,‘张成’,‘男’,18,‘CS’);/*注:没有指出属性,表示表中所有属性且同顺序*/\n例2将选课记录(200815128,1)插入到SC表中.INSERTINTOSC(Sno,Cno)VALUES(‘200815128’,‘1’);/*注:未指出Grade,自动赋值为NULL*/INSERTINTOSCVALUES(‘200815128’,‘1’,NULL);/*注:没有指出属性,表示表中所有属性且同顺序*/\n例3求计算机系的所有学生的学号和姓名,并把结果存入数据库.CREATETABLEStu_CS/*建立计算机系学生表*/(SnoCHAR(9),CnameCHAR(20));INSERTINTOStu_CSSELECTSno,Sname/*子查询结果*/FROMStudentWHEREStudent.Sdept=‘CS’;\n3.5.2修改数据一般格式UPDATE<表名>SET<列名>=<表达式>[<列名>=<表达式>]…[WHERE<条件>];/*注:缺省时表示所有元组*/功能修改指定表中满足WHERE条件的元组的列值\n例1将学生200815001的年龄改为21岁,系改为JP.UPDATEStudentSETSage=21,Sdept=‘JP’WHERESno=‘200815001’;例2将所有学生的年龄增加1岁.UPDATEStudentSETSage=Sage+1\n例3将所有计算机系学生的成绩置为0.UPDATESCSETGrade=0WHERE‘CS’=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);\n3.5.3删除数据一般格式DELETEFROM<表名>[WHERE<条件>];/*注:缺省时表示所有元组*/功能删除指定表中满足WHERE子句条件的元组\n例1删除学号为200815001的学生记录.DELETEFROMStudentWHERESno=‘200815001’;例2删除所有学生的选课记录.DELETEFROMSC;/*注:SC成为空表!*/\n例3删除计算机系所有学生的选课记录.DELETEFROMSCWHERE‘CS’=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);\n主要内容3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结\n3.4数据查询一般格式SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]\n3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式\n3.4.1单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句\n1.查询指定列例1查询全体学生的学号和姓名.SELECTSno,SnameFROMStudent;例2查询全体学生的姓名、学号、所在系.SELECTSname,Sno,Sdept/*列的顺序可以任意*/FROMStudent;\n2.查询全部列例3查询全体学生的详细记录.SELECT*FROMStudent;SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;\n3.查询经过计算的值SELECT子句的<目标列表达式>可以为：属性列算术表达式字符串常量函数\n3.查询经过计算的值例4查询全体学生的姓名及其出生年份.SELECTSname,2009－Sage/*假定当年是2009*/FROMStudent;结果:\n3.查询经过计算的值例5查询全体学生的姓名、出生年份和系,要求用小写表示所有系名.SELECTSname,‘YearofBirth:’,2009－Sage,LOWER(Sdept)/*大写:UPPER*/FROMStudent;结果:\n3.查询经过计算的值例5查询全体学生的姓名、出生年份和系,要求用小写表示所有系名.SELECTSname‘姓名’,‘出生年份:’‘出生’,2009－Sage‘生日’,LOWER(Sdept)‘系’FROMStudent;结果:\n3.4.1单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句\n1.消除取值重复的行例6查询选修了课程的学生学号.SELECTSnoFROMSC;结果:SELECTDISTINCTSnoFROMSC;\n2.查询满足条件的元组常用的查询条件:查询条件谓词比较大小=,>,<,>=,<=,!=,<>,!>,!<确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件(逻辑运算)AND,OR,NOT\n(1)比较大小例7查询计算机系全体同学的名单.SELECTSnameFROMStudentWHERESdept=‘CS’;结果:\n(1)比较大小例8查询所有年龄在20岁以下的学生姓名及其年龄.SELECTSname,SageFROMStudentWHERESage<20;结果:\n(1)比较大小例9查询考试成绩小于85的学生的学号和成绩.SELECTSno,GradeFROMSCWHEREGrade<85;结果:\n(2)确定范围例10查询年龄在[20,23]内学生的姓名,系和年龄.SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;结果:\n(2)确定范围例11查询年龄不在[20,23]内学生的姓名,系和年龄.SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23;结果:\n(3)确定集合例12查询三个系(CS,MA,IS)学生的姓名,性别和系.SELECTSname,Ssex,SdeptFROMStudentWHERESdeptIN(‘CS’,‘MA’,‘IS’);结果:\n(3)确定集合例13查询系(CS,MA,IS)之外学生的姓名,性别和系.SELECTSname,Ssex,SdeptFROMStudentWHERESdeptNOTIN(‘CS’,‘MA’,‘IS’);结果:\n(4)字符匹配一般格式[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]通配符%:任意长度(可以是0)的字符串通配符_:单个字符(注:一个汉字要用__)ESCAPE:把通配符转换成一般字符\n(4)字符匹配例14查询学号为200815001的学生的详细情况.SELECT*FROMStudentWHERESnoLIKE‘200815001’;SELECT*FROMStudentWHERESno=‘200815001’;说明不含通配符时,LIKE即=不含通配符时,NOTLIKE即!=或<>\n(4)字符匹配例15查询所有姓刘的学生的姓名,学号和性别.SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE‘刘%’;结果:\n(4)字符匹配例16查询姓欧阳且全名是3个字的学生的姓名.SELECTSnameFROMStudentWHERESnameLIKE‘欧阳__’;例17查询名字中第2个字是阳的学生的学号和姓名.SELECTSno,SnameFROMStudentWHERESnameLIKE‘__阳%’;\n(4)字符匹配例18查询所有不姓刘的学生的姓名和系.SELECTSname,SdeptFROMStudentWHERESnameNOTLIKE‘刘%’;结果:\n(4)字符匹配例19查询DB_Design课程的课程号和学分.SELECTSno,ScreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE‘\’;例20查询以DB_开头,且倒数第3个字符是i的课程.SELECT*FROMCourseWHERECnameLIKE‘DB\_%i__’ESCAPE‘\’;\n(5)涉及空值的查询谓语ISNULLISNOTNULL注:IS不能用=代替\n(5)涉及空值的查询例21查询学生有选课但无成绩的学生学号和课程号.SELECTSno,CnoFROMSCWHEREGradeISNULL;例22查询所有有成绩的学生学号和课程号.SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;\n(6)多重条件查询逻辑运算符AND,OR,NOTAND的优先级高于OR可以用(…)来改变优先级其它谓语[NOT]IN[NOT]BETWEEN…AND…\n(6)多重条件查询例23查询计算机系年龄在20岁以下的学生情况.SELECT*FROMStudentWHERESdept=‘CS’ANDSage<20;SdeptIN(‘CS’,‘MA’,‘IS’)Sdept=‘CS’ORSdept=‘MA’ORSdept=‘IS’\n3.4.1单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句\nORDERBY子句ORDERBY可以按一个或多个属性列排序ASC:升序,默认DESC:降序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示\nORDERBY子句例24查询选修3号课学生的学号及成绩,按成绩降序.SELECTSno,GradeFROMSCWHERECno=‘3’ORDERBYGradeDESC;结果:\nORDERBY子句例25查询全体学生,排列按系升序,同一系年龄降序.SELECT*FROMStudentORDERBYSdept,SageDESC;结果:\n3.4.1单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句\n聚集函数COUNT([DISTINCT|ALL]*)COUNT([DISTINCT|ALL]<列名>)SUM([DISTINCT|ALL]<列名>)AVG([DISTINCT|ALL]<列名>)MAX([DISTINCT|ALL]<列名>)MIN([DISTINCT|ALL]<列名>)ALL:默认\n聚集函数例26查询学生总人数.SELECTCOUNT(*)FROMStudent;例27查询选修了课程的学生人数.SELECTCOUNT(DISTINCTSno)FROMSC;例28查询1号课程的学生平均成绩.SELECTAVG(Grade)FROMSCWHERECno=‘1’;\n聚集函数例29查询选修1号课程的学生最高分数.SELECTMAX(Grade)FROMSCWHERECno=‘1’;例30查询学生200815001选修课的总学分.SELECTSUM(Ccredit)FROMSC,CourseWHERESno=‘200815001’ANDSC.Cno=Course.Cno;\n3.4.1单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列二、选择表中的若干元组三、ORDERBY子句四、聚集函数五、GROUPBY子句\nGROUPBY子句查询结果按某一列或多列的值分组,值相等为一组细化聚集函数的作用对象未对查询结果分组,聚集函数将作用于整个查询结果对查询结果分组后,聚集函数将分别作用于每个组\nGROUPBY子句例31求各个课程号及其选课人数.SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;结果:\nGROUPBY子句例32查询选修了3门以上课程的学生学号.SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3;说明GROUPBY按Sno进行分组HAVING选出满足条件的组,作用于组WHERE作用于基本表或视图\n3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式\n3.4.2连接查询连接查询：同时涉及多个表的查询主要有等值与非等值连接查询自身连接查询外连接查询复合条件连接查询\n等值与非等值连接查询WHERE连接条件[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>\n等值连接查询例33查询每个学生及其选课情况.SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;结果:SELECTStudent.*,SC.Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;自然连接\n自身连接查询例35查每门课的间接先修课(即:先修课的先修课).SELECTCourse.Cno,Cview.Cpno/*Cview是Course的视图,即:副本*/FROMCourse,CviewWHERECourse.Cpno=Cview.CnoANDCview.CpnoISNOTNULL;结果:\n外连接查询例36查询每个学生及其选课情况.(保留未选课学生)SELECTStudent.*,SC.Cno,GradeFROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno);结果:\n复合连接查询例37查询选修2号课程且成绩在90以上的所有学生.SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=‘2’ANDSC.Grade>90;结果:\n复合连接查询例38查询每个学生的学号,姓名,选修课程名及成绩.SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;结果:\n3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式\n3.4.3嵌套查询查询块一个SELECT-FROM-WHERE语句嵌套查询一个查询块作为另一个查询块的WHERE或HAVING的条件例SELECTSname/*外层查询或父查询*/FROMStudentWHERESnoIN(SELECTSno/*内查询或子查询*/FROMSCWHERECno=‘2’);子查询不能使用ORDERBY!最外层可以!\n3.4.3嵌套查询一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY(SOME)或ALL谓词的子查询四、带有EXISTS谓词的子查询\n带有IN谓词的子查询例39查询与刘晨同一个系的所有学生.SELECT*/*②求学生*/FROMStudentWHERESdeptIN(SELECTSdept/*①求系名*/FROMStudentWHERESname=‘刘晨’);结果:注:这样的查询称为不相关子查询\n带有IN谓词的子查询例40查询选修了“信息系统”的学生学号和姓名.SELECTSno,Sname/*③求姓名*/FROMStudentWHERESnoIN(SELECTSno/*②求学号*/FROMSCWHERECnoIN(SELECTCno/*①求课程号*/FROMCourseWHERECname=‘信息系统’));结果:\n带有比较运算符的子查询如果能确定子查询返回单值时,可使用比较运算符=,>,<,>=,<=,!=或<>可以与ANY或ALL谓词配合使用注意:子查询必须在比较符之后!\n带有比较运算符的子查询例41找出每个学生超过他选修课平均成绩的课程号.SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno);注:这样的查询称为相关子查询\n带有ANY(SOME)或ALL的子查询>ANY大于子查询结果中的某个值>ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值>=ALL大于等于子查询结果中的所有值<=ANY小于等于子查询结果中的某个值<=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值(无实际意义)!=(<>)ANY不等于子查询结果中的某个值!=(<>)ALL不等于子查询结果中的任何一个值\n带有ANY(SOME)或ALL的子查询例42查询其它系中比CS中某一学生年龄小的学生.SELECT*FROMStudentWHERESage<<=>>=ANYIN--MIN>=MINALL--NOTINMAX>=MAX\n带有EXISTS谓词的子查询EXISTS子查询不返回任何数据,只产生逻辑真true或逻辑假false若子查询结果非空,则外层的WHERE子句返回true若子查询结果为空,则外层的WHERE子句返回false由EXISTS引出的子查询,其目标列表达式通常都用*因为只返回真或假,给出列名无实际意义NOTEXISTS若子查询结果非空,则外层的WHERE子句返回false若子查询结果为空,则外层的WHERE子句返回true\n带有EXISTS谓词的子查询例44查询选修了1号课程的所有学生的姓名.SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘1’);结果:\n带有EXISTS谓词的子查询例45查询没有选修1号课程的所有学生的姓名.SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘1’);结果:\n带有EXISTS谓词的子查询例46查询选修了全部课程的学生姓名./*查询这样的学生,没有一门课是他不选修的*//*(x)P≡(x(P))*/SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));\n带有EXISTS谓词的子查询例47查询至少选修了学生200815002选修的全部课程的学生姓名./*(y)pq≡y(p∧q)*/SELECTDISTINCTSnoFROMSCscxWHERENOTEXISTS(SELECT*FROMSCscyWHEREscy.Sno=‘200815002’ANDNOTEXISTS(SELECT*FROMSCsczWHEREscz.Sno=scx.SnoANDscz.Cno=scy.Cno));\n3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式\n3.4.4集合查询集合操作并操作UNION交操作INTERSECT差操作EXCEPT注意:参加集合操作的各查询结果必须满足列数相同对应项的数据类型相同\n3.4.4集合查询例48查询CS系的学生和年龄不大于19岁的学生.SELECT*FROMStudentWHERESdept=‘CS’UNIONSELECT*FROMStudentWHERESage<=19;SELECT*FROMStudentWHERESdept=‘CS’ORSage<=19;\n3.4.4集合查询例49查询选修1号或2号课程的学生.SELECT*FROMCCWHERECno=‘1’UNIONSELECT*FROMCCWHERECno=‘2’;SELECT*FROMCCWHERECno=‘1’ORCno=‘2’;\n3.4.4集合查询例50查询CS系且年龄不大于19岁的学生.SELECT*FROMStudentWHERESdept=‘CS’INTERSECTSELECT*FROMStudentWHERESage<=19;SELECT*FROMStudentWHERESdept=‘CS’ANDSage<=19;注:SQL2005以上才支持INTERSECT！\n3.4.4集合查询例51查询选修1号和2号课程的学生.SELECT*FROMCCWHERECno=‘1’INTERSECTSELECT*FROMCCWHERECno=‘2’;SELECT*FROMCCWHERECno=‘1’ANDCno=‘2’;\n3.4.4集合查询例52查询CS系学生与年龄不大于19岁的学生的差集.SELECT*FROMStudentWHERESdept=‘CS’EXCEPTSELECT*FROMStudentWHERESage<=19;SELECT*FROMStudentWHERESdept=‘CS’ANDSage>19;\n3.4数据查询3.4.1单表查询3.4.2连接查询3.4.3嵌套查询3.4.4集合查询3.4.5SELECT语句的一般格式\n3.4.5SELECT的一般格式一般格式SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…FROM<表名或视图名>[,<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]见课本114页\n主要内容3.1SQL概述3.2学生－课程数据库3.3数据定义3.4数据查询3.5数据更新3.6视图3.7小结\n3.6视图视图的特点虚表,是从一个或几个基本表(或视图)导出的表只存放视图的定义,不存放视图对应的数据基本表中的数据发生变化,从视图中查询出的数据也随之改变视图的操作查询删除有一定限制的更新定义基于该视图的新视图\n3.6.1定义视图一般格式CREATEVIEW<视图名>[(<列名>[,<列名>]…)]AS<子查询>[WITHCHECKOPTION];子查询:任意复杂的SELECT语句,但不能不能含ORDERBY子句和DISTINCT短语列名:只能全部指定或省略(子查询SELECT中的目标列)WITHCHECKOPTION:对视图进行INSERT,UPDATE,DELETE操作时要满足子查询中的条件DBMS只把视图的定义存入数据字典,只有在查询时才执行SELECT语句并将数据从基本表中查处\n3.6.1定义视图删除视图DROPVIEW<视图名>[CASCADE]删除基本表之前,应先删除该表上的视图,否则无法使用例(SQLServer2000)DROPVIEWIS_Stu;\n例1建立信息系学生的视图.CREATEVIEWIS_StuASSELECTSno,Sname,SageFROMStudentWHERESdept=‘IS’WITHCHECKOPTION;/*对视图进行插入,修改和删除时会自动加上Sdept=‘IS’,因此对非信息系的同学是写保护*/\n例2建立信息系选修了1号课程的学生的视图./*视图建立在多个基本表上*/CREATEVIEWIS_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=‘IS’ANDStudent.Sno=SC.SnoANDSC.Cno=‘1’;\n例3建立信息系选修了1号课程且成绩在90分以上的学生的视图./*视图建立在视图上*/CREATEVIEWIS_S2ASSELECTSno,Sname,GradeFROMIS_S1WHEREGrade>=90;\n例4定义一个反映学生出生年份的视图./*带表达式的视图*/CREATEVIEWBT_Stu(Sno,Sname,Sbirth)ASSELECTSno,Sname,2009－SageFROMStudent;派生属性虚拟列\n例5定义学生的学号及其平均成绩的视图./*分组视图*/CREATEVIEWG_Stu(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;\n例6将Student表中所有女生定义为一个视图.CREATEVIEWF_Stu(no,name,sex,age,dept)ASSELECT*/*不指定属性列*/FROMStudentWHERESsex=‘女’;如果修改基表Student的结构,F_Stut视图与Student表的映象关系将被破坏,导致该视图不能正确工作.缺陷\n3.6.2查询视图用户的角度:查询视图与查询基本表相同DBMS实现视图查询的方法:视图消解法进行有效性检查转换成等价的对基本表的查询执行修正后的查询\n例1在信息系学生的视图中找出年龄小于20的学生.SELECTSno,SageFROMIS_StuWHERESage<20;转换后的查询语言为:SELECTSno,SageFROMStudentWHERESdept=‘IS’ANDSage<20;\n例2查询选修了1号课程的信息系学生.SELECTIS_Stu.Sno,SnameFROMIS_Stu,SCWHEREIS_Stu.Sno=SC.SnoANDSC.Cno=‘1’;\n视图消解法的缺陷有些情况下,视图查询不能直接转换成表查询!例3查询G_Stu视图从平均成绩在90分以上的学生.SELECT*FROMG_StuWHEREGavg>=90;CREATEVIEWG_Stu(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;SELECTSno,AVG(Grade)FROMSCWHEREAVG(Gavg)>=90GROUPBYSno;直接转换WHERE不能含聚集函数!\n视图消解法的缺陷有些情况下,视图查询不能直接转换成表查询!例3查询G_Stu视图从平均成绩在90分以上的学生.SELECT*FROMG_StuWHEREGavg>=90;SELECTSno,AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90;直接对基本表查询\n3.6.3更新视图更新视图通过对视图的插入、删除和修改来更新基本表的数据原理把对视图的更新操作转换为对基本表的更新操作\n视图的修改数据例1把信息系学生的视图中学号为200815002的学生改名为“刘辰”.UPDATEIS_StuSETSname=‘刘辰’WHERESno=‘200815002’;转换为对基本表的更新:UPDATEStudentSETSname=‘刘辰’WHERESno=‘200815002’ANDSdept=‘IS’;\n视图的插入数据例2向信息系学生的视图中插入一位新同学的记录(200815021,赵新,20).INSERTINTOIS_StuVALUES(‘200815021’,‘赵新’,20);转换为对基本表的更新:INSERTINTOStudent(Sno,Sname,Sage,Sdept)VALUES(‘200815021’,‘赵新’,20,‘IS’);\n视图的删除数据例3删除信息系学生的视图中学号为200815002的记录.DELETEFROMIS_StuWHERESno=‘200815002’;转换为对基本表的更新:DELETEFROMIS_StuWHERESno=‘200815002’ANDSdept=‘IS’;\n更新视图的限制一些视图是不可更新的因为对这些视图的更新不能唯一有意义地转换成对相应基本表的更新例:G_Stu视图UPDATEG_StuSETGavg=90WHERESno=‘200815001’CREATEVIEWG_Stu(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;\n视图是否可更新的规定通常,行列子集视图是可更新的DB2规定:若视图是由两个以上基本表导出的,则不允许更新.若视图的字段来自字段表达式或常数,则不允许对执行INSERT和UPDATE操作,但允许执行DELETE操作.若视图的字段来自集函数,则不允许更新.若视图定义中含有GROUPBY子句,则不允许更新.若视图定义中含有DISTINCT短语,则不允许更新.若视图定义中有嵌套查询,并且内层查询的FROM子句中涉及的表也是导出该视图的基本表,则不允许更新.一个不允许更新的视图上定义的视图也不允许更新.\n3.6.4视图的作用1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询\nQ&AThankyou!