MySQL（六）----基础五

12.1.事务的属性

1. 原子性：

   原子性指：事务是一个不可分割的工作单位，食物中的操作要么全发生，要么什么都不发生。

2. 一致性：

   事务必须从一个一致性状态变换到另外一个一致性状态

3. 隔离性：

   一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

4. 永久性：

   事务一旦被提交，对数据库中的数据改变就是永久的，不能被撤销。接下来的其他操作和数据库故障不能对其造成影响

12.2.事务的创建

1. 隐式事务

   如updata , delete , insert

2. 显示事务(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逻辑，不保存查询结果

应用场景：

1. 多个地方用到了同样的查询结果

2. 该查询结果使用的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.视图的更新

视图的可更新性和视图中查询的定义有关，以下类型的视图是不能更新的

1. 包含以下关键字的sql语句：分组函数，distinct ， group by ， having ， union或者是union all

2. 常量视图

3. select中包含子查询

4. join

5. from一个不能更新的视图

6. 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;