MySQL学习笔记

最新推荐文章于 2024-10-13 21:08:42 发布
最新推荐文章于 2024-10-13 21:08:42 发布
阅读量85 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/woshiabao666/article/details/115964988
版权

文章目录

一、SQL语句

1.1 SQL介绍

1.1.1 什么是SQL

	- Structured Query Language：结构化查询语言
	- 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”。

1.1.2 SQL的通用语法

- SQL 语句可以单行或多行书写，以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 数据库的注释：
  - 单行注释：-- 注释内容       #注释内容(mysql特有)
  - 多行注释：/* 注释内容 */

1.1.3 SQL分类

- DDL(Data Definition Language)数据定义语言
  - 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
  
- DML(Data Manipulation Language)数据操作语言
  - 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
  
- DQL(Data Query Language)数据查询语言
  - 用来查询数据库中表的记录(数据)。关键字：select, where 等
  
- DCL(Data Control Language)数据控制语言(了解)
  - 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

三、约束

3.1 外键约束

	让表和表之间产生关系，从而保证数据的准确性，主表的删除与从表的添加收到了限制
  • 建表时外键约束格式：
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
  • 删除外键约束：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
  • 建表后单独添加外键格式：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

3.2 级联更新与级联删除

	当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除
	在真实开发过程中，尽量避免使用，风险较高
  • 添加外键约束，同时添加级联更新
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;
  • 添加外键约束，同时添加级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
  • 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;

四、多表操作

4.1 一对多

	- 使用场景：
		用户和订单。一个用户可以有多个订单
		商品分类和商品。一个分类下可以有多个商品
	- 实现原则
		在多的一方，建立外键约束，来关联一的一方主键

-- 创建user表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);

-- 添加数据
INSERT INTO users VALUES(NULL,'张三'),(NULL,'李四');

-- 创建orderlist表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
uid INT,
-- 在多的一方，建立外键约束，来关联一的一方主键
CONSTRAINT out_fk1 FOREIGN KEY(uid) REFERENCES users(id) 
);

-- 创建category表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10)
);

-- 创建product表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255),
cid INT,
-- 在多的一方，建立外键约束，来关联一的一方主键
CONSTRAINT pc_fk1 FOREIGN KEY(cid) REFERENCES category(id)	
);

4.2 多对多

	- 案例分析：
		学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择
	- 实现原则：
		需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

# 创建学生表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);

# 创建课程表
CREATE TABLE course(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) 
);

-- 添加数据
INSERT INTO student VALUES (1,'张三'),(2,'李四');
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');

-- 创建中间表
CREATE TABLE stu_course(
id INT PRIMARY KEY AUTO_INCREMENT,
sid INT,
cid INT,
CONSTRAINT sc_fk1 FOREIGN KEY(sid) REFERENCES student(id),
CONSTRAINT sc_fk2 FOREIGN KEY(cid) REFERENCES course(id)
);

五、多表查询

5.1 笛卡尔积

	有两张表，获取这两个表的所有组合情况

-- 标准语法
SELECT 列名 FROM 表名1,表名2,...;

5.2 内连接查询

	内连接查询的是两张表有交集的部分数据(有主外键关联的数据)

-- 多表操作内连接查询
-- 查询用户信息与对应的订单信息
SELECT * FROM USER JOIN orderlist ON orderlist.uid = USER.id;

-- 查询用户的姓名，年龄，订单编号(显式连接查询)
SELECT
			u.name,
			u.age,
			o.number
FROM
			`USER` u
INNER JOIN
			orderlist o
ON 
			u.id = o.uid;
			
-- 隐式内连接查询
SELECT 
			u.name,
			u.age,
			o.number
FROM 
			`USER` u,
			orderlist o
WHERE 
			u.id = o.uid;

5.3 外连接查询

	- 左外连接查询：查询左表的全部数据，和左右两张表有交集部分的数据
	- 右外连接查询：查询右表的全部数据，和左右两张表有交集部分的数据

-- 查询用户的所有信息，以及用户对应的订单编号
SELECT
					U.*,
					O.number
FROM 	
					USER U
LEFT JOIN
					orderlist O
ON
					U.id = O.uid; 
					
-- 查询所有的订单信息，以及所属订单的用户姓名
SELECT 
				O.*,
				U.name
FROM 
				`USER` U
RIGHT JOIN
				orderlist O
ON 
				U.id = O.uid;

5.4 子查询

	查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询
  • 子查询-结果是单行单列的，可以作为条件，使用运算符进行判断
-- 子查询
-- 查询年龄最高的用户姓名
SELECT 
			U.name,
			U.age
FROM 
			USER U
WHERE 
			U.age = (SELECT MAX(age) FROM USER);
  • 子查询-结果是多行多列的，可以作为条件，使用运算符IN或NOT IN进行判断
-- 查询张三与李四的订单信息
SELECT 
			*
FROM
			orderlist O
WHERE
			uid IN (SELECT id FROM USER WHERE name IN ('张三','李四'));


-- 查询订单中id大于4的订单信息和所属用户角色
SELECT * FROM orderlist WHERE id > 4;

SELECT 
				U.name,
				temp.number
FROM
				(SELECT * FROM orderlist WHERE id > 4) temp,
			  USER U
WHERE
				U.id = temp.uid;

5.5 自关联查询

	同一张表中有数据关联。可以多次查询这同一个表

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析：
	员工姓名 employee表        直接上级姓名 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表交集部分数据，使用左外连接
*/
SELECT
	t1.name,	-- 员工姓名
	t1.mgr,		-- 上级编号
	t2.id,		-- 员工编号
	t2.name     -- 员工姓名
FROM
	employee t1  -- 员工表
LEFT OUTER JOIN
	employee t2  -- 员工表
ON
	t1.mgr = t2.id;

六、视图

6.1 视图的概念

  • 视图是一种虚拟存在的数据表
  • 这个虚拟的表并不在数据库中实际存在
  • 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
  • 视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

6.2 视图的优点

  • 简单：对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
  • 安全：视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
  • 数据独立： 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

6.3 视图的创建

  • 标准语法：
-- 标准语法
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
  • 代码演示：
-- 普通多表查询，查询城市和所属国家
SELECT
	t1.*,
	t2.country_name
FROM
	city t1,
	country t2
WHERE
	t1.cid = t2.id;

-- 创建一个视图。将查询出来的结果保存到这张虚拟表中
CREATE VIEW 
						city_country 
AS
						SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid = t2.id; 

-- 使用视图
SELECT * FROM city_country;

6.4 视图的修改与删除

  • 修改视图表中的数据
-- 标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;

-- 修改视图表中的城市名称北京为北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';

-- 查询视图
SELECT * FROM city_country;

-- 查询city表,北京也修改为了北京市
SELECT * FROM city;

-- 注意：视图表数据修改，会自动修改源表中的数据
  • 修改视图表结构
-- 标准语法
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;

-- 查询视图2
SELECT * FROM city_country2;

-- 修改视图2的列名city_id为id
ALTER
VIEW
	city_country2 (id,city_name,cid,country_name)
AS
	SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;
  • 视图的删除
-- 标准语法
DROP VIEW [IF EXISTS] 视图名称;

-- 删除视图
DROP VIEW city_country;

-- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

七、MySQL存储过程与函数

7.1 概念

	存储过程和函数是：事先经过编译并存储在数据库中的一段 SQL 语句的集合，函数必须有返回值，存储过程没有返回值
  • 存储过程和函数的优点：
    • 存储过程和函数可以重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
    • 减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
    • 减少数据在数据库和应用服务器之间的传输，可以提高数据处理的效率
    • 将一些业务逻辑在数据库层面来实现，可以减少代码层面的业务处理

7.2 创建存储过程

7.2.1 存储过程语法结构

-- 修改分隔符为$
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
	sql语句;
END$

-- 修改分隔符为分号
DELIMITER ;

7.2.2 调用存储过程

-- 标准语法
CALL 存储过程名称(实际参数);

7.2.3 代码操作

  • 将该语句作为一个存储过程
-- 按照性别进行分组，查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) FROM student GROUP BY gender ORDER BY SUM(score) asc;
  • 创建stu_group()存储过程，将上述查询语句封装
-- 创建存储过程
DELIMITER $

CREATE PROCEDURE stu_group()
BEGIN 
			SELECT gender,SUM(score) FROM student GROUP BY gender ORDER BY SUM(score) asc;
END $
DELIMITER;

-- 使用存储过程
CALL stu_group();
  • 存储过程的查看与删除
-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';

-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;

-- 删除stu_group存储过程
DROP PROCEDURE stu_group;

7.3 存储过程进阶语法

7.3.1 变量

  • 变量的定义
  • DECLARE定义的是局部变量，只能用在BEGIN END范围之内
-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内

-- 定义一个int类型变量、并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	DECLARE num INT DEFAULT 10;   -- 定义变量
	SELECT num;                   -- 查询变量
END$

DELIMITER ;

-- 调用pro_test1存储过程
CALL pro_test1();
  • 变量的赋值：方式一
-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量，并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE NAME VARCHAR(10);   -- 定义变量
	SET NAME = '存储过程';       -- 为变量赋值
	SELECT NAME;                -- 查询变量
END$

DELIMITER ;

-- 调用pro_test2存储过程
CALL pro_test2();
  • 变量的赋值：方式二
-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];

DELIMITER $

CREATE PROCEDURE pro_test01()
BEGIN
-- 定义一个变量
DECLARE men_grade INT;

-- 将男生的总成绩赋值给变量
SELECT SUM(grade) INTO men_grade FROM student WHERE gender = '男';

SELECT men_grade;
END $
DELIMITER;

CALL pro_test01;

7.3.2 if 语句

  • 标准语法
-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
  • 案例演示
/*
	定义一个int变量，用于存储班级总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上    学习优秀
		320 ~ 380     学习不错
		320以下       学习一般
*/

DELIMITER $

CREATE PROCEDURE pro_test02()
BEGIN
-- 定义变量
DECLARE total INT;
DECLARE info VARCHAR(15);
-- 查询并给变量赋值
SELECT SUM(grade) INTO total FROM student;

-- 循环判断
IF total >380 THEN
SET info = '成绩不错';
ELSEIF total >= 350 AND total <= 380 THEN
SET info = '成绩一般';
ELSEIF total < 350 THEN
SET info = '成绩很差';
END IF;

-- 查询总成绩与描述信息
select total,info;

END $

DELIMITER;

7.3.3 参数的传递

  • 参数传递的语法
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$
/*
	IN:代表输入参数，需要由调用者传递实际数据。默认的
	OUT:代表输出参数，该参数可以作为返回值
	INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;
  • 输入参数
    • 标准语法
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型)
BEGIN
	执行的sql语句;
END$

DELIMITER ;

	案例演示：

/*
	输入总成绩变量，代表学生总成绩
	定义一个varchar变量，用于存储分数描述
	根据总成绩判断：
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/

DELIMITER $
CREATE PROCEDURE pro_test03(IN total INT,OUT info varchar(15))
BEGIN
IF total >= 380 THEN
SET info = '成绩优秀';
ELSEIF total < 380 AND total >= 320 THEN
SET info = '成绩一般';
ELSEIF total < 320 THEN
SET info = '成绩较差';
END IF;
END $
DELIMITER;

-- 调用存储过程
CALL pro_test03(385,@info);
SELECT @info;

CALL pro_test03((SELECT SUM(grade) FROM student),@info);
SELECT @info;
  • @变量名: 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
  • @@变量名: 这种在变量前加上 “@@” 符号, 叫做系统变量

7.3.4 while 循环

  • while循环基本语法
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;
  • 案例演示
/*
	计算1~100之间的偶数和
*/


DELIMITER $

CREATE PROCEDURE pro_test04()
BEGIN
			-- 定义求和变量
			DECLARE total INT DEFAULT 0;
			-- 定义初始化变量
			DECLARE num INT DEFAULT 1;
			
			WHILE num <= 100 DO
			
				IF num % 2 = 0 THEN
					SET total = total + num;
				END IF;
				
				SET num = num + 1;
			END WHILE;
			
			-- 查询求和操作
			SELECT total;
END $

DELIMITER;

CALL pro_test04();

7.4 函数

7.4.1 概述

- 存储函数和存储过程是非常相似的。存储函数可以做的事情，存储过程也可以做到
- 存储函数有返回值，存储过程没有返回值(参数的out其实也相当于是返回数据了)

7.4.2 函数的语法格式

  • 创建存储函数
DELIMITER $

-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
	执行的sql语句;
	RETURN 结果;
END$

DELIMITER ;
  • 调用存储函数
-- 标准语法
SELECT 函数名称(实际参数);
  • 删除存储函数
-- 标准语法
DROP FUNCTION 函数名称;

7.4.3 案例演示

/*
	定义存储函数，获取学生表中成绩大于120分的学生数量
*/

set global log_bin_trust_function_creators=TRUE;
-- 删除函数
DROP FUNCTION fuc_test01;

DELIMITER $

-- 创建函数
CREATE FUNCTION fuc_test01()
RETURNS INT

BEGIN
			-- 定义变量
			DECLARE stu_count INT;
			-- 查询操作 赋值给变量
			SELECT COUNT(*) INTO stu_count FROM student where grade > 120;
			-- 返回统计结果
			RETURN stu_count;
END $

DELIMITER;

-- 调用函数
SELECT fuc_test01();

八、 MySQL触发器

8.1 触发器的概念

  • 触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句
  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发

触发器的分类：

触发器类型OLD的含义NEW的含义
INSERT 型触发器无 (因为插入前状态无数据)NEW 表示将要或者已经新增的数据
UPDATE 型触发器OLD 表示修改之前的数据NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据无 (因为删除后状态无数据)

8.2 创建触发器

标准语法：

DELIMITER $

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;
  • 创建日志表与账户表
-- 创建账户表account
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAR(20),					-- 姓名
	money DOUBLE						-- 余额
);

-- 创建日志表account_log
CREATE TABLE account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 日志id
	operation VARCHAR(20),				-- 操作类型 (insert update delete)
	operation_time DATETIME,			-- 操作时间
	operation_id INT,					-- 操作表的id
	operation_params VARCHAR(200)       -- 操作参数
);
  • 创建INSERT触发器
-- 创建INSERT触发器
DELIMITER $

CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;
  • 创建UPDATE触发器
-- 创建UPDATE触发器
DELIMITER $

CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$

DELIMITER ;
  • 创建DELETE触发器
-- 创建DELETE触发器
DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
	INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$

DELIMITER ;

8.3 对触发器的操作

  • 查看触发器
-- 标准语法
SHOW TRIGGERS;
  • 删除触发器
-- 标准语法
DROP TRIGGER 触发器名称;

8.4 触发器的总结

  • 触发器是与表有关的数据库对象
  • 可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句
  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容

九、MySQL事务

9.1 概述

  • 什么是事务：

一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。

9.2 事务的相关操作

  • 操作事务的三个步骤：
  1. 开启事务：记录回滚点，并通知服务器，将要执行一组操作，要么同时成功、要么同时失败
  2. 执行sql语句：执行具体的一条或多条sql语句
  3. 结束事务(提交|回滚)
    • 提交：没出现问题，数据进行更新
    • 回滚：出现问题，数据恢复到开启事务时的状态
  • 开启事务：
-- 标准语法
START TRANSACTION;
  • 回滚事务:
-- 标准语法
ROLLBACK;
  • 提交事务:
-- 标准语法
COMMIT;

事务的演示

-- 事务的演示

START TRANSACTION;

-- 转账演示
UPDATE account SET money = money - 500 WHERE `name` = '老王';

wrong...

UPDATE account SET money = money + 500 where `name` = '李四';

-- 回滚(执行出错时)
ROLLBACK;

-- 提交（没有问题时）
COMMIT;

9.3 事务的提交方式

  • 自动提交(MySQL默认为自动提交)

  • 手动提交

  • 查看提交方式:

-- 标准语法
SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交
  • 修改提交方式(不建议使用)
-- 标准语法
SET @@AUTOCOMMIT=数字;

-- 修改为手动提交
SET @@AUTOCOMMIT=0;

-- 查看提交方式
SELECT @@AUTOCOMMIT;

9.4 事务的四大特征(ACID)

  • 原子性(atomicity)
    • 原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响
  • 一致性(consistency)
    • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态
    • 拿转账来说，假设张三和李四两者的钱加起来一共是2000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是2000，这就是事务的一致性
  • 隔离性(isolcation)
    • 隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
  • 持久性(durability)
    • 持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

9.5 事务的隔离级别

9.5.1 隔离级别的概念

  • 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的
  • 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题
  • 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题

9.5.2 四种隔离级别

1读未提交read uncommitted
2读已提交read committed
3可重复读repeatable read
4串行化serializable

可能引发的问题：

问题现象
脏读是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功

9.5.3 事务隔离的相关操作

  • 查询数据库隔离级别
-- 标准语法
SELECT @@TX_ISOLATION;
  • 修改数据库隔离级别
-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- 查看隔离级别
SELECT @@TX_ISOLATION;   -- 修改后需要断开连接重新开

9.5.4 隔离级别总结

隔离级别名称出现脏读出现不可重复读出现幻读数据库默认隔离级别
1read uncommitted读未提交是是是
2read committed读已提交否是是Oracle / SQL Server
3repeatable read可重复读否否是MySQL
4**serializable **串行化否否否

注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.

9.6 事务总结

  • 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。例如转账操作

  • 开启事务：start transaction;

  • 回滚事务：rollback;

  • 提交事务：commit;

  • 事务四大特征

    • 原子性
    • 持久性
    • 隔离性
    • 一致性

  • 事务的隔离级别

    • read uncommitted(读未提交)
    • read committed (读已提交)
    • repeatable read (可重复读)
    • serializable (串行化)
woshiabao666
关注
专栏目录
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
尚硅谷MySQL学习笔记
热门推荐
IAMLSL的博客
02-11 2万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
mysql 学习笔记
javaTalk
01-23 3485
mysql 学习记录，包含日志、事务、索引、锁等功能。
MYSQL学习笔记
weixin_44134589的博客
04-04 1307
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 MYSQL 学习笔记（一）一、掌握概念库的增删改查1、系统数据库2、创建数据库2.1.语法2.2.数据库的命名规则3、数据库的相关操作二、存储引擎1、什么是存储引擎2、mysql 支持的存储引擎3、指定表的类型/存储引擎三、表的增删改查1、表的介绍2、创建表3、查看表的结构4、修改表的结构4.1.修改表名4.2.增加字段4.3.删除字段4.4.修改字段5、复制表 一、掌握概念库的增删改查 1、系统数据库 information_schem.
Mysql学习笔记
huangbaokang的博客
07-22 1138
查看mysql默认的存储引擎 show variable like 'storage_engine%'; 查看表的详情 show create table table_name \G mysql修改表名 alter table old_table_name rename [to] new_table_name mysql修改字段名字 alter table table_name change...
MySQL学习笔记8——游标
qq_44778824的博客
04-24 2220
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
MySQL学习笔记1：数据库概述（尚硅谷）
qq_43101414的博客
02-22 3576
MySQL学习系列1
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 520
数据库mysql学习笔记
m0_53853047的博客
09-18 1832
数据库mysql学习笔记
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
MySQL中表的约束
最新发布
pouop的博客
10-13 557
表中一定要有各种约束，通过约束，让我们来插入数据库中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员插入正确的数据；反过来，站在MySQL的角度来单，内部已经插进来的数据，都i是符合数据约束的数据。约束的最终目标：保证数据完整性和可预期性为了完成以上的所有目标，就需要除了数据类型自带的范围约束以外的更多约束。
【MySQL】入门篇—数据库基础：数据库的定义与用途
thinking_chou的专栏
10-10 1381
数据库（Database）是一个有组织的数据集合，通常以电子形式存储在计算机系统中。数据库管理系统（DBMS）是用于创建、管理和操作数据库的软件。数据库可以支持多种数据类型，如文本、数字、图像等，并允许用户通过查询语言（如SQL）对数据进行操作。二、数据库的用途数据库在社会中扮演着至关重要的角色，几乎每个行业都依赖于数据库来管理和分析数据。通过高效的数据存储和管理，企业和组织能够更好地理解客户需求、优化运营流程、提高决策能力，并在竞争中保持优势。
MySQL(B站CodeWithMosh)——2024.10.10(13)
unicorn_lemon的博客
10-10 1302
运算符：LENGTH、UPPER、LOWER、LTRIM、RTRIM、SUBSTRING、LOCATE、REPLACE、CONCAT、NOW、CURDATE、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、DAYNAME、EXTRACT、DATE_FORM、DATE_ADD、INTERVAL、IFNULL、COALESCE、IF
MySQL 基本概念
weixin_44965579的博客
10-10 953
MySQL 基本概念
MySQL数据的增删改查
2301_79985750的博客
10-09 1229
基于比较运算符和逻辑运算符，可以对复杂的条件进行过滤，从而达到要求，其中，区间查询，模糊查询以及null查询都是条件查询。限制查询结果集的条数，通过分页查询可以有效的控制一次查询出的结果集中的记录条数，可以有效的减少数据库的压力，同时对用户也比较友好。例如，查姓唐同学的成绩，%代表唐后面所有字符都被选中，类似还有%唐，%唐%，分别表示唐前面的字符被选中，唐左右的字符被选中。这是一个非常危险的操作，如果数据量太大，会消耗大量的内存和CPU资源，可能导致数据库服务器的性能下降。，特别是在多表查询的情况下。
MySQL学习笔记：DQL查询语句详解
"本篇是关于MySQL学习笔记的第六部分，主要关注DQL（Data Query Language），即查询语句的使用。通过创建一个名为`student02`的表，包含了id、NAME、age、sex、address、math和english等字段，并插入一些学生数据，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值