数据库（三）

MySql变量分类
    1.系统变量@@
        系统全局变量：global variables
        系统会话变量：session variables
    2.状态变量
        全局状态变量：global status
        会话状态变量：session status
    3.用户变量@
        自定义全局变量
        自定义局部变量

变量赋值
    set
    select

范例：

-- 系统变量
show global variables;

-- 查看当前系统的编码
show global variables like '%char%';

-- 查看当前会话(连接)的编码
show session variables like '%char%';

-- 获取当前的数据库状态
show status;
show session status;
show session status like '%show%';

-- 使用具体的系统变量
select @@global.auto_increment_increment;

-- 使用用户自定义变量
-- 使用set直接赋值
set @test = 1;
-- 使用select直接赋值
select @test;

-- 从数据库中查询
use schooldb;
select @num := count(*) from student;
select @num;

select * from student;
-- 查询学员姓名并赋值给变量
select @stuName := Name from student;
select @stuName;
select * from student;

select @stuName;

select @num;

select @test;

-- 使用@符号自定义的变量属于session级别，不会影响其他连接

   
===============================存储过程============================
定义：
预先存储好的SQL程序-->保存在MySql-->通过名称和参数执行，也可返回结果
单个SELECT、语句SELECT语句块、SELECT语句与逻辑控制语句

优点：
    执行速度快，允许模块化程序设计，提高系统安全性，减少网络流通量
缺点：
    服务器成本高、要专门的DBA、不适应需要频繁改动的项目

语法：    
    1.定义存储过程

 -- 声明分隔符
 delimiter $$
 create procedure 存储过程名 (参数名1 参数类型1,参数名2 参数类型2,…)
 -- 语句块开始
 begin
 --SQL语句及逻辑代码
 -- 语句块结束
 end$$
 -- 还原分隔符
 delimiter ;

    2.调用存储过程
        call 存储过程名(参数1,参数2,…);
    
范例：定义一个存储过程，用来计算2个整数进行四则运算的结果

use myschool;
/*创建一个存储过程，用来实现一个简单的加法运算*/
/* 重新定义分隔符 */
delimiter $$

-- 如果存储过程存在，就先删除
drop procedure if exists myAdd $$

/* 创建存储过程 */
create procedure myAdd(num1 int ,num2 int)
begin
    select num1 + num2 as 和;
end$$
/* 还原分隔符 */
-- 【中间一定要空格】
-- ★★【注意：存储过程中的注释要单独成为一行】
delimiter ;
/* 调用存储过程 */
call myAdd(14,15);

范例：由于数学题目考试太难，所以决定修改成绩信息

/* 由于数学考试题目太难，所以决定修改成绩信息
1.考试的及格人数小于一半，每个不及格的人加5分
2.及格人数超过一半，就不加分
3.最后显示加分前的及格率，以及加分后的及格率
*/
use schooldb;
select * from `subject`;
select * from student;
select * from score;

delimiter $$
drop procedure if exists changeScore $$
create procedure changeScore()
begin
    /*定义局部变量，用来保存及格的人数和总人数*/
    declare passNum int default 0;
    declare total int default 0;
    declare passNum1 int default 0;

    /* 从学生表中查询出总人数 */
    select count(*) into total from student where GradeID = 1;

    select count(*) into passNum from score
        inner join `subject` on score.SubjectID = `subject`.SubjectID
        where Score >= 60 and `subject`.GradeID = 1;
    
    /* 判断及格的人数小于一半 */
    if (passNum < total / 2) then
        -- 加分
        update score set Score = Score + 5 where Score < 60
        and
        SubjectID in (select SubjectID from `subject` where GradeID = 1);
    elseif passNum > 1 then
        select * from student;
    end if;

    /* 查询加分后的及格人数 */
    select count(*) into passNum1 from score
        inner join `subject` on score.SubjectID = `subject`.SubjectID
        where score >= 60 and `subject`.GradeID = 1;

    select passNum / total as 加分前,
           passNum as 加分前及格人数,
           passNum1 / total as 加分后,
           passNum1 as 加分后及格人数;
end$$
delimiter ;
/* 调用存储过程 */
call changeScore();

定义使用变量
    declare 变量名 数据类型 [default 值];
逻辑：
        1.　　

while 循环条件 do
-- 循环操作;
-- 更新循环变量    
end while;

       

　　 2.      

repeat         
-- 循环操作
until 循环结束条件
end repeat;

 
        3.

语句标号: loop         
--循环操作
if 条件 then
leave 语句标号;
end if;    
end loop;

范例：计算1到100的和

/* 定义一个存储过程，实现1~100的累加求和 */
delimiter $$
drop procedure if exists sumNum $$
create procedure sumNum()
begin
    declare i int default 1;
    declare sumTotal int default 0;

    -- 使用while循环
    /*while i <= 100 do
        set sumTotal = sumTotal + i;
        set i = i + 1;
    end while;*/

    -- 使用repeat循环
    /*repeat
        set sumTotal = sumTotal + i;
        set i = i + 1;
        until i > 100
    end repeat;*/

    -- 使用loop循环
    loop_label:loop
        set sumTotal = sumTotal + i;
        set i = i + 1;
        if i > 100 then
            leave loop_label;
        end if;
    end loop;

    select sumTotal as 总和;
end$$
delimiter ;
call sumNum();

================================事务=========================
步骤：
    1.开启事务
        start transaction;
    2.提交事务
        commit;
    3.回滚事务
        rollback;

范例：转账        

-- 使用事务完成银行转账
drop table if exists accounts;
create table accounts(
    userName varchar(20) primary key comment '账户',
    balance double not null comment '余额'
)engine = InnoDB comment '账户表';

-- 添加数据
insert into accounts values ('张三',3000);
insert into accounts values ('李四',0);

select * from accounts;

-- 转账的存储过程
delimiter $$
drop procedure if exists transfer_account $$
create procedure transfer_account(srcName varchar(20),tarName varchar(20),money double)
begin
    -- 定义一个变量用来表示事务是否有错误
    declare temp_error int default 0;

    -- 定义出现异常的时候事务是有错误的,错误值1
    declare continue handler for sqlexception begin
        set temp_error = 1;
    end;

    -- 开启事务
    start transaction;

    update accounts set balance = balance - money where userName = srcName;
    -- 判断当前语句是否执行成功
    if row_count() < 1 then
        set temp_error = 1;
    end if;
 
    update accounts set balance = balance + money where userName = tarName;
    -- 判断当前语句是否执行成功
    if row_count() < 1 then
        -- 当受影响的行数大于0时错误值为1
        set temp_error = 1;
    end if;

    -- 判断事务是否成功
    -- 根据错误值决定事务的提交或回滚
    if temp_error > 0 then
        select '转账失败' as 结果,temp_error;
        rollback;
    else
        select '转账成功' as 结果,temp_error;
        commit;
    end if;

end $$
delimiter ;

select * from accounts;

call transfer_account('张三','李四',1000);

call transfer_account('张三','王五',1000);