中级SQL(Chapter 4)

Join

join.jpg

普通连接的条件


A join B on A.id=B.courseid;

根据on后面的条件将表A与B进行连接

Outer Join

与inner join和natural join不同，减少了在连接中信息的丢失

left outer join

保留左边的table中的所有tuple，没有对应的就设为null

right outer join

保留右边的table中的所有tuple，没有对应的就设为null

full outer join

保留两边的table中的所有tuple，没有对应的就设为null

Inner Join

如果没有说明是outer join，默认就是inner join

视图（View）

出于数据安全及隐私的考虑，需要建立视图

定义

v表示视图名


create view v as <query expression>;

如果要指定视图的属性名，可以如下显式指定


CREATE VIEW faculty(ID,Name,Department,Total_salary) AS

    SELECT 

        Id, name, dept_name,sum(salary)

    FROM

        instructor;

使用

视图使用起来和表没有什么差别
可以用于select from
可以用于创建其他的视图

物化视图（materialized view）

如果用于定义视图的实际关系发生改变，视图也得随之修改

保持物化视图一直在最新状态的过程称为物化视图的维护

不同的数据库的更新频率不一致

有的只要实际关系一发生改变，就进行更新
有的允许物化视图过时，周期性进行更新
有的在视图被访问时进行更新

更新视图

可以通过对某些符合要求的视图进行插入删除更新来影响到原表，但是一般不建议

能进行操作的视图需要满足以下条件：

from子句中只有一个table
select中只有属性名，不包含任何表达式，聚集函数和distinct等声明
没有被select属性没有not null约束，也不构成主码
query expression中不包含group by或having语句

事务（Transaction）

由查询或/和更新语句的序列组成。当一条sql语句被执行时，就隐式地开启了事务

结束事务

commit work

提交当前事务，当前事务所做的更新在数据库中永久保存。事务被提交后，自动开始新的事务

rollback work

回滚当前事务，撤销该事物对数据库所做的更新

由于有的操作不能彼此分离，要么都做要么都没做，所以引入原子事务

格式如下


begin atomic

<transaction>

end;

其间的transaction所包含的多条语句全部完成才会提交，否则回滚

完整性约束

单关系约束

not null 约束

在create table时


name varchar(20) not null

unique约束


unique(A1,A2,...)

相当于指出属性A1,A2,...构成了一组候选码，如果未约束not null，那么null是可以的

check约束

check(P)


check(semester in ('Spring','Summer','Fall','Winer'))

或者

check(budget>0)

经实测，MySQL不支持check语句

断言（Assertion）

域约束和参照完整性约束都只是断言的特殊形式


create assertion <assertion name> check <predicate>;

其他数据结构

默认值

在create table的时候可以为属性设置默认值，如果一个元组被插入到table中但是没有被给出该属性值的时候，就自动填充为默认值


dept_name varchar(20) default 'CS'

索引（Index）

查询文件中的少量记录，利用索引可以不用扫描关系中的所有元组，进行高效查询


create index studentID_index on student(ID);

在student关系的属性ID上创建了一个名为studentID_index的索引

大对象数据类型

用法和varchar，int一致

clob 字符数据的大对象数据类型
blob 二进制数据的大对象数据类型

用户定义数据类型（类似C中的typedef）

定义type


create type Dollars as numeric(12,2) final;

create type Pounds as numeric(12,2) final;

final无实际意义

虽然Dollars和Pounds都是numeric(12,2)，但是如果把Dollars的值赋给Pounds也会出错

强制类型转换形式如下


cast(dept.budget to numeric(12,2));

可以用drop type或alter type修改以前创造过的类型

定义domain


create domain DDollars as numeric(12,2) not null;

DDollars域可以像Dollars属性一样使用

type与domain之间的差别

domain上可以声明约束而type上不行
domain没有强制类型检查，只要基本类型相同就可以相互赋值

用户权限

权限种类：

select
insert
update
delete

授权与撤销

通过grant语句授予权限


grant <权限列表>

on <关系名或视图名>

to <用户/角色列表>;

可用all privileges代指所有权限

在授予update/insert权限时，可以指定属性名，如果不指定，就默认是关系中所有属性的update/insert权限

如果只授予部分属性的insert权限，那么insert时，其余属性要么是默认值，要么是null


grant update(budget)

on departments

to Amit,Chen;

用户名如果是public，代指系统所有的当前用户和将来用户
通过revoke语句撤销权限


revoke <权限列表>

on <关系名或视图名>

from <用户/角色列表>;

用户角色（role）

创建角色


create role instructor;

角色可以像用户一样被授予权限


grant select on dept to instructor;

角色也可以被授予给用户，也可以被授予给其他角色
一个用户/角色的权限包括：所有直接授予的权限；授予给用户/角色的角色所拥有的权限

视图的授权

创建视图的用户在原关系上拥有的权限不会增多，如果原来没有update权限，创建视图后对视图也不会有update权限
默认函数所具有的权限是创建者的权限，但是如果函数定义有一个额外的sql security invoker子句，那么就在调用该函数的用户的权限下进行

模式的授权

原本只有模式的拥有者才能够执行对模式的任何操作
references权限，可以像update权限那样授予与收回，允许用户在创建关系时声明foreign key


grant references(dept_name) on department to Amit;

意味着用户Amit可以在创建关系时声明外码参照department中的dept_name

Amit定义的foreign key可以限制其他用户对department将来的行为

如果想要新建关系r上的check约束，而约束里面有参照department的子查询，那也需要references权限

权限转移

使用以下形式进行


grant select on department to Amit with grant option;

意味着在给用户Amit授权department上的select时，还赋予其给其他用户授予select权的权限

即被赋予给Amit的department上的select可以被Amit转移给其他用户

以此种方式授权的在revoke时默认级联删除，如果Amit的权限被revoke了，那么它给予别的用户的权限也将失效
想要防止级联删除权限，可以加关键字restrict


revoke select on department from Amit restrict;

想要收回Amit的权限转移能力（一些数据库不支持）


revoke grant option for select on department from Amit;

某些时候不希望这样的级联，所以就允许权限通过角色而非用户被授予（4.6.6）

Chapter 4 Intermediate SQL