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语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行,类似于编程语言中的方法或函数。
- 存储过程的优点:
- 存储过程是对SQL语句的封装,增强可复用性
- 存储过程可以隐藏复杂的业务逻辑、商业逻辑
- 存储过程支持接收参数,并返回运算结果
- 存储过程的缺点:
- 存储过程的可移植性较差,如果更换数据库,要重写存储过程
- 存储过程难以调试和扩展
- 无法使用Explain对存储过程进行分析
- 《阿里巴巴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; //发现发送了邮件