函数：

含义：一组预先编译好的sql语句的集合，理解成批处理语句

1：提高代码的重用性

2：简化操作

3：减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

函数和存储过程的区别：

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新。

函数：有且仅有1个返回，适合做处理数据后返回一个结果。

创建语法：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型

BEGIN

函数体

END

注意：

1：参数列表包含两部分：

参数名 参数类型

2：函数体：肯定会有return语句，如果没有也不会报错

如果return语句没有放在函数体的最后也不会爆粗，但不建议。

return值：

3：函数体中仅有一句话，则可以省略begin end

4：使用 delimiter语句设置标记结束

调用语法：

SELECT 函数名(参数列表)

示例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
无参有返回
例1：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;	#定义局部变量
	SELECT COUNT(*) INTO c      #赋值
	FROM employees;
	RETURN c;
END $

SELECT myf1()$

有参有返回：
2：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;#定义用户变量 
	SELECT salary INTO @sal   #赋值
	FROM employees
	WHERE last_name = empName;
	
	RETURN @sal;
END $

SELECT myf2('k_ing') $

3：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE ;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id = d.department_id
	WHERE d.department_name=deptName;
	RETURN sal;
END $

SELECT myf3('IT')

查看函数：
SHOW CREATE FUNCTION myf3;

删除函数：
DROP FUNCTION myf3;

例：创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
	DECLARE SUM FLOAT DEFAULT 0;
	SET SUM=num1+num2;
	RETURN SUM;
END $

SELECT test_fun1(1,2)$
调用函数：

SELECT 函数名 (实参列表)

函数和存储过程的区别：
关键字 调用语法 返回值 应用场景
函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个
存储过程 PROCEDURE CALL 存储过程() 可以有0个或多个 一般用于更新
字符函数：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
concat 拼接

substr 截取子串

upper 转换成大写

lower 转换成小写

trim 去掉前后自定的空格和字符

ltrim 去掉左边空格

rtrim 去掉右边空格

replace 替换

lpad 左填充

rpad 右填充

instr 返回子串第一次出现的索引

length 获取字节个数
数组函数：
1
2
3
4
5
6
7
8
9
10
11
round 四舍五入

rand 随机数

ceil 向上取整

floor 向下取整

mod 取余()

truncate 截断
日期函数：
1
2
3
4
5
6
7
8
9
now 当前系统日期+时间

curdate 当前系统日期

curtime 当前系统时间

str_to_date 将字符串转换成日期

data_format 将日期转换成字符
分组函数：
1
2
3
4
5
6
7
8
9
10
11
12
13
sum 求和
max 最大值
min 最小值
avg 平均值
count 计数

特点：
1：以上五个分组函数都忽略null值，除了count(*)
2：sum和avg一般用于处理数值型。
   max、min、count可以处理任何数据类型
3：都可以搭配distinct使用，用于统计去重后的结果
4：count的参数可以支持(建议使用*)：
	字段、*、常量值，一般放1
流程控制函数：
if函数：
1
2
3
4
SELECT IF (10<5, '大' , '小')；

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM employees;
case函数：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
case函数的使用一：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
case函数的使用二：类似于 多重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 emplyees;

2021-04-03