多表设计

概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多
  • 多对多
  • 一对一

一对多

在一对多的关系中，一的一方为父表，多的一方为子表

外键约束

物理外键

  • 概念：使用foreign key定义外键关联另外一张表。

  • 缺点：

    • 影响增、删、改的效率（需要检查外键关系）。
    • 仅用于单节点数据库，不适用与分布式、集群场景。
    • 容易引发数据库的死锁问题，消耗性能。

外键语法

1
2
3
4
5
6
7
8
9
-- 创建表时指定
create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);

-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名);

逻辑外键

  • 概念：在业务层逻辑中，解决外键关联。
  • 通过逻辑外键，就可以很方便的解决物理外键的问题。

一对一

  • 案例：用户与身份证信息的关系
  • 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
  • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
  • 一对一可以看作特殊的一对多

多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多表查询

多表查询: 指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。(在多表查询时，需要添加条件消除无效的笛卡尔积)

给表起别名

1
select e.name,d.name from tb_emp e tb_dept d where e.dept id d.id;

连接查询

  • 内连接：相当于查询A、B交集部分数据
  • 外连接
    左外连接：查询左表所有数据（包括两张表交集部分数据）
    右外连接：查询右表所有数据（包括两张表交集部分数据）

内连接

  • 隐式内连接：select 字段列表 from 表1，表2 where 条件…;
  • 显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件…;

外连接

  • 左外连接：select 字段列表 from 表1 left [outer] join表2 on 连接条件…；
  • 右外连接：select 字段列表 from 表1 right [outer] join表2 on 连接条件…；

子查询

概述

  • 介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
  • 形式：select * from t1 where column1= (select column1 from t2 …);
  • 子查询外部的语句可以是insert/update/delete/select的任何一个，最常见的是select。

分类

  • 标量子查询：子查询返回的结果为单个值
  • 列子查询：子查询返回的结果为一列
  • 行子查询：子查询返回的结果为一行
  • 表子查询：子查询返回的结果为多行多列

标量子查询

  • 子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式

  • 常用的操作符：= <> > >= < <=

1
2
3
4
5
-- 查询工资大于平均工资的员工
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

列子查询

  • 子查询返回的结果是一列（可以是多行）
  • 常用的操作符：in、not in等
1
2
3
4
5
-- 查询属于特定部门的员工
SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

行子查询

  • 子查询返回的结果是一行（可以是多列）
  • 常用的操作符：=、<>、in、not in
1
2
3
4
5
-- 查询与某个指定员工工资和部门相同的员工
SELECT employee_id, employee_name
FROM employees
WHERE (salary, department_id) = (SELECT salary, department_id FROM employees WHERE employee_id = 101);

表子查询

  • 子查询返回的结果是多行多列，常作为临时表
  • 常用的操作符：in
1
2
3
4
5
6
7
-- 查询所有工资高于平均工资的员工及其部门名称
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_name FROM departments) d
ON e.department_id = d.department_id
WHERE e.salary > (SELECT AVG(salary) FROM employees);