mysql高级2
学习目标
- 能够理解视图的作用
- 能够在Mysql中使用视图
- 能够理解触发器并使用触发器
- 理解存储过程和存储函数的作用
- 能够独立编写、调用、查看、删除存储过程
- 能够在存储过程中定义变量、并赋值
- 能够在存储过程中使用 if 语句
- 能够给存储过程传入参数、并返回结果
- 能够在存储过程中使用 case when… 语句
- 能够在存储过程中使用while循环语句
- 能够在存储过程中使用repeat循环语句
- 能够在存储过程中使用loop循环语句
-
第一章 视图
sql脚本
create database demo01 default charset=utf8mb4;use demo01;CREATE TABLE `city` (`city_id` int(11) NOT NULL AUTO_INCREMENT,`city_name` varchar(50) NOT NULL,`country_id` int(11) NOT NULL,PRIMARY KEY (`city_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `country` (`country_id` int(11) NOT NULL AUTO_INCREMENT,`country_name` varchar(100) NOT NULL,PRIMARY KEY (`country_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);insert into `country` (`country_id`, `country_name`) values(1,'China');insert into `country` (`country_id`, `country_name`) values(2,'America');insert into `country` (`country_id`, `country_name`) values(3,'Japan');insert into `country` (`country_id`, `country_name`) values(4,'UK');
3.1 视图概述
视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图相对于普通的表的优势主要包括以下几项。 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
- 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
- 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
3.2 创建或者修改视图
创建视图的语法为：
修改视图的语法为： ```sql ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION]
选项 : WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。 LOCAL ： 只要满足本视图的条件就可以更新。 CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.
示例: <br />创建city_country_view视图 , 执行如下SQL :
```sql
create or replace view city_country_view
as
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;
3.3 查看视图
从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。
同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。
如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看 ：
3.4 删除视图
语法:
DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]
示例:
删除 city_country_view 视图
DROP VIEW city_country_view ;
第二章 触发器
4.1 触发器概述
触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
| 触发器类型 | NEW 和 OLD的使用 |
|---|---|
| INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 |
4.2 创建触发器
语法:
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
trigger_stmt ;
end;
示例:
需求: 通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;
sql脚本: 创建一张日志表,存放日志信息
create table emp_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型, insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_id int(11) not null comment '操作表的ID',
operate_params varchar(500) comment '操作参数',
primary key(`id`)
)engine=innodb default charset=utf8;
创建 insert 型触发器，完成插入数据时的日志记录 :
DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',age:',new.age,', salary:',new.salary,')'));
end $
DELIMITER ;
创建 update 型触发器，完成更新数据时的日志记录 :
DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'update',now(),new.id,concat('修改前(id:',old.id,',name:',old.name,',age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,',age:',new.age,', salary:',new.salary,')'));
end $
DELIMITER ;
创建delete 行的触发器 , 完成删除数据时的日志记录 :
DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,',name:',old.name,', age:',old.age,', salary:',old.salary,')'));
end $
DELIMITER ;
测试:
-- 添加员工信息,查看日志表数据
insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);
-- 修改和删除员工信息,查看日志表数据
update emp set age = 39 where id = 3;
delete from emp where id = 5;
4.3 查看触发器
可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
show triggers ;
4.4 删除触发器
drop trigger trigger_name
第三章 存储过程
1 存储过程和函数概述
存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
函数 ： 是一个有返回值的过程 ；
过程 ： 是一个没有返回值的函数 ；
2 创建存储过程
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
-- SQL语句
end ;
示例:
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;
知识小贴士
DELIMITER
该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。
默认情况下，delimiter是分号 ; 。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
3 调用存储过程
call procedure_name() ;
4 查看存储过程
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';
-- 查询存储过程的状态信息
show procedure status;
-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;
5 删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name ;
6 存储过程语法
存储过程是可以编程的，意味着可以使用变量，表达式，控制结构 ， 来完成比较复杂的功能。
6.1 变量
DECLARE
通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。
语法:DECLARE var_name[,...] type [DEFAULT value]示例:
delimiter $ create procedure pro_test2() begin declare num int default 5; select num+ 10; end$ delimiter ;SET
直接赋值使用 SET，可以赋常量或者赋表达式
语法:SET var_name = expr [, var_name = expr] ...示例:
DELIMITER $ CREATE PROCEDURE pro_test3() BEGIN DECLARE NAME VARCHAR(20); SET NAME = 'MYSQL'; SELECT NAME ; END$ DELIMITER ;也可以通过select … into 方式进行赋值操作 :
DELIMITER $ CREATE PROCEDURE pro_test4() BEGIN declare countnum int; select count(*) into countnum from city; select countnum; END$ DELIMITER ;6.2 if条件判断
语法:
if search_condition then statement_list [elseif search_condition then statement_list] ... [else statement_list] end if;需求：
根据定义的身高变量，判定当前身高的所属的身材类型 180 及以上 ----------> 身材高挑 170 - 180 ---------> 标准身材 170 以下 ----------> 一般身材示例:
delimiter $ create procedure pro_test6() begin declare height int default 175; declare description varchar(50); if height >= 180 then set description = '身材高挑'; elseif height >= 170 and height < 180 then set description = '标准身材'; else set description = '一般身材'; end if; select description ; end$ delimiter ;6.3 传递参数
语法:
create procedure procedure_name([in/out/inout] 参数名 参数类型) ... IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认 OUT: 该参数作为输出，也就是该参数可以作为返回值 INOUT: 既可以作为输入参数，也可以作为输出参数IN 输入
需求:
根据定义的身高变量，判定当前身高的所属的身材类型示例:
delimiter $ create procedure pro_test7(in height int) begin declare description varchar(50) default ''; if height >= 180 then set description='身材高挑'; elseif height >= 170 and height < 180 then set description='标准身材'; else set description='一般身材'; end if; select concat('身高 ', height , '对应的身材类型为:',description); end$ delimiter ;OUT 输出
示例:
需求:根据传入的身高变量，获取当前身高的所属的身材类型sql语句:
delimiter $ create procedure pro_test8(in height int , out description varchar(100)) begin if height >= 180 then set description='身材高挑'; elseif height >= 170 and height < 180 then set description='标准身材'; else set description='一般身材'; end if; end$ delimiter ;执行结果:
知识小贴士
_@_description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 “@@” 符号, 叫做 系统变量6.4 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;示例:
需求:给定一个月份 , 然后计算出所在的季度sql
delimiter $ create procedure pro_test9(month int) begin declare result varchar(20); case when month >= 1 and month <=3 then set result = '第一季度'; when month >= 4 and month <=6 then set result = '第二季度'; when month >= 7 and month <=9 then set result = '第三季度'; when month >= 10 and month <=12 then set result = '第四季度'; end case; select concat('您输入的月份为 :', month , ',该月份为 : ' , result) as content ; end$ delimiter ;6.5 while循环
语法:
while search_condition do statement_list end while;示例:
需求:计算从 1加到n的值sql
delimiter $ create procedure pro_test10(n int) begin declare total int default 0; declare num int default 1; while num<=n do set total = total + num; set num = num + 1; end while; select total; end$ delimiter ;6.6 repeat结构
有条件的循环控制语句, 当满足条件的时候退出循环 。while 是满足条件才执行，repeat 是满足条件就退出循环。
语法:REPEAT statement_list UNTIL search_condition END REPEAT;示例:
需求:计算从 1加到n的值sql
delimiter $ create procedure pro_test11(n int) begin declare total int default 0; repeat set total = total + n; set n = n - 1; until n=0 end repeat; select total ; end$ delimiter ;6.7 loop语句
LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现
语法: ```java [begin_label:] LOOPstatement_list
END LOOP [end_label]
如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。
<a name="4d6b8652"></a>
### 6.8 leave语句
用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。<br />示例: <br />需求: 使用loop和leave实现数字累加<br />sql:
```sql
delimiter $
CREATE PROCEDURE pro_test12(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END$
delimiter ;
6.9 游标/光标
游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE
语法:
DECLARE cursor_name CURSOR FOR select_statement ; -- 声明游标
OPEN cursor_name ; -- 打开游标
FETCH cursor_name INTO var_name [, var_name] ... ; -- 获取游标中的数据并赋给指定变量
CLOSE cursor_name ; -- 关闭游标
示例:
初始化脚本:
create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;
insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰
王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);
方式1-实例sql:
delimiter $
create procedure pro_test13()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
-- 创建游标
open emp_result; -- 打开游标
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为:
',e_salary);
close emp_result; -- 关闭游标
end$
delimiter ;
方式1-直接结果:
方式2-实例sql:
delimiter $
create procedure pro_test14()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare has_data int default 1;
declare emp_result cursor for select * from emp; -- 创建游标
declare exit handler for not found set has_data = 0;
open emp_result; -- 打开游标
repeat
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
until has_data = 0
end repeat;
close emp_result; -- 关闭游标
end$
delimiter ;
7 存储函数
语法:
CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;
示例:
需求: 定义一个存储函数,根据传入的条件,计算满足条件的总记录数
delimiter $
create function count_city(countryId int)
returns int
begin
declare cnum int ;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end$
delimiter ;
8 存储过程练习
需求:
将转账的业务,写入存储过程,调用存储过程实现转账的业务
sql
-- 创建表的sql
CREATE TABLE account(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
money FLOAT
);
INSERT INTO account VALUES(NULL,"tom",1000),(NULL,"rose",1000);
-- 存储过程sql
DELIMITER $
CREATE PROCEDURE pro_transfer(
IN fromSub VARCHAR(50),
IN toSub VARCHAR(50),
IN money_ FLOAT
)
BEGIN
DECLARE result INT(2) DEFAULT 0;
START TRANSACTION;
UPDATE account SET money=money-money_ WHERE `name`=fromSub;
IF ROW_COUNT() > 0 THEN
UPDATE account SET money=money+money_ WHERE `name`=toSub;
END IF;
IF ROW_COUNT() > 0 THEN
SET result = 1;
COMMIT;
ELSE
ROLLBACK;
END IF;
SELECT result;
END $
DELIMITER ;
第四章 Mysql体系结构
整个 MySQL Server由以下组成
- Connection Pool : 连接池组件
- Management Services & Utilities : 管理服务和工具组件
- SQL Interface : SQL 接口组件
- Parser : 查询分析器组件
- Optimizer : 优化器组件
- Caches & Buffers : 缓冲池组件
- Pluggable Storage Engines : 存储引擎
- File System : 文件系统
连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。服务层
第二层架构主要完成大多数的核心服务功能，如 SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。引擎层
存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。存储层
数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。mysql的不同
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。第五章 存储引擎
1 存储引擎概述
和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。
可以通过指定 show engines ， 来查询当前数据库支持的存储引擎 ：
创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎， MySQL5.5之前的默认存储引擎是
MyISAM，5.5之后就改为了InnoDB。
查看Mysql数据库默认的存储引擎 ， 指令show variables like '%storage_engine%';
2 存储引擎特性
下面重点介绍几种常用的存储引擎， 并对比各个存储引擎之间的区别， 如下表所示 ：
| 特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | ||||
| 全文索引 | 支持(5.6版本后) | 支持 | |||
| 集群索引 | 支持 | ||||
| 数据索引 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 高 | 低 | N/A | 低 | 低 |
| 内存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支持外键 | 支持 |
下面我们将重点介绍最长使用的两种存储引擎： InnoDB、MyISAM ， 另外两种 MEMORY、MERGE ， 了解即可。
InnoDB
InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点 ：
事务控制
InnoDB是支持事务控制,示例如下:
sql脚本:
create table goods_innodb(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
执行sql:
start transaction;
insert into goods_innodb(id,name)values(null,'华为p40');
commit;
外键约束
MySQL支持外键的存储引擎只有InnoDB ， 在创建外键的时候， 要求主表必须有对应的索引 ，从表在创建外键的时候，关联对应的索引字段。
下面两张表中 ， country_innodb是主表 ， country_id为主键索引，city_innodb表是从表，country_id字段为外键，对应于country_innodb表的主键country_id 。
create table country_innodb(
country_id int NOT NULL AUTO_INCREMENT,
country_name varchar(100) NOT NULL,
primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table city_innodb(
city_id int NOT NULL AUTO_INCREMENT,
city_name varchar(50) NOT NULL,
country_id int NOT NULL,
primary key(city_id),
key idx_fk_country_id(country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES
country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),
(null,'BeiJing',1);
删除country_id为1 的country数据：
delete from country_innodb where country_id = 1;
更新主表 country表的字段 country_id :
update country_innodb set country_id = 100 where country_id = 1;
知识小贴士
在创建索引时， 可以指定在删除、更新主表时，对从表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL和 NO ACTION。
RESTRICT和NO ACTION相同， 是指限制在从表有关联记录的情况下， 主表不能更新；
CASCADE表示主表在更新或者删除时，更新或者删除从表对应的记录；
SET NULL 则表示主表在更新或者删除的时候，从表的对应字段被SET NULL 。
针对上面创建的两个表， 从表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候， 如果从表有对应记录， 则不允许删除， 主表在更新记录的时候， 如果从表有对应记录， 则从表对应更新 。
存储位置
表结构存放在.frm文件中, 数据和索引保存在.idb文件中
MyISAM
MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点：
不支持事务
create table goods_myisam(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
执行sql
insert into goods_myisam values(null,'笔记本');
通过测试，我们发现，在 MyISAM存储引擎中，是没有事务控制的
文件存储方式
每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：
- .frm (存储表定义)；
- .MYD(MYData , 存储数据)；
- .MYI(MYIndex , 存储索引)；
3 存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。
- InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
- MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- MEMORY ：将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE ：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。
第六章 锁
1 锁概述
锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。
在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。2 锁分类
从对数据操作的粒度分 ：
表锁：操作时，会锁定整个表。
- 行锁：操作时，会锁定当前操作行。
从对数据操作的类型分：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
3 mysql锁
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：
MySQL这3种锁的特性可大致归纳如下 ：
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。4 MyISAM 表锁
MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。
如何加表锁
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCKTABLE 命令给 MyISAM 表显式加锁。
加表锁语法:加读锁 : lock table table_name read; 加写锁 : lock table table_name write；读锁案例
准备环境
create database demo03 default charset=utf8mb4; use demo03; CREATE TABLE `tb_book` ( `id` INT(11) auto_increment, `name` VARCHAR(50) DEFAULT NULL, `publish_time` DATE DEFAULT NULL, `status` CHAR(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=myisam DEFAULT CHARSET=utf8 ; INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思 想','2088-08-01','1'); INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思 想','2088-08-08','0'); CREATE TABLE `tb_user` ( `id` INT(11) auto_increment, `name` VARCHAR(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=myisam DEFAULT CHARSET=utf8 ; INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲'); INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');示例
客户端 一:
1 获得tb_book 表的读锁:
lock table tb_book read;2 执行查询操作
select * from tb_book;客户端 二 ：
3 执行查询操作
select * from tb_book;客户端 一 ：
4 查询未锁定的表
select name from tb_user;客户端 二 ：
5 查询未锁定的表
select name from tb_user;客户端 一 ：
6 执行插入操作
insert into tb_book values(null,'Mysql 高级','2088-01-01','1');
执行插入， 直接报错 ， 由于当前tb_book 获得的是 读锁， 不能执行更新操作。客户端 二 ：
7 执行插入操作
insert into tb_book values(null,'Mysql 高级','2088-01-01','1');
当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 inesrt 语句 ， 立即执行 ；写锁案例
客户端 一 :
1 获得tb_book 表的写锁
lock table tb_book write ;2 执行查询操作
select * from tb_book ;
查询操作执行成功；
3 执行更新操作update tb_book set name = 'java 编程思想（第二版）' where id = 1;客户端 二 :
4 执行查询操作
select * from tb_book ;
当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 select 语句 ， 立即执行 ；
结论
锁模式的相互兼容性如表中所示：
由上表可见：
1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。查看锁的争用情况
show open tables ;
In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。show status like 'Table_locks%';
Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。
Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。5 InnoDB 行锁
行锁介绍
行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。InnoDB的行锁模式
InnoDB 实现了以下两种类型的行锁。
共享锁（ S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。- 排他锁（ X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；
可以通过以下语句显示给记录集加共享锁或排他锁 。
共享锁（ S）: SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) : SELECT * FROM table_name WHERE ... FOR UPDATE
案例准备工作
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
行锁基本演示
以上， 操作的都是同一行的数据，接下来，演示不同行的数据 ：
无索引行锁升级为表锁
如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
查看当前表的索引 ：
show index from test_innodb_lock ;
由于 执行更新时 ， name字段本来为varchar类型， 我们是作为数组类型使用，存在类型转换，索引失效，最终行锁变为表锁 ；
间隙锁危害
当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个 “间隙” 加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁） 。
示例: 
InnoDB行锁争用情况
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
总结
InnoDB 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
优化建议：
若有收获，就点个赞吧
0 人点赞
