学习内容概要

• 视图
• 触发器
• 存储过程
• 事物
• 内置函数
• 流程控制、循环结构
• 索引与慢查询

内容详细

视图

• 解释：SQL语句执行的结果为一张虚拟表 我们基于这张虚拟表去做其他操作
• 含义：如果需要频繁的使用这张基于SQL语句执行后得出的这张虚拟表 我们可以将这张虚拟表 保存起来 我们把保存后这张表叫做 “ 视图 ” （本质就是一张虚拟表）

# 视图的语句格式：
create view 视图名 as SQL语句：


# 例如:
create view emp2dep as select * from emp inner join dep on emp.dep_id = dep.id; 


# 结果：上述功语句为 emp表和dep表做内拼接 因为业务需要经常的用到这个虚拟表 所以我们就基于这个做虚拟表 做了一个视图 起名为emp2dep 以后在用到这个表的时候 就可以直接调用了  

select * from emp2dep(展示出这个表中的数据)

# 告知：
"""
1.在硬盘中 视图只有表结构 没有表数据文件（虚拟的 不存在） 

2.视图通常是用于查询  不能修改视图中的数据

3.尽量的少去用视图（因为如果创建视图很多 会视觉混淆 分不清表和视图 降低效率  也会浪费硬盘空间）
"""

# 删除视图：drop view 视图名

触发器

• 本质：
  • 针对表数据的增、删、改 自动触发的功能（增前、增后 、删前、删后、改前、改后）

# 语法结构：
create trigger 触发器的名字 before(前)/after(后) insert(增)/delete(删)/update(改) on 表名 for each row begin
	sql语句
end

# 注意：
	触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
    所以为了写出完整触发器的代码 需要临时修改SQL语句中的默认的结束符
    delimiter $$
   	用$$代替SQL中的结束符 让;可以在触发器中 正常的使用 
    delimiter ;
    改回正常的结束SQL语句的符号
    
    
# 案例：
       # 创建表cmd（类似于记录命令）
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代表执行失败
);
       # 创建表errlog（记录插入cmd中的错误数据）
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  # 触发器的名字 tri_after_insert_cmd (tri 触发器名字的前缀 after后的意思 insert 插入 意思为 针对cmd这个表 插入数据之后触发的命令)
    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;
    
 

事物

• 1.mysql中的事务（Transaction）是什么？有什么用？

  • 简单来说，事务就是用户定义的一个数据库操作序列，这些操作要么全做要么全都不做，是一个不可分割的工作单位，一般是指要做的或所做的事情。

  • 我们也可以理解为事务是由一个或多个SQL语句组成

• 如果其中有任何一条语句不能完成或者产生错误，那么这个单元里所有的sql语句都要放弃执行！

  • 只有事务中所有的语句都成功地执行了，才可以说这个事务被成功地执行！

• 2.事物分为四大特征(ACID)

  特征	含义
  原子性（Atomicity）	原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
  一致性（Consistency）	在一个事务中，事务前后数据的完整性必须保持一致，可以想象银行转账、火车购票
  隔离性（Isolation）	多个事务，事务的隔离性是指多个用户并发访问数据库时， 一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离
  持久性（Durability）	持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

• 3.事务有什么用？事务安全是什么？

• MySQL 事务主要用于处理操作量大，复杂度高的数据

  • 事务安全：为了保证一系列操作的结果保持同步，保证数据的完整性

  • 事务的作用：比如我们在淘宝进行购物（存在事务），当我们成功提交订单并在提示已经付完款时，突然断网，此时我们再刷新页面可以发现，显示已经成功付款。 相反（没有事务），如果我们明明付过款了，就因为突然断网，再刷新页面时，就会显示重新付款！

# 实例演练：
     # 创建表
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 命令后 就等于确定修改 不能在执行回滚了

"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行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;

扩展知识点(重要)

MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT

事务处理中有几个关键词汇会反复出现
"""

  事务(transaction)
  
  回退(rollback)
  
  提交(commit)
  
  保留点(savepoint)



"""

		为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
    
    """
    创建占位符可以使用savepoint
    	savepoint sp01;
    	
    回退到占位符地址
    	rollback to sp01;
    
    """

    # 保留点在执行rollback或者commit之后自动释放
    
 
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
	set transaction isolation level 级别

1.read uncommitted(未提交读)
	事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
	大多数数据库系统默认的隔离级别
  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读)		# MySQL默认隔离级别
	能够解决"脏读"问题，但是无法解决"幻读"
  所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
	强制事务串行执行，很少使用该级别
    
 
事务日志可以帮助提高事务的效率 
	存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
  事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

MVCC 多版并发控制

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC

  一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号

每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

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

存储过程

类似于python中的自定义函数

delimiter 临时结束符
create procedure 名字(参数,参数)
begin
	sql语句;
end 临时结束符
delimiter ;

delimiter $$
create procedure p1(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select tname from teacher where tid > m and tid < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要先提前定义
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

"""
查看存储过程具体信息
	show create procedure pro1;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
"""

内置函数

• 可以通过 help 函数名 查看帮助信息！
• 1.字符出函数

• 2.日期函数

# .日期格式:date_format

'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
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');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	    增加一个日期 
	addtime	    增加一个时间
	datediff    计算两个日期差值

    
# 返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
		但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
		where Soundex(name)=Soundex('J.Lie')
"""

流程控制

• 分支结构之if

# 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

# 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 ;

索引

• 1 . 索引含义：

  • 索引就好比一本书的目录，它能让你更快的找到自己想要的内容
  • 让获取的数据更有目的性，从而提高数据库检索数据的性能。

• 2 . 索引在MySQL中也叫做 “ 键 ” 是存储引擎用于快速找到记录的一种数据结构

  • primary key(主键)
  • unique key(唯一键)
  • index key(普通索引)
  • 上述的三种键在数据查询的时候使用都可以加快查询的速度
    • primary key、unique key除了可以加快数据查询还有额外的限制
    • index key只能加快数据查询 本身没有任何的额外限制

• 3 . 聚集索引：其实指的就是 primary key 主键 innodb 引擎规定一张表必须要有一个主键

• 4 . 辅助索引：unique 、index 为辅助索引 在需要用到name age 作为索引条件是 因主键已经被id 所用 不能同时出现两个主键 所以只能用辅助索引来处理 一般用（index）

• 5 . 覆盖索引：在辅助索引的条件中就已经拿到所需要拿到的结果

• 6 . 非覆盖索引：在辅助索引中去查找其他数据 辅助索引需要去到主键索引 然后通过主键索引 去筛选条件后 拿到想要查询字段的值