mysql 数据库 day_07(完结)

存储的程序
========================================
  * 数据库中存储的程序，对数据进行运算处理
  
  * 存储过程
  * 函数
  * 触发器
  






会话变量
========================================
  * 一次会话过程中，可以设置一些变量保存数据
  
  * set @name='张三'
  
      * @表示回话变量
      * @@表示全局变量，指定是系统变量
            
            set @@sql_mode='STRICT_TRANS_TABLES';
            set @@autocommit=OFF
            set sql_mode='STRICT_TRANS_TABLES';
            set autocommit=OFF


  * 显示变量的值
      
      select @name, @a, @b, @c;
    




存储过程
==================================================
  * 创建存储过程
      
      delimiter // -- 设置语句的结束符号
      
      create procedure p1()
      begin
          select * from t;
      end;
      //
      
      delimiter ; -- 语句的结束符重新设置会成分号
  
  * 调用存储过程
      
      call p1();
  
  * 删除存储过程
      
      drop procedure [if exists] p1;
  
  * 参数
      
      存储过程的参数三种：
      in      输入参数（默认）
      out     输出参数
      inout   即可输入也可输出
      
      定义存储过程： p2(in a int, out b int)
                        ...
                        ...
                        set b = a*2;
      
      调用 p2()：
            
            call p2(255, @v1)
            select @v1;
  
  * 流程控制
      *)选择判断
          
          if 条件 then
              ...
          end if;
          -----------------
          if 条件 then
              ...
          else
              ...
          end if;
          ==========================
          case 
              when 条件 then ...;
              when 条件 then ...;
              when 条件 then ...;
              else ...;
          end case;
      
      *) 循环
          
          while 条件 do
              ...
          end while;
          ---------------
          loop
              ...
          end loop;  
          ---------------
          repeat
              ...
          until 条件 end repeat; 
          ---------------
          
          leave     跳出循环
          iterate   直接进入下次迭代
          
          ---------------
          
          循环命名
              lp: loop
                  leave lp;
                  iterate lp;
              end loop;


  * declare 
        定义局部变量
        
        *) declare a int;             -- null
        *) declare a int default 1;
  
        *) 局部变量在 end 结束时销毁
              
              begin
                  declare a int default 1;
                  begin
                        declare b int default 2;
                  end;
                  select a; -- ok
                  select b; -- 错误
              end;








函数
==================================================
  * 与存储过程类似，
  * 函数有返回值
  * 用 select f() 调用函数
  
  * 创建
      
      create function f(参数) returns int
      begin
          ....
          return 计算结果;
      end;
      
        
游标 cursor
==================================================
  * 游标： 查询结果的数据指针
  * 只能一步一步向下一行移动
  * 不能任意的被定位
  * 游标操作：
        
        *)定义游标：
          declare c cursor for select ...
        
        *)打开游标：
          open c;
        
        *)从游标取一行数据（自动下移）：
          -- 从游标取出几个字段的数据，存入几个变量
          fetch c into v1,v2,v3,v4;
        
        *)关闭游标：
          close c;


  * 从头到尾用游标访问数据
  
        mysql 的错误处理机制：
            declare continue handler for ...
            
            当出现指定错误(not found)时，执行一段代码(done=)，
            使程序可以继续正常执行(continue)
        
        declare done int default 0;
        declare c cursor for select ...
        declare continue handler for not found set done=1;
        
        open c;
        while done=0 do
            -- 在末尾取不到数据，会出现 not found 错误
            -- 触发 done=1 执行
            fetch c into ... 
        end while;






触发器
=============================================
  * 对一行数据进行增删改操作，
    可以触发一端代码执行
    
  * 六种： 一张表最多就能创建6个触发器
        before insert
        before update
        before delete
        
        after insert
        after update
        after delete
  
  * 创建触发器
        
        create trigger 名称 before insert 
        on tb1 for each row
        begin
            ....
        end;
  
  * 两个隐含对象
        
        new  - 新的数据行
        old  - 旧的数据行
        
        new.id,  new.name,  new.price
        old.id,  old.name,  old.price
        
        *)插入操作
              new  - 新插入的一行数据
              old  - 没有
        *)修改操作
              new  - 修改后的新数据
              old  - 修改前的旧数据
        *)删除操作
              new  - 没有
              old  - 被删掉的数据
















  
  






练习
===================================================
  1. 新建库新建表
        create database db1 charset=utf8;
        
        use db1;
        
        create table t(
            c int
        );
  
  2. 创建存储过程
        delimiter //                -- 修改语句结束符
  
        drop procedure if exists p1; -- 删除存储过程
        //
        create procedure p1()       -- 定义存储过程 p1
        begin
            insert into t values(floor(rand()*10));
            select * from t;
        end;
        //        
        call p1();//                -- 调用存储过程
        //


  
  3. 测试输入参数和输出参数
        
        drop procedure if exists p2;
        //
        create procedure p2(in a int, out b int)
        begin
            set b = a*2; -- 设置输出变量 b 的值
        end;
        //
        call p2(1, @a); -- a变量在p2()存储过程中被赋值
        call p2(2, @b);
        call p2(3, @c);
        select @a, @b, @c;
        //


  4. 测试选择判断
  
      drop procedure p3 if exists;
      //
      create procedure p3(in a int)
      begin
          if a=1 then  -- 如果a是1
              select 10;
          end if;
          --
          case 
              when a=2 then select 20;   -- 如果a是2
              when a=3 then select 30;   -- 如果a是3
              when a=4 then select 40;   -- 如果a是4
              else select 100;           -- 如果a是234之外的其他值
          end case;          
      end;
      //
      call p3(1);
      call p3(2);
      call p3(3);
      call p3(4);
      call p3(5);
      //
      
  5. 循环测试，循环插入 1,2,3,4,5
        
        truncate table t; -- 删除再重建表
        //
        drop procedure if exists p4;
        //
        create procedure p4(in a int)
        begin
            -- 定义变量1.局部变量 2.会话变量
            declare i int default 1; -- 局部变量 i
            
            --
            while i<=a do -- 当<=a时重复执行
                insert into t values(i);
                set i = i+1;
            end while;
            --
            set i=1;
            lp: loop
                insert into t values(i);
                set i = i+1;
                if i>a then  -- 如果超过a离开循环
                    leave lp;
                end if;
            end loop;
            --
            set i=1;
            repeat
                insert into t values(i);
                set i = i+1;
            until i>a end repeat;  -- 直到超过a结束循环
        end;
        //
        
        call p4(5);
        //
        select * from t;
        //


  6. 测试函数，求 a 的 b 次方
        
        drop function if exists func_pow;
        //
        create function func_pow(a bigint, b bigint) returns bigint
        begin
            declare r bigint;             -- 用来保存结果
            declare i bigint default 1;   -- 用来控制循环次数
            set r = a;
            
            while i<b do                  -- 重复b-1次
                set r = r*a;              -- 重复地乘a
                set i=i+1;                -- 次数递增
            end while;
            
            return r;                     -- 返回计算结果
        end;
        //
        select func_pow(2,3);
        select func_pow(3,2);
        select func_pow(2,8);
        //
        
        select c, func_pow(c, 2) from t;
        //


  7. 产生随机字符串
          
          可以产生大量随机字符串填入表中，
          对查询效率进行测试
          
       drop function if exists rand_str;
       //
       -- 产生的随机字符串长度范围 [a, b)
       create function rand_str(a int, b int) returns varchar(255)
       begin
            -- 从这些字符当中随机挑选几个
            declare s0 varchar(600) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两体制机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该铁价严龙飞';
            declare len int;                    -- 保存随机长度值
            declare s varchar(255) default '';  -- 保存随机字符串结果
            declare i int default 0;            -- 用来控制次数
            declare j int;                      -- 随机位置
            -- 随机长度，范围 [a, b)
            set len = a+floor(rand()*(b-a));
            -- 产生 len 个随机字符连接到 s
            while i<len do
                -- 随机位置 [1, char_length(s0))
                set j = 1+floor(rand()*(char_length(s0)-1));
                -- 从s0取i位置字符，连接到 s
                set s = concat(s, substring(s0, j, 1));
                set i = i+1; -- i递增
            end while;
            
            return s; -- 返回随机字符串结果
       end;
       //
       select rand_str(3,8);
       select rand_str(3,8);
       select rand_str(3,8);
       select rand_str(3,8);
       select rand_str(3,8);
       //
       
       
          
                
        
      
              1.具体长度 len
                    [3,8)
                    3+[0,5)
                    3+floor(rand()*5)
                    len = a+floor(rand()*(b-a))
                    
                    0                   0
                    0.1          *5     0.5
                    0.2                 1
                    0.3                 1.5
                    0.8                 4
                    0.9999              4.xxxx
                    rand()
              
              2. 需要一个空串 s=''
              
              3. 循环 len 次
                    4. 产生随机位置 j
                       1+ [0, char_length(s0)-1)
                       j = 1+floor(rand()*(char_length(s0)-1))
                    5. 截取 s0 的 i 位置字符
                       substring(s0, j, 1)
                    6. 这个字符连到 s 上
                        s = concat(s, substring(s0, j, 1))


  8. 在表中插入大量随机字符串
        
        1.创建一个内存表
              engin=innodb
                    myisam
                    memory 内存表
        
        2.循环向内存表插入 10 万数据
        3.将内存的10万数据直接插入磁盘表
        
        -- 内存表
        drop table if exists tm;
        create table tm(
          c varchar(255)
        )engine=memory;
        //
        
        -- 磁盘表
        drop table if exists td;
        create table td(
          id int primary key auto_increment,
          c varchar(255)
        )engine=innodb;
        //
        
        -- 存储过程
        drop procedure if exists rand_data;
        //
        create procedure rand_data(n int)
        begin
            declare i int default 0; -- 用来控制循环多少次
            while i<n  do  -- 循环 n 次
                -- 向内存插入随机字符串
                insert into tm values(rand_str(3,6));
                set i=i+1;
            end while;
            -- 内存数据全部插入磁盘表
            insert into td(c) select * from tm;
            delete from tm; -- 清空内存表
        end;
        //
        call rand_data(9);
        //
        call rand_data(20000);//


  * 查询 td 表的 c 字段
        
        select * from td where id=2918757
        //        
        select * from td where c='值w农计调';
        //        
        select * from td where c like '值w%';
        //        
        select * from td where c like '%计调';
        //
        select * from td where c like '王__';
        //        
  
  * 对 c 字段创建索引，再查询
    或删除索引，在查询
        
        create index td_c_index on td(c);
        //
        
        explain select * from td where c like '值w%';
        //        
        explain select * from td where c like '%计调';
        //
        
        -- 删除索引
        alter table td drop index td_c_index;
        //
        


  * 游标测试
      
      drop procedure if exists p5;
      //
      create procedure p5() 
      begin
          declare sum int default 0; -- 累加变量
          declare a int; -- 保存每行的值
          declare done int default 0; -- 结束标志
          declare c cursor for select * from t; -- 游标
          -- 错误处理，找不到数据值将标志值设置成1
          declare continue handler for not found set done=1;
          open c; -- 打开游标
          while done=0 do -- 当结束标志是0，没结束
              fetch c into a;  -- 抓取下一行数据存到变量a
              set sum = sum + a;
          end while;
          close c;
          select sum;
      end;
      //
      call p5();
      //


  
  * 商品分类表(tb_item_cat)
    修改数据时，不必手动修改 updated 字段，
    用触发器自动填入当前时间
        
        update tb_item_cat set name='新类别'
        where id=16;


        update tb_item_cat set name='新类别',updated=now()
        where id=16;
        
        use jtds//
        
        create trigger cat_updated_trigger
        before update on tb_item_cat
        for each row
        begin
            set new.updated=now();-- 新数据的更新时间字段填入系统时间
        end;
        //        
        select id,name,updated from tb_item_cat
        where id in(3,4,5,6,7);
        //
        update tb_item_cat 
        set name=concat('>>>', name)
        where id in(3,4,5,6,7);
        //
        
  
  * 阻止删除商品表数据
      
      *)当 before delete 时，手动产生一个错误，
        使删除操作失败
        
      create trigger item_del_trigger 
      before delete on tb_item
      for each row
      begin
          -- 手动暴力产生错误
          delete from 不允许商品表的删除操作;
      end;
      //
      delete from tb_item where id=10000028;
      //








作业
=======================================================
  用触发器1实现：
    tb_item 商品表的商品数量，不允许减为负数
    如果是负数，暴力产生错误
  
  用触发2器实现：
    向订单详情表(tb_order_item)插入数据时，
    商品表商品数量减少