- DDL语句与常见的数据库对象(涉及到的表只有emp表)
目录(因为我是在Word写好再复制的,所以很多编号都变了,但具体内容就是目录里的这些)
- 导图
- DDL 语句
定义:
DDL语句-数据定义语言:用来定义数据库对象的操作语句
特点:
DDL语句不需要提交,执行完成立即生效(DML语句需要进行提交或回滚)
-
- DDL语句的分类
CREATE-创建 --不常用
DROP-删除 --不常用
ALTER-修改 --不常用
TRUNCATE-清空 --频繁使用(DELETE的代替品)
RENAME-重命名 --不建议使用
1.区分DML语句与DDL语句,两者都有增删改,有什么区别?
DML:针对数据源中的数据进行的操作
DDL:针对数据库中各种对象的操作
2.什么是数据库对象,数据库对象有哪些?
数据库对象是数据库的组成部分,常见的如表、视图、索引、序列、约束、同义词、存储过程、存储函数等。
- 表
- 基本语法
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;
- 建立备份表的方法
- 获取原表的建表语句,修改表名后执行,再将数据从原表中查询出来插入到备份表中;
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。
- 表
语法:
加一个字段: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关键字,而且必须加上括号
- 不能删除所有列,至少保留一个列
语法:
修改单个字段: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;
表重命名语法:
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;
注意:
- 强烈不建议使用重命名,一旦使用重命名,相关的程序都需要做出相应的变更,一旦有遗漏,会给整个系统带来隐患。
- 同一用户下不允许出现重复表名,同一表下不允许出现重复列名。
- 索引
索引;数据库中对象的一种,它记录了索引列中的数据以及对应的物理位置-ROWID。
索引建立时会获取指定列的数据,及其对应的ROWID,并自动地按照某种规则进行排序,索引与表是完全不同的两种对象,两者只是在使用上会有所关联。
- 全盘扫描:按照条件在对应数据列从第一条数据检索到最后一条数据,一旦条件在某条数据上成立,就将对应的ROWID取出,直到检索到最后一条,然后按照ROWID去对应的物理位置获取整行数据。
- 索引扫描:按照条件在对应索引列从大概位置开始检索,检索到最后一条符合条件的数据即停止检索,符合条件的数据获取对应的ROWID,最后按照ROWID去对应的物理位置获取整行数据。
- ROWID直接扫描:除全盘扫描和索引扫描外,还有一种最快的检索方式,就是使用ROWID直接进行扫描,但ROWID的获取需要依赖至少一次查询,所以这种检索方式一般不在考虑范围之内。
--全盘扫描与索引扫描的比较
而全盘扫描与索引扫描二者相比较,一般情况下索引扫描的速度要高于全盘扫描,所以建立有效的索引是程序运行中提高查询效率的的有效方法。
-
-
- 为何索引扫描的效率会高于全盘扫描?
-
- 因为索引在建立时不仅会获取索引列的数据和对应的ROWID,还会对数据进行排序,检索数据时就不必遍历所有数据,自然能够提高查询的效率。
- 其实索引扫描本质上是牺牲了DML语句的操作效率来换取更高的查询效率,若表中建立了索引,在维护该表数据时,会因数据的改变而使索引重新进行排序,这就导致DML的操作时间增加。不过相比于复杂的查询逻辑引起的查询效率降低,绝大多数情况下更能接受DML的效率降低。
- 另外有些程序中会在DML操作前先将索引置为失效,等DML操作结束后再重新激活索引。
-
- 全盘扫描与索引扫描的类比:
-
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));
适用场景:对某列进行筛选时经常需要配合函数使用(例如查找姓名中的首字母)
语法:
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);
语法:
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连接走复合索引。
--简单的执行计划使用方法:(面试可能会问)
将要执行的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索引,通配符出现在搜索词的首位时不会引用索引。
- 在索引列上使用<> !=号时,或对空值进行判断时,索引不会生效
语法:
DROP INDEX IND_NAME;
DROP INDEX IND_EMP_SAL;
ALTER INDEX IND_NAME UNUSABLE;
ALTER INDEX IND_EMP_SAL UNUSABLE;
ALTER INDEX IND_NAME REBUILD;
ALTER INDEX IND_EMP_SAL REBUILD;
注意!
【插入完成后统一维护索引】比【一边插入一边维护】的速度要快!
--所有索引
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';
- 视图定义
- 定义:视图是从一个或几个基本表(或视图)导出的表。它与基本表不同,
是一张虚表。数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化,从视图中可以查询出的数据也就随之改变了。从这个意义上讲,视图就像一个窗口,透过它可以看到数据库中自己感兴趣的数据及其变化。
(视图是将SQL查询语句的结果动态保存到数据库中的一张虚拟表)
- 定义解读
1)视图可以像普通的事实表一样去使用
2)视图本身不存储数据,它只是动态地获取表的查询结果
权限要求:GRANT CREATE VIEW TO USERNAME;
GRANT CREATE VIEW TO SCOTT; --对普通用户开放视图权限
- 建立视图
- 语法
CREATE [OR REPLACE] VIEW V_viewname [(别名1,别名2...)]
AS <子查询>
[WITH CHECK OPTION] --检查 --可读写视图
[WITH READ ONLY] ; --只读(不可对视图进行增删改)
--这两个要么不写要么只能写一个
基表(基本表):视图的来源表
在下列三种情况下必须明确指定组成视图的所有列名:
- 某个目标列不是单纯的属性名,而是聚集函数或列表达式
- 多表连接时选出了几个同名列作为视图的字段
- 需要在视图中为某个列启用新的更合适的名字
- 视图举例
- 创建一个视图,内容包含员工信息表中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;
-
- 删除视图 (DROP)
基本表删除后由其导出的视图用不了,但还存在,
--视图的删除:
DROP VIEW V_NAME [CASCADE];(级联删除语句)
--CASCADE
视图删除后视图的定义将从数据字典上删除。如果该视图上还导出了其他视图,则使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除。
CREATE VIEW EMP_DETT_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;
- 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';
- 视图能简化用户操作
- 视图使用户能以多种角度看待同一问题
- 视图对重构数据集提供了一定程度的逻辑独立性
- 视图能够对机密数据提供安全保护
- 适当利用视图可以更清晰地表达查询
数据字典名: USER_VIEWS --查询用户所有视图
SELECT VIEW_NAME, --视图名称
TEXT, --视图定义
READ_ONLY --是否只读
FROM USER_VIEWS;
序列是ORACLE提供的一组能够自动增长的序号。常常用来生成每条数据的唯一标识。
权限要求:CREATE SEQUENCE
- 语法
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 之前指定 ,二者应同时有效。
CREATE SEQUENCE SEQ_A
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20
CACHE 10
NOCYCLE;
值当前值:CURRVAL
下一个值:NEXTVAL
注意:
1)第一次使用CURRVAL必须先使用一次NEXTVAL
SELECT SEQ_1.NEXTVAL FROM DUAL;
SELECT SEQ_1.CURRVAL FROM DUAL;
1)第一次使用NEXTVAL得到的是序列中的起始
SELECT SEQ_1.CURRVAL FROM DUAL;
SELECT SEQ_1.NEXTVAL FROM DUAL;
--给员工按顺序赋值
CREATE SEQUENCE SEQ_0;
SELECT SEQ_0.NEXTVAL 学号,ENAME FROM EMP;
--简单运用
SELECT TO_CHAR(SYSDATE,'YYYYMMDD')||LPAD(SEQ_0.NEXTVAL,6,'0'),ENAME FROM EMP; --给每个同学一个入校日期加序号,但有的人的序号是一位的,有的人的序号是双位的,为了统一,给它们设置序号长度为六,不够的就在左边补0
ALTER SEQUENCE SEQ_NAME CYCLE; --修改循环
ALTER SEQUENCE SEQ_1 CYCLE;
ALTER SEQUENCE SEQ_ NAME INCREMENT BY 3; --修改涨幅
ALTER SEQUENCE SEQ_1 INCREMENT BY 2; --将涨幅改为2
注意:
无法直接调整初始值,只能删除后重建,或先设置一个新的涨幅,通过nextval执行到目标序号后再调回旧的涨幅。
删除语法:
DROP SEQUENCE SEQ_ NAME;
--注意事项
1.CACHE值小于等于CYCLE值(指一次循环能够生成的序号个数)(缓存值小于循环值)
2.未设置起始值时,默认递增序列从最小值开始,递减序列从最大值开始
3.涨幅必须是非零整数
4.若要指定起始值,起始值必须在最小值与最大值之间
5.若未指定最大值和最小值 递增时:最小值是1 最大值是10^27 递减时:最小值是-10^27 最大值是 -1
6.ABS(INCREMNET)<=ABS(MAXVALUE-MINVALUE)(循环时缓存值限定范围)
7.缓存值尽量设置大一些