mysql关联查询

---

一、关联关系

• mysql中，数据表的关联关系分为三种

1. 一对一

   • A、B两张表，A表中一条数据对应B表中的一条数据
   • 比如：用户表和用户信息扩展表、商品表和商品信息扩展表
   • 建立关系：在从表中添加外键指向主表的主键

2. 一对多

   • 一个部门对应多个员工、一个员工对应一个部门
   • 比如：员工表和部门表；商品表和商品分类表
   • 建立关系：在多的表中添加外键指向另外一张表的主键；员工表中加外键（部门id）对应部门表的主键

3. 多对多

   • A、B两张表，A中的一条数据对应B中的多条数据，B中的一条数据对应A中的多条数据
   • 比如： 老师表和学生表，一个老师对应多个学生，一个学生对应多个老师
   • 建立关系：创建一张中间表（关联表），中间表中的两个外键分别指向两个表中的主键

二、关联查询

• 同时查询多张表的查询方式称为关联查询，多张表通过相等信息的字段建立联系

1、连接方式

1. 等值连接

   select
    *
   from A,B
   where A.x=B.x
       and A.age=18;
   1
   2
   3
   4
   5

2. 内连接

   -- 1、等值连接
   select
    *
   from A
   join B on A.x=B.x
   where A.age=18;
   
   -- 2、非等值连接
   select
    *
   from emp e
   join grade g on e.sal between g.min and g.max;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12

3. 外连接

   -- 1、左外链接
   -- A表全部 + B中和A关联的记录，在B找不到和A关联的记录的则用null填充
   select
    *
   from A
   left join B on A.x=B.x
   where A.age=18;
   
   -- 2、右外链接
   -- B表全部 + A中和B关联的记录，在A找不到和A关联的记录的则用null填充
   select
    *
   from A
   right join B on A.x=B.x
   where A.age=18;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15

2、自关联

• 一个表的外键指向该表的主键，这种关联方式叫做自关联
• 通常该表有层次结构：如部门表：表中存在上级部门id

-- 一级部门的parent_id=0，所以它的上级部门是关联不上的，所以使用left join
select
    t1.id,
    t1.name '部门名称'
    ifnull(t2.name,'无') '上级部门名称'
from dept t1
left join dept t2 on (t1.parent_id=t2.id)
1
2
3
4
5
6
7

3、笛卡尔积

• 表关联查询，不写关联关系时出现，得到两张表结果的乘积，这个乘积称为笛卡尔积
• 如：A表有x条记录，B表有y条记录，查询结果=x*y条记录

4、关联查询on和where的区别

• 先根据on条件生成临时表，where则是在临时表的基础上再进行条件过滤

  select
      t1.username,
      t2.dept_name
  form t_user t1
  join dept t2 on t1.dept_id = t2.id
  where t2.name='xxx'
  1
  2
  3
  4
  5
  6

• 当外连接时on中条件失效问题

  -- 1、left jon
  -- left join会返回左表的全部数据，此时在on中添加左表的条件会失效
  -- 若要对左表进行条件过滤则只能放在where后面
  select
      t1.username,
      t2.dept_name
  form t_user t1
  left join dept t2 on (t1.dept_id = t2.id and t1.username='xxx')
  
  -- 2、right join
  -- right join会返回右表的全部数据，此时在on中添加右表的条件会失效
  -- 若要对右表进行条件过滤则只能放在where后面
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

三、关联更新

update table_1 t1,table_2 t2
set t1.column = t2.column
where t1.id = t2.pid

update t_user user,t_dept dept
set dept.name=concat(user.username,substring(dept.name,4))
where user.dept_id=dept.id
1
2
3
4
5
6
7