视图

视图其实就是一张被查询出来的表，这张表是虚拟的，无法进行删除
- 视图通常用作查询，尽量不要修改其中的数据
- 视图只有表结构文件，没有表数据文件
```
关键字：
    view
模板：
    create view 视图名 as SQL语句
       # 尽量少用
```

触发器

关键字
```
trigger
```

触发器是针对表数据的增改删前后自动触发的功能

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

使用触发器前，需要先对结束符进行修改，因为触发器会用到分号结束符

delimiter 要修改后的结束符
  # 结束符修改后仅限临时使用，结束后要修改回来

触发器实例

# 1.创建一个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代表执行失败
 );
# 2.创建一个errlog表，记录每次执行错误的数据
 CREATE TABLE errlog (
     id INT PRIMARY KEY auto_increment,
     err_cmd CHAR (64),
     err_time datetime
 );
# 3.使用触发器自动触发针对表数据的增改删
  delimiter $$   # 修改结束符
  
  create trigger tri_after_insert_cmd after insert on cmd for each row 
  begin
      if NEW.success = 'no' then     # NEW是MYSQL里面封装的数据对象
          insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
  end $$  
  
  delemiter ;    # 结束之后改回来结束符

# 4，往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');
# 5.查询错误日志表errlog的记录
  select * from errlog;
          
           # 删除触发器
        drop trigger tri_after_insert_cmd;   # 第一次给触发器起名要做到见名知意

事务

事务的四大特征(ACID)

A: 原子性 : 必须保证事务语句操作，同时成功或者同时失败,发生错误回退，返回至上一状态
C: 一致性 : 事务前后数据的完整要保持一致，与原子性密切相关              
I: 隔离性 : 多个用户并发访问数据库，每一个开启的用户事务互不干扰，隔离开。
D: 持久性 : 事务提交后改变是永久的，数据库发生问题不会影响

事务的创建

关键字
```
start transaction
```

创建事务操作

       # 模拟银行转账
 # 1.创建一个用户表
    create table user(id int primary key auto_increment,
        name char(32),
        balance int
    );
 # 2.插入用户数据
    insert into user(name,balance) values
        ('jason',1000),
        ('kevin',1000),
        ('tank',1000);
 # 3.开启事务操作
    start transaction;
 # 4.修改数据操作
    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元
 # 5.使用rollback返回到上一次状态(模拟转账转错了，追回金额)
    rollback;
      # 关于为什么可以回退金额：
          因为，开启事务后，我们操作的数据并没有保存到硬盘，
          所以为了数据能够刷到硬盘需要使用commit操作
 # 6.将修改后的数据刷到硬盘中
    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 sp01;    # sp01是自己创建占位符
        回滚占位符：   rollback to sp01;

事务的隔离级别

数据库中定义了四种事务的隔离级别，隔离级别规定了能对事务做修改权限。

# 1. read uncommitted (未提交读)
   可以读取到事务里修改了但未提交的数据，也被称为"脏读",其他事务对脏读是可见的
# 2. read committed (提交读)
   能读取到提交后的数据，但是读取不了提交之前的所作修改的数据，也叫做"不可重复读"
       提交读是大部分数据库默认的隔离级别
# 3. repeatable read (可重复读)
   可以解决脏读，无法解决幻读，幻读指的是事务读取某个范围的记录时，插入的新的记录，
   事务再次读取会产生幻行，InnoDB要通过多版本并发控制(MVCC)及间隙锁策略解决问题
# 4. serializable (可串行读)
   强制事务串行执行，很少使用该级别

InnoDB支持所有隔离级别

语法:  set transaction isolation level 级别

事务的日志

记录事务修改存储记录，将其持久到硬盘上的事务日志中

MVCC多版本并发控制

MVCC只在提交读(read committed)和可重复读(repeatable read)两种隔离级别下工作
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现

由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。

存储过程

类似于python中的自定义函数

存储过程中的基本使用：
模板:
   delimiter 临时结束符
   create procedure 名字(参数,参数)     # 加的参数要给出名字和数据类型
   begin
       sql语句;
   end 临时结束符
   delimiter ;

实操: 
    # 1. 针对res需要提前定义出来
    set @res=10;         # 定义
    select @res;         # 查看
    # 2. 存储res
    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 ; 
   
    # 3. 查看res的值
    call p1(1,5,@res);   # 调用
    select @res;         # 查看

调用需要使用call

内置函数

可以通过使用(help 函数名) 查看帮助信息

1. 移除指定字符
```
Trim,LTrim,RTrim
```
1. 大小写转换
```
Lower,Upper
```
3.获取左右起始指定个数字符
```
Left,Right
```

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

Soundex   
  例：  jason，搜索所有与其读音相似的
  where Soundex(name)=Soundex('jason')

5.日期格式

date_format 

  # 相关日期函数
    adddate  增加一个日期
    addtime  增加一个时间
    datediff 计算两个日期差值
例：
    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	计算两个日期差值

流程控制(与python区别)

if条件

 delimiter //
 CREATE PROCEDURE proc_if ()
 BEGIN                              
     declare i int default 0;
     if i = 1 THEN          # THEN = :
         SELECT 1;
     ELSEIF i = 2 THEN      # ELSEIF = elif
         SELECT 2;
     ELSE
         SELECT 7;
     END IF;                # 声明结束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中实现索引的方式叫做键，作为一种数据结构，大致有三种
  ```
  primary key   主键
  unique key    唯一键
  index key     索引键，无任何意义
  ```
  * 区别
    ```
    primary key,unique key 加快了数据查询，但是有额外限制
    index key              没有额外限制，加快数据查询
    ```
  * 索引的存在可以加快数据的查询 但是会减慢数据的增删

索引底层

 * 索引的底层其实就是树
   * 树是一种数据结构，主要用于优化数据查询的操作

树

二叉树
B树
- 除了叶子节点外的其他节点最多只能有两个分支(所有节点都能直接存放完整大小的数据块)
B+树
- 只有叶子节点存放完整的数据，其他节点只存主键值
B* 树
- 在树节点添加了其他节点的通道，减少查询次数

慢查询优化

explain命令

语法 : explain select 字段名 from 表名 where 筛选条件;

explain在不同版本mysql数据库中存在微小的区别：

 在5.6版本中使用explain不会显示partitions,filtered。

第一次变种:

explain extended 可以在mysql5.6显示filtered字段。
    rows*filtered/100 可以估算将要与explain中前一个表进行连接的行数。

第二次变种:

explain partitions 可以在mysql5.6显示partitions字段。
    可以显示查询将访问的分区。

实际的这两个字段也并不重要

show warnings;

在explain操作结束后，对mysql内部优化后的结果按照展示的结果执行。

explain命令应用

查询数据的方式

全表查询

在explain语句结果中type的值为ALL

什么时候出现全表查询？

1. 业务需要获取所有的数据
2. 不执行索引导致要全表扫描
   * 2.1 没有索引
   * 2.2 索引创建有问题
   * 2.3 语句有问题

mysql内要避免全表扫描，因为在进行全表扫描时会降低mysq的性能

索引扫描

常见的索引扫描类型

1. index :  在index下还有一个all，区别是index类型的只遍历索引树
2. range :  索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行
3. ref   :  使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
4. eq_ref:  类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
5. const :  当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问
6. system:  当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问
7. null  :  MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
   从上到下，性能越强，我们一般写的sql语句需要到达range级别以上

MYSQL数据库 触发器，流程控制

视图

触发器

事务