SQL函数

SQL函数

日期：2019.05.22
作者：chj

单行函数

单行SQL函数

• 操作数据对象
• 接受函数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是列、值或表达式

字符函数

大小写转换函数：

函数	结果
lower（‘HelloWorld’）	helloworld
upper（‘HelloWorld’）	HELLOWORLD
initcap（‘HElloWorld’）	HelloWorld

字符控制函数：

函数	结果	含义
concat（‘Hello’，‘world’）	HelloWorld	将值连接在一起（只适用于两个参数）
substr（‘Helloworld’，1,5）	Hello	取一个确定长度的字符串，“1”表示从第一个字符开始，“5”表示后面5个字符
length（‘Helloworld’）	10	将字符串的长度显示为数值
instr（‘HelloWorld’，‘W’）	6	查找指定字符的位置
lpad（‘15684’）,10，‘*’	*****15684	使用‘*’在数值左填充，满十位
rpad（‘15684’）,10，‘*’	15684*****	使用‘*’在数值右填充，满十位
replace(‘JACK and JUE’,’J’,’BL’)	BLACK and BLUE	查找字符，并进⾏替换字符
TRIM(‘x’ FROM ‘xxxHelloWorldxxx’)	HelloWorld	从字符串中修剪开头或结尾的字符

数字函数

• ROUND：四舍五入到制定的十进制值
• TRUNC : 截断到制定十进制值
• MOD : 返回余数

函数	结果
round（45.926,2）	45.93
trunc（45.926,2）	45.92
mod（1600,300）	100

使用日期

修改当前session日期格式

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss day'; 
select sysdate from dual;

日期的算术运算

• 在日期上加上或减去一个数字结果仍为日期
  date+number 、date - number
• 两个日期相减返回日期之间的相差天数
  date - date
• 可以用小时数除以24，可以加小时到日期上
  date+number/24

日期操作函数

函数	结果
months_between	两个日期相差的月数
add_months	向指定日期中加上若干月数
next_day	指定日期的下一个日期
last_day	指定本月的最后一天
round	日期四舍五入
trunc	日期截断

使用方法：

函数	结果
months_between（‘01-SEP-95’，‘11-JAN-94’）	19.6774194
add_months（‘31-JAN-96’,1）	‘29-FEB-96’
next_day（‘01-SEP-96’，‘Friday’）	‘08-SEP-95’
last_day（‘01-FEB-95’）	‘28-FEB-95’

ROUND和TRUNC的使用方法：
假设：sysdate=‘25-JUL-03’：

函数	结果
round（sysdate，‘month’）	01-AUG-03
round（sysdate，‘year’）	01-JAN-04
trunc（sysdate，‘month’）	01-JUL-03
trunc（sysdate，‘year‘）	01-JAN-03

转换函数

• SQL中不同类型的转换函数
• 使用to_char，to_number, 和 to_date转换函数
• 在select语句中应用条件表达式

转换函数分为：隐式数据类型转换、显示数据类型转换
要尽量避免隐式数据类型转换（效率低）

显示数据类型转换

日期格式的组成

使用TO_CHAR函数对日期的转换

元素	结果
YYYY	完整的年份
YEAR	年（英文）
MM	双位数字月份
Month	完整的月份名称
Mon	月份的三个字母缩写
DY	星期三个字母的缩写
DAY	完整的星期名称
DD	月份的数字天

• 时间格式：

HH24:MI:SS AM	15：45:32 PM

使用双引号向日期中添加数字：

DD “of” Month	12 of october

• 日期在月份中的位置

ddspth	fourteenth

使用TO_CHAR函数对数字进行转换

元素	结果
9	代表一个数字
0	强制显示0
$	放置一个浮动的美元符号
L	采用浮动本地货币符号
.	打印小数点
,	打印一个逗号作为前卫标识符

使用TO_NUMBER和TO_DATE函数

• 使用TO_NUMBER 函数将字符转换为数字格式：``

to_number(char[, 'format_model'])

• 使用TO_DATE 函数将字符转换为数字格式：

to_date(char[, 'format_model'])

通用函数

函数	含义
nvl（expr1，expr2）	转换null值为实际的值
nvl2（expr1，expr2，expr3）	如果expr1不是null，那么转换为expr2，如果expr1是空值，则转换为expr3
nullif（expr1，expr2）	比较两个表达式，如果相等，则返回null，如果不相等，返回第一个表达式
coalesce（expr1，expr2，…，exprn）	返回表达式列表中的第一个非空表达式

• nvl函数的用方法：

这里表示判断comm是否为空值，若为空值则赋值为0，若不是空值则输出原有值

• coalesce函数的用法：
  部门编号拼接员工姓名，第二例显示入职的月份（小写），第三列判断奖金列，如果有奖金，那么输出月薪加奖金，如果没有奖金，那么仅输出月薪。并且使用银行显示钱的那种格式。第四列，判断是否有奖金，没有奖金的列出"no comm!!", 如果有奖金那么直接输出。并且并且使用银行显示钱的那种格式。
  

COALESCE函数

CASE表达式

• 查询员工姓名、职位、薪资和奖金，
  当职位为ANALYST的员工年薪按照150%进行发放，
  如果职位是salesman的员工年薪按照140%进行发放，
  如果职位是MANAGER的员工年薪按照130%进行发放，
  其他职位的员工年薪按照正常的进行发放。
  
• 查询员工姓名，职位，薪资和奖金
  当员工薪资小于1500的，则提示 工资太低
  当员工薪资在1500到4000区间，则提示 good
  当员工薪资大于4000的，则提示 very good
  

DECODE函数

• 查询员工姓名，职位，薪资和奖金
  当职位为 ANALYST 和 MANAGER 的员工年薪按照150%进行发放
  如果职位是salesman的员工年薪按照130%进行发放
  其他职位的员工年薪按照正常的进行发放