DDL语句及索引、视图、序列 ORACLE数据库

最新推荐文章于 2024-02-21 21:52:48 发布

原创最新推荐文章于 2024-02-21 21:52:48 发布 · 439 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

DDL语句与常见的数据库对象（涉及到的表只有emp表）

目录（因为我是在Word写好再复制的，所以很多编号都变了，但具体内容就是目录里的这些）

一. DDL语句的定义与DDL操作分类

二. 常见数据库对象及相关的DDL语法

1.1 建表语法

1.2 建立备份表的方法和备份表的命名规范

DDL语句的定义与DDL操作分类

导图

DDL 语句

定义：

DDL语句-数据定义语言：用来定义数据库对象的操作语句

特点：

DDL语句不需要提交，执行完成立即生效（DML语句需要进行提交或回滚）

1. DDL语句的分类

CREATE-创建 --不常用

DROP-删除 --不常用

ALTER-修改 --不常用

TRUNCATE-清空 --频繁使用（DELETE的代替品）

RENAME-重命名 --不建议使用

1.区分DML语句与DDL语句，两者都有增删改，有什么区别？

DML:针对数据源中的数据进行的操作

DDL:针对数据库中各种对象的操作

2.什么是数据库对象，数据库对象有哪些？

数据库对象是数据库的组成部分，常见的如表、视图、索引、序列、约束、同义词、存储过程、存储函数等。

常见数据库对象及相关的DDL语法

表
1. 建表语法

基本语法

CREATE TABLE TB_NAME (

COL_NAME1 COL_TYPE,

COL_NAME2 COL_TYPE,

....

COL_NAME3 COL_TYPE);

create table EMP_20220728

(

empno NUMBER(4) not null,

ename VARCHAR2(10),

sal NUMBER(7,2),

deptno NUMBER(2)

)

2.特殊语法（建备份表）

CREATE TABLE TB_NAME AS SELECT ... ;

CREATE TABLE EMP_0 AS SELECT ENAME FROM EMP; --将数据源表EMP里面的ename字段和以下的数据一并放入刚刚建好的EMP_0表里面

SELECT * FROM EMP_0;

注意：

该语法用于基于某查询建立新表，查询结果会作为新表的数据一并插入到表中；

CREATE TABLE EMP_0 AS SELECT ENAME FROM EMP;

2.如果仅是复制某表的表结构，不要数据，可在SELECT语句中添加WHERE 1=0条件；

SELECT * FROM EMP_20220728_1;

CREATE TABLE EMP_20220728_1 AS SELECT * FROM EMP WHERE 1=0;

3.如果SELECT子句中出现函数、常量等非原始字段，要为该列添加别名。

CREATE TABLE AAAA AS SELECT 'AAA' AS AAA,123 AS BBB,TRUNC(SYSDATE) AS CCC FROM DUAL;

SELECT * FROM AAAA;

1. 建立备份表的方法和备份表的命名规范
建立备份表的方法

获取原表的建表语句，修改表名后执行，再将数据从原表中查询出来插入到备份表中；

2.执行CREATE TABLE 备份表 AS SELECT * FROM 原表;

CREATE TABLE EMP_20220726 AS

SELECT * FROM (SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP);--直接建表并插入

表的命名规范

原表名_BAK日期(bak 备份)

（日期指进行备份操作的日期或者是数据的日期）

CREATE TABLE EMP_20220726 AS

SELECT * FROM (SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP);--直接建表并插入

建表的一些注意点

每一个列必须要紧跟一个字段属性
同一表中，列与列之间是不能重名的
同一个用户下不能出现重复的表名
不同表下列名可以重复，不同用户下表名可以重复
每个表都有所属的一个用户

例题

1、删除10号部门员工信息前，为保证日后数据的恢复，请备份EMP表数据。

CREATE TABLE EMP_10 AS SELECT * FROM EMP; --备份emp表中数据到emp_10

SELECT * FROM EMP_10;

DELETE FROM EMP WHERE DEPTNO=10;--删除10号部门的员工

SELECT * FROM EMP;

--十号部门的员工又回来了，现需要把他们的数据恢复

MERGE INTO EMP A

USING EMP_10 B

ON(A.DEPTNO=B.DEPTNO)

WHEN NOT MATCHED THEN

INSERT(A.EMPNO,A.ENAME,A.JOB,A.MGR,A.HIREDATE,A.SAL,A.COMM,A.DEPTNO)

VALUES(B.EMPNO,B.ENAME,B.JOB,B.MGR,B.HIREDATE,B.SAL,B.COMM,B.DEPTNO);

对表进行删除和清空

1.删除

语法：

DROP TABLE TB_NAME;

删除的前提：当前存在这张表

DROP TABLE EMP_0;

注意：

删除时会将表结构与表中数据一并删除，删表前注意确认与备份

2.清空

语法:

TRUNCATE TABLE TB_NAME;

SELECT * FROM EMP_10;

TRUNCATE TABLE EMP_10; --直接清空，不会回滚

TRUNCATE TABLE EMP_10 WHERE DEPTNO=20; --ERROR//不能添加条件

注意：

1、TRUNCATE的执行效果等同于DELETE FROM TB_NAME；但是TRUNCATE属于DDL语句，而DELETE属于DML语句，前者的执行并不会留有提交或回滚的机会，只要语法正确，数据会立即被清除；

2、另外，DELETE会留下回滚日志，而TRUNCATE不会留下回滚日志，这个差别也是为何TRUNCATE的效率更高，所以虽然TRUNCATE有些危险，但是在保证操作正确的前提下，工作中会尽可能采用TRUNCATE。

表
1. 添加（字段）

语法：

加一个字段：ALTER TABLE TB_NAME ADD (COL_NAME COL_TYPE);

加多个字段：ALTER TABLE TB_NAME ADD (COL_NAME1 COL _TYPE,COL_NAME2 COL _TYPE,...);

SELECT * FROM AAAA;

ALTER TABLE AAAA ADD(DDD CHAR(4),EEE NUMBER);

注意点：

列名不能出现重复
只添加一个字段时可以省略括号，建议加上
同时添加多个字段，必须加上括号

删除（字段）

语法：

删除一个字段：

ALTER TABLE TB_NAME DROP COLUMN COL_NAME;

ALTER TABLE TB_NAME DROP (COL_NAME);

删除多个字段：

ALTER TABLE TB_NAME DROP (COL_NAME1,COL_NAME2,...)

ALTER TABLE AAAA DROP(DDD,EEE);

注意点：

要删除的字段是已经存在的字段
删除单个字段，且字段两边未使用括号括起，则必须加上COLUMN关键字
删除多个字段，不能添加COLUMN关键字，而且必须加上括号
不能删除所有列，至少保留一个列
1. 修改（字段）

语法：

修改单个字段：ALTER TABLE TB_NAME MODIFY COL_NAME COL _TYPE_NEW;

修改多个字段：ALTER TABLE TB_NAME MODIFY (COL_NAME1 COL _TYPE_NEW,COL_NAME2 COL _TYPE_NEW,...);

COL _TYPE_NEW：包括字段属性与精度

ALTER TABLE AAAA ADD(DDD CHAR(4),EEE NUMBER);

SELECT * FROM AAAA;

ALTER TABLE AAAA MODIFY(DDD NUMBER,EEE VARCHAR(5));

注意点：

修改字段属性及精度时，不用写明原来是什么属性和精度，直接将最终属性和精度写上即可
一般只会扩充长度不会去缩减长度，缩减长度只能缩减到最长数据的长度
若跨数据类型修改字段属性，必须先将该字段置为空才能修改，同类型间的字段属性修改不必置为空
只能修改现有字段
修改单个字段时，括号可以省略
修改多个字段时，括号不能省略，必须加上括号
VARCHAR2类型的字段修改长度由高到低、从大到小时，缩减到低于原表最长数据的长度时，也要清空列数据才可以修改

--思考：（1）

将SAGE的字段属性由数值型改为字符串，同时还能保留原本的信息（建一个只要字段不要数据的空的备份表，然后在这个表上进行字段的修改操作，再将原表的数据插入进去）

SELECT * FROM STUDENT;

思路：

1）备份STUDENT表中的字段，不要数据

CREATE TABLE STUDENT_1 AS SELECT * FROM STUDENT WHERE SDEPT='AS';

SELECT * FROM STUDENT_1; --没有数据的表

2)在没有数据的表上进行字段属性的修改

ALTER TABLE STUDENT_1 MODIFY(SAGE CHAR(10));

3)将STUDENT表中数据放入STUDENT_1

INSERT INTO STUDENT_1 SELECT * FROM STUDENT;

SELECT * FROM STUDENT_1;

将MGR的字段属性由数值型改为字符串，同时还能保留原本的信息（2），但这样修改的只是备份表，并不是要求的表，而且在备份表上，原表的约束除了非空外，都没有了。

--（2）将EMP表中的数据进行备份，然后将EMP数据清空，再在清空的表里进行字段属性的修改，再将备份表里的数据插入进去

SELECT * FROM EMP;

CREATE TABLE EMP_0 AS SELECT * FROM EMP; --备份表字段和数据

SELECT * FROM EMP_0;

DELETE FROM EMP; --删除表内所有数据

ALTER TABLE EMP MODIFY(MGR VARCHAR2(7));--修改MGR字段属性为VARCHAR2

INSERT INTO EMP SELECT * FROM EMP_0; --将备份表内的数据插入EMP;

1. 重命名（字段/表）

表重命名语法：

ALTER TABLE TB_NAME_OLD RENAME TO TB_NAME_NEW;

ALTER TABLE AAA RENAME TO AA;

SELECT * FROM AA;

列重命名语法：

ALTER TABLE TB_NAME RENAME COLUMN COL_NAME_OLD TO COL_NAME_NEW;

ALTER TABLE AA RENAME COLUMN EMAIL TO WMAILS;

SELECT * FROM AA;

注意：

强烈不建议使用重命名，一旦使用重命名，相关的程序都需要做出相应的变更，一旦有遗漏，会给整个系统带来隐患。
同一用户下不允许出现重复表名，同一表下不允许出现重复列名。

索引
1. 定义

索引;数据库中对象的一种，它记录了索引列中的数据以及对应的物理位置-ROWID。

索引建立时会获取指定列的数据，及其对应的ROWID，并自动地按照某种规则进行排序，索引与表是完全不同的两种对象，两者只是在使用上会有所关联。

1. 数据库检索数据方式
全盘扫描：按照条件在对应数据列从第一条数据检索到最后一条数据，一旦条件在某条数据上成立，就将对应的ROWID取出，直到检索到最后一条，然后按照ROWID去对应的物理位置获取整行数据。
索引扫描：按照条件在对应索引列从大概位置开始检索，检索到最后一条符合条件的数据即停止检索，符合条件的数据获取对应的ROWID，最后按照ROWID去对应的物理位置获取整行数据。
ROWID直接扫描：除全盘扫描和索引扫描外，还有一种最快的检索方式，就是使用ROWID直接进行扫描，但ROWID的获取需要依赖至少一次查询，所以这种检索方式一般不在考虑范围之内。

--全盘扫描与索引扫描的比较

而全盘扫描与索引扫描二者相比较，一般情况下索引扫描的速度要高于全盘扫描，所以建立有效的索引是程序运行中提高查询效率的的有效方法。

1. 1. 为何索引扫描的效率会高于全盘扫描？
因为索引在建立时不仅会获取索引列的数据和对应的ROWID，还会对数据进行排序，检索数据时就不必遍历所有数据，自然能够提高查询的效率。
其实索引扫描本质上是牺牲了DML语句的操作效率来换取更高的查询效率，若表中建立了索引，在维护该表数据时，会因数据的改变而使索引重新进行排序，这就导致DML的操作时间增加。不过相比于复杂的查询逻辑引起的查询效率降低，绝大多数情况下更能接受DML的效率降低。
另外有些程序中会在DML操作前先将索引置为失效，等DML操作结束后再重新激活索引。
1. 1. 全盘扫描与索引扫描的类比：

1）1.查字典

全盘扫描：从字典的第一页开始找，即是已经错过内容也不停止，直到找到最后一页

索引扫描：先翻目录找到对应的页码，然后拿着页码去找内容

1. 索引的分类及建立语法
  1. 按存储形式（索引中存储的内容不同）
索引的命名规范：IND_TBNAME_COLNAME（默认行业习惯）

CREATE INDEX IND_EMP_SAL ON EMP(SAL);

CREATE INDEX IND_EMP_SAL ON EMP.SAL; --ERROR

3.B-TREE索引(索引列原始数据+ROWID)

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE INDEX IND_EMP_DEPTNO ON EMP(DEPTNO);

适用场景：列基数比较大的时候使用（行业、身高）

列基数：该列不重复数据的个数 COUNT(DISTINCT COL)

根块：索引的顶级块，指向下一节点

分支块：包含下一节点的信息，指向下一分支块或者是叶块

叶块：存放索引的入口数据

4.位图索引(位图+ROWID) (bitmap)

说明：位图索引在创建时，会扫描整张表，为索引列的每个取值建立一个不重复的位图（BITMAP）来描述该取值

语法：CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE BITMAP INDEX IND_EMP_DEPTNO ON EMP(DEPTNO);

适用场景：列基数比较小的时候使用（性别、婚姻状况）

5.反向键索引(索引列原始数据的反向存储+ROWID) (reverse)

说明：可以视作一种特殊的B-TREE索引，存储索引列的反向值

背景：为防止B-TREE索引在某叶上数据量占比过高而使用的一种索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE;

CREATE INDEX IND_EMP_ENAME ON EMP(ENAME) REVERSE;

适用场景：原始数据分支不明显但反向数据分支明显的列（身高：集中在一米七一米八）

6.基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)

说明：可以视作一种特殊的B-TREE索引，存储函数处理后的数据

背景：在某个字段上以原值建立了索引，但是在筛选时在该字段上经常加函数，导致索引无法生效

语法：

CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));

CREATE INDEX IND_EMP_JOB ON EMP(LENGTH(JOB));

适用场景：对某列进行筛选时经常需要配合函数使用（例如查找姓名中的首字母）

1. 1. 按唯一性（索引中的数据是否有重复值）
唯一索引 --索引列中不可能出现重复值

语法：

CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE UNIQUE INDEX IND_EMP_EMPNO ON EMP(EMPNO);

注意点：

B-TREE索引可以建立唯一索引，位图索引不能建立唯一索引（因为位图索引上有很多

重复值）

CREATE UNIBUE BITMAP INDEX IND_EMP_ENAME ON EMP(ENAEM); --ERROR

2）如果在某列上建立了唯一约束或主键约束，ORACLE会自动在该列上建立一个同名的唯一索引

非唯一索引 --索引列中可能出现重复值

语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE INDEX IND_EMP_ENAME ON EMP(ENAME);

1. 1. 按列的个数(索引覆盖的列的个数)
单列索引 --基于一个列建立的索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE INDEX IND_EMP_SAL ON EMP(SAL);

复合索引 (也称为联合索引） --基于两个或两个以上列建立的索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);

CREATE INDEX IND_EMP_ENAME ON EMP(SAL,COMM);

注意：or连接不走复合索引，and连接走复合索引。

1. 索引引用

--简单的执行计划使用方法：（面试可能会问）

将要执行的SQL语句放到explain plan窗口运行，INDEX即为索引扫描，FULL为全盘扫描

查询

思考：

--1.为什么不建议在WHERE筛选中出现隐式转换？（会导致索引失效）

SELECT * FROM EMP WHERE EMPNO=7369; --隐式转换

SELECT * FROM EMP WHERE TO_CHAR(EMPNO)='7369';--EMPNO上的索引会失效

注意：隐式转换时，它可能是将数值型转为字符型，而我们建立索引的列的属性是数值型，这就会导致索引失效，因为类型不一样，引用不到索引。

--2.索引是越多越好吗？

按需建立；因为索引带来的效率提升是牺牲了DML的效率的

/*1 首先数据量小的表不需要建立索引,因为小的表即使建立索引也不会有大的用处,还会增加额外的索引开销

2 不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义

3 经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率

4 索引并不是一劳永逸的,用的时间长了需要进行整理或者重建*/

索引建立或使用的规则与建议

如果对某大表进行筛选时，某列或某几列频繁出现在WHERE子句中，并且检索出的数据低于总行数的15%（50%），应考虑在这些列上建立索引。
如果对某大表进行排序时，某列或某几列频繁出现在ORDER BY子句中，应考虑在这些列上建立索引。
小表不要建立索引。
对于含有空值的列，如果经常在查询时查询非空值，建议在该列上建立索引；如果经常在查询时查询空值，建议在该列上建立基于函数的索引。(建立索引会进行一个排序，默认是升序)CREATE INDEX IND_EMP_COMM ON EMP(NVL(COMM,0));
为了提高表连接的性能，应在连接列上建立索引（建立一般普通的索引即可）
索引是数据库的一种实体对象，级别类似于表，会占用内存空间，ORACLE会自动进行索引维护，表和索引可以建立在不同的表空间。
通过索引可以提升数据的查询速度，但是会相对地降低DML语句的操作速度，尤其是插和改的速度，ORACLE会花费时间在索引维护上，所以说要把握好索引的数量
对于列基数比较大的列，适合B-TREE索引，列基数比较小的列，适合位图索引。
对于复合索引，至少要引用到索引列中的第一个列才会使用该索引。（该点为早期说法，现行理论有时非第一列查询也能引用，以实际为准）
某列可以出现在多个索引中，但相同的某列或某几列无法多次建立索引。 --就是可以有多个组合，但不能有重复组合，不同顺序的相同几列视为不同组合
索引建立后并不一定会被引用，ORACLE会分析整个SQL后做出最优的执行方式。（如果查询所得的数据量很少，不会走索引，因为SQL是按最优的执行方式去执行的）
ORACLE会自动在主键约束和唯一约束列上建立唯一索引。
对于一般的B-TREE索引，通配符出现在搜索词的首位时不会引用索引。
在索引列上使用<> !=号时，或对空值进行判断时，索引不会生效
1. 索引的删除

语法：

DROP INDEX IND_NAME;

DROP INDEX IND_EMP_SAL;

1. 索引的禁用与重建
禁用索引的语法：（unusable）

ALTER INDEX IND_NAME UNUSABLE;

ALTER INDEX IND_EMP_SAL UNUSABLE;

重建索引的语法：

ALTER INDEX IND_NAME REBUILD;

ALTER INDEX IND_EMP_SAL REBUILD;

注意！

【插入完成后统一维护索引】比【一边插入一边维护】的速度要快！

1. 索引相关数据字典
所有索引

--所有索引

SELECT INDEX_NAME, --索引名称

INDEX_TYPE, --索引类型

TABLE_NAME, --表名

UNIQUENESS, --是否唯一

STATUS, --索引状态 VALID 可用的 UNUSABLE 不可用的

TABLESPACE_NAME,--表空间

LOGGING --是否记录日志

FROM USER_INDEXES

WHERE 1=1

AND INDEX_NAME = 'IND_EMP_DEPTNO' --索引名

AND TABLE_NAME = 'EMP'; --表名

索引列

--索引列

SELECT INDEX_NAME, --索引名称

TABLE_NAME, --表名

COLUMN_NAME, --列名

COLUMN_POSITION, --字段在索引中的位置

DESCEND --排序方式默认ASC

FROM USER_IND_COLUMNS

WHERE INDEX_NAME ='IND_EMP_UPENAME';

索引函数

SELECT INDEX_NAME,

TABLE_NAME,

COLUMN_EXPRESSION

FROM USER_IND_EXPRESSIONS

WHERE INDEX_NAME ='IND_EMP_UPENAME';

视图

1. 定义视图
视图定义

定义：视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，

是一张虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化，从视图中可以查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

（视图是将SQL查询语句的结果动态保存到数据库中的一张虚拟表)

定义解读

1）视图可以像普通的事实表一样去使用

2）视图本身不存储数据，它只是动态地获取表的查询结果

权限要求：GRANT CREATE VIEW TO USERNAME;

GRANT CREATE VIEW TO SCOTT; --对普通用户开放视图权限

建立视图
1. 语法

CREATE [OR REPLACE] VIEW V_viewname [(别名1,别名2...)]

AS <子查询>

[WITH CHECK OPTION] --检查 --可读写视图

[WITH READ ONLY] ; --只读（不可对视图进行增删改）

--这两个要么不写要么只能写一个

基表（基本表）：视图的来源表

在下列三种情况下必须明确指定组成视图的所有列名：

某个目标列不是单纯的属性名，而是聚集函数或列表达式
多表连接时选出了几个同名列作为视图的字段
需要在视图中为某个列启用新的更合适的名字
1. 视图举例

创建一个视图，内容包含员工信息表中20号部门员工的工号、姓名和工资。

CREATE VIEW EMP_20

AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO=20;

SELECT * FROM EMP_20;

SELECT * FROM EMP;

UPDATE EMP_20 SET SAL=1.2*SAL; --对视图数据的修改

该视图是基于一个表建立起来的，查询语句中不含函数表达式和分组。

1）可以通过对视图的增删改来达到对基表的增删改；

2）对于增加数据，视图本身没有限制，新增的数据只需符合基表的要求即可。若新增的数据符合视图的定义，则会同时体现在视图和基表上，若不符合视图的定义，新增数据仅会体现在基表上；（比如视图要的是20号部门的员工信息，这时候插入一条30号部门的员工信息，

不符合视图的定义，则不会在视图上显示，只会在基本表上显示）

3）对于删除和修改：无法对视图中没有的数据进行操作；

4）若添加了WITH CHECK OPTION参数，新增的数据必须要符合视图定义的要求（如视图要求20号部门的员工信息，现在要插入30号部门的员工信息则插不进去）

5）若添加了WITH READ ONLY参数，该视图只能查询，不能增删改

2.创建一个视图，内容包含各部门名称、工作地点、小写姓名、薪资、奖金、薪资奖金合计值。

--WITH CHECK OPTION (可修改，只能在特定条件内进行修改)

CREATE VIEW EMP_DETT(DNAME,LOC,ENAME,SAL,COMM,SUM_SM) AS

SELECT DNAME,LOC,LOWER(ENAME),SAL,COMM,SAL+NVL(COMM,0) FROM DEPT A RIGHT JOIN EMP B ON A.DEPTNO=B.DEPTNO WITH CHECK OPTION;

--定义了WITH CHECK OPTION 则不能将不符合定义的数据插入视图

SELECT * FROM EMP_DETT;

DROP VIEW EMP_DETT;

--WITH READ ANLY(只读)

CREATE VIEW EMP_DETT(DNAME,LOC,ENAME,SAL,COMM,SUM_SM) AS --只读操作，不能进行增删改操作

SELECT DNAME,LOC,LOWER(ENAME),SAL,COMM,SAL+NVL(COMM,0) FROM DEPT A RIGHT JOIN EMP B ON A.DEPTNO=B.DEPTNO WITH READ ONLY;

UPDATE EMP_DETT SET SAL=1.2*SAL;--定义了WITH READ ONLY 则不能进行增删改操作

该视图是基于多个基表建立起来的，且查询语句中包含函数表达式

1）对于基表中原有的列，定义视图时可以不加别名；对于基表中原本没有的列，定义视图时必须加别名；

2）此时无论是否有添加WITH READ ONLY参数，都不能通过增删改视图来增删改基表

3.基于上一个例题创建的视图，再创建一个视图，内容包含各部门名称，各部门薪资总计。

CREATE VIEW EMP_DETT_1(DNAME,SUM1) AS

SELECT DNAME,SUM(SAL) FROM EMP_DETT GROUP BY DNAME;

SELECT * FROM EMP_DETT_1;

该视图是基于其他视图建立的

1）基于其他视图建立新视图时，其他视图的基表发生变化也会引起新视图的变化

视图的修改：（replace）

通过[REPLACE]重新执行视图的定义信息来达到视图的修改

视图举例：

修改视图3的定义信息，内容改为各部门名称，各部门人数总计和平均薪资。

Create or replace(创建或替换)

CREATE OR REPLACE VIEW EMP_DETT_1 AS

SELECT DNAME,COUNT(1) T1,AVG(SAL) A1 FROM EMP_DETT GROUP BY DNAME WITH CHECK OPTION;

SELECT * FROM EMP_DETT_1;

1. 删除视图（DROP）

基本表删除后由其导出的视图用不了，但还存在，

--视图的删除：

DROP VIEW V_NAME [CASCADE];(级联删除语句)

--CASCADE

视图删除后视图的定义将从数据字典上删除。如果该视图上还导出了其他视图，则使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除。

CREATE VIEW EMP_DETT_1;

1. 视图查询
查询视图EMP_DETT_1视图中SALES部门的人数和平均薪资

SELECT DNAME,T1,A1 FROM EMP_DETT_1 WHERE DNAME='SALES';

视图消解:关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表，视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的基本表的查询，然后再执行修正了的查询。这一过程叫视图消解。

--上面语句转换为

SELECT DNAME,COUNT(1),AVG(SAL) FROM EMP_DETT WHERE DNAME='SALES' GROUP BY DNAME;

1. 更新视图
UPDATA SET(修改)

SELECT * FROM EMP_DETT;

UPDATE EMP_DETT SET SAL=2*SAL;

INSERT INTO（插入）

INSERT INTO EMP_DETT VALUES('ACCOUNTING','NEW YORK','HJH',1500,200,1700);

--该视图是修改不了的，因为它定义了WITH READ ONLY,视图内还有一列是由聚合函数得出来的列，这两者只要满足一点就修改不了。但视图中数据修改的语句就是这样。

DELETE FROM（删除）

DELETE FROM EMP_DETT WHERE ENAME='KING';

视图的作用

视图能简化用户操作
视图使用户能以多种角度看待同一问题
视图对重构数据集提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护
适当利用视图可以更清晰地表达查询
1. 视图相关数据字典

数据字典名： USER_VIEWS --查询用户所有视图

SELECT VIEW_NAME, --视图名称

TEXT, --视图定义

READ_ONLY --是否只读

FROM USER_VIEWS;

序列
1. 定义

序列是ORACLE提供的一组能够自动增长的序号。常常用来生成每条数据的唯一标识。

权限要求：CREATE SEQUENCE

1. 创建语法
语法

CREATE SEQUENCE 序列名称 SEQ_NAME

START WITH N --初始序号递增：默认MINVALUE 递减：默认MAXVALUE

INCREMENT BY N --增长幅度 N为正递增 N为负递减

MINVALUE N | NOMINVALUE --最小值 N | 无最小值

MAXVALUE N | NOMAXVALUE --最大值 N | 无最大值

CACHE N | NOCACHE --缓存 N个序号 | 无缓存默认缓存20个序号 --尽量设置大一点的值

CYCLE | NOCYCLE ;--循环达到极值时是否从新循环生成序号

--创建序列

DROP SEQUENCE SEQ_1;

CREATE SEQUENCE SEQ_1

START WITH 1

INCREMENT BY 1 (正是增负是减)

MINVALUE 1

MAXVALUE 10

NOCACHE

CYCLE

特别说明：循环序列在创建时，缓存值限定在：

CEIL((MAXVALUE-MINVALUE)/ABS(INCREMENT)) 以内，非循环序列缓存值设定没有限定，但实际缓存受所剩序列号影响。

语法解析

1) INCREMENT BY用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表Oracle序列的值是按照此步长递减的。

2) START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

3) MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增Oracle序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

4) MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是-10的26次方;对于递增序列，最小值是1。

5) CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

6) CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

大量语句发生请求，申请序列时，为了避免序列在运用层实现序列而引起的性能瓶颈。Oracle序列允许将序列提前生成 cache x个先存入内存，在发生大量申请序列语句时，可直接到运行最快的内存中去得到序列。但cache个数也不能设置太大，因为在数据库重启时，会清空内存信息，预存在内存中的序列会丢失，当数据库再次启动后，序列从上次内存中最大的序列号+1 开始存入cache x个。这种情况也能会在数据库关闭时也会导致序号不连续。

7) NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用。

8) CURRVAL 中存放序列的当前值,NEXTVAL 应在 CURRVAL 之前指定，二者应同时有效。