Day45——视图,触发器,事务,存储过程

视图

• 概念

  视图就是SQL语句执行完后产生的一张虚拟表，我们可以基于该表做一些操作
  如果该表之后会频繁的进行一些操作，我们可以将其保存起来，然后起别名，保存起来之后这张表就被成为视图(本质上还是虚拟表)
  1
  2

• 作用

  如果我们需要频繁的使用一张虚拟表，可以考虑做成一张虚拟表，降低操作的难度

• 结构

  create view 视图名 as SQL语句;
  1

• 强调

  在硬盘中，视图只有表结构，没有数据结构
  视图大多数是用来查询数据的，所以我们尽量不要去修改视图数据

注意：视图看起来好用，但是容易造成表的混乱，毕竟视图不是真正的数据源

触发器(trigger)

• 本质

  是提供给程序员和数据分析员来保证数据完整性的一种方法，他是与表事件相关的特殊的存储过程，它的执行不是由程序调用的，也不是自己手动调用的，而是由事件自动调用的。
  主要是针对数据进行增，删，改操作之后自动触发的功能
  1
  2

• 强调

  因为在触发器中会使用到分号，但是分号在SQL语句中又是结束符的含义，所以为了两者不冲突，我们需要临时使用delimiter关键字先将SQL语句中的结束符改为其他的符号。

• 结构

  delimiter 新结束符;
  要进行操作的SQL语句
  delimiter ;  # 将结束符修改回去，不影响后面的操作
  1
  2
  3

• 作用

  1. 可以在写入数据表之前，进行强制的校验或转换数据
  2. 触发器发生错误的时候，异动产生的结果会被撤销
  3. 部分数据库管理系统可以针对数据自定义语言来使用触发器
  4. 可以依照特定的情况，替换掉异动的指令

• 触发器的创建

  create trigger 触发器名字 before/after insert/update/delete
  on 表名 for each row
      begin
    	  SQL语句
      end
  1
  2
  3
  4
  5

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

1. tri_after_insert_t1
2. tri_before_update_t2
3. tri_before_delete_t3

• 补充

  删除库下指定的触发器

  drop trigger 触发器名称;
  1

  查看库下的触发器信息

  show triggers\G;
  1

注意：

• 当在临时表(视图)或系统表上创建触发器的时候是会被拒绝的
• 我们也不能过分的使用触发器，因为会影响数据库的结构，加强维护数据系统管理的复杂性
• 触发器与存储过程的唯一区别就是触发器不能使用execute语句调用，而是在用户执行transact_sql语句的时候自动执行。

事务

• 概念

  一般指要做的或所做的事情。
  在关系型数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个应用程序
  1
  2

• 作用

  高某某欠了李某某一笔钱 现在想要还钱
  高某某拿着交行的银行卡去招商银行的ATM机给李某某的建行卡转钱
    1.朝交行的服务器发送请求 修改高某某账户余额(减钱)
    2.朝建行的服务器发送请求 修改李某某账户余额(加钱)
  保证不会因为其他因数造成只有一方成功
  1
  2
  3
  4
  5

• 四大特征

  简称ACID

  1. 原子性：一个事务要么都实现，要么都不实现
  2. 一致性：事务必须是使得数据库从一个一致性状态转换成另一个一致性状态，与原子性密切相关
  3. 隔离性：当一个事务在执行的时候，是不能被其他的事物干扰的;并发执行的各个事务是互不干扰的
  4. 持久性：也称永久性;事务一旦被提交，数据就不会再被修改

• 特性

  事务是恢复和并发控制的基本单位
  1

具体使用：

1.创建表及录入数据
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);
2.事务操作
开启一个事务的操作
	start transaction;
编写SQL语句(同属于一个事务)
	update user set balance=900 where name='jason';
		update user set balance=1010 where name='kevin'; 
		update user set balance=1090 where name='tank';
事务回滚(返回执行事务操作之前的数据库状态)
	rollback;  # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
	commit;  # 执行完确认提交之后 无法回滚 事务自动结束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

关键SQL语句

1. start transaction：开启事务管理操作，这是要在对数据修改之前进行操作

2. rollback：回滚到上一个状态

3. commit：提交事务，提交之后就不能对该修改任务进行回滚操作了

4. savepoint：标记，与回滚密切相关，有了标识就可以做到回滚到任意位置(保留点)

   savepoint 点名;  # 创建保留点
   rollback to 点名;  # 回滚到保留点
   # 保留点在执行了rollback或者commit之后释放
   1
   2
   3

隔离的四大级别：

1. 未提交读：事务中的修改即使未提交，但是也是对其它事务可见的，其它事务也可以读取未提交的数据，这一现象也成为脏读
2. 提交读：事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这一级别也称为不可重复读
3. 可重复读：能解决脏读，但是无法解决幻读;所谓幻读，就是当某个事务在读取某个范围内的记录时另外的事务又在该范围内插入新的记录，当之前的事务在来读取记录的时候会出现幻行;InnoDB可以通过多版本的并发控制(MVCC)以及间隙锁来解决该幻行问题
4. 可串行读：强制事务可以串行执行

存储过程

• 概念

  是为了完成特定功能的SQL语句，存储在数据库中，一次编译之后就永久有效，用户通过指定的存储过程名并给出参数来执行它
  1

• 格式

  delimiter 临时结束符;
  create procedure 名字(参数，参数)
  begin
  	sql语句;
  end 临时结束符
  delimiter ;
  1
  2
  3
  4
  5
  6

• 创建存储过程

  create procedure 存储过程名
  @[参数名1][参数类型1],[参数2][参数类型2]
  as
  begin
  	sql语句
  end
  1
  2
  3
  4
  5
  6

• 调用存储过程

  在cmd命令下，基本语法为：exec 存储过程名[参数名];
  在SQL环境下，基本语法为：call 存储过程名[参数名];

• 删除存储过程

  基本语法：drop procedure 存储过程名
  注意事项：不能在一个存储过程中去删除另外一个存储过程，只能调用另外一个存储过程

• 查看存储过程具体信息

  基本语法：show create procedure 存储过程名;
  查看所有存储过程：show procedure status;

• 存储过程的三种传回值方式

  1).以Return传回整数
  2).以output格式传回参数
  3).Recordset

  传回值的区别：
  output和return都可在批次程序中用变量接收，而recordset则传回到执行批次的客户端

强调：

存储过程只在创造的时候进行编译，以后每次执行存储过程都不用再重新进行编译
存储过程可以重复使用，可减少数据库开发人员的工作量

内置函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！
可以通过help 函数名 查看帮助信息!

举例说明：

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

更多的筛选条件：

where Date(sub_time) = '筛选条件'
where Year(sub_time) = '筛选条件' and Month(sub_time) = '筛选条件'
1
2

更多的日期处理相关函数

addate:增加一个日期
addtime:增加一个时间
datediff:计算两个日期的差值
1
2
3

流程控制

• MySQL if判断

  if 条件 then
        子代码
  elseif 条件 then
        子代码
  else
        子代码
  end if;
  1
  2
  3
  4
  5
  6
  7

• MySQL while循环

  DECLARE num INT ;
  SET num = 0 ;
  WHILE num < 10 DO
  SELECT num ;
  SET num = num + 1 ;
  END WHILE ;
  1
  2
  3
  4
  5
  6

索引

• 思考

  为什么要使用索引？

  索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
  1

  如何正确看待索引?

  索引不是越多越好，并且建议大家最好在提前创建好索引，而不是等火烧眉毛
  1

• 本质

  索引是为了加速对表中数据的检索而创建的一种分散的存储结构，针对表而建立
  索引就像是书的目录，它能更快的让我们找到我们想要的内容
  1
  2

• 作用

  1. 能够快速的获取到数据
  2. 保证数据记录的唯一性
  3. 实现表与表之间的参照完整性
  4. 在使用ORDER by，group by子句进行数据检索时，使用索引可以减少排序和分组的时间

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

primary key  # 主键
unique key  # 唯一键
index key  # 索引键
1
2
3

三种键在查询的时候的区别

primary key,unique key除了可以加快数据记录的查询速度，还有额外的限制
index key 只能加快数据的查询，本身是没有任何的限制
1
2

注意：

我们在加索引的时候，不能无限制的添加索引
当我们基于索引查找数据的时候是很快，但是基于add的查找数据就会很慢
我们可以给add也创建索引

• 索引分类

  • B+树索引(等值查询与范围查询都快)

    分类：b树，红黑树，二叉树，b*树，b+树
    以上结构都是为了更好的基于树查找到相应的数据
    1
    2

  • hash索引(等值查询快，范围查询慢)

    将数据打散再去查询
    1

  • fulltext：全文索引(只用在MyISAM存储引擎)

    通过关键字的匹配进行查询，类似于like的模糊查询
    1

不同的存储引擎支持的索引类型也不一样

• InnoDB存储引擎

  支持事务，支持行级别锁定，支持 B-tree（默认）、Full-text 等索引，不支持 Hash 索引；
  1

• MyISAM存储引擎

  不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
  1

• Memory存储引擎

  不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
  1

innodb存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的

b树：

除了叶子节点可以有多个分支，其他节点最多只能有两个分支
所有的节点都可以直接存放完整数据(每一个数据块都是有固定大小的)
1
2

b+树：

只有叶子节点上才存放真正的数据，其他节点都只存放主键值(辅助索引值)
1

b*树：

在树节点添加了通往其他节点的通道，减少了查询的次数
1

强调：只有叶子结点存放真实的数据，而根和树枝结点存放的是虚拟数据，查询的次数由树的层级决定，层次越低查询次数越少

覆盖查询：只在辅助索引的叶子节点中就已经找到了我们要查询的数据

非覆盖查询：虽然查询的时候找到了索引字段，但是查找的是其他字段，所以还要通过主键去查找

慢查询优化

索引扫描基本语法：explain select 字段名 from 表名 where 筛选条件;

常见的索引扫描类型：

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在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
1
2
3
4
5
6
7

从上到下，性能从最差到最好，我们认为至少要达到range级别

DBMS自动完成维护和自动选择是否使用索引以及使用哪些索引