Mysql数据库多表连接查询完整版

Mysql数据库多表连接查询

1. 首先了解笛卡尔积的概念

    SELECT 
    	emp.ename,emp.deptno,dept.deptno 
    FROM emp ,dept
    ;

笛卡尔积的发生条件
（1）没有连接条件
（2）连接条件不对

运行上面的sql代码就会产生 的查询数=单独查询emp表的数据数*单独查询dept表产生的数据

2. 多表连接写法步骤

• 分析要查询的列都来自于哪些表，构成FROM子句;

• 分析这些表之间的关联关系，如果表之间没有直接关联关系，而是通过另一个中间表关联，则也要在FROM子句中补充中间关联表;

• 接下来在WHERE子句中补充表之间的关联关系，通常N个表，至少要有N-1个关联关系;

• 分析是否还有其它限制条件，补充到WHERE子句的表关联关系之后，作为限制条件;

• 根据用户想要显示的信息，补充SELECT子句。

• 分析是否有排序要求，如果排序要求中还涉及到其它表，则也要进行第2步补充排序字段所在的表，并且添加表之间的关联关系;

3. 非等值连接
   查询每个员工的姓名，工资，工资等级

	SELECT 	
		e.ename, e.sal, s.grade
   	FROM	
   		emp e, salgrade s
   	WHERE 	
   		e.sal
   	BETWEEN 	
   		s.losal 
   	AND 
   		s.hisal
   	;

4. 自身连接

• 自身连接也叫自连接，将一个表看做两个表

      SELECT 
        worker.ename ‘WNAME’,manager.ename ‘LNAME’
      FROM 
        emp worker, emp manager
      WHERE 	
        worker.mgr = manager.empno
      ;

• 这里是将emp表分成了worker表和manager表
  这里是想要查询员工对应的经理的员工号
  因为
  “WORKER表中的 MGR 等于 MANAGER表中的EMPNO”

5. 交叉连接

• 交叉连接会产生连个表的交叉乘积，和两个表之间的笛卡尔积是一样的；
  使用CROSS JOIN 子句完成。

6. 自然连接

• 自然连接是对两个表之间相同名字和数据类型的列进行的等值连接；
  如果两个表之间相同名称的列的数据类型不同，则会产生错误；
  使用NATURAL JOIN子句来完成。
  （不需要去找连接条件，但是如果找到的连接条件的数据类型不同则会报错）

    SELECT  
    	empno,ename,sal,deptno,loc 
    FROM    
    	emp 
    NATURAL JOIN 	
    	dept
    ;

7. using字句

• 自然连接是使用所有名称和数据类型相匹配的列作为连接条件，而USING子句可以指定用某个或某几个相同名字和数据类型的列作为连接条件。

    SELECT 	
    	e.ename,e.ename,e.sal,deptno,d.loc 
    FROM   	
    	emp e 
    JOIN 
    	dept d 
    USING (deptno) 
    WHERE  	
    	deptno = 20
    ;

有定义可以知道自然连接和using子句是不能同时使用的

使用USING子句创建连接时，应注意以下几点：

• 如果有若干个列名称相同但数据类型不同，自然连接子句可以用USING子句来替换，以指定产生等值连接的列。
• 如果有多于一个列都匹配的情况，使用USING子句只能指定其中的一列。
• USING子句中的用到的列不能使用表名和别名作为前缀。
• NATURAL JOIN子句和USING子句是相互排斥的，不能同时使用

8. ON子句

• 自然连接条件基本上是具有相同列名的表之间的等值连接；
  如果要指定任意连接条件,或指定要连接的列，则可以使用ON子句；
  用ON将连接条件和其它检索条件分隔开，其它检索条件写在WHERE子句；

ON子句可以提高代码的可读性。

    SELECT 	
    	e.empno, e.ename, e.deptno, d.deptno, d.loc 
    FROM   	
    	emp e 
    JOIN 	
    	dept d 
    ON  	
    	(e.deptno = d.deptno)
    ; 

• 使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

    SELECT
    	 e.ename,d.dname,d.loc,s.grade 
   FROM
    	emp e 
    JOIN
    	 dept d  
    ON 
    	e.deptno and d.deptno 
    JOIN 
    	salgrade s  
    on 
    	e.sal between s.losal and s.hisal
    where 
    	d.loc ='CHICAGO'
    ;

将连接条件分离出来实现解耦的目的

9. 外部连接

• 分为左外连接和右外连接
  在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。
  左外连接
  左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。
  右外连接
  右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来

• 左外连接
  查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

    SELECT 
    	e.ename,e.deptno,d.loc 
    FROM 
      	emp e 
    LEFT OUTER JOIN 
    	dept d 
    ON 
        (e.deptno = d.deptno)
    ; 

• 右外连接
  查询没有员工号的部门
  即把表的位置互换位置

    SELECT 
    	e.ename,e.deptno,d.loc 
    FROM 
      	emp e 
    RIGHT  OUTER JOIN 
    	dept d 
    ON  
       	(e.deptno = d.deptno)
 	;