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。

相关文章

Ubuntu中远程连接Mysql数据库的详细图文教程

Ubuntu中远程连接Mysql数据库的详细图文教程

《Ubuntu中远程连接Mysql数据库的详细图文教程》Ubuntu是一个以桌面应用为主的Linux发行版操作系统,这篇文章主要为大家详细介绍了Ubuntu中远程连接Mysql数据库的详细图文教程,有... 目录1、版本2、检查有没有mysql2.1 查询是否安装了Mysql包2.2 查看Mysql版本2.
阅读更多...
基于SpringBoot+Mybatis实现Mysql分表

基于SpringBoot+Mybatis实现Mysql分表

《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录基本思路定义注解创建ThreadLocal创建拦截器业务处理基本思路1.根据创建时间字段按年进
阅读更多...
Python3.6连接MySQL的详细步骤

Python3.6连接MySQL的详细步骤

《Python3.6连接MySQL的详细步骤》在现代Web开发和数据处理中,Python与数据库的交互是必不可少的一部分,MySQL作为最流行的开源关系型数据库管理系统之一,与Python的结合可以实... 目录环境准备安装python 3.6安装mysql安装pymysql库连接到MySQL建立连接执行S
阅读更多...
MySQL双主搭建+keepalived高可用的实现

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制，确认复制是否成功4.同
阅读更多...
MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL？1.2 动态SQL的优点1.3 动态S
阅读更多...
Mysql表的简单操作(基本技能)

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例：修改表在数据库中，表的操作主要
阅读更多...
mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言：第一步：第二步：第三步：总结：前言：当你想通过命令窗口想打开mysql时候发现提http://www.cpp
阅读更多...
MySQL大表数据的分区与分库分表的实现

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区？1.2 分区的类型1.3 分区的优点1.4 分
阅读更多...
MySQL错误代码2058和2059的解决办法

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》：本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法（敲重点！！！）1. php前置理解2058和2059的错误
阅读更多...
Mysql删除几亿条数据表中的部分数据的方法实现

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2