一、常见函数
1、函数
函数：将一组逻辑语句封装在方法体重，对外暴露方法名；
2、好处
（1）隐藏了实现细节；
（2）提高代码的复用性；
3、函数调用
语法：
select 函数名(实参列表) 【from 表】;
4、分类
（1）单行函数
如：concat、length、ifNull 等
（2）分组函数
功能：做统计使用，由称统计函数，聚合函数，组函数；
5、常见函数
（1）字符函数
Lower()
Upper()
concat()
substr()
length()
Instr()
Lpad()
Rpad()
Trim()
replace()
（2）数学函数
round()
ceil()
floor()
truncate()
mod()
（3）日期函数
now()
curdate()
curtime()
year()
month()
monthname()
day()
hour()
minute()
second()
str_to_date()
date_format()
（4）其他
version()
database()
user()
（5）控制函数
if
case
二、字符函数
1、大小写控制函数
这类函数可以改变字符的大小写。
案例：
SELECT UPPER('John'); #JOHN
SELECT UPPER('john'); #JOHN
SELECT LOWER('John'); #john
SELECT LOWER('john'); #john
案例2：将姓变大写，名变小写，然后拼接
SELECT
CONCAT(UPPER(last_name), LOWER(first_name)) 姓名
FROM
employees ;
2、length()
Length()：获取参数值的字节个数
案例：
SELECT LENGTH('john'); # 4
案例2：
SELECT LENGTH('张三丰'); #9
注意：在客户器端使用的 utf8 编码即，所以一个汉字占三个字节。
查看字符集：
SHOW VARIABLES LIKE '%char%';
3、concat()
concat()：用于拼接字符串
案例：
SELECT
CONCAT(last_name, '_', first_name)
FROM
employees ;
还可以使用别名指定列：
SELECT
CONCAT(last_name, '_', first_name) 姓名
FROM
employees ;
4、substr() | substring()
这两个函数功能是一样的，都是截取从指定索引处后面所有的字符。
注意：MySQL 中的索引是从1开始的。
这两个函数都有重载的方式：
方式一：对字符串 str 从pos位置截取到末尾；截取从指定索引处后面所有字符
substr(str, pos);
方式二：对字符串 str 从 pos 位置开始截取，截取从指定索引处指定字符长度的字符
substr(str, pos, len);
一定要注意，这里截取的字符长度。
案例1：
SELECT
SUBSTR('Hello Java', 1, 5) out_put ;
案例2：
SELECT
SUBSTR('我爱中国', 1, 2) out_put ;
应用：姓名中首字符大写，其他字符小写，然后用"_"拼接，显示出来
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
"_",
LOWER(SUBSTR(last_name, 2))
) 姓名
FROM
employees ;
5、instr(str, substr)
instr(str, substr)：返回 substr 第一次出现的索引，如果找不到返回 0
案例：
SELECT
INSTR(
'张无忌大战光明顶光明顶',
'光明顶'
) AS out_put ; # 6
6、trim()
trim() 去除字符串首尾空格；
案例1：
SELECT
LENGTH(TRIM(' 张翠山 ')) AS out_put ; #6
trim() 还可以在字符串首尾中去除指定的字符
语法：
trim(要去除的字符 from str);
案例2：
SELECT
TRIM(
'a' FROM 'aaaaaaaaaaa张aaaa翠山aaaaaaaaaaa'
) AS out_put ;
7、lpad()
lpad()：用指定的字符实现左填充指定长度，如果多了会截断
案例1：
SELECT
LPAD('殷素素', 10, '*') AS out_put ; # *******殷素素
案例2：
SELECT
LPAD('殷素素', 2, '*') AS out_put ; #殷素
8、rpad()
rpad() 用指定的字符实现左填充指定长度，如果多了会截断
案例1：
SELECT
RPAD('殷素素', 12, 'a') AS out_put ; #殷素素aaaaaaaaa
案例2：
SELECT
RPAD('殷素素', 2, 'a') AS out_put ; #殷素
9、replace()
replace(str, A, B)：会把字符里的字符 A 全部替换为 B
案例：
SELECT
REPLACE(
'张无忌爱上了周芷若爱上了周芷若',
'周芷若',
'赵敏'
) ; #张无忌爱上了赵敏爱上了赵敏
三、数学函数
1、round()：四舍五入
round()：四舍五入函数
案例：
SELECT ROUND(3.14159); # 3 默认取整
SELECT ROUND(3.14159, 2); # 3.14 保留到指定精度，保留多少位小数
2、ceil()：向上取整
ceil()：向上取整，返回大于等于该参数的最小整数
案例：
SELECT CEIL(1.52); # 2
SELECT CEIL(1.01); # 2
SELECT CEIL(1.00); # 1
SELECT CEIL(-1.10); # -1
3、floor()：向上取整
floor()：向上取整，返回小于等于该参数的最大整数
案例：
SELECT FLOOR(9.99); # 9
SELECT FLOOR(-9.99); # -10
4、truncate() 截断
truncate 截断，小数点后保留几位，不需要进位，直接截断
案例：
SELECT TRUNCATE(1.6999, 2); # 1.69
5、mod() 取余
mod() 取余
案例：
SELECT MOD(10, 3); # 1
SELECT 10 % 3; # 1
SELECT MOD(-10, 3); # -1
SELECT -10 % 3; # 1
扩展：mod 的是怎么计算的呢？
mod(a, b) => a-a/b*b
例如：
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
四、日期函数
1、now()：获取当前系统日期+时间
SELECT NOW(); #2021-06-15 16:13:36
2、curdate()：返回当前系统日期，不包含时间
SELECT CURDATE(); #2021-06-15
3、curtime() 返回当前时间，不包含日期
SELECT CURTIME(); #16:14:42
4、获取指定的部分
SELECT YEAR(NOW()); #2021 年份
SELECT MONTH(NOW()); #6 月份
SELECT MONTHNAME(NOW()); #June 月份（英文）
SELECT DAY(NOW()); #15 日
SELECT DAYNAME(NOW()); #Tuesday 周几
SELECT HOUR(NOW()); #16 小时
SELECT MINUTE(NOW()); #18 分钟
SELECT SECOND(NOW()); #35 秒
5、str_to_date：将日期格式的字符转换成指定格式的日期
案例：
SELECT STR_TO_DATE('1998-3-02', '%Y-%c-%d');
应用：查询入职日期为 1992-4-3 的员工信息
SELECT
*
FROM
employees
WHERE hiredate = STR_TO_DATE('4-3 1992', '%c-%d %Y') ;
6、date_format:将日期转换成字符
案例：
SELECT
DATE_FORMAT('2021-6-8', '%Y年%m月%d日') AS out_put ;
应用：
SELECT
last_name,
DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入职日期
FROM
employees
WHERE commission_pct IS NOT NULL ;
五、其他函数
1、查看MySQL版本号
SELECT VERSION();
2、查看当前使用的数据库
SELECT DATABASE();
3、查看当前登录用户
SELECT USER();
六、流程控制函数
1、if 函数
语法：
IF(expr1, expr2, expr3)
当 expre1 为true，执行 expr2，否则执行 expr3
案例：
SELECT IF(10 > 5, '大', '小');
应用：
SELECT
last_name,
commission_pct,
IF (
commission_pct IS NULL,
'呵呵',
'有奖金，哈哈'
) AS 备注
FROM
employees ;
2、case 函数
（1）方式一：完全匹配（类似于 switch... case 的效果）
语法：
case 要判断的变量、字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
when 常量3 then 要显示的值3或语句3;
...
else 要显示的值n或语句n;
end
案例：查询员工的工资，要求
① 部门号=30，显示的工资为1.1倍；
② 部门号=40，显示的工资为1.2倍；
③ 部门号=50，显示的工资为1.3倍；
④ 其他部门，显示的工资为原工资
SELECT salary 原始工资, department_id,
CASE department_id
WHEN 30 THEN salary * 1.1
WHEN 40 THEN salary * 1.2
WHEN 50 THEN salary * 1.3
ELSE salary
END
AS 新工资
FROM employees;
（2）方式二：范围匹配（类似于多重 if）
语法：
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
...
else 要显示的值 n 或语句 n
end
案例：查询员工中的工资，
① 如果工资 > 20000，显示A级别
② 如果工资 > 15000，显示B级别
③ 如果工资 > 10000, 显示C级别
④ 否则，显示D级别
SELECT salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
七、练习
1、显示系统时间(注：日期+时间)
SELECT NOW();
2、查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT
employee_id,
last_name,
salary,
salary * 1.2 "new salary"
FROM
employees ;
3、将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT
LENGTH(last_name) 长度,
SUBSTR(last_name, 1, 1) 首字符,
last_name
FROM
employees
ORDER BY 首字符 ;
4、做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary"
FROM employees
WHERE salary=24000;
5、使用case-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
SELECT last_name,job_id AS job,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_PRE' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM employees
WHERE job_id = 'AD_PRES';
