【SQL】之函数：单行函数

单行函数

数值函数，字符串函数

-- 单行函数

#四舍五入
SELECT ROUND(123.756),ROUND(123.456,0),ROUND(123.456,2),ROUND(123.456,-1),ROUND(123.456,-2)
FROM DUAL;

#连接字符串
SELECT CONCAT(e.last_name,' worked for ',m.last_name) "details"
FROM employees e JOIN employees m
ON e.`manager_id`=m.`employee_id`;

SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;

#替换索引位置
#字符串的索引是从1开始的！
SELECT INSERT('helloworld',2,3,'aaabbb') #从第二个开始的三个字符被替换
FROM DUAL;
SELECT REPLACE('hello','lo','mmm')
FROM DUAL;

#填补
#在salary列数据的左边补*至10位（右对齐）
SELECT employee_id,last_name,LPAD(salary,10,'*')
FROM employees;
#rpad:左对齐

流程控制函数

-- 流程控制函数

#1.if(value,v1,v2)
SELECT last_name,salary,IF(salary>=6000,'高工资','低工资') "details"
FROM employees;

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)) "annual_sal"
FROM employees;

#2.ifnull(v1,v2)
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;

#3.case...when...then...when...then...else...end
SELECT last_name,salary,CASE WHEN salary>=15000 THEN '高薪'
			     WHEN salary>=10000 THEN '潜力股'
			     WHEN salary>=8000 THEN '小屌丝'
			     ELSE '草根' END "details"  #可以不要else，直接end
FROM employees;

SELECT employee_id,last_name,department_id,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 "details"
FROM employees;

SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary*1.1
								WHEN 20 THEN salary*1.2
								WHEN 30 THEN salary*1.3
								END "details"
FROM employees
WHERE department_id IN (10,20,30);

加密解密函数，其他函数

-- 加密解密函数

SELECT PASSWORD('mysql') #mysql8.0中被弃用
FROM DUAL;

SELECT MD5('mysql'),SHA('mysql')
FROM DUAL;

SELECT ENCODE('alibaba','mysql'),DECODE(ENCODE('alibaba','mysql'),'mysql') #mysql8.0弃用
FROM DUAL;

-- 其他函数
#benchmark()测试表达式的执行效率
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;
#字符集转换
SELECT CHARSET('alibaba'),CHARSET(CONVERT('alibaba' USING 'gbk'))
FROM DUAL;

练习

# 1.显示系统时间(注：日期+时间)
SELECT NOW(),SYSDATE(),LOCALTIME(),CURDATE()
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary*1.2 "new salary"
FROM employees;

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
ORDER BY last_name ASC;
#order by name_length asc; 姓名长度按从小到大排序

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT_WS(',',employee_id,last_name,salary) "out_put"
FROM employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
#datediff(date1,date2)————返回date1->date2的日期间隔天数
#curdate()当前年月日
SELECT employee_id,DATEDIFF(CURDATE(),hire_date) "worked_days",
ROUND(DATEDIFF(CURDATE(),hire_date)/365) "worked_years",
TO_DAYS(CURDATE())-TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：
#雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
AND YEAR(hire_date)>='1997';

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date)>=10000;

# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,'earns',TRUNCATE(salary,0),'monthly but wants',TRUNCATE(salary*3,0)) "dream salary"
FROM employees;

#9.case...when
SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
							WHEN 'ST_MAN' THEN 'B'
							WHEN 'IT_PROG' THEN 'C'
							WHEN 'SA_REP' THEN 'D'
							WHEN 'ST_CLERK' THEN 'E'
							END "Gread"
FROM employees;