MySQL补充（函数/过程/索引/视图）

一、函数

1. MySQL内置函数

/* 
函数
MySQL内置函数
~ 聚合函数：sum / avg / count / max / min / stddev_pop / var_pop
    ~ 日期函数：curdate / curtime / now / datediff / timestampdiff / adddate
    ~ 数值函数：round / floor / ceil / abs / sqrt / exp / power / log
    ~ 字符串函数：char_length / trim / substr / concat / lpad / rpad / format / left / right
    ~ 窗口函数：rank / dense_rank / row_number / lag / lead
    ~ 流程控制函数：if / ifnull / nullif
用户自定义函数（UDF）

*/

use school;

-- 如果学院介绍超过50个字符，就将其截断，后面跟一个省略号
select col_name
     , case when char_length(col_intro) > 50 
            then concat(left(col_intro, 50), '……')
            else col_intro
  end as col_intro
  from colleges;

时间函数：

2. 用户自定义函数（UDF）

-- 重新定义终止符
delimiter $$

create function truncate_string (
original_string varchar(16383),
    max_char_length int unsigned
) returns varchar(16383) no sql
begin
if char_length(original_string) > max_char_length then
return concat(left(original_string, max_char_length), '……');
else
        return original_string;
    end if;
end $$

-- 恢复原来的终止符
delimiter ;

二、过程

-- 存储过程（stored procedure）
-- 提前准备好一组SQL存储在数据库服务器端，需要的使用这组SQL的时候直接调用即可

use hrs;

delimiter $$

create procedure upgrade_salary()
begin
declare flag boolean default 1;
    declare continue handler for sqlexception set flag = 0;
    
start transaction;

update tb_emp set sal = sal + 500 where dno = 10;
    update tb_emp set sal = sal + 800 where dno = 20;
    update tb_emp set sal = sal + 300 where dno = 30;
    
    if flag then
commit;
    else
rollback;
end if;
end $$

-- 调用过程
call upgrade_salary();

三、索引

use hrs;

-- explain - 生成执行计划

-- 1. 主键
explain select * from tb_emp where eno = 7800;

-- 2. 无索引 全表扫描
explain select * from tb_emp where ename = '张三丰';

-- 创建索引
create index idx_emp_ename on tb_emp (ename);

-- 删除索引
drop index idx_emp_ename on tb_emp;
alter table tb_emp drop index idx_emp_ename;

-- 前缀索引
-- 时间和空间是不可调和的矛盾
create index idx_emp_ename on tb_emp (ename(1));

-- 复合索引
-- 最左匹配原则
create index idx_emp_ename on tb_emp (ename, job);

explain select * from tb_emp where ename = '张三丰' and job = '总裁';

explain select * from tb_emp where ename = '张三丰';

-- 违反了最左匹配原则无法使用索引
explain select * from tb_emp where job = '总裁';

explain select * from tb_emp where job = '总裁' and ename = '张三丰';

-- 如果使用了or连接条件无法使用索引
explain select * from tb_emp where ename = '张三丰' or job = '总裁';

explain select * from tb_emp where ename like '张%';

-- 违反了最左匹配原则无法使用索引
explain select * from tb_emp where ename like '%张%';

-- 由于使用了复合索引，复合索引包含了ename、job字段，所以下面的查询不需要回表
select eno, ename, job from tb_emp where ename = '张三丰';

explain可用来分析SQL的执行计划，
结果：

四、json

1. json对象

use hrs;

create table `tb_test`
(
`user_id` bigint unsigned,
`login_info` json,
primary key (`user_id`)
) engine=innodb;

insert into `tb_test` values 
    (1, '{"tel": "13122335566", "QQ": "654321", "wechat": "jackfrued"}'),
    (2, '{"tel": "13599876543", "weibo": "wangdachui123"}');

-- '$'表示整个对象
select user_id
     , json_unquote(json_extract(login_info, '$.tel')) as tel
     , json_unquote(json_extract(login_info, '$.wechat')) as wechat
     , json_unquote(json_extract(login_info, '$.weibo')) as weibo
  from tb_test;
  
select user_id
     , login_info ->> '$.tel' as tel
     , login_info ->> '$.wechat'as wechat
  from tb_test;

2. json数组（用户标签系统）

create table `tb_tags`
(
`tag_id` int unsigned not null comment '标签ID',
`tag_name` varchar(20) not null comment '标签名',
primary key (`tag_id`)
) engine=innodb;

insert into `tb_tags` (`tag_id`, `tag_name`) 
values
    (1, '70后'),
    (2, '80后'),
    (3, '90后'),
    (4, '00后'),
    (5, '爱运动'),
    (6, '高学历'),
    (7, '小资'),
    (8, '有房'),
    (9, '有车'),
    (10, '爱看电影'),
    (11, '爱网购'),
    (12, '常点外卖');

create table `tb_users_tags`
(
`user_id` bigint unsigned not null comment '用户ID',
`user_tags` json not null comment '用户标签'
) engine=innodb;

insert into `tb_users_tags` values 
    (1, '[2, 6, 8, 10]'),
    (2, '[3, 10, 12]'),
    (3, '[3, 8, 9, 11]');

-- 查询爱看电影的用户
select `user_id` 
  from `tb_users_tags` 
 where 10 member of (`user_tags`->'$');

-- 查询爱看电影的80后
-- json_contains一个数组有没有包含另一个数组的元素
select `user_id` 
  from `tb_users_tags` 
 where json_contains(`user_tags`->'$', '[2, 10]');
 
-- 查询爱看电影或80后或90后
select `user_id` 
  from `tb_users_tags`
 where json_overlaps(user_tags->'$', '[2, 3, 10]');

五、视图

视图是关系型数据库中将⼀组查询指令构成的结果集组合成可查询的数据表的对象。
简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是⼀种实体结构，⽽视图是⼀种虚拟结构，你也可以将视图理解为保存在数据库
中被赋予名字的 SQL 语句。

-- 创建视图
create view `vw_emp_simple`
as
select `eno`,
 `ename`,
 `job`,
 `dno`
 from `tb_emp`;
 
-- 查找
select * from `vw_emp_simple`;

-- 删除视图
 drop view if exists `vw_emp_simple`;