数据库基础Database2-中级SQL

作者: sunblog | 来源:发表于2018-04-16 23:42 被阅读0次

数据库基础Database2

三中级SQL

中级SQL主要包含形式更复杂的SQL查询、视图定义、事务、完整性约束、授权。

3.1 连接表达式(JOIN)

3.1.1 连接条件 JOIN...ON

前面我们讲了JOIN...USING，它是一种自然连接的形式，只需要在指定属性上取值相同。

JOIN...ON和JOIN...USING类似，不过JOIN...ON中，相同属性值的元组会出现两次。

比如：

SELECT *
FROM student JOIN takes on student.ID = takes.ID;

#和下面的查询等价
SELECT * 
FROM student, takes
WHERE student.Id = takes.ID

针对上面的查询结果，每个元组中ID会出现两次（一次来自student，一次来自takes）。而JOIN...USING只会出现一次ID。

3.1.2 外连接 OUTER JOIN

外连接与我们前面讲的NATURAL JOIN类似，不同的地方是，外连接在结果中可以包含部分属性为空值的元组（可以包含哪些空值元组请见下面）。

假设一些学生没有选课。现在我们要找出所有学生，以及他们所选的课。

下面的查询是错误的：

SELECT *
FROM student NATURAL JOIN takes;

因为上面的查询忽略了一些学生没有选课，那么他们也没有出现在takes中，也就不会出现在结果中了。

根据不存在的属性，可以把外连接划分为三类：

左外连接（LEFT OUTER JOIN)：保留出现在外连接运算符之前的元组（该元组右边部分为空）。
右外连接（RIGHT OUTER JOIN): 保留出现在外连接运算符后的元组（该元组左边部分为空）。
全外连接（FULL OUTER JOIN): 保留出现在两个关系中的元组（某些元组左边为空，某些元组右边为空）

如下是一个左外连接的结果例子：student NATURAL LEFT OUTER JOIN takes

left_outer_join

根据takes中不存在的属性构成的元组，保留了student中的属性，takes中的属性为空

找出一门课程也没有选择的学生：

SELECT name
FROM student NATURAL LEFT OUTER JOIN takes
WHERE course_id is NULL;


#不用join
SELECT name 
FROM student
WHERE name NOT IN (SELECT DISTINCT name FROM takes);

下面是一个右外连接的结果例子：takes NATURAL RIGHT OUTER JOIN student

right_outer_join

它和前面左连接的结果是一样的。因为takes 和 student次序也便饭了。

3.1.3 内连接（INNER JOIN)

为了把常规连接（NATURAL JOIN, JOIN...USING)和外连接（OUTER JOIN)区分开来，我们把常规连接称作INNER JOIN。

SELECT *
FROM student JOIN takes USING (ID);

#等价于
SELECT *
FROM student INNER JOIN takes USING (ID);

同样的NATURAL JOING等价于NATURAL INNER JOIN

3.2 视图（VIEW)

3.2.1 视图基础

前面我们的所有操作都是在逻辑层上。我们所有的操作都是对应数据库中存在的表。

视图（VIEW)是属于视图层，是对逻辑层的抽象。和面相对象一样，抽象的一个目的是封装变化。数据库中的关系可能变化，如果像前面一样，直接对关系进行查询，那么查询语句也很可能需要改变。使用VIEW可以避免这个问题。

同时，让用户看到整个逻辑层，是不合适的。

视图（VIEW)是一种虚关系。视图并不预先计算并存储，而是在使用的时候在进行查询。既然是一种关系，我们可以用视图进行查询，就像普通的关系一样。

视图包含的属性，是定义视图包含的实际查询返回的那些属性。

通用形式：

# v是视图的名字
CREATE VIEW v AS <real query expr>;

例子：

# 视图的属性包含：ID, name, dept_name
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;

上面的视图在查询的时候就像一个实际存在的关系：

SELECT name
FROM faculty
WHERE ID=12345;

抽象复杂查询的视图：

SELECT VIEW course_2008_2009 AS
SELECT course_id
FROM course
WHERE semester = 'Fall' AND year = 2008 
    AND course_id IN (
                    SELECT course_id 
                    FROM course 
                    WHERE semester='Spring' AND year = 2009)

3.2.2 视图更新

视图在查询的时候非常有用，但由于构成视图定义的关系可能不止一个，也有可能也是视图构成的，当视图用作更新、删除的时候就比较困难。

这里直接省略相关的讨论。

3.2.3 物化视图（Materialized View）

一般的视图是不会提前存储查询结果。物化视图会存储查询的结果。

物化视图：特定数据库系统允许存储视图关系，但是它们保证：如果用于定义视图的实际关系发生改变，视图也跟着修改。这样的视图称作物化视图。

既然物化视图存储查询结果，就会存储数据过期的情况，即：实际关系中存储的数据改变了，而视图还没有改变。

保持物化视图一直在最新状态的过程成为物化视图维护（Materialized View Maintenance），或者简称视图维护（View Maintenance）。

视图维护有两种方式：

当构成视图定义的实际关系改变时，视图也立即更新。
周期性地检查并改变过期视图。（当构成定义视图的实际关系改变时，不立即更新视图）。

具体采用哪种方式，可以对数据库系统配置。

如果实际关系地数据很大，由于物化视图提前存储了相关的查询结果，在查询的时候，就不用再查询全部实际，这可以得到更快的响应时间。所以物化视图也是一种优化视图的方式。显而易见，物化视图会占用额外的存储空间。

3.3 事务（Transact）

事务由一系列查询和更新语句组成。SQL标准定义：当一条SQL语句被执行的时候，就隐式的开始了一个事务（一条语句就是一个事务）。当语句执行完后，就立即提交了事务了。

显示地开始一个事务用： BEGIN TRANSACTION

下列SQL语句之一会结束一个事务：

COMMIT (提交，保存更改）
ROLLBACK （回滚，取消更改）

例子：

BEGIN TRANSACTION;  # 开始事务
DELETE FROM instructor;
DELETE FROM courses;
# ...
ROLLBACK;           # 取消变更。数据和BEGIN TRANSACTION前一样
# COMMIT            # 或者保存变更

事务是SQL提供地一个非常有用的机制。生活中大量用到事务，比如银行转账，各种计费系统。

事务的几个特性：

原子性（atomicity）：事务的所有操作在数据库中要么全部正确反映出来，要么完全不反映。
一致性（consistency）：隔离执行事务时（换言之，在没有其他事务并发执行的情况下）保持数据库的一致性。
隔离性（isolation）：尽管多个事务可能并发执行，但系统保证，对于任何一对事务Ti和Tj，在Ti看来，Tj或者在Ti开始之前已经执行结束，或者在Ti执行结束后开始执行。因此，每个事务都感觉不到系统中有其他事务在并发地执行。
持久性（durability）：一个事务完成后，它对数据库的改变必须是完整的，即使出现系统故障。

以上几个特性称为事务的ACID特性(ACID property)。

3.4 完整性约束（integrity constraint）

完整性约束保证授权用户对数据库所做的修改不会破坏数据地一致性。

一般来说，一个完整性约束可以是属于数据库的任意谓词。但检测任意谓词的代价可能太高。所以一般使用开销极小的完整性约束

3.4.1 单个关系上的完整性约束

PRIMARY KEY
NOT NULL
UNIQUE
CHECK <Pred>

PRIMARY KEY

PRIMARY KEY: 用主键修饰地属性（一个或多个），必须要保证关系中元组对应的值（对于多个属性来说，是值的组合）唯一。被PRIMARY KEY修饰地属性不能为NULL（所以效果也包含了NOT NULL 约束）。

一个关系中只能有一个PRIMARY KEY约束。

比如：

CREATE TABLE students (
    ID INT PRIMARY KEY,     #等价于 ID INT PRIMARY KEY NOT NULL,
    name VARCHAR(20) NOT NULL,
);

PRIMARY KEY保证了，ID属性不能重复。比如：关系中已经存在了（1, 'xiaoming') ，在插入(1, 'xiaohua')的时候由于违反了PRIMARY KEY约束，数据库就会报错。

NOT NULL

NOT NULL: 即该属性的值不能为空。

针对上面那个关系，如果插入 (2, NULL)是不被允许地，因为name属性被指明为NOT NULL。

UNIQUE (不重复）

UNIQUE: 和PRIMARY KEY类似，但是一个关系中允许有多个UNIQUE约束。UNIQUE约束地属性可以为NULL（因为NULL不等于任何值）。

比如：

CREATE TABLE students (
    ID INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    address TEXT,
    UNIQUE (name),      # name不能重复
    # UNIQUE (name, address),   #仅作为举例
);

如果关系中已经由(1, 'xiaoming') ，在插入(2, 'xiaoming')地时候，就会报错。因为违反了unique约束。

CHECK 子句

CHECK 子句指定一个谓词: CHECK (pred). 每个元组都必须满足谓词pred。

如下：

CREATE TABLE course (
    course_id INT PRIMAERY KEY,
    name VARCHAR(20) NOT NULL,
    semester NOT NULL,
    CHECK (semester IN ('Spring', 'Summer', 'Fall', 'Winter));
)

CHECK 的检测有可能开销很大。

('Spring', 'Summer', 'Fall', 'Winter)是一个枚举类型。关于枚举类型的使用，请查阅手册。

3.4.2 多个关系上的约束

参照完整性（referential integrity）

我们常常希望保存在一个关系中给定属性集上的取值也在另一个关系的特定属性集的取值中出现。这种情况称为参照完整性。

默认情况下，SQL中外键参照的属性是被参照属性的主键。

形式：

CREATE TABLE r1 (
    Ai Di,
    Aj Dj,
    ...,,
    Ak Dk
    Am Dm,
    ...
    Az Dz,
    PRIMARY KEY(Ai, Aj, ..., Ak)
);

CREATE TEABLE r2 (
    A0 D0,
    A1 D1,
    A2 D2,
    ...
    Ai Di,
    Aj Dj,
    ...
    Ak Dk,
    ...
    An Dn,
    PRIMARY KEY (A0),   # 也可以是其他的
    FOREIGN KEY (Ai, Aj, ..., Ak) REFERENCES r1
);

上面的例子中，r2对应外键是(Ai, Aj, ..., Ak)，它们同时也是r1的主键。

可以看出，r2(Ai, Aj, ..., Ak)是r1(Ai, Aj, ..., Ak)的一个子集。

当删除r2中的一个元组的时候，要考虑该元组是否被r1中的某个元组应用。如果不考虑这个关系直接删除元组的话，会造成r1中的某个元组没有应用r2的元组，从而违反外键约束。此时，我们有几种方式来处理这种情况：

ON DELETE/UPDATE CASCADE: 级联。跟着删除/更新r2中的元组
ON DELETE/UPDATE SET NULL: 如果允许（没有违反其他约束），设置为NULL
ON DELETE/UPDATE SET DEFAULT：如果允许，设置为该默认值。

一个实际的例子：

CREATE TABLE department (
    dept_name VARCHAR(20),
    building VARCHAR(15),
    budget numeric(12, 2),
    PRIMARY_KEY (dept_name)
);

CREATE TABLE course (
    course_id VARCHAR(7),
    title VARCHAR(50) NOT NULL,
    dept_name VARCHAR(20),
    credits numeric(2, 0)
    PRIMARY_KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department
);

即course.dept_name必须属于department.dept_name：课程一定是某个部门开设的，但有的部门不一定开设了课程。如果准备删除（或者更新）department中某个元组，且该元组被course引用，则删除不被允许。

CREATE TABLE course (
    course_id VARCHAR(7),
    title VARCHAR(50) NOT NULL,
    dept_name VARCHAR(20),  # 通常情况外键声明为NOT NULL
    credits numeric(2, 0)
    PRIMARY_KEY (course_id),
    FOREIGN KEY (dept_name) REFERENCES department
        ON DELETE SET NULL
        ON UPDATE SET CASACDE
);

如果删除了某个部门，此时系统不拒绝删除，而是把course中的dept_name设置为NULL(这里仅作举例，dept_name允许为NULL)，如果有的部门更新了，把course中对应的dept_name也更新。

对外键NULL值的处理：通常情况，外键一般都声明为NOT NULL。如果允许为NULL，则自动被认为满足约束。

3.5 授权

授权包括：

授权读取数据
授权插入新数据
授权更新数据
授权删除数据

每种类型都成为一个权限（privilege)。

当用户提交查询或更新的时候，SQL先检查该用户是否含有对应权限，如果没有权限就会拒绝该操作。

如果某用户创建了某个模式（database），则该用户自动拥有该模式的所有权限。如果某用户创建了一个关系，则该用户某人被授予该关系的所有权限。

3.5.1 权限的授予与收回

权限包括：SELECT, INSERT, UPDATE, DELETE 和 ALL。其中ALL代表所有权限。

通用形式：

# 授予
GRANT <权限列表>
ON <关系名或视图名>
TO <用户/角色列表>;

# 回收
REVOKE <权限列表>
ON <关系名或视图名>
FROM <用户/角色列表>;

例子：

GRANT SELECT
ON student, instructor
TO robert, james;


REVOKE SELECT, INSERT
ON course
FROM lee, donald;

上面的授权了表student, instructor的SELECT权限给robert, james。他们可以在这个表上进行查询。从lee，donald回收了course上的SELECT和INSERT权限。

如果授权了UPDATE权限，则默认拥有了关系全部属性的修改的权限。如果我们只想授予部分属性的修改权限，我们可以在UPDATE后面加入对应的属性名:

GRANT SELECT, UPDATE (score)
ON takes
TO james;

# 只回收UPDATE权限
REVOKE UPDATE (score)
ON takes
FROM james;

授予了表student的SELECT和修改score属性的权利给james。

3.5.2 角色 ROLE

假如我们想给所有老师修改成绩的权限（显而易见不恰当，这里仅仅是举例），我们不可能为每一个老师都执行GRANT UPDATE ON操作。使用角色可以达到这个目的。就像操作系统中的用户组和用户一样。

CREATE ROLE teacher;
CREATE ROLE student;

# 授予权限给角色
GRANT UPDATE ON takes TO teacher;

# 把某个用户赋予角色
GRANT teacher TO Amy;

#teacher 也拥有student角色
GRANT student to teacher;

关于视图：如果某用户基于关系r创建了视图v，该用户用户视图v的所有权限。但是如果该用户没有关系r的权限（比如SELECT)，他将仍然不能在该视图上执行对应（查询）操作。

3.5.3 权限的转移 `WITH GRANT OPTION`

用户A创建了关系r，A授予B所有权限（ALL)，但B无权授予C在关系r上的任何权限。

如果想让用户也能授权个其他用户，我们需要加入 WITH GRANT OPTION ：

GRANT SELECT 
ON student
TO Amy
WITH GRANT OPTION;

这样就会出现权限链。被授予权限的用户的权限不可能超过主动授予它权限的用户的权限。即U4不可能超过U1。

privilege_chain

3.5.4 权限的收回 REVOKE ON FROM

# 回收
REVOKE <权限列表>
ON <关系名或视图名>
FROM <用户/角色列表>;

前面已经有了几个例子。这里主要讲WITH GRANT OPTION的回收。

# 仅仅回收GRANT权限，不回收SELECT权限
REVOKE GRANT OPTION FOR SELECT ON student FROM Amy;

由于存在权限链，回收权限的时候，有可能也回收了其他用户的权限。这也是默认行为。比如：如果回收了U1的权限，那么U4的所有权限，和U5部分权限(U1授予它的）也会被回收。

如果不想回收其他用户的权限，可以用RESTRICT来表示：

REVOKE SELECT ON student FROM Amy RESTRICT;

3.5.4 外键（reference）的授权

GRANT REFERENCES (dept_name) ON department TO mario;

使用场景：mario创建了关系r，但关系r包含了外键，引用department.dept_name。

CREATE TABLE r (
    dept_name VARCHAR(20),
    FOREIGN KEY (dept_name) REFERENCES department
);

数据库基础Database2-中级SQL