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;