MySQL数据库的多表查询

MySQL数据库的多表查询

1. 交叉连接

• 定义：交叉连接是不带WHERE 子句的多表查询，它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
• 关键字：CROSS JOIN
• 格式：SELECT * from 表1 CROSS JOIN 表2
  或者将两个表之间用逗号连接

mysql> select * from DEPT;
4 rows in set (0.00 sec)
//DEPT有4行
mysql> select * from EMP;
14 rows in set (0.00 sec)
//EMP有14行
mysql> select * from EMP,DEPT;
56 rows in set (0.00 sec)
//EMP和DEPT交叉连接查询后有4×14=56行。 

2. 内连接

• 定义：仅将两个表中满足连接条件的行组合起来作为结果集。在内连接中，只有在两个表中匹配的行才能在结果集中出现
• 关键词：INNER JOIN
• 格式：SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式

2.1 等值连接

等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列

mysql> select * from EMP INNER JOIN DEPT ON EMP.deptno = DEPT.deptno where EMP.deptno = '10';

+-------+--------+-----------+------+---------------------+---------+------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+---------------------+---------+------+--------+--------+------------+----------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+---------------------+---------+------+--------+--------+------------+----------+

2.2 不等连接

不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>

mysql> SELECT * from EMP INNER JOIN DEPT on EMP.deptno > DEPT.deptno where EMP.deptno = 20;

+-------+-------+---------+------+---------------------+---------+------+--------+--------+------------+----------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno | deptno | dname      | loc      |
+-------+-------+---------+------+---------------------+---------+------+--------+--------+------------+----------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |     10 | ACCOUNTING | NEW YORK |
+-------+-------+---------+------+---------------------+---------+------+--------+--------+------------+----------+

3. 自身连接

• 定义：自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系（也称作递归关系）中抽取数据。
• 例如人力资源数据库中员工与领导的关系。

mysql> select A.ename 员工, B.ename 领导 from EMP AS A, EMP AS B where A.mgr = B.empno;
//给EMP表起别名AS成A表和B表，对A表和B表进行连接。
+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

4. 外连接

4.1 左外连接

• 定义：在内连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL
• 关键字：LEFT JOIN

//首先插入一条左表中不符合条件的数据行
mysql> insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (1111, 'xxxx', 'CLERK', 7782, '1982-01-23', 1200 , null, 50);

//左外连接查看数据，不匹配项的右表用NULL填写
mysql> select * from EMP left join DEPT on EMP.deptno = DEPT.deptno where EMP.deptno = 50;
+-------+-------+-------+------+---------------------+---------+------+--------+--------+-------+------+
| empno | ename | job   | mgr  | hiredate            | sal     | comm | deptno | deptno | dname | loc  |
+-------+-------+-------+------+---------------------+---------+------+--------+--------+-------+------+
|  1111 | xxxx  | CLERK | 7782 | 1982-01-23 00:00:00 | 1200.00 | NULL |     50 |   NULL | NULL  | NULL |
+-------+-------+-------+------+---------------------+---------+------+--------+--------+-------+------+

4.2 右外连接

• 定义：在内连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL
• 关键字：RIGHT JOIN

mysql> select * from EMP right join DEPT on EMP.deptno = DEPT.deptno where DEPT.deptno = 40;
//右外连接的不匹配行的左表用NULL填写。
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| empno | ename | job  | mgr  | hiredate | sal  | comm | deptno | deptno | dname      | loc    |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
|  NULL | NULL  | NULL | NULL | NULL     | NULL | NULL |   NULL |     40 | OPERATIONS | BOSTON |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+

4.3 完全连接

• 定义：在内连接的基础上，还包含两个表中所有不符合条件的数据行，并在其中的左表、和右表列填写NULL
• 关键字：FULL JOIN （MySQL不支持）

MySQL不支持FULL JOIN但是可以用UNION来模拟完全连接
mysql> select * from EMP left join DEPT on EMP.deptno = DEPT.deptno 
    -> union
    -> select * from EMP right join DEPT on EMP.deptno = DEPT.deptno ;
//完全连接就是等值连接加上左连接和右连接的合集，UNION和以去重。
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+
|  1111 | xxxx   | CLERK     | 7782 | 1982-01-23 00:00:00 | 1200.00 |    NULL |     50 |   NULL | NULL       | NULL     |
|  NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |   NULL |     40 | OPERATIONS | BOSTON   |
+-------+--------+-----------+------+---------------------+---------+---------+--------+--------+------------+----------+

5. 自然连接

• 定义：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列

//自然连接有三类
1. natural join（内连接）
mysql> select * from EMP natural join DEPT;
+--------+-------+--------+-----------+------+---------------------+---------+---------+------------+----------+
| deptno | empno | ename  | job       | mgr  | hiredate            | sal     | comm    | dname      | loc      |
+--------+-------+--------+-----------+------+---------------------+---------+---------+------------+----------+
|     20 |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL | RESEARCH   | DALLAS   |
|     30 |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 | SALES      | CHICAGO  |
|     30 |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 | SALES      | CHICAGO  |
|     20 |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL | RESEARCH   | DALLAS   |
|     10 |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL | ACCOUNTING | NEW YORK |
+--------+-------+--------+-----------+------+---------------------+---------+---------+------------+----------+

2. natural left join （左外连接）
mysql> select * from EMP natural left join DEPT;
|     50 |  1111 | xxxx   | CLERK     | 7782 | 1982-01-23 00:00:00 | 1200.00 |    NULL | NULL       | NULL     |
//左外自然连接在natural join的基础上多了上面一行数据

3. natural right join（右外连接） 
mysql> select * from EMP natural right join DEPT;
|     40 | OPERATIONS | BOSTON   |  NULL | NULL   | NULL      | NULL | NULL                |    NULL |    NULL |
//右外自然连接在natural join的基础上多了上面一行数据