mysql学习之旅途（五）

最新推荐文章于 2024-08-25 03:14:53 发布
最新推荐文章于 2024-08-25 03:14:53 发布
阅读量128 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43850162/article/details/108201941
版权

文章目录

事务

事务：事务由单独单元的一个或多个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
郭襄	1000

update 表 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
AS

SELECT 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.插入  结果1

INSERT 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 all

CREATE 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
AS

SELECT 'john' NAME;

SELECT * FROM myv2;

#更新
UPDATE myv2 SET NAME='lucy';### 结果3





#③Select中包含子查询

CREATE OR REPLACE VIEW myv3
AS

SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

#更新
SELECT * FROM myv3;
UPDATE myv3 SET 最高工资=100000;


#④join
CREATE OR REPLACE VIEW myv4
AS

SELECT 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
AS

SELECT * FROM myv3;

#更新

SELECT * FROM myv5;

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



#⑥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
);

#更新
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学习之旅途（四）
weixin_43850162的博客
08-23 278
文章目录库和表的管理常见的数据类型数值型字符型日期型常见约束 库和表的管理 #DDL /* 数据定义语言 库和表的管理 一、库的管理 创建、修改、删除 二、表的管理 创建、修改、删除 创建： create 修改： alter 删除： drop */ #一、库的管理 #1、库的创建 /* 语法： create database [if not exists]库名; */ #案例：创建库Books CREATE DATABASE IF NOT EXISTS books ; #2、库的修改
mysql学习之旅途（一）
weixin_43850162的博客
08-19 299
文章目录MySQL服务的启动和停止MySQL服务的登录和退出查看服务器的版本MySQL的语法规范SQL的语言分类基础查询条件查询排序查询常见函数 MySQL服务的启动和停止 方式一：计算机——右击管理——服务 方式二：通过管理员身份运行 net start 服务名（启动服务） net stop 服务名（停止服务） MySQL服务的登录和退出 方式一：通过mysql自带的客户端 只限于root用户 方式二：通过windows自带的客户端 登录： mysql 【-h主机名 -P端口号 】-u用户名 -p密码 -
mysql学习之旅途（二）
weixin_43850162的博客
08-21 248
文章目录分组函数分组查询sql中where和having的区别用的地方不一样执行的顺序不一样子句有区别总结连接查询sql99语法的连接查询join的七种理论 分组函数 #二、分组函数 /* 功能：用作统计使用，又称为聚合函数或统计函数或组函数 分类： sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数 特点： 1、sum、avg一般用于处理数值型 max、min、count可以处理任何类型 2、以上分组函数都忽略null值，因为null+任何值结果都为null,
mysql学习之旅途（六）
weixin_43850162的博客
08-25 128
文章目录变量 变量 #变量 /* 系统变量： 全局变量 会话变量：一个连接的变量 自定义变量： 用户变量 局部变量 */ #一、系统变量 /* 说明：变量由系统定义，不是用户定义，属于服务器层面 注意：全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别 使用步骤： 1、查看所有系统变量 show global|【session】variables; 2、查看满足条件的部分系统变量 show global|【session】 variables lik
mysql学习之旅途（三）
weixin_43850162的博客
08-22 391
文章目录子查询标量子查询多行子查询select查询from查询exists查询分页查询联合查询数据的增删改 子查询 标量子查询 #进阶7：子查询 /* 含义： 出现在其他语句中的select语句，称为子查询或内查询 外部的查询语句，称为主查询或外查询 分类： 按子查询出现的位置： select后面： 仅仅支持标量子查询 from后面： 支持表子查询 where或having后面：★ 标量子查询（单行） √ 列子查询 （多行） √ 行子查询 exists后面（
mysql学习之旅途（七）（高级部分）
weixin_43850162的博客
08-28 115
文章目录mysql逻辑架构解析存储引擎简介性能下降sql慢sql执行顺序 mysql逻辑架构解析 和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上， ==插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。==这种架构可以根据业务的需求和实际需要选择合适的存储引擎。 1.连接层 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授
MySQL数据库实例教程学习心得：一个憧憬又充满挑战的旅途
MeiYu__的博客
06-29 464
例如，使用 `SELECT` 语句查询数据，使用 `WHERE` 子句进行条件过滤，使用 `JOIN` 子句进行表连接。3. **合理使用连接**：在进行表连接时，尽量使用内连接（`INNER JOIN`），避免使用外连接（`LEFT JOIN`、`RIGHT JOIN`），因为外连接的性能较差。2. **索引的使用**：索引可以显著提高查询性能，但同时也会增加写操作的开销。4. **分页查询**：对于大数据量的查询，可以使用分页查询（`LIMIT` 子句）来减少一次性返回的数据量，提高查询效率。
mysql bigdecimal相加
weixin_40456928的博客
07-22 66
MySQL中的BigDecimal相加 在处理高精度数值计算时，我们经常会遇到需要对两个BigDecimal进行相加的情况。BigDecimal是一种在Java中用于表示大数值的类，它提供了一种机制来避免浮点数计算中的精度问题。然而，在MySQL数据库中，我们通常使用浮点数类型如FLOAT或DOUBLE来存储数值，这些类型...
Logstash日志写入Mysql数据库
weixin_36880837的博客
08-25 55
我整理的一些关于【MySQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkR使用Logstash将日志写入MySQL数据库的指南 随着数据量的不断增长，日志的管理与分析变得愈加重要。Logstash作为Elastic Stack的一部分，是一个强大的开源数据收集...
人在旅途旅游网站设计与实现：Java，Struts，MySQL技术应用
"人在旅途网站的设计与实现" 这篇毕业论文详细阐述了“人在旅途”旅游网站的设计与实现过程，旨在提供一个独特的在线旅游服务平台。...对于学习和研究旅游网站开发的学生或专业人士来说，这是一个有价值的参考资源。
基于C#+MySQL实现（WinForm）旅行记录本软件【100010443】
01-18
《基于C# + MySQL 实现的旅行记录本软件开发详解》 在当今信息化时代，旅行已经成为许多人生活的一...通过学习和理解这个项目的实现，开发者不仅可以掌握C#与MySQL的集成应用，还能对WinForm应用开发有更深入的认识。
MySQL基础：CharacterSet和Collation，netty常见面试题
2401_84434975的博客
04-21 541
1、看视频进行系统学习这几年的Crud经历，让我明白自己真的算是菜鸡中的战斗机，也正因为Crud，导致自己技术比较零散，也不够深入不够系统，所以重新进行学习是很有必要的。我差的是系统知识，差的结构框架和思路，所以通过视频来学习，效果更好，也更全面。关于视频学习，个人可以推荐去B站进行学习，B站上有很多学习视频，唯一的缺点就是免费的容易过时。2、读源码，看实战笔记，学习大神思路“编程语言是程序员的表达的方式，而架构是程序员对世界的认知”。所以，程序员要想快速认知并学习架构，读源码是必不可少的。
如何使用Navicat连接宝塔面板上安装的mysql数据库？
2401_84023683的博客
05-03 985
1、看视频进行系统学习这几年的Crud经历，让我明白自己真的算是菜鸡中的战斗机，也正因为Crud，导致自己技术比较零散，也不够深入不够系统，所以重新进行学习是很有必要的。我差的是系统知识，差的结构框架和思路，所以通过视频来学习，效果更好，也更全面。关于视频学习，个人可以推荐去B站进行学习，B站上有很多学习视频，唯一的缺点就是免费的容易过时。2、读源码，看实战笔记，学习大神思路“编程语言是程序员的表达的方式，而架构是程序员对世界的认知”。所以，程序员要想快速认知并学习架构，读源码是必不可少的。
【51 单片机】4 位密码锁（断电保存）
10-18
【51 单片机】4 位密码锁（断电保存）
冬奥会科普平台 JAVA毕业设计 源码+数据库+论文 Vue.js+SpringBoot+MySQL.zip
10-18
冬奥会科普平台 JAVA毕业设计 源码+数据库+论文 Vue.js+SpringBoot+MySQL 系统启动教程：https://www.bilibili.com/video/BV11ktveuE2d
算法部署-使用NCNN部署YOLOv9目标检测算法-支持Android平台的部署-优质算法部署项目实战.zip
10-18
算法部署_使用NCNN部署YOLOv9目标检测算法_支持Android平台的部署_优质算法部署项目实战
Linux笔记1111
10-18
Linux笔记1111
根据提供的文本处理转换成固定格式的文本工具
最新发布
10-18
根据提供的文本处理转换成固定格式的文本工具
毕业设计论文SpringBoot助农捐赠服务平台.docx
10-18
毕业设计论文
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值