MySQL数据库的多表查询
1. 交叉连接
- 定义:交叉连接是不带WHERE 子句的多表查询,它返回被连接的两个表所有数据行的笛卡尔积。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
- 关键字:CROSS JOIN
- 格式:SELECT * from 表1 CROSS JOIN 表2
或者将两个表之间用逗号连接
mysql> select * from DEPT;
4 rows in set (0.00 sec)
mysql> select * from EMP;
14 rows in set (0.00 sec)
mysql> select * from EMP,DEPT;
56 rows in set (0.00 sec)
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';
+-------+--------+-----------+------+---------------------+---------+------+--------+--------+------------+----------+
| 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 |
2.2 不等连接
不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>
mysql> SELECT * from EMP INNER JOIN DEPT on EMP.deptno > DEPT.deptno where EMP.deptno = 20;
+-------+-------+---------+------+---------------------+---------+------+--------+--------+------------+----------+
| 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 |
3. 自身连接
- 定义:自身连接是指同一个表自己与自己进行连接。这种一元连接通常用于从自反关系(也称作递归关系)中抽取数据。
- 例如人力资源数据库中员工与领导的关系。
mysql> select A.ename 员工, B.ename 领导 from EMP AS A, EMP AS B where A.mgr = B.empno;
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
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);
4.2 右外连接
- 定义:在内连接的基础上,还包含右表中所有不符合条件的数据行,并在其中的左表列填写NULL
- 关键字:RIGHT JOIN
mysql> select * from EMP right join DEPT on EMP.deptno = DEPT.deptno where DEPT.deptno = 40;
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
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 ;
+
| 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(内连接)
| 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 |
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 |
3. natural right join(右外连接)
mysql> select * from EMP natural right join DEPT;
| 40 | OPERATIONS | BOSTON | NULL | NULL | NULL | NULL | NULL | NULL | NULL |