亚洲最大看欧美片,亚洲图揄拍自拍另类图片,欧美精品v国产精品v呦,日本在线精品视频免费

  • 站長資訊網(wǎng)
    最全最豐富的資訊網(wǎng)站

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    本篇文章給大家分享學(xué)習(xí)mysql存儲(chǔ)過程時(shí)的筆記,其咋哄詳細(xì)介紹了mysql儲(chǔ)存過程的相關(guān)知識(shí),希望對大家有幫助。

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    一、定義

    存儲(chǔ)過程(Stored Procedure)是在大型數(shù)據(jù)庫系統(tǒng)中,一組為了完成特定功能的SQL 語句集,存儲(chǔ)在數(shù)據(jù)庫中,經(jīng)過第一次編譯后調(diào)用不需要再次編譯,用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)(如果該存儲(chǔ)過程帶有參數(shù))來執(zhí)行它。存儲(chǔ)過程是數(shù)據(jù)庫中的一個(gè)重要對象。

    二、存儲(chǔ)過程的特點(diǎn)

    1、能完成較復(fù)雜的判斷和運(yùn)算
    2、可編程行強(qiáng),靈活
    3、SQL編程的代碼可重復(fù)使用
    4、執(zhí)行的速度相對快一些
    5、減少網(wǎng)絡(luò)之間的數(shù)據(jù)傳輸,節(jié)省開銷

    三、創(chuàng)建一個(gè)簡單的存儲(chǔ)過程

    1、創(chuàng)建存儲(chǔ)過程的簡單語法

    create procedure 名稱() begin ......... end

    2、創(chuàng)建一個(gè)簡單的存儲(chǔ)過程

    create procedure testa() begin     select * from users;     select * from orders; end;

    3、調(diào)用存儲(chǔ)過程

    call testa();

    運(yùn)行結(jié)果如圖(1)和圖(2):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(1)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(2)

    四、存儲(chǔ)過程的變量

    1、先通過一個(gè)簡單的例子來學(xué)習(xí)變量的聲明和賦值

    create procedure test2() begin   -- 使用 declare語句聲明一個(gè)變量   declare username varchar(32) default '';   -- 使用set語句給變量賦值   set username='xiaoxiao';   -- 將users表中id=1的名稱賦值給username   select name into username from users where id=1;   -- 返回變量   select username; end;

    2、概括
    (1)、變量的聲明使用declare,一句declare只聲明一個(gè)變量,變量必須先聲明后使用;

    (2)、變量具有數(shù)據(jù)類型和長度,與mysql的SQL數(shù)據(jù)類型保持一致,因此甚至還能制定默認(rèn)值、字符集和排序規(guī)則等;
    (3)、變量可以通過set來賦值,也可以通過select into的方式賦值;
    (4)、變量需要返回,可以使用select語句,如:select 變量名。

    五、變量的作用域

    1、變量作用域說明:
    (1)、存儲(chǔ)過程中變量是有作用域的,作用范圍在begin和end塊之間,end結(jié)束變量的作用范圍即結(jié)束。
    (2)、需要多個(gè)塊之間傳值,可以使用全局變量,即放在所有代碼塊之前
    (3)、傳參變量是全局的,可以在多個(gè)塊之間起作用
    2、通過一個(gè)實(shí)例來驗(yàn)證變量的作用域

    需求: 創(chuàng)建一個(gè)存儲(chǔ)過程,用來統(tǒng)計(jì)表users、orders表中行數(shù)數(shù)量和orders表中的最大金額和最小金額

    create procedure test3() begin   begin     declare userscount int default 0; -- 用戶表中的數(shù)量     declare ordercount int default 0; -- 訂單表中的數(shù)量     select count(*) into userscount from users;     select count(*) into ordercount from orders;     select userscount,ordercount; -- 返回用戶表中的數(shù)量、訂單表中的數(shù)量   end;   begin      declare maxmoney int default 0; -- 最大金額     declare minmoney int default 0; -- 最小金額     select max(money) into maxmoney from orders;     select min(money) into minmoney from orders;     select maxmoney,minmoney; -- 返回最金額、最小金額    end; end;

    調(diào)用以上存儲(chǔ)過程,結(jié)果如圖(3)和圖(4):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    (3)

    十分鐘帶你搞懂mysql存儲(chǔ)過程
    (4)

    3、我將過程test(3)改為如下:

    create procedure test3()

    begin

    begin

    declare userscount int default 0; — 用戶表中的數(shù)量

    declare ordercount int default 0; — 訂單表中的數(shù)量

    select count(*) into userscount from users;

    select count(*) into ordercount from orders;

    select userscount,ordercount; — 返回用戶表中的數(shù)量、訂單表中的數(shù)量

    end;

    begin

    declare maxmoney int default 0; — 最大金額

    declare minmoney int default 0; — 最小金額

    select max(money) into maxmoney from orders;

    select min(money) into minmoney from orders;

    select userscount,ordercount,maxmoney,minmoney; — 返回最金額、最小金額

    end;

    end;

    再次調(diào)用call test3(); 會(huì)報(bào)錯(cuò)如圖(5):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(5)

    4、將userscount,ordercount改為全局變量,再次驗(yàn)證

    create procedure test3()

    begin

    declare userscount int default 0; — 用戶表中的數(shù)量

    declare ordercount int default 0; — 訂單表中的數(shù)量

    begin

    select count(*) into userscount from users;

    select count(*) into ordercount from orders;

    select userscount,ordercount; — 返回用戶表中的數(shù)量、訂單表中的數(shù)量

    end;

    begin

    declare maxmoney int default 0; — 最大金額

    declare minmoney int default 0; — 最小金額

    select max(money) into maxmoney from orders;

    select min(money) into minmoney from orders;

    select userscount,ordercount,maxmoney,minmoney; — 返回最金額、最小金額

    end;

    end;

    再次調(diào)用call test3(); 會(huì)報(bào)錯(cuò)如圖(6)和圖(7):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(6)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(7)

    因此,存儲(chǔ)過程中變量的作用域,作用范圍在begin和end塊之間,end結(jié)束變量的作用范圍即結(jié)束

    六、存儲(chǔ)過程參數(shù)

    1、基本語法

    create procedure 名稱([IN|OUT|INOUT] 參數(shù)名 參數(shù)數(shù)據(jù)類型 ) begin ......... end

    存儲(chǔ)過程的參數(shù)類型有:IN,OUT,INOUT,下面分別介紹這個(gè)三種類型:

    2、存儲(chǔ)過程的傳出參數(shù)IN

    說明:

    (1)、傳入?yún)?shù):類型為in,表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過程事指定,如果不顯示指定為in,那么默認(rèn)就是in類型。

    (2)、IN類型參數(shù)一般只用于傳入,在調(diào)用過程中一般不作為修改和返回

    (3)、如果調(diào)用存儲(chǔ)過程中需要修改和返回值,可以使用OUT類型參數(shù)

    通過一個(gè)實(shí)例來演示:

    需求:編寫存儲(chǔ)過程,傳入id,根據(jù)id返回name

     create procedure test4(userId int)     begin             declare username varchar(32) default '';             declare ordercount int default 0;             select name into username from users where id=userId;             select username;     end;

    運(yùn)行如圖(8)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(8)

    3、存儲(chǔ)過程的傳出參數(shù)out

    需求:調(diào)用存儲(chǔ)過程時(shí),傳入userId返回該用戶的name

    create procedure test5(in userId int,out username varchar(32))

    begin

    select name into username from users where id=userId;

    end;

    調(diào)用及運(yùn)行結(jié)果如圖(9):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(9)

    概括:

    1、傳出參數(shù):在調(diào)用存儲(chǔ)過程中,可以改變其值,并可返回;

    2、out是傳出參數(shù),不能用于傳入?yún)?shù)值;

    3、調(diào)用存儲(chǔ)過程時(shí),out參數(shù)也需要指定,但必須是變量,不能是常量;

    4、如果既需要傳入,同時(shí)又需要傳出,則可以使用INOUT類型參數(shù)

    (3).存儲(chǔ)過程的可變參數(shù)INOUT

    需求:調(diào)用存儲(chǔ)過程時(shí),傳入userId和userName,即使傳入,也是傳出參數(shù)。

    create procedure test6(inout userId int,inout username varchar(32)) begin     set userId=2;     set username='';     select id,name into userId,username from users where id=userId; end;

    調(diào)用及運(yùn)行結(jié)果如圖(10)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(10)
    概括:
    1、可變變量INOUT:調(diào)用時(shí)可傳入值,在調(diào)用過程中,可修改其值,同時(shí)也可返回值;
    2、INOUT參數(shù)集合了IN和OUT類型的參數(shù)功能;
    3、INOUT調(diào)用時(shí)傳入的是變量,而不是常量;

    七、存儲(chǔ)過程條件語句

    1、基本結(jié)構(gòu)

    (1)、條件語句基本結(jié)構(gòu):

    if() then...else...end if;

    (2)、多條件判斷語句:

    if() then... elseif() then... else ... end if;

    2、實(shí)例
    實(shí)例1:編寫存儲(chǔ)過程,如果用戶userId是偶數(shù)則返回username,否則返回userId

    create procedure test7(in userId int) begin    declare username varchar(32) default '';    if(userId%2=0)    then        select name into username from users where id=userId;       select username;    else       select userId;       end if; end;

    調(diào)用及運(yùn)行結(jié)果如圖(11)和圖(12):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(11)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(12)

    2、存儲(chǔ)過程的多條件語句應(yīng)用示例

    需求:根據(jù)用戶傳入的uid參數(shù)判斷

    (1)、如果用戶狀態(tài)status為1,則給用戶score加10分;

    (2)、 如果用戶狀態(tài)status為2,則給用戶score加20分;

    (3)、 其他情況加30分

    create procedure test8(in userid int) begin    declare my_status int default 0;    select status into my_status from users where id=userid;    if(my_status=1)    then         update users set score=score+10 where id=userid;     elseif(my_status=2)     then         update users set score=score+20 where id=userid;     else         update users set score=score+30 where id=userid;     end if; end;

    調(diào)用程之前的users表的數(shù)據(jù)如圖(13),調(diào)用 call test8(1); 及運(yùn)行結(jié)果圖(14):

    十分鐘帶你搞懂mysql存儲(chǔ)過程
    圖(13)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(14)

    八、存儲(chǔ)過程循環(huán)語句

    1、while語句

    (1)、while語句的基本結(jié)構(gòu)

    while(表達(dá)式) do     ......   end while;

    (2)、示例
    需求:使用循環(huán)語句,向表test1(id)中插入10條連續(xù)的記錄

    create procedure test9() begin   declare i int default 0;   while(i<10) do      begin          select i;         set i=i+1;         insert into test1(id) values(i);      end;   end while; end;

    調(diào)用及運(yùn)行結(jié)果結(jié)果如圖(15)和圖(16):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(15)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(16)

    2、repeat語句
    (1)、repeat語句基本的結(jié)構(gòu):

    repeat...until...end repeat;

    (2)、示例

    需求:給test1表中的id字段插入數(shù)據(jù),從1到10

    create procedure test10() begin     declare i int default 0;     repeat      begin          select i;         set i=i+1;         insert into test1(id) values(i);     end;     until i>=10 -- 如果i>=10,則跳出循環(huán)     end repeat; end;

    調(diào)用及運(yùn)行結(jié)果結(jié)果如圖(17)和圖(18)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(17)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(18)

    概括:
    until判斷返回邏輯真或者假,表達(dá)式可以是任意返回真或者假的表達(dá)式,只有當(dāng)until語句為真是,循環(huán)結(jié)束。

    九、存儲(chǔ)過程游標(biāo)的使用

    1、什么是游標(biāo)
    游標(biāo)是保存查詢結(jié)果的臨時(shí)區(qū)域
    2、示例
    需求:編寫存儲(chǔ)過程,使用游標(biāo),把users表中 id為偶數(shù)的記錄逐一更新用戶名

    create procedure test11()     begin         declare stopflag int default 0;         declare username VARCHAR(32);         -- 創(chuàng)建一個(gè)游標(biāo)變量,declare 變量名 cursor ...         declare username_cur cursor for select name from users where id%2=0;         -- 游標(biāo)是保存查詢結(jié)果的臨時(shí)區(qū)域         -- 游標(biāo)變量username_cur保存了查詢的臨時(shí)結(jié)果,實(shí)際上就是結(jié)果集         -- 當(dāng)游標(biāo)變量中保存的結(jié)果都查詢一遍(遍歷),到達(dá)結(jié)尾,將變量stopflag設(shè)置為1,用于循環(huán)中判斷是否結(jié)束         declare continue handler for not found set stopflag=1;          open username_cur; -- 打卡游標(biāo)         fetch username_cur into username; -- 游標(biāo)向前走一步,取出一條記錄放到變量username中         while(stopflag=0) do -- 如果游標(biāo)還沒有結(jié)尾,就繼續(xù)             begin                  -- 在用戶名前門拼接 '_cur' 字符串                 update users set name=CONCAT(username,'_cur') where name=username;                 fetch username_cur into username;             end;         end while; -- 結(jié)束循環(huán)         close username_cur; -- 關(guān)閉游標(biāo)     end;

    調(diào)用結(jié)果如圖(19):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(19)

    十、自定義函數(shù)

    函數(shù)與存儲(chǔ)過程最大的區(qū)別是函數(shù)必須有返回值,否則會(huì)報(bào)錯(cuò)

    1、一個(gè)簡單的函數(shù)

    create function getusername(userid int) returns varchar(32)     reads sql data  -- 從數(shù)據(jù)庫中讀取數(shù)據(jù),但不修改數(shù)據(jù)     begin         declare username varchar(32) default '';         select name into username from users where id=userid;         return username;     end;

    調(diào)用及運(yùn)行結(jié)果如圖(20):

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(20)

    概括:

    1.創(chuàng)建函數(shù)使用create function 函數(shù)名(參數(shù)) returns 返回類型;

    2.函數(shù)體放在begin和end之間;

    3.returns指定函數(shù)的返回值;

    4.函數(shù)調(diào)用使用select getusername()。

    2、示例

    需求:根據(jù)userid,獲取accoutid,id,name組合成UUID作為用戶的唯一標(biāo)識(shí)

      create function getuuid(userid int) returns varchar(64)     reads sql data  -- 從數(shù)據(jù)庫中讀取數(shù)據(jù),但不修改數(shù)據(jù)     begin         declare uuid varchar(64) default '';         select concat(accontid,'_',id,'_',name) into uuid from users where id=userid;         return uuid;     end;

    調(diào)用及運(yùn)行結(jié)果如圖(21)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(21)

    十一、觸發(fā)器

    1、什么是觸發(fā)器

    觸發(fā)器與函數(shù)、存儲(chǔ)過程一樣,觸發(fā)器是一種對象,它能根據(jù)對表的操作時(shí)間,觸發(fā)一些動(dòng)作,這些動(dòng)作可以是insert,update,delete等修改操作。

    2、示例1

    (1)、需求:出于審計(jì)目的,當(dāng)有人往表users插入一條記錄時(shí),把插入的userid,username,插入動(dòng)作和操作時(shí)間記錄下來。

    create trigger tr_users_insert after insert on users     for each row      begin          insert into oplog(userid,username,action,optime)         values(NEW.id,NEW.name,'insert',now());     end;

    創(chuàng)建成功后,給uses表中插入一條記錄:

    insert into users(id,name,age,status,score,accontid)     values(6,'小周',23,1,'60','10001');

    執(zhí)行成功后,打開oplog表,可以看到oplog表中插入了一條記錄如圖(22)
    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(22)

    (2)、總結(jié)

    1、創(chuàng)建觸發(fā)器使用create trigger 觸發(fā)器名
    2、什么時(shí)候觸發(fā)?after insert on users,除了after還有before,是在對表操作之前(before)或者之后(after)觸發(fā)動(dòng)作的。
    3、對什么操作事件觸發(fā)? after insert on users,操作事件包括insert,update,delete等修改操作;
    4、對什么表觸發(fā)? after insert on users
    5、影響的范圍?for each row

    3、示例2

    需求:出于審計(jì)目的,當(dāng)刪除users表時(shí),記錄刪除前該記錄的主要字段值

    create trigger tr_users_delete before delete on users     for each row      begin          insert into oplog(userid,username,action,optime)         values(OLD.id,OLD.name,'delete',now());     end;

    刪除users表中的一條記錄

    delete from users where id=6;

    執(zhí)行成功后,打開oplog表,可以看到oplog表中插入了一條記錄如圖(23)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(23)

    十二、流程控制

    1、case分支

    (1)、基本語法結(jié)構(gòu)

    case ... when ... then.... when.... then.... else ...  end case;

    (2)、示例

    users表中,根據(jù)userid獲取status值,如果status為1,則修改score為10;如果status為2,則修改為20,如果status3,則修改為30;否則修改為40。

     create procedure testcate(userid int)     begin          declare my_status int default 0;         select status into my_status from users where id=userid;          case my_status             when 1 then update users set score=10 where id=userid;             when 2 then update users set score=20 where id=userid;             when 3 then update users set score=30 where id=userid;             else update users set score=40 where id=userid;         end case;     end;

    調(diào)用過程 call testcate(1); ,執(zhí)行結(jié)果如圖(24);

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(24)

    十四、存儲(chǔ)過程+event(事件)

    1、使用存儲(chǔ)過程+事件事件一個(gè)簡單的實(shí)現(xiàn)福彩3D開獎(jiǎng)

    需求:設(shè)計(jì)一個(gè)福彩的開獎(jiǎng)過程,沒3分鐘開獎(jiǎng)一次
    第一步:先編寫一個(gè)存儲(chǔ)過程open_lottery,產(chǎn)生3個(gè)隨機(jī)數(shù),生成一條開獎(jiǎng)記錄
    第二步:編寫一個(gè)時(shí)間調(diào)度器,每3分鐘調(diào)用一次這個(gè)過程

    create procedure open_lottery()         begin              insert into lottery(num1,num2,num3,ctime)             select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now();         end;
    create event if not exists lottery_event -- 創(chuàng)建一個(gè)事件         on schedule every  3 minute  -- on schedule 什么時(shí)候來執(zhí)行,沒三分鐘執(zhí)行一次         on completion preserve          do call open_lottery;

    運(yùn)行結(jié)果如圖(25)

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    圖(25)

    注意,如果event之一沒有運(yùn)行,請按照以下辦法解決:

    (1)、 show variables like '%event_scheduler%';
    set global event_scheduler=on;

    (2)、 alert event lottery_event enable;

    2、解析event的創(chuàng)建格式
    (1)、基本語法

    create event[IF NOT EXISTS]event_name -- 創(chuàng)建使用create event     ON SCHEDULE schedule -- on schedule 什么時(shí)候來執(zhí)行     [ON COMPLETION [NOT] PRESERVE] -- 調(diào)度計(jì)劃執(zhí)行完成后是否還保留     [ENABLE | DISABLE] -- 是否開啟事件,默認(rèn)開啟     [COMMENT 'comment'] -- 事件的注釋     DO sql_statement; -- 這個(gè)調(diào)度計(jì)劃要做什么?

    (2)、執(zhí)行時(shí)間說明

    1.單次計(jì)劃任務(wù)示例
    在2019年2月1日4點(diǎn)執(zhí)行一次

    on schedule at '2019-02-01 04:00:00'

    2. 重復(fù)計(jì)劃執(zhí)行
    on schedule every 1 second 每秒執(zhí)行一次
    on schedule every 1 minute 每分鐘執(zhí)行一次
    on schedule every 1 day 沒天執(zhí)行一次

    3.指定時(shí)間范圍的重復(fù)計(jì)劃任務(wù)
    每天在20:00:00執(zhí)行一次
    on schedule every 1 day starts '2019-02-01 20:00:00'

    十五、本文所用到的表

    1、lottery表

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    2、oplog表

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    3、orders表

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    4、test1表

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    5、user表

    十分鐘帶你搞懂mysql存儲(chǔ)過程

    推薦學(xué)習(xí):mysql視頻教程

    贊(0)
    分享到: 更多 (0)
    網(wǎng)站地圖   滬ICP備18035694號-2    滬公網(wǎng)安備31011702889846號