04中级SQL

最新推荐文章于 2024-04-08 08:00:00 发布

原创最新推荐文章于 2024-04-08 08:00:00 发布 · 368 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

Database 专栏收录该内容

19 篇文章

订阅专栏

连接条件

select A1, A2, … , Am
from r1 join r2 on r1.A1 = r2.A1
where P;

on比using更通用，可以表示任何SQL谓词

优点

在外连接中，on条件的表现和where条件是不同的
在on子句中指定连接条件，在where子句中出现其余的条件，这样更让人读懂

外连接

select A1, A2, … , Am
from r1 left outer join r2 …  left outer join rn
where P;

select A1, A2, … , Am
from r1 right outer join r2 …  left outer join rn
where P;

select A1, A2, … , Am
from r1 full outer join r2 …  left outer join rn
where P;

外连接通过在结果中创建包含空值元组的方式，保留了在连接中丢失的元组
左外连接left outer join只保留出现在左外连接运算之前的关系中的元组
右外连接right outer join只保留出现在右外连接运算之前的关系中的元组
全外连接full outer join只保留出现在两个关系中的元组
on和where在外连接中表现是不同的，原因是外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果，而这样的元组会被where子句谓词排除掉

内连接

select A1, A2, … , Am
from r1 inner join r2 …  inner join rn
where P;

常规连接，inner是可选的，当没有outer前缀，默认是inner

连接

需要两个关系，并返回另一个关系
- 通常在from子句中用作子查询表达式
- 连接条件：定义两个关系中哪些元组匹配，以及连接结果中存在哪些属性
- 连接类型：定义如何处理每个关系中与其他关系中任何元组不匹配的元组（基于连接条件）

视图（view）

任何不是概念模型但作为“虚拟关系”对用户可见的关系称为视图
- 视图提供了从某些用户的视图中隐藏某些数据的机制
- 视图关系在概念上包括查询结果中的元组，但并不进行预计算和存储。当我们定义一个视图时，数据库系统存储视图的定义本身，而不存储该视图的查询表达式的执行结果。
- 无论何时执行这个查询，视图关系都被重新计算

物化视图（materialized view）

在定义视图的查询的结果中创建包含所有元组的物理表
保持物化视图一致在最新状态的过程称为物化视图维护，简称视图维护。当构成视图定义的人和关系被更新时，可以马上进行视图维护

创建视图

create view v as <query expression>;

视图更新

插入insert、删除delete、更新update和关系一样
除了一些有限的情况之外，一般不允许对视图关系进行修改
如果定义视图的查询对下列条件都满足，就成SQL视图是可更新的
from子句中只有一个数据库关系
select子句中只包含关系的属性名，不包含任何表达式、聚集、distinct
任何没有出现在select子句中的属性可以取空值
查询中不含有group by或having子句
可以在视图定义的末尾包含with check option子句，在插入、修改不满足条件时会被拒绝

事务（transaction）

工作的单元，由查询或更新语句的序列组成
- ACID性质
- 原子性(Atomicity)
- 事务的操作在数据库中要么全部反映，要么完全不反映
- 由恢复系统的组件处理
- 一致性(Consistency)
- 隔离执行事务时，保持数据库的一致性
- 确保单个事务的一致性是编写该事务的程序员的责任
- 完整性约束的自动检查给这项工作带来了便利
- 隔离性(Isolation)
- 每个事务都感觉不到系统中有其他的事务并发执行
- 由并发控制系统的部件处理
- 持久性(Durability)
- 事务成功完成后，对数据库的改变必须是永久的，即使出现系统故障
- 由恢复系统的组件处理
- 事务隐式开始，显式结束

结束事务

commit work：提交当前事务，将该事务所做的更新在数据库中持久保存。在事务被提交后，一个新的事务自动开始
rollback work：回滚当前事务，撤销该事务中所有SQL语句对数据库的更新
一旦失误执行了commit work，就不能用rollback work来撤销

在断电、系统崩溃这些故障情况下，一个失误没有完成commit work，其影响将被回滚
默认方式下每个SQL语句自成一个失误，且一执行完就提交。可以使用begin atomic … end 来构成单一事务

完整性约束（Integrity Constraints）

完整性约束保证授权用户对数据库所做的修改不会破坏数据的一致性，防止对数据的意外破坏
一个完整性约束可以时属于数据库的任意谓词，但是检测任意谓词代价太高。因此大多数数据库系统允许用户指定那些只需要极小开销就可以检测的完整性约束
完整性约束可以在创建关系的create table命令的一部分被声明，也可以在alter table <table name> add <constraint>来施加约束

单个关系上的约束

not null
sql A1 varchar(20) not null
primary key
sql primary key ( A1, A2, ..., Am)
unique
sql unique ( A1, A2, ..., Am)
check <predicate>
sql check A1 in (s1, s2… , sn)

参照完整性（referential integrity）

一个关系中给定属性集上的取值在另一关系的特定属性集的取值中出现
参照完整性约束/子集依赖
- r2中A的取值集合必须是r1中B上的取值集合的子集
- 与外码约束不同，r1中B取值可能是相同的
foreign key
```
foreign key <A1> reference r2
on delete set null
on update cascade
```
- on delete cascade表示如果删除r2中的元组导致完整性约束被违反，对r1关系作级联删除
- on delete set null表示如果更新r2中的元组导致完整性约束被违反，对r1关系中A1设置为null
- 默认方式是立即检查约束，可以将initially deferred加入到约束声明或者执行set constraints <constraint> deffered指明在事务结束后检查约束条件
断言（assertion）
```
create assertion <assertion-name> check <predicate>;
```
- 一个断言就是一个谓词，表达了我们希望数据库总能满足的一个条件。域约束和参照完整性约束是断言的特殊形式。

SQL时间和日期类型

Date
date ‘2005-7-27’
Time
time ‘09:00:30’
Timestamp
timestamp ‘2005-7-27 09:00:30.75’
Interval
interval ‘1’ day

索引（index）

在关系的属性创建的索引是一种数据结构，允许数据库系统高效地找到关系中那些在索引属性上取给定值的元组，而不用扫描关系中的所有元组。如果用户提交的SQL查询可以从索引的使用中获益，那么SQL查询处理器就会自动使用索引

create index <index-name> on r1(A1)

用户定义的类型

独特类型
用户定义的域

独特类型

create type <type-name> as <old-type-name> final;

有强制类型检查，进行运算时需要使用case <type-name> to <new-type-name>转换到另外一个域，运算之后再转换回来
结构化数据结构

用户定义的域

create domain <domain-name> as <old-type-name> not null;

无强制类型检查
可以声明约束、设置默认值

类型和域的不同

域上可以声明约束，也可以为域类型变量定义默认值，用户定义类型不能声明约束和默认值
域不是强类型的，一个域类型的值可以赋给另外一个域，只要它们的基本类型是相容的

创建模式相同的表

create table <new-table> like <old-table>;

create table <new-table> as <query expression> with data;

如果省略with data表会创建，但是不会载入数据

授权（authorization）

授权类型

Read
Insert
Update
Delete

授予

grant <privilege-list> on <relation-name or view-name> to <user list>;

标准权限包括select，insert，update，delete。all privileges可以表示所有权限。references权限允许用户在创建关系时声明
用户名public用来指系统当前用户和将来用户
创建视图的用户不需要获得该视图上的所有权限
如果在grant后面附加with grant option，则允许用户/角色奖得到的权限再授予其他用户/角色

收回

revoke <privilege-list> on <relation-name or view-name> to <user list>;

默认是级联收回，在revoke后面附加restrict表示禁止级联收回

角色

表示某一类用户

创建

create role <role-name>;

授予

grant <role-name> to <role-name or user-name>

一个用户或角色的权限包括
所有直接授予用户/角色的权限
所有授予给用户/角色所拥有角色的权限