Oracle进阶总结（3）

Oracle总结

数据操作语言（DML）

设计操作有语句的增，删，改，合并
添加语句

INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

删除语句

DELETE [FROM] table [WHERE condition];

修改语句

UPDATE table SET column = value [, column = value] [WHERE condition];

事务处理语言（TPL）

事务

也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。

事务组成

–在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）语句。

事务特征

原子性（Atomicity）
•事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
一致性（Consistency）
•一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
隔离性（Isolation）
•一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
持久性（Durability）
•也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

事务结束

提交(COMMIT)：使用COMMIT命令实现，以成功的方式结束事务，组成事务的DML语句操作全部生效。
回滚(ROLLBACK)：使用ROLLBACK命令实现，以失败的方式结束事务，组成事务的DML语句操作全部被取消。

事务开启

事务自动开启于上一个事务结束后，执行的第一个DML语句。

提交或回滚前数据状态

–数据变化前的状态可以被恢复;
–当前会话可以使用SELECT语句来验证DML操作后的结果;
–其它会话不能查看由当前用户的DML操作结果;
–受影响记录被锁定，也就是其它用户不能改变受影响记录中的数据;

提交后数据状态

提交(COMMIT) 后的数据状态

–在数据库中数据变化成为永久性的，先前的数据状态永久性的消失；
–所有用户/会话都可以查询到提交COMMIT后的结果；
–锁定的记录被释放，可以有效地被其他用户操作；
–所有的存储节点被清除；

回滚(ROLLBACK)后的数据状态

–先前的数据状态被恢复;
–锁定的记录被释放;
–所有的存储节点被清除;

数据定义语言（DDL）

由CREATE、ALTER与DROP三个语法所组成。

数据库对象

数据库对象，是数据库的组成部分，有表、约束、索引、视图、序列、同义词、触发器、存储过程、函数等。
约束:表是用来存放用户数据的对象，由行和列组成，列就是字段，行就是表中的记录
表:是一种保证数据完整性的规则。约束设置在单个字段或者多个字段组合上，写入这些字段的行数据必须要符合约束的规则。
索引:构建于表的单字段或者字段组合上，用于加速对表中数据的查询
视图:虚表，是一个命名的查询，用于改变基表数据的显示，简化查询。访问方式与表相同，同样可使用查询语句
序列:产生顺序的不重复数字串，被作为主键约束值的参照
同义词:数据库对象的别名

约束类型

NOT NULL:非空约束
UNIQUE :不能重复
CHECK :检查性约束
PRIMARY KEY: 主键约束 每个表只能创建一个主键约束。主键列唯一且不能为空
FOREIGN LEY :外键约束
–外键列的值必须在引用列值的范围内，或者为空；
–外键参照的是列必须是主键或者唯一键；

约束操作

定义,添加,删除 启用或禁用
追加约束

ALTER TABLE table ADD [CONSTRAINT constraint] type (column);

追加外键约束：

ALTER TABLE table ADD [CONSTRAINT constraint] FOREIGN KEY(column) REFERENCES table(column));
例如:
ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY(mgr) REFERENCES emp (empno);

删除约束

ALTER TABLE table DROP PRIMARY KEY|UNIQUE(column) |CONSTRAINT constraint [CASCADE];
例如:
从emp表中删除emp_mgr_fk约束
ALTER TABLE emp DROP CONSTRAINT emp_mgr_fk;
删除dept表上的PRIMARY KEY约束，并且删除相关联的在emp表deptno上的FOREIGN KEY约束
ALTER TABLE dept DROP PRIMARY KEY CASCADE;

视图

创建视图

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW viewname
[(column[, column]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraintname]]
[WITH READ ONLY]
 OR REPLACE：如果所创建的视图已经存在该选项表示修改原视图的定义
 FORCE：不管视图所基于的基表是否存在，都会创建该视图；
 NOFORCE：只有视图所基于的基表都存在，才会创建该视图；
 viewname ：视图的名称；
 column ：列名，列名的数量必须和视图所对应查询语句的列数量相等；
 subquery：一条完整的SELECT语句；
 WITH CHECK OPTION：一个约束条件，通过视图所插入或修改的数据行必须
满足视图所定义的查询； constraintname：约束名；
 WITH READ ONLY：确保在该视图上不能进行任何DML操作；

修改视图
CREATE OR REPLACE VIEW
视图上执行DML操作的要求
• 在简单视图上可以执行DML 操作；
• 您可以通过视图删除基表中数据，只要视图中不出现以下情况：
– Group 函数；
–GROUP BY 子句；
– DISTINCT 关键字；
• 您可以通过视图修改基表中数据，只要视图中不出现以下情况：

–GROUP函数、GROUP BY子句，DISTINCT关键字；
– 使用表达式定义的列；
– ROWNUM 伪列；
您可以通过视图向基表插入数据，只要视图中不出现以下情况：
–GROUP函数、GROUP BY子句，DISTINCT关键字；
– 使用表达式定义的列；
–ROWNUM 伪列；
– 基表中未在视图中选择的其它列定义为非空并且没有默认值；

序列

– 序列是按照一定规则能自动增加/减少数字的一种数据库对象。
– 通常可以使用序列自动地生成主键值。
创建序列

CREATE SEQUENCE [schema.] sequencename
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];

-sequencename:序列对象的名字
– INCREMENT BY n：序列连续两个值之间的间隔n，默认为1。
– START WITH n ：序列起始值n，该项省略，起始值为1
– MAXVALUE n ：序列最大值；NOMAXVALUE ：指定序列无最大值
– MINVALUE n ：序列最小值；NOMINVALUE ：指定序列无最小值
– CYCLE|NOCYCLE：表示序列在达到最大值或最小值之后是否继续产生序
列值，NOCYLE表示不再产生，NOCYLE是默认选项。
– CACHE n|NOCACHE:表示序列值被服务器预先分配并存储在内存中，
NOCACHE表示不预先分配并存储，CACHE 20是默认选项
例如:

例：创建序列test_seq，起始值为10，每次增长
2，最大值100，最小值9，循环序列，每次缓存10
CREATE SEQUENCE test_seq
START WITH 10 --序列从10开始
INCREMENT BY 2 --序列每次增加2
MAXVALUE 100 --序列最大值100
MINVALUE 9 --序列最小值9
CYCLE --序列循环,每次增加2,一直到100后回到9从新开始
CACHE 10； --缓存中序列值个数为10

序列属性
NEXTVAL和CURRVAL伪列
– CURRVAL：表示序列返回的当前值；
– NEXTVAL：表示序列返回的下一个值；
– CURRVAL在被引用之前，必须先使用NEXTVAL来产生一个序
列值；
– 可用语句序列名.CURRVAL或序列名.NEXTVAL来访问序列;
使用序列

–创建序列student_seq：
CREATE SEQUENCE student_seq
START WITH 10000
使用序列t d t 生成t d t表中id列插入值
INCREMENT BY 1;
– student_seqstudentsid：
INSERT INTO student
VALUES student seq NEXTVAL 'Scott' Science' 查看student 序列当前值：
(student_seq.NEXTVAL, Scott', 'Computer Science', 11);
– student_seqSELECT student_seq.CURRVAL FROM dual;

修改序列
alter sequence 序列名…
删除序列
DROP SEQUENCE 序列名

索引

两种方式：
– 自动创建: 当有PRIMARY KEY 或者UNIQUE 约束时，数据
库会自动创建一个索引；
– 手动创建: 用户使用创建索引语法来进行创建；
• 创建索引语法：

CREATE INDEX indexname
ON  table (column );

• 建议索引命名格式:idx_tablename_columnname
删除索引
DROP INDEX 索引名
适合创建索引的情况
• 表数据量很大
• 要查询的结果集在2%-4%左右
• 经常用来做WHERE条件中的列或者多表连接的列
• 查询列的数据范围分布很广
• 查询列中包含大量的NULL值,因为空值不包含在索引中

同义词（ SYNONYM ）

是指向数据库对象（如：表、视图、序列、存储过程等）的数据库指针。
–使用同义词好处：
. 可以简化对数据库对象的访问;
. 方便对其他用户表的访问;
. 简化过长的对象名称;
. 节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;
. 扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;
. 同义词可以创建在不同一个数据库服务器上，通过网络实现连接
创建以及删除同义词

CREATE [PUBLIC] SYNONYM 同义词 FOR [schema.]对象名；
创建employees表的别名。
CREATE SYNONYM s_emp
FOR hr.employees;
删除: DROP SYNONYM s_emp;

创建表

CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][, ...]);
示例
CREATE TABLE DOSSIER (
ID NUMBER(4),
CNAME VARCHAR2(20 ),
BIRTHDAY DATE,
STATURE NUMBER(3),
WEIGHT NUMBER(5, 2),
COUNTRY_CODE CHAR(2 ) DEFAULT ‘01’);

修改表

ALTER TABLE table
ADD (columnname datatype[DEFAULT expr]
[ columnname datatype] )

-修改列语法：
ALTER TABLE table
MODIFY(columnname datatype[DEFAULT expr]
[, columnname datatype]…);
例如:
ALTER TABLE dossier MODIFY (sex DEFAULT ‘男’);
– 删除列语法：
ALTER TABLE table DROP (columnname [,columnname]);
例如:ALTER TABLE emp DROP COLUMN sex;
添加新列
例如:
ALTER TABLE dossier ADD (sex CHAR(1));

删除表

DROP TABLE table；

重命名表

必须是对象的所有者

DROP TABLE table；

截断表

• 执行TRUNCATE语句的前提，必须是表的所有者
• 或者有DELETE ANY TABLE系统权限来截断表。

TRUNCATE TABLE table;

TRUNCATE和DELETE区别
–TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，
使用ROLLBACK不可以回滚。
– DELETE是DML，可以删除指定记录，不释放存储空间，使用
ROLLBACK可以回滚。

数据字典

数据字典表：由Oracle 服务器创建和维护的表，通过数据字典，可以很容易了解当前用户的对象信息

select * FROM user_tables 查看当前表下所有表的属性
SELECT COUNT(*) FROM user_tables  查看当前表下表的数量