重识Oracle

重识Oracle

sqlplus命令行常用工具

host cls 	    // 清屏
/	 	    // 执行上一条命令
set linesize	    // 行的宽度
col 列名 for a60	    // 设置列的宽度
break on 列名	    // 相同的列名只显示一次
skip [2]	    // 跳过2行
set pagesize 30     // 每页显示30条数据
@D:xxx		    // 执行路径下sql文件

执行计划

问：什么是执行计划？

答：所谓执行计划，顾名思义，就是对一个查询任务，做出一份怎样去完成任务的详细方案。举个生活中的例子，我从珠海要去英国，我可以选择先去香港然后转机，也可以先去北京转机，或者去广州也可以。但是到底怎样去英国划算，也就是我的费用最少，这是一件值得考究的事情。同样对于查询而言，我们提交的SQL仅仅是描述出了我们的目的地是英国，但至于怎么去，通常我们的SQL中是没有给出提示信息的，是由数据库来决定的。（摘自xhfk1982的博客）

查看执行计划：

explain plan for xxxsql    // 编译
select * from table adbms_xplan.display    // 查看

分组函数

avg（平均值）、sum（总和）、min（最小值）、max（最大值）、count（个数）
wm_concat（行转列）：会用逗号拼接结果
distinct（去掉重复数） 例：select (distinct xx列名) ...

注：分组函数会忽略空值
解决：使用nvl函数
例：select count( nvl(comm,0) ) from emp;
解释：如果comm为空则返回第二个参数0，不为空值返回为第一个。

group by分组函数

在select列表中所有未包含在组函数中的列都应该在group子句中。

例：select a,b,c 组函数(x) from table group by a,b,c必须有

注：where子句中本包涵分组函数

order by排序函数

可以按照列、列名、表达式、序号进行排序

例：select deptno,avg(sal) as 平均工资
       group by deptno
       order by ____  avg(sal)/avg(sal)/平均工资/2

多表查询

外链接where

类似inner join。

where e.deptno(+) = d.deptno。用法于加号的位置相反（sql为右外连接）

自连接

select e.name 员工姓名,b.ename 老板姓名
from emp e,emp b
where e.mgr = b.empno;

自连接不适合作用于大表，会造成笛卡尔集太大，效率太低。

解决办法：使用层次查询（操作单表）

遍历的sql：

from emp 
connect by prior [上一层的员工号] = [老板号]
start whit [条件，从什么开始遍历]

例：
from emp
connect by prior empno = mgr
start with empno = xxx;    // 从什么开始遍历
// 如果要查询所有节点，可以使用：
start with mgr is null;    // 老板号为空，故为根

子查询

注：使用位置，where、select、having、from。不可使用的位置，group bu语句中。

Rownum

注：只能使用<、<=；不能使用>、>=

多行操作符

in：匹配多个值

any：匹配任意一个值

all：跟所有值作比较

多行子句遇到的空值问题

select * from emp
where empno in (slect mgr from emp);    // in相当于any

select * from emp
where empno not in (select mgr from emp);    // not in相当于<> all。故mgr有空值时会报错

// 解决：需要在子句中添加
where mgr is not null;

decode函数

decode(列名,'xxx',值1,值2);

如果列值等于xxx返回值1，否则返回值2。类似于三元表达式

闪回

Commit提交回滚后怎么恢复数据呢？用闪回

注意：在删除数据回滚后，不能做dll操作，不然无法闪回表。

alter table tablename enable row movement;
flashback table tablename to timestamp to_timestamp('2018-12-04 08:30:00','yyyy-mm-dd hh24:mi:ss');

还会有很多参数，自行查询。