mysql数据库的基本操作(事务、变量、触发器、函数、存储过程)

录:
1.事务
2.事务原理
3.事务--回滚点    
4.自动事务
5.事务特性ACID
6.变量
7.触发器
8.创建触发器
9.查看触发器
10.使用触发器
11.触发器记录
12.if分支
13.while循环
14.函数
15.自定义函数
16.查看函数
17.删除函数
18.函数的参数
19.函数--作用域    
20.存储过程    
21.存储过程--参数
    
1.事务transaction    <--返回目录
    * 需求:有一张银行账户表,有A用户给B用户转账:A账户先减少,B账户增加,但是A操作完
        之后断电了。
    * 解决方案:A减少钱,但是不要立即修改数据表,B收到钱之后,同时修改数据表。
    
    * 事务安全
        - 事务:一系列连续的操作。这些操作要么全部成功,要么全部失败。
        - 事务安全:一种保护连续操作同时满足的机制
        - 事务安全的意义:保证数据操作的完整性
        
    * 注意:引擎myisam不支持事务,也不支外键;innodb支持事务,也支持外键。
        -- 创建表时指定引擎
        drop table if exists tb_account;
        create table tb_account(
            number varchar(20) not null unique comment ‘账户‘,
            name varchar(20) not null comment ‘户主名‘,
            money decimal(10,2) default 0.0 comment ‘账户余额‘
        )charset utf8 engine innodb;    
        insert into tb_account values(null,‘0001‘,‘张三‘,1000.00),(null,‘0002‘,‘李四‘,1000.00);    
    * 手动开启事务:start transaction;        
        update tb_account set money = money -100 where id = 1;
        update tb_account set money = money +100 where id = 2;
        
    * 提交事务 commit;
        
    * 回滚事务 rollback;    
        
2.事务原理    <--返回目录
    * 事务操作原理:事务开启之后,所有的操作都会临时保存到事务日志,事务日志只有在得到commit命令
        才会同步到数据表,其他任何情况都会清空(rollback,断电,断开连接)
        
3.事务--回滚点        <--返回目录
    * 设置回滚点:savepoint 回滚点名;
    * 回到回滚点:rollback to 回滚点名;
        
4.自动事务    <--返回目录
    * 在mysql中,默认的都是自动事务处理,用户操作完会立即同步到数据表中
    * 自动事务,系统通过autocommit变量控制
        show variables like ‘autocommit‘;      select @@autocommit;
    * 关闭自动事务处理 set autocommit = off/0;      
    * 自动事务处理关闭后需要手动提交或回滚
    * 通常,我们都会使用自动事务,  开启 set autocommit = on/1;
        
5.事务特性ACID    <--返回目录
    * atomic[??t?m?k]:原子性,事务的整个操作是一个整体,不可分割,要么全部成功,要么全部失败
    * consistency[k?n?s?st?nsi]:一致性,事务操作的前后,数据表的数据没有变化
    * isolation:隔离性,事务操作时相互隔离,不受影响    
    * durability:持久性,数据一旦提交,不可改变,永久的改变数据表数据

    * 锁机制:innodb默认是行锁,但是如果在事务操作过程中,没有使用到索引,那么系统会自动全表检索数据,
        自动升级为表锁
        
6.变量    <--返回目录
    * 系统变量:系统定义好的变量,用来控制服务器的表现的,如autocommit等
    * show variables; -- 查看所有变量     variable[?veri?bl]
    * 查看mysql的版本:select @@version;    show  variables like ‘version‘;    
    * 修改系统变量:会话级别和全局级别
        会话级别 set [@@]变量名 = 修改的值;比如:set autocommit = off/0;
        全局级别 set global 变量名 = 值;
    
    * 自定义变量  set @自定义变量=值;
        select @自定义变量;    
    
    * 在mysql中,"="会默认的当成比较符号处理,mysql重写定义了赋值":="
        - set @age := 20;
    
    * mysql允许从数据表中获取数据,然后赋值给变量
        方法1:边赋值,边查看结果
            select @变量名 := 字段名 from 表名; -- 最后一个字段值赋值给变量
        方法2:只有赋值,不看结果;数据记录最多只允许获取一条,mysql不支持数组
            select 字段列表 from 表名 where id = 1 into @变量名1,@变量名2; -- 通过where条件限定只获取一条记录
        
    * 所有自定义变量都是会话级别:当前客户端当次连接有效。
        - 变量与当前用户关联,与数据库无关
    
7.触发器    <--返回目录
    * 需求:有两张表,一张订单表,一张商品表,每生成一个订单,意味着商品的库存要减少
    * 触发器:trigger[?tr?g?(r)] ,事先为某张表绑定一段代码,当表中的某些内容发生改变的时候(增删改)
        系统自动触发代码执行
        
    * 触发器:事件类型,触发时间,触发对象
        事件类型:增删改 insert delete update
        触发时间:前后,before after
        触发对象:表中的每一条记录
        
    * 一张表中只能拥有一种触发时间的一种类型的触发器:一张表最多有6个触发器(增删改*前后)
    
8.创建触发器    <--返回目录
    * 在mysql高级结构中,没有大括号,都是用对应的字符符号代替
    * 临时修改语句结束符 delimiter 自定义符号
    * 将临时修改修正过来 delimiter ;
    * 触发器基本语法:
        create trigger 触发器名 触发时间 触发类型 on 表名 for each row
        begin    -- 代表(
        
        end      -- 代码)
    
    * 例子:
    -- 创建表 tb_goods
    create table tb_goods(
        id int primary key auto_increment,
        name varchar(20) not null,
        price decimal(10,2) default 1,
        inv int comment ‘库存数量‘
    )charset utf8 engine innodb;
    insert into tb_goods values(null,‘iphone6s‘,5288,100),(null,‘iphone8‘,6288,100),(null,‘iphone10‘,7288,100);
    
    -- 创建表 tb_order
    create table tb_order(
        id int primary key auto_increment,
        good_id int not null comment ‘商品id‘,
        g_number int comment ‘商品数量‘
    )charset utf8 engine innodb;
    =======================创建触发器 start ===========================
    -- 临时修改语句结束符
    delimiter $$
    
    -- 触发器,订单生成一个,商品库存减少
    create trigger after_order after insert on tb_order for each row
    begin
        update tb_goods set inv = inv -1 where id = 2;
        -- select count(*) from tb_order;  -- 出错,不允许返回一个结果
    end
    $$  -- 结束符号
    
    -- 将临时修改修正过来
    delimiter ;
    =======================创建触发器 end ===========================
    * 注意:delimiter前面不能有空格等空白。所有复制以上代码到sqlyog时去掉delimiter前面的空白
    
9.查看触发器    <--返回目录
    show triggers [like];
    show create trigger 触发器名;

10.使用触发器    <--返回目录
    * 不需要手动调用,而是当某种情况发生时自动触发(上面的例子:当订单表插入记录自动触发)

    * 触发器删除
        触发器不能修改,只能先删除后更新
        drop trigger 触发器名;

11.触发器记录    <--返回目录
    * 触发器记录:不管触发器是否触发了,只要当某种操作准备执行,系统就会将当前要操作的
        记录的当前状态和即将执行之后新的状态分别保留下来,供触发器使用;其中要操作的当前状态
        保存到old中,操作之后的可能形态保存到new
    * old代表的是旧记录,new代表的是新记录
        - 删除的时候是没有new的,插入的时候是没有old
        - 使用方式:old.字段名/new.字段名

    * update tb_goods set inv = inv - new.g_number where id = new.good_id;

12.if分支    <--返回目录
    * 需求:判断商品库存是否足够;如果订单的数量大于库存,insert失败
    delimiter %%
    create trigger before_order before insert on tb_order for each row
    begin
        -- 判断商品库存是否足够
        -- 获取商品库存:商品库存在表中
        select inv from tb_goods where id = new.good_id into @inv;
        -- 比较库存
        if @inv < new.g_number then
            -- 库存不够:触发器没有提供能够阻止事件发生的能力,只能暴力终止
            insert into XXX values(XXX);
        end if
    end
    %%
    delimiter ;
    
13.while循环    <--返回目录
    * 语法
        循环名:while 条件判断 do
            -- 执行代码
            -- 变更循环条件
            -- 循环控制leave/iterate 循环名;
        end while;
    
    * 循环控制
        mysql中没有对应的continue和break;
        iterate:迭代,类似continue,后面的代码不执行,循环重新
        leave:类似break
    
14.函数    <--返回目录
    * 函数:将一段代码块封装到一个结构中,在需要执行代码块的时候,调用结构执行即可(代码复用)
    * 函数分为:系统函数、自定义函数
    
    * 系统函数:系统定义好的函数,直接调用即可。任何函数都有返回值,因此函数的调用是通过select调用
    
    * 字符串相关
        - substring函数:
            set @cn = ‘世界你好‘;
            select substring(@cn,1,2);  -- 结果是输出‘世界‘
            -- 字符串下标从1开始;substring函数操作单位是字符
        - char_length:字符长度
        - length:字节长度
            - select char_lenght(@cn),length(@cn);
        - instr:判断字符串是否在某个具体的字符串存在,存在返回位置,不存在返回0
            select instr(@cn,‘你好‘); -- 3
        - lpad(str,len,str2):左填充,将字符串str2填充到str左边,填充后总共len位字符
            select lpad(@cn,8,‘欢迎‘);  -- 欢迎欢迎世界你好
            select lpad(@cn,7,‘欢迎‘); -- 欢迎欢世界你好
        - insert:替换
            select insert(@cn,3,1,‘您‘);-- 从下标3开始,长度1,替换成‘您‘;结果:世界您好
        - strcmp:字符串比较 相等返回0,小返回-1,大返回1;看校对集,默认是不区分大小写
            select strcmp(@str1,@str2);
        - upper(字符串):将字符串全部变成大写
        - lower(字符串):将字符串全部变成小写
        
    * 日期时间相关:
        select now(); -- 2018-10-07 01:30:54
        select curdate(); -- 2018-10-07
        select curtime(); -- 01:29:10
        select month(curdate()); -- 返回当前日期的月份,1~12
        select curdate(),curtime(),month(birthday字段) from tb_stu;
        
    * 数学函数
        set @x=9;
        abs(@x) 求绝对值
        sqrt(@x)求平方根
        mod(@x,@y)取模,求余

    * 加密函数
        password(str)
        md5(str)
        encode(需要加密的字符串, 密钥字符串):加密,结果是二进制,需要blob类型存储
        decode(需要加密的字符串, 密钥字符串):解密
        
15.自定义函数    <--返回目录
    * 创建函数
        DELIMITER $$
        create function 函数名([形参列表]) returns 数据类型
        begin
            -- 函数体
            -- 返回值 return 类型;
        end
        $$
        DELIMITER ;
    
    * 例子:
        create function display() returns int
            return 100;
            
        - 自定义函数的调用    select display();
        
    * 例子:
        delimiter $$
        create function fun_book (bookId int) returns varchar(20)
        begin
            return (select bookName from tb_book where bookTypeId=bookId);
        end
        $$
        delimiter ;
        
        -- 调用存储过程
        select fun_book(1);
        
16.查看函数    <--返回目录
    * show function status[like ‘模糊匹配‘] [\G];
    * 函数属于指定数据库,说明要在对应数据库下才可以调用。
    * 查看函数的创建语句
        show create function 函数名;
    
17.删除函数    <--返回目录
    * drop function 函数名;
    
18.函数的参数    <--返回目录
    * 函数定义时的参数是形参,调用时的参数是实参
    * 需求:计算1-指定数之间的和
        delimiter $$
        create function display1(int_1 int) returns int
        begin
            set @i = 1;  -- 定义回话级别的全局变量
            set @res = 0;   -- 定义回话级别的全局变量
            while @i <= int_i do
                set @res = @res + @i;
                set @i = @i +1;
            end while;
            return @res;
        end
        $$
        delimiter ;
        -- 函数调用
        select display(10);  select @res,@i;
        
    * 在函数内部@定义的变量在函数外部也可以访问

19.函数--作用域    <--返回目录
    * mysql中的作用域与js中的作用域完全一样
        - 全局变量可以在任何地方使用;局部变量只能在函数内部使用;
    * 全局变量:set @变量名;定义的为全局变量
    * 局部变量:使用declare [d??kle?(r)]声明,并且必须在函数体开始之前定义;
    
        delimiter $$
        create function display2(int_1 int) returns int
        begin
            declare i int default 1;    -- 定义局部变量i
            declare res int default 0;  -- 定义局部变量res
            while i <= int_i do
                set res = res + i;
                set i = i +1;
            end while;
            return res;
        end
        $$
        delimiter ;
    
20.存储过程    <--返回目录
    * 存储过程简称过程procedure,是一种用来处理数据的方式。
        存储过程是一种没有返回值的函数
        
    * 创建过程
        create procedure 过程名字([形参列表])
        begin
            过程体;
        end;
        
    * 查看过程与查看函数方法一样
    
    * 调用过程  call 过程名();
    
    * 删除过程 drop procedure 过程名;
    
    * 例子:
        delimiter $$
        create procedure pro_book (in bt int, out count_num int)
        reads sql data
        begin
            select count(*) from tb_book where bookTypeId=bt;
        end
        $$
        delimiter ;
        
        -- 调用存储过程
        call pro_book(1,@total);
    
21.存储过程--参数    <--返回目录
    * 函数的参数需要指定数据类型,过程比函数更严格
    * 过程有自己的类型限定:三种类型
        in:数据只是从外部传入到内部使用(值传递),可以是数值也可以是变量
        out:
        inout:
    MySQL的参数模式可以被定义为IN、OUT和INOUT。
  IN:这是缺省模式,它说明参数可以被传入存储程序内部,但是任何对于该参数的修改都不会被返回给调用它的程序。
  OUT:这个模式意味着存储程序可以对参数复制(修改参数的值),并且这个被修改的值会被返回给它的调用程序。
  INOUT:这个模式意味着程序既可以读取传入的参数,而且任何对于该参数的修改对于它的调用程序而言都是可见的。   
---

mysql数据库的基本操作(事务、变量、触发器、函数、存储过程)

上一篇:WPF 2D 碰撞检测


下一篇:ORACLE 11.2 性能调优之01(安装后梳理)