事物控制
事务(Transaction)是指作为一个逻辑工作单元执行的一系列操作,这些操作要么全部成功,要么全部失败。事务确保对多个数据的修改作为一个单元来处理。
- 例:在ATM机上进行转账,张三给李四转帐100块钱,要么成功,要么失败,并且需要同步操作。在张三给李四转帐一百块成功时,张三账户余额 -100,李四账户余额 +100。
在MySQL中,只有使用了Innodb存储引擎的数据库或表才支持事务。
事务用于维护数据库的完整性,保证成批的sql语句要么都执行,要么都不执行
事务用于管理INSERT、UPDATE和DELETE语句。
- 如果某个数据库声称支持事务,那么该数据库必须具备ACID四个特性,即Atomicity(原子性)、Consistency(一致性)、Isolation(隔离性)和Durability(持久性)。
- 在默认情况下,MySQL是自动提交事务的,即每一条INSERT、UPDATE、DELETE的SQL语句提交后会立即执行COMMIT操作。因此,要开启一个事务,可以使用start transaction或begin,或者将autocommit的值设置为0
使用UPDATE语句进行更新内容
- 语法:
UPDATE 更新UPDATE 表名 SET 更新内容 [限制条件]
- 通过案例掌握事务控制
CREATE TABLE bank_account(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(30) COMMENT "姓名",
balance decimal(18,2) comment "账户余额"
);
INSERT INTO bank_account(id,name,balance) VALUES(1,"张三",0),(2,"李四",0);
//显示系统变量autocommit的值,值为1-->自动提交事务 可设置为0
SELECT @@autocommit;
SELECT * FROM bank_account;
//富婆给张三转钱
UPDATE bank_account SET balance = balance + 1000 WHERE id = 1;
//执行事务 张三有钱了给兄弟李四转账
UPDATE bank_account SET balance = balance - 100 WHERE id = 1;
UPDATE bank_account SET balance = balance + 100 WHERE id = 2;
//提交事务
COMMIT;
SELECT * FROM bank_account;
- 操作MySQL命令难免会出现失误,这时可以用ROLLBACK命令进行回滚操作,但MySQL每执行一条事务默认提交即要开启一个事务(start transaction/begin)将autocommit的值设为0
使用ROLLBACK进行回滚操作
语法:ROLLBACK;
SELECT * FROM bank_account;
//执行事务 张三又有钱了给兄弟李四转账
UPDATE bank_account SET balance = balance - 200 WHERE id = 1;
UPDATE bank_account SET balance = balance + 200 WHERE id = 2;
//这是发现钱不够,想撤回操作,进行回滚操作
ROLLBACK;
SELECT * FROM bank_account;
//这是发现,并没有回滚成功,因为MySQL每执行一条事务默认提交
//那么就要开启一个事务(start transaction/begin)将autocommit的值设为0或直接设置autocommit的值为0,再进行执行事务
SEART TRANSACTION; 或 BEGIN; 或 SET autocommit = 0;
//执行事务 张三又有钱了给兄弟李四转账
UPDATE bank_account SET balance = balance - 200 WHERE id = 1;
UPDATE bank_account SET balance = balance + 200 WHERE id = 2;
//进行回滚操作
ROLLBACK;
SELECT * FROM bank_account;
//此时回滚成功
视图
视图是一个虚拟表,其内容由select查询语句定义。和真实的表一样,视图也包含行和列,对视图的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成,视图中的数据都存储在基表中。
- 可读性
简化了复杂的查询,使复杂的查询更易于理解和使用
例如:一些复杂的查询结果封装到视图中,当需要的时候,直接查询视图即可 - 安全性
视图可以隐藏一些敏感的信息可以把权限限定到行列级别
例如:一张用户信息表,查询信息的会有一些敏感字段不需要显示,这时不能删除该字段,需要将查询的字段结果封装在视图中即可 - 重用性
视图是对复杂查询语句的封装对数据库重构,不会影响程序的运行
例如:将一些需要重复需要查询的操作结果封装到视图,需要时查询视图即可
视图的基本操作
视图是一个虚拟表,其内容由select查询语句定义。和真实的表一样,视图也包含行和列,对视图的操作与对表的操作基本一致。视图中的数据是在使用视图时动态生成,视图中的数据都存储在基表中。视图表的数据变化会影响到基表,基表的数据变化也会影响视图表。
创建视图
语法:
CREATE [OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS SELECT_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
- CREATE:表示创建视图的关键字
- OR REPLACE:表示该语句能够替换已有视图,但必须在视图上具有DROP权限
- ALGORITHM:可选值有UNDEFINED | MERGE | TEMPTABLE,表示视图使用的算法
- UNDEFINED:表示MySQL将自动选择所要使用的算法
- MERGE:表示将使用视图的语句与视图定义合并起来,使得视图定义的某一部分取代语句的对应部分
- TEMPTABLE:表示将视图的结果存入临时表,然后使用临时表执行语句
- view_name:表示要创建的视图名称
- column_list:可选,表示属性清单。指定了视图中各个属性的名,默认情况下,与SELECT语句中查询的属性相同
- AS 表示指定视图要执行的操作
- SELECT_statement:是一个完整的查询语句,表示从某个表或视图中查出某些满足条件的记录,将这些记录导入视图中
- WITH CHECK OPTION:可选,表示创建视图时要保证在该视图的权限范围之内
- CASCADED:可选。表示创建视图时,需要满足跟该视图有关的所有相关和表的条件,该参数为默认值
- LOCAL:可选。表示创建视图时,只要该视图本身定义的条件即可
注:
1. 创建视图时要求具有针对视图的CREATE VIEW权限,以及针对由SELECT语句选择的每一列上的某些权限。对于在SELECT语句中其他地方使用的列,必须具有SELECT权限。如果还有OR REPLACE子句,必须在视图上具有DROP权限
2. 需要注意的是,视图属于数据库,在默认情况下,将在当前数据库创建新视图,要想在给定数据库中明确创建视图,创建时应将名称指定为db_name.view_name
自定义视图字段名称
语法:CREATE VIEW view_name(字段name...) AS SELECT 字段... FROM table_name;
查看视图信息
DESCRIBE语句可以查看视图的字段信息,其中包括字段名、字段类型等信息
语法:DESCRIBE view_name; 或 DESC view_view;
得到的信息对应字段
- Key:表示该列是否已经编制索引
- Default:表示该列是否有默认值
- NULL:表示该列是否可以存储NULL值
- Extra:表示获取到的与给定列相关的附加信息
使用SHOW TABLE STATUS语句查看视图
语法:SHOW TABLE STATUS LIKE 'view_name';
在上述格式中,“LIKE”表示后面匹配的是字符串,“view_name”表示要查看的视图的名称,视图名称需要使用单引号引起来
修改视图
修改视图是指修改数据库中存在的视图的定义,比如,当基本表中的某些字段发生时,可以通过修改视图的方式来保持视图与基本表的一致性
- 使用Update语句修改视图
语法:Update view_name set 字段名= ,… WHERE id = 主键(指定修改对象),若无则修改全部
更新视图
- 使用UPDATE语句更新视图
例:将AB_view视图中的sex字段改为"女"
UPDATE AB_sex SET sex = “女”; - 使用INSERT语句更新视图
在表中插入了数据后, 对应的视图中的数据也随之改变 - 使用DELETE语句更新视图
视图中删除某一段的记录后,视图中的一整条记录全部被删除了,对应的数据表中的整条记录也会被删除
删除视图
- 删除视图时,只能删除视图的定义,不会删除数据。删除一个或多个视图可以使用DROP VIEW语句
语法:DROP VIEW[IF EXISTS] view_name [,view_name1]…
视图名称可以添加多个,各个名称之间使用逗号隔开,删除视图必须拥有DROP权限
存储过程
存储过程(Stored Procedure)是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行,类似于编程语言中的方法或函数
- 存储过程的优点:
- 存储过程是对SQL语句的封装,
增强可复用性
- 存储过程
可以隐藏复杂的业务逻辑、商业逻辑
- 存储过程
支持接收参数,并返回运算结果
- 存储过程的缺点:
- 存储过程的
可移植性较差,如果更换数据库,要重写存储过程
- 存储过程难以调试和扩展
无法使用Explain对存储过程进行分析
《阿里巴巴Java开发手册》中禁止使用存储过程
#存储过程定义:求两数之和
delimiter // --> delimiter是声明语句结束符,防止定义存储过程中 ";" 出现混淆
create procedure my_sum(in a int, in b int, out result int)
begin
set result = a + b;
end
//
delimiter ;
# in 定义入参(也就是调用该存储过程需要传入的参数) out 定义出参数(存储过程需要返回的参数)
# SET 表明进行赋值操作
#存储过程调用
call my_sum(10, 20, @result);
select @result;
条件的定义
在MySQL中,特定异常需要特定处理。这些异常可以联系到错误,以及子程序中的一般流程控制。
定义异常是事先定义程序执行过程中遇到的问题,异常处理定义了在遇到问题时对应当采取的处理方式,并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。
语法:
DECLARE condition_name CONDITION FOR condition_value;
- condition_name参数表示条件的名称;
- condition_value参数表示条件的类型;
- sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。
- 例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
- handler_type(指明错误的处理方式): 有以下三种取值
- continue(遇到错误不处理,继续执行) exit(遇到错误马上退出)
- undo(表示遇到错误后撤回之前的操作,MySQL中暂时不支持这样的操作)
- condition_value:表示错误类型,有以下六种取值
- SQLSTATE[VALUE] sqlstate_value包含5个字符的字符串错误值
- condition_name DECLARE CONDITION 定义的错误条件名称
- sqlwarning 匹配所有以01开头的SQLSTATE错误代码
- not found 匹配所有以02开头的SQLSTATE错误代码
- sqlexception 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
- mysql_error_code 匹配数值类型错误代码
- sp_statement:参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程。
#方法一:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';
#方法二:捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';
#方法三:先定义条件,然后调用
DECLARE can_not_find CONDITION FOR 1146 ;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';
#方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
#方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';
#方法六:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
光标
在编写存储过程时,查询语句可能会返回多条记录,如果数据量非常大,则需要使用光标来逐条读取查询结果集中的记录
。
光标是一种用于轻松处理多行数据的机制。
目前 mysql中的光标只能在存储过程或函数中使用
声明语法:DECLARE cursor_name CURSOR FOR select_statement
注意:声明位置要在变量和条件之后,在存储过程处理程序之前
-
光标的使用:
打开OPEN cursor_name
使用FETCH cursor_name INTO var_name[,var_name]…
关闭光标:CLOSE cursor_name;
-
整体案例
DECLARE s_name,s_address CHAR(20);
DECLARE cursor_student CURSOR FOR SELECT name,address FROM student;
OPEN cursor_student;
FETCH cursor_student INTO s_name,s_address;
SELECT s_name,s_address;
CLOSE cursor_student;