MySQL存储过程、函数、视图、触发器、索引和锁的基本知识

MySQL存储过程、函数、视图、触发器、索引和锁的基本知识

高山仰止

• 了解视图的使用
• 了解存储过程、函数的创建和使用
• 了解触发器的创建和使用
• 了解MySQL常见的存储引擎和它们的特点
• 掌握创建索引的方式
• 理解MySQL锁机制、死锁的概念

第一章-视图

环境准备

-- 一个放款流水表
CREATE TABLE t_pay_flow (
	id INT (11) PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR (120),
	pay_amt DOUBLE ,
	STATUS INT (11),
    insert_date DATETIME,
    update_date DATETIME
); 
INSERT INTO t_pay_flow  VALUES(NULL, 'zs','1000','1', NOW(), NOW());
INSERT INTO t_pay_flow  VALUES(NULL, 'ls','100000','0', NOW(), NOW());
INSERT INTO t_pay_flow  VALUES(NULL, 'ww','2000','1', NOW(), NOW());
INSERT INTO t_pay_flow  VALUES(NULL, 'zl','3000','0', NOW(), NOW());

-- 一个还款流水表
CREATE TABLE t_repay_flow (
	id INT (11) PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR (120),
	repay_amt DOUBLE ,
	STATUS INT (11),
    insert_date DATETIME,
    update_date DATETIME
); 
INSERT INTO t_repay_flow  VALUES(NULL, 'zs','1000','1', NOW(), NOW());
INSERT INTO t_repay_flow  VALUES(NULL, 'ls','100000','0', NOW(), NOW());
INSERT INTO t_repay_flow  VALUES(NULL, 'ww','2000','1', NOW(), NOW());
INSERT INTO t_repay_flow  VALUES(NULL, 'zl','3000','0', NOW(), NOW());

知识点-视图概述

1.高山

• 理解视图的概念、作用

2.演绎

• 视图的概念
• 视图的作用

3.攀登

3.1 视图的概念

视图存储了查询，当调用的时候会生成查询语句对应的结果集，一个视图可以看成是一个虚拟的表。

使用视图和使用表的方式是一样的。

视图和表同一级别，也属于数据库。

3.2 视图的作用

• 便捷性
  • 可以把经常使用的需要查询比较复杂的业务的SQL语句创建为一个视图，这样可以在获取这些信息的时候直接从视图中获取即可。
• 安全性
  • 视图往往只有一部分数据，我们还可以将重要的数据字段不在视图中展示，这样对视图的操作较难影响重要数据。
• 独立性
  • 原表增加列不影响视图的使用

4.回望

• 视图是什么？
• 视图的作用？

知识点-视图的CRUD

1.高山

• 掌握视图的CRUD操作

2.演绎

• 创建视图
• 修改视图
• 查看视图
• 对视图数据进行操作
• 解决视图数据操作影响基表的问题
• 删除视图

3.攀登

3.1 创建视图

视图可以从各式各样的查询语句中创建，视图可以引用基表或者其它视图进行创建。

可以使用 连接、union、子查询等。

3.1.1 创建视图语法

create [or replace] view 视图名 as select语句;

如果指定or replace，则会替换已经存在的视图。

注意： 视图和表共享数据库中相同的名称空间，视图名不能和表名相同。

注意：视图创建语句中的select语句不能将子查询作为表来使用。

3.1.2 创建单表视图

练习1： 创建一个视图，表示id为1用户的放款流水信息

create view v_zs_flow as select * from t_pay_flow where id = 1;

2.1.3 创建多表视图

练习2：创建一个包含用户信息以及成绩的视图

create view v_user_score as
select * from t_user as u, t_score_math as s where u.uname = s.sname;

3.2 修改视图

3.2.1 修改视图语法

-- 将视图改为新的select语句
alter view 视图名 as select语句;
-- 等效于以下语句, 存在则重新创建
create or replace view 视图名 as select 语句;

修改视图即替换了原有的视图。

练习1： 将视图v_zs_flow修改为id为2用户的放款流水信息

alter view v_zs_flow as select * from t_pay_flow where id = 2;

3.3 查看视图

可以查看视图的定义结构：

SHOW CREATE VIEW 视图名;

练习1： 查看视图v_zs_flow的定义结构

show create view v_zs_flow;

3.4 对视图数据进行操作

视图可以看成一个虚拟的表，所以可以像对表操作一样操作视图。

注意： 单表的视图进行数据修改会影响原表的数据

3.4.1 从视图中查询数据

练习1： 从视图v_zs_flow中获取所有数据信息

select * from v_zs_flow;

练习2：往视图v_zs_flow中插入姓名为lsm、金额为1000、status为1 的记录

发现基表的数据已经发生了改变，插入了一条数据：

练习3：将视图v_zs_flow的id为2的数据的金额改为500

update v_zs_flow set pay_amt = 500  where id = 2;

执行后发现基表中id为2的数据的金额也被改为500了。

练习4： 删除v_zs_flow中id为2的用户

DELETE FROM v_zs_flow WHERE id = 2;

执行后，发现基表中id为2的用户数据也被删除了。

3.5 解决视图数据操作影响基表的问题

对视图数据的操作影响了基表，如何来解决这个问题呢？

我们可以在创建视图的时候指定检查方式，从而禁止修改基表。

create view 视图名 as
select语句
-- 增加检查动作约束
with [cascade | local] check option;

默认是cascade，会对基表影响，可以指定为local选项。

可以修改视图v_zs_flow的定义：

alter view v_zs_flow as select * from t_pay_flow where id = 1
with local check option
;

此时对视图做delete操作：

DELETE FROM v_zs_flow ;

但是基表t_pay_flow的数据还在。

3.6删除视图

删除视图也是使用drop关键字，语法：

drop view 视图名;

练习1： 删除视图v_zs_flow

drop view v_zs_flow;

3.7 视图的应用场景

• 信息保密
  • 比如员工的薪资，希望财务人员能看到薪资，其他部门的人看不到薪资。就可以使用视图来显示数据。
• 简化sql的编写
  • 有些固定业务的查询sql很复杂很长（比如达到几十条语句），这时候可以使用一个视图，业务查询的时候直接查询视图即可。极大的缩减了查询语句的编写工作量。

4. 回望

• 视图的CRUD操作
• 对视图数据操作不影响基表的解决方案

更多精彩请访问本文源地址： https://blog.csdn.net/zixiao217

第二章-存储过程和函数

知识点-存储过程和函数的介绍

1.高山

• 了解存储过程、函数的概念

2.演绎

• 存储程序介绍
• 存储过程、函数区别

3.攀登

3.1 存储过程、函数介绍

​ 存储过程是可以被存储在服务器中的一套SQL语句，通常和业务密切相关。一旦它被存储了，客户端不需要再重新发布单独的语句，而是可以引用存储过程来替代，类似于编译好的程序代码块。

下面一些情况下存储过程尤其有用：

• 当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。
• 安全极为重要之时。比如，银行对所有普通操作使用存储过程。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储过程。
• 一般用于一些长时间的批量业务处理，比如金融业凌晨账单跑批，批量对账等功能 函数也是一套既定的SQL语句的组合，但是一般用于实现通用的功能，通常与业务无关。

3.2 存储过程和函数的区别

存储过程和函数本质上没区别，都属于存储程序。

• 返回值的限制： 函数只能返回一个变量的限制、存储过程可以返回多个。
• 使用方式限制：函数是可以嵌入在sql中使用的,可以在select中调用、而存储过程不行。存储过程一般是作为一个独立的部分来执行（CALL执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
• 应用场景区别：存储过程一般针对于特定的业务处理，而函数的实现的通用性较强。
• 关键字不一样，存储过程使用 procedure、函数使用 function。

4.回望

知识点-存储过程的CRUD

1.高山

2.演绎

3.攀登

3.1 创建存储过程、函数

3.1.1 创建存储过程的语法

CREATE PROCEDURE 过程名 ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter参数列表形式：

 [in | out  | inout] 参数名 类型,[in | out  | inout] 参数名 类型 ...
 -- 其中 in、out、inout 分别表示参数是 输入参数、输出参数、既是输入参数又是输出参数 

characteristic 表示存储过程特征:

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

常见特性的说明：

• CONTAINS SQL : 不包含读或者写的SQL语句
• NO SQL ： 不包含SQL语句
• READS SQL DATA ：包含读的语句，但不包含写的语句
• MODIFIES SQL DATA ： 包含写的语句
• SQL SECURITY ： 定义了权限
  • 值有 DEFINER、INVOKER 用于指定安全上下文，指定了谁命名谁调用该存储过程。
  • 如果DEFINER 指定了，则用户的形式必须是mysql用户的形式如： ‘user_name’@‘host_name’ 、CURRENT_USER 或者 CURRENT_USER() 。
  • 如果省略了 DEFINER ，则默认用户为执行 CREATE PROCEDURE 语句的用户，即等同于DEFINER = CURRENT_USER。

存储过程执行使用 call，存储过程定义几个参数就需要传入几个。

routine_body 包含合法的SQL过程语句体。

练习1： 创建一个存储过程，用来获得t_user表的用户数

DELIMITER $$

CREATE PROCEDURE p_user_cnt (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t_user;
END $$

DELIMITER ;

调用查看结果：

 CALL p_user_cnt(@a)
 SELECT @a 

说明： delimiter命令可以用来改变查询结尾定界符;（比如改变为//、$$ 等）使得; 可被用在子程序体中。

用户自定义的变量使用 @变量名

3.1.2 创建函数的语法

CREATE FUNCTION 函数名 ([func_parameter[,...]]) RETURNS type   
[characteristic ...] routine_body

func_parameter形式：

参数名 类型, 参数名 类型...

函数需要 RETURNS 指定返回的类型。

routine_body 包含合法的SQL过程语句体。

练习2：创建一个函数，用来返回两个参数的连接字符串

CREATE FUNCTION f_concat(param1 VARCHAR(20), param2 VARCHAR(10)) RETURNS VARCHAR(40) 
RETURN CONCAT(param1, param2)

使用：

SELECT f_concat('ni', 'hao')   -- nihao

注意： 无论是存储过程还是函数定义，由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数 默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或 INOUT

注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）

RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

3.2 routine_body 包含的SQL过程语句

存储过程、函数可能都会包含较多的sql语句，这其中可能涉及到其他一些语法。

3.2.1 CALL语句

格式：

CALL sp_name([parameter[,...]])

可以调用存储过程。

练习： 调用 存储过程 p_user_cnt

CALL p_user_cnt(@a)

3.2.2 BEGIN … END复合语句

格式：

[begin_label:] BEGIN
    [statement_list]
END [end_label]

可以使用BEGIN … END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

delimiter命令可以用来改变查询结尾定界符;（比如改变为//、$$ 等）使得; 可被用在子程序体中。

3.2.3 DECLARE语句

DECLARE仅被用在BEGIN … END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

3.2.4 存储程序中的变量

DECLARE局部变量

• DECLARE var_name[,...] type [DEFAULT value] 这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。 局部变量的作用范围在它被声明的BEGIN … END块内。它可以被用在嵌套的块中，除了那些用相同名字 声明变量的块。

变量SET语句

SET 变量名= 值表达式 被参考变量可能是子程序内声明的变量，或者是全局变量，多个可以使用逗号分隔开。

SELECT … INTO语句给变量赋值

SELECT col_name[,...] INTO var_name[,...] from 表

表示将查询结果赋值给对应的变量。

3.2.5 DECLARE处理程序

处理程序的含义是： 通过这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。 (类似于java的异常捕获处理)

语法如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
 
handler_type:
    CONTINUE
  | EXIT
  | UNDO
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

对一个CONTINUE类型的处理程序，当前子程序在执行 处理程序语句之后继续。对于EXIT处理程序，当前BEGIN…END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。

• SQLWARNING是对所有以01开头的SQLSTATE代码的速记。
• NOT FOUND是对所有以02开头的SQLSTATE代码的速记。
• SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。

除了SQLSTATE值，MySQL错误代码也被支持。

示例：

CREATE TABLE t (s1 int,primary key (s1));
 
delimiter //
 
CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t VALUES (1);
SET @x = 2;
INSERT INTO t VALUES (1);
SET @x = 3;
END;
//
 
CALL handlerdemo()//
 
SELECT @x//  -- 结果为3

说明： 注意到，@x是3，这表明MySQL被执行到程序的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1; 这一行不在，第二个INSERT因PRIMARY KEY强制而失败之后,MySQL可能已经采取 默认(EXIT)路径，并且SELECT @x可能已经返回2。

3.2.6 游标

游标可以看成是一个select语句的指针。

游标的一个常见用途就是保存查询结果，以便以后使用。

游标的结果集是由SELECT语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

游标使用步骤:

1. 声明游标
2. 打开游标
3. 从游标中获取数据
4. 关闭游标

3.2.6.1 声明游标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。SELECT语句不能有INTO子句。

3.2.6.2 游标OPEN语句

OPEN cursor_name

这个语句打开先前声明的光标。

3.2.6.3 游标FETCH语句

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针，并且赋值给变量。

3.2.6.4 游标CLOSE语句

CLOSE cursor_name

这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

3.2.6.5 游标练习示例

-- 一个小额放款流水表
CREATE TABLE t_lowpay_flow (
	id INT (11) PRIMARY KEY AUTO_INCREMENT,
	uname VARCHAR (120),
	pay_amt DOUBLE ,
	STATUS INT (11),
    insert_date DATETIME,
    update_date DATETIME
); 

/*
编写一个存储过程，从t_pay_flow表中获取，金额小于1000的全部重新写入t_lowpay_flow表
*/
delimiter $$
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a varchar(40);
  DECLARE b double;
  -- 声明游标
  DECLARE cur1 CURSOR FOR SELECT uname, pay_amt FROM t_pay_flow;
  -- 声明一个处理程序
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  -- 打开游标
  OPEN cur1;
 
  REPEAT
    FETCH cur1 INTO a, b;
    IF NOT done THEN
       IF b < 1000 THEN
          INSERT INTO t_lowpay_flow(uname, pay_amt) VALUES (a,b);
       END IF;
    END IF;
  UNTIL done END REPEAT;
 
  CLOSE cur1;
END $$
delimiter ;

-- 执行并查看小额借款标
CALL curdemo();
select * from t_lowpay_flow;

3.2.7 流程控制语句

SQL也有类似java一样的流程控制语句，主要有以下几种：

• IF语句
• CASE语句
• LOOP语句
• LEAVE语句
• ITERATE语句
• REPEAT语句
• WHILE语句

3.2.7.1 if语句

if语句用于条件判断，其完整语法如下(和java里的 if..else if...else if...else 类比)：

IF 条件 THEN 语句列表;
    [ELSEIF 条件 THEN 语句列表]; ...
    [ELSE 语句列表];
END IF;

练习： 使用if语句判断：如果还款金额大于等于借款金额，就认为结清欠款。

DELIMITER $$
CREATE PROCEDURE p_check_pay_status(OUT msg VARCHAR(40))
BEGIN
DECLARE payamt DOUBLE;
DECLARE repayamt DOUBLE;
-- declare msg varchar(40);
SELECT pay_amt, repay_amt INTO payamt, repayamt 
	FROM t_pay_flow AS p, t_repay_flow  AS r WHERE p.uname = r.uname AND p.uname = 'zs';

IF repayamt >= payamt THEN SET msg = '结清欠款';
ELSE SET msg = '尚有欠款';
END IF;

END $$

-- 执行并获取结果
CALL p_check_pay_status(@a);
SELECT @a;

3.2.7.2 case语句

case语句和case函数(回顾一下case函数)大同小异，只是结束需要使用 end case，具体语法如下：

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

-- 或者

CASE
    WHEN search_condition THEN statement_list;
    [WHEN search_condition THEN statement_list]; ...
    [ELSE statement_list];
END CASE;

练习： 如果借款金额小于1000，则显示小额借款，否则显示普通借款

DELIMITER $$
CREATE PROCEDURE p_check_pay_status(OUT msg VARCHAR(40))
BEGIN
DECLARE payamt DOUBLE ;
SELECT pay_amt INTO payamt
	FROM t_pay_flow AS p WHERE p.uname = 'zs';
	
CASE 
	WHEN payamt < 1000 THEN SET msg = '小额借款';
	ELSE SET msg = '普通借款';
END CASE; 
END $$
DELIMITER ;

-- 执行并获取结果
CALL p_check_pay_status(@a);
SELECT @a;

3.2.7.3 LOOP语句

LOOP是循环语句，语法如下：

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。

LOOP语句可以被标记。如果没有指明begin_label，则end_label不能有；如果两者都出现，它们必须是同样的。

3.2.7.4 LEAVE语句

leave 语句用来退出流程控制语句体。语法如下：

LEAVE label

这个语句被用来退出任何被标注的流程控制构造。它和BEGIN … END或循环一起被使用。

练习: 结合loop和leave编写一个求1-100的累加循环的函数

DELIMITER $$
CREATE FUNCTION fun_sum( start_num INT ,  end_num INT)
RETURNS INT
BEGIN 
DECLARE result INT DEFAULT 0; -- 注意，如果没有指定默认值，则为null
DECLARE cnt INT DEFAULT start_num;

sumlabel: LOOP
	IF cnt > end_num THEN LEAVE sumlabel;
	ELSE SET result = result + cnt, cnt = cnt + 1;
	END IF;
END LOOP sumlabel;
RETURN result;
END $$

DELIMITER ;

-- 测试结果为 5050
SELECT fun_sum(1, 100)

3.2.7.5 ITERATE语句

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：再次循环(类似于java的continue)。语法如下：

ITERATE label

示例：

DELIMITER $$
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END $$
DELIMITER ;

-- 执行查看结果为 10
CALL doiterate(1)
SELECT @x

3.2.7.6 REPEAT语句

REPEAT语句内的语句或语句群被重复，直至search_condition 为真。

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT 语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

示例：

delimiter //
 
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT 
	SET @x = @x + 1; 
UNTIL @x > p1 
END REPEAT;
END
//
delimiter ;
 
CALL dorepeat(1000);

 SELECT @x; -- 得到1001

3.2.7.7 WHILE语句

WHILE语句内的语句或语句群被重复，直至search_condition 为真。语法如下：

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

示例：

delimiter $$
CREATE PROCEDURE dowhile(out result int)
BEGIN
  DECLARE v1 INT DEFAULT 5;
 
  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
  set result = v1;
END $$
delimiter ;

-- 执行存储过程
CALL dowhile(@res);
-- 获取输出参数
SELECT @res;

3.3 修改存储过程、函数

修改存储过程、函数 使用 alter 语句：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]

说明，修改只能修改特征，并不能修改内容，如果要修改存储过程的内容，需要先删除再重建。

characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

3.4 查看存储过程、函数定义结构

可以使用show来查看存储过程、函数定义结构

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

练习： 查看存储过程p_user_cnt、函数f_concat的定义结构：

show create procedure p_user_cnt;
show create function f_concat;

3.5 删除存储过程、函数

删除存储过程、函数使用 drop 关键字：

drop {PROCEDURE | FUNCTION}   sp_name;

练习: 删除存储过程p_user_cnt、函数f_concat

drop procedure p_user_cnt;
drop function f_concat;

4.回望

• 创建存储过程、函数的语法
• 删除存储过程、函数
• 执行存储过程、函数的方式

第三章-触发器

知识点-触发器介绍

1.高山

• 了解触发器的作用

2.演绎

• 触发器概述
• 触发器作用

3.攀登

3.1 触发器概述

触发器是数据库中与表相关的对象，当表的一个特定事件发生时，触发器就会被激活。

触发器一般定义在关联表进行 insert、update 或者 delete 操作的时候激活。这些行操作都是触发事件。

例如，可以通过 insert 或者 load data 语句进行插入数据，此时一个insert 触发器就可以被插入的每一行给激活。

另外，还可以把触发器设置在事件之前、之后触发。

例如，你可以设置一个触发器在每一行被insert进表之前 和 每一行被update之后触发。

3.2 触发器作用

触发器可以用于对表中插入某个值、或者修改某个值的时候进行一些检查动作。

4.回望

• 触发器概念
• 触发器作用

知识点-触发器的CRUD

1.高山

• 能够创建、删除触发器

2.演绎

• 创建触发器
• 删除触发器

3.攀登

3.1 创建触发器

语法：

create trigger 触发器名 触发时机 触发事件
on 表名 for each row 触发器语句

说明：

1. 表名：必须为永久性表，不能为临时表、视图；
2. 触发时机：可以是 before、after，标明触发器是在激活它的语句之前还是之后执行；
3. 触发事件：执行触发语句的类型，可以是：
   • insert : 插入新数据的时候激活，insert、load data 和 replace语句
   • update ： 更改某一行时激活，update操作
   • delete ： 从表中删除数据激活， delete 或者 replace语句
4. 触发器语句： 是当触发程序激活时执行的语句。如果触发器语句较多，可以使用 BEGIN … END复合语句结构。

注意事项： 对于一张表，不能有两个 before update 的触发器； 但是可以有 一个 before insert 和 一个 before update 触发器。

练习1：给t_user表增加一个触发器，当往表里插入一条数据后，也往t_score_math表插入一条用户名相同，成绩为0的数据。

DELIMITER $$
CREATE TRIGGER tri_after_insert AFTER INSERT
ON t_user FOR EACH ROW 
BEGIN
INSERT INTO t_score_math VALUES(NEW.uname, 0);
END; $$
DELIMITER ;

说明: NEW、OLD 是mysql的扩展语法，在触发器里分别表示，新行、旧行。

并且对于 insert 型触发器，只能使用 NEW；对于 delete 型触发器，只能使用 OLD； 对于 update 型则可以使用 NEW、OLD。

3.2 查看触发器

可以使用show命令查看触发器定义结构：

SHOW CREATE TRIGGER 触发器名;

练习2：查看触发器的定义结构

show create trigger tri_after_insert;

3.3 修改触发器

并没有专门的类似 alter 的语法，修改触发器可以先删除再创建。

3.4 删除触发器

删除触发器的语法：

drop trigger 触发器名;

练习3： 删除触发器 tri_after_insert

drop trigger tri_after_insert;

4.回望

• 创建触发器
• 删除触发器

第四章-MySQL高级特性

知识点-MySQL引擎

1.高山

• 了解MySQL常见引擎以及各自特点

2.演绎

• 存储引擎概述
• 存储引擎的使用
• InnoDB引擎

3.攀登

3.1 MySQL存储引擎

存储引擎是MySQL的组件，用于处理不同类型表的SQL操作，不同的存储引擎数据存储的结构是不一样的。

InnoDB 是默认的存储引擎，也是oracle公司首推的通用存储引擎，除非你需要处理特定的案例的时候则可以选择其他存储引擎。 create table 语句默认使用InnoDB存储引擎。

MySQL使用可插拔性的存储引擎架构，以便可以在运行中的MySQL服务中装载或者卸载指定的存储引擎。

MySQL插件式存储引擎的体系结构:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o84zPy4U-1571814025255)(img\1571639095619.png)]

SHOW ENGINES语句可以查看你当前的MySQL服务支持哪些存储引擎。

3.1.1 常见的存储引擎

• InnoDB: 默认的存储引擎；InnoDB是一个事务安全的存储引擎，支持事务，拥有commit、rollback、崩溃恢复等机制保障用户数据。InnoDB行级锁和oracle风格的行读取无锁提升了读写性能。InnoDB将用户数据存储在聚集索引中，来减少基于主键的通用查询I/O开销。为了维护数据集成，InnoDB也支持FOREIGN KEY外键引用。
• MyISAM：这类表占空间较少。表级锁 限制了读写性能，所以常用来建设只读、或者大部分情况下都是读居多的表。
• Memory：将所有的数据存储在RAM，主要用于在需要快速访问得到快速响应的环境中。该引擎以前也被称作HEAP引擎。但是这个引擎的使用场景已经越来越少了。
• CSV： 该引擎的表是真正的文本文件，数据以逗号,分隔开。CSV引擎的表可以让你以CSV格式进行数据的导入、转储。因为CSV文件没有索引，所以通常情况在做一些正常操作时是以InnoDB存储的，只是在导入、导出时使用CSV。

3.1.2 各存储引擎的特性

你不需要受限于整个数据库都是用同一种数据库，可以针对单独的表使用不同的存储引擎。

例如，大都数情况下表都是InnoDB存储引擎的，有一个表使用的是CSV存储引擎，用于导出数据使用，一些表使用的是MEMORY存储引擎用于临时工作空间。

下表列出了各个存储引擎的相关特性，根据这些特性，可以根据自身的业务表选择合适的存储引擎。

3.2 设置存储引擎

3.2.1 查看存储引擎

可以使用SHOW ENGINES语句来查看你当前的MySQL服务支持哪些存储引擎。

还可以使用 SHOW VARIABLES LIKE '%storage_engine%'; 语句来查看当前DBMS中默认的存储引擎。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o7yH50Kq-1571814025256)(img\1571639694227.png)]

3.2.2 设置表的存储引擎

在创建一张新表时，你可以通过添加ENGINE操作在CREATE TABLE语句中来指定表的存储引擎。

-- 设置InnoDB存储引擎
CREATE TABLE t1(i int ) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

如果你忽略了ENGINE操作，就会使用默认的存储引擎。MySQL目前默认使用的是InnoDB。

3.2.3 设置全局默认的存储引擎

1. 你也可以通过在配置文件my.ini或者my.conf中指定default-storage-engine=INNODB来修改默认的存储引擎。
2. 你也可以通过设置default_storage_engine变量来指定当前会话的默认存储引擎：

set default_storage_engine = INNODB;

3.2.4 修改表的存储引擎

修改表的存储引擎，可以使用ALTER TABLE语句：

ALTER TABLE 表名 engine = 引擎名;

3.3 InnoDB 存储引擎

InnoDB存储引擎是MySQL默认的。

InnoDB存储引擎支持 事务、B树索引、哈希索引、行级锁、外键、MVCC（多版本并发控制）等特性。

3.2.1 名词介绍

• 哈希索引
  • 是索引的一种存储形式，类似于哈希表，key-value的形式，速度很快，但是不支持范围查找。
• MVCC
  • 多版本并发控制，采用了一个快照版本的数据来供当前事务使用，也是可重复读隔离级别用来解决不可重复读问题的底层方案。
• B树索引
  • 是索引的一种存储形式，目前InnoBD广泛采用的是B+树存储。

3.2.2 InnoDB 和 MyISAM 的比较

对于 InnoDB 和 MyISAM ，我们可以从以下几个方面进行比较：

1. 特性差异
   • InnoDB支持事务、B树索引、哈希索引、行级锁、外键、MVCC（多版本并发控制）等特性。
   • MyISAM 不支持事务；不支持外键；不支持行级锁。
2. 锁差异
   • InnoDB 支持表级锁、行级锁
   • MyISAM 仅仅支持表级锁
3. 数据文件差异
   • InnoDB 属于索引组织表，一般而言采用多表空间存储方式，每个表都有一个 .ibd 文件对应，用来存储表数据＋索引。
   • MyISAM属于堆表，在磁盘存储有3个文件：
     • .frm 存储表的定义
     • .MYD 存放数据
     • .MYI 存放表索引
4. 索引差异
   • InnoDB
     • InnoDB 的数据是主索引的一部分，附加索引保存的是主索引的值。
     • InnoDB 表如果没有主键，则会自动生成一个用户不可见的主键（6个字节）
     • InnoDB 索引和数据一块保存在表空间里
   • MyISAM
     • MyISAM 允许没有任何索引和主键存在。
     • MyISAM 的索引保存行的地址

对于 InnoDB 和 MyISAM，我们如何选择呢？

你可以从你的需求出发：

• 是否需要外键？需要则选用 InnoDB。
• 是否需要事务支持? 支持则选用 InnoDB。
• 是否需要全文索引？ 支持则选用InnoDB。
• 经常使用什么查询方式？基于主键还是统计count()较多，前者才建议采用InnoDB。
• 你的数据有多大？ 数据量大可以使用InnoDB，在数据恢复的时候可以利用其事务日志进行更快的恢复。

4.回望

• 存储引擎的概念
• 常用的存储引擎
• InnoDB的特点

知识点-MySQL索引

1.高山

• 了解索引的概念以及创建索引

2.演绎

• 索引介绍
• 索引的创建
• 删除索引

3.攀登

3.1 索引介绍

3.1.1 索引概述

在数据库中进行查询操作的时候，经常需要查找特定的数据，例如： 执行 select * from t_user where id = 100000 这样的语句，mysql会从第一条记录开始遍历查找，直至找到这样的数据，显然效率是很低下的。

MySQL可以通过增加索引的方式来加快对数据表的查询操作。

索引属于表，和列是同一层次的对象结构。通过索引，我们可以提升查询操作的性能。

注意事项: 索引虽然可以提升查询效率，但是会占用存储空间，随数量的增加而增加索引存储空间，所以需要综合考虑索引的优缺点。

3.1.2 索引分类

索引又分为以下几种：

• 普通索引
  • 普通索引由 key 或者 index 来定义，是mysql中的基本索引类型。
• 唯一索引
  • 唯一索引是由 unique 指定的索引，该索引字段必须是唯一的。
• 全文索引
  • 全文索引使用 fulltext定义，只能创建类型为 char、varchar 或者 text 的字段。旧版的MySQL全文索引只有在MyISAM实现了，新版的MySQL5.6.24上InnoDB引擎也加入了全文索引。
• 单列索引
  • 索引列为单个字段，类型可以是普通、唯一以及全文索引。
• 多列索引（联合索引）
  • 索引列为多个字段，类型可以是普通、唯一以及全文索引。一般而言，查询条件使用了第一个字段时才会生效。

3.2 索引的创建

创建索引有3种方式。

3.2.1 在建表时同时指定索引

create table 表名(
	列 类型 [约束],
    列 类型 [约束],
    [unique | fulltext ] index index_name(列, [列,...])
);

练习1： 创建一张员工表，主键id自增长、姓名、年龄、手机号，并且给手机号添加唯一索引

create table t_emp(
	id int primary key auto_increment,
    name varchar(40),
    age int,
    phone char(11),
    unique index idx_phone(phone)
);

3.2.2 建表后使用创建索引的语法

create index index_name on 表名(列);

练习2： 给员工表的姓名增加一个索引

create index idx_name on t_emp(name);

3.2.3 建表后对表进行修改的方式

alter table 表名 add index index_name(列);

练习3：给员工表创建基于姓名、手机号的多列索引

alter table t_emp add index idx_u(name, phone);

3.3 删除索引

由于索引会占用磁盘空间，所以对于不再使用的索引，应该及时删除。索引删除有2种方式：

1. 通过修改表语法删除索引

alter table 表名 drop index 索引名;

练习1： 删除员工表的姓名索引

alter table t_emp drop index idx_name;

1. 删除索引语法来进行删除

drop index 索引名 on 表名;

练习2：删除员工表的年龄所

4.回望

• 索引的作用
• 创建索引
• 删除索引

知识点-MySQL查询缓存

1.高山

• 理解查询缓存的作用

2.演绎

• 查询缓存介绍
• 查询缓存使用

3.攀登

3.1 查询缓存介绍

3.1.1 查询缓存概述

我们可以使用查询缓存，可以提升查询效率。

一般而言，一条SQL语句的执行有这么几个步骤： 解析–优化–执行; MySQL中有一个配置可以开启 查询缓存，默认是关闭的，开启后，所有的查询操作将优先从缓存中进行查找，存在则返回结果。

3.1.2 查询缓存的原理

开启查询缓存后，缓存SELECT操作的结果集和SQL语句，key为sql，value为查询结果集；

如果新的SELECT语句来了，以这个sql为key去缓存中查询，如果匹配，就把缓存的结果集返回；

匹配标准：与缓存的SQL语句是否完全一样，sql中字母区分大小写以及中间的空格，简单理解为存储了一个key-value结构，key为sql，value为sql查询结果，例如：

select age from t_user; 与 select AGE from t_user; 不会匹配，因为大小写不同；
select age from t_user; 与 select  age from t_user; 不会匹配，因为空格不同；

sql两边的空格可忽略，可以认为是对key进行过trim操作之后再进行equals比较。

3.2 查询缓存使用

3.2.1 查看查询缓存的设置

可以使用SHOW VARIABLES LIKE '%query_cache%'; 命令查看当前查询缓存的设置选项（注意： 在MySQL 8.0.3中，这个变量被移除了。）：

SHOW VARIABLES LIKE '%query_cache%';

查询缓存相关变量(了解即可)：

• have_query_cache: 是否有查询缓存
• query_cache_limit : MySQL能够缓存的最大查询结果；如果某查询的结果大小大于此值，则不会被缓存；
• query_cache_min_res_unit : 查询缓存中分配内存的最小单位；(注意：此值通常是需要调整的，此值被调整为接近所有查询结果的平均值是最好的) 计算单个查询的平均缓存大小：（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache
• query_cache_size : 查询缓存的总体可用空间，单位为字节；其必须为1024的倍数；
• query_cache_type: 查询缓存类型；是否开启缓存功能，开启方式有三种 [ON | OFF | DEMAND]；
• query_cache_wlock_invalidate : 当其它会话锁定此次查询用到的资源时，是否不能再从缓存中返回数据；（OFF表示可以从缓存中返回数据）

3.2.2 开启查询缓存

可以使用 query_cache_type 变量来开启查询缓存，开启方式有三个值：

• ON : 正常缓存。表示在使用 SELECT 语句查询时，若没指定 SQL_NO_CACHE 或其他非确定性函数，则一般都会将查询结果缓存下来。
• DEMAND ：指定SQL_CACHE才缓存。表示在使用 SELECT 语句查询时，必须在该 SELECT 语句中显示指定 SQL_CACHE 才会将该SELECT语句的查询结果缓存下来。 例如：select SQL_CACHE uname from t_user where id = 1;
• OFF： 关闭查询缓存。

例如： SET QUERY_CACHE_TYPE = ON

也可以在mysql配置文件中指定开启，在 my.ini(或者my.cnf)中增加： query_cache_type = ON

3.2.3 设置查询缓存空间的大小

查询缓存能够缓存多少数据与其缓存空间大小有关。可以通过设置 query_cache_size 的值来改变。

例如： 在 my.ini(或者my.cnf)中增加： query_cache_size = 512M

3.2.4 查询缓存命中率

SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select';

• Com_select : 非缓存查询次数
• Qcache_hits : 缓存命中次数

缓存命中率 = Qcache_hits/(Qcache_hits+Com_select)

3.2.5 查询缓存演示

开启了查询缓存，现在历史查询的情况如下（已经执行过 SELECT * FROM t_user; 语句）：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wu7jXhpg-1571814025258)(img\1571636231425.png)]

再次执行一下SELECT * FROM t_user;语句， 可以看到查询缓存命中的变量结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-koV4p2g1-1571814025258)(img\1571636245388.png)]

可以看到 SELECT * FROM t_user; 语句命中了查询缓存，这样提升了效率。

3.3 不会缓存的情况

有以下一些场景查询缓存无法达到理想效果：

• 当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数NOW()，CURRENT_DATE()等类似的函数，或者用户自定义的函数，存储函数，用户变量等都不会被缓存。
• 当查询的结果大于query_cache_limit设置的值时，结果不会被缓存。
• 对于InnoDB引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务，会大大降低缓存命中率。
• 查询的表是系统表，如 select * from mysql。
• 查询语句不涉及表，例如 select 1。

练习： 多次执行 SELECT NOW(); 你会发现 Qcache_hits变量的值并没有增加，即没有走缓存。

4.回望

• 理解查询缓存的作用
• 在mysql中开启、关闭查询缓存

知识点-MySQL锁机制

1.高山

• 理解mysql锁机制

2.演绎

• 锁介绍
• 读写锁
• 死锁概念以及演示

3.攀登

3.1 锁介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。

锁保证数据并发访问的一致性、有效性；

锁冲突也是影响数据库并发访问性能的一个重要因素。

锁是Mysql在服务器层和存储引擎层的的并发控制。

加锁是会消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等都会消耗资源。

3.1.1 锁的分类

锁按性能分：

• 乐观（比如使用version字段比对，无需等待） 乐观锁，如它的名字那样，总是认为别人不会去修改，只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。
• 悲观（需要等待其他事务） 悲观锁，正如它的名字那样，数据库总是认为别人会去修改它所要操作的数据，因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。
• 读锁(共享锁)、写锁(排他锁) 均属于悲观锁

MySQL 不同的存储引擎支持不同的锁机制。

锁按粒度分：

• 表级锁：就是数据操作的时候会锁表； MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）。 开销小，加锁快，锁冲突高并发度低，难发生死锁。
• 行级锁：InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 开销大，加锁慢，锁冲突低并发度高，可能会出现死锁；
• 页级锁：开销和加锁时间界于表锁和行锁之间。 可能会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。

但是一些特殊场景中， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。

3.2 InnoDB行级锁和表级锁

3.2.1 InnoDB 常见的锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁（意向锁是 InnoDB 自动加的， 不需用户干预）：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

3.2.2 InnoDB加锁方法

• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)；
• 对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显式给记录集加共享锁或排他锁：
• • 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
  • 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁。

练习： 在sqlyog中打开两个连接A、B，并开启事务。在A中进行如下操作：

START TRANSACTION;
SELECT * FROM t_user  FOR UPDATE;

再在B中进行如下操作：

START TRANSACTION;

INSERT INTO t_user VALUES(NULL, 'df', 50, 1);

可以看到B中语句一直处于等待状态…

3.2.3 在以下情况下，表锁定优先于页级或行级锁定

• 表的大部分语句用于读取。
• 在整个表上有许多扫描或GROUP BY操作，没有任何写操作。

3.3 读写锁

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）

为达到最高锁定速度，除InnoDB和BDB引擎之外，对所有存储引擎，MySQL使用表锁定(而不是页、行或者列锁定)。

3.3.1 读写锁加锁机制

对 WRITE，MySQL使用的表锁定方法原理如下：

• 如果在表上没有锁，则在它上面放一个写锁。
• 否则，把锁定请求放在写锁定队列中。

对 READ，MySQL使用的锁定方法原理如下：

• 如果在表上没有写锁定，把一个读锁定放在它上面。
• 否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁可以被写锁队列中的线程得到，然后是读锁定队列中的线程。

这意味着，如果你在一个表上有许多更新，SELECT语句将得到没有更新才获得锁。

可以通过检查 table_locks_waited 和 table_locks_immediate状态变量来分析系统上的表锁定争夺，如果 table_locks_waited 值比较大，就需要检查是否有很多在等待获取锁的操作了：

SHOW STATUS LIKE 'Table%';

3.3.2 读写锁演示

读写锁演示可以参考：读写锁演示.md

3.4 死锁概念以及演示

3.4.1 死锁的概念

当两个事务同时持有对方想要的资源，并且又需要等待对方释放资源，就可能产生死锁。

说明： 有两个事务A、事务B； 事务A持有资源A保持占有状态，事务B持有资源B保持占有状态； 此时事务A需要获取资源B即等待事务B释放锁，且事务B需要获取资源A即等待事务A释放锁。这样两个事务互相占有对方的资源，且不释放资源，就造成了死锁。

3.4.2 产生死锁的四个必要条件

• 互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
• 请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
• 不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
• 环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

解决死锁问题：打破任意一个条件即可。

3.4.3 使用排他锁(X)演示死锁

按以下顺序依次执行，然后查看死锁日志：

事务A执行：select * from t_user where id = 1 for update;

事务B执行：select * from t_user where id = 2 for update;

事务A执行：select * from t_user where id = 2 for update;

事务B执行：select * from t_user where id = 1 for update;

查看死锁日志：

show engine innodb status\G