中级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）
网友评论