数据库原理与设计03关系数据语言SQL.ppt

关系数据库语言提供关系数据库的数据定义、查询、更新和控制等功能，是用户和数据库管理员建立、使用数据库的主要工具。基于关系代数的语言是用对关系（表）的运算来表达查询的语言，它的运算基于关系代数，包括一般的集合运算和特殊的关系运算投影、选择、连接、求商等。基于谓词演算的语言是用查询得到的元组（Tuple）所应满足的谓词来表达查询的语言，它的运算基于关系演算。这一类语言又可按谓词变元的基本对象是元组变量（Tuple Variable）或是域变量（Domain Variable）分为两类：元组演算式语言（Tuple Calculus Language）与域演算式语言（Domain Calculus Laguage）。,第3章 关系数据语言SQL,Home,结构化查询语言SQL（Structured Query Language）主要是基于关系代数的，但有部分是基于谓词演算的，还有部分则是其他的。SQL86、SQL89、SQL2（又称SQL92）、SQL3（又称SQL99）。从字面看SQL 只是一个查询语言，而实际上SQL作为一种标准数据库语言，从对数据库的随机查询到数据库的管理和程序设计，SQL几乎无所不能，功能十分丰富；而且SQL是一种陈述性的语言，语言简洁易学，书写非常简单，使用方便。SQL既可以作为交互式语言独立使用，用作联机终端用户与数据库系统的接口；也可以作为子语言嵌入宿主语言中使用，增强应用程序的处理能力。,第3章 关系数据语言SQL,Home,第3章 关系数据语言SQL,3.1 SQL语言组成,3.2 SQL的数据定义,3.3 SQL的数据查询,3.4 SQL的数据操纵,3.5 SQL的数据控制,Home,3.6 嵌入式SQL,3.7 构造数据类型、域定义与动态SQL,3.1 SQL语言组成,3.1.1 关系与表,3.1.2 SQL的数据类型,3.1.3 SQL的语句类型,Home,3.1.1 关系与表,表（或称关系）具有下列特征：一个表中有一个或多个命名列（Column）。各列无从左到右的顺序。一个表中可有零个或多个行（Row）。行的顺序是无关紧要的。一个表中的每一行都是唯一的。任何行和列的交点都有一个唯一的值。同一列中的所有值都必须取自同一值域。每一个表都有一个称为主关键字（Primary Key）的列的子集，它的值可以唯一确定每一行。表关系，行元组、记录，列属性、字段。,Home,3.1.1 关系与表,教学管理数据库,Home,早期SQL标准定义的基本数据类型有8种：CHARACTER（字符）、NUMERIC（数值）、DECIMAL（十进制数）、INTEGER（整数）、SMALLINT（短整数）、FLOAT（浮点数）、REAL（实数）、DOUBLE PRECISION（双精度数）。在SQL3中规定了3类数据类型：预定义数据类型、构造数据类型和用户定义数据类型（UDT，User Defined Type）。,3.1.2 SQL的数据类型,Home,1数值型 INTEGER 简写为INT。SMALLINT NUMERIC(p,s)例如，语句：x2 NUMERIC(3，2)DECIMAL(p,s)例如，语句：x3 DEC(4,3)FLOAT(p)例如，语句：x5 FLOAT(10)REAL DOUBLE PRECISION,Home,3.1.2 SQL的数据类型,2字符串型 CHARACTER(n)定义数据类型为字符串。例如，语句 x1 CHAR(30)VARCHAR(n)定义可变长字符串。3位串型 BIT(n)BIT VARYING(n)例如，语句 y1 BIT(8)y1可以存放一个二进制位串，如十六进制表示的位串X 44，或用二进制表示的同一位串B,01000100。,Home,3.1.2 SQL的数据类型,4时间型 DATE 定义日期。例如，语句 start_date DATE TIME TIMESTAMP 定义时间戳。5布尔型 BOOLEAN 定义布尔数，其值可以是：TRUE（真）、FALSE（假）、UNKNOWN（未知）。,Home,3.1.2 SQL的数据类型,对于数值型数据，可以执行算术运算和比较运算，但对其他类型数据，只可以执行比较运算，不能执行算术运算。构造数据类型是由特定的保留字和预定义数据类型构造而成的，如用“REF”定义的引用类型，用“ROW”定义的行类型，用“ARRAY”定义的聚合类型等。用户定义数据类型（UDT）是一个对象类型，是由用户按照一定的规则用预定义数据类型组合定义的自己专用的数据类型。许多SQL产品还扩充了其他一些数据类型，如TEXT（文本）、MONEY（货币）、GRAPHIC（图形）、IMAGE（图像）、GENERAL（通用）、MEMO（备注）等。,Home,3.1.2 SQL的数据类型,（1）查询语言（QL）查询语言用来对已存在的数据库中的数据按照指定的组合、条件表达式或排序进行检索。它的基本结构是由SELECT子句、FROM子句、WHERE子句组成的查询块。（2）数据操纵语言（DML）DML的命令用来改变数据库中的数据，它有3个基本语句：INSERT（插入）、UPDATE（修改）、DELETE（删除）。,3.1.3 SQL的语句类型,Home,（3）数据定义语言（DDL）DDL用来创建数据库中的各种对象，包括数据库模式、表、视图、索引、同义词、聚簇等，它的基本语句有：CREATE SCHEMA、CREATE TABLE、CREATE VIEW、CREATE INDEX等。（4）数据控制语言（DCL）DCL用来授予或回收访问数据库的某种特权，控制数据操纵事务的发生时间及效果、对数据库进行监视等。数据控制基本语句有：GRANT、REVOKE、COMMIT、ROLLBACK、LOCK、UNLOCK等。,Home,3.1.3 SQL的语句类型,SQL3按照语句的功能把SQL语句分为7类：SQL模式语句、SQL数据语句、SQL事务语句、SQL控制语句、SQL连接语句、SQL会话语句、SQL诊断语句。SQL语句由动词、SQL对象、限定词等组成。SQL3规定的SQL对象（Object）有：簇集（Cluster）、授权ID、特权（Privilege）、目录（Catalog）、模式（Schema）、表（Table）、列（Column）、SQL域（Domain）和UDT、约束和断言（Constraint and Assertion）、字符集（Character Set）、聚合（Collation）、翻译（Translation）、触发器（Trigger）、模块（Module）、调用例程（SQL-invoked Routine）等。,Home,3.1.3 SQL的语句类型,3.2 SQL的数据定义,Home,3.2.1 表的建立和删除,3.2.2 表的扩充与修改,3.2.3 视图（View）,索引,数据库模式的创建与删除,3.2.1 表的建立和删除,1表的建立 CREATE TABLE 表名(列定义 完整性约束)列定义给出列的名称、数据类型、缺省值。每一个列的数据类型可以是预定义数据类型，也可以是用户定义数据类型。缺省值用默认子句表达：DEFAULT 缺省值。完整性约束可以是对表的约束或对列的约束，以保证数据的完整性。,Home,完整性约束可用以下3种语句表达。（1）主关键字子句：PRIMARY KEY(列名)（2）外来关键字子句：FOREIGN KEY(列名)REFERENCES 表名(列名)（3）检验子句：CHECK(约束搜索条件)CHECK子句规定了一个规则，即一个表的一个列或多列的组合只能包含在这个规则定义的集合之内的值，其中的约束搜索条件可以是一个条件表达式或一个包含SELECT语句的SQL语句。,Home,3.2.1 表的建立和删除,例3.1 对于表3.1 的教学管理数据库中的表STUDENTS，可以定义如下：CREATE TABLE STUDENTS(SNO NUMERIC(6,0)NOT NULL SNAME CHAR(8)NOT NULL AGE NUMERIC(3,0)SEX CHAR(2)BPLACE CHAR(20)PRIMARY KEY(SNO)凡带有NOT NULL的列，不允许出现空值；未带NOT NULL的列，可以出现空值。空值NULL表示“不知道”。当首次用CREATE TABLE定义一个新表后，只是建立了一个空表。,Home,3.2.1 表的建立和删除,例3.2 对于表3.1 的教学管理数据库中的表ENROLLS，可以定义如下：CREATE TABLE ENROLLS(SNO NUMERIC(6,0)NOT NULL CNO CHAR(4)NOT NULL GRADE INT PRIMARY KEY(SNO,CNO)FOREIGN KEY(SNO)REFERENCES STUDENTS(SNO)FOREIGN KEY(CNO)REFERENCES COURSES(CNO)CHECK(GRADE IS NULL)OR(GRADE BETWEEN 0 AND 100),Home,3.2.1 表的建立和删除,Home,3.2.1 表的建立和删除,例3.3 根据表3.1的STUDENTS表，建立一个只包含学号、姓名、年龄的女学生表。CREATE TABLE GIRL AS SELECT SNO,SNAME,AGE FROM STUDENTS WHERE SEX=女；,Home,3.2.1 表的建立和删除,2表的删除 DROP TABLE 表名 CASCADE|RESTRICT 当采用任选项CASCADE时，在删除表时，该表中的数据、表本身以及在该表上所建的索引和视图将全部随之消失；当采用任选项RESTRICT时，只有在先清除了表中的全部记录行数据，以及在该表上所建的索引和视图后，才能删除一个空表，否则拒绝删除表。例3.4 删除教师表TEACHER。DROP TABLE TEACHER,3.2.2 表的扩充和修改,1在现存表中增加新列 ALTER TABLE 表名 ADD COLUMN(列名 数据类型 NULLNOT NULL，)例3.5 在教师表中增加住址列。ALTER TABLE TEACHERS ADD(ADDR CHAR(50),Home,2删除已存在的某个列 ALTER TABLE 表名 DROP COLUMN 列名 CASCADE|RESTRICT 例3.6 把STUDENTS表中的BPLACE列删除，并且把引用BPLACE列的所有视图和约束也一起删除。ALTER TABLE STUDENTS DROP BPLACE CASCADE,Home,3.2.2 表的扩充和修改,3补充定义主关键字 ALTER TABLE 表名 ADD PRIMARY KEY(列名表)例3.7 补充定义ENROLLS表的主关键字。ALTER TABLE ENROLLS ADD PRIMARY KEY(SNO,CNO)；,Home,3.2.2 表的扩充和修改,4删除主关键字 ALTER TABLE 表名 DROP PRIMARY KEY5补充定义外来关键字 ALTER TABLE 表名-1 ADD FOREIGN KEY(外来关键字名)(列名表)REFERENCES 表名-2 ON DELETE CASCADE|RESTRICT|SET NULL6删除外来关键字 ALTER TABLE 表名 DROP 外来关键字名,Home,3.2.2 表的扩充和修改,视图是以现存表的全部或部分内容建立起来的一个表。视图不是物理存在的，它不包含真正存储的数据，不占存储空间。但视图可以像一般的表那样操作（少有限制），因此视图也称作虚表，而真正物理存在的表称作实表或基本表。优点是提高了数据库对应用程序的独立性，有利于保持数据的一致性。其次是简化了复杂查询，提供了保持数据库安全性的手段。,3.2.3 视图（View）,Home,1建立视图 CREATE VIEW视图名（列名1，列名2，）AS 查询子句 例3.9 建立一个只包括教师号、姓名和年龄的视图FACULTY。CREATE VIEW FACULTY AS SELECT TNO,TNAME,AGE FROM TEACHERS,Home,3.2.3 视图（View）,视图创建总是包括一个查询语句SELECT。可以利用SELECT语句从一个表中选取所需要的行或列（使用查询条件WHERE 子句）构成视图，也可以从几个表中选取所需要的行或列（使用子查询和链接技术）构成视图。但要注意，在视图定义中不能包含ORDER BY子句。,Home,3.2.3 视图（View）,例3.10 从学生表、课程表和选课表中产生一个视图GRADE_TABLE，它包括学生姓名、课程名和成绩。CREATE VIEW GRADE_TABLE AS SELECT SNAME,CNAME,GRADE FROM STUDENTS,COURSES,ENROLLS WHERE STUDENTS.SNOENROLLS.SNO AND COURSES.CNO=ENROLLS.CNO2删除视图 DROP VIEW 视图名 CASCADE|RESTRICT 例3.11 删除视图GRADE_TABLE DROP VIEW GRADE_TABLE RESTRICT,Home,3.2.3 视图（View）,早期的SQL标准中采用了索引技术，但是SQL2和SQL3不主张使用索引，而代之以在创建表时直接定义关键字。1创建索引 CREATE UNIQUE INDEX 索引名 ON 表名(列名1ASCDESC，列名2ASCDESC，)例3.12 在学生表中按学号建立索引。CREATE UNIQUE INDEX ST ON STUDENTS(SNO,ASC),3.2.4 索引,Home,2删除索引 DROP INDEX ON表名 例3.13 删除按学号所建立的索引。DROP INDEX ST,3.2.4 索引,Home,3.2.5 数据库模式的创建与删除,1数据库模式的创建 CREATE SCHEMA 模式名 AUTHRIZATION 用户名 CREATE DOMAIN子句|CREATE TABLE子句|CREATE VIEW|例3.14 创建一个简易教学数据库的数据库模式 TEACHING_DB，属主为ZHANG。CREATE SCHEMA TEACHING_DB AUTHRIZATION ZHANG,Home,2数据库模式的删除 DROP SCHEMA 模式名 CASCADE|RESTRICT当用DROP SCHEMA语句删除数据库模式时，可以选用两种方式。（1）选用CASCADE，即当删除数据库模式时，则本数据库模式和其下属的基本表、视图、索引等全部被删除。,3.2.5 数据库模式的创建与删除,Home,（2）选用RESTRICT，即本数据库模式下属的基本表、视图、索引等事先已清除，才能删除本数据库模式，否则拒绝删除。例3.15 删除简易教学数据库模式TEACHING_DB。DROP SCHEMA TEACHING_DB CASCADE,3.2.5 数据库模式的创建与删除,Home,SQL的SELECT语句用于检索数据。“SELECT-FROM-WHERE”的基本结构块：SELECT A，A，An FROM R，R，Rm WHERE PSELECT-FROM-WHERE查询语句块的作用等价于下列的关系代数表达式：,3.3 SQL的数据查询,Home,3.3 SQL的数据查询,Home,3.3.1 单表查询,3.3.2 多表查询,3.3.3 表达式与函数的使用,相关子查询,关系代数运算,3.3.1 单表查询,1从表中选出指定的列 SELECT*FROM COURSES SQL对查询的结果不会自动去除重复行，如果要求删除重复行，可以使用限定词DISTINCT。例如 SELECT DISTINCT CLASS FROM TEACHING2从表中选出若干记录 WHERE子句允许用户确定一个谓词，该谓词的值随着记录（元组）数据的不同可以为真（True）或假（False）。带有WHERE子句的SELECT语句，执行结果只给出使谓词为真的那些记录（元组）值。WHERE之后的谓词就是查询条件。,Home,例3.16 找出3个学分的课程号和课程名。SELECT CNO,CNAME FROM COURSES WHERECREDIT3例3.17 查询年龄大于22岁的学生情况。SELECT*FROM STUDENTS WHERE AGE22,Home,3.3.1 单表查询,例3.18 找出籍贯为河北的男生的姓名和年龄。SELECT SNAME,AGE FROM STUDENTS WHEREBPLACE河北 AND SEX男例3.19 找出年龄在2023岁之间的学生的学号、姓名和年龄，并按年龄升序排序。SELECT SNO,SNAME,AGE FROM STUDENTS WHERE AGE BETWEEN 20 AND 23 ORDER BY AGE,Home,3.3.1 单表查询,谓词ORDER BY后可以跟多个排序的变量名，第一个变量为主序，下面依次类推。每一个排序列名后可用限定词ASC（升序）或DESC（降序）声明排序的方式，缺省为升序。例3.20 找出年龄小于23岁、籍贯是湖南或湖北的学生的姓名和性别。SELECT SNAME,SEX FROM STUDENTS WHERE AGE23 AND BPLACE LIKE湖 或 SELECT SNAME,SEX FROM STUDENTS WHERE AGE23 AND BPLACE IN（湖南，湖北）,Home,3.3.1 单表查询,条件比较运算符、和逻辑运算符AND（与），此外还可以使用的运算符有：（大于）、（大于等于）、（小于等于）、（不等于）、NOT（非）、OR（或）等。谓词LIKE 只能与字符串联用，常常是“列名 LIKE pattern”的格式。特殊字符“_”和“%”作为通配符。谓词IN表示指定的属性应与后面的集合（括号中的值集或某个查询子句的结果）中的某个值相匹配，实际上是一系列的OR（或）的缩写。谓词NOT IN表示指定的属性不与后面的集合中的某个值相匹配。谓词BETWEEN是“包含于之中”的意思。,Home,3.3.1 单表查询,例3.21 找出年龄在1822岁的学生的姓名和性别。SELECT SNAME,SEX FROM STUDENTS WHERE AGE BETWEEN 18 AND 22,Home,3.3.1 单表查询,3NULL NULL的唯一含义就是“未知值”。NULL不能用来表示无形值、缺省值、不可用值，以及取最低值或取最高值。SQL规定，在含有运算符+、*、/的算术表达式中，若有一个值是空值，则该算术表达式的值也是空值；任何一个含有NULL 的比较操作的结果的取值都为“假”。,Home,3.3.1 单表查询,在SQL中不能使用条件：列名NULL。在SQL中只有一个特殊的查询条件允许查询NULL值：WHERE SNO IS NOT NULL 例3.22 找出学生表中籍贯是空值的学生的姓名和性别。SELECT SNAME,SEX FROM STUDENTS WHERE BPLACE IS NULL,Home,3.3.1 单表查询,1子查询 WHERE子句可以包含另一个称为子查询（Subquery）的查询，即在SELECT语句中先用子查询查出一个表的值，主句（主查询）根据这些值再去查另一个表的内容（与单表SELECT操作一样）。子查询总是括在圆括号中，作为表达式的可选部分出现在条件比较运算符的右边，并且可有选择地跟在IN、ANY（SOME）、ALL和EXISTS等谓词后面。采用子查询的查询又称嵌套查询。,3.3.2 多表查询,Home,例3.23 找出成绩为95分的学生的姓名。SELECT SNAME FROM STUDENTS WHERESNO(SELECT SNO FROM ENROLLS WHERE GRADE95),Home,3.3.2 多表查询,例3.24 找出成绩在90分以上的学生的姓名。SELECT SNAME FROM STUDENTS WHERE SNO IN(SELECT SNO FROM ENROLLS WHERE GRADE90)或 SELECT SNAME FROM STUDENTS WHERE SNOANY(SELECT SNO FROM ENROLLS WHERE GRADE90),Home,3.3.2 多表查询,谓词IN的含义是，对于表STUDENTS中的记录，只有当它的SNO 在子查询返回的SNO表中时，WHERE子句的值才为真。谓词ANY的含义是，如果所规定的运算符（这里是）对于子查询返回的结果集合中的任何一个值为真的话，WHERE子句的值就为真。如果子查询只返回一行和一列，则称为标量子查询（Scalar Subquery）；如果子查询只返回一行但多于一列，则称为行子查询（Row Subquery）；如果子查询返回多行和多列，则称为表子查询（Table Subquery）。,Home,3.3.2 多表查询,2连接查询 所谓连接查询，就是通过连接，使查询的数据从多个表中检索取得。例3.25 查询全部学生的学生名和所学课程号及成绩。SELECT SNAME,CNO,GRADE FROM STUDENTS,ENROLLS WHERE STUDENTS.SNOENROLLS.SNO 当两个列名相同时（如本例的SNO），必须在其列名前加上所属表的名字和一个圆点“”以示区别。表的连接除“”外，还可用比较运算符、以及BETWEEN、LIKE、IN等谓词。,Home,3.3.2 多表查询,例3.26 找出籍贯为山西或河北，成绩为90分以上的学生的姓名、籍贯和成绩。SELECT SNAME,BPLACE,GRADE FROM STUDENTS,ENROLLS WHERE BPLACE IN(山西，河北)AND GRADE 90 ANDSTUDENTS.SNO=ENROLLS.SNO 当构造多表连接查询命令时，必须遵循两条规则。第一，连接条件数正好比表数少1（若有三个表，就有两个连接条件)；第二，若一个表中的主关键字是由多个列组成，则对此主关键字中的每一个列都要有一个连接条件（也有少数例外情况）。一般来说，连接查询可以替换大多数的嵌套子查询。,Home,3.3.2 多表查询,3FROM子句中的子查询 SQL3规定允许通过一个子查询引用查询结果的表，即在FROM子句中可以使用子查询，其查询的结果表称为导出关系（Derived Relation）。例3.28 查出课程成绩在80分以上的女学生的姓名、课程名和成绩。SELECT SNAME,CNAME,GRADE FROM(SELECT SNAME,CNAME,GRADE FROM STUDENTS,ENROLLS,COURSES WHERE SEX女)AS TEMP(SNAME,CNAME,GRADE)WHERE GRADE80,3.3.2 多表查询,Home,3.3.3 表达式与函数的使用,1算术表达式 算术表达式由算术运算符、*、与列名或数值常量所组成。例3.29 查询各课程的学时数。SELECT CNAME,COURSE_TIMECREDIT*16 FROM COURSES,Home,2内部函数 内部函数（Build-In Function）SQL标准中只使用COUNT、SUM、AVG、MAX、MIN函数，称之为聚集函数（Set Function）。COUNT函数的结果是该列统计值的总数目，SUM函数求该列统计值之和，AVG函数求该列统计值之平均值，MAX函数求该列最大值，MIN函数求该列最小值。例3.30 找出教师的最小年龄。SELECT MIN(AGE)FROM TEACHERS,3.3.3 表达式与函数的使用,Home,例3.31 统计年龄小于等于22岁的学生人数。SELECT COUNT(*)FROM STUDENTS WHERE AGE 22 例3.32 找出学生的平均成绩和所学课程门数。SELECT SNO,AVG(GRADE),COURSESCOUNT(*)FROM ENROLLS GROUP BY SNO 纯量函数，如算术函数INTEGER（取整）、SQRT（求平方根）、三角函数SIN、COS、字符串函数SUBSTRING（取子串）、UPPER（大写字符）以及日期型函数MONTHS_BETWEEN（月份差）等。这些函数都是针对某个列操作的，产生的结果是一个新的数据列。,3.3.3 表达式与函数的使用,Home,例3.33 假设一个表TT含有两个列A和B，表的记录为：则查询命令为：SELECT A,B,A*B,SQRT(B)FROM TT 产生的结果是：,3.3.3 表达式与函数的使用,Home,而使用聚集函数的查询命令为：SELECT MIN(A),MIN(B),MIN(A*B),MIN(SQRT(B)FROM TT 产生的结果是：,3.3.3 表达式与函数的使用,Home,例3.34 找出年龄超过平均年龄的学生姓名。SELECT SNAMEFROM STUDENTSWHERE AGE(SELECT AVG(AGE)FROM STUDENTS),3.3.3 表达式与函数的使用,Home,3GROUP BY 与 HAVING GROUP BY 子句把一个表按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。GROUP BY子句总是跟在WHERE子句后面，当WHERE子句缺省时，它跟在 FROM子句后面。HAVING子句常用于在计算出聚集之后对行的查询进行控制。例3.35 找出各课程的平均成绩，按课程号分组，且只选择学生超过3人的课程的成绩。SELECT CNO,AVG(GRADE),STUDENTSCOUNT(*)FROM ENROLLS GROUP BY CNO HAVING COUNT(*)=3,3.3.3 表达式与函数的使用,Home,4判断 基本的SQL条件是判断，它表明了结果是True、False或Unknown的条件。,3.3.3 表达式与函数的使用,Home,谓词ALL、ANY、SOME的使用格式：标量表达式比较运算符 ALL|ANY|SOME（表子查询）SELECT SNO,SNAME FROM STUDENTS WHERE SNO NOT IN(SELECT SNO FROM ENROLLS WHERE CNO=C1)或 SELECT SNO,SNAME FROM STUDENTS WHERE SNO ALL(SELECT SNO FROM ENROLLS WHERE CNO=C1),3.3.3 表达式与函数的使用,Home,3.3.4 相关子查询,当一个子查询涉及到一个来自外部查询的列时，称为相关子查询（Correlated Subquery)。相关子查询要用到存在测试谓词EXISTS和NOT EXISTS，以及ALL、ANY（SOME）等。例3.37 查询没有选任何课程的学生的学号和姓名。SELECT SNO,SNAME FROM STUDENTS WHERE NOT EXISTS(SELECT*FROM ENROLLS WHERE ENROLLS.SNO=STUDENTS.SNO),Home,例3.38 查询哪些课程只有男生选读。SELECT DISTINCT CNAME FROM COURSES C WHERE 男 ALL(SELECT SEX FROM ENROLLS，STUDENTS WHERE ENROLLS.SNO=STUDENTS.SNO AND ENROLLS.CNO=C.CNO),3.3.4 相关子查询,Home,例3.39 要求给出一张学生、籍贯列表，该表中的学生的籍贯省份，也是其他一些学生的籍贯省份。SELECT SNAME,BPLACE FROM STUDENTS A WHERE EXISTS(SELECT*FROM STUDENTS B WHERE A.BPLACE=B.BPLACE AND A.SNO B.SNO),3.3.4 相关子查询,Home,在SQL中只有存在测试谓词EXISTS和NOT EXISTS。然而任何一个带全称量词的谓词可以转换为等价的带存在量词的谓词：(x)p(x(p)可以用双嵌套NOT EXISTS来实现带全称量词的查询，解决所谓的“FOR ALL”问题查找所有与B相关的A。,3.3.4 相关子查询,Home,例3.40 找出选修了全部课程的学生的姓名。本查询可以改为：查询这样一些学生，没有一门课程是他不选修的。SELECT SNAME FROM STUDENTS WHERE NOT EXISTS(SELECT*FROM COURSES WHERE NOT EXISTS(SELECT*FROM ENROLLS WHERE ENROLLS.SNOSTUDENTS.SNO AND ENROLLS.CNOCOURSES.CNO),3.3.4 相关子查询,Home,3.3.5 关系代数运算,在SQL2和SQL3中提供了一些可以直接使用的关系代数运算谓词。1UNION、INTERSECT、EXCEPT（1）UNION 例3.41 设有某商场工作人员的两张表：营业员表SP_SUBORD和营销经理表SP_MGR，其关系数据模式如下：SP_SUBORD(SALPERS_ID,SALPERS_NAME,MANAGER_ID,OFFICE)SP_MGR(SALPERS_ID,SALPERS_NAME,MANAGER_ID,OFFICE)其中，属性SALPERS_ID为工作人员的编号,SALPERS_NAME为工作人员的姓名,MANAGER_ID为所在部门经理的编号,OFFICE为工作地点。,Home,若查询全部商场工作人员，可以用下面的SQL语句：(SELECT*FROM SP_SUBORD)UNION(SELECT*FROM SP_MGR)或等价地用下面的SQL语句：SELECT*FROM(TABLE SP_SUBORD UNION TABLE SP_MGR),3.3.5 关系代数运算,Home,（2）INTERSECT(SELECT*FROM SP_SUBORD)INTERSECT(SELECT*FROM SP_MGR)或等价地用下面的SQL语句：SELECT*FROM(TABLE SP_SUBORD INTERSECT TABLE SP_MGR)或用带ALL的SQL语句：(SELECT*FROM SP_SUBORD)INTERSECT ALL(SELECT*FROM SP_MGR)或 SELECT*FROM(TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR),3.3.5 关系代数运算,Home,（3）EXCEPT(SELECT*FROM SP_MGR)EXCEPT(SELECT*FROM SP_SUBORD)或等价地用下面的SQL语句：SELECT*FROM(TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)或用带ALL的SQL语句：(SELECT*FROM SP_MGR)EXCEPT ALL(SELECT*FROM SP_SUBORD),3.3.5 关系代数运算,Home,2JOIN 在SQL2和SQL3中扩展了关系代数的连接操作概念，提供了一组连接运算谓词，可以实现自然连接（Natural Join）、内连接（Inner Join）、外连接（Outer Join）、合并连接（Union Join）、交叉连接（Cross Join）等，下面仅介绍自然连接。例3.42 查询籍贯为四川、课程成绩在80分以上的学生信息及其成绩。(SELECT*FROM STUDENTS WHERE BPLACE=四川)NATURAL JOIN(SELECT*FROM ENROLLS WHERE GRADE=80),3.3.5 关系代数运算,Home,3外连接与外部并 外连接允许在结果表中保留非匹配元组，空缺部分填以NULL。外连接的作用是在做连接操作时避免丢失信息。外连接有3类：（1）左外连接（Left Outer Join）。连接运算谓词为LEFT OUTER JOIN，其结果表中保留左关系的所有元组。,3.3.5 关系代数运算,Home,（2）右外连接（Right Outer Join）。连接运算谓词为RIGHT OUTER JOIN，其结果表中保留右关系的所有元组。（3）全外连接（Full Outer Join）。连接运算谓词为FULL OUTER JOIN，其结果表中保留左右两关系的所有元组。,3.3.5 关系代数运算,Home,例3.43 列出全部教师的姓名及其任课的课程号、班级。SELECT TNAME,CNO,CLASS FROM TEACHERS LEFT OUTER JOIN TEACHING USING(TNO)外部并允许对两个非相容可并的关系进行并操作，操作的结果表的属性是两个关系属性集的并，而对于那些元组中没有的属性，填以NULL。外部并的作用是在做并操作时避免丢失信息。外部并运算谓词为OUTER UNION。,3.3.5 关系代数运算,Home,3.4 SQL的数据操纵,Home,3.4.1 插入数据,3.4.2 修改数据,3.4.3 删除数据,向数据库表插入新行用INSERT语句。1用VALUES子句向表中插入数据 INSERT INTO 表名(列名1，列名2，)VALUES(值1，值2，)例3.44 把教师李映雪的记录加入到教师表TEACHERS中。INSERT INTO TEACHERS VALUES(1476，李映雪，44，副教授),3.4.1 插入数据,Home,Home,3.4.1 插入数据,2用子查询向表中插入数据 INSERT INTO 表名(列名1，列名2，)SELECT 列名1，列名2，FROM 表名 WHERE 搜索条件 例3.45 成绩优秀的学生将留下当教师。INSERT INTO TEACHERS(TNO，TNAME)SELECT DISTINCT SNO，SNAME FROM STUDENTS，ENROLLS WHERE STUDENTS.SNOENROLLS.SNO AND GRADE90,修改数据库中的记录行用UPDATE语句。1用SET子句修改表中数据 UPDATE 表名 SET 列名1表达式1，列名2表达式2，WHERE条件表达式 例3.47 把所有学生的年龄增加一岁。UPDATE STUDENTS SET AGEAGE+1,3.4.2 修改数据,Home,Home,3.4.2 修改数据,2带子查询的修改 例3.48 学生张春明在数据库课考试中作弊，该课成绩应作零分计。UPDATE ENROLLS SET GRADE0 WHERE CNOC1 AND 张春明(SELECT SNAME FROM STUDENTS WHERE STUDENTS.SNO=ENROLLS.SNO),Home,3.4.3 删除数据,DELETE FROM 表名 WHERE 条件表达式 例3.49 从教师表中删除年龄已到60岁的退休教师的数据。DELETE FROM TEACHERS WHERE AGE 60,3.5 SQL的数据控制,Home,SQL数据控制的功能主要包括：控制用户对数据的存取能力，维护数据的安全性和完整性，数据库的并发控制与恢复等。在SQL2和SQL3标准中增设了数据控制语言（SQL-DCL），它提供了一系列的功能，包括撤消已定义的数据库模式、表、视图、列及数据值域的操作，授予和回收系统特权，变更一个域的定义，变更表的定义，撤消或增加对表的约束，对表的封锁与解锁等。,1表视图特权的授予 一个SQL特权允许一个被授权者在给定的数据库对象上进行特定的操作。授权操作的数据库对象包括：表/视图、列、域等。授权