【笔记】数据库--中级SQL

中级SQL

• 连接表达式
• 视图
• 事务
• 完整性约束
• SQL的数据类型与模式
• 授权

---

连接表达式

• 连接条件：on
• 外连接：
  左外连接：left outer join
  右外连接：right outer join
  全外连接：full outer join
• 连接类型和条件
  内连接：inner //SQL常规连接，join子句中没有outer前缀，默认连接类型为inner join

视图

任何不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图。

• 视图定义：create view view_name as < query expression >; //<>内为合法的查询表达式。
  例：

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

• SQL查询中使用视图
  视图的属性名可以显式指定：

  **create view** departments_total_salary( dept_name, total_salary ) **as**  **select** dept_name, sum( salary )  **from** instructor  **group by** dept_name;

  注：当我们定义视图时，数据库系统寻出视图的定义本身，二部存储定义改视图的查询表达式的执行结果。即，无论我们何时执行这个查询，视图关系都被重新计算。
  一个视图可能被用到另一个视图的表达式中。

• 物化视图：特定数据库允许存储视图关系，但，如果用于定义视图的实际关系改变，视图也跟着修改。
  物化视图维护：保持物化视图一直在最新状态的过程。//简称 视图维护
• 视图更新：除了一些有限的情况之外，一般不允许对视图关系进行修改。
  可更新的(updatable)SQL视图需满足：
  
  • from子句中只有一个数据库关系；
  • select子句中只包含关系的属性名，不包含任何表达式、聚集货distinct声明；
  • 任何没有在select子句中的属性可以取空值(null)//无not null约束，也不构成主码的一部分；
  • 查询中不含有group by 或having子句。

事务

由查询和(或)更新语句的序列组成。SQL标准规定当一条SQL语句被执行，就隐式地开始了一个事务。
- Commit work：提交当前事务。//将改事务所作的更新在数据库中持久保存；事务被提交后，一个新的事务自动开始。
- Rollback work：回滚当前事务。//撤销该事务中所有SQL语句对数据库的更新。

完整性约束

保证授权用户对数据库所做的修改不会破坏数据的一致性。
即，防止对数据的意外破坏。

• 单个关系上的约束
  
  • not null
  • unique
  • check(< 谓词 >)
• not null约束
  禁止空值。
  例：通过限定属性name和budget的域来排除空值：

name **varchar(20) not null** budget **numeric( 12, 2 ) not null**

/* not null 声明禁止在该属性上插入空值，主码不需显式声明not null */

• uniqu
  unique (A1，A2，..., An );
  A1，A2…形成一个候选码；
  即，在关系中没有两个元组（表中的每行为一个元组）能在所有列出的属性上取值相同。
  但是候选码属性可以为null，除非已被显式声明为 not nul(空值不等于其他任何值)。

• check子句
  当用于关系声明时，check(P)子句指定一个谓词P，关系中的每个元组都必须满足谓词P。
  例： 创建”Persons” 表，且 “Id_P” 列必须只包含大于 0 的整数。

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CHECK (Id_P>0))

例：check( semester in ( ‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’ ) );
//check子句模拟了一个枚举类型，指定semester必须是’Fall’, ‘Winter’, ‘Spring’, ‘Summer’ 中的一个。

• 参照完整性

  • 保证在一个关系中给定属性集上的取值也在另一关系的特定属性集的取值中出现。
    例： foreign key( dept_name ) references department
    表示：在每个课程元组中指定的系名必须在department关系中存在，若没有这个约束，就可能会为一门课程指定一个不存在的系名。
  • foreign key是 外键（所在的表相当于从表），是从别的表（主表）中引用过来的，从表里面的数据必须是从主表中引用过来的，即从表里面的数据主表里面必须有记录。
  • 主要目的是为了保证数据的完整性。
  • on delete cascade
    如果删除department中的 元组导致了次参照完整性约束被违反，则删除并不被系统拒绝，而是对凑额色关系作“级联”删除，即删除参照了被删除系的元组。
  • on update cascade
    如果更新被参照字段时违反了约束，则更新操作并不被系统拒绝，而是将course中参照的元组的dept_name字段也改为新值。

• 事务中对完整性约束的违反
  由立即检查造成的暂时性违反完整性约束。
  initially deferred：在事务结束时进行完整性约束检查。
  例：
  set constraints constraint-list deferred
  //对指定约束的检查被延迟到该事务结束时执行。

• 复杂check条件与断言：？？？

SQL数据类型与模式

• SQL中的日期和时间类型

• 默认值
  例：
  create table student(
  ID archer (5),
  name carchar(20) not null,
  dept_name varchar(20),
  tot_cred numeric( 3, 0 ) default 0,
  primary key ( ID ) );
  tot_cred属性的默认值被声明为0。

• 创建索引

  • 之后的章节会讲。

• 大对象类型

• 用户定义的类型

  • 独特类型
    例：
    create type Dollars as numeric（ 12， 2 ）final；
    用户定义类型Dollars订一位总共12位数字的十进制数，其中两位放在十进制小数点后。
    在此关键字final并不是真的有意义，SQL：1999标准要求的。
    例：
    create table department(
    dept_name varchar(20),
    building varchar(15),
    budget Dollars );

  • drop type／alter type：删除／修改以前创建过的类型。

  • 结构化数据类型：后面的章节。

• create table 的扩展

  • create table like：创建一个与现有某个表具有相同模式的新表temp_instructor。
    例：
    create table temp_instructor like instructor;

• 模式、目录与环境

• 授权
  读取数据、插入新数据、更新数据、删除数据。

  • 权限( privilege )的授予与收回

  • 授予grant：
    grant < 权限列表 > //select insert update delete
    on < 关系名或视图名 >
    to < 用户 /角色列表 >;
    例：授予数据库用户Amit和Satoshi在department关系上的select权限：
    grant select on department to Amit, Satoshi；

  • 收回revoke
    revoke < 权限列表 > //select insert update delete
    on < 关系名或视图名 >
    to < 用户 /角色列表 >;
    例：收回数据库用户Amit和Satoshi在department关系上的budget属性上的更新权限：
    revoke update ( budget ) on department from Amit, Satoshi；

• 角色

  • 创建角色： create role instructor;
  • 授予权限：
    grant select on takes
    to instructor;
  • 用户或角色的权限：
    所有直接授予用户／角色的权限；
    所有授予给用户／角色所拥有角色的权限。

• 视图的授权

• 模式的授权
• 权限的转移
• 权限的收回