数据库进阶

1、视图

每条sql语句查出来的内容都是一张虚拟表，视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用。

所以，视图也是一张表。可以通过show tables查看。

如果要频繁的操作一张虚拟表，就可以制作成视图，后续直接操作。

语法：

create view 表名 as 虚拟表的sql语句

使用：

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

需要注意的是：

创建视图在硬盘上只会有表结构，没有表数据，数据还是在之前的表里
视图的数据只用来查询，不用来修改
创建视图很多会导致数据库文件难以维护，所以尽量少使用

2、触发器

在满足对表数据进行增删改的情况下，自动触发的功能称之为触发器。

使用触发器可以实现监控、日志、或其他自动处理的事情。

语法：

create tigger 触发器的名字 before/after(在事件前/事件后触发) insert/update/delete(监听触发的方式) on 表名
for each row
begin
    sql语句
end

具体使用：

# 增前触发器
create tigger before_insert_t1 before insert on t1
for each row
begin
    if NEW.success = 'no' then 
      insert into errlog(err) values(NEW.info);
    end if;
end

# 增后触发
create tigger before_insert_t1 after insert on t1
for each row
begin
    sql语句
end

sql语句的地方需要结束符，触发器也需要结束符，需要修改结束符才可以。

修改结束符的语法delimiter $$ 把结束符修改为$$，只针对当前窗口生效，退出或关闭之后重新打开就失效了。

delimiter $$

create tigger before_insert_t1 before insert on t1
for each row
begin
    if NEW.success = 'no' then 
      insert into errlog(err) values(NEW.info);
    end if;
end$$

delimiter ;

删除触发器

drop trigger before_insert_t1;

3、事务

开启一个事务可以包含多条sql语句，这些sql语句都必须成功才都会成功执行，任意一条sql语句执行失败其他任何sql语句都会失败。这个特点称之为事务的原子性

这个特性保证了对数据操作的安全性。

事务的四大特性ACID:

A：原子性一个事务是一个不可分割的单位，所有成功才成功，一个失败都失败。
C：一致性事务必须是使数据库从一个一致性的状态变成另一个一致性的状态。
I：隔离性一个事务的执行不能被其他事务干扰，一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互不干扰的。
D：持久性永久性指一个事务一旦执行成功，那么它对数据的修改是永久的，直接修改硬盘的数据。

事务的关键字：

开启事务：start transaction;
回滚事务(回到事务之前的状态)：rollback;
确认事务执行成功(确认之后就无法回滚，确认后修改硬盘)：commit;

使用：

start transaction;
sql语句1
sql语句2
sql语句3

# 执行结果有问题的话就
rollback;

# 执行结果没问题
commit;

4、存储过程

类似于python中的自定义函数；它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行。

语法：

# 定义
create procedure 名字(args)
begin
    sql语句
end

# 调用  调用定义的sql语句
call 名字(args);

使用：

delimiter $$

# 需要提前指定类型
# in 只接受不返回的参数
# out 可以返回的参数,返回的参数是需要定义一个变量
create procedure p1(
  in m int,
  in n int,
  out res int
)
begin
    select tname from teacher where tid>m and tid<n;
    # 修改res，用来标识存储过程执行了
    set res = 2;
end $$
delimiter ;

# 调用
set @ret = 1;
call p1(1, 5, @ret);
select @ret;

4.1 pymysql中调用存储过程

cursor.callproc('p1', (1,5,1))
print(cursor.fetchall())

5、函数

函数和存储过程是有区别的，存储过程是自定义过程，函数是内置函数。

内置函数的参考网站：https://blog.csdn.net/hellokandy/article/details/82964077

6、流程控制

if判断
while循环

# if
if i = 1 then
    select 1;
else
    select 2;
end if;

# while

declare num int;
set num = 0;
while num < 10 do
  select
      num;
  set num = num + 1;

end while;

7、索引

数据都是存在于硬盘上的，查询数据不可避免的需要进行IO操作。

索引就是一种数据结构，类似于书的目录。

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

有三种索引：

primary key：主键
unique key：唯一键
index key：普通索引

前面两种key除了可以增加查询速度之外还有各自的约束条件，而index key没有任何的约束条件。

索引的本质就是通过不断的缩小范围来筛选出最终的结果，同时将随机事件变成顺序事件(先找目录，再找数据)

索引虽然能提升查询速度，但是也有缺点：

当表中有大量的数据存在的前提下，创建索引速度会很慢
在索引创建完毕之后，查询速度提升很多，但是写的性能也会大幅度降低

7.1 B+树

只有叶子节点存放的是真实的数据，其他节点仅仅是用来指路的。

树的层级越高，查询的步骤就越多，树有几层查询数据就需要几步。

7.2 聚集索引(primary key)

聚集索引就是主键

innodb：只有两个文件，直接将主键放在了idb（数据）表中
MyIsam：三个文件，单独将索引存在一个文件

7.3 辅助索引(unique，index)

unique，index都是辅助索引。

查询数据的时候不可能一直使用主键查询，使用其他字段查询的时候没有办法利用聚集索引，这个时候可以根据情况给其他字段设置辅助索引(也是B+树)

只不过辅助索引的叶子节点存放的是数据对应的主键值。根据这个主键值再去聚集索引里面找真实数据。

7.4 覆盖索引

在辅助索引的叶子节点就已经拿到了需要的数据。

select name from user where name = 'aaa';

7.5 非覆盖索引

select age from user where name = 'aaa';

猫说