12.1.事务的属性
-
原子性:
原子性指:事务是一个不可分割的工作单位,食物中的操作要么全发生,要么什么都不发生。
-
一致性:
事务必须从一个一致性状态变换到另外一个一致性状态
-
隔离性:
一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰
-
永久性:
事务一旦被提交,对数据库中的数据改变就是永久的,不能被撤销。接下来的其他操作和数据库故障不能对其造成影响
12.2.事务的创建
-
隐式事务
如
updata , delete , insert
-
显示事务(s事务具有明显的开启和结束的标志)
前提:必须先设置禁用自动提交的功能(
set autocommit = 0
)--开启事务的步骤 --1.开启事务 set autocommit = 0; start transaction ; #可选 --2.编写事务中的sql语句,select , upadata , delete , insert 语句1; 语句2; --3.结束事务 commit;#提交事务 rollback;#回滚事务 DROP TABLE if EXISTS Stu; create table if not exists Stu( id int PRIMARY KEY AUTO_INCREMENT, stuName varchar(10), money INT ); INSERT INTO Stu VALUES(null , 'zdd' , 1000), (null , 'zxx' ,1000); -- 开始事务 SET autocommit = 0; START TRANSACTION; -- 设置sql语句 UPDATE Stu set money = 1500 where stuName = 'zdd'; UPDATE Stu SET money = 500 WHERE stuName = 'zxx'; -- 提交 COMMIT; SELECT * FROM Stu; -- 设置回滚(此时zdd 1500; zxx 500) -- 开始事务 SET autocommit = 0; START TRANSACTION; -- 设置sql语句 UPDATE Stu set money = 1000 where stuName = 'zdd'; UPDATE Stu SET money = 1000 WHERE stuName = 'zxx'; ROLLBACK; --虽然设置了他们都是1000 , 但是并没有进行提交,而是进行了回滚到默认的状态
12.3.多个事务并发产生的问题
每启动一个 MySQL 程序,就会获得一个单独的数据库连接。每 个数据库连接都有一个全局变量 @@transaction_isolation
,表示当前的 事务隔离级别。
-
查看当前的隔离级别:
select @@transaction_isolation;
-
设置当前 MySQL 连接的隔离级别:
set session transaction isolation level read committed;
-
设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;
对于两个事务T1 , T2。
1.脏读:T1读取了已经被T2更新但是却没有提交的字段之后(此时T1读取的是更改的值) , 但时如果T2进行回滚,T1读取的内容就是临时无效的
2.不可重复读:当T1读取了一个字段 , 然后T2更新了该字段之后,T1再次读取同一个字段,值就不同了
3.幻读:T1从一个表中读取了一个字段 , 然后T2在该表中插入了新的行之后,如果T1再次读取同一个表就会出现多行
12.4.事务的隔离级别
1.read uncommitted
2.read committed
3.repeatable read
4.serializalbe
MySQL
中的隔离级别
√表示可以出现,×表示解决 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read uncommitted | √ | √ | √ |
read committed | × | √ | √ |
repeatable read | × | × | √ |
serializalbe | × | × | × |
12.5.savepoint
savepoint
+ 保存点名称,需要结合rollback使用,即回滚到保存点
事例:
DROP TABLE if EXISTS Stu;
create table if not exists Stu(
id int PRIMARY KEY AUTO_INCREMENT,
stuName varchar(10),
money INT
);
INSERT INTO Stu
VALUES(null , 'zdd' , 1000),
(null , 'zxx' ,1000);
-- 开始事务
SET autocommit = 0;
START TRANSACTION;
-- 设置sql语句
UPDATE Stu set money = 2000 where stuName = 'zdd';
SAVEPOINT a;
UPDATE Stu SET money = 3000 WHERE stuName = 'zxx';
-- 当回滚到保存点a的时候 , 只会执行保存点前面的,保存点后面的是不会执行的
ROLLBACK TO a;
SELECT * FROM Stu;
13.视图
视图:
一种虚拟存在的表,行和列的数据均来自自定义视图的查询中使用的表,并且是在使用视图时动态生成的,
只保存了sql逻辑,不保存查询结果
应用场景:
-
多个地方用到了同样的查询结果
-
该查询结果使用的sql语句较为复杂
视图的创建方法:
CREATE VIEW 视图名
AS
select语句
事例:
-- 如果经常使用含有id , last_name , department_id , department_name的表,可以为其创建一个视图
CREATE VIEW vvv
AS
SELECT employee_id , last_name ,e.department_id,department_name
FROM employees e
INNER JOIN departments d;
SELECT *
FROM vvv
WHERE last_name="K_ing";
13.1.视图的修改
--方法1
CREATE OR REPLACE VIEW 视图名
AS
SELECT 查询 ....
CREATE OR REPLACE VIEW vvv
AS
SELECT job_id , AVG(salary)
FROM employees
GROUP BY job_id;
--方法2
ALTER VIEW 视图名
AS
SELECT 查询 ....
ALTER VIEW vvv
AS
SELECT job_id , MIN(salary)
FROM employees
GROUP BY job_id;
13.2.视图的删除
-- 删除多个视图
drop view 视图名 , 视图名
13.3.测试
-- 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资,邮箱,电话
CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name , salary , email , phone_number
FROM employees
WHERE phone_number like '011%';
SELECT * FROM emp_v1;
-- 创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
CREATE OR REPLACE VIEW emp_v2
AS
SELECT d.*
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY e.department_id
HAVING MAX(salary) >12000;
SELECT * FROM emp_v2;
13.4.视图的更新
视图的可更新性和视图中查询的定义有关,以下类型的视图是不能更新的
包含以下关键字的sql语句:分组函数,distinct , group by , having , union或者是union all
常量视图
select中包含子查询
join
from一个不能更新的视图
where中的子查询引用了from子句中的表
更新语句 update , delete , insert
如果视图可更新 ,那么更新视图的时候,视图中所涉及到的表也会更新
13.5.视图和表的对比
创建的关键字 | 是否占用实际内存 | 使用 | |
---|---|---|---|
视图 | create view | 保存了sql的逻辑 | 增删改查(一般只使用查) |
表 | create table | 保存了数据 | 增删改查 |
14.测试
/*
1.创建book表,字段如下
bid 整形 要求主键
bname 字符型 要求设置唯一键,并非空
price 浮点型 要求默认值10
btypedId 类型编号,要求引用bookType表的id字段
已知bookType表,字段如下
id 主键
name 字符型 不为空
*/
CREATE TABLE bookType(
id INT PRIMARY KEY,
tName VARCHAR(20) NOT NULL
);
--
-- INSERT INTO bookType
-- VALUES(2 , '玄幻');
-- SELECT * from bookType;
CREATE TABLE Book(
bid INT PRIMARY KEY,
bname VARCHAR(10) unique NOT NULL,
price FLOAT DEFAULT(10),
btypeId INT,
FOREIGN KEY(btypeId) REFERENCES bookType(id)
);
-- DESC Book;
-- 2.开启事务
-- 向表中插入1行数据并结束
set autocommit = 0;
start TRANSACTION;
INSERT INTO Book
VALUES(1 , '斗破苍穹' , 18.0 , 2);
COMMIT;
-- SELECT * FROM Book;
-- 3.创建视图,实现查询价格大于100的书名和类型名
-- INSERT INTO Book
-- VALUES(2,'坏蛋是怎样炼成的' , 102 , 2),
-- (3 , '校花的贴身高手' , 200 ,2);
--
CREATE VIEW vvv1
AS
SELECT bname , tName
FROM Book
INNER JOIN bookType
ON Book.btypeId = bookType.id
WHERE price > 100;
-- SELECT * FROM vvv1;
-- 4.修改视图。实现查询价格在90-120之间的书名和价格
ALTER VIEW vvv1
AS
SELECT bname , price
FROM Booksql
WHERE price between 90 AND 120;
-- 5.删除上面的视图
DROP VIEW vvv1;