数据库多表查询

1、多表设计

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

• 一对多(多对一)

• 多对多

• 一对一

1.1 一对多

1.1.1 表设计

1.1.2 外键约束

外键约束：让两张表的数据建立连接，保证数据的一致性和完整性

对应的关键字：foreign key

外键约束的语法：

-- 创建表时指定
create table 表名(
    字段名    数据类型,
    ...
    [constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名)    
);
​
​
-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key(外键字段名) references 主表(主表列名);

物理外键和逻辑外键

• 物理外键

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

  • 缺点：

    • 影响增、删、改的效率（需要检查外键关系）。

    • 仅用于单节点数据库，不适用与分布式、集群场景。

    • 容易引发数据库的死锁问题，消耗性能。

• 逻辑外键

  • 概念：在业务层逻辑中，解决外键关联。

  • 通过逻辑外键，就可以很方便的解决上述问题。

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范中，会明确指出禁止使用物理外键 foreign key。

2.2 一对一

一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。

一对一 ：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

2.3 多对多

多对多的关系在开发中属于也比较常见的。比如：学生和课程的关系，一个学生可以选修多门课程，一个课程也可以供多个学生选修。

案例：学生与课程的关系

• 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

• 实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

2、多表查询

多表查询：查询时从多张表中获取所需数据。

要执行多表查询，只需要使用逗号分隔多张表即可，语法：

select  字段列表   from   表1 , 表2 ;

3、分类

多表查询可以分为：

1、连接查询

• 内连接：相当于查询A、B交集部分数据

        

2、外连接

• 左外连接：查询左表所有数据(包括两张表交集部分数据)

• 右外连接：查询右表所有数据(包括两张表交集部分数据)

3、子查询

3.1 内连接

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为：

• 隐式内连接

• 显式内连接

隐式内连接语法：

select  字段列表   from   表1 , 表2   where  条件 ... ;

显式内连接语法：

select  字段列表   from   表1  [ inner ]  join 表2  on  连接条件 ... ;

多表查询时给表起别名：

• tableA as 别名1 , tableB as 别名2 ;

• tableA 别名1 , tableB 别名2 ;

注意事项:

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

3.2 外连接

外连接分为两种：左外连接 和 右外连接。

左外连接语法结构：

select  字段列表   from   表1  left  [ outer ]  join 表2  on  连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法结构：

select  字段列表   from   表1  right  [ outer ]  join 表2  on  连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。而在日常开发使用时，更偏向于左外连接。

3.3 子查询

3.3.1 介绍

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 ... );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值[一行一列]）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

1. where之后

2. from之后

3. select之后

3.3.2 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。

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

3.3.3 列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。

常用的操作符：

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内

3.3.4 行子查询

子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

3.3.5 表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。