MySQL一周从入门到精通Day5

MySQL高级进阶1

---

事务控制

• 事务(Transaction)作为一个逻辑工作单元执行的一系列操作（增删改），这些操作要么全部成功要么全部失败。事务确保对多个数据的修改作为一个单元来处理
• MySQL中，使用了Innodb存储引擎的数据库表才支持事务，show engines
• 事务用于管理 insert update 和 delete 语句（变更操作），不包括查询
• 事务的四个特性： ACID特性
  • Atomoticity 原子性 ：事务中的操作要么都做，要么都不做
  • Consistency 一致性 ： 事务完成后，必须使所有数据保持一致状态
  • Isolation 隔离性 ： 独立运行
  • Durability 持久性 ： 影响永久性
• MySQL默认 自动提交事务，即一条SQL一次提交事务。查看事务控制 select @@ autocommit; 默认为 1
• 开启一个事务，可以使用start transaction或begin，或者将autocommit参数值设为 0 ，commit提交。

---

实战：银行账户张三向李四转账

use mydb;
#创建表
create table bank_account(
	id int not null auto_increment primary key,
	name varchar(30) comment '姓名',
	balance decimal(18,2) comment '账户余额'
);
#插入字段
insert into bank_account(id,name,balance) values(1,'张三',0);
insert into bank_account(id,name,balance) values(2,'李四',0);
update bank_account set balance=balance+1000 where id=1;

#开启事务
start transaction;

update bank_account set balance=balance+1000 where id=2;
update bank_account set balance=balance-1000 where id=1;
#提交
commit;


#开启事务
start transaction;

update bank_account set balance=balance+1000 where id=2;
update bank_account set balance=balance-1000 where id=1;
select * from bank_account;
#回滚事务
rollback;
select * from bank_account;

---

分区表

• 按照某种规则将同一张表的数据分段划分到多个位置存储。物理位置不一样，但是逻辑上还是一个表。这样提高了数据库的性能。
• 注：MySQL8.0中，只有InnoDB和NDB两个存储引擎支持分区
• 优势：存储更多/便于管理/提升查询效率/并行处理/提高查询吞吐

---

• 4种类型：Range/ List/ Hash/ Key 分区，range最常用
• range分区： 基于一个给定连续区间的列值，把多行分配给分区
  主键必须包含分区字段

create table user_range(
	id int not null auto_incremen;
	name varchar(30),
	age int,
	province int,
	primary key(id,age)
);		//主键必须包含分区字段
partition by range(age)(
	partition p1 values less then (20) data directory = 'c:/data/p1',
	partition p2 values less then (40) data directory = 'c:/data/p1',
	partition p3 values less then maxvalue data directory = 'c:/data/p1'
);

• list分区： 基于列值匹配一个离散值集合中的某个值来进行选择

create table user_list(
	id int not null auto_incremen;
	name varchar(30),
	age int,
	province int,
	primary key(id,province)
);		//主键必须包含分区字段
partition by list(province)(
	partition p1 values in  (1,3,5,7,9,11), 
	partition p2 values in (2,4,6,8,10,12) ,
	partition p3 values in (22,33,44,55)   
);

• hash分区： 基于用户定义的表达式返回值来进行选择

create table user_hash(
	id int not null auto_incremen;
	name varchar(30),
	age int,
	birthday date,
	province int,
	primary key(id,birthday)
);		//主键必须包含分区字段
partition by hash(year(birthday))
	partition 5;

• key分区： 类似hash分区，但这里的hash key是系统产生的

create table user_key(
	id int not null auto_incremen;
	name varchar(30),
	age int,
	birthday date,
	province int,
	primary key(id,age)
);		//主键必须包含分区字段
partition by key(age)
	partition 5;

---

• 其他分区操作

#新增分区
alter table 'user' add partition(partition p4 values less then maxvalue);
#对已有的表进行分区
alter table 'user' partition by range(age)(
	partition p1 values less then (20) data directory = 'c:/data/p1',
	partition p2 values less then (40) data directory = 'c:/data/p1',
	partition p3 values less then maxvalue data directory = 'c:/data/p1'
);
#删除分区（数据也删除）
alter table 'user' drop partiton p5;
#移除分区（数据不删除）
alter table 'user' remove partitioning;

实战range分区

use mydb;
show tables;

#创建表并分区
create table user_range(
	id int not null auto_incremen;
	name varchar(30),
	age int,
	province int,
	primary key(id,age)
);		//主键必须包含分区字段
partition by range(age)(
	partition p1 values less then (20) data directory = 'c:/data/p1',
	partition p2 values less then (40) data directory = 'c:/data/p1',
	partition p3 values less then maxvalue data directory = 'c:/data/p1'
);

#插入数据验证 （一个18一个28 应该在不同分区）
insert into user_range(name,age,province) values('张三',18,1);
insert into user_range(name,age,province) values('李四',28,1);

#	可以看到在不同分区
explain select * from user_range where age=18 \G
explain select * from user_range where age=28 \G

---

视图

• 视图是一个虚拟表，内容由select查询语句定义。视图的数据在使用视图时动态生成，存储在基表中。可以理解为报表，对复杂查询语句的封装。
• 可读性，安全性，重用性
• 视图表和基表的数据变化会互相影响。（单表）
• 如果时基于多表连接，不会有并行更改。

#创建视图
create view view_name as select...;
#修改视图
alter view_name as select...'
#查看视图创建语句
show create view view_name;
#查看有哪些视图
show table status where comment='视图';
#删除视图
drop view view_name;

实操

select * from student;
select * from score;

#创建视图
create view view_student as select * from student;
select * from view_student;		//视图表和基表一样

#查看有哪些视图
show table status where comment='视图';

#修改视图表数据
update view_student set name='lily2' where stu_no='2020001';

#查看视图
select * from view_student;		//被修改
#查看基表
select * from student;		//被修改

#做一个左连接
create view stu_score_view as
select A.*,B.course,B.score
from student A
left join score B on(A.stu_no=B.stu_no);

#查看
select * from stu_score_view;
//相当于把操作进行了封装，封装成了一个视图

#这时候在修改字段试试
update view_student set name='lily3' where stu_no='2020001';
//发现失败了！因为现在时基于多张表

---

存储过程及基本使用

• 存储过程（Stored Procedure是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行，类似于编程语言中的方法或函数。
• 存储过程的优点：

1. 存储过程是对SQL语句的封装，增强可复用性
2. 存储过程可以隐藏复杂的业务逻辑、商业逻辑
3. 存储过程支持接收参数，并返回运算结果

• 存储过程的缺点：

4. 存储过程的可移植性较差，如果更换数据库，要重写存储过程
5. 存储过程难以调试和扩展
6. 无法使用Explain对存储过程进行分析
7. 《阿里巴巴Java开发手册》中禁止使用存储过程

---

示例：求两数之和

#存储过程定义：求两数之和
delimiter //		/* 声明语句结束符 // ，防止和 ;  混淆 */
create procedure my_sum(in a int, in b int, out result int)		/* 定义入参，出参 */
begin
 	set result = a + b;		/* 变量赋值 */
end
//
delimiter ; 

#存储过程调用
call my_sum(10, 20, @result);
select @result;

示例：计算1+2+…+n的和

#存储过程定义：计算1+2+...+n的和
delimiter //
create procedure my_n_sum(in n int, out result int)
begin
	 declare i int default 1;
	 declare sum int default 0;
	 while i<=n do
		 set sum = sum + i;
		 set i = i + 1;
	 end while;
	 set result = sum;
end;
//
delimiter ; 

---

存储过程实战：给指定用户发邮件通知

根据用户id和邮件内容content给用户发邮件

drop table if exists user_info;
drop table if exists email_info;
#创建用户表，邮件表
create table user_info(
	 id int not null auto_increment primary key,
	 name varchar(30),
	 email varchar(50)
);
#插入用户数据
insert into user_info(id, name, email) values(1, 'hu', 'hu@qq.com');
insert into user_info(id, name, email) values(2, '张三', 'zhangsan@qq.com');

create table email_info(
	 id int not null auto_increment primary key,
	 email varchar(50),
	 content text,
	 send_time datetime
);

#存储过程示例：根据用户id和邮件内容content给用户发邮件
delimiter //
create procedure send_email(in user_id int, in content text)
begin
	 /* 根据用户id查询邮箱email */
	 set @user_email=(select email from user_info where id=user_id);
	 /* 模拟发送邮件 */
	 insert into email_info(email, content, send_time) values(@user_email, content, now());
end;
//
delimiter ;
#调用测试
call send_email(1, '欢迎加入MySQL阵营！');

---

触发器实战：给新用户发邮件

• 触发器（trigger） 用于监视某种情况并触发某种操作，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，而是由事件来触发。例如，当对某张表进行insert、delete、update操作时就会触发执行它。
• 创建触发器语法

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON table_name FOR EACH ROW trigger_stmt
/*参数说明：
trigger_name：触发器名称
trigger_time：触发时间，取值有before、after
trigger_event：触发事件，取值有insert、update、delete
table_name：触发器监控的表名
trigger_stmt：触发执行的语句，可以使用OLD、NEW来引用变化前后的记录内容
			NEW.columnName：获取INSERT触发事件中新插入的数据
			OLD.columnName：获取UPDATE和DELETE触发事件中被更新、删除的数据*/

实战目标：当有新用户插入时，自动给用户发送邮件。

drop table if exists user_info;
drop table if exists email_info;
#建表
create table user_info(
	 id int not null auto_increment primary key,
	 name varchar(30),
	 email varchar(50)
);
#插入数据
insert into user_info(id, name, email) values(1, 'hu', 'hu@qq.com');
insert into user_info(id, name, email) values(2, '张三', 'zhangsan@qq.com');

create table email_info(
	 id int not null auto_increment primary key,
	 email varchar(50),
	 content text,
	 send_time datetime
);

#触发器实战：给新用户发邮件
delimiter //
CREATE TRIGGER send_email_trigger AFTER INSERT ON user_info FOR EACH ROW
BEGIN
	 insert into email_info(email, content, send_time) values(NEW.email, '欢迎加入MySQL阵营！ ', now());
END
//
delimiter ;

#插入数据测试
insert into user_info(id,name,email) values (3,'lisi','lisi@qq.com');

#查看结果
select * from email_info;	//发现发送了邮件