《数据库系统概论：chp3 关系数据库标准语言SQL》由会员分享，可在线阅读，更多相关《数据库系统概论：chp3 关系数据库标准语言SQL（239页珍藏版）》请在金锄头文库上搜索。
1、数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQLSQL（StructuredQueryLanguage）1本章是整个课程的重点之一，内容比较多。SQL是关系数据库的标准语言，是介于关系代数与关系演算之间的查询语言。本章主要介绍SQL的四个功能：数据定义；数据操纵；数据控制；嵌入式SQL。要结合实验教材学习本章，学习并应用SQLServer2000的SQL语言。2要求要求-掌握掌握SQL的概念及使用的概念及使用掌握掌握SQL数据定义语句:会定义、修改和撤消基本表、索引和视图嵌入式SQL语句的应
2、用:知道嵌入式SQL怎么用熟练掌握熟练掌握SQL数据查询语句:熟练的运用SELECT语句表达各种查询SQL视图的应用:会定义、查询、更新和删除视图SQL数据更新语句的应用:熟练地运用插入语句、修改语句和删除语句更新数据3第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述概述3.2学生学生-课程数据库课程数据库3.3数据定义数据定义3.4数据查询数据查询3.5数据更新数据更新3.6视图视图3.7小结小结4SQL概述概述3.1.1SQL的产生与发展的产生与发展3.1.2SQL的特点的特点3.1.3SQL的基本概念的基本概念5SQL标准的进展过程标准的进展过程1974年，由Boy
3、ce和Chamber提出；1975-1979年，在SystemR上实现，由IBM的SanJose研究室研制，称为Sequel；以后进一步修改完善扩充为SQL。SQL是关系数据库语言的国际标准标准大致页数发布日期nSQL/861986.10nSQL/89(FIPS127-1)120页1989年nSQL/92(SQL2)622页1992年nSQL99(SQL3)1700页1999年nSQL20032003年6本章将针对SQL/2作一总体介绍，不是SQL的全部，并且为了突出基本概念和基本功能，略去了许多语法细节。应该注意到：许多具体的RDBMS实现的SQL与标准有一定区别有些标准在具体系统中还未实现
4、而具体系统对SQL都有一定的扩充我们实验使用SQLServer2000系统，因此在学习教材上的SQL语言有关内容时，要结合SQLServer2000的SQL语言，进行对照理解应用。73.1.2SQL的特点的特点1.综合统一n集数据定义语言（DDL），n数据操纵语言（DML），n数据控制语言（DCL）。n可以独立完成数据库生命周期中的全部活动：定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护数据库安全性、完整性控制等n用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。n数据操作符统一82.高度非过程化高度非过程化非关系数据模型的数据操纵语言“
5、面向过程面向过程”，必须制定存取路径SQL只要提出“做什么”，无须了解存取路径。存取路径的选择，以及SQL的操作过程，由系统自动完成。93.面向集合的操作方式面向集合的操作方式非关系数据模型，采用面向记录的操作方式，操作对象是一条记录SQL采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象，可以是元组的集合104.以同一种语法结构以同一种语法结构提供多种使用方式提供多种使用方式SQL是独立的语言能够独立地用于联机交互的使用方式SQL又是嵌入式语言SQL能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用115.语言简洁，易学易用语言简洁，易学
6、易用SQL功能极强，接近英语口语，完成核心功能核心功能只用了9个动词。12SQL的基本概念（续）的基本概念（续）SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模外模式式模模 式式内模内模式式SQL支持关系数据库三级模式结构基本表基本表本身独立存在的表一个关系就对应一个基本表一个表可以带若干索引一个(或多个)基本表对应一个存储文件13SQL的基本概念（续）的基本概念（续）SQL视图视图2视图视图1基本表基本表2基本表基本表1基本表基本表3基本表基本表4存储文件存储文件2存储文件存储文件1外模外模式式模模 式式内模内模式式S
7、QL支持关系数据库三级模式结构存储文件存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明14SQL的基本概念（续）的基本概念（续）SQL支持关系数据库三级模式结构视图视图从一个或几个基本表导出的表数据库中只存放视图的定义，而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图SQL视图2视图1基本表1基本表2基本表3基本表4外模式模式内模式存储文件1存储文件215第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述概述3.2学生学生-课程数据库课程数据库3.3数据定义数据定义3.4数据查询数据查询3.5数据更新数据更新3.6视图视图3.7小结小结16
8、3.2本章用例：学生本章用例：学生-课程课程数据库数据库学生表：Student(Sno,Sname,Ssex,Sage,Sdept)课程表：Course(Cno,Cname,Cpno,Ccredit)学生选课表：SC(Sno,Cno,Grade)17课程号课程号Cno课程课程Cname先行课先行课Cpno学分学分Ccredit1234567数据库数据库数学数学信息系统信息系统操作系统操作系统数据结构数据结构数据处理数据处理PASCAL语言语言516764243424学学号号Sno姓名姓名Sname性性别Ssex年年龄Sage所在系所在系Sdept200215121200215122200215
9、123200515125李勇李勇刘晨刘晨王敏王敏张立立男男女女女女男男20191819CSCSMAIS学学号号Sno课程号课程号Cno成绩成绩Grade200215121200215121200215121200215122200215122123239285889080183.3数据定义数据定义SQL的数据定义功能:模式定义、表定义、视图和索引的定义 193.3数据定义数据定义3.3.1模式的定义与删除模式的定义与删除3.3.2基本表的定义、删除与修改基本表的定义、删除与修改3.3.3索引的建立与删除索引的建立与删除203.3.1模式的定义与删除SQL模式定义为基本表的集合。定义模式实际上定
10、义了一个命名空间，命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。模式由模式名和模式拥有者的用户名或账号来确定。由于“SQL模式”这个名词学术味太重，因此大多数RDBMS中不愿采用这个名词，而是采用“数据库”(Database)这个名词。大多数系统中把“创建SQL模式”称为“创建数据库”。另外有的系统允许使用多库。213.3.1模式的定义与删除SQL模式的创建： CREATESCHEMA模式名AUTHORIZATION用户名; n如果没有指定，那么隐含为例1定义一个学生-课程模式S-TCREATESCHEMA“S-T”AUTHORIZATIONWANG;为用户W
11、ANG定义了一个模式S-T例2CREATESCHEMAAUTHORIZATIONWANG；隐含为用户名WANG22CREATESCHEMAAUTHORIZATION|例3CREATESCHEMATESTAUTHORIZATIONZHANGCREATETABLETAB1(COL1SMALLINT，COL2INT，COL3CHAR(20)，COL4NUMERIC(10，3)，COL5DECIMAL(5，2)；为用户ZHANG创建了一个模式TEST，并在其中定义了一个表TAB1。23删除模式删除模式nDROPSCHEMACASCADE(级联)删除模式的同时，把该模式中所有的数据库对象全部删除REST
12、RICT(限制)如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。例4DROPSCHEMAZHANGCASCADE；24SQLServer数据库的管理数据库的管理创建数据库CREATEDATABASE数据库名；修改数据库ALTERDATABASE数据库名；打开、选择要操作的数据库USEDATABASE数据库名；删除数据库DROPDATABASE数据库名；253.3数据定义数据定义3.3.1模式的定义与删除模式的定义与删除3.3.2基本表的定义、删除与修改基本表的定义、删除与修改CREATETABLEDROPTABLEALTERT
13、ABLE3.3.3索引的建立与删除索引的建立与删除263.3.2基本表的定义一、基本表定义语句格式参见P88. CREATE TABLE表名(列名数据类型default缺省值not null，列名数据类型default 缺省值not null，primary key (列名，列名)，foreign key(列名，列名)references表名(列名，列名)，check(条件);列级完整性约束条件表级完整性约束条件外键约束主键约束外键引用的表,列检查约束27学生表表Student例5建立“学生”表Student，学号是主码，姓名取值唯一。CREATETABLEStudent(SnoCHAR(9)
14、PRIMARYKEY,/*列级完整性约束条件*/SnameCHAR(20)UNIQUE,/*Sname取唯一值*/SsexCHAR(2),SageSMALLINT,SdeptCHAR(20)CHECK(Ssex=男ORSsex=女)如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。28课程表课程表Course 例6 建立一个“课程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，CnameCHAR(40)，CpnoCHAR(4)，CcreditSMALLINT，FOREIGNKEY(Cpno)REFE
15、RENCESCourse(Cno),CHECK(Ccredit0ANDCcredit=10); Cpno是外码是外码 被参照表是被参照表是Course被参照列是被参照列是Cno29学生选课表学生选课表SC 例例7 建立一个建立一个“学生选课学生选课”表表SCSCCREATETABLESC(SnoCHAR(9)，CnoCHAR(4)，GradeSMALLINT，PRIMARYKEY(Sno，Cno)，/*主码由两个属性构成，必须作为表级完整性进行定义*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*表级完整性约束条件，Sno是外码，被参照表是Student*/F
16、OREIGNKEY(Cno)REFERENCESCourse(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/);30二、数据类型二、数据类型SQL中域域的概念用数据类型数据类型来实现定义表的属性时，需要指明其数据类型及长度选用哪种数据类型n取值范围n要做哪些运算31二、数据类型二、数据类型数据数据类型型含含义CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串INT长整数（也可以写作INTEGER）SMALLINT短整数NUMERIC(p，d)定点数，由p位数字（不包括符号、小数点）组成，小数后面有d位数字REAL取决于机器精度的浮点数Dou
17、ble Precision取决于机器精度的双精度浮点数FLOAT(n)浮点数，精度至少为n位数字DATE日期，包含年、月、日，格式为YYYY-MM-DDTIME时间，包含一日的时、分、秒，格式为HH:MM:SS32基本表的修改：格式基本表的修改：格式修改基本表定义(ALTER) 命令格式：ALTERtable表名add新列名数据类型完整性约束drop完整性约束名drop列名modify列名数据类型；增加新列增加新列删除完整性约束删除完整性约束删除列删除列修改列定义修改列定义33四、修改基本表四、修改基本表ALTERTABLEADD完整性约束DROPDROPALTERCOLUMN；增加新列增加新
18、列删除完整性约束删除完整性约束删除列删除列修改列定义修改列定义34例8向Student表增加“入学时间”列，日期型。ALTERTABLEStudentADDS_entranceDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。例9将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。ALTERTABLEStudentALTERCOLUMNSageINT；例10增加课程名称必须取唯一值的约束条件。ALTERTABLECourseADDUNIQUE(Cname);35五、删除基本表五、删除基本表DROPTABLERESTRICT|CASCADE；nRESTRICT：删除表是有
19、限制的。欲删除的基本表，不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除nCASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除36删除基本表删除基本表例11删除Student表DROPTABLEStudentCASCADE;n基本表定义被删除，数据被删除n表上建立的索引、视图、触发器等一般也将被删除37若表上建有视图，CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudent WHERESdept=IS；选择选择RESTRICT时表不能删除时表不能删除DROPTABLEStudentRESTRICT;-ER
20、ROR:cannotdroptableStudentbecauseotherobjectsdependonit选择选择CASCADE时可以删除表，视图也自动被删除时可以删除表，视图也自动被删除DROPTABLEStudentCASCADE;-NOTICE:dropcascadestoviewIS_StudentSELECT*FROMIS_Student;-ERROR:relationIS_Studentdoesnotexist383.3数据定义数据定义3.3.1模式的定义与删除模式的定义与删除3.3.2基本表的定义、删除与修改基本表的定义、删除与修改CREATETABLEDROPTABLEAL
21、TERTABLE3.3.3索引的建立与删除索引的建立与删除39索引作用索引作用建立索引可有效提高查询的速度。如果把一个基本库表比作一本书，索引就好像书的目录，通过查询目录，可找到相关章节的页号，从而可迅速地找到那一节内容。不同的是，基本表可建立不止一个索引，它可按不同的属性或表达式建立多个索引。40索引索引RDBMS中索引一般采用B+树、HASH索引来实现nB+树索引具有动态平衡的优点nHASH索引具有查找速度快的特点索引是关系数据库的内部实现技术，属于内模式的范畴，索引属于物理存储的路径概念，而不是逻辑的概念。索引规定了基本表记录的一种逻辑顺序。索引和基本表的关系类似与书的目录与正文的关系，
22、索引是依附于基本表的，随表数据的更新而变化。41索引索引创建索引的原则创建索引的原则： （1）对较大的表才建立索引（约5000条记录以上），并检索的数据少于总行数的2%到4% （2）一个表可建立任意多个索引，但不能太多，索引过多则耗费空间，且降低了插入、删除、更新的效率，会增加系统维护的开销。索引建立后由系统维护， （3）经常出现在WHERE子句，或出现在联接条件中的列，作为索引关键字 （4）索引关键字可以是一个列，也可以是多个列组合成的复合索引。如果是复合索引，则查询条件中含有主关键字时，系统使用索引，加快查询速度。如果查询条件中只含有次关键字，则系统不使用索引。 （5）索引信息会存储到数据
23、字典中。42索引索引谁可以建立索引DBA或表的属主（即建立表的人）DBMS一般会自动建立以下列上的索引PRIMARYKEYUNIQUE谁维护索引（如：基本表数据变动时）DBMS自动完成使用索引DBMS自动选择是否使用索引以及使用哪些索引43一、建立索引一、建立索引语句格式CREATEUNIQUECLUSTERINDEXON(,)；UNIQUE唯一索引，每个索引值对应唯一的记录CLUSTER聚簇索引，索引项的顺序与表中记录的物理顺序一致。银行客户表的账号余额列适合建立聚簇索引吗？44建立聚簇索引建立聚簇索引例13CREATECLUSTERINDEXStusnameONStudent(Sname)
24、；在Student表的Sname（姓名）列上建立一个聚簇索引在最经常查询的列上建立聚簇索引，以提高查询效率一个基本表上最多只能建立一个聚簇索引经常更新的列不宜建立聚簇索引45建立唯一索引建立唯一索引例14为学生-课程数据库中的Student，Course，SC三个表建立索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；Student表按学号升序建唯一索引Course表按课程号升序建唯一索引SC表按学号升序和
25、课程号降序建唯一索引46二、删除索引二、删除索引建立索引是为了加快查询操作，但是如果数据增加删除修改频繁，系统会花费许多时间来维护索引，这时，可以删除一些不必要的索引。DROPINDEX；删除索引时，系统会从数据字典中删去有关该索引的描述。例15删除Student表的Stusname索引DROPINDEXStusname；47小结：数据定义小结：数据定义SQL的数据定义功能:模式定义、表定义、视图和索引的定义 48第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述概述3.2学生学生-课程数据库课程数据库3.3数据定义数据定义3.4数据查询数据查询3.5数据更新数据更新3.6
26、视图视图3.7小结小结493.4SQL数据查询功能数据查询功能3.4.3嵌套查询嵌套查询IN谓词比较算符ANY 或ALL谓词EXISTS谓词3.4.4集合查询集合查询3.4.5SELECT语句的一语句的一般形式般形式3.4.13.4.1 单表查询单表查询n选择列n选择元组n排序n集函数n分组3.4.23.4.2 连接查询连接查询n等值与非等值连接n自身连接n外连接n复合条件连接50SQL数据查询语句与关系代数数据查询语句与关系代数 数据查询是关系运算理论在SQL中的主要体现。数据查询语句既有关系代数的特点，又有关系演算的特点SELECT语句句型是从关系代数表达式演变来的，它能表达所有的关系代数
27、运算，且更灵活：select A1,A2,AnfromR1,R2,RmwhereF；A1,A2,An(F(R1R2Rm)51SQL中中Select语句格式语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；SELECT语句的执行过程是：从FROM子句后的表或视图中，取出由SELECT子句指定的列；如有WHERE子句，则只在满足条件的元组中取数；如有GROUPBY子句，则对前面的结果按列名1分组，每组输出一行，HAVING控制选满足条件表达式1的组输出；如有ORDERBY子句，则按列名2来升或降序排列输出。523.4.1单表
28、查询单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列一、选择表中的若干列: SELECT子句子句查询指定列；查询全部列；查询经过计算的值；列更名二、选择表中的若干元组:WHERE子句消除取值重复的行；查询满足条件的元组常用的查询谓词：BETWEEN,IN,LIKE,NULL,AND,OR三、对查询结果排序：ORDERBY子句四、使用集函数：COUNT,SUM,AVG,MAX,MIN五、对查询结果分组:GROUPBYHAVING子句53一、一、选择表中的若干列选择表中的若干列1.查询指定列例1查询全体学生的学号与姓名。Sno,Sname(Student)或1,2(Student)SEL
29、ECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname,Sno,SdeptFROMStudent542.查询全部列查询全部列选出所有属性列：n在SELECT关键字后面列出所有列名n将指定为*例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FROMStudent；553.查询经过计算的值查询经过计算的值SELECT子句的可以为：算术表达式字符串常量函数列别名56例4查全体学生的姓名及其出生年份。SELECTSname,2004-Sage/*假定当年的年份为200
30、4年*/FROMStudent；输出结果：输出结果：李勇1984刘晨1985王敏1986张立1985查询经过计算的值（续）查询经过计算的值（续）57查询经过计算的值（续）查询经过计算的值（续）例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名SELECTSname，YearofBirth:，2004-Sage，LOWER(Sdept)FROMStudent；输出结果：输出结果：李勇YearofBirth:1984cs刘晨YearofBirth:1985is王敏YearofBirth:1986ma张立YearofBirth:1985is58查询经过计算的值（续）查询经过计算的值
31、（续）使用列别名改变查询结果的列标题:SELECTSnameNAME,YearofBirth:BIRTH,2004-SageBIRTHDAY,LOWER(Sdept)DEPARTMENTFROMStudent输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1984cs刘晨YearofBirth:1985is王敏YearofBirth:1986ma张立YearofBirth:1985is593.4.1单表查询单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列:SELECT子句查询指定列；查询全部列；查询经过计算的值；列更名二、选择表中的若干元
32、组二、选择表中的若干元组: WHERE子句子句消除取值重复的行；查询满足条件的元组常用的查询谓词：BETWEEN,IN,LIKE,NULL,AND,OR三、对查询结果排序：ORDERBY子句四、使用集函数：COUNT,SUM,AVG,MAX,MIN五、对查询结果分组:GROUPBYHAVING子句60二、选择表中的若干元组二、选择表中的若干元组1.消除取值重复的行如果没有指定DISTINCT关键词，则缺省为ALL例6查询选修了课程的学生学号。SELECTSnoFROMSC；等价于：SELECTALLSnoFROMSC；执行上面的SELECT语句后，结果为：Sno2002151212002151
33、2120021512120021512220021512261消除取值重复的行（续）消除取值重复的行（续）指定DISTINCT关键词，去掉表中重复的行SELECTDISTINCTSnoFROMSC；执行结果：Sno200215121200215122622.查询满足条件的元组查询满足条件的元组查 询 条条 件件谓 词比较=，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件（逻辑运算） AND，OR，NOT表3.4常用的查询条件事物之间的关系事物
34、之间的关系63概念复习：谓词与量词谓词逻辑谓词逻辑：分析命题逻辑命题逻辑中原子命题原子命题的内在联系。谓词逻辑中，原子命题分解成个体词和谓词。个体词个体词是可以独立存在的客体，它可以是具体事物或抽象的概念，如小张，房子，大米，思想，实数2等等。谓词谓词，是用来刻划个体词的性质或事物之间的关系的词。量词量词，是在命题中表示数量的词，两类：全称量词，表示“所有的”或“每一个”；存在量词，表示“存在某个”或“至少有一个”。64概念复习：谓词与量词例如三个简单命题：(1)ln5是无理数；(2)张三比李四高4cm；(3)郑州位于北京和广州之间。ln5，张三，李四，郑州，北京，广州等都是个体词，“是无理数
35、”，“比高4cm”，“位于和之间”等都是谓词。注意，单独的个体词和谓词不能构成命题，注意，单独的个体词和谓词不能构成命题，将个体词和谓词分开将个体词和谓词分开也也不是命题不是命题。 65(1)比较大小比较大小例7查询计算机科学系全体学生的名单。Sname(Cdept=CS(Student)SELECTSnameFROMStudentWHERESdept=CS尽量避免全表扫描尽量避免全表扫描例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage20；例9查询考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMS
36、CWHEREGrade60；66（2）确定范围）确定范围谓词谓词:BETWEENANDNOTBETWEENAND例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23例11查询年龄不在2023岁之间的学生姓名、系别和年龄SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；67(3)确定集合确定集合谓词：谓词：IN,NOTIN例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名
37、和性别。SELECTSname,SsexFROMStudentWHERESdeptIN(IS，MA，CS);例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptNOTIN(IS，MA，CS);68(4)字符匹配字符匹配谓词：谓词：NOTLIKEESCAPE 可是固定字符串或通配符 % 或 _。%（百分号）代表任意长度（长度可为0）的字符串，_（下横线）代表任意单个字符。(一个汉字占两个ASCII字符)1)匹配串为固定字符串例14 查询学号为200215121的学生的详细情况。 SELECT*FROMSt
38、udentWHERESnoLIKE200215121等价于： SELECT * FROM Student WHERE Sno = = 200215121；69(4)字符匹配（续）字符匹配（续）2)匹配串为含通配符的字符串例15查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘%；例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳_；70(4)字符匹配字符匹配（续）（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，Sn
39、oFROMStudentWHERESnameLIKE_阳%例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%71(4)字符匹配（续）字符匹配（续）3)使用换码字符将通配符转义为普通字符例19查询DB_Design课程的课程号和学分。SELECTCno,CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE例20查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；ESCAPE表示表示“”
40、为换码字符为换码字符72(5)涉及空值的查询涉及空值的查询n谓词：谓词：ISNULL或ISNOTNULLn“IS”不能用“=”代替例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；73(6)多重条件查询多重条件查询逻辑运算符：AND和OR来联结多个查询条件lAND的优先级高于ORl可以用括号改变优先级可用来实现多种其他谓词lNOTINlNOTBETWE
41、ENAND74(6)多重条件查询（续）多重条件查询（续）例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage20；75(6)多重条件查询多重条件查询（续）（续）改写改写例例12例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname,SsexFROMStudentWHERESdeptIN(IS,MA,CS)可改写为：SELECTSname，SsexFROMStudentWHERESdept=ISORSdept=MAORSdept=CS；763.4.1单表查询单表查询单表查
42、询是指仅涉及一个表的查询一、选择表中的若干列:SELECT子句查询指定列；查询全部列；查询经过计算的值；列更名二、选择表中的若干元组:WHERE子句消除取值重复的行；查询满足条件的元组常用的查询谓词：BETWEEN,IN,LIKE,NULL,AND,OR三、对查询结果排序：三、对查询结果排序：ORDER BY子句子句四、使用集函数：COUNT,SUM,AVG,MAX,MIN五、对查询结果分组:GROUPBYHAVING子句77三、三、ORDERBY子句子句ORDERBY子句n可以按一个或多个属性列排序n升序：ASC；降序：DESC；缺省值为升序当排序列含空值时（空值的ASCII码值最大）nAS
43、C：排序列为空值的元组最后显示nDESC：排序列为空值的元组最先显示78ORDERBY子句子句（续）（续）例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno,GradeFROMSCWHERECno=3ORDERBYGradeDESC例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept,SageDESC793.4.1单表查询单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列:SELECT子句查询指定列；查询全部列；查询经过计算的值；列更名二、选择表中的若干元
44、组:WHERE子句消除取值重复的行；查询满足条件的元组常用的查询谓词：BETWEEN,IN,LIKE,NULL,AND,OR三、对查询结果排序：ORDERBY子句四、使用聚集函数四、使用聚集函数：COUNT,SUM,AVG,MAX,MIN五、对查询结果分组:GROUPBYHAVING子句80四、聚集函数四、聚集函数聚合函数对一组值执行计算并返回单一的值。除COUNT函数之外，聚合函数忽略空值。聚合函数经常与SELECT语句的GROUPBY子句一同使用。AVGMAXBINARY_CHECKSUMMINCHECKSUMSUMCHECKSUM_AGGSTDEVCOUNTSTDEVPCOUNT_BIG
45、VARGROUPINGVARP81四、聚集函数四、聚集函数计数COUNT（DISTINCT|ALL*）统计元组个数与列名无关COUNT（DISTINCT|ALL）按列名统计元组个数计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）最大最小值MAX（DISTINCT|ALL）MIN（DISTINCT|ALL）82聚集函数聚集函数（续）（续）例26查询学生总人数。SELECTCOUNT(*)FROMStudent；例27查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；/*不管选几门课，每学号取一行计数*/例28计算1号课程
46、的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=183聚集函数聚集函数（续）（续）例29查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno=1例30查询学生200215012选修课程的总学分数。SELECTSUM(Ccredit)FROMSCWHERSno=200215012andCcreditISNOTNULL;843.4.1单表查询单表查询单表查询是指仅涉及一个表的查询一、选择表中的若干列:SELECT子句查询指定列；查询全部列；查询经过计算的值；列更名二、选择表中的若干元组:WHERE子句消除取值重复的行；查询满足条件
47、的元组常用的查询谓词：BETWEEN,IN,LIKE,NULL,AND,OR三、对查询结果排序：ORDERBY子句四、使用集函数：COUNT,SUM,AVG,MAX,MIN五、对查询结果分组对查询结果分组: GROUP BY HAVING 子句子句85五、五、GROUPBY子句子句GROUPBY子句分组：细化聚集函数的作用对象未对查询结果分组，聚集函数将作用于整个查询结果对查询结果分组后，聚集函数将分别作用于每个组作用对象是查询的中间结果表按指定的一列或多列值分组，一列或多列值相等的为一组86GROUPBY子句（续）子句（续）例31求各个课程号及相应的选课人数。SELECTCno,COUNT(
48、Sno)FROMSCGROUPBYCno查询结果：CnoCOUNT(Sno)122234344433548SELECT子句中包含聚合函数，则计算每组的汇总值。指定GROUPBY时，选择列表中任一非聚合表达式内的所有列非聚合表达式内的所有列都应包含在GROUPBY列表中，或者GROUPBY表达式必须与选择列表表达式完全匹配。87HAVING子句（续）子句（续）指定组或聚合的搜索条件例32查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；按学号分组，每组中记录数3的组才被显示。88GROUPBY子句（续）子句（续）HAVING短语与
49、WHERE子句的区别：作用对象不同WHERE子子句句作用于基表或视图，从中选择满足条件的元组HAVING短语短语作用于组，从中选择满足条件的组。893.4SQL数据查询功能数据查询功能3.4.3嵌套查询嵌套查询IN谓词比较算符ANY 或ALL谓词EXISTS谓词3.4.4集合查询集合查询3.4.5SELECT语句的一语句的一般形式般形式3.4.13.4.1 单表查询单表查询n选择列n选择元组n排序n集函数n分组3.4.23.4.2 连接查询连接查询n等值与非等值连接n自身连接n外连接n复合条件连接903.4.2连接查询连接的概念连接操作的执行过程一、等值与非等值连接查询二、自身连接三、外连接四
50、、复合条件连接913.4.2连接查询连接查询连接查询：同时涉及多个表的查询组合两个或多个表的内容，并生成合并了每个表的行和列的结果集。通常使用各个表共有的数据联接这些表。连接条件或连接谓词：用来连接两个表的条件一般格式：n.n.BETWEEN.AND.连接字段：连接谓词中的列名称n连接条件中的各连接字段类型必须是可比可比的，但名字不必是相同的92连接操作的执行过程连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个
51、元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕。93连接操作的执行过程（续）连接操作的执行过程（续）排序合并法(SORT-MERGE)（有的DBMS不提供,如：ORACLE）常用于等值连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1中的全部元组都处理完毕。94连接操作
52、的执行过程（续）连接操作的执行过程（续）索引连接(INDEX-JOIN)对表2按连接字段建立索引（这样在表2中查询速度快）对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。953.4.2连接查询联接条件可在FROM或WHERE子句中指定，WHERE子句中指定的内联接称为旧式内联接。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选联接条件所选的行。联接可分为以下几类：内联接innerjoin（典型的联接运算，使用像=或之类的比较运算符）。包括相等联接和自然联接。内联接使用比较运算符根据每
53、个表共有的列的值匹配两个表中的行。外联接outerjoin。外联接可以是左向外联接、右向外联接或完整外部联接。交叉联接crossjoin。交叉联接也称作笛卡尔积。如果添加一个WHERE子句，则交叉联接的作用将同内联接一样。963.4.2连接查询连接的概念连接操作的执行过程一、等值与非等值连接查询二、自身连接三、外连接四、复合条件连接97一、等值与非等值连接查询一、等值与非等值连接查询等值连接：连接运算符为=例33查询每个学生及其选修课程的情况SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno98等值与非等值连接查询（续）等值与非等
54、值连接查询（续）Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade200215121李勇男20CS200215121192200215121李勇男20CS200215121285200215121李勇男20CS200215121388200215122刘晨女19CS200215122290200215122刘晨女19CS200215122380查询结果：查询结果：99等值与非等值连接查询（续）等值与非等值连接查询（续）自然连接：由于重复相同的信息没有意义，因此可以通过更改选择列表消除两个相同列中的一个。例34对例33用自然连接完成。SELECTStudent
55、.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；等价于：SelectStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudentJOINSCONStudent.Sno=SC.Sno100二、自身连接二、自身连接自身连接：一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀例35查询每一门课的间接先修课（即先修课的先修课）SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，Co
56、urseSECONDWHEREFIRST.Cpno=SECOND.Cno；101自身连接（续）自身连接（续）WHEREFIRST.Cpno=SECOND.CnoFIRST表（Course表）SECOND表（Course表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语语言言 6 4Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构
57、7 4 6数据处理数据处理 2 7PASCAL语语言言 6 4102三、外连接三、外连接外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出，无值的列填NULL左外连接与右外连接例36改写例33SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTJOINSCON(Student.Sno=SC.Sno)103三、外连接三、外连接在FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：LEFTJOIN或LEFTOUTERJOIN。左向外联接的
58、结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。RIGHTJOIN或RIGHTOUTERJOIN。右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。FULLJOIN或FULLOUTERJOIN。完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。104外连接（续外连接（续）执行结果：执行结果：Student.SnoSn
59、ameSsexSageSdeptCnoGrade200215121李勇男20CS192200215121李勇男20CS285200215121李勇男20CS388200215122刘晨女19CS290200215122刘晨女19CS380200215123王敏女18MANULLNULL200215125张立男19ISNULLNULL105连接查询（续）连接查询（续）一、等值与非等值连接查询二、自身连接三、外连接四、复合条件连接106四、复合条件连接四、复合条件连接复合条件连接：WHERE子句中含多个连接条件例37查询选修2号课程且成绩在90分以上的所有学生SELECTStudent.Sno,S
60、nameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND/*连接谓词*/SC.Cno=2ANDSC.Grade90；/*其他限定条件*/107复合条件连接（续）复合条件连接（续）例38查询每个学生的学号、姓名、选修的课程名及成绩SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，Course/*多表连接多表连接*/WHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；1083.4SQL数据查询功能数据查询功能3.4.3嵌套查询嵌套查询IN谓词比较算符ANY 或ALL谓词EXISTS
61、谓词3.4.4集合查询集合查询3.4.5SELECT语句的一语句的一般形式般形式3.4.13.4.1 单表查询单表查询n选择列n选择元组n排序n集函数n分组3.4.23.4.2 连接查询连接查询n等值与非等值连接n自身连接n外连接n复合条件连接1093.4.3嵌套查询嵌套查询概述概述一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询110嵌套查询嵌套查询嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块查询块将一个查询块，嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
62、嵌套查询SELECTSname/*外层查询/父查询*/FROMStudentWHERESnoIN（SELECTSno/*内层查询/子查询*/FROMSCWHERECno=2）；111嵌套查询嵌套查询(续续)子查询的限制不能使用ORDERBY子句，因只有主查询有输出。层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代112嵌套查询求解方法嵌套查询求解方法不相关子查询：子查询的查询条件不依赖于父查询n由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询
63、相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止1133.4.3嵌套查询嵌套查询概述一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询114一、带有一、带有IN谓词的子查询谓词的子查询例39查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成确定“刘晨”所在系名查找所有在该系学习的学生。SELECTSdeptFROMStudentWHERESname=刘晨；结果为：CSSELECTSno，Sname，SdeptFROMS
64、tudentWHERESdept=CS；115带有带有IN谓词的子查询（续）谓词的子查询（续）将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudent WHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)；此查询为不相关子查询。内查询结果为单行时，可用=号代替IN116带有带有IN谓词的子查询（续）谓词的子查询（续）用自身连接完成例39查询要求SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.S
65、name=刘晨；117带有带有IN谓词的子查询（续）谓词的子查询（续）例40查询选修了课程名为“信息系统”的学生学号和姓名在关系Student中查询学生学号和姓名条件为：选修了课程名为“信息系统”选修关系要在SC中查询课程名的信息在关系course中118带有带有IN谓词的子查询（续）谓词的子查询（续）例40查询选修了课程名为“信息系统”的学生学号和姓名SELECTSno,Sname最后在Student关系中 FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno首先在
66、Course关系中找出FROMCourse“信息系统”的课程号,为3号WHERECname=信息系统);还可用连接查询实现还可用连接查询实现119带有带有IN谓词的子查询（续）谓词的子查询（续）用连接查询实现例40SELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系统；1203.4.3嵌套查询嵌套查询一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询121二、带有比较运算符的
67、子查询二、带有比较运算符的子查询当能确切知道内层查询返回单值时,可用比较运算符（，=，=，!=或）替代IN内层查询返回多值，须与ANY或ALL谓词配合使用122带有比较运算符的子查询（续）带有比较运算符的子查询（续）例39查询与“刘晨”在同一个系学习的学生。假设一个学生只可能在一个系学习，并且必须属于一个系，则在例39可以用=代替IN：SELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=刘晨)；子查询一定要跟在比较符之后错误示例：WHERE(SELECTSdeptFROMStudentWHER
68、ESname=刘晨)=Sdept；123带有比较运算符的子查询（续）带有比较运算符的子查询（续）例41找出每个学生超过他本人选修课程平均成绩的课程号。SELECTSno,CnoFROMSCxWHEREGrade=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno);子查询与外查询依赖，执行的时候是：先从外查询中取一个元组，然后根据条件y.Sno=x.Sno处理子查询，得到结果；再从外查询中取第二个元组，如果反复。相关子查询相关子查询 124可能的执行过程：1.从外层查询中取出SC的一个元组x，将元组x的Sno值（200215121）传送给内层查询。SELECTAV
69、G(Grade)FROMSCyWHEREy.Sno=200215121;2.执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：SELECTSno,CnoFROMSCxWHEREGrade=88；3.执行这个查询，得到（200215121，1）（200215121，3）4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。结果为:（200215121，1）（200215121，3）（200215122，2）1253.4.3嵌套查询嵌套查询一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询ANY：任意一个值
70、ALL：所有值四、带有EXISTS谓词的子查询126需要配合使用比较运算符Page108ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值ANY小于子查询结果中的某个值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值=ANY小于等于子查询结果中的某个值=ALL小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值=ALL等于子查询结果中的所有值（通常没有实际意义）!=（或）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值127带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询（续）（续）例42查询其他系中比计算机
71、科学系某一某一学生年龄小的学生姓名和年龄SELECTSname,SageFROMStudentWHERESageANY(SELECTSageFROMStudent/*子查询结果为（19，20）*/WHERESdept=CS)ANDSdeptCS;/*父查询块中的条件*/SnameSage王敏18张立19128带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询（续）（续）用聚集函数实现例42ANYSELECTSname,SageFROMStudentWHERESage(SELECTMAX(Sage)FROMStudentWHERESdept=CS)ANDSdeptCS;129带有带有
72、ANY（SOME）或）或ALL谓词的子查询谓词的子查询（续）（续）例43查询其他系中比计算机科学系所有所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECTSname,SageFROMStudentWHERESageALL(SELECTSageFROMStudentWHERESdept=CS)ANDSdeptCS;130带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询（续）（续）方法二：用聚集函数SELECTSname,SageFROMStudentWHERESage(SELECTMIN(Sage)FROMStudentWHERESdept=CS)ANDSdeptCS
73、;比计算机系最小的还小即可。131带有带有ANY（SOME）或）或ALL谓词的子查询谓词的子查询（续）（续）Page109表3.5ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAX1323.4.3嵌套查询嵌套查询一、带有IN谓词的子查询二、带有比较运算符的子查询三、带有ANY（SOME）或ALL谓词的子查询四、带有EXISTS谓词的子查询1331.EXISTS谓词n存在量词n带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内
74、层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值n由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义n所有使用IN或由ANY或ALL修改的比较运算符的查询都可以通过EXISTS表示2.NOTEXISTS谓词若内层查询结果非空，则外层的WHERE子句返回假值若内层查询结果为空，则外层的WHERE子句返回真值134带有带有EXISTS谓词的子查询谓词的子查询(续）续）例44查询所有选修了1号课程的学生姓名。思路分析：n涉及Student和SC关系n在Student中依次取每个元组的Sn
75、o值，用此值去检查SC关系n若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=1，则取此Student.Sname送入结果关系135例44查询所有选修了1号课程的学生姓名。n用嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；n用连接运算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=1;EXISTS量词只关心是否有返回值，不需要查具体值，量词只关心是否有返回值，不需要查具体值，有时是高
76、效的方法有时是高效的方法136带有带有EXISTS谓词的子查询谓词的子查询(续）续）例45 查询没有选修1号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；137 不同形式的查询间的替换n一些带EXISTS或NOTEXISTS谓词的子查询，不能被其他形式的子查询等价替换n所有带IN谓词、比较运算符、ANY和ALL谓词的子查询，都能用带EXISTS谓词的子查询等价替换例例39查询与查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。可以用带EXISTS谓词的子
77、查询替换：SELECTSno,Sname,SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=刘晨)； 138用用EXISTS/NOTEXISTS实现全称量词实现全称量词(难点难点)SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词，转换为等价的，带有存在量词的谓词：(x)P(x(P)例46 查询选修了全部全部课程的学生姓名。即：查询这样的学生，没有一门课是他不选修的。回顾：关系代数的除法rs=R-S(r )R-S(R-S (r )xs )r )139用用EXIST
78、S/NOTEXISTS实现实现1、全称量词、全称量词2、逻辑蕴函逻辑蕴函SQL语言中没有全称量词（Forall）可以把带有全称量词的谓词，转换为等价的，带有存在量词的谓词：(x)P(x(P)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：pqpq1401、用、用EXISTS/NOTEXISTS实现全称量词实现全称量词设全部课程=1,2，1号同学选择全部课程，中间层查询无元组，主查询有输出；2号同学只选1门课程，中间层查询有一个元组，主查询无输出。例46 查询选修了全部课程的学生姓名。SELECTSnameFROMStudentWHERENOTEX
79、ISTS（SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno）；即：查询这样的学生，没有一门课是他不选修的。1412、用用EXISTS/NOTEXISTS实现逻辑蕴函实现逻辑蕴函SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：pqpq例47查询至少选修了学生200215122选修的全部课程的学生号码。142例47查询至少选修了学生200215122选修的全部课程的学生号码。解题思路：n用逻辑蕴函表达：查询学号为x的学生，对所有的课程y
80、，只要200215122学生选修了课程y，则x也选修了y。n形式化表示：用P表示谓词“学生200215122选修了课程y”用q表示谓词“学生x选修了课程y”则上述查询为:(y)pq143用P表示谓词“学生200215122选修了课程y”用q表示谓词“学生x选修了课程y”则上述查询为:(y)pq等价变换：等价变换：(y)pq(y(pq)全称量词的转换(y(pq)蕴涵关系的转换y(pq)p表示谓词“学生200215122选修了课程y”q表示谓词“学生x选修了课程y”n变换后语义：不存在这样的课程y，学生200215122选修了y，而学生x没有选。 y(p q)p q144不存在这样的课程y，学生2
81、00215122选修了y，而学生x没有选。SELECTSnoFROMSCWHERENOTEXISTS（学学生生200215122200215122选选修修了了y，而而学学生生x没没有选有选）SELECT*FROMSCWHERESC.Sno=200215122AndNOTEXISTSpy(pq)SELECT*FROMSCWHERESC.Sno=q145不存在这样的课程y，学生200215122选修了y，而学生x没有选。SELECTSnoFROMSCSCXWHERENOTEXISTS（学学生生200215122200215122选选修修了了y，而而学学生生x没没有选有选）SELECT*FROMSC
82、SCYWHERESC.Sno=200215122AndNOTEXISTSpy(pq)SELECT*FROMSCSCZWHERESCZ.Sno=SCXAndSCZ.Cno=SCY.Cnoq146例例47查询至少选修了学生查询至少选修了学生200215122选选修的全部课程的学生号码。修的全部课程的学生号码。SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno=200215122ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SCX.SnoANDSCZ.Cno=SCY.Cno)
83、；1473.4SQL数据查询功能数据查询功能3.4.3嵌套查询嵌套查询IN谓词比较算符ANY 或ALL谓词EXISTS谓词3.4.4集合查询集合查询3.4.5SELECT语句的一语句的一般形式般形式3.4.13.4.1 单表查询单表查询n选择列n选择元组n排序n集函数n分组3.4.23.4.2 连接查询连接查询n等值与非等值连接n自身连接n外连接n复合条件连接1483.4.4集合查询集合查询集合操作的种类并操作UNION交操作INTERSECT差操作EXCEPT参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同149例48查询计算机科学系的学生及年龄不大于19岁的学生。方法一：
84、SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19；nUNION：将多个查询结果合并起来时，系统自动去掉重复元组。nUNIONALL：将多个查询结果合并起来时，保留重复元组150集合查询（续）集合查询（续）方法二：SELECTDISTINCT*FROMStudentWHERESdept=CSORSage=19；151集合查询（续）集合查询（续）例49查询选修了课程1或者选修了课程2的学生。SELECTSnoFROMSCWHERECno=1UNIONSELECTSnoFROMSCWHERECno=2；152集合查询
85、（续）集合查询（续）例50查询计算机科学系的学生与年龄不大于19岁的学生的交集SELECT*FROMStudentWHERESdept=CSINTERSECT-sqlserver查询分析器不支持SELECT*FROMStudentWHERESage=19153集合查询（续）集合查询（续）例50实际上就是查询计算机科学系中年龄不大于19岁的学生SELECT* FROMStudent WHERESdept=CSANDSage=19；154集合查询（续）集合查询（续）例51查询选修课程1的学生集合与选修课程2的学生集合的交集SELECTSnoFROMSCWHERECno=1INTERSECT-sql
86、server查询分析器不支持SELECTSnoFROMSCWHERECno=2；155集合查询（续）集合查询（续）例51查询选修课程1的学生集合与选修课程2的学生集合的交集如果改写成：SELECTSnoFROMSCWHERECno=1ANDCno=2得到的结果会是什么？例51所要的结果是这样吗156集合查询（续）集合查询（续）例51实际上是查询既选修了课程1又选修了课程2的学生SELECTSnoFROMSCWHERECno=1ANDSnoIN(SELECTSnoFROMSCWHERECno=2)；（因两个条件在同一列上，不能直接用AND）157集合查询（续）集合查询（续）例52查询计算机科学系
87、的学生与年龄不大于19岁的学生的差集。SELECT*FROMStudentWHERESdept=CSEXCEPT-sqlserver查询分析器不支持SELECT*FROMStudentWHERESage19；1593.4SQL数据查询功能数据查询功能3.4.3嵌套查询嵌套查询IN谓词比较算符ANY 或ALL谓词EXISTS谓词3.4.4集合查询集合查询3.4.5SELECT语句的一语句的一般形式般形式3.4.13.4.1 单表查询单表查询n选择列n选择元组n排序n集函数n分组3.4.23.4.2 连接查询连接查询n等值与非等值连接n自身连接n外连接n复合条件连接1603.4.5SELECT语句
88、的一般格式语句的一般格式SELECTALL|DISTINCT别名，别名INTOnew_tableFROMtable_source WHEREsearch_condition GROUPBYgroup_by_expression HAVINGsearch_condition ORDERBYorder_expressionASC|DESC可以在查询之间使用UNION运算符，以将查询的结果组合成单个结果集。所有查询中的列数和列的顺序必须相同。数据类型必须兼容。161SELECTSELECT练习一练习一对STUDENT、COURSE、SC三个表:1.创建关系stu1(sno，sname，ssex，sa
89、ge，sdept)。2.对STUDENT按姓名建立索引。3.查询信息系所有年龄不大于21岁的女生。4.查询1982年出生的男生的姓名。5.查询信息系、金融系所有姓“王”的同学的姓名和年龄。6.查询姓“王”的男同学的人数。7.查询2号课程的最低分。8.查询总分最高的学生的学号。9.查询每个同学的平均分。10.查询每个同学所选修的课程门数。1624.查询1982年出生的男生的姓名。sage=year(getdate()-1984sage适合做属性？7.查询2号课程的最低分selectmin(grade)fromscwherecno=29.查询每个同学的平均分selectsno,avg(grade)
90、fromscgroupbysno10.查询每个同学所选修的课程门数selectsno,count(cno)fromscgroupbysno1638.查询总分最高的学生的学号1），查询总分SELECTsno,SUM(grade)ASzongfenFROMscGROUPBYsnoselectmax(zongfen)from(SELECTsno,SUM(grade)ASzongfenFROMscGROUPBYsno)Qgroupbysno2），总分最高SELECTsno,max(SUM(grade)ASTopzongfenFROMscGROUPBYsno服务器:消息130，级别15，状态1，行1不能
91、对包含聚合或子查询的表达式执行聚合函数。2651701648.查询总分最高的学生的学号SELECTTop1sno,SUM(grade)ASzongfenFROMscGROUPBYsnoORDERBYzongfenDESC总分第一的有多个？WITHTIESSELECTsno,SUM(grade)ASzongfeninto#tempzfFROMscGROUPBYsnoselectsno,zongfenfrom#tempzf1wherezongfen=(selectmax(zongfen)from#tempzf1)1658.查询总分最高的学生的学号selectsno,sum(grade)fromsc
92、groupbysnohavingsum(grade)=all(selectsum(grade)fromscgroupbysno)166练习二练习二对STUDENT、COURSE、SC三个表完成以下操作：1.查询“计算机网络“分数大于90分的同学的姓名。2.查询每门课程的课程名及选修人数。3.查询选修了全部课程的学生姓名。4.查询总学分已超过40学分的学生学号、姓名、总学分。5.查询至少选修了学生”020001“选修的全部课程的姓名。6.查询其他系中比CS系任一学生年龄都小的学生名单。7.查询每一个同学的学号、姓名、选修的课程名及分数。8.查询信息系学生及年龄小于20岁的学生。9.查询信息系学生
93、与年龄小于20岁学生的交集。10.查询信息系学生与年龄小于20岁学生的差集。1674.查询总学分已超过40学分的学生学号、姓名、总学分。selectsno,sname,sum(ccredit)fromcourse,sc,o=oandsc.sno=student.snogroupbysnohavingsum(ccredit)40selectsc.sno,sname,sum(ccredit)fromcourse,sc,o=oandsc.sno=student.snogroupbysc.sno,snamehavingsum(ccredit)401685.查询至少选修了学生查询至少选修了学生”0200
94、01“选修的全部课程的学生姓名。选修的全部课程的学生姓名。不存在这样的课程y，学生020001选修了y，而学生x没有选。y(pq)169不存在这样的课程y，学生020001选修了y，而学生x没有选。SELECTSnoFROMSCSCXWHERENOTEXISTS(学生学生020001020001选修了选修了y，而学生，而学生x没有选没有选)SELECT*FROMSCSCYWHERESC.Sno=020001AndNOTEXISTSpy(pq)SELECT*FROMSCSCZWHERESCZ.Sno=SCXAndSCZ.Cno=SCY.Cnoq170第3章补充题：写SQL语句(3) 检索住在同一
95、个城市的一对供应商编号。171第3章补充题：写SQL语句(3) 检索住在同一个城市的一对供应商编号。SelectFIRST.Sno,SECOND.SnoFromSASFIRST,SASSECONDWhereFIRST.City=SECOND.CityandFIRST.Sno SECOND.Sno;该该条条件件使使结结果果的的供供应商编号不同应商编号不同!172第3章补充题：写SQL语句解1连接查询:SelectJnameFromJ,SPJWhereJ.Jno=SPJ.JnoandSno=S1;解2使用IN谓词的嵌套查询:selectJnameFromJWhereJnoin(selectJnof
96、romSPJwhereSno=S1);解3使用EXISTS谓词的嵌套查询:SelectJnamefromJWhereexists(select*fromSPJwhereSPJ.Jno=J.JnoandSPJ.Sno=S1);(4) 检索供应商S1提供零件的工程项目名称。173第3章补充题：写SQL语句解1连接查询:SelectJnameFromJ,SPJWhereJ.Jno=SPJ.JnoandSno=S1;解2使用IN谓词的嵌套查询:selectJnameFromJWhereJnoin(selectJnofromSPJwhereSno=S1);解3使用EXISTS谓词的嵌套查询:Select
97、JnamefromJWhereexists(select*fromSPJwhereSPJ.Jno=J.JnoandSPJ.Sno=S1);(4) 检索供应商S1提供零件的工程项目名称。174第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述概述3.2学生学生-课程数据库课程数据库3.3数据定义数据定义3.4数据查询数据查询3.5数据更新数据更新3.6视图视图3.7小结小结1753.5数数据据更更新新3.5.1插入数据插入数据3.5.2修改数据修改数据3.5.3删除数据删除数据1763.5.1插入数据插入数据两种插入数据方式1.插入元组2.插入子查询结果可以一次插入多个元组1
98、77一、插入元组一、插入元组n将新元组插入指定表中语句格式INSERTINTO(，)VALUES(，)178插入元组（续）插入元组（续）INTO子句n属性列的顺序,可与表定义中的顺序不一致n没有指定属性列n指定部分属性列VALUES子句n提供的值必须与INTO子句匹配值的个数值的类型179插入元组（续）插入元组（续）例1将一个新学生元组插入到Student表中（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）。INSERTINTOStudent(Sno，Sname，Ssex，Sdept，Sage)VALUES(200215128，陈冬，男，IS，18)；180插入元
99、组（续）插入元组（续）例2将学生张成民的信息插入到Student表中。INSERTINTOStudentVALUES(200215126，张成民，男，18，CS);181插入元组（续）插入元组（续）例3插入一条选课记录(200215128，1)。INSERTINTOSC(Sno，Cno)VALUES(200215128，1)；RDBMS将在新插入记录的Grade列上,自动地赋空值。或者：INSERTINTOSCVALUES(200215128，1，NULL)；182二、插入子查询结果二、插入子查询结果将子查询结果插入指定表中语句格式INSERTINTO(，)子查询；子查询nSELECT子句目标
100、列,必须与INTO子句匹配值的个数值的类型183插入子查询结果（续）插入子查询结果（续）例4对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表CREATETABLEDept_age(SdeptCHAR(15)/*系名*/Avg_ageSMALLINT)；/*学生平均年龄*/第二步：插入数据INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；184插入子查询结果（续）插入子查询结果（续）RDBMS在执行插入语句时，会检查所插元组,是否破坏表上已定义的完整性规则:实体完整性参照完整性
101、用户定义的完整性NOTNULL约束UNIQUE约束值域约束1853.5数数据据更更新新3.5.1插入数据插入数据3.5.2修改数据修改数据3.5.3删除数据删除数据1863.4.2修改数据修改数据语句格式UPDATESET=，=WHERE；nSET子句指定修改方式要修改的列修改后取值nWHERE子句指定要修改的元组缺省表示要修改表中的所有元组187修改数据（续）修改数据（续）三种修改方式1.修改某一个元组的值2.修改多个元组的值3.带子查询的修改语句1881.修改某一个元组的值修改某一个元组的值例5将学生200215121的年龄改为22岁UPDATEStudentSETSage=22WHERE
102、Sno=200215121；1892.修改多个元组的值修改多个元组的值例6将所有学生的年龄增加1岁UPDATEStudentSETSage=Sage+1；1903.带子查询的修改语句带子查询的修改语句例7将计算机科学系全体学生的成绩置零。UPDATESCSETGrade=0WHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；或：UPDATESCSETGrade=0WHERESC.SnoIN(SELETESnoFROMStudentWHERESdept=CS)；191修改数据（续）修改数据（续）RDBMS在执行修改语句时，会检查修改操作
103、是否破坏表上已定义的完整性规则n实体完整性n主码不允许修改n用户定义的完整性NOTNULL约束UNIQUE约束值域约束1923.5数数据据更更新新3.5.1插入数据插入数据3.5.2修改数据修改数据3.5.3删除数据删除数据1933.5.3删除数据删除数据语句格式DELETEFROMWHERE；WHERE子句n指定要删除的元组n缺省表示要删除表中的全部元组，表的定义仍在字典中194删除数据（续）删除数据（续）三种删除方式1.删除某一个元组的值2.删除多个元组的值3.带子查询的删除语句1951.删除某一个元组的值删除某一个元组的值例8删除学号为200215128的学生记录。DELETEFROMS
104、tudentWHERESno=200215128；1962.删除多个元组的值删除多个元组的值例9删除所有的学生选课记录。DELETEFROMSC；1973.带子查询的删除语句带子查询的删除语句例10删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；198练习练习设有如下关系表R(NO,NAME,SEX,AGE,CLASS)主关键字是NO，其中NO为学号，NAME为姓名，SEX为性别，AGE为年龄，CLASS为班号。写出实现下列功能的SQL语句：(1)插入一个记录(25,李明
105、,男,21,95031)。(2)插入95031班学号为30、姓名为郑和的学生记录。(3)将学号为10的学生姓名改龙王华。(4)将所在95101班号改为95091。(5)删除学号为20的学生记录。(6)删除姓王的学生记录。199补充补充常用函数常用函数1.数学函数-ABS():求绝对值-CEILING():舍入到最大整数-FLOOR():舍入到最小整数-ROUND():四舍五入selectABS(-3)selectCEILING(3.33)selectCEILING(-3.61)selectFLOOR(2.98)selectFLOOR(-3.61)selectROUND(-3.61,1)-第二个
106、参数是精度，小数点后的位数selectROUND(-3.61,0)selectROUND(3.1415926,3)200补充补充常用函数常用函数2.字符串函数-LEN():计算字符串长度-LOWER(),UPPER():转大小写-LTRIM():去掉字符串左侧的空格-RTRIM():去掉字符串右侧的空格-SUBSTRING(string,start_positoin,length):-索引从1开始selectSUBSTRING(abc111,2,3)-结果是bc1selectFName,SUBSTRING(FName,2,2)fromT_EmployeeselectLEN(abc)-结果是3s
107、electFName,LEN(FName)fromT_Employee201补充补充常用函数常用函数3.日期函数-GETDATE():获取当前日期时间-DATEADD(datepart,numbre,date):计算增加以后的日期，-参数date为待计算的日期;参数number为增量;参数datepart为计量单位,时间间隔单位;-DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额-DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,selectDATEADD(DAY,3,getdate()selectDATEADD
108、(MONTH,-3,getdate()selectDATEADD(HOUR,8,getdate()selectDATEDIFF(YEAR,1989-05-01,GETDATE()selectDATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE()-查询员工的工龄,年为单位selectFName,FInDate,DATEDIFF(year,FInDate,getdate()as工龄fromT_Employee202补充补充常用函数常用函数4.类型转换函数-CAST(expressionasdata_type)-CONVERT(data_type,express
109、ion)selectCAST(123asint),CAST(2010-09-08asdatetime),CONVERT(datetime,2010-09-08),CONVERT(varchar(20),123)5.空值处理函数isNull-ISNULL(expression,value)selectISNULL(FName,佚名)as姓名fromT_Employee203补充补充空值处理空值处理-null表示不知道,不是没有值-null和其他值计算结果是nullselectnull+1-查询名字是null的数据select*fromT_EmployeewhereFNameisnull-查询名字
110、不为空null的数据select*fromT_EmployeewhereFNameisnotnull204第三章第三章关系数据库标准语言关系数据库标准语言SQL3.1SQL概述概述3.2学生学生-课程数据库课程数据库3.3数据定义数据定义3.4数据查询数据查询3.5数据更新数据更新3.6视图视图3.7小结小结205视图的类型视图的类型2063.6视视图图视图正如其名字的含义一样，是另一种查看数据的入口.视图可以看作定义在SQLServer上的虚拟表.常规视图本身并不存储实际的数据，而仅仅存储视图的定义（Select语句）.基表中的数据发生变化，从视图中查询出的数据也随之改变207为什么要使用视
111、图为什么要使用视图(View)三级模式结构中的子模式/外模式视图隐藏了底层的表结构，简化了数据访问操作因为隐藏了底层的表结构，所以大大加强了安全性，用户只能看到视图提供的数据使用视图，方便了权限管理，让用户对视图有权限而不是对底层表有权限，进一步加强了安全性视图提供了一个用户访问的接口，当底层表改变后，改变视图的语句来进行适应，使已经建立在这个视图上客户端程序不受影响2083.6视视图图基于视图的操作查询删除受限更新定义基于该视图的新视图2093.6视视图图3.6.1定义视图定义视图建立视图建立视图删除视图删除视图3.6.2查询视图查询视图3.6.3更新视图更新视图3.6.4视图的作用视图的作
112、用210一、建立视图一、建立视图语句格式CREATEVIEW(，)ASWITHCHECKOPTION；组成视图的属性列名：全部省略或全部指定子查询不允许含有ORDERBY子句和DISTINCT短语WITHCHECKOPTION：增删改要保证数据行满足试图定义中的谓词条件。211建立视图（续）建立视图（续）例1建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；212例2CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdep
113、t=ISWITHCHECKOPTION；对IS_Student视图的更新操作：修改操作：自动加上Sdept=IS的条件删除操作：自动加上Sdept=IS的条件插入操作：自动检查Sdept属性值是否为IS如果不是，则拒绝该插入操作如果没有提供Sdept属性值，则自动定义Sdept为IS213基于基于多个基表多个基表的视图的视图例3建立信息系选修了1号课程的学生视图。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.
114、Cno=1；214基于视图基于视图的视图的视图例4建立信息系选修了1号课程且成绩在90分以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade=90；215带表达式的视图带表达式的视图例5定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent；216分组视图分组视图例6将学生的学号及他的平均成绩定义为一个视图假设SC表中“成绩”列Grade为数字型CREATVIEWS_G(Sno，Gavg)ASSEL
115、ECTSno，AVG(Grade)FROMSCGROUPBYSno；217不指定属性列不指定属性列例7将Student表中所有女生记录定义为一个视图CREATEVIEWF_Student(F_Sno，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex=女；缺点：修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。218二、删除视图二、删除视图语句的格式：DROPVIEW；该语句从数据字典中删除指定的视图定义如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所
116、有视图一起删除删除基表时，由该基表导出的所有视图定义都必须显式地使用DROPVIEW语句删除219删除视图删除视图(续）续）例8删除视图BT_S：DROPVIEWBT_S；删除视图IS_S1：DROPVIEWIS_S1；拒绝执行级联删除：DROPVIEWIS_S1CASCADE;2203.6视视图图3.6.1定义视图定义视图3.6.2查询视图查询视图3.6.3更新视图更新视图3.6.4视图的作用视图的作用2213.6.2查询视图查询视图用户角度：查询视图与查询基本表相同RDBMS实现视图查询的方法视图消解法（ViewResolution）1.进行有效性检查2.转换成等价的对基本表的查询3.执行
117、修正后的查询222例9在信息系学生的视图中，找出年龄小于20岁的学生。SELECTSno，SageFROMIS_StudentWHERESage20；CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS视图消解转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept=ISANDSage=90；S_G视图的子查询定义：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；225查询转换查询转换错误：SELECTSn
118、o，AVG(Grade)FROMSCWHEREAVG(Grade)=90GROUPBYSno；因WHERE子句不可用组函数，这种转换不合理。正确：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)=90；此时虽然正确，但生成视图时并非此处的查询语句。2263.6视视图图3.6.1定义视图定义视图3.6.2查询视图查询视图3.6.3更新视图更新视图3.6.4视图的作用视图的作用227更新视图更新视图例12将信息系学生视图IS_Student中学号200215122的学生姓名改为“刘辰”。UPDATEIS_StudentSETSname=刘辰W
119、HERESno=200215122；转换后的语句：UPDATEStudentSETSname=刘辰WHERESno=200215122ANDSdept=IS；228更新视图（续）更新视图（续）例13向信息系学生视图IS_S中插入一个新的学生记录：200215129，赵新，20岁INSERTINTOIS_StudentVALUES(95029，赵新，20)；转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(200215129，赵新，20，IS)；229更新视图（续）更新视图（续）例14删除信息系学生视图IS_Student中学号为2
120、00215129的记录DELETEFROMIS_StudentWHERESno=200215129；转换为对基本表的更新：DELETEFROMStudentWHERESno=200215129ANDSdept=IS；230更新视图（续）更新视图（续）更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地、有意义地转换成对相应基本表的更新例：视图S_G为不可更新视图。UPDATES_GSETGavg=90WHERESno=200215121；这个对视图的更新无法转换成对基本表SC的更新231更新视图（续）更新视图（续）允许对行列子集视图进行更新对其他类型视图的更新，不同系统有不同限
121、制2323.6视视图图3.6.1定义视图定义视图3.6.2查询视图查询视图3.6.3更新视图更新视图3.6.4视图的作用视图的作用2333.6.4视图的作用视图的作用1.视图能够简化用户的操作2.视图使用户能以多种角度看待同一数据3.视图对重构数据库提供了一定程度的逻辑独立性4.视图能够对机密数据提供安全保护5.适当的利用视图可以更清晰的表达查询234附录附录数据库备份与恢复CreateSchema与CreateDatabase数据库各组成部分235数据库备份与恢复数据库备份与恢复BACKUPDATABASENorthwindTODISK=c:Northwind.bakRESTOREFILEL
122、ISTONLYFROMDISK=c:Northwind.bakRESTOREDATABASETestDBFROMDISK=c:Northwind.bakWITHMOVENorthwindTOE:Curricula2012DB2012DBFiletestdb.mdf,MOVENorthwind_logTOE:Curricula2012DB2012DBFiletestdb.ldfGO236CreateSchema与与CreateDatabaseCREATESCHEMA创建一个架构，并且可以在概念上将其看作是包含表、视图和权限定义的对象。微软：Schema是一个独立于数据库用户的非重复命名空间，您可
123、以将架构视为对象的容器。SQLServer2000中一个schema对应一个user，但是，05中并不是这样的。命名空间名其实就是文件夹名。一个对象只能属于一个Schema，就像一个文件只能存放于一个文件夹中一样。要访问一个数据库对象的时候，通常应该是引用它的全名架构名.对象名，237数据库各组成部分数据库各组成部分数据库由存储特定结构化数据集的表表集合组成。表中包含行（记录或元组）和列（属性）的集合。表中的每一列都设计为存储某种类型的信息（例如，日期、名称、美元金额或数字）。表上有几种控制控制（约束、规则、触发器、默认值和自定义用户数据类型）用于确保数据的有效性。表上可以有索引索引（与书中的
124、索引非常相似），利用索引可以快速地找到行。可将声明引用完整性(DRI)约束添加到表上，以确保不同表中相互关联的数据保持一致。数据库还可以定义视图和存储过程视图和存储过程，存储过程使用Transact-SQL编程代码对数据库中的数据执行操作。238实验实验 SQL SERVERSQL SERVER基本操作基本操作1打开SQLSERVER数据库的企业管理器，学习企业管理器的使用，学习菜单和工具条的功能。2在企业管理器中，建立学生管理数据库SM和数据表(student,course,sc)，并输入记录数据。3学习数据库管理的操作界面。4在企业管理器中，练习SQL语句的用法。5创建多种数据库对象，如视图、规则、等。239
