✨你好啊，我是“ 罗师傅”，是一名程序猿哦。
🌍主页链接：楚门的世界 - 一个热爱学习和运动的程序猿
☀️博文主更方向为：分享自己的快乐 briup-jp3-ing
❤️一个“不想让我曾没有做好的也成为你的遗憾”的博主。
💪很高兴与你相遇，一起加油！

前言

目标：Mysql数据库的使用及数据库分析及设计实践

数据库事务

概述

事务（Transaction）,是由一些列对数据库表中数据，进行访问与更新的操作，所组成的一个执行逻辑单元

只有DML语句才会产生事务，其他语句不会产生事务
DML语句执行的时候，如果当前有事务，那么就使用这个事务。如果当前没有事务，则产生一个新事物
commit、rollback、DDL语句都可以把当前事务给结束掉
commit和DDL语句结束事务的方式是把这个事务给提交了，然后DML操作永久生效
rollback结束事务的方式是把这个事务给回滚了，默认回滚到事务开始的状态
mysql默认是开启事务，即autocommit=1,自动提交事务。即执行insert、update、delete操作，立即提交

事务的生命周期

事务特征

数据库事务必须具备ACID特性，ACID是Atomic（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）

原子性（Atomicity）

一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。

事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

如：张三转账给李四钱，转账过程中停电了，应该转账不生效（回退），事物的原子性

一致性（Consistency）

事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。

如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。

如：张三转账给李四钱，转帐之前和转账之后，账务总额应该保持不变。

隔离性（Isolation）

指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。

如：张三转账过程，李四看不到张三账户的变化

持久性（Durability）

指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

如：如果转到错误的人的账户上，钱是退不回来的，事务一旦完成，事务中所涉及的数据，不能再被此次事务所更改。

提交回滚

查看事务

1	select @@autocommit;

开启事务

事务开启一次即可！

SET AUTOCOMMIT=0; # 用来禁止使用当前会话的自动提交。
# 开启事务，下面二选一即可 
BEGIN;
START TRANSACTION;

事务提交

开启事务以后，执行DML语句遇到commit关键字或DDL语句，会提交事务。

-- 事务已经开启
insert ... 第一次执行DML语句，新建事务(用A标识)
update ... 这个操作是事务A中的操作
insert .. 这个操作是事务A中的操作
commit; 让事务A里面的三个操作生效、事务A结束
delete ... 此时没有事务，执行新的DML语句会产生新事务(用B标识)
insert .. 这个操作是事务B中的操作
insert .. 这个操作是事务B中的操作
rename .. 遇到DDL语句，事务自动提交
insert .. 此时没有事务，执行新DML会创建新事务(用C标识)
...

注意：有一些语句会使得事务结束

DDL
- alter database、alter event、alter procedure、alter table、alter view、create table、drop table、rename table、truncate table等；
修改MYSQL架构的语句
- create user、drop user、grant、rename、user、revoke、set password；
管理语句
- analyze table、cache index、check table、load index、into cache、optimize table、repair table等

-- 开启事务
start transaction;
insert into t_customer(id,name) values(1,'tom');
insert into t_customer(id,name) values(2,'jack');
insert into t_customer(id,name) values(3,'rose');
update t_customer set name='lucy' where id=1;
commit;
-- 开启事务
start transaction;
delete from t_customer where id=3;
update t_customer set name='lily' where id=2;
select * from t_customer;
-- DDL语句，会自动提交事务
alter table user3 add phone varchar(20);
select * from t_customer;

事务回滚

使用关键字 savepoint ，用来设置回滚点

# DML语句1
savepoint A 
#  DML语句2
savepoint B
# DML语句3
rollback to A/B
rollback

-- 开启事务
start transaction;
insert into t_user values(1,'tom',1000);
savepoint A;
insert into t_user(id,name) values(2,'zs');
savepoint B;
delete from t_user;
-- 回滚到B
rollback to B;
select * from t_user;
-- 回滚事务（生效）
rollback;
select * from t_user;
-- 测试完成后可以删除表
drop table t_user;

隔离级别

事务隔离级别指的是隔离性的强弱

不可重复读：在同一事务中，两次读取同一数据，得到内容不同

幻影读：同一事务中，用同样的操作读取两次，得到的记录数不相同。

在可重复读隔离级别下，事务B只能在事务A修改过数据并提交后，自己也提交事务后，才能读取到事务B修改的数据

脏读：会话1读到会话2未提交的数据。

4类隔离级别

READ UNCOMMITTED 幻影读、不可重复读和脏读都允许
READ COMMITTED 允许幻影读、不可重复读，不允许脏读
REPEATABLE READ （默认）允许幻影读，不允许不可重复读和脏读
SERIALIZABLE 幻影读、不可重复读和脏读都不允许

show variables like '%isolation%'; 
set session transaction isolation level READ COMMITTED;
-- 恢复成默认隔离级别
set session transaction isolation level REPEATABLE READ;

注意事项：会话级别只针对当前生效，永久生效mysql配置文件修改

索引

在 MySQL 中，索引（Index）是一种数据结构，用于提高数据库表的查询性能。

索引可以加速数据的检索，减少查询所需的时间和资源消耗。索引用于快速定位和访问表中的数据。它类似于书籍的目录，可以根据关键字快速找到对应的数据行，而无需扫描整个表。

索引优缺点：

优点：加快数据检索速度，提高查找效率
缺点：占用数据库物理存储空间，当对标中数据更新时，索引需要动态维护，降低数据写入效率

创建原则：

对where和order by涉及的列上尽量建立索引
更新频繁的列不应设置索引
数据量小的表不要使用索引
重复数据多的字段不应设为索引（如性别，只有男和女），重复数据超15%就不该建索引

存储引擎

在 MySQL 中，存储引擎（Storage Engine）是负责处理数据的存储和检索的模块。

MySQL 支持多种存储引擎，不同的存储引擎可以在不同的应用场景中提供不同的优势和特性。

常见存储引擎：

InnoDB：InnoDB 是 MySQL 5.5 版本之后的默认存储引擎。它提供了事务支持、行级锁定、外键约束和崩溃恢复等功能。 InnoDB 引擎适用于需要事务支持和高并发读写操作的应用场景。
MyISAM：MyISAM 是 MySQL 5.5 版本之前的默认存储引擎。它以其简单和高性能而闻名，适用于大量的读操作和只读数据。 MyISAM 引擎不支持事务和行级锁定。
Memory：或称为 Heap存储引擎，将数据存储在内存中，提供了非常快速的读写性能。但是，由于数据存储在内存中，重启服务器或断电会导致数据丢失。Memory 引擎适用于临时数据存储和高速缓存等场景。

底层实现

MySQL 8 默认存储引擎为 InnoDB ，其采用B+Tree作为索引常用底层数据结构。

Hash表

弊端：通过hash值定位数据非常快，但只有精确匹配索引所有列的查询才有效！

因为散列表中的值是无序的，无法进行大小的比较

在MySQL中，常见存储引擎并不支持 Hash 索引，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。

二叉树

使用二叉树可以提高查询数据的速度，但是有可能产生不平衡类似于链表的结构

平衡二叉树

平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，用这个树形结构的数据减少无关数据的检索，大大的提升了数据检索的速度，其特点：

它的左子树和右子树都是平衡二叉树
左子树比中间小，右子树比中间值大
左子树和右子树的深度之差的绝对值不超过1

BTree

MyISAM引擎

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

InnoDB引擎

InnoDB引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据，相比 MyISAM效率要高一些，但是占硬盘内存更多。

单列索引

普通索引

三种创建索引的方法

CREATE TABLE [IF NOT EXISTS] tb_name [(
    字段1 数据类型 [约束条件] [默认值] [COMMENT '注解'],
    字段2 数据类型 [约束条件] [默认值] [COMMENT '注解'],
    字段3 数据类型 [约束条件] [默认值] [COMMENT '注解'],
    ......
    [表约束条件]
    index index_name(col_name...)
    ...
)][engine=innodb] [default charset=utf8];

1	create index index_name on tb_name(col_name);

1	alter table tb_name add index index_name(col_name);

# 查看jd2311数据库中的所有索引
select *
from mysql.innodb_index_stats
where database_name='jd2311';

# 查看briup数据库中教师表中所有索引
select *
from mysql.innodb_index_stats
where database_name='briup' and table_name='tea';

1 2	# 查看briup数据库中教师表中所有索引 show index from tea;

1
2
3

# 删除tea表中的索引
drop index index_name on tea;
alter table tea drop index index_name;

唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

create table tea3(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    unique index_name(name)
);
create unique index index_name on tea3(name);
alter table tea3 add unique index_name(name);

主键索引

创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键是具有唯一性并且不允许为NULL。

create table tea4(
    id int primary key auto_increment,
    name varchar(20),
    age int
);

create table tea4(
    id int,
    name varchar(20),
    age int
);
alter table tea4 add primary key(id);

组合索引

create table tea5(
    id int,
    name varchar(20),
    age int,
    index index_name(id,name)
);
create index index_name on tea5(id,name);
alter table tea5 add index index_name(id,name);

注意事项：复合索引的使用复合最左原则 table_name(id, name)

where id=1可以使用索引，条件里面必须包含索引前面的字段才能够继续进行匹配
where name=’lisi’ 不可以使用索引
where name=’lisi’ and id=12可以使用索引，mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引
where id=12 and name=’lisi’ 可以使用，顺序一致

全文索引

为什么要全文索引？

like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。

全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

索引变量（了解即可）

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

全文检索语法：

1	match (col_name1,col_name2,...) against(expr [search_modifier])

1	show variables like '%ft%'; # 查看索引变量

create table Poetry(
    id int primary key auto_increment,
    name varchar(20),
    content text,
    fulltext(content)
);
create fulltext index index_content on Poetry(content);
alter table Poetry add fulltext index_content(content);

insert into Poetry values(1,'rain','Rain is falling all around');
insert into Poetry values(2,'rain','It falls on field and tree');
insert into Poetry values(3,'rain','It rains on the umbrella here');
insert into Poetry values(4,'rain','And on the ships at sea');

# 基于全文检索查询含有And内容
select *
from Poetry
where match(content) against('And');

空间索引

# 创建超市，并记录经纬度
create table shop(
    id int primary key auto_increment,
    name varchar(64) not null,
    point geometry not null,
    spatial key geom_index(point)
);

视图

视图优点：

操作简单
减少数据冗余
数据安全
适应多变的需求
能够分解复杂的查询逻辑

CREATE [OR REPLACE] VIEW view_name [(字段列表)]
AS
select语句  # 表示一个完整的查询语句，将查询记录映射到视图中
[WITH [CASCADED |LOCAL] CHECK OPTION]  # 可选项，表示更新视图时要保证在该视图的权限范围之内

单表视图

-- 新建或更新视图
create or replace view v_emp(id,username)
as
select id,name
from emp;
desc emp;
select * from emp;
-- 查看视图结构
desc v_emp;
select * from v_emp;

create view v_emp1
as
select id,name,salary,salary*30
from emp;

create view v_emp_new
as
select name,salary*30 as yearSal
from v_emp1;
select * from v_emp_new;

多表视图

create table v_emp_dept
as
select concat(e.name,'(',d.name,')') as
username,e.salary,e.dept_id,d.name dept_name
from emp e, dept d
where e.dept_id=d.id;
select * from v_emp_dept;

show tables; # 查看当前数据库下的视图语法
rename table old_view_name to new_view_name; # 重命名视图

# 修改视图 
alter view view_name
as
select查询语句

drop view if exists view_name; # 删除视图

注意：修改视图可以使用创建视图替换

视图更新

注意事项：对基表（数据表）进行更新改会影响视图

如果视图包含下述结构中的任何一种，那么它就是不可更新的：

聚合函数（SUM(), MIN(), MAX(), COUNT()等）
select查询列表有数学表达式
DISTINCT
UNION 或 UNION ALL
位于选择列表中的子查询
GROUP BY
HAVING
JOIN
常量视图

函数和存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合，调用存储过程和函数可以简化应用开发工作，提高数据处理的效率。

函数创建

1 2	# mysql8 增加了一个安全选项，需要执行一下代码才能创建函数 set global log_bin_trust_function_creators=TRUE;

delimiter 自定义符号    # 门主习惯使用一个 $ 作为结束标志
create function 函数名(形参列表) returns 返回类型 -- 注意是
retruns
begin
函数体 -- 若干sql语句，但是不要直接写查询
return val;
end 自定义符号
delimiter ;
-- 格式说明：
-- delimiter 自定义符号 是为了在函数内写语句方便，制定除了;之外的符号作为
-- 函数书写结束标志，一般用$$或者//   
-- 形参列表：形参名 类型 类型为mysql支持类型
-- 返回类型: 函数返回的数据类型，mysql支持类型即可
-- 函数体：若干sql语句组成
-- return: 返回指定类型返回值

-- 开启函数创建
set global log_bin_trust_function_creators=TRUE;
-- 创建无参数的函数
delimiter $$
create function func01() returns int
begin
return (select salary from s_emp order by salary desc limit
1);
end $$
delimiter ;
select func01();
select * from s_emp where salary=func01();

-- 创建包含参数的函数
delimiter $$
create function func02(eid int)
returns varchar(25)
begin
return (select last_name from s_emp where id=eid);
end $$
delimiter ;
select func02(1);

设置变量：

定义用户变量

-- 定义格式
set @[变量名] = 值;
-- 使用格式
@[变量名]

set @eid=10;
select func02(@eid);

定义局部变量

DECLARE variable_name datatype [DEFAULT initial_value];
-- variable_name 是变量的名称
-- datatype 是变量的数据类型
-- initial_value 是可选的初始值

DELIMITER $
CREATE FUNCTION get_total_price(num INT, price DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE total DECIMAL(10, 2);
    SET total = num * price;
    RETURN total;
END $
DELIMITER ;
select get_total_price(5,2.5);
-- 删除函数
drop function get_total_price;

存储过程创建

创建存储过程语法与创建函数基本相同，但是没有返回值。

delimiter 自定义符号
create procedure 存储过程名(形参列表)
begin
存储过程 -- sql语句构成存储过程语句集
end 自定义符号
delimiter ;
-- 说明：
-- delimiter 自定义符号 是为了在存储过程内写语句方便，制定除了;之外的符号
-- 作为函数书写结束标志
-- 形参列表：[IN | OUT | INOUT] 形参名 类型
-- in 输入 (只有里面可以用)
-- out 输出 (只有返回出给外面用)
-- inout 可以输入也可以输出 (里外都能用)
-- 存储过程：若干sql语句组成，如果只有一条语句也可以不写delimiter和
-- begin,end

-- 例如： 存储过程创建和调用
delimiter $$
create procedure proc01()
begin
select id,last_name from s_emp;
select id,salary from s_emp order by salary desc;
end $$
delimiter ;
call proc01();

drop procedure if exists proc_param;
-- 例如：分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure proc_param(OUT num int)
begin
select num;
set num=100;
select num;
end $$
delimiter ;
set @num=10;
call proc_param(@num);
-- 输出num变量的值
select @num;

扩展：如何根据日期创建31张不同的表呢？

create database envir;
create user 'envir'@'localhost' identified by 'envir';
grant all on envir.* to 'envir'@'localhost';
set names utf8;
use envir;
--使用存储过程建表
DROP PROCEDURE IF EXISTS create_table_do;
delimiter //
CREATE PROCEDURE create_table_do()
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i < 32 DO
		SET @STMT = CONCAT("DROP TABLE IF EXISTS `env_detail_",i,"`;");
		PREPARE STMT FROM @STMT; 
		EXECUTE STMT;
		SET @STMT = CONCAT("CREATE TABLE IF NOT EXISTS `env_detail_",i,
                    "`(`name` varchar(20) NOT NULL COMMENT '传感器名称',
                    `srcId` varchar(5) NOT NULL COMMENT '发送端id',
                    `dstId` varchar(5) NOT NULL COMMENT '树莓派系统id',
                    `sersorAddress` varchar(7) NOT NULL COMMENT '模块上传感器地址',
                    `count` int(2) NOT NULL DEFAULT '1' COMMENT '传感器个数',
                    `cmd`  varchar(5) NOT NULL  DEFAULT '3' COMMENT '指令标号(3表示需要接受数据  16表示需要发送数据)',
                    `data` float(9,4) NOT NULL COMMENT '采集的数据',
                    `status` int(2) NOT NULL DEFAULT '1'  COMMENT '状态标示(默认为1表示成功)',
                    `gather_date` timestamp NOT NULL COMMENT '采集时间')ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='数据详情日表';"
                    );
		PREPARE STMT FROM @STMT; 
		EXECUTE STMT;
    	SET i = i + 1;
	END WHILE;
END//
CALL create_table_do();

调用格式

调用存储过程语法：

1	call 存储过程名字([存储过程参数[,……]])

调用存储函数语法：

1	select 存储函数名字([函数参数[,……]])

使用show create语句查看存储过程和函数的定义

1	show create {procedure\|function} 存储过程或存储函数的名称

查看所有函数或者存储过程：

-- 格式1：查看数据库中函数和存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';
SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
-- 具体案例
SHOW PROCEDURE STATUS WHERE Db = 'briup';
SHOW FUNCTION STATUS WHERE Db = 'briup';
-- 格式2：查看指定数据库中 函数和存储过程的名称与类型
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'your_database_name';
-- 具体案例
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'briup';

删除存储过程或存储函数：

1
2
3

DROP {PROCEDURE | FUNCTION} [IF EXISTS] name
drop function if exists func01;
drop procedure if exists proc01;

04-事务索引视图

前言