BUAA-DataBase-Chapter2

第 3 章 SQL 概述

数据定义

支持 SQL 的关系数据库管理系统同样支持关系数据库的三级模式结构。存储结构如图所示：

• 外模式：若干视图、部分基本表
• 模式：若干基本表
• 内模式：存储文件

基本表和视图一致，都属于关系。视图是从一个或多个基本表导出的表，属于虚表，用户可以查看视图，但视图并不实际存放数据。通常一个关系数据库管理系统可以包含多个数据库，一个数据库可以建立多个模式，一个模式可以包含多个表、视图、索引等数据库对象

数据定义语句如下：创建（CREATE）、删除（DROP）、修改（ALTER），对象包含模式（SCHEMA）、视图（VIEW）、表（TABLE）、索引（INDEX）

具体使用时采用 $<操作>+<对象>$ 的语句即可。其中只有表和索引可以执行修改操作

模式的定义与删除

定义模式实际上定义了一个命名空间。在模式中可以进一步定义数据库对象。模式定义语句如下：

CREATE SCHEMA <NAME> AUTHORIZATION <USERNAME>;

• 如果没有指定模式名 NAME 则默认为用户名称 USERNAME

在定义了模式后，可以继续追加定义该模式中的对象：

CREATE SCHEMA <NAME> AUTHORIZATION <USERNAME> [<TABLE> | <VIEW> | <AUTHORIZE>];

• 这里的可选项是完整的定义子句，直接追加在模式定义的同一句中即可

模式在删除时有两类操作，第一类（CASCADE）会连带删除模式下的所有对象；第二类（RESTRICT）只允许删除空的模式，语句如下：

DROP SCHEMA <NAME> <CASCADE | RESTRICT>;

基本表的定义、删除与修改

基本表的定义语句如下：

/* Create Table */
CREATE TABLE <NAME> (<列名><数据类型> [完整性约束条件][, <列名><数据类型> [完整性约束条件]]);
/* Delete Table */
DROP TABLE <NAME> [CASCADE | RESTRICT]

要注意删除表时，RESTRICT 要求表不能存在引用、视图、触发器、存储过程/函数，CASCADE 会连带删除

创建整个表时，需要设置其所属的模式，设置有三种方法：

/* 1. 创建表时显式给出模式名 */
CREATE TABLE Schema1.Student();

/* 2. 创建模式时直接创建表 */
CREATE SCHEMA Schema1 AUTHORIZATION CookedBear
	CREATE TABLE Student();

/* 3. 不显式声明，搜索路径：使用搜索到的第一个模式，如果找不到模式则报错 */

在创建列时定义的完整性约束条件会被存储在数据字典中，当用户操作数据时会自动检查是否满足约束条件；如果一个约束条件需要跨多个属性列，就必须定义在表上，如下例：

CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,   /* Cno = 主码 */
 Cname CHAR(40) NOT NULL,   /* Cname != null*/
 Cpno CHAR(4),
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES Course(Cno)
 /* Cpno 是外码，被参照属性是 Course 的 Cno 列 */
);

基本表中列的数据类型有以下几种常用的数据类型：

数据类型	含义
CHAR(n), CHARACTER(n)	长度为 n 的定长字符串
VARCHAR(n), CHARACTERVARYING(n)	最大长度为 n 的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT, INTEGER	4 bit 长整数
SMALLINT	2 bit 短整数
BIGINT	8 bit 大整数
NUMERIC(p, d)	定点数，总长 p 位，小数点后有 d 位
DECIMAL(p, d), DEC(p, d)	同 NUMERIC
REAL	单精度浮点数
DOUBLE PRECISION	双精度浮点数
FLOAT(n)	可选精度浮点数，精度大于等于 n
BOOLEAN	布尔量
DATE	日期，YYYY-MM-DD
TIME	时间，HH:MM:SS
TIMESTAMP	时间戳
INTERVAL	时间间隔

修改基本表使用如下语句：

ALTER TABLE <NAME>
[ADD [COLUMN] <LineName><DataType> [Restriction]]
[ADD <TableLevelRestriction>]
[DROP [COLUMN] <LineName> [CASCADE | RESTRICT]]
[DROP CONSTRAINT <RestrictionName> [CASCADE | RESTRICT]]
[ALTER COLUMN <LineName><DataType>];

• 在 DROP 操作中，如果删除的列被其他的列所引用且使用了 CASCADE 模式，则连带删除所有引用的对象（如视图）

索引的建立与删除

索引可以加速大数据量的查询速度。数据库索引有多种类型，常见的包括顺序文件索引、B+树索引、散列索引、位图索引等。索引是关系数据库管理系统的内部实现技术，属于内模式的管理范围

• 索引需要占用一定的存储空间，并且需要随数据更新而维护，因此需要有选择地创建索引。

CREATE [UNIQUE] [CLUSTER] INDEX <IndexName> ON <TableName>(<LineName>[<排列次序>][, <LineName>[排列次序]]);
ALTER INDEX <OldName> RENAME TO <NewName>;  /* 索引的重命名 */
DROP INDEX <IndexName>                      /* 删除索引    */

• 索引可以建立在多个列上，每个列使用逗号隔开
• 次序指定索引值的排列次序，默认为 ASC(升序)，也可采用 DESC(降序)
• UNIQUE 表示索引值只唯一对应数据记录
• CLUSTER 表示建立聚簇索引

数据查询

SQL 使用 SELECT 语句进行数据查询，SELECT 指令可以进行单表查询，也可进行复杂的连接查询和嵌套查询，其指令格式为：

SELECT [ALL | DISTINCT] <LineExp> [, <LineExp>]
FROM <Table/View> [, <Table/View>] | (<SELECT语句>) [AS] <别名>
[WHERE <Cond>]
[GROUP BY <LineName1> [HAVING <Cond>]]
[ORDER BY <LineName2> [ASC | DESC]];

单表查询

列查询

• 查询指定列时则可以在第一行中的 LineExp 中指定查询的属性列，选择全部列时可以简写为 *（输出顺序和原表中一致）
• LineExp 不仅可以输入列的名，也可以输入含列的表达式，例如 SELECT Sno+10 FROM Student;，就把 Sno+10 这个表达式作为元素进行输出

条件查询

• 当输出内容中包含有完全相同的两个元组时，可以通过 DISTINCT 对重复行进行合并，使用 ALL 则不合并输出
• 当需要对元组进行内容上的筛选时，使用 WHERE 子句对元组进行筛选，常用的查询条件如下：

条件	谓词
比较	比较运算符
确定范围	[NOT] BETWEEN <data1> AND <data2>
确定集合	[NOT] IN (element1, element2)
字符匹配	[NOT] LIKE <String> ESCAPE <换码字符>
空值	IS [NOT] NULL
逻辑运算	AND, OR, NOT

• 比较：表示某个字段是否满足数学上的关系

• 确定范围：寻找属性值在指定范围内的元组；Cno BETWEEN 1 AND 10

• 确定集合：寻找属性值是 element<i> 的元组；Cname IN (name1, name2)

• 字符匹配：换码字符表示其后的字符不再具有通配符的含义，转义为普通的字符，例如 LIKE 'APP\_00__' ESCAPE '\' 中，换码字符为 \，那么在 00 前的 \_ 就将通配符转化为了一般的下划线字符，而后面的两个通配符没有进行转义，仍表示通配意义

  • 关于字符串匹配符：表示多个字符用 %，单个字符用 _，中文字符在 ASCII 中使用两个下划线， GBK 中使用一个

• 多条件约束：使用 AND 和 OR 连接多个查询条件

聚集函数

• 聚集函数用于查询的返回值字段，用于执行一些整体上的统计功能，包含 DISTINCT 和 ALL 两个短语，默认为后者，前者会取消计数重复值
• 除了第一个 COUNT(*) 外，其余的聚集函数会跳过 NULL 值
• 聚集函数不能应用于 WHERE 子句中，只能使用在 HAVING 子句和 SELECT 子句中

聚集函数名	作用
COUNT(*)	统计返回的元组个数
COUNT([DISTINCT | ALL] <LineName>)	计算指定列有值（不为 NULL）的个数
SUM([DISTINCT |ALL] <LineName>)	计算指定列值的总和
AVG([DISTINCT |ALL] <LineName>)	计算指定列值的平均值
MAX([DISTINCT |ALL] <LineName>)	同理，最大值
MIN([DISTINCT |ALL] <LineName>)	最小值

分组操作

• GROUP BY 语句将查询结果值相等的所有元组看作一个组，在分组操作后，聚集函数会单独应用于每个组内
• 类似的可以对组进行筛选，使用 HAVING 语句指定筛选条件

SELECT Sno             /* 查询 学生学号 */
FROM SC                /* 从 选课记录表 中进行选择 */
GROUP BY Sno           /* 以 学生学号 分组 */
HAVING COUNT(*) > 3;   /* 筛选 元组数量大于 3 的组 */

我们可以注意到，聚集函数的功能都是应用于复数个元组的，所以显然不能在 WHERE 语句中使用，但对于 GROUP BY 语句，它操作的对象是组，也就是复数个元组，此时就可以使用聚集函数进行筛选，获得符合计算要求的组

多表查询

• 当查询操作同时涉及多个表，操作便成为了连接查询，连接查询是关系数据库中最主要的查询操作
• 连接语句中 WHERE 子句用于连接两个表的条件被称为连接条件，通过控制连接条件驱动进行多表查询，连接条件中字段类型必须是可比较的，但是列名不需要完全一致
• 对于两个表以上的连接操作，设置复数个连接条件即可

SELECT A, B, C
FROM T1, T2, T3
WHERE T1.Z=T2=Z AND T2.Y=T3.Y;

等值、非等值连接查询

• 当连接条件中的连接运算符为 = 时，操作被称为等值连接

例如查询每个学生的信息与选课情况：

SELECT Student.* SC.*       /* 展示字段 */
FROM Student, SC            /* 数据来源 */
WHERE Student.Sno = SC.Sno; /* 等值连接 */

执行结果如下：

• 可以在 WHERE 执行连接操作的同时追加其他的限定条件从而对连接后的结果进行筛选

自身连接

一个表也可以和自己连接，查询操作一致，只需要注意命名问题：

SELECT FIRST.Cno, SECOND.Cpno
FROM Course FIRST, Course SECOND /* 注意需要给两个表起名进行区分 */
WHERE FIRST.Cpno = Second.Cno;

外连接

当元组不满足连接条件时，连接操作会默认将这些元组删去，可以用 LEFT | RIGHT OUTER JOIN <Table> ON (Condition) 来执行外连接操作

例如 FROM 语句可以写成：

FROM LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno) 
/* 这里等号左边就是 LEFT 表了 */

嵌套查询

• 嵌套查询：将一个查询块嵌至另一个查询块的 WHERE、HAVING 条件中就形成了嵌套查询；两个操作分别被称为外层查询和内层查询；

• 当子查询的查询条件与父查询不相关时是不相关子查询，有依赖时称为相关子查询

• SQL 允许多层嵌套，但只有最外层操作可以使用 ORDER BY 进行排序

嵌套查询一般分有几类，IN 查询、比较运算符查询、ANY/ALL 谓词查询、EXISTS 谓词查询

当子查询返回了一个元组/值（聚集函数）时，可以直接使用比较运算符进行处理，若返回了多个元组，就要使用 ANY/ALL 进行辅助（或者利用聚集函数转化结果为单值）

ANYALL 谓词需要和比较运算符连用：

EXISTS 查询返回逻辑真值

下面是一些查询的示例：

集合查询

实质上是把多次查询的结果做了运算，包括集合上的交并差三种（分别为 INTERSECT、UNION、EXCEPT）集合运算。

SELECT 1
UNION
SELECT 2

派生表查询

将子查询放在 FROM 子句中，充当父级查询的数据来源，如：

# 查询SELECT Sno, Cno
FROM SC, (
    SELECT Sno, Avg(Grade) 
    FROM SC GROUP BY Sno
) AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno = Avg_sc.Sno AND SC.Grade >= Avg_sc.avg_grade

数据更新

主要分为增删改三种操作，简单说明：

• 插入：INSERT INTO <Table> <Column>, <Column> VALUES <Value>, <Value>
• 更新：UPDATE <Table> SET <Column>=<Value>, <Column>=<Value>
• 删除：DELETE FROM <Table> WHERE <Cond>
  • 这里的 <Cond> 可以用来构造子查询，充当删除项的筛选条件

数据更新操作可能会破坏数据表的参照完整性

空值

空值与另一个值的算数运算结果为空值，比较运算结果为 UNKNOWN。在查询语句中，只有使得 WHERE 和 HAVING 的条件为 TRUE 的元组才能被选中。

• 要注意一个空值和另一个空值做 eq 运算结果可能为 Unknown，需要注意判断条件

视图

视图是从几个基本表中导出的虚表，不存放实际数据。基本表数据若发生变化，视图数据也会变化。允许在视图上再定义视图，但更新视图有一定限制。

视图的操作

• 定义视图：CREATE VIEW <NAME> <Column>, <Column> AS <子查询> [WITH CHECK OPTION]。子查询可以是任意 SELECT 指令，最后的条件语句说明修改视图时会自动加上子查询中的筛选条件，不合法则不执行。

CREATE VIEW V_S
AS
SELECT Sno, Sname
FROM Student
WHERE Sdept = 6
WITH CHECK OPEION;

这里就会把所有 6 系学生的学号+姓名拿出来形成一个新视图，在修改视图时，数据库系统会自动检查修改后是否满足 Sdept = 6 这个限制条件。

• 删除视图：DROP VIEW <NAME> [CASCADE] ，如果被删除的视图导出了其他的视图，CASECADE 关键字会将所有关联视图都删除掉

• 查询视图：操作与查基本表相同，视图会持久保存

• 修改数据：也和基本表类似，但是需要注意并不是所有视图都支持更新。如果视图的某一列使用了类似聚集函数等操作（例如AVG），因为系统无法更改所有与之相关的数据从而修改 AVG，所以这个表就是不可更新的。

以下是不允许更新的视图：

• 需要注意不允许更新视图仍然有可能更新，但是不可更新视图一定不能更新

第 4 章 数据库的安全性与完整性

这一章不是授课重点喵，过一遍 ppt 选点放放

SQL 的数据控制

当数据库管理员建立了一个新用户之后，必须授予它一定的权限，该用户才能使用数据库。在数据库系统中可以授予用户两类权限：

• 用户级权限：用户级权限是数据库管理员为每个用户授予的特定权限。这种权限与整个数据库相关，与数据库中具体的关系无关。这种权限是对用户使用整个数据库的权限的限定。
• 关系级权限：关系级权限是数据库管理员或数据库对象的拥有者为用户授予的与关系或视图有关的权限。这种权限是对用户使用关系和视图的权限的限定。

用户级权限的管理

• 通过 Grant 语句为用户授予用户级权限或角色，其语法格式为：

Grant <用户级权限>|<角色> [{,<用户级权限>|<角色>}]
To <用户名>|<角色>|public [{,<用户名>|<角色>}]
[With Grant Option]

• 通过 Revoke 指令收回权限

Revoke <用户级权限>|<角色> [{,<用户级权限>|<角色>}]
From <用户名>|<角色>|public [{,<用户名>|<角色>}]

• public 指数据库中所有用户，With Grant Option 则说明权限是否可以二次传递，若包含则说明本次被 Grant 的用户也有了使用 Grant 语句的权力
• 仍要注意，当收回权限时，若该用户已将权限授予其它用户，则也一并收回。

完整性规则

• 一条完整性规则可以用一个五元组描述（DOACP）：
• D（Data）约束所作用的数据对象
• O（Operation）触发完整性检查的数据库操作，即当用户发出什么操作请求时需要检查该完整性规则，是立即检查还是延迟检查。
• A（Assertion）数据对象必须满足的断言或语义约束。
• C（Condition）选择A作用的数据对象值的谓词。
• P（Procedure）违反完整性规则时触发的过程。

完整性约束的分类

• 固有约束：在 DBMS 实现时已经考虑，不必特殊说明。
• 隐含约束的说明可以通过 DDL 语句来定义实现，实体完整性、参照完整性
• 显式完整性约束的说明一般有过程说明方式，断言说明方式，触发器说明方式等几种。

---

• 过程说明方式：把约束的说明和检验作为一个过程，由程序员编码到每个更新数据库的事务中。用以检验数据库更新是否违反了给定约束。使用这种方法，为程序员利用通用程序设计语言编制高效率完整性验证程序提供了有利条件。然而，这种方法也为程序员增加了负担。程序员必须清楚他所编码的事务所涉及的所有完整性约束，为每个约束编制一个验证过程。程序员的任何误解、遗漏，疏忽都将导致数据库不正确。同时，一旦完整性约束发生变化，应用程序就必须作相应的修改。
• 断言说明方式：断言指数据库必须满足的逻辑条件，数据库完整性约束可以看成一系列断言的集合。使用这种方法DBMS需要提供断言说明语言，用以定义断言。并提供完整性验证子系统将其编译和存储到约束库，对于每个更新事务，完整性检查子系统利用约束库中的定义进行检查，判断是否违背了完整性约束。利用这种方法可以将约束集中在约束库中，免除了程序员在应用程序中分散定义和维护完整性约束的问题，减少了编程的麻烦，方便了应用程序和约束的维护。但其实现比较复杂，开销也大，降低了数据库更新的性能。例如 ASSERT 余额约束 ON 储蓄表：余额>=0；
• 触发器说明方式：所谓触发器就是一类事件驱动的特殊过程，一旦定义，当发生预定义的事件时，系统自动执行相应的触发器。定义触发器时，需要定义：触发器的触发条件以及触发器应当完成的操作。注意：断言表示数据库状态应当满足的条件，而触发器中的条件却是违反约束的条件，二者正好相反。