MySQL 之视图、 触发器、事务、存储过程、内置函数、流程控制、索引(一)

阅读目录

本文内容：

• 视图
• 触发器
• 事务
• 存储过程
• 内置函数
• 流程控制
• 索引

一、视图

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可。

如果要频繁使用一张虚拟表，可以不用重复查询

视图使用方法：

--------------------------------------------------------------------
	注：如果你对python感兴趣，我这有个学习Python基地，里面有很多学习资料，感兴趣的+Q群：895817687
-------------------------------------------------------------------

-- 将表1与表2通过on后面的条件进行内连接，产生的新表 就是我们创建的视图表
create view 视图表名 as
    select * from 表1 inner join 表2
        on 内连接条件

具体示例：

先建基础数据表及其记录（由于博客园暂找不到上传文件的地方，所以只能插入创建表的sql语句，将其复制粘贴到txt文档里面，最好是notpad++里面，然后存为sql文件，在Navicat里面导入就行了）

View Code

/*
 Navicat Premium Data Transfer

 Source Server         : sgt'mysql
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : day41

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 17/05/2019 14:54:11
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '三年二班');
INSERT INTO `class` VALUES (2, '三年三班');
INSERT INTO `class` VALUES (3, '一年二班');
INSERT INTO `class` VALUES (4, '二年九班');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `fk_course_teacher`(`teacher_id`) USING BTREE,
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '生物', 1);
INSERT INTO `course` VALUES (2, '物理', 2);
INSERT INTO `course` VALUES (3, '体育', 3);
INSERT INTO `course` VALUES (4, '美术', 2);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `fk_score_student`(`student_id`) USING BTREE,
  INDEX `fk_score_course`(`course_id`) USING BTREE,
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 53 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 10);
INSERT INTO `score` VALUES (2, 1, 2, 9);
INSERT INTO `score` VALUES (5, 1, 4, 66);
INSERT INTO `score` VALUES (6, 2, 1, 8);
INSERT INTO `score` VALUES (8, 2, 3, 68);
INSERT INTO `score` VALUES (9, 2, 4, 99);
INSERT INTO `score` VALUES (10, 3, 1, 77);
INSERT INTO `score` VALUES (11, 3, 2, 66);
INSERT INTO `score` VALUES (12, 3, 3, 87);
INSERT INTO `score` VALUES (13, 3, 4, 99);
INSERT INTO `score` VALUES (14, 4, 1, 79);
INSERT INTO `score` VALUES (15, 4, 2, 11);
INSERT INTO `score` VALUES (16, 4, 3, 67);
INSERT INTO `score` VALUES (17, 4, 4, 100);
INSERT INTO `score` VALUES (18, 5, 1, 79);
INSERT INTO `score` VALUES (19, 5, 2, 11);
INSERT INTO `score` VALUES (20, 5, 3, 67);
INSERT INTO `score` VALUES (21, 5, 4, 100);
INSERT INTO `score` VALUES (22, 6, 1, 9);
INSERT INTO `score` VALUES (23, 6, 2, 100);
INSERT INTO `score` VALUES (24, 6, 3, 67);
INSERT INTO `score` VALUES (25, 6, 4, 100);
INSERT INTO `score` VALUES (26, 7, 1, 9);
INSERT INTO `score` VALUES (27, 7, 2, 100);
INSERT INTO `score` VALUES (28, 7, 3, 67);
INSERT INTO `score` VALUES (29, 7, 4, 88);
INSERT INTO `score` VALUES (30, 8, 1, 9);
INSERT INTO `score` VALUES (31, 8, 2, 100);
INSERT INTO `score` VALUES (32, 8, 3, 67);
INSERT INTO `score` VALUES (33, 8, 4, 88);
INSERT INTO `score` VALUES (34, 9, 1, 91);
INSERT INTO `score` VALUES (35, 9, 2, 88);
INSERT INTO `score` VALUES (36, 9, 3, 67);
INSERT INTO `score` VALUES (37, 9, 4, 22);
INSERT INTO `score` VALUES (38, 10, 1, 90);
INSERT INTO `score` VALUES (39, 10, 2, 77);
INSERT INTO `score` VALUES (40, 10, 3, 43);
INSERT INTO `score` VALUES (41, 10, 4, 87);
INSERT INTO `score` VALUES (42, 11, 1, 90);
INSERT INTO `score` VALUES (43, 11, 2, 77);
INSERT INTO `score` VALUES (44, 11, 3, 43);
INSERT INTO `score` VALUES (45, 11, 4, 87);
INSERT INTO `score` VALUES (46, 12, 1, 90);
INSERT INTO `score` VALUES (47, 12, 2, 77);
INSERT INTO `score` VALUES (48, 12, 3, 43);
INSERT INTO `score` VALUES (49, 12, 4, 87);
INSERT INTO `score` VALUES (52, 13, 3, 87);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `fk_class`(`class_id`) USING BTREE,
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '男', 1, '理解');
INSERT INTO `student` VALUES (2, '女', 1, '钢蛋');
INSERT INTO `student` VALUES (3, '男', 1, '张三');
INSERT INTO `student` VALUES (4, '男', 1, '张一');
INSERT INTO `student` VALUES (5, '女', 1, '张二');
INSERT INTO `student` VALUES (6, '男', 1, '张四');
INSERT INTO `student` VALUES (7, '女', 2, '铁锤');
INSERT INTO `student` VALUES (8, '男', 2, '李三');
INSERT INTO `student` VALUES (9, '男', 2, '李一');
INSERT INTO `student` VALUES (10, '女', 2, '李二');
INSERT INTO `student` VALUES (11, '男', 2, '李四');
INSERT INTO `student` VALUES (12, '女', 3, '如花');
INSERT INTO `student` VALUES (13, '男', 3, '刘三');
INSERT INTO `student` VALUES (14, '男', 3, '刘一');
INSERT INTO `student` VALUES (15, '女', 3, '刘二');
INSERT INTO `student` VALUES (16, '男', 3, '刘四');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张磊老师');
INSERT INTO `teacher` VALUES (2, '李平老师');
INSERT INTO `teacher` VALUES (3, '刘海燕老师');
INSERT INTO `teacher` VALUES (4, '朱云海老师');
INSERT INTO `teacher` VALUES (5, '李杰老师');

-- ----------------------------
-- View structure for teacher2course
-- ----------------------------
DROP VIEW IF EXISTS `teacher2course`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `teacher2course` AS select `teacher`.`tid` AS `tid`,`teacher`.`tname` AS `tname`,`course`.`cid` AS `cid`,`course`.`cname` AS `cname`,`course`.`teacher_id` AS `teacher_id` from (`teacher` join `course` on((`teacher`.`tid` = `course`.`teacher_id`)));

SET FOREIGN_KEY_CHECKS = 1;

-- 创建视图
create view teacher2course as
select * from teacher inner join course
on teacher.tid=course.teacher_id

-- 查看创建的视图
select * from teacher2course 
tid tname    cid cname teacher_id
1   张磊老师   1   生物   1
2   李平老师   2   物理   2
3   刘海燕老师	3   体育   3
2   李平老师   4   美术   2

这里需要强调几点：

• 在硬盘中，视图只有表结构文件，没有表数据文件
• 视图通常是用于查询，尽量不要修改视图中的数据
• 删除视图代码：drop view teacher2course

思考：真实开发过程中是否会使用视图？
我们已经说过，视图是mysql的功能，这个功能主要用于查询，但是如果一个项目中使用了很多视图，那么如果项目某个功能需要修改的时候，就会需要对视图进行修改，这时候就需要在mysql端将视图进行修改，然后再去应用程序修改对应的sql语句，其实这就会导致一个跨部门沟通问题，部门与部门沟通并不是不可以，但是我们应该在软件代码层面上尽量减少这么沟通次数，因为一方面人与人之间的交往问题，另一方面也是项目扩展高效性的一方面考虑。一般程序扩展功能都是通过修改sql语句来完成的。（以上仅个人意见，欢迎交流）

二、触发器

• 定义：当对某张表的记录进行增、删、改的行为下，会满足这一行为条件后自动触发某一设定功能称之为触发器。
• 目的：触发器主要是专门针对我们队某一张表记录进行新增insert、删delete、改update的行为，这类行为一旦执行，就会满足触发器触发条件，即自动运行触发器设定的另一段sql语句。
• 如何创建触发器：

-- 针对插入时触发sql代码...
create trigger tri_after_insert_t1 after insert on 表名 for each row -- 插入后触发
BEGIN
    sql代码...
END

create trigger tri_before_insert_t2 before insert on 表名 for each row -- 插入前触发
BEGIN
    sql代码...
END
-- ------------------------------------------------------------------------------
-- 针对删除时触发sql代码...
create trigger 触发器名 after delete on 表名 for each row  -- 删除后触发
BEGIN
    sql代码...
END

create trigger 触发器名 before delete on 表名 for each row -- 删除前触发
BEGIN
    sql代码...
END
-- ------------------------------------------------------------------------------
-- 针对修改时触发sql代码...
create trigger 触发器名 after update on 表名 for each row  -- 修改后触发
BEGIN
    sql代码...
END

create trigger 触发器名 before update on 表名 for each row -- 修改前触发
BEGIN
    sql代码...
END

以上触发器的创建代码格式比较固定，只是分了6种情况而已

• 下面通过一个案例来进一步认识触发器：

-- 创建2张表
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')
    );

create table errlog(
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
    );

-- 创建触发器
delimiter // -- 将mysql默认的结束符换成//
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success='no' then -- 用NEW代表mysql捕获并封装成的新纪录对象
        insert into errlog(err_cmd,err_time) values (NEW.cmd,NEW.sub_time);
    end if;  -- if语句结束语
end // -- 前面讲结束符改为//。这里写上//代表触发器创建完毕，结束
delimiter ; -- 结束后记得将结束符改回默认；

-- 插入数据；
insert into cmd(user,priv,cmd,sub_time,success) values
    ('王大锤','0755','ls -l /etc',NOW(),'yes'), -- NOW（）代表获取当前时间
    ('孙大炮','0755','cat /etc/passwd',NOW(),'no'),
    ('李大大','0755','useradd xxx',NOW(),'no'),
    ('赵州桥','0755','ps aux',NOW(),'yes');
-- 向cmd表插入数据时候，触发器触发，会根据触发器内if条件语句判断是否决定插入错误日志

-- 查询errlog表记录，看看是否触发了触发器
select * from errlog;
-- 结果：
-- id      err_cmd                err_time
-- 1      cat /etc/passwd      2019-05-17 16:03:23
-- 2      useradd xxx          2019-05-17 16:03:23
-- 删除触发器
drop trigger tri_after_insert_cmd;

三、事务

• 简言之：多个sql语句执行生效的状态必须同步进行
  也就是说开启事务后，事务里的所有sql语句，要么全部生效成功，只要有一个失败，就全部不生效不成功。（应用场景可以想象银行转账，双方必须都完成应该有的过程才能算转账成功，否则转账不成功。）
• 作用：保证事务内数据处理的同步性，让数据操作更具安全性。
• 事务四大属性：（需要重点记忆）

1. 原子性：一个事务是不可分割的集合，其中包括的操作必须都成功，否则视为不成功

2. 一致性：事务必须是使数据库从一个一致性状态变到另一个一致性状态，与原子性密切相关的

3. 隔离性：多个事务直接互不干扰，也就是说事务内数据操作与另一事务内的数据操作是相互隔离的，并发执行的各个事务之间互不干扰。

4.持久性：永久性，事务如果提交，对数据库的改变是永久性的，接下来的其他操作货故障不会对其有任何影响。

• 使用实例：

create table user(
    id int primary key auto_increment,
    name char(32),
    balance int
    );

insert into user(name,balance) values
    ('李逍遥',1000),
    ('酒剑仙',1000),
    ('赵灵儿',1000);

-- 修改数据之前开启事务操作：
    start transaction;

    -- 修改操作
    update user set balance=900 where id=1;-- 买支付100元
    update user set balance=900 where id=2;-- 中介拿走10元
    update user set balance=900 where id=3;-- 卖家拿到90元

-- 查看修改后的表：
select * from user;
-- 结果
-- id    name    balance
-- 1    李逍遥    900
-- 2    酒剑仙    900
-- 3    赵灵儿    900
-- 注意注意
-- 事务下的sql语句执行完毕后并没有最终对数据库数据产生实质性改变，如果要
-- 产生最终结果生效，也就是数据真正地刷新到硬盘，就必须要执行一段提交的语句
-- 在执行提交语句前，进行的修改还可以还原，也就是sql回滚语句
rollback;
-- 再查看表：
select * from user;
-- 结果（数据还原了）
-- id    name    balance
-- 1    李逍遥    1000
-- 2    酒剑仙    1000
-- 3    赵灵儿    1000
-- 再次执行修改操作并提交：
-- 修改操作
    update user set balance=900 where id=1;-- 买支付100元
    update user set balance=900 where id=2;-- 中介拿走10元
    update user set balance=900 where id=3;-- 卖家拿到90元
commit;
select * from user;
-- 结果
-- id    name    balance
-- 1    李逍遥    900
-- 2    酒剑仙    900
-- 3    赵灵儿    900

-- 思考：
-- 如果站在python代码的角度，该如何实现检测事务内操作的全部成功性，不成功就回滚到前一个状态：
update user set balance=900 where id=1;-- 买支付100元
update user set balance=900 where id=2;-- 中介拿走10元
update user set balance=900 where id=3;-- 卖家拿到90元
if 检测到三方的余额都发生应该有的变化:
    commint;
else:
    rollblack;

四、存储过程

1. 简言之：将一些列的可执行的sql语句，封装为一个存储过程，存放于MySQL中，通过调用他的名字就可以执行其内部的一堆sql语句的目的。
2. 在认识存储过程之前我们需要先了解下三种开发模型：
3. 应用程序：只需要开发应用程序的逻辑 mysql：编写好存储过程，以供应用程序调用
   优点：开发效率高，执行效率高（因为我只需要负责应用程序逻辑层的问题，数据库层的有人帮我封装好了存储过程，我直接调用就行。）
   缺点：将开发应用分为2个部门，如果涉及到扩展情况，相关的存储过程需要修改的话，就需要与数据库部门产生沟通过程，考虑到人为因素，跨部门沟通等问题，综合性来说会导致扩展性变差。
4. 应用程序：两方面都会，既会开发应用程序的逻辑，又会写sql，写存储过程。 优点：比上一种方式在扩展性方面（非技术性上）更高
   缺点：开发效率和执行效率都不方第一种模型低，因为一个人2个方面的事都他干了，开发效率和执行效率能高吗！同时考虑到编写sql语句的复杂性，同时也要考虑到sql语句的优化问题，这些都涉及到术业有专攻的问题，最终还是会导致开发效率低的问题。
5. 应用程序：开发应用程序的逻辑，不需要写sql，而是基于别人编写好的框架来处理处理数据，ORM 。
   优点：不用像模型2那样编写sql，开发效率肯定比模型2高，同时兼容了2的扩展性高得好处 缺点：执行效率上面比较低，比2低。

创建存储过程：

delimiter //
create procedure p1(
    in m int,    -- in表示这个参数必须只能是传入不能被返回出去
    in n int,
    out res int) -- out表示这个参数可以被返回出去，还有一个inout表示传入传出可返回出去

begin
    select tname from teacher where tid > m and tid < n;
    set res = 0;
end //
delimiter ;

• 使用存储过程：

-- 直接在mysql中调用：

delimiter //
create procedure p1(
    in m int,    -- in表示这个参数必须只能是传入不能被返回出去
    in n int,
    out res int) -- out表示这个参数可以被返回出去，还有一个inout表示传入传出可返回出去

begin
    select tname from teacher where tid > m and tid < n;
    set res = 0;
end //
delimiter ;

-- 需要注意：存储过程在哪个数据库下创建的就必须只能在对应的数据库下面使用
-- 直接在mysql中调用
set @res=8 -- res是判断存储过程是否被执行成功的依据，在这里需要先定义一个变量@res=8
-- call p1(2,4,8); -- 如果直接传8则报错，必须传事先定义的变量@res，这是规定语法规范
call p1(2,4,@res);
-- tname
-- 刘海燕老师
--查看结果
select @res;  -- res=0
-- 存储过程被调用，@res变量值发生变化

-- 在python程序中调用：

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='sgtkey',
    database='day41',
    charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc('p1', (2, 4, 8))  # 这里就不需要提前定义个变量@res了，pymsql自动给你做了这件事
# 这里的内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=8;
print(cursor.fetchall())   # 结果 [{'tname': '刘海燕老师'}]
cursor.execute('select @_p1_2')
print(cursor.fetchall())   # 结果 [{'@_p1_2': 0}]

未完待续