CSIC_716_20191217【事务、视图、触发器、存储过程、索引】

事务：

事务保证对数据操作时的安全性，事务中的代码要么一起成功，要么一起失败。

事务以 start transaction 开始，中间可以写诸多个sql 语句对数据库进行操作，以rollback（失败）或者commit（成功）结束。

伪代码 try: ...sql语句 except 异常:　rollback； else: commit；

事务的四大属性（ACID）:

A：原子性(一个事务就是一个基本的执行单位，事务里的代码要么全部执行，要么全部不执行)

C：一致性 (执行的结果要么一起成功，要么一起失败。一致性与原子性性密切相关)

I：隔离性（事物之间是隔离的，并发执行的各个事务之间不能互相干扰（并发的时候每行数据难道不应该有锁吗？））

D：持久性（一个事务一旦提交，就产生永久性的结果）

数据库设计的三大范式：

第一范式（NF1）：每个字段要有原子性，不可再分割

第二范式（NF2）：每个字段与主键要有相关性。

第三范式（NF3）：每个字段与主键要直接相关，而不是间接相关。

视图：视图在硬盘中只有表结构文件，没有数据文件。（较少使用）

语法：创建：create view 视图名字 as sql语句

　　　删除：drop view 视图名字

存储过程：通过存储过程，让不会sql语句的人能顺利操作数据库

存储过程在哪个库中创建，只能在那个库中使用。

语法：存储过程分为有参存储过程和无参存储过程

创建有参存储过程

delimiter //

create procedure p1(

    in arg1 int,   # in 是代表传入，不可传出

    out agr2 varchar, # out 是代表可以传出

    inout arg3 int    # inout 代表既可以传入也可以传入

)

begin

　　select * from t1 where agr1 =1 or arg2 = '2'

　　set arg3 = 0

end //

delimiter ;

在mysql中调用

# 在mysql中调用

set @xx = 11    # 先初始化一个值，备用，一遍将来可以查询是否变化

call p1(1,'4',@xx) # 调用存储过程

select @xx  # 查看结果，看sql语句是否已被执行

在python中调用

#在python中

import pymysql

coon = pymysql.connect(

    user='',

    passwd='',

    host= '',

    port=3306,

    db='',

    charset='',

    autocommit=True

)

cursor = coon.cursor(pymysql.cursors.DictCursor)

cursor.callproc('p1',(1,'4',11)) # 此时，内部会产生变量用来接元组中的参数

#参数的规律为  @_p1_0=1,@_p2_1='4,@_p3_2=11

cursor.execute('select @_p1_2')

#为什么查@_p1_2呢，因为在存储过程中是通过第三个参数来反馈是否执行完成

创建无参存储过程

delimiter //

create procedure p1( )

begin

　　select * from t1 where agr1 =1 or arg2 = '2'

end //

delimiter ;

在mysql中调用

# 在mysql中调用

call p1( ) # 调用存储过程

在python中调用

#在python中

import pymysql

coon = pymysql.connect(

    user='',

    passwd='',

    host= '',

    port=3306,

    db='',

    charset='',

    autocommit=True

)

cursor = coon.cursor(pymysql.cursors.DictCursor)

cursor.callproc('p1')

以下为事务结合存储过程的小例子：

delimiter //

create PROCEDURE p5(

    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;

      update user set balance=900 where id =1;

      update user123 set balance=1010 where id = 2;

      update user set balance=1090 where id =3;

  COMMIT;

  -- SUCCESS

  set p_return_code = 0; #0代表执行成功

END //

delimiter ;

触发器trigger：

在对表增、删、改的前或后，自动触发的功能称为触发器。

语法：create trigger tri_before_insert_t1 before|after insert|update|delete on t1 for each row begin sql代码 end

注意，在写触发器的时候，因为中间插入的SQL代码最后有分号，会导致上述触发器语句在分号处中断，故需要通过一种特殊手段改变局部命令的结束符。

delimiter %% 　　#声明后续语句以%%结束

create trigger tri_before_insert_t1 before|after insert|update|delete on t1 for each row begin sql代码 end %% # 这一条语句要用%%结束

delimiter ; #声明后续语句以 ; 结束

索引

索引是一种数据结构，索引可以加速数据的查询，类似于书的目录

primary key（聚集索引）：加速查询、非空唯一

unique key（辅助索引）：加速查询、唯一

index key（辅助索引） : 没有任何约束，只是加速查询

创建索引，会使得查询速度变快，但是，创建写的速度会变慢。

Innodb索引的原理

B+树、非聚簇索引叶子节点上存的是索引，聚簇索引叶子节点上存的数据

https://www.cnblogs.com/rjzheng/p/9915754.html

InnoDB索引原理

巴特西

CSIC_716_20191217【事务、视图、触发器、存储过程、索引】

最新文章

热门文章