第3章-关系数据库语言SQL

第3章关系数据库语言SQL内容提纲1）SQL简介SQL数据库的体系结构SQL的组成2）SQL的数据定义SQL模式、基本表和索引的创建和撤销3）SQL的数据查询SELECT语句的句法SELECT语句的三种形式及各种限定基本表的连接操作2内容提纲4）SQL的数据更新插入、删除和修改语句5）视图创建和撤消视图的更新限制6）嵌入式SQL（自学）7）存储过程与SQL/PLM（自学）3引言SQL是关系数据库的标准语言对关系模型的发展和商用DBMS的研制起着重要的作用SQL语言是介乎于关系代数和元组演算之间的一种语言SQL语言的9个核心词汇Create,Alter,Drop,Select,Insert,Update,DeleteGrant,Revoke本章详细介绍SQL的核心部分内容数据定义数据查询数据更新4SQL简介SQL发展史1970年美国IBM研究中心的E.F.Codd连续发表多篇论文，提出关系模型1972年IBM公司开始研制实验型关系数据库管理系统SYSTEMR，配制的查询语言称为SQUARE语言1974年把SQUARE修改为SEQUEL语言1978年，SEQUEL简称为SQL，即“结构式查询语言”5SQL简介SQL发展史SQL861986年10月，ANSISQL标准1987年6月，ISOSQL标准SQL21992年8月，ISOSQL标准SQL31999年，ISOSQL标准在未来很长一段时间，SQL仍将是关系数据库领域的主流语言在软件工程、人工智能领域，SQL已显示出相当大的潜力6SQL简介SQL数据库的体系结构7SQL简介SQL数据库的体系结构1）一个SQL模式（Schema）是表和约束的集合2）一个表由行集构成，一行是列的序列3）表类型：基本表、视图和导出表4）基本表与存储文件是M:N联系存储文件与外部存储器的物理文件是一一对应的5）SQL语句执行对基本表和视图查询等操作在用户看来，两者是一样的，都是表6）SQL用户可以是应用程序，也可以是终端用户8SQL简介SQL组成1）数据定义语言，即SQLDDL用于定义SQL模式、基本表、视图、索引等结构2）数据操纵语言，即SQLDML数据查询数据更新：包括插入、删除和修改三种操作3）嵌入式SQL语言规则SQL语句嵌入在宿主语言程序中的规则4）数据控制语言，即SQLDCL授权、完整性规则的描述、事务控制等9SQL简介SQL特点1）灵活和强大的查询功能SELECT语句能完成相当复杂的查询操作2）SQL不是一个应用开发语言只提供对数据库的操作功能，不能完成屏幕控制、菜单管理、报表生成等功能3）SQL是国际标准语言有利于各种数据库之间交换数据，有利于程序的移植，有利于实现高度的数据独立性，有利于实现标准化4）SQL的词汇不多完成核心功能只用了9个英语动词，它的语法结构接近英语，因此容易学习和使用10SQL的数据定义SQL模式的创建和撤销注模式（Schema）一词，来自于“ISOSQL标准”协议Schema在SQL数据库中相当于一个容器数据库所有的对象如表、视图、索引、用户、存储过程、触发器等都位于容器内创建SQL模式，就是定义一个存储空间在商业DBMS中，大多都使用Database代替Schema11SQL的数据定义SQL模式的创建和撤销创建CREATESCHEMA<模式名>AUTHORIZATION<用户名>撤销DROPSCHEMA<模式名>［CASCADE│RESTRICT］方式CASCADE（级联式）RESTRICT（约束式）商业DBMS中CREATEDatabase……DROPDatabase……12SQL的数据定义常用数据类型1）数值型：Integer，Smallint，Numeric(p,d)/DEC(p,d)2）字符串型：Char(n)，Varchar(n)3）位串型：Bit(n)，BitVarying(n)4）时间型：DATE，TIME注在很多DBMS中支持自定义数据类型，如在Oracle中CREATEDOMAIN<域名> [AS]<数据类型>[列级完整性约束];DROPDOMAIN<标识符>;13SQL的数据定义常用数据类型示例：在Oracle中定义数据类型与应用定义CreatedomainCOLORChar(6)Default’???’ ConstraintVALID_COLORS

Check(valuein(‘Red’,’Yellow’,’Green’,’???’));使用CreateTablePART( ……

partcolorCOLOR, ……);14SQL的数据定义基本表的创建、修改和撤销表的创建句法CREATETABLE<表名>( <列名><类型>[域完整性约束条件] {,<列名><类型>[域完整性约束条件]} [,<表级完整性约束条件>]);域完整性约束条件NOTNULL/[NULL]：列值是否可以为空UNIQUE：列值唯一，不得重复DEFAULT：列值空缺时，由系统填写默认值

15SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件主键（PRIMARYKEY）子句格式：PRIMARYKEY(<列名表>)作用：提供实体完整性约束的说明说明系统一般自动在主键上建索引主键为单属性时，可直接在属性后的域完整性约束条件中，使用PRIMARYKEY定义主键16SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGNKEY）子句格式：Foreignkey[外键名](<列名表1>) references<主表名>[(列名表2)] [ondelete<restrict｜cascade｜setnull>]作用：提供参照完整性约束的说明17SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件外键（FOREIGNKEY）子句 ondelete<restrict｜cascade｜setnull>子句的说明RESTRICT：主表的主键值行不得删除CASCADE：主表主键值行删除，从表的相关行随之删除SETNULL：主表主键值行删除，从表的相关行的列值设为NULL，前提是该列的值可以为NULL18SQL的数据定义基本表的创建、修改和撤销表的创建表级完整性约束条件检查（CHECK）子句格式：CHECK(约束表达式)作用：对某元组某属性取值的约束说明说明表创建后是一个空表，需要使用DML（insert，update，delete）语句装入或维护数据行19SQL的数据定义基本表的创建、修改和撤销表的创建示例20SQL的数据定义基本表的创建、修改和撤销表的创建示例21SQL的数据定义基本表的创建、修改和撤销表的创建示例22SQL的数据定义基本表的创建、修改和撤销表的创建问关于表T、S、C、SC的创建顺序，下列选项正确的是？A）C→T→SC→SB）T→S→C→SCC）S→T→C→SCD）S→T→SC→CE）T→C→S→SCF）SC→C→S→TG）SC→C→T→S23SQL的数据定义基本表的创建、修改和撤销表的修改句法ALTERTABLE<表名><修改动作……>增加属性Altertable<表名>add<属性名><类型>；删除属性Altertable<表名>drop<属性名>[cascade｜restrict]CASCADE：引用该列的视图和约束随之删除RESTRICT：只在没有被引用时才可删除不是所有的DBMS都支持24SQL的数据定义基本表的创建、修改和撤销表的修改句法ALTERTABLE<表名><修改动作……>修改属性的类型Altertable<表名>modify<列名><类型>modify→altercolumn其他修改，如补充定义主键，撤销主键定义补充定义外键，撤销外键定义定义和撤销别名25SQL的数据定义基本表的创建、修改和撤销表的撤销句法DROPTABLE<表名>[cascade｜restrict]说明CASCADE：删除该基本表时，所有引用该基本表的视图和约束一起自动被删除RESTRICT：没有视图和约束引用该基本表时，才能撤销26SQL的数据定义索引的建立和撤销索引概念是物理存取路径，不属于逻辑数据模式RDBMS通常在主键上自动建立索引查询、更新时自动起作用（适当建立索引会提高查询速度）句法创建索引CREATE[UNIQUE]INDEX<索引名> ON基表名(<列名>[ASC｜DESC]

[{,<列名>[ASC｜DESC]}]);撤销索引DROPINDEX<索引名列表>;UNIQUE索引，只能建立在候选键上27SQL的数据定义索引的建立和撤销示例CreateindexAge_Index onS(Age);CreateuniqueindexAge_IndexonS(AgeASC);CreateuniqueindexSC_Index onSC(SNO,CNODESC);DropindexAge_Index,SC_Index;CreateindexC_Index onC(TNODESC, CreditDESC);28SQL的数据查询SELECT语句的基本结构句型SELECT<目标表的列名或列表达式序列> FROM<表名、视图名或导出表序列> WHERE<行条件表达式>只有SELECT和FROM子句是每个SQL查询语句所必需的

语义示例查询选修了DB课程的学生的姓名29SQL的数据查询SELECT语句的基本结构示例ΠSNAME(σCNAME=‘DB‘ANDS.SNO=SC.SNOANDSC.CNO=C.CNO(S

SC

C))SelectSnameFromS,SC,C WhereCname=‘DB’AndS.Sno=SC.SnoAndSC.Cno=C.Cno

1000行1000行10000行30SQL的数据查询SELECT语句的基本结构示例ΠSNAME(σCNAME=‘DB‘(S⋈SC⋈C))DBMS的优化 ΠSNAME(ΠSNO,SNAME(S)⋈ΠSNO(ΠCNO(σCNAME=‘DB‘(C))⋈ΠSNO,CNO(SC)))SelectSnameFromS WhereSnoin(SelectSnoFromSCWhereCnoin(SelectCnoFromCWhereCname=‘DB’));ΠCNO(σCNAME=‘DB‘(C))

ΠSNO,CNO(SC)⋈ΠSNO,SNAME(S)ΠSNO()⋈ΠSNAME()1000→110000<=10001000<=1000<=1000<=100031SQL的数据查询SELECT语句的基本结构常见的3种SELECT语句写法示例：查询选修‘C2’课程的学生学号和姓名1）连接查询SelectS.Sno,Sname FromS,SC WhereS.Sno=SC.SnoandCno=‘C2’语句的执行步骤1）笛卡尔积2）等值连接2）选择和投影32SQL的数据查询SELECT语句的基本结构常见的3种SELECT语句写法示例：查询选修‘C2’课程的学生学号和姓名2）IN嵌套查询SelectSno,SnameFromSWhereSnoin (SelectSnoFromSCWhereCno=‘C2’);SelectSno,SnameFromSWhere‘C2’in (SelectCnoFromSCWhereSno=S.Sno);区别1）由里到外，内层查询（执行1次）结果供给外层使用2）依赖于外层值，内层（执行n次）结果供给外层使用33SQL的数据查询SELECT语句的基本结构常见的3种SELECT语句写法示例：查询选修‘C2’课程的学生学号和姓名3）EXISTS嵌套查询SelectSno,SnameFromS

WhereEXISTS(Select*FromSC WhereSno=S.SnoandCno=‘C2’);说明1）3个位置特殊，需注意2）EXISTS表示“

”，判定内层查询结果是否为非空， 即至少存在一个元组（满足外查询的条件）34SQL的数据查询SELECT语句的基本结构基本句型的综合应用1）检索学习课程号为C2课程的学生学号与成绩ΠSNO，Grade（σCNO='C2'（SC））SelectSno,Grade FromSC WhereCno=‘C2’;2）检索学习课程号为C2的学生学号与姓名ΠSNO，SNAME（σCNO='C2'（S⋈SC））略35SQL的数据查询SELECT语句的基本结构基本句型的综合应用3）检索至少选修LIU所授课程一门课程的学生学号与姓名ΠSNO，SNAME（σTNAME='LIU'（S⋈SC⋈C⋈T））SelectS.Sno,SnameFromS,SC,C,T WhereTname=‘LIU’andS.Sno=SC.Sno andSC.Cno=C.CnoandC.TNO=T.Tno;SelectSno,SnameFromSWhereSnoin(SelectSnoFromSCWhereCnoin(SelectCnoFromCWhereTnoin(SelectTnoFromTWhereTname=‘LIU’)));36SQL的数据查询SELECT语句的基本结构基本句型的综合应用4）检索选修课程号为C2或C4的学生学号ΠSNO（σCNO='C2‘∨CNO='C4'（SC））

SelectSnoFromSC WhereCno=‘C2’orCno=‘C4’;SelectSnoFromSC WhereCnoin(‘C2’,‘C4’);SelectSnoFromSC WhereCno=SOME(‘C2’,‘C4’);37SQL的数据查询SELECT语句的基本结构基本句型的综合应用5）检索至少选修课程号为C2和C4的学生学号Π1（σ1=4∧2='C2‘∧5='C4'（SC×SC））SelectX.Sno FromSCasX,SCasY WhereX.Sno=Y.Sno andX.Cno=‘C2’ andY.Cno=‘C4’;FROM子句中，可以用as为表和视图取一别名，这种别名只在本句中有效

38学号课程号成绩984101C485984101C290984102C285984103…C4…90…学号课程号成绩984101C485984101C485984101C485984101…C4…85…学号课程号成绩984101C485984101C290984102C285984103C490学号课程号成绩984101C485984101C290984102C285984103C490SCSCSC×SC思考：至少选了2门课的学生学号？至少有两个人选的课程号？39SQL的数据查询SELECT语句的基本结构基本句型的综合应用6）检索不学C2课的学生姓名与年龄ΠSNAME，AGE(S)－ΠSNAME，AGE(σCNO='C2‘(S⋈SC))不能用连接查询写SelectSname,AgeFromS WhereSnonotin( SelectSnoFromSCWhereCno=‘C2’);SelectSname,AgeFromSWherenotExists( Select*FromSC WhereSno=S.SnoandCno=‘C2’);40SQL的数据查询SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名ΠSNAME（S⋈（ΠSNO，CNO（SC）÷ΠCNO（C）））SelectSnameFromS WherenotExists( Select*FromC WherenotExists( Select*FromSC WhereSno=S.SnoandCno=C.Cno));41SQL的数据查询SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名理解逐行扫描S表中的每条记录，对每一个Sno，都顺序扫描C表，读取全部的Cno，形成（Sno，Cno）对于每一个Sno的（Sno，Cno）全集，都在SC表中查找有没有与之对应的行若一个Sno的（Sno，Cno）全 集，都在SC表中找到对应关系， 则这个Sno的学生就选修了全部 的课程42SQL的数据查询SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名理解在SQL中没有全称量词，涉及到“全部”的问题只能通过“(∨x)P≡┐(

x(┐P))”的转化来解决，即：查询“没有一门课程是他没选修的”的学生姓名SQL规定：子查询的where句可以调用上面任意层次的主查询中关系的属性43SQL的数据查询SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名、理解1）查询未选过的所有课程select*fromCwherenotexists( select*fromSCwhereCno=C.Cno)2）查询某学生（设学号为X）未选过的所有课程select*fromCwherenotexists( select*fromSCwhereSno=XandCno=C.Cno)44SQL的数据查询SELECT语句的基本结构基本句型的综合应用7）检索学习全部课程的学生姓名、理解3）遍历S表，验证每个学生是不是这个X，若不是就把Sname投影出来selectSnamefromSwherenotexists( select*fromCwherenotexists( select*fromSCwhereSno=S.Sno andCno=C.Cno));45SQL的数据查询SELECT语句的基本结构基本句型的综合应用8）检索所学课程包含学生S3所学课程的学生学号ΠSNO，CNO（SC）÷ΠCNO（σSNO='S3‘（SC））SelectDistinctSnoFromSCasX WherenotExists( Select*FromSCasY WhereY.Sno=‘S3’andnotExists( Select*FromSCasZ WhereSno=X.SnoandCno=Y.Cno));46SQL的数据查询SELECT语句的基本结构基本句型的综合应用8）检索所学课程包含学生S3所学课程的学生学号理解“包含学生S3所学课程”是“全部”的一个限定条件思考：查询选修了‘LIU’老师所授全部课程的学生学号？47SQL的数据查询SELECT语句的完整结构句型执行过程①读取FROM子句中的数据源，执行笛卡尔积（×）②选择（σ）满足WHERE子句值的元组③按GROUPBY子句指定的列值，将元组进行分组④在③分出的所有组中，选取满足HAVING子句值的组⑤按SELECT子句的指定项，求值投影（Π）输出⑥按ORDERBY子句对⑤式的输出进行排序48SQL的数据查询SELECT语句的完整结构查询中的5个聚合函数49SQL的数据查询SELECT语句的完整结构应用示例统计选修课程的人次数SelectCOUNT(Sno)as人次数 FromSC;统计选修课程的人数SelectCOUNT(DistinctSno)as人数 FromSC;50SQL的数据查询SELECT语句的完整结构应用示例统计男学生的总人数和平均年龄SelectCOUNT(*)as总人数, AVG(Age)as平均年龄 FromS WhereSex='M';按性别统计学生的总人数和平均年龄SelectSex,COUNT(*)as总人数,

AVG(Age)as平均年龄 FromS GroupBySex;51SQL的数据查询SELECT语句的完整结构应用示例统计每门课程的学生选修人数，要求显示课程号、课程名和学生人数SelectC.Cno,Cname,COUNT(Sno)as学生人数 FromC,SC WhereC.Cno=SC.Cno GroupByC.Cno,Cname;52SQL的数据查询SELECT语句的完整结构应用示例按教师号统计每位教师每门课程的学生选修人数，要求：1）仅显示选修人数在3人（>=3）以上的信息2）显示TNO、CNO和选修人数3）显示时，查询结果按选修人数降序排列，人数相同按TNO升序、CNO降序排列53SQL的数据查询SELECT语句的完整结构应用示例SelectTno,C.Cno,COUNT(Sno)as选修人数 FromC,SC WhereC.Cno=SC.Cno GroupByTno,C.Cno

HavingCOUNT(*)>=3

OrderBy3DESC,Tno,C.CnoDESC54SQL的数据查询SELECT语句的完整结构［GROUPBY<列名序列>［HAVING<组条件表达式>］］作用1）数据按GROUPBY子句列名序列中的列值进行分组2）组内数据按SELECT子句中的聚合函数进行计算3）提取满足HAVING子句的条件表达式值的分组注意HAVING子句支持聚合函数WHERE子句不支持聚合函数SELECT子句只能取聚集函数 或GROUPBY子句指的列

55SQL的数据查询SELECT语句的完整结构［ORDERBY<列名︱列序号>［ASC︱DESC］ ［｛,<列名︱列序号>［ASC︱DESC］｝］对查询结果按子句中指定列的值排序，如果ORDERBY后有多个列名先按第一列名值排序再对于第一列值相同的行，按第二列名值排序依次类推……列序号是在SELECT子句中出现的序号（选的列是聚集函数或表达式时）ASC表示升序，DESC表示降序，缺省时表示升序56SQL的数据查询SELECT语句的完整结构应用示例问：在使用聚合函数进行数据统计时，为什么有的语句使用了GroupBy子句，有的没有？什么情况下，应该有？可以没有？57SQL的数据查询查询中的限制和规定Select子句的规定子句描述查询输出的表格结构，即输出值的列名或表达式格式SELECT［ALL︱DISTINCT］<列名或列表达式序列>︱*释义All︱Distinct，［保留︱消除］查询结果中的重复行*是对From子句中表的所有列的简写列表达式列名、常量、算数运算符、函数、聚合函数58SQL的数据查询查询中的限制和规定别名用关键字AS为列、表、表达式起别名，AS可以省略集合运算(Select查询1)UNION［ALL］(Select查询2)(Select查询1)INTERSECT［ALL］(Select查询2)(Select查询1)EXCEPT［ALL］(Select查询2)备注并交差运算的前提：相同的关系模式关键字ALL，表示保留运算后的重复元组59SQL的数据查询查询中的限制和规定示例SelectNativePlace,Sname,Sex FromS;SelectSno,Sname,Age,Sex,NativePlace FromS;Select* FromS;60SQL的数据查询查询中的限制和规定示例SelectSname,

Year(getdate())–AgeasBirthYear FromSASX;SelectSnameas姓名,‘BirthYear:’asBIRTH, 2011–AgeasYEAY,Lower(Sex)as性别FromS;说明函数getdate(),year(),month(),day()函数upper(),lower()61SQL的数据查询62SQL的数据查询查询中的限制和规定示例SelectSno FromSC;SelectAllSno FromSC;SelectDistinctSno FromSC;Selecttop5Sno FromSC;63SQL的数据查询条件表达式中的比较操作构成Where语句的条件运算符64SQL的数据查询条件表达式中的比较操作算数比较运算查询学号是S3的学生姓名SelectSnameFromSWhereSno=‘S3’;查询年龄小于18的学生姓名SelectSnameFromSWhereAge<18

;查询性别为男的学生的姓名、年龄和籍贯SelectSname,Age,NativePlace FromS WhereSex=‘M’

;65SQL的数据查询条件表达式中的比较操作区间运算查询年龄［不］在17~19岁之间的学生的学号SelectSnoFromSWhereAgeBetween17and19;SelectSnoFromSWhereAge>=17andAge<=19;SelectSnoFromS WhereAgenotBetween17and19;SelectSnoFromS Wherenot(Age>=17andAge<=19);SelectSnoFromSWhereAge<17orAge>19;66SQL的数据查询条件表达式中的比较操作字符匹配运算查询籍贯［不］是上海的学生姓名SelectSnameFromSWhereNativePlace=‘上海’;SelectSnameFromS WhereNativePlaceLike‘%上海%’;SelectSnameFromS WhereNativePlaceLike‘%上%海%’;SelectSnameFromS WhereNativePlace<>‘上海’;SelectSnameFromS WhereNativePlaceNotLike‘%上海%’;67SQL的数据查询条件表达式中的比较操作字符匹配运算注解LIKE与通配符“%”和“_”使用格式：[not]Like<匹配串>[Escape<换码字符>]“%”：字符串中所处位置的0~n个字符“_”

：字符串中所处位置的1个字符换码字符查询课程名以“DB_C%A”开头的课程号和学分SelectCno,CreditFromC WhereCnamelike‘DB\_C\%A%’

Escape‘\’;68SQL的数据查询条件表达式中的比较操作空值比较运算查询［未］缺考学生的学号和课程号SelectSno,CnoFromSC WhereGradeisNull;SelectSno,CnoFromSC WhereGradeisnotNull;说明，在Where语句中“isNULL”不能用“=NULL”替换“isnotNULL”不能用“<>NULL”替换69SQL的数据查询条件表达式中的比较操作空值比较运算查询［未］缺考过的学生的学号SelectDistinctSnoFromSC WhereGradeisNull;SelectDistinctSnoFromSC WhereGradeisnotNull;SelectSnoFromS WhereSnonotin(SelectSnoFromSC WhereGradeisNull);70SQL的数据查询条件表达式中的比较操作集合成员运算查询［未］选修‘C2’或‘C3’课程的学生学号SelectSnoFromSC WhereCno=‘C2’orCno=‘C3’;SelectSnoFromSC WhereCno=(‘C2’,‘C3’);SelectSnoFromSCWhereCnoin(‘C2’,‘C3’);SelectSnoFromSCWhereCno=SOME(‘C2’,‘C3’);SelectSnoFromSCWhereCnonotin(‘C2’,‘C3’);SelectSnoFromSCWhereCno<>ALL(‘C2’,‘C3’);71SQL的数据查询条件表达式中的比较操作集合成员运算查询［未］选修‘C2’或‘C3’课程的学生学号SelectSnoFromS WhereSnonotin( SelectSnofromSC WhereCnoin(‘C2’,‘C3’));SelectSnoFromS WhereSno<>ALL( SelectSnofromSC WhereCnoin(‘C2’,‘C3’));72SQL的数据查询条件表达式中的比较操作集合成员运算查询［未］选修‘LIU’老师所授课程的学生学号SelectSnoFromSC,C,T WhereTname=‘LIU’ andSC.Cno=C.CnoandC.TNO=T.Tno;SelectSnoFromSCWhereCnoin( SelectCnoFromCWhereTnoin( SelectTnoFromTWhereTname=‘LIU’));SelectSnoFromS WhereSnonotin (选过…的学号);73SQL的数据查询条件表达式中的比较操作集合成员运算查询选修了‘LIU’老师所授全部课程的学生姓名SelectSnameFromSWherenotExists( Select*FromC WhereTnoin(SelectTnoFromT

WhereTname=‘LIU’) andnotExists( Select*FromSC WhereSno=S.Sno andCno=C.Cno));74SQL的数据查询条件表达式中的比较操作集合成员运算查询至少有一门成绩超过S4一门成绩的学生学号SelectDistinctSnoFromSC WhereGrade>Some( SelectGradeFromSC WhereSno=‘S4’);75SQL的数据查询条件表达式中的比较操作集合成员运算查询平均成绩最高的学生学号使用导出表SelectS# From(SelectSno,AVG(Grade) FromSCGroupBySno)ASTmpTable(S#,AVG_Grade) WhereAVG_Grade>=ALL(SelectAVG(Grade) FromSCGroupBySno);76()ASRESULT(S#,AVG_GRADE)SELECTS#,AVG_GRADEFROMWHEREAVG_GRADE>80;

导出表的使用

在FROM子句中使用子查询,给子查询的结果起表名和相应列名例：检索平均成绩超过80分的学生学号和平均成绩

SELECTS#，AVG（GRADE）

FROMSCGROUPBYS#HAVINGAVG（GRADE）>80；用导出表：

SELECTS#，AVG（GRADE）FROMSCGROUPBYS#第三章关系数据库语言SQL3.3SQL的数据查询77SQL的数据查询条件表达式中的比较操作集合空否的测试Select… FromR Where［NOT］EXISTS(Select* FromS WhereS.A=R.Aand…);说明当内层集合非空，返回True，否则返回False78SQL的数据查询条件表达式中的比较操作集合中是否有重复元组的判定查询只选修了一门课的学生的学号和姓名SelectSno,SnameFromS Whereunique(SelectSnoFromSC WhereSno=S.Sno);SelectS.Sno,Sname FromS,SC WhereS.Sno=SC.Sno

GroupbyS.Sno,Sname

HavingCount(Cno)=1;79SQL的数据查询嵌套查询的改进写法导出表在From子句中使用子查询，子查询必须起别名命名的导出表只在From中起作用，离开From子句失效WITH语句和临时视图（SQLServer2005以上版本）作用类同于导出表，但语句的逻辑组织比含导出表更清晰WITHTmpTable(S#,AVG_Grade)AS SelectSno,AVG(Grade)FromSCGroupBySno SelectS#FromTmpTable WhereAVG_Grade>=ALL(SelectAVG_Grade FromTmpTable);80SQL的数据查询基本表的联接操作略81SQL的数据查询SQL3中的递归查询略82SQL的数据更新数据插入句法1）单元组的插入INSERTINTO基本表名[(列名表)]VALUES(元组值)2）多元组的插入：不是所有版本的DBMS都支持INSERTINTO基本表名[(列名表)] VALUES(元组值),…,(元组值)3）查询结果的插入INSERTINTO基本表名[(列名表)]<SELECT查询语句>4）表的插入：INSERTINTO基本表名1[(列名表)]TABLE基本表名283SQL的数据更新数据插入说明若插入的元组值，其属性个数、次序和域，与表结构的定义一致，则列名表可以省略否则需要列名表，且列名表的属性个数和次序，需与待插入值一一对应数据插入受关系完整性约束的制约数据类型实体完整性：主键值唯一，非空参照完整性：不允许引用不存在的实体用户自定义的完整性84SQL的数据更新数据插入示例InsertIntoS(Sno,Sname,Age,Sex,NativePlace) Values('S5','GU',20,'M',NULL);InsertIntoSValues('S5','GU',20,'M',NULL);InsertIntoS(Sno,Sname,Age,Sex) Values('S5','GU',20,'M');InsertIntoS(Sname,Sno,Sex,Age) Values('GU','S5','M',20);InsertIntoSC(S#,C#)Values('S5','C8');InsertIntoSCValues('S5','C8',NULL);85数据插入示例InsertIntoS_Grade(S#,AVG_Grade) SelectSno,AVG(Grade)FromSC WhereSnoin(SelectSnoFromSWhereSex＝'M') GroupBySno HavingAVG(Grade)＞80;InsertIntoSC(Sno,Cno)TABLESC4;SQL的数据更新86SQL的数据更新数据插入示例，问：InsertIntoSCValues('S4','C569',105);InsertIntoSCValues(‘S4’,‘C5’,105);87SQL的数据更新数据插入示例，问：InsertIntoSCValues(‘S4’,‘C5’,95);InsertIntoSCValues(‘S4’,‘C4’,95);如图示4条相关数据的插入顺序 是什么？88SQL的数据更新数据删除句法DELETEFROM<基本表名>[WHERE条件表达式]语义是从基本表中删除满足条件表达式的元组注意DELETE语句只能从一个基本表中删除元组WHERE子句中条件可以嵌套，也可以是来自几个基本表的复合条件如果没有WHERE子句，则删除表的所有元组，表成为空表89SQL的数据更新数据删除示例DeleteFromSCWhereGradeisNULL;DeleteFromCWhereCnameLike‘%M%’;DeleteFromSCWhereCnoin( SelectCnoFromCWhereCname=‘MATHS’);DeleteFromSC,C WhereSC.Cno=C.CnoandCname=‘MATHS’;DeleteFromSCWhereCno=‘C4’andGrade<( SelectAVG(Grade)FromSCWhereCno=‘C4’);90SQL的数据更新数据删除示例，问DeleteFromSWhereSnameLike‘%U%’;91SQL的数据更新数据删除示例，问DeleteFromSWhereSnameLike‘%U%’; DeleteFromSCWhereSnoin( SelectSnoFromS WhereSnameLike‘%U%’); DeleteFromSWhereSnameLike‘%U%’;92SQL的数据更新数据删除示例，问如图示4条相关数据的删除顺序是什么？DropTableSC

与 DeleteFromSC

的区别是什么93SQL的数据更新数据删除当LIU老师不再教授课程时，删除其基本信息DeleteFromT WhereTname=‘LIU’ ANDTnoNotin（SelectTnoFromC）当PM课程没人选修时，删除这门课程信息当WANG同学不再选修课程时，删除其基本信息94SQL的数据更新数据删除TRUNCATETABLE<表名>与DELETEFrom<表名>功能上相同：均删除表中的全部行但TRUNCATETABLE比DELETE速度快，且使用的系统和事务日志资源少DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项TRUNCATETABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放95SQL的数据更新数据删除TRUNCATETABLE<表名>与DELETEFrom<表名>TRUNCATETABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变；新行标识所用的计数值重置为该列的种子，如果想保留标识计数值，应使用DELETE如果要删除表定义及其数据，应使用DROPTABLE语句对于由FOREIGNKEY约束引用的表，不能使用TRUNCATETABLE，而应使用不带WHERE子句的DELETE语句由于TRUNCATETABLE不记录在日志中，所以它不能激活触发器TRUNCATETABLE不能用于参与了索引视图的表96SQL的数据更新数据修改句法UPDATE<基本表名> SET列名=值表达式[，列名=值表达式…]│ROW=(元组) [WHERE条件表达式]语义修改基本表中满足条件表达式元组的指定属性值说明WHERE子句表示要修改的元组需满足的条件SET子句表示要修改的