1.连接表达式

基本分类

• 连接成分
  • 包括两个输入关系、连接条件、连接模型
• 连接条件
  • 决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性
• 外连接
  • 决定如何处理与连接条件不匹配的元组

连接条件

• 自然连接（natural join）
  • 出现在结果关系中的两个连接关系的元组在全部公共属性上取值相等，且公共属性只出现一次。
• join…using（A~1~，A~2~,...,A~n~）
  • （A₁，A₂,...,A_n）是两个连接关系的公共属性的子集，元组在（A₁，A₂,...,A_n）上取值相等，且（A₁，A₂,...,A_n）只出现一次。
• join...on<P>
  • 出现在结果关系中的两个连接关系的元组在公共属性上取值满足谓词条件P，且公共属性出现两次。
  • on子句特点：
    • on条件可表示任何谓词，可表示比自然连接更丰富的连接条件。
    • 对于外连接，on条件的表现与where不同。
    • 在on子句中指定连接条件，在where中指定其他条件，更清晰易懂。
    • on条件是外连接声明的一部分，where子句不是。

内连接

• 舍弃不匹配的元组

• 是默认连接类型，关键词inner可选

  select * from student natural join takes
  

外连接

• 通过在结果中创建包含空值元组的方式，保留哪些在连接中丢失的元组
  • 左外连接（left outer join）
    • 内连接加上左侧适配的元组，缺少右侧关系属性用null
  • 右外连接
    • 内连接加上右侧失配的元组，缺少左侧关系属性用null
  • 全外连接（full outer join）
    • 内连接+左侧失配元组+右侧失配元组，缺少的关系属性用null

关系的连接

• cross join
  • 两个关系的笛卡尔积
• union join
  • 左边关系中失配的元组+右边关系中失配的元组
• 对于外连接，连接条件是必须的
• 对于内连接，；连接条件是可选的，没有连接条件等价于两个关系的笛卡尔积

2.视图

• 视图：作为虚关系，对用户可见的关系

• 定义视图

  create view view_name[(col_name[,col_name]...)] as (select...)
  

  • 视图的属性名缺省为子查询结果中的属性名，也可以显式指明。
  • 视图名可以出现在任何关系名可以出现的地方。

• 撤销视图

  drop view view_name
  

• 视图关系在概念上包含查询结果的元组，但并不进行预计算和存储结果元组，只存储与视图关系相关联的查询表达式。当视图关系被访问时，才计算产生查询结果元组。

物化视图（materialized view）

• 物化视图：某些DBS允许存储视图，但保证如果定义视图的实际关系改变，视图也跟着修改。
• 物化视图维护：保持物化视图一直在最新状态。
  • 各数据库产品采用的维护方式不同。
• 频繁使用视图的应用将会从视图的物化获益。
• SQL没有定义指定物化视图的标准方式。

视图更新

• 视图更新最终是对所设计的实表的更新。由视图更新导致实表更新可能带来问题。

• 如果下面条件都满足，称视图时可更新的：

  • from子句中只有一个数据库关系
  • select中只包含关系属性名，不包含表达式、聚集、distinct声明
  • 出现在select子句中的属性限制null
  • 查询中不包含group by或having子句

• 即便视图可更新，依然存在问题

  • with check option

    • 视图定义时，指定with check option，检查通过视图进行修改时，结果必须在视图中，否则拒绝更新。

    • 无with check option：

      可以更新，更新后元组不再出现在视图中；

    • 有with check option：

      不可以更新，update语句将被dbms拒绝；

3.事务

• 事务由查询或更新语句的序列组成。
• sql中事务的开始：
  • 一个sql执行，隐含开始了一个事务
• 结束事务：
  • commit work：完成所有事务，提交事务
  • rollback worl：不能完成所有任务，回滚事务
  • commit/rollback：结束事务，其后的sql属于新的事务

4.完整性约束

• 完整性：
  • 数据的正确性和相容性
• 完整性检查：
  • DBMS必须提供一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据是正确的，避免非法的不合语义的错误数据的输入和输出，即所谓的‘Garbage in garbage out’所造成的无效操作和错误结果。
• 完整性子系统：
  • 数据库的非法更新情况：
    • 数据本身是错误的
    • 数据原来是正确的，操作或程序错误导致输入数据错误
    • 由于系统故障，导致数据错误
    • 事务的并发执行产生不正确的结果
    • 认为故意破坏
  • 功能：
    • 监督事务执行，检查是否违反完整性规则
    • 如有违反，采取相应措施（拒绝、报告、改正）

完整性控制

• 完整性规则集
  • 由DBA或程序员事先提供的有关数据约束的一组规则
• 规则的组成
  • 约束条件：要检查什么样的错误（性别为男女）
  • 触发条件：什么时候使用规则进行检查（插入元组）
  • ELSE子句：若检查出错误，该怎样进行处理（显示错误）
• 规则的分类：
  • 域完整性规则：属性取值范围
  • 域联系规则：在一个或多个关系中，属性间的联系影响约束
  • 关系完整性规则：更新操作对数据库中值的影响和限制

完整性约束

• 完整性约束通常被看成是数据库模式设计过程的一部分

  • create table命令中的约束

    • not null
    • unique
    • check（<P>）

  • alter table table_name add constraint
    

• check子句

  • check子句模拟枚举

    create table table_name 
    (
        ...
        check(semester in ('Fall','Winter','Spring','Summer'))
    );
    

• 域约束子句

  • 用create domain定义域时，可以出现check

    create domian age_domian smallint
        check((value>=15)and(value<=25));
    

• 主码约束

  • 主码值不允许空，也不允许重复出现
  • 意义：关系对应到现实世界中的实体集，元组对应到实体，实体是相互可区分的，通过主码来唯一标识，若主码为空，则出现不可标识的实体，这是不容许的

• 参照完整性（referential integrity）

  • 一个关系中给定属性集上的取值也在另一关系的特定属性集中出现

    • 如果关系R2的某个元组t2参照了关系R1的某个元组t1，则t1必须存在。

  • 外码约束：

    • 关系R包含一个属性组，与另一个关系S的主码相对应， 则称这个属性组为R的外码。
    • 如果关系R2的外部码Fk与关系R1的主码Pk相对应，则R2 中的每一个元组的Fk值或者等于R1 中某个元组的Pk 值， 或者为空值。（对R2的检查）

  • 外码约束定义形式

    foreign key (sno) references s(sno)
    

  • 参照完整性约束与外码约束不同，通常不要 求被参照关系上的参照属性一定是主码或候 选码，但这种形式SQL还不能直接声明。

  • references子句与foreign key子句

    • references子句作为属性定义的一部分，声明外码

      当违反约束时，通常是拒绝执行导致破坏完整性的操作。

    • foreign key 子句可以指明：如果被参照关系上的删改或更新动作违反了约束，系统必须采取一些步骤通过修改参照关系中的元组来恢复完整性，而不是简单拒绝。

      foreign key (dept_name) references department
        [on <delete|update> cascade
           on <delete|update> set null
           on <delete|update> set default]
      

      • 删除基本关系（被参照关系）元组
        • RESTRICT方式 : 只有当依赖关系中没有一个外码值与要删除的基本关 系的主码值相对应时，才可以删除该元组，否则系统 拒绝此删除操作
        • CASCADE方式 : 将依赖关系中所有外码值与基本关系中要删除的主码 值所对应的元组一起删除
        • SET NULL方式 : 删除基本关系中元组时，将依赖关系中与基本关系中 被删主码值相对应的外码值置为空值
      • 修改基本关系主码
        • RESTRICT方式 ：只有当依赖关系中没有一个外码值与要修改的基本关 系的主码值相对应时，才可以修改该元组主码，否则 系统拒绝此次修改
        • CASCADE方式 ： 将依赖关系中所有与基本关系中要修改的主码值所对 应的外码值一起修改为新值
        • SET NULL方式： 修改基本关系中元组主码时，将依赖关系中与基本关 系中被修改主码值相对应的外码值置为空值

约束

约束的命名

constraint 约束名 <约束条件>   

• Sno char(4) constraint s_pk primary key
  age smallint constraint age_val
    check(age>=15 and age<=25)
  

约束的撤销和添加

• alter...drop...

  alter...add...

域约束的创建、添加与撤销

• create domain age_domain smallint
    constraint dc_age check(value<=25 and value >=15)
  

• alter domain age_domain
    add constraint dc_age check(value<=35 and value>=15)
  

• alter domain age_domain
    drop constrain dc_age
  

事务中对完整性约束的违反

• 事务中某一部会暂时违反完整性约束
  • 默认约束时立即检查
• 延迟约束检查
  • 约束声明中加入initially deffered子句，在事务结束时检查。
  • 一个约束可以被指定位deferrable可延迟的。对于可延迟的约束,执行 set constraints constrain-list deferred 命令作为事 务一部分,延迟到事务结束时检查。
• 许多数据库不支持延迟约束

复杂check条件与断言

• 全局约束（复杂check条件，check子句中使用子查询）

  • 全局约束设计多个属性间的或多个关系间的联系

    create table sc
    (
        sno char(4),
        cno char(4),
        grade smallint,
        primary key(sno,cno),
        check(sno in (select sno from s)),
        check(cno in (select cno from c))
    )
    

  • 复杂的check'检测开销可能很大

• 断言（assertion）是一个谓词，表达一个条件。域约束和参照完整性约束就是断言的特殊形式。

• 大多数数据库实现不支持以上两种用法

断言

• 定义：create assertion <断言名> check <条件>

  断言是谓词，表达数据库总应该满足的条件

• 一旦定义了断言，系统验证其有效性，并且对每个可能违反该断言的更新操作都进行检查。

• 这种检查会带来巨大的系统负载，因此应该谨慎使用断言

• 对断言“所有X, P(X)”，是通过检查“not exists X， P(X)”来实现的

• create assertion ASSE2 check (not exists (select * from SC where Cno in (select Cno fromC where TEACHER = ‘张’)
  and Sno in (select Sno from S where SEX = ‘M’)))
  

5.sql的数据类型与模式

默认值

• name varchar(20) default ‘张三’

sql中的日期和时间类型

• date:’2001-04-25’

• time:’09:30:00’

• timestamp:’2001-04-25 09:29:01.45’

• 字符串与日期时间类型转换：

  cast string to t

• 从日期，时间中提取单独的域

  extract(field from d);

  field可以是year,month,day,hour,minute,second

  时区中的信息用timezone_hour,timezone_minute

• 获取当前日期、时间函数：

  • current_date
  • current_time
  • local_time
  • current_timestamp,local_timestamp

• 运算结果：interval类型

大对象类型

• SQL提供字符数据的大对象数据类型clob和二进制数据的大对象数据类型blob
  • bookview clob(10KB)
  • image blob(10MB)
  • movie blob(2GB)

用户定义类型

• SQL提供独特类型（distinct type）SQL:1999
  • create type Dollars as numeric(12,2) final
  • 一些数据库实现忽略final
• create type和create domain还没有被大多数数据库实现完全支持

创建索引

• 在关系的属性上创建索引，可以提高查询速度。

• sql没有给出创建索引的正式语法定义

• 很多数据库支持以下语法

  create index studentID_indexon student(ID)
  

模式、目录与环境

• 当代数据库系统提供三层结构的关系命名机制
  • 目录catalog
  • 模式schema
  • 关系、视图对象 catalog5.univ_schema.course
• 用户连接到数据库（验证身份）后，有一个默认 的目录和模式。默认目录和默认模式可以省略
• 每个数据库连接会建立SQL环境:包括目录、模式 和用户授权标识。
• 大多数数据库系统，环境随用户账户创建而自动创建， 此时模式名被置为用户账户名
• 用create schema和drop schema语句创建、删除模式

6.授权

• 安全性控制定义
  • 安全性控制保护数据库以防止不合法的使用所造成的数据泄露和破坏。其基本措施是存取控制。
• 安全性措施
  • 物理级
  • 人际级
  • 操作系统级
  • 网络级
  • 数据库系统级

数据控制功能

• 数据权限：数据的读取、插入、更新、删除
• 模式权限：创建、修改或删除关系
• 权限的授权、转授和回收
  • 允许用户把已获得的权限转授给其他用户，也可以把已授给其他用户的权限再回收上来

权限的授予

• 授权命令：

  grant <权限列表> on <表名|视图名> to <用户/角色列表|public> [with grant option]

  • 权限列表包括：select, update, insert, delete, index, alter, drop, resource以及它们的总和all，其中对select，update，insert可指定属性列表

  • with grant option表示获得权限的用户可以把权限再授予其它用户。

  • 例：

    grant update(budget) on department to Amy;
    

权限的收回

• 回收命令：

  revoke 表级权限 on {表名|视图名} from {用户,...|public}

• 收回权限时，若该用户已将权限授予其他用户，则也一并收回。授权路径的起点一定是DBA

• 例：

  revoke insert on S from Amy
  

角色

• 创建角色

  create role Instructor;

• 给角色授权

  grant select on takes to Instructor;

• 角色可以授予用户或其他角色：

  grant Instructor to Amy

  create role Dean; 
  grant Instructor to Dean; 
  grant Dean to Alice;
  

• 基于角色的授权概念并没有在SQL中指定。

视图的授权

• 视图的访问会转换成实际关系的访问，所以创建视图的用户在视图上的权限，不会超越在实际关系上的权限。
• SQL2003：如果函数定义有sql security invoker子句，那么它就在调用该函数的用户权限下执行，而不是在函数定义者的权限下运行。

模式的授权

• 模式的基本授权：模式的拥有者才拥有对模式的修改权限。如：增、删关系，增、删关系的属性， 以及增、删索引。

• references权限，允许用户在创建关系时声明外码。此权限可以授予到指定属性上

  grant references(dept_name) on department to Amy

  允许Amy创建关系能参照department的dept_name

• 因为外码会限制其他用户对被参照关系将来的行为，所以需要授权。

权限的转移

• 获得了某些形式授权的用户可能被允许传递给其他用户

授权图

• authorization graph
• 结点时用户，根结点是DBA，有向边U_i→U_j,表示用户U_i把某权限授予给用户U_j

• 用户具有权限的充分必要条件是：当且仅当存在从根结点到该用户结点的路径

  
  
  授权图.png

• 级联收回（默认）：收回权限时，若该用户已将权限授予其他用户，则也一并收回

• 防止权限级联收回：

  revoke select on department from Amy restrict ;

  如果存在任何级联收回，返回错误，不执行任何收权动作。

  • 仅仅收回grant oprtion ，保留其他权限

    revoke grant option for select on department from Amy;

    一些数据库权限不支持上述语法。变通方法：收回权限；然后不带grant option重新授权。

  • 通过角色授权，避免用户授权的级联收回

    • 设置会话的当前角色为已定义角色
    • 由当前角色授权，授权语句后加：grant by current_role

数据库级权限授权

• 支持多库的数据库系统中授权对象可以是数据库

  grant 数据库权限 to {用户,...|public}

  • 数据库权限包括：

    • connect：允许用户在database语句中指定数据库

    • resource：connect权限+建表、删除表及索引权限

    • dba：resource权限+授予或撤销其他用户的connect，resource，dba权限

      不允许dba撤销自己的dba权限。