关系数据语言SQL.ppt

数据库系统基础教程（第2版）,叶小平 汤 庸 汤 娜 潘 明 编著,普通高等教育“十一五”国家级规划教材,清华大学出版社,2,关系数据库作为当前数据库的主流有着很多商业化的产品，例如Oracle、Sybase、Informix、Cobase和SQL Server等。不同产品有着各自不同的界面和操作方式，但它们的核心部分都是相同的，就都采用标准化的结构化数据查询语言SQL。,第3章 关系数据库语义SQL,3.1 SQL概述,3.1.1 SQL产生与标准,3,SQL原型来自于1972年IBM为实验型关系数据库管理系统System R配置的查询语言称为SQUARE（Specifying Queries as Relation Expression），其中使用了较多的数学符号。经过多年改进与演变，1999年ISO发布了标准化文件ISO/IEC9075：数据库语言SQL-99，延续SQL的叫法，人们称之为SQL3。,3.1.1 SQL产生与标准,4,作为数据库语言国际标准，SQL经历四个发展阶段。1987年，国际标准化组织（International Organization for Standardization,ISO）通过ANSI的SQL-86标准，并于1989年公布了SQL-89标准。1992年，ISO公布了SQL-92标准，习惯上称之为SQL2。SQL2集关系数据库查询语言之大成，标志着SQL已经成为功能比较齐全、内容相当完善的关系数据库语言。,3.1.1 SQL产生与标准,5,1999年：ISO发布了标准化文件ISO/IEC9075：数据库语言SQL-99，延续SQL的习惯称呼，人们称之为SQL3。SQL3的重要特点是反映了关系数据模型到对象模型的重要扩充。2003年：ISO发布了SQL：2003，其标志着传统关系模型到非关系模型的第二次重要扩充（对象模型和XML模型）。在通常情况下，SQL标准每三年左右修订一次,3.1.1 SQL产生与标准,6,1.基本功能特征（1）综合统一性 DDL、DML和DCL的统一 操作过程的统一（2）非过程化语言 只要提出“做什么”，不需说明“如何做”,3.1.2 SQL功能与组成,7,（3）面向集合操作方式SQL语句以接收集合作为输入，返回集合作为输出，一次一集合（4）一种语法，两种使用方式 SQL是“自含式”语言也是“嵌入式”语言,3.1.2 SQL功能与组成,8,3.1.2 SQL语言功能,（5）结构简洁，易学易用 SQL语言功能极强，但设计构思却非常巧妙，语言结构简洁明快。在SQL中，完成所有核心功能只使用如图3-1所示的9个动词。,9,3.1.2 SQL语言功能,（6）支持三级模式结构,10,（1）数据定义语言（DDL）,3.1.3 SQL基本构成,SQL模式（数据库）的创建、撤销与更改 基本表的创建、撤销与更改。索引的创建与撤销。域、触发器和自定义类型的创建于撤销。,11,（2）数据操纵语言（DML）,3.1.3 SQL基本构成,数据查询：单表查询，多表查询，其中包括连接查询和嵌套查询。数据更新：数据插入、删除和修改。查询所需的附加功能：求和函数 SUM、平均函数AVG、元组个数求和 COUNT；最大函数 MAX和最小函数 MIN等。,12,（3）数据控制语言（DCL）,3.1.3 SQL基本构成,数据库保护：数据库的安全性和完整性保护。事务管理：数据库故障恢复和并发事务处理。,13,（4）嵌入式与会话规则,3.1.3 SQL基本构成,嵌入式与主语言接口：嵌入式和动态SQL规则规定了SQL语句在高级程序设计语言中使用的规范与标准。调用与会话规则：SQL还提供远程调用功能，在远程方式下客户机中的应用可通过网络调用服务器数据库中的存储过程,14,3.1.4 语句类型与数据类型,模式语句 模式语句功能是创建、更新和撤销模式及其对象。其基本语句为：CREATE SCHEMA、CREATE TABLE、CREATE VIEW；CREATE DOMAIN、CREATE TRIGGER、CREATE TYPE等,1.语句类型,15,3.1.4 语句类型与数据类型,数据语句 数据语句功能是完成数据库的查询和更新操作。其主要语句为：SELECT（查询）；INSERT（插入）、UPDATE（修改）和DELETE（删除）。,1.语句类型,16,3.1.4 语句类型与数据类型,事务与控制语句 该语句功能为完成数据库的授权、事务管理和控制SQL语句集合的运行，其主要语句为：GRANT；START TRANSATION，REVOKE，COMMIT，ROLLBACK，SAVEPOINT；LOCK，UNLOCK，CALL等,1.语句类型,17,3.1.4 语句类型与数据类型,链接、会话与诊断语句 该语句功能为建立数据库链接，为SQL会话设置参数、获取诊断等，其主要语句为：SET CONNECTION；SET TIME ZONE；SET SESSIONAUTHORIZATION；GET DLAGNOSTICS等。,1.语句类型,18,3.1.4 语句类型与数据类型,SQL在定义表的各个属性的时候，要求指明其中数据类型和长度。一般而言，在SQL中规定了三种数据类型，即预定义数据类型，构造数据类型和用户自定义数据类型（user defined type，UDT）。但具体来说，除了各种DBMS都支持的一些基本数据类型之外，不同的DBMS支持的数据类型不尽完全相同。下图列举了Microsoft SQL Server 支持的主要数据类型。,2.数据类型,19,3.1.4 语句类型与数据类型,20,3.2.1 SQL模式（数据库）定义,3.2 数据定义,SQL模式由CREATE语句定义，其一般格式如下：CREATE SCHEMA AUTHORIZATION子句|子句|子句|例如，教学数据库的SQL模式定义如下：CREATE SCHEMA JIAOXUE AUTHORIZATION JOHN,21,3.2.1 SQL模式（数据库）定义,当一个SQL模式不需要时，可以用DROP语句予以撤销。DROP语句的使用格式如下：DROP SCHEMA CASCADE|RESTRICT例如，需要撤销SQL模式“JIAOXUE”以及下属所有元素，可以用下属语句实现：DROP SCHEMA JIAOXUE CASCADE,22,1基本表创建,基本表定义,SQL使用CREATE TABLE 语句创建基本表，一般格式为：CREATE TABLE模式名（列级完整性约束条件，列级完整性约束条件，）其中 内的内容“”是可选项，是所要定义的基本表名称。,23,2基本表更新,基本表定义,（1）增加属性列增加新的属性列使用“ALTERADD”语句，基本格式为：ALTER TABLE ADD完整性约束条件 例3-2 在基本表S中添加一个新的地址属性ADDRESS：ALTER TABLE S ADD ADDRESS VARCHAR（30）；,24,1基本表更新,基本表定义,（2）删除属性列删除已有属性列使用“ALTERDROP”语句，其基本格式为：ALTER TABLE DROP CASCADE|RESTRICT 例3-3 在S中删除属性列Sa：ALTER TABLE S DROP Sa CASCADE；,25,1基本表更新,基本表定义,（3）修改属性列修改已有属性列类型及宽度使用“ALTERMODIFY”语句，其基本格式为：ALTER TABLE MODIFY 例3-4 在S中将S#的长度修改为“6”：ALTER TABLE S MODIFY S#CHAR（6）；,26,2基本表更新,基本表定义,（4）补充定义主键在SQL中，并不要求每个表都需定义主键，可以在需要情况下随时定义，这称为主键的补充定义。补充定义主键的语句格式为ALTER TABLEADD PRIMARY KEY（）,27,2基本表更新,基本表定义,例3-5 设有全体男生的表Smale，其结构与S表相同，补充定义Smale的主键的SQl语句如下：ALTER TEBLE SmaleADD PRIMARY KEY（S#）；,28,2基本表更新,基本表定义,（5）删除主键由于一个表可以不定义主键，所以可以从一个表中删除主键。删除主键的SQL语句格式为：ALTER TABLEDROP PRIMARY KEY（）例3-6 删除S表中主键S#的SQL语句如下：ALTER TEBLE SDROP PRIMARY KEY（S#）；,29,3撤销基本表,基本表定义,SQL使用“DROP TABLE”语句撤销基本表，一般格式为DROP TABLE CASCADE|RESTRICT例3-7 撤消基本表S，但要求只有在没有视图或约束引用S的属性列时才能撤销，否则拒绝撤销，则其实现语句为：DROP TABLE S RESTRICT；,30,1创建索引,3.2.3 索引定义,SQL使用CREATE INDEX 语句创建索引，一般格式为：CREATE UNIQUE CLUSTERED INDEX ON（，）,31,1创建索引,3.2.3 索引定义,例3-8 在S（S#）上建立一个按升序排列的索引S_XSNO：CREATE UNIQUE INDEX S_XSNO ON S（S#）；例3-9 在SC上建立一个按（S#,C#）升序排列名为SC_XSC的索引：CREATE INDEX SC_XSC ON SC（S#,C#）；,32,3.3.1 SQL映像语句,3.3 数据查询,SQL中查询的基本语句映像（Mapping）语句由SELECT、FROM和WHERE三个子句构成。SELECT子句 表示查询的目标属性，指定做投影 运算。FROM子句 表示查询所涉及的关系，指定多个关系做连接运算。WHERE子句 表示查询的逻辑条件，指定做选择运算。,1.SQL映像语句,33,3.3.1 SQL映像语句,由这3个子句组成的映像语句的一般格式为：SELECTALL|DISTINCT，FROM，WHEREGROUP BY HAVINGORDER BYASC|DESC,34,3.3.1 SQL映像语句,上述整个映像语句的含义是：首先，根据WHERE子句的条件表达式；其次，从FROM子句指定的基本表或视图中找出满足条件的元组；最后，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果关系表。,35,3.3.1 SQL映像语句,例3-1 创建关系数据库S(S#,Sn,Sa,Se，Sd)；C(C#,Cn,P#)；SC(S#,C#,G)，其中S表示学生关系，S#表示学号，Sn表示学生姓名，Sa 表示学生年龄，Se表示学生性别，Sd表示学生所在的系别；C表示课程关系，C#表示课程编号，Cn表示课程名称，P#表示课程的先修课程编号；SC表示学生选课关系，G表示课程的考试成绩。,36,3.3.1 SQL映像语句,CREATE TABLE S（S#CHAR（4）NOT NULL UNIQUE，Sn CHAR（20）NOT NULL，Sa SMALLINT，Se CHAR（2），Sd CHAR（20）PRIMARY KEY（S#），CHECK（Sa BETWEEN 15 AND 25）；,37,3.3.1 SQL映像语句,CREATE TABLE C(C#CHAR(4)NOT NULL，Cn CHAR(10)NOT NULL，P#CHAR(4)PRIMARY KEY（C#）；,38,3.3.1 SQL映像语句,CREATE TABLE SC(S#CHAR(4),NOT NULL，C#(CHAR(4),NOT NULL，G SMALLINT，PRIMARY KEY（S#，C#），FOREIGN KEY（S#）REFERENCES S（S#）ON DELETE CASCADE，FOREIGN KEY（C#）REFERENCES C（C#）ON DELETE RESTRICT）；,39,3.3.1 SQL映像语句,由上述语句可以知道：基本表的定义就是并列说明属性列名称和属性列类型，并且可以指明主键和多个外键。在定义外键时，用保留字REFERENCES指出外键来自的表名，即主表。另外引用完整性中的任选项ON DELETE指出当主表中被引用主属性删除时，可采用如下方法保证完整性要求：,40,3.3.1 SQL映像语句,选用RESTRICT选项：表明被基本表所引用的主属性不得删除。选用CASCADE选项：表明若主表中删除被引用的主属性，则基本表中引用该外键的对应行随之被删除。选用SET NULL选项：当然此时该列在前面说明应没有NOT NULL限制。在基本表定义时，可以使用CHECK语句，说明各列中值应当满足的条件，例如基本表S定义中要求“大学生年龄应当在1525周岁之间”。,41,2.WHERE子句表现能力,3.3.1 SQL映像语句,映像语句在数据查询中有着强大的检索功能，这在很大程度上得益于WHERE子句丰富的表现能力。这主要表现在：映像语句在WHERE子句中可以进行嵌套。WHERE子句中的逻辑条件不仅具有命题公式形式，还具有一阶谓词公式形式以及集合表达式形式,42,3.3.1 SQL映像语句,43,1.不具条件的列查询,3.3.2 单表查询,不带条件的列的查询是指查询表的全部列或者指定列，这里一般仅使用SELECT子句和FROM子句。例3-11（查询所有列）查询S所有列的情形：SELECT*FROM S；,44,2.具条件的列查询,3.3.2 单表查询,带条件的列的查询可以看作是先查询指定列，在结果中按照给定的条件对元组进行选择。此类查询需要用到整个映像语句。例3-14 查询计算机科学系（CS）全体学生姓名与年龄：SELECT Sn，SaFROM SWHERE Sd=CS；,45,2.具条件的列查询,3.3.2 单表查询,例3-15 查询所有成绩不及格的学生学号：SELECT S#FROM SCWHERE G 60；例3-16 查询所有年龄不在20岁以下的学生姓名和年龄：SELECT Sn，SaFROM SWHERE NOT Sa=20,46,2.具条件的列查询,3.3.2 单表查询,例3-17 查询年龄不在18至21岁的学生姓名与年龄：SELECT Sn，SaFROM SWHERE Sa NOT BETWEEN 18 AND 21；例3-18 查询信息系（IS），数学系（MA）和计算机科学系（CS）的学生姓名和年龄：SELECT Sn,SaFROM SWHERE Sd IN（IS，MA，CS）；,47,2.具条件的列查询,3.3.2 单表查询,例3-19 查询既不是信息系（IS）、数学系（MA），也不是计算机科学系（CS）的学生姓名和年龄：SELECT Sn,SaFROM SWHERE Sd NOT IN（IS，MA，CS）；例3-20 查询课程分数为空的学号和课程号：SELECT S#,C#FROM SCWHERE G IS NULL；,48,2.具条件的列查询,3.3.2 单表查询,例3-21 查询计算机系年龄在20岁以下的学生姓名：SELECT SnFROM SWHERE Sd=CSAND Sa 20；,49,3.查询结果排序,3.3.2 单表查询,例3-22 查询计算机系（CS）所有学生的名单并按学号升序显示：SELECT S#，SnFROM SWHERE Sd=CSORDER BY S#ASC；例3-23 查询全体学生情况，结果按学生年龄降序排列：SELECT*FROM SORDER BY Sa DESC；,50,4.查询通配符,3.3.2 单表查询,关键词“LIKE”可以看作谓词，用以进行字符串的匹配，其一般格式如下：NOTLIKEESCAPE匹配串设置方式是：通配字符%表示可以与任意长的字符匹配。例如a%b表示以a开头，以b 结尾的任意长度的字符串。像acb，affrrd，ab等。,51,4.查询通配符,3.3.2 单表查询,通配字符“_”（下横线）表示可以与单个的任意字符相配；其他字符表示其本身。例如a_b表示以a开头，以b 结尾的长度为3的任意字符串。像acb，afb，abb等。如果LIKE之后的匹配串中不含通配符，则可以用“=”运算符代替LIKE谓词，用代替NOT LIKE谓词。,52,4.查询通配符,3.3.2 单表查询,例3-24 查询姓名以A打头，且第3个字符必须为P的学生的姓名与系别：SECLET Sn,SdFROM SWHERE Sn LIKE A_P%；例3-25 查询姓名以A开头的学生姓名及所在系别：SELECT Sn，SaFROM SWHERE Sn LIKE A%；,53,4.查询通配符,3.3.2 单表查询,例3-26 查询课程名以“数据_”开头，且倒数第2个汉字为“原”的课程情形。SELECT*FROM CWHERE Cn LIKE 数据-%_ _ESCAPE；,54,3.3.3 基于集合运算多表查询,在查询中，如果同时涉及到两个或者两个以上的表，就称其为多表查询。关系数据查询结果是一个（元组）集合。关系数据多表查询就是考察相应多个查询结果集合之间的联系与制约。此时，多表查询所涉及到的逻辑条件就表现为“元素x与集合S”或者“集合S1与集合S2”之间关系的表示问题。,55,3.3.3 基于集合运算多表查询,集合S1与集合S2之间的关系主要表现为：集合S1与集合S2之间的包含或相等关系。集合S1与集合S2之间的代数运算关系。元素x与集合S间的关系主要表现为：不带量词的元素x相对集合S的属于或不属于关系：xS或S。带量词的元素x相对集合S的属于或不属于关系：xS、xS或xS、xS。,56,基于集合运算多表查询,按照上述的思路，多表查询实现有三种基本途径，即基于传统集合运算的多表查询、基于连接运算的多表查询和基于嵌套的多表查询。,57,3.3.3 基于集合运算多表查询,如果将多表查询涉及到的表“平等”看待，则可以通过集合间传统运算来描述查询结果集合间的某些关系。集合间包含关系 集合与集合间的包含关系可以通过WHERE子句中的映像语句（或元组集合）间的包含符实现。,58,3.3.3 基于集合运算多表查询,例3-27 查询至少修读S4所修读的所有课程的学生的学号：SELECT SC.S#FROM SCWHERE（SELECT SCx.C#FROM SCx:SC WHERE SC.S#=SCx.S#)CONTAINS(SELECT SCy.C#FROM SCy:SC WHERE SCy.S#=S4)；,59,3.3.3 基于集合运算多表查询,集合间代数运算关系 集合间代数运算可以通过映像语句间的集合操作来实现。集合操作主要有并操作UNION，交操作INTERSECT和差操作EXCEPT。,60,3.3.3 基于集合运算多表查询,例3-28 查询计算机科学系的学生以及年龄小于20岁的学生：SELECT*FROM SWHERE Sd=CSUNIONSELECT*FROM SWHERE Sa20；,61,3.3.3 基于集合运算多表查询,例3-29 查询计算机科学系的学生不大于20岁的交集：SELECT*FROM SWHERE Sd=CSINTERSECTSELECT*FROM S,62,3.3.3 基于集合运算多表查询,例3-30 查询计算机科学系的学生与年龄不大于20岁学生的差集：SELECT*FROM SWHERE Sd=CSEXCEPTSELECT*FROM SWHERE Sa=20；,63,3.3.4 基于连接多表查询,关系查询基本特色是引入了非传统集合运算例如单表的投影、选择和多表的连接，因此实际应用中多表查询也常常需要通过连接运算来实现。连接查询中连接条件表现在相应的WHERE子句当中。用来连接两个表的条件称为连接条件或连接谓词，一般语句格式为，.，.按照连接谓词的不同可以将其分为如下几种类型。,64,3.3.4 基于连接多表查询,（1）等值与非等值连接连接中，如果连接谓词为比较运算符，则称之为等值或非等值连接。例3-31 查询学习课程号为C1课程的所有学生学号与姓名：这是一个涉及到两张表的查询，它可以写成为：SELECT S#，SnFROM S，SCWHERE S.S#=SC.S#AND C#=C1；,65,3.3.4 基于连接多表查询,例3-32 查询修读课程名为DATABASE的所有学生的姓名：这是一个涉及到三张表的查询，它可以写为SELECT S.SnFROM S,SC,CWHERE S.S#=SC.S#AND SC.C#=C.C#AND C.Cn=DATABASE；,66,3.3.4 基于连接多表查询,（2）自身连接有时在查询中需要对相同的表进行连接，即同一个表与其自身进行连接，称其为自身连接。在自身连接中，为了区别两个相同的表，需对一个表使用两种表名。例3-33 查询至少修读S5所修读一门课的学生学号：SELECT FIRST.SC.S#FROM SC AS SC1，SC AS SC2WHERE SC1.C#=SC2.C#AND SC2.S#=S5；,67,3.3.4 基于连接多表查询,（3）外连接在通常连接操作中，只有满足连接条件的元组才作为结果输出，例如在例3-27中，如果学号为S7和S8的同学没有选修课程C1，由于在表SC中就没有相应元组，查询结果中就不会出现他们的学号。但在某些情况下，需要以S表为主体，列出每个学生的基本情况和选课情况，如有一个学生没有选课，就只输出其基本情况，选课信息设为空值即可，这就需要使用外连接（Out Join）。为了实现上述想法，改写例3-27中查询语句如下。,68,3.3.4 基于连接多表查询,例3-34 查询所有学生的基本情况和选课情况：SELECT S#，Sn，Sa，Sd，C#，GFROM S LEFT OUT JOIN SC ON S.S#=SC.S#（*）；说明：外连接可以看作是为连接中的某个表（例如本例中的SC表）添加一个“通用”的行，这个行全部由“空值”组成，它能够和另一个表（例如本例中的S表）中所有不满足连接条件的元组进行连接。在本例中就是对S表中S7和S8元组进行连接。由于这个通用行中的各列全部都是空值，因此在连接中，S7和S8两行中来自SC表的属性全部是空值。,69,3.3.5 基于嵌套多表查询,嵌套查询使用嵌套结构逐次求解，可将复杂问题转化为多个相对简单的查询，具有突出的分层结构特征，同时“结构化查询语言”SQL的数据查询也真正表现出“结构”的意义。嵌套查询具有下述特点：映像语句的WHERE子句或HAVING子句中再使用了另一个映像语句。涉及到的各个SELECT语句都是单表形式，即其中FROM子句都只涉及一个关系表。,70,3.3.5 基于嵌套多表查询,嵌套查询一般是“由内向外”或“由外向内”的处理方式。嵌套查询的实质是确定关系表R1的查询结果集的条件涉及到另一个关系表R2（注意，R2可以是R1的别名关系表）查询结果集，即R2查询结果集合构成R1查询结果集的形成条件。处于嵌套外层的关系称为外层关系，处于内层的称为内层关系。此时，如果内层查询结果集合与外层查询无关，则称其为不相关内层查询，否则称为相关内层查询。,71,3.3.5 基于嵌套多表查询,（1）不相关内层查询不相关内层查询现执行“由内向外”的查询过程，内层查询结果集用于构建外层查询的查询条件。在不相关内层查询主要涉及到外层查询结果的条件元素与内层查询查询结果集合的关系。一个元素a相对于一个集合A的关系可以分为下述几类：a“属于”或“不属于”集合A。在SQL中，使用“a NOT IN A”表示这种情形。,72,3.3.5 基于嵌套多表查询,a 与集合A中全体元素或部分元素之间的运算关系，例如“a大于或等于A的全部元素”或“a大于或等于A的某个元素”等。在SQL中，使用“a ALL（SOME）A”表示这种情形。需要指出，“a IN A”等价于“a=SOME A”，“a NOT IN A”等价于“a ALL A”。,73,3.3.5 基于嵌套多表查询,例3-35 查询修读课程号为C1的所有学生的姓名：SELECT S.SnFROM SWHERE S.S#IN(SELECT SC.S#FROM SCWHERE SC.C#=C1),74,3.3.5 基于嵌套多表查询,下面的例子就是三重嵌套。例3-36 查询修读课程名为C+的所有学生的姓名：SELECT Sn FROM S WHERE S.S#IN(SELECT SC.S#FROM SC WHERE SC.C#IN(SELECT C.C#FROM C WHERE C.Cn=C+）；,75,3.3.5 基于嵌套多表查询,（2）相关内层查询 相关内层查询的特点是外层查询的条件由内层查询结果集确定，而内层查询结果集的查询条件又于外层查询有关。此时，外层查询中逻辑条件由WHERE子句中带有量词的语句实现。量词有两种形式：存在量词“”与全称量词“”。在SQL中存在量词“”用“EXISTS”表示，存在量词“”的非“”用“NOT EXISTS”表示。,76,3.3.5 基于嵌套多表查询,相关内层查询过程是先由外层查询涉及关系集合中依次取出各个元组，将元组中某个属性值取检验内层查询涉及关系表中是否存在元组，其相应属性值等于该值。如果存在，就返回“true”，此时，外层关系表中该元组就放入结果表；否则返回“false”，此时放弃该元组。这可以看做是一种“由外向内”的执行方式。也就是说，相关内层查询并不返回具体数据，值产生逻辑真值，在某些情况下可以具有比不相关内层查询更高的效率。,77,3.3.5 基于嵌套多表查询,例3-37 在前述例3-35中，查询“修读课程号为C1的所有学生的姓名”还可以用下述语句表示：SELECT SnFROM SWHERE EXISTS(SELECT*FROM SCWHERE S.S#=SC.S#AND SC.C#=C1)；,78,3.3.5 基于嵌套多表查询,例3-38 查询修读课程号不为C1的所有学生的姓名：SELECT SnFROM SWHERE NOT EXISTS(SELECT*FROM SC WHERE S.S#=SC.S#AND SC.C#=C1）,79,3.3.5 基于嵌套多表查询,例3-39查询选修了全部课程的学生姓名。这里查询条件是一个带有全称量词的表达式。SQL语言中没有全称量词“”，但是由于成立：yQ（y）=x（Q（y）故在SQL可以使用NOTEXIST通过转换来表示全称量词。转换的基本思路是先将查询用含有全称量词的谓词公式形式写出，再将此公式转换为用存在量词表示的形式。,80,3.3.5 基于嵌套多表查询,本例所需查询实际上是求出集合学生姓名|学生选修了所有课程。如果设变量x表示学生姓名，变量y表示课程，谓词Q（y）表示学生x选修课程y，则查询所要求出的集合就是：x|yQ（y）。这里的谓词“yQ（y）”就是WHERE子句中应当出现的条件，其存在量词表达式就是：y Q（y）。其语义为：查询这样的学生，没有一门课程是他不选修的。由此可以写出相应的SQL语句。,81,3.3.5 基于嵌套多表查询,SELECT Sn FROM S WHERE NOT EXISTS(SELECT*FROM C WHERE NOT EXISTS(SELECT*FROM SC WHERE S#=S.S#AND C#=C.C#）；,82,3.3.5 基于嵌套多表查询,例3-40查询所学课程包含学号为S3的学生所学课程的所有学号。本查询可以使用逻辑蕴涵式表达：查询学号为“x”的学生，对所有的课程y，只要S学生选修了课程y，则x也选修了课程y。我们用表示谓词“学生S选修了课程y”；用Q表示谓词“学生x选修了课程y”。此时所需查询可以用谓词公式表示为：yPQ。,83,3.3.5 基于嵌套多表查询,由于SQL没有全称量词“”，该公式可以转换为如下等价形式：（y（PQ）（y（PQ）y（PQ）其语义为：不存在这样的课程y，学生S选修了y，而学生x没有选修。由此可以写出如下的SQL语句：,84,3.3.5 基于嵌套多表查询,SELECT DISTINCT S#FROM SC AS X WHERE NOT EXISTS(SELECT*FROM SC AS Y WHERE Y.S#=S3 AND NOT EXISTS(SELECT*FROM SC AS Z WHERE Z.S#=X.S#AND Z.C#=Y.C#）,85,1.聚集函数,3.3.6 函数与表达式,SQL 的查询中可以插入一些常用的聚集函数（Aggregate Function），它们主要有以下五种类型。COUNT函数I COUNT（DISTINCT|ALL*）关系中元组个数统计COUNT函数II COUNT（DISTINCT|ALL）关系中给定列中属性值个数统计,86,1.聚集函数,3.3.6 函数与表达式,SUM函数 SUM（DISTINCT|ALL）关系中计算数值型属性值总和。AVG函数 AVG（DISTINCT|ALL）关系中计算数值型属性值平均值。MAX函数 MAX（DISTINCT|ALL）关系中计算给定列中数值型属性值的最大者。MIN函数 MIN（DISTINCT|ALL）关系中计算给定列中数值型属性值的最大小者,87,2.数据表达式与纯量公式,3.3.6 函数与表达式,SQL查询输出结果中可以基本算术运算表达式，这些表达式可以有“+”、“-”“*”、“/”与属性列名以及数值常量和相关纯量函数组成。常用的纯量函数主要有：取整函数INTEGER；平方根函数SQRT；三角函数SIN、COS；字符串函数SUBSRING；大写字符函数UPPER；日期型函数MONTHS_BETWEEN（月份差）。,88,2.数据表达式与纯量公式,3.3.6 函数与表达式,例3-43 给出修读课程为C7的所有学生的学生分级（即学分数*3）:SELECT S#,C#,G*3FROM SWHERE C#=C7；例3-44 给出计算机系下个年度学生的年龄:SELECT Sn,Sa+1FROM SWHERE Sd=CS；,89,3.3.7 函数与表达式,例3-45 给定一个关系表T（A，B），表中属性值均为整数类型。设有如下查询语句:SELECT A,B,A*B，SQRT（B）FROM T；上述查询输出结果为:A,B以及A，B的乘积与B的平方根。聚集函数与纯量函数还可以进行“复合”运算以实现较为复杂的查询，例如：SELECT MIN(A),MIN(B),MIN(A*B)，MIN(SQRT（B）)FROM T；,90,3.分组域筛选表达式,3.3.6 函数与表达式,SQL语句中使用“GROUP BY”子句和“HAVING”子句对映像语句所得到的集合元组分组和设置逻辑条件进行筛选。在SQL语句中，分组与筛选语句一般格式为GROUP BYHAVING；,91,3.3.6 函数与表达式,例3-46 给出每个学生的平均成绩:SELECT S#,AVG(G)FROM SCGROUP BY S#；例3-47 给出每个学生修读课程的门数:SELECT S#,COUNT(C#)FROM SCGROUP BY S#；,92,3.3.6 函数与表达式,例3-48 给出所有超过5个学生所修读课程的学生数:SELECT C#,COUNT(S#)FROM SCGROUP BY C#HAVING COUNT(*)5；,93,3.4 数据更新,SQL的更新功能包括删除、插入及修改等三种操作，相应关键词如下图所示,94,1.删除多个元组,3.4.1 数据删除,例3-50 删除学生WANG的记录：DELETE FROM SWHERE SN=WANG；,95,2.带子查询语句的删除,3.4.1 数据删除,例3-51 删除计算机系全体学生的选课纪录:DELETE FROM SCWHERE CS=(SELECT SD FROM S WHERE S.S#=SC.S#)；,96,3.4.2 数据插入,SQL插入语句的一般形式为:INSERT INTO)|该语句的含义是执行一个插入操作，将VALUES所给出的值插入INTO所指定的表中。,97,3.4.2 数据插入,3-52 将一个学生新记录（S#：S35，Sn：liu，Sa：20，Sd：CS）插入到表S中：INSERTINTO SVALUES（S35，liu，20，CS）；例3-53 在SC中插入记录（S23，C10）：INSERTINTO SC（S#，C#）VALUES(S23,C10)；,98,3.4.3 数据修改,SQL 修改语句的一般格式为:UPDATE SET=表达式，=表达式WHERE 该语句的含义是修改（UPDATE）指定基本表中满足（WHERE）逻辑条件的元组，并把这些元组按照SET子句中的表达式修改相应列上的值。,99,3.4.3 数据修改,（1）修改单个元组的值例3-54 将学号为S16的学生系别改为CS:UPDATE SSET Sd=CSWHERE S#=S16；,100,3.4.3 数据修改,（2）修改多个元组的值例3-55 将数学系的学生年龄均加一岁:UPDATE SSET Sa=Sa+1WHERE Sd=MA；,101,3.4.3 数据修改,例3-56 将计算机系学生的成绩全部置为零:UPDATE SCSET G=0WHERE CS=(SELECT Sd FROM S WHERE S.S#=SC.S#)；,102,3.5 视图管理,SQL的视图管理机制具有十分重要的意义，其主要表现在以下方面：简化用户操作 视图简化了用户观点，用户不必了解整个模式，仅需将注意力集中于它所关注的领域，大大方便了使用。用户可以多角度看待同一数据 对于同一基本表，不同的用户可以建立不同的视图，从而以不同的观点以多角度来观察和看待同一数据，扩大了数据的应用界面。,103,3.5 视图管理,提供一定的逻辑独立性 由于视图的存在，当基本表发生改变时，例如对关系模式进行扩充或者分解，应用程序不需要改变，因为新建立的视图可以定义用户原来的各种关系，使得用户外模式保持不变，用户应用程序通过视图机制仍然能够查找数据，从而在一定程度上提供了数据的逻辑独立性。对机密数据提供安全保护 在设计数据库应用系统时，对不同的用户定义不同的视图，使得机密数据不出现在不应当看到这些数据的用户视图上，从而视图机制就自动提供了机密数据的安全保护功能。,104,1.视图创建,3.5.1 视图定义,（1）一般视图创建SQL的视图可由创建视图语句予以建立，其一般格式如下：CREATE VIEW(,)AS WITH CHECK OPTION；其中，可以是任意复杂的SELECT语句，但其中不能含有ORDER BY子句和DISTINCT短语。,105,3.5.1 视图定义,组成视图的属性列要么全部省略，要么全部指定。如果视图定义中省略属性列名，则隐含该视图由映像语句中SELECT子句的目标列组成。但在下列情况下必须明确指定组成视图的所有属性列名：某个目标列不是单纯的属性列名，而是聚集函数或表达式。多表连接导出的视图中有几个同名列作为该视图的属性列名。需要在视图中为某个列启用更合适的名称。,106,3.5.1 视图定义,例3-57 创建一个计算机系学生的视图：CREATE VIEW CS-S(S#,SN,SD,SA，G)AS SELECT*FROM S WHERE Sd=CS WITH CHECK OPTION；,107,3.5.1 视图定义,例3-58 定义学生姓名和他修读课程名及其成绩视图：CREATE VIEW S-C-G(SN,CN,G)AS SELECT S.SN,C.CN,SC.GFROM S,C,SC WHERE S.S#=SC.S#AND SC.C#=C.C#；,108,3.5.1 视图定义,（2）视图基本特例在实际应用中，通常有如下三种有用的视图特例：行列子集视图 即视图仅由单个基本表导出，同时只是去掉了基本表的某些行或某些列并且保留了主键。分组视图 即带有集函数和GROUP BY子句查询所定义的视图。具虚拟列视图 即设置了一些基本表中并不存在的衍生属性列(