数据库期末复习之中级SQL（双语）

中级SQL

• Join Expressions （连接）
• Views（视图）
• • 引入视图的原因
  • View Definition
  • Update base table of a View
• transation(事务)
• Integrity Constraints（完整性约束）
• • Integrity Constraints on a Single Relation
  • Cascade（级联）
  • Integrity Constraint Violation During Transactions
  • Complex Check Clauses
• Built-in Data Types in SQL （数据类型）
• • Index Creation
  • Environments, Catalogs, and Schemas
• Authorization
• • Managing Privileges
  • Managing Roles
  • Managing View
  • References Privileges

Join Expressions （连接）

整合在初级SQL中了

Views（视图）

引入视图的原因

• 安全原因
• 数据库变更，但不想修改应用程序
• 提高性能（物化视图materialized view）

让所有用户都看到整个逻辑模型是不合适的。出于安全考虑，可能需要向用户隐藏特定的数据。

我们还可能希望创建一个比逻辑模型更符合特定用户直觉的个人化的关系集合

视图（虚关系）：虚关系并不预先计算并存储，而是在使用虚关系的时候才通过执行查询被计算出来

View Definition

create view v as < query expression >

视图定义不同于创建一个新的关系表

创建视图只是保存了视图所定义的查询（保存在数据库管理系统的数据字典中）

使用视图时，将执行视图所定义的查询

Update base table of a View

只允许对简单视图进行更新：进行update、insert、delete操作

简单视图：

from子句只有一个表 select子句只含有这个关系的属性名，不含有任何表达式、组函数或者没有指定distinct

Select 子句未列出的属性可以为null值 视图定义中不含有group by和having子句

transation(事务)

事务（transation）由查询和（或）更新语句的系列组成。

Commit (work)

Rollback (work)

关于事务的四要素acid

Integrity Constraints（完整性约束）

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

因此，完整性约束防止的是对数据的意外破坏。

Integrity Constraints on a Single Relation

• not null
• primary key
• unique
• check (P), where P is a predicate
• Referential Integrity constraint / subset dependency

Cascade（级联）

create table course ( 
	...
	dept_name varchar(20),
	foreign key (dept_name) references department 
		on delete cascade 
		on update cascade,
	... 
)
           

如果删除 department 中的元组导致了此参照完整性约束被违反，则删除并不被系统拒绝 ， 而是对 course 关系作"级联"删除， 即删除参照了被删除系的元组

Integrity Constraint Violation During Transactions

set constraints constraint-list deferred

约束延迟检查

Complex Check Clauses

check

比如：在表Sectioin上声明参照完整性约束

check ( time_slot_id in (select time_slot_id from time_slot ))

assertion

create assertion <assertion-name> check <predicate>;

# 断言
# 对于student 关系中的每个元组，它在属性tot_cred 上的取值必须等于该 生所成功修完课程的学分总和。 
# SQL 不支持所有量词“for all X, P (X)” 结构(其中P是一个谓词) 
# 将所有量词等价替换为存在量词"not exists X such that not P (X) " 结构来实现此约束
create assertion credits-earned-constraint check 
( not exists (select ID 
	      from student	
	      where tot_cred < > ( select sum ( credits ) 
				   from takes natural join course 
				   where student. ID = takes. ID 
				   	and grade is not null and grade < > ‘ F' ) ; 
           

触发器

Built-in Data Types in SQL （数据类型）

SQL中的日期和时间数据类型

date，time，timestamp，interval

允许设定默认值

Large-Object Types

blob：binary large object

clob：character large object

User-Defined Types

create type Dollars as numeric (12,2) final

强制类型转换：cast (department.budget to numeric(12,2))

删除类型：drop type

修改类型：alter type

定义域：

create domain person_name char(20) not null

Index Creation

create index studentID_index on student(ID)

加速通过ID搜索

Environments, Catalogs, and Schemas

Catalog.schema.table or Database.schema.table

SQL environment

用户标识（ user identifier ）：也称为授权用户标识（authorization identifier）

默认的目录（catalog）

默认的模式（schema）

Authorization

Managing Privileges

# 授予
grant <Privileges>(attribute)
on <table or view>
to <users or roles>
(with grant option);	#授予权限转移的权限
# 收回
revoke <Privileges>(attribute)
on <table or view>
from <users or roles> (restrict);	#restrict防止级联收回，cascade级联收回
#用户名 public 指系统的所有当前用户和将来的用户 。因此，对 public 的授权隐含着对所有当前用户和将来用户的授权。
#revoke grant option for select on department from Amit; 收回grant option 
           

Managing Roles

创建角色：create role

角色可以授予给用户，也可以授予给其他角色

一个用户或一个角色的权限包括:

• 所有直接授予用户/角色的权限。
• 所有授予给用户/角色所拥有角色的权限。

Managing View

如果用户创建一个视图，而此用户在该视图上不能获得任何权限， 系统会拒绝这样的视图创建请求

References Privileges

假定 Marian在关系 r 中创建了一个外码，它参照 department 关系 的 dept_name 属性，然后在 r 中插入一条属于 Geology 系的元组。 那么就再也不可能从 department 关系中将 Geology 系删除，除非同时也修改关系 r。 这样，Mariano 定义的外码限制了其他用户将来的行为。因 此 ，需要有 references 权限。