前言:多表查询是非常重要的!其实就是多张表一起查询(当然是有一定条件的查询)
引入
首先我们先建立两个表:
CREATE TABLE DEPARTMENT (
DEP_ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键
DEP_NAME VARCHAR(20)
);
CREATE TABLE EMPLOYEE (
ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键
NAME VARCHAR(20) NOT NULL,
AGE INT,
SALARY INT,
JOIN_DATE TIMESTAMP,
DEP_ID INT, -- 外键
CONSTRAINT EMP_DEP_ID FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT (DEP_ID) -- 添加外键
);
按照我们之前的查询方法:
SELECT * FROM DEPARTMENT,EMPLOYEE;就是这样的结果:(多表查询出来的结果集也叫笛卡尔积)
概念:笛卡尔积:A,B两个集合,这两个集合的所有组成情况。
eg:A集合中有三个元素,B集合中有两个元素,那么A集合和B集合的笛卡尔积就有六种组合情况。
这里有很多不合理的数据:eg:我们添加外键的目的就是让多个员工对应一个部门,而不是多个部门对应一个员工(eg:第二三行的员工数据和部门对应不上)。所以我们需要消除这些无用的数据,或者说保留我们想要的数据
多表查询的分类
我们就用一下三种方式消除无用的数据:
1.内连接查询
2.外连接查询
3.子链接查询
1.内连接查询
1.1隐式内连接
- 就是向查询表时的where条件一样。eg:
SELECT * FROM DEPARTMENT,EMPLOYEE WHERE DEPARTMENT.`DEP_ID` = EMPLOYEE.`DEP_ID`;
(这里面的`DEP_ID` = EMPLOYEE.`DEP_ID`的单引号可以省略不写)
就会查询到下面的结果:
当然我们也可以把*换成指定的字段(字段就是一个表的某一列名称)eg:查询员工所对应的工资和部门:
(正规工作时我们都会:1.条件写一行,关键字写一行,方便别人也方便自己阅读。2.一般我们都会给这个表起个别名,方便我们书写。3.写注释。)
SELECT
t2.name, -- 员工表的姓名
t2.SALARY, -- 员工表的薪水
t1.DEP_NAME -- 部门表的名字
FROM
DEPARTMENT t1, -- 部门表起别名为t1
EMPLOYEE t2 -- 员工表起别名为t2
WHERE
t1.`DEP_ID` = t2.`DEP_ID`;
结果:
1.2显式内连接
同样的需求(查询员工所对应的工资和部门:)用显式内连接:
SELECT
t1.name, -- 员工表的姓名
t1.SALARY, -- 员工表的薪水
t2.DEP_NAME -- 部门表的名字
FROM
EMPLOYEE T1
INNER JOIN
DEPARTMENT T2
ON
T1.`DEP_ID` = T2.`DEP_ID`;
(关键字’INNER’时可以省略的)
可以看到可以达到一样的效果。
内连接查询我们需要知道些什么?
我们需要知道:
- 1.从哪些表中查。
- 2.筛选的条件是什么。
- 3.查询哪些字段。
2.外连接查询
需求:假如现在公司又来一个员工 马儿扎哈,但由于刚来,暂时没有部门,现在我要查询所有员工,如果这个员工有部门就显示部门,没有则只显示姓名等相关信息即可。
- 首先添加数据:
INSERT INTO
EMPLOYEE (ID,NAME,AGE,SALARY)
VALUES
(NULL,'马儿扎哈',22,12000);
- 其次,实现查询:
我们先用内连接的方法试一下:
SELECT
t2.name, -- 员工表的姓名
t2.SALARY, -- 员工表的薪水
t1.DEP_NAME -- 部门表的名字
FROM
DEPARTMENT t1, -- 部门表起别名为t1
EMPLOYEE t2 -- 员工表起别名为t2
WHERE
t1.`DEP_ID` = t2.`DEP_ID`;
发现没有马儿扎哈?!
原因一猜就知道了呗,因为新员工在员工表的DEP_ID是NULL,在父表 部门表中没有相对应的部门,所以当然不显示了。于是内连接无法实现我们这个需求,但外连接可以实现:
2.1左外连接查询
语法:
SELECT
T1.`NAME`, -- 员工表的姓名
T1.`AGE`, -- 员工表的年龄
T2.`DEP_NAME` -- 部门表的名字
FROM
EMPLOYEE T1
LEFT OUTER JOIN
DEPARTMENT T2
ON
T1.`DEP_ID`= T2.`DEP_ID`;
(关键字‘OUTER’可省略)
左外连接查询的作用:查询出左表的所有数据和交集部门的数据(哪个表写在前面哪个表就是左表)
所以,我们在查询时,把员工表写在了前面,所以左外连接就会查询出所有的员工表指定字段的数据,加上,同时显示满足条件的指定字段数据,也就满足了需求。
运行结果:
2.2右外连接查询
其实会了左外查询,右外查询自然就会了,一个道理。只要把关键字‘LEFT’变成->'RIGHT’即可。
右外连接查询的作用:查询出右表的所有数据和交集部门的数据(哪个表写在后面哪个表就是右表)
SELECT
T1.`NAME`,
T1.`AGE`,
T2.`DEP_NAME`
FROM
DEPARTMENT T2
RIGHT JOIN
EMPLOYEE T1
ON
T1.`DEP_ID` = T2.`DEP_ID`;
(把员工表放在右面(写在后面)就可以达到一样的效果了)
总结:
其实左外连接和右外连接在使用时没什么太大区别,就是一个相对的概念;用哪个都能达到想要的效果,换一下位置就行了。所以左外连接和右外连接我们掌握一个就够了
3.子链接查询
- 什么是子链接查询?
查询里面嵌套查询,这个嵌套查询就叫做子链接查询。
我们可以某一部的查询结果直接作为结果,再次当为条件使用。
eg:
- 需求:想要查询员工里工资最高的员工所有信息?
- 首先我们需要先知道最高的工资是多少:
- 查询所有信息即可。
SELECT MAX(employee.`SALARY`) FROM employee -- 查询最高工资
-- 子链接查询:
SELECT -- 查询工资最高的员工所有信息
*
FROM
employee T1
WHERE
T1.`SALARY` = (SELECT MAX(employee.`SALARY`) FROM employee);
子查询情况:
1.结果为单行单列,(即一个结果)。
如果是单行单列为第一次查询结果,我们常可以用它进行>,<,<=……的操作。
如上面的例子
SELECT MAX(employee.`SALARY`) FROM employee -- 查询最高工资
-- 子链接查询:
SELECT -- 查询工资最高的员工所有信息
*
FROM
employee T1
WHERE
T1.`SALARY` = (SELECT MAX(employee.`SALARY`) FROM employee);
查询结果:
2.结果为多行单列
如果是多行单列为第一次查询结果,我们就可以用‘IN……’进行操作。
eg:
- 需求:查询指定部门所有员工的姓名和薪水(这里以‘开发部’,‘策划部’为例)
- 首先找到指定部门所对应的部门号。
- 以这个部门号去查找满足条件的员工
代码:
(提一下:DEP_NAME = ‘开发部’ OR ‘策划部’ 这条sql语句等价于 DEP_NAME IN (‘开发部’,‘策划部’))
-- 查找指定部门对应的DEP_ID:
SELECT
T1.`DEP_ID`
FROM
department T1
WHERE
T1.`DEP_NAME` = '开发部' OR T1.`DEP_NAME` = '策划部';
-- 子链接查询:
SELECT
T1.`NAME`,
T1.`SALARY`
FROM
employee T1
WHERE
T1.`DEP_ID` IN (SELECT T2.`DEP_ID` FROM department T2 WHERE T2.`DEP_NAME` IN ('开发部','策划部'));
查询结果:
3.结果为多行多列
结果为多行多列,不就相当于一个表了嘛,就把他当成一个表就行。这个表也叫 “虚拟表”。
eg:
- 需求:查询某某日期之后入职的员工信息和部门信息
- 查询所有在某日期之后入职的员工信息
2.第一步查询到的应该是一个表,用这个表再进行操作
-- 查询在2020-05-07 11:40:01后入职的所有员工信息
SELECT
*
FROM
employee
WHERE
employee.JOIN_DATE > '2020-05-07 11:40:01';
结果:
那么显然这个查询得到的是一个表,我们只要把这个表也当作表来使用即可。
SELECT
*
FROM
department T1,
(SELECT
*
FROM
employee
WHERE
employee.JOIN_DATE > '2020-05-07 11:40:01') T2
WHERE
T1.`DEP_ID` = T2.DEP_ID;
运行结果:
后续:
- 表的查询往往都不是只有一个方法的,比如最后一个例子用内连接查询也完全可以:
SELECT
*
FROM
employee T1,
department T2
WHERE
T1.`JOIN_DATE` > '2020-05-07 11:40:01'
AND
T1.`DEP_ID` = T2.`DEP_ID`;