存储过程和函数¶

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

一、函数¶

好处：

隐藏了实现细节
提高代码的重用性

调用：

SELECT 函数名(实参列表) [FROM 表];

分类：

单行函数：如concat、length、ifnull等
分组函数：做统计使用，又称为统计函数、聚合函数、组函数

1. 单行函数¶

1.1 字符函数¶

1. `CONCAT`拼接字符¶

SELECT CONCAT(first_name," ",last_name) 姓名 FROM employees;

2.`LENGTH`获取字节长度¶

mysql [(none)]>SELECT LENGTH('hello 北京');
+------------------------+
| LENGTH('hello 北京')   |
+------------------------+
|                     12 |
+------------------------+

3.`CHAR_LENGTH` 获取字符个数¶

mysql [(none)]>SELECT CHAR_LENGTH('hello 北京');
+-----------------------------+
| CHAR_LENGTH('hello 北京')   |
+-----------------------------+
|                           8 |
+-----------------------------+

4. `SUBSTRING` 截取子串¶

注意：起始索引从1开始！

SUBSTR(str,起始索引，截取的字符长度)

SUBSTR(str,起始索引)

mysql [(none)]>SELECT SUBSTR('张三丰爱上了郭襄',1,3);
+----------------------------------------+
| SUBSTR('张三丰爱上了郭襄',1,3)         |
+----------------------------------------+
| 张三丰                                 |
+----------------------------------------+
mysql [(none)]>SELECT SUBSTR('张三丰爱上了郭襄',7);
+--------------------------------------+
| SUBSTR('张三丰爱上了郭襄',7)         |
+--------------------------------------+
| 郭襄                                 |
+--------------------------------------+

5. `INSTR`获取字符第一次出现的索引¶

mysql [(none)]>mysql [(none)]>SELECT INSTR('孙悟空大战猪八戒，猪八戒大战孙悟空','猪八戒') AS output;
+--------+
| output |
+--------+
|      6 |
+--------+

6. `TRIM`去前后指定的字符，默认是去空格¶

mysql [(none)]>SELECT TRIM('    虚竹    ')  AS output;
+--------+
| output |
+--------+
| 虚竹   |
+--------+
mysql [(none)]>SELECT TRIM('x' FROM 'xxx虚xxx竹xxx')  AS output;
+-----------+
| output    |
+-----------+
| 虚xxx竹   |
+-----------+

7.LPAD/RPAD 左填充/右填充

mysql [(none)]>SELECT LPAD('木婉清',10,'x');
+--------------------------+
| LPAD('木婉清',10,'x')    |
+--------------------------+
| xxxxxxx木婉清            |
+--------------------------+
mysql [(none)]>SELECT RPAD('木婉清',10,'x');
+--------------------------+
| RPAD('木婉清',10,'x')    |
+--------------------------+
| 木婉清xxxxxxx            |
+--------------------------+

8.`UPPER`/`LOWER` 变大写/变小写¶

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT" FROM myemployees.employees;

9.`STRCMP` 比较两个字符大小¶

mysql [(none)]>SELECT STRCMP('aec','aec');
+---------------------+
| STRCMP('aec','aec') |
+---------------------+
|                   0 |
+---------------------+

10.`LEFT`/`RIGHT` 截取子串¶

mysql [(none)]>SELECT LEFT('鸠摩智',1);
+---------------------+
| LEFT('鸠摩智',1)    |
+---------------------+
| 鸠                  |
+---------------------+
mysql [(none)]>SELECT RIGHT('鸠摩智',1);
+----------------------+
| RIGHT('鸠摩智',1)    |
+----------------------+
| 智                   |
+----------------------+

1.2 数学函数¶

1. `ABS` 绝对值¶

mysql [(none)]>SELECT ABS(-2.4);
+-----------+
| ABS(-2.4) |
+-----------+
|       2.4 |
+-----------+

2. `CEI` 向上取整返回>=该参数的最小整数¶

mysql [(none)]>SELECT CEIL(-1.09),CEIL(0.09),CEIL(1.00);
+-------------+------------+------------+
| CEIL(-1.09) | CEIL(0.09) | CEIL(1.00) |
+-------------+------------+------------+
|          -1 |          1 |          1 |
+-------------+------------+------------+

3. `FLOOR` 向下取整，返回<=该参数的最大整数¶

mysql [(none)]>SELECT FLOOR(-1.09), FLOOR(0.09), FLOOR(1.00);
+--------------+-------------+-------------+
| FLOOR(-1.09) | FLOOR(0.09) | FLOOR(1.00) |
+--------------+-------------+-------------+
|           -2 |           0 |           1 |
+--------------+-------------+-------------+

4. `ROUND` 四舍五入¶

mysql [(none)]>SELECT ROUND(1.8712345), ROUND(1.8712345,2);
+------------------+--------------------+
| ROUND(1.8712345) | ROUND(1.8712345,2) |
+------------------+--------------------+
|                2 |               1.87 |
+------------------+--------------------+

5. `TRUNCATE` 截断¶

mysql [(none)]>SELECT TRUNCATE(1.8712345,1);
+-----------------------+
| TRUNCATE(1.8712345,1) |
+-----------------------+
|                   1.8 |
+-----------------------+

6. `MOD` 取余¶

# 两种方式
mysql [(none)]>SELECT MOD(-10,3), -10%3, 10%3;
+------------+-------+------+
| MOD(-10,3) | -10%3 | 10%3 |
+------------+-------+------+
|         -1 |    -1 |    1 |
+------------+-------+------+

1.3 日期函数¶

1. `NOW()`¶

# 返回当前系统日期+时间 
mysql [(none)]>SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-12-09 22:45:12 |
+---------------------+

2.`CURDATE()`¶

# 返回当前系统日期，不包含时间 
mysql [(none)]>SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2020-12-09 |
+------------+

3. `CURTIME()`¶

# 返回当前时间，不包含日期
mysql [(none)]>SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 22:47:45  |
+-----------+

4. `DATEDIFF()`¶

# 返回两个日期相差的天数
mysql [(none)]>SELECT DATEDIFF('2018-7-16','2019-7-13');
+-----------------------------------+
| DATEDIFF('2018-7-16','2019-7-13') |
+-----------------------------------+
|                              -362 |
+-----------------------------------+

5. `DATE_FORMAT()`¶

# 将日期转换成字符串 
mysql [(none)]>SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期;
+-----------------------------------------+
| 出生日期                                |
+-----------------------------------------+
| 1998年07月16日 00小时00分钟00秒         |
+-----------------------------------------+

6. `STR_TO_DATE()`¶

# 按指定格式解析字符串为日期类型
mysql [(none)]>SELECT STR_TO_DATE('1998 3/2', '%Y %c/%d') AS output ;
+------------+
| output     |
+------------+
| 1998-03-02 |
+------------+

1.4 系统信息函数¶

SELECT VERSION();   # 当前数据库服务器的版本
SELECT DATABASE();  # 当前打开的数据库
SELECT USER();      # 当前用户

1.5 加密函数¶

SELECT password('123456');
SELECT md5('123456');

2. 流程控制函数¶

1. `IF`函数¶

mysql [(none)]>SELECT IF(100>9,'好','坏');
+-----------------------+
| IF(100>9,'好','坏')   |
+-----------------------+
| 好                    |
+-----------------------+
# 如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金,commission_pct
FROM employees;

2. `CASE`函数¶

2.1 使用1：类似于switch语句，可以实现等值判断¶

语法

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1;
WHEN 常量2 THEN 要显示的值2或语句2;
...
ELSE 要显示的值n或语句n;
END

案例：

# 部门编号是30，工资显示为2倍
# 部门编号是50，工资显示为3倍
# 部门编号是60，工资显示为4倍
# 否则不变

# 显示 部门编号，新工资，旧工资
SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END  newSalary
FROM employees;

2.2 使用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;  

3. 分组函数¶

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum() 求和、avg() 平均值、max() 最大值、min() 最小值、count() 计数（非空）

特点：

sum、avg一般用于处理数值型数据
max、min、count可以处理任何类型数据
以上分组函数都忽略null值

# 查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
mysql [myemployees]>SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;
+-------------+-------------+-------------+-------------+---------------+
| SUM(salary) | AVG(salary) | MIN(salary) | MAX(salary) | COUNT(salary) |
+-------------+-------------+-------------+-------------+---------------+
|   691400.00 | 6461.682243 |     2100.00 |    24000.00 |           107 |
+-------------+-------------+-------------+-------------+---------------+

3.1 `COUNT()`¶

1 统计结果集的行数，推荐使用count(*)

mysql [myemployees]>SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
|      107 |
+----------+

2 搭配distinct实现去重的统计

mysql [myemployees]>SELECT COUNT(DISTINCT department_id) FROM employees;
+-------------------------------+
| COUNT(DISTINCT department_id) |
+-------------------------------+
|                            11 |
+-------------------------------+

存储过程和函数¶

一、函数¶

1. 单行函数¶

1.1 字符函数¶

1. CONCAT拼接字符¶

2.LENGTH获取字节长度¶

3.CHAR_LENGTH 获取字符个数¶

4. SUBSTRING 截取子串¶

5. INSTR获取字符第一次出现的索引¶

6. TRIM去前后指定的字符，默认是去空格¶

8.UPPER/LOWER 变大写/变小写¶

9.STRCMP 比较两个字符大小¶

10.LEFT/RIGHT 截取子串¶

1.2 数学函数¶

1. ABS 绝对值¶

2. CEI 向上取整 返回>=该参数的最小整数¶

3. FLOOR 向下取整，返回<=该参数的最大整数¶

4. ROUND 四舍五入¶

5. TRUNCATE 截断¶

6. MOD 取余¶