Oracle（二）函数，与各种连接查询

函数

    Nvl

              selectename,sal*13+comm*13 from emp;

              如果comm字段中有null数据，结果会变null,如何让它转为0呢？

              Selectename,sal*13+nvl(comm,0)*13 from emp;

             

              如何查找1982.1.1后入职的员工？

              日期是无法做比较的，需要加to_char(列名，‘yyyy-mm-dd’)。

              例：select * from emp whereto_char(hiredate,’yyyy-mm-dd’)>’1982-1-1’;

              查询4月份入职的员工

              Select*from emp where to_char(hiredate,’mm’)=’4’;

 

查询工资高于500或者岗位为MANAGER的员工，同时还满足他们姓首字母大写为J。

              Select*from emp where(sal>500 or job = ‘MANAGER’)and(ename like’J%’);

    数据分组

              max,min,avg,sum,count

              如何显示所有员工最高工资和最低工资

              Selectmax(sal) from emp;   select min(sal) fromemp;

             

              显示所用员工的平均工资和工资总和。

              Selectavg(sal),sum(sal) from emp;

    平均值;avg

avg(sal)不会把sal为null的行进行统计，因此我们要注意，如果希望为空值也考虑进去（全部都算）---select sum(sal)/count(*) from emp;

 

    统计;count

              计算共有多少员工---select count(*) from emp;

              Count(*)可以对一个字段进行统计，比如count(comm)

    子查询

              请显示工资高于平均工资的员工信息

              Select*from emp where sal >(select avg(sal) from emp);

    Group by 和 having 子句

              Groupby 用于对查询的结果分组统计

              Having用于过滤分组显示结果

             

              如何显示每个部门的平均工资和最高工资

              Selectavg(sal),max(sal),deptno from emp group by deptno;

             

              如何显示每个部门的平均工资和最低工资的每种岗位

              Selectavg(sal),max(sal),deptno from emp group by deptno job;

 

              显示部门平均工资低于2000的部门号和它的平均工资

              思路：1.查询出各个部门的平均工资

                     Selectavg(sal),deptno from emp group by deptno

2.挑出低于2000

Select avg(sal),deptno from emp group by deptno havingavg(sal)<2000;

 

小结：

1.    分组函数只能出现在选择列表，having,order by 子句中。

2.    如果在select语句中同时包含有group by，having,order by,那么他们的顺序是group by --- having --- order by

3.    在选择列中如果有列，表达式，和分组函数，那么这些列和表达式必须有一个出现在group by 中，否则出错。

 

多表查询

       在多表查询的时候，如果不带任何条件，则会出现笛卡尔集

       怎样避免迪卡尔集？

       注意：多表查询的条件是，至少不能少于表的个数-1；

      

       如何显示部门号为10的部门名，员工名和工资

Select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptnoand emp.deptno = 10;

 

自连接：是指在同一张表的连接查询

显示各员工的姓名和他的上级领导姓名

思路：把emp表看做两张表（worker,boss）

       Selectworker.ename,boss.ename form emp worker,emp boss where worker.mgr=boss.empno;

 

分页查询

       Mysql: select * from 表名 where 条件limit 从第几条取，取几条。

       Sqlserver: select top 4 *from 表名 where id not in(selecttop 4 id from 表名 where 条件)；  排除前4条，再取4条，这个案例实际上是取出5~8

 

       Oracle:selectt2.* from (select t1.*,rownum rn from (select * from emp)t1 where rownum<=6)t2 where rn >=4;

       说明：上面这个sql是oracle数据库效率比较高的方法。

       第一层:select * from emp;

       第二层：select t1.*,rownum rn form (select * from emp )t1 whererownum<=6’

       第三层：~

 

       练习：请按照入职时间的先后顺序，查询从第7到第10个人是谁？

Select t2.* from(select t1.*,rownum rn from (select * from emp order by hiredate)t1 whererownum<=10)t2 where rn>=7;

10表示取到第几条，7表示从第几条开始取。

如果我们针对不同的情况，分页，请在最内层进行处理

Rownum是oracle每张表都默认隐藏的字段。

 

自我复制（蠕虫）

       Createtable mytest as select empno,ename.sal.comm.deptno from emp;

       说明：以emp为模板，把其中的字段数据取出来，作为新创建mytest表的字段。

 

Insert intomytest (emptno,ename,sal,comm,deptno)

selectempno,ename,sal,comm,deptno from mytest;

说明：以自己的数据为基础，查询出来后添加在自己身上。

 

合并查询

（1）    union：用于取两个结果集的并集，会自动去掉结果集中重复行

（2）    union all：它不会取消重复行

（3）    insersect：用于取得两结果集的交集

（4）    miuns：用于取两个结果集的差集，只会显示存在第一个集合中

 

                     关键字：cube(t1,t2)

              Selectavg(sal),deptno,job from emp group by cube(deptno,job);

              先用deptno分组，再用job分组，两者结合再分组。

 

内连接和外连接

内连：t1 inner join t2 on

Select emp.ename,dept.dname from emp,dept whereemp.deptno=dept.deptno;

Select emp.ename,dept.dname from emp inner join deptnoemp.deptno=dept.deptno;

内连接特点是只有两张表同时匹配，才被选择

左外连接：left join on

Select stu,name,stu.id,exam.grade from stu left join examon stu.id = exam.id;

如果stu(左表)的记录没有和exam任何一条匹配，也会被选中

以左表为准，左表全显示，右表只显示匹配上的，没有则为null

完全连接：full outer join on

两个表查询，不管有没有匹配都显示，若无相应的配对，显示空

Select stu.name,stu.id,exam.grade from stu full outer join exam onstu.id=exam.id;