mysql基础知识(六)

mysql之视图view

什么是视图？

视图就是通过查询得到的一张虚拟表，然后保存下来，下次直接进行使用即可。

即：将SQL语句的查询结果当做虚拟表保存起来，以后可以反复进行使用

视图的作用？

如果要频繁使用一张虚拟表，那么通过视图的方式就可以不用重复查询

比如：我们来查看两张表的连接(emp：员工表和dep：部门表)查看员工对应的部门

select * from emp inner join dep on emp.dep_id=dep.did;

结论：如果想要多表查询的话每次都要输入这一串sql命令才可以看到结果。
 
假设：这张表的数据非常多 我们要一点点分析，这样的话我们是不是分析一段就要再输入一边sql语句再去看去分析，如果这条sql语句非常长的话过于繁琐。
 
解决：所以这里用到上述的视图(将sql语句的结果保存下来可以反复使用)来解决这个问题

如何使用视图：

		语法结构：create view 视图表名 as 需要保存为视图的SQL语句；
		
		实例：create view emp_dep as select * from emp inner join dep on emp.dep_id=dep.id;
		
		
补充：字段名冲突无法建立视图
修改字段名：alter table <表名> change <字段名> <字段新名称> <字段的类型>。
查看视图结果是否与原sql语句结果相等：select * from emp_dep;

删除视图：drop view 视图名;
	eg ：drop view emp_dep;
	
	
1、在硬盘中，视图只有表结构，没有表的数据文件(数据来源据原表)
2、视图通常是用来查询，尽量不要修改视图中的数据：因为修改视图数据很有可能修改原表数据
 

mysql之触发器trigger

定义：在满足特定条件后自动执行
在MySQL只有三种情况下可以触发
    1、针对表的增
    	增加数据前 增加数据后 
    2、针对表的改
    	修改数据前 修改数据后
    3、针对表的删
    	删除数据前 删除数据后
        
        
语法结构：
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row begin
	sql语句
end
 
触发器的名字推荐使用下列方式(见名知意)：
	tri_after_insert_t1  ： 在给t1表插入数据之后添加一个触发器

# 六种情况演示

增加：
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
	sql语句
end   # 增加之后触发
 
create trigger tri_before_insert_t1 before insert on 表名 for each row
begin
	sql语句
end   # 增加之前触发
 
修改：
create trigger tri_after_update_t1 after update on 表名 for each row
begin
	sql语句
end   # 修改之后触发
 
create trigger tri_before_updata_t1 before update on 表名 for each row
begin
	sql语句
end   # 修改之前触发
 
删除：
create trigger tri_after_delete_t1 after delete on 表名 for each row 
begin
	sql语句
end   # 删除之后触发
 
create trigger tri_before_delete_t2 before delete on 表名 for each row
begin
	sql语句
end   # 删除之前触发

需要注意：在书写sql语句的时候结束符是; 而整个触发器结束时也需要用到分号;就会出现语法冲突，需要修改我们的结束符号。
临时修改sql语句的结束符号：只在当前窗口有效
结构： delimiter 新符号
验证： delimiter $$

# 案例
create table cmd (
    id int primary key auto_increment,   # 主键字段
    USER char (32),
    priv char (10),   
    cmd char (64),   	# cmd命令字段
    sub_time datetime, 	# 提交时间
    success enum ('yes', 'no')
);
 
# 错误日志表
create table errlog (
    id int primary key auto_increment,
    err_cmd char (64),
    err_time datetime
);
 
# 创建触发器
delimiter $$  # 将mysql默认结束符;换为$$
create trigger tri_afrer_insert_cmd after insert on cmd for each row  # 在insert插入数据之后创建触发器
begin  # 触发器代码
	if NEW.success = 'no' then  # mysql中if语句固定格式
		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
                        ('tony','0755','ls -l /etc',NOW(),'yes'),
                        ('tony','0755','cat /etc/passwd',NOW(),'no'),
                        ('tony','0755','useradd xxx',NOW(),'no'),
                        ('tony','0755','ps aux',NOW(),'yes');
                        
# 查询errlog表记录
select * from errlog;
# 结果：在插入数据后自动给errlog表插入里两条数据 并且这两条数据为插入success为'no'的

# 查看触发器
show triggers;

# 删除触发器
格式：drop trigger 触发器名字;
eg： drop trigger tri_after_insert_cmd;

mysql之事务及四大特性

事务四大特性(ACID):
    A: 原子性
        每个事务都是不可分割的最小单位(同一个事物内的多个操作要么同时成功要么同时失败)
    C: 一致性
        执行完事务之后数据库的数据状态(从一个状态变为另一个状态)
    I: 隔离性
        事务与事务之间彼此不干扰
    D: 持久性
        一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

我们来看一个现象：
准备三个身份：
张三
ATM机
李四
 
张三用建行的卡通过ATM机给工行李四转10000元，
那么建行就会给张三的卡减10000元
工行就会给李四的卡增加10000元。
在转钱的过程中工行的系统出现了问题，张三的卡明明已经扣了10000元，但是李四的卡因工行系统问题并没有收到那10000元。
这种情况在之前是常有发生的。
 
那么怎么解决这个问题呢？
这里发明了事务：同时改变状态，要么同时成功要么同时失败。(我不增加钱对方就不会减钱，对方减了钱数那我肯定增加钱)

# 开启事务：(下面的所有操作都包含在一个事务里边：下面的操作要么同时成功要么同时失败 可以回退)
start transaction;
 
# 如何回滚：
rollback;
 
# 如何确认：
commit;

实例验证：

create table user(
    id int primary key auto_increment,
    name char(32),
    balance int	   		# 账户余额
);
 
insert into user(name,balance) values ('jason',1000),('jack',1000),('tony',1000);
 
start transaction;   	# 开启事务
 
# 修改操作
update user set balance=900 where name='jason';  # 支付100元
update user set balance=1010 where name='jack';  # 中介拿走10元
update user set balance=1090 where name='tony';  # 收款方收到90

但是在事务里边这个数据还没有到硬盘中，还没有保存。
现在这个状态还可以回退(我反悔了我不买了)
回滚操作：rollback;

确认数据：确认支付成功不反悔了
commit;

结论：在事务下面的操作 要么同时成功要么同时失败可以回退，事务在提交后是在内存中，没有刷新到硬盘必须执行commit;确认才会刷新到硬盘 整个事务结束。

mysql之存储过程

定义：相当于python中的自定义函数 自己写一个功能
关键字：procedure
格式 ：create procedure 名字(参数)
       begin
    	   功能体代码块
       end 
调用：call 存储过程名字

1、无参数存储过程
delimiter $$    # 修改默认结束符号
create procedure p1()   # 创建p1存储过程
begin
	select * from user;  # 查看user表数据
end $$
delimiter ;    # 修改回默认结束符
 
call p1()   # 调用

2、有参存储过程
delimiter $$
create procedure p2(
    in m int,    # in表示则个参数必须只能是传入不能被返回出去 int表示在传值时只能传int类型
    in n int,
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from user where id > m and id < n;
    set res=0;   # 用来标志存储过程是否执行(后面演示)
end $$
delimiter ;
 
# 针对res需要提前定义
set @res=10;   # 定义  # 指定res=10
select @res;   # 查看
call p1(1,5,@res)   # 调用
select @res    # 查看

查看存储过程具体信息:
	show create procedure 存储过程名字;
查看所有存储过程：
	show procedure status;
删除存储过程
	drop procedure 存储过程名字;

mysql之函数及流程控制

#  相当于python中的内置函数，系统自带的，作者编写好的功能
注意 ：注意与存储过程的区别，mysql内置的函数只能在sql语句中使用
补充 ：可以通过 (help 函数名) 的方式查看帮助信息

1、移除指定字符(相当于python的.strip())
Trim  ：移除左右两边指定字符
LTrim ：移除左侧指定字符
RTrim ：移除右侧指定字符

2、大小写转换
Lower ：全部转换为小写
upper ：全部转换为大写
 
# 应用于图片验证码 ：图片验证码为大写和小写结合，但是我们通常全部输入小写或者全部输入大写就可以，这就应用到了用户输入后，全部转换为大写/小写在匹配。

3、获取左右起始指定个数字符
left : 从左起始
rigth: 从右起始

4、返回读音相似的值(只对英文效果)
soundex

5、日期格式：date_format
%Y:年
%m:月
%d:日
%H:时
%M:分
%S:秒
%X:时分秒

# 在mysql中表示时间格式尽量采用2022-1-1的形式
create table blog(
    id int primary key auto_increment,
    name char(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');  # group by分组时间格式为年月分组并且count计数

date ：年月日
where date(sub_time) = '2015-03-01'
year ：年份
where year(sub_time) = 2016
month : 月份
where month(sub_time) = 07
 
adddate 增加一个日期
addtime 增加一个时间
datediff  计算两个日期的差值

# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;


# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

mysql索引与慢查询优化

索引：简单的理解为可以帮助用户加快数据查询速度的工具，也可以把索引比喻成一个书的目录，字典的查字表等，更快的搜寻到用户想要的数据。
 
 
数据结构是计算机存储、组织数据的方式。数据结构是指相互之间存在一种或多种特定关系的数据元素的集合。通常情况下，精心选择的数据结构可以带来更高的运行或者存储效率。数据结构往往同高效的检索算法和索引技术有关。

1、peimary key
	主键索引 除了有加速拆卸你的效果之外，还具有一定的约束条件
2、unique key
	唯一键索引 除了有加速查询的效果之外，还有一定的约束条件
3、index
	普通索引 只有加速查询的效果，没有额外的约束
4、foreign key
	注意：外键不是索引，仅仅是用来创建表与表之间的关系的

树:是一种数据结构 主要用于优化数据查询的操作

二叉树：它是一种查找效率非常高的数据结构，它有三个特点。
 
    (1)每个节点最多只有两个子树。
 
    (2)左子树都为小于父节点的值，右子树都为大于父节点的值。
 
    (3)在n个节点中找到目标值，一般只需要log(n)次比较。
    
二叉查找树的结构不适合数据库，因为它的查找效率与层数相关。越处在下层的数据，就需要越多次比较。极端情况下，n个数据需要n次比较才能找到目标值。对于数据库来说，每进入一层，就要从硬盘读取一次数据，这非常致命，因为硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。

B树是对二叉查找树的改进。它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。

b树的特点：
(1)一个节点可以容纳多个值。
 
(2)除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好。
 
(3)子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。

假设我们找 id=38的数据
select * from user where id=38; 
如果我们不使用任何树形结构 我们要从1找到38 这样需要找38次
 
如果我们使用树形结构来找这个数据：
一切都从根节点出发：
		1、先判断根节点5(这里不能确定是否在这个根节点下)
		2、再判断根节点28(这里也不能确定是否在这个跟节点下)
		3、再判断根节点65(这里可以确定 要找的38在28根节点下 因为65代表这个根节点下最小的值)
		4、往根节点28的枝节点找(枝节点也分为三个节点(三枝))
		5、这里可以把枝节点看作为根节点同上操作找到枝节点35
		6、最后找到了38
		
我们可以看到通过b树我们只需要三步就可以找到想要的数据 对比从头找38次 显然是大大所见的查询速度

B+树:
       只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)

我们可以看到b+树和b树有不同的地方就是在叶子节点处添加了'指针'
那么这个指针的作用是什么呢 我们同样使用范围查找的例子来看一下：
select * from user where id > 38 and id <73;
 
	一切都从根节点出发：
		1、先判断根节点5(这里不能确定是否在这个根节点下)
		2、再判断根节点28(这里也不能确定是否在这个跟节点下)
		3、再判断根节点65(这里就可以判断要找到范围数据在28和65根节点下)
		4、找到根节点28的枝节点判断后在35和56枝节点下
		5、找到根节点65的枝节点判断后在65枝节点下
		6、在35枝节点下找到了符合数据38和50
不同处 ： 7、这里不在'回头'找，直接通过指针往后找找到56、60、63再通过指针往后找65、73

结论：精确查找情况下b+树 和 b树是没有区别的,都是3次IO因为不需要走'指针'
	 范围查找数据:b+树只需要5次IO 在b树的基础上更加的提升的查找的效率。

B*树
    	在树节点添加了通往其他节点的通道 减少查询次数
    	
    	
我们可以看到b*树是在枝节点的时候就添加了指针，这样更大程度上的缩减了查询速度。

结论：
b树    枝节点和叶子节点没有指针
b+树   叶子节点添加了指针
b*树   枝节点添加了指针(叶子节点也有)
 
无论是树结构的每一次'进化'都是为了提升范围查看的效率，如果是精确查找那么只根树的'高度'有关，树有几层就查找几次

全表扫描：不走索引，一行行查找数据，效率极低，生产环境下尽量不要书写类似sql
索引扫描：走索引，加快数据查询，建议书写该类型sql
 
explain则就是用来判断该条sql语句属于那种扫描
 
具体使用：在具体的sql语句前添加explain即可
 
# 如果返回结果type为all则表示没有走索引扫描 只要不为all就说明走索引扫描了。

索引扫描的类型：
1)index
2)range
3)ref
4)eq_ref
5)const
6)system
7)null
 
# 从上到下，性能从最差到最好，我们认为至少要达到range级别
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
 
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
 
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
 
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
 
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
			  如将主键置于where列表中，MySQL就能将该查询转换为一个常量
    
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

1.没有查询条件，或者查询条件没有建立索引
	eg:select * from table;
在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。
 
2.查询结果集是原表中的大部分数据，应该是25％以上
	如果业务允许，可以使用limit控制。
	结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
 
 
3.索引本身失效，统计数据不真实
    索引有自我维护的能力。
    对于表内容变化比较频繁的情况下，有可能会出现索引失效。
    重建索引就可以解决
 
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
错误的例子：select * from test where id-1=9;
错误的例子：select * from test where id-1=9;

隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
	set transaction isolation level 级别
 
1.read uncommitted(未提交读)
	事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
	大多数数据库系统默认的隔离级别
  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读)		# MySQL默认隔离级别
  能够解决"脏读"问题，但是无法解决"幻读"
  所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
	强制事务串行执行，很少使用该级别