项目中用到的SQL-总结
基本sql总结：
Group by的理解：having子句，分组函数 Group by使用的限定： 1.出现在Select列表中的字段或者出现在order by后面的字段，如果不是包含在分组函数中，那么该字段必须同时在Group by子句中出现。 2.包含在Group by子句中的字段则不必须出现在在Select列表中。 3.如果没有Group by子句，Select列表中不允许出现字段（单行函数）与分组函数混用的情况 4.不允许在where子句中使用分组函数，用having子句替代 看下面的例子： select empno, sal from emp;--合法 select avg(sal) from emp; --合法 select initcap(ename), avg(sal) from emp ; --非法单行函数不能和分组函数混用，在没有Group by的情况下 select ename,avg(sal) from emp;--非法 下面看几个例子： 统计各部门下平均工资大于500的部门 select deptno,avg(sal) from emp group by deptno having avg(sal)>500 ; 算出部门30中得到最多奖金的员工姓名 （第3点，不允许出现字段和分组函数混合使用的情况） select ename from emp where comm=(select max(comm) from emp); 算出每个职位的员工数和最低工资 select job,min(sal),count(*) from emp group by job; 得到工资大于自己部门平均工资的员工信息 （自连接加Group by） select * from emp e1, (select deptno, avg(sal) as avgsal from emp group by deptno) e2 where e1.deptno = e2.deptno and e1.sal > e2.avgsal; 分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金) select deptno, job, avg(nvl(comm, 0)), sum(sal + nvl(comm, 0)) from emp group by deptno, job; 分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500 select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500; --这里体现了where Group by having的执行顺序 运行结果 DEPTNO AVG(SAL) 1 30 2850 2 20 2991.66666666667 3 10 3725 得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置 select * from dept where deptno = ( select e.deptno from (select deptno,sum(sal) from emp group by deptno order by sum(sal))e where rownum=1 ); 删除一张表的重复记录： select d.rowid,d.* from dept d; DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON insert into dept values(90,'ACCOUNTING','NEW YORK'); 表里面的数据： DEPTNO DNAME LOC 90 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 查询表里面的重复记录：（使用自连接） select d1.deptno from dept d1, dept d2 where d1.deptno > d2.deptno and d1.dname = d2.dname and d1.loc = d2.loc 结果为： DEPTNO 1 90 里面有重复记录，然后删除重复记录： 保留重复记录中主键最小的那条记录，删除重复的记录： delete from dept where deptno not in (select min(deptno) from dept d group by d.dname, d.loc); 1. 机构对应的余额（一个字段的Group by） SELECT LN.FR_CZJG, FR_YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN FR_CZJG FR_YE 10889 059200001 5000000.00 1961 059200001 12000000.00 34393 059200001 5000000.00 。。。。。。。 FR_CZJG FR_YE 15993 079000001 10000000.00 15992 079000001 1000000.00 15994 079000001 1000000.00 2.按照机构代码统计余额 SELECT LN.FR_CZJG--机构代码 , SUM(LN.FR_YE) AS FR_YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN WHERE (LN.FR_YWLX IN ('1','6') OR (LN.FR_YWLX = '5' AND LN.FR_CPLB NOT IN ('2160010','2160020','2160030','2160040'))) AND LN.FR_SJTX IS NOT NULL--贷款实际投向，行业门类 AND LN.FR_HB = 'CNY' AND LN.FR_KJRQ = '20130930' GROUP BY LN.FR_CZJG （1）把上面相同的机构对应的余额进行汇总 机构代码 余额 FR_CZJG FR_YE 17 059200001 799393381.25 第一组 50 079000001 549080000 第二组 3.查询贷款质量 对应的余额 SELECT LN.FR_DKZL , FR_YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN FR_DKZL FR_YE 2595 QL05 500000.00 7 QL05 7008440.00 51 QL05 1600000.00 4652 QL04 200000.00 3755 QL04 4000000.00 2778 QL04 3320000.00 4.按照贷款质量统计余额 SELECT LN.FR_DKZL , SUM(LN.FR_YE) AS FR_YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN WHERE (LN.FR_YWLX IN ('1', '6') OR (LN.FR_YWLX = '5' AND LN.FR_CPLB NOT IN ('2160010', '2160020', '2160030', '2160040'))) AND LN.FR_SJTX IS NOT NULL --贷款实际投向，行业门类 AND LN.FR_HB = 'CNY' AND LN.FR_KJRQ = '20130930' GROUP BY LN.FR_DKZL （1）把相同贷款质量对应的月余额进行汇总 贷款质量 余额 FR_DKZL FR_YE 1 QL04 10396932.25 2 QL05 3610000 3 QL03 13149921.74 4 QL01 15949763240.65 5 QL02 442100035.38 5. 查询机构，贷款质量，余额记录（按照两个字段统计） SELECT LN.FR_CZJG,LN.FR_DKZL , FR_YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN 4112 059200001 QL01 4600000.00 725 059200001 QL01 2673000.00 2842 059200001 QL01 260000.00 2031 059200001 QL02 10000000.00 5011 059200001 QL02 1407172.53 855 059200001 QL02 492922.22 2594 059200001 QL03 10000000.00 3980 059200001 QL03 4915562.54 5035 059200001 QL03 4929222.22 788 059200101 QL01 100000.00 4854 059200101 QL01 20000000.00 7361 059200101 QL01 93460.00 60 059200201 QL01 205294139.81 6.按照机构，贷款质量进行分组统计（按照两个字段统计） SELECT LN.FR_CZJG,LN.FR_DKZL, SUM(LN.FR_YE) AS FR_YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN WHERE (LN.FR_YWLX IN ('1', '6') OR (LN.FR_YWLX = '5' AND LN.FR_CPLB NOT IN ('2160010', '2160020', '2160030', '2160040'))) AND LN.FR_SJTX IS NOT NULL --贷款实际投向，行业门类 AND LN.FR_HB = 'CNY' AND LN.FR_KJRQ = '20130930' GROUP BY LN.FR_DKZL, LN.FR_CZJG FR_CZJG FR_DKZL FR_YE 52 059200001 QL01 711662251.23 63 059200001 QL03 19844784.76 24 059200001 QL02 67886345.26 13 059200101 QL01 252445184.00
简单的Select语句：and,or ,in, not in,is not null,not like 找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) Select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK'); 结果: EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 3 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 select * from emp where deptno=10 and job not in('MANAGER','CLERK') and sal >=2000; 找出有奖金的员工的不同工作 select distinct job from emp where emp.comm is not null and emp.comm>0 找出姓名中不带R这个字母的员工 select * from emp where ename not like '%R%'; 函数： 单行函数： Upper,Lower，Initcap(第一个字母大写)， Concat（表示连接） select concat('a','b') from dual; Select 'a' || 'b' from dual; Substr() select substr(to_char(sysdate,'yyyymmdd'),1,8) from dual;--20140827 select substr(replace('2013/09/30','/'),1,6) from dual;--201309 Replace() select replace('2013/09/30','/','-') from dual;--2013-09-30 select replace('2013/09/30','/') from dual;--20130930 Instr() Select instr('Hello World','or') from dual; --在第8个位置 Lpad(),rpad() 填充 select rpad(ename,10,'*') from emp;-- SMITH***** select lpad(ename,10,'*') from emp;-- *****SMITH Trim() 过滤首尾空格 select trim(' l p ') from dual;--l p Trunc()表示从第几位截断，第二位为正数表示从小数点右边第几位截断，第二位是几，就保留几位。负数表示从小数点左边第几位截断 select trunc(412.16,-1) from dual; --410 select trunc(412.16,+1) from dual; --412.1 还可以与日期函数一起用： 表示截断为当年的第一天：结果为2013-1-1 select trunc(to_date('20131130','yyyymmdd'),'yyyy') from dual; 表示截断为当月的第一天：结果为2013-11-1 select trunc(to_date('20131130','yyyymmdd'),'mm') from dual; 表示截断为本季的第一天：结果为：2013-10-1 select trunc(to_date('20131130','yyyymmdd'),'q') from dual;--求季初一年有四个季度：1月1日，4月1日，7月1日，10月1日 Least函数： select least(1,2,3) from dual;--返回1 select least('A','B','C') from dual;--返回A 在项目中使用： 取数条件： 从担保信息中担保方式为存单质押取，（质押物认定价值，授信余额）孰低取孰 SELECT ln.fr_kjrq, substr(ln.fr_kjrq, 1, 6), ln.fr_czjg, ln.fr_hb, wd.zbdm , SUM(least(LN.FR_RDJZ,LN.FR_YE)) AS YE FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN, fr_wh_zbwd_dt wd WHERE LN.FR_CPLB LIKE '2120%' AND LN.FR_DBFS = '0401010'--担保方式 AND LN.FR_KJRQ = TO_CHAR(SYSDATE, 'YYYYMMDD') and bbid = 'GF43' and wdbs = 'F4303002' GROUP BY ln.fr_kjrq, substr(ln.fr_kjrq, 1, 6), ln.fr_czjg, ln.fr_hb, Wd.zbdm Greatest()函数 select greatest(1,2,3) from dual;--括号最大的 select greatest('A','B','C') from dual;--C select greatest (null, 'B', 'C' ) from dual ; -- 返回null --处理Null值的函数nvl,nvl2,Nullif select nvl(e.comm,0) from emp e;--如果x为空，就返回value；否则返回x select Nullif(1,2) from dual;--如果第一个数和第二个数不相等，则返回第一个数，否则返回null select nvl2(1,2,3) from dual;--如果x非空，就返回value1；否则返回value2 select coalesce ( null, 2, 1 ) from dual ; -- 返回2,返回表达式中的第一个非空值 日期函数 MONTHS_BETWEEN（前面-后面的日期）相差的月数 SELECT MONTHS_BETWEEN(to_date('2014-8-27','yyyy-mm-dd'), to_date('2014-7-27','yyyy-mm-dd')) from dual;--1 两个日期相差的天数 select floor(sysdate - to_date('20130827','yyyymmdd')) from dual; 查询到目前为止员工入职几个月 Select e.*, months_between(sysdate,hiredate) from emp e; add_months select add_months(sysdate,1) from dual; --月数加一 next_day： select next_day(sysdate,'星期一') from dual; --下一个星期一日期 last_day 本月最后一天 select last_day(sysdate)+1 from dual;--下月月初日期 to_char： -取时间的月 02 select to_char(to_date('2014-02-02','yyyy-mm-dd'),'dd')from dual; --取时间年 2014 select to_char(to_date('2014-02-02','yyyy-mm-dd'),'yyyy')from dual; 求年初：20130101 select to_char(trunc(to_date('20130910','yyyymmdd'),'yyyy'),'yyyymmdd') from dual; 求月初： select to_char(trunc(to_date('20130910','yyyymmdd'),'mm'),'yyyymmdd') from dual; 求季初：一年有四个季度：1月1日，4月1日，7月1日，10月1日 select to_char(trunc(to_date('20130910','yyyymmdd'),'q'),'yyyymmdd') from dual;--20130701 下季初：add_months的使用 select add_months(trunc(to_date('2013-09-10','yyyy-mm-dd'),'q'),3) from dual; 上季初： select add_months(trunc(to_date('20130910','yyyy-mm-dd'),'q'),-3) from dual; 上年初： select to_char(add_months(trunc(to_date('20130910','yyyy-mm-dd'),'yyyy'),-12),'yyyymmdd')from dual; 下年初：20140101 select to_char(add_months(trunc(to_date('20130910','yyyy-mm-dd'),'yyyy'),12),'yyyymmdd')from dual; 同理可以求出上月月初，下月月初 求下月月初：还可以使用last_day： select to_char(last_day(to_date('2013-09-10','yyyy-mm-dd'))+1,'yyyymmdd') from dual; 找到2月份受雇的员工 select * from emp where to_char(hiredate,'fmmm')='2'; 条件分支实现多路分支结构 case when ...then..else select empno, ename, sal, case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售部' else '未知部门' end 部门 from emp; 项目中用到的例子： SELECT ln.fr_kjrq, substr(ln.fr_kjrq, 1, 6), ln.fr_czjg, ln.fr_hb, wd.zbdm, sum(ln.FR_YE) FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN, fr_wh_zbwd_dt wd WHERE ln.fr_kjrq = TO_CHAR(SYSDATE, 'YYYYMMDD') AND ln.fr_ye > 0 and WD.bbid = 'S67' AND WD.WDBS = 'S670052' and (CASE WHEN TO_NUMBER(substr(ln.FR_DKCZRQ, 1, 4)) = TO_NUMBER(SUBSTR(LN.fr_kjrq, 1, 4)) THEN '本年' WHEN TO_NUMBER(substr(ln.FR_DKCZRQ, 1, 4)) = TO_NUMBER(SUBSTR(LN.fr_kjrq, 1, 4)) - 1 THEN '上年' WHEN TO_NUMBER(substr(ln.FR_DKCZRQ, 1, 4)) = TO_NUMBER(SUBSTR(LN.fr_kjrq, 1, 4)) - 2 THEN '上两年' WHEN TO_NUMBER(substr(ln.FR_DKCZRQ, 1, 4)) = TO_NUMBER(SUBSTR(LN.fr_kjrq, 1, 4)) - 3 THEN '上三年' WHEN TO_NUMBER(substr(ln.FR_DKCZRQ, 1, 4)) < TO_NUMBER(SUBSTR(LN.fr_kjrq, 1, 4)) - 3 THEN '上三年以前' END) = wd.wd1 AND LN.FR_CPLB = '1040040' AND LN.FR_DBFS LIKE '020%' AND LN.FR_YE / nullif(to_number(FR_DYPJZ), 0) > 0.7 GROUP BY ln.fr_kjrq, substr(ln.fr_kjrq, 1, 6), ln.fr_czjg, ln.fr_hb, wd.zbdm decode()函数也用于实现多路分支结构 select empno, ename, sal, decode(deptno, 10, '财务部', 20, '研发部', 30, '销售部', '未知部门') 部门 from emp; 分组函数：Avg，max，min，sum --求均值的时候记得结合nvl处理null函数使用，否则求出的平均值不准 select avg(nvl(comm, 0)) from emp; --157.142857142857 select avg(comm) from emp;--550 创建两张测试表： -- Create table create table TEST1 ( eid NUMBER(10), name VARCHAR2(20), birth DATE, salary NUMBER(8,2) ) tablespace RIDED pctfree 10 initrans 1 maxtrans 255 storage ( initial 4 next 4 minextents 1 maxextents unlimited pctincrease 0 ); -- Create table create table TEST2 ( eid NUMBER(10), name VARCHAR2(20), birth DATE, salary NUMBER(8,2) ) tablespace RIDED pctfree 10 initrans 1 maxtrans 255 storage ( initial 4 next 4 minextents 1 maxextents unlimited pctincrease 0 ); 连接查询： 1 select * from test2 for update; EID NAME BIRTH SALARY 2 1 张三 2014/1/13 16:08:34 2300.00 3 2 李四 2014/1/13 16:08:51 6600.00 1 4 p 4 3 JJJ 2014/1/13 16:08:51 6600.00 2. select * from test2 for update; EID NAME BIRTH SALARY 3 2 李四 2014/1/13 16:08:51 6600.00 2 3 王五 2014/1/13 16:19:23 7799.00 4 4 PPP 2014/1/13 16:08:34 2300.00 1 9 不同人 LEFT JOIN SELECT * FROM TEST1 T1 LEFT JOIN TEST2 T2 ON T1.EID=T2.EID; EID NAME EID NAME 1 3 JJJ 3 王五 2 2 李四 2 李四 3 4 p 4 PPP 4 1 张三 完全显示t1表所有的行,t2中显示主键（EID相等）连接上的行 RIGHT JOIN SELECT t1.eid,t1.name,t2.eid,t2.name FROM TEST1 T1 RIGHT JOIN TEST2 T2 ON T1.EID=T2.EID; EID NAME EID NAME 1 4 p 4 PPP 2 2 李四 2 李四 3 3 JJJ 3 王五 4 9 不同人 完全显示t2表中的所有记录,只显示t1表中连接上的记录。 INNER JOIN SELECT t1.eid,t1.name,t2.eid,t2.name FROM TEST2 T2 INNER JOIN TEST1 T1 ON T1.EID=T2.EID; 相当于 select t1.EID,T1.NAME,T2.EID,T2.NAME FROM TEST1 T1,TEST2 T2 WHERE T1.EID=T2.EID; 2 2 李四 2 李四 3 3 JJJ 3 王五 1 4 p 4 PPP 结论：只显示连接上的行 full join select * from test1 t1 full join test2 t2 on t1.eid=t2.eid; EID NAME EID NAME 1 4 p 4 PPP 4 3 JJJ 3 王五 3 2 李四 2 李四 2 1 张三 5 9 不同人 结论：显示连接上的也显示未连接上的 应用： 列出员工表中每个部门的员工数（员工数必须大于3），和部门名称 with empTemp as (select deptno,count(*) cou from emp group by deptno having count(*) > 3 ) select d.* ,e.cou from dept d inner join empTemp e on d.deptno=e.deptno; select d.* ,e.cou from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>3) e where d.deptno=e.deptno; 结果： DEPTNO DNAME LOC COU 1 30 SALES CHICAGO 6 2 20 RESEARCH DALLAS 5 列出所有员工的姓名和其上级的姓名 ，自连接的应用（因为普通员工和上级都是员工且都在emp表中） select e1.ename as lower ,e2.ename as upper from emp e1,emp e2 where e1.mgr = e2.empno; 结果： LOWER UPPER 1 FORD JONES 2 SCOTT JONES 3 TURNER BLAKE 4 ALLEN BLAKE 5 WARD BLAKE 6 JAMES BLAKE 查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入 select low.empno, low.ename, low.sal + nvl(low.comm, 0) from emp low, emp up where low.mgr = up.empno and (low.sal + nvl(low.comm, 0)) > (up.sal + nvl(up.comm, 0)); 以职位分组，找出平均工资最高的两种职位和平均工资 （看到最高，最低多少想到用top N） select job,avgsal from (select job,avg(sal) as avgsal from emp group by job order by avg(sal) desc) where rownum < 3 查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称 (比任何一个工资都高=》大于工资的最大值) select e1.ename,d.dname from emp e1, dept d where e1.deptno = d.deptno and e1.deptno != 20 and sal > (select max(sal) from emp e where e.deptno = 20); 集合： UNION ALL: SELECT t1.eid,t1.name FROM TEST1 T1 union all SELECT t2.eid,t2.name FROM TEST2 T2; EID NAME 2 1 张三 3 2 李四 7 2 李四 6 3 王五 4 3 JJJ 8 4 PPP 1 4 p 5 9 不同人 结论：并集，所有的内容都显示，包括并集，包括重复的内容 union SELECT t1.eid,t1.name FROM TEST1 T1 union SELECT t2.eid,t2.name FROM TEST2 T2; 1 1 张三 2 2 李四 --重复的只显示一次 3 3 JJJ 4 3 王五 5 4 PPP 6 4 p 7 9 不同人 结论：并集，所有的内容都查询，重复的显示一次（记录完全一样） INTERSECT select t1.eid,t1.name from test1 T1 INTERSECT SELECT t2.eid,t2.name FROM TEST2 T2; EID NAME 1 2 李四 结论:交集，只显示重复的，李四为重复的记录 Minus: select t1.eid,t1.name from test1 t1 minus SELECT t2.eid,t2.name FROM TEST2 T2; 1 1 张三 2 3 JJJ 3 4 p select t2.eid,t2.name from test2 t2 minus SELECT t1.eid,t1.name FROM TEST1 T1; EID NAME 1 3 王五 2 4 PPP 3 9 不同人 总结： 差集，只显示对方没有的，有先后顺序，t1-t2，显示t1中有的t2中没有的 差集，只显示对方没有的，有先后顺序，t2-t1 （记录完全一样，才是对方有的。） 部门表： select * from dept ; DEPTNO DNAME LOC 1 10 ACCOUNTING NEW YORK 2 20 RESEARCH DALLAS 3 30 SALES CHICAGO 4 40 OPERATIONS BOSTON 员工表： select * from emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 5 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 8 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 9 7839 KING PRESIDENT 1981-11-17 5000.00 10 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 11 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 12 7900 JAMES CLERK 7698 1981-12-3 950.00 30 13 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 14 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 子查询： --单行子查询 select sal from emp where empno = 7566； 结果： SAL 1 2975.00 select * from emp where sal > (select sal from emp where empno = 7566); 结果：sal都比2975.00大。 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 2 7839 KING PRESIDENT 1981-11-17 5000.00 10 3 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 --多行子查询 理解：select deptno,avg(sal) from emp group by deptno 结果：求出每个部门的工资的平均值 3 10 2916.66666666667 2 20 2175 1 30 1566.66666666667 --大于部门平均工资最小的员工，也就是比1566.66666666667大就满足 select * from emp where sal > any(select avg(sal) from emp group by deptno); 结果： EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 5 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 --大于部门平均工资最大的员工，也就是说要比2916.66666666667大 select * from emp where sal > all(select avg(sal) from emp group by deptno); 结果： 1 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 2 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 3 7839 KING PRESIDENT 1981-11-17 5000.00 10 4 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 查询工作和MARTIN或'SMITH'一样的员工。 select job from emp where ename = 'MARTIN' or ename = 'SMITH' 结果： JOB 1 CLERK 2 SALESMAN select * from emp where job in (select job from emp where ename = 'MARTIN' or ename = 'SMITH'); 结果： 3 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 4 7369 SMITH CLERK 7902 1980-12-17 800.00 20 5 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 6 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 TopN查询： 查询test1表中工资前10的员工 select * from emp 结果 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 5 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 8 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 9 7839 KING PRESIDENT 1981-11-17 5000.00 10 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 11 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 12 7900 JAMES CLERK 7698 1981-12-3 950.00 30 13 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 14 7934 MILLER CLERK 7782 1982-1-23 1300.00 10\ 查询序号为1或者2的员工,rownum指数据库中的序号 select * from emp where rownum=1 or rownum=2; 结果： EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7369 SMITH CLERK 7902 1980-12-17 800.00 20 2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 查询工资排名为前十的员工： select * from (select * from emp e order by e.sal desc) where rownum <= 10 结果： EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7839 KING PRESIDENT 1981-11-17 5000.00 10 2 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 3 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 5 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 8 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 9 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 10 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 查询工资最高的员工： 两种方法： 第一种：利用rownum select * from (select * from emp e order by e.sal desc) where rownum <= 1; 第二种：利用子查询 select * from emp e where e.sal = (select max(e1.sal) from emp e1); 结果： EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 1 7839 KING PRESIDENT 1981-11-17 5000.00 10 以job分组，找出平均工资最高的两种职位，利用rownum 先理解：以job分组，平均工资最大排序 select job,avg(sal) from emp group by job order by avg(sal) desc； 结果： JOB AVG(SAL) 1 PRESIDENT 5000 2 ANALYST 3000 3 MANAGER 2758.33333333333 4 SALESMAN 1400 5 CLERK 1037.5 Select * from ( select job,avg(sal) from emp group by job order by avg(sal) desc) where rownum <=2; 结果： JOB AVG(SAL) 1 PRESIDENT 5000 2 ANALYST 3000 分页查询，每页显示5条记录，按工资从大到小显示 select * from (select rownum no, e.* from (select * from emp order by sal desc) e) where no >= 1 and no <= 10 select * from (select rownum as no, e.* from (select * from emp e order by e.sal desc) e where rownum <= 5) where rownum >= 1 得到每个月工资总数最少的那个部门的部门标号，部门名称，部门位置： 先求出工资总数最少的部门编号： select e1.deptno from (select e.deptno, sum(sal) from emp e group by e.deptno order by sum(sal)) e1 where rownum = 1 结果: DEPTNO DNAME LOC 1 10 ACCOUNTING NEW YORK 然后通过部门标号作为条件查部门表： select * from dept d where d.deptno = (select e1.deptno from (select e.deptno, sum(sal) from emp e group by e.deptno order by sum(sal)) e1 where rownum = 1); 结果： DEPTNO DNAME LOC 1 10 ACCOUNTING NEW YORK 查出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置 . select e2.ename,e2.sal,d.dname, d.loc from (select rownum no, e1.* from (select * from emp e where e.deptno = 20 or e.deptno = 10 order by e.sal desc) e1) e2, dept d where e2.deptno = d.deptno and e2.no >= 3 and e2.no <= 5; 按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置） select dept.dname, dept.loc from (select rownum no, deptno from (select deptno, count(*) as empcount from emp group by deptno order by empcount desc))e, dept where e.deptno = dept.deptno and no between 2 and 5; 查询出king所在部门的部门号\部门名称\部门人数 两种方法：第一种效率比第二种效率高了很多 第一种： with temp as ( select deptno, count(*) empcount from emp e2 where e2.deptno = (select deptno from emp e1 where lower(e1.ename) = 'king') group by deptno) select d.dname,d.loc,temp.empcount from temp,dept d where temp.deptno = d.deptno; 第二种 select d.dname,d.loc,temp.empcount from (select deptno, count(*) empcount from emp e2 where e2.deptno = (select deptno from emp e1 where lower(e1.ename) = 'king') group by deptno) temp, dept d where temp.deptno = d.deptno; 查询出king所在部门的工作年限最大的员工名字 select e1.ename, e1.hiredate from emp e1 where e1.hiredate in (select min(e.hiredate) longhire from emp e where e.deptno = (select deptno from emp where lower(ename) = 'king')) 查询出工资成本最高的部门的部门号和部门名称 (第一种比第二种效率低) select d.deptno,d.dname,t.sum_sal from dept d, (select deptno,sum(sal) sum_sal from emp group by deptno having sum(sal) = (select max(sum(sal)) from emp group by deptno) ) t where d.deptno = t.deptno; select d.deptno, d.dname, t.sum_sal from dept d, (select * from (select deptno, sum(sal) sum_sal from emp group by deptno order by sum(sal) desc) where rownum <= 1) t where d.deptno = t.deptno; exists： （1）由于exists关键字的返回值取决于查询是否返回行，而不取决于这些行的内容，因此对子查询来说，输出列表无关紧要，可以使用*代替。如果子查询返回一行或者多行，那么exist便返回true，否则返回false （2）对于in 和 exists的性能区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主 查询记录较少，子查询中的表大，又有索引时使用exists。 其实我们区分in 和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists， 那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返 回为目标，那么就会考虑到索引及结果集的关系了 另外IN 是不对NULL进行处理 如： select 1 from dual where null in (0,1,2,null) 为空 视图 视图：是一个封装了各种复杂查询的语句，就称为视图。 15.1、创建视图 CREATE VIEW 视图名字(字段) AS 子查询 建立一个只包含20部门雇员信息的视图（雇员的编号、姓名、工资） CREATE VIEW empv20 (empno,ename,sal) AS SELECT empno,ename,sal FROM emp WHERE deptno=20 ; 例如：将之前的一个复杂语句包装成视图 显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资： SELECT deptno,MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp WHERE deptno=20) ; 此时就可以将上面的复杂查询语句建立一张视图，之后查询视图即可。 15.2、高级视图 如果要创建一个同名的视图，则必须先将之前的视图删除掉，再进行创建： 删除视图：drop view empv20; 有些时候如果先删除再创建操作会比较麻烦，所以有时候最好的方式：如果视图存在则先 自动删除，之后自动创建。 create or replace view empv20(deptno,minsal) As select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20); 例如，还是创建一个只包含20部门的视图 CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno) AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 ; 现在直接更新视图里的数据 将7369的部门编号修改为30。此操作在视图中完成。 update empv20 SET deptno=30 where empno=7369 ; 此时，提示更新完成。 默认情况下创建的视图，如果更新了，则会自动将此数据从视图中删除，之后会更新原本 的数据。 思考： 如果能这样做的话，肯定存在问题，因为视图最好还是不要更新。 在建立视图的时候有两个参数： • WITH CHECK OPTION 保护视图的创建规则 CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno) AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 WITH CHECK OPTION CONSTRAINT empv20_ck; 再执行更新操作： update empv20 SET deptno=30 where empno=7369 ; 此处更新的是部门编号，失败 |- 之前是按照部门编号建立的视图，所以不能修改部门编号 update empv20 SET ename='tom' where empno=7369 ; 可以更新，更新的是名字，成功 • WITH READ ONLY（只读，不可修改），视图最好不要轻易的修改 CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno) AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20 WITH READ ONLY; 现在任意的字段都不可更改，所以现在的视图是只读的。 如果视图的基表有多行查询（比如：group by,distinct）那么该视图也是只读的 索引 select * from user_indexes; --查询现有的索引 select * from user_ind_columns;--可获知索引建立在哪个字段上 创建索引 create index abc on student(sid,sname)； create index abc1 on student(sname,sid); 索引对 abc select * from student where sid=1 这样的查询更有效。 索引对 abc select * from student where sname =‘loci‘ 这样的查询更有效。 因此建立索引的时候，字段的组合顺序是非常重要的，一般情况下需要经常访问的字段放在前面。 删除索引： drop index abc; 索引类型： B树索引（B-Tree Index） 创建索引的默认类型，结构是一颗树，采用的是平衡B树算法： 右子树节点的键值大于等于父节点的键值 左子树节点的键值小于等于父节点的键值 比如有数据:100,101,102,103,104,105,106 位图索引(BitMap Index) ：位图索引主要是针对大量相同值得列而创建。 例子：全国居民信息表，假设有四个字段：姓名、性别、年龄、和身份证号。年龄和性别连个字段会产生许多相同的值，性别只有男女两种值，年龄1-120（假设年龄最大值为120）。那么不管一张表有几亿条记录，但根据性别字段来区分的话，只有两种取值（男，女），那么位图字段就是根据字段的这种特性所建立的一种索引。 如果表中的某些字段取值范围比较小，比如职员性别、分数列ABC级等。只有两个值。 这样的字段如果建B树索引没有意义，不能提高检索速度。这时我们推荐用位图索引 Create BitMap Index student on(sex); 管理索引 1）先插入数据后创建索引 向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行 数据的时候都要更改索引。这样会大大降低插入数据的速度。 2）设置合理的索引列顺序 3）限制每个表索引的数量 4）删除不必要的索引 5）为每个索引指定表空间 6）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎 DDL改变表的结构 创建表 create table emp1( id varchar(2), name varchar(10) ); 使用子查询创建表 create table emp2 as select * from emp1; 添加字段 alter table emp2 add sex varchar(2); 修改字段 alter table emp2 modify sex varchar(2); --修改字段值大小 alter table emp2 rename column sex to sex1; --修改字段名称 删除字段 alter table emp2 drop column name; 清空表中数据 delete from emp1； truncate table emp1; delete 删除数据，如果发现删除了，可以通过rollback进行回滚。如果使用了截断表，则表示所有的数据不可恢复了。所以速度很快。 删除表 Drop table student； 重命名表 rename emp1 to emp3; DML改变数据结构 Insert 表间拷贝数据： Insert into dept1(id,name) select deptno,dname from dept; Update 将编号为7779用户的工作换成编号为7566的雇员的工作和所属上级。 Update myemp set(job,mgr) = (select job,mgr from myemp where empno=7566) where empno=7779; Merge create table test1(eid number(10), name varchar2(20),birth date,salary number(8,2)); insert into test1 values (1001, '张三', '20130901', 2300); insert into test1 values (1002, '李四', '20131023', 6600); insert into test1 values (1003, '王五', '20131025', 5000); select * from test2; create table test2(eid number(10), name varchar2(20),birth date,salary number(8,2)); insert into test2 values (1001, '王五', '20140403', 5000); insert into test2 values (1004, '祝捷', '20141203', 8000); select * from test2; merge当id匹配的时候更新，当不匹配的时候插入 merge 的三种用法： （1）既插入也更新 merge into test2 using test1 on(test1.eid = test2.eid ) when matched then update set name = test1.name, birth = test1.birth, salary = test1.salary when not matched then insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary); (2) 只插入不更新 Merge into test2 Using test1 0n(test1.eid=test2.eid) When not matched then insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary); (3)只更新不插入 Merge into test2 Using test1 on (test1.eid=test2.eid) When matched then Update set name = test1.name,birth =test1.birth,salary = test1.salary; 例子： 5.为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长 18%（要求用DECODE函数） update emp set sal=decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18)); 6.根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。 update emp set sal= round(sal * (1+(sysdate - hiredate)/365/12/100),2); 序列、同义词 序列创建 -- Create sequence create sequence SEQ_FIN_CHECK_RELATION_DEF minvalue 1 maxvalue 999999999999999999999999999 start with 361 increment by 1 cache 20; Create sequence myseq Start with 1 Increment by 1 Order cache 20 Nocycle; select myseq.nextval from dual; select myseq.currval from dual; 查询完之后就已经自增1了 Insert into table1 values(myseq.nextval) 这时候已经是2了 Cycle，Cache 而用了nocycle，就可以确保当该序列用于多张表的时候，ID 是唯一的 用cycle时，用法如下： create sequence myseq2 start with 1 increment by 1 cycle maxvalue 3 nocache ; 这样到3之后，要会重新从1开始 如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里 面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉 （shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止 这种情况 不能改变当前值，但是可以改变增量 Alter sequence myseq increment by 3; 同义词 使用PL/SQL建立DB Link -- Create database link 创建dblink create database link ODMLP connect to ODM using 'DW_GOV'; 创建同义词： create or replace synonym LP for DUAL@ODMLP; 删除同义词LP drop synonym LP; 作用： 很方便的操作不同用户下的对象 能使两个应用程序使用不同的名字指向同一张表 使用不同的用户指向同一张表的。 Create synonym dept for soctt.dept;(这样创建的同义词是私有的，只有创建者才能用) Drop synonym dept; Create public synonym dept for soctt.dept;（这样创建的同义词才是公有的） Drop public synonym dept; 解锁 SELECT /*+ rule */ s.username, decode(l.type,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, o.owner,o.object_name,o.object_type, s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser FROM v$session s,v$lock l,dba_objects o WHERE l.sid = s.sid AND l.id1 = o.object_id(+) AND s.username is NOT NULL; alter system kill session '35,2947'; alter system kill session '15,2407'; 存储过程学习： SP_F_CARD_EVENT增量：流水表，数据量大，跑增量，删除当天的数据，根据时间进行删除，数据按时间段删除。给一张表只增不减。 SP_F_CARD_ACCT变量：客户信息表，即数据会增加也会减少，改变某个字段的信息，例如改变客户的年龄这个字段。 全量：数据量小SP_RPT_F_LN_LNMOA_H 建立分区：为了提高查询效率，建立分区相当于建立块 -- Create table create table RPT_F_LN_LNMOA_H ( inst_no VARCHAR2(10), curr_cd VARCHAR2(10), it_cd VARCHAR2(4), ac_sq VARCHAR2(8), check_wz VARCHAR2(1), cust_no VARCHAR2(10), cust_name VARCHAR2(62), bal_at VARCHAR2(1), subj_cd VARCHAR2(5), last_tx_dt NUMBER(8), last_bal NUMBER(15,2), last_jx_dt NUMBER(8), last_zx_dt NUMBER(8), con_no VARCHAR2(32), acct_bal NUMBER(15,2), jx_ff VARCHAR2(1), fx_falg VARCHAR2(1), ny_inrt VARCHAR2(1), inrt NUMBER(9,7), accum_inrt NUMBER(13,2), accum_bal NUMBER(20,2), yjj_inrt NUMBER(13,2), accum_yjj_inrt NUMBER(20,2), ln_acct_no VARCHAR2(25), cl_acct_no VARCHAR2(25), open_dt NUMBER(8), open_user VARCHAR2(6), clean_dt NUMBER(8), clean_user VARCHAR2(6), mt_dt NUMBER(8), mt_user VARCHAR2(6), st_cd VARCHAR2(1), ch_amt NUMBER(15,2), bal NUMBER(15,2), moa_amt NUMBER(15,2), next_back_dt NUMBER(8), etl_bz_dt VARCHAR2(50), etl_src_sys VARCHAR2(50), etl_load_dt DATE ) partition by range (ETL_BZ_DT) ( partition P20131231 values less than ('20140101') tablespace RIDED pctfree 10 initrans 1 maxtrans 255 storage ( initial 72M next 1M minextents 1 maxextents unlimited ), partition P20140228 values less than ('20140301') tablespace RIDED pctfree 10 initrans 1 maxtrans 255 storage ( initial 8M next 1M minextents 1 maxextents unlimited ), partition P20140331 values less than ('20140401') tablespace RIDED pctfree 10 initrans 1 maxtrans 255 storage ( initial 8M next 1M minextents 1 maxextents unlimited ) ); -- Add comments to the table comment on table RPT_F_LN_LNMOA_H is '欠息主文件'; -- Add comments to the columns comment on column RPT_F_LN_LNMOA_H.inst_no is '机构代号'; comment on column RPT_F_LN_LNMOA_H.curr_cd is '货币代号'; comment on column RPT_F_LN_LNMOA_H.it_cd is '业务代号'; comment on column RPT_F_LN_LNMOA_H.ac_sq is '帐号序号'; comment on column RPT_F_LN_LNMOA_H.check_wz is '检查位'; comment on column RPT_F_LN_LNMOA_H.cust_no is '客户号'; comment on column RPT_F_LN_LNMOA_H.cust_name is '客户中文名'; comment on column RPT_F_LN_LNMOA_H.bal_at is '余额性质'; comment on column RPT_F_LN_LNMOA_H.subj_cd is '科目号'; comment on column RPT_F_LN_LNMOA_H.last_tx_dt is '上次交易日'; comment on column RPT_F_LN_LNMOA_H.last_bal is '上期余额'; comment on column RPT_F_LN_LNMOA_H.last_jx_dt is '上次计息日'; comment on column RPT_F_LN_LNMOA_H.last_zx_dt is '上次转息日'; comment on column RPT_F_LN_LNMOA_H.con_no is '合同编号'; comment on column RPT_F_LN_LNMOA_H.acct_bal is '帐户余额'; comment on column RPT_F_LN_LNMOA_H.jx_ff is '计息方法'; comment on column RPT_F_LN_LNMOA_H.fx_falg is '复息标志'; comment on column RPT_F_LN_LNMOA_H.ny_inrt is '年／月利率'; comment on column RPT_F_LN_LNMOA_H.inrt is '利率'; comment on column RPT_F_LN_LNMOA_H.accum_inrt is '累计利息'; comment on column RPT_F_LN_LNMOA_H.accum_bal is '积数'; comment on column RPT_F_LN_LNMOA_H.yjj_inrt is '应加/减利息'; comment on column RPT_F_LN_LNMOA_H.accum_yjj_inrt is '应加/减积数'; comment on column RPT_F_LN_LNMOA_H.ln_acct_no is '贷款帐号'; comment on column RPT_F_LN_LNMOA_H.cl_acct_no is '结算帐号'; comment on column RPT_F_LN_LNMOA_H.open_dt is '开户日期'; comment on column RPT_F_LN_LNMOA_H.open_user is '开户柜员'; comment on column RPT_F_LN_LNMOA_H.clean_dt is '销户日期'; comment on column RPT_F_LN_LNMOA_H.clean_user is '销户柜员'; comment on column RPT_F_LN_LNMOA_H.mt_dt is '维护日期'; comment on column RPT_F_LN_LNMOA_H.mt_user is '维护柜员'; comment on column RPT_F_LN_LNMOA_H.st_cd is '记录状态'; comment on column RPT_F_LN_LNMOA_H.ch_amt is '挂帐金额'; comment on column RPT_F_LN_LNMOA_H.bal is '余额'; comment on column RPT_F_LN_LNMOA_H.moa_amt is '欠息金额'; comment on column RPT_F_LN_LNMOA_H.next_back_dt is '下次还款日期'; comment on column RPT_F_LN_LNMOA_H.etl_bz_dt is '业务日期'; comment on column RPT_F_LN_LNMOA_H.etl_src_sys is '业务系统'; comment on column RPT_F_LN_LNMOA_H.etl_load_dt is '业务装载时间';
简单的Select语句：and,or ,in, not in,is not null,not like
找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
Select*from emp where(deptno=10and job='MANAGER')or(deptno=20and job='CLERK');
结果:
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
1 7369 SMITH CLERK 7902 1980-12-17800.00 20
2 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
3 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工
select*from emp where deptno=10and job notin('MANAGER','CLERK')and sal >=2000;
找出有奖金的员工的不同工作
selectdistinct job from emp where emp.comm isnotnulland emp.comm>0
找出姓名中不带R这个字母的员工
select*from emp where ename notlike'%R%';
函数：
单行函数：
Upper,Lower，Initcap(第一个字母大写)，
Concat（表示连接）
select concat('a','b') from dual;
Select'a'||'b'from dual;
Substr()
select substr(to_char(sysdate,'yyyymmdd'),1,8)from dual;--20140827
select substr(replace('2013/09/30','/'),1,6)from dual;--201309
Replace()
selectreplace('2013/09/30','/','-')from dual;--2013-09-30
selectreplace('2013/09/30','/')from dual;--20130930
Instr()
Select instr('Hello World','or')from dual; --在第8个位置
Lpad(),rpad() 填充
select rpad(ename,10,'*')from emp;-- SMITH*****
select lpad(ename,10,'*')from emp;-- *****SMITH
Trim() 过滤首尾空格
selecttrim(' l p ')from dual;--l p
Trunc()表示从第几位截断，第二位为正数表示从小数点右边第几位截断，第二位是几，就保留几位。负数表示从小数点左边第几位截断
select trunc(412.16,-1)from dual; --410
select trunc(412.16,+1)from dual; --412.1
还可以与日期函数一起用：
表示截断为当年的第一天：结果为2013-1-1
select trunc(to_date('20131130','yyyymmdd'),'yyyy')from dual;
表示截断为当月的第一天：结果为2013-11-1
select trunc(to_date('20131130','yyyymmdd'),'mm')from dual;
表示截断为本季的第一天：结果为：2013-10-1
select trunc(to_date('20131130','yyyymmdd'),'q')from dual;--求季初一年有四个季度：1月1日，4月1日，7月1日，10月1日
Least函数：
select least(1,2,3) from dual;--返回1
select least('A','B','C') from dual;--返回A
在项目中使用：
取数条件：
从担保信息中担保方式为存单质押取，（质押物认定价值，授信余额）孰低取孰
SELECT ln.fr_kjrq,
substr(ln.fr_kjrq, 1, 6),
ln.fr_czjg,
ln.fr_hb,
wd.zbdm
, SUM(least(LN.FR_RDJZ,LN.FR_YE)) AS YE
FROM PD_RPT_FRSS_MERGE_LOAN_SLOW LN, fr_wh_zbwd_dt wd
WHERE LN.FR_CPLB LIKE '2120%'
AND LN.FR_DBFS = '0401010'--担保方式
AND LN.FR_KJRQ = TO_CHAR(SYSDATE, 'YYYYMMDD')
and bbid = 'GF43'
and wdbs = 'F4303002'
GROUP BY ln.fr_kjrq,
substr(ln.fr_kjrq, 1, 6),
ln.fr_czjg,
ln.fr_hb,
Wd.zbdm
Greatest()函数
select greatest(1,2,3)from dual;--括号最大的
select greatest('A','B','C')from dual;--C
select greatest (null,'B','C')from dual ;-- 返回null
--处理Null值的函数nvl,nvl2,Nullif
select nvl(e.comm,0)from emp e;--如果x为空，就返回value；否则返回x
select Nullif(1,2)from dual;--如果第一个数和第二个数不相等，则返回第一个数，否则返回null
select nvl2(1,2,3)from dual;--如果x非空，就返回value1；否则返回value2
selectcoalesce(null,2,1)from dual ;-- 返回2,返回表达式中的第一个非空值
日期函数
MONTHS_BETWEEN（前面-后面的日期）相差的月数
SELECT MONTHS_BETWEEN(to_date('2014-8-27','yyyy-mm-dd'), to_date('2014-7-27','yyyy-mm-dd'))from dual;--1
两个日期相差的天数
select floor(sysdate- to_date('20130827','yyyymmdd'))from dual;
查询到目前为止员工入职几个月
Select e.*, months_between(sysdate,hiredate)from emp e;
add_months
select add_months(sysdate,1)from dual; --月数加一
next_day：
select next_day(sysdate,'星期一')from dual; --下一个星期一日期
last_day 本月最后一天
select last_day(sysdate)+1from dual;--下月月初日期
to_char：
-取时间的月 02
select to_char(to_date('2014-02-02','yyyy-mm-dd'),'dd')from dual;
--取时间年 2014
select to_char(to_date('2014-02-02','yyyy-mm-dd'),'yyyy')from dual;
求年初：20130101
select to_char(trunc(to_date('20130910','yyyymmdd'),'yyyy'),'yyyymmdd')from dual;
求月初：
select to_char(trunc(to_date('20130910','yyyymmdd'),'mm'),'yyyymmdd')from dual;
求季初：一年有四个季度：1月1日，4月1日，7月1日，10月1日
select to_char(trunc(to_date('20130910','yyyymmdd'),'q'),'yyyymmdd')from dual;--20130701
下季初：add_months的使用
select add_months(trunc(to_date('2013-09-10','yyyy-mm-dd'),'q'),3)from dual;
上季初：
select add_months(trunc(to_date('20130910','yyyy-mm-dd'),'q'),-3)from
dual;
上年初：
select to_char(add_months(trunc(to_date('20130910','yyyy-mm-dd'),'yyyy'),-12),'yyyymmdd')from dual;
下年初：20140101
select to_char(add_months(trunc(to_date('20130910','yyyy-mm-dd'),'yyyy'),12),'yyyymmdd')from dual;
同理可以求出上月月初，下月月初
求下月月初：还可以使用last_day：
select to_char(last_day(to_date('2013-09-10','yyyy-mm-dd'))+1,'yyyymmdd')from dual;
找到2月份受雇的员工
select*from emp where to_char(hiredate,'fmmm')='2';
条件分支实现多路分支结构
case when ...then..else
select empno,
ename,
sal,
case deptno
when10then
'财务部'
when20then
'研发部'
when30then
'销售部'
else
'未知部门'
end部门
from emp;
项目中用到的例子：
SELECT ln.fr_kjrq,
substr(ln.fr_kjrq,1,6),
ln.fr_czjg,
ln.fr_hb,
wd.zbdm,
sum(ln.FR_YE)
FROM
PD_RPT_FRSS_MERGE_LOAN_SLOW LN, fr_wh_zbwd_dt wd
WHERE
ln.fr_kjrq = TO_CHAR(SYSDATE,'YYYYMMDD')
AND ln.fr_ye >0
and WD.bbid ='S67'
AND WD.WDBS ='S670052'
and(CASE
WHEN TO_NUMBER(substr(ln.FR_DKCZRQ,1,4))=
TO_NUMBER(SUBSTR(LN.fr_kjrq,1,4))THEN
'本年'
WHEN TO_NUMBER(substr(ln.FR_DKCZRQ,1,4))=
TO_NUMBER(SUBSTR(LN.fr_kjrq,1,4))-1THEN
'上年'
WHEN TO_NUMBER(substr(ln.FR_DKCZRQ,1,4))=
TO_NUMBER(SUBSTR(LN.fr_kjrq,1,4))-2THEN
'上两年'
WHEN TO_NUMBER(substr(ln.FR_DKCZRQ,1,4))=
TO_NUMBER(SUBSTR(LN.fr_kjrq,1,4))-3THEN
'上三年'
WHEN TO_NUMBER(substr(ln.FR_DKCZRQ,1,4))<
TO_NUMBER(SUBSTR(LN.fr_kjrq,1,4))-3THEN
'上三年以前'
END)= wd.wd1
AND LN.FR_CPLB ='1040040'
AND LN.FR_DBFS LIKE'020%'
AND LN.FR_YE / nullif(to_number(FR_DYPJZ),0)>0.7
GROUPBY ln.fr_kjrq,
substr(ln.fr_kjrq,1,6),
ln.fr_czjg,
ln.fr_hb,
wd.zbdm
decode()函数也用于实现多路分支结构
select
empno,
ename,
sal,
decode(deptno,10,'财务部',20,'研发部',30,'销售部','未知部门')部门
from emp;
分组函数：Avg，max，min，sum
--求均值的时候记得结合nvl处理null函数使用，否则求出的平均值不准
selectavg(nvl(comm,0))from emp;--157.142857142857
selectavg(comm)from emp;--550
创建两张测试表：
-- Create table
createtable TEST1
(
eid NUMBER(10),
name VARCHAR2(20),
birth DATE,
salary NUMBER(8,2)
)
tablespace RIDED
pctfree10
initrans1
maxtrans255
storage
(
initial4
next4
minextents1
maxextentsunlimited
pctincrease0
);
-- Create table
createtable TEST2
(
eid NUMBER(10),
name VARCHAR2(20),
birth DATE,
salary NUMBER(8,2)
)
tablespace RIDED
pctfree10
initrans1
maxtrans255
storage
(
initial4
next4
minextents1
maxextentsunlimited
pctincrease0
);
连接查询：
1 select * from test2 forupdate;
EID NAME BIRTH SALARY
2 1 张三 2014/1/13 16:08:34 2300.00
3 2 李四 2014/1/13 16:08:51 6600.00
1 4 p
4 3 JJJ 2014/1/13 16:08:51 6600.00
2. select * from test2 forupdate;
EID NAME BIRTH SALARY
3 2 李四 2014/1/13 16:08:51 6600.00
2 3 王五 2014/1/13 16:19:23 7799.00
4 4 PPP 2014/1/13 16:08:34 2300.00
1 9 不同人
LEFTJOIN
SELECT * FROM TEST1 T1 LEFTJOIN TEST2 T2 ON T1.EID=T2.EID;
EID NAME EID NAME
1 3 JJJ 3 王五
2 2 李四 2 李四
3 4 p 4 PPP
4 1 张三
完全显示t1表所有的行,t2中显示主键（EID相等）连接上的行
RIGHTJOIN
SELECT t1.eid,t1.name,t2.eid,t2.name FROM TEST1 T1 RIGHTJOIN TEST2 T2 ON T1.EID=T2.EID;
EID NAME EID NAME
1 4 p 4 PPP
2 2 李四 2 李四
3 3 JJJ 3 王五
4 9 不同人
完全显示t2表中的所有记录,只显示t1表中连接上的记录。
INNERJOIN
SELECT t1.eid,t1.name,t2.eid,t2.name FROM TEST2 T2 INNERJOIN TEST1 T1 ON T1.EID=T2.EID;
相当于
select t1.EID,T1.NAME,T2.EID,T2.NAME FROM TEST1 T1,TEST2 T2 WHERE T1.EID=T2.EID;
2 2 李四 2 李四
3 3 JJJ 3 王五
1 4 p 4 PPP
结论：只显示连接上的行
fulljoin
select * from test1 t1 fulljoin test2 t2 on t1.eid=t2.eid;
EID NAME EID NAME
1 4 p 4 PPP
4 3 JJJ 3 王五
3 2 李四 2 李四
2 1 张三
5 9 不同人
结论：显示连接上的也显示未连接上的
应用：
列出员工表中每个部门的员工数（员工数必须大于3），和部门名称
with empTemp
as(select deptno,count(*) cou from emp groupby deptno havingcount(*)>3)
select d.*,e.cou from dept d innerjoin empTemp e on d.deptno=e.deptno;
select d.*,e.cou from dept d,(select deptno,count(*) cou from emp group
by deptno havingcount(*)>3) e where
d.deptno=e.deptno;
结果：
DEPTNODNAME LOCCOU
1 30 SALES CHICAGO 6
2 20 RESEARCH DALLAS5
列出所有员工的姓名和其上级的姓名 ，自连接的应用（因为普通员工和上级都是员工且都在emp表中）
select e1.ename as lower ,e2.ename as upper from emp e1,emp e2 where e1.mgr
= e2.empno;
结果：
LOWER UPPER
1 FORD JONES
2 SCOTT JONES
3 TURNERBLAKE
4 ALLEN BLAKE
5 WARD BLAKE
6 JAMES BLAKE
查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入
select
low.empno, low.ename, low.sal + nvl(low.comm,0)
from emp low, emp up
where
low.mgr = up.empno
and(low.sal + nvl(low.comm,0))>(up.sal + nvl(up.comm,0));
以职位分组，找出平均工资最高的两种职位和平均工资 （看到最高，最低多少想到用top N）
select job,avgsal from(select job,avg(sal)as avgsal from emp groupby job orderbyavg(sal)desc)whererownum<3
查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称
(比任何一个工资都高=》大于工资的最大值)
select e1.ename,d.dname
from emp e1, dept d
where
e1.deptno = d.deptno
and e1.deptno !=20
and sal >(selectmax(sal)from emp e where e.deptno =20);
集合：
UNIONALL:
SELECT t1.eid,t1.name FROM TEST1 T1 unionallSELECT t2.eid,t2.name FROM TEST2 T2;
EID NAME
2 1 张三
3 2 李四
7 2 李四
6 3 王五
4 3 JJJ
8 4 PPP
1 4 p
5 9 不同人
结论：并集，所有的内容都显示，包括并集，包括重复的内容
union
SELECT t1.eid,t1.name FROM TEST1 T1 unionSELECT t2.eid,t2.name FROM TEST2 T2;
1 1 张三
2 2 李四 --重复的只显示一次
3 3 JJJ
4 3 王五
5 4 PPP
6 4 p
7 9 不同人
结论：并集，所有的内容都查询，重复的显示一次（记录完全一样）
INTERSECT
select t1.eid,t1.name from test1 T1 INTERSECTSELECT t2.eid,t2.name FROM TEST2 T2;
EID NAME
1 2 李四
结论:交集，只显示重复的，李四为重复的记录
Minus:
select t1.eid,t1.name from test1 t1 minusSELECT t2.eid,t2.name FROM TEST2 T2;
1 1 张三
2 3 JJJ
3 4 p
select t2.eid,t2.name from test2 t2 minusSELECT t1.eid,t1.name FROM TEST1 T1;
EID NAME
1 3 王五
2 4 PPP
3 9 不同人
总结：
差集，只显示对方没有的，有先后顺序，t1-t2，显示t1中有的t2中没有的
差集，只显示对方没有的，有先后顺序，t2-t1
（记录完全一样，才是对方有的。）
部门表：
select*from dept ;
DEPTNODNAME LOC
1 10 ACCOUNTINGNEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONSBOSTON
员工表：
select*from emp ;
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
1 7369 SMITH CLERK 7902 1980-12-17800.00 20
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.0030
3 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.0030
4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
5 7654 MARTINSALESMAN 7698 1981-9-28 1250.00 1400.00 30
6 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
8 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
9 7839 KING PRESIDENT 1981-11-175000.00 10
10 7844 TURNERSALESMAN 7698 1981-9-8 1500.00 0.00 30
11 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
12 7900 JAMES CLERK 7698 1981-12-3 950.00 30
13 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
14 7934 MILLERCLERK 7782 1982-1-23 1300.00 10
子查询：
--单行子查询
select sal from emp where empno =7566；
结果：
SAL
1 2975.00
select*from emp where sal >(select sal from emp where empno =7566);
结果：sal都比2975.00大。
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
1 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
2 7839 KING PRESIDENT 1981-11-17 5000.00 10
3 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
--多行子查询
理解：select deptno,avg(sal)from emp groupby deptno
结果：求出每个部门的工资的平均值
3 10 2916.66666666667
2 20 2175
1 30 1566.66666666667
--大于部门平均工资最小的员工，也就是比1566.66666666667大就满足
select*from emp where sal >any(selectavg(sal)from emp groupby deptno);
结果：
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
5 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.0030
--大于部门平均工资最大的员工，也就是说要比2916.66666666667大
select*from emp where sal >all(selectavg(sal)from emp groupby deptno);
结果：
1 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
2 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
3 7839 KING PRESIDENT 1981-11-17 5000.00 10
4 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
查询工作和MARTIN或'SMITH'一样的员工。
select job from emp where ename ='MARTIN'or ename ='SMITH'
结果：
JOB
1 CLERK
2 SALESMAN
select*from emp where job in(select job from emp where ename ='MARTIN'or ename ='SMITH');
结果：
3 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
4 7369 SMITH CLERK 7902 1980-12-17 800.00 20
5 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.0030
6 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
TopN查询：
查询test1表中工资前10的员工
select*from emp
结果
EMPNOENAMEJOB MGR HIREDATESAL COMM DEPTNO
1 7369 SMITHCLERK 7902 1980-12-17 800.00 20
2 7499 ALLENSALESMAN7698 1981-2-201600.00 300.0030
3 7521 WARD SALESMAN7698 1981-2-221250.00 500.0030
4 7566 JONESMANAGER 7839 1981-4-22975.00 20
5 7654 MARTINSALESMAN7698 1981-9-281250.00 1400.00 30
6 7698 BLAKEMANAGER 7839 1981-5-12850.00 30
7 7782 CLARKMANAGER 7839 1981-6-92450.00 10
8 7788 SCOTTANALYST 7566 1987-4-193000.00 20
9 7839 KING PRESIDENT 1981-11-17 5000.00 10
107844 TURNERSALESMAN7698 1981-9-81500.00 0.00 30
117876 ADAMSCLERK 7788 1987-5-231100.00 20
127900 JAMESCLERK 7698 1981-12-3950.00 30
137902 FORD ANALYST 7566 1981-12-33000.00 20
147934 MILLERCLERK 7782 1982-1-231300.00 10\
查询序号为1或者2的员工,rownum指数据库中的序号
select*from emp whererownum=1orrownum=2;
结果：
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
1 7369 SMITH CLERK 7902 1980-12-17800.00 20
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.0030
查询工资排名为前十的员工：
select*from(select*from emp e orderby e.sal desc)whererownum<=10
结果：
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
1 7839 KING PRESIDENT 1981-11-175000.00 10
2 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
3 7902 FORD ANALYST 7566 1981-12-3 3000.00 20
4 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
5 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
6 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.0030
8 7844 TURNERSALESMAN 7698 1981-9-8 1500.00 0.00 30
9 7934 MILLERCLERK 7782 1982-1-23 1300.00 10
10 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.0030
查询工资最高的员工：
两种方法：
第一种：利用rownum
select*from(select*from emp e orderby e.sal desc)whererownum<=1;
第二种：利用子查询
select*from emp e where e.sal =(selectmax(e1.sal)from emp e1);
结果：
EMPNO ENAME JOBMGRHIREDATE SALCOMM DEPTNO
1 7839 KING PRESIDENT 1981-11-175000.00 10
以job分组，找出平均工资最高的两种职位，利用rownum
先理解：以job分组，平均工资最大排序
select job,avg(sal)from emp groupby job orderbyavg(sal)desc；
结果：
JOBAVG(SAL)
1 PRESIDENT 5000
2 ANALYST 3000
3 MANAGER 2758.33333333333
4 SALESMAN 1400
5 CLERK 1037.5
Select*from(select job,avg(sal)from emp groupby job orderbyavg(sal)desc)whererownum<=2;
结果：
JOB AVG(SAL)
1 PRESIDENT 5000
2 ANALYST 3000
分页查询，每页显示5条记录，按工资从大到小显示
select*
from(selectrownumno, e.*from(select*from emp orderby sal desc) e)
whereno>=1andno<=10
select*
from(selectrownumasno, e.*
from(select*from emp e orderby e.sal desc) e
whererownum<=5)
whererownum>=1
得到每个月工资总数最少的那个部门的部门标号，部门名称，部门位置：
先求出工资总数最少的部门编号：
select e1.deptno
from(select
e.deptno,sum(sal)
from emp e
groupby e.deptno
orderbysum(sal)) e1
whererownum=1
结果:
DEPTNO DNAME LOC
1 10 ACCOUNTING NEW YORK
然后通过部门标号作为条件查部门表：
select*
from dept d
where
d.deptno =(select e1.deptno
from(select
e.deptno,sum(sal)
from emp e
groupby e.deptno
orderbysum(sal)) e1
whererownum=1);
结果：
DEPTNODNAME LOC
1 10 ACCOUNTINGNEW YORK
查出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字，部门名字，部门位置 .
select e2.ename,e2.sal,d.dname, d.loc
from(selectrownumno, e1.*
from(select*
from emp e
where e.deptno =20
or e.deptno =10
orderby e.sal desc) e1) e2, dept
d
where
e2.deptno = d.deptno
and e2.no>=3
and e2.no<=5;
按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
select dept.dname, dept.loc
from(selectrownumno, deptno
from(select deptno,count(*)as empcount
from emp
groupby deptno
orderby empcount desc))e,
dept
where e.deptno = dept.deptno
andnobetween2and5;
查询出king所在部门的部门号\部门名称\部门人数
两种方法：第一种效率比第二种效率高了很多
第一种：
with temp as
(select deptno,count(*) empcount
from emp e2
where
e2.deptno =
(select deptno from emp e1 where lower(e1.ename)='king')
groupby deptno)
select
d.dname,d.loc,temp.empcount from temp,dept d where
temp.deptno = d.deptno;
第二种
select d.dname,d.loc,temp.empcount from
(select deptno,count(*) empcount
from emp e2
where
e2.deptno =
(select deptno from emp e1 where lower(e1.ename)='king')
groupby deptno) temp, dept d where
temp.deptno = d.deptno;
查询出king所在部门的工作年限最大的员工名字
select e1.ename, e1.hiredate
from emp e1
where
e1.hiredate in
(selectmin(e.hiredate) longhire
from emp e
where
e.deptno =
(select deptno from emp where lower(ename)='king'))
查询出工资成本最高的部门的部门号和部门名称 (第一种比第二种效率低)
select d.deptno,d.dname,t.sum_sal
from
dept d,
(select deptno,sum(sal) sum_sal from emp groupby deptno havingsum(sal)=
(selectmax(sum(sal))from emp groupby deptno)
) t where
d.deptno = t.deptno;
select d.deptno, d.dname, t.sum_sal
from dept d,
(select*
from(select deptno,sum(sal) sum_sal
from emp
groupby deptno
orderbysum(sal)desc)
whererownum<=1) t
where
d.deptno = t.deptno;
exists：
（1）由于exists关键字的返回值取决于查询是否返回行，而不取决于这些行的内容，因此对子查询来说，输出列表无关紧要，可以使用*代替。如果子查询返回一行或者多行，那么exist便返回true，否则返回false
（2）对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主
查询记录较少，子查询中的表大，又有索引时使用exists。
其实我们区分in 和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，
那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返
回为目标，那么就会考虑到索引及结果集的关系了
另外IN 是不对NULL进行处理
如：
select 1 from dual where null in (0,1,2,null)
为空
视图
视图：是一个封装了各种复杂查询的语句，就称为视图。
15.1、创建视图
CREATE VIEW 视图名字(字段) AS 子查询
建立一个只包含20部门雇员信息的视图（雇员的编号、姓名、工资）
CREATE VIEW empv20 (empno,ename,sal) AS SELECT empno,ename,sal FROM emp
WHERE deptno=20 ;
例如：将之前的一个复杂语句包装成视图
显示部门内最低工资比20部门最低工资要高的部门的编号及部门内最低工资：
SELECT deptno,MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal)>(SELECT
MIN(sal) FROM emp WHERE deptno=20) ;
此时就可以将上面的复杂查询语句建立一张视图，之后查询视图即可。
15.2、高级视图
如果要创建一个同名的视图，则必须先将之前的视图删除掉，再进行创建：
删除视图：dropview empv20;
有些时候如果先删除再创建操作会比较麻烦，所以有时候最好的方式：如果视图存在则先
自动删除，之后自动创建。
createorreplaceview empv20(deptno,minsal)
As
select deptno,min(sal) from emp groupby deptno havingmin(sal)>(selectmin(sal) from emp where deptno=20);
例如，还是创建一个只包含20部门的视图
CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno) AS SELECT
empno,ename,sal,deptno FROM emp WHERE deptno=20 ;
现在直接更新视图里的数据
将7369的部门编号修改为30。此操作在视图中完成。
update empv20 SET deptno=30 where empno=7369 ;
此时，提示更新完成。
默认情况下创建的视图，如果更新了，则会自动将此数据从视图中删除，之后会更新原本
的数据。
思考：
如果能这样做的话，肯定存在问题，因为视图最好还是不要更新。
在建立视图的时候有两个参数：
· WITH CHECK OPTION 保护视图的创建规则
CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno)
AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20
WITH CHECK OPTION CONSTRAINT empv20_ck;
再执行更新操作：
update empv20 SET deptno=30 where empno=7369 ; 此处更新的是部门编号，失败
|- 之前是按照部门编号建立的视图，所以不能修改部门编号
update empv20 SET ename='tom' where empno=7369 ; 可以更新，更新的是名字，成功
· WITH READ ONLY（只读，不可修改），视图最好不要轻易的修改
CREATE OR REPLACE VIEW empv20 (empno,ename,sal,deptno)
AS SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20
WITH READ ONLY;
现在任意的字段都不可更改，所以现在的视图是只读的。
如果视图的基表有多行查询（比如：group by,distinct）那么该视图也是只读的
索引
select * from user_indexes; --查询现有的索引
select * from user_ind_columns;--可获知索引建立在哪个字段上
创建索引
createindex abc on student(sid,sname)；
createindex abc1 on student(sname,sid);
索引对 abc select * from student wheresid=1 这样的查询更有效。
索引对 abc select * from student where sname =‘loci‘ 这样的查询更有效。
因此建立索引的时候，字段的组合顺序是非常重要的，一般情况下需要经常访问的字段放在前面。
删除索引：
dropindex abc;
索引类型：
B树索引（B-Tree Index）
创建索引的默认类型，结构是一颗树，采用的是平衡B树算法：
右子树节点的键值大于等于父节点的键值
左子树节点的键值小于等于父节点的键值
比如有数据:100,101,102,103,104,105,106
位图索引(BitMap Index) ：位图索引主要是针对大量相同值得列而创建。
例子：全国居民信息表，假设有四个字段：姓名、性别、年龄、和身份证号。年龄和性别连个字段会产生许多相同的值，性别只有男女两种值，年龄1-120（假设年龄最大值为120）。那么不管一张表有几亿条记录，但根据性别字段来区分的话，只有两种取值（男，女），那么位图字段就是根据字段的这种特性所建立的一种索引。
如果表中的某些字段取值范围比较小，比如职员性别、分数列ABC级等。只有两个值。
这样的字段如果建B树索引没有意义，不能提高检索速度。这时我们推荐用位图索引
Create BitMap Index student on(sex);
管理索引
1）先插入数据后创建索引
向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行
数据的时候都要更改索引。这样会大大降低插入数据的速度。
2）设置合理的索引列顺序
3）限制每个表索引的数量
4）删除不必要的索引
5）为每个索引指定表空间
6）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎
DDL改变表的结构
创建表
createtable emp1(
idvarchar(2),
namevarchar(10)
);
使用子查询创建表
createtable emp2 asselect * from emp1;
添加字段
altertable emp2 add sex varchar(2);
修改字段
altertable emp2 modify sex varchar(2); --修改字段值大小
altertable emp2 rename column sex to sex1; --修改字段名称
删除字段
altertable emp2 dropcolumnname;
清空表中数据
deletefrom emp1；
truncatetable emp1;
delete 删除数据，如果发现删除了，可以通过rollback进行回滚。如果使用了截断表，则表示所有的数据不可恢复了。所以速度很快。
删除表
Drop table student；
重命名表
rename emp1 to emp3;
DML改变数据结构
Insert
表间拷贝数据：
Insert into dept1(id,name) select deptno,dname from dept;
Update
将编号为7779用户的工作换成编号为7566的雇员的工作和所属上级。
Update myemp set(job,mgr) = (select job,mgr from myemp where empno=7566) where empno=7779;
Merge
create table test1(eid number(10), name varchar2(20),birth date,salary number(8,2));
insert into test1 values (1001, '张三', '20130901', 2300);
insert into test1 values (1002, '李四', '20131023', 6600);
insert into test1 values (1003, '王五', '20131025', 5000);
select * from test2;
create table test2(eid number(10), name varchar2(20),birth date,salary number(8,2));
insert into test2 values (1001, '王五', '20140403', 5000);
insert into test2 values (1004, '祝捷', '20141203', 8000);
select * from test2;
merge当id匹配的时候更新，当不匹配的时候插入
merge 的三种用法：
（1）既插入也更新
merge into test2
using test1
on(test1.eid = test2.eid )
when matched then
update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);
(2) 只插入不更新
Merge into test2
Using test1 0n(test1.eid=test2.eid)
When not matched then
insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);
(3)只更新不插入
Merge into test2
Using test1 on (test1.eid=test2.eid)
When matched then
Update set name = test1.name,birth =test1.birth,salary = test1.salary;
例子：
5.为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长
18%（要求用DECODE函数）
update emp
set sal=decode(deptno,'10',sal*(1+0.1), '20',sal*(1+0.15), '30',sal*(1+0.2),sal*(1+0.18));
6.根据工作年限长工资，标准是：为公司工作了几个月就长几个百分点。
update emp set sal= round(sal * (1+(sysdate - hiredate)/365/12/100),2);
序列、同义词
序列创建
-- Create sequence
createsequence SEQ_FIN_CHECK_RELATION_DEF
minvalue1
maxvalue999999999999999999999999999
startwith361
incrementby1
cache20;
Createsequence myseq
Startwith1
Incrementby1
Order
cache20
Nocycle;
select myseq.nextval from dual;
select myseq.currval from dual;
查询完之后就已经自增1了
Insert into table1 values(myseq.nextval) 这时候已经是2了
Cycle，Cache
而用了nocycle，就可以确保当该序列用于多张表的时候，ID 是唯一的
用cycle时，用法如下：
createsequence myseq2 startwith1incrementby1cyclemaxvalue3
nocache ;
这样到3之后，要会重新从1开始
如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里
面的取完后，oracle自动再取一组到cache。 使用cache或许会跳号， 比如数据库突然不正常down掉
（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止
这种情况
不能改变当前值，但是可以改变增量
Alter sequence myseq increment by 3;
同义词
使用PL/SQL建立DB Link
-- Create database link
创建dblink
createdatabaselink ODMLP
connectto ODM
using'DW_GOV';
创建同义词：
createorreplacesynonym LP
for DUAL@ODMLP;
删除同义词LP
dropsynonym LP;
作用：
很方便的操作不同用户下的对象
能使两个应用程序使用不同的名字指向同一张表
使用不同的用户指向同一张表的。
Create synonym dept for soctt.dept;(这样创建的同义词是私有的，只有创建者才能用)
Drop synonym dept;
Create public synonym dept for soctt.dept;（这样创建的同义词才是公有的）
Drop public synonym dept;
解锁
SELECT/*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username isNOTNULL;
altersystemkillsession'35,2947';
altersystemkillsession'15,2407';
存储过程学习：
SP_F_CARD_EVENT增量：流水表，数据量大，跑增量，删除当天的数据，根据时间进行删除，数据按时间段删除。给一张表只增不减。
SP_F_CARD_ACCT变量：客户信息表，即数据会增加也会减少，改变某个字段的信息，例如改变客户的年龄这个字段。
全量：数据量小SP_RPT_F_LN_LNMOA_H
建立分区：为了提高查询效率，建立分区相当于建立块
-- Create table
createtable RPT_F_LN_LNMOA_H
(
inst_no VARCHAR2(10),
curr_cd VARCHAR2(10),
it_cd VARCHAR2(4),
ac_sq VARCHAR2(8),
check_wz VARCHAR2(1),
cust_no VARCHAR2(10),
cust_name VARCHAR2(62),
bal_at VARCHAR2(1),
subj_cd VARCHAR2(5),
last_tx_dt NUMBER(8),
last_bal NUMBER(15,2),
last_jx_dt NUMBER(8),
last_zx_dt NUMBER(8),
con_no VARCHAR2(32),
acct_bal NUMBER(15,2),
jx_ff VARCHAR2(1),
fx_falg VARCHAR2(1),
ny_inrt VARCHAR2(1),
inrt NUMBER(9,7),
accum_inrt NUMBER(13,2),
accum_bal NUMBER(20,2),
yjj_inrt NUMBER(13,2),
accum_yjj_inrt NUMBER(20,2),
ln_acct_no VARCHAR2(25),
cl_acct_no VARCHAR2(25),
open_dt NUMBER(8),
open_user VARCHAR2(6),
clean_dt NUMBER(8),
clean_user VARCHAR2(6),
mt_dt NUMBER(8),
mt_user VARCHAR2(6),
st_cd VARCHAR2(1),
ch_amt NUMBER(15,2),
bal NUMBER(15,2),
moa_amt NUMBER(15,2),
next_back_dt NUMBER(8),
etl_bz_dt VARCHAR2(50),
etl_src_sys VARCHAR2(50),
etl_load_dt DATE
)
partitionbyrange (ETL_BZ_DT)
(
partition P20131231 valueslessthan ('20140101')
tablespace RIDED
pctfree10
initrans1
maxtrans255
storage
(
initial72M
next1M
minextents1
maxextentsunlimited
),
partition P20140228 valueslessthan ('20140301')
tablespace RIDED
pctfree10
initrans1
maxtrans255
storage
(
initial8M
next1M
minextents1
maxextentsunlimited
),
partition P20140331 valueslessthan ('20140401')
tablespace RIDED
pctfree10
initrans1
maxtrans255
storage
(
initial8M
next1M
minextents1
maxextentsunlimited
)
);
-- Add comments to the table
commentontable RPT_F_LN_LNMOA_H
is'欠息主文件';
-- Add comments to the columns
commentoncolumn RPT_F_LN_LNMOA_H.inst_no
is'机构代号';
commentoncolumn RPT_F_LN_LNMOA_H.curr_cd
is'货币代号';
commentoncolumn RPT_F_LN_LNMOA_H.it_cd
is'业务代号';
commentoncolumn RPT_F_LN_LNMOA_H.ac_sq
is'帐号序号';
commentoncolumn RPT_F_LN_LNMOA_H.check_wz
is'检查位';
commentoncolumn RPT_F_LN_LNMOA_H.cust_no
is'客户号';
commentoncolumn RPT_F_LN_LNMOA_H.cust_name
is'客户中文名';
commentoncolumn RPT_F_LN_LNMOA_H.bal_at
is'余额性质';
commentoncolumn RPT_F_LN_LNMOA_H.subj_cd
is'科目号';
commentoncolumn RPT_F_LN_LNMOA_H.last_tx_dt
is'上次交易日';
commentoncolumn RPT_F_LN_LNMOA_H.last_bal
is'上期余额';
commentoncolumn RPT_F_LN_LNMOA_H.last_jx_dt
is'上次计息日';
commentoncolumn RPT_F_LN_LNMOA_H.last_zx_dt
is'上次转息日';
commentoncolumn RPT_F_LN_LNMOA_H.con_no
is'合同编号';
commentoncolumn RPT_F_LN_LNMOA_H.acct_bal
is'帐户余额';
commentoncolumn RPT_F_LN_LNMOA_H.jx_ff
is'计息方法';
commentoncolumn RPT_F_LN_LNMOA_H.fx_falg
is'复息标志';
commentoncolumn RPT_F_LN_LNMOA_H.ny_inrt
is'年／月利率';
commentoncolumn RPT_F_LN_LNMOA_H.inrt
is'利率';
commentoncolumn RPT_F_LN_LNMOA_H.accum_inrt
is'累计利息';
commentoncolumn RPT_F_LN_LNMOA_H.accum_bal
is'积数';
commentoncolumn RPT_F_LN_LNMOA_H.yjj_inrt
is'应加/减利息';
commentoncolumn RPT_F_LN_LNMOA_H.accum_yjj_inrt
is'应加/减积数';
commentoncolumn RPT_F_LN_LNMOA_H.ln_acct_no
is'贷款帐号';
commentoncolumn RPT_F_LN_LNMOA_H.cl_acct_no
is'结算帐号';
commentoncolumn RPT_F_LN_LNMOA_H.open_dt
is'开户日期';
commentoncolumn RPT_F_LN_LNMOA_H.open_user
is'开户柜员';
commentoncolumn RPT_F_LN_LNMOA_H.clean_dt
is'销户日期';
commentoncolumn RPT_F_LN_LNMOA_H.clean_user
is'销户柜员';
commentoncolumn RPT_F_LN_LNMOA_H.mt_dt
is'维护日期';
commentoncolumn RPT_F_LN_LNMOA_H.mt_user
is'维护柜员';
commentoncolumn RPT_F_LN_LNMOA_H.st_cd
is'记录状态';
commentoncolumn RPT_F_LN_LNMOA_H.ch_amt
is'挂帐金额';
commentoncolumn RPT_F_LN_LNMOA_H.bal
is'余额';
commentoncolumn RPT_F_LN_LNMOA_H.moa_amt
is'欠息金额';
commentoncolumn RPT_F_LN_LNMOA_H.next_back_dt
is'下次还款日期';
commentoncolumn RPT_F_LN_LNMOA_H.etl_bz_dt
is'业务日期';
commentoncolumn RPT_F_LN_LNMOA_H.etl_src_sys
is'业务系统';
commentoncolumn RPT_F_LN_LNMOA_H.etl_load_dt
is'业务装载时间';
