24 -Oracle学习（2）- 两种函数

1 单行函数

• 单行函数：用于操作数据对象,比如操作字符，数值，日期

函数格式：参数可以是一个列或一个值
function_name[(arg1,arg2,...)]

1.1 字符操作

lower  	转小写
upper 	转大写
Initcap 首写母大写

---

substr(a,b) 	从a中，第b位开始取

---

substr(a,b,c) 	从a中，第b位开始取,取后面c个字符

---

instr(a,b)	 b字符串在a 中的位置

---

length 字符数
lengthb 字节数

---

lpad 左填充
rpad 右填充

---

trim 	去掉前后指定的字符

---

replace 替换

1.2 浮点操作

round 四舍五入

---

trunc 截断

1.3 时间格式化

1.4 函数常用格式

1.5 时间操作

sysdate  当前时间

---

to_char 	格式化时间

---

时间相加减

---

months_between		两个日期相差的月数

---

add_months	当前日期加上n个月的时间

---

next_day 	下个星期几
应用：设置时间自动备份数据

---

to_char	也可以格式数字

1. 年和月的四舍五入
   
2. 2017-06-29 09:50:09今天是星期四
   
3. 查询员工薪水：两位小数 千位符 货币代码
   

1.6 空操作

nvl2(a,b,c) 	当a=null的时候，返回c；否则返回b

如果奖金是空，输出0，注意2前的是L的小写，不是1

---

nullif(a,b) 	当a=b的时候，返回null；否则返回a

---

coalesce 	从左到右找到第一个不为null的值

---

1.7 条件判断

单行条件判断可以使用case when then end或者decode

---

需求：涨工资，总裁1000 经理 800 其他400

decode

2 分组函数

2.1 组函数

SELECT [column,] group_function(column),...
FROM table
[WHERE condition]
[GROUP BY column]
[GROUP BY column]
Having...

2.2 常用组函数

Avg 求平均数

---

Count 求记录数

---

Max 求最大值

Min 求最小值

---

Sum 	示各和

SQL> select avg(comm) from emp; 奖金不为空的员工的平均奖金

SQL> select sum(comm)/count(*) from emp;所有员工的平均奖金

nvl(a,b)	如果a是 null,赋值为0

---

1. null值 组函数（多行函数）自动滤空，也就是空值也会计算
   

2.3 group by 分组函数

在select列表中，所有未包含的组函数中的列都应该包含在group by子句中

1. 分组时不能查询所有的字段
   

---

2. 求每个部门的平均工资【一个列的分组】
   

---

3. 按部门和职位求平均工资
   

---

4. 求平均工资大于2000的部门
   
   where和having的区别：where后面不能使用多行函数
   查询10号部门的平均工资
   

---

5. rollup groupby语句增加【报表汇总】
   

---

break on deptno skip 2 		格式化输出 skip2表示空两行
break on null

3 多表查询

3.1 连接类型

等值连接

不等值连接

工资等级表

查询员工信息： 员工号 姓名 月薪 工资级别【员工表和级别表是没有外键关联的】

---

外连接

1. 按部门统计员工人数：部门号 部门名称 人数
   
   
   对于某些不成立的记录，任然希望包含在最后的结果中
   

    左外连接
   

   当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含写法:where e.deptno=d.deptno(+)

---

右外连接：当`where e.deptno=d.deptno`不成立的时候，等号右边的表任然被包含
写法: `where e.deptno(+)=d.deptno`

---

自连接: 通过表的别名，将同一张表视为多张表

查询员工信息：员工姓名 老板(上级)姓名

---

笛卡尔积总数

3.2 层次查询

from 表
connect by prior 条件
start with 开始条件

---

JONES 是 7566

---

从 KING 开始