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
结果:
注意:自连接不适合数据量大的表,因为自连接实际上是将一张表看成了两张表 ,那么两张表关联起来查询形成笛卡尔积那么查询的条数就很多