Mysql之多表查询

合集 - mysql(2)

1.Mysql2023-12-03

2.Mysql之多表查询2023-12-07

收起

关于Mysql的多表操作

表与表的关系

通常来说我们根据业务要求及其业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以我们将表与表的关系分为三类

1. 一对一
2. 一对多
3. 多对多

物理外键和逻辑外键

物理外键是值使用foreign key作为外键关联另一张表的字段的连接方法

物理外键删除一条信息，如果不把外键关联的信息同步删除，数据库操作会进行报错

逻辑外键删除一条信息，对应的信息可以不删除，保留，也不会报错

• 物理外键：强调数据完整性，操作中性能消耗比较大，小数据库量推荐

• 逻辑外键：不强制对应信息，只是在代码中体现，如果代码质量不完整，删除导致的垃圾数据会越来越多，大数据库量推荐

表与表的关系实现

1.一对一

通常指的是单表对应单表

是一种特殊的一对多，我们可以在任意一方表中添加外键

SQL脚本

-- 用户基本信息表
create table tb_user(
    id int unsigned  primary key auto_increment comment 'ID',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1 男  2 女',
    phone char(11) comment '手机号',
    degree varchar(10) comment '学历'
) comment '用户基本信息表';
-- 用户身份信息表
 
 
create table tb_user_card(
    id int unsigned  primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    birthday date not null comment '生日',
    idcard char(18) not null comment '身份证号',
    issued varchar(20) not null comment '签发机关',
    expire_begin date not null comment '有效期限-开始',
    expire_end date comment '有效期限-结束',
    user_id int unsigned not null unique comment '用户ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户身份信息表';

注意：在任意的一表加入外键，关联另外一表的主键，并且设置外键为唯一的 unique

2.一对多

列如一个人对应一个部门，而一个部门有很多个人，人与部门就构成了一对多的关系

• 具体实现

在一对多的表设计中，我们通常将对应 多的那张表设置外键关联另一张表的主键

3.多对多

列如一个学生有多个老师，一个老师有多个学生，n:m对应

• 具体实现

在多对多的表设计中我们我们通常会建立第三张表，中间表至少包含两个外键，分别关联两张表主键

多表查询

多表查询是指从多张表中获取所需数据

• 笛卡尔积

select * from 表1，表2;

如果我们这样查询，将会看到两表内的数据会自由组合。这种现象被称为笛卡尔积

指的是两个集合的所有组合情况

内连接

隐式内连接语法

select 字段 from 表1，表2 where 连接条件

显式内连接语法

select 字段 from 表1 join 表2 on 连接条件

注意

在多表查询的时候，如果两表有冲突相同的表名，我们通常会在字段名前面加上表名，来指定字段

外连接

外连接分为左外连接和右外连接

左外连接语法：（查询左表所有数据，包括表1和表2交集部分）

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

右外连接语法：（查询右表所有数据，包含表1和表2交集部分）

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

子查询

指的是select语句的嵌套查询，又称为子查询

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

子查询可以书写的位置

• where后
• from后（作为临时表）
• select后

插件--分页插件

在项目中，分页查询功能编写比较复杂，而分页查询的功能在项目中比较常见

分页查询的思路和步骤比较固定，在Mapper接口中定义两个方法执行两条不同的sql语句

1. 查询总记录
2. 指定页码的数据列表

获取的数据封装到pageBean对象中，一般为（总记录数，查询结果列表）

对于Mybatis来讲现在最主流的就是PageHelper。

依赖

<!--分页插件PageHelper-->
<dependency>
       <groupId>com.github.pagehelper</groupId>
       <artifactId>pagehelper-spring-boot-starter</artifactId>
       <version>1.4.7</version>
</dependency>

在sql语句中，语句正常书写

@Select("select e.*, d.name deptName from emp as e left join dept as d on e.dept_id = d.id")

但是依赖会将语句执行两遍

1. 将select后替换成count（）执行一遍
2. 在后面拼接上limit ?,? 进行一遍