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

1 事务

delete : 回滚之后数据不会删除

truncate:回滚之后数据会被删除

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：可以避免脏读(🎈oracle默认)
        - REPEATABLE READ：可以避免脏读、不可重复读和一部分幻读（🎈mysql默认）
        - 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;

 

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个或多个          一般用于更新

 

 

 

 

 

 

 

 

 

 

 

 

 

5 变量