MySQL复习笔记（三）

6、事物(Transaction)★★★★★

（1）、基础知识：

    事务是由一步或几步数据库操作序列组成的逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。一般而言，一段程序中可能包含多个事务。事务具有四个特性ACID：

（2）、事物的四大特性，简称“一致持久隔离原子”

①、原子性(Atomicity) ，事物是应用中最小的执行单位，就如原子是自然界最小的颗粒一样(现代物理研究表明，原子并不是微 微观物质中最小的颗粒)；

②、一致性(Consistency) ，事物的执行结果，必须使数据库从一个一致性状态变到另一个一致性状态；

③、隔离性(Isolation) ，各个事物的执行互不干扰，任意一个事务的内部操作对其他并发的事物都是隔离的，也就是说，并发执 执行的事物之间不能看到对方的中间状态，并发执行的事物之间不能互相影响；

④、持续性也成为持久性(Durability)，指事物一旦提交，对数据的任何改变，都要永久记录到存储器中，通常是保存进物理数据 据库。

（3）、数据隔离问题

多线程下多个线程同时修改，肯定会互相捣乱， 隔离；

多线程下多个线程同时读取，没有任何问题；

多线程下一个线程修改，一个线程读取，可能出现下面的问题：

①、脏读( Dirty Reads)：所谓脏读就是对脏数据( Drity Data)的读取，而脏数据所指的就是未提交的数据。也就是说，一个事务正在对条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的(可能提交也可能回滚)这时，第二个事务来读取这条没有提交的数据，并基于此数据做进一步的处理，就会生未提交的数据依赖关系。这种现象被称为脏读；

②、不可重复读(Non-Repeatable Reads)：一个事务先后读取同—条记录，但两次读取的数据不同，我们称之为不可重复读。也就是说，这个事务在两次读取之间该数据被其它事务所修改；

③、幻读( Phantom Reads)：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。

（4）、事务的四个隔离级别：

①、未提交读( Read Uncommitted)：SELECT语句以非锁定方式被执行，所以有可能读到脏数据,隔离级别最低。

②、提交读( Read Committed)：只能读取到已经提交的数据。即解决了脏读但未解决不可重复读。

③、可重复读( Repeated Read)：在同一个事务内的查询都是事务开始时刻一致的，InnoDB引擎的默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。

④、串行读( Serializable)：完全的串行化读，所有 SELECT语句都被隐式的转换成SELECT....LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会阻塞。隔离级别最高。

1、查看数据的隔离级别
show variables like 'transaction_isolation';
或者是：
select @@transaction_isolation;


2、开启事务，事物是基于数据库连接的，默认引擎下，事物是关闭的，并且是自动提交的，
start transaction;

savepoint /**保存点，回滚的时候可以选择保存点回滚**/

（5）、手动提交、关闭事物

先关闭事物的自动提交
set autocommit=0;  /**1:自动提交，0：手动提交**/
insert into tsinger(sname,ssalary,sdisplay) values('谭维维','1111.11','实力唱将');
/**此时刷新表数据，并未实际插入成功**/
commit;
/**提交后，数据插入成功**/

insert into tsinger(sname,ssalary,sdisplay) values('萧敬腾','1111.11','退出直播间');
/**此时刷新表数据，并未插入成功**/
rollback;
/**事物的回滚，即撤销数据的插入**/

（6）、事物的执行、提交、回滚(此处有问题，按理说应该set autocommit=0然后才会出现下面的效果，但是autocommit=1时也是出现了手动提交的情况，即将autocommit设置为0和1都是手动提交的效果，留以后证)

set autocommit=1;   /**先关闭自动提交，及设置为手动提交**/
start transaction;  /**开启事物**/

select * from tsinger;  /**查询操作**/

update tsinger set sage=58 where sname='腾格尔';

delete from tsinger where sname='洛天依';

commit;   /**等执行了这句后，上述操作才会一次性完成**/

rollback;  /**若执行了这句后，则上述操作不会成功**/

（7）、JDBC中的事物

超级明显的占位符__________________.......hahhaha

 

7、存储过程

（1）、存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。类似于Java中的方法。

1、创建一个存储过程
drop procedure if exists po1;
/**创建前要先执行此删除语句**/

create procedure po1(in newname varchar(10),in newdisplay varchar(300),out count int)
begin 
	insert into tsinger(sname,sdisplay) values(newname,newdisplay);
	set count = (select count(*) from tsinger);
end;
/**set count=...., 此处设置count的值为表里的记录数**/

2、调用存储过程，需要传参
call po1('韩磊','向天再借五百年',@count);
select @count;

（2）、创建一个存储过程，实现根据传入的参数，在后面添加数据到10行

drop procedure if exists pro2;

create procedure pro2(in num int)
begin
	declare newname varchar(10);
	declare newdisply varchar(300);
	while num<=10 do 
		set newname = substring(uuid(),1,10);
		set newdisply = uuid();
		insert into tsinger(sname,sdisplay) values(newname,newdisply);
	set num = num+1;
	end while;
end;
/**declare 关键字：声明一个变量**/

call pro2(8);/**调用存储过程**/

 

8、存储函数

（1）、存储函数： 是一个特殊的存储过程，类似于Java中的方法；很遗憾，新版本MySQL(亲测5.7和8.0版本都不可以)不支持在function里写动态的DML语句；存储函数里都是入参,'in'关键字可以省略不写。

1、设计一个函数，传入参数为歌手的名字和display，然后新增一个歌手的到数据库，同时删除名字为10个长度的歌手，如果都成功，则提交，如果某操作失败，则回滚
drop function if exists fun1;
create function fun1(newname varchar(10),newdisplay varchar(300))
returns int reads sql data
begin
	/**声明一个返回值变量**/
	declare count int;
	/**定义一个变量,此句类似于Java里的  int flag = 0; **/
	declare flag int default 0;
  /**定义一个sql异常的句柄/监视器**/
	declare continue handler for sqlexception set flag = 1;   
	/**开启事务**/
	start transaction;
	/**新增数据**/
	insert into tsinger(sname,sdisplay) values(newname,newdisplay);
	/**删除所有名字长度为10的数据**/
	delete from tsinger where sname like '__________';
	/****/
	set count = (select count(sid) from tsinger);
	/**异常提交或回滚**/
	if flag!=1 then commit;
	else rollback;
	end if;
	return count;
end;

/**很遗憾，写完后发现MySQL不支持在function，里写动态DML语句，因此这个存储函数创建失败**/

（2）、可以创建的存储函数（里面不包含动态DML语句）

1、传如一个id，输出id小于这个参数的歌手数量
drop function if exists fun2;
create function fun2(id int(11))
returns int reads sql data
begin
	/**声明一个返回值变量**/
	declare count int;
	/**查询语句**/
	set count = (select count(sid) from tsinger where sid<id);
	/**返回数据**/
	return count;
end;

select fun2(1010);
/**调用这个存储函数**/

（3）、带事物的存储过程

1、向tsinger表里插入一条指定名字和描述的数据，删除一条名字里含有指定的参数数据，返回最后表里有多少条记录
drop procedure if exists protrans;
create procedure protrans(in newname varchar(10),in newdisplay varchar(300),in str varchar(3),out count int)
begin
	/**定义一个标志变量**/
	declare flag int default 0;
	/**定义如果出现sql异常，就设置flag为1**/
	declare continue handler for sqlexception set flag=1;
	/**开启事务**/
	start transaction;
	/**新增一条记录**/
	insert into tsinger(sname,sdisplay) values(newname,newdisplay);
	/**删除一条数据**/
	delete from tsinger where sname like concat('%',str,'%');
	/**提交或回滚判断**/
	if flag != 1 then commit;
	else rollback;
	end if;
	/**查询总记录数，用来返回**/
	set count = (select count(sid) from tsinger);
end;

call protrans('那英','茉莉花','-7',@count);
select @count;

call protrans('ny','茉莉花','-7',@count);
select @count;
/**这次调用时因为有异常，所以会回滚，删除失败，因为sdisplay字段是捡了唯一索引的**/

9、游标

（1）、游标，类似于Java里的iterator迭代器。游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标充当指针的作用。尽管游标能遍历结果集中的所有行，但是它一次只能指向一行。

（2）、概括来讲，SQL的游标是一种临时的数据库对象，既可以用来存放在数据库表中的数据行副本，也可以指向存储在数据库中的数据行的指针。游标提供了在逐行的基础上操作表中数据的方法。

（3）、游标的一个常见用途就是保存查询结果，以便以后使用。游标的结果集是由select语句产生，如果处理过程需要重复使用一个记录集，那么创建一次游标而重复使用若干次，比重复查询数据库要快的多。

（4）、大部分数据库设计语言都能使用游标来检索SQL数据库中的数据，在程序中嵌入游标和在程序中嵌入SQL语句相同。

从mysql V5.5开始，进行了一次大的改变，就是将InnoDB作为默认的存储引擎。InnoDB支持事务，而且拥有相关的RDBMS特性：ACID事务支持，数据完整性（支持外键），灾难恢复能力等等。

1、用游标的方式计算ssalary列的和（用游标每次从ssalary字段拿到一个数据，若不为空，则将它们依次累加）
drop procedure if exists sums; 
create procedure sums(out salas decimal(8,2))
begin
	/**用变量c来接收每一次从游标里读取的数据**/
	declare c decimal(7,2) default 0;
	declare flag int default 0;
	/**声明一个游标，游标是来自查询tsinger表里所有ssalary这一列的**/
	declare cur1 cursor for select ssalary from tsinger;
	/**声明一个继续监控的handler，监控游标循环到最后找不到数据了**/
	declare continue handler for not found set flag=1;
	set salas = 1;
	/**开启游标**/
	open cur1;
	/**取一条游标里的数据到一个变量c中**/
	fetch cur1 into c;
	/**当flag还不是1的时候，说明游标还没有遍历完**/
	while flag!=1 do
		if c is not null then set salas = salas+c;
		end if;
		/**继续向下一行游标索要数据**/
		fetch cur1 into c;
	end while;
	/**关闭游标**/
	close cur1;
end;

call sums(@sal);
select @sal;

10、触发器

（1）、触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行增、删、改操作时就会触发，或者更改表结构时也会触发。触发器经常用于加强数据的完整性约束以及业务规则等。综上所述：触发器是一种与表操作有关的数据库对象，当触发器所在的表上发生指定事件时，将会调用该对象，即表的操作事件触发表上触发器的执行。

（2）、触发器分为三类：DML触发器，DDL触发器，登录触发器。我们重点来看一下DML触发器和DDL触发器。DML触发器是指在数据库中发生DML操作时将会触发的事件。DDL触发器是指在数据库中发DDL操作时将会触发的事件。

（3）、触发器的作用：

1. 安全性：可以基于数据库的值使用户具有操作数据库的某种权利。可以基于时间限制用户操作，例如不允许节假日或下班时间对数据库操作。可以基于数据库中的数据限制用户的操作，例如不允许股票的价格一次上浮10%。
2. 审计：可以跟踪用户对数据库的操作。例如：审计用户操作数据库的语句；例如：把用户对某些关键表的操作写入到审计表中。
3. 实现复杂的数据完整性规则。例如：实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
4. 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库相关的表进行连环更新，例如在某个表上的删除触发器可以导致相应的删除与之关联的其他表的数据或更改某列的值。
5. 同步的实时复制表中的数据。
6. 自动计算数据值，如果某个表中的某列字段值达到了一定的要求，则进行特定的处理

（4）、创建触发器的SQL语法如下：

create trigger trigger_name trigger_time trigger_event on table_name for each row

触发器是与表操作相关的数据库对象，所以触发的命名与表相关，在创建触发器时需要指定表名table_name，表为永久性的表，所以触发器不能与临时表和视图建立关联。

trigger：创建触发器的关键字，定义触发器名称。

trigger_name：触发器的名称。

trigger_time：触发器的执行时间，它的值为before或者after，是指在激活它的语句之前还是之后执行。一般情况下是在激活它的语句运行完成后再执行触发器。

trigger_event：触发事件，它的值可以使insert、update、delete。在这个三个动作上我们需要了解数据库里两个临时的虚拟表：deleted、inserted，在mysql中使用old和new关键字来表示。

动作	deleted表	inserted表
insert	不存储记录	存储新插入的记录
update	存放之前的记录	存放更新后的记录
delete	存放被删除的记录	不存储记录

1、案例：设计一个触发器来记录tsinger表里哪个记录发生了修改，并记住原来的名字和新名字，以及修改时间
/**先创建一个用来记录这些信息的数据表**/
create table myrecord(
	rid varchar(36) primary key ,
	sid int,
	oldname varchar(20),
	newname varchar(20),
	updatetime datetime
);

/**创建一个触发器，名字是rec，监视的事件是  修改，时间之后**/
drop trigger if exists rec;
create trigger rec after update on tsinger for each row
begin
	insert into myrecord values(uuid(),old.sid,old.sname,new.sname,now());
end;

/**触发器在设定的时间和时间符合时自动调用**/
update tsinger set sname='张三' where sname='那英';
/**执行完这句后，myrecord表里会自动记录好一条数据**/

11、综合练习题

（1）、设计一存储过程. 在向tsinger表中插入数据时, 如果 ssalary 没给值 就赋值为0.00，如果sbirthday字段没给值，就赋值为 当前日期。
 

MySQL

（2）、设计一存储过程.  带事务，修改 tsinger表 指定id的那条记录的sdisplay为一个新传入的新描述。修改不成功!!!!
修改表中数据，月薪小于1万的就乘以3，月薪大于1万的就乘以1.1，修改完毕后，返回 表中月薪大于1万的歌手总数。

MySQL