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

朴灿烈づ我的快乐病毒、 2023-01-03 14:05 138阅读 0赞

**目录**

一、索引

二、存储过程

三、存储过程中的变量

四、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;