mysql学习之旅途（五）

2023-12-12 16:20
文章标签 mysql 学习 database 旅途

本文主要是介绍mysql学习之旅途（五），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

      • 事务
          • 存储引擎
          • 事务的ACID特性
          • 演示事务的隔离级别
            • 演示read uncommitted
            • 演示read committed
            • 演示repeatable read
      • 视图

事务

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

存储引擎

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

事务的ACID特性
  1. 原子性（Atomicity）
    原子性是指事务是一个不可分割的工作单位，事务中的操作要么
    都发生，要么都不发生。
  2. 一致性（Consistency）
    事务必须使数据库从一个一致性状态变换到另外一个一致性状态
    。
  3. 隔离性（Isolation）
    事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个
    事务内部的操作及使用的数据对并发的其他事务是隔离的，并发
    执行的各个事务之间不能互相干扰。
  4. 持久性（Durability）
    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是
    永久性的，接下来的其他操作和数据库故障不应该对其有任何影
    响
#TCL
/*
Transaction Control Language 事务控制语言事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。案例：转账张三丰  1000
郭襄	1000update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'show engines;
事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句delete from 表 where id =1;显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用set autocommit=0;步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)###create alter delete 属于DDL语言，没有事务之说。
语句1;
语句2;
...步骤3：结束事务
commit;提交事务
rollback;回滚事务savepoint 节点名;设置保存点对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。事务的隔离级别：脏读		不可重复读	幻读
read uncommitted：√		√		√
read committed：  ×		√		√
repeatable read： ×		×		√
serializable	  ×    ×        ×mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'update 表 set 郭襄的余额=1500 where name='郭襄' 
结束事务的语句;*/SHOW VARIABLES LIKE 'autocommit';#自动提交
SHOW ENGINES;#1.演示事务的使用步骤DROP TABLE IF EXISTS accounts;
CREATE TABLE account(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(20),balance DOUBLE
) ;
INSERT INTO account(username,balance)
VALUES('张无忌',1000),('赵敏',1000);
SELECT * FROM account;###结果一#开启事务   ### 结果二
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 500 WHERE username='张无忌';
UPDATE account SET balance = 1500 WHERE username='赵敏';
#结束事务
COMMIT;
SELECT * FROM account;#开启事务  ### 结果三
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';
#结束事务
ROLLBACK;
SELECT * FROM account;#2.演示事务对于delete和truncate的处理的区别SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
#delete可以回滚，truncate不可以回滚。#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;

结果一：
在这里插入图片描述
结果二：
在这里插入图片描述
结果三：
在这里插入图片描述
2.演示事务对于delete和truncate的处理的区别
演示delete
在这里插入图片描述
在delete之后，rollback之前，
在这里插入图片描述在rollback之后，
在这里插入图片描述
演示truncate
rollback之后，
在这里插入图片描述
3.演示savepoint 的使用
在这里插入图片描述
在这里插入图片描述

演示事务的隔离级别

在这里插入图片描述
通过命令行方式进行：
select @@tx_isolation;查询事务的默认隔离级别。
在这里插入图片描述
设置事务的隔离级别。
set session transaction isolation level read uncommitted;
在这里插入图片描述
更改字符集。set names gbk;
在这里插入图片描述
T1黄色字体，T2绿色字体。

演示read uncommitted

当对表进行更新操作时候，但此时并没有进行提交。注意 在更改前要添加set autocommit=0;
在这里插入图片描述
然后另一个事务读取数据,读取数据前设置隔离级别，set session transaction isolation level read uncommitted;然后加上set autocommit=0;
在这里插入图片描述
此时若T1事务进行回滚，
在这里插入图片描述
此时T2再进行查看，
在这里插入图片描述

演示read committed

此时并没有进行提交，
在这里插入图片描述
但此时读取到的是john,所以避免了脏读。
在这里插入图片描述
此时进行提交，可以发现，没有避免不可重复读的问题。
在这里插入图片描述
在这里插入图片描述

演示repeatable read

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
可以看到避免了不可重复读问题。
验证repeatable read不能避免幻读问题
可以看到此时有两行数据，
在这里插入图片描述
当T2数据进行插入并提交后，T1会出现三行更改的现象。
在这里插入图片描述
在这里插入图片描述
=验证serializable可以避免幻读问题
在这里插入图片描述
会发现T2事务回车后会阻塞，直到T1提交完成。
在这里插入图片描述
在这里插入图片描述
当前指的是当前连接，只对当前连接有效，若新打开一个连接，需要重新设置。新打开一个询问还是一个连接
全局对所有连接有效，需要重启。

视图

MySQL从5.0.1版本开始提供视图功能。一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。视图中的数据并没有占据磁盘空间
应用场景
多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂
视图的好处
重用sql语句
简化复杂的sql操作，不必知道它的查询细节
保护数据，提高安全性

#视图
/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据比如：舞蹈班和普通班级的对比创建语法的关键字	是否实际占用物理空间	使用视图	create view		只是保存了sql逻辑	增删改查，只是一般不能增删改表	create table		保存了数据		增删改查*/#案例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`
WHERE s.`stuname` LIKE '张%';CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`= m.`id`;SELECT * FROM v1 WHERE stuname LIKE '张%';#一、创建视图
/*
语法：
create view 视图名
as
查询语句;*/
USE myemployees;#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1
ASSELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id  = d.department_id
JOIN jobs j ON j.job_id  = e.job_id;#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';#2.查询各部门的平均工资级别  结果一#①创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;#②使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;#3.查询平均工资最低的部门信息SELECT * FROM myv2 ORDER BY ag LIMIT 1;### 结果2#4.查询平均工资最低的部门名和工资CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;SELECT d.*,m.ag     ###结果3
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;#二、视图的修改#方式一：
/*
create or replace view  视图名
as
查询语句;*/
SELECT * FROM myv3 CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;#方式二：
/*
语法：
alter view 视图名
as 
查询语句;*/
ALTER VIEW myv3
AS
SELECT * FROM employees;#三、删除视图/*语法：drop view 视图名,视图名,...;
*/DROP VIEW myv1,myv2,myv3;#四、查看视图DESC myv3;SHOW CREATE VIEW myv3;

视图创建
结果一：
在这里插入图片描述
select * from myv2;结果
在这里插入图片描述
结果2：
在这里插入图片描述
结果3：
在这里插入图片描述
select* from myv3;结果
在这里插入图片描述
视图修改
方式一
select* from myv3
在这里插入图片描述
再次select* from myv3
在这里插入图片描述
查看视图
SHOW CREATE VIEW myv3;
在SQLyog显示的结果，
在这里插入图片描述
在命令行中，显示的比较全。
在这里插入图片描述

#五、视图的更新CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;SELECT * FROM myv1;
SELECT * FROM employees;
#1.插入  结果1INSERT INTO myv1 VALUES('张飞','zf@qq.com');#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';### 对原始表也进行了更新。#3.删除
DELETE FROM myv1 WHERE last_name = '张无忌';### 对原始表也进行了删除#具备以下特点的视图不允许更新#①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union allCREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;SELECT * FROM myv1;#更新
UPDATE myv1 SET m=9000 WHERE department_id=10;### 结果2#②常量视图
CREATE OR REPLACE VIEW myv2
ASSELECT 'john' NAME;SELECT * FROM myv2;#更新
UPDATE myv2 SET NAME='lucy';### 结果3#③Select中包含子查询CREATE OR REPLACE VIEW myv3
ASSELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;#④join
CREATE OR REPLACE VIEW myv4
ASSELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;#更新SELECT * FROM myv4;
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';#可以更新
INSERT INTO myv4 VALUES('陈真','xxxx');#不可以插入#⑤from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
ASSELECT * FROM myv3;#更新SELECT * FROM myv5;UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;#⑥where子句的子查询引用了from子句中的表CREATE OR REPLACE VIEW myv6
ASSELECT last_name,email,salary
FROM employees
WHERE employee_id IN(SELECT  manager_idFROM employeesWHERE manager_id IS NOT NULL
);#更新
SELECT * FROM myv6;
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

SELECT * FROM myv1;
在这里插入图片描述
结果1：
在这里插入图片描述
SELECT * FROM employees;原始表中也插入了数据。
在这里插入图片描述
具备以下特点的视图不允许更新
SELECT * FROM myv1;
在这里插入图片描述
结果2：
在这里插入图片描述
SELECT * FROM myv2;
在这里插入图片描述
结果3：
在这里插入图片描述
SELECT * FROM myv4;
在这里插入图片描述
可以更新
在这里插入图片描述
不可以插入：
在这里插入图片描述

这篇关于mysql学习之旅途（五）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/485213。

相关文章

Springboot中分析SQL性能的两种方式详解

Springboot中分析SQL性能的两种方式详解

《Springboot中分析SQL性能的两种方式详解》文章介绍了SQL性能分析的两种方式：MyBatis-Plus性能分析插件和p6spy框架,MyBatis-Plus插件配置简单,适用于开发和测试环... 目录SQL性能分析的两种方式:功能介绍实现方式:实现步骤:SQL性能分析的两种方式:功能介绍记录
阅读更多...
使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置（可选）启动服务应用场景
阅读更多...
oracle DBMS_SQL.PARSE的使用方法和示例

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包，它允许动态地构建和执行
阅读更多...
SQL 中多表查询的常见连接方式详解

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接（INNERJOIN）、左连接（LEFTJOIN）、右连接（RIGHTJOIN）、全外连接（FULLOUTER... 目录一、连接类型图表（ASCII 形式）二、前置代码（创建示例表）三、连接方式代码示例1. 内连接（I
阅读更多...
在MySQL执行UPDATE语句时遇到的错误1175的解决方案

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式（SafeUpdateMode）限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式（Safe Upd
阅读更多...
Java深度学习库DJL实现Python的NumPy方式

Java深度学习库DJL实现Python的NumPy方式

《Java深度学习库DJL实现Python的NumPy方式》本文介绍了DJL库的背景和基本功能,包括NDArray的创建、数学运算、数据获取和设置等,同时,还展示了如何使用NDArray进行数据预处理... 目录1 NDArray 的背景介绍1.1 架构2 JavaDJL使用2.1 安装DJL2.2 基本操
阅读更多...
轻松上手MYSQL之JSON函数实现高效数据查询与操作

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》：本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE
阅读更多...
MySql死锁怎么排查的方法实现

MySql死锁怎么排查的方法实现

《MySql死锁怎么排查的方法实现》本文主要介绍了MySql死锁怎么排查的方法实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录前言一、死锁排查方法1. 查看死锁日志方法 1：启用死锁日志输出方法 2：检查 mysql 错误
阅读更多...
MySQL数据库函数之JSON_EXTRACT示例代码

MySQL数据库函数之JSON_EXTRACT示例代码

《MySQL数据库函数之JSON_EXTRACT示例代码》：本文主要介绍MySQL数据库函数之JSON_EXTRACT的相关资料,JSON_EXTRACT()函数用于从JSON文档中提取值,支持对... 目录前言基本语法路径表达式示例示例 1: 提取简单值示例 2: 提取嵌套值示例 3: 提取数组中的值注意
阅读更多...
MySQL修改密码的四种实现方式

MySQL修改密码的四种实现方式

《MySQL修改密码的四种实现方式》文章主要介绍了如何使用命令行工具修改MySQL密码,包括使用`setpassword`命令和`mysqladmin`命令,此外,还详细描述了忘记密码时的处理方法,包... 目录mysql修改密码四种方式一、set password命令二、使用mysqladmin三、修改u
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2