MySQL 之视图、触发器、事务、存储

2023-01-31 00:01:34 视图事务触发器

本文内容：

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

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

一、视图：

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

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

视图使用方法：

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

具体示例：

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



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低。

创建存储过程：

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}]

-- 存储过程与事务使用的举例

delimiter //
create procedure p5(    -- 创建存储过程p5
    out p_return_code tinyint
)    
begin
   -- 这里表示如果捕获到异常，则执行下面set p_return_code = 1并且回滚操作
    declare exit handler for sqlexception
    begin
        -- ERROR
        set p_return_code = 1;
        rollback;
    end;
    -- 这里表示如果捕获sql警告，则执行下面set p_return_code = 2并且回滚操作
    declare exit handler for sqlwarning
    begin
        -- WARNINGS
        set p_return_code = 2;
        rollback;
    end;
    start transaction;    # 开始启用事务
        update user set balance=900 where id =1;
    update user set balance=1010 where id = 2;
    update user set balance=1090 where id =3;
    commit;   # 如果没有出现异常或者警告就会继续执行提交语句
    -- success
    set p_return_code = 0; -- 代表执行成功
end //
delimiter ;

五、函数

注意！
函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
若要想在begin...end...中写sql，请用存储过程

MySQL中提供了许多内置函数，例如：

一、数学函数
    ROUND(x,y)
        返回参数x的四舍五入的有y位小数的值
        
    RAND()
        返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。

二、聚合函数(常用于GROUP BY从句的SELECT查询中)
    AVG(col)返回指定列的平均值
    COUNT(col)返回指定列中非NULL值的个数
    MIN(col)返回指定列的最小值
    MAX(col)返回指定列的最大值
    SUM(col)返回指定列的所有值之和
    GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果    
    
三、字符串函数

    CHAR_LENGTH(str)
        返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ，则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接（自定义连接符）
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

    CONV(N,from_base,to_base)
        进制转换
        例如：
            SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
        例如：
            SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos：要替换位置其实位置
            len：替换的长度
            newstr：新字符串
        特别的：
            如果pos超过原字符串长度，则返回原字符串
            如果len超过原字符串长度，则由新字符串完全替换
    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)
        不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'
            
四、日期和时间函数
    CURDATE()或CURRENT_DATE() 返回当前的日期
    CURTIME()或CURRENT_TIME() 返回当前的时间
    DAYOFWEEK(date)   返回date所代表的一星期中的第几天(1~7)
    DAYOFMONTH(date)  返回date是一个月的第几天(1~31)
    DAYOFYEAR(date)   返回date是一年的第几天(1~366)
    DAYNAME(date)   返回date的星期名，如：SELECT DAYNAME(CURRENT_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的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
    NOW()    返回当前的日期和时间
    QUARTER(date)   返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
    WEEK(date)   返回日期date为一年中第几周(0~53)
    YEAR(date)   返回日期date的年份(1000~9999)
    
    重点:
    DATE_FORMAT(date,format) 根据format字符串格式化date值

       mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
       mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
       mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
        ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
       mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
        ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
       mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
       mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'
        
五、加密函数
    MD5()    
        计算字符串str的MD5校验和
    PASSWORD(str)   
        返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
        
六、控制流函数            
    CASE WHEN[test1] THEN [result1]...ELSE [default] END
        如果testN是真，则返回resultN，否则返回default
    CASE [test] WHEN[val1] THEN [result]...ELSE [default]END  
        如果test和valN相等，则返回resultN，否则返回default

    IF(test,t,f)   
        如果test是真，返回t；否则返回f

    IFNULL(arg1,arg2) 
        如果arg1不是空，返回arg1，否则返回arg2

    NULLIF(arg1,arg2) 
        如果arg1=arg2返回NULL；否则返回arg1        
        
七、控制流函数小练习
#7.1、准备表


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', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');

#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

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;

View Code

需要掌握函数：date_format

--date_format的一些应用：
-- 准备表和记录
create table emp_info(
    id int primary key auto_increment,
    name varchar(32),
    hiredate date);
    
insert into emp_info(name,hiredate) values
    ('sgt','2009-09-02'),
    ('wdc','2010-11-20'),
    ('qxl','2010-11-12'),
    ('ffz','2009-09-18'),
    ('yl','2014-06-16'),
    ('fqy','2014-06-05'),
    ('cnj','2012-04-28'),
    ('bvf','2009-09-18'),
    ('hge','2012-04-22');

-- 提取hire_date字段的值，按照格式化后结果年-月来分组。
select date_format(hiredate,'%Y-%m'),count(id) from emp_info
GROUP BY date_format(hiredate,'%Y-%m')
--结果
-- date_format(hiredate,'%Y-%m')    count(id)
-- 2009-09                          3
-- 2010-11                          2
-- 2012-04                          2
-- 2014-06                          2
-- 按时间分组，统计同一入职时间段的员工人数

关于函数的其他知识点可参考这篇博客

Http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2

六、流程控制

流程控制实际上就是在sql语句中使用if else、while循环的操作，不同的是语法格式有所不同

# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;

# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

七、索引和慢查询优化

首先我们要知道索引的存在感，数据库中的数据最终形式肯定是存在硬盘中，那么我们队数据库的操作不可厚非的都会进行IO操作
既然有IO操作，肯定会存在操作耗时问题，简单的单个或者多个数据在操作时候我们感觉不出来快慢或者耗时太少，但是现实工作中或者实际的项目的数据库中的数据可能非常的大，这样在对数据库数据操作的IO过程中就会无法忽视IO操作的耗时问题。
由于数据的IO操作导致MySQL的存储引擎有一套用于快速找到记录的一种数据结构，这个结构称之为--索引，在MySQL中也叫‘键’
- primary key
- unique key
- index key
- 注意，我们前面学的外键foreign key 不是用来加速查询的，不算索引，上面的三种key前两种出了有加速查询的作用，还有额外的约束条件（primary key：非空且唯一，unique key ：唯一），而index 没有任何约束功能，仅仅具有加速查询的作用。
所以我们可以总结一下：索引就是一种数据结构，类似于新华字典的最前面那几页的查询方法，按拼音还是按部首可以根据实际情况去选择，目的只有一个：加速我们找到一个汉字的具体位置，索引就是这样，也就是说我们在数据库操作数据应该都是先查找数据，怎么用最快的速度找到数据是个重要的问题，对该数据修改仅仅只算小问题了。
本质：通过不断缩小我们想要获取数据的查找范围，来筛选出最终我们想要的结果，把漫无目的的搜寻变成顺理成章的查找，有方法有目的性的去锁定我们需要的数据。
索引有优点肯定也有缺点：
1. 在表中有大量数据的时候，创建索引速度会很慢，同时索引也会占用数据空间
2. 在索引创建完成后，对表的查询性能会大幅度提升，但是写的能力会降低
b+树

上图，就是b+树的结构图，只有最下面的叶子节点是存放真实数据的，根和树枝节点存的仅仅是虚拟的数据
查询次数主要由树的层数决定，也就是说层数越少查找次数越少
同时一块磁盘块的大小也是有限制的，也就是说叶子节点处的磁盘快存放的数据应该是那种特别小的单位。
要想降低树的层级高度，我们应该在一张表中建立一个主键id字段，通过该唯一的主键字段来锁定到我们想要的数据。
聚集索引（primary key）
聚集索引其实指的就是表的主键，innodb存储引擎规定一张表中必须要有主键，在建表时候会在硬盘产生2个文件，一个是表结构frm文件，一个是ibd文件，这里面就存着记录数据和索引数据。而myisam存储引擎在建表的时候会在硬盘中产生3个文件，一个表结构frm文件，一个MYD文件存记录，一个MYI索引文件专门来存索引。
辅助索引（unique，index）
查询数据的时候不可能都是用id作为查询筛选条件，也会用到其他字段名作为查询条件，name这个时候无法利用到聚集索引来加速查询，就需要给其他字段建立索引，这些索引就叫做辅助索引。
特点：叶子节点存放的是辅助索引字段对应的那条记录的主键的值，比如辅助索引是name这个字段，那么叶子节点存放的是name这个字段对应的id主键值。
```
-- 覆盖索引
select name from user where name = 'sgt';
此语句叫做覆盖索引
只在辅助索引的叶子节点中就找到了我们想要的数据记录

-- 非覆盖索引
select age from user where name = 'sgt';
此语句叫非覆盖索引，虽然查询的时候用的是name索引字段，但是要查询的是age字段。
```
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
以下是了解内容，有兴趣可以测试下

索引的测试

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明 分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

慢查询日志

设定一个时间检测所有超出改时间的sql语句，然后针对性的进行优化！

相关文章

MySQL 之视图、 触发器、事务、存储