	1.什么是视图
	我们在执行SQL语句其实就是对表进行操作，所得到的其实也是一张表，而我们需要经常对这些表进行操作，拼接什么的都会产生一张虚拟表，我们可以基于该表进行其他的操作，有时候我们需要经常使用，那么我们为了方便可使用以将虚拟表保存起来之后就称为"视图"，本质上其实就是一张虚拟表
	2.生成视图SQL语句
	create view 视图名 as SQL语句; 就可以产生一个视图供我们查看
	create vire vire_name as
	select column_name(s)
	from table_name
	where conditon
	3.视图注意事项
	3.1 在硬盘中，视图只有表结构文件，没有数据文件
	3.2 我们的视图通常只能用于查询并不能进行修改因为本来就是拼接来的，没有办法通过视图来修改别的表的数据
	3.3 视图使用的越多，那么你的系统就会占用越多的资源严重影响你的计算机的使用，所以一般建议少使用视图，尽量见面资源浪费

	1.修改运行符号;
	我们在使用mysql的时候可能会遇到关键字与执行键冲突的情况，那么我们就肯定遇到这种情况肯定就需要死一个，既然执行程序的符号有特殊含义，为了sql语句的完整性，那么我们就只有改自己的执行键（结束符）。
	语法结构：
	delimiter 你自己所设置的不与其它关键字冲突的执行键（结束符）
	编写我们所需要用到的分号的各种语句
	delimiter ; #最后我们需要修改回来
	2.触发器
	触发器其实就是针对标的增，删，改自动触发的功能（增前，增后，改前，改后，删前，删后）等操作时那么他就会激活并执行。触发器经常用语加强数据的完整性约束和业务规则等。触发器可以从DBA_TRIGGERS，USER_TRIGGERS 数据字典中查到。SQL的触发器是一个能由西永自动执行对数据库修改的语句。
	触发器可以查询其他表，而且可以包含负责的SQL语句。他们主要用于强制服从负责的业务规则或要求。触发器也可以用于强制引用完整性，以便再多个表中添加，更新或展出行时，保留在这些表之间所定义的关系，然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。
	语法结构：
	create trigger 触发器名称
	before/after inser/update/delete
	on 表名 for each row
	begin
	sql语句
	end # 结束必须输入end意味着这个触发器的结束
	3.触发器案例
	# 案例
	CREATE TABLE cmd (
	id INT PRIMARY KEY auto_increment,
	USER CHAR (32),
	priv CHAR (10),
	cmd CHAR (64),
	sub_time datetime, #提交时间
	success enum ('yes', 'no') #0代表执行失败
	);

	CREATE TABLE errlog (
	id INT PRIMARY KEY auto_increment,
	err_cmd CHAR (64),
	err_time datetime
	);

	delimiter $$ # 将mysql默认的结束符由;换成$$
	create trigger tri_after_insert_cmd after insert on cmd for each row
	begin
	if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
	insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
	end if;
	end $$
	delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了

	#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
	INSERT INTO cmd (
	USER,
	priv,
	cmd,
	sub_time,
	success
	)
	VALUES
	('kevin','0755','ls -l /etc',NOW(),'yes'),
	('kevin','0755','cat /etc/passwd',NOW(),'no'),
	('kevin','0755','useradd xxx',NOW(),'no'),
	('kevin','0755','ps aux',NOW(),'yes');

	# 查询errlog表记录
	select * from errlog;
	# 删除触发器
	drop trigger tri_after_insert_cmd;

	create table user(
	id int primary key auto_increment,
	name char(32),
	balance int
	);

	insert into user(name,balance)
	values
	('jason',1000),
	('kevin',1000),
	('tank',1000);

	# 修改数据之前先开启事务操作
	start transaction;

	# 修改操作
	update user set balance=900 where name='jason'; #买支付100元
	update user set balance=1010 where name='kevin'; #中介拿走10元
	update user set balance=1090 where name='tank'; #卖家拿到90元

	# 回滚到上一个状态
	rollback;

	# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
	commit;
	"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

	# 站在python代码的角度，应该实现的伪代码逻辑，
	try:
	update user set balance=900 where name='jason'; #买支付100元
	update user set balance=1010 where name='kevin'; #中介拿走10元
	update user set balance=1090 where name='tank'; #卖家拿到90元
	except 异常:
	rollback;
	else:
	commit;

	例如:
	刚插入第一条数据的时候，我们的默认事务id为1，实际是这样存储的
	\| username \| create_version \| delete_version \|
	\| joseph \| 1 \| \|
	我们可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1,1是这次插入操作的事务id然后我们将joseph改为了Alice，实际存储是这样的
	\| username \| create-version \| delete_version \|
	\| joseph \| 1 \| 2 \|
	\| Alice \| 2 \| \|
	还可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id当我们删除数据的时候，实际存储就是这样的
	\| username \| create_version \| delete_version \|
	\| Alice \| 2 \| 3 \|
	最后就是当我们呢查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来:
	1.当前是都id要大于或者等于当前行的delete_version值，这表示在事务开始前这行数据已经存在了
	2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除

	存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象，目的是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过定制存储过程的名字给参数（需要时）来调用执行。
	1.与存储过程相关方法
	1.1.查看存储过程信息
	show create procedure prol;
	1.2.查看所有存储过程
	show procedure status;
	1.3.删除存储过程
	drop procedure prol;
	2.实例演示
	delimiter $$
	create proceduer p1(
	in m int,
	in n int,
	out res int
	)
	bgein
	select tname from teacher where tid > m and tid < n;
	set res=0;
	end $$
	delimiter ;
	3.针对res需要提前定义
	set @res=0; 定义
	select @res; 查看
	call p1(1,5,@res); 调用
	select @res; 查看

	我们其实可以在mysql中用过help的方式得到该内置函数的使用方法，mysql中的内置使用方法还挺好的说的很详细
	1.移除指定字符
	Trim、LTrim、RTrim
	select '[mobile]',concai('[',trim('molile')']');
	2.大小写转换
	Lower、Upper
	select lower('blue'),lower('blue');
	3.获取左右起始指定个数字符
	Left、Right
	select left('mysql',2)
	4.返回读音相似的值（只对英文有效果）
	Soundex
	首先我们首先需要一张表并且这个表中有名称读音相近的名称
	\|name\|
	\|json\|
	\|jason\|
	例如：where Soundex(name)=Soundex('jason')
	5.日期格式
	date_fromat
	在MySQL中表示时间格式尽量采用2022-02-22形式来表示
	create table blog(
	id int primary key auto_incrment,
	name varchar(32),
	sub_time datetime
	);
	insert into blog (name,sub_time)
	values
	('第1篇','2015-03-01 11:31:21'),
	('第2篇','2015-03-11 16:31:21'),
	('第3篇','2016-07-01 10:21:31'),
	('第4篇','2016-07-22 09:23:21'),
	('第5篇','2016-07-23 10:11:11'),
	('第6篇','2016-07-25 11:21:31'),
	('第7篇','2017-03-01 15:33:21'),
	('第8篇','2017-03-01 17:32:21'),
	('第9篇','2017-03-01 18:31:21');
	select date_format(sub_time,'%Y-%m'),
	count(id) from blog group by
	date_format(sub_time,'%Y-%m');
	1.where Date_fromat(sub_time) = '2021-1-06' # 返回制定默认值
	2.where Year(sub_time)=2021 and Month(sub_time)=07; # 从指定日期值来获取年分值
	更多操作
	addate 增加一个日期
	addtime 增加一个时间
	datediff 计算两个日期的差值

	mysql中的流程控制使用的一般是if、loop、iterate、repeat、while语句来控制流程我们简单地实验几种
	1.if条件判断语句
	delimiter $$
	cerate procedoure proc_if()
	begin
	declare i int default 0;
	if i = 1 then
	select 1;
	elself i = 2 then
	select 2;
	else
	select 7;
	end if;
	end $$
	delimiter ;

	2.while循环，在mysql中只有while循环没有我们的for循环
	delimiter $$
	cerate procedure proc_while()
	begin
	declare num int;
	set num = 0;
	hwile num < 10 do
	select
	num;
	sey num = num + 1;
	end while;
	end $$
	delimiter ;

	3.case语句
	delimiter $$
	case
	when i_stall_id = 2 then
	set @x1 = @x1 + d_amount;
	else
	set @x2 = @x2 +d_amount;
	end acse
	end $$
	delimiter ;

	#1. 准备表
	create table s1(
	id int,
	name varchar(20),
	gender char(6),
	email varchar(50)
	);

	#2. 创建存储过程，实现批量插入记录
	delimiter $$ #声明存储过程的结束符号为$$
	create procedure auto_insert1()
	BEGIN
	declare i int default 1;
	while(i<3000000)do
	insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
	set i=i+1;
	end while;
	END$$ #$$结束
	delimiter ; #重新声明分号为结束符号

	#3. 查看存储过程
	show create procedure auto_insert1\G

	#4. 调用存储过程
	call auto_insert1();
	# 表没有任何索引的情况下
	select * from s1 where id=30000;
	# 避免打印带来的时间损耗
	select count(id) from s1 where id = 30000;
	select count(id) from s1 where id = 1;

	# 给id做一个主键
	alter table s1 add primary key(id); # 速度很慢

	select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级
	select count(id) from s1 where name = 'jason' # 速度仍然很慢


	"""
	范围问题
	"""
	# 并不是加了索引，以后查询的时候按照这个字段速度就一定快
	select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多
	select count(id) from s1 where id >1 and id < 3;
	select count(id) from s1 where id > 1 and id < 10000;
	select count(id) from s1 where id != 3;

	alter table s1 drop primary key; # 删除主键单独再来研究name字段
	select count(id) from s1 where name = 'jason'; # 又慢了

	create index idx_name on s1(name); # 给s1表的name字段创建索引
	select count(id) from s1 where name = 'jason' # 仍然很慢！！！
	"""
	再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
	那这个树其实就建成了“一根棍子”
	"""
	select count(id) from s1 where name = 'xxx';
	# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
	select count(id) from s1 where name like 'xxx';
	select count(id) from s1 where name like 'xxx%';
	select count(id) from s1 where name like '%xxx'; # 慢最左匹配特性

	# 区分度低的字段不能建索引
	drop index idx_name on s1;

	# 给id字段建普通的索引
	create index idx_id on s1(id);
	select count(id) from s1 where id = 3; # 快了
	select count(id) from s1 where id*12 = 3; # 慢了索引的字段一定不要参与计算

	drop index idx_id on s1;
	select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
	# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
	create index idx_name on s1(name);
	select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速

	drop index idx_name on s1;
	# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

	create index idx_id on s1(id);
	select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了先通过id已经讲数据快速锁定成了一条了
	select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了基于id查出来的数据仍然很多，然后还要去比较其他字段

	drop index idx_id on s1

	create index idx_email on s1(email);
	select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快通过email字段一剑封喉

	select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
	# 如果上述四个字段区分度都很高，那给谁建都能加速查询
	# 给email加然而不用email字段
	select count(id) from s1 where name='jason' and gender = 'male' and id > 3;
	# 给name加然而不用name字段
	select count(id) from s1 where gender = 'male' and id > 3;
	# 给gender加然而不用gender字段
	select count(id) from s1 where id > 3;

	# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
	create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放
	select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快

	【初始化介绍】
	一颗b+树,浅蓝色的块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),
	如磁盘块1包含数据项17和35,包含指针P1、P2、P3,
	P1表示小于17的磁盘块,P2表示在17和35间的磁盘块,P3表示大于35的磁盘块。
	真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99
	非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
	【查找过程】
	如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次O,在内存中用二分查找确定29在17和35之间,锁定磁
	盘块1的P2指针,内存时间因为非常短(相比磁盘的O)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存
	,发生第二次1O,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次1,同时内存中做二分查找找
	到23,结束查询,总计三次。
	（1）MyISAM: MyISAM中主键索引和辅助索引采用的都是B+树作为索引存储结构，叶子节点存储的都是数据地址，即索引和数据是分开存储的也叫非聚集索引
	（2）InnoDB: INNODB的主键索引和辅助索引采用的是B+树作为索引存储结构，在主键索引的叶子节点存放的是主键及所有数据，辅助索引的叶子节点存储的是关键字和主键值，查询其他数据还需要借助主键索引，INNODB的数据和索引存放在一起，也把INNODB叫做聚集索引.这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

joseph-bright

MySQL事务概念与流程和索引控制

MySQL事务概念与流程和索引控制

视图

触发器

事务

事务四大特性

事务的其他特性

实例演示

知识补充

隔离级别

MVCC多版本并发控制

MVCC定义

MVCC注意事项

存储过程

内置函数

流程控制

索引

索引底层原理

慢查询优化

测试索引

联合索引

树（B数，B+数，B*数，红黑树，二叉树）

公告

搜索

常用链接

随笔分类

随笔档案

相册

阅读排行榜

目录导航