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

佚名 6年前 (2019-05-17) Python 1473人围观抢沙发百度已收录

本文内容：

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

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

一、视图：

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

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

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

视图使用方法：

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

具体示例：

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

MySQL 之视图、触发器、事务、存储过程、内置函数、流程控制、索引 Python 第1张

/*
 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;

View Code

-- 创建视图
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语句，要么全部生效成功，只要有一个失败，就全部不生效不成功。（应用场景可以想象银行转账，双方必须都完成应该有的过程才能算转账成功，否则转账不成功。）
作用：保证事务内数据处理的同步性，让数据操作更具安全性。
事务四大属性：（需要重点记忆）

原子性：一个事务是不可分割的集合，其中包括的操作必须都成功，否则视为不成功
一致性：事务必须是使数据库从一个一致性状态变到另一个一致性状态，与原子性密切相关的
隔离性：多个事务直接互不干扰，也就是说事务内数据操作与另一事务内的数据操作是相互隔离的，并发执行的各个事务之间互不干扰。
持久性：永久性，事务如果提交，对数据库的改变是永久性的，接下来的其他操作货故障不会对其有任何影响。

使用实例：

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;

四、存储过程

简言之：将一些列的可执行的sql语句，封装为一个存储过程，存放于MySQL中，通过调用他的名字就可以执行其内部的一堆sql语句的目的。
在认识存储过程之前我们需要先了解下三种开发模型：

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