Oracle数据库中通用的函数实例详解(2)-木庄网络博客

范例：查询所有的雇员将将年月日分开，此时可以使用 TO_CHAR 函数来拆分

拆分时需要使用通配符
年：y, 年是四位使用 yyyy
月：m, 月是两位使用 mm
日：d, 日是两位使用 dd

查询所有的雇员将将年月日分开

--查询所有的雇员将将年月日分开
select empno,ename,
       to_char(hiredate,'yyyy') as 年,
       to_char(hiredate,'mm') as 月,
       to_char(hiredate,'dd') as 日
       from emp;

日期将日期格式改为yyyy-mm-dd字符串格式

-- 初始格式
select * from emp;
--日期将日期格式改为yyyy-mm-dd字符串格式
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

在结果中10以下的月前面补了0，可以使用fm去掉前置0

--在结果中10以下的月前面补了0，可以使用fm去掉前置0
select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;

TO_DATE:日期转换函数

TO_DATE 可以把字符串的数据转换成日期类型

--TO_DATE 可以把字符串的数据转换成日期类型
select to_date('2022-03-10','yyyy/mm/dd:ss')as 当前日期 from dual;

（4）通用函数

空值处理 nvl

范例：查询所有的雇员的年薪

--查询所有的雇员的年薪 comm年终奖
select ename,sal*12+comm from emp;

我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null 和任何数值计算都是

null，这时我们可以使用 nvl 来处理

--查询所有的雇员的年薪 comm年终奖
select ename,nvl(comm,0) 年终奖 ,sal*12+nvl(comm,0)年薪 from emp;

Decode 函数

--该函数类似 if....else if...esle
 
--语法：
 DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression:列名或表达式
 
1. Search1，search2...:用于比较的条件
2. Result1, result2...:返回值
3. 如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值

--decode函数
--1. 我是1
select decode(1,1,'我是1',2,'我是2','我是无名') from dual;
--2. 我是2
select decode(2,1,'我是1',2,'我是2','我是无名') from dual;
--3. 我是无名
select decode(3,1,'我是1',2,'我是2','我是无名') from dual;

范例：查询出所有雇员的职位的中文名

select ename,decode(job,
                   'clerk','业务员',
                   'SALESMAN','销售',
                   'PRESIDENT','总裁',
                   'MANAGER','经理',
                   'NALYST','分析员',
                   '员工' 
                   ) from emp ;

case when

CASE expr WHEN comparison_expr1 THEN return_expr1
 [WHEN comparison_expr2 THEN return_expr2
 WHEN comparison_exprn THEN return_exprn 
 ELSE else_expr]
END

范例：查询出所有雇员的职位的中文名

--范例：Case when 查询出所有雇员的职位的中文名
select t.empno,t.ename,
       case
          when t.job='clerk' then '业务员'
          when t.job='SALESMAN' then '销售'
          when t.job='PRESIDENT' then '总裁'
          when t.job='MANAGER' then '经理'
          when t.job='NALYST' then '分析员'
          else '员工'
          end
          from emp t;

三、多行函数（聚合函数）

1、统计记录数

范例：查询出所有员工的记录数

-- 范例：查询出所有员工的记录数
select count(*) from emp;

不建议使用 count(*)，可以使用一个具体的列以免影响性能。

--不建议使用 count(*)，可以使用一个具体的列以免影响性能。
select count(ename) from emp;

2、最小值查询 min()

范例：查询出来员工最低工资

--范例：查询出来员工最低工资
select min(sal) from emp;

3、最大值查询 max()

范例：查询出员工的最高工资

--范例：查询出员工的最高工资
select max(sal) from emp;

4、平均值查询 avg()

范例：查询出员工的平均工资

--范例：查询出员工的平均工资
select avg(sal) from emp;

5、求和函数

范例：查询出 20 号部门的员工的工资总和

--范例：查询出 20 号部门的员工的工资总和
select sum(sal) from emp where deptno=20;

四、分组统计

分组统计需要使用 GROUP BY 来分组

--语法：
SELECT * |列名 FROM 表名 
{WEHRE 查询条件} 
{GROUP BY 分组字段}
 ORDER BY 
 列 名 1 ASC|DESC，列名 2...ASC|DESC

范例：查询每个部门的人数

-- 范例：查询每个部门的人数
select deptno,count(ename) from emp group by deptno;

范例：查询出每个部门的平均工资

-- 范例：查询出每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

如果我们想查询出来部门编号，和部门下的人数

-- 如果我们想查询出来部门编号，和部门下的人数
select deptno,count(ename) from emp;

我们发现报了一个 ORA-00937 的错误

注意：

1.如果使用分组函数，SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来，不能有其

他字段。

2. 如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值

范例：按部门分组，查询出部门名称和部门的员工数量

-- 范例：按部门分组，查询出部门名称和部门的员工数量
select d.deptno,d.dname,count(e.ename) 
from emp e,dept d
where e.deptno=d.deptno 
group by d.deptno,d.dname

范例：查询出部门人数大于 5 人的部门

-- 范例：查询出部门人数大于 5 人的部门
--(分析：需要给 count(ename)加条件，此时在本查询中不能使用 where，可以使用 HAVING)
select d.deptno,d.dname,count(e.ename) 
 from emp e,dept d
 where e.deptno=d.deptno 
 group by d.deptno,d.dname 
 having  count(e.ename)>5 ;

分析：需要给 count(ename)加条件，此时在本查询中不能使用 where，可以使用 HAVING

范例：查询出部门平均工资大于 2000 的部门

-- 范例：查询出部门平均工资大于 2000 的部门
 
select d.deptno,d.dname,avg(e.sal) 
from dept d ,emp e
 where d.deptno=e.deptno 
 group by d.deptno,d.dname
  having avg(e.sal)>2000;