MySQL Day03多表链接查询

多表链接查询和视图

什么是多表链接查询？

• 多表查询： 在查询时，需要涉及到两个以上表的查询

为什么要使用多表查询？

做项目的规范， 实际业务中由于数据库的设计规范， 需要查询的信息往往来源于多个表
例如 查询所有员工信息，以及他们所在部门的基本信息”，设计到的是两个数据源，一个是员工表，另一个是部门表。
解决方案
SELECT * FROM emp ， dept；
但是查询出来的结果是一个笛卡尔积：第一个表的所有记录和第二张表中的所有记录逐个匹对，但结果大部分并不是我们想要的，想要获取到符合条件的数据，就需要到：d链接查询。
注意： 使用多表查询时必须弄清楚表之间的关联， 这是多表查询的基础，需要通过建立两个表之间的关系，使用链接条件筛选后查询出必要结果

如何建立多表连接？

1. 通过主键-外键链接
2. 通过相同相关字段链接
3. 注意 主键与外键不需要必须相同，但是习惯相同
4. 外键取值可以为空吗？可以

等值链接

• 多张表的链接值相等
• WHERE子句中写的多表链接的条件中使用等号链接两个相同的字段
• 表1.字段 = 表二.字段
• 链接条件的个数有N-1个 N表示数据表的数目

--  查询20号部门的员工的员工姓名，职位，平均工资，所在部门名称。
SELECT e.ename,e.job,AVG(e.sal) avgsal,d.dname
FROM emp e,dept d 
WHERE e.deptno=d.deptno AND e.deptno=20;

不等值链接

• 多张表的链接值不相等
• WHERE子句条件中写的多表连接的条件中要使用除了等号以外的运算符连接多张表的连接值。 在连接条件中 可以使用的运算符有:>,<,<=,>= ,<>,BETWEEN…AND
• – 任务1：查询出员工的姓名，薪水及薪水等级。
• SELECT e.ename,e.sal,sg.grade
• FROM emp e,salgrade sg
• WHERE e.sal BETWEEN sg.losal AND sg.hisal;
• – 任务2：查询10号部门工资小于2000的员工的姓名，工资及工资等级。
• SELECT e.ename,e.deptno,e.sal,sg.grade
• FROM emp e,salgrade sg
• WHERE e.sal BETWEEN sg.losal AND sg.hisal AND e.deptno=10 AND e.sal<2000;
• – 任务3：查询工资等级处于第四级别的员工的姓名。
• SELECT e.ename,e.sal,sg.grade
• FROM emp e,salgrade sg
• WHERE e.sal BETWEEN sg.losal AND sg.hisal AND sg.grade=4;

内连接

• 内连接是一种常用的多表关联查询方式，一般使用关键字INNER JOIN来实现。其中，INNER关键字可以省略， 当只使用JOIN关键字时，语句只表示内连接操作。在使用内连接查询多个表时，必须在FROM子句之后定义一 个ON子句，该子句用来指定两个表实现内连接的“连接条件
• • column_list: 字段列表
• • table_name1和table_name2：两个要实现内连接的表。
• • join_condition:实现内连接的条件表达式

需要注意的是，在内连接的检索结果中，所有记录行都是满足连接条件的。内连接的语法格式如下： SELECT column_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition;

-- 任务1 查询10号部门的所有员工信息，显示员工姓名，部门编号，部门名称。
SELECT e.ename,d.deptno,d.dname 
FROM emp e 
INNER JOIN dept d ON e.deptno=d.deptno WHERE d.deptno=10; 
-- 任务2  查询在研发部('RESEARCH')工作员工的编号，姓名，工作部门，工作所在地      SELECT e.empno,e.ename,d.dname,d.loc
FROM emp e 
JOIN dept d ON e.deptno=d.deptno AND d.dname='RESEARCH';

内连接INNER JOIN…ON：返回完全满足条件的记录。
面试要点: (1)、注意内连接的语法格式 (2)、注意内连接的条件是写在on子句后面

自链接

• 把一张表当两张表用
• SELECT e.mgr,e.deptno,e.ename,d.ename FROM emp e LEFT JOIN emp d on e.mgr=d.empno;
• 语法： SELECT select_list FROM table_name t1，table_name t2 WHERE search_condition; 面试要点: 用户 可能会拥有“自引用式”的外键。“自引用式”外键是指表中的一个列可以是该表主键的一个外键。比如 :emp表中 某一行的mgr列值（管理者列）可能是另一行的empno列值（员工列），因为管理者本身也是公司的员工

外连接

• 外连接：返回所有的匹配行和一些或全部不匹配的行，也就是会返回不满足条件的记录，这取决于所建立的外 连接的类型。 MySQL支持二种： (1). 左外连接 LEFT OUTER JOIN…ON (2). 右外连接 RIGHT OUTER JOIN…ON
• 注：OUTER可以省略 。 面试要点： 根据关联的表匹配返回的行记录与所想要查询的记录判断清楚到底需要用左外连接还是右外 连接。
• – 任务 统计各个部门的员工人数 – 左外连接
• SELECT d.deptno,COUNT(e.empno)
• FROM dept d
• LEFT OUTER JOIN emp e ON d.deptno = e.deptno
• GROUP BY d.deptno;
  – 右外连接
  SELECT d.deptno,COUNT(e.empno)
  FROM emp e RIGHT OUTER JOIN dept d ON d.deptno = e.deptno
  GROUP BY d.deptno;