MySQL数据库学习笔记

今日内容概要

• 视图
• 触发器
• 事务
• MVCC多版本并发控制
• 存储过程
• 内置函数
• 流程控制
• 索引

今日内容详细

视图

视图的概念

通过SQL语句的执行得到的一张虚拟表，保存下来之后就称之为视图。

视图的作用

如果需要频繁的使用一张虚拟表，可以考虑制作成视图，降低操作难度。

视图的制作

create view 视图名 as SQL语句;

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

注意:

1.在硬盘中，视图只有表结构文件，没有表数据文件

2.视图通常是用于查询，尽量不要修改视图中的数据

总结：保存的视图会占据在库中的表格里，也会占用资源；视图如果过多会造成库下的表混乱；而且库不支持增删改。

触发器

触发器的概念

针对表数据的增、改、删自动触发的功能(增前、增后、改前、改后、删前、删后)。

触发器的作用

专门针对表数据的操作，定制个性化配套功能。

触发器种类：表数据新增之前、新增之后；表数据修改之前、修改之后；表数据删除之前、删除之后。

触发器的创建

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

'''
触发器的名字一般情况下建议采用下列布局形式
    tri_after_insert_t1
    tri_before_update_t2
    tri_before_delete_t3
'''

注意触发器内部的SQL语句需要用到分号 但是分号又是SQL语句默认的结束符
所以为了能够完整的写出触发器的代码 需要临时修改SQL语句默认的结束符
    delimiter $$
    编写需要用到分号的各种语句
    delimiter ;

具体案例

# 1.先创建两张表
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
);

# 2.需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
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 ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#3.往表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;

事务

事务的概念

事务可以包含诸多SQL语句并且这些SQL语句，要么同时执行成功，要么同时执行失败。这是事务的原子性特点

事务的作用

1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。

2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务的特性

1 、A：原子性
事务是数据库的逻辑工作单位，一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立。
2 、C：一致性
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
3 、I：隔离性
一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4 、D：持久性
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

具体使用

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;

扩展知识点

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值，这表示在事务开始之后这行记录才被删除。
"""

存储过程

mysql的存储过程类似于python中的自定义函数。

delimiter 临时结束符
create procedure 名字(参数,参数)
begin
    sql语句;
end 临时结束符
delimiter ;
# 相当于调用函数
call 名字()

类似于有参函数

 delimiter $$
  create procedure p1(
      in m int,  # in表示这个参数必须只能是传入不能被返回出去
      in n int,
      out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
  )
  begin
      select tname from userinfo where id > m and id < 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;
"""

内置函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！.

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

1.移除指定字符

ltrim(str):去除字符串左边的空格
rtrim(str):去除字符串右边的空格
trim(str): 去除字符串两边的空格

SELECT TRIM("    RUNOOB       ");

2.大小写转换

Lower(str):将字符串str的所有字母转换成小写字母
Upper(str):将字符串str的所有字母转换成大写字母

SELECT UPPER('hello world!');

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

Left(str,length):返回具有指定长度的字符串的左边部分
right(str,length):返回具有指定长度的字符串的右边部分

select left('hello world',7);

4.返回读音相似值(对英文效果)

Soundex(str):返回读音和str相似的值

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

5.日期格式:date_format

ps:在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');
# 查询某一天的日记
select * from blog where Date(sub_time) = '2015-03-01';
# 查看某个月的日记
select * from blog where Year(sub_time)=2016 AND Month(sub_time)=07;

# 更多日期处理相关函数 
    adddate  增加一个日期
    addtime  增加一个时间
    datediff 计算两个日期差值

流程控制

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

MySQL 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中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

primary key  主键   非空且唯一
unique key  唯一键  唯一
index key  索引建   无约束条件

上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询。

索引的基本用法

比如说一个表里有id，name，pwd，post_comment，addr，age等字段。基于id查找数据很快，但是基于addr查找数据就很慢。

解决的措施：可以是给addr添加索引

ps：索引虽然好用 但是不能无限制的创建!!!

索引的影响：

• 在表中有大量数据的前提下，创建索引速度会很慢

• 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

问题：

比如一个表有一个字段，想经常用这个字段查数据但是很慢，有没有什么方式解决？

可以将这个字段添加成索引但是索引不能添加的过多，添加的过多的话会影响表数据增，删，改的操作。

索引相关概念

聚集索引(primary key)

指的就是表的主键，innodb引擎规定一张表中必须要有主键

特点：叶子结点放的一条条完整的记录

辅助索引(unique key,index key)

查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据

覆盖索引

只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select name from user where name='jason';

非覆盖索引

虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

select age from user where name='jason';

前缀索引

根据字段的前N个字符建立索引

alter table test add index idx_name(name(10));

避免对大列建索引，如果有，就使用前缀索引

联合索引

多个字段建立一个索引
特点：前缀生效特性

原则：把最常用来做为条件查询的列放在最前面
#创建people表
     create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));
#创建联合索引
     alter table people add index idx_gam(gender,age,money);

索引底层原理

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

索引的底层数据结构是b+树

二叉树:两个分支
B树(B-树)、B+树、B*树
    B树:
      除了叶子节点可以有多个分支 其他节点最多只能两个分支
      所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
    B+树:
      只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
    B*树
      在树节点添加了通往其他节点的通道 减少查询次数

慢查询优化

explain命令使用方法

mysql> explain select name,countrycode from city where id=1;

explain命令应用

查询数据的方式

1.全表扫描

1）在explain语句结果中type为ALL

2）什么时候出现全表扫描?

1. 业务确实要获取所有数据

2.不走索引导致的全表扫描
  1. 没索引
  2. 索引创建有问题
  3. 语句有问题

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

2.索引扫描

常见的索引扫描类型:
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子句里带有<,>查询。

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

join B
on A.sid=B.sid

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer

如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population;

当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现

mysql> explain select * from city where population>30000000 order by population;
mysql> select * from city where population=2870300 order by population;

key_len: 越小越好

前缀索引去控制

rows: 越小越好