Oracle拾遗

SQL优化的原则：

1. 尽量使用列名
2. where解析的顺序： 右–》 左
3. 尽量使用where (少用having,需要使用组函数除外)
4. 尽量使用多表查询 (少用子查询)
5. 尽量使用union all (在有索引情况下union all效率比where 跟or 或者 in的效率高,不然不如)
   (ps: union = union all + distinct)
6. 尽量不要使用集合运算

---

当前用户,所有表, 表结构

SQL> –当前用户
SQL> show user

SQL> –当前用户的表
SQL> select * from tab;

SQL> –员工表的结构
SQL> desc emp

---

SQL中的null值：

SQL> 1. 包含null的表达式都为null
SQL> 2. null永远！=null
SQL> –null值 3. 如果集合中含有null，不能使用not in；但可以使用in

SQL> select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;

SQL> –2. null永远！=null
SQL> –查询奖金为null的员工
SQL> select *
2 from emp
3 where comm=null;

未选定行

SQL> select *
2 from emp
3 where comm is null;

SQL> –查询员工信息，按照奖金排序
SQL> –null值4. null的排序,–原因：空值最大

select * from emp order by comm desc nulls last 

SQL> –null 值 5. 组函数（多行函数）自动滤空;

select count(*),count(nvl(comm,0)) from emp

---

Order by

select * from emp order by salary,deptno;
select * from emp order by salary, deptno desc;
select * from emp order by salary desc, deptno desc;

order by作用于后面所有的列；desc只作用于离他最近的列

---

转大写, 小写, 首字母大写

SQL> select lower(‘Hello WOrld’) 转小写,upper(‘Hello WOrld’) 转大写,initcap(‘hello world’) 首字母大写 from dual;
hello world HELLO WORLD Hello World

---

substr

SQL> –substr(a,b) 从a中，第b位开始取
SQL> select substr(‘Hello World’,3) 子串 from dual;

子串 : llo World

SQL> –substr(a,b,c) 从a中，第b位开始取,取c位
SQL> select substr(‘Hello World’,3,4) 子串 from dual;

子串 : llo

---

instr 在字符串中查找

–instr(a,b)
select instr(‘Hello World’,’ll’) 位置 from dual;

位置 : 3

---

左右填充

SQL> –lpad 左填充 rpad 右填充
SQL> – abcd —> 10位
SQL> select lpad(‘abcd’,10,’‘) 左,rpad(‘abcd’,10,’‘) 右 from dual;

左         右                                                                                                                                         ---------- ----------                                                                                                                                 ******abcd abcd******    

---

四舍五入

SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;

    一         二         三         四         五                                                                                                

---

 45.93       45.9         46         50          0                                                                                                

SQL> –截断

select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五 from dual    一         二         三         四         五                                                                                                

---

 45.92       45.9         45         40          0                                                                                                

---

日期

SQL> –格式化时间
SQL> select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;

SQL> –months_between 相差的月数
SQL> select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二
2 from emp;

SQL> –add_months
SQL> –53个月后
SQL> select add_months(sysdate,53) from dual;

SQL> –last_day
SQL> select last_day(sysdate) from dual;

SQL> –next_day
SQL> –下一个星期五
SQL> select next_day(sysdate,’星期五’) from dual;

---

null运算中处理

SQL> –通用函数
SQL> –nvl2(a,b,c) 当a=null的时候，返回c；否则返回b
SQL> select sal*12+nvl2(comm,comm,0) from emp;

SQL> –nullif(a,b) 当a=b的时候，返回null；否则返回a
SQL> select nullif(‘abc’,’abc’) 值 from dual;

---

判断语句

 select empno,ename,job,sal 涨前,           case job when 'PRESIDENT' then sal+1000                    when 'MANAGER' then sal+800                    else sal+400            end 涨后    from emp;

 select empno,ename,job,sal 涨前,           decode(job,'PRESIDENT',sal+1000,                      'MANAGER',sal+800,                                sal+400) 涨后    from emp;

---

组函数

–多个列的分组
SQL> select deptno,job,sum(sal)
2 from emp
3 group by deptno,job
4 order by 1;
-group by :先按照第一个列分组；再按照第二列分组，以此类推-

where和having的区别：where后面不能使用多行函数

---

多表连接

内连接

SQL> –等值连接
SQL> select e.empno,e.ename,e.sal,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno;

SQL> – 不等值连接
SQL> select e.empno,e.ename,e.sal,s.grade
2 from emp e,salgrade s
3 where e.sal between s.losal and s.hisal;

外连接

SQL> 希望：对于某些不成立的记录（40号部门），任然希望包含在最后的结果中
SQL> 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中
SQL> 写法：where e.deptno=d.deptno(+)
SQL> 右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中
SQL> 写法: where e.deptno(+)=d.deptno
SQL> */
SQL> select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数
2 from emp e,dept d
3 where e.deptno(+)=d.deptno
4 group by d.deptno,d.dname;

部门号 部门名称             人数                                            

---

    10 ACCOUNTING              3                                                40 OPERATIONS              0                                                20 RESEARCH                5                                                30 SALES                   6        

自连接

SQL> –自连接：通过表的别名，将同一张表视为多张表
SQL> select e.ename 员工姓名,b.ename 老板姓名
2 from emp e,emp b
3 where e.mgr=b.empno;

–自连接：不适合操作大表

---

子查询

注意的问题：

SQL> 1. 合理的书写风格
SQL> 2. 括号
SQL> 3. 可以在主查询的where select having from后面使用子查询
SQL> 4. 不可以在group by后面使用子查询
SQL> 5. 强调from后面的子查询
SQL> 6. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用即可
SQL> 7. 一般不在子查询排序；但top-n分析问题中，必须对子查询排序
SQL> 8. 一般先执行子查询，再执行主查询；但相关子查询例外
SQL> 9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10. 子查询中的null

解决问题10,子查询中的null

 select * from emp where empno not in (select mgr from emp where mgr is not null);