【视图】

 1 什么是视图？
 2     视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接用
 3     其实视图也是表
 4     
 5 为什么要用视图？
 6     如果要频繁的操作一张虚拟表，就可以制作成视图，下次可以直接操作
 7     
 8 如何操作
 9     # 固定语法
10     create view 表名 as 虚拟表的查询sql语句
11     具体操作
12         CREATE VIEW teacher2course AS
13         SELECT * FROM teacher INNER JOIN course ON teacher.tid=course.teacher_id;
14 
15 视图使用率高不高呢？
16     不高
17     因为当创建很多视图以后，会造成表不好维护
18 
19 
20 注意：创建视图在硬盘上只会有表结构，没有表数据（数据还是来源于之前的表）
21     视图一般只用来查询，里面的数据不要继续修改，可能会影响真正的表
22 
23 视图的总结：
24     使用视图可以简化查询操作、保护数据安全性、实现数据完整性、提高系统的可维护性和性能优化。
25     视图提供了一种灵活且安全的数据访问方式，使用户能够根据自身需要方便地获取和操作数据。

。

。

【触发器】

触发器：
    在满足对表数据进行增删改的情况下，自动触发的功能
    使用触发器可以帮助我们实现监控、日志记录、数据校验等操作
    触发器可以在六种情况下自动触发，增前，增后，删前删后，改前改后

基本的语法结构
    create trigger 触发器名称 before/after insert/update/delete on 表名 for each row
    begin
        触发器操作sql语句
    end

具体使用：针对触发器的名字，我们要做到见名知意
针对增：
    create tigger tri_before_insert_t1 before insert on 表名t1 for each row
    begin
        sql语句
    end
    或是：
    create tigger tri_after_insert_t1  after insert on 表名t1 for each row
    begin
        sql语句
    end

针对删除和修改 书写格式一致
PS：修改MYSQL默认的语句结束符,只作用于当前窗口
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')
    );
    #
    create table errlog(
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
    );

# 当cm表中的success字段为no时，自动触发触发器的执行，去errlog表中插入一条记录
delimiter $$   修改结束语句
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if new.success='no' then    # new 指的是cmd里的一条条数据对象
        insert into errlog(err_cmd,err_time)
        values(new.cmd,new.sub_time);
        end if;
end $$
delimiter ;
但是语句一看到;就结束了，那怎么触发呢
其实触发器早就已经工作了

朝cmd表中插入数据
insert into cmd(user,priv,cmd,sub_time,success)
values
('jason','0755','ls -l/etc',now(),'yes'),
('jason','0755','cat /etc/passwd',now(),'no'),
('jason','0755','useradd xxx',now(),'no'),
('jason','0755','ps aux',now(),'yes');

但是errlog表中没有操作却有数据，说明触发器触发成功
那怎么删除触发器呢？
drop trigger tri_after_insert_cmd;

。

。

【事务】

 1 开启一个事务可以包含多条sql语句，这些sql语句要么同时成功，要么一个都别想成功，称之为：事务的原子性。
 2 作用：保证了对数据操作的安全性
 3 
 4 事务的四大特性：
 5     ACID
 6     A：原子性:
 7         一个事务中的所有操作，要么同时成功，要么同时失败
 8     C：一致性：
 9         事务必须是使数据库从一个一致性的状态变到另一个一致性的状态
10         一致性和原子性是密切相关的
11     I：隔离性：
12         一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的
13         各个事务之间不能互相干扰
14     D：持久性：
15         一个事务一旦提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
16 
17 如何使用事务：
18     事务相关的关键字
19         开启事务：start transaction;
20         回滚（回到事务执行之前的状态）：rollback;
21         确认（确认之后就无法回滚了）：commit;
22 
23 
24 模拟转账功能
25 create table user1(
26     id int primary key auto_increment,
27     name char(16),
28     balance int
29     );
30 
31 insert into user1(name,balance)
32 values
33 ('jason',1000),
34 ('egon',1000),
35 ('tank',1000);
36 
37 1.开启事务
38     start transaction;
39 2.多条sql语句
40     update user1 set balance=900 where name='jason';
41     update user1 set balance=1010 where name='egon';
42     update user1 set balance=1090 where name='tank';
43 3.回滚
44      rollback;
45 
46 4.二次确认：
47     commit;(确认之后无法回滚)

 。

。

【存储过程】

 1 存储过程类似于python中的自定义函数
 2 它的内部包含了一系列可以执行的sql语句，存储过程放于MYSQL服务端，我们可以通过调用存储过程触发内部sql语句的执行
 3 
 4 基本使用：
 5     create procedure 存储过程名(形参1，形参2)
 6     begin
 7         sql代码
 8     end
 9 
10 调用：
11     call 存储过程名(实参列表)
12 
13 =================================================
14 三种开发模型：
15     第一种：
16         应用程序：程序员写代码开发
17         MYSQL：提前编写好存储过程，供应程序员调用
18         好处：开发效率提升了，执行效率也提升了
19         坏处：存储过程的扩展性差
20 
21     第二种:
22         应用程序：程序员写代码开发之外，涉及到数据库操作也自己写
23         优点：扩展性高
24         缺点：开发效率低，还要考虑sql优化问题
25 
26     第三种：
27         应用程序：只写程序代码，不写sql语句，基于别人写好的操作mysql的python框架直接调用操作即可：ORM框架
28         优点：开发效率比上面两种都高
29         缺点：扩展性一般，可能会出现效率低下的问题
30 
31 存储过程：
32     in 只进不出，m不能返回
33     out 该形参可以返回出去
34     set res=0;  将res变量修改，用来表示当前的存储过程代码确实执行了
35 
36     delimiter $$
37     create procedure p1(
38     in m int,
39     in n int,
40     out res int,
41     )
42     begin
43         select tname from teacher where tid>m and tid<n;
44         set res=666;
45      end $$
46      delimiter ;
47 针对形参res不能直接传参数，应该传一个变量名
48 定义变量
49 set @ret=10;
50 查看变量对应的值
51 select @ret;

import pymysql
#
conn = pymysql.connect(
    host='127.0.0.1',
    user='root',
    password='199721',
    charset='utf8',
    port=3306,
    database='day4')

cursor = conn.cursor(pymysql.cursors.DictCursor)
# #调用存储过程
# cursor.callproc('proc_test', [1, 2])
# print(cursor.fetchall())
cursor.execute('select * from user')
print(cursor.fetchall())
调用存储过程
call p1(1,100,@ret);

52 调用存储过程 53 call p1(1,100,@ret);

。

。

【函数】

 1 函数跟存储过程是有区别的，存储过程是自定义函数，函数就是类似于内置函数
 2 
 3 ('jason','0755','ls-l/etc',now(),'yes')
 4 
 5 案例
 6 create table blog(
 7     id int primary key auto_increment,
 8     name char(32),
 9     sub_time datetime
10     );
11 
12     insert into blog(name,sub_time)
13     values
14     ('第1篇','2024-01-25 15:30:00'),
15     ('第2篇','2024-01-23 15:00:00'),
16     ('第3篇','2023-11-25 14:30:00'),
17     ('第4篇','2023-11-23 10:00:00'),
18     ('第5篇','2023-11-24 15:30:00'),
19     ('第6篇','2022-02-25 15:30:00'),
20     ('第7篇','2023-02-25 17:30:00');
21      # 对日期进行格式化操作
22     select date_format(sub_time,'%Y-%m') ,count(id) from blog group by
23     date_format(sub_time,'%Y-%m');

 。

。

【流程控制】

 1 if 判断：
 2     delimiter //
 3     create procedure proc_if()
 4     begin
 5         declare i int default 0;
 6         if i=1 then
 7             select 1;
 8         elseif i=2 then
 9             select 2;
10         else
11             select 7;
12         end if;
13     end //
14     delimiter ;
15 ===========================================
16 
17 while循环
18     delimiter //
19     create procedure proc_while()
20     begin
21         declare num int ;
22         set num=0;
23         while num<10 do
24             select
25                 num;
26             set num=num+1;
27         end while;

。

。

【索引】

ps:数据都是存在与硬盘上的，查询数据不可避免的需要进行I0操作
索引:就是一种数据结构，类似于书的目录。意味着以后在查询数据的应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低I0操作
索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
    primary key
     unique key
    index key
注意foreign key不是用来加速查询用的，不在我们的而研究范围之内
上面的三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，
而最后种index key没有任何的约束条件，只是用来帮助你快速查询数据

本质：
    通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机事件（一页一页的翻）变成顺序事件（先找目录，再找数据）
    也就是说有了索引机制，我们可以总是用一种固定的方式查找数据

一张表中可以有多个索引（多个目录）
索引虽然能够加快查询速度，但是也有缺点：

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


===========b+树
只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据
仅仅是用来指路的
树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
    占得空间少 一个磁盘块能够存储的数据多那么久降低了树的高度 从而减少査询次数



==========================聚集索引
聚集索引：primary key
    InnoDB只有两个文件，直接将主键存放在idb表中
    MyISAM有三个文件，主键和数据分别存放在两个文件中

============================辅助索引
辅助索引：unique ,index
查询数据的时候不可能一直使用主键，也有可能会使用到name password等字段，
那么这个时候无法利用聚集索引，这个时候我们可以根据情况给其他字段设置辅助索引（也就是b+树）

叶子节点存放的是数据对应的主键值
先按照辅助索引拿到数据的主键值
之后还是需要去主键的聚集索引里面查询数据


========================覆盖索引
在辅助索引的也在节点就已经拿到了需要的数据，

# 给name设置辅助索引
select name from user where name='张三';
# 非覆盖索引
select age from user where name='张三';