oracle学习笔记-未整理

/*
在oracle数据库内有一种特殊的表dual。
dual表是oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的select中。
dual表由oracle连同数据字典一同创建，所有的用户都可以用名称dual访问该表。
这个表里只有一列dummy，该列定义为varchar2(1)类型，有一行值x。
从dual表选择数据常被用来通过select语句计算常数表达式，由于dual只有一行数据，所以常数只返回一次。
*/

/*
字符串函数
*/
select upper ('abcde') from dual ;
select * from emp where ename=upper('smith') ;

select lower('abcde') from dual ;

select initcap(ename) from emp;

select concat('a','b') from dual;
select 'a' || 'b' from dual;

select instr('hello world','or') from dual;/*8*/


select lpad('love',10,'u') from dual;/*uuuuuulove*/
select trim('        love     ') from dual;/*love*/
select rpad('love',10,'u') from dual;

/*
数值函数
*/
/*四舍五入*/
select round(570.186,-2) from dual;/*600*/    
select round(570.186,2) from dual;/*570.19*/
/*截取*/
select trunc(570.186,-2) from dual;/*500*/
select trunc(570.186,2) from dual;/*570.13*/

select mod(5,2.1) from dual;/*0.8*/

/*
round函数对日期进行“四舍五入”，trunc函数对日期进行截取。
如果我们不指定格式的话，round会返回一个最接近date参数的日期，而trunc函数只会简单的截取时分秒部分，返回年月日部分。
*/
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now_date,
       to_char(round(sysdate), 'yyyy-mm-dd hh24:mi:ss') round_date,
       to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss') trunc_date
  from dual;
/*2012-12-10 17:24:50          2012-12-11 00:00:00      2012-12-10 00:00:00*/

select next_day(sysdate,'星期一') from dual;

select last_day(sysdate) from dual;

select to_char(sysdate,'yyyy') from dual; 
select to_char(sysdate,'fmyyyy-mm-dd') from dual; 
select to_char(sal,'l999,999,999') from emp;
select to_char(sysdate,'d') from dual;

select to_number('13')+to_number('14') from dual;

select to_date('20121221','yyyymmdd') from dual;

select add_months(sysdate,1) from dual;

--日期求月差
select months_between(to_date(201612,'yyyymm'),to_date(201202,'yyyymm')) from dual;

select empno,
       ename,
       sal,
       case deptno
         when 10 then
          '财务部'
         when 20 then
          '研发部'
         when 30 then
          '销售部'
         else
          '未知部门'
       end 部门
  from emp;

select empno,
       ename,
       sal,
       decode(deptno, 10, '财务部', 20, '研发部', 30, '销售部', '未知部门') 部门
  from emp;

select substr(ename,0,1) from emp;/*显示所有员工的姓名的第一个字*/

select * from emp where to_char(hiredate,'fmmm')='2';

/*
分组函数
*/
select avg(comm) from emp;
select sum(comm) from emp;

/*
出现在select列表中的字段或者出现在order by 后面的字段，如果不是包含在分组函数中，那么该字段必须同时在group by子句中出现。
*/

select empno, sal from emp; --合法
select avg(sal) from emp; --合法
select initcap(ename), avg(sal) from emp; --非法
/*不允许在 where 子句中使用分组函数。*/
select deptno, avg(sal)
from emp
--where avg(sal) > 2000;
group by deptno;

/*
在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行。而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)。HAVING子句可以让我们筛选成组后的各组数据。
HAVING子句在聚合后对组记录进行筛选，而WHERE子句在聚合前先筛选记录。也就是说作用在GROUP BY 子句和HAVING子句前。
*/
select deptno, job, avg(sal) as avgsal
  from emp
 where hiredate >= to_date('1981-05-01', 'yyyy-mm-dd')
 group by deptno, job
having avg(sal) > 1200
 order by avgsal;

--得到工资大于自己部门平均工资的员工信息
select *
  from emp e1, (select deptno, avg(sal) avgsal from emp group by deptno) e2
 where e1.deptno = e2.deptno
   and e1.sal > e2.avgsal;

/*多表查询*/
select empno, ename, sal, emp.deptno, dname
  from emp, dept
 where emp.deptno = dept.deptno;
 
select e.empno, e.ename, m.empno, m.ename
  from emp e, emp m
 where e.mgr = m.empno;
 
 --topn查询
select * from (select * from emp order by sal desc) where rownum <= 5;

--分页查询
select *
  from (select rownum no, e.* from (select * from emp order by sal desc) e)
 where no >= 3
   and no <= 5;

--我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询


--得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置
select deptno
  from (select deptno, sum(sal) sumsal
          from emp
         group by deptno
         order by sumsal) e
 where rownum = 1;

/*
高级查询
*/
--随机返回5条记录
select *
  from (select ename, job from emp order by dbms_random.value())
 where rownum <= 5;

--处理空值排序
select * from emp order by comm desc nulls last;
--select * from emp order by comm desc nulls first;

--求和
--按部门“连续”求总和
select ename,deptno,sal,sum(sal) over (partition by deptno order by ename) from emp;

--不按部门，求所有员工总和，效果等同于sum(sal)。
select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from emp;

--根据子串分组
select to_char(hiredate,'yyyy'),avg(sal) from emp group by to_char(hiredate,'yyyy');

--计算一年内天数
--add_months返回date加上integer个月的日期
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;

/*
ddl
*/
--使用子查询创建表
create table myemp as select * from emp;
--添加字段
alter table emp add age number(5);
--删除字段
alter table emp drop column age;
--清空表中数据
truncate table myemp;
--删除表
drop table myemp;
--重命名表
rename myemp to empcopy;
/*
dml
*/

/*
创建视图
*/
create view empv20 (empno,ename,sal) as select empno,ename,sal from scott.emp where deptno=20 ;

/*
将之前的一个复杂语句包装成视图
显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资：
*/
create or replace view empv20 (deptno,msal) as
 (select deptno,min(sal) from scott.emp 
         group by deptno 
         having min(sal)>(select min(sal) from scott.emp where deptno=20)) ;
--select * from empv20;
--drop view empv20;

/*
创建索引
*/
select * from user_indexes;-- 查询现有的索引
select * from user_ind_columns;-- 可获知索引建立在那些字段上

/*
建立索引的优点
1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
索引的缺点
1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
*/

/*
创建索引的原则
创建索引：创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。 
--在select操作占大部分的表上创建索引； 
--在where子句中出现最频繁的列上创建索引； 
--在选择性高的列上创建索引（补充索引选择性，最高是1，eg：primary key） 
--复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二
列……。 
--小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。
*/