21 天 Python 计划：MySQL视图、触发器、存储过程、函数与流程控制

Python是一种强大且易于学习的编程语言。通过这个21天的计划，我们将逐步深入MySQL视图、触发器、存储过程、函数与流程控制。无论你是初学者还是有一定基础的开发者，这个计划都将帮助你巩固和扩展你的Python知识。

在学习本篇之前，我们先复习一下前面的内容：
day1：Python下载和开发工具介绍
day2：数据类型、字符编码、文件处理
day3：基础语法与课外练习
day4：函数简单介绍
day5：模块与包
day6：常用模块介绍
day7：面向对象
day8：面向对象高级
day9：异常处理
day10：网络编程
day11：并发编程
day12：MySQL数据库初识
day13：MySQL库相关操作
day14：MySQL表相关操作
day15：MySQL中DML与权限管理

一、视图

视图是一个虚拟表，它并不真实存在于数据库中，其本质是根据 SQL 语句获取动态的数据集，并为其命名。用户使用时只需使用这个名称即可获取结果集，就像操作普通表一样。

-- 两张有关系的表
mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)mysql> select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+
5 rows in set (0.00 sec)-- 查询李平老师教授的课程名
mysql> select cname from course where teacher_id = (select tid from teacher where tname='李平老师');
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
2 rows in set (0.00 sec)

1.1 创建视图

视图的创建语法为：CREATE VIEW 视图名称 AS SQL 语句。以下是一个示例：

-- 创建视图
create view teacher_view as select tid from teacher where tname='李平老师';-- 使用视图查询李平老师教授的课程名
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname  |
+--------+
| 物理   |
| 美术   |
+--------+
2 rows in set (0.00 sec)

使用视图可以避免每次都重写复杂的子查询 SQL，但需要注意的是，视图的效率可能不如直接写子查询，而且视图存放在数据库中，如果程序中的 SQL 过分依赖视图，会导致扩展 SQL 极为不便。

1.2 使用视图

视图的数据修改会影响原始表，反之亦然。以下是一个示例：

mysql> select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)mysql> create view course_view as select * from course;
Query OK, 0 rows affected (0.52 sec)mysql> select * from course_view;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+
4 rows in set (0.00 sec)mysql> update course_view set cname='xxx';
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0mysql> insert into course_view values(5,'yyy',2);
Query OK, 1 row affected (0.03 sec)mysql> select * from course;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   1 | xxx   |          1 |
|   2 | xxx   |          2 |
|   3 | xxx   |          3 |
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
5 rows in set (0.00 sec)

不过，我们不建议修改视图中的记录，特别是在涉及多个表的情况下，修改视图记录可能会出现问题。

1.3 修改视图

修改视图的语法为：ALTER VIEW 视图名称 AS SQL 语句。示例如下：

mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)mysql> select * from teacher_view;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
|   4 | xxx   |          2 |
|   5 | yyy   |          2 |
+-----+-------+------------+
2 rows in set (0.00 sec)

1.4 删除视图

删除视图的语法为：DROP VIEW 视图名称。例如：

DROP VIEW teacher_view

二、触发器

触发器可以定制用户对表进行增、删、改操作时前后的行为，但不支持查询操作。

2.1 创建触发器

触发器的创建语法如下：

-- 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN...
END-- 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN...
END-- 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN...
END-- 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN...
END-- 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN...
END-- 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN...
END

以下是一个具体的示例：

-- 准备表
CREATE TABLE cmd (id INT PRIMARY KEY auto_increment,USER CHAR (32),priv CHAR (10),cmd CHAR (64),sub_time datetime, -- 提交时间success enum ('yes', 'no') -- 0代表执行失败
);CREATE TABLE errlog (id INT PRIMARY KEY auto_increment,err_cmd CHAR (64),err_time datetime
);-- 创建触发器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGINIF NEW.success = 'no' THEN -- 等值判断只有一个等号INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; -- 必须加分号END IF ; -- 必须加分号
END//
delimiter ;-- 往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (USER,priv,cmd,sub_time,success
)
VALUES('xiao','0755','ls -l /etc',NOW(),'yes'),('xiao','0755','cat /etc/passwd',NOW(),'no'),('xiao','0755','useradd xxx',NOW(),'no'),('xiao','0755','ps aux',NOW(),'yes');-- 查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd         | err_time            |
+----+-----------------+---------------------+
|  1 | cat /etc/passwd | 2017-09-14 22:18:48 |
|  2 | useradd xxx     | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)

特别的，NEW表示即将插入的数据行，OLD表示即将删除的数据行。

2.2 使用触发器

触发器无法由用户直接调用，而是在对表进行增、删、改操作时被动触发。

2.3 删除触发器

删除触发器的语法为：drop trigger 触发器名称。例如：

drop trigger tri_after_insert_cmd;

三、存储过程

存储过程包含了一系列可执行的 SQL 语句，存放在 MySQL 中，通过调用它的名字可以执行其内部的 SQL 语句。

3.1 介绍

使用存储过程的优点是可以替代程序写的 SQL 语句，实现程序与 SQL 解耦，并且基于网络传输时，传别名的数据量小。缺点是程序员扩展功能不方便。

程序与数据库结合使用的三种方式：

-- 方式一：MySQL：存储过程程序：调用存储过程-- 方式二：MySQL：程序：纯 SQL 语句-- 方式三：MySQL:程序：类和对象，即 ORM（本质还是纯 SQL 语句）

3.2 创建简单存储过程（无参）

delimiter //
create procedure p1()
BEGINselect * from blog;INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;-- 在 mysql 中调用
call p1() -- 在 python 中基于 pymysql 调用
cursor.callproc('p1') 
print(cursor.fetchall())

3.3 创建存储过程（有参）

存储过程的参数有三类：in仅用于传入参数，out仅用于返回值，inout既可以传入又可以当作返回值。

-- 示例 1：in 参数
delimiter //
create procedure p2(in n1 int,in n2 int
)
BEGINselect * from blog where id > n1;
END //
delimiter ;-- 在 mysql 中调用
call p2(3,2)-- 在 python 中基于 pymysql 调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())-- 示例 2：in 和 out 参数
delimiter //
create procedure p3(in n1 int,out res int
)
BEGINselect * from blog where id > n1;set res = 1;
END //
delimiter ;-- 在 mysql 中调用
set @res=0; -- 0代表假（执行失败），1代表真（执行成功）
call p3(3,@res);
select @res;-- 在 python 中基于 pymysql 调用
cursor.callproc('p3',(3,0)) -- 0相当于 set @res=0
print(cursor.fetchall()) -- 查询 select 的查询结果cursor.execute('select @_p3_0,@_p3_1;') -- @p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())-- 示例 3：inout 参数
delimiter //
create procedure p4(inout n1 int
)
BEGINselect * from blog where id > n1;set n1 = 1;
END //
delimiter ;-- 在 mysql 中调用
set @x=3;
call p4(@x);
select @x;-- 在 python 中基于 pymysql 调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) -- 查询 select 的查询结果cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

3.4 执行存储过程

-- 无参数
call proc_name()-- 有参数，全 in
call proc_name(1,2)-- 有参数，有 in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

以下是 Python 中基于 pymysql 调用存储过程的示例：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysqlconn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()conn.commit()
cursor.close()
conn.close()print(result)

3.5 删除存储过程

删除存储过程的语法为：drop procedure 存储过程名称。例如：

drop procedure proc_name;

四、函数

MySQL 提供了许多内置函数，以下是一些常见的函数分类和示例：

4.1 数学函数

• ROUND(x,y)：返回参数 x 的四舍五入的有 y 位小数的值。
• RAND()：返回 0 到 1 内的随机值，可以通过提供一个参数（种子）使 RAND() 随机数生成器生成一个指定的值。

4.2 聚合函数

• AVG(col)：返回指定列的平均值。
• COUNT(col)：返回指定列中非 NULL 值的个数。
• MIN(col)：返回指定列的最小值。
• MAX(col)：返回指定列的最大值。
• SUM(col)：返回指定列的所有值之和。
• GROUP_CONCAT(col)：返回由属于一组的列值连接组合而成的结果。

4.3 字符串函数

• CHAR_LENGTH(str)：返回字符串 str 的长度，长度的单位为字符。
• CONCAT(str1,str2,…)：字符串拼接，如有任何一个参数为 NULL，则返回值为 NULL。
• CONCAT_WS(separator,str1,str2,…)：字符串拼接（自定义连接符），不会忽略任何空字符串，但会忽略所有的 NULL。
• CONV(N,from_base,to_base)：进制转换。
• FORMAT(X,D)：将数字 X 的格式写为 ‘#,###,###.##’，以四舍五入的方式保留小数点后 D 位，并将结果以字符串的形式返回。
• INSERT(str,pos,len,newstr)：在 str 的指定位置插入字符串。
• INSTR(str,substr)：返回字符串 str 中子字符串的第一个出现位置。
• LEFT(str,len)：返回字符串 str 从开始的 len 位置的子序列字符。
• LOWER(str)：变小写。
• UPPER(str)：变大写。
• REVERSE(str)：返回字符串 str，顺序和字符顺序相反。
• SUBSTRING(str,pos)、SUBSTRING(str FROM pos)、SUBSTRING(str,pos,len)、SUBSTRING(str FROM pos FOR len)：从字符串 str 返回子字符串。

4.4 日期和时间函数

• CURDATE()或 CURRENT_DATE()：返回当前的日期。
• CURTIME() 或 CURRENT_TIME()：返回当前的时间。
• DAYOFWEEK(date)：返回 date 所代表的一星期中的第几天（1~7）。
• DAYOFMONTH(date)：返回 date 是一个月的第几天（1~31）。
• DAYOFYEAR(date)：返回 date 是一年的第几天（1~366）。
• DAYNAME(date)：返回 date 的星期名。
• FROM_UNIXTIME(ts,fmt)：根据指定的 fmt 格式，格式化 UNIX 时间戳 ts。
• HOUR(time)：返回 time 的小时值（0~23）。
• MINUTE(time)：返回 time 的分钟值（0~59）。
• MONTH(date)：返回 date 的月份值（1~12）。
• MONTHNAME(date)：返回 date 的月份名。
• NOW()：返回当前的日期和时间。
• QUARTER(date)：返回 date 在一年中的季度（1~4）。
• WEEK(date)：返回日期 date 为一年中第几周（0~53）。
• YEAR(date)：返回日期 date 的年份（1000~9999）。

4.5 加密函数

在数据处理过程中，为了保证数据的安全性，常常需要对敏感信息进行加密处理。MySQL 提供了一些实用的加密函数。

4.5.1 MD5()

MD5()函数用于计算字符串的 MD5 校验和。MD5 是一种广泛使用的哈希函数，它可以将任意长度的输入转换为固定长度（128 位）的哈希值。示例如下：

SELECT MD5('your_string');

上述代码会返回 your_string 的 MD5 哈希值。

4.5.2 PASSWORD(str)

PASSWORD(str) 函数返回字符串 str 的加密版本，这个加密过程是不可逆转的，并且和 UNIX 密码加密过程使用不同的算法。通常用于存储用户密码，示例如下：

SELECT PASSWORD('your_password');

该代码会返回 your_password加密后的结果。

4.6 控制流函数

控制流函数可以根据不同的条件返回不同的结果，在数据处理和查询中非常有用。

4.6.1 CASE WHEN[test1] THEN [result1]…ELSE [default] END

这种形式的 CASE 语句用于根据多个条件进行判断。如果 testN为真，则返回 resultN，否则返回 default。示例如下：

SELECT CASE WHEN score >= 90 THEN '优秀'WHEN score >= 80 THEN '良好'WHEN score >= 60 THEN '及格'ELSE '不及格'END AS grade
FROM student_scores;

上述代码根据学生的成绩 score进行判断，返回对应的等级。

4.6.2 CASE [test] WHEN[val1] THEN [result]…ELSE [default]END

这种形式的 CASE语句用于判断 test是否等于 valN。如果相等，则返回 resultN，否则返回 default。示例如下：

SELECT CASE subjectWHEN '数学' THEN 'Math'WHEN '语文' THEN 'Chinese'ELSE 'Other'END AS subject_english
FROM courses;

该代码根据课程名称 subject` 返回对应的英文名称。

4.6.3 IF(test,t,f)

IF()函数根据 test 的真假返回不同的值。如果 test为真，返回 t；否则返回 f。示例如下：

SELECT IF(age >= 18, '成年人', '未成年人') AS age_group FROM users;

上述代码根据用户的年龄 age 判断其是否为成年人。

4.6.4 IFNULL(arg1,arg2)

IFNULL() 函数用于判断 arg1是否为空。如果arg1不是空，返回 arg1；否则返回 arg2。示例如下：

SELECT IFNULL(email, '未提供邮箱') AS user_email FROM users;

该代码在用户邮箱 email 为空时，返回 未提供邮箱`。

4.6.5 NULLIF(arg1,arg2)

NULLIF()函数用于比较 arg1 和 arg2。如果 arg1 = arg2，返回 NULL；否则返回 arg1。示例如下：

SELECT NULLIF(value1, value2) FROM data_table;

上述代码会根据 value1和 value2的比较结果返回相应的值。

4.7 控制流函数小练习

4.7.1 准备表

我们创建了几个表，包括 course、score、student 和 teacher，并插入了一些示例数据。以下是创建表和插入数据的 SQL 代码：

SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (`c_id` int(11) NOT NULL,`c_name` varchar(255) DEFAULT NULL,`t_id` int(11) DEFAULT NULL,PRIMARY KEY (`c_id`),KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`s_id` int(10) DEFAULT NULL,`c_id` int(11) DEFAULT NULL,`num` double DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`s_id` varchar(20) NOT NULL,`s_name` varchar(255) DEFAULT NULL,`s_age` int(10) DEFAULT NULL,`s_sex` char(1) DEFAULT NULL,PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (`t_id` int(10) NOT NULL,`t_name` varchar(50) DEFAULT NULL,PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '张三');
INSERT INTO `teacher` VALUES ('2', 'lisi');
INSERT INTO `teacher` VALUES ('3', 'xiao');
INSERT INTO `teacher` VALUES ('4', 'peiqi');

4.7.2 统计各科各分数段人数

我们要统计各科各分数段的人数，显示格式为：课程 ID、课程名称、[100 - 85]、[85 - 70]、[70 - 60]、[ < 60]。可以使用以下 SQL 语句：

select  score.c_id,course.c_name, sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;    

该 SQL 语句通过 CASE语句对分数进行分段统计，并使用 SUM()函数计算每个分数段的人数。

五、自定义函数

在 MySQL 中，我们可以自定义函数来实现特定的功能。需要注意的是，函数中不要写 SQL 语句，函数仅仅只是一个功能，是一个在 SQL 中被应用的功能。若要想在 begin…end… 中写 SQL，请用存储过程。

5.1 创建自定义函数

以下是两个自定义函数的示例：

delimiter //
create function f1(i1 int,i2 int)
returns int
BEGINdeclare num int;set num = i1 + i2;return(num);
END //
delimiter ;delimiter //
create function f5(i int
)
returns int
begindeclare res int default 0;if i = 10 thenset res=100;elseif i = 20 thenset res=200;elseif i = 30 thenset res=300;elseset res=400;end if;return res;
end //
delimiter ;

f1函数用于计算两个整数的和，f5 函数根据输入的整数返回不同的结果。

5.2 删除函数

如果不再需要某个自定义函数，可以使用 DROP FUNCTION语句将其删除，示例如下：

drop function func_name;

其中 func_name是要删除的函数名。

5.3 执行函数

可以通过以下方式执行自定义函数：

-- 获取返回值
select UPPER('xiao') into @res;
SELECT @res;-- 在查询中使用
select f1(11,nid) ,name from tb2;

上述代码展示了如何获取函数的返回值以及在查询中使用函数。

六、流程控制

MySQL 提供了多种流程控制语句，如条件语句和循环语句。

6.1 条件语句

以下是一个使用 IF语句的存储过程示例：

delimiter //
CREATE PROCEDURE proc_if ()
BEGINdeclare i int default 0;if i = 1 THENSELECT 1;ELSEIF i = 2 THENSELECT 2;ELSESELECT 7;END IF;END //
delimiter ;

该存储过程根据变量 i的值返回不同的结果。

5.2 循环语句

6.2.1 WHILE 循环

delimiter //
CREATE PROCEDURE proc_while ()
BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END //
delimiter ;

该存储过程使用 WHILE循环，当 num 小于 10 时，不断输出 num的值并将其加 1。

6.2.2 REPEAT 循环

delimiter //
CREATE PROCEDURE proc_repeat ()
BEGINDECLARE i INT ;SET i = 0 ;repeatselect i;set i = i + 1;until i >= 5end repeat;END //
delimiter ;

该存储过程使用 REPEAT 循环，先执行循环体，然后判断条件，直到 i 大于等于 5 时停止循环。

6.2.3 LOOP 循环

BEGINdeclare i int default 0;loop_label: loopset i=i+1;if i<8 theniterate loop_label;end if;if i>=10 thenleave loop_label;end if;select i;end loop loop_label;END

结语

通过这个21天的Python计划，我们涵盖了MySQL视图、触发器、存储过程、函数与流程控制。希望这些内容能帮助你更好地理解和使用Python。继续学习和实践，你将成为一名优秀的Python开发者！

📢 注意啦！文末有彩蛋！参与评论就有机会把这本好书抱回家～动动手指，说不定下个锦鲤就是你！赠书福利