• MySQL——事务和视图

    2023.9.17

            本章开始介绍TCL语言（Transaction Control Language 事务控制语言）。

    事务

    事务的概念：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

    事务的特性：（ACID）

    • 原子性：一个事务不可再分割，要么都执行要么都不执行。
    • 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态。
    • 隔离性：一个事务的执行不受其他事务的干扰。
    • 持久性：一个事务一旦提交，则会永久的改变数据库的数据。

    事务的创建及使用步骤：
            隐式事务：事务没有明显的开启和结束的标记，如insert、update、delete语句。

            显式事务：事务具有明显的开启和结束的标记。前提：必须先设置自动提交功能为禁用。

    步骤1：开启事务

    set autocommit=0;
    start transaction;（可选）


    步骤2：编写事务中的sql语句(select insert update delete)

    语句1;
    语句2;
    ...

     步骤3：结束事务 

    commit;提交事务
    rollback;回滚事务 

    savepoint 的使用案例：

    1. SET autocommit=0;
    2. START TRANSACTION;
    3. DELETE FROM account WHERE id=25;
    4. SAVEPOINT a;#设置保存点
    5. DELETE FROM account WHERE id=28;
    6. ROLLBACK TO a;#回滚到保存点

    数据库的隔离级别：

            对于同时运行的多个事务，当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:

    • 脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段。之后,若T2回滚,T1读取的内容就是临时且无效的。
    • 不可重复读:对于两个事务T1, T2,T1读取了一个字段,然后T2更新了该字段。之后, T1再次读取同一个字段,值就不同了。
    • 幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行。之后,如果T1再次读取同一个表,就会多出几行。

            各个隔离级别：

                                         脏读        不可重复读    幻读
    read uncommitted：     √                  √                √
    read committed：          ×                 √                √
    repeatable read：          ×                  ×               √
    serializable：                 ×                  ×               ×

    mysql中默认 第三个隔离级别 repeatable read
    oracle中默认 第二个隔离级别 read committed
    查看隔离级别：

    select @@tx_isolation;

    设置隔离级别：

    set session|global transaction isolation level 隔离级别;

    视图：

    含义：可以理解为一张虚拟表，和普通表一样使用，用于保存一些临时表数据，方便再次使用。
    mysql5.1版本出现的新特性，是通过表动态生成的数据。

    视图与表的对比：

                创建语法的关键字     是否实际占用物理空间                       使用

    视图     create view         只是保存了sql逻辑（数据没有保存）      增删改查，只是一般不能增删改

    表        create table           保存了数据                                             增删改查

    视图的好处：
    ①重用sql语句。
    ②简化复杂的sql操作，不必知道它的查询细节。
    ③保护数据，提高安全性。

    相关笔记：

    1. #一、创建视图
    2. /*
    3. 语法：
    4. create view 视图名
    5. as
    6. 查询语句;
    7. */
    8.  
    9.  
    10. #1.查询姓名中包含a字符的员工名、部门名和工种信息
    11. #创建视图
    12. CREATE VIEW v1
    13. AS 
    14. SELECT `last_name`,`department_name`,`job_title`
    15. FROM `employees` e
    16. INNER JOIN `departments` d ON e.`department_id`=d.`department_id`
    17. INNER JOIN `jobs` j ON j.`job_id`=e.`job_id`; 
    18. #使用视图
    19. SELECT * FROM v1 WHERE `last_name` LIKE '%a%';
    20.  
    21.  
    22. #2.查询各部门的平均工资级别
    23. #创建视图查看每个部门的平均工资
    24. CREATE VIEW v2
    25. AS
    26. SELECT AVG(`salary`) ag ,`department_id`
    27. FROM `employees`
    28. GROUP BY `department_id`;
    29. #使用视图
    30. SELECT v2.ag,g.`grade_level`
    31. FROM v2
    32. INNER JOIN  `job_grades` g
    33. ON v2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;
    34.  
    35. #3.查询平均工资最低的部门信息
    36. #利用刚刚生成的视图v2
    37. SELECT * FROM v2 ORDER BY ag ASC LIMIT 1; 
    38.  
    39. #4.查询平均工资最低的部门名和工资
    40. #视图可以套娃
    41. CREATE VIEW v3
    42. AS 
    43. SELECT * FROM v2 ORDER BY ag ASC LIMIT 1; 
    44.  
    45. SELECT d.`department_name`,v.ag
    46. FROM v3 v
    47. INNER JOIN `departments` d
    48. ON v.`department_id`=d.`department_id`;
    49.  
    50. #二、视图的修改
    51.  
    52. #方式一：
    53. /*
    54. create or replace view  视图名
    55. as
    56. 查询语句;
    57. */
    58.  
    59. CREATE OR REPLACE VIEW myv3
    60. AS
    61. SELECT AVG(salary),job_id
    62. FROM employees
    63. GROUP BY job_id;
    64.  
    65. #方式二：
    66. /*
    67. 语法：
    68. alter view 视图名
    69. as 
    70. 查询语句;
    71. */
    72. ALTER VIEW myv3
    73. AS
    74. SELECT * FROM employees;
    75.  
    76. #三、删除视图
    77.  
    78. /*
    79. 语法：drop view 视图名,视图名,...;
    80. */
    81.  
    82. DROP VIEW v1,v2,v3;
    83.  
    84. #四、查看视图
    85. DESC myv3;
    86.  
    87. SHOW CREATE VIEW myv3;
    88.  
    89. #五、视图的更新
    90. CREATE OR REPLACE VIEW myv1
    91. AS
    92. SELECT last_name,email
    93. FROM employees;
    94.  
    95. #1.插入
    96.  
    97. INSERT INTO myv1 VALUES('张飞','zf@qq.com');
    98.  
    99. #2.修改
    100. UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
    101.  
    102. #3.删除
    103. DELETE FROM myv1 WHERE last_name = '张无忌';
    104.  
    105. #具备以下特点的视图不允许更新
    106.  
    107.  
    108. #①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all
    109.  
    110. CREATE OR REPLACE VIEW myv1
    111. AS
    112. SELECT MAX(salary) m,department_id
    113. FROM employees
    114. GROUP BY department_id;
    115.  
    116. SELECT * FROM myv1;
    117.  
    118. #更新
    119. UPDATE myv1 SET m=9000 WHERE department_id=10;
    120.  
    121. #②常量视图
    122. CREATE OR REPLACE VIEW myv2
    123. AS
    124.  
    125. SELECT 'john' NAME;
    126.  
    127. SELECT * FROM myv2;
    128.  
    129. #更新
    130. UPDATE myv2 SET NAME='lucy';
    131.  
    132.  
    133.  
    134.  
    135.  
    136. #③Select中包含子查询
    137.  
    138. CREATE OR REPLACE VIEW myv3
    139. AS
    140.  
    141. SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
    142. FROM departments;
    143.  
    144. #更新
    145. SELECT * FROM myv3;
    146. UPDATE myv3 SET 最高工资=100000;
    147.  
    148.  
    149. #④join
    150. CREATE OR REPLACE VIEW myv4
    151. AS
    152.  
    153. SELECT last_name,department_name
    154. FROM employees e
    155. JOIN departments d
    156. ON e.department_id  = d.department_id;
    157.  
    158. #更新
    159.  
    160. SELECT * FROM myv4;
    161. UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
    162. INSERT INTO myv4 VALUES('陈真','xxxx');
    163.  
    164.  
    165.  
    166. #⑤from一个不能更新的视图
    167. CREATE OR REPLACE VIEW myv5
    168. AS
    169.  
    170. SELECT * FROM myv3;
    171.  
    172. #更新
    173.  
    174. SELECT * FROM myv5;
    175.  
    176. UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;
    177.  
    178.  
    179.  
    180. #⑥where子句的子查询引用了from子句中的表
    181.  
    182. CREATE OR REPLACE VIEW myv6
    183. AS
    184.  
    185. SELECT last_name,email,salary
    186. FROM employees
    187. WHERE employee_id IN(
    188. 	SELECT  manager_id
    189. 	FROM employees
    190. 	WHERE manager_id IS NOT NULL
    191. );
    192.  
    193. #更新
    194. SELECT * FROM myv6;
    195. UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

  • 相关阅读:
    Springboot启动流程
    Linux 日志管理
    HCM系统的五大功能
    Linux安装Anaconda(Anaconda3-2022.10-Linux-x86_64.sh版本)
    使用OpenCVSharp利用PictrueBox对接摄像头获取视频图像
    Hyperf微服务——五、JsonRpc远程调用
    ZYNQ图像处理项目——线性神经网络识别mnist
    Node.js、Chrome V8 引擎、非阻塞式I/O介绍
    抖音微短剧小程序源码搭建：实现巨量广告数据高效回传
    2022 计网复习简答题【太原理工大学】
  • 原文地址：https://blog.csdn.net/m0_61028090/article/details/132945200