视图、触发器、事物、存储过程、函数、索引

一、视图

1.什么是视图？

视图是一个虚拟表，并非真实存在的。

产生的视图我们在data文件夹中只能找到表结构文件，而找不到表的数据文件，这是因为视图中的数据来源于查询的原表。而为了数据的安全，避免修改视图而影响原表中的数据，视图只能进行查询字段操作，而拒绝修改字段操作

若需要修改视图，需要修改原表，再重新创建视图

2.为什么要用视图

使用视图我们可以把查询过程中的临时表取出来，用视图来表示，这样以后再想操作这些查询的内容时，无需再次查询，直接使用视图查询就可以

缺点：

　　效率低下，并且视图是存放数据库中的，使用sql过分依赖视图，即强耦合，使得数据库的扩展性变低

因此工作中不建议使用视图，还是建议使用表连接查询

3.怎么使用视图

⑴、创建视图

 

 

语法：

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

 

 

⑵、使用视图

二、触发器

使用触发器可以定制用户对表进行增、删、改操作前后的行为，没有查询操作

1.创建触发器

语法：

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

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

2.使用触发器

delimiter

　　sql语句默认是分号结束，使用delimiter可以临时自定义结束符号，例如：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 ;


#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
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');

触发器无法由用户直接调用，而需要由对表的增删改操作被动触发

查询错误日志，发现有两条记录，插入后触发

三、事物

1.什么是事物？

事物中如果有一个操作失败了，整体就算失败了。要么整体成功，要么同时失败

事物的四大特性：

⑴、原子性：一个事物是一个不可分割的单位，要么整体成功，要么整体失败

⑵、一致性：和事物的原子性密不可分

⑶、隔离性：事物与事物之间是相互隔离的，一个事物执行不会被其他事物干扰

⑷、持久性：事物一旦提交，对数据库中数据改变就是永久的，不能回滚

2.为什么要用事物？

事物可以保证数据操作的安全性和一致性

支持回滚操作，一旦数据操作的结果不符合预期结构，可以回滚到操作之前的状态，例如银行转账，假如转账给他人，自己的账户金额已经扣款，但是因为网络原因，对方账户未收到，这就表示转账失败，账户就需要回滚到转账之前的状态

3.怎样使用事物？

开启事物：

　　start transaction;

回滚：

　　rollback

确认：

　　commint

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

insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);

回滚：

#出现异常，回滚到初始状态
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;
commit;

 

 

 原子操作：

#原子操作
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;

 

 

 数据库的三大设计范式

第一范式：确保每列保持原子性，也就是数据库中的所有字段值都是不可再拆分的原子值

例如用户信息表中的地址，本来可以直接将用户地址作为一个字段值，但是假如后面需要用到省，市，区，就需要将地址这个字段值再拆分，需要根据实际需求来操作

第二范式：确保表中的每列都和主键相关

例如一张货物表，里面有（ID，货物类别，货物名称，注意事项），货物ID和类别组成联合主键，很明显，货物注意事项不完全依赖于ID，而仅仅依赖货物类别，所以需要把货物注意事项从该表中去掉

第三范式：非主键列间不存在函数依赖关系

还是上面货物表，货物主键为货物ID，但是注意事项和货物类别有依赖关系，不符合，需要去掉

三大设计范式是设计数据库的基本理论，可以建立冗余较小、结构合理的数据库

四、存储过程

1.什么是储存过程

存储过程就是包含了一些列可执行的sql代码，类似于Python中的自定义函数，存储过程存放于MySQL中，可以调用它的名字执行其内部的sql语句

2.为什么要用储存过程

优点：

　　用于替代程序写的SQL语句，实现数据库编写与程序编写分开，解耦合

　　基于网络传输，传别命的数据量小，节省资源

缺点：

　　程序扩展不方便

3.怎么使用存储过程

关键字：procedure

语法结构：

create procedure 存储过程的名字(
    形参1,
    形参2,
    ...
)
begin
    sql语句;
end;

注意：在创建有参的存储过程时，需要声明参数的类型：

　　in：仅用于传入参数

　　out：仅用于返回值用，类似于return

　　inout：既可以传入，也可以当做返回

无返回值调用方式：

　　在mysql中：call 存储过程名()

　　在Python中：cursor.callproc('存储过程名')

有返回值调用方式：

　　在mysql中：需要设置一个参数 set @res=0

　　　　　　　　call 存储过程名(参数1，@res)

　　在Python中：cursor.execute('select @_存储过程名_0,@_存储过程名_1')　　@_存储过程名_0,@_存储过程名_1分别表示的是第一个参数，第二个参数，即返回值

五、函数

MySQL中包含了许多内置函数，类似于Python中的内置函数

与存储过程比较：

　　函数中不能写sql语句，且只能在mysql中使用

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');

六、流程控制

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

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

七、索引

1.什么是索引

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

索引就是类似于书的目录，意味着查询数据可以先找到目录再找数据，而不是用翻页的方式查询数据

索引分类：

　　聚集索引：其实就是表的主键，一般是表的ID字段，因为该字段数据小，也就是树的高度会变小，提升查询速度

　　辅助索引：查询数据不可能都是用ID筛选，也会用其他字段，将非主键字段作为索引，也就是辅助索引

2.为什么要用索引

索引本质上就是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引优缺点：

　　优点：对表的查询性能会大幅度提升

　　缺点：创建索引速度很慢，同时修改数据的速度也会很慢，因为修改数据会删除索引再重新创建索引

3.怎么用索引

关键字：index

语法：create index 索引名 on 表名(字段名)

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快