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,casewhen 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编码，6select char_length('数据库')//          -- 3select char_length(trim(' 数据库 '))//  --3select 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 rowbegin
    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 rowbegin
    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 rowbegin
    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//