SQL_数据库对象（视图、序列、同义词、索引 都是DDL语句）

数据库对象（视图、序列、同义词、索引  都是DDL语句）

1 视图
    视图是从一个或几个实体表（或视图）导出的表。它与实体表不同，视图本身是一个不包含任何真是数据的虚拟表。视图最终是定义在实体表之上的，对视图的一切操作最终也要转换为对实体表的操作。
    有经验的数据库设计人员往往会为开发者将各个复杂的SQL语句封装成一个个独立的视图。
--创建视图
    CREATE [FORCE|NOFORCE] [OR REPLACE] VIEW 视图名称 [(别名1,别名2,...)]
        AS
    子查询 ;
    
    FORCE: 表示要创建视图的表不存在也可以创建视图。
    NOFORCE:（默认）表示要创建视图的表必须存在，否则无法创建视图。
    OR REPLACE ： 表示视图的替换。如果创建的视图不存在，则创建新的视图；如果视图已经存在，则将其替换。
    
--用例1：创建一张基本工资大于2000的雇员信息的视图
CREATE VIEW v_myview
    AS
SELECT * FROM emp WHERE sal>2000;

--用例2： 查看视图是否已经创建
SELECT * FROM tab WHERE tabtype='VIEW'    and lower(TNAME)='v_myview';
SELECT view_name,text_length,text FROM user_views WHERE  lower(view_name)='v_myview';

--用例3：替换 v_myview ,创建一张基本工资大于2000的雇员信息的视图,
CREATE OR REPLACE VIEW v_myview
    AS
SELECT * FROM emp WHERE sal>2000;

--用例3：替换 v_myview ,为视图中查询的列起别名
CREATE OR REPLACE VIEW v_myview
    (部门编号,部门名称,位置,人数,平均工资,总工资,最高工资,最低工资)
    AS
SELECT d.deptno,d.dname,d.loc,
    COUNT(e.empno) count,
    NVL(ROUND(AVG(sal),2),0) avg,
    NVL(SUM(sal),0) sum,
    NVL(MAX(sal),0) max,
    NVL(min(sal),0) min
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,d.dname,d.loc;

--用例4：在视图上执行DML操作
    UPDATE 视图后，原表会同步更新，缺失字段默认为空。当用户对视图执行删除操作之后，会影响原始数据表中的数据。 但是无法通过多表映射的视图修改多个表。
重点： 能否有其他方式可以通过视图同步更新多张数据表？
    视图如果由多张数据表组成，将无法进行增加及更新的操作。
    答： 利用触发器完成多表同时更新。

1.1 WITH CHECK OPTION 子句： 保证视图的创建条件不被修改。
     在创建视图的时候需要使用WHERE 子句做一些条件限制，但是默认情况下视图创建完成之后，是可以通过视图修改在WHERE子句中所使用的字段内容，此时就需要通过 WITH CHECK OPTION 子句来保证视图的创建条件不被更新。
   语法：WITH CHECK OPTION 子句
   CREATE [FORCE|NOFORCE] [OR REPLACE] VIEW 视图名称 [(别名1,别名2,...)]
       AS
   子查询 [WITH CHECH OPTION [CONSTRAINT 约束名称]];
   
--用例1：
   CREATE OR REPLACE VIEW v_emp20
       AS
   SELECT * FROM emp WHERE deptno=20
   WITH CHECK OPTION CONSTRAINT v_emp20_ck;

1.2 WITH READ ONLY 子句： 创建只读视图,让视图的其他字段无法更新。
    语法：WITH READ ONLY 子句
    CREATE [FORCE|NOFORCE] [OR REPLACE] VIEW 视图名称 [(别名1,别名2,...)]
        AS
    子查询 [WITH CHECH OPTION [CONSTRAINT 约束名称]]
    [WITH READ ONLY]
    ;
    
--用例1
    CREATE OR REPLACE VIEW v_emp20
        AS
    SELECT * FROM emp WHERE deptno=20
    WITH READ ONLY;
--READ_ONLY 字段    
SELECT view_name,text_length,text,READ_ONLY FROM user_views WHERE  lower(view_name)='v_myview';

1.3 删除视图
    语法：DROP VIEW 视图名称;

--用例1:
DROP VIEW v_emp20;
SELECT view_name,text_length,text,READ_ONLY FROM user_views WHERE  lower(view_name)='v_emp20';


2 序列
    序列是Oracle 中最为重要的对象，利用序列可以实现数据表流水号(自动增长列)的操作。
2.1 序列的作用及创建
    语法：序列的完整创建语法
    CREATE SEQUENCE 序列名称
    [INCREMENT BY 步长]
    [START WITH 开始值]
    [MAXVALUE 最大值|NOMAXVALUE]
    [MINVALUE 最小值|NOMINVALUE]
    [CYCLE|NOCYCLE]
    [CACHE 缓存大小|NOCACHE];

    SEQUENCE

--用例1：创建默认序列
CREATE SEQUENCE myseq;
--用例2：查看序列数据字典
SELECT * FROM user_sequences;
    
    字段说明：
    SEQUENCE_NAME:表示序列的名称，此处的名称为之前创建的MYSEQ。
    MIN_VALUE:此序列开始的默认最小值（默认是0）。
    MAX_VALUE:此序列开始的默认最大值（默认是27个9 ，即 999999999999999999999999999）。
    INCREMENT_BY:序列每次增长的步长，默认是1。
    CYCLE_FLAG: 循环标记，如果是循环序列则显示Y，非循环序列则显示为N（默认为N）。
    CACAHE_SIZE:序列操作的缓存量，默认是20 。
    LAST_NUMBER:最后一次操作的值。
    序列名称.currval: 表示取得当前序列已经增长的结果，重复调用多次后序列内容不会有任何变化，同时当前序列的大小(LAST_NUMBER)不会改变。
    序列名称.nextval: 表示取得一个序列的下一次增长值，每调用一次，序列都会增长。
    
    
--用例3：通过 nextval 属性操作序列。
SELECT myseq.nextval FROM dual;

--用例4：通过 currval 属性操作序列。
SELECT myseq.currval FROM dual;

--用例5：创建一张 member 表，并使用序列进行ID列的自动增长操作。
    DROP TABLE member PURGE;
    DROP TABLE advice PURGE;
    CREATE TABLE member(
        mid NUMBER PRIMARY KEY,
        name VARCHAR2(200) NOT NULL,
        email VARCHAR2(50),
        CONSTRAINT uk_email UNIQUE(email),
        CONSTRAINT pk_mid PRIMARY KEY(mid)
    ); 
    INSERT INTO member(mid,name) VALUES(myseq.nextval,'XXXX学院');

2.2 序列的删除
    语法：
    DROP SEQUENCE 序列名称;

--用例1：删除 myseq 序列
DROP SEQUENCE myseq;
SELECT * FROM user_sequences;

2.3 创建特殊功能的序列
--用例1：设置序列的增长步长 INCREMENT BY
CREATE SEQUENCE myseq INCREMENT BY 3;
SELECT * FROM user_sequences WHERE SEQUENCE_NAME='myseq';

--用例2：设置序列的初始值 START WITH,创建序列，让其初始值设置为30，每次增长步长为2.
    一个序列的默认初始值是1，用户在创建序列的时候可以直接通过 START WITH 属性设置。
    CREATE SEQUENCE 序列名称 START WITH 开始值;

    DROP SEQUENCE myseq;
    CREATE SEQUENCE myseq 
        INCREMENT BY 3
        START WITH 30;
    
    SELECT myseq.nextval FROM dual;

--用例3：设置序列的缓存 CACHE 缓存大小|NOCACHE, 可能引起跳号的问题。
    创建序列并由用户设置缓存操作。
    CREATE SEQUENCE 序列名称 CACHE 缓存大小|NOCACHE。
    
    --创建序列，缓存设置为100 。
    CREATE SEQUENCE myseq CACHE 100;

    --创建序列，不设置缓存 。
    CREATE SEQUENCE myseq NOCACHE;

    SELECT cache_size,last_number FROM user_sequences;

--用例4：设置循环序列。循环序列指的是一个序列在每次调用 nextval 属性后可以产生指定范围的数据。例如，现在希望一个序列的内容是在1/3/5/7/9 这5个数字之间循环，这就属于循环序列。
    CREATE SEQUENCE 序列名称
        [MAXVALUE 最大值|NOMAXVALUE]
        [MINVALUE 最小值|NOMINVALUE]
        [CYCLE|NOCYCLE];

    DROP SEQUENCE myseq;
    CREATE SEQUENCE myseq 
        START WITH 1
        INCREMENT BY 2
        MAXVALUE 10
        MINVALUE 1
        CYCLE 
        CACHE 3;

2.4 修改序列  （不建议修改数据库对象 ）
    语法：
    ALTER SEQUENCE 序列名称
        [INCREMENT BY 步长]
        [START WITH 开始值]
        [MAXVALUE 最大值|NOMAXVALUE]
        [MINVALUE 最小值|NOMINVALUE]
        [CYCLE|NOCYCLE]
        [CACHE 缓存大小|NOCACHE];

--用例1：修改 myseq 序列
ALTER SEQUENCE myseq
    INCREMENT BY 10
    MAXVALUE 98765
    MINVALUE 1
    CACHE 100;

SELECT cache_size,last_number FROM user_sequences;
    
2.5 自动序列，自动增长序列。
语法：
CREATE SEQUENCE 表名称(
    列名称 类型 GENERATED BY DEFAULT AS IDENTITY(
                                  [INCREMENT BY 步长]
                                  [START WITH 开始值]  
                                  [MAXVALUE 最大值|NOMAXVALUE] 
                                  [MINVALUE 最小值|NOMINVALUE]
                                  [CYCLE|NOCYCLE]
                                  [CACHE 缓存大小|NOCACHE]),
    列名称 类型 ,...                                  
);
    
--用例1：创建带有自动增长列的数据表。
DROP SEQUENCE myseq PURGE;    
CREATE TABLE mytab(
    mid NUMBER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1),
    name VARCHAR2(20) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);    

    
SELECT sequence_name,min_value,max_value,increment_by FROM user_sequences;    
    
--用例2： 自动序列的删除
    自动序列依附于数据表而存在，如果在执行数据表的删除操作时没有设置 PURGE 参数，那么表删除后序列依然会被保留，这时候不能利用 DROP SEQUENCE 命令删除序列，必须使用清空回收站的方式才可以删除。
    PURGE recyclebin;    
    
    
3 同义词
    数据库中所谓的同义词实际上就是指为一个数据库对象其一个其他名字。     
创建同义词语法：
CREATE [PUBLIC] SYNONYM 同义词名称 FOR 数据库对象;

删除同义词语法：
DROP SYNONYM 同义词名称;

--用例1：创建同义词    
CREATE  SYNONYM myemp FOR emp;
SELECT * FROM user_synonyms WHERE synonym_name='MYEMP';    

--用例2：删除同义词
DROP SYNONYM myemp ;    

--用例3：创建公共同义词 myemp
CREATE PUBLIC SYNONYM myemp FOR emp;    
    
    
4 Oracle 伪列    
    在 Oracle 数据库中为了实现完整的关系数据库的功能，专门为用户提供了许多的伪列，像之前在讲解序列时所使用的 NEXTVAL 和 CURRVAL 就是两个默认提供的操作伪列。同时在前面使用的 SYSDATE 与 SYSTIMESTAMP 也属于伪列（而想查询使用 DUAL 称为伪表）。这些数据伪列并不是用户在建立数据库对象时有用户完成的，而是 Oracle 自动帮助用户建立的，用户只需要按照要求使用即可。另外两个重要的伪列，分别是 ROWNUM 和 ROWID 。
       1. ROWID 伪列
       2. ROWNUM 伪列
       3. NEXTVAL、CURRVAL 
       4. SYSDATE、SYSTIMESTAMP 
       5. DUAL 伪表

4.1 ROWID 伪列
   在数据表中每一行所保存的记录，实际上 Oracle 都会默认为每条记录分配一个唯一的地址编号，而这个地址编号就是通过 ROWID 进行表示的，ROWID 本身是一个数据的伪列，所有的数据都利用 ROWID 进行数据定位。

--用例1：   
SELECT ROWID,deptno,dname,loc FROM dept;

读讲解ROWID 的组成:AAAWecAAGAAAAC2AAA：
    1.数据对象号(data object number)：AAAWec
    2.相对文件号(relative file number)：AAG
    3.数据块号(block number)：AAAAC2
    4.数据行号(row number)：AAA

--ROWID 的操作函数：
    1. DBMS_ROWID.rowid_object(ROWID) : 从一个 ROWID 中，取得数据对象号。
    2. DBMS_ROWID.rowid_relative_fno(ROWID) : 从一个 ROWID 中，取得相对文件号。
    3. DBMS_ROWID.rowid_block_number(ROWID) : 从一个 ROWID 中，取得数据块号。
    4. DBMS_ROWID.rowid_row_number(ROWID) : 从一个 ROWID 中，取得数据行号。

--拆分 ROWID，取数据。
    SELECT ROWID,
           DBMS_ROWID.rowid_object(ROWID),
           DBMS_ROWID.rowid_relative_fno(ROWID),
           DBMS_ROWID.rowid_block_number(ROWID),
           DBMS_ROWID.rowid_row_number(ROWID),
           deptno,dname,loc
     FROM dept;

-- 使用 ROWID 可以定位一个数据库中的任何数据行。因为一个段只能存在一个表空间内，所以通过使用数据对象号，Oracle 服务器就可以找到包含数据行的表空间。之后使用表空间中的相对文件号就可以确定文件，在利用数据块号就可以确定包含所需数据行的数据块，最后使用行号就可以定位数据行的行目录项。       

--用例2 ：经典题目
   向 mydept 表中增加一些数据
   INSERT INTO mydept(deptno,dname,loc) VALUES(10,'ACCOUNTING','NEW YORK');
   INSERT INTO mydept(deptno,dname,loc) VALUES(10,'ACCOUNTING','NEW YORK');
   INSERT INTO mydept(deptno,dname,loc) VALUES(20,'RESEARCH','DALLAS');
   INSERT INTO mydept(deptno,dname,loc) VALUES(20,'RESEARCH','DALLAS');
   INSERT INTO mydept(deptno,dname,loc) VALUES(20,'RESEARCH','DALLAS');

现在要求用户将所有重复的数据删除到只剩1条，即：现在表中有3个10部门的信息，要求值保留1个10部门的信息，删除重复数据后，mydept表应只剩下4行单独的数据。
答案：
   --1 对mydept 表分组，统计出唯一的ROWID数据。
   SELECT deptno,dname,loc,MIN(ROWID)
   FROM mydept
   GROUP BY deptno,dname,loc;
   
   --2 编写删除语句删除重复数据（必须要按照deptno 部门分组，否则会只保留一个值。）
   DELETE FROM mydept
   WHERE ROWID NOT IN (
       SELECT MIN(ROWID) 
       FROM mydept 
       GROUP BY deptno);

4.2 ROWNUM 伪列
    ROWNUM 表示的是一个数据行编号的伪列，它的内容是在用户查询数据的时候，为用户动态分配的一个数字(行号)。
    ROWNUM作用：
        1.取出一个查询的第一行记录。
        2.取出一个查询的前N行记录。
--用例1：   
SELECT ROWNUM,deptno,dname,loc FROM dept;

--用例2：取出一个查询的第一行记录。
SELECT * FROM emp WHERE ROWNUM=1;

--用例3：取出一个查询的前N行记录。
SELECT * FROM emp WHERE ROWNUM>5;

--用例4：经典应用，分页显示。ROWNUM 数据伪列前N 行查询的语法
SELECT * FROM(
    SELECT 列1[,列2,...],ROWNUM rownum 别名
    FROM 表名称 [别名]
    WHERE ROWNUM <= (currentPage(当前所在页) * lineSize(每页显示记录行数)) temp
WHERE temp.rownum 别名 >(currentPage(当前所在页)-1)*lineSize(每页显示记录行数);


--显示雇员表中前5条记录。
SELECT * FROM(
    SELECT empno,ename,job,hiredate,sal,mgr,deptno,ROWNUM rn
    FROM emp WHERE ROWNUM<=5) temp
) temp.rn>0;



4.3 ORACLE 12C 新特性，（方便数据分页显示操作，指定范围内的操作数据）
FETCH语法：
    SELECT [DISTIINCT] [][] 
    FROM 表名称1 [表别名1],表名称2 [表别名2],...
    [WHERE 条件(s)]
    [GROUP BY 分组字段1,分组字段2]
    [HAVING 过滤条件(s)]
    [ORDER BY 排序字段 ASC|DESC]
    [FETCH FIRST 行数]|[OFFSET 开始位置 ROWS FETCH NEXT 个数]|[FETCH NEXT 百分比 PERCENT] ROW ONLY 
    
    1.获取前N行记录：FETCH FIRST 行数  ROW ONLY ;
    2.取得指定范围的记录: OFFSET 开始位置 ROWS FETCH NEXT 个数 ROW ONLY ;
    3.按照百分比取得记录：FETCH NEXT 百分比 PERCENT ROW ONLY 

--用例1：取得emp表中的前5行记录。
SELECT * FROM emp FETCH FIRST 5 ROW ONLY;

--用例2：为数据排序，取得前5行记录。
SELECT * FROM emp 
ORDER BY sal DESC 
FETCH FIRST 5 ROW ONLY;

--用例3：取得指定范围的记录，取得4~5条记录。（下标为0，所有行从0行开始）
SELECT * FROM emp 
ORDER BY sal DESC 
OFFSET 3 ROWS FETCH NEXT 2 ROW ONLY; /*下标为0，所有行从0行开始*/

--用例4：按百分比取部分数据。
SELECT * FROM emp 
ORDER BY sal DESC 
FETCH NEXT 10 PERCENT ROW ONLY ;


5 索引 （利用ROWID查找是最高效的做法）
    在数据库中，索引是一种专门用于数据库查询操作性能的一种手段。在 Oracle中，为了维护这种查询性能，需要对某一类数据进行指定结构的排列。但是针对 Oracle 中，针对不同的情况会有不同的索引使用。
    索引有：
      1. B树索引
      2. 降序索引
      3. 位图索引
      4. 函数索引

5.1 B*Tree 索引 （二叉树索引）
   B树索引是最基本的索引结构，在 Oracle 中默认建立的索引就是此类型索引。一般B树索引在检索高基数数列（该列上的重复内容较少或没有）的时候可以提高性能的检索操作。
   原理：
      1. 取得第一个数据作为根节点。
      2. 比根节点小的数据放在左子树，比根节点大的数据放在右子树。      

--在Oracle 中想要创建 B*Tree 索引，有以下两种方式：
    1. 当某一列上设置了主键约束或唯一约束，则会自动创建索引。（PK、UK）
    2. 利用命令直接创建索引： CREATE INDEX [用户名.]索引名称 ON [用户名.]表名称 (列名称 [ASC|DESC],...);

--用例1：
CREATE INDEX emp_sal_ind ON emp(sal);
--查看哪张表上存在索引。
SELECT index_name,index_type,table_owner,table_name,uniqueness,status FROM user_indexes;
--查看索引所在列。
SELECT * FROM user_ind_columns WHERE lower(index_name)='emp_sal_ind';

--用例2：在 hiredate 字段上设置降序索引
CREATE INDEX emp_hiredate_ind_desc ON emp(hiredate);

--用例3：创建函数索引
CREATE INDEX emp_ename_ind ON emp(LOWER(sal));


5.2 位图索引
    如果现在某一列上的数据都数据低基数(Low - Cardinality)列的时候,就可以利用位图索引来提升查询性能。例如deptno,春夏秋冬。
    位图索引以一种压缩数据的格式存放，因此所占用的磁盘空间要比 B*Tree 索引小很多。

语法：
创建位图索引：
    CREATE BITMAP INDEX [用户名.]索引名称 ON [用户名.]表名称 (列名称 [ASC|DESC],...);
--用例1：
CREATE BITMAP INDEX emp_deptno_ind ON emp(deptno);
SELECT index_name,index_type,table_owner,table_name,uniqueness,status FROM user_indexes;

5.3 删除索引
语法：
DROP INDEX 索引名称;
--用例1：
DROP INDEX emp_deptno_ind;