mysql单行函数

#字符串函数
SELECT ASCII('Abc')
FROM DUAL;
#CONCAT(s1,s2,......,sn)连接s1,s2,......,sn为一个字符串
SELECT CONCAT(e.last_name,'worked fro',mgr.last_name)
FROM employees e JOIN employees mgr
ON e.`employee_id` = mgr.`employee_id`;
SELECT CONCAT_WS('','hello','world','hello','beijing')
FROM DUAL;
#insert 插入替换 SQL中的数组索引从1开始 区别于java的0开始
SELECT INSERT('helloworld',2,3,'aaaa')
FROM DUAL;
#replace 替换函数
SELECT REPLACE('hello','ll','mmm')
FROM DUAL;
#lower upper 将字母全部转化为 小写 vs 大写
SELECT last_name,salary
FROM employees
WHERE LOWER (last_name) = 'king';
#lapd rpad
SELECT employee_id,last_name,LPAD(salary,10,'')
FROM employees;
# trim 去除空格
SELECT LENGTH(TRIM(' h e ll o '))
FROM DUAL;

###时间和时间函数
#3.1 获取时间，日期
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),
SYSDATE(),UTC_DATE(),UTC_TIME()
FROM DUAL;
# 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),
#from_unixtime()
FROM DUAL;
#获取月份、星期、星期数、天数等函数
# 日期的操作函数
#流程控制函数
SELECT last_name,salary,IF(salary >=6000,'高工资','低工资')"details"
FROM employees;
#练习：测试员工的年薪为多少(commission_pct有可能为null)
SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary *12 *(1+IF(commission_pct IS NOT NULL,commission_pct,0)) "auual_sal"
FROM employees;

# case when then
SELECT last_name salary,CASE WHEN salary >=15000 THEN '白骨精'
WHEN salary >=1000 THEN '潜力股'
WHEN salary >=8000 THEN '小屌丝'
ELSE '草根' END AS "details"
FROM employees;
/**
查询部门号为 10 ，20,30的员工信息
若部门号为10 则打印起工资的1.1倍
20 1.2
30 1.3
*/
SELECT last_name ,salary,CASE department_id WHEN 10 THEN salary *1.1
WHEN 20 THEN salary *1.2
WHEN 30 THEN salary*1.3
ELSE salary *1.4 END AS "datails"
FROM employees;
#6. 加密解密函数：
SELECT MD5('mysql')
FROM DUAL;
#其他函数
SELECT BENCHMARK(1,MD5('mysql'))
FROM DUAL;