1. 事务
1.1 简介
- TCL：(Transaction Control Language)，事务控制语言
- 事务：一个或一组SQL语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行
- 案例：A向B转账，涉及两个账户，一个账户需要加钱，另一个账户需要减钱，这两个操作的结果需要保持一致，即要么都成功，要么都失败，此时就可以通过事务来解决
- 事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句时相互依赖的。而整个单独单元作为一个不可分割的整体。如果单元中一条SQL语句执行失败或产生错误，整个单元都会回滚。所有受到影响的数据将返回到事物开始之前的状态；如果单元中的语句均执行成功，则事务被顺利运行
1.2 存储引擎
- 概念：在MySQL中的数据用于各种不同的技术存储在文件（或内存）中，也叫表类型
- 通过
show engines查看MySQL支持的存储引擎 - 在MySQL中用的最多的存储引擎有：innodb、MyISam、memory等。其中innodb支持事务，其他两个不支持
1.3 事务的ACID属性
- 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
- 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态
- 隔离性（Isolation）：事务的隔离性是指一个事务的执行不能被其他事务所干扰，即一个事务的内部操作及使用的数据对并发的其他事务没有影响
- 持久性（Durability）：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
1.4 事务的创建
- 隐式事务：事务没有明显的开启和结束标记，如insert、update、delete语句
- 显示事务：事务具有明显的开始和结束标记，前提是先设置自动提交功能为禁用
步骤一：
set autocommit = 0;：关闭自动提交，只对当前事务有效start transation;开始标记，可以省略
步骤二：
- 编写事务的相关语句
步骤三：
commit;：提交执行事务rollback;回滚事务
示例：
# 开启事务
SET AUTOCOMMIT = 0;
START TRANSACTION;
# 编写一组事务语句
UPDATE account
SET
balance = 500
WHERE
username = 'hxuanyu';
UPDATE account
SET
balance = 1500
WHERE
username = 'hxy';
# 结束事务
COMMIT;1.5 事务的隔离级别
对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：
- 脏读：对于两个事务T1、T2，T1读物了已经被T2更新但还没有被提交的字段，之后，若T2回滚，T1读取的内容就是临时且无效的
- 不可重复度：对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同一个字段时，发现值被修改
- 幻读：对于两个事务T1、T2，T1从表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行
数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使它们不会互相影响，避免出现各种并发问题
数据库提供四种隔离级别：
- 读未提交（READ UNCOMMITED）：允许事务读取未被其它事务提交的变更，会出现脏读、不可重复读和幻读
- 读已提交（READ COMMITED）：只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可避免不可重复读和幻读
- 可重复读（REPETABLE READ）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读，但幻读的问题仍然存在
- 串行化（SERIALIZABLE）：确保事务可以从一个表中读取相同的行。在这个事务执行期间，禁止其他事务对该表进行插入、删除、修改操作，所有并发问题都能避免，但是会导致性能下降
Oracle只支持读已提交和串行化两种隔离级别，默认为读已提交，MySQL支持所有四种隔离级别，MySQL默认为可重复读
在事务中使用SAVEPOINT 保存点可以设置保存点，在回滚时可以使用ROLLBACK TO 保存点将事务回滚到保存点而不是事务执行之前。
1.6 delete和trancate在事务中的区别
- delete
SET AUTOCOMMIT = 0;
START TRANSACTION;
DELETE
FROM
account;
ROLLBACK;撤销后数据还在
- trancate
SET AUTOCOMMIT = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;撤销后数据不在
- 总结：事务中的delete操作可以被回滚，而trancate操作由于直接提交到磁盘，因此不能回滚恢复数据
2. 视图
2.1 简介
MySQL从5.0.1版本开始提供视图功能。视图是一种虚拟的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存你查询结果
应用场景：
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂
优势
- 重用了sql语句
- 简化了复杂的sql操作，不必知道它的细节
- 保护数据，提高安全性
- 视图与表的区别
| 创建语法的关键字 | 是否实际占用物理空间 | 使用 | |
|---|---|---|---|
| 视图 | create view | 没有，只是保存了sql逻辑 | 增删改查，一般不能增删改 |
| 表 | vreate table | 占用，保存了实际数据 | 增删改查 |
2.2 视图的创建
- 语法：
CREATE VIEW 视图名
AS
查询语句;- 示例：
CREATE VIEW my_view
AS
SELECT
stuname,
majorname
FROM
stuinfo s
INNER JOIN major m ON s.majorid = m.id;- 使用
SELECT *
FROM
my_view
WHERE
stuname LIKE '张%';- 案例
# 案例查询姓名中包含a字符的员工名、部门名和工种信息
CREATE VIEW myv1
AS
SELECT
last_name,
department_name,
job_title
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;
# 使用
SELECT *
FROM
myv1
WHERE
last_name LIKE '%a%';# 查询各部门的平均工资级别
# 创建视图，查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT
avg(salary) ag,
department_id
FROM
employees
GROUP BY
department_id;
SELECT *
FROM
myv2;
# 使用
SELECT
myv2.ag,
g.grade_level
FROM
myv2
JOIN job_grades g ON ag BETWEEN g.lowest_sal AND g.highest_sal;2.3 视图修改
2.3.1 方式一
- 语法
create or replace view 视图名
as
查询语句;- 示例
CREATE OR REPLACE VIEW
myv2 AS
SELECT
avg(salary) ag,
department_id
FROM
employees
GROUP BY
department_id;2.3.2 方式二
- 语法
alter view 视图名
as
查询语句;- 示例
ALTER VIEW myv2
AS
SELECT *
FROM
employees;2.4 视图的删除
- 语法：可以一次删除多个视图
drop view 视图名，视图名，视图名。。。- 示例
DROP VIEW myemployees.myv2,myv1;2.5 查看视图
- 语法：
desc 视图名;
show create view 视图名;- 示例：
DESC myemployees.myv2;2.6 视图的更新
可以对视图进行更新操作，但是一般不建议这样做，且大多数视图无法更新
- 插入操作
# 插入
INSERT INTO
myemployees.myv1
VALUES
('hxuanyu', 'mail@qq.com');- 修改操作
UPDATE myemployees.myv1
SET
last_name='hxy'
WHERE
last_name = 'hxuanyu';- 删除操作
# 删除
DELETE
FROM
myemployees.myv1
WHERE
last_name = 'hxy';具备以下特点的视图不允许更新
- 包含分组函数、
DISTINCT、group by、having、union或union all的视图 - 常量视图
select子句中包含子查询的视图joinfrom一个不能更新的视图where子句的子查询引用了from子句中的表
- 包含分组函数、