MySql事务&视图&存储过程与函数

1 事务

1. 概念
	* 如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
-------------------------------------------------------------------------
2. 数据库引擎
	* 概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中
	* 操作：
		* show engines: 查看mysql支持的存储引擎
	* 面试题：MyISAM 与 InnoDB 区别？

3. 事务的ACID特征
	* 原子性（Atomicity）：是不可分割的最小操作单位，要么同时成功，要么同时失败
	* 一致性（Consistency）：保证数据的状态操作前和操作后保持一致
	* 隔离性（Isolation）：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
	* 持久性（Durability）：当事务提交或回滚后，数据库会持久化的保存数据
-------------------------------------------------------------------------
4. 事务操作步骤
	1. 开启事务
	2. 编写事务逻辑操作单元
	3. 提交事务或回滚事务
-------------------------------------------------------------------------
5. 事务分类
	* 隐式事务：没有明显的开启和结束事务的标志
		* insert、update、delete语句本身就是一个事务
	* 显式事务：具有明显的开启和结束事务的标志
		1. 取消自动提交事务：set autocommit=0;
		2. 开启事务：start transaction;
		3. 提交或回滚事务：
			- commit;
			- rollback;
-------------------------------------------------------------------------
6. 事务的隔离级别（面试常考）
	* 事务并发问题是如何产生的？
		- 当多个事务同时操作同一个数据库的相同数据时
	* 事务并发问题
		- 脏读：一个事务读取到了另外一个事务未提交的数据
		- 不可重复读：同一个事务中，多次读取到的数据不一致
		- 幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据
	* 处理事务并发问题，设置事务隔离级别
		- READ UNCOMMITTED
		- READ COMMITTED：可以避免脏读
		- REPEATABLE READ：可以避免脏读、不可重复读和一部分幻读
		- SERIALIZABLE：可以避免脏读、不可重复读和幻读
	* 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
	* 设置隔离级别
		- set session|global  transaction isolation level 隔离级别名;
	* 查看隔离级别
		- select @@tx_isolation;

2 视图

含义：一张虚拟的表

好处：

• sql语句提高重用性，效率高
  • 和表实现了分离，提高了安全性

* 视图创建
	* 语法
		CREATE VIEW  视图名
		AS 查询语句;
	* 示例
		CREATE VIEW myview as select * from user;
-------------------------------------------------------------------------
* 视图更新
	* 语法
		- CREATE OR REPLACE VIEW 视图名
		AS 查询语句;
		- ALTER VIEW 视图名
		AS 查询语句;
	* 示例
		- CREATE OR REPLACE VIEW myview
		AS SELECT name FROM employees
		WHERE employee_id>100;
		- ALTER VIEW myview
		AS SELECT employee_id FROM employees;
	* 注意：视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的
		- 包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
		- 常量视图
		- Select中包含子查询
		- join
		- from一个不能更新的视图
		- where子句的子查询引用了from子句中的表
-------------------------------------------------------------------------
* 视图删除
	* 语法
		DROP VIEW 视图名1,视图名2...;
	* 示例
		DROP VIEW test_v1,test_v2,test_v3;
-------------------------------------------------------------------------
* 视图结构查看
	* 语法
		- DESC 视图名;
		- SHOW CREATE VIEW 视图名;
	* 示例
		- DESC test_v7;
		- SHOW CREATE VIEW test_v7;

---

* 查看视图数据
	* 语法
		SELECT 查询字段
		FROM 视图名；
	* 示例
		SELECT * FROM myview WHERE name='Partners';
-------------------------------------------------------------------------
* 插入视图数据
	* 语法
		INSERT INTO 视图名（视图字段）
		VALUES(值)
	* 示例
		INSERT INTO myview(name,age) VALUES('虚竹',90);
-------------------------------------------------------------------------
* 修改视图数据
	* 语法
		UPDATE 视图名
		SET 视图字段名=值 
		WHERE 条件
	* 示例
		UPDATE myview SET name ='梦姑' WHERE name='虚竹';
-------------------------------------------------------------------------
*删除视图数据
	* 语法
		DELETE FROM 视图名
		WHERE 条件;
	* 示例
		DELETE FROM myview;

面试题

视图与表的区别？
使用方式 占用物理空间

视图 完全相同 不占用，仅仅保存的是SQL逻辑

表 完全相同 占用

3 存储过程

含义：一组经过预先编译的sql语句的集合
好处：

• 提高了sql语句的重用性，减少了开发程序员的压力
• 提高了效率
• 减少了传输次数

分类：

• 无返回无参
• 仅仅带in类型，无返回有参
• 仅仅带out类型，有返回无参
• 既带in又带out，有返回有参
• 带inout，有返回有参

注意：in、out、inout都可以在一个存储过程中带多个

创建存储过程

* 语法
    create procedure 存储过程名(in|out|inout 参数名  参数类型,...)
    begin
       存储过程体
     end

类似于方法：

修饰符 返回类型 方法名(参数类型 参数名,...){

	方法体;
}

注意:

1、需要设置新的结束标记
	delimiter 新的结束标记
示例：
	delimiter $
	CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
	BEGIN
		sql语句1;
		sql语句2;
	END $
-------------------------------------------------------------------------
2、存储过程体中可以有多条sql语句，如果仅仅一条sql语句，则可以省略begin end
-------------------------------------------------------------------------
3、参数前面的符号的意思
	- in:该参数只能作为输入 （该参数不能做返回值）
	- out：该参数只能作为输出（该参数只能做返回值）
	- inout：既能做输入又能做输出

调用存储过程

* 语法
	call 存储过程名(实参列表)

删除存储过程

* 语法
	DROP PROCEDURE 存储过程名;
* 注意：不能同时删除多个存储过程

查看存储过程结构

* 语法
	SHOW CREATE PROCEDURE 存储过程名; 

4 函数

含义：经过编译并存储在数据库中的一段sql语句的集合

创建函数

* 语法
    CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
    BEGIN
        函数体
    END

调用函数

* 语法
	SELECT 函数名（实参列表）

查看函数结构

* 语法
	SHOW CREATE FUNCTION 函数名;

删除函数

* 语法
	DROP FUNCTION 函数名;

函数和存储过程的区别

		  关键字		调用语法	  返回值			        应用场景
函数		FUNCTION  SELECT 函数()  只能是一个  一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个
存储过程   PROCEDURE CALL 存储过程() 可以有0个或多个		   一般用于更新