mysql入門（五）

SQL編程

1).if-elseif-else語句

語法:
if 條件 then
    //條件1
elseif 條件 then
    //條件2
else
    //條件3
end if;

示例演示：

create procedure pro_9(in grade int)  -- 輸入等級
begin
    if grade=1 then
        select '最強王者' as '排位等級';
    elseif grade=2 then
        select '超凡大師' as '排位等級';
    elseif grade=3 then
        select '璀璨鑽石' as '排位等級';
    else
        select '恥辱廢鐵' as '排位等級';
    end if;
end//
​
call pro_9(3)//

(2).case-when-then

語法:
case 變數
    when 1 then 條件1
    when 2 then 條件2
    when 3 then 條件3
    else 預設條件
end case;

示例演示：

create procedure pro_10(in grade int)
begin
    case grade
        when 1 then select '最強王者' as '排位等級';
        when 2 then select '超凡大師' as '排位等級';
        when 3 then select '璀璨鑽石' as '排位等級';
        else select '恥辱廢鐵' as '排位等級';
    end case;
end//
​
call pro_10(2)//

case-when-then語句寫在sql語句當中：

select sid,sname,sex,age,city,ch,ma,case
when ch>=90 then '等級A'
when ch>=80 then '等級B'
when ch>=70 then '等級C'
when ch>=60 then '等級D'
else '退學吧' end as '等級'
from stuinfo left join stumarks using(sid)//

(3).loop迴圈

在python中迴圈遇到break會退出，MySQL中leave=break。

​create procedure pro_11(in num int)
begin
    declare total int default 0;
    declare i int default 0;
    -- 方法體可以起別名
    xiaofang:loop
        set total = total+i;
        set i=i+1;
        if i>num then
            leave xiaofang;
        end if;
    end loop;
    select total from dual;
end//
​
call pro_11(100)//

(4).while迴圈

語法:
while 條件 do
    //代碼
end while

示例演示： 

create procedure pro_12(in num int)
begin
    declare total int default 0;
    declare i int default 0;
    while num>=i do
        set total = total+i;
        set i=i+1;
    end while;
    select total from dual;
end//
​
call pro_12(100)//

(5).repeat迴圈

語法:
repeat
    代碼
    until 條件  --碰到條件為true就結束迴圈
end repeat;

 示例演示：

create procedure pro_13(in num int)
begin
    declare total int default 0;
    declare i int default 0;
    repeat
        set total = total+i;
        set i=i+1;
        until i>num
    end repeat;
    select total from dual;
end//
​
call pro_13(100)//

(6).leave和iterate

leave相當於break；iterate相當於continue。

示例演示：

create procedure pro_14()
begin
    declare i int default 0;
    sign:while i<5 do
        set i=i+1;
        if(i=3) then
            iterate sign;
        end if;
        select i from dual;
    end while;
end//
​
call pro_14()//

函數

(1)內置函數

a.數字類

b.大小寫轉換

c.截取字元串

d.字元拼接

關鍵字:concat()

select concat(sid,sname,age,sex,city) from stuinfo//

e.獲取字元的長度

length()        #位元組長度
char_length()   #字元長度
trim()          #去除前後空格
replace()        #替換

select length('資料庫')//               --cmd中輸出的是gbk編碼，6
​
select char_length('資料庫')//          -- 3
​
select char_length(trim(' 資料庫 '))//  --3
​
select replace('MySQL','SQL','sql')//  --將MYSQL中的SQL替換成sql

f.獲取Unix時間戳

select unix_timestamp()//
​
-- 把時間戳轉變為當前的時間格式
select from_unixtime(unix_timestamp())//

g.時間函數

select now(),year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())\G

h.西式時間函數

select dayname(now()) as `星期`,monthname(now()) as `月份`,dayofyear(now()) as '本年第幾天'//

(2)自定義函數

語法：
create function `函數名`(參數....) returns 返回的數據類型
begin
    函數體;
end//

 示例演示：

create function func_1() returns varchar(64)
begin
    return '2020年東京奧運會';
end//
​
select func_1()//


create function func_2(num int) returns int(64)
begin
    return num*num;
end//
​
select func_2(10)//

觸發器

1.觸發器是一個特殊的存儲過程
2.不需要調用,MySQL自動調用
3.是一個事務,可以回滾
4.跟著表後面的

(1).觸發器的種類

觸發事件，產生行為（增刪改）
1.insert觸發器
2.update觸發器
3.delete觸發器

(2).創建觸發器

語法：
create trigger `觸發器名`  觸發時間[before|after]  觸發事件 on `表名` for each row
begin
    代碼+sql
end//

(3)new表和old表

1.這兩張表都是臨時表 ；註意和視圖的區別，視圖是merge合併演算法，是永久表
2.當觸發器觸發的時候,記憶體中自動創建,執行完畢以後自動銷毀
3.它們的表結構和關聯的表是一致的
4.只讀,不能修改

(4)insert觸發器

create trigger `trig1` after insert on `stuinfo`for each row
begin
    declare sid_ int default 0;
    declare ch_ float default 0;
    declare ma_ float default 0;
    set sid_ = new.sid;
    insert into stumarks values(sid_,ch_,ma_);
end//
​
insert into stuinfo values(null,'ABC',2,18,'Q',90);

(5)update觸發器

create trigger `trig2` after update on `stuinfo`for each row
begin
    declare sid_ int default 0;
    declare old_sid int default 0;
    declare ch_ float default 0;
    declare ma_ float default 0;
    set sid_ = new.sid;
    set old_sid = old.sid;
    update stumarks set sid=sid_ where sid=old_sid;
end//
​
update stuinfo set sid=17 where sid=16//

(6)delete觸發器

create trigger `trig3` after delete on `stuinfo`for each row
begin
    declare sid_ int default 0;
    declare ch_ float default 0;
    declare ma_ float default 0;
    set sid_ = old.sid;
    delete from stumarks where sid=sid_;
end//
​
delete from stuinfo where sid=17//