数据库（三）

-- 系统变量 2 show global variables; 3 4 -- 查看当前系统的编码 5 show global variables like ‘%char%‘; 6 7 -- 查看当前会话(连接)的编码 8 show session variables like ‘%char%‘; 9 10 -- 获取当前的数据库状态 11 show status; 12 show session status; 13 show session status like ‘%show%‘; 14 15 -- 使用具体的系统变量 16 select @@global.auto_increment_increment; 17 18 -- 使用用户自定义变量 19 -- 使用set直接赋值 20 set @test = 1; 21 -- 使用select直接赋值 22 select @test; 23 24 -- 从数据库中查询 25 use schooldb; 26 select @num := count(*) from student; 27 select @num; 28 29 select * from student; 30 -- 查询学员姓名并赋值给变量 31 select @stuName := Name from student; 32 select @stuName; 33 select * from student; 34 35 select @stuName; 36 37 select @num; 38 39 select @test; 40 41 -- 使用@符号自定义的变量属于session级别，不会影响其他连接

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

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

语法：    
    1.定义存储过程

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

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

 1 use myschool;
 2 /*创建一个存储过程，用来实现一个简单的加法运算*/
 3 /* 重新定义分隔符 */
 4 delimiter $$
 5 
 6 -- 如果存储过程存在，就先删除
 7 drop procedure if exists myAdd $$
 8 
 9 /* 创建存储过程 */
10 create procedure myAdd(num1 int ,num2 int)
11 begin
12     select num1 + num2 as 和;
13 end$$
14 /* 还原分隔符 */
15 -- 【中间一定要空格】
16 -- ★★【注意：存储过程中的注释要单独成为一行】
17 delimiter ;
18 /* 调用存储过程 */
19 call myAdd(14,15);

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

 1 /* 由于数学考试题目太难，所以决定修改成绩信息
 2 1.考试的及格人数小于一半，每个不及格的人加5分
 3 2.及格人数超过一半，就不加分
 4 3.最后显示加分前的及格率，以及加分后的及格率
 5 */
 6 use schooldb;
 7 select * from `subject`;
 8 select * from student;
 9 select * from score;
10 
11 delimiter $$
12 drop procedure if exists changeScore $$
13 create procedure changeScore()
14 begin
15     /*定义局部变量，用来保存及格的人数和总人数*/
16     declare passNum int default 0;
17     declare total int default 0;
18     declare passNum1 int default 0;
19 
20     /* 从学生表中查询出总人数 */
21     select count(*) into total from student where GradeID = 1;
22 
23     select count(*) into passNum from score
24         inner join `subject` on score.SubjectID = `subject`.SubjectID
25         where Score >= 60 and `subject`.GradeID = 1;
26     
27     /* 判断及格的人数小于一半 */
28     if (passNum < total / 2) then
29         -- 加分
30         update score set Score = Score + 5 where Score < 60
31         and
32         SubjectID in (select SubjectID from `subject` where GradeID = 1);
33     elseif passNum > 1 then
34         select * from student;
35     end if;
36 
37     /* 查询加分后的及格人数 */
38     select count(*) into passNum1 from score
39         inner join `subject` on score.SubjectID = `subject`.SubjectID
40         where score >= 60 and `subject`.GradeID = 1;
41 
42     select passNum / total as 加分前,
43            passNum as 加分前及格人数,
44            passNum1 / total as 加分后,
45            passNum1 as 加分后及格人数;
46 end$$
47 delimiter ;
48 /* 调用存储过程 */
49 call changeScore();

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

1 while 循环条件 do
2 -- 循环操作;
3 -- 更新循环变量    
4 end while;

       

　　 2.      

1 repeat         
2 -- 循环操作
3 until 循环结束条件
4 end repeat;

 
        3.

1 语句标号: loop         
2 --循环操作
3 if 条件 then
4 leave 语句标号;
5 end if;    
6 end loop;

范例：计算1到100的和

 1 /* 定义一个存储过程，实现1~100的累加求和 */
 2 delimiter $$
 3 drop procedure if exists sumNum $$
 4 create procedure sumNum()
 5 begin
 6     declare i int default 1;
 7     declare sumTotal int default 0;
 8 
 9     -- 使用while循环
10     /*while i <= 100 do
11         set sumTotal = sumTotal + i;
12         set i = i + 1;
13     end while;*/
14 
15     -- 使用repeat循环
16     /*repeat
17         set sumTotal = sumTotal + i;
18         set i = i + 1;
19         until i > 100
20     end repeat;*/
21 
22     -- 使用loop循环
23     loop_label:loop
24         set sumTotal = sumTotal + i;
25         set i = i + 1;
26         if i > 100 then
27             leave loop_label;
28         end if;
29     end loop;
30 
31     select sumTotal as 总和;
32 end$$
33 delimiter ;
34 call sumNum();

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

范例：转账        

 1 -- 使用事务完成银行转账
 2 drop table if exists accounts;
 3 create table accounts(
 4     userName varchar(20) primary key comment ‘账户‘,
 5     balance double not null comment ‘余额‘
 6 )engine = InnoDB comment ‘账户表‘;
 7 
 8 -- 添加数据
 9 insert into accounts values (‘张三‘,3000);
10 insert into accounts values (‘李四‘,0);
11 
12 select * from accounts;
13 
14 -- 转账的存储过程
15 delimiter $$
16 drop procedure if exists transfer_account $$
17 create procedure transfer_account(srcName varchar(20),tarName varchar(20),money double)
18 begin
19     -- 定义一个变量用来表示事务是否有错误
20     declare temp_error int default 0;
21 
22     -- 定义出现异常的时候事务是有错误的,错误值1
23     declare continue handler for sqlexception begin
24         set temp_error = 1;
25     end;
26 
27     -- 开启事务
28     start transaction;
29 
30     update accounts set balance = balance - money where userName = srcName;
31     -- 判断当前语句是否执行成功
32     if row_count() < 1 then
33         set temp_error = 1;
34     end if;
35  
36     update accounts set balance = balance + money where userName = tarName;
37     -- 判断当前语句是否执行成功
38     if row_count() < 1 then
39         -- 当受影响的行数大于0时错误值为1
40         set temp_error = 1;
41     end if;
42 
43     -- 判断事务是否成功
44     -- 根据错误值决定事务的提交或回滚
45     if temp_error > 0 then
46         select ‘转账失败‘ as 结果,temp_error;
47         rollback;
48     else
49         select ‘转账成功‘ as 结果,temp_error;
50         commit;
51     end if;
52 
53 end $$
54 delimiter ;
55 
56 select * from accounts;
57 
58 call transfer_account(‘张三‘,‘李四‘,1000);
59 
60 call transfer_account(‘张三‘,‘王五‘,1000);

 

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
   
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
   
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
   

数据库（三）

标签：