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

朴灿烈づ我的快乐病毒、 2022-12-09 02:52 154阅读 0赞

# 一、函数的分类 #

**函数**分为**单行函数、聚集函数**  
**单行函数**：每行都有一个返回值，并且一般使用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——修改保存—— / 执行