mysql自学之路-02（索引、存储过程、游标、存储函数、触发器等）

 

目录

一、索引

二、存储过程

三、存储过程中的变量

四、if的使用方法

五、in 输入参数

六、out 输出参数

七、case when then 语法结构的学习

八、mysql 循环的学习

九、游标的学习 -01（基础学习）

十、学习存储函数

十一、触发器的学习

---

一、索引

-- 查询索引
show index  from ods_report_work;

-- 新增索引
create index idx_type_name on ods_report_work(type_name);
create unique index idx_sys_uid on ods_report_work(sys_uid);
create fulltext index idx_secd_type_name on ods_report_work(secd_type_name);
create index idx_type_name on ods_report_work(type_name,sys_uid,secd_type_name);

-- 删除索引
drop index idx_secd_type_name on ods_report_work;

二、存储过程

 

-- 创建存储过程
create procedure proc_test01()
begin
    select "hello world"  from dual;
end;

-- 调用存储过程
call proc_test01();

-- 查看存储过程的状态
show procedure status;

-- 查看存储过程的信息
select * from mysql.proc where db='mpdo_dev';

-- 显示创建存储过程的时候的语句
show create procedure proc_test01;

-- 删除存储过程
drop procedure if exists proc_test01;

-- 修改结束分隔符（将分隔符设置为$,默认分隔符为;）
delimiter $

三、存储过程中的变量

 

-- 存储过程中的变量
-- 定义变量
create procedure proc_test02()
begin 
declare num int default 5;
select num+5;
end;

call proc_test02;

-- 给变量赋值 -01 set
create procedure proc_test03()
begin 
    declare num int default 5;
set num=num+10;
select num;
end;

call proc_test03;

-- 给变量赋值 -01 select ...into
create procedure proc_test04()
begin
    declare num int default 0;
select count(*) into num from ods_report_work;
select concat('ods_report_work 表的个数:',num);
end;
call proc_test04;

四、if的使用方法

 

-- if的使用方法
create procedure proc_test05()
begin
    declare height int default 175;
declare description varchar(50) default '';
if height >=180 then 
set description='好身材';
elseif height<180 and height>=175 then
set description='一般身材';
else
set description='不好的身材';
end if;
select concat('身高:',height,'对应的身材:',description);
end;

call proc_test05;

五、in 输入参数

 

-- in 输入参数
-- 根据传入的身高变量，获取当前身高的所属的身材类型
create procedure proc_test06(in height int)
begin
    -- declare height int default 175;
declare description varchar(50) default '';
if height >=180 then 
set description='好身材';
elseif height<180 and height>=175 then
set description='一般身材';
else
set description='不好的身材';
end if;
select concat('身高:',height,'对应的身材:',description);
end;

call proc_test06(176);

 

六、out 输出参数

 

-- out 输出参数
-- 根据传入的身高变量，获取当前身高的所属的身材类型
create procedure proc_test07(in height int,out description varchar(50))
begin
    -- declare height int default 175;
-- declare description varchar(50) default '';
if height >=180 then 
set description='好身材';
elseif height<180 and height>=175 then
set description='一般身材';
else
set description='不好的身材';
end if;
-- select concat('身高:',height,'对应的身材:',description);
end;

call proc_test07(166,@description);
select @description;

-- 设置临时会话变量 使用@
set @name='zhangsan';
select @name;

 

七、case when then 语法结构的学习

 

-- case when then 语法结构的学习
create procedure proc_test08(in mon int)
begin 
    declare res varchar(50) default '';
case 
when mon >=1 and mon<=3 then 
set res='第一季度';
when mon >=4 and mon<=6 then 
set res='第二季度';
when mon >=7 and mon<=9 then 
set res='第三季度';
else 
set res='第四季度';
end case;
select concat('输入的月份:',mon,'所属的季度为:',res) as content;
end;

call proc_test08(8);

八、mysql 循环的学习

-- mysql 循环的学习
-- 计算从1加到N的和 通过while循环实现
create procedure proc_test09(in n int)
begin 
    declare total int default 0;
    declare num int default 1;
    while num<=n do
        set total=total+num;
        set num=num+1;
    end while;
    select total;
end;
call proc_test09(100);

-- 计算从1加到N的和 通过repeat循环实现
create procedure proc_test10(in n int)
begin 
    declare total int default 0;
    repeat
    set total=total+n;
    set n=n-1;
    until n=0
    end repeat;
    select total;
end;

call proc_test10(100);

-- 计算从1加到N的和 通过loop循环实现
create procedure proc_test11(in n int)
begin 
    declare total int default 0;
    c:loop
        set total=total+n;
        set n=n-1;
        if n<=0 then 
          leave c;
        end if;
    end loop c;
    select total;
end;

call proc_test11(3);

九、游标的学习 -01（基础学习）

 

-- 游标的学习 -01（基础学习）

-- 1. 先建表
CREATE TABLE `test_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `ord` varchar(64) DEFAULT NULL,
  `comm` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

-- 2. 并插入数据
INSERT INTO test_test (name,ord,comm) VALUES 
('zhangsan','1','1')
,('lisi','2','2')
;

-- 3. 如果该存储过程已经存在则将其删除
drop procedure if exists proc_test12;

-- 4. 创建存储过程
create procedure proc_test12()
begin
    declare e_id int(11);
    declare e_name varchar(64);
    declare e_ord varchar(64);
    declare e_comm varchar(64);

    -- ① 声明游标
    declare test_res cursor for select * from test_test;
    -- ②打开游标
    open test_res;
    -- ③获取游标
    fetch test_res into e_id,e_name,e_ord,e_comm;
    -- 查询数据
    select concat('id=',e_id,',name=',e_name,',ord=',e_ord,',comm=',e_comm);

    -- 获取游标
    fetch test_res into e_id,e_name,e_ord,e_comm;
    -- 查询数据
    select concat('id=',e_id,',name=',e_name,',ord=',e_ord,',comm=',e_comm);
    -- ④关闭游标
    close test_res;
end;

-- 5. 调用存储过程
call proc_test12;

-- 使用循环语句移动展示游标，避免not found的报错
create procedure proc_test13()
begin
    declare e_id int(11);
    declare e_name varchar(64);
    declare e_ord varchar(64);
    declare e_comm varchar(64);
    declare has_data int default 1;

    -- ① 声明游标
    declare test_res cursor for select * from test_test;
    -- 声明退出句柄，当数据找不到的时候将has_data设置为0，该语句必须紧跟在游标声明语句（select语句）的后面
    declare exit handler for not found set has_data=0;
    -- ②打开游标
    open test_res;

    repeat 
        -- ③获取游标
        fetch test_res into e_id,e_name,e_ord,e_comm;
        -- 查询数据
        select concat('id=',e_id,',name=',e_name,',ord=',e_ord,',comm=',e_comm);
        until has_data=0
    end repeat;
    -- ④关闭游标
    close test_res;
end;

call proc_test13;

 

十、学习存储函数

 

-- 存储过程是没有返回值的函数
-- 存储函数是有返回值的过程
-- 学习存储函数
create function fun01(cname varchar(64))
returns int
begin
    declare cnum int;
    select count(*) into cnum from test_test where name=cname;
    return cnum;
end;

-- SQL 错误 [1418] [HY000]: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 报错的解决方法
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
select fun01('zhangsan');

drop function if exists fun01;

 

十一、触发器的学习

-- 触发器的学习
-- 通过触发器记录test_test表的数据变更日志 test_test_logs,包含增加、修改、删除；
-- 1.先建表
CREATE TABLE `test_test_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `operation_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '操作类型',
  `operation_time` datetime NOT NULL COMMENT '操作时间',
  `operation_id` int(11) NOT NULL COMMENT '被操作的id',
  `operation_param` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '被操作的参数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
-- 新建触发器
create trigger trigger_01
after insert
on test_test
for each row
begin
    insert into test_test_logs(id,operation_type,operation_time,operation_id,operation_param)values
    (null,'insert',now(),new.id,concat('插入后（id:',new.id,',name:',new.name,',ord:',new.ord,',comm:',new.comm,')'));
    
end;

-- 执行插入操作
INSERT INTO test_test (name,ord,comm) VALUES ('wangwu','3','3');

-- 查看 test_test_logs表
select * from test_test_logs;

-- 新建触发器(更新 update)
create trigger trigger_update_01
after update
on test_test
for each row
begin
    insert into test_test_logs(id,operation_type,operation_time,operation_id,operation_param)values
    (null,'update',now(),new.id,concat('更新前(id:',old.id,',name:',old.name,',ord:',old.ord,',comm:',old.comm,
    ')。更新后(id:',new.id,',name:',new.name,',ord:',new.ord,',comm:',new.comm,')'));
    
end;

-- 执行更新操作
update test_test set ord=6 where comm=3;

-- 查看 test_test_logs表
select * from test_test_logs;

-- 新建触发器(删除 delete)
create trigger trigger_delete_01
after delete
on test_test
for each row
begin
    insert into test_test_logs(id,operation_type,operation_time,operation_id,operation_param)values
    (null,'delete',now(),old.id,concat('删除前(id:',old.id,',name:',old.name,',ord:',old.ord,',comm:',old.comm,')'));
    
end;

-- 执行删除操作
delete from test_test  where comm=3;

-- 查看 test_test_logs表
select * from test_test_logs;

-- 查看触发器
show triggers;

-- 删除触发器
drop trigger trigger_01;