Mysql基础知识学习

前言

最近发现对SQL的大部分知识都有点忘记了，平台SQL语句也写的少，平时都是通过ORM实现的（SpringDataJPA），以前也是学过SQL语句，但是时间一久就都忘了差不多了，这个主要是让自己有点印象，在写代码的时候，使用多表查询，经常用到左连接进行查询，由于手写SQL已经是很久的事情了，所以这篇文章对这些知识进行补充补充

数据库左连接、右连接、内连接、全连接区别

left join （左连接）：左表的所有记录都会出现在结果中，不管右表中是否有匹配的记录。
right join （右连接）：右表的所有记录都会出现在结果中，不管左表中是否有匹配的记录。
inner join （等值连接或者叫内连接）：仅返回两张表中连接条件匹配的记录。
full join （全外连接）：返回左表和右表中的所有记录。如果在其中一张表中没有找到匹配的行，另一张表的字段会用 NULL 填充。

创建表

创建 students 表（学生表）

CREATE TABLE students (
    id INT PRIMARY KEY,  -- 学生 ID（主键）
    name VARCHAR(100)    -- 学生姓名
);

创建 courses 表（课程表）

CREATE TABLE courses (
    id INT PRIMARY KEY,  -- 课程 ID（主键）
    student_id INT,      -- 学生 ID（外键，关联学生表）
    course_name VARCHAR(100),  -- 课程名称
    FOREIGN KEY (student_id) REFERENCES students(id)  -- 外键约束
);

插入数据

INSERT INTO students (id, name) VALUES
(1, 'yunzhi'),
(2, 'zhangsan'),
(3, 'lisi'),
(4, 'mengyunzhi');

INSERT INTO courses (id, student_id, course_name) VALUES
(1, 1, '计算机网络'),
(2, 1, '计算机组成原理'),
(3, null, '数据结构'),
(4, null, '计算机操作系统');

1、内联接

目标：查询选修了课程的学生及其课程。

SELECT students.name AS student_name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

执行结果：

student_name	course_name
yunzhi	计算机网络
zhangsan	计算机组成原理

2、左连接

查询所有学生及他们选修的课程。如果学生没有选修课程，则课程名显示为 NULL。

SELECT students.name AS student_name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;

执行结果

student_name	course_name
yunzhi	计算机网络
zhangsan	计算机组成原理
lisi	null
mengyunzhi	null

3、右连接

查询所有课程及选修该课程的学生。如果课程没有被学生选修，则学生姓名显示为 NULL。

SELECT students.name AS student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

执行结果

student_name	course_name
yunzhi	计算机网络
zhangsan	计算机组成原理
null	数据结构
null	计算机操作系统

4、全连接

全连接使用FULL JOIN或FULL OUTER JOIN关键字来表示。MySQL不直接支持全连接，但可以通过左连接和右连接的组合来模拟实现。

查询所有学生和所有课程，无论学生是否选修课程，课程是否被选修。如果没有匹配的记录，显示为 NULL。

SELECT DISTINCT student_name, course_name
FROM (
    SELECT students.name AS student_name, courses.course_name
    FROM students
    LEFT JOIN courses ON students.id = courses.student_id

    UNION ALL

    SELECT students.name AS student_name, courses.course_name
    FROM students
    RIGHT JOIN courses ON students.id = courses.student_id
) AS combined_result;

执行结果

student_name	course_name
yunzhi	计算机网络
zhangsan	计算机组成原理
lizi	null
mengyunzhi	null
null	数据结构
null	计算机操作系统

连接方式总结对比

连接类型	返回的结果	说明
内连接	仅返回两表中满足条件的记录	两表中有匹配的记录才会出现在结果中。
左连接	返回左表所有记录和右表中匹配的记录	左表的所有记录都会出现在结果中，右表中没有匹配的用 NULL 填充。
右连接	返回右表所有记录和左表中匹配的记录	右表的所有记录都会出现在结果中，左表中没有匹配的用 NULL 填充。
全连接	返回左表和右表的所有记录，匹配的记录会合并	无论左右表是否匹配，所有记录都会出现在结果中，未匹配的用 NULL 填充。

MySQL 事务隔离级别

MySQL 支持四种标准的事务隔离级别，每种隔离级别都定义了事务之间的可见性以及对并发操作的处理方式。

隔离级别	脏读	不可重复读	幻读
读未提交 (Read Uncommitted)	是	是	是
读已提交 (Read Committed)	否	是	是
可重复读 (Repeatable Read)	否	否	是
串行化 (Serializable)	否	否	否

创建accounts表

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

INSERT INTO accounts (id, balance) VALUES (1, 1000.00);

1：读未提交（Read Uncommitted）

事务 A 开始并查询账户余额。
事务 B 开始并更新账户余额，但尚未提交。
事务 A 在事务 B 提交之前，读取到事务 B 尚未提交的更新。

设置隔离级别为读未提交（Read Uncommitted）

-- 事务 A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;

-- 事务 B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = 1200.00 WHERE id = 1;
-- 事务 B 尚未提交

-- 事务 A
SELECT balance FROM accounts WHERE id = 1;

这里我们可以看到当前事务B没有进行提交，但是获取到未提交的值

2：读已提交 (Read Committed)

事务 A 开始并查询账户余额。
事务 B 开始并更新账户余额，并提交更改。
事务 A 在事务 B 提交之后再次读取余额。

-- 会话 A: 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;

-- 会话 B: 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
UPDATE accounts SET balance = 1200.00 WHERE id = 1;
-- 未提交

COMMIT;

未提交查询的值还是1000

提交后

3：可重复读（Repeatable Read）

事务 A 开始并查询账户余额。
事务 B 开始并更新账户余额，并提交更改。
事务 A 在事务 B 提交之后再次读取余额，余额不会发生变化。

-- 会话 A: 
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;

-- 会话 B: 
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE accounts SET balance = 1200.00 WHERE id = 1;
COMMIT;

当会话 B进行修改后提交，但是会话A提前开启动，没有进行commit，查询的值还是原来的值

-- 会话 A: 
SELECT balance FROM accounts WHERE id = 1;

3：串行化 (Serializable)

事务 A 开始并查询账户余额。
事务 B 开始并试图更新账户余额。
在串行化隔离级别下，事务 B 必须等待事务 A 完成，才能修改数据。

-- 会话 A: 
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;

-- 会话 B
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
UPDATE accounts SET balance = 1200.00 WHERE id = 1;

如果会话A未提交，执行会话而会出错，必须等待会话A进行提交之后才能执行会话B

1205 - Lock wait timeout exceeded; try restarting transaction（1205 -锁等待超时；尝试重新启动事务）

总结

以前也学习过这些知识，只是长时间没有接触，基本都忘的七七八八了，这篇还是为了以后自己忘了看看，不过重新学的时候挺快的，学过一遍之后再学其实挺轻松的