MySQL 基础---多表数据查询

多表数据记录查询:

• 关系数据的各种操作
• 内连接查询
• 外连接查询
• 子查询

1. 关系数据操作
   传统的运算:

• 并(UNION)
• 笛卡尔积(CARTESIAN PRODUCT)
• 专门针对数据库操作的运算---连接(JOIN)

(1) 并(UNION)
“并”就是把具有相同字段数目和字段类型的表合并到一起。
(2) 笛卡尔积(CARTESIAN PRODUCT)
笛卡尔积就是没有连接条件表关系返回的结果。
(3) 内连接(INNER JOIN)
所谓连接，就是在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。连接又分为内连接(INNER JOIN)、外链接(OUTER JOIN)、交叉连接(CROSS JOIN)。
所谓内连接(INNER JOIN), 就是在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配的条件可以分为自然连接、等值连接和不等连接。

• 自然连接(NATURAL JOIN)
  自然连接操作就是表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。
• 等值连接
  等值连接操作就是表关系的笛卡尔积中，选择所匹配字段值相等的数据记录。
• 不等连接
  不等连接操作就是表关系的笛卡尔积中，选择所匹配字段值不相等的数据记录。
  (4) 外连接(OUTER JOIN)
  所谓外链接，就是在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)、和全外连接(FULL OUTER JOIN)。
• 左外连接(LEFT OUTER JOIN)
  所谓左外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。
• 右外连接(RIGHT OUTER JOIN)
  所谓右外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。
• 全外连接(FULL OUTER JOIN)
  所谓全外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录。
  (5) 交叉连接(CROSS JOIN)
  交叉连接就是表关系笛卡尔积后数据记录。

t_dept表.png

t_employee表.png

1. 内连接查询(INNER JOIN...ON)
   内连接查询: 等值连接、不等连接。

(1) 自连接--特殊的等值连接

select e.ename employeename, e.job, l.ename loadername 
    from t_employee e 
    inner join t_employee l 
    on e.mgr=l.empno;

(2) 等值连接
内连接查询中的等值连接，就是在关键字ON后的匹配条件中通过关系运算符(=) 来实现等值条件。

select e.empno, e.ename, e.job, d.dname, d.loc 
    from t_employee e 
    inner join t_dept d 
    on e.deptno=d.deptno;

三表查询

select e.empno, e.ename employeename, e.sal, e.job, l.ename loadername, d
.dname, d.loc 
    from t_employee e 
    inner join t_employee l on e.mgr=l.empno 
    inner join t_dept d on l.deptno=d.deptno;

或

select e.empno, e.ename employeename, e.sal, e.job, l.ename loadername, d.dname, d.loc 
    from t_employee e, t_employee l, t_dept d 
    where e.mgr=l.empno and l.deptno=d.deptno;

(3) 不等连接
内连接查询中的不等连接，就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，可以使用的关系运算符包含“>”、“>=”、“<”、“<=”和“!=”等运算符号。

select e.ename employeename, e.job, l.ename loadername 
    from t_employee e
    inner join t_employee l 
                on e.mgr=l.empno and e.empno>l.empno;

1. 外连接查询(OUTER JOIN...ON)
   外连接查询: 左外连接、右外连接、全外连接。

(1) 左外连接
外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字LEFT JOIN左边的表为参考表。

select e.ename employeename, e.job, l.ename loadername 
    from t_employee e
    inner join t_employee l on e.mgr=l.empno;

(2) 右外连接
外连接查询中的右外连接，就是指新关系中执行匹配条件时，以关键字RIGHT JOIN 右边的表为参考表。

select e.empno, e.ename, e.job, d.dname, d.loc from t_dept d right join t
_employee e on e.deptno=d.deptno;

1. 合并查询数据记录

t_cstudent表.png

t_mstudent表.png

(1) 带有关键字UNION的合并操作
关键字UNION会把查询结果集直接合并到一起，同时将会去掉重复数据记录。

 select *
          from t_cstudent
 union
 select *
          from t_mstudent;

(2) 带关键字UNION ALL的合并操作
关键字UNION ALL会把查询结果集直接合并在一起。

 select *
          from t_cstudent
 union all
 select *
          from t_mstudent;

1. 子查询
   虽然可以通过连接查询实现多表查询数据记录，但不建议使用。因为连接查询的性能很差，因此出现了连接查询的替代者子查询。
   所谓子查询，就是指一个查询之中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。
   (1) 返回结果为单行单列和单行多列子查询

• 返回结果为单行单列子查询

select *
         from t_employee
         where sal>(
                 select sal
                         from t_employee
                         where ename='swith');

• 返回结果为单行多列子查询

select ename, sal, job
         from t_employee
         where (sal,job)=(
                 select sal,job
                 from t_employee
                         where ename='swith');

(2) 返回结果为多行单列子查询
当子查询的返回结果为多行单列数据记录时，该子查询语句一般会在主查询语句的WHERE子句里出现，通常会包含IN、ANY、ALL、EXISTS等关键字。

• 带有关键字IN的子查询

select *
         from t_employee
         where deptno in(
                 select deptno from t_dept
         );

• 带有关键字ANY的子查询
  关键字ANY用来表示主查询的条件为满足子查询返回查询结果中任意一条数据记录，匹配方式: =ANY(其功能与关键字IN一样)、>ANY(比子查询中返回数据记录中最小的还要大于数据记录)、<ANY(比子查询中返回数据记录中最小的还要小于数据记录)

select ename, sal
         from t_employee
         where sal>any(
                 select sal
                         from t_employee
                         where job='manager'
         );

• 带有关键字ALL的自查询
  关键字ALL用来表示主查询的条件为满足子查询返回查询结果中 所有数据记录。匹配方式: >ALL(比子查询中返回数据记录中最大的还要大于数据记录)、<ALL(比子查询中返回数据记录中最大的还要小于数据记录).

select ename, sal
         from t_employee
         where sal>all(
                 select sal
                         from t_employee
                         where job='manager'
         );

• 带有关键字EXISTS的子查询

select * from t_dept c where not exists(
         select * from t_employee a
                 where a.deptno=c.deptno);

(3) 返回结果为多行多列子查询

select d.deptno, d.dname, d.loc, number, average from t_dept d inner join(
          select deptno dno, count(empno) number, avg(sal) average
                 from t_employee
                 group by deptno desc) employee
          on d.deptno=employee.dno;