Oracle的系统内置函数、数据分组

系统内置函数

单行函数

字符函数

字符函数输入参数为字符类型，其返回值是字符类型或数字类型

1. Upper（）：字符串转换成大写格式

2. Lower（）：字符串转换为小写格式

3. Initcap（）：字符串中每个单词的首字母大写，其他字符小写

4. Concat（）：连接字符串

5. Substr（）：截取字符

substr('Oracle',1,3)  结果:Ora

6. Length（）：字符长度
7. Lpad（）/ Rpad（）：填充字符右到左 / 填充字符左到右

Lpad('字符串'，m，‘n’)
Rpad('字符串'，m，‘n’)
//m总字节，n是填充的字符

8. Replace（）：换字符

Replace(‘字符串’，‘字符1’，‘字符2’)
//字符串中的字符1换成字符2

数值函数

1. Round(n,m)四舍五入
2. Trunc(n,m)截取函数
3. Mod(n,m)求余

日期和时间函数

1. Sysdate：系统时间

Select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from daul;

2. Months_between（）：时间之差（月数）

Select months_between（'现在系统时间'，'指定开始时间'） from 表名;

3. Add_months（）：返回指定时间之后的指定时间

Select Add_months（'指定时间'，'任意整数'） from 表名;

4. Next_day（）：返回指定时间之后的第一个工作日

Select Next_day（'指定时间'，'任意整数'） from 表名;

5. Last_day（）：当前月份的最后一天

Select  列名  from 表名 where 列名 = Last_day（'列名');

6. Round（）：返回时间值的四舍五入

Round(日期,m)，m值是决定四舍五入的方式
year以7月1日为分界点，month以16日为分界点

设置系统时间‘12日-7月-20年’
Round('sysdate','year')，结果是：1日-1月-21年
Round('sysdate','month')，结果是：1日-7月-20年

转换函数

该函数用于把一种数据类型转换成另外一种数据类型。
Oracle可以隐式、自动的进行数据类型转换

1. To_char()：日期类型转换成字符类型

to_char('列名'，'转换格式')
Select to_char('列名'，'YYYY-MM-DD') from 表名; 
Select to_char('列名'，'YYYY"年"MM"月"DD"日"') from 表名; 

2. To_char()：数值类型转换成字符类型

to_char('指定数值'，'数字格式')
Select to_char('指定数值'，'L000，000') from 表名; //补全0
Select to_char('指定数值'，'L999，999') from 表名; //不补全0

3. To_date()：字符类型转换成日期类型
4. To_dnumber()：字符类型转换成数值类型

通用函数（处理非空值）

Oracle9i之前，处理null只能用函数NVL，但从Oracle9i之后，NVL或者NVL2 都可以处理NVL

1. NVL（）

Nvl(数值1，数值2)，处理Null
如果数值1是Null，返回数值2；
如果数值1不是Null，返回数值1；

2. NVL2（）

Nvl(数值1，数值2，数值3)，处理Null
如果数值1是Null，返回数值2；
如果数值1不是Null，返回数值3；

3. Nullif（）

Nullif（数值1，数值2），比较
如果两者相等，返回null
如果两者不相等，返回数值1

4. Coalesce（）

Coalesce（数值1，数值2，数值3，N）
返回第一个 不为空的数值

条件表达式

都可以用if_then_else逻辑判断

1. Case

Case 列名
   when 指定值1 then 返回值1
   when 指定值2 then 返回值2
   when 指定值3 then 返回值3
   else 返回值4
end

2. Decode（）

Docode(列名，'列值1'，'返回值1'，'列值2'，'返回值2'，'列值3'，'返回值3'，'返回值4')

分组函数

1. Avg（） 求平均数
2. Sum（）求和
3. Min（）求最小值
4. Max（）求最大值
5. Count（）统计、计数
6. Wm_concat（） 行转换列

数据分组

1. Group by

1. 在Select语句中未包含在函数中的列，必须包含在group by子句里，但包含在group by子句中列，不必包含在select语句中
2. 分组函数的嵌套函数必须加上Group by子句

• 详解

表1，表名为test：

执行如下SQL语句：

SELECT name FROM test
GROUP BY name

查询出来会变成就是这样，表2：

为了能够更好的理解 “group by” 多个列“和 ”聚合函数“ 的应用，我把中间的一层变化说明了一下
表1到表2的变化中，相成一张表3

接下来就要针对虚拟表3执行Select语句了：

1. 如果执行select *的话，那么返回的结果应该是虚拟表3，可是id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的，所以执行select * 语句就报错
2. 我们再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢，因为我们就是用name列来group by分组的。
3. 那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id)，sum(number)，而每个聚合函数的输入就是每一个多数据的单元格。

2. Having

1. Where子句中 不允许使用分组函数，Having可以
2. Select->From->Where->Group by ->Having->order by