排序 select * from emp order by sal desc(降序),hiredate asc(升序,默认) 模糊查询 select * from emp where ename not like '_A%';第二个字母不是A的 ‘A%’(以A开头) ‘%A%’(中间有A的) _ 单个匹配 % 多个匹配
日期函数 select sysdate from dual; select sysdate,sysdate+3,sysdate-3,sysdate-hiredate from dual; 日期只有加数字,减数字,减日期(大减小) 无日期+日期 select last_day(sysdate) from dual;本月最后一天 select next_day(sysdate,'星期一') from dual;求出下个星期1的日期 select add_months(sysdate,3) from dual;3个月之后的今天 select ename,months_between(sysdate,hiredate) from emp;获得到目前的月份,最好trunc(months_between(sysdate,hiredate))截取掉小数
转换函数(number,date,varchar) 日期转字符串: select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;以2011-11-22开始,如果是1月1日,结果为2011-01-01,保留0(消除0加fm)即‘fmyyyy-mm-dd’ 默认是12小时制,换成24时应为hh24:mi:ss 注yyyy,mm,dd,hh,mi,ss是固定格式
通用函数 nvl处理null select ename,(sal+comm)*14 from emp;会发现comm有的字段是空,所以算出来有的年薪也为空 解决办法就是select ename,(sal+nvl(comm,0))*14 from emp;
decode(数值|列,判断1,显示1,判断2,显示2,···)处理多值判断,必须成对出现,类似于if else select ename,sal,empno,decode(job,'CLERK','办事者','SALESMAN','销售者','MANAGER','管理者','ANALYST','分析员','PRESIDENT','总裁') from emp; 注意CLERK等一定要大写
左右连接 (+)= select e.ename,e.sal,m.ename from emp e,emp m where e.mgr=m.empno(+);
分组函数 count()一共记录数 avg()平均值 sum()求和 max()最大值 min()最小值 group by 分组函数 select count(*),avg(sal),sum(sal),max(sal),min(sal) from emp; 注意 1,分组函数可以在没有分组的时候单独使用,但不能出现其他字段 select count(*),sal from emp 错误;不是单独的分组函数 不能出现sal 2,如果进行分组,则select后只能出现分组的字段和统计函数 select job,count(*),max(sal) from emp group by job
select d.dname,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname;
where和having区别: where: 执行group by之前的过滤,表示从全部数据中选出部分数据,where中不能使用统计函数,即 where avg(sal) having: 执行group by之后的再次过滤,可以使用统计函数 having avg(sal)>2000 如果想在分组之后再次过滤 就用having
select d.dname,count(e.empno),nvl(avg(e.sal),0) from emp e,dept d where e.deptno(+)=d.deptno group by d.dname having avg(e.sal)>2000;
select e.job,sum(sal) from emp e where e.job<>'salesman' group by e.job having sum(sal)>5000 order by sum(sal);
子查询 in any all in 指定是不是在此范围 select * from emp where sal not in(select sal from emp where ename='BLAKE'); any 与每一个内容相匹配,有3种形式:=any,>any,<any select * from emp where sal=any(select sal from emp where ename='BLAKE');与in相同 select * from emp where sal>any(select sal from emp where ename='BLAKE');比最小的还要大 select * from emp where sal<any(select sal from emp where ename='BLAKE');比最大的还要小
all与每一个相比较 两种形式 >all <all select * from emp where sal>all(select sal from emp where ename='BLAKE');比最大的还要大 select * from emp where sal<all(select sal from emp where ename='BLAKE');比最小的还要小
与多表查询区别: 例题:查询出每个部门的编号,名称,部门人数,平均工资 多表查询:select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal) from emp e,dept d where d.deptno=e.deptno group by d.deptno,d.dname,d.loc; 产生14*4=56条笛卡尔积 子查询: select d.deptno,d.dname,d.loc,temp.count,temp.avg from dept d,(select avg(sal) avg,deptno depno,count(ename) count from emp group by deptno) temp where temp.depno=d.deptno; 子查询查emp表14条,查出3条,dept表4条 笛卡尔积一共12条,加上子查询的14条一共26条 性能比较好 子查询适用于如果最终的查询结果之中需要select子句,但是又不能直接使用统计函数,就在子查询中统计信息,在外部负责将统计信息与表数据想关联
数据伪列 ROWNUM 实现分页 取第6到10条数据不能用rownum between 6 and 10 因为rownum不是真实的列,只能用子查询 即子查询中查出rownum小于10的,外查询选出大于5的 select * from (select rownum rm,empno,ename,job,hiredate,sal from emp where rownum<=5) temp where temp.rm>0; 第一页 select * from (select rownum rm,empno,ename,job,hiredate,sal from emp where rownum<=10) temp where temp.rm>5; 第二页 select * from (select rownum rm,empno,ename,job,hiredate,sal from emp where rownum<=15) temp where temp.rm>10; 第三页
ROWID 实现删除重复名字的行 delete from dept where rowid not in (select min(rowid) from dept group by dname,loc); 以dname分组,如果dname重复,则选出来这个重复名字的最小的rowid,不是这个的都删除
select e.empno,e.ename,tem.e2,tem.dn from emp e, (select en.deptno e1,en.ename e3,nvl(te.ename,null) e2,d.dname dn from emp en,emp te ,dept d where te.empno(+)=en.mgr and d.deptno=en.deptno) tem where tem.e3=e.ename and e.sal>all( select sal from emp where ename in('SMITH','ALLEN') ) ;
select e.ename,e.ename,d.dname,m.ename from emp e,emp m,dept d where e.sal>all(select sal from emp where ename in('SMITH','ALLEN')) and e.mgr=m.empno(+) and e.deptno=d.deptno;
列出所有员工的编号,姓名,领导的编号,姓名,显示结果按领导的年工资的降序排列 select e.empno,e.ename,m.empno,m.ename,(m.sal+nvl(m.comm,0))*12 income from emp e,emp m where e.mgr=m.empno(+) order by income desc;
列出受雇日期早于直接上级的所有员工的编号,姓名,部门位置,部门名称,部门人数 select e.empno,e.ename,e.hiredate,d.dname,d.loc,temp.count from emp e,emp m,dept d ,(select deptno dno,count(deptno) count from emp group by deptno) temp where e.mgr=m.empno and e.deptno=d.deptno and e.hiredate<m.hiredate and e.deptno=dno;
列出部门名称和这些部门员工的信息(数量,平均工资),同时列出没有员工的部门 select d.dname,avg(e.sal),count(e.deptno) from emp e,dept d where e.deptno=d.deptno(+) group by d.dname;
select d.dname,e.avg,e.count from dept d,(select deptno dno,avg(sal) avg,count(sal) count from emp group by deptno) e where d.deptno=e.dno(+);
6列出所有‘clerk’(办事员)的姓名及其部门名称,部门人数,工资等级
false:select e.ename,d.dname,s.grade,count(e.empno)from emp e,dept d,salgrade s where e.deptno=d.deptno(+) and e.job='CLERK' and e.sal between s.losal and s.hisal group by e.ename,d.dname,s.grade; true:select e.ename,d.dname,s.grade,temp.count from emp e,dept d,salgrade s ,(select deptno dno,count(empno)count from emp group by deptno) temp where e.deptno=temp.dno and e.sal between s.losal and s.hisal and e.job='CLERK' and e.deptno=d.deptno;
7.列出最低薪金大于1500的各种工作的全部雇员人数及所在的部门名称,位置,平均工资
false:select temp.job,d.loc,d.dname,avg from dept d,(select job,deptno,min(sal) min,avg(sal)avg from emp group by job,deptno having min(sal) >1500)temp where temp.deptno=d.deptno(+) ; true:select temp.job,temp.count,d.dname,e.ename,res.avg from dept d,emp e, (select e.job job,count(e.empno) count from emp e group by job having min(e.sal)>1500)temp, (select deptno,avg(sal) avg from emp group by deptno)res where e.deptno=d.deptno and e.job=temp.job and res.deptno=e.deptno;
6,7两题错误的原因是不懂group by的真正意思,例如第7题的temp表和res表不能合并,加入合并 代码为 select job,deptno ,count(empno) count from emp group by job,deptno having min(sal)>1500 这时count后的值基本上全为1,除了analyst的sal都是3000,为什么出现这情况呢?group by以···分组,后面两个字段则以两个字段分组 所以第七条正确答案拼了两个表,分别group by job,和group by deptno,而不是group by job,deptno 之后由于group by job后没法和外面关联,所以新建一个emp表,用其job字段和temp表关联
排序select * from emp order by sal desc(降序),hiredate asc(升序,默认)模糊查询select * from emp where ename not like '_A%';第二个字母不是A的 ‘A%’(以A开头) ‘%A%’(中间有A的) _ 单个匹配 % 多个匹配substr 截取字符串select ena