SQL多表连接查询
一，数据的完整性约束
1）概述：
数据的完整性约束从单表、多表的角度又分为：单表约束和多表约束。
2）单表约束：
2.1> 主键约束
2.2> 唯一约束
2.3> 非空约束
3）多表约束：
1> 外键约束：用来保证（多表之间）数据完整性。
二，添加外键的SQL语句：
学生表(主表)
CREATE TABLE student(
sid int primary key,
name varchar(50) not null,
sex varchar(10) default '男'
);
成绩表（从表）
create table score(
id int,
score int,
sid int ,
constraint foreign key(sid) references student(sid)
);
-- PS：外键的字段的数据类型一定要与主键的类型一致；
三）多表查询概述：
1> 合并结果集：
union、union all
2> 连接查询：
2.1> 内连接：
inner join on xxx=yyy
2.2> 外连接：
outer join on xxx=yyy
2.3> 左外连接：
left outer join on xxx=yyy
2.4> 右外连接：
right outer join on xxx=yyy
3）内连接：
SQL标准的内连接为：
select * from emp e
inner join dept d
on e.deptno=d.deptno;
**注意：on后面是：连接条件；**
**内连接的特点：就是两边的表必须都满足查询条件。**
4）左外连接：
包括左外连接和右外连接，外连接的特点：查询出的结果中掺杂有 不满足条件的数据。
**左外连接：以左表为主表，右表为从表；**
select * from emp e
left outer join dept d
on e.deptno=d.deptno;
左连接是先查询出左表（即以左表为主），然后查询右表，**左表中满足条件和不满足条件的数据都会显示出来**，**右表中不满足条件的数据会显示为NULL**。
5）右外连接
右外连接就是： 先把右表中所有记录都查询出来（无论满足条件还是不满足条件），然后左表 满足条件的数据 显示出来，不满足则显示NULL。
**右外连接：右表为主表，左表是从表；**
select * from emp e
right outer join dept d
on e.deptno=d.deptno;
6）连接子查询：
示例： 查询工资高于 JONES 的员工。
结果：
SELECT * FROM emp
WHERE sal >
(SELECT sal FROM emp WHERE ename='JONES');
7）内连接和外连接的区别：
1》左外连接：
以左表为主表，右表为从表；
左外连接是，先查询出左表（即以左表为主），然后查询右表，
左表中满足条件和不满足条件的数据都会显示出来，右表中不满足条件的数据会显示为NULL。
2》右外连接：
以右表为主表，左表是从表；
右外连接是，先查询出右表（即以右表为主），然后查询左表，
先把右表中所有记录都查询出来（无论满足条件还是不满足条件），然后左表中满足条件的数据会显示出来，不满足则显示NULL。
3》外连接的特点：
外连接包括 左外连接和右外连接，外连接的特点：查询出的结果中掺杂有 不满足条件的数据。
4》内连接的特点：
相当于左连接与右连接的合并，**内连接会去掉所有含NULL的数据行**，剩下的全是按照条件查询出来的数据。
内连接其实就是**两边的表都必须满足条件**。
四，多表查询详述
1）前言：
本文主要列举两张和三张表来讲述多表连接查询。
新建两张表：
表1：student 截图如下：
表2：course 截图如下：
2）外连接
外连接可分为：**左连接、右连接、完全外连接**。
1、左连接：
left join / left outer join
select * from student left join course on student.ID=course.ID
执行结果：
可以看出，左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).
注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。
2、右连接
right join / right outer join
select * from student right join course on student.ID=course.ID
执行结果：
右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。
注：同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。
3、完全外连接
full join / full outer join
select * from student full join course on student.ID=course.ID
执行结果：
完全外连接**包含full join左右两表中所有的行**，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。
3）内连接
join / inner join
select * from student inner join course on student.ID=course.ID
执行结果：
inner join 是比较运算符，**只返回符合条件的行**。
此时相当于：
select * from student,course where student.ID=course.ID
4）两表关系为一对多，多对一或多对多时
1）前言：
当然上面两表为一对一关系，那么如果表A和表B为一对多、多对一或多对多的时候，我们又该如何写连接SQL语句呢？
其实两表一对多的SQL语句和一对一的SQL语句的写法都差不多，只是查询的结果不一样，当然两表也要略有改动。
比如:
表1的列可以改为：`Sno Name Cno`
表2的列可以改为：`Cno CName`
这样两表就可以写一对多和多对一的SQL语句了，写法和上面的一对一SQL语句一样。
下面介绍一下当两表为多对多的时候我们该如何建表以及些SQL语句。
新建三表：
表A: student 截图如下：
表B: course 截图如下：
表C(中间表): student_course 截图如下：
一个学生可以选择多门课程，一门课程可以被多个学生选择，因此学生表student和课程表course之间是多对多的关系。
当两表为多对多关系的时候，我们需要**建立一个中间表student_course**，中间表至少要有两表的主键，当然还可以有别的内容。
2）SQL语句：
select s.Name,C.Cname from student_course as sc
left join student as s on s.Sno=sc.Sno
left join course as c on c.Cno=sc.Cno
3）执行结果：
此条SQL执行的结果是学生选课的情况。
五，三表联合查询：
1）前言：
用户表user：
| user_id | user_name | user_sex |
|---|---|---|
| 100111 | 佳佳 | 女 |
课程表subject：
| subject_id | subject_name | achievement | user_number |
|---|---|---|---|
| 111 | 计算机科学与技术 | 99 | 100111 |
学院表college：
| college_id | college_name | subject_number |
|---|---|---|
| 201 | 网络工程学院 | 111 |
2）SQL：
select
u.user_id, u.user_name,u.user_sex,c.college_name,s.subject_name, s.achievement
from user u
LEFT JOIN subject s ON s.user_number=u. user_id
LEFT JOIN college c ON c.subject_number=s.subject_id;
3）三表关联查询结果：
| user_id(学号) | user_name（姓名） | user_sex（性别） | college_name（学院） | subject_name（学科） | achievement（成绩） |
|---|---|---|---|---|---|
| 100111 | 佳佳 | 女 | 网络工程学院 | 计算机科学与技术 | 99 |
六，多表查询的join 规律：
比如：inner join(内连接)
只返回两个表中连接字段相等的结果
① 2个表进行查询
SELECT
t.tab1,
t.tab2，p.tab1
FROM
表1 AS t
INNER JOIN 表2 AS p
ON t.tab1 = p.tab1
GROUP BY t.tab2
② 3个表进行查询
SELECT
t.tab1,
t.tab2，p.tab1
FROM
(
表1 AS a
INNER JOIN 表2 AS p
ON a.tab1 = p.tab1
)
INNER JOIN 表3 AS t
ON a.tab1 = t.tab1
GROUP BY t.tab2
③ 4个表进行查询
SELECT
t.tab1,
t.tab2，a.tab1
FROM
(
(
表1 AS a
INNER JOIN 表2 AS p
ON a.tab1 = p.tab1
)
INNER JOIN 表3 AS c
ON a.tab1 = c.tab1
)
INNER JOIN 表4 AS t
ON a.tab1 = t.tab1
GROUP BY t.tab2
④ 5个表进行查询
SELECT
t.tab1,
t.tab2，a.tab1
FROM
(
(
(
表1 AS a
INNER JOIN 表2 AS p
ON a.tab1 = p.tab1
)
INNER JOIN 表3 AS c
ON a.tab1 = c.tab1
)
INNER JOIN 表4 AS d
ON a.tab1 = d.tab1
)
INNER JOIN 表5 AS t
ON a.tab1 = t.tab1
GROUP BY t.tab2
⑤ 6个表进行查询…
多个表嵌套， 以此类推......
总结：
**left join**(左外连接) ：返回包括左表中的所有记录和右表中连接字段相等的记录
**right join**(右外连接)： 返回包括右表中的所有记录和左表中连接字段相等的记录
注： **left join 、right join 和 inner join 一样嵌套即可， 不做举例~~**
七，多表关联查询中怎样接受查询结果的问题？
答：改造pojo类
Cart购物车 与 Goods商品，这两个实体类的关系为1对多；
可以在Goods实体类中 补上Cart实体类的属性；
也可以在Cart实体类中 补上Goods实体类的属性；
或者直接新建一个实体类，糅合上边这两个实体类的属性们；
糅合一个新的pojo实体类：
以下为第三种方式，糅合一个新的实体类：
Cart实体类：
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Cart {
private Integer id;
private Integer userId;
private Integer goodsId;
private Integer GoodsNum;
}
Goods实体类：
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Goods {
private Integer id;
private String title;
private String imgPath;
private String bannerPath;
private BigDecimal price;
private String detail;
private Date createTime;
private Integer typeId;
}
新建一个类：CartAndGoods：(糅合上边两个实体类的属性：)
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CartAndGoods {
private Integer id;
private Integer userId;
private Integer goodsId;
private Integer GoodsNum;
private String title;
private String imgPath;
private String bannerPath;
private BigDecimal price;
private String detail;
private Date createTime;
private Integer typeId;
}
DAO层：
List<CartAndGoods> queryAll(@Param("userId") Integer userId);
<select id="queryAll" resultType="per.zyh.pojo.CartAndGoods">
select t_cart.*,t_goods.* from cart
left join goods
on cart.goodsId=goods.id
where cart.userId=#{userId}
</select>
测试：
List<CartAndGoods> cartList = cartDAO.queryAll(8);
System.out.println(cartList.toString());
查询结果：
[CartAndGoods(id=7, userId=8, goodsId=1, GoodsNum=98, title=草莓, imgPath=images/goods/goods003.jpg, bannerPath=images/goods_detail.jpg, price=30, detail=草莓外销。, createTime=Mon Oct 07 00:00:00 CST 2019, typeId=1)]
若有收获，就点个赞吧
0 人点赞
