day33_mysql函数,存储过程，索引，视图

day33

总结

• 函数

  -- 2021-6-11
  
  use school;
  
  select col_name, 
  if (char_length(col_intro)>50,
  concat(substring(col_intro, 1, 50), '......'), col_intro)
  from tb_college;
  
  
  -- 修改终止符
  delimiter $$
  -- 创建函数
  create function fn_truncate_string(x varchar(16383), cnt smallint) 
  returns varchar(16383) no sql
  begin
  	declare result varchar(16383) default '';
  	IF char_length(x)>cnt then
  		set result=concat(substring(x,1,cnt));
      else
  		set result=x;
      end if;
      return result;
  end $$
  -- 修改终止符
  delimiter ;
  
  select fn_truncate_string('hfeafeae', 3);
  
  drop function if exists fn_truncate_string;
  
  -- 为什么要加 no sql
  -- MySQL函数的小问题
  
  -- mysql开启了bin-log, 我们就必须指定我们的函数是否是哪种类型： 
  -- 1 DETERMINISTIC 不确定的 
  -- 2 NO SQL 没有SQL语句，当然也不会修改数据 
  -- 3 READS SQL DATA 只是读取数据，当然也不会修改数据 
  -- 4 MODIFIES SQL DATA 要修改数据 
  -- 5 CONTAINS SQL 包含了SQL语句
  
  -- 第一种是在创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个
  
  -- 第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:
  
  -- 1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
  -- 2.MySQL启动时，加上--log-bin-trust-function-creators选贤，参数设置为1
  -- 3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1
  
  
  delimiter $$
  
  create function fn_truncate_string2(x varchar(16383), cnt smallint)
  returns varchar(16383) no sql
  begin
  	declare result varchar(16383) default '';
      if char_length(x)>cnt then
  		set result=concat(substring(x,1,cnt), '......');
  	else
  		set result=x;
  	end if;
      return result;
  end $$
  delimiter ;
  
  

• 存储过程

  use hrs;
  
  SELECT * FROM hrs.tb_emp;
  
  explain select eno, ename, job from tb_emp where ename='张三丰';
  explain select eno, ename, job from tb_emp where eno='7800';
  
  explain select eno, ename, job from tb_emp where ename='张三丰' and job='总裁';
  explain select eno, ename, job from tb_emp where ename='张三丰' or job='总裁';
  explain select eno, ename, job from tb_emp where job='总裁';
  
  -- 添加(非聚集)索引
  create index idx_ename on tb_emp (ename);
  -- 删除索引
  alter table tb_emp drop index idx_ename;
  
  -- 前缀索引
  create index idx_ename on tb_emp (ename(1));
  
  alter table tb_emp drop index idx_ename;
  
  -- 回表: 索引没有覆盖到所有的列，那么通过索引定位到数据之后，
  -- 还需要做一次回表（再查一次聚集索引），才能获得所有的数据
  
  -- 覆盖索引 (索引已经覆盖到了想获取的所有的列)
  -- 复合索引 (遵循最左匹配原则,如果左边的索引不匹配则不走索引)
  create index idx_emp on tb_emp (ename, job);
  -- 复合索引中job倒序
  create index idx_emp on tb_emp (ename, job desc);
  alter table tb_emp drop index idx_emp;
  
  

• 视图

  
  -- 创建视图（查询的快照）
  create view vw_emp as 
  select eno, ename, job, mgr, dno from tb_emp;
  
  create view vw_dept_emp_count as 
  select dname, ifnull(total, 0) as total 
  from tb_dept t1 left outer join 
  (select dno, count(*) as total from tb_emp group by dno) t2 
  on t2.dno=t1.dno;
  
  select dname, total from vw_dept_emp_count;
  
  drop view vw_dept_emp_count;
  

• 索引

  use hrs;
  
  SELECT * FROM hrs.tb_emp;
  
  explain select eno, ename, job from tb_emp where ename='张三丰';
  explain select eno, ename, job from tb_emp where eno='7800';
  
  explain select eno, ename, job from tb_emp where ename='张三丰' and job='总裁';
  explain select eno, ename, job from tb_emp where ename='张三丰' or job='总裁';
  explain select eno, ename, job from tb_emp where job='总裁';
  
  -- 添加(非聚集)索引
  create index idx_ename on tb_emp (ename);
  -- 删除索引
  alter table tb_emp drop index idx_ename;
  
  -- 前缀索引
  create index idx_ename on tb_emp (ename(1));
  
  alter table tb_emp drop index idx_ename;
  
  -- 回表: 索引没有覆盖到所有的列，那么通过索引定位到数据之后，
  -- 还需要做一次回表（再查一次聚集索引），才能获得所有的数据
  
  -- 覆盖索引 (索引已经覆盖到了想获取的所有的列)
  -- 复合索引 (遵循最左匹配原则,如果左边的索引不匹配则不走索引)
  create index idx_emp on tb_emp (ename, job);
  -- 复合索引中job倒序
  create index idx_emp on tb_emp (ename, job desc);
  alter table tb_emp drop index idx_emp;