MySQL之视图、触发器、事务、索引及其他知识补充

一、视图

视图是将sql语句的查询结果当做虚拟表实体化保存起来，以后可以反复使用

create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

drop view teacher2course;
-- 视图使用频率不高

二、触发器(trigger)

触发器：满足特点条件之后自动执行。
在Mysql只有三种情况下可以触发：

• 针对表的增
  • 增前
  • 增后
• 针对表的改
  • 改前
  • 改后
• 针对表的删
  • 删前
  • 删后

语法结构

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
	sql语句
end

触发器名字在命名的时候推荐使用如下方式：

tri_after_insert_t1、tri_before_delete_t1

如何临时修改SQL语句的结束符：

delimiter $$

在mysql中NEW特指数据对象可以通过点的方式获取字段对应的数据

id    name	pwd  hobby
1     jason  123  read
NEW.name  >>>  jason

案例：

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
    ("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;
-- 查看触发器
show triggers;
-- 删除触发器
drop trigger tri_after_insert_cmd;

三、事务（重要）

3.1 四大特性（ACID）

• A：原子性

  每个事务都是不可分割的最小单位(同一个事务内的多个操作要么同时成功要么同时失败)

• C：一致性

  事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

• I：隔离性

  事务与事务之间彼此不干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• D：持久性

  持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的；接下来的其他操作或故障不应该对其有任何影响

3.2 事务相关操作

start transcation;  -- 开启事务
  	诸多SQL操作
rollback;  -- 回滚到操作之前的状态
commit;  -- 确认事务操作，之后不能回滚

示例如下 ：

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

insert into user(name,balance)
values
("jason",1000),
("eGon",1000),
("tank",1000);

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

-- 修改操作
update user set balance=900 where name="jason";   -- 买支付100元
update user set balance=1010 where name="egon";   -- 中介拿走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="egon"; #中介拿走10元
    update user set balance=1090 where name="tank"; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

四、存储过程

类似于Python中的自定义函数。

4.1 无参存储过程

delimiter $$
create procedure p1()
begin
	select * from user;
end $$
delimiter ;
-- 调用
call p1();

4.2 有参存储过程

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

4.3 python代码操作存储过程

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    passwd="456852",
    db="db6",
    charset="utf8",
    autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p2",(1,3,10))
# @_p1_0=1,@_p1_1=3,@_p1_2=10;
print(cursor.fetchall())

五、函数

相当于Python中的内置函数。

ps：可以通过 help 函数名 查看帮助信息!

• 移除指定字符

  Trim()、LTrim()、RTrim()
  

• 大小写转换

  Lower()、Upper()
  

• 获取左右起始指定个数字符

  Left()、Right()
  

• 返回读音相似值

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

• 日期格式

  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");
  
  
  -- 更多日期处理相关函数 
  where Date(sub_time) = "2015-03-01"
  where Year(sub_time)=2016 AND Month(sub_time)=07;
  	aDDDate	增加一个日期 
  	addtime	增加一个时间
  	datediff计算两个日期差值
    ...
  

六、流程控制

-- if判断
if i = 1 THEN
       SELECT 1;
ELSEIF i = 2 THEN
       SELECT 2;
ELSE
       SELECT 7;
END IF;

-- while循环
SET num = 0 ;
WHILE num < 10 DO
    SELECT
        num ;
    SET num = num + 1 ;
END WHILE ;

七、索引与慢查询优化（重要）

索引可以简单的理解为帮助你加快数据查询速度的工具，也可以把索引比喻成书的目录。

索引的建立涉及到几种数据结构：

• B树
• B+树（叶子节点添加了指针）
• B*树（基于B+树在枝节点也添加了指针）

将某个字段添加成索引就相当于依据该字段建立了一颗B+树，从而加快查询速度。

如果某个字段没有添加索引，那么依据该字段查询数据会非常的慢（遍历查找）。

7.1 索引分类

1. primary key

   主键索引除了有加速查询的效果之外，还具有一定的约束条件；

2. unique key

   唯一键索引，除了有加速查询的效果之外，还具有一定的约束条件；

3. index key

   普通索引，只有加速查询的效果，没有额外约束条件；

注意外键 foreign key 不是索引，它仅仅是用来创建表与表之间关系的。

7.2 创建索引

-- 创建唯一索引需要提前排查是否有重复数据
select count(字段) from 表名;
select count(distinct(字段)) from 表名;

-- 查看当前表内部索引值
show index from 表名;
	
-- 创建主键索引
alter table t1 add primary key pri_id(id);
	
-- 创建唯一索引
alter table t1 add unique key uni_age(age);

-- 创建普通索引
alter table t1 add index idx_name(name);

-- 前缀索引(属于普通索引)
"""
避免对大列建索引,如果有就使用前缀索引
eg:博客内容 百度搜索内容等
"""
alter table t1 add index idx_name(name(4));

-- 联合索引(属于普通索引，遵循最左匹配原则)
alter table t1 add index idx_all(name,age,sex);

-- 删除索引
alter table t1 drop index 索引名(idx_name、idx_all...);

7.3 explain句式

• 全表扫描：不走索引，遍历表查找数据，效率极低，生产环境下尽量不要书写类似SQL；
• 索引扫描：走索引，加快数据查询，建议书写该类型SQL；

explain 就是帮助我们查看SQL语句属于那种扫描。

常见的索引扫描类型：

1. index
2. range
3. ref
4. eq_ref
5. const
6. system
7. null

从上到下，性能从最差到最好，生产环境下认为至少要达到range级别。

不走索引情况（熟悉四条及以上）：

• 没有查询条件，或者查询条件没有建立索引；

• 查询结果是原表中的大部分数据（%25以上）；

• 索引本身失效，统计数据不真实；

• 查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)；

• 隐式转换导致索引失败；

  eg：字段是字符串类型，查询使用整形

• <> ，not in不走索引

  单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成uNIOn。

• like "%_" 百分号在最前面不走；

• 单独引用联合索引里非第一位置的索引列（不遵循最左匹配原则）；

索引的创建会加快数据的查询速度，但是一定程度会拖慢数据的插入和删除速度。

八、隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改。

InnoDB支持所有隔离级别：set transaction isolation level

• read uncommitted(未提交读)

  事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"

• read committed(提交读)—大多数数据库系统默认的隔离级别

  一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"

• repeatable read(可重复读)—MySQL默认隔离级别

  能够解决"脏读"问题，但是无法解决"幻读"

  所谓"幻读"指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(mvcC)及间隙锁策略解决了该问题。

• serializable(可串行读)

  强制事务串行执行，很少使用该级别

原文地址：https://www.cnblogs.com/JZjuechen/arcHive/2022/02/24/15929882.html