Oracle总结二

傷城~ 2022-10-09 00:46 12阅读 0赞

Oracle的强大之处之一，就在于有较多的函数方便进行数据分析操作，Oracle中函数分成了两个大类，一类是单行函数，另一类是多行函数，而每一类函数下面又分为了较为多类型的函数。

### 单行函数 ###

（注释：输入一个参数，输出一个结果）

#### 字符串函数： ####

对字符串操作

<table> 
 <thead> 
  <tr> 
   <th>演示实列</th> 
   <th>操作结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT(‘Hello’, ‘World’)</td> 
   <td>HelloWorld</td> 
  </tr> 
  <tr> 
   <td>SUBSTR(‘HelloWorld’,1,5)</td> 
   <td>Hello</td> 
  </tr> 
  <tr> 
   <td>LENGTH(‘HelloWorld’)</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>INSTR(‘HelloWorld’, ‘W’)</td> 
   <td>6</td> 
  </tr> 
  <tr> 
   <td>LPAD(10000,10,’*’)</td> 
   <td>*****24000</td> 
  </tr> 
  <tr> 
   <td>RPAD(10000, 10, ‘*’)</td> 
   <td>24000*****</td> 
  </tr> 
  <tr> 
   <td>TRIM(‘a’ FROM ‘aHelloWorld’); 去除首个字符</td> 
   <td>elloWorld</td> 
  </tr> 
  <tr> 
   <td>TRIM(’ HelloWorld’)</td> 
   <td>HelloWorld</td> 
  </tr> 
  <tr> 
   <td>TRIM(‘Hello World’)</td> 
   <td>Hello World</td> 
  </tr> 
 </tbody> 
</table>

#### 数字函数： ####

对数字进行计算，返回一个数字

<table> 
 <thead> 
  <tr> 
   <th>演示实列</th> 
   <th>操作结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>round(2.666) – round () 四舍五入</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>trunc(2.666,1); – trunc() 截取小数点后位置</td> 
   <td>2.6</td> 
  </tr> 
  <tr> 
   <td>mod(3,10); – mod() 模运算符（求余）</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

#### 日期函数： ####

对日期和时间进行处理

<table> 
 <thead> 
  <tr> 
   <th>演示实列</th> 
   <th>操作结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>MONTHS_BETWEEN(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),TO_DATE(‘2021-04-10’,‘yyyy-mm-dd’))</td> 
   <td>1.16129032</td> 
  </tr> 
  <tr> 
   <td>ADD_MONTHS(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),2)</td> 
   <td>2021/7/15</td> 
  </tr> 
  <tr> 
   <td>NEXT_DAY(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),‘FRIDAY’)</td> 
   <td>2021/5/21</td> 
  </tr> 
  <tr> 
   <td>NEXT_DAY(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),1)</td> 
   <td>2021/5/16</td> 
  </tr> 
  <tr> 
   <td>LAST_DAY(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’))</td> 
   <td>2021/5/31</td> 
  </tr> 
  <tr> 
   <td>ROUND(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),‘MONTH’)</td> 
   <td>2021/5/1</td> 
  </tr> 
  <tr> 
   <td>ROUND(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),‘YEAR’)</td> 
   <td>2021/1/1</td> 
  </tr> 
  <tr> 
   <td>TRUNC(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’), ‘MONTH’)</td> 
   <td>2021/5/1</td> 
  </tr> 
  <tr> 
   <td>TRUNC(TO_DATE(‘2021-05-15’,‘yyyy-mm-dd’),‘YEAR’)</td> 
   <td>2021/1/1</td> 
  </tr> 
 </tbody> 
</table>

#### 转换函数： ####

可以将一种数据类型转换为另外一种数据类型

*  大小写，首字母转换

<table> 
 <thead> 
  <tr> 
   <th>演示实列</th> 
   <th>操作结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LOWER(‘SQL Course’)</td> 
   <td>sql course</td> 
  </tr> 
  <tr> 
   <td>UPPER(‘SQL Course’)</td> 
   <td>SQL COURSE</td> 
  </tr> 
  <tr> 
   <td>INITCAP(‘SQL course’)</td> 
   <td>Sql Course</td> 
  </tr> 
 </tbody> 
</table>

*  TO\_CHAR(date, ‘format\_model’) ;函数：日期到字符串的转换,format\_model的可选参数如下

<table> 
 <thead> 
  <tr> 
   <th><strong>日期<strong><strong>格式</strong></strong>化****元素</strong></th> 
   <th><strong>意义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>YYYY</td> 
   <td>4位数字表示的年份</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>英文描述的年份</td> 
  </tr> 
  <tr> 
   <td>MM</td> 
   <td>2位数字表示的月份</td> 
  </tr> 
  <tr> 
   <td>MONTH</td> 
   <td>英文描述的月份</td> 
  </tr> 
  <tr> 
   <td>MON</td> 
   <td>三个字母的英文描述月份简称</td> 
  </tr> 
  <tr> 
   <td>DD</td> 
   <td>2位数字表示的日期</td> 
  </tr> 
  <tr> 
   <td>DAY</td> 
   <td>英文描述的星期几</td> 
  </tr> 
  <tr> 
   <td>DY</td> 
   <td>三个字母的英文描述的星期几简称</td> 
  </tr> 
  <tr> 
   <td>HH24:MI:SS AM</td> 
   <td>时分秒的格式化</td> 
  </tr> 
  <tr> 
   <td>DDspth</td> 
   <td>英文描述的月中第几天</td> 
  </tr> 
  <tr> 
   <td>fm</td> 
   <td>格式化关键字，可选</td> 
  </tr> 
 </tbody> 
</table>

例句：

SELECT last_name, TO_CHAR(hire_date, 'fmDD "of" Month YYYY') AS  HIREDATE
    FROM employees;

*  TO\_CHAR(number, ‘format\_model’) ; 函数：数字到字符串的转换
 *  TO\_NUMBER();函数：字符串到数字的转换
 *  TO\_DATE(char\[, ‘format\_model’\]);函数：字符串到日期的转换

<table> 
 <thead> 
  <tr> 
   <th><strong>数字<strong><strong>格式</strong></strong>化****元素</strong></th> 
   <th><strong>意义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>9</td> 
   <td>表示一个数字</td> 
  </tr> 
  <tr> 
   <td>0</td> 
   <td>强制显示0</td> 
  </tr> 
  <tr> 
   <td>$</td> 
   <td>放一个美元占位符</td> 
  </tr> 
  <tr> 
   <td>L</td> 
   <td>使用浮点本地币种符号</td> 
  </tr> 
  <tr> 
   <td>.</td> 
   <td>显示一个小数点占位符</td> 
  </tr> 
  <tr> 
   <td>,</td> 
   <td>显示一个千分位占位符</td> 
  </tr> 
 </tbody> 
</table>

例句：

SELECT TO_CHAR(salary, 'L99,999.00') SALARY FROM employees
    WHERE last_name = 'Ernst';

例句：

select TO_NUMBER('4456') from dual;
    select TO_NUMBER('$4,456','$9,999' )from dual;
    select TO_NUMBER('$4,456,455.000','$9,999,999,999,999.999' )from dual;

例句：

select to_date ('22-FEB-11') from dual;
    select to_date('2011-2-22','YYYY-MM-DD') from dual;
    select to_date('2-22-2011','MM-DD-YYYY') from dual;
    select to_date('2011-FEB-22','YYYY-MON-DD') from dual;

#### 其他函数 ####

<table> 
 <thead> 
  <tr> 
   <th>演示实例</th> 
   <th>操作结果</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NVL (expr1, expr2)</td> 
   <td>如果expr1为空，这返回expr2</td> 
  </tr> 
  <tr> 
   <td>NVL2 (expr1, expr2, expr3)</td> 
   <td>如果expr1为空，这返回expr3（第2个结果）否则返回expr2</td> 
  </tr> 
  <tr> 
   <td>NULLIF (expr1, expr2)</td> 
   <td>如果expr1和expr2相等，则返回空</td> 
  </tr> 
  <tr> 
   <td>COALESCE (expr1, expr2, …, exprn)</td> 
   <td>如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到 一个不为NULL的值 或者如果全部为NULL，也只能返回NULL 了</td> 
  </tr> 
 </tbody> 
</table>

例子：

SELECT last_name, salary, NVL(commission_pct, 0),  (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL  FROM employees;

### 多行函数 ###

（输入多个参数，或者是内部扫描多次，输出一个结果）

#### 数量 count ####

count(0)，--用来查询表中有多少条记录
     count（字段名），--查询指定字段的数量
     count(distinct 字段名),--可以先去除重在计算某个字段的值。

#### 总和 sum ####

sum(sal) --获取某个字段的总和

#### 平均值 avg ####

avg(字段) --获取某个字段的平均值

#### 最大最小值 max/min ####

max(字段) --查询最大值
    min(字段) --查询最小值