一.流程控制函数
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
FROM employees;
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '高新'
WHEN salary >= 10000 THEN '潜力股'
WHEN salary >= 8000 THEN '小屌丝'
ELSE '草根' END details,department_id
FROM employees;
SELECT last_name,department_id,salary,CASE WHEN department_id = 10 THEN salary * 1.1
WHEN department_id = 20 THEN salary *1.2
WHEN department_id = 30 THEN salary*1.3
ELSE salary * 1.4
END details
FROM employees;
SELECT 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');
SELECT MD5('mysql'),SHA('mysql');
SELECT ENCODE('mysql','my'),DECODE('ENCODE('mysql','my') ','my');
信息函数
SELECT VERSION(),CONNECTION_ID(),DATABASE(),USER(),CHARSET('学习使人进步'),COLLATION('学习');
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/368c8f5f03cbe8631ef403d6253a2830.png)
其他函数(了解)
SELECT FORMAT(4.2356,3),FORMAT(4,3),FORMAT(231.23,-1);
SELECT CONV(3,16,2),CONV(888,10,16),CONV(NULL,10,2)
SELECT INET_ATON('192.168.1.100')
SELECT INET_NTOA(3232235876);
SELECT BENCHMARK(100000,MD5('mysql'));
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'utf8mb4' ));
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/029401f2ad72a167b4eec118eb67b510.png)
单行函数练习
SELECT NOW();
SELECT employee_id,last_name,salary, salary*1.2 "new salary"
FROM employees;
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name
SELECT CONCAT(employee_id,last_name,salary ) OUT_PUT
FROM employees
SELECT last_name,YEAR(NOW())-YEAR(hire_date) job_year,DATEDIFF(NOW(),hire_date) job_day
FROM employees
ORDER BY job_year DESC;
SELECT last_name,hire_date,department_id
FROM employees
WHERE YEAR(hire_date) >= 1997 AND department_id IN (80,90,100) AND commission_pct IS NOT NULL
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(NOW(),hire_date) >=10000
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0),' monthly but wants ',FORMAT(salary*3,-1)) "Dream Salary"
FROM employees
SELECT last_name,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 "Grade"
FROM employees