oracle基础 第七篇 查询sql 之单行函数

单行函数:

操作数据对象。

接受参数返回一个结果。

只对一行进行变换 。

每行返回一个结果 。

可以转换数据类型 。

可以嵌套 。

参数可以是一列或一个值。

 

分别有：

字符函数。

数值函数。

日期函数。

通用函数。

转换函数。

 

字符函数：

1.大小写控制函数:

这类函数改变字符的大小写。有：

LOWER(lower)（将输出结果全换成小写）、

UPPER(upper)（将输出结果全换成大写）、

INITCAP(initcap)（输出结果单词首字母大写）。

例如：

SELECT
	LOWER ('TeST JaVA'),
	UPPER ('TEST JAVA'),
	INITCAP ('TEST JAVA')
FROM
	dual;

输出结果：

应用：

SELECT
	last_name
FROM
	employees
WHERE
	LOWER (last_name) = 'king';

字符控制函数：

这类函数控制字符:

CONCAT(concat)(连接字符串)、

SUBSTR(substr)(截取字符串，注意：oracle的下标是从1开始算的)、

LENGHT(lenght)(输出字符串的长度)、

INSTR(instr)(判断某个字符在字符串当中首次出现的位置，如果不存在就返回0)、

LPAD(lpad)(按照位数输出一个字段的数据，不够在左边用 ' * ' ,' #' ...等或者空格补齐)、

RPAD(rpad)(按照位数输出一个字段的数据，不够在右边用 ' * ' ,' #' ...等或者空格补齐)、

TRIM(trim)(在字符串的首尾删除掉指定字符)、

REPLACE(replace)(用另外一个字符代替掉所有在字符串的指定字符)。

 

 

 

例如：

 SELECT
   CONCAT('Hello','World'),
   SUBSTR('Hello',2,3),
   LENGTH('Hello')
FROM
  dual;

输出结果：

 SELECT
  INSTR('hello world','o')
FROM
  dual;

输出结果：

 SELECT
  last_name,
  employee_id,
  LPAD(salary, 10, '#'),
  RPAD(salary, 10, '*')
FROM
  employees;

输出结果：

 SELECT
  TRIM('n' FROM 'nameNnameNamennn')
FROM
  dual;

输出结果：

数值函数：

ROUND(round): 四舍五入。

TRUNC(trunc):截断。

MOD(mod): 求余。

例如：

SELECT
	ROUND (456.456, 1),
	ROUND (456.456),
	ROUND (456.456, -1)
FROM
	dual;

输出结果：

 

SELECT
	TRUNC (456.456, 1),
	TRUNC (456.456),
	TRUNC (456.456, -1)
FROM
	dual;

输出结果：

 

SELECT
	mod(1500,400)
FROM
	dual;

输出结果：

日期：

SYSDATE （非函数）：返回系统日期和时间。

例如：

SELECT
	SYSDATE
FROM
	dual;

日期的数学运算：

在日期上加上或减去一个数字结果仍为日期。

两个日期相减返回日期之间相差的天数。 （两个日期不允许做加法运算，无意义 ）

可以用数字除24来向日期中加上或减去天数。

 

例如：

SELECT
	SYSDATE,
	SYSDATE + 1,
	SYSDATE - 1
FROM
	dual;

 

输出结果：

 

SELECT
	employee_id,
  last_name,
  ROUND(sysdate-hire_date) as "就职时间天数"
FROM
	employees;

输出结果：

日期函数：

函数	描述
MONTHS_BETWEEN(months_between)	两个日期相差的月数
ADD_MONTHS(next_months)	向指定日期中加上若干月数
NEXT_DAY(next_day)	指定日期的下一个星期 * 对应的日期
LAST_DAY(last_day)	本月的最后一天
ROUND(round)	日期四舍五入
TRUNC (trunc)	日期截断

 

 

 

 

 

 

 

 

例如：

SELECT
	employee_id,
	last_name,
	MONTHS_BETWEEN (sysdate, hire_date) AS "就职时间月数"
FROM
	employees;

输出结果：

 

SELECT
	SYSDATE,
	ADD_MONTHS (SYSDATE, 1),
	ADD_MONTHS (SYSDATE ,- 2),
	NEXT_DAY (SYSDATE, '星期一')
FROM
	employees;

输出结果：

 

SELECT
	last_name,
  hire_date
FROM
	employees
WHERE
   hire_date=last_day(hire_date);

输出结果：

SELECT
  ROUND(sysdate, 'month'),
  ROUND(sysdate, 'day'),
  TRUNC(sysdate, 'hh')
FROM 
  dual;

输出结果：

转换函数：

隐式数据类型转换：

源数据类型	目标数据类型
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

 

 

 

 

 

 

例如：

SELECT
 sysdate,
 sysdate+'2'
FROM 
  dual;

输出结果：

显式数据类型转换：

TO_CHAR函数对日期的转换：

格式: 必须包含在单引号中而且大小写敏感。

          可以包含任意的有效的日期格式。

           日期之间用逗号隔开。

例如：

SELECT
	employee_id,
	hire_date
FROM
	employees
WHERE
	TO_CHAR (hire_date, 'yyyy-mm-dd') = '1994-08-17';

输出结果：

 

SELECT
	TO_CHAR (
		1234567.89,
		'999,999,999,99'
	)
FROM
	dual;

输出结果：

 

SELECT
	TO_CHAR (
		1234567.89,
		'$999,999,999,99'
	)
FROM
	dual;

 

输出结果：

 

SELECT
	TO_NUMBER (
		'$001,234,567.89',
		'$999,999,999.99'
	)
FROM
	dual;

 

输出结果：

 

日期格式的元素：

YYYY	2004
YEAR	TWO THOUSAND AND FOUR
MM	02
MONTH	JULY
MON	JUL
DY	MON
DAY	MONDAY
DD	02

 

 

 

 

 

 

 

 

 

时间格式：

HH24:MI:SS AM

15:45:32 PM

 

 

使用双引号向日期中添加字符:

DD "of" MONTH

12 of OCTOBER

 

 

通用函数：

这些函数适用于任何数据类型，

同时也适用于空值：

NVL(nvl) (expr1, expr2) ：将空值转换成一个已知的值，

expr1为字段，其本身若为null，则用expr2代替，否则用expr1本身的数值。

 

可以使用的数据类型有日期、字符、数字。

例如：

SELECT
	employee_id,
	last_name,
	salary * 12 * (1 + NVL(commission_pct,0)) as "年收入"
FROM
	employees;

 

SELECT
  last_name,
  nvl(to_char(department_id ,'999999'),'没有部门'）
FROM
	employees;

NVL2(nvl2) (expr1, expr2, expr3)： expr1（字段）不为NULL，返回expr2；为NULL，返回expr3。

例如：

SELECT
  last_name,
  commission_pct,
  nvl2(commission_pct,commission_pct,0)
FROM
	employees;

 

NULLIF (nullif)(expr1, expr2) ：expr1和expr2相等返回null，不等返回expr1。

COALESCE (coalesce)(expr1, expr2, ..., exprn):COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。 如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。

 

条件表达式：

在 SQL 语句中使用IF-THEN-ELSE 逻辑

使用两种方法:

CASE (case)表达式 。

DECODE(decode) 函数。

例如:

查询部门号为 10, 20, 30 的员工信息,

若部门号为 10, 则打印其工资的 1.1 倍,

20 号部门, 则打印其工资的 1.2 倍,

30 号部门打印其工资的 1.3 倍数。

 

SELECT
	employee_id,
	last_name,
	CASE department_id
WHEN 10 THEN
	salary * 1.1
WHEN 20 THEN
	salary * 1.2
ELSE
	salary * 1.3
END AS "根据条件打印的薪酬"
FROM
	employees
WHERE
	department_id IN (10, 20, 30);

SELECT
	employee_id,
	last_name,
	DECODE (
		department_id,
		10,
		salary * 1.1,
		20,
		salary * 1.2,
		salary * 1.3
	) AS "根据条件打印出来的薪酬"
FROM
	employees
WHERE
	department_id IN (10, 20, 30);

 

 

 

参考来自尚硅谷