oracle部分整理
一、子查询
查询的注意地方
1. 括号
2. 合理的书写风格
3. 可以在主查询的where select having from后面都可以放置子查询
4. 不可以在group by放置子查询
5. 强调from后面的子查询
6. 主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用 即可
7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
8. 一般先执行子查询，再执行主查询；但相关子查询例外
9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10. 子查询中null（参考http://www.cnblogs.com/lulu638/p/4498098.html）
SQL> --6. 主查询和子查询可以不是同一张表；只要子查询返回的结果主查询可以使用 即可 SQL> --查询部门名称是SALES的员工 SQL> select * 2 from emp 3 where deptno=(select deptno 4 from dept 5 where dname='SALES'); SQL> --SQL优化原则： 理论上，尽量使用多表查询 SQL> host cls
SQL> --in 在集合中 SQL> --查询部门名称是SALES和ACCOUNTING的员工 SQL> select * 2 from emp 3 where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');
SQL> --any:和集合中的任意一个值比较 SQL> --查询工资比30号部门任意一个员工高的员工信息 SQL> select * 2 from emp 3 where sal > any (select sal from emp where deptno=30);
SQL> --all: 和集合中的所有值比较 SQL> --查询工资比30号部门所有员工高的员工信息 SQL> select * 2 from emp 3 where sal > all (select sal from emp where deptno=30);
分页的例子：
--第5-8条记录 select * from (select rownum r,e1.* from (select * from emp order by sal) e1 where rownum <=8 ) where r >=5;
相关子查询：
需求：员工表中薪水大于本部门平均薪水的员工
SQL> --相关子查询： 将主查询中的值作为参数传递给子查询 SQL> select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal 2 from emp e 3 where sal > (select avg(sal) from emp where deptno=e.deptno);
sql类型
SQL的类型:
1. DML(Data Manipulation Language 数据操作语言): select insert updete delete
2. DDL(Data Definition Language 数据定义语言): create table,drop table,truncate table
create/drop view,sequence,index,synonym(同义词)
3. DCL(Data Control Language 数据控制语言): grant(授权) revoke(撤销权限）
海量拷贝数据（移动数据）
1. 数据泵(导出 导入 拷贝): PLSQL程序
2. SQL*Loader SQL>
3. 外部表(external table)
delete和truncate的区别：
1. delete逐条删除；truncate先摧毁表，再重建
2.(根本)delete是DML(可以回滚) truncate是DDL（不可以回滚）
3. delete不会释放空间 truncate会 (原因：undo表空间 undo数据)
4. delete会产生碎片 truncate不会
5. delete可以闪回(flashback) truncate不可以
Oracle中事务的标志
1. 起始标志: 事务中第一条DML语句
2. 结束标志： 提交： 显式 commit
隐式 正常退出exit
回滚 显式 rollback
隐式 非正常退出,掉电，宕机
数据库对象
视图
·视图是一种虚表
·必须建立在已有表的基础上
·不能存储数据，只能通过操作查看数据
·只能简化查询，不能提高查询的性能
·视图只能创建替换与删除，不能修改
SQL> create or replace view empinfoview 2 as 3 select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname 4 from emp e, dept d 5 where e.deptno=d.deptno 6 with read only; 视图已创建。
序列化
可供多个用户来产生唯一数值的数据库对象
·自动提供唯一的数值
·共享对象
·主要用于提供主键值
·将序列装入内存可以提高访问效率
SQL> create sequence myseq; 序列已创建。
nextval和currval伪列
nextval：返回序列中下一个有效的值，任何用户都可以引用
currval：中存放序列的当前值
nextval应在currval之前定义，二者应同时有效。
使用序列
序列在以下情况会出现裂缝：
·回滚
·系统异常
·多个表同时使用同一个序列
索引
· 通过指针加速oracle的查询速度
·删除一个表时，所有基于该表的索引会自动删除
·通过快速定位数据的方法，减少磁盘的i/o
·索引一旦建立，有oracle统一管理
原理图
自动创建：主要用于定义primary key 或unique约束后系统自动在相应的列上创建唯一性索引
手动创建：用户在其他列上创建非唯一性的索引，以加速查询
创建索引的要求
·列中的数据分布范围 广
·列经常在where子句或链接条件中出现
·列经常被访问而且数量巨大，访问的数据大概占总量的2%到4%
SQL> --索引 index SQL> create index myindex 2 on emp(deptno); 索引已创建。
同义词
·方便访问其他用户的对象
·缩短对象名称的长度
SQL> --为hr.employees表取别名---> 同义词 SQL> create synonym hremp for hr.employees; create synonym hremp for hr.employees
后续 PLSQL&存储过程存储函数&触发器--链接
http://files.cnblogs.com/files/lulu638/PLSQL%26%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E5%AD%98%E5%82%A8%E5%87%BD%E6%95%B0%26%E8%A7%A6%E5%8F%91%E5%99%A8.rar