MySQL多表数据记录查询详解--688IT编程网

MySQL多表数据记录查询详解

在实际应⽤中，经常需要实现在⼀个查询语句中显⽰多张表的数据，这就是所谓的多表数据记录连接查询，简称来年将诶查询。

在具体实现连接查询操作时，⾸先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录。连接查询分为内连接查询和外连接查询。

在具体应⽤中，如果需要实现多表数据记录查询，⼀般不使⽤连接查询，因为该操作效率⽐较低。于是MySQL⼜提供了连接查询的替代操作，⼦查询操作。

1.关系数据操作：

在连接查询中，⾸先需要对两张或两张以上的表进⾏连接操作。连接操作是关系数据操作中专门⽤于数据操作的关系运算。

1.1 并（UNION）：

在SQL语⾔中存在⼀种关系数据操作，叫做并操作。“并”就是把具有相同字段数⽬和字段类型的表合并到⼀起。通过并操作将两张表的数据记录合并到⼀起，合并后的字段数为表1的字段数或者表2的字段数；合并后的总记录数为：表1的记录数 + 表2的记录数 - 表1和表2 重复的记录数。

1.2 笛卡尔积（CARTESIAN PRODUCT）：

在SQL语⾔中存在⼀种关系数据操作，叫做笛卡尔积操作。笛卡尔就是没有连接条件，表中的数据任意的组合。笛卡尔积的字段数为表1的字段数 + 表2的字段数。笛卡尔积的记录数为：表1的记录数 * 表2的记录数。

1.3 内连接（INNER JOIN）：

为了便于操作，专门提供了⼀种针对数据库操作的运算—连接（JOIN）。所谓连接就是在表关系的笛卡尔积数据记录中，按照相应字段值的⽐较条件进⾏选择⽣成⼀个新的关系。连接⼜分为内连接（INNER JOIN）、外连接（OUTER JOIN）、交叉连接（CROSS JOIN）。

所谓内连接，就是在表关系的笛卡尔积数据中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配的条件可以分成⾃然连接、等值连接和不等连接。

1.3.1 ⾃然连接（NATURAL JOIN）：

⾃然连接就是在表关系的笛卡尔积中，⾸先根据表关系中相同名称的字段⾃动进⾏记录匹配，然后去掉重复的字段。

通过⾃然连接后，新关系的字段数为：表1字段数 + 表2字段数 - 表1和表2中的重复字段数。⾃然连接后新关系的记录数为：表1的记录数 * 表2的记录数 - 表1和表2相同字段的值不相等记录数。

⾃然连接⼜如下特点：

1. 在具体执⾏⾃然连接时，会⾃动判断相同名称的字段，然后进⾏数据值的匹配。

2. 在执⾏完⾃然连接的新关系中，虽然可以指定包含哪些字段，但是不能指定执⾏过程中的匹配条件，即哪些字段的值进⾏匹配。

3. 在执⾏⾃然连接的新关系中，执⾏过程中所匹配的字段名只有⼀个，即会去掉重复字段。

vbs代码move方法移动窗口1.3.2 等值连接：

所谓等值连接操作就是表关系的笛卡尔积中，选择所匹配字段值相等的数据记录。关闭的resultset

通过等值连接后，新关系的字段数为：表1字段数 + 表2字段数。等值连接后新关系的记录数为：表1的记录数 * 表2的记录数 - 表1和表2相同字段的值不相等记录数。

与⾃然连接相⽐，等值连接操作需要在执⾏过程中⽤“=”指定匹配条件，在新关系中不会去掉重复字段。

1.3.3 不等连接：

所谓不等连接操作就是表关系的笛卡尔积中，选择所匹配字段值不相等的数据记录。

通过不等连接后，新关系的字段数为：表1字段数 + 表2字段数。等值连接后新关系的记录数为：表1的记录数 * 表2的记录数 - 表1和表2相同字段的值相等的记录数。

与⾃然连接相⽐，等值连接操作需要在执⾏过程中⽤“!=”指定匹配条件，在新关系中不会去掉重复字段。

1.4 外连接（OUTER JOIN）：

所谓外连接（OUTER JOIN），就是在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，⽽且还会保留部分不匹配的数据记录。按照保留不不匹配条件数据记录来源可以分为：左外连接、右外连接、全外连接。

1.4.1 左外连接：

所谓左外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。

通过左外连接后，新关系的字段数为：左表字段数 + 右表字段数。左外连接后新关系的记录数为：左表的记录数 * 右表的记录数 - 左表和右表相同字段的值不相等的记录数 + 左表中未匹配的记录数。

1.4.2 右外连接：

所谓右外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。

通过右外连接后，新关系的字段数为：左表字段数 + 右表字段数。右外连接后新关系的记录数为：左表的记录数 * 右表的记录数 - 左表和右表相同字段的值不相等的记录数 + 右表中未匹配的记录数。

1.4.3 全外连接：

所谓右外连接操作就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录。

通过全外连接后，新关系的字段数为：左表字段数 + 右表字段数。全外连接后新关系的记录数为：左表的记录数 * 右表的记

录数 - 左表和右表相同字段的值不相等的记录数 + 左表未匹配的记录数 + 右表中未匹配的记录数。

2.内连接查询：

在MySQL中实现连接查询有两种语法：

1. 在from⼦句中利⽤逗号区分多个表，在where⼦句中通过逻辑表达式来实现匹配条件，从⽽实现表的连接。

2. ANSI连接语法形式，在from⼦句中使⽤“join on”关键字，⽽连接条件写在关键字on⼦句中。推荐使⽤第⼆种⽅式。

按照匹配条件，内连接查询可以分为两类：等值连接；不等连接。

内连接查询语法为：

select field1, field2 ...fieldn

from join_tablename1 inner join join_tablename2 [inner join join_tablename]

on join_condition

//参数filedn 表⽰所要查询的字段名称，来源于所连接的表join_tablename1 和 join_tablename2，关键

字inner join表进⾏内连接，join_condition表⽰进⾏匹配的条件。

2.1 ⾃连接：

内连接查询中存在⼀种特殊的等值连接—⾃连接。所谓⾃连接就是指表与其⾃⾝进⾏连接。

⽰例（查询每个雇员的姓名、职位、领导姓名）：

mysql> ame,e.ame from t_employee e inner join t_employee l on e.pno;

+---------+----------+-------+

| 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.2等值连接：

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

⽰例：

个人主页静态网页模板mysql> ame,e.job,d.dname,d.loc from t_employee e inner join t_dept d on e.deptno=d.deptno;

+-------+---------+-----------+------------+----------+

+-------+---------+-----------+------------+----------+

+-------+---------+-----------+------------+----------+

14 rows in set (0.00 sec)

2.3不等连接：

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

⽰例：

mysql> ame employeename, e.ame loadername from t_employee e inner join t_employee l =l.empno

pno&pno;

+--------------+----------+------------+

| 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)

3.外连接查询：

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

语法为：

select field1, field2, ...fieldn

from join_tablename1 left|rigth|full [outer] join join_tablename2

on join_condition

3.1左外连接：

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

⽰例：

mysql> ame employeename, e.job ame leadername from t_employee e left join t_employee l =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)

3.2右外连接：

外连接查询中的右外连接，就是指新关系中执⾏匹配条件时，以关键字right join 右边的表为参考。

4.合并查询数据记录：

在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

......

5.⼦查询：

在MySQL中虽然可以通过连接查询实现多表查询数据记录，但却不建议使⽤。这是因为连接查询的性能很差。因此出现了

连接查询的替代者⼦查询。推荐使⽤⼦查询来实现多表查询数据记录。

5.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⼦句中的⼦查询，该位置处的⼦查询⼀般返回多⾏多列数据记录，可以当作⼀张临时表。

5.2 返回结果为单⾏单列和单⾏多列⼦查询：

当⼦查询的返回结果为单⾏蛋类数据记录时，该⼦查询语句⼀般在主查询语句的where⼦句中，通常会包含⽐较运算符（> < = != 等）

5.2.1 单⾏单列⼦查询：

⽰例（⼯资⽐Smith⾼的全部雇员信息）：

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

+-------+---------+-----------+------+------------+---------+---------+--------+

+-------+---------+-----------+------+------------+---------+---------+--------+

| 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)

5.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)

5.3 返回结果为多⾏单列⼦查询：

当⼦查询的返回结果为多⾏单列数据记录时，该⼦查询语句⼀般会在主查询语句的where⼦句中出现，通常会包含IN ANY ALL EXISTS等关键字。

5.3.1 带有关键字in的⼦查询：

当主查询的条件在⼦查询的查询结果中时，可以通过关键字in来进⾏判断。相反，如果想实现主查询的条件不在⼦查询的查询结果中时，可以通过关键字not in来进⾏判断。

⽰例：

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

+-------+---------+-----------+------+------------+---------+---------+--------+

+-------+---------+-----------+------+------------+---------+---------+--------+

| 7369 | SMITH | CLEAR | 7902 | 1981-03-12 | 800.00 | NULL | 20 |