PostgreSQL 表数据的回滚 plsql哪个是回滚

Oracle Database-数据处理和表的管理部分

*SQL的类型

• DML（Data Manipulation Language 数据操作语言）:select/insert/update/delete
• DDL（Data Definition Language 数据定义语言）:create table/alter table/truncate table/drop table

• create/drop view/sequnece/index/synonym(同义词)

• DCL（Data Control Language 数据控制语言）:grant(授权)/revoke(撤销权限)

DML语句（增/删/改）

INSERT语句

用于插入数据

• 为每一列添加一个新值
• 按列的默认顺序列出各个列的值
• 在INSERT 子句中随意列出列名和他们的值
• 字符和日期型数据应包含在单引号中

语法

使用这种语法一次只能向表中插入一条数据

示例：

插入空值

使用地址符来占位具体值

类似JDBC的PreparedStatement，用占位符"?"来表示以后将要插入的值的位置

在Oracle中可以使用"&"符号来指定列值，位置放在VALUES子句中

通常使用与列名一样的地址符后缀，如：id——&id

地址符几乎可以使用在所有SQL语句中，不仅仅是插入语句

如：

从其他表中拷贝数据

语法是在INSERT语句后加入子查询

• 不需要书写VALUES子句
• 子查询中的值列表应该与INSERT子句中要被插入的列名相对应
• 这种方式可以一次插入多条数据

当需要插入海量数据时，这种方法效率很低，通常使用以下的方法来解决

海量插入数据

• Oracle提供了数据泵（PLSQL程序）：dbms_datapump（程序包）
• SQL*Loader
• 外部表

UPDATE语句

语法

可以一次更新多条数据

一般使用WHERE子句来确定需要更新哪些数据

在UPDATE语句中使用子查询

更新操作中的数据完整性错误

约束

DELETE语句

语法

可以一次删除多条数据

使用WHERE子句来确定需要更新哪些数据

删除操作中的数据完整性错误

关于DELETE和TRUNCATE的异同

• 都是删除表中的数据
• delete是逐条删除，truncate是先摧毁表再重建
• delete速度更快，truncate速度比delete慢

• 原因：Oracle的undo数据功能

• delete是DML（数据操作语言）语句，truncate是DDL（数据定义语言）语句
• Delete操作可以回滚rollback
• Delete操作可以闪回（Oracle特有，再事务提交后可以闪回）

• 在Oracle中，大部分操作都可以闪回，即大部分操作都是可逆的

• Delete操作可能产生碎片，并且不释放空间

• 关于碎片
• 碎片使表的数据条目之间不连续，影响查询的速度
• 清理碎片的方法：

注意：插入、更新和删除操作会引起数据的的变化，必须考虑数据的完整性

Oracle中的事务

Oracle中事务的概念

数据库事务的组成

• 一个或多个DML语句
• 一个DDL（Data Definition Language – 数据定义语言）语句
• 一个DCL（Data Control Language – 数据控制语言）语句

数据库事务的执行流程

• 以第一个DML语句的执行作为开始
• 以下面其中一种作为结束

• 显示结束：commit rollback
• 隐式结束（自动提交）：DDL语言、DCL语言、exit(事务正常退出)

• DDL语句附带提交此前事务的操作

• 隐式回滚（系统异常终了）：关闭窗口、死机或掉电等

COMMIT和ROLLBACK语句

使用COMMIT和ROLLBACK语句可以

• 确保数据完整性
• 数据改变被提交之前预览
• 将逻辑上相关的操作分组

通过SAVEPOINT控制事务

• 使用SAVEPOINT语句可以在当前事务中创建保存点。
• 使用ROLLBACK TO SAVEPOINT语句回滚到创建的保存点
• 使用SAVEPOINT来回滚可以避免出现小错误而回滚整个事务

数据库事务的隔离级别

没有采取必要的隔离机制，就会导致各种并发问题:

• 脏读：对于两个事物T1，T2，T1读取了已经被T2更新但还没有被提交的字段。之后，若T2 回滚，T1读取的内容就是临时且无效的
• 不可重复读：对于两个事物T1，T2，T1读取了一个字段,然后T2更新了该字段。之后，T1再次读取同一个字段，值就不同了
• 幻读：对于两个事物T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行

数据库事务的隔离性

力，使它们不会相互影响，避免各种并发问题 一个事务与其他事务隔离的程度称为隔离级别

隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致 性就越好，但并发性越弱

数据库提供的4种事务隔离级别

Oracle支持的两种事务隔离级别（或三种）

• Oracle支持2种事务隔离级别：READ COMMITED和SERIALIZABLE（或加上READ ONLY只读共三种)
• Oracle默认的事务隔离级别为：READ COMMITED

Mysql支持4种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ

DDL语句（管理及操作表）

常见的数据库对象

表空间

表空间是Oracle数据库的逻辑单元

数据库——表空间

一个表空间可以于多个数据文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户，一个用户下可以建立多张表

建立表空间的语句

用户

创建用户的语句

为用户赋权限语句

新创建的用户没有任何权限，登陆后会提示

角色

Oracle中存在三个主要角色

• connect角色
• resource角色
• dba角色

分别有以下权限

赋权语法

创建表

CREATE TABLE语句

*前提：

• 用户需要具备CREATE TABLE权限
• 有足够的存储空间

语法

表名， 列名及其 数据类型和 数据类型的大小

示例：

DEFAULT VALUE-默认值

执行insert操作时，可以为其指定默认值

语法：

• 值、表达式和SQL语句都可以作为默认值
• 其他的列名或者是伪列都是非法的
• 默认值的类型必须和该列的类型一致

利用select语句创建表

select语句查询的结果相当于一张表，我们可以将这个查询结果变成一张表，即创建一张存储select语句查询结果的表

这样就可以很方便的创建新的表了

as关键字

示例：

拷贝现有的表结构（创建与现有表的结构相同的表）

利用上述通过select语句创建查询结果对应的表的功能：

create table 新表 as select * from where 1=2 ;

将会拷贝表但不拷贝表中的数据

同理可以在where条件中加上筛选条件拷贝需要的表数据

列的数据类型

关于ROWID（行地址）：

• 是一串字符串
• 使用ROWID可以找到这一行

*表及列名的命名规则

• 必须以字母开头
• 必须在1–30 个字符之间
• 必须只能包含A–Z、a–z、0–9、_、$、和#
• 必须不能和用户定义的其他对象重名
• 必须不能是Oracle的保留字
• Oracle默认存储是都存为大写
• 数据库名只能是1~8位，datalink可以是128位，和其他一些特殊字符

*使用子查询创建表

使用AS subquery选项，将创建表与插入数据结合

语法：

要求：

• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列

示例：

修改表

ALTER TABLE语句

使用ALTER TABLE语句可以

• 追加新的列
• 修改现有的列
• 删除一个列

语法

列的追加/修改/删除/重命名

追加列的示例：

修改一个列的示例

可以修改列的数据类型，数据类型大小和默认值

对默认值的修改只影响 今后对表的修改，此前的数据不影响

删除一个列示例

删除表

DROP TABLE语句

使用DROP TABLE语句

• 数据和结构都被删除
• 所有正在运行的相关事物被提交
• 所有相关索引被删除
• DROP TABLE 语句不能回滚，但是可以闪回

语法

DROP TABLE不是将表彻底删除，只是将表放到RECYCLEBIN（回收站）中

可以通过加双引号的方法查出已删除的表

回收站可以被清空

注意：回收站并不是一定存在的，某些用户没有回收站（如sys-管理员用户），要注意

purge关键字

可以跳过回收站彻底删除

从回收站恢复已删除的表

有多种方式，如闪回删除：

七种方式：

其中闪回数据归档Oracle11G才有，其余六种11G和10G均有

详细自查

重命名对象

RENAME语句

名称

前提：必须是对象的拥有者

语法

清空表

TRUNCATE TABLE语句

使用TRUNCATE TABLE语句将

• 删除表中所有的数据
• 释放表的存储空间

语法

注意：

• TRUNCATE语句不能回滚
• 可以使用DELETE语句代替TRUNCATE语句删除数据

约束

约束的概念

• 约束是表一级的限制
• 如果存在依赖关系，约束可以防止错误的删除数据
• 约束的类型

• NOT NULL-非空约束
• UNIQUE-唯一约束
• PRIMARY KEY-主键约束
• FOREIGN KEY-外键约束
• CHECK-检查性约束

约束级别

在Oracle中有两种约束级别

• 列级约束：如果没有特别指明，一般都是列级约束
• 表级约束

• 典型：联合主键

约束规则

• 用户可以自定义约束，也可以使用OracleServer的sys_cn格式命名约束
• 约束创建的时机：

• 创建表的时候，同时创建约束
• 表结构创建完成后

• 约束可以定义在列一级，或者是表一级
• 通过数据字典查看约束

约束的类型

NOT NULL-非空约束

保证该列的值不为空

UNIQUE-唯一性约束

保证该列的值唯一

PRIMARY KEY-主键约束

索引，关于原理见下文索引部分

约束列的值：

• 不为空
• 唯一

FOREIGN KEY-外键约束

与主表的主键有约束

• FOREIGN KEY: 在子表中，定义了一个表级的约束
• REFERENCES: 指定表和父表中的列
• ON DELETE CASCADE: 当删除父表时，级联删除子表记录（谨慎使用，比较危险）
• ON DELETE SET NULL: 将子表的相关依赖记录的外键值置为null，此时子表这个记录与父表没有联系了

子表的外键必须是父表的主键

check-检查性约束

• 定义每一行记录所必须满足的条件

• 如工资应该是正数（>0）等

• 下面的表达式可以使用在check约束中:

• 引用CURRVAL、NEXTVAL、LEVEL、和ROWNUM
• 调用SYSDATE、UID、USER和USERENV函数
• 另一个表的查询记录

在定义检查性约束的时候最好定义这个检查性约束的名称，当不满足约束时回显这个名称告知约束条件，上图emp_salary_min就是这个约束的名称

小结

建表示例：

常见的数据库对象

视图

• 视图是一种虚表
• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表

• 视图本身是不存放数据的，数据来自基表

• 向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
• 视图向用户提供基表数据的另一种表现形式

视图的优点

创建视图-基本

权限

一般来讲普通用户没有创建视图的权限，需要被管理员授权

语法

• FORCE：子查询不一定存在
• NOFORCE：子查询存在（默认）
• CHECK OPTION：只操作视图对应的数据，若操作视图不存在的数据则报错

• 如：视图为10号部门的员工视图，就不可以插入部门号为其他的员工条目了
• “只能操作看得到的”
• 不建议通过视图对表进行操作

• WITH READ ONLY：只能做查询操作

子查询可以是复杂的SELECT语句

示例：

显示视图结构：使用

示例2：创建视图时在子查询为列定义别名

之后应该在选择视图中的列时使用别名

查询视图

语法

简单视图和复杂视图

替换（相当于修改）视图

使用CREATE OR REPLACE VIEW子句修改视图

创建或者替换

列相对应

创建复杂视图

复杂视图举例：查询各个部门的最低工资，最高工资，平均工资

视图中使用DML的规定

表 进行操作，原因就是使用DML的规定太多了

可以在简单视图中执行DML操作

当视图定义中包含以下元素之一时不能使用delete

• 组函数
• GROUP BY子句
• DISTINCT（去重）关键字
• ROWNUM伪列

当视图定义中包含以下元素之一时不能使用update

• 组函数
• GROUP BY子句
• DISTINCT关键字
• ROWNUM伪列
• 列的定义为表达式

当视图定义中包含以下元素之一时不能使用insert

• 组函数
• GROUP BY子句
• DISTINCT关键字
• ROWNUM伪列
• 列的定义为表达式
• 表中非空的列在视图定义中未包括

屏蔽DML操作

• 可以使用WITH READ ONLY 选项屏蔽对视图的DML操作
• 任何DML操作都会返回一个Oracle server错误

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据

序列

多个用户用来产生 唯一数值的数据库对 象

• 自动提供唯一的数值
• 共享对象
• 主要用于提供主键值
• 将序列值装入内存中可以提高访问效率
• *创建序列就相当于创建一个数组

Oracle中的序列从功能上来说相当于MySQL中的auto_increment一样

创建序列

创建序列就相当于创建一个数组，数组长度默认为20

语法

• INCREMENT BY n：定义自增量（步长），默认为1
• START WITH n：定义起始值，默认为1
• MAXVALUE n / MINVALUE n | NOMAXVALUE / NOMINVALUE：最大值/最小值 | 无最大值/无最小值，默认为无最大最小值
• CYCLE | NOCYCLE：是否循环（会产生重复的值），默认为NOCYCLE
• CACHE n | NOCACHE：缓存长度（相当于这个数组的长度），默认为20 | 无缓存，即长度为1

示例：

• 创建序列DEPT_DEPTID_SEQ为表DEPARTMENTS提供主键
• 自增量为10
• 起始值为120
• 默认无最小值
• 最大值为9999
• 不循环
• 不定义缓存

查询序列

示例

列定义信息

LAST_NUMBER显示序列中下一个有效的值

NEXTVAL和CURRVAL伪列（相当于操作数组的指针）

这个指针默认是在第1个元素的前面（即CURRVAL此时无意义）

• NEXTVAL返回序列中下一个有效的值，任何用户都可以引用
• CURRVAL中存放序列的当前值
• NEXTVAL应在CURRVAL之前指定，二者应同时有效

序列的应用示例

序列的特点

• 将序列值装入内存可提高访问效率
• 序列在下列情况下出现裂缝（不连续了）

• 回滚
• 系统异常
• 多个表同时使用同一序列

• 如果不将序列的值装入内存(NOCACHE)，可使用表USER_SEQUENCES查看序列当前的有效值

修改序列

示例

否装入内存

修改序列的注意事项

• 修改序列的用户必须是序列的拥有者或对序列有ALTER权限
• 只有将来的序列值会被改变
• 改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列

• 使用DROP SEQUENCE语句删除序列
• 删除之后，序列不能再次被引用

示例：

索引

索引是用于加速数据存取的数据对象，合理地使用索引可以大大降低I/O次数，从而提高数据访问性能

• 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
• 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
• 索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引，用户不用在查询语句中指定使用哪个索引
• 在删除一个表时，所有基于该表的索引会自动被删除
• 通过指针加速Oracle服务器的查询速度
• 通过快速定位数据的方法，减少磁盘I/O
• “类似一本书的目录”

创建索引

索引的类型

• 单列索引：基于单个列所建立的索引

• 如CREATE index 索引名 on 表名（列名）

• 复合索引(多级索引）：基于两个或多个列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同

• 如：CREATE index emp_idx1 on emp(ename,job);和CREATE index emp_idx2 on emp(job,ename);

索引底层原理类型

• B树（默认）
• 位图

自动创建

束后系统自动在相应的列上创建唯一性索引

手动创建

以加速查询

示例

执行计划（效率对比-不创建索引和创建索引）

Cost（%CPU）越低越好

创建索引的时机

以下情况可以创建索引

• 列中数据值分布范围很广
• 列经常在WHERE子句或连接条件中出现
• 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%

以下情况不应该创建索引

• 表很小
• 列不经常作为连接条件或出现在WHERE子句中
• 查询的数据大于2%到4%
• 表经常更新（经常更新索引对应的列）

查询索引

示例

USER_IND_COLUMNS查看索引的信息

删除索引

使用DROP INDEX命令删除索引

删除索引UPPER_LAST_NAME_IDX

只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引

同义词（别名）

使用同义词访问相同的对象

• 方便访问其它用户的对象
• 缩短对象名字的长度

语法

有否PUBLIC关键字表示公有还是私有同义词（对于当前用户）

创建和删除同义词

需要权限

创建同义词

删除同义词