Oracle(一)

r('1520.99','9999.99') from dual;--参数2：转换后的格式
    
    需求10：通用函数nvl2(a,b,c) 
    特点：当a=null 时返回c,否则返回b    
    例如 select ename,sal,comm,sal+nvl2(comm,comm,0) from emp;--回忆nvl(字段,0)
    
    需求11：nullif(a,b) 
    特点：当a=b的时候返回null,否则返回a  
    例如 select nullif('abc','abc') from dual;  
    用处：可以判断两个值是否相等
    
    需求11：从左往右找到第一个不是null的值 coalesce(comm,sal)    
    例如: select comm,sal,coalesce(comm,sal) as "第一个不为空的值" from emp; 
    注意：字段别名要用双引号引起来
    */
（9）需求--如给不同职位的人加不同的工资  

    方式1：用 case  when  then end  语句来完成条件选择

select ename,sal as 涨前, 
  case job when 'PRESIDENT' 
    then sal+1000
           when 'MANAGER' 
    then sal+800
    ELSE sal+300 -- 注意这里的写法
  end 
  as 加后 from emp;
说明：SQL99的规范，条件语句语法，以case打头，以 end结尾

   方式2：采用 decode()函数来实现条件选择

--decode(['参数1','参数2','参数3']...);
--参1 判断的字段名，条件的值是什么，你想干什么，条件的值是什么，你想干什么，else
select ename,job,sal as 涨前,
   decode('job','PRESIDENT',sal+1000,'MANAGER',sal+800,sal+400) 
as 涨后 from emp;
（10）组合数

/*组函数(相当于MySQL中的聚合函数)  
     max(sal) 求最大值
     min(sal) 求最小值
     count(*) 统计个数 --注意：指定字段与不指定字段的区别！！！
     sum(sal) 求和
     avg(sal) 求平均值*/
注意：组函数一般会自动忽略null值；

（11） groupby 和 having的分组查询

需求1：查询每个部门的总工资  

select deptno,sum(sal) as 总工资 from emp group by deptno;
思路：先分组，再查询

需求2：查询每个部门的平均工资在两千以上的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
思路：先分组，再查询，最后过滤

需求3：查询每个部门中不同岗位的总工资

select deptno,job,sum(sal) from emp group by deptno,job;  
语法要求：select后面要查询的字段(除了组函数字段)，必须在 group by 后面也要有，不然语法报错 

说明：分组条件可以有多个 ，这里是按部门和岗位分组的

需求4：查询 10 部门的平均工资

方式1：用where来筛选(查询时就指定条件)

 select deptno,avg(sal) from emp where deptno=10 group by deptno;
方式2：先分组，再用having 筛选

select deptno,avg(sal) from emp group by deptno having deptno=10;
 注意： where 和 having的区别是where 是在分组之前进行筛选, 而having 是对分组之后的结果集 进行再次筛选

需求5：按照部门统计各部门不同工种的工资情况按照下图的格式输出(group by 语句增强 )

思路：

--(1)求各部门每个工种的总工资
    select deptno,job,sum(sal) from emp group by deptno,job;
--(2)求每个所有部门的总工资
 select deptno,sum(sal) from emp group by deptno;
--(3)求总工资
select sum(sal) from emp;
增强：

select deptno,job,sum(sal) from emp group by rollup(deptno,job);
-- 思路：先按(deptno,job)分组，然后deptno分组，最后null分组
-- 逐次递减
结果：

    

需求：那上面的样子不好看我们可以排版，用下面一条命令

break on deptno skip 2； 
-- break on deptno 表示相同的部门号只显示一次, skip 2 每个部门之间,相隔两个空行
/  --斜杠表示执行上一条语句
（12）多表查询

（1）笛卡尔积(非条件)  

需求1：查询员工的信息--员工编号、员工名称、工资、部门名称

select emp.empno,emp.ename,emp.sal,dept.deptno from emp,dept;
结果(未列出全表，部门展示)

     

特点：多张表没有条件的组合在一块，查出的数据(交叉组合)不准确也没有意义

（2）条件查询(需求同上--等值连接)

说明：员工信息在emp表，部门名称在dept表，所以需要两张表连接条件是：两张表的deptno 相等；

     

注意：连接条件个数=表的数量-1

（3）需求2：查询员工的工资级别(不等值连接)

分析：如果一个员工的工资大于等于下限， 小于等于上限，我们就可以知道工资的级别

--方式1  
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal>=salgrade.losal and emp.sal<=salgrade.hisal;
--方式2(between and)  
select emp.ename,emp.sal,salgrade.grade from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal;
结果：

      

说明：用到两张表emp 表和 salgrade表 

（4）需求3：查询部门号、部门名称以及这个部门有多少个人(内连接)

    分析：要统计多少个人肯定要查emp表，就需要查询 emp 表和dept表

select dept.deptno,dept.dname,count(emp.empno) from dept,emp where dept.deptno=emp.deptno group by dept.deptno,dept.dname;
    结果：

      

    思考：注意观察上面的查询结果，发现结果值罗列出了10、20、30 号3个部门的信息，但是一个40 号部门没列出来？

    原因：因为40号部门没有人

    内连接的特点：就是不符合条件的就不会显示出来

   需求：即使这个部门没有人，我也要把部门编号和部门名称显示出来，人数以0显示 

   做法：那么我们就需要用外连接

（5）外连接(左外连接、右外连接)

需求1：对于某些不成立的记录（40号部门），任然希望包含在最后的结果中

左连接：

--写法：where e.deptno=d.deptno(+);当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中
右连接：

--写法: where e.deptno(+)=d.deptno;--右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中
说明：'+'号在左边表示是右连接，反之亦然

结果：

说明：上面的只是一种简便形式

方式2：

-- 采用 left outer join 表名 on 两张表的连接条件  outer 可以省略不写
select dept.deptno,dept.dname,count(emp.empno) as 人数 from dept left join emp on dept.deptno=emp.deptno group by dept.deptno,dept.dname;
方式1和方式2的部分对比：

 --from dept,emp where dept.deptno=emp.deptno(+)
 --from dept left join emp on dept.deptno=emp.deptno
 --说明：摘取部分(两种方式等价)
（6）需求：要查询员工姓名对应的老板姓名(自连接查询)

特点：通过表的别名给一张表起两个别名，将他视为两张表来进行查询

分析：因为这些信息都在一张表 emp中， 比如员工号7369的SMITH它对应的老板编号是(MGR)7902，而7902 又是员工FORD(7902) 那FORD对应的老板编号又是 7566，所以说一个员工既是某几个员工的老板，他也有自己的老板，所以我要查询这个员工的所对应的老板就可以使用自连接查询。

假设：有两张表一张员工表、一张老板表，如果员工的老板号=老板的员工号就表示这个员工是另外一个员工的老板

select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno; --注意这里给表起别名时尽量不要写as
结果：

注意：自连接不适合数据量大的表，因为自连接实际上是将一张表看成了两张表 ,那么两张表关联起来查询形成笛卡尔积那么查询的条数就很多