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索引原理
posted @ 2019-12-17 21:18  HEU葉孤城  阅读(120)  评论(0编辑  收藏  举报