oracle的学习Ⅱ（单行函数、聚集函数、集合运算、decode函数和case、rownum）

一、函数的分类

函数分为单行函数、聚集函数
单行函数：每行都有一个返回值，并且一般使用dual（虚拟表）来当作来源
dual：虚拟表，它用来验证单行函数的功能，使多次返回的同一值只返回一次
聚集函数：一次使用只有一个返回值

二、单行函数

包含有字符函数、数值函数、日期函数、转换函数
例如：

使用转换函数提取日期中的年月日
to_char(param, format)			// param代表数据源，format代表需要转换成的格式
select to_char(sysdate, 'yyyy-mm-dd') from dual;	// 获得年月日，但只打印一次
select to_char(sysdate, 'yyyy') from EMP;		// 获得年份，EMP表有多少列则打印多少列

字符函数

length(string)：用于返回字符串的长度
concat(string1, string2)：返回连接好的字符串与||类似
upper(string)：返回字符串中所有字符的大写
lower(string)：返回字符串中所有字符的小写
ASCII(string | char)：返回string中首字母或char的ASCII码
chr(number)：返回ASCII码为number的字符
initcap(string)：用于将每个单词首字母大写，其余小写，若参数为null则结果仍为null
substr(string[, m, [n]])：用于截取字符串；m=0则从首字符开始，负数则从尾部开始；没有n或大于长度则取到字符串尾部
replace(string, search_string[, replace_string])：用于代替字符串中的子串，若无replace_string则用空格，只执行一次
lpad(string, length[, padding])：左补位函数，将string用padding补成length位，padding默认为空格
rpad(string, length[, padding])：右补位函数，将string用padding补成length位，padding默认为空格
ltrim(string1[, string2])：从左开始，截去子串（删掉子串）
rtrim(string1[, string2])：从右开始，截去子串（删掉子串）
translate(string, from_string, to_string)：将string中的from_string翻译位to_string，执行多次
instr(string1, string2[, n[, m]])：用于获取string2在string1的位置，从n位开始（位指定从1开始，m指子串第几次出现，默认为1），若结果为0则未找到目标字符串

数值函数

ceil(number)：返回大于或等于的最小整数
floor(number)：返回小于或等于的最大整数
mod(num1, num2)：取余，如mod(7, 3)得1
round(number, n)：对munber四舍五入，且保留n位小数

日期函数

sysdate：日期函数，返回当前日期，固有格式有：yyyy（完整年份），yeary（2位数年份），mm（2位数月份），dy（三个字母的星期），day（某天的星期全名），dd（某天日期）
month_between(param1, param2)：param1与param2之间的月份相差

转换函数

to_char(param, format)：把number或date转化为varchar
to_data(param, format)：把char转化为date
to_number(param, format)：把char转化为number

三、聚集函数

max(value)、min(value)、sum(value)、count(value)、avg(value)
注意：
①、所有聚集函数自动忽略空值
②、聚集函数可出现在select、having、order by子句中
③、在分组中，having是因group by的出现而出现的，分组的对象的数据越少越好，当查询中有单值列（或多个），又有聚集函数，则所有出翔的胆汁列必须作为分组元素

四、其他

空值函数：nvl(param1， param2)
decode(value, if1, then1, if2, then2……, else)：判断是否位等值，类似于if，但只能判断等值
case：

// 类似于if，可判断范围
case
when……then……
when……then……
else……
end
例如：
// 把SALESMAN的salary提升120%，把CLERK的salary提升130%，把ANALYST的salary提升140%，并输出
select
case
when JOB = 'SALESMAN' then SAL *1.2
when JOB = 'CLERK' then SAL *1.3
when JOB = 'ANALYST' then SAL * 1.4
else SAL * 1.4
end
from EMP;

集合运算：union all（所有的结果都并起来，不去重），union（所有的结果都并起来，去重）， intersect（交），minus（差）
特定记录查询（rownum）：rownum是个伪列，可给查询的表加行号，且是对结果的排序若结果一旦改变，则排序也改变，最后在where中rownum是从1开始的
例如：

select rownum, DEPT.* from dept where rownum = 2;	// 错误的，没有结果
select rownum, DEPT.* from dept where rownum <= 2;	// 正确的，有结果，注意格式，星号前要带有表名，且要有rownum伪列

快速改错：edit——修改保存—— / 执行