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 | 月份的数字天 |
使用双引号向日期中添加数字:
DD “of” Month | 12 of october |
---|
使用TO_CHAR函数对数字进行转换
元素 | 结果 |
---|
9 | 代表一个数字 |
0 | 强制显示0 |
$ | 放置一个浮动的美元符号 |
L | 采用浮动本地货币符号 |
. | 打印小数点 |
, | 打印一个逗号作为前卫标识符 |
使用TO_NUMBER和TO_DATE函数
- 使用TO_NUMBER 函数将字符转换为数字格式:``
to_number(char[, 'format_model'])
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) | 返回表达式列表中的第一个非空表达式 |
这里表示判断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%进行发放
其他职位的员工年薪按照正常的进行发放