您现在的位置:首页 > >

03 数据库原理及应用 第3章 关系数据库的标准语言SQL

发布时间:

数据库原理
Principles of Database 第3章 关系数据库的标准语言SQL

湖南工学院计算机系
数据库原理

第3章 关系数据库的标准语言SQL
?

【本章掌握内容】 1、数据定义语言的使用方法 2、数据操作语言的使用方法

【本章了解内容】 1、存储过程的编写 2、触发器的编写

第3章 关系数据库的标准语言SQL
? ? ? ? ?

3.1 3.2 3.3 3.4 3.5

SQL概述 SQL的数据定义 SQL的数据操纵 视图 触发器

数据库原理

3.1 SQL概述
?

?

SQL最早是1974年由Boyce和Chamberlin提 出,并作为IBM公司研制的关系数据库管理系 统原型System R的一部分付诸实施的。 现在SQL已经成了关系数据库的标准语言,并 且发展了三个主要标准,即ANSI(美国国家标 准机构)SQL;对ANSI SQL修改后在1992年采 纳的标准,称为SQL-92或SQL2;最近又出了 SQL-99,也称SQL3标准。
数据库原理

3.1.1 SQL的特点
?

1.综合统一
?

?

?

?

(1)数据定义语言(Data Definition Language, DDL)。DDL用于定义数据库的逻辑机构,是对关 系模式一级的定义,包括基本表、视图及索引的定 义。 (2)数据查询语言(Data Query Language, DQL)。DQL用于查询数据。 (3)数据操纵语言(Data Manipulation Language,DML)。DML用于对关系模式中的具体 数据进行增、删、改等操作。 (4)数据控制语言(Data Control Language, DCL)。DCL用于数据访问权限的控制。
数据库原理

?

2.高度非过程化
?

用户无需了解存取路径,存取路径的选择以及SQL 语句的操作过程由系统自动完成。这不但大大减轻 了用户负担,而且有利于提高数据独立性。 SQL语言既是自含式语言,又是嵌入式语言。作为 自含式语言,它能够独立地用于联机交互的使用方 式,用户可以在终端键盘上直接输入SQL命令对数 据库进行操作。作为嵌入式语言,SQL语句能够嵌 入到高级语言(例如C)程序中,供程序员设计程 序时使用。

?

3.用同一种语法结构提供两种使用方式
?

?

4.语言简洁,易学易用
数据库原理

3.1.2 SQL语言的基本概念
数据库的体系结构分为三级,SQL也支持这三级模式结构 ? 1.基本表(Base Table) 基本表是模式的基本内容。实际存储在数据库中的表对应 一个实际存在的关系。 ? 2.视图(View) 视图是外模式的基本单位,用户可以通过视图使用数据库 中基于基本表的数据。视图是从其它表(包括其它视图) 中导出的表,它仅是保存在数据的一种逻辑定义字典 中,本身并不独立存储在数据库中,因此视图是一种虚 表。 ? 3.存储文件 存储文件是内模式的基本单位。一个基本表对应一个或多 个存储文件,一个存储文件可以存放在一个或多个基本 表,一个基本表可以有若干个索引,索引同样存放在存 储文件中。存储文件的存储结构对用户来说是透明的。
数据库原理

SQL支持的数据库体系结构
SQL 用户 SQL 用户

视图 1

视图 2

外模式

基本表 1

基本表 2

基本表 3

模式

存储文件 1

存储文件 2

存储文件 3

内模式

数据库原理

3.2 SQL的数据定义
?

通过SQL语言的数据定义功能,可以完成 基本表、视图、索引的创建和修改

数据库原理

数据类型
1)数值型 ? INTEGER:定义数据类型为整数类型,它的精 度(总有效位)由执行机构确定。 ? SMALLINT:定义数据类型为短整数类型,它 的精度由执行机构确定。 ? NUMERIC(p,s): 定义数据类型为数值型,并给 定精度p(总的有效位)或标度s(十进制小数 点右面的位数)。 ? FLOAT(p): 定义数据类型为浮点数值型,其精 度等于或大于给定的精度p。 ? REAL: 定义数据类型为浮点数值型,它的精度 由执行机构确定。
数据库原理

DOUBLE PRECISION: 定义数据类型为双精度浮点类型,它的精 度由执行机构确定。 (2)字符类型 ? CHARACTER(n): 定义数据类型为字符串,并给定串长度(字符 数)。 ? VARCHAR(n): 定义可变长度的字符串,其最大长度为n。 (3)位串型 ? BIT(n):定义数据类型为二进制位串,其长度为n。 ? BIT VARYING(n): 定义可变长的二进制位串,其最大长度为n。 (4)时间型 ? DATETIME: 定义一个日期时间类型,日期和时间数据由有效的日 期或时间组成。 (5)布尔型 ? BOOLEAN: 定义布尔数,其值可以是TRUE(真)或FALSE(假)。
?

数据库原理

3.2.1 基本表的定义
CREATE TABLE <基本表名> (<列名1> <列数据类型> [列完整性约 束], <列名2> <列数据类型> [列完整性约 束], …… [表级完整性约束])
数据库原理

创建基本表例子
?

例3.1 创建员工关系表。 CREATE TABLE Employee (Eno CHAR(5), Ename VARCHAR(10), Sex CHAR(2), Marry CHAR(2), Dno CHAR(4));
数据库原理

?

例3.2 创建员工关系表时加入列完整性约束。 CREATE TABLE Employee (Eno CHAR(5) NOT NULL UNIQUE, Ename VARCHAR(10) NOT NULL, Sex CHAR(2) NOT NULL, Marry CHAR(2) DEFAULT'未', Dno CHAR(4) NULL);
数据库原理

3.2.2 基本表的修改与删除
ALTER TABLE <基本表名> [ADD <新列名> <列数据类型> [列完整性 约束]] [DROP COLUMN <列名>] [MODIFY <列名> <新的数据类型>] [ADD CONSTRAINT <表级完整性约束>] [DROP CONSTRAINT <表级完整性约束>]
数据库原理

基本表修改例子
例3.3 在Employee表中增加一个Birth列(出 生)。 ALTER TALBE Employee ADD Birth DATETIME NULL; ? 例3.4 修改Employee表中Marry列为BOOLEAN。 ALTER TALBE Employee MODIFY Marry BOOLEAN; ? 例3.5 删除Employee表中新增的Birth列。 ALTER TALBE Employee DROP COLUMN Birth;
?

数据库原理

2.基本表的删除
?

当数据库某个基本表不再使用时,可以 将其删除。当一个基本表被删除后,该 表中的所有数据连同该表建立的索引都 会被删除。但由该表导出的视图的定义 仍然存在数据字典当中,只是无法使用。 DROP TABLE <基本表名>

数据库原理

3.2.3 索引的建立和维护
为了改善查询性能,可以建立索引。 索引是根据表中的一列或若干列按照一定顺序建立 的列值,与表中记录行之间具有一一对应关系的 辅助表结构。索引属于物理存储的路径概念,而 不是用户使用的逻辑概念。建立在多个列上的索 引被称为复合索引。 有两种重要的索引: ? 聚集索引(Clustered Index) ? 非聚集索引(Non-clustered Index)。
数据库原理

聚集索引
?

聚集索引类似于按姓氏排列数据的电话 簿。由于聚集索引规定了数据在表中的 物理存储顺序,因此一个表只能包含一 个聚集索引。但该索引可以包含多个列 (组合索引),就像电话簿按姓氏和名 字进行组织一样。聚集索引对于那些经 常要搜索范围值的列特别有效。
数据库原理

非聚集索引
?

非聚集索引与书本中的索引类似。数据存储在 一个地方,索引存储在另一个地方,索引带有 指针指向数据的存储位置。索引中的项目按索 引键值的顺序存储,而表中的信息则按另一种 顺序存储(这可以由聚集索引规定)。与使用 书中索引的方式相似,DBMS在搜索数据值 时,先对非聚集索引进行搜索,找到数据值在 表中的位置,然后从该位置直接检索数据

数据库原理

1.创建索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名 > ON <基本表名> (<列名> [<次序>],[,<列 名> [<次序>]]…); 说明: (1)UNIQUE:规定索引的每一个索引值只对应 于表中的唯一记录。 (2)CLUSTER:规定此索引为聚集索引。省略 CLUSTER则表示创建的索引为非聚集索引。 (3)<次序>:建立索引时指定列名的索引表是 数据库原理 ASC(升序)或DESC(降序)。

创建索引例子
例3.7 在Employee表的属性列Eno上创建 一个非聚集索引。 CREATE INDEX IDX_DNO_ENO ON Employee(Dno ASC,Eno ASC); ? 例3.8 在Employee表的属性列Ename上 创建一个聚集索引。 CREATE CLUSTER INDEX IDX_ENAME ON Employee(Ename ASC);
?

数据库原理

2.删除索引
?

过多或不当的索引会导致系统低效。用 户在表中每加进一个索引,数据库就要 做更多的工作。过多的索引甚至会导致 索引碎片,降低系统效率。 DROP INDEX <索引名>

?

数据库原理

3.3 SQL的数据操纵
?

SQL语言的数据操纵功能主要包括查询 (SELECT)、插入(INSERT)、删除 (DELETE)和更新(update)四个方面。

数据库原理

3.3.1 数据查询
SQL数据查询是SQL语言中最重要、最丰富也 是最灵活的内容。 SELECT <列名或表达式A1>,<列名或表达式 A2>,…,<列名或表达式An> FROM <表名或视图名R1>,<表名或视图名 R2>,…<表名或视图名Rm> WHERE P; ? 查询的基本结构包括了三个字句:SELECT、 FROM、WHERE。
?

数据库原理

(1)SELECT子句,对应关系代数中的投影运 算,用于列出查询结果的各属性。 ? (2)FROM子句,对应关系代数中的广义笛卡 尔乘积,用于列出被查询的关系:基本表或视 图。 ? (3)WHERE子句,对应关系代数中的选择谓 词,这些谓词涉及FROM子句中的关系的属 性,用于指出连接、选择等运算要满足的查询 条件。 ? SQL数据查询的基本结构在关系代数中等价于: πA1,A2,…An(бp(R1×R2×…×Rm))
?

数据库原理

SQL数据查询的一般格式为:
SELECT [ALL|DISTINCT] <列名或表达式> [别名1] [,<列名或表达式> [别名2]]… FROM <表名或视图名> [表别名1] [,<表 名或视图名> [表别名2]]… [WHERE <条件表达式>] [GROUP BY <列名1>] [HAVING <条件 表达式>] [ORDER BY <列名2>] [ASC|DESC]
数据库原理

1.单表无条件查询
单表无条件查询是指只含有SELECT子句 和FROM子句的查询 SELECT [ALL | DISTINCT] <列名或表达式 > [别名1] [,<列名或表达式> [别名 2]]… FROM <表名或视图名> [表别名1] [,<表 名或视图名> [表别名2]]…
?

数据库原理

① 查询关系中的指定列
例3.10 查询所有学生的学号、姓名、年 龄。 SELECT Sno, Sname, Age FROM Student; ? 例3.11 查询所有课程的基本情况。 SELECT Cno, Cname, Credit FROM Course
?

数据库原理

② DISTINCT保留字的使用
当查询的结果只包含元表中的部分列时,结果 中可能会出现重复列,使用DISTINCT保留 字可以使重复列值只保留一个。

③ 查询列中含有运算的表达式
SELECT子句的目标列中可以包含带有+、-、 ×、/的算术运算表达式,其运算对象为常 量或元组的属性。

数据库原理

列名中含有算术表达式例子
【例3.13】查询所有学生的学号、姓名 和出生年份。 SELECT Sno, Sname, 2005-Age FROM Student
?

数据库原理

(4)查询列中含有字符串常量
【例3.14】 查询每门课程的课程名和学分。 SELECT Cname, '学分', Credit FROM Course ? 这种书写方式可以使查询结果增加一个 原关系里不存在的字符串常量列,元组 在该列上的每个值就是字符串常量。

数据库原理

(5)查询列中含有集函数
COUNT(*) 统计查询结果中的元组个数 ? COUNT(<列名>) 统计查询结果中一个列上值 的个数 ? MAX(<列名>) 计算查询结果中一个列上的最大值 ? MIN(<列名>) 计算查询结果中一个列上的 最小值 ? SUM(<列名>) 计算查询结果中一个数值列上的总 和 ? AVG(<列名>) 计算查询结果中一个数值列上的平 均值 ① 除COUNT(*)外,其他集函数都会先去掉空值再计 算。 ② 在<列名>前加入DISTINCT保留字,会将查询结果中 重复的列去掉后再计算。
?

数据库原理

列名含有函数运算例子
? ?

?

?

【例3.15】 COUNT函数的使用。 SELECT COUNT(*) FROM Student 统计学生表中的记录数。 SELECT COUNT(Place) FROM Student 统计学生的籍贯(去掉空值)。 SELECT COUNT(DISTINCT Place) FROM Student 统计学生的籍贯种类数。
数据库原理

2.单表带条件查询
一般地,数据库中的数据量都非常大,显示表 中所有的行是很不现实的,可以在查询的时候 根据查询条件对表进行水平分割,使用WHERE 子句即可实现。 SELECT [ALL | DISTINCT] <列名或表达式> [别 名1] [,<列名或表达式> [别名2]]… FROM <表名或视图名> [表别名1] [,<表名或 视图名> [表别名2]]… WHERE <条件表达式>
?

数据库原理

(1)使用关系运算表达式的查询
使用比较运算符的条件表达式的一般形式为: <列名>θ<列名> 和 <列名>θ常量值
?

例3.17 查询籍贯是湖北的学生信息。 SELECT * FROM Student WHERE Slace='湖北'
?

数据库原理

?

例3.18 查询选修了C01号课程且考试及 格的学生学号和成绩。 SELECT Sno, Grade FROM Study WHERE Cno=’C01’ AND Grade>=60

数据库原理

(2)使用特殊运算符
ANSI标准SQL允许在WHERE子句中使用特殊的运算 符。
运算符
IN、NOT IN BETWEEN…AND…、NOT BETWEEN…AND… IS NULL、IS NOT NULL LIKE、NOT LIKE





判断属性值是否在一个集合内 判断属性值是否在某个范围中 判断属性值是否为空 判断字符串是否匹配

数据库原理

?

例3.19 查询籍贯为湖北和福建两地的学生信息。 SELECT * FROM Student WHERE Place IN ('湖北','福建') 相当于 SELECT * FROM Student WHERE Place ='湖北' OR place='福建')
数据库原理

?

例3.20 从Study表中查询考试成绩在85和95之 间的学生学号。 SELECT Sno FROM Study WHERE Grade BETWEEN 85 AND 95
?

SELECT Sno FROM Study WHERE Grade >= 85 AND Grade <= 95

数据库原理

(3)字符串比较
?

?

?

在SQL中可以使用关系运算符来进行字符串比 较。实际上比较的是它们的词典顺序(如字典 顺序或字母表顺序)。 如果a1a2…an和b1b2…bm是两个字符串,若 a1<b1或a1=b1且a2<b2或a1=b1, a2=b2且 a3<b3,当n<m并且1a2…an=b1b2…bn时, 字符串a1a2…an<b1b2…bm,也就是第一个 字符串正好是第二个字符串的前缀。 例如fodder<foo
数据库原理

SQL也提供了一种简单的模式匹配功能用 于字符串比较,可以使用LIKE和NOT LIKE来实现=和<>的比较功能 ? 基本格式为: <列名> LIKE / NOT LIKE <字符串常数>
?

数据库原理

通配符
?

字符串常数中通常要使用通配符。在字符串常数中 除通配符外的其它字符只代表自己。通配符可以出 现在字符串的任何位置。但通配符出现在字符串首 时查询效率会变慢。
通配符
% _ (下划线)





表示任意长度的字符 串 表示任意的单个字符

数据库原理

例3.21 查询姓王的学生的学号、姓名、年龄。 SELECT Sno, Sname, Age FROM Student WHERE Sname LIKE '王%' ? 若通配符本身就是字符串常量的内容,则可增 加短语ESCAPE,使之转义。 如:WHERE sname LIKE ‘To\_%’ESCAPE‘\’, 则紧跟在 \ 后面的 _ 不是通配符而是字符串常 量的一个字符而已。该查询条件为:Sname属 性值以“To_”开头,后面紧跟任意个字符的串。
?

数据库原理

3.分组查询和排序查询
?

(1)GROUP BY与HAVING
?

含有GROUP BY的查询称为分组查询。 GROUP BY子句把一个表按某一指定列(或 一些列)上的值相等的原则分组,然后再对 每组数据进行规定的操作。分组查询一般和 查询列的集函数一起使用,当使用GROUP BY子句后,所有的集函数都将是对每一个组 进行运算,而不是对整个查询结构进行运算。
数据库原理

例3.22 查询每一门课程的平均得分。 在Study关系表中记录着学生选修的每门课程和相应的考试 成绩。由于一门课程可以有若干个学生学习,SELECT语 句执行时首先把表Study的全部数据行按相同课程号划分 成组,即每一门课程都有一组学生和相应的成绩,然后 再对各组执行AVG(grade)
?

SELECT Cno, AVG(Grade) FROM Study GROUP BY Cno

数据库原理

?

查询课程的平均得分
Cno
C01 C02 C03 C04 C05

AVG(Grade)
75.5 83 75 73.5 97

数据库原理

例3.23 查询被3人以上选修的每一门课程的平 均成绩、最高分、最低分。 SELECT Cno, AVG(Grade), MAX(Grade), MIN(Grade) FROM Study GROUP BY Cno HAVING COUNT(*)>=3 ? 本例中SELECT语句执行时首先按Cno把表 Study分组,然后对各组的记录执行 AVG(Grade)、MAX(Grade)、MIN(Grade)等集 函数,最后根据HAVING子句的条件表达式 COUNT(*)>=3过滤出组中记录数在3条以上的 分组。
?

数据库原理

(2)排序查询
?

? ?

SELECT子句的ORDER BY子句可使输出的查询结果 按照要求的顺序排列。由于是控制输出结果,因此 ORDER BY子句只能用于最终的查询结果。基本格 式是: ORDER BY <列名> [ASC|DESC] 有了ORDER BY子句后,SELECT语句的查询结果表 中各元组将按照要求的顺序排列:首先按第一个< 列名>值排列;前一个<列名>相同的,再按下一个 <列名>值排列
数据库原理

?

例3.24 查询所有学生的基本信息,并按 年龄升序排列,年龄相同的按学号降序 排列。 SELECT * FROM Student ORDER BY Age, Sno DESC

数据库原理

4.多表查询
?

在数据库中通常存在着多个相互关联的 表,用户常常需要同时从多个表中找出 自己想要的数据,这就涉及到多个数据 表的查询。SQL通过连接查询、并操作、 交操作、差操作可以实现关系代数中的 运算功能。

数据库原理

(1)连接查询
SQL提供在FROM子句中列出每个关系,然后在 SELECT子句和WHERE子句中引用FROM子句中的 关系的属性,而WHERE子句中用来连接两个关系 的条件称为连接条件。 ? 例3.25 查询籍贯为湖北的学生的学号、选修的课 程号和相应的考试成绩。 SELECT Student.Sno, Cno, Grade FROM Student, Study WHERE Student.Sno = Study.Sno AND Place LIKE '湖北‘
?

数据库原理

④ 在等值连接中,目标列可能出现重复的 列,例如: SELECT Student .*, Study . * FROM Student, Study WHERE Student.Sno = Study.Sno AND Place LIKE '湖北' ? 这里Student . Sno和Study . Sno是两个 重复列
数据库原理

?

⑤ 还有一种特殊的连接运算,它不带连 接条件,称为乘积运算
SELECT Student.Sno, Cno, Grade FROM Student, Study 两个关系的乘积会产生大量没有意义的元组, 并且这种操作要消耗大量的系统资源,一般 很少使用。

数据库原理

可以进行两个以上的连接。 ? 例3.26 查询籍贯为湖北的学生的姓名、 选修的课程名称和相应的考试成绩。
SELECT Sname, Cname, Grade FROM Student, Study, Course WHERE Student.Sno = Study.Sno AND Study.Cno=Course.Cno AND Place LIKE '湖北'
数据库原理

(3)自身连接
?

有一种连接是一个关系与自身进行的连 接,这种连接称做自身连接。SQL允许为 FROM子句中的关系R的每一次出现定义 一个别名。这样在SELECT子句和WHERE 子句中的属性前面就可以加上“别名.<属 性名>”。

数据库原理

?

例3.27 查询籍贯相同的两个学生的基本 信息。
SELECT A.* FROM Student A, Student B WHERE A.Place = B.Place
?

该例中要查询的内容属于表Student。上面 的语句将表Student分别取两个别名A、B。 这样A、B相当于内容相同的两个表。将A和 B中籍贯相同的元组进行连接,经过投影就 得到了满足要求的结果。
数据库原理

(3)并操作
?

?

SQL使用UNION把查询的结果并起来,并去掉 重复的元组,如果要保留所有重复,则必须使 用UNION ALL。 【例3.28】 查询籍贯是湖北的学生以及姓张的 学生的基本信息。
SELECT * FROM Student WHERE Place LIKE '湖北' UNION SELECT * FROM Student WHERE Sname LIKE '张%'

数据库原理

(4)交操作
?

?

SQL使用INTERSECT把同时出现在两个查询中的结果取 出,实现交操作,并且也会去掉重复的元组,如果要保 留所有重复,则必须使用INTERSECT ALL。 例3.29 查询年龄大于18岁、姓张的学生的基本信息。
SELECT * FROM Student WHERE Age >18 INTERSECT SELECT * FROM Student WHERE Sname LIKE '张%'

数据库原理

(5)差操作
SQL使用MINUS把出现在第一个查询结果中但不 出现在第二个查询结果中的元组取出,实现差 操作。 ? 例3.30 查询年龄大于20岁的学生基本信息与女 生的基本信息的差集。
SELECT * FROM Student WHERE Age >20 MINUS SELECT * FROM Student WHERE Sex LIKE '女'

数据库原理

5.嵌套查询
?

?

?

嵌套查询是指一个SELECT-FROM-WHERE查询 块嵌入在另一个SELECT–FROM– WHERE查询 块WHERE子句中的查询。 外层查询称为父查询,内层查询称为子查询。 子查询中还可以嵌套其它子查询,即允许多层 嵌套查询,其执行过程是由里到外的,每一个 子查询是在上一级查询处理之前完成的 在子查询中不能使用ORDER BY子句。
数据库原理

(1)返回单值的子查询
?

?

子查询返回的检索信息是单一的值。这类子查 询看起来就像常量一样,因此经常把这类子查 询的结果与父查询的属性用关系运算符来比较。 例3.31 查询选修了C语言的学生的学号和相应 的考试成绩。
SELECT Sno, Grade FROM Study WHERE Cno = (SELECT Cno FROM Course WHERE Cname LIKE ‘C语言’)

数据库原理

例3.32 查询考试成绩大于总平均分的学生学号。 SELECT DISTINCT Sno FROM Study WHERE Grade > (SELECT AVG(Grade) FROM Study) ? 在嵌套查询中,只有能确切知道内层查询返回 的是单值时,才可以直接使用关系运算符进行 比较。
?

数据库原理

(2)返回多值的子查询
? ?

使用ALL、ANY等谓词来解决。 例3.33 查询成绩至少比选修了C02号课程的一个学生成绩 低的学生学号。
SELECT Sno FROM Study WHERE Grade < ANY (SELECT Grade FROM Study WHERE Cno ='C02') AND Cno <> 'C02'

?

ANY运算符表示至少一个或某一个,因此使用<ANY就可表 示至少比某集合中的一个少的含义

数据库原理

?

实际上,比最大的值小就等价于<ANY, 该例子可用聚合函数MAX来实现。
SELECT Sno FROM Study WHERE Grade < (SELECT MAX(Grade) FROM Study WHERE Cno ='C02') AND Cno <> 'C02'
数据库原理

?

例3.34 查询成绩比所有选修了C02号课程的学 生成绩低的学生学号。
SELECT Sno FROM Study WHERE Grade < ALL (SELECT Grade FROM Study WHERE Cno ='C02') AND Cno <> 'C02'

?

ALL运算符表示所有或者每个,因此使用<ALL 就可表示至少比某集合所有都少的含义。实际 上,比最小的值小就等价于<ALL
数据库原理

?

该例子可用聚合函数MIN来实现。
SELECT Sno FROM Study WHERE Grade < (SELECT MIN(Grade) FROM Study WHERE Cno ='C02') AND Cno <> 'C02'
数据库原理

?

?

对于在父查询中需要判断某个属性的值与子查询结果 中某个值相等的这类查询可以用IN实现,也就是说可 以用IN来代替“=ANY”。 例3.35 查询选修了C语言的学生的基本信息。
SELECT * FROM Student WHERE Sno IN (SELECT Sno FROM Study WHERE Cno IN SELECT Cno FROM Course WHERE Cname LIKE ‘C语言’)

数据库原理

(3)相关子查询
?

?

子查询的查询条件依赖于父查询,每次 要对子查询中的外部元组变量的某一项 赋值,这类子查询称为相关子查询。 在相关子查询中经常使用EXISTS谓词。 子查询中含有EXISTS谓词后不会返回任 何结果,只得到“真”或“假”。

数据库原理

?

【例3.36】 查询选修了C语言的学生的学号。
SELECT Sno FROM Study WHERE EXISTS (SELECT * FROM Course WHERE Study.Cno=Course.Cno AND Cname LIKE ‘C语言‘)

?

首先取外层查询中Study表的第一个元组,根据它 与内层查询相关的属性值(即Cno值)处理内层 查询,若WHERE子句的返回值为真(即内层查询 结果非空),则取此元组放入结果表中

数据库原理

?

?

与EXISTS谓词相对应的是NOT EXISTS谓词。使用 存在量词NOT EXISTS后,若内层查询结果为空, 则外层的WHERE子句返回真值,否则返回假值。 例3.37 查询所有没选C04号课程的学生的姓名。
SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Study WHERE Study.Sno=Student.Sno AND Cno = ‘C04’)
数据库原理

?

SQL语言中没有全称量词,因此必须利用谓词演算将一个 带有全称量词的谓词转换为等价的带有存在量词的谓词:

(?x ) p ≡ ?(?x (? p ))
?

例3.38 查询选修了全部课程的学生姓名。
由于没有全称量词,这里将题目的意思转换成等价的存在量词形式: 查询这样的学生姓名,没有一门课程是他不选的。该查询可以形 式化的表示如下: ? 用p表示谓词“该学生选课” ? 用x表示谓词“课程”,则上述查询可表示为,即“对于任何一门课程 该学生都选了”,将它转换为等价的带有存在量词的谓词后,则表 示为,即“没有任何一门课程该学生不选”。

数据库原理

?

该查询涉及3个关系表:存放学生姓名的Student表、 存放所有课程信息的Course表、存放学生选课信息的 Study关系表。其SQL语句如下:
SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM Study WHERE Sno=Student.Sno AND Cno=Course.Cno))

数据库原理

?

SQL语言中也没有蕴函逻辑运算,因此必须利 用谓词演算将一个逻辑蕴函的谓词转换为等价 的带有存在量词的谓词:

p → q ≡ ?p ∨ q

数据库原理

例3.39 查询至少选修了学生03061选修的全部课程的学生 学号。 本题的查询要求解释如下:查询这样的学生,凡是03061选 修的课程,他都选了。换句话说,若有一个学号为x的学 生,对所有的课程y,只要学号为03061的学生选修了课程 y,则x也选修了y,那么就将他的学号选出来。该查询可 以形式化如下:
?

用p表示谓词“学生03061选修了课程y” ? 用q表示谓词“学生x选修了课程y” 则上述查询可表示为:该查询可转换为如下等价形式:
?

(?y )( p → q ) ≡ ??y (?( p → q )) ≡ ??y (?(? p ∨ q )) ≡ ??y ( p ∧ ? q )
数据库原理

?

它所表达的语义为:不存在这样的课程y,学生03061 选了y,而学生x没有选。用SQL语言可表示如下:
SELECT DISTINCT Sno FROM Study X WHERE NOT EXISTS (SELECT * FROM Study Y WHERE Y.Sno='03061' AND NOT EXIST (SELECT * FROM Study Z WHERE Z.Sno=X.Sno AND Z.Cno=Y.Cno))

数据库原理

3.3.2 插入数据
当基本表建立以后,就可以往表中插入数 据了,在SQL中插入数据使用INSERT语 句实现。INSERT语句有两种插入形式: ? 插入单个元组 ? 插入多个元组。

数据库原理

1.插入单个元组
INSERT INTO <基本表名> [(<列名1>,< 列名2>,…,<列名n>)] VALUES(<列值1>,<列值2>,…,<列 值n>) ? 其中,<基本表名>指定要插入元组的表 的名字;<列名1>,<列名2>,…,<列 名n>为要添加列值的列名序列;VALUES 后则一一对应要添加列的输入值。
数据库原理

?

例3.40 在学生表中插入一个学生记录 (04027,肖文,男,19,福建)。
INSERT INTO Student VALUES('04027','肖文','男',19,'福建')

?

例3.41 在学习表中插入一个学生选课记 录(04027,05)。
INSERT INTO Study(Sno , Cno) VALUES(‘04027’,‘05’)
数据库原理

2.插入多个元组
INSERT INTO <基本表名> [(<列名1>,< 列名2>,…,<列名n>)] 子查询 ? 这种形式可将子查询的结果集一次性插 入基本表中。如果列名序列省略,则子 查询所得到的数据列必须和要插入数据 的基本表的数据列完全一致。如果列名 序列给出,则子查询结果与列名序列要 一一对应。
数据库原理

?

【例3.42】 如果已建有课程平均分表 Course_avg(Cno,Average),其中average表 示每门课程的平均分,向Course_avg表中插入 每门课程的平均分记录。
INSERT INTO Course_avg(Cno,Average) SELECT Cno , AVG(Grade) FROM Study GROUP BY Cno
数据库原理

3.3.3 删除数据
?

SQL提供了DELETE语句用于删除每一个 表中的一行或多行记录。 DELETE是数据 操纵语句,只是删除表中的每条记录, 不能删除表的定义。

DELETE语句的一般格式: ? DELETE FROM <表名>[WHERE <条件>]
数据库原理

?

例3.43 删除籍贯为湖北的学生基本信息。
DELETE FROM Student WHERE Place LIKE '湖北' 此查询会将籍贯列上值为“湖北”的所有记录全部删除。 在WHERE条件中同样可以使用复杂的子查询。

?

例3.44 删除成绩不及格的学生的基本信息。
DELETE FROM Student WHERE Sno IN (SELECT Sno FROM Study WHERE Grade < 60) 数据库原理

3.3.4 修改数据
?

在SQL中使用UPDATE语句修改满足指定 条件元组的指定列值。满足指定条件的 元组可以是一个元组,也可以是多个元 组。UPDATE语句的一般格式为:
UPDATE <基本表名> SET <列名> = <表达式> [,<列名> = <表 达式>]… [WHERE <条件>]
数据库原理

?

【例3.45】 将数据库原理的学分改为3。
UPDATE Course SET Credit = 3 WHERE Cname LIKE '数据库原理'

? ?

在WHERE条件中同样可以使用复杂的子查询。 【例3.46】 将所有选了数据结构的学生成绩加5分。
UPDATE Study SET Grade = Grade +5 WHERE cno IN (SELECT Cno FROM Course WHERE Cname LIKE ‘数据结构’) 数据库原理

3.4 视图
?

视图是外模式的基本单位,从用户观点来 看,视图和基本表是一样的。实际上视图是 从若干个基本表或视图导出来的表,因此当 基本表的数据发生变化时,相应的视图数据 也会随之改变。视图定义后,可以和基本表 一样被用户查询、更新,但通过视图来更新 基本表中的数据要有一定的限制。

数据库原理

3.4.1 建立视图
SQL语言用CREATE VIEW命令建立视图,其一般 格式为: CREATE VIEW <视图名>[(<列名>[,<列名 >]…)] AS (子查询) [WITH CHECK OPTION] ? WITH CHECK OPTION是可选项,该选项表示 对所建视图进行INSERT、UPDATE和DELETE 操作时,让系统检查该操作的数据是否满足子 查询中WHERE子句里限定的条件,若不满足, 则系统拒绝执行。 数据库原理

?

例3.47 建立一个湖北考籍的学生信息视图。
CREATE VIEW HBStudent AS SELECT Sno , Sname , Sex , Age FROM Student WHERE Place LIKE '湖北'

?

例3.48 建立一个学习了C语言的所有学生信息的视图。
CREATE VIEW C_stud_info(Sno , Sname , Sex , Place) AS SELECT Sno , Sname , Sex , Place FROM Student WHERE Sno IN (SELECT Sno FROM Study WHERE Cno IN (SELECT Cno FROM Course WHERE Cname LIKE ‘C语言’))

数据库原理

3.4.2 删除视图
?

?

在SQL中删除视图使用DROP VIEW语 句,其一般格式为: DROP VIEW <视图名> 例3.49 删除视图HBStudent。
DROP VIEW HBStudent 本例将从数据字典中删除视图HBStudent的定 义。
数据库原理

?

3.4.3 查询视图
?

视图已经建立,用户就可对视图进行查询操作。从用户角 度来说,查询视图与查询基本表是一样的,可是视图是存 在于数据库当中的虚表,所以DBMS执行对视图的查询实 际上是根据视图的定义转换成等价的对基本表的查询。 因此DBMS对某SELECT语句进行处理时,若发现被查询对 象是视图,则DBMS将进行下述操作:
(1)从数据字典中取出视图的定义。 (2)把视图定义的子查询和本SELECT语句定义的查询相结合,生 成等价的对基本表的查询(此过程称为视图的消解)。 (3)执行对基本表的查询,把查询结果(作为本次对视图的查询结 果)向用户显示。

?

数据库原理

【例3.50】 在例3.47建立的视图HBStudent中查 找年龄大于20岁的学生基本信息。
SELECT Sno , Sname , Sex , Age FROM HBStudent WHERE Age > 20

本例在执行时会转化为下列执行语句:
SELECT Sno , Sname , Sex , Age FROM Student WHERE Place LIKE '湖北' AND Age > 20

数据库原理

3.4.4 更新视图
?

?

视图更新是指对视图进行插入(INSERT)、删 除(DELETE)和修改(UPDATE)操作。同查 询视图一样,因为视图是虚表,所以对视图的 更新实际是转换成对基本表的更新。 若加上子句WITH CHECK OPTION,则在对视 图更新时,系统将自动检查原定义的条件是否 满足。若不满足,则拒绝执行。

数据库原理

?

【例3.51】在湖北考籍的学生视图HBStudent中插 入一湖北考生信息,该学生信息为:(04031,戴 敏,女,21)
INSERT INTO HBstudent VALUES(‘04031’,‘戴敏’,‘女’,21)

该语句执行时将转换成对Student表的插入:
INSERT INTO Student VALUES(‘04031’,‘戴敏’,‘女’,21,‘湖北’) 系统会自动将学生所在的考籍“湖北”放入VALUES子句中。
数据库原理

?

为了能正确执行视图更新,各DBMS对视图更新都有若 干规定,由于各系统在实现方法上存在差异,这些规 定也不尽相同。一般的限制有如下几个:

(1)通常对于由一个基本表导出的视图,如果是从基本 表去掉除码外的某些列和行,那么是允许更新的。 (2)一般对于多表连接得到的视图不允许更新。 (3)若视图的列是由库函数或计算列构成,则不能更新。 (4)若视图定义中含有DISTINCT、GROUP BY等子句, 则不允许更新。
数据库原理

3.4.5 视图的优点
1.视图提供一定程度的逻辑独立性
用户程序是通过视图来访问数据库的,视图相当于用 户的外模式,只需要修改用户的视图定义来保证用 户的外模式不变,而用户的程序不必改变。

2.视图有利于数据的保密
对于不同的用户定义不同的视图,而只授予用户访问 自己视图的权限

3.视图简化了用户的操作
视图机制是用户把注意力集中在自己所关心的数据上。 这种视图所表达的数据逻辑结构相比基本表而言, 更易被用户所理解。
数据库原理

3.5 触发器
触发器技术是保证数据完整性的高级技术,触发器还可以用 于对系统的高级监测,确保系统在正常的工作环境中运行。 一个触发器由两部分组成:触发事件及触发条件和动作。
?

(1)触发事件。对数据库进行插入、删除、修改等操作时触 发器被激发。
① AFTER:在触发事件完成之后,测试触发条件,如满足,则执行触 发动作。 ② INSTEAD OF:在触发事件发生时,测试触发条件,如满足,则执行 触发器动作,从而替代触发器事件的操作。

(2)触发条件和动作。条件指定其它准则,以确定DELETE、 INSERT或UPDATE语句是否导致执行触发器动作。
数据库原理

3.5.2 创建触发器
?

触发器由CREATE TRIGGER语句创建,一般格 式为:
CREATE TRIGGER <触发器名> ON <表名> FOR | AFTER | INSTEAD OF {[DELETE , INSERT , UPDATE]} AS [IF UPDATE(<列名>)[{AND | OR} UPDATE(<列 名>)]…] SQL语句[…]
数据库原理

1) 触发器名是要建立的触发器名字;表名是该触发器的操作对象; AFTER和INSTEAD OF是触发事件的两种时间选项,如果仅指定 FOR关键字,则AFTER是默认设置;{[DELETE , INSERT , UPDATE]}是激活触发器的触发事件。 (2)必须至少指定一个选项,其顺序可以任意组合,如果指定的选 项多于一个,则需要用逗号分隔这些选项。 (3)[IF UPDATE(<列名>)[{AND | OR} UPDATE(<列名>)]…] 用于测试在指定的列上进行的INSERT或UPDATE操作,不能用于 DELETE操作。用AND或OR可以组合指定多列。在INSERT操作 中,IF UPDATE将返回TRUE值,因为这些列插入了显式值或隐性 值(NULL)。 (4)SQL语句[…]是触发器的条件和操作。

数据库原理



热文推荐
猜你喜欢
友情链接: 工作计划 总结汇报 团党工作范文 工作范文 表格模版 生活休闲