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