DataBase Base 多表查询&事务

多表查询 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

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

1
2
3
4
5
6
7
8
9
10
11

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

外连接查询 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        |
+-------+-----------+

自连接查询 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

• 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。如果事务中的某个操作失败，那么整个事务将被回滚，数据库状态不会改变。
• 一致性（Consistency）：事务确保数据库从一个一致的状态转换到另一个一致的状态。事务开始和结束时，所有的业务规则都必须保持一致。
• 隔离性（Isolation）：每个事务都在一个独立的工作区中执行，事务之间不会互相影响。这意味着并发事务的修改必须被隔离，直到事务提交，其他事务才能看到这些修改。
• 持久性（Durability）：一旦事务被提交，它对数据库的更改就是永久性的。即使发生系统故障，更改也不会丢失。

并发事务 Concurrent Transaction

事务并发问题

多用户并发访问数据库时,可能会出现以下问题：

• 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.

• dirty read
  A transaction reads data written by a concurrent uncommitted transaction.

• 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).

• 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.

• 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.

Transaction Isolation Level

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

1
2

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

Ref

postgresql-transaction-isolation-levels
zhihu