mysql数据库进阶

1.视图

  视图是一个虚拟表,本身并不存储数据,当sql在操作视图时所有数据都是从其他表中查出来的,因此其本质是:根据SQL语句获取动态的数据集,并为其命名,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。

视图操作:

mysql数据库进阶
mysql数据库进阶
-- CREATE TABLE students(
--         nid INT NOT NULL AUTO_INCREMENT,
--         name VARCHAR(128) NOT NULL,
--         gender CHAR(8) NOT NULL,
--         age INT NOT NULL,
--         class VARCHAR(64) NOT NULL,
--         PRIMARY KEY (nid)
-- )DEFAULT CHARSET=utf8;

-- INSERT INTO students(name, gender,age,class) VALUES(李木,,24,python班);

-- INSERT INTO students(name, gender,age,class) 
-- VALUES(王华,,26,python班),
-- (王钰,,22,前端);



-- 创建并使用视图view1
-- SELECT * FROM (SELECT nid,name FROM students WHERE nid>1) AS view1 WHERE view1.name="王华";

-- 创建视图格式: CREATE VIEW 视图名称 AS sql语句;
-- CREATE VIEW view1 AS SELECT nid, name FROM students WHERE nid>1;

-- 修改视图格式:ALTER VIEW 视图名称 AS sql语句;
-- ALTER VIEW view1 AS SELECT nid,name FROM students WHERE name="李木";

-- 使用视图:使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。
-- SELECT * FROM view1;

-- 删除视图格式:DROP VIEW 视图名称;
-- DROP VIEW view1;
mysql数据库进阶

 2.触发器

   对某个表行进行增删改操作,如果希望操作前后触发某些特定的操作,便可以使用触发器。触发器就像信号或中间件类似,可以定制特定的操作,注意的是行操作触发。

触发器操作:

mysql数据库进阶
mysql数据库进阶
-- 创建触发器语法
 
每一行插入前触发
CREATE TRIGGER tri_before_insert_table BEFORE INSERT ON students FOR EACH ROW;
BEGIN

END

插入后触发
CREATE TRIGGER tri_after_insert_table AFTER INSERT ON students FOR EACH ROW;
BEGIN

END


删除前触发
CREATE TRIGGER tri_before_delete_table BEFORE DELETE ON students FOR EACH ROW;
BEGIN

END

删除后触发
CREATE TRIGGER tri_after_delete_table AFTER DELETE ON students FOR EACH ROW;
BEGIN

END

更新前触发
CREATE TRIGGER tri_before_update_table BEFORE UPDATE ON students FOR EACH ROW;
BEGIN

ON
更新后触发
CREATE TRIGGER tri_after_update_table AFTER UPDATE ON students FOR EACH ROW;
BEGIN
mysql数据库进阶

触发器使用示例

mysql数据库进阶
mysql数据库进阶
CREATE TABLE class(
    nid INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(64) NOT NULL,
    count INT NOT NULL,
    PRIMARY KEY (nid)
) DEFAULT CHARSET = utf8

INSERT INTO class(name,count) VALUES("python班",45),("算法班",50);

创建插入前触发器
NEW表示即将新插入的行
delimiter //
CREATE TRIGGER tri_before_insert BEFORE INSERT ON students FOR EACH ROW
BEGIN
    IF
        NEW.class = "前端" THEN
            INSERT INTO class ( name,count )
        VALUES
            ( "前端",1 ) ;
    END IF;
END// 
delimiter;

创建插入后触发器
delimiter //
CREATE TRIGGER tri_after_insert AFTER INSERT ON students FOR EACH ROW 
BEGIN
    IF NEW.class="python" THEN
        INSERT INTO class(name,count) VALUES("python",1);
    ELSEIF NEW.class = "数据结构" THEN
        INSERT INTO class(name,count) VALUES("数据结构",2);
    END IF;
END//
delimiter;

使用触发器(插入一行数据时触发)
insert into students(name,gender,age,class)values(李曼,"",25,"python");
insert into students(name,gender,age,class)values(王军,"",25,"数据结构");

删除触发器
DROP TRIGGER tri_before_insert;
mysql数据库进阶

(触发器中,NEW表示即将插入的数据行,OLD表示即将删除的数据行。)

触发器性能:https://segmentfault.com/q/1010000004907411

      https://www.cnblogs.com/geaozhang/p/6819648.html

3. 存储过程

  存储过程是一组SQL语句集合,经过编译创建并保存在数据库中,用户可以通过指定存储过程的名字并给定参数(若需要时)来调用。当调用存储过程时,其内部的SQL语句会按逻辑顺序执行。

创建存储过程:

mysql数据库进阶
mysql数据库进阶
--创建存储过程
delimiter // 
CREATE PROCEDURE p1()
BEGIN
    SELECT * FROM    students WHERE class = "前端";
END // 
delimiter;

--调用存储过程
call p1()
mysql数据库进阶

存储过程还可以接受参数,包括三类参数:

  • in          仅用于传入参数用
  • out        仅用于返回值用
  • inout     既可以传入又可以当作返回值
mysql数据库进阶
mysql数据库进阶
-- 传入和传出参数

delimiter //
CREATE PROCEDURE p2(
    IN i1 INT,
    IN i2 INT,
    INOUT i3 INT,
    OUT i4 INT
)
BEGIN
DECLARE temp1 INT;
DECLARE temp2 INT DEFAULT 0;
SET temp1=1;
SET i4 =i1+i2+temp1+temp2;
set i3 = i3+100;
END//
delimiter;

-- 执行存储过程p2()
SET @t1=4;
SET @t2=5;
CALL p2(1,2,@t1,@t2);
SELECT @t1,@t2;

-- 输出结果
-- @t1,@t2
-- 104, 4
mysql数据库进阶

通过存储过程的返回值,可以用来判断其执行状态,如下:

mysql数据库进阶
mysql数据库进阶
-- 支持事务(通过返回值能判断执行的状态)
delimiter//
CREATE PROCEDURE p3(
    OUT p_return_code TINYINT
)
BEGIN
-- 出现错误
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    set p_return_code=1;
    ROLLBACK;
    END;
-- 出现警告
DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
    set p_return_code=2;
    ROLLBACK;
    END;
START TRANSACTION;
    DELETE FROM class;
    INSERT INTO students(name,gender,age,class) VALUES("宋红云","",23,"python");
COMMIT;
-- 成功执行
SET p_return_code = 0;
END//
delimiter;

SET @r1 = -1;
CALL p3(@r1);
SELECT @r1;
-- 输出结果 @r1=0
mysql数据库进阶

存储过程使用游标

mysql数据库进阶
mysql数据库进阶
-- 使用游标
delimiter//
CREATE PROCEDURE p4()
BEGIN
DECLARE sage INT;
DECLARE sname VARCHAR(128);
DECLARE done INT DEFAULT FALSE;

DECLARE my_cursor CURSOR FOR SELECT name,age FROM students;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;

OPEN my_cursor;
    myloop: LOOP
     FETCH my_cursor INTO sname,sage;
         IF done THEN
             LEAVE myloop;
       END IF; 
         INSERT INTO class(cname) values(sname);
  END LOOP myloop;
CLOSE my_cursor;
END//
delimiter;

CALL p4();
mysql数据库进阶

利用prepare,execute,dellocate, 可以实现动态执行SQL语句,同时对SQL语句进行检查,防治SQL注入(?为查询条件中占位符)

mysql数据库进阶
mysql数据库进阶
delimiter//
CREATE PROCEDURE p5(
    IN i INT
)
BEGIN
    SET @i2 = i;
    PREPARE prod FROM "select * from students where nid=? ";
    EXECUTE prod USING @i2;  
-- 变量只能是SET @i2 = value的变量,传入和declare的变量不行
    DEALLOCATE PREPARE prod;

END//
delimiter;

CALL p5(2);

-- sql注入:
-- sql = "select * from students where name="+i+";"
-- 如果采用上面字符窜拼接sql语句时,当用户输入的i="ss or 1=1",则where语句肯定为TRUE,会拿到所有的name数据
mysql数据库进阶

上述语句中注意EXECUTE prod USING @i1,@i2;可以传入多个查询变量,和占位符按顺序对应,而且变量只能是变量只能是SET @i2 = value的变量,传入和declare的变量不行。

python中通过pymysql模块也可以调用存储过程

mysql数据库进阶
mysql数据库进阶
#coding:utf-8

import pymysql

conn = pymysql.connect(host="localhost",port=3306,db="learningsql",user="root",passwd="",charset="utf8")

cursor = conn.cursor()
#cursor = conn.cursor(cursor = pymysql.cursors.DictCursors)  最后返回的结果,每一行为字典
cursor.callproc("p5",args=(1,))

#执行需要参数的存储过程
# a,b=0,0
# cursor.callproc("p2",args=(1,2,a,b))
# cursor.execute("select @_p2_0,@_p2_1,@_p2_3,@_p2_4")  #拿到第1,2,3,4个参数。。。。


result = cursor.fetchall()
cursor.close()
conn.close()

print(result)
mysql数据库进阶

4.函数

  内置函数:https://dev.mysql.com/doc/refman/5.7/en/functions.html(官方文档)

常用内置函数

mysql数据库进阶
mysql数据库进阶
CHAR_LENGTH(str)
        返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

length:   是计算字段的长度,一个汉字是算两个或三个字符,一个数字或字母算一个字符;

char_length:不管汉字还是数字或者是字母都算是一个字符;


    CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ,则返回值为 NULL。
    CONCAT_WS(separator,str1,str2,...)
        字符串拼接(自定义连接符)
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。

    CONV(N,from_base,to_base)
        进制转换
        例如:
            SELECT CONV(a,16,2); 表示将 a 由16进制转换为2进制字符串表示

    FORMAT(X,D)
        将数字X 的格式写为#,###,###.##,以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点,或不含小数部分。
        例如:
            SELECT FORMAT(12332.1,4); 结果为: 12,332.1000
    INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos:要替换位置其实位置
            len:替换的长度
            newstr:新字符串
        特别的:
            如果pos超过原字符串长度,则返回原字符串
            如果len超过原字符串长度,则由新字符串完全替换
    INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

    LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

    LOWER(str)
        变小写

    UPPER(str)
        变大写

    LTRIM(str)
        返回字符串 str ,其引导空格字符被删除。
    RTRIM(str)
        返回字符串 str ,结尾空格字符被删去。
    SUBSTRING(str,pos,len)
        获取字符串子序列

    LOCATE(substr,str,pos)
        获取子序列索引位置

    REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL,则返回 NULL 。
    REPLACE(str,from_str,to_str)
        返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
    REVERSE(str)
        返回字符串 str ,顺序和字符顺序相反。
    RIGHT(str,len)
        从字符串str 开始,返回从后边开始len个字符组成的子序列

    SPACE(N)
        返回一个由N空格组成的字符串。

    SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

        mysql> SELECT SUBSTRING(Quadratically,5);
            -> ratically

        mysql> SELECT SUBSTRING(foobarbar FROM 4);
            -> barbar

        mysql> SELECT SUBSTRING(Quadratically,5,6);
            -> ratica

        mysql> SELECT SUBSTRING(Sakila, -3);
            -> ila

        mysql> SELECT SUBSTRING(Sakila, -5, 3);
            -> aki

        mysql> SELECT SUBSTRING(Sakila FROM -4 FOR 2);
            -> ki

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。

        mysql> SELECT TRIM(  bar   );
                -> bar

        mysql> SELECT TRIM(LEADING x FROM xxxbarxxx);
                -> barxxx

        mysql> SELECT TRIM(BOTH x FROM xxxbarxxx);
                -> bar

        mysql> SELECT TRIM(TRAILING xyz FROM barxxyz);
                -> barx

部分内置函数
mysql数据库进阶

自定义函数

mysql数据库进阶
mysql数据库进阶
-- 定义函数,注意定义返回值类型
delimiter \CREATE FUNCTION fun(
    i1 INT,
    i2 INT
)
RETURNS INT
BEGIN
    DECLARE sum INT;
    SET sum = i1+i2;
    RETURN(sum);
END \delimiter ;

-- -- 通过select使用
SELECT fun(1,2);

-- -- 对查询表字段进行函数操作
SELECT name,fun(1,age) from students;

-- 将函数结果存储到变量
SELECT fun(1,2) INTO @sum;
SELECT @sum;

SELECT UPPER("zack") INTO @name;
SELECT @name;

-- 删除函数
DROP FUNCTION fun;
mysql数据库进阶

 自定义函数时注意用户变量,局部变量,全局变量,会话变量,参考:https://blog.csdn.net/qq_34626097/article/details/86528466

5.事务

  事务是满足 ACID 特性的一组操作,要么全部执行,要么全部不执行,可以通过 Commit 提交一个事务,也可以使用 Rollback 进行回滚。在mysql中,事务用于将某些操作的多个SQL操作作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。

原子性(Atomicity):事务被视为不可分割的最小单位,要么全部执行成功,要么全部失败,进行回滚。(回滚可以通过回滚日志实现,回滚日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可。)

一致性(Consistency):数据库在事务执行前后都保持一致性状态。在一致性状态下,所有事务对一个数据的读取结果都是相同的。

隔离性(Isolation):一个事务所做修改在最终提交前,对其他事务是不可见的。多个事务并发执行时互不影响。

持久性(Durability):一但事务提交,其所做的修改将永远保存在数据库中,即使系统发生崩溃,事务执行的结果也不能丢失。(可以用重做日志来保证持久性)

对ACID特性的理解:(几个特性不是一种平级关系)

  • 只有满足一致性,事务的执行结果才是正确的。
  • 在无并发的情况下,事务串行执行,隔离性一定能够满足。此时只要能满足原子性,就一定能满足一致性。
  • 在并发的情况下,多个事务并行执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性。
  • 事务满足持久化是为了能应对数据库崩溃的情况

mysql数据库进阶

ACID特性

mysql数据库进阶

一致性状态和中间状态

 

   5.1 事务的并发一致性问题

    在多并发条件下,多个事务的隔离性很难保证,因此会出现很多并发一致性问题,包括丢失修改,脏读,不可重复读,幻读。

    丢失修改:事务T1和T2都修改了数据,T1先修改,T2后修改,T2的修改覆盖了T1的修改。

mysql数据库进阶

    脏读:事务T1修改了数据,事务T2读取了修改后的数据。随后T1回滚撤销了修改,事务T2读取到的就是脏数据。

mysql数据库进阶

 

    不可重复读:事务T2读取一个数据,T1对数据做了修改,如果T2再次 读取这个数据,此时读取的结果和第一次不一致。

mysql数据库进阶

    幻读:T1读取某个范围的数据,T2在这个范围了插入了一条数据,T1再次读取这个范围了数据,和第一次读取的结果不同。

    (场景:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。)

mysql数据库进阶

  5.2 事务的隔离级别

  上述的并发一致性问题是由于破坏了事务的隔离性,得通过并发控制来保证事务的隔离性。数据库管理系统提供了事务的隔离级别来处理并发一致性问题,包括未提交读(read uncommitted),提交读(read committed),可重复读(repeated read),可串行化(serilization)。(并发控制可以通过*来实现,但是*操作需要用户自己控制,相当复杂。)

  未提交读:最低的隔离级别,什么都不需要做,一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生

  提交读:只有在事务提交后,其更新结果才会被其他事务看见。可以解决脏读问题。

  可重复读:在一个事务中,对于同一份数据的读取结果总是相同的,无论是否有其他事务对这份数据进行操作,以及事务是否提交。可以解决脏读、不可重复读。

   可串行化:事务串行化执行,隔离级别最高,牺牲了系统的并发性。可以解决并发事务的所有问题。

mysql数据库进阶

  5.3 多版本并发控制(MVCC,Multi-Version Concurrent Control)

    多版本并发控制是MySQL的InnoDB提供的一种实现隔离级别的具体方式。MVCC可以用于实现提交读和可重复读。未提交读隔离级别,总是读取最新的数据行,无需使用 MVCC。可串行化需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。(一句话讲,MVCC就是同一份数据临时保留多个版本/快照)

  MVCC实现机制

    1.版本号:    

      系统版本号:是一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。(包含insert、delete和update操作的事务)

      事务版本号:事务开始时的系统版本号。

    2.隐藏的列

      MVCC 在每行记录后面都保存着两个隐藏的列,用来存储两个版本号:创建版本号和删除版本号

      创建版本号:对该行数据创建操作时的事务版本号

                 删除版本号:对该行数据进行删除操作时的事务版本号

    3.实现过程(可重复读隔离级别)  

  • select:满足以下两个条件innodb会返回该行数据
    • 该行的创建版本号小于等于当前版本号,用于保证在select操作之前所有的操作已经执行落地。
    • 该行的删除版本号大于当前版本或者为空。删除版本号大于当前版本意味着有一个并发事务将该行删除了。
  • insert:将新插入的行的创建版本号设置为当前系统的版本号。
  • delete:将要删除的行的删除版本号设置为当前系统的版本号。
  • update:不执行原地update,而是转换成 delete+insert。将旧行的删除版本号设置为当前系统版本号,并将新行insert,同时设置创建版本号为当前系统版本号。
  • 在写操作(insert、delete和update)执行后,需要将系统版本号递增
  • 由于旧数据并不真正的删除,所以必须对这些数据进行清理,innodb会开启一个后台线程执行清理工作,具体的规则是将删除版本号小于当前系统版本的行删除,这个过程叫做purge。

   4.undo日志

    MVCC 使用到的快照存储在 Undo 日志中,该日志通过回滚指针把一个数据行(Record)的所有快照连接起来。

   (TRX_ID: 事务版本号,为3时表示最新版本快照,通过回滚指针依次指向2,1,版本号)

    mysql数据库进阶

  5.4 Next-key locks

    参考:https://www.cnblogs.com/zhoujinyi/p/3435982.html

       http://hedengcheng.com/?p=771

       https://zhuanlan.zhihu.com/p/35477890

   Next-Key Locks是MySQL的InnoDb的一种锁实现。MVCC不能解决幻读问题,Next-Key Locks 就是为了解决这个问题而存在的。在可重复读隔离级别下,MVCC+Next-Key Locks可以解决幻读问题。Next-Key Locks是Record Locks和Gap Locks的结合。

   Record Locks:锁定一个记录上的索引,而不是记录本身。(如果表没有设置索引,InnoDB 会自动在主键上创建隐藏的聚簇索引,因此 Record Locks 依然可以使用。)

   Gap Locks:锁定索引之间的间隙,但是不包含索引本身。例如当一个事务执行以下语句,其它事务就不能在 t.c 中插入 15。

      SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

   Next-Key Locks:不仅锁定一个记录上的索引,也锁定索引之间的间隙。例如一个索引包含以下值:10, 11, 13, and 20,那么就需要锁定以下区间:

      (negative infinity, 10]
      (10, 11]
      (11, 13]
      (13, 20]
      (20, positive infinity)

   索引在[10,20]区间会被锁定,锁定后对该区间的操作都会被阻塞

   5.5 *

    5.5.1*粒度:

      MySQL提供了两种*粒度:行级锁和表级锁。 在选择*粒度时,需要在锁开销和并发程度之间做一个权衡。因为锁定的数据量越少,发生锁争用的可能就越小,系统的并发程度就越高。但是加锁需要消耗资源,锁的各种操作(包括获取锁、释放锁、以及检查锁状态)都会增加系统开销。因此*粒度越小,系统开销就越大。

    5.5.2*类型:

      a.读写锁:

                       排它锁(Exclusive),简写为 X 锁,又称写锁。

          一个事务对数据对象 A 加了 X 锁,就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。

                       共享锁(Shared),简写为 S 锁,又称读锁。

          一个事务对数据对象 A 加了 S 锁,可以对 A 进行读取操作,但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁,但是不能加 X 锁。

      兼容性

mysql数据库进阶

 

      b .意向锁:        

      使用意向锁(Intention Locks)可以更容易地支持多粒度*。

      在存在行级锁和表级锁的情况下,事务 T 想要对表 A 加 X 锁,就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁,那么就需要对表 A 的每一行都检测一次,这是非常耗时的。

      意向锁在原来的 X/S 锁之上引入了 IX/IS,IX/IS 都是表锁,用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定:

          1.  一个事务在获得某个数据行对象的 S 锁之前,必须先获得表的 IS 锁或者更强的锁;

          2. 一个事务在获得某个数据行对象的 X 锁之前,必须先获得表的 IX 锁。

                       通过引入意向锁,事务 T 想要对表 A 加 X 锁,只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁,如果加了就表示有其它事务正在使用这个表或者表中某一行的锁,因此事务 T 加 X 锁失败。

       兼容性:

           任意 IS/IX 锁之间都是兼容的,因为它们只是表示想要对表加锁,而不是真正加锁;

        S 锁只与 S 锁和 IS 锁兼容,也就是说事务 T 想要对数据行加 S 锁,其它事务可以已经获得对表或者表中的行的 S 锁。

mysql数据库进阶

    5.5.3*协议:

    参考:https://yq.aliyun.com/articles/626848

               https://yq.aliyun.com/articles/626848

     a.三级*协议

       一级*协议:事务T修改数据时必须加X锁,直到T结束才能释放。因为不能同时有两个事务对数据修改,可以解决丢失修改问题

       二级*协议:在一级的基础上,要求读数据A时必须加S锁,读完马上释放锁。因为一个事务对数据A修改时会加X锁,就不会再加S锁,不能               读取数据,可以解决脏读问题。

       三级*协议:在二级的基础上,要求读取数据A时必须加S锁,直到事务结束时才能释放锁。因为读数据A时一直有S锁,不能再加X锁,保证了              数据不会改变,可以解决不可重复读问题

         b.两段锁协议:加锁和解锁分为两个阶段进行。

          可串行化调度是指,通过并发控制,使得并发执行的事务结果与某个串行执行的事务结果相同。

          事务遵循两段锁协议是保证可串行化调度的充分条件

   5.5.4隐式锁和显式锁:MySQL 的 InnoDB 存储引擎采用两段锁协议,会根据隔离级别在需要的时候自动加锁,并且所有的锁都是在同一时刻被释放,这被  称为隐式锁定。InnoDB 也可以使用特定的语句进行显示锁定。(SELECT ... LOCK In SHARE MODE;

  5.6 事务语句

    start transaction........commit;

    失败时rollback

mysql数据库进阶
mysql数据库进阶
delimiter \create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION; 
    DELETE from tb1;
    insert into tb2(name)values(seven);
  COMMIT; 
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END\delimiter ;


使用事务
set @i=-1;
call p1(@i);
select @i;
mysql数据库进阶

6.条件判断和循环语句

  条件语句:if..then.;.elseif.then.;.else..;.end if;

mysql数据库进阶
mysql数据库进阶
-- 条件判断语句
delimiter \CREATE PROCEDURE p1(
    IN i INT
)
BEGIN
    IF i = 2 THEN
            SELECT * FROM students WHERE nid=2;
    ELSEIF i>2 THEN
            SELECT * FROM students WHERE nid>2;
    ELSE 
        SELECT * FROM students WHERE nid<2;
    END IF;
END\delimiter ;

CALL p1(2);
mysql数据库进阶

  循环语句:while循环,repeat循环,loop循环

mysql数据库进阶
mysql数据库进阶
-- while 循环语句
delimiter \CREATE PROCEDURE p2(
    IN i INT
)
BEGIN
    DECLARE sum INT;
    SET sum = 0;
    WHILE sum<10 DO
        SELECT sum;
        SET sum = sum +i;
    END WHILE;
END \delimiter ;

CALL p2(1);
mysql数据库进阶
mysql数据库进阶
mysql数据库进阶
-- repeat 循环
delimiter \CREATE PROCEDURE p3()
BEGIN 
    DECLARE i INT;
    SET i = 0;
    REPEAT
       SET i = i+1;
         SELECT i;
  UNTIL i>3 END REPEAT;
END \delimiter;

CALL p3();
mysql数据库进阶
mysql数据库进阶
mysql数据库进阶
-- loop循环
delimiter \CREATE PROCEDURE p4()
BEGIN
    DECLARE i INT DEFAULT 0;
    label: LOOP
    SET i = i+2;
    IF i=4 THEN
       SELECT * FROM students WHERE nid>4;
     END IF;
    IF i>6 THEN
        LEAVE label; 
    END IF;
    SELECT i;
END LOOP label;
END \delimiter;

CALL p4();
mysql数据库进阶

 

参考:http://www.cnblogs.com/wupeiqi/articles/5713323.html

   https://github.com/CyC2018/CS-Notes

 

mysql数据库进阶

上一篇:MySQL5.7.26编译安装


下一篇:OALP数据库优化之2 – Cube处理优化