MySQL的事务和视图

事务

TCL：Transaction Control Language 事务控制语言

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

  • 概念：在mysql中的数据用各种不同的技术存储在文件（或内存）中。
  • 通过show engines；来查看mysql支持的存储引擎。
  • 在mysql中用的最多的存储引擎有：innodb，myisam，memory等。其中innodb支持事务，而myisam、memory等不支持.

转账案例

张三丰转给郭襄500元

# 张三丰  1000
# 郭襄    1000

update 表 set 张三丰的余额=张三丰的余额-500 where name='张三丰'

#　中间发生意外，张三丰的余额少了500，而郭襄的余额并没有增加

update 表 set 郭襄的余额=郭襄的余额+500 where name='郭襄'

事物的特性( ACID )

  1. 原子性（Atomicity）

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

  2. 一致性（Consistency）

    事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性（Isolation）

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

  4. 持久性（Durability）

    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事务的创建

隐式事务

事务没有明显的开启和结束的标记，比如insert、update、delete语句

# 执行完表中id列为1的那一行数据就直接被删除了
delete from 表 where id =1;

显式事务

事务具有明显的开启和结束的标记

前提：必须先设置自动提交功能为禁用 set autocommit=0;

# 查看自动提交是否开启
SHOW VARIABLES LIKE 'autocommit';

# 查看数据库引擎
SHOW ENGINES;

开启事务步骤

  1. 开启事务

    # 关闭自动提交
set autocommit=0;

#　开启事物（可选）
start transaction;

  2. 编写事务中的sql语句（select、insert、update、delete）

  3. 可选：savepoint 节点名；设置回滚点

  4. 结束事务

    commit;提交事务
# 或者
rollback;回滚事务
# 或者
rollback to 回滚点名;回滚到指定的地方

并发事务

  1. 事务的并发问题是如何发生的？

    多个事务 同时 操作 同一个数据库的相同数据时

  2. 并发问题都有哪些？

    脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的

    不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了(在一个事物中不管读多少次，读取的数据应该都一样)

    幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行

  3. 如何解决并发问题

    通过设置隔离级别来解决并发问题

事务隔离级别

√：已解决 ×：未解决

隔离级别 脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √

mysql中默认 第三个隔离级别 repeatable read

oracle中默认第二个隔离级别 read committed

查看隔离级别

select @@tx_isolation;

设置隔离级别

# 设置当前 mySQL 连接的隔离级别: 
set transaction isolation level read committed;

# 设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;

案例

  1. 事务的使用步骤

    # 开启事务
SET autocommit=0;
START TRANSACTION;
# 编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';

# 结束事务
ROLLBACK;
# COMMIT;

SELECT * FROM account;

  2. 事务对于DELETE和TRUNCATE处理的区别

    SET autocommit=0;
START TRANSACTION;

DELETE FROM account;
ROLLBACK;

    TRUNCATE无法回滚

    SET autocommit=0;
START TRANSACTION;

TRUNCATE TABLE account;
ROLLBACK;

  3. savepoint的使用

    SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

SELECT * FROM account;

视图

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中 使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

应用场景

  • 多个地方用到同样的查询结果
  • 该查询结果使用的sql语句较复杂

优点

  1. 简化sql语句
  2. 提高了sql的重用性
  3. 保护基表的数据，提高了安全性

创建视图

create view 视图名
as
查询语句;

  1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

    CREATE VIEW emp_v1 AS SELECT
    last_name,
    salary,
    email 
FROM
    employees 
WHERE
    phone_number LIKE '011%';

  2. 创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

    # 创建视图，查询每个部门的最高工资,筛选出高于12000的
DROP VIEW emp_v2;
CREATE VIEW emp_v2 AS SELECT
department_id,
MAX( salary ) max_salary 
FROM
    employees 
GROUP BY
    department_id 
HAVING
    max_salary > 12000;

# 根据创建的视图连接departments表查询部门信息
SELECT
    d.*,
    ev2.max_salary 
FROM
    departments d
    JOIN emp_v2 ev2 
    ON d.department_id = ev2.department_id;

  3. 查询姓名中包含a字符的员工名、部门名和工种信息

    # 查询员工名、部门名和工种信息
CREATE VIEW emp_v3 AS SELECT
e.last_name,
d.department_name,
j.job_title 
FROM
    employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    LEFT JOIN jobs j ON e.job_id = j.job_id;

# 筛选出姓名中包含a字符的员工
SELECT * FROM emp_v3 WHERE last_name LIKE '%a%';

  4. 查询各部门的平均工资级别

    # 查询各部门的平均工资
CREATE VIEW emp_v4 AS SELECT
AVG( salary ) avg_salary,
department_id 
FROM
    employees 
GROUP BY
    department_id;

# 查询各部门的平均工资级别
SELECT
    department_id,
    j.grade_level 
FROM
    emp_v4 ev4
    LEFT JOIN job_grades j 
    ON ev4.avg_salary BETWEEN j.lowest_sal 
    AND j.highest_sal

  5. 查询平均工资最低的部门信息

    SELECT
    d.*,
    ev4.avg_salary 
FROM
    departments d
    JOIN ( SELECT * FROM emp_v4 ORDER BY avg_salary LIMIT 1 ) ev4 
    ON d.department_id = ev4.department_id

修改视图

方式一：

create or replace view  视图名
as
查询语句;

方式二：

alter view 视图名
as 
查询语句;

删除视图

drop view 视图名,视图名,...

DROP VIEW emp_v1,emp_v2,emp_v3,emp_v4;

查看视图

DESC 视图名;

SHOW CREATE VIEW 视图名;

CREATE VIEW emp_v1 AS SELECT
* 
FROM
    employees;

DESC emp_v1;

SHOW CREATE VIEW emp_v1;

视图的更新

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

  • 包含以下关键字的sql语句：分组函数、distinct、group by
  • having、union或者union all
  • 常量视图
  • Select中包含子查询
  • join
  • from一个不能更新的视图
  • where子句的子查询引用了from子句中的表

案例

  1. 张飞能否成功插入？

    不能，原表中没有annual salary那一列

    # 创建视图，查询员工的姓名，邮箱和年薪

CREATE 
    OR REPLACE VIEW myv1 AS SELECT
    last_name,
    email,
    salary * 12 *(1+IFNULL ( commission_pct, 0 )) "annual salary" 
FROM
    employees;

# 插入一条数据
INSERT INTO myv1 VALUES('张飞','zf@qq.com',94862.00);

  2. 张飞能否成功插入？

    能，并且原表中也存在

    # 创建视图，查询员工的姓名和邮箱

CREATE 
    OR REPLACE VIEW myv1 AS SELECT
    last_name,
    email
FROM
    employees;

# 插入一条数据
INSERT INTO myv1 VALUES('张飞','zf@qq.com');

  3. 能否将张飞修改为张无忌？

    能

    UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

  4. 能否干掉张无忌？

    能

    DELETE FROM myv1 WHERE last_name = '张无忌';

  5. 能否将10号部门的最高薪水改为9000？

    不能 ，包含group by和分组函数

    #　创建视图，查询每个部门的最高工资
CREATE 
    OR REPLACE VIEW myv1 AS SELECT
    MAX( salary ) m,
    department_id 
FROM
    employees 
GROUP BY
    department_id;

＃ 将10号部门的最高薪水改为9000
UPDATE myv1 SET m=9000 WHERE department_id=10;

  6. 能否更改？

    不能，常量视图

    CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

#更新
UPDATE myv2 SET NAME='lucy';

  7. 能够将最高工资列修改为100000？

    不能，select中包含子查询

    CREATE OR REPLACE VIEW myv3 AS 
SELECT department_id,( SELECT MAX( salary ) FROM employees ) 最高工资 
FROM
    departments;

# 修改

UPDATE myv3 SET 最高工资=100000;

  8. 修改和插入能成功吗？

    可以更新，但是不能插入

    # 创建视图，查询员工名与部门名
CREATE 
    OR REPLACE VIEW myv4 AS SELECT
    last_name,
    department_name 
FROM
    employees e
    JOIN departments d ON e.department_id = d.department_id;

# 修改
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
# 插入
INSERT INTO myv4 VALUES('陈真','Acc');

  9. 能修改吗？

    不能，from一个不能更新的视图，myv3中select使用了子查询

    CREATE 
    OR REPLACE VIEW myv5 AS SELECT
    * 
FROM
    myv3;

# 修改
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;

  10. 能修改吗？

    不能，where子句的子查询引用了from子句中的表

    # 查询所有的领导信息
CREATE OR REPLACE VIEW myv6
AS

SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
    SELECT  manager_id
    FROM employees
    WHERE manager_id IS NOT NULL
);

# 修改
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

    查询领导Id（将所有员工的上级Id查出来，这些Id就是领导Id）：

    SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL

视图和表的对比

关键字 是否占用物理空间 使用
视图 create view 占用较小，只保存sql逻辑 一般用于查询
表 create table 保存实际的数据 增删改查
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,793评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,567评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,342评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,825评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,814评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,680评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,033评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,687评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,175评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,668评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,775评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,419评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,020评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,978评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,206评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,092评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,510评论 2赞 343

推荐阅读更多精彩内容

  • mysql视图
    视图 /*含义：虚拟表，和普通表一样使用mysql5.1版本出现的新特性，是通过表动态生成的数据 比如：舞蹈班和普...
    majorty阅读 1,671评论 0赞 0
  • 我的139道MySQL题
    查询关键字： 1. 删除重复内容：distinct 2. 模糊查询：like 3. between and 4.筛...
    爱吃胡萝卜的小白兔阅读 709评论 0赞 0
  • mysql 视图
    视图 1、什么是视图 视图：MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图...
    养仙女的猪阅读 449评论 0赞 0
  • Mysql数据库
    基础查询 select 查询列表 from 表名; USE myemployees; //最好写上 1.查询表中的...
    暧莓悠阅读 571评论 0赞 1
  • 15天的OCP培训笔记
    第一天 7月13日OCP笔记： Oracle Ocp11g准备资料： OracleFundmentals 书 管理...
    fjxCode阅读 2,797评论 0赞 4