（1）多表查询的问题
- 数据准备
- 部门表dept
- 员工表emp：有外键对应dept ``` CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, name varchar(20) ); INSERT dept (name) VALUES(‘开发部’),(‘市场部’),(‘财务部’);
CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), gender CHAR(1), salary DOUBLE, join_date DATE, dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) );
INSERT INTO emp (name,gender,salary,join_date,dept_id) VALUES(‘孙悟空’,’男’,7200,’2013-02-24’,1); INSERT INTO emp (name,gender,salary,join_date,dept_id) VALUES(‘猪八戒’,’男’,3600,’2010-12-02’,2); INSERT INTO emp (name,gender,salary,join_date,dept_id) VALUES(‘唐僧’,’男’,9000,’2008-08-08’,2); INSERT INTO emp (name,gender,salary,join_date,dept_id) VALUES(‘白骨精’,’女’,5000,’2015-10-07’,3); INSERT INTO emp (name,gender,salary,join_date,dept_id) VALUES(‘蜘蛛精’,’女’,4500,’2011-03-14’,1);
- 同时查询两张表的所有数据，SELECT * FROM emp,dept;- dep三条数据，emp5条数据，多表查询最终出现15条数据- 笛卡尔积：取两个集合的所有组成情况
mysql> SELECT * FROM dept; +——+—————-+ | id | name | +——+—————-+ | 1 | 开发部 | | 2 | 市场部 | | 3 | 财务部 | +——+—————-+ 3 rows in set (0.00 sec)
mysql> SELECT * FROM emp; +——+—————-+————+————+——————+————-+ | id | name | gender | salary | join_date | dept_id | +——+—————-+————+————+——————+————-+ | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | +——+—————-+————+————+——————+————-+ 5 rows in set (0.00 sec)
mysql> SELECT * FROM emp,dept; +——+—————-+————+————+——————+————-+——+—————-+ | id | name | gender | salary | join_date | dept_id | id | name | +——+—————-+————+————+——————+————-+——+—————-+ | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | 1 | 开发部 | | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | 2 | 市场部 | | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | 3 | 财务部 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | 1 | 开发部 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | 2 | 市场部 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | 3 | 财务部 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | 1 | 开发部 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | 2 | 市场部 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | 3 | 财务部 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | 1 | 开发部 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | 2 | 市场部 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | 3 | 财务部 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | 1 | 开发部 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | 2 | 市场部 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | 3 | 财务部 | +——+—————-+————+————+——————+————-+——+—————-+ 15 rows in set (0.01 sec)
<a name="Xune0"></a>
### （2）多表查询的分类
<a name="501qq"></a>
#### 内连接查询
<a name="FMlPS"></a>
#### 外连接查询
<a name="0nHhc"></a>
#### 子查询
<a name="PjVF6"></a>
### （3）内连接查询
<a name="9CzhN"></a>
#### 隐式内连接
- 使用where条件消除无用数据
> -- 添加限定条件
> SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
-- 表的别名> SELECT * FROM emp AS t1,dept AS t2 WHERE t1.dept_id = t2.id;
> -- 指定列
> SELECT t1.id,t1.name,t2.name FROM emp AS t1,dept AS t2 WHERE t1.dept_id = t2.id;
<a name="4wrJg"></a>
#### 显示内连接
- INNER可以省略
> SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
<a name="afmt4"></a>
### （4）外连接查询
- 数据准备，dept、emp各自添加一条数据，并且没有对应关系
mysql> SELECT * FROM emp; +——+—————-+————+————+——————+————-+ | id | name | gender | salary | join_date | dept_id | +——+—————-+————+————+——————+————-+ | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | | 6 | 白龙马 | NULL | NULL | NULL | NULL | +——+—————-+————+————+——————+————-+ 6 rows in set (0.00 sec)
mysql> SELECT * FROM dept; +——+—————-+ | id | name | +——+—————-+ | 1 | 开发部 | | 2 | 市场部 | | 3 | 财务部 | | 4 | 保安部 | +——+—————-+ 4 rows in set (0.00 sec)
<a name="dPlLC"></a>
#### 左外连接
- OUTER可以省略
- 交集+左边集合的值
mysql> SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id; +——+—————-+————+————+——————+————-+———+—————-+ | id | name | gender | salary | join_date | dept_id | id | name | +——+—————-+————+————+——————+————-+———+—————-+ | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | 1 | 开发部 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | 1 | 开发部 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | 2 | 市场部 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | 2 | 市场部 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | 3 | 财务部 | | 6 | 白龙马 | NULL | NULL | NULL | NULL | NULL | NULL | +——+—————-+————+————+——————+————-+———+—————-+ 6 rows in set (0.00 sec)
<a name="VH1zA"></a>
#### 右外链接
- OUTER可以省略
- 交集+右边集合的值
mysql> SELECT * FROM emp RIGHT JOIN dept ON emp.dept_id = dept.id; +———+—————-+————+————+——————+————-+——+—————-+ | id | name | gender | salary | join_date | dept_id | id | name | +———+—————-+————+————+——————+————-+——+—————-+ | 1 | 孙悟空 | 男 | 7200 | 2013-02-24 | 1 | 1 | 开发部 | | 2 | 猪八戒 | 男 | 3600 | 2010-12-02 | 2 | 2 | 市场部 | | 3 | 唐僧 | 男 | 9000 | 2008-08-08 | 2 | 2 | 市场部 | | 4 | 白骨精 | 女 | 5000 | 2015-10-07 | 3 | 3 | 财务部 | | 5 | 蜘蛛精 | 女 | 4500 | 2011-03-14 | 1 | 1 | 开发部 | | NULL | NULL | NULL | NULL | NULL | NULL | 4 | 保安部 | +———+—————-+————+————+——————+————-+——+—————-+ 6 rows in set (0.00 sec) ```
（5）子查询
子查询结果是单行单列
— 小于平均工资 SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
子查询结果多行单列
— 查询市场部、财务部的员工 SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name in (‘财务部’,’市场部’));
- 子查询结果多行多列，作为一个虚拟表
— 查询入职在2011-11-11之后的员工信息和部门信息 SELECT FROM dept t1,(SELECT FROM emp WHERE emp.join_date > ‘2011-11-11’) t2 WHERE t1.id = t2.dept_id;
若有收获，就点个赞吧
0 人点赞
