mysql 03: 连接查询

连接查询

• 实际开发，常用多表联合查询，取出最终结果，一个业务会对应多张表
• 分类
  • 根据语法出现的年代
    • SQL92
    • SQL99(较新的语法)
  • 根据表的连接方式
    • 内连接
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接
      • 左外连接(左连接)
      • 右外连接(右连接)
    • 全连接
• 笛卡尔乘积现象
  • select ename,dname from tb_name1, tb_name2;
  • 两张表连接查询，未加任何条件限制，结果条数是两个表中记录的乘积数
  • 类似双重循环
• 关于表的别名
  • 执行效率高
  • 可读性好
• 怎么避免笛卡尔积现象
  • 加限制条件
    • select e.ename, d.dname from tb_name1 e, tb_name2 d where e.deptno = d.deptno;
  • 避免笛卡尔积现象会减少记录的匹配次数么？
    • 不能，只是显示的是有效记录
    • 而且，必然要把所有结果都走一遍才合理

内连接

等值连接

• 最大特点：条件是等量关系

• SQL92：（太老，不用了）

  select
  	e.ename, d.dname
  from
  	emp e, dept d
  where
  	e.deptno = d.deptno and XXXX;         --表连接条件和后续过滤条件的结构不清晰
  

• SQL99：

  select
  	e.ename, d.dname
  from
  	emp e
  join
  	dept d
  on
  	e.deptno = d.deptno;
  	
  	
  语法：
  ...
  	A
  inner join 
  	B
  on
  	连接条件       --连接条件
  where		      --过滤条件
  	...
  

  • inner可以省略，带着可读性更强

非等值连接

• 最大特点：连接条件中的关系是非等量关系

• select
  	e.ename,e.sal, s.grade
  from
  	emp e
  inner join
  	salgrade s
  on 
  	e.sal between s.losal and s.hisal;
  

自连接

• 最大特点：一张表看作两张表，自己连接自己

• 找出员工名和自己对应的领导名(实际应该用外连接)

  select 
  	a.ename as '员工名', b.ename as '领导名'
  from
  	emp a
  inner join
  	emp b
  on
  	a.mgr = b.empno;
  

外连接

• 什么是外连接？和内连接有什么区别？

  • 内连接：凡是A表和B表能匹配的记录都查询出来
    • 两张表没有主副之分，两张表是平等的
  • 外连接：A表和B表有一张是主表，一张表是副表，主要查询主表，顺带查询副表
    • 当副表中没有与主表匹配时，副表自动模拟出NULL与主表数据匹配

• 分类

  • 左外连接：左表是主表
  • 右外连接：右表是主表
  • 左连接有右连接的写法，右连接有左连接的写法

• 示例：找出所有员工的上级领导(所有员工必须全部查询出来，员工表占主导地位)

  select
  	a.ename '员工名', b.ename '领导名'
  from	
  	emp a
  left join
  	emp b
  on 
  	a.mgr = b.empno;
  	
  	
  另一种写法：
  
  select 
  	a.ename '员工名', b.ename '领导名'
  from
  	emp b
  right outer join 
  	emp a	--主表地位一直确定在 a表 -> 员工表 
  on 
  	a.mgr = b.empno;
  

  • outer可以省略

• 外连接使用较多

  • 实际很少有表的关系处于平等地位
  • 查不到也不能出现数据缺失
  • 主表数据全部无条件的查询出来

• 示例：找出哪个部门没有员工？

  select
  	d.*
  from
  	emp e
  right join
  	dept d
  on 
  	e.deptno = d.deptno    --连接条件
  where			       --过滤条件
  	e.empno is null;
  

三张表的连接查询

• 格式

  ...
  	A
  join
  	B
  join 
  	C
  on
  	..
  

• 找出每个员工名称，所在部门名称和工资等级

  select e.ename, d.dname,s.grade
  from
  emp e
  join
  dept d
  on
  e.deptno = d.deptno
  join 
  salgrade s
  on
  e.sal between s.losal and s.hisal;
  

• 找出每个员工名称，所在部门名称和工资等级，以及上级领导

  select e.ename '员工名', d.dname, s.grade, e1.ename '领导名'
  from
  emp e
  join                --内连接1
  dept d
  on
  e.deptno = d.deptno  --连接条件1
  join                 --内连接2
  salgrade s			 
  on
  e.sal between s.losal and s.hisal  --连接条件2
  left join            --外连接1，注意必须使用外连接的条件：某数据对应的那个数据有没有可能为NULL
  emp e1
  on 
  e.mgr = e1.empno;    --连接条件3