Oracle SQL(三)

视图

特性简单视图复杂视图关联的表数量1个1个或多个查询中包含函数否是查询中包含分组数据否是允许对视图进行DML操作是否 CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]]; CREATE VIEW viewname (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name; --删除视图 DROP VIEW viewname; --top n SELECT [column_list], ROWNUM FROM (SELECT [column_list] FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N; --查询工资最低的三个人 SELECT salary ,last_name ,e_rownum ,rownum FROM (SELECT rownum e_rownum ,e.salary ,e.last_name FROM employees e ORDER BY e.salary) WHERE rownum <= 3;

序列,索引

序列

CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; 从序列取值： CURRVAL 取当前值， NEXTVAL --更改序列 ALTER SEQUENCE sequencename INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; --删除序列 DROP SEQUENCE sequencename;

索引

CREATE INDEX indexname ON tablename(columnsname);

适合创建索引：

查询条件中使用到这个列（或者这个列于其他列的组合），且这个列（或者与其他列的组合）上的数字量 范围跨度很大，而大多数情况下我们要获取的数据的量占整个表的数据总量 小于4%。

不适合创建索引：

被查询的表本身就很小，即是是全表扫描也非常快； ； 或者基于这张表的查询，大多数情况下需要获取的数据量都超过了总量的4% ；或者这张表需要频繁的被更新，建立索引的话会引起索引的频繁更新，从而反而降低数据库的整体效率。

函数索引：

当查询语句的Where 条件中，对于某些列使用了函数表达式时，必须创建函数索引。

SELECT * FROM departments WHERE UPPER(department_name) = 'SALES'; --对于上语句，需要建立函数索引 CREATE INDEX indexname ON departments(UPPER(department_name));

同义词

当数据库用户A 要访问数据库用户B 中的一张表Table1 的时候，需要加前缀Select * from B.table1

我们要通过DB-LINK 访问另一个数据库中的某张表的时候我们需要加@ 后缀Select * from table1@db-link-name

CREATE SYNONYM Table1 for B.Table1 -- 建立同义词 Select * from Table1 -- 同义词访问

集合

集合功能union去除重复记录union all保留重复记录intersect取交集minus取差集(前面-交集)

用户权限

--过角色来简化管理 CREATE ROLE manager ; GRANT create table, create view to manager; GRANT manager to DEHAAN, KOCHHAR; WITH GRANT OPTION;--有授权的权限 --让所有人拥有权限 GRANT select ON tablename TO PUBLIC;

对象权限：区别于系统权限，细化到某个具体的数据库对象上的权限访问控制

通过数据字典查询系统中的赋权情况：

数据字典视图描述ROLE_SYS_PRIVS角色对应的系统权限ROLE_TAB_PRIVS角色对应的表权限USER_ROLE_PRIVS用户的角色分配表USER_TAB_PRIVS_MADE用户对象上赋权者与被赋者的历史赋权情况USER_TAB_PRIVS_RECD用户对象上拥有者与被赋者的历史赋权情况USER_COL_PRIVS_MADE用户对象列上赋权者与被赋者的历史赋权情况USER_COL_PRIVS_RECD用户对象列上拥有者与被赋者的历史赋权情况USER_SYS_PRIVS用户的系统权限 --收回权限 REVOKE {privilege [, privilege...]|ALL} ON object FROM {user[, user...]|role|PUBLIC} CREATE PUBLIC DATABASE LINK linkname USING username; SELECT * FROM emp@linkname;

Group By 子句增强

rollup():Rollup 后面跟了n个字段，就将进行n+1 次分组，从右到左每次减少一个字段进行分组；然后进行union

cube:Cube 产生Rollup 结果集 + 多维度的交叉表数据源

ROLLUP只对第一个参数的列进行统计，而CUBE都会进行统计。

Rollup(a,b,c): (a,b,c),(a,b),(a),(全表)

cube(a,b,c):(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),(全表)

Grouping 函数； 没有被Grouping 到返回1 ，否则返回0

SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id);

使用Grouping Sets 来代替多次UNION.

GROUPING SETS的效果:1.只需要访问一次基表.2.不需要写很复杂的UNION语句.

子查询增强

子查询分标量子查询、表子查询。

标量子查询：子查询的返回值必须只有一行记录，而且只能有一个列。可以用在select语句的列表中、表达式中、where语句中等。

表子查询：列值子查询可以返回一个多行多列的结果集。表子查询可以看作一个临时表，表子查询可以用在select语句的from子句、insert语句、连接、in子句等。

非相关子查询：独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询,并且它是优先于外部查询先执行的，他执行了再执行外部。(非相关子查询当作一张表使用)

相关子查询：执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。并且是外部先查询一次，然后再执行一次内部查询。效率低。

--非相关子查询 SELECT a.last_name, a.salary, a.department_id, b.salavg FROM employees a, (SELECT department_id, AVG(salary) salavg FROM employees GROUP BY department_id) b WHERE a.department_id = b.department_id AND a.salary > b.salavg; --相关子查询 SELECT last_name, salary, department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id =outer.department_id) ;

Not In 里面 只要有一个NULL，结果就为null

in：只执行一次,它查出B表中的所有内容并缓存起来

exists:不缓存结果集，会执行多次

子查询结果集小,用IN，外表小,子查询表大,用EXISTS

连接查询不需要建立临时表，因此其速度比子查询快。

WITH

如果在后面多次使用则可以简化SQL适当提高性能 --相当于建了个e临时表 with e as (select * from user) select * from e; --相当于建了e、d临时表 with e as (select * from user), d as (select * from role) select * from e, d where e.id = d.uid;

递归查询

递归查询： 使用语句SQL 语句即可把整个递归树全部查询出来。

select … from tablename start with 条件1 connect by 条件2 where 条件3;--过滤条件 --PRIOR表示上一条记录 -- CONNECT BY PRIOR org_id = parent_id；就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。

使用LEVEL 关键字和 LPAD 函数 ：显示树形层次

lpad函数：将左边的字符串填充一些特定的字符

lpad( string1, padded_length, [ pad_string ] ) padded_length<string1.length --截断 padded_length>string1.length --左边加pad_string lpad('tech', 8, '0'); --0000tech

Insert增强

Insert…Select :使用Insert Select实现同时向多个表插入记录

insert first：对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。

insert all ：对于每一行数据，对每一个when条件都进行检查，如果满足条件就执行插入操作。

--一个来源插入多个目标表 INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id > 200; INSERT ALL WHEN condition THEN insert_into_clause values_clause [WHEN condition THEN] [insert_into_clause values_clause] …… [ELSE] [insert_into_clause values_clause] Subquery； --一个来源插入多个目标表（有条件，首次匹配即跳到下一条） INSERT FIRST WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE) SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees GROUP BY department_id;

SQL进阶

over分析函数

sum(sal) over (partition by deptno) 按部门求薪酬总和 um(sal) over (partition by deptno order by sal) 按部门累计薪酬 部门编号员工姓名员工编码薪酬按部门求薪酬总和按部门累计薪酬7934MILLER101300875013007782CLARK102450875037507839KING10500087508750 rank()是跳跃排序，有两个第二名时接下来就是第四名dense_rank()l是连续排序，有两个第二名时仍然跟着第三名 SELECT d.department_name, e.last_name, e.salary, rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank1, dense_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2, row_number() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank3 FROM employees e, departments d WHERE 1 = 1 AND e.department_id = d.department_id;

oracle的分析函数over(Partition by...) 及开窗函数

查询之前一段时间内修改行原先的值

闪回的时效是有限制的，具体能闪回到多久之前的数据和数据库的设置有关系

SELECT * FROM departments AS OF TIMESTAMP SYSDATE-5/(24*60) WHERE department_name = 'Finance';

全局临时表global

基于会话

CREATE GLOBAL TEMPORARY TABLE temp_table_session (...) ON COMMIT PRESERVE ROWS;

基于会话的临时表，在会话断开之前，或者通过一个delete 或truncate 物理地删除行之前 ，这些行会一直存在于这个临时表中。只有我的会话能看到这些行，即使我已经提交，其他会话也无法看到我的行。

基于事务

CREATE GLOBAL TEMPORARY TABLE temp_table_session (...) ON COMMIT DELETE ROWS;

基于事务的临时表，我的会话提交时，临时表中的行就不见了。只需把分配给这个表的临时区段交回,这些 行就会消失，在临时表的自动清除过程中不存在开销。

物化视图(materialized)

物化 视图使用的也比较普遍，是一种特殊的物理表，有别于普通的数据视图。在一段时间内保存固定的数据，提供自动刷新和手动刷新的机制

CREATE MATERIALIZED VIEW materialized_view_name BUILD [IMMEDIATE|DEFERRED] --1. 创建方式 REFRESH [COMPLETE|FAST|FORCE|NEVER] --2. 物化视图刷新方式 ON [COMMIT|DEMAND] --3. 刷新触发方式 START WITH (start_date) --4. 开始时间 NEXT (interval_date) --5. 间隔时间 WITH [PRIMARY KEY|ROWID] --6. 默认 primary key ENABLE QUERY REWRITE --7. 是否启用查询重写 AS --8. 关键字 SELECT STATEMENT; --9. 基表选取数据的 select 语句 build immediate是在创建物化视图的时候就生成数据。build deferred则在创建时不生成数据，以后根据需要在生成数据。

刷新触发方式

on demand 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH).默认on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新.速度慢

物化视图刷新方式

完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。快速刷新（FAST）： 采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

Oracle物化视图的一般使用

length是字符个数，lengthb 指的是字节数。

substr值是按字来取值,substrb按字节取值。

VPD 全称Virtual Private Database：提供了对 数据库信息的细粒度访问控制，通过策略函数来实现的具体的控制。

通过查询数据字典，确认数据库对象上是否有策略函数：SELECT * FROM dba_policies t WHERE t.object_name = 'EMPLOYEES';

转载于:https://www.cnblogs.com/sufferingStriver/p/9403462.html

相关资源：Oracle数据库学习日记