【悄咪咪学MySql】7. 多表查询

别再写JS了吧
137 阅读5分钟

一. 多表关系

  • 多表中存在三种对应关系

    • 一对一
    • 一对多（多对一）
    • 多对多

  • 一对一关系中，需要定义其中一表为子表，为子表添加外键关联父表，并限制子表外键为UNIQUE以严格限制其为一对一关系

  • 一对多关系中，可以在"多表"中添加外键，不能限制子表外键为UNIQUE以表达其一对多关系

  • 多对多关系中，通常需要建立一张中间表，并设置两个外键关联向两张父表，且均不能限制子表外键为UNIQUE以表达其多对多关系

二. 多表查询

SELECT 多表不加入条件配对，就会产生笛卡尔积条数据

1. 分类

  • 连接查询
    • 内连接：相当于查询 A、B交际部分数据
    • 外连接：
      • 左连接：查询左表的所有数据，以及两张表交集部分数据
      • 右连接：查询右表的所有数据，以及两张表交集部分数据
    • 自连接：当前表与自身连接查询，自连接必须使用表别名
  • 子查询

三. 内连接 INNER JOIN

查询 A、B交际部分数据

1. 隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;

2. 显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件 ...;

3. 案例

查询每一个员工的姓名 及 关联的部门名称

  • 表结构：EMP, DEPT
  • 连接条件：EMP.DEPT_ID = DEPT.ID
-- 隐式内连接
SELECT E.NAME NAME, D.NAME DEPT_NAME FROM EMP E, DEPT D WHERE E.DEPT_ID = D.ID;

-- 显式内连接
SELECT E.NAME NAME, D.NAME DEPT_NAME FROM EMP E INNER JOIN DEPT D ON E.DEPT_ID = D.ID;

四. 外连接 OUTER JOIN

使用外连接时，左表不会根据 ON 连接条件过滤任何记录； 连接条件仅仅作为连接右表使用； 如果需要过滤左表，需要做子查询 或给外连接完成后的视图做 WHERE 过滤

1. 左连接

完全包含表1数据，部分包含表2数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件 [WHERE 条件] ...;

2. 右连接

完全包含表2数据，部分包含表1数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件 [WHERE 条件] ...;

3. 案例

  • 查询 EMP 表的所有数据，和对应的部门信息
SELECT E.*, D.NAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPT_ID = D.ID;
  • 查询 DEPT 表的所有数据，和对应的员工信息
SELECT E.*, D.* FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPT_ID = D.ID;

五. 自连接

1. 语法

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 连接条件 [WHERE 条件] ...;

2. 案例

  • 表结构：EMP

  • 连接条件：EMP.MANAGERID = EMP.ID

  • 查询员工 及其 所属领导的名字

SELECT 
    E1.NAME NAME,
    E2.NAME MANEGER_NAME
FROM
    EMP E1,
    EMP E2
WHERE
    E1.NAMAGERID = E2.ID;
  • 查询所有员工 EMP 极其领导名字 EMP，如果员工没有领导，也需要查询出来
SELECT
    E1.NAME NAME,
    E2.NAME MANAGER_NAME
FROM
    EMP E1
LEFT OUTER JOIN
    EMP E2
ON
    E1.NAMEGERID = E2.ID;

六. 联合查询 UNION

对于 UNION 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

1. 语法

SELECT 字段列表 FROM 表1 ...
UNION [ALL]
SELECT 字段列表 FROM 表2 ...;

UNION 合并结果去重，UNION ALL 合并结果不去重

2. 案例

  1. 将薪资低于 5000 的员工，和年龄大于 50 岁的员工全部查询出来
-- 单表查询
SELECT * FROM EMP WHERE SALARY < 5000 OR AGE > 50;

-- 联合查询
SELECT * FROM EMP WHERE SALARY < 5000
UNION
SELECT * FROM EMP WHERE AGE > 50;

七. 子查询

1. 概述

SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询。

2. 语法

SELECT * FROM T1 WHERE COLUMN1 = (SELECT COLUNM1 FROM T2);

3. 分类

  1. 根据子查询结果不同，分为： a. 标量子查询：子查询结果为单个值 b. 列子查询：子查询结果为一列 c. 行子查询：子查询结果为一行 d. 表子查询：子查询结果为多行多列

  2. 根据子查询位子不同，分为 a. WHERE 之后 b. FROM 之后 c. SELECT 之后

4. 标量子查询

常用操作符：> >= < <= = <> !=

  1. 查询 销售部 的所有员工信息
SELECT * FROM EMP WHERE DEPT_ID = (SELECT ID FROM DEPT WHERE NAME = '销售部');
  1. 查询在 东方白 入职之后的员工信息
SELECT * FROM EMP WHERE ENTRYDATE > (SELECT ENTRYDATE FROM EMP WHERE NAME = '东方白')

5. 列子查询

常用操作符描述
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围之内
ANY子查询返回别表中，又任意一个满足即可
SOME与 ANY 相同，使用 SOME 的地方都可以使用 ANY
ALL子查询返回列表的所有值都必须满足
  1. 查询 销售部 和 市场部 的所有员工信息
SELECT * FROM EMP WHERE DEPT_ID IN (SELECT ID FROM DEPT WHERE NAME IN ('销售部', '市场部'));
  1. 查询比 财务部 所有人工资都高的员工信息
-- 标量子查询
SELECT * FROM EMP WHERE SALARY > (SELECT MAX(SALARY) FROM EMP WHERE DEPT_ID = (SELECT ID FROM DEPT WHERE NAME = '财务部'));

-- 列子查询
SELECT * FROM EMP WHERE SALARY > ALL (SELECT SALARY FROM EMP WHERE DEPT_ID = (SELECT ID FROM DEPT WHERE NAME = '财务部'));
  1. 查询比 研发部 其中任意一人工资高的员工信息
-- 标量子查询
SELECT * FROM EMP WHERE SALARY > (SELECT MIN(SALARY) FROM EMP WHERE DEPT_ID = (SELECT ID FROM DEPT WHERE NAME = '研发部'));

-- 列子查询
SELECT * FROM EMP WHERE SALARY > ANY (SELECT SALARY FROM EMP WHERE DEPT_ID = (SELECT ID FROM DEPT WHERE NAME = '研发部'));

6. 行子查询

常用操作符：=, <>, !=, IN, NOT IN

  1. 查询与 张无忌 的薪资及领导相同的员工
SELECT * FROM EMP WHERE (SALARY, MANAGERID) = (SELECT SALARY, MANAGERID FROM EMP WHERE NAME = '张无忌');

7. 表子查询

常用操作符：IN

该子查询常用作临时视图，提供给父查询继续查询

  1. 查询与 鹿杖客，宋远桥 的职位和薪资相同的员工
SELECT * FROM EMP WHERE (JOB, SALARY) IN (SELECT JOB, SALARY FROM EMP WHERE NAME IN ('鹿杖客', '宋远桥'));
  1. 查询入职日期是 2006-01-01 之后的员工信息及其部门信息
-- 外连接查询
SELECT E.* FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPT_ID = D.ID WHERE E.ENTRYDATE > '2006-01-01';

-- 外连接 + 表子查询
SELECT E.* FROM (SELECT * FROM EMP WHERE ENTRYDATE > '2006-01-01') E LEFT OUTER JOIN DEPT D ON E.DEPT_ID = D.ID;

八. 案例

  1. 查询低于本部门平均工资的员工信息
SELECT E1.* FROM EMP E1 WHERE SALARY < (SELECT AVG(SALARY) FROM EMP E2 WHERE E1.DEPT_ID = E2.DEPT_ID);
avatar
文章
阅读
粉丝