数据库(四)之中级SQL：连接、视图、完整性约束、事务、SQL数据类型与模式、授权

#包含连接、视图、完整性约束、事务、SQL的数据类型和模式、授权

一、连接

1. 自然连接：运算两个关系，并产生一个关系作为结果，自然连接只考虑那些在两个关系中都出现的属性上取值相同的元组对，也可以使用join table_name using关键字指明属性；

select name,title from (instructor natural join teaches) join course using(course_id);

2. 连接表达式 join …on
   on:表示条件，其中 select * from students join takes on students.ID = takes.ID 等价于select * from students,takes where s.ID = t.ID;
3. 外连接：通过在结果中创建包含空值元组的方式，保留了那些在连接中丢失的元组。
   a.左外连接：只保留出现在左外连接运算之前的关系中的元组,关键字 left outer join
   b.右外连接：只保留出现在右外连接运算之后的关系中的元组，关键字 right outer join
   c.全外连接：保留出现在两个关系中的元组，关键字 full outer join
   d.on在外连接中与where的区别:on在外连接中是连接条件，where 是笛卡尔积关系中的条件，如果使用where可能会忽略外连接中补上null的元组；
4. 内连接：只选择相同值的项，关键字谓词 join…using()

二、视图：一种虚关系，并不会实际创建持久性的表

1. 视图定义(视图定义中可利用视图)
   create view view_name[(A1,A2…An)] as ()

create view faculty as (select ID, name, dept_name from instructor);

2. 视图查询：与其他查询一样
3. 视图更新：一般情况下不允许对视图进行更新，具体数据库有具体的限制要求。

三、事务：查询和(或)更新语句的序列组成

1. 隐式执行：当一条SQL语句被执行时，就会隐性开始一个事务；
2. Commit work:提交当前事务，将更新持久保存；
3. Rollback work: 回滚当前事务，即撤销该事务中所有SQL语句对数据库的更新；
4. 说明：一旦事务提交了，回滚操作将不起作用.

四、完整性约束:保证授权用户对数据库所做的修改不会破坏数据的一致性，防止对数据的意外破坏

1. not null
2. unique约束(超码约束): unique(A1,A2,A3…An)；
3. check子句：check(…)
   create table section
   (…
   check(grade>0));
4. 参照完整性： 外码说明更新操作 on delete cascade;
5. 断言:表达了希望数据库总能满足的一个条件

create assertion assertion_name check(...);

五、SQL的数据类型和模式

1. 日期和时间类型
   a. date: ‘2010-04-25’;
   b. time: ‘09:30:00’;
   c. timestamp: ‘2010-04-25 09:30:00’;
   d. with timezone: 将时区与时间一起保存;
   e.可以利用 cast e as t形式的表达式来将一个字符串e转换成时间类型t，字符串格式一定要正确;
   f.可以利用 extract(field from d)从date或time值d中提取单独的域;
   g.获取时间的函数： current_date,current_time 等；
   h.时间类型可以运算。
2. 索引：一种数据结构，允许数据库高效地找到关系中那些在索引属性上取定值的元组，而不用扫描关系中的所有元组；

create index index_name on student(ID);

3. 大数据类型 blob()

4. 用户定义新类型：独特类型和结构化数据类型,这里主要讲独特类型
   a.定义新类型 create type type_name as num_type; create type Dollars as numeric(12,2);
   b.由于有强类型检查，Dollars类型和numeric类型不匹配;
   c.类型无法被约束(not null)和定义默认值.

5. 定义域： create domain DDollars numeric [constraint salary_value_test][check(value>2900)]
   a.域可以添加约束和默认值；

6. create table 的扩展
   a. create table table_name1 like table_name2 :创建一个和table_name2模式相同的表;
   b. create table table_name as(select...): 创建一个查询后的表.

7. 目录和模式：用来标识关系的唯一性如文件结构一样
   a.一个关系(表)的完整标识名为： catalog5.univ_shema.course ,其中catalog5是目录名，univ_shema是模式名,course是关系名;
   b.每个用户都用一个默认目录和默认模式，在默认下关系可以写成 course;
   c.可以用 create schema 和 drop schema 来创建删除模式.

六、授权

1. 权限类别： 授权读取数据(select),授权插入数据(insert)，授权更新数据(update),授权删除数据(delete);视图授权，模式授权
2. 授权语句： grant <权限列表> on <关系名或视图名> to <用户列表>; grant update(budget) on department to Amit,Satoshi;
3. 回收权限 : revoke <权限列表> on <关系名或试图名> from <用户列表>;
4. 用户名 public 指系统的所有当前用户和将来的用户，因此对public 授权 隐含对当前所有用户和将来的用户授权;
5. 角色：
   a.角色的作用：统一授权;
   b.角色语句：create role role_name;
   c.权限隐式继承. 角色a 授权 角色b ,角色b 授权用户c ,c就有b的直接授权和a的隐式授权。
6. 视图的授权：想创建视图就必须有对视图中关系的select权限；
7. 模式的授权：只有模式的拥有者才能够执行对模式的任何修改；
8. 权限的转移：默认方式下，被授权的角色/用户无权将权限转移，可以在grant命令后面附加 with grant option子句提供权限转移授权
   a.一个对象(关系/视图/角色)的创建者拥有该对象的所有权限，包括给其他用户授权;
   b. grant select on department to Ami with grant option;
   c.用户具有权限的充分必要条件：当且仅当存在从授权图的根到代表该用户顶点的路径;
9. 权限的收回
   a.级联回收:收回某个用户/角色的权限将回收该用户/角色对之后所有的授权,防止级联回收在末尾加restrict子句.