MySQL7

流程控制函数

函数	用法
IF(value,value1,value2)	如果value的值为true，返回value1，否则返回value2
IFNULL(value1,value2)	如果value不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2…[ELSE result] END	相当于JAVA的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1…[ELSE 值n]END	相当于IAVA的switch…case

# IF(value,value1,value2)
SELECT  last_name,salary,IF(salary>=6000,'高工资','低工资') "details"
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0,) "detials"
FROM employees;

#IFNULL(VALUE1,VALUE2):看作是IF(value,value1,value2)的特殊情况
SELECT  last_name,commission_pct,IFNULL(commission_pct,0) "detials"
FROM employees;

#CASE WHEN  ...THEN ... WHEN ... THEN ...ELSE... END
#类似于java的if...else if...else...
SElECT  last_name,salary,CASE WHEN salary>=15000 THEN '白骨精'
                              WHEN salary>=10000 THEN '潜力股'
                              WHEN salary>=8000 THEN '小屌丝'
                              ELSE '草根' END 'detials',department_id
                              #ELSE后面的就是剩余情况
                              #EAD后面的是这些情况的字段名
FROM employees;

#CASE  expr WHEN ... THEN ... WHEN ... THEN ...ELSE ....END
#类似于java的switch语句
/*
练习1：查询部门号为10,20,30的员工信息
若部门号为10，则打印其工资的1.1倍，
部门号为20，则打印其工资的1.2倍，
部门号为30，则打印其工资的1.3倍，
其他部门，打印其工资的1.4倍
*/
SELECT  emplouee_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 ""detials
FROM employees;

/*
练习2：查询部门号为10,20,30的员工信息
若部门号为10，则打印其工资的1.1倍，
部门号为20，则打印其工资的1.2倍，
部门号为30，则打印其工资的1.3倍
*/
SELECT  emplouee_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 ""detials
FROM employees;

加密与解密函数

函数	用法
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串，加密结果不可逆，常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式，若参数为NULL，则返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL （SHA比MD5更加安全）
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)	返回使用password_seed作为加密密码value

SELECT  PASSWORD('MYSQL')  #在8.0的版本废弃
FROM DUAL;

SELECT MD5('MYSQL'),SHA('MYSQL'),  MD5(MD5('MYSQL'))  #不可逆
FROM DUAL;

SELECT   ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
#第二个返回atguigu
FROM DUAL;

信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE(),SCHMA()	返回MySQL命令行当前所在的数据库
USER(),CURRENT_USER(),SYSTEM_USER()	返回当前连接MySQL的用户名，返回结果格式为“名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

SELECT VERSION()
,CONNECTION_ID(),DATABASE(),SCHEMA(),USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;

其他函数

函数	用法
FORMAT(value,n)	返回对数字valu进行格式化后的结果数据。n表示四舍五入后保留到小数点后m位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字
INET_NTOA(value)	将数字形式的ip地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MYSQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)	将value所使用的字符编码修改为char_code

#如果n的值小于等于0，只保留整数部分
SELECT  FORMT(123.125,2)  #123.13
 FORMT(123.125,0)   #123
 FORMT(123.125,-2)   #123
FROM DUAL;

SELECT  INET_ATON('192.168.1.100'),INET_NTOA(INET_ATON('192.168.1.100'))
#两个互转
FROM DUAL;

#BENCHMARK()用于测试表达式的执行效率
SELECT    BENCHMARK(100000,MD5('MYSQL'))
FROM DUAL;

#CONVERT():可以实现字符集的转换
SELECT  CHARSET('MYSQL'),CHARSET(CONVERT('atguigu'  USING 'gbk'))
#后者输出gbk
FROM DUAL;

课后练习

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

2.查询员工号、姓名、工资以及工资提高百分之20后的结果
SELECT employee_id,last_name,salary,salary*120% "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(employeeid,',',last_name,',',salary) "OUT_PUT"
FROM employees;

5.查询员工工作的年数、工作的天数，并且按照工作年数的降序排序
SELECT  employee_id,DATEDIFF(CURDATE(),hire_date)/365 "work_years",
TO_DAYS(CURDATE())-TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY work_years DESC;
6.查询员工的姓名，hire_date,department_id满足一下条件：
雇佣时间在1997年之后，department_id为80或者90或者110 ,commissiom_pct不为空
SELECT  last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
#AND hire_date>='1997-01-01'; #最好日期结构写完整，存在隐式转换
AND DATE_FORMAT(hire_date,'%Y')>='1997';#显示转换，日期-->字符串