DB 多表查询&事务
发表Dev / DataBase16 分钟读完 (约2336字)Views:   

DB 多表查询&事务

多表查询 Multi-Table Query

多表关系

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联所以各个表结构之间也存在着各种联系,基本上分为三种：
一对一 one to one
如用户与用户详情的关系
多用于单表拆分,将一张表的基础字段和扩展字段分开存储,减少数据冗余,提高数据库性能.可在任意一方添加外键关联另一方的主键,并且外键字段设置 UNIQUE 约束.
一对多(多对一) one to many
如一个部门对应多个员工,一个员工只能对应一个部门.
这种关系一般是通过在多的一方添加外键来实现的.(员工表中设置部门 id 作为外键,指向部门表中的主键)
多对多 many to many
如一个学生可以选择多个课程,一个课程也可以被多个学生选择.
这种关系一般是通过添加第三张表来实现的.(学生表,课程表,学生课程表),中间表中设置学生 id 和课程 id 作为外键,指向学生表和课程表的主键.

为方便理解,以下以员工表和部门表为例进行说明.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 SELECT * FROM employees;
 +----+-------+--------------+
 | ID | Name  | DepartmentID |
 +----+-------+--------------+
 |  1 | John  |          100 |
 |  2 | Alice |          200 |
 |  3 | Bob   |          100 |
 |  4 | David |          300 |
 +----+-------+--------------+

 SELECT * FROM departments;
 +-----+-----------+
 | ID  | Name      |
 +-----+-----------+
 | 100 | Sales     |
 | 200 | Marketing |
 | 300 | HR        |
 +-----+-----------+

多表查询概述

合并查询(笛卡尔积,显示所有组合结果,不常用)
SELECT * FROM table1,table2;
消除无效组合(内连接)
SELECT * FROM employee, department WHERE employee.dept_id = department.id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT * FROM employees,departments;
+----+-------+--------------+-----+-----------+
| ID | Name  | DepartmentID | ID  | Name      |
+----+-------+--------------+-----+-----------+
|  1 | John  |          100 | 300 | HR        |
|  1 | John  |          100 | 200 | Marketing |
|  1 | John  |          100 | 100 | Sales     |
|  2 | Alice |          200 | 300 | HR        |
|  2 | Alice |          200 | 200 | Marketing |
|  2 | Alice |          200 | 100 | Sales     |
|  3 | Bob   |          100 | 300 | HR        |
|  3 | Bob   |          100 | 200 | Marketing |
|  3 | Bob   |          100 | 100 | Sales     |
|  4 | David |          300 | 300 | HR        |
|  4 | David |          300 | 200 | Marketing |
|  4 | David |          300 | 100 | Sales     |
+----+-------+--------------+-----+-----------+

SELECT * FROM employees,departments WHERE employees.DepartmentID = departments.ID;
+----+-------+--------------+-----+-----------+
| ID | Name  | DepartmentID | ID  | Name      |
+----+-------+--------------+-----+-----------+
|  1 | John  |          100 | 100 | Sales     |
|  2 | Alice |          200 | 200 | Marketing |
|  3 | Bob   |          100 | 100 | Sales     |
|  4 | David |          300 | 300 | HR        |
+----+-------+--------------+-----+-----------+

内连接查询 Inner Join

内连接查询的是两张表交集的部分

隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接(性能较高)
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查询员工姓名,以及对应的部门名称
-- 隐式
SELECT e.Name,d.Name FROM employees AS e, departments AS d WHERE e.DepartmentID = d.ID;
+-------+-----------+
| Name  | Name      |
+-------+-----------+
| John  | Sales     |
| Alice | Marketing |
| Bob   | Sales     |
| David | HR        |
+-------+-----------+
-- 显式
SELECT e.Name,d.Name FROM employees AS e INNER JOIN departments AS d ON e.DepartmentID = d.ID;
+-------+-----------+
| Name  | Name      |
+-------+-----------+
| John  | Sales     |
| Alice | Marketing |
| Bob   | Sales     |
| David | HR        |
+-------+-----------+

sql inner join 3 tables example
SQL Inner Join

1
2
3
4
5
6
7
8
9
10
11
12
 SELECT
 	first_name,
 	last_name,
 	job_title,
 	department_name
 FROM
 	employees e
 INNER JOIN departments d ON d.department_id = e.department_id
 INNER JOIN jobs j ON j.job_id = e.job_id
 WHERE
 	e.department_id IN (1, 2, 3);
SQL Inner Join

外连接查询 Outer Join

分为左外连接、右外连接、全外连接(Left Join, Right Join, Full Join)

左连接查询：查询左表的所有数据,右表中符合条件的数据
右连接查询：查询右表的所有数据,左表中符合条件的数据
全连接查询：查询两个表的所有数据

1
2
3
4
5
6
7
8
9
10
11
12
13
-- left join
SELECT e.Name,d.Name FROM employees AS e LEFT JOIN departments AS d ON e.DepartmentID = d.ID;
-- right join
SELECT e.Name,d.Name FROM departments AS d RIGHT JOIN employees AS e ON e.DepartmentID = d.ID;
-- 以上两句等价
+-------+-----------+
| Name  | Name      |
+-------+-----------+
| John  | Sales     |
| Alice | Marketing |
| Bob   | Sales     |
| David | HR        |
+-------+-----------+

sqltutorial-left-join
sqltutorial-full-outer-join

自连接查询 Self Join

当前表与自身的连接查询，自连接必须使用表别名;自连接查询，可以是内连接查询，也可以是外连接查询

1
2
3
4
5
6
7
8
9
SELECT A.Name,B.Name
FROM employees A
JOIN employees B
ON A.DepartmentID = B.DepartmentID && A.Name < B.Name;
+------+------+
| Name | Name |
+------+------+
| Bob  | John |
+------+------+

sqltutorial-self-join

联合查询 Union

把多次SELECT查询的结果合并，形成一个新的查询集

1
2
3
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...

Note: UNION 会自动去重, UNION ALL 不会去重;联合查询比使用 or 效率高，不会使索引失效

1
2
3
4
5
6
7
8
9
10
11
12
SELECT Name FROM employees UNION SELECT Name FROM departments;
+-----------+
| Name      |
+-----------+
| John      |
| Alice     |
| Bob       |
| David     |
| Sales     |
| Marketing |
| HR        |
+-----------+

子查询 Subquery

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
在 SQL 中，子查询（Subquery）是嵌入在其他 SQL 查询中的查询,又称嵌套查询。
子查询可以用在各种 SQL 语句中，如 SELECT、INSERT、UPDATE、DELETE，以及在 WHERE 或 HAVING 子句中。

1
2
3
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

根据子查询结果可以分为：

  • 标量子查询（子查询返回值为单个值,数字,String,Date）
    常用操作符：- < > > >= < <=
  • 列子查询（子查询返回值为一列）
    常用操作符:IN,NOT IN,SOME,ANY,ALL(SOME和ANY是等价的,只要子查询返回的任意一个值满足条件即可,ALL是所有值都满足条件才返回结果)
  • 行子查询（子查询返回值为一行）
    常用操作符:=, <, >, IN, NOT IN
  • 表子查询（子查询返回值为多行多列）
    常用操作符:IN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 标量子查询
SELECT * FROM employees WHERE Salary > (SELECT AVG(Salary) FROM employees);
-- 列子查询
SELECT *
FROM employees
WHERE Salary IN (SELECT Salary FROM employees WHERE DepartmentID = 100);

SELECT *
FROM employees
WHERE Salary > ALL (SELECT Salary FROM employees WHERE DepartmentID = 100);

SELECT *
FROM employees
WHERE Salary > ANY (SELECT Salary FROM employees WHERE DepartmentID = 100);
-- 行子查询
SELECT *
FROM employees
WHERE (salary, manager) = (SELECT salary,manager FROM employee WHERE name = 'xxx');
-- 表子查询
SELECT e.*, d.*
FROM (SELECT * FROM employees WHERE entrydate > '2006-01-01') AS e
LEFT JOIN dept AS d
ON e.dept = d.id;

根据子查询位置可分为：

  • WHERE 之后
  • FROM 之后
  • SELECT 之后

事务 Transaction

Introduction

在 SQL 中，事务（Transaction）是作为单个逻辑工作单元执行的一系列操作，这些操作要么完全完成，要么完全不完成。事务是数据库完整性的关键，它确保了数据库从一个一致的状态转换到另一个一致的状态。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Manual transaction
SET @@autocommit = 0;

SELECT * FROM employees WHERE ID = 1;
UPDATE employees SET Salary = 1000 WHERE ID = 1;
UPDATE employees SET Salary = 2000 WHERE ID = 2;
COMMIT;

-- 如果出现错误,可以使用 ROLLBACK 回滚事务
ROLLBACK;

-- 使用START/BEGIN TRANSACTION
START TRANSACTION; -- 或者 BEGIN;
statement1;
statement2;
COMMIT;

ACID

DefinitionDescription
Atomicity一个事务包含多个操作，这些操作要么全部执行，要么全都不执行
Consistency事务只能将数据库从一个有效状态转换到另一个有效状态
Isolation并发执行的事务之间相互隔离，
Durability一旦事务被提交，即使在系统故障（断电、崩溃等）情况下，它也将保持提交状态
Consistency In Transaction

This(Consistency）does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors cannot result in the violation of any defined database constraints.

一致性

1
2
3
4
5
6
7
8
9
10
11
 -- 定义一致性约束
 CREATE TABLE accounts (
     id INT PRIMARY KEY,
     balance DECIMAL NOT NULL CHECK (balance >= 0), -- 确保余额非负
     customer_id INT NOT NULL,
     FOREIGN KEY (customer_id) REFERENCES customers(id) -- 引用完整性
 );
 
 -- 尝试违反一致性的操作（会被拒绝）
 UPDATE accounts SET balance = -100 WHERE id = 1;

并发事务 Concurrent Transaction

Serialization Anomaly

The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.

dirty read

A transaction reads data written by a concurrent uncommitted transaction.

1
2
3
4
5
6
7
8
9
10
11
12
-- 事务A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时未提交

-- 事务B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读取到事务A未提交的数据
-- 基于这个可能不正确的余额做决策

-- 事务A
ROLLBACK; -- 撤销修改，事务B读取的数据变成了"脏数据"
nonrepeatable read

A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 事务A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读取余额为1000

-- 事务B
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 事务A
SELECT balance FROM accounts WHERE id = 1; -- 再次读取，余额变为900
-- 同一事务内两次读取结果不一致
COMMIT;
phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

1
2
3
4
5
6
7
8
9
10
11
12
-- 事务A
BEGIN;
SELECT * FROM accounts WHERE balance > 500; -- 返回5条记录

-- 事务B
BEGIN;
INSERT INTO accounts (id, name, balance) VALUES (6, 'Tom', 600);
COMMIT;

-- 事务A
SELECT * FROM accounts WHERE balance > 500; -- 返回6条记录，多了一条"幻影"记录
COMMIT;
lost update

Transaction A and Transaction B read and updates the same data, The update of Transaction A is lost because Transaction B overwrites it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 事务A和事务B同时读取账户余额1000
-- 事务A将余额增加100，变为1100
-- 事务B将余额减少200，变为800
-- 如果事务B后提交，最终余额为800，事务A的更新被丢失

-- 事务A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读取余额为1000
-- 计算新余额 1000 + 100 = 1100
UPDATE accounts SET balance = 1100 WHERE id = 1;
COMMIT;

-- 事务B（几乎同时）
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 读取余额为1000
-- 计算新余额 1000 - 200 = 800
UPDATE accounts SET balance = 800 WHERE id = 1;
COMMIT; -- 事务B的更新覆盖了事务A的更新

Transaction Isolation

Isolation LevelDirty ReadNonrepeatable ReadPhantom ReadSerialization Anomaly
Read uncommittedAllowed, but not in PGPossiblePossiblePossible
Read committedNot possiblePossiblePossiblePossible
Repeatable readNot possibleNot possibleAllowed, but not in PGPossible
SerializableNot possibleNot possibleNot possibleNot possible
1
2
3
4
5
6
7
8
9
10
-- 查询当前会话隔离级别
SELECT @@tx_isolation; -- mysql
SELECT current_setting('transaction_isolation'); -- postgresql


-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- <READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE>

Ref

postgresql-transaction-isolation-levels
zhihu

DB 多表查询&事务

https://vluv.space/transaction/

作者

GnixAij

发布于

2024-02-12

更新于

2025-06-27

许可协议

#

评论

目录

×