Oracle(一)

青旅半醒 2022-05-04 12:58 194阅读 0赞

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  
结果：

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