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.数字类

语句	含义
select rand() from dual//	随即小数
select * from stuinfo order by rand()//	随即排序
select round(5.6) from dual//	四舍五入
select ceil(5.6)//	向上取整
select floor(5.6)//	向下取整

 

b.大小写转换

语句	含义
select ucase('hello')//	大写
select lcase('Hello')//	小写

 

c.截取字符串

语句	含义
select left('abcdef',3)//	从左边截取3个字符
select right('abcdef',3)//	从右边截取3个字符
select substring('abcdef',2,3)//	从第二个位置向后取3个字符

 

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//