数据库｜SQL / MySQL的基本理论用法

article/2025/11/11 16:06:58

本文从数据库MySQL的数据类型、关系模型、增删改查语句、管理MySQL、实用SQL语句、事务等方面进行介绍。

数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

名称类型说明
INT整型4字节整数类型，范围约+/-21亿
BIGINT长整型8字节整数类型，范围约+/-922亿亿
REAL浮点型4字节浮点数，范围约+/-1038
DOUBLE浮点型8字节浮点数，范围约+/-10308
DECIMAL(M,N)高精度小数由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)定长字符串存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)变长字符串存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN布尔类型存储True或者False
DATE日期类型存储日期，例如，2018-06-22
TIME时间类型存储时间，例如，12:20:59
DATETIME日期和时间类型存储日期+时间，例如，2018-06-22 12:20:59

上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如JSON。

关系模型

主键

  1. 在关系数据库中，一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成的。例如，students表的两行记录：
idclass_idnamegenderscore
11小明M90
21小红F95

  1. 对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

  2. 对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

​ 所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

  1. 作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：
  • 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
  • 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型。

如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

  1. 联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，不允许为空，但可允许一列有重复，只要不是所有主键列都重复即可：

id_numid_typeother columns…
1A…
2A…
2B…

如果我们把上述表的id_num和id_type这两列作为联合主键，那么上面的3条记录都是允许的，因为没有两列主键组合起来是相同的。没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

外键

  1. 当我们用主键唯一标识记录时，我们就可以在students表中确定任意一个学生的记录：
idnameother columns…
1小明…
2小红…

我们还可以在classes表中确定任意一个班级记录：

idnameother columns…
1一班…
2二班…

但是我们如何确定students表的一条记录，例如，id=1的小明，属于哪个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

idclass_idnameother columns…
11小明…
21小红…
52小白…

这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

例如：

  • 小明的class_id是1，因此，对应的classes表的记录是id=1的一班；
  • 小红的class_id是1，因此，对应的classes表的记录是id=1的一班；
  • 小白的class_id是2，因此，对应的classes表的记录是id=2的二班。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。 比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。

  1. 外键并不是通过列名实现的，而是通过定义外键约束实现的：
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

  1. 由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

  2. 要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

  1. 还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

索引

  1. 在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
  2. 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
  3. 创建索引
--创建索引--
--a. 普通创建
CREATE INDEX indexName ON table_name (column_name)--b. 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)--c. 创建表的时候直接指定
CREATE TABLE mytable(  ID INT NOT NULL,   username VARCHAR(16) NOT NULL,  INDEX [indexName] (username(length))  );
  • 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。
  • 可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
  • 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。
  1. 删除索引
DROP INDEX [indexName] ON mytable;
  1. 唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

查询数据

为了便于讲解和练习，我们先准备好了一个students表和一个classes表，它们的结构和数据如下：

students表存储了学生信息：

idclass_idnamegenderscore
11小明M90
21小红F95
31小军M88
41小米F73
52小白F81
62小兵M55
72小林M85
83小新F91
93小王M89
103小丽F85

classes表存储了班级信息：

idname
1一班
2二班
3三班
4四班

下载sql脚本：https://github.com/michaelliao/learn-sql/blob/master/mysql/init-test-data.sql

基本查询

//基本查询
SELECT * FROM <表名>//SELECT语句其实并不要求一定要有FROM子句。
//计算100+200
SELECT 100+200;

虽然SELECT可以用作计算，但它并不是SQL的强项。但是，不带FROM子句的SELECT语句有一个有用的用途，就是用来判断当前到数据库的连接是否有效。许多检测工具会执行一条SELECT 1;来测试数据库连接。

条件查询

--条件查询
SELECT * FROM <表名> WHERE <条件表达式>--AND条件
SELECT * FROM students WHERE score >= 80 AND gender = 'M'--OR条件
SELECT * FROM students WHERE score >= 80 OR gender = 'M';--NOT条件，表示“不符合该条件”的记录；不是二班的学生
--NOT class_id = 2其实等价于class_id <> 2
SELECT * FROM students WHERE NOT class_id = 2; --多个条件要用小括号
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';--确定范围：BETWEEN...AND...  NOT BETWEEN...AND...
SELECT * FROM students WHERE score BETWEEN 80 AND  90;--确定集合：IN可以用来查找属性值属于指定集合的元组
select * from Websites where name in ('Google','菜鸟教程');
select * from Websites where name='Google' or name='菜鸟教程';-- 字符匹配：LIKE
-- 以小开头
SELECT * FROM students WHERE name LIKE "小%";
-- 以红结尾
SELECT * FROM students WHERE name LIKE "%红";
-- 包含小的所有记录
SELECT * FROM students WHERE name LIKE "%小%";
-- 选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户
SELECT * FROM Website WHERE name LIKE '_oogle';
-- 通配符
-- 使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
-- 选取gender以"F" 或 "M" 开始的所有学生
SELECT * FROM students
WHERE gender REGEXP '^[FM]';-- SELECT TOP, LIMIT, ROWNUM 子句
SELECT * FROM students LIMIT 2;

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

投影查询

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列，这种操作称为投影查询。

--从students表中返回id、score和name这三列
SELECT id, score, name FROM students;-- 使用投影查询，并将列名重命名：
SELECT id, score points, name FROM students;-- 使用投影查询+WHERE条件：
SELECT id, score points, name FROM students WHERE gender = 'M';

排序ORDER BY

--按score从低到高，默认ASC
SELECT id, name, gender, score FROM students ORDER BY score;--按score从高到低
SELECT id, name, gender, score FROM students ORDER BY score DESC;--按score降序，gender升序
SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;--如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。
SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。要实现分页功能，实际上就是从结果集中显示第1-100条记录作为第1页，显示第101-200条记录作为第2页，以此类推。因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过 LIMIT OFFSET 子句实现。

--分页查询--每页3条记录，获取第1页的记录
--注意SQL记录集的索引从0开始。
SELECT id, name, gender, score 
FROM students 
ORDER BY score DESC
LIMIT 3 OFFSET 0;--查询第二页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3; --LIMIT 3表示的意思是“最多3条记录”

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

  • LIMIT总是设定为pageSize；
  • OFFSET计算公式为pageSize * (pageIndex - 1)。

OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT OFFSET分页时，随着N越来越大，查询效率也会越来越低。

聚合查询

  1. 对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。
  2. COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。通常，使用聚合查询时，我们应该给列名设置一个别名，便于处理结果。

当聚合函数遇到空值时，除COUNT(* )外，都跳过空值而只处理非空值。

--查询students表一共有多少条记录
SELECT COUNT(*) FROM students;--聚合起别名
SELECT COUNT(*) num FROM students;--聚合+where条件
SELECT COUNT(*) boys FROM students WHERE gender = 'M';--男生平均成绩
SELECT AVG(score) average FROM students WHERE gender = 'M';--WHERE条件gender = 'X'匹配不到任何行，返回NULL
SELECT AVG(score) average FROM students WHERE gender = 'X';
  1. 除了COUNT()函数外，SQL还提供了如下聚合函数：
函数说明
SUM计算某一列的合计值，该列必须为数值类型
AVG计算某一列的平均值，该列必须为数值类型
MAX计算某一列的最大值
MIN计算某一列的最小值

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

  1. 分组聚合

如果我们要统计一班的学生数量，我们知道，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;。如果要继续统计二班、三班的学生数量，难道必须不断修改WHERE条件来执行SELECT语句吗？

--分组聚合，按class_id分组:
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;-- 按class_id, gender分组:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

多表查询

  1. 查询多张表的语法是：SELECT * FROM <表1> <表2>。
  2. 笛卡尔查询：
--从students表和classes表的“乘积”
SELECT * FROM students, classes;

查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

--多表查询
--区分结果集中的相同的名字，通过投影查询的设置列别名
SELECTstudents.id sid,students.name,students.gender,students.score,classes.id cid,classes.name cname
FROM students, classes;--SQL还允许给表设置一个别名，简洁操作
SELECTs.id sid,s.name,s.gender,s.score,c.id cid,c.name cname
FROM students s, classes c;--多表查询+WHERE条件
SELECTs.id sid,s.name,s.gender,s.score,c.id cid,c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询

  1. 连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
  2. 内连接——INNER JOIN
  • 先确定主表，仍然使用FROM <表1>的语法；
  • 再确定需要连接的表，使用INNER JOIN <表2>的语法；
  • 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
  • 可选：加上WHERE子句、ORDER BY等子句。
  1. RIGHT OUTER JOIN，LEFT OUTER JOIN以及FULL OUTER JOIN的区别是：
  • INNER JOIN只返回同时存在于两张表的行数据，由于students表的class_id包含1，2，3，classes表的id包含1，2，3，4，所以，INNER JOIN根据条件s.class_id = c.id返回的结果集仅包含1，2，3。
  • RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。
  • LEFT OUTER JOIN则返回左表都存在的行。如果我们给students表增加一行，并添加class_id=5，由于classes表并不存在id=5的行，所以，LEFT OUTER JOIN的结果会增加一行，对应的class_name是NULL：
--连接查询
--内连接INNER JOIN，选出所有学生，同时返回班级名称
SELECT s.id, s.name, s.class_id, c.name class_name,  s.gender, s.score 
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;--右外连接RIGHT OUTER JOIN，
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;
  1. 假设查询语句是：
SELECT ... FROM tableA ??? JOIN tableB ON tableA.column1 = tableB.column2;

我们把tableA看作左表，把tableB看成右表，那么INNER JOIN是选出两张表都存在的记录：

image

LEFT OUTER JOIN是选出左表存在的记录：

image

RIGHT OUTER JOIN是选出右表存在的记录：

image

FULL OUTER JOIN则是选出左右表都存在的记录：

image

多功能查询

  1. SELECT INTO 从一个表复制信息到另一个表
SELECT *
INTO WebsitesBackup2016
FROM Websites;

MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT 。

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

修改数据

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。

INSERT

--INSERT语句的基本语法：
INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);--向students表插入一条新记录
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);--一次性添加多条记录
INSERT INTO students (class_id, name, gender, score) VALUES(1, '大宝', 'M', 87),(2, '二宝', 'M', 81);SELECT * FROM students;

UPDATE

--UPDATE语句的基本语法是：
UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;--更新id=1的记录
UPDATE students SET name='大牛', score=66 WHERE id=1;
-- 查询并观察结果:
SELECT * FROM students WHERE id=1;--在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：
UPDATE students SET score=score+10 WHERE score<80;
-- 查询并观察结果:
SELECT * FROM students;--UPDATE语句可以没有WHERE条件,整个表的所有记录都会被更新
UPDATE students SET score=60;

DELETE

--DELETE语句的基本语法是：
DELETE FROM <表名> WHERE ...;--删除students表中id=1的记录
DELETE FROM students WHERE id=1;--删除多条记录
DELETE FROM students WHERE id>=5 AND id<=7;--不带WHERE条件的DELETE语句会删除整个表的数据
DELETE FROM students;

MySQL

  1. 安装完MySQL后，除了MySQL Server，即真正的MySQL服务器外，还附赠一个MySQL Client程序。MySQL Client是一个命令行客户端，可以通过MySQL Client登录MySQL，然后，输入SQL语句并执行。
  2. 打开命令提示符，输入命令mysql -u root -p，提示输入口令。填入MySQL的root口令，如果正确，就连上了MySQL Server，同时提示符变为mysql>

image.png

管理MySQL

1.数据库

  • 列出数据库：mysql> show databases;

image.png

其中，information_schema、mysql、performance_schema是系统库，不要去改动它们。其他的是用户创建的数据库。

  • 创建数据库：mysql> create database test1;
  • 删除数据库：mysql> drop database test1;
  • 切换为当前数据库：mysql> use study

image.png

2.表

  • 列出当前数据库的所有表：mysql> SHOW TABLES;
  • 查看一个表的结构：mysql> DESC students;

image.png

  • 查看创建表的SQL语句：mysql> SHOW CREATE TABLE students;

image.png

  • 删除表：mysql>DROP TABLE students;
  • 给表新增一列birth：mysql>ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;
  • 修改birth列：mysql> ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;
  • 删除列：mysql> ALTER TABLE students DROP COLUMN birthday;

实用SQL语句

  1. 插入或替换
-- 不管原来是否有这条记录，都将其插入进去，且保证只留一条记录。
-- 若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
  1. 插入或更新
-- 希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录。
-- 若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。
INSERT INTO students (id, class_id, name, gender, score) 
VALUES (1, 1, '小明', 'F', 99) 
ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
  1. 插入或忽略
-- 插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略。
-- 若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。
INSERT IGNORE INTO students (id, class_id, name, gender, score)
VALUES (1, 1, '小明', 'F', 99);
  1. 快照
-- 对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;
  1. 写入查询的结果集

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

CREATE TABLE statistics (id BIGINT NOT NULL AUTO_INCREMENT,class_id BIGINT NOT NULL,average DOUBLE NOT NULL,PRIMARY KEY (id)
);

然后，我们就可以用一条语句写入各班的平均成绩：

INSERT INTO statistics (class_id, average) 
SELECT class_id, AVG(score) 
FROM students 
GROUP BY class_id;

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

> SELECT * FROM statistics;
+----+----------+--------------+
| id | class_id | average      |
+----+----------+--------------+
|  1 |        1 |         86.5 |
|  2 |        2 | 73.666666666 |
|  3 |        3 | 88.333333333 |
+----+----------+--------------+
3 rows in set (0.00 sec)

事务

事务概述

  1. 在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：
-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。

  1. 这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

  2. 数据库事务的特性

  • A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  • C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

  1. 对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

  2. 要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为显式事务，例如，把上述的转账操作作为一个显式事务：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
  1. 有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;
  1. 隔离级别

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致的情况：

Isolation Level脏读（Dirty Read）不可重复读（Non Repeatable Read）幻读（Phantom Read）
Read UncommittedYesYesYes
Read Committed-YesYes
Repeatable Read--Yes
Serializable---

Read Uncommitted

  1. Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
  2. 举例，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：
时刻事务A事务B
1SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2BEGIN;BEGIN;
3UPDATE students SET name = ‘Bob’ WHERE id = 1;
4SELECT * FROM students WHERE id = 1;
5ROLLBACK;
6SELECT * FROM students WHERE id = 1;
7COMMIT;

当事务A执行完第3步时，它更新了id=1的记录，但并未提交，而事务B在第4步读取到的数据就是未提交的数据。

随后，事务A在第5步进行了回滚，事务B再次读取id=1的记录，发现和上一次读取到的数据不一致，这就是脏读。

可见，在Read Uncommitted隔离级别下，一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。

Read Committed

  1. Read Committed，顾名思义，就是读已提交，一个事务只能看到其他并发的已提交事务所作的修改。在Read Committed隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。
  2. 不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。
  3. 举例，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：
时刻事务A事务B
1SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2BEGIN;BEGIN;
3SELECT * FROM students WHERE id = 1;
4UPDATE students SET name = ‘Bob’ WHERE id = 1;
5COMMIT;
6SELECT * FROM students WHERE id = 1;
7COMMIT;

当事务B第一次执行第3步的查询时，得到的结果是Alice，随后，由于事务A在第4步更新了这条记录并提交。所以，事务B在第6步再次执行同样的查询时，得到的结果就变成了Bob，因此，在Read Committed隔离级别下，事务不可重复读同一条记录，因为很可能读到的结果不一致。

Repeatable Read

  1. Repeatable Read，顾名思义，可重复读，也即在一个事务范围内相同的查询会返回相同的数据。在Repeatable Read隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。
  2. 幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。
  3. 举例，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：
时刻事务A事务B
1SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2BEGIN;BEGIN;
3SELECT * FROM students WHERE id = 99;
4INSERT INTO students (id, name) VALUES (99, ‘Bob’);
5COMMIT;
6SELECT * FROM students WHERE id = 99;
7UPDATE students SET name = ‘Alice’ WHERE id = 99;
8SELECT * FROM students WHERE id = 99;
9COMMIT;

事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

Serializable

  1. Serializable，顾名思义，可串行化的，也即并发事务串行执行。很显然，该级别可以避免前面讲到的所有问题：“脏读”、“不可重复读”和“幻读”。代价是处理事务的吞吐量低，严重浪费数据库的性能，因此要慎用此事务隔离级别。
  2. 如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。
  3. 举例，假设当前事务隔离级别为Serializable，如果小明连接数据库去查询自己的成绩，由于还没有录入，因此没有成绩；这时小明的班主任王老师也连接数据库来录入成绩，可是他会卡在插入第一条成绩信息这里，只有当小明结束本次查询后，王老师才能插入完第一条成绩。如果小明久久不结束查询，还会导致王老师录入成绩超时。

附：如何卸载干净SQL Server：http://www.uzzf.com/news/26685.html

如果您觉得有收获的话，欢迎点赞评论加关注，互相学习进步，一直在路上~~


http://chatgpt.dhexx.cn/article/kMj5Rp6j.shtml

相关文章

KNN模型算法研究与案例分析

KNN模型算法研究与案例分析

KNN模型算法研究与案例分析( 白宁超 2018年8月29日15:39:13 ) 导读&#xff1a;机器学习算法中KNN属于比较简单的典型算法&#xff0c;既可以做聚类又可以做分类使用。本文通过一个模拟的实际案例进行讲解。整个流程包括&#xff1a;采集数据、数据格式化处理、数据分析、数据归…
阅读更多...
SQL总结

SQL总结

目录 简介 在Android中存储数据有时会用到数据库&#xff0c;Android给我们提供了 一系列的API来操作数据库&#xff0c;非常简单&#xff0c;我们只需要输入对应的SQL语句&#xff0c;甚至不懂SQL语句&#xff0c;只传入对应的参数即可使用。还有一些第三方库&#xff0c;如G…
阅读更多...
Windows开机启动项设置详解

Windows开机启动项设置详解

一、开机启动原理 Windows系统都有一个“启动”文件夹&#xff0c;把需要打开的程序的快捷方式或脚本放到“启动”文件夹里&#xff0c;就可以实现开机自启动。 启动”文件夹分为两种&#xff1a;“系统启动文件夹”和“用户启动文件夹”。 系统启动文件夹 Win10系统“启动”…
阅读更多...
「C#」设置开机启动

「C#」设置开机启动

自己写了个监控键盘按键的小程序。 在界面上实时显示按下的键&#xff0c;但是想实现程序的开机自启如何实现呢。 开机自启动一种是在windows的“C:\Users\用户名\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup”家快捷方式。但是这种方法自测有时候不成功。…
阅读更多...
centos7 设置开机启动项

centos7 设置开机启动项

高端的废话就是没有引言这种废话。 1.这里我已我的centos7为例输入&#xff1a; systemctl list-unit-files #查看开机启动表如图下&#xff1a; 最左边就是服务 &#xff0c;最右边就是状态 。如当你想要服务器开机启动firewalld&#xff08;防火墙&#xff09;输入 system…
阅读更多...
计算机怎么管理自启,电脑如何设置开机启动项

计算机怎么管理自启,电脑如何设置开机启动项

大家都知道将程序添加到开机启动项进入系统就可以自动打开了&#xff0c;但是有些流氓软件会强制进入开机启动项&#xff0c;这就导致电脑开机速度变慢&#xff0c;内存占用过多&#xff0c;运行卡顿的问题。下面&#xff0c;我就教大家如何设置开机启动 现在几乎家家户户都配备…
阅读更多...
电脑设置开机

电脑设置开机

我们知道电脑可以通过修改系统任务计划来实现定时关机的功能&#xff0c;那么&#xff0c;能不能让电脑实现自动开机的功能呢?答案是可以的&#xff0c;我们可以通过BIOS设置&#xff0c;指定电脑在某个时间点自动开机&#xff0c;接下来&#xff0c;小编来介绍一下如何通过BI…
阅读更多...
nginx 设置开机自启动

nginx 设置开机自启动

一、下载 在windows下实现开机自启动需要一个开源项目Windows Service Wrapper 来实现。 我用的是这个版本。 下载下来&#xff0c;放在Nginx根目录下 下载下来&#xff0c;放在Nginx根目录下&#xff0c;名字改为start-nginx.exe&#xff0c;再新建一个txt文件&#xff0c…
阅读更多...
Win11开机启动项怎么调整，Win11开机启动项怎么设置

Win11开机启动项怎么调整，Win11开机启动项怎么设置

Win11开机启动项怎么调整&#xff1f;Win11开机启动项怎么设置&#xff1f;现在很多应用或软件下载安装之后默认都是开机自启的&#xff0c;如果开机自启的软件多了的话难免会导致系统开机速度变慢。最近有使用win11系统的小伙伴就遇到了这个问题&#xff0c;有网友想了解怎么设…
阅读更多...
windows设置开机启动程序

windows设置开机启动程序

1.新建文件,填写路径 echo off cd F:\程序路径\ //后面填写3D所在的路径 F: //程序的个盘符 run.bat把这个文件填写完成后,改个名字,后缀改为bat,并把这个文件放在机房的程序目录下 2.设置开机计划任务 windows 7 在【开始】菜单中&#xff0c;输入【任务计划】&#xf…
阅读更多...
计算机软件自启动设置,设置开机启动项,详细教您电脑如何设置开机启动项

计算机软件自启动设置,设置开机启动项,详细教您电脑如何设置开机启动项

身边不少朋友买了电脑都在比拼电脑开机速度&#xff0c;一般新电脑开机启动时间保持在30s左右就已经很不错了&#xff0c;不过不少朋友电脑开机时间需要1分多钟&#xff0c;不少朋友的答案的设置开机启动项目&#xff0c;尽量减少程序开机启动&#xff0c;那么如何优化提升电脑…
阅读更多...
Windows 三种开机自启动的设置方式（全面）

Windows 三种开机自启动的设置方式（全面）

一、修改注册表的方式 进入注册表&#xff0c;点击任务栏开始&#xff0c;输入regedit.exe&#xff0c;进入[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run]位置后&#xff0c;新建一个二进制字符类型的项&#xff0c;数值数据位置填入所需开机自启动的应…
阅读更多...
linux设置开机启动脚本的3种方法

linux设置开机启动脚本的3种方法

Linux 下设置开机启动的三种简单但可行的开机启动设置方法。 方法一&#xff1a;修改 /etc/rc.d/rc.local 文件 /etc/rc.d/rc.local 文件会在 Linux 系统各项服务都启动完毕之后再被运行。所以你想要自己的脚本在开机后被运行的话&#xff0c;可以将自己脚本路径加到该文件里…
阅读更多...
Win11怎么设置开机启动项？

Win11怎么设置开机启动项？

我们在使用电脑的时候经常会打开非常多的软件&#xff0c;而每次开机都需要手动去点击&#xff0c;就会变得非常的麻烦&#xff0c;那么在Win11操作系统中我们应该怎么设置呢&#xff1f;其实方法非常简单&#xff0c;下面小编就带着大家一起看看吧&#xff01; 操作方法&#…
阅读更多...
WebLogic RMI 反序列化漏洞

WebLogic RMI 反序列化漏洞

文章目录 WebLogic RMI 反序列化基础知识影响版本漏洞概述漏洞危害漏洞复现目标机搭建漏洞环境漏洞验证端口探测 漏洞检测脚本在kali上运行命令下载反序列化漏洞利用工具 ysoserial启动 JRMPListener server执行反弹shell命令 使用python脚本发送payload下载 exploit脚本脚本执…
阅读更多...
常见的Web漏洞——反序列化漏洞

常见的Web漏洞——反序列化漏洞

目录 漏洞简介 漏洞成因 漏洞可能出现的位置 漏洞原理 Python反序列化漏洞实验 PHP反序列化漏洞实验 serialize函数输出格式 PHP中常用魔术方法 Java反序列化漏洞实验 FastJson反序列化漏洞简单实验 ASP.NET反序列化实验 防御方法 常用工具 参考文章 漏洞简介 序…
阅读更多...
shrio反序列漏洞修复_shiro反序列化漏洞

shrio反序列漏洞修复_shiro反序列化漏洞

shrio反序列化漏洞 一、漏洞介绍 Shiro 是 Java 的一个安全框架。Apache Shiro默认使用了CookieRememberMeManager，其处理cookie的流程是：得到rememberMe的cookie值 > Base64解码–>AES解密–>反序列化。然而AES的密钥是硬编码的，就导致了攻击者可以构造恶意数据造…
阅读更多...
如何挖掘反序列化漏洞

如何挖掘反序列化漏洞

文章目录 代码审计payload 1payload 2 平时挖的或者看到的反序列例子都很曲折&#xff0c;或者很个例&#xff0c;无法作为一个科普文。偶然看到大佬KeePassX的Java反序列化漏洞从入门到关门&#xff0c;发现这是一个难得一见的反序列化挖掘的好例子&#xff0c;它很舒畅&#…
阅读更多...
Shiro反序列化漏洞

Shiro反序列化漏洞

文章目录 一、Shrio反序列化导致命令执行&#xff08;Shiro-550 CVE-2016-4437&#xff09; 1.1 漏洞介绍 1.1.1 漏洞简介1.1.2 影响版本1.1.3 漏洞原理1.1.4 漏洞特征1.1.5 漏洞影响 1.2 环境配置1.3 漏洞利用 1.3.1 利用方法&#xff08;一&#xff09;1.3.2 利用方法&#x…
阅读更多...
PHP session反序列化漏洞

PHP session反序列化漏洞

PHP session反序列化漏洞 PHP session反序列化漏洞&#xff0c;就是当【序列化存储Session数据】与【反序列化读取Session数据】的方式不同导致session反序列化漏洞的产生 什么是session 官方Session定义&#xff1a;在计算机中&#xff0c;尤其是在网络应用中&#xff0c;称…
阅读更多...
推荐文章

Copyright @ 2022~2023