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 | SELECT * FROM employees,departments; |
内连接查询 Inner Join
内连接查询的是两张表交集的部分
隐式内连接:SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接(性能较高)SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
1 | -- 查询员工姓名,以及对应的部门名称 |
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 | -- left join |
sqltutorial-left-join
sqltutorial-full-outer-join
自连接查询 Self Join
当前表与自身的连接查询,自连接必须使用表别名;自连接查询,可以是内连接查询,也可以是外连接查询
1 | SELECT A.Name,B.Name |
联合查询 Union
把多次SELECT
查询的结果合并,形成一个新的查询集
1 | SELECT 字段列表 FROM 表A ... |
Note: UNION 会自动去重, UNION ALL 不会去重;联合查询比使用 or 效率高,不会使索引失效
1 | SELECT Name FROM employees UNION SELECT Name FROM departments; |
子查询 Subquery
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
在 SQL 中,子查询(Subquery)是嵌入在其他 SQL 查询中的查询,又称嵌套查询。
子查询可以用在各种 SQL 语句中,如 SELECT
、INSERT
、UPDATE
、DELETE
,以及在 WHERE
或 HAVING
子句中。
1 | SELECT EmployeeName, Salary |
根据子查询结果可以分为:
- 标量子查询(子查询返回值为单个值,数字,String,Date)
常用操作符:- < > > >= < <=
- 列子查询(子查询返回值为一列)
常用操作符:IN,NOT IN,SOME,ANY,ALL
(SOME
和ANY
是等价的,只要子查询返回的任意一个值满足条件即可,ALL
是所有值都满足条件才返回结果) - 行子查询(子查询返回值为一行)
常用操作符:=, <, >, IN, NOT IN
- 表子查询(子查询返回值为多行多列)
常用操作符:IN
1 | -- 标量子查询 |
根据子查询位置可分为:
- WHERE 之后
- FROM 之后
- SELECT 之后
事务 Transaction
Introduction
在 SQL 中,事务(Transaction)是作为单个逻辑工作单元执行的一系列操作,这些操作要么完全完成,要么完全不完成。事务是数据库完整性的关键,它确保了数据库从一个一致的状态转换到另一个一致的状态。
[Ch5-DbManagement#事务管理]
1 | -- Manual transaction |
ACID
- 原子性(Atomicity):事务中的所有操作要么全部完成,要么全部不完成。如果事务中的某个操作失败,那么整个事务将被回滚,数据库状态不会改变。
- 一致性(Consistency):事务确保数据库从一个一致的状态转换到另一个一致的状态。事务开始和结束时,所有的业务规则都必须保持一致。
- 隔离性(Isolation):每个事务都在一个独立的工作区中执行,事务之间不会互相影响。这意味着并发事务的修改必须被隔离,直到事务提交,其他事务才能看到这些修改。
- 持久性(Durability):一旦事务被提交,它对数据库的更改就是永久性的。即使发生系统故障,更改也不会丢失。
并发事务 Concurrent Transaction
事务并发问题
[Ch5-DbManagement#事务管理]
多用户并发访问数据库时,可能会出现以下问题:
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 | -- 设置事务隔离级别 |
DataBase Base 多表查询&事务