SQL多表连接查询

一，数据的完整性约束

1）概述：

  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  截图如下：

SQL多表连接查询（详细实例） - 图1

    表2：course  截图如下：

                                                                                   ![](mdpic/11302UZ3-1.jpg#crop=0&crop=0&crop=1&crop=1&id=UlEZ8&originalType=binary&ratio=1&rotation=0&showTitle=false&status=done&style=none&title=)

2）外连接

    外连接可分为：**左连接、右连接、完全外连接**。

1、左连接：

left join / left outer join

        select * from student left join course on student.ID=course.ID

执行结果：

SQL多表连接查询（详细实例） - 图2

    可以看出，左外连接包含left join左表所有行，如果左表中某行在右表没有匹配，则结果中对应行右表的部分全部为空(NULL).

    注：此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

2、右连接

right join / right outer join

        select * from student right join course on student.ID=course.ID

执行结果：

SQL多表连接查询（详细实例） - 图3

    右外连接包含right join右表所有行，如果左表中某行在右表没有匹配，则结果中对应左表的部分全部为空(NULL)。

    注：同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数，因为左右两表此时为一对一关系。

3、完全外连接

full join / full outer join

        select * from student full join course on student.ID=course.ID

执行结果：

SQL多表连接查询（详细实例） - 图4

    完全外连接**包含full join左右两表中所有的行**，如果右表中某行在左表中没有匹配，则结果中对应行右表的部分全部为空(NULL)，如果左表中某行在右表中没有匹配，则结果中对应行左表的部分全部为空(NULL)。

3）内连接

join / inner join

        select * from student inner join course on student.ID=course.ID

执行结果：

SQL多表连接查询（详细实例） - 图5

    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 截图如下：

SQL多表连接查询（详细实例） - 图6

表B: course 截图如下：

SQL多表连接查询（详细实例） - 图7

表C(中间表): student_course 截图如下：

SQL多表连接查询（详细实例） - 图8

    一个学生可以选择多门课程，一门课程可以被多个学生选择，因此学生表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多表连接查询（详细实例） - 图9

此条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)]