MySQL数据库——多表查询

多表查询

多表查询有如下几种：

1. 合并结果集；UNION 、 UNION ALL

2. 连接查询

   2.1内连接 [INNER] JOIN ON

   2.2外连接 OUTER JOIN ON

   • 左外连接 LEFT [OUTER] JOIN
   • 右外连接 RIGHT [OUTER] JOIN

3. 子查询

一、合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！

合并结果集有两种方式：

• UNION：去除重复记录，例如：SELECT* FROM t_1 UNION SELECT * FROM t_2；

• UNION ALL：不去除重复记录，例如：SELECT * FROM t_1 UNION ALL SELECT * FROM t_2。

注意：被合并的两个结果：列数、列类型必须相同（列类型可以不同，但这样将无意义，所以规定还是相同）。

1、 使用 UNION ：

2 、使用 UNION ALL ：

二、连接查询

连接查询就是求出多个表的乘积（笛卡尔积），例如t1连接t2，那么查询出的结果就是t1*t2

但这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。所以此时就规定我们对于要查询的多个表之间必须存在关联关系，通过关联关系去除笛卡尔积。

现在我们仿照oracle数据库的两张表举例：

emp表：

CREATE TABLE emp(
    empno   int,
    ename   varchar(50),
    job     varchar(50),
    mgr     int,
    hiredate    date,
    sal     decimal(7,2),
    comm    decimal(7,2),
    deptno  int
);
#添加数据SQL语句省略

dept表：

CREATE TABLE dept(
    deptno      int,
    dname       varchar(14),
    loc     varchar(13)
);
#添加数据SQL语句省略

以上表建立好以后，我们就可以进行连接查询了，连接查询分为内连接和外链接（包括左外连接和右外链接），如下所示：

一：内连接

内连接的特点：查询结果必须满足关联条件。

SQL标准的内连接为：

• SELECT * FROM 表1 [别名] INNER JOIN 表2 [别名] ON 关联条件;

即：

 SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

注意：on后面 主外键关系

此语法为MySQL提供的标准内连接语法，内连接还有另外一种写法（方言）：

• SELECT * FROM 表1 [别名] , 表2 [别名] , …, 表n [别名]
  WHERE 关联条件;

例如：

SELECT * FROM emp e,dept d 
WHERE e.deptno=d.deptno;

二、外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

1、左外连接

特点：左连接是先查询出左表（即以左表为主），然后查询右表，左表中满足条件和不满足条件都显示出来，右边不满足条件的显示NULL。

语法 ： SELECT * FROM 左表 [别名] LEFT OUTER JOIN 右表 [别名] ON 关联条件;

例如：

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

2、右外连接

特点：与左外连接相反，右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

语法：SELECT * FROM 左表 [别名] RIGHT OUTER JOIN 右表 [别名] ON 关联条件;

例如：

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

注：使用外链接只是从显示的角度消除了笛卡尔积，并没有真正避免笛卡尔积的产生，所以一般在数据量比较小的时候会使用外链接，数据量大或表比较多时，禁止使用外链接查询。

三、子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。里面的查询叫做子查询，外层的查询叫父查询，一般情况都是先执行子查询，再执行父查询。

子查询出现的位置：

a. where后，作为被查询的条件的一部分；（返回结果必须为单列）

b. from后，作临时表；（返回结果一般为多行多列）

当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any -- 存在即返回true

b. all  -- 全部满足返回true

子查询结果集的形式：

a. 单行单列（用于条件）

b. 多行单列（用于条件）

c. 多行多列（用于表）

示例：

• 查询工资高于JONES的员工。

-- 分析：

-- 查询条件：工资>JONES工资，其中JONES工资需要一条子查询。

-- 第一步：查询JONES的工资

    SELECT sal FROM emp WHERE ename='JONES';

-- 第二步：查询高于JONES工资的员工

    SELECT * FROM emp WHERE sal > (第一步结果);

-- 结果：

    SELECT * 
    FROM emp 
    WHERE sal > ( SELECT sal 
                  FROM emp  
                  WHERE ename='JONES');

• 查询与SCOTT同一个部门的员工。

-- 子查询作为条件
-- 子查询形式为单行单列

-- 分析：
-- 查询条件：部门=SCOTT的部门编号，其中SCOTT 的部门编号需要一条子查询。
-- 第一步：查询SCOTT的部门编号

    SELECT deptno FROM emp WHERE ename='SCOTT';

-- 第二步：查询部门编号等于SCOTT的部门编号的员工

    SELECT *  
    FROM emp  
    WHERE deptno = (SELECT deptno  
                    FROM emp  
                    WHERE ename='SCOTT');

• 工资高于30号部门所有人的员工信息

-- 方法一：不使用子查询：

    SELECT * FROMemp WHERE sal>(SELECT MAX(sal)FROM emp WHERE deptno=30);

-- 方法二 ：使用子查询
-- 查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。
-- 第一步：查询30部门所有人工资

    SELECT sal FROM emp WHERE deptno=30;

-- 第二步：查询高于30部门所有人工资的员工信息

    SELECT * FROM emp WHERE sal > ALL (第一步)

-- 结果：

    SELECT *  
    FROM emp  
    WHERE sal > ALL (SELECT sal  
                     FROM emp  
                     WHERE deptno=30)

--  子查询作为条件
--  子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

FROM 后面子查询

子查询的结果将作为一张临时表使用（多行多列），为了方便使用临时表中的数据，一般情况下要为其起个响亮的别名。

 SELECT ename,job,hiredate  
 FROM (SELECT ename,job,hiredate  
       FROM emp  
       WHERE hiredate>'1987-1-1') AS temp;