MySQL-视图_触发器_事务_存储过程_函数_流程控制_索引原理

MySQL

1. 视图

1. 视图介绍

1. 视图是一个虚拟表(非真实存在),其本质是: 根据SQL语句获取动态的数据集,并为其命名
2. 用户使用时只需使用 '名称' 即可获取结果集,可以将该结果集当做表来使用
3. 修改视图记录,修改的是原始表

2. 创建视图

# emp 数据表 select * from emp;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | EGON       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
|  7 | lili       | female |   48 |   NULL |
+----+------------+--------+------+--------+

# dep 数据表 select * from dep;
+-----+-----------------+
|  id |     name        |
+-----+-----------------+
| 200 |     技术         |
| 201 |     人力资源      |
| 202 |     销售         |
| 203 |     运营         |
+-----+-----------------+

# emp2dep 数据表 select * from emp2dep;
+----+-----------+--------+------+--------+--------------+
| id | name      | sex    | age  | dep_id | dep_name     |
+----+-----------+--------+------+--------+--------------+
|  1 | EGON      | male   |   18 |    200 | 技术         |
|  2 | alex      | female |   48 |    201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 | 技术         |
+----+-----------+--------+------+--------+--------------+

# 内连接
select * from emp inner join dep on emp.dep_id = dep.id;

# 创建视图语法: create view 视图名称 as sql语句
create view emp2dep as 
	select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id;
   
# 修改视图记录
update emp2dep set name = 'EGON' where id = 1;

# 修改视图
alter view emp2dep as sql语句;

# 删除视图
drop view emp2dep;

2. 触发器

1. 创建触发器

# 使用触发器可以定制用户对表进行 增、删、改 操作时前后的行为，注意：没有查询
# before: 操作表之前的行为  after: 操作表之后的行为
# 注意：NEW 表示即将插入的数据行，OLD 表示即将删除的数据行
# 触发器无法被用户直接调用，而是由于对表的【增/删/改】操作被动引发的

# 增 => insert 
create trigger tri_before/after_insert_t1 before/after insert on t1 for each row
begin
	sql语句;
end    

# 删 => delete
create trigger tri_before/after_delete_t1 before/after delete on t1 for each row
begin
	sql语句;
end 

# 改 => update
create trigger tri_before/after_update_t1 before/after update on t1 for each row
begin
	sql语句;
end 

# delimiter + 符号 => 定义结尾符号 => delimiter $$ 把sql语句结尾的分号替换为 $$ (符号自定)
delimiter //
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
    insert into t2 values(NEW.name);
end //

delimiter ;

# 练习
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 $$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = 'no' then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

insert into cmd(user,priv,cmd,sub_time,success) values
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

# 删除触发器    
drop trigger tri_after_insert_cmd ;

3. 事务

1、事务概念:
	数据库事务是指作为单个逻辑工作单元执行的一系列操作（SQL语句）。这些操作要么全部执行，要么全部不执行
2、为什么需要事务
	经典的银行转账行为，A账户转给B账户10元，数据库操作需要两步
    第一步A账户减10元，第二步B账户加10元，如果没有事务并且在两步中间发生异常，就会导致A的账户少了10元，但B的账户没有变化，如果不能保证这两步操作统一，银行的转账业务也没法进行展开了
	事务管理是每个数据库（oracle、mysql、db等）都必须实现的
3、事务特性（4种）:
	原子性 （atomicity）  : 强调事务的不可分割
	一致性 （consistency）: 事务的执行的前后数据的完整性保持一致
	隔离性 （isolation）  : 一个事务执行的过程中,不应该受到其他事务的干扰
	持久性 （durability） : 事务一旦结束,数据就持久到数据库
4、事务运行模式（3种）
	自动提交事务：默认事务管理模式。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句
	显式事务：以BEGIN TRANSACTION显式开始，以 COMMIT 或 ROLLBACK 显式结束
	隐性事务：当连接以此模式进行操作时，sql将在提交或回滚当前事务后自动启动新事务。无须描述事务的开始，只需提交或回滚每个事务。它生成连续的事务链
5、总结
	事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性
    
# 回滚     rollback;
# 提交数据  commit;

4. 存储过程

1. 存储过程介绍

# 存储过程
 	包含一系列可执行的 sql 语句，存储过程存放于 MySQL 中，通过调用它的名字可以执行其内部的 sql 语句

# 存储过程的优点
	1. 用于替代程序写的SQL语句，实现程序与sql解耦
	2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

# 存储过程的缺点
	程序员扩展功能不方便
    
# 程序与数据库结合使用的三种方式
#方式一：
    MySQL：存储过程
    程序：调用存储过程

#方式二：
    MySQL：
    程序：纯SQL语句

#方式三：
    MySQL:
    程序：类和对象，即ORM（本质还是纯SQL语句）
    ORM: object relational Mapping 对象关系映射

2. 创建存储过程

# 1. 创建无参存储过程
delimiter $$
create procedure p1()
begin
    select * from emp;
end $$

delimiter ;

# 在mysql中调用
call p1();

# 在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())

# 2. 创建有参存储过程
'''
in        仅用于传入参数使用
out       仅用于返回值使用
inout     既可以传入又可以当作返回值
'''
# in 和 out
delimiter $$
create procedure p2(
    in n int,
    out res int
)
begin
    select * from emp where id > n;
    set res=1;
end $$

delimiter ;

# 在mysql中调用
set @res=0; # 0代表假（执行失败），1代表真（执行成功）
call p2(3,@res);
select @res;

# 在python中基于pymysql调用
cursor.callproc('p2',(3,0)) # 0相当于set @res=0
print(cursor.fetchall())    # 查询select的查询结果

cursor.execute('select @_p2_0,@_p2_1;') # @p2_0代表第一个参数，@p2_1代表第二个参数，即返回值
print(cursor.fetchall())

# inout
delimiter //
create procedure p3(
    inout n1 int
)
BEGIN
    select * from blog where id > n1;
    set n1 = 1;
END //
delimiter ;

# 在mysql中调用
set @x=3;
call p3(@x);
select @x;

# 在python中基于pymysql调用
cursor.callproc('p3',(3,))
print(cursor.fetchall()) # 查询select的查询结果

cursor.execute('select @_p3_0;') 
print(cursor.fetchall())

3. 执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

# 执行存储过程
# !/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()

conn.commit()
cursor.close()
conn.close()

print(result)

# 删除存储过程
drop procedure proc_name;

4. date_format 函数

# date_format 格式化时间

# 案例
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") as t,count(id) from blog group by t;

# 查询结果
+---------+-----------+
| t       | count(id) |
+---------+-----------+
| 2015-03 |         2 |
| 2016-07 |         4 |
| 2017-03 |         3 |
+---------+-----------+

5. 索引原理

1. 索引介绍

# 1. 使用索引的目的
	为了优化查询速度,但是一张表一旦创建了索引,会降低写入速度
    
# 2. 索引概念
	索引是 mysql 数据库的一种数据结构，在 mysql 里称之为 key(键)
    索引是存储引擎用于快速找到记录的一种数据结构
    在mysql中使用最广泛的数据引擎是 InnoDB 引擎，它里面用的是 B+ 树索引

2. 索引原理 B+树

# 1. 索引目的及本质	
    索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
    
    通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据
    
# 2. 磁盘IO与预读
	磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计
    
    考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助
    
# 3. 二叉查找树
# 概念
	每个节点左边节点的值都小于该节点，右边节点的值都大于该节点，没有值相等的节点，最顶端的节点称为根节