10.MySQL学习【存储过程，内置函数，流程控制，索引】

本文链接：https://blog.csdn.net/m0_52559893/article/details/121246203

1.存储过程

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

2.基本使用
  create procedure 存储过程的名字(形参1,形参2,...)
  begin
    sql代码
  end
  call 存储过程的名字();              # 调用



3.MySQL终端与python端执行封装操作：
    (1)MySQL服务器封装：
        delimiter $$               # 改执行符号
        create procedure p1(
            in m int,              # in 只进不出，没返回值
            in n int,
            out res int            # 该形参执行完结果，返回出去
        )
        begin
            select tname from teacher where tid>m and tid<n;
            set res=666;           # 将res变量修改用来标识当前的存储过程代码确实执行了，即返回值变说明已执行
        end $$
        delimiter ;

    (2)调用：
        a.在MySQL终端:
            注意：在MySQL终端，针对形参res 不能直接传数据 应该传一个变量名
            事先定义变量：
                set @ret = 10;
            调用：
                call p1(1, 5, @ret);
            查看out变量对应的值：
                select @ret;
         
        b.在pymysql下操作：
            import pymysql
            conn = pymysql.connect(host='127.0.0.1',
                                   port=3306,
                                   user='root',
                                   passwd='123456',
                                   db='day48',
                                   charset='utf8',
                                   autocommit=True)
            cursor = conn.cursor(pymysql.cursors.DictCursor)

            cursor.callproc('p1', (1, 5, 10))           # 这时不用写变量来代替
                                                        # 因为在pymysql下默认以以下方式传值
                                                        '''@_p1_0=1
                                                           @_p1_1=5
                                                           @_p1_2=10
                                                        '''
            cursor.execute('select @_p1_2;')            # 查看out变量对应的值
            print(cursor.fetchall())

2.内置函数

函数
1.存储过程是自定义函数，函数就类似于是内置函数如：NOW()
2.函数很多，参考菜鸟教程https://www.runoob.com/mysql/mysql-functions.html

3.内置函数处理数据案例：
    (1).造表，存数据
        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');
    (2)用内置函数处理数据
        select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
        # 即按月分组分篇数统计

3.流程控制

1.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 ;


2.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 ;

4.索引

1.索引(俗称目录）基本概念
    (1)引入:数据都是存在与硬盘上的，查询数据不可避免的需要进行IO操作
    (2)定义：索引:就是一种数据结构，类似于书的目录。
            意味着以后在查询数据的应该先找目录再找数据，而不是从头到尾依序查找，从而提升查询速度降低IO操作
            索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
    (3)索引分类：
        a primary key
        b unique key
        c index key
        注意:foreign key不是用来加速查询用的
            上面的三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，
            而最后一种index key没有任何的约束条件，只是用来帮助你快速查询数据
    (4)本质
        通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机事件(一页一页的翻)
        变成顺序事件(先找目录、找数据)
        也就是说有了索引机制，可以总是用一种固定的方式查找数据

    (5)补充：
        a.一张表中可以有多个索引(多个目录)
        b.索引虽然能够帮助你加快查询速度但是也有缺点:
            当表中有大量数据存在的前提下 创建索引速度会很慢
            在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
                因为每改一条数据，该索引处之后都默认清除重做，索引也不要随意的创建


2.b+树
    (1)只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 
        仅仅是用来指路的树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
    
    (2)如果把每片叶子的大小比喻一个固定的磁盘，叶子（磁盘）里放的是索引+数据
        索引占的空间越多，那数据占的空间反而越少，导致数据存储让树的层次越高，索引找到数据时间越慢
        所有要让索引占空间相对小，数据占多
        因此用id字段作为索引,int型占位比char型占位小
  	        占得空间少 一个磁盘块能够存储的数据多
  	        那么久降低了树的高度 从而减少查询次数


3.聚集索引(primary key)
    聚集索引指的就是主键 
        (1)Innodb  只有两个文件  直接将主键存放在了idb表中 
        (2)MyIsam  三个文件  单独将索引存在一个文件


4.辅助索引(unique,index)
    (1)查询数据的时候不可能一直使用到主键，也有可能会用到name,password等其他字段
        当这个时候没有办法利用聚集索引，可以根据情况给其他字段设置辅助索引(也是一个b+树）
    (2)叶子节点存放的是数据对应的主键值
        先按照辅助索引拿到数据的主键值
        之后还是需要去主键的聚集索引里面查询数据


5.覆盖索引(辅助索引直接拿到数据)
    在辅助索引的叶子节点就已经拿到了需要的数据
    (1)给name设置辅助索引
        select name from user where name='zcy';
    (2)非覆盖索引
        select age from user where name='wsx';      # 还要通过主键索引找数据