MySQL（九）多表数据记录查询

最新推荐文章于 2021-10-15 20:42:36 发布

key_zhaojd

最新推荐文章于 2021-10-15 20:42:36 发布

阅读量1.6k

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/ZJDWHD/article/details/52296980

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

  在实际应用中，经常需要实现在一个查询语句中显示多张表的数据，这就是所谓的多表数据记录连接查询。在具体实现连接查询操作时，首先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录。
关系数据操作：
  1. 并（UNION）：
  “并”即把具有相同字段数目和字段类型的表合并到一起。通过并操作将两张表的数据记录合并到一起。合并后的字段数为：表1的字段数或表2的字段数；合并后的总记录数为：表1的记录数 + 表2的记录数 - 表1和表2 重复的记录数。
  2. 笛卡尔积（CARTESIAN PRODUCT）：
  笛卡尔积就是没有连接条件，表中的数据任意的组合。笛卡尔积的字段数为表1 + 表2。笛卡尔积的记录数为：表1 * 表2。
  3. 连接（JOIN）：
  连接即在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。连接又分为内连接（INNER JOIN）、外连接（OUTER JOIN）、交叉连接(笛卡尔积的连接)（CROSS JOIN）。
  3.1 内连接（JOIN）：
  所谓内连接，就是在表关系的笛卡尔积数据中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配的条件可以分成自然连接、等值连接和不等连接。
  3.1.1 自然连接（NATURAL JOIN）：
  自然连接就是在表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。通过自然连接后，新关系的字段数为：表1+ 表2 - 表1和表2中的重复字段数。自然连接后新关系的记录数为：表1的记录数 * 表2的记录数 - 表1和表2相同字段的值不相等记录数。
自然连接有如下特点：
  1. 在具体执行自然连接时，会自动判断相同名称的字段，然后进行数据值的匹配。
  2. 在执行完自然连接的新关系中，虽然可以指定包含哪些字段，但是不能指定执行过程中的匹配条件，即哪些字段的值进行匹配。
  3. 在执行自然连接的新关系中，执行过程中所匹配的字段名只有一个，即会去掉重复字段。
  3.1.2 等值连接：
  等值连接即：表关系的笛卡尔积中，选择所匹配字段值相等的数据记录。通过等值连接后，新关系的字段数为：表1 + 表2。等值连接后新关系的记录数为：表1 * 表2 - 表1和表2相同字段的值不相等记录数。与自然连接相比，等值连接操作需要在执行过程中用“=”指定匹配条件，在新关系中不会去掉重复字段。
  3.1.3 不等连接：
    不等连接即：表关系的笛卡尔积中，选择所匹配字段值不相等的数据记录。通过不等连接后，新关系的字段数为：表1 + 表2。不等连接后新关系的记录数为：表1 * 表2 - 表1和表2相同字段的值相等的记录数。与自然连接相比，不等连接操作需要在执行过程中用“!=”指定匹配条件，在新关系中不会去掉重复字段。
  3.2 外连接（OUTER JOIN）：
  所谓外连接（OUTER JOIN）,就是在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件可以分为：左外连接、右外连接、全外连接。
  3.2.1 左外连接：
  左外连接：表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。通过左外连接后，新关系的字段数为：左表 + 右表。左外连接后新关系的记录数为：左表 * 右表 - 左表和右表相同字段的值不相等的记录数 + 左表中未匹配的记录数。
  3.2.2 右外连接：
  右外连接：表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。通过右外连接后，新关系的字段数为：左表+ 右表。右外连接后新关系的记录数为：左表 * 右表 - 左表和右表相同字段的值不相等的记录数 + 右表中未匹配的记录数。
  3.2.3 全外连接：
  全外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录。通过全外连接后，新关系的字段数为：左表+ 右表。全外连接后新关系的记录数为：左表 * 右表 - 左表和右表相同字段的值不相等的记录数 + 左表未匹配的记录数 + 右表中未匹配的记录数。

总结：
关系数据操作：
    1.并
    2.笛卡尔积
    3.连接：在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件生成一个新的关系。
        1.内连接：表关系笛卡尔积中，保留匹配的数据记录，舍弃不匹配数据记录。
            1.自然连接    笛卡尔积 - 相同字段不相等 
            2.等值连接    笛卡尔积 - 等值不匹配
            3.不等连接    笛卡尔积 - 不等值不匹配
        2.外连接：表关系笛卡尔积中，不仅保留所有匹配的数据记录，而且保留部分不匹配的数据记录。
            1.左外连接    笛卡尔积 - 不匹配 + 左表不匹配
            2.右外连接    笛卡尔积 - 不匹配 + 右表不匹配
            3.全外连接    笛卡尔积 - 不匹配 + 左右表不匹配
        3.笛卡尔积连接（叉连接）

内连接查询：
  在MySQL中实现内连接查询有两种语法：
  1. 在from子句中利用逗号区分多个表，在where子句中通过逻辑表达式来实现匹配条件，从而实现表的连接。
  2. ANSI连接语法形式，在from子句中使用“join on”关键字，而连接条件写在关键字on子句中。推荐使用第二种方式。

内连接查询语法为：
select field1, field2 ...fieldn 
    from 表1 inner join 表2 
        on 连接条件
--参数filedn 表示所要查询的字段名称，来源于所连接的表1和表2,关键字inner join表示内连接。

1 等值连接：
内连接查询中的等值连接，就是在关键字on后的匹配条件中通过等于关系运算符“=”来实现等值条件。

示例：
mysql> 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;
+-------+---------+-----------+------------+----------+
| empno | ename   | job       | dname      | loc      |
+-------+---------+-----------+------------+----------+
|  7788 | SCOTT   | ANALYST   | ACCOUNTING | NEW YORK |
|  7839 | KING    | PRESIDENT | ACCOUNTING | NEW YORK |
|  7934 | MILLER  | CLEAR     | ACCOUNTING | NEW YORK |
|  7369 | SMITH   | CLEAR     | RESEARCH   | DALLAS   |
|  7499 | ALLEN   | SALESMAN  | RESEARCH   | DALLAS   |
|  7566 | JONES   | MANAGER   | RESEARCH   | DALLAS   |
|  7782 | CLARK   | MANAGER   | RESEARCH   | DALLAS   |
|  7876 | ADAMS   | CLEAR     | RESEARCH   | DALLAS   |
|  7902 | FORD    | ANALYST   | RESEARCH   | DALLAS   |
|  7521 | MARD    | SALESMAN  | SALES      | CHICAGO  |
|  7654 | MARRTIN | SALESMAN  | SALES      | CHICAGO  |
|  7698 | BLAKE   | MANAGER   | SALES      | CHICAGO  |
|  7844 | TURNER  | SALESMAN  | SALES      | CHICAGO  |
|  7900 | JAMES   | CLEAR     | SALES      | CHICAGO  |
+-------+---------+-----------+------------+----------+
14 rows in set (0.00 sec)

1.1 自连接：
自连接是一种特殊的等值连接。所谓自连接就是指表与其自身进行连接。

示例（查询每个雇员的姓名、职位、领导姓名）：
mysql> select e.ename,e.job,l.ename from t_employee e inner join t_employee l on e.MGR=l.empno;
+---------+----------+-------+
| ename   | job      | ename |
+---------+----------+-------+
| SCOTT   | ANALYST  | JONES |
| FORD    | ANALYST  | JONES |
| ALLEN   | SALESMAN | BLAKE |
| MARD    | SALESMAN | BLAKE |
| MARRTIN | SALESMAN | BLAKE |
| TURNER  | SALESMAN | BLAKE |
| JAMES   | CLEAR    | BLAKE |
| MILLER  | CLEAR    | CLARK |
| ADAMS   | CLEAR    | SCOTT |
| JONES   | MANAGER  | KING  |
| BLAKE   | MANAGER  | KING  |
| CLARK   | MANAGER  | KING  |
| SMITH   | CLEAR    | FORD  |
+---------+----------+-------+
13 rows in set (0.00 sec)

2 不等连接：
内连接查询中的不等连接，就是在关键字on后的匹配条件中通过除了等于关系运算符来实现不等条件外，可以使用的关系运算符包含> >= < <= !=

示例：
mysql> 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;
+--------------+----------+------------+
| employeename | job      | loadername |
+--------------+----------+------------+
| SCOTT        | ANALYST  | JONES      |
| FORD         | ANALYST  | JONES      |
| TURNER       | SALESMAN | BLAKE      |
| JAMES        | CLEAR    | BLAKE      |
| MILLER       | CLEAR    | CLARK      |
| ADAMS        | CLEAR    | SCOTT      |
+--------------+----------+------------+
6 rows in set (0.00 sec)

外连接查询：
外连接查询会返回所操作表中至少一个表的所有数据。外连接分为三类：左外连接、右外连接、全外连接

语法为：
select field1, field2, ...fieldn
    from 表1 left|rigth|full [outer] join 表2
    on 连接条件

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

示例：
mysql> select e.ename employeename, e.job job,l.ename leadername from t_employee e left join t_employee l on e.mgr=l.empno;
+--------------+-----------+------------+
| employeename | job       | leadername |
+--------------+-----------+------------+
| SMITH        | CLEAR     | FORD       |
| ALLEN        | SALESMAN  | BLAKE      |
| MARD         | SALESMAN  | BLAKE      |
| JONES        | MANAGER   | KING       |
| MARRTIN      | SALESMAN  | BLAKE      |
| BLAKE        | MANAGER   | KING       |
| CLARK        | MANAGER   | KING       |
| SCOTT        | ANALYST   | JONES      |
| KING         | PRESIDENT | NULL       |
| TURNER       | SALESMAN  | BLAKE      |
| ADAMS        | CLEAR     | SCOTT      |
| JAMES        | CLEAR     | BLAKE      |
| FORD         | ANALYST   | JONES      |
| MILLER       | CLEAR     | CLARK      |
+--------------+-----------+------------+
14 rows in set (0.00 sec)

  2 右外连接：
  外连接查询中的右外连接，就是指新关系中执行匹配条件时，以关键字right join 右边的表为参考。
合并查询数据记录：
  在MySQL中通过关键字UNION来实现”并”操作，即可以通过其将多个select语句的查询结果合并在一起组成新的关系。
  1. 关键字union的合并操作
  关键字union会把查询结果集直接合并在一起，同时将会去掉重复数据记录。
  2. 关键字union all的合并操作
  关键字union all会把查询结果集直接合并在一起。

语法为：
select field1, field2, ...fieldn 
    from tablename1
union | union all
select field1, field2, ...fieldn
    from tablename2
union | union all
select field1, field2, ...fieldn
    from tablename3
......

子查询：
  在MySQL中虽然可以通过连接查询实现多表查询数据记录，但却不建议使用。这是因为连接查询的性能很差。因此出现了连接查询的替代者子查询。推荐使用子查询来实现多表查询数据记录。
  1 为什么使用子查询：
  在日常开发中，经常接触到查询多表数据记录操作，例如查询部门表t_dept和雇员表t_employee表的数据记录。对于新手，直接使用select * from t_dept t,t_employee e where t.deptno=e.deptno;这条sql语句在执行时，首先会对两个表进行笛卡尔积操作，然后在选取符合匹配条件的数据记录。如果两张表的数据量较大，则在进行笛卡尔积操作时会造成死机。有经验的开发者通常会首先用统计函数查看操作表笛卡尔积后的数据记录数，然后再进行多表查询。因此多表查询一般会经过如下步骤：
  1. 通过统计函数count(1)查询所关联表笛卡尔积后的数据的记录数。然后再进行多表查询。
  2. 如果查询到的数据记录数mysql可以接受，然后再进行多表查询，否则就应该考虑通过其他方式来实现。
  如果笛卡尔积后的数据远远大于mysql软件可以接受的范围，为了解决多表查询，mysql提供了子查询来实现多表查询。
  子查询即：在一个查询中嵌套了其他若干查询，即在一个select 查询语句的where或from子句中包含另一个select查询语句。在查询语句中，外层select查询语句称为主查询，where子句中select查询语句被称为子查询，也被称为嵌套查询。通过子查询可以实现多表查询，该查询语句中可能包含in，any，all，exists等关键字。除此之外还可能包含比较运算符。理论上子查询可以出现在查询语句的任何位置，但在实际开发中，子查询经常出现在where或from子句中。where子句中的子查询，该位置处的子查询一般返回单行单列、多行多列、单行多列数据记录。from子句中的子查询，该位置处的子查询一般返回多行多列数据记录，可以当作一张临时表。
  2 返回结果为单行单列和单行多列子查询：
  当子查询的返回结果为单行单列时，该子查询语句一般在主查询语句的where子句中，通常会包含比较运算符（> < = != 等）
  2.1 单行单列子查询：

示例（工资比Smith高的全部雇员信息）：
mysql> select * from t_employee where sal > (select sal from t_employee where ename='smith');
+-------+---------+-----------+------+------------+---------+---------+--------+
| empno | ename   | job       | MGR  | Hiredate   | sal     | comm    | deptno |
+-------+---------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN   | SALESMAN  | 7698 | 1982-03-12 | 1600.00 |  300.00 |     20 |
|  7521 | MARD    | SALESMAN  | 7698 | 1983-03-12 | 1250.00 |  500.00 |     30 |
|  7566 | JONES   | MANAGER   | 7839 | 1981-03-12 | 2975.00 |    NULL |     20 |
|  7654 | MARRTIN | SALESMAN  | 7698 | 1981-03-12 | 2850.00 | 1400.00 |     30 |
|  7698 | BLAKE   | MANAGER   | 7839 | 1981-03-12 | 2850.00 |    NULL |     30 |
|  7782 | CLARK   | MANAGER   | 7839 | 1985-03-12 | 2450.00 |    NULL |     20 |
|  7788 | SCOTT   | ANALYST   | 7566 | 1981-03-12 | 3000.00 |    NULL |     10 |
|  7839 | KING    | PRESIDENT | NULL | 1981-03-12 | 5000.00 |    NULL |     10 |
|  7844 | TURNER  | SALESMAN  | 7698 | 1989-03-12 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS   | CLEAR     | 7788 | 1998-03-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES   | CLEAR     | 7698 | 1987-03-12 |  950.00 |    NULL |     30 |
|  7902 | FORD    | ANALYST   | 7566 | 0000-00-00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER  | CLEAR     | 7782 | 1981-03-12 | 1300.00 |    NULL |     10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

2.2 单行多列子查询：
where子句中的子查询除了是返回单行单列的数据记录外，还可以是返回多行多列的数据记录，不过这种子查询很少出现。

示例（工资和职位和Smith一样的全部雇员）：
mysql> select ename,job,sal from t_employee where (sal,job)=(select sal,job from t_employee where ename='smith');
+-------+-------+--------+
| ename | job   | sal    |
+-------+-------+--------+
| SMITH | CLEAR | 800.00 |
+-------+-------+--------+
1 row in set (0.00 sec)

  3 返回结果为多行单列子查询：
  当子查询的返回结果为多行单列数据记录时，该子查询语句一般会在主查询语句的where子句中出现，通常会包含IN ANY ALL EXISTS等关键字。
  3.1 带有关键字in的子查询：
    当主查询的条件在子查询的查询结果中时，可以通过关键字in来进行判断。相反，如果想实现主查询的条件不在子查询的查询结果中时，可以通过关键字not in来进行判断。

示例：
mysql> select * from t_employee where deptno in(select deptno from t_dept);
+-------+---------+-----------+------+------------+---------+---------+--------+
| empno | ename   | job       | MGR  | Hiredate   | sal     | comm    | deptno |
+-------+---------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH   | CLEAR     | 7902 | 1981-03-12 |  800.00 |    NULL |     20 |
|  7499 | ALLEN   | SALESMAN  | 7698 | 1982-03-12 | 1600.00 |  300.00 |     20 |
|  7521 | MARD    | SALESMAN  | 7698 | 1983-03-12 | 1250.00 |  500.00 |     30 |
|  7566 | JONES   | MANAGER   | 7839 | 1981-03-12 | 2975.00 |    NULL |     20 |
|  7654 | MARRTIN | SALESMAN  | 7698 | 1981-03-12 | 2850.00 | 1400.00 |     30 |
|  7698 | BLAKE   | MANAGER   | 7839 | 1981-03-12 | 2850.00 |    NULL |     30 |
|  7782 | CLARK   | MANAGER   | 7839 | 1985-03-12 | 2450.00 |    NULL |     20 |
|  7788 | SCOTT   | ANALYST   | 7566 | 1981-03-12 | 3000.00 |    NULL |     10 |
|  7839 | KING    | PRESIDENT | NULL | 1981-03-12 | 5000.00 |    NULL |     10 |
|  7844 | TURNER  | SALESMAN  | 7698 | 1989-03-12 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS   | CLEAR     | 7788 | 1998-03-12 | 1100.00 |    NULL |     20 |
|  7900 | JAMES   | CLEAR     | 7698 | 1987-03-12 |  950.00 |    NULL |     30 |
|  7902 | FORD    | ANALYST   | 7566 | 0000-00-00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER  | CLEAR     | 7782 | 1981-03-12 | 1300.00 |    NULL |     10 |
+-------+---------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

3.2 带有关键字any的子查询：
关键字any：主查询的条件为满足子查询的查询结果中任意一条数据记录，该关键字有三种匹配方式；
1. =any：其功能与关键字in一样
2. > any（>=any）：只要大于（大于等于）子查询中最小的一个即可。
3. < any（<=any）：只要小于（小于等于）子查询中最大的一个即可。

示例（查询雇员工资不低于职位为manager的工资）：
mysql> select ename,sal from t_employee where sal>any(select sal from t_employee where job='manager');
+---------+---------+
| ename   | sal     |
+---------+---------+
| JONES   | 2975.00 |
| MARRTIN | 2850.00 |
| BLAKE   | 2850.00 |
| SCOTT   | 3000.00 |
| KING    | 5000.00 |
| FORD    | 3000.00 |
+---------+---------+
6 rows in set (0.00 sec)

  3.3 带有关键字all的子查询：
  关键字all用来表示主查询的条件为满足子查询返回查询结果中所有数据记录，有两种匹配方式：
  1. > all（>=all）：比子查询结果中最大的还要大（大于等于）的数据记录；
  2. < all（<= all）：比子查询结果中最小的还要小（小于等于）的数据记录。

示例：
mysql> select ename,sal from t_employee where sal>all(select sal from t_employee where job='manager');
+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
3 rows in set (0.00 sec)

  3.4 带有关键字exists的子查询：
  关键字exists是一个boolean类型，当能返回结果集时为true，不能返回结果集时为false。查询时exists对外表采用遍历方式逐条查询，每次查询都会比较exists的条件语句，当exists里的条件语句返回记录行时则条件为真，此时返回当前遍历到的记录；反之，如果exists里条件语句不能返回记录行，则丢弃当前遍历到的记录。
  4 返回结果为多行多列子查询：
  当子查询的返回结果为多行多列数据记录时，该子查询语句一般会在主查询语句的from子句里，被当作一张临时表的方式来处理。

示例（查询雇员表中各部门的部门号、部门名称、部门地址、雇员人数、和平均工资）：
通过内连接来实现：
mysql> select d.deptno,d.dname,d.loc,count(e.empno) number,avg(e.sal) average from t_employee e inner join t_dept d on e
.deptno=d.deptno group by d.deptno;
+--------+------------+----------+--------+-------------+
| deptno | dname      | loc      | number | average     |
+--------+------------+----------+--------+-------------+
|     10 | ACCOUNTING | NEW YORK |      3 | 3100.000000 |
|     20 | RESEARCH   | DALLAS   |      6 | 1987.500000 |
|     30 | SALES      | CHICAGO  |      5 | 1880.000000 |
+--------+------------+----------+--------+-------------+
3 rows in set (0.00 sec)

通过子查询来实现：
mysql> select d.deptno,d.dname,d.loc,number,average from t_dept d inner join(select deptno dno,count(empno) number,avg(s
al) average from t_employee group by deptno) employee on d.deptno=employee.dno;
+--------+------------+----------+--------+-------------+
| deptno | dname      | loc      | number | average     |
+--------+------------+----------+--------+-------------+
|     10 | ACCOUNTING | NEW YORK |      3 | 3100.000000 |
|     20 | RESEARCH   | DALLAS   |      6 | 1987.500000 |
|     30 | SALES      | CHICAGO  |      5 | 1880.000000 |
+--------+------------+----------+--------+-------------+
3 rows in set (0.00 sec)