SQL函数
日期：2019.05.22 作者：chj
单行函数
单行SQL函数
操作数据对象接受函数返回一个结果只对一行进行变换每行返回一个结果可以转换数据类型可以嵌套参数可以是列、值或表达式
字符函数
大小写转换函数：
函数结果
lower（‘HelloWorld’）helloworldupper（‘HelloWorld’）HELLOWORLDinitcap（‘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.93trunc（45.926,2）45.92mod（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.6774194add_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-03round（sysdate，‘year’）01-JAN-04trunc（sysdate，‘month’）01-JUL-03trunc（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 AM15：45:32 PM
使用双引号向日期中添加数字：
DD “of” Month12 of october
日期在月份中的位置
ddspthfourteenth
使用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是空值，则转换为expr3nullif（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%进行发放 其他职位的员工年薪按照正常的进行发放
