mysql 学习笔记day3

1.索引
2.函数
3.存储过程
4.视图
5.触发器

1.索引
    在数据库中索引最核心的作用是：加速查找。：例如：在含300万条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒
    1.1索引原理
        为什么加上索引后速度能有这么大的提升呢？
        因为索引的底层是基于B+Tree的数据结构存储的
        数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同
            myisam引擎，非聚簇索引（数据和索引结构分开存储）
            innodb引擎，聚簇索引（数据和主键索引结构存储到一起）
        1.1.1非聚簇索引（myisam引擎）
            create table 表名(
                in int not null auto_increment primary key,
                name varchar(32) not null,
                age int
            )engine=myisam default charset=utf8;
        1.1.2聚簇索引
            create table 表名(
                in int not null auto_increment primary key,
                name varchar(32) not null,
                age int
            )engine=innodb default charset=utf8;
    一般使用innodb引擎，聚簇索引
    1.2常见索引
        在innodb引擎下，索引底层等都是基于B+Tree的数据结构存储的（聚簇索引）
            主键索引：加速查找，不能为空，不能重复。+联合主键索引
            唯一索引：加速查找，不能重复。+联合唯一索引
            普通索引：加速查找。+联合索引
        1.2.1
            主键和联合主键索引
                create table 表名(
                    id int not null auto_increment primary key,    --主键
                    name varchar(32) null
                );
                create table 表名(
                    id int not null auto_increment,
                    name varchar(32) not null,
                    primary key(id)
                );
                create table 表名(
                    id int not null auto_increment,
                    name varchar(32) not null,
                    primary key(列1，列2)
                );                                    --如果有多列，称为联合主键（不常用且myisam引擎支持）
            
                如果表结构已经创建完成：
                    alter table 表名 add primary key(列名);
                    alter table 表名 drop primary key;
                注意：删除索引是时可能会报错，自增列必须定义为键。
                    如果删除键，必须连带把那列的自增取消：alter table 表 change id int not null;
            唯一和联合唯一索引
                create table 表名(
                    id int not null auto_increment primary key,
                    name varchar(32) not null,
                    email varchar(64) not null,
                    unique ix_name(name),
                    unique ix_email(email)                    --两个单列的唯一索引
                );
                reate table 表名(
                    id int not null auto_increment primary key,
                    name varchar(32) not null,
                    email varchar(64) not null,
                    unique (列1，列2)                        --如果有多列称为联合唯一索引
                );
                
                create unique index 索引名 on 表名（列名）;
                drop unique index 索引名 on 表名;
            
            
            1.2.3索引和联合索引
                create table 表名(
                    id int not null auto_increment primary key,
                    name varchar(32) not null,
                    email varchar(64) not null,
                    index ix_name(name)
                );
                create table 表名(
                    id int not null auto_increment primary key,
                    name varchar(32) not null,
                    email varchar(64) not null,
                    index  ix_email_name(列1，列2)                        --如果有多列称为联合索引
                );
                
                create index 索引名 on 表名（列名）;
                drop index 索引名 on 表名;
    1.3操作表————查询是要命中索引呀
        一般情况下，我们针对只要通过索引去搜搜都可以命中索引（通过索引结构加速查找）
        特殊情况下，让我们无法命中索引（即使创建了索引），这也是大家在开发中需要注意的：
            类型不一致：
                select * from big where name=123;   --未命中
                select * from big where email=123;  --未命中
                特殊的主键：
                    select * from big where id='123';
            使用不等于：
                select * from big where name!="wupeiqi";        --未命中
                select * from big where email!="111@qq.com"        --未命中
                特殊的主键：
                    select * from big where id!=123             --命中
            or,当or条件中有为建立索引的列才失效
                select *from big where id=123 or password='xx';        --未命中
                特别的：
                    select * from big where id=10 or password='xx' and name='xx';        --命中
            排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引
                select * from big order by name asc;        --未命中
                特殊的：
                    select name from big order by name asc;        --命中
                特别的主键：
                    select * from big order by id desc;
            like,模糊匹配时
                select * from big where name like "%u-12-19999";    --未命中
                select * from big where name like "_u-12-19999";    --未命中
                select * from big where name like "wu-%-19999";    --未命中
                特别的：
                    select * from big where name like "u-12-19999%";    --命中，通配符在最后
                    select * from big where name like "u-%";            --命中，通配符在最后
            使用函数：
                select *from big where reverse(name)="wupeiqi";        --未命中
                特别的：
                select *from big where name=reverse("wupeiqi");        --命中,函数在最后
            最左前缀，如果是联合索引，要遵循最左前缀原则：
                如果联合索引为：（name,password）:
                    name and password    --命中
                    name                --命中
                    password            --未命中
                    name or password    --未命中
        常见的无法命中索引的情况就是上述示例。

1.4执行计划
    MySQL中提供了执行计划，让你能够预判SQL的执行（只能给到一定的参考，不一定完全能预判准确）
    explain+sql语句
    其中比较重要的是type,它是SQL性能比较重要的标志，性能从低到高依次是
        all     <         index         <         range         <         index_merge <ref_or_null<    ref            <    eq_ref    <    system        /        const
        全盘扫描    全索引扫描            对索引范围扫描            索引合并                索引查找             连表操作    系统表仅有一行/（根据主键/唯一索引且结果只有一行）
小结：
    根据情况创建合适的索引（加速查找）
    有索引，则查询时要命中索引。
2.函数
    2.1常用函数
        selelct count(id),max(id),min(id),avg(id) from d1;
                个数      最大值  最小值  平均值    
        reverse(name)反转
        concat(name,name)字符串拼接
        now()时间
        date_format(now(),"%Y-%m-%d %H:%i:%s")格式化时间
    
        select concat('alex','sb')
        select sleep(1)等两秒钟
        char_length(str)
        concat_ws(separator,str1,str2,...)
            字符串拼接（自定义连接符）
            concat_ws()不会忽略任何空字符串。（然而会忽略所有的NULL）。
        conv(N,from_base,to_base)
            进制装换
                例如：
                    select conv('a',16,2);表示将a由16进制转换为2进制字符串表示
    2.2创建函数
        delimiter $$
        create function f1(
            i1 int,
            i2 int)
        return int
        begin
            declare num int;
            declare maxid int;
            select max(id) from big into maxid;
            set num=i1+i2+maxid;
            return(num)
        end $$
        delimiter ;
        执行函数
            select f1(11,22);
            select f1(11,id),name from d1;
        删除函数
            drop function fi;
        
        delimiter $$将默认的终止符换成$$
3.存储过程
    存储过程，是一个存储在MySQL中的sql语句集合，当主动去调用内存时，其中内部的SQL语句会按照逻辑执行。
        创建存储过程
        delimiter $$
        create procedure p1()
        begin
            select * from d1;
        end $$
        delimiter ;
    执行存储过程
        call p1();
    删除存储过程：
        drop procedure p1;
        
        
    3.1参数类型
        存储过程的参数可以有如下三种：
            in，仅用于传入参数用
            out,仅用于返回值用
            inout,既可以传入又可以当做返回值
        delimiter $$
        create procedure p2(
            in i1 int,
            in i2 int,
            inout i3 int,
            out r1 int
        )
        begin
            declare temp1 int;
            declare temp2 int default 0;
            set temp1=1;
            set r1=i1+i2+temp1+temp2;
            set i3=i3+100;
        end $$
        delimiter ;
        
        
        set @t1=4;
        set @t2=0;
        call p2(1,2,@t1,@t2);
        select @t1,@t2;
        
    3.2返回值&结果集
    3.3事物和异常
        事物，成功都成功，失败都失败
        delimiter $$
        create procedure p4(
            out p_return_code tinyint
        )
        begin
            declare exit handler for sqlexception
            begin
                --error
                set p_return_code=1;
                rollback;        
            end;
        
            declare exit handler for sqlwarning
            begin
                --warning
                set p_return_code=2;
                rollback;        --回滚，删除的数据在恢复过来
            end;
        
            start transaction;    --开启事物
                delete from d1'
                insert into tb(name)values('seven');
            commit;--提交事务
        
            --success
            set p_return_code=0;
        
            end $$
        delimiter ;
        
        set @ret=100;
        call p4(@ret);
        select @ret;
    
    3.4游标（效率比较低）
        delimiter $$
        create procedure p5()
        begin
            declare sid int;
            declare sname varchar(50);
            declare done int default false;
            
            
            declare my_cursor cursor for select id,name from d1;
            
            declare continue handler for not found set done=True;
            
            open my_cursor;
                xxoo:loop
                    ferch my_cursor into sid,sname;
                    if done then
                        leave xxoo;
                    end if;
                    insert into t1(name) values(sname);
                end loop xxoo;
            close my_cursor;
        end $$
        delimiter ;
        call p5();
        
4.视图
    视图其实是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当做表来使用。
    子查询
        select 
            *
        from
            (select nid,name from tb1 where nid>2) as A
        where
            A.name>"alex"
    创建视图
        create view v1 as select id,name from d1 where id>1;
    使用视图
        select *from va;
    删除视图
        drop view v1;
    修改视图
        alter view v1 as Sql语句
    注意：基于视图只能查询，针对视图不能执行增加，修改，删除。如果原表发生变化，视图表也会发生变化
5.触发器
    对某个表进行【增删改】操作的前后如果希望触发某个特定的行为时，可以使用触发器
    #插入前
    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
    删除
    更新
    ...
    
    示例：
        在T1表中插入数据之前，先在T2表中插入一行数据。
            delimiter $$
            create trigger tri_before_insert_tb1 before insert on t1 for each row
            begin
            
            if new.name='alex' then
                insert into t2(name) values(new.id);
            end if;
            
            end $$
            delimiter ;
            
            insert into t1(id,name,email) values(1,'alex','111@qq.com')
        在t1表中删除数据之后，再在t2 表中插入一行数据。
            delimiter $$
            create trigger tri_after_insert_tb1 after delete on t1 for each row
            begin
            
            if new.name='alex' then
                insert into t2(name) values(old.id);
            end if;
    
            end $$
            delimiter ;
        特别的：new表示新数据，old表示原来的数据