Oracle函数学习笔记分享

2020年1月22日19:22:17
评论

1. Oracle单行函数

1.1 Oracle字符函数

LOWER 使字符串小写;

select LOWER('HeLp') from dual --->help

UPPER 使字符串大写;

select UPPER('HeLp') from dual --->HELP

INITCAP 使字符串的第一个字母大写,其它为小写

select INITCAP('hELp') from dual --->Help

LENGTH 返回表达式中的字符串长度

select LENGTH('hELP') from dual ---->4

CONCAT 将值连接到一起(,对于此函数,只能使用2个参数)

select CONCAT('Hello', 'World') from dual --->HelloWorld

SUBSTR 抽取确定长度的字符串

select SUBSTR('HelloWorld',1,5) from dual ---->Hello

INSTR 查找指定字符的数字位置

select INSTR('HelloWorld', 'W') from dual --->6

LPAD 按右对齐填充字符串

select LPAD(salary,10,'*') from dual --->*****24000

RPAD 按左对齐填充字符串

select RPAD(salary, 10, '*') from dual --->24000*****

TRIM(leading|trailing|both,trim_character FROM trim_source) 从字符串中截去头部或者尾部的字符(或者头尾都截掉)(如果trim_character或trim_source是一个字符型文字值,则必须将它包含在单引号之内。)

select TRIM('H' FROM 'HelloWorld') from dual --->elloWorld

REPLACE(text,search_string,replacement_string) 搜索字符串的文本表达式,如果找到,用指定的替代字符串替换它。

select REPLACE('HelloWorld','Hello','hi') from dual --->hiWorld

1.2数字函数

ROUND 将值四舍五入到指定的位数

ROUND(45.926, 2) ---->45.93

TRUNC 将值截断到指定的小数位(不舍入)

TRUNC(45.926, 2) ---->45.92

MOD 返回除法运算的余数

MOD(1600, 300) --->100

1.3 日期函数

在oracle中,日期是以数字的形式存储的,故可以直接对日期类型的数据进行+-*/等运算。

MONTHS_BETWEEN 两个日期之间的月数,结果以月为单位

MONTHS_BETWEEN(date1, date2)

ADD_MONTHS 将日历月份添加到日期中

ADD_MONTHS(date, n) n为月数

NEXT_DAY 指定日期的下一个月

NEXT_DAY(date, 'char') char可以是星期,月数

LAST_DAY 月份的最后一天

LAST_DAY(date)

ROUND 四舍五入日期

ROUND(date[, 'fmt'])

设置日期格式:alter session set nls_date_format='yyyy-mm-dd:hh24:mi:ss'

TRUNC 截断日期

TRUNC(date[, 'fmt'])

1.4 转换函数

TO_CHAR(number|date,'[fmt]',[nlsparams])将数字和日期值转换为格式样式为fmt的varchar2字符串。

nlsparams参数指定返回的月份名称、日期名称以及缩写所用的语言。

TO_NUMBER(char,'[fmt]',[nlsparams])将包含数字的字符串转换为格式样式为fmt指定的格式表示的数字。

nlsparams参数与TO_CHAR()相同,是进行数字转换。

TO_DATE(char,'[fmt]',[nlsparams])将代表日期的字符串按照指定的fmt转换为日期值。如果省略了fmt,则格式为DD-MON-YY。

nslparams参数在此函数中的用途是进行日期转换。

1.5嵌套函数

NVL (expr1, expr2) 将空值转换为实际的值。

NVL2 (expr1, expr2, expr3) 如果expr1为非空,返回expr2。如果expr1为空值,将返回expr3。expr1可以是任何数据类型。

NULLIF (expr1, expr2) 比较2个值,如相等,返回空值,否则返回第一个表达式。

COALESCE (expr1, expr2, ..., exprn) 返回表达式中第一个非空值。

1.6 条件表达式

CASE表达式(符合ANSISQL)

通过执行IF-THEN-ELSE语句的任务,可以简化条件查询

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

DECODE函数(Oracle专用语法)

通过执行IF-THEN-ELSE语句的任务,可以简化条件查询

DECODE(col|expression, search1, result1

[, search2, result2,...,]

[, default])

2、分组函数

使用分组函数的准则:

DISTINCT 使函数只考虑非重复值;ALL使之考虑包括重复值在内的所有值。默认值为ALL,因此它不需

要专门指定。

带有expr参数的函数的数据类型可以是CHAR、VARCHAR2、NUMBER或者DATE。

所有分组函数都忽略空值。要用一个值代替空值,可以使用NVL、NVL2或者COALESCE函数。

在使用GROUP BY 字句时,Oracle服务器隐式地按照升序对结构集进行排序。要改写此默认顺序,可以

在ORDER BY 字句中使用DESC。

2.1 AVG([DISTINCT|ALL]n) n的平均值,忽略空值

2.2 COUNT({*|[DISTINCT|ALL]expr}) 行数,其中expr用来判断非空值(使用*计算所有选定行,包括重

复行和带有空值的行)

2.3 MAX([DISTINCT|ALL]expr) expr的最大值,忽略空值

2.4 MIN([DISTINCT|ALL]expr) expr的最小值,忽略空值

注:可以对任何数据类型使用MIN 和 MAX 函数

2.5 STDDEV([DISTINCT|ALL]x) n的标准偏差,忽略空值

2.6 SUM([DISTINCT|ALL]n) n的总计值,忽略空值

2.7 VARIANCE([DISTINCT|ALL]x) n的方差,忽略空值

例1:

SELECT AVG(salary), MAX(salary),

MIN(salary), SUM(salary)

FROM employees

WHERE job_id LIKE '%REP%';

例2:

SELECT MIN(hire_date), MAX(hire_date)

FROM employees;

例3:

SELECT COUNT(*)

FROM employees

WHERE department_id = 50;

2.8 GROUP BY 字句

语法:GROUP BY group_by_expression group_by_expression--->指定列,这些列的值是对行进行分组的基础。

作用:通过使用GROUP BY字句将表中的行分成更小的组。也可以对多个列使用GROUP BY子句。

例4:

SELECT department_id dept_id, job_id, SUM(salary)

FROM employees

GROUP BY department_id, job_id ;

注意:

A、 SELECT 列表中不是聚合函数的任何列或表达式都必须在GROUP BY子句中。

B、 不能使用 WHERE 子句来限制组;

C、 可以使用HAVING子句来限制组;

D、 不能在 WHERE 子句中使用分组函数。

2.9 嵌套分组函数

分组函数可以嵌套两层。

例5:

SELECT MAX(AVG(salary))

FROM employees

GROUP BY department_id;

企鹅博客
  • 本文由 发表于 2020年1月22日19:22:17
  • 转载请务必保留本文链接:https://www.qieseo.com/145192.html

发表评论