数据库复习三 - 数据库 - 计算机科学 | Flüstern = Whispering's Blog = 无需过去，无关未来，只有现在

数据库复习三

# SQL 概述

# 一、SQL 的特点：

综合统一：集 DDL，DML，DCL 于一体
高度非过程化：只需提出 "做什么"，不关心 "怎么做"。
面向集合的操作方式：查询，插入，删除，更新操作对象及结果都是集合
以同一种语法结构提供两种使用方法：可交互式和嵌入式使用
以简洁的自然语言作为操作语言：定义了少量关键字实现对数据库定义、操纵和控制功能

# 二、SQL 语言所使用的动词

SQL 功能	动词
数据查询	SELECT
数据定义	CREATE,DRPO,ALTER
数据操纵	INSERT,UPDATE,DELETE
数据控制	GRANT,REVOKE

# 数据定义

SQL 提供了专门的语言用来定义数据库、表、索引等数据库对象，这些语言被称作数据库定义语言

# SQL 的数据定义语句

操作对象		操作方式
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

创建数据库：CREATE DATABASE 数据库名；

例如：CREATE DATABASE ST;

SQL-DDL 的数据库指定与关闭指令：

指定当前数据库：use 数据库名；

关闭当前数据库：close 数据库名；

# 模式的定义

现代关系数据库管理系统提供了一个层次化的数据库对象命名机制
- 一个关系数据库管理系统的实例中可以建立多个数据库
- 一个数据库中可以建立多个模式
- 一个模式下通常包括多个表、视图和索引数据库对象
服务器名称・数据库名称・模式名称・对象名称
定义模式实际定义了一个命名空间
在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等；
- CREATE SCHEMA <模式名>
- AUTHORIZATION <用户名>
例子：
- 定义一个学生 - 课程模式 S-T
  CREATE SCHEMA "S-T" AUTHORIZATION WANG
  为用户 WANG 定义了一个模式 S-T
在 CREATE SCHEMA 中可以接受 CREATE TABLE,CREATE VIEW 和 GRANT 子句
- CREATE SCHEMA <模式名> AUTHORIZATION < 用户名 > |< 表定义子句 >|< 视图定义子句 >|< 授权定义子句 >|
删除模式
- DROP SHEMA <模式名> <CASCAD | RESTRICT>
  - CASCAD (级联)：删除模式的同时把模式中所有的数据库对象全部删除
  - RESTRICT (限制)：如果该模式中定义了下属的数据库对象 (如表、视图等)，则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行

# 创建基本表

CREATE TABLE <表名> (< 列名 >< 数据类型 >[< 列级完整性约束条件 >] [,< 列名 >< 数据类型 >[< 列级完整性约束条件 >]]...[,< 表级约束条件 >]);

<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性 (列)
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

# 1、常用完整性约束

(1) 主码约束：PRIMARY KEY

(2) 唯一性约束：UNIQUE (不能取相同值但允许多个空值)

(3) 非空值约束：NOT NULL

(4) 参照完整性约束：FOREIGN KEY < 列名 > REFERENCES < 表名 > (< 列名 >)

(5) 用户定义完整性约束：Check (< 约束条件表达式 >)

(6) Default 缺省值约束：Default < 缺省值 >;

# 2、SQL 中的数据类型

SMALLINT 半字长二进制整数
INTEGER 或 INT 全字长二进制整数
DECIMAL (p [,q]) 压缩十进制数
FLOAT 双字长浮点数
CHAPTER (n) 或 CHAR (n) 长度为 n 的字符串
VARCHAR (n) 最大长度为 n 的字符串
GRAPHIC (N) 长度为 n 的定长图形字符串
VARGRAPHIC (n) 最大长度为 n 的图形字符串
DATE 日期型，格式为 yyyy-mm-dd
TIME 时间型，格式为 hh.mm.ss
TIMESTAMP 日期加时间

例子：建立一个学生表 Student，它由学号 sno，姓名 sname，性别 ssex，年龄 sage，所在系 sdept 五个属性构成。其中学号不能为空，值时唯一的，并且姓名取值也唯一。

	CREATE TABLE Student
	(sno CHAR(5) NOT NULL,UNIQUE,
	sname CHAR(20) UNIQUE,
	ssex CHAR(1),
	sage int,
	sdept CHAR(5),
	PRIMARY KEY sno);

模式与表：
- 每一个基本表都属于一个模式
- 一个模式包含多个基本表
- 定义基本表所属模式
  - 方法一：在表名中明显给出模式名 Create table Test.Student (...);
  - 方法二：在创建模式语句中同时创建表
  - 方法三：设置所属的模式

# 3、删除基本表

DROP TABLE <表名>

# 4、修改基本表

ALTER TABLE <表名> [ADD < 新列名 >< 数据类型 >[完整性约束]] [DROP < 完整性约束 >< 列名 >] [MODIFY < 列名 >< 数据类型 >]

<表名>：要修改的基本表
ADD 子句：增加新列和新的完整性约束条件
DROP 子句：删除指定的完整性约束条件
MODIFY 子句：用于修改列名和数据类型

# 索引

单一索引：每一个索引值只对应唯一的数据记录。当建立单一索引后，索引项不可以再插入已有值，但可以多个空值。同样，当建立单一索引时，如果待索引项存在相同值则不能建立

聚簇索引：索引项顺序与表中数据记录的物理顺序一致

索引是定义在存储表基础之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构，由一系列存储在磁盘上的索引项组成，每一索引项又由两部分构成：

索引字段：由 Table 中某列 (通常是一列) 中的值串接而成。索引中通常存储了索引字段的每一个值。索引字段类似于词典中的词条
行指针：指向 Table 中包含索引字段值的记录在磁盘上的存储位置。行指针类似于词条在书籍、词典中出现的页码

存储索引项的文件为索引文件，相对应，存储表又称为主文件

利用索引提高了查询效率是以空间和增加了数据更改的时间为代价的
在设计和创建索引时应确保对性能的提高程度大于在存储空间和处理资源方面的代价。

索引是关系数据库的内部实现技术，属于内模式的范畴

CREATE INDEX 语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引

# 1、创建索引

CREATE [UNIQUE] [CLUSTER] INDEX < 索引名 > on < 表名 >(< 列名 >[< 次序 >] [,< 列名 >[< 次序名 >]]...);

用 <表名> 指定要索引的基本表名字
索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
用 <次序> 指定索引值的排列次序，升序：ASC，降序：DESC，缺省值：ASC
UNIQUE 表明此索引的每一个索引值都对应唯一的数值记录

# 2、删除索引

DROP INDEX <索引名>

删除索引时，系统会从数据字典中删去有关该索引的描述

单一索引 (唯一值索引)

对于已含重复值的属性列不能建 UNIQUE 索引
对某个列建立 UNIQUE 索引后，插入新纪录时 DBMS 会自动检查新记录在该列上是否取了重复值。这相当于增加了一个 UNIQUE 约束

聚簇索引 (聚集索引)

建立聚集索引后，聚簇索引的索引项顺序与表中记录的物理顺序一致
在一个基本表上最多只能建立一个聚簇索引
聚簇索引的用途：对于某些类型的查询，可以提高查询效率
聚簇索引的适用范围
- 很少对表进行增删操作
- 很少对其中的变长列进行修改操作
- 建立索引是加快查询速度的有效手段
- 建立索引
  - DBA 或表的属主 (即建立表的人) 根据需要建立
  - 有 DBMS 自动建立以下列上的索引
    - PRIMARY KEY
    - UNIQUE
- 维护索引
  - DBMS 自动完成
- 使用索引
  - DBMS 自动选择是否使用索引以及使用哪些索引

# 查询

查询语法：

SELECT [ALL|DISTINCT] < 目标列表达式 > [,< 目标列表达式 >]...

FROM <表名或视图名>[,< 表名或视图名 >]...

[WHERE < 条件表达式>]

[GROUP BY < 列名 1> [HAVING < 条件表达式 >]]

[ORDER BY < 列名> [ASC|DESC]];

SELECT 子句：指定要显示的属性列
FROM 子句：指定查询对象 (基本表或视图)
WHERE 子句：指定查询条件
GROUP BY 子句：对查询结果按指定列的值分组，该属性列值相等的元组为一组。通常会在每组中作用集函数
HAVING 短语：筛选出只有满足指定条件的组
ORDER BY 子句：对查询结构按指定列值的升序或降序排序

# 一、单表查询 —— 只涉及到一个表的查询

# (一) 选择表中的若干列 ( $\pi$ )

# 1) 目标列 (SELECT 子句) 为表达式的查询

	SELECT Sno,Sname
	FROM Student;

# 2) 使用列别名改变查询结果的列标题

	SELECT Sname NAME,'Year of Birth' BIRTH,2019-Sage BIRTHDAY,ISLOWER(Sdept) DEPARTMENT
	FROM Student;

# 3) 消除结果中取值重复的行 —— 在 SELECT 子句中使用 DISTINCT 短语

查询选修了课程的学生学号：

	SELECT DISTINCT Sno
	FROM SC;

注意：DISTINCT 短语的作用范围是所有目标列

# (二) 选择表中的若干元组 ( $\sigma$ )—— 使用 WHERE 子句

# 1) 使用比较运算符确定元组

查询计算机系全体学生的信息

	SELECT *
	FROM Student
	WHERE Sdept='CS';

注意：不等于在 SQL 中不能写成 "≠"，应该为 "<>"

# 2) 确定范围

查询年龄在 20~23 岁 (包括 20 岁和 23 岁) 之间的学生的姓名、系别和年龄。

	SELECT Sname,Sdept,Sage
	FROM Student
	WHERE Sage BETWEEN 20 AND 23;

# 3) 确定集合

查询信息系 (IS)、数学系 (MA) 和计算机系 (CS) 学生的姓名和性别

	SELECT Sname,Ssex
	FROM Student
	WHERE Sdept IN ('IS','MA','CS');

# 4) 字符串匹配

[NOT] LIKE '< 匹配串 >' [ESCAPE '< 换码字符 >']
<匹配串>：指定模板
匹配模板：固定字符或含通配符的字符串。当匹配模板为固定字符串时，可以用 = 运算符取代 LIKE 谓词，用！= 或 <> 运算符取代 NOT LIKE 谓词
%：代表任意长度 (长度可以为 0) 的字符串
_：代表任意单个字符
当用户要查询的字符串本身就含有 % 或_时，要使用 ESCAPE '<换码字符>' 短语对通配符进行转义

查询所有姓刘学生的姓名、学号和性别

	SELECT Sname,Sno,Ssex
	FROM Student
	WHERE Sname LIKE '刘%';

查询姓 "欧阳" 且全名为三个汉字的学生的姓名

	SELECT Sname
	FROM Student
	WHERE Sname LIKE '欧阳__';

查询姓名中第二字为 "阳" 字的学生的姓名和学号。

	SELECT Sname,Sno
	FROM Student
	WHERE Sname LIKE '_阳%'

查询 DB_Design 课程的课程号和学分。

	SELECT cno,credit
	FROM Course
	WHERE Cname LIKE 'DB\_Design' ESCAPE '\'

# 5) 涉及空值的查询

使用谓词 IS NULL 或 IS NOT NULL，其中 "IS NULL" 不能用 "=NULL" 代替

现行 DBMS 的空值处理小结：
- 属性定义有 NOT NULL 约束条件的不能取空值，加了 UNIQUE 限制的属性不能取空值，码属性不能取空值
- 除 IS [NOT] NULL 之外，空值不满足任何查找条件
- 如果 NULL 参与算术运算，则该算术表达式的值为 NULL
- 如果 NULL 参与比较运算，则结果可视为 UNKNOWN
- 如果 NULL 参与聚集运算，则除 count (*) 之外其它聚集函数都忽略 NULL

# 6) 多重条件查询

用逻辑运算符 AND 和 OR 来联结多个查询条件

AND 的优先级高于 OR
可以用括号改变优先级

SQL 代码优化：如果建立了 WHERE 子句中属性列的索引，[NOT] BETWEEN ... AND ... 和 [NOT] IN 将不会提高索引提高查询效率，应该为多重条件查询

# (三) 对查询结果排序输出

使用 ORDER BY 子句
可以按一个或多个属性列排序
- 升序：ASC
- 降序：DESC
- 缺省值为升序
当排序列含空值时
- ASC：排序列为空值的元组最后显示
- DESC：排序列为空值的元组最先显示 (将空值作为最大值来理解)

# (四) 使用集函数

5 类主要集函数：

计数
COUNT( [DISTINCT|ALL] *)
COUNT ([DISTINCT|ALL] < 列名 >)
计算总和
SUM ([DISTINCT|ALL]< 列名 >)
计算平均值
AVG ([DISTINCT|ALL]< 列名 >)
求最大值
MAX ([DISTINCT|ALL]< 列名 >)
求最小值
MIN ([DISTINCT|ALL]< 列名 >)

# (五) 对查询结果分组输出 —— 使用 GROUP BY 子句

作用：细化集函数的作用对象

未对查询结果分组，集函数将作用于整个查询结果
对查询结果分组后，集函数将分别作用于每个组
GROUP BY 子句的作用对象是查询的中间结果表
分组方法：按指定的一列或多列值分组，值相等为一组
使用 GROUP BY 子句了，SELECT 子句的列名列表中智能出现分组属性和集函数

求不及格课程超过两门的同学的学号：

	SELECT S
	FROM SC
	WHERE Score<60
	GROUP BY HAVING COUNT(*)>2;

查询选修了 3 门以上课成的学生学号：

	SELECT sno
	FROM SC
	GROUP BY sno HAVING COUNT(*)>3;

HAVING 短语与 WHERE 子句的区别
- 作用对象不同
- WHERE 子句作用与基表或视图，从中选择满足条件的元组
- HAVING 短语作用于组，从中选择满足条件的组

# 二、连接查询 —— 将两个以上表连接进行查询

同时涉及多个表的查询
连接查询的意义等价于关系代数中的 $\theta$ 连接、等值连接和自然连接

# (一) 等值与非等值连接查询

查询每个学生及其选修课程的情况

	SELECT Student.,SC.
	FROM Student,SC
	WHERE Student.Sno=SC.Sno

# (二) 自身连接 —— 一个表与其自己进行连接

查询每一门课的先修课的先修课

	SELECT FIRST.Cno,SECOND.Cpno
	FROM Course FIRST,Course SECOND
	WHERE FIRST.Cpno=SECOND.Cno;

# (三) 外连接

外连接和普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
外连接的类型
- 左外连接 —— 在连接条件的右边出现空行
- 右外连接 —— 在连接条件的左边出现空行
- 左右外连接 —— 在连接条件的左右两边出现空行

求所有教师的任课情况 (没有任课的教师也需列在表中)

	SELECT Teacher.T,Tname,Cname
	FROM Teacher left outer join Course on Teacher.T =Course.T
	Order by Teacher.T ASC;

# (四) 复合条件连接 ——WHERE 子句中含多个连接条件

查询选修 2 号课程且成绩在 90 分以上的所有学生的学号、姓名：

	SELECT Student.Sno,Student.Sname
	FROM Student,SC
	WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.GRADE>90;

# 三、嵌套查询

一个 SELECT-FROM-WHERE 语句称为一个查询块

将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中查询称为嵌套查询

子查询的限制 —— 不能使用 ORDER BY 子句
层层嵌套方式反映了 SQL 语言的结构化
有些嵌套查询可以用连接运算代替

# (一) 带有 IN 谓词的子查询

查询与 "刘晨" 在同一个系学习的学生

	SELECT Sno,Sname,Sdept
	FROM Student
	WHERE Sdept in
	(SELECT Sdept
	FROM Student
	WHERE Sname='刘晨');

这种查询称为不相关子查询，即子查询的执行不依赖于父查询的条件

# (二) 带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符。

注意：子查询一定要跟在比较符之后

# (三) 带有 ANY 或 ALL 谓词的子查询

谓语语义：(1) ANY (SOME): 某些值 (2) ALL: 所有值

例：查询其他系中比信息系某些学生年龄小的学生姓名和年龄

	SELECT Sname,Sage
	FROM Student
	WHERE Sage< ANY(SELECT Sage
	FROM Student
	WHERE Sdept='IS')
	AND Sdept<>'IS';

找出所有课程都不及格的学生姓名

	SELECT Sname
	FROM Student
	WHERE 60>all(Select Score
	FROM SC
	WHERE Sno=Student.Sno);

# (四) 带有 EXISTS 谓词的子查询

EXISTS 谓词的意义：是存在量词 $\exists$ 在 SQL 的应用，带有 EXISTS 谓词的子查询不返回任何数据，只产生 true 或 false

若内层查询结果非空，返回真值
若内存查询结果为空，返回假值

由 EXISTS 引出的子查询，其目标列表达式通常都用 *，因为带 EXISTS 的子查询只返回真值或假值，给出列名无实际意义

例：查询所有选修了 1 号课程的学生姓名：

	SELECT Sname
	FROM Student
	WHERE EXISTS
	(SELECT *
	FROM SC
	WHERE cno='1' and Student.Sno=Sno);

用 EXISTS/NOT EXISTS 实现全称量词

SQL 语言中没有全称量词 $\forall$
可以把带有全称量词的谓词转化为等价的带有存在量词的谓词： $\forall x(P(x)\rightarrow Q)\equiv \lnot\exists x(P(x)\rightarrow\lnot Q)$

例：查询选修了全部课程的学生姓名

	SELECT Sname
	FROM Student
	WHERE NOT EXISTS
	(SELECT *
	FROM C
	WHERE NOT EXISTS
	(SELECT *
	FROM SC
	WHERE sno=Student.sno and cno=C.cno));

查询至少选修了学生 95002 选修的全部课程的学生学号

	SELECT Sno
	FROM SC SC1
	WHERE NOT EXISTS
	(SELECT *
	FROM SC SC2
	WHERE SC2.Sno='95002' and NOT EXISTS
	(SELECT *
	FROM SC SC3
	WHERE SC3.cno=SC2.cno and SC3.sno=SC1.sno));

# 四、集合查询

将两个 SELECT-FROM-WHERE 查询块用集合操作语句联合起来

集合操作命令：

并操作 (UNION)
交操作 (INTERSECT)
差操作 (EXCEPT)

语句形式：

<查询块> UNION < 查询块 >

查询计算机科学系的学生及年龄不大于 19 岁的学生

	SELECT *
	FROM Student
	WHERE Sdept='CS' AND Sage<='19';

或

	SELECT DISTINCT *
	FROM Student
	WHERE Sdept='CS'
	UNION
	SELECT *
	FROM Student
	WHERE Sage<='19';

UNION: 将多个查询结果合并起来时，系统自动去掉重复元组
UNION-ALL: 将多个查询结果合并起来时，保留重复元组

例题：

假设一个元组在子查询 1 中出现 m 次，在子查询 2 中出现 n 次，其中 m>0,n>0, 则下列说法正确是__。

A. 该元组在 "子查询 1 Except 子查询 2" 中出现 0 次。

B. 该元组在 "子查询 1 Union 子查询 2" 中出现 m+n 次

C. 该元组在 "子查询 1 Except 子查询 2" 中出现 m-n 次

D. 该元组在 "子查询 1 Union ALL 子查询 2" 中出现 max (m,n) 次

A 是正确答案，Except 是差操作，只要有一样的就全部去掉，Union 是并操作，普通并操作结果为 max (m,n) 次，Union ALL 操作会保留所有，因此会出现 m+n 次

# 注意事项

# 一、ORDER BY 子句在复合查询中的应用

ORDER BY 子句对于查询结果进行排序后再输出，故只用与最外层的查询，则子查询中不应该出现 ORDER BY 子句

# 二、GROUP BY 子句的使用

(1) GROUP BY 子句用来对查询结果进行分组，通常用作对各组的统计，可用于子查询

(2) 使用了分组的查询语句中，其 SELECT 子句中只能出现分组属性和集函数，而不能有 GROUP BY 没有出现的属性

# 三、别名的使用

(1) 别名用于对输出属性列的重命名

(2) 别名用于自身连接查询与对同一表的相关子查询中，用于区别对同一表的不同引用

(3) 对不相关子查询可以不使用别名

# 四、DISTINCT 使用

DISTINCT 区分相同记录，将多条相同记录作为一条处理

例：查询成绩不及格的学生人数

	SELECT COUNT(DISTINCT Sno)
	FROM SC
	WHERE Grade<60;

注意：如果不用 DISTINCT 加以限定，则同一个人有多门课不及格按多次计算，不符合

# 五、集函数的使用

集函数智能用于 SELECT 子句和 HAVING 短语之中，而绝对不能出现在 WHERE 子句中

例：查询年龄最大的学生：

	SELECT *
	FROM Student
	WHERE Sage=(SELECT MAX(Sage)
	FROM Student);

注意：集函数没有复合功能，不能出现 MAX (AVG (Grade))。

# 六、输出多个表的属性的查询

查询的输出只能取自最外层查询所使用的表，对于子查询中的属性是不能作为最终的输出的。如果输出的属性设计多个表，则最外层查询智能使用连接查询。

# 七、基于派生表作为数据源的查询

子查询结果集可以看做基本关系一样作为查询的数据源，需要对结果命名别名以便引用

例：查询全体学生的学号、姓名和平均成绩

	SELECT Sno,Sname,avg_grade
	FROM Student,(SELECT Sno,AVG(Grade) AS avg_grade
	FROM SC
	GROUP BY sno) AS SC_AVG
	WHERE Student.sno=SC_AVG.sno;

# 数据更新

# 一、插入数据

(1) 插入单个元组 —— 新元组插入指定表中

语句格式：

INSERT INTO <表名> [(< 属性 1>[,< 属性列 2>]...)] VALUES (< 常量 1>[,< 常量 2>]...);

注意：

INTO 子句：

指定要插入数据的表名及属性列
属性列的顺序可与表定义中的顺序不一致
没有指定的属性列：表示要插入的是一条完整的元组，且属性列属性与表定义的顺序一致
指定部分属性列：插入元组在其余属性上取空值

VALUES 子句：

提供的值的个数和值的类型必须与 INTO 子句匹配

(2) 插入子查询结果

语句格式：

INSERT INTO <表名> [(< 属性列 1>[,< 属性列 2>...])] 子查询；

子查询：SELECT 子句目标列属性个数和类型必须与 INTO 子句匹配

例：对每一个系，求学生平均年龄，把结果存入数据库

	CREATE TABLE Deptage(Sdept CHAR(15),Avgage SMALLINT); #建表
	INSERT INTO Deptage(Sdept,Avgage)
	SELECT Sdept,AVG(Sage)
	FROM Student
	GROUP BY Sdept;

DBMS 在执行插入语句时，会检查欲插入的元组是否会破坏表上已定义的完整性规则。(实体完整性，参照完整性，用户定义完整性)

# 二、修改数据

语句格式：

UPDATE <表名>

SET <列名>=< 表达式 >[,< 列名 >=< 表达式 >]...

[WHERE < 条件>];

功能：修改指定表中满足 WHERE 子句条件的元组

注意：SET 子句 —— 指定修改方式，要修改的列和修改的值

WHERE 子句 —— 指定要修改的元组，缺省表示要修改表中所有的元组

# 三、删除数据

语句格式：

DELETE

FROM <表名>

[WHERE < 条件>];

功能：删除指定表中满足 WHERE 子句条件的元组

注意：WHERE 子句 —— 指定要删除的元组，缺省表示要删除表中所有元组

# 视图

什么是视图

视图是从一个或几个基本表 (或视图) 导出的表，它与基本表不同，是一个虚表
在数据字典中只存放视图的定义
基表中的数据发生变化，从视图中查询出的数据也随之改变
视图一经定义，就可以和基本表一样被查询和删除
视图对应三级模式体系结构中的外模式

# 一、视图定义

# (1) 建立视图 —— 语句格式：

CREATE VIEW <视图名> [(< 列名 >[,< 列名 >,...])] AS < 子查询 > [WITH CHECK OPTION];

注意：但在下列情况下明确指定视图的所有列名

某个目标列是集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字

子查询中的属性列不允许定义别名，不允许含有 ORDER BY 子句和 DISTINCT 短语

WITH CHECK OPTION 表示对视图进行更新操作数据需满足视图定义的谓词条件 (子查询的条件表达式)。

例：建立信息系学生视图，并要求透过该视图进行的更新操作只涉及信息系学生。

	CREATE VIEW IS_Student
	AS
	SELECT Sno,Sname,Sage
	FROM Student
	WHERE Sdept='IS'
	WITH CHECK OPTION;

针对此视图，当进行一下操作时，

修改操作、删除操作：DBMS 自动嘉盛 Sdept='IS' 条件
插入操作：DBMS 自动检查 Sdept 属性值是否为 'IS'
- 如果不是，则拒绝该插入操作
- 如果没有提供 Sdept 属性值，则自动定义 Sdept 为 'IS'

当子查询中有由表达式构成的派生属性列 (也称为虚拟列) 时，必须明确定义组成视图的各个属性列名

例：将 Student 表中所有女生记录定义为一个视图

	CREATE VIEW F_Student(stdnum,name,sex,age,dept)
	AS
	SELECT *
	FROM Student
	WHERE Ssex='女';

存在问题：修改基表 Student 的结构后 (在非末尾增加一列),Student 表与 F_Student 视图的映象关系会被破坏，导致该视图不能正确工作

处理方法：在子查询 SELECT 子句中明确指出各属性列的名称，可以避免对基本表的属性列增加而破坏与视图间的映象关系。

# (2) 删除视图

语句格式：

DROP VIEW <视图名>;

注意：该语句从数据字典中删除指定的视图定义，由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显示删除，删除基表时，由该基表导出的所有视图定义都是必须显式删除

例：删除视图 IS_S1

DROP VIEW IS_S1

由于在有视图 IS_S1 之上建立的视图 IS_S2, 在 IS_S1 被删除后 IS_S2 已经无法使用，这时应该使用下列语句删除 IS_S2；DROP VIEW IS_S2;

# 二、查询视图

例：在信息系学生的视图中找出年龄小于 20 岁的学生

信息系学生的视图定义 (视图定义例 1)：

	CREATE VIEW IS_Student
	AS
	SELECT Sno,Sname,Sdept
	FROM Student
	WHERE Sdept='IS';

查询语句：

	SELECT Sno,Sage
	FROM IS_Student
	WHERE Sage<20;

根据视图定义将对视图的查询转换为对基本表的查询。

视图消解：从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询，这一转换过程称为视图消解

注意：含有集函数的视图不能正确地转换为对基本表的查询，应当直接对基本表进行

# 三、更新视图

(1) 用户角度：更新视图与更新基本表相同

(2) DBMS 实现视图更新方法：

视图实体化法
视图消解法

(3) 指定 WITH CHECK OPTION 子句后，DBMS 在更新视图时会先进行检查，防止用户对不属于视图范围内的基本表数据进行更新。

# 四、视图的作用

(1) 视图能够简化用户的操作

(2) 视图对重构数据库提供了一定程度的逻辑独立性

(3) 视图能够对机密数据提供安全保护

# 嵌入式 SQL

什么是嵌入式 SQL：SQL 语句嵌入到其他高级语言中，这时高级语言被称为 (宿) 主语言
为什么要引入嵌入式 SQL：
- SQL 语言是非过程性语言
- 事务处理应用需要高级语言

# 一、如何区分主语言与 SQL 语句

前缀：EXEC SQL
结束标志：随主语言的不同而不同

如：C 语言中使用 SQL:EXEC SQL <SQL 语句>;

# 二、SQL 与主语言如何交互

1、SQL 通信区

向主语言传递 SQL 语句的执行状态信息
主语言能够据此控制程序流程

2、主变量

主语言向 SQL 语句提供参数
将 SQL 语句查询数据库的结果交主语进一步处理

3、游标

解决集合性操作语言与过程性操作语言的不匹配

含嵌入式 SQL 的执行流程:

主语言进行变量声明和界面处理，并读取用户数据
SQL 语句用主变量从主语言中接收执行参数，操纵数据库
SQL 语句的执行状态由 DBMS 送至 SQLCA 中
主语言程序从 SQLCA 中取出状态信息，据此决定下一步操作
如果 SQL 语句从数据库中成功地检索出数据，则通过主变量传给主语言做进一步处理，否则退出主程序
如果 SQL 语句返回的是结果集，则主语言通过游标逐一处理各记录

# 三、不用游标的 SQL 语句

1、说明性语句

说明性语句是专为嵌入式 SQL 中说明主变量，SQLCA 等而设置的

说明主变量
(1)EXEC SQL BEGIN DECLARE SECTION;
(2)EXEC SQL END DECLARE SECTION;
这两条语句必须配对出现，相当于一个括号，两条语句中间是主变量的说明
说明 SQLCA
(3)EXEC SQL INCLUDE SQLCA;

2、数据定义语句

例：建立一个 "学生" 表 Student

EXEC SQL CREATE TABLE Student
    (sno CHAR(5) NOT NULL UNIQUE,
    Sname CHAR(20),
    Ssex CHAR(1),
    Sage INT,
    Sdept CHAR(15));

注意：数据定义语句中不允许使用主变量

3、查询结果为单记录的 SELECT 子句

格式：EXEC SQL SELECT [ALL|DISTINCT] < 目标列表达式 >[,< 目标列表达式 >],...

INTO <主变量>[< 指示变量 >] [,< 主变量 >|< 指示变量 >]...

FROM <表名或视图名>[,< 表名或视图名 >]...

[WHERE < 条件表达式>]

[GROUP BY < 列名 1> [HAVING < 条件表达式 >]]

[ORDER BY < 列名 2> [ASC|DESC]];

说明：把从数据库中找到的符合条件的记录，放到 INTO 子句指出的主变量中去

使用注意事项：

1、主变量的使用范围

INTO 子句
WHERE 子句的条件表达式
HAVING 短语的条件形式

2、使用指示变量

指示变量只能用于 INTO 子句中
如果 INTO 子句中主变量后面跟有指示变量，则当查询得出的某个数据项为空值时，系统会自动将指示变量置为负值，但不向该主变量执行赋值操作，即主变量仍保持仍保持执行 SQL 语句之前的值
当发现指示变量值为负值时，不管主变量为何值，均应认为主变量值为 NULL

3、查询结果为空集

如果数据库中没有满足条件的记录，即查询结果为空，则 DBMS 将 SQLCODE 的值置为 100

4、查询结果为多条记录

程序出错：DBMS 会在 SQLCA 中返回错误信息

例：查询某个学生选修某门课程的成绩

假设已将要查询的学生的学号赋给了主变量 givensno, 将课程号赋给了主变量 givencno。

	EXEC SQL SELECT Sno,Cno,Grade
	INTO :Hsno,:Hcno,:Hgrade:Gradeid
	FROM SC
	WHERE Sno=:givesno AND Cno=:givencno;

当该学生成绩为空时，SQL 语句执行后会给指示变量 Gradeid 置为负值，而主变量 Hgrade 仍保持原来的值 (不正确), 这时主程序应当根据指示变量的值判定主变量为 NULL。

从提高应用程序的数据独立性角度考虑，SELECT 语句在任何情况下都应该使用游标。对于仅返回一行结果数据的 SELECT 语句虽然可以不使用游标，但如果以后数据库改变，该 SELECT 语句可能会返回多行数据，这时该语句就会出错。

5、非 CURRENT 形式的 UPDATE 语句

例：将全体学生 1 号课程的考试成绩增加若干分

假设增加的分数已赋给主变量 Raise

	EXEC SQL UPDATE SC
	SET Grade=Grade+:Raise
	WHERE Cno='1';

6、非 CURRENT 形式的 DELETE 语句

例：某个学生退学了，现要将有关他的所有选课记录删除掉

假设该学生的姓名已赋给主变量 stdname

	EXEC SQL DELETE
	FROM SC
	WHERE Sno=
	(SELECT Sno
	FROM Student
	WHERE Sname=:stdname);

7、非 CURRENT 形式的 INSERT 语句

例：某个学生新选修了某门 INSERT 语句，将有关记录插入 SC 表

假设学生的学号已赋给主变量 stdno，课程号已赋给主变量 couno。

	gradeid=1;
	EXEC SQL INSERT
	INTO SC(Sno,Cno,Grade)
	VALUES(:stdno,:couno,:gr:gradeid);

由于该学生刚选修课程，尚未考试，因此成绩为空。所以本例中用指示变量指示相应的主变量为空值。

# 四、使用游标的 SQL 语句

1、查询结果为多条记录的 SELECT 语句

使用游标的步骤
(1) 说明游标
EXEC SQL DECLARE <游标名> CURSOR
FOR <SELECT 语句>;
(2) 打开游标
EXEC SQL OPEN <游标名>;
(3) 移动游标指针，然后取当前记录
EXEC SQL FETCH [[NEXT|PRIOR|FIRST|LAST] FROM] < 游标名 > INTO < 主变量 >[< 指示变量 >] [,< 主变量 >[< 指示变量 >]]...;
(4) 关闭游标
EXEC SQL CLOSE <游标名>;

2、CURRENT 形式的 UPDATE 语句和 DELETE 语句

使用游标步骤：
(1) 说明游标
(2) 打开游标，把所有满足查询条件的记录从指定表取至缓冲区
(3) 推进游标指针，并把当前记录从缓冲区取出来送至主变量
(4) 检查该记录是否是要修改或删除的记录，是则处理之
(5) 重复第 (3) 和第 (4) 步，用逐条取出结果集中的行进行判断和处理
(6) 关闭游标，释放结果集占用的缓冲区和其他资源

例：对某个系的学生信息，根据用户的要求修改其中某些人的年龄字段

	EXEC SQL INCLUDE SQLCA;
	EXEC SQL BEGIN DECLARE SECTION;
	/* 说明主变量 deptname,Hsno,HSname,HSsex,HSage,NEWAge*/
	EXEC SQL END DECLARE SECTION;
	...
	gets(deptname); /* 为主变量 deptname 赋值 */
	EXEC SQL DECLARE SX CURSOR FOR
	SELECT Sno,Sname,Ssex,Sage
	FROM Student
	WHERE SDept=:deptname
	FOR UPDATE OF Sage; /* 说明游标 */
	EXEC SQL OPEN SX /* 打开游标 */
	WHILE(1) /* 用循环结构逐条处理结果集中的记录 */
	{
	EXEC SQL FETCH SX INTO :HSno,:HSname,:HSsex,:HSage;
	/* 将游标指针向前推进一步，然后从结果集中取当前行，送相应主变量 */
	if(sqlca.sqlcode <> SUCCESS)
	break;
	/* 若所有查询记录均已处理完成，出现 SQL 错误语句，则退出循环 */
	printf("%s,%s,%s,%d",HSno,HSname,HSsex,HSage);/* 显示记录 */
	scanf("%c",&yn);
	if(yn=='y' \|\| yn=='Y')
	{
	printf("INPUT NEW AGE:");
	scanf("%d",&NEWAge); /* 输入新的年龄值 */
	EXEC SQL UPDATE Student
	SET Sage=:NEWAge
	WHERE CURRENT OF SX; /* 修改当前记录的年龄字段 */
	}
	}
	EXEC SQL CLOSE SX; /* 关闭游标 */

# 动态 SQL

静态 SQL 特点：SQL 语句在程序中已经按要求写好，只需要把一些参数通过变量 (程序中不带冒号，而嵌入式 SQL 语句中带冒号的变量) 传送给 SQL 语句即可

动态 SQL 特点：SQL 语句可以按程序中动态构造，就像构造字符串

嵌入式 SQL 的程序流程：
- 主语言头文件、变量声明
- SQLCA 声明
- 主变量声明
- 主语言对主变量赋值
  - 不含游标的 SQL 语句
  - 含游标的 SQL 语句
    - 定义游标
    - 打开游标
    - 推进游标指针，读取游标中的当前记录
    - 读取成功判定
      - 是，则处理当前记录，再跳到推进游标指针的那一步
      - 否，则关闭游标

数据库

# SQL 概述

# 一、SQL 的特点：

# 二、SQL 语言所使用的动词

# 数据定义

# SQL 的数据定义语句

# 模式的定义

# 创建基本表

# 1、常用完整性约束

# 2、SQL 中的数据类型

# 3、删除基本表

# 4、修改基本表

# 索引

# 1、创建索引

# 2、删除索引

# 查询

# 一、单表查询 —— 只涉及到一个表的查询

# (一) 选择表中的若干列 (π\piπ)

# 1) 目标列 (SELECT 子句) 为表达式的查询

# 2) 使用列别名改变查询结果的列标题

# 3) 消除结果中取值重复的行 —— 在 SELECT 子句中使用 DISTINCT 短语

# (二) 选择表中的若干元组 (σ\sigmaσ)—— 使用 WHERE 子句

# 1) 使用比较运算符确定元组

# 2) 确定范围

# 3) 确定集合

# 4) 字符串匹配

# 5) 涉及空值的查询

# 6) 多重条件查询

# (三) 对查询结果排序输出

# (四) 使用集函数

# (五) 对查询结果分组输出 —— 使用 GROUP BY 子句

# 二、连接查询 —— 将两个以上表连接进行查询

# (一) 等值与非等值连接查询

# (二) 自身连接 —— 一个表与其自己进行连接

# (三) 外连接

# (四) 复合条件连接 ——WHERE 子句中含多个连接条件

# 三、嵌套查询

# (一) 带有 IN 谓词的子查询

# (二) 带有比较运算符的子查询

# (三) 带有 ANY 或 ALL 谓词的子查询

# (四) 带有 EXISTS 谓词的子查询

# 四、集合查询

# 注意事项

# 一、ORDER BY 子句在复合查询中的应用

# 二、GROUP BY 子句的使用

# 三、别名的使用

# 四、DISTINCT 使用

# 五、集函数的使用

# 六、输出多个表的属性的查询

# 七、基于派生表作为数据源的查询

# 数据更新

# 一、插入数据

# 二、修改数据

# 三、删除数据

# 视图

# 一、视图定义

# (1) 建立视图 —— 语句格式：

# (2) 删除视图

# 二、查询视图

# 三、更新视图

# 四、视图的作用

# 嵌入式 SQL

# 一、如何区分主语言与 SQL 语句

# 二、SQL 与主语言如何交互

# 三、不用游标的 SQL 语句

# 四、使用游标的 SQL 语句

# 动态 SQL

数据库复习二

数据库复习四

# (一) 选择表中的若干列 ( $\pi$ )

# (二) 选择表中的若干元组 ( $\sigma$ )—— 使用 WHERE 子句