文章目录

  • Select语句
  • 一、SQL底层执行原理
    • （一）、查询的结构
    • （二）、SQL语句的执行过程
      • 1、WHERE 为什么不包含聚合函数的过滤条件？（面试题）
      • 2、为什么where 的效率要比 having的效率高？（面试题）
      • 3、WHERE 与 HAVING 的对比
  • 二、基本的SELECT语句
    • （一）基本的SELECT语句
      • SELECT…
      • SELECT … FROM
      • 列的别名 AS
      • 去除重复行 DISTINCT
      • 空值参与运算
      • 着重号 “
      • 查询常数
      • 显示表结构 DESCRIBE/DESC
    • （二）、过滤数据 where
    • （三）、BETWEEN AND 运算符
    • （四）、IN/NOT IN 运算符
    • （五）、模糊匹配 LIKE 运算符
    • （六）、ORDER BY
      • 排序规则
      • 单列排序
      • 多列排序
    • （七）、分页 lIMIT
      • 规则
    • （八）、MySQL 8 分页新特性 LIMIT … OFFSET …
  • 三、运算符
    • （一）、算数运算符
    • （二）、比较运算符
      • 1、符号类型的运算符
      • 2、非符号类型的运算符
        • 空运算符 IS NULL \ ISNULL 和 非空运算符 IS NOT NULL
        • 最大小值运算符 LEAST 、GREATEST
        • REGEXP \ RLIKE :正则表达式
    • （三）、逻辑运算符 OR || AND && NOT ! XOR 要保证条件完整
    • （四）、位运算符 & | ^ ~ >> <<
    • （五）、运算符的优先级
    • （六）、拓展：使用正则表达式查询
  • 四、子查询
    • （一）、实际问题
    • （二）、子查询的基本语法结构
    • （三）、子查询的分类
      • 1、角度1：从内查询返回的结果的条目数 单行子查询 vs 多行子查询
      • 2、角度2：内查询是否被执行多次 相关子查询 vs 不相关子查询
    • 非相关子查询操作
    • （四）、单行子查询 —> 非相关子查询
      • 1、单行子查询操作符
      • 2、WHERE中的子查询
      • 3、HAVING 中的子查询
      • 4、CASE中的子查询
      • 5、子查询中的空值问题
      • 6、非法使用子查询
    • （五）、多行子查询 —> 非相关子查询
      • 1、多行子查询的操作符 IN、ANY、ALL、SOME(同ANY)
      • 2、In
      • 3、ANY / ALL:
      • 4、多行子查询空值问题
    • 相关子查询操作
    • （六）、相关子查询执行流程
    • （七）、在 FROM 中使用子查询（相关子查询）
    • （八）、在ORDER BY 中使用子查询（相关子查询）
    • （九）、EXISTS 与 NOT EXISTS关键字
      • 1、EXISTS 案例：
      • 2、NOT EXISTS案例：
      • 3、子查询 更新
      • 4、子查询 删除
    • （十）、问题：自连接和子查询有好坏之分吗？
    • （十一）、相关子查询和非相关子查询的经典题型
      • 1、非相关子查询
      • 2、相关子查询
  • 五、多表查询
    • （一）、7种SQL JOINS的实现
    • （二）、多表查询分类
      • 1、方式一：等值连接 vs 非等值连接
        • ① 等值连接
        • ② 非等值连接
      • 2、方式二：自连接 vs 非自连接
      • 3、方式三：内连接 vs 外连接
    • （三）、SQL92：使用(+)创建连接
    • （四）、SQL99语法实现多表查询
      • 基本语法：使用JOIN…ON子句创建连接的语法
      • 1、SQL99 内连接语句 INNER JOIN ON / JOIN ON
      • 2、SQL99 左外连接语句 LEFT OUTER JOIN / LEFT JOIN
      • 3、SQL99 右外连接语句 RIGHT OUTER JOIN
      • 4、满外连接(FULL OUTER JOIN)
    • （五）、合并查询 UNION的使用
    • （六）、SQL99语法新特性
      • 1、自然连接
      • 2、USING连接

作者: gh-xiaohe
gh-xiaohe的博客
觉得博主文章写的不错的话，希望大家三连（✌关注，✌点赞，✌评论），多多支持一下！！

Select语句

一、SQL底层执行原理

（一）、查询的结构

sql92语法：

  • SELECT    (存在聚合函数）
  • FROM
  • WHERE   多表的连接条件 AND 不包含聚合函数的过滤条件、不可以使用别名
  • HAVING   包含聚合函数的过滤条件
  • ORDER BY   (ASC / DESC )
  • LIMIT

sql99语法：

  • SELECT   (存在聚合函数)
  • FROM   (LEFT / RIGHT)JOIN …ON 多表的连接条件
  • (LEFT / RIGHT)JOIN …ON…
  • WHERE   不包含聚合函数的过滤条件、不可以使用别名
  • GROUP BY   AND/OR 不包含聚合函数的过滤条件
  • HAVING   包含聚合函数的过滤条件
  • ORDER BY   (ASC / DESC )
  • LIMIT

总结：

  • from：从哪些表中筛选
  • on：关联多表查询时，去除笛卡尔积
  • where：从表中筛选的条件
  • group by：分组依据
  • having：在统计结果中再次筛选
  • order by：排序
  • limit：分页

（二）、SQL语句的执行过程

    FROM …,…-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY ->HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

1、WHERE 为什么不包含聚合函数的过滤条件？（面试题）

    从执行过程来看 可以向前引用，但是没有办法向下引用

2、为什么where 的效率要比 having的效率高？（面试题）

    由SQL的执行顺序可知，where的执行在 having的前面，现在where 中过滤一些条件，分组时效率高很多，提升很大，如果此时 过滤条件写在 having 中 先分组后进行条件筛选，很多数据都是不需要的数据，无用功

3、WHERE 与 HAVING 的对比

  • 1、从适用范围上来讲，HAVING的适用范围更广。

  • 2、如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING

优点缺点
WHERE先筛选数据再关联，执行效率高不能使用分组中的计算函数进行筛选
HAVING可以使用分组中的计算函数在最后的结果集中进行筛选，执行效率较低

二、基本的SELECT语句

（一）基本的SELECT语句

SELECT…

SELECT 1; #没有任何子句SELECT 9/2; #没有任何子句SELECT 1 + 1,3 * 2FROM DUAL; #dual：伪表

SELECT … FROM

不推荐你直接使用 SELECT * 进行查询

列的别名 AS

使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。不要使用 ’ ’ 单引号

MySql 中 ’ ’ 单号好使 mysql 不严谨 order 中就不可以 （规定中也不可以）

去除重复行 DISTINCT

空值参与运算

    所有运算符或列值遇到null值，运算的结果都为null

    MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

着重号 “

    我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对“（着重号）引起来。

查询常数

显示表结构 DESCRIBE/DESC

使用DESCRIBE 或 DESC 命令，表示表结构。

DESCRIBE employees; #显示了表中字段的详细信息或DESC employees;

（二）、过滤数据 where

（三）、BETWEEN AND 运算符

between and 先写小值、在写大值，不可交换

#查询工资在6000 到 8000的员工信息SELECT employee_id,last_name,salaryFROM employees#where salary between 6000 and 8000;WHERE salary >= 6000 && salary <= 8000;

（四）、IN/NOT IN 运算符

  • in： 判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
  • not in： 用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。
#练习：查询部门为10,20,30部门的员工信息SELECT last_name,salary,department_idFROM employees#where department_id = 10 or department_id = 20 or department_id = 30;WHERE department_id IN (10,20,30); #练习：查询工资不是6000,7000,8000的员工信息SELECT last_name,salary,department_idFROM employeesWHERE salary NOT IN (6000,7000,8000);

（五）、模糊匹配 LIKE 运算符

  • LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
    • “%”：匹配0个或多个字符。
    • “_”：只能匹配一个字符。
    • 此时使用了转移字符 \ 后 _ 在此时就不表示任意一人字符
#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息#写法1：SELECT last_nameFROM employeesWHERE last_name LIKE '%a%' AND last_name LIKE '%e%';#写法2：SELECT last_nameFROM employeesWHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%'; #练习：查询第3个字符是'a'的员工信息SELECT last_nameFROM employeesWHERE last_name LIKE '__a%';# 此时使用了转移字符 \  后 _ 在此时就不表示任意一人字符SELECT job_idFROM jobsWHERE job_id LIKE 'IT\_%';

（六）、ORDER BY

排序规则

  • ASC（ascend）: 升序
  • DESC（descend）:降序
  • ORDER BY 子句在SELECT语句的结尾
  • 没有使用排序操作： 默认情况下查询返回的数据是按照添加数据的顺序显示的。
  • 在ORDER BY 后没有指名排序的方式 默认按照升序排列。
  • 可以使用列的别名，进行排序

单列排序

# 练习1：按照salary从高到低的顺序显示员工信息SELECT employee_id,last_name,salaryFROM employeesORDER BY salary DESC;

多列排序

#练习：显示员工信息，按照department_id的降序排列，salary的升序排列SELECT employee_id,salary,department_idFROM employeesORDER BY department_id DESC,salary ASC;

（七）、分页 lIMIT

规则

  • 公式： （当前页数-1）* 每页条数，每页条数
  • 位置： LIMIT 子句必须放在整个SELECT语句的最后。
  • 好处： 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回，减少数据表的网络传输量，也可以提升查询效率
SELECT employee_id,last_name,salaryFROM employeesWHERE salary > 6000ORDER BY salary DESC#limit 0,10;LIMIT 10;

（八）、MySQL 8 分页新特性 LIMIT … OFFSET …

#练习：表里有107条数据，我们只想要显示第 32、33 条数据怎么办呢？SELECT employee_id,last_nameFROM employeesLIMIT 2 OFFSET 31;

三、运算符

（一）、算数运算符

    连接运算符前后的两个数值或表达式，对数值或表达式进行 加（+）、减（-）、乘（*）、除（/）和取模（%）运算

（二）、比较运算符

1、符号类型的运算符

    (!=)、、<、、>=，比较运算符用来对表达式左边的操作数和右边的操作数进行比较

  • 结果为真： 返回1
  • 结果为真： 返回0
  • 其他情况则： 返回null
  • 经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

2、非符号类型的运算符

空运算符 IS NULL \ ISNULL 和 非空运算符 IS NOT NULL

判断一个值是否为NULL，如果为NULL则返回1，否则返回0

#练习：查询表中commission_pct为null的数据有哪些SELECT last_name,salary,commission_pctFROM employeesWHERE commission_pct IS NULL;#或SELECT last_name,salary,commission_pctFROM employeesWHERE ISNULL(commission_pct); #查询commission_pct不等于NULLSELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct  NULL;SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

最大小值运算符 LEAST 、GREATEST

    GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值NULL。

SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);

REGEXP \ RLIKE :正则表达式

（三）、逻辑运算符 OR || AND && NOT ! XOR 要保证条件完整

    逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。

（四）、位运算符 & | ^ ~ >> <<

    位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数

（五）、运算符的优先级

    数字编号越大，优先级越高，优先级高的运算符先进行计算。赋值运算符的优先级最低，使用“()”括起来的表达式的优先级最高。

（六）、拓展：使用正则表达式查询


四、子查询

子查询可以应用的位置：

​ 结论：除了GROUP BY 和 LIMIT 之外，其他位置都可以声明子查询！

（一）、实际问题


 
方式一：进行两次查询

SELECT salaryFROM employeesWHERE last_name = 'Abel'; SELECT last_name,salaryFROM employeesWHERE salary > 11000 ;

方式二：自连接

SELECT e2.last_name,e2.salaryFROM employees e1,employees e2WHERE e1.last_name = 'Abel' #多表的连接条件AND e2.`salary` > e1.`salary`;

方式三：子查询

SELECT last_name,salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel');

（二）、子查询的基本语法结构

  • 子查询（内查询）在主查询之前一次执行完成

  • 子查询的结果被主查询（外查询）使用 。

  • 注意事项

    • 子查询要包含在括号内

    • 将子查询放在比较条件的右侧

    • 单行操作符对应单行子查询，多行操作符对应多行子查询

（三）、子查询的分类

1、角度1：从内查询返回的结果的条目数 单行子查询 vs 多行子查询

     按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、多行子查询

2、角度2：内查询是否被执行多次 相关子查询 vs 不相关子查询

按内查询是否被执行多次，分为 相关(或关联)子查询 和 不相关(或非关联)子查询

  • 不相关子查询：子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行
  • 相关子查询：如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部嵌套的执行方式：

举例：

  • 相关子查询的需求：查询工资大于本部门平均工资的员工信息。（变化）
  • 不相关子查询的需求：查询工资大于本公司平均工资的员工信息。（固定）

非相关子查询操作

（四）、单行子查询 —> 非相关子查询

1、单行子查询操作符

操作符含义
=equal to
>greater than
>=greater than or equal to
<less than
<=less than or equal to
not equal to

2、WHERE中的子查询

#题目1：查询工资大于149号员工工资的员工的信息SELECT employee_id,last_name,salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE employee_id = 149);#题目2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资SELECT last_name,`job_id`,salaryFROM employeesWHERE job_id = (SELECT job_idFROM employeesWHERE `employee_id` = 141)AND salary > (SELECT salaryFROM employeesWHERE `employee_id` = 143);#题目3：返回公司工资最少的员工的last_name,job_id和salarySELECT last_name,job_id,salaryFROM employeesWHERE salary = (SELECT MIN(salary)FROM employees);#题目4：查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id。#方式1：SELECT employee_id,manager_id,department_idFROM employeesWHERE manager_id = (    SELECT manager_id    FROM employees    WHERE employee_id = 141   )AND department_id = (    SELECT department_id    FROM employees    WHERE employee_id = 141   )AND employee_id  141; ----------------------------------------- #方式2：了解SELECT employee_id,manager_id,department_idFROM employeesWHERE (manager_id,department_id) = (    SELECT manager_id,department_id            FROM employees    WHERE employee_id = 141   )AND employee_id  141;

3、HAVING 中的子查询

  • 首先执行子查询
  • 向主查询中的HAVING 子句返回结果。
#题目1：查询最低工资大于110号部门最低工资的部门id和其最低工资SELECT department_id,MIN(salary)FROM employeesWHERE department_id IS NOT NULLGROUP BY department_idHAVING MIN(salary) > (SELECT MIN(salary)FROM employeesWHERE department_id = 110     );

4、CASE中的子查询

    在CASE表达式中使用单列子查询

#题目：显式员工的employee_id,last_name和location。#其中，若员工department_id与location_id为1800的department_id相同，#则location为’Canada’，其余则为’USA’。SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_idFROM departmentsWHERE location_id =1800      )THEN 'Canada'ELSE 'USA'END"location"FROM employees;

5、子查询中的空值问题

    子查询不返回任何行

# 子查询中的空值问题SELECT last_name, job_idFROM   employeesWHERE  job_id =                (SELECT job_id                 FROM   employees                 WHERE  last_name = 'Haas');  # 没有此人,无job_id,无法查询出结果,返回空值

6、非法使用子查询


（五）、多行子查询 —> 非相关子查询

1、多行子查询的操作符 IN、ANY、ALL、SOME(同ANY)

操作符含义
IN等于列表中的任意一个
ANY需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME实际上是ANY的别名，作用相同，一般常使用ANY

2、In

# 案例1：返回每组员工中工资最少的的 员工id 和 姓名SELECT employee_id, last_nameFROM   employeesWHERE  salary IN                (SELECT   MIN(salary)                 FROM     employees                 GROUP BY department_id);

3、ANY / ALL:

# 体会 ANY 和 ALL 的区别        # 非诚勿扰综艺，五个女生给你亮灯，        # ANY 随便牵手哪一个都可以  只选一个        # ALL 全部都带走        #题目1：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salaryFROM employeesWHERE job_id  'IT_PROG'AND salary < ANY (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'  ); #  小于 9000 就可以                      #题目2：返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的员工号、姓名、job_id 以及salarySELECT employee_id,last_name,job_id,salaryFROM employeesWHERE job_id  'IT_PROG'AND salary < ALL (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG');# 小于 4200  最小工资才可以             #题目3：查询平均工资最低的部门id# 让查询的结果充当一张表，此时列叫 avg_sal 而不是一个函数 MIN(avg_sal)#MySQL中聚合函数是不能嵌套使用的。 Order中支持聚合函数嵌套#方式1：SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) = (SELECT MIN(avg_sal)FROM(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id)  t_dept_avg_sal# Every derived table must have its own alias 每一个保留下来的表，起个别名); #方式2：SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary) <= ALL( # <= ALL 小于等于所有的 相当于小于最小的 SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id)

4、多行子查询空值问题

# 空值问题# 结果中含有空值  内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况SELECT last_nameFROM employeesWHERE employee_id NOT IN (SELECT manager_id FROM employees# where manager_id IS NOT NULL);

注意：结果中含有空值 内查询还有null值 在含有null的情况下 再去判断 结果 就是一个空的情况

结果：返回的结果为空

相关子查询操作

（六）、相关子查询执行流程

    子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表并进行了条件关联因此每执行一次外部查询，子查询都要重新计算一次这样的子查询就称之为关联子查询 。

    相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

（七）、在 FROM 中使用子查询（相关子查询）

    from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名， 把它当成一张“临时的虚拟的表”来使用。

#回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id#方式1：使用相关子查询SELECT last_name,salary,department_idFROM employees e1WHERE salary > (  SELECT AVG(salary)FROM employees e2 WHERE department_id = e1.department_id # 比较的是那个部门);#方式2：在FROM中声明子查询SELECT e1.last_name,e1.salary,e1.department_idFROM employees e1,(SELECT department_id,AVG(salary) avg_sal # 需要传入department_id 用于做链接条件FROM employeesGROUP BY department_id) t_dept_avg_salWHERE e1.department_id = t_dept_avg_sal.department_idAND e1.salary > t_dept_avg_sal.avg_sal; # avg_sal 此时是字段 不是函数 必须有别名

（八）、在ORDER BY 中使用子查询（相关子查询）

# 案例1 查询员工的id,salary,按照department_name 排序SELECT employee_id,salaryFROM employees eORDER BY ( SELECT department_name FROM departments d WHERE e.`department_id` = d.`department_id`) ASC;# 案例2 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，#       输出这些相同id的员工的employee_id,last_name和其job_id SELECT e.employee_id,last_name,e.job_idFROM employees eWHERE 2 <= (      SELECT COUNT(*)      FROM `job_history` j      WHERE j.employee_id = e.employee_id);

（九）、EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

  • 子查询中不存在满足条件的行
    • 条件返回 FALSE
    • 继续在子查询中查找
  • 子查询中存在满足条件的行：
    • 不在子查询中继续查找
    • 条件返回 TRUE

NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

1、EXISTS 案例：

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息#方式1：自连接SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_idFROM employees emp JOIN employees mgrON emp.manager_id = mgr.employee_id; #方式2：子查询SELECT employee_id,last_name,job_id,department_idFROM employeesWHERE employee_id IN (SELECT DISTINCT manager_idFROM employees); #方式3：使用EXISTSSELECT employee_id,last_name,job_id,department_idFROM employees e1WHERE EXISTS (       SELECT *  # 此时 与 * 还是什么字段 已经不重要了，找到记录就要，没有找到记录就不要了       FROM employees e2       WHERE e1.`employee_id` = e2.`manager_id` # 相当于一条条数据往下找，找到满足的数据 为 true 就不像下寻找     );

2、NOT EXISTS案例：

# 查询departments表中，不存在于employees表中的部门的department_id和department_name#方式1： 外连接 相当于 只需要个 一个表 去除 两个表的交集部门SELECT d.department_id,d.department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL; #方式2：SELECT department_id,department_nameFROM departments dWHERE NOT EXISTS (SELECT *FROM employees eWHERE d.`department_id` = e.`department_id`);

3、子查询 更新

# 使用相关子查询依据一个表中的数据更新另一个表的数据。# 1）ALTER TABLE employeesADD(department_name VARCHAR2(14));# 2）UPDATE employees eSET department_name = (SELECT department_name           FROM  departments d           WHERE e.department_id = d.department_id);

4、子查询 删除

# 使用相关子查询依据一个表中的数据删除另一个表的数据DELETE FROM employees eWHERE employee_id in      (SELECT employee_id      FROM  emp_history      WHERE employee_id = e.employee_id);

（十）、问题：自连接和子查询有好坏之分吗？

  • 解答：自连接方式好因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

  • 可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

# 问题：谁的工资比Abel的高？# 方式1：自连接SELECT e2.last_name,e2.salaryFROM employees e1,employees e2WHERE e2.`salary` > e1.`salary` #多表的连接条件AND e1.last_name = 'Abel';# 方式2：子查询SELECT last_name,salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel');

（十一）、相关子查询和非相关子查询的经典题型

1、非相关子查询

#8.查询平均工资最低的部门信息#方式1：SELECT *FROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) = (SELECT MIN(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal ));#方式2：SELECT *FROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) <= ALL(SELECT AVG(salary)FROM employeesGROUP BY department_id)); #方式3： LIMIT SELECT *FROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) =(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASCLIMIT 1)); #方式4： SELECT d.*FROM departments d,(SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASCLIMIT 0,1) t_dept_avg_salWHERE d.`department_id` = t_dept_avg_sal.department_id#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）#方式1：SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_salFROM departments dWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) = (SELECT MIN(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal )); #方式2： SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_salFROM departments dWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) <= ALL(SELECT AVG(salary)FROM employeesGROUP BY department_id)); #方式3： LIMIT SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_salFROM departments dWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING AVG(salary ) =(SELECT AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASCLIMIT 1)); #方式4： SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.`department_id`) avg_salFROM departments d,(SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASCLIMIT 0,1) t_dept_avg_salWHERE d.`department_id` = t_dept_avg_sal.department_id

2、相关子查询

#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询） SELECT department_nameFROM departments dWHERE 5 < (   SELECT COUNT(*)   FROM employees e   WHERE d.department_id = e.`department_id`  );

五、多表查询

表连接的约束条件可以有三种方式：WHERE, ON, USING

  • WHERE：适用于所有关联查询
  • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

注意关联条件：

  • 把关联条件写在where后面
  • 把关联条件写在on后面，只能和JOIN一起使用
  • 把关联字段写在using()中，只能和JOIN一起使用,而且两个表中的关联字段必须名称相同，而且只能表示=
  • n张表关联，需要n-1个关联条件

注意：

    我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

    【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能

多表查询的好处：相对于把所有字段整合到一张表中

  • 减少了冗余的字段
  • 查询时，数据量过大 io次数也增加 减少了io次数
  • 方便维护
  • 不涉及表锁定问题

（一）、7种SQL JOINS的实现

# 中图：内连接SELECT employee_id,department_nameFROM employees e JOIN departments dON e.`department_id` = d.`department_id`; # 左上图：左外连接SELECT employee_id,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`; # 右上图：右外连接SELECT employee_id,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`; # 左中图：SELECT employee_id,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULL; # 右中图：SELECT employee_id,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL;  # 左下图：满外连接# 方式1：左上图 UNION ALL 右中图 SELECT employee_id,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`UNION ALLSELECT employee_id,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL;  # 方式2：左中图 UNION ALL 右上图 SELECT employee_id,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULLUNION ALLSELECT employee_id,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`; # 右下图：左中图  UNION ALL 右中图SELECT employee_id,department_nameFROM employees e LEFT JOIN departments dON e.`department_id` = d.`department_id`WHERE d.`department_id` IS NULLUNION ALLSELECT employee_id,department_nameFROM employees e RIGHT JOIN departments dON e.`department_id` = d.`department_id`WHERE e.`department_id` IS NULL;

（二）、多表查询分类

1、方式一：等值连接 vs 非等值连接

① 等值连接

# 查询员工名 Abel 的在那个城市工作 #1、先查出 员工白中叫Abel 的人 的部门idSELECT * FROM employeesWHERE last_name = 'Abel';  #2、根据部门 id 查询出 他所在的地点idSELECT * FROM departmentsWHERE `department_id` = 80; #3、根据他所在的地点id 查询出所在的城市SELECT city FROM locationsWHERE `location_id` = 2500; # 查询员工名 Abel 的在那个城市工作 SELECT last_name,locations.`city`FROM employees,departments,`locations`WHERE employees.department_id = departments.department_id AND last_name = 'Abel' AND departments.location_id = locations.`location_id`;

  • 拓展：

  • 1、多个连接条件与 AND 操作符

  • 2、区分重复的列名

    • 多个表中有相同列时，必须在列名之前加上表名前缀。

    • 在不同表中具有相同列名的列可以用 表名 加以区分。

  • 3、表的别名

    • 使用别名可以简化查询。

    • 列名前使用表名前缀可以提高查询效率。

  • 3、连接n个表，至少需要n-1个连接条件。

  • 注意：如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。

② 非等值连接

# 查询员工的工资水平SELECT em.last_name,em.`salary`,job.`grade_level`FROM `employees` em,`job_grades` jobWHERE em.`salary` BETWEEN job.`lowest_sal` AND job.`highest_sal`;

2、方式二：自连接 vs 非自连接

     当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

#练习：查询员工id,员工姓名及其管理者的id和姓名SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_nameFROM employees emp ,employees mgrWHERE emp.`manager_id` = mgr.`employee_id`;

3、方式三：内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

  • 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  • 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

#练习：查询所有的员工的last_name,department_name信息 # 注意：所有  ---->且来自不同的表，外连接（有不匹配的数据） # 此时是错误的。 SELECT employee_id,department_name  FROM employees e,departments dWHERE e.`department_id` = d.department_id;    # 查出数据为106条数据 不正确 少了一个 为null的 数据

（三）、SQL92：使用(+)创建连接

  • SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
  • Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
  • 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。
SELECT employee_id,department_nameFROM employees e,departments dWHERE e.`department_id` = d.department_id(+);

（四）、SQL99语法实现多表查询

基本语法：使用JOIN…ON子句创建连接的语法

    SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

语法说明：

  • 可以使用 ON 子句指定额外的连接条件。
  • 这个连接条件是与其它条件分开的。
  • ON 子句使语句具有更高的易读性。
  • 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

分类：

  • 内连接：INNER JOIN ON
  • 外连接：OUTER JOIN
  • 左外连接：LEFT OUTER JOIN
  • 右外连接：RIGHT OUTER JOIN
  • 满外连接：FULL OUTER JOIN（MySQL 不支持，但是我们有办法实现）

1、SQL99 内连接语句 INNER JOIN ON / JOIN ON

# 两个表SELECT last_name,department_name FROM `employees` e,`departments` dWHERE e.`department_id` = d.`department_id`; # 三个表#SQLSELECT last_name,department_name,cityFROM `employees` e JOIN `departments` d ON e.`department_id` = d.`department_id`JOIN locations l ON d.`location_id` = l.`location_id`;

2、SQL99 左外连接语句 LEFT OUTER JOIN / LEFT JOIN

语法

#实现查询结果是ASELECT 字段列表FROM A表 LEFT JOIN B表ON 关联条件WHERE 等其他子句
SELECT last_name,department_nameFROM `employees` e LEFT OUTER JOIN `departments` d ON e.`department_id` = d.`department_id`; # 或者  可以省略 OUTERSELECT last_name,department_nameFROM `employees` e LEFT JOIN `departments` d ON e.`department_id` = d.`department_id`; SELECT last_name,department_nameFROM `employees` e LEFT OUTER JOIN `departments` d ON (e.`department_id` = d.`department_id`);


3、SQL99 右外连接语句 RIGHT OUTER JOIN

语法

#实现查询结果是BSELECT 字段列表FROM A表 RIGHT JOIN B表ON 关联条件WHERE 等其他子句;
SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id);

4、满外连接(FULL OUTER JOIN)

  • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。
#满外连接：mysql不支持FULL OUTER JOINSELECT last_name,department_nameFROM employees e FULL OUTER JOIN departments dON e.`department_id` = d.`department_id`;

（五）、合并查询 UNION的使用

合并查询结果：

    利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。

语法格式：

SELECT column,... FROM table1UNION [ALL]SELECT column,... FROM table2
  • UNION 操作符：操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL 操作符：操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：

  • 执行UNION ALL语句时所需要的资源比UNION语句少。

  • 如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

# 实例 ：查询部门编号>90或邮箱包含a的员工信息# 方式一SELECT *FROM employeesWHERE email LIKE '%a%' OR `department_id` >= 90; # 方式二SELECT * FROM employees WHERE email LIKE '%a%' UNIONSELECT * FROM employees WHERE`department_id` >= 90;

（六）、SQL99语法新特性

1、自然连接

    SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

SELECT employee_id,last_name,department_nameFROM employees e JOIN departments dON e.`department_id` = d.`department_id`AND e.`manager_id` = d.`manager_id`; # NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。SELECT employee_id,last_name,department_nameFROM employees e NATURAL JOIN departments d;

2、USING连接

    SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。

SELECT employee_id,last_name,department_nameFROM employees e JOIN departments dON e.department_id = d.department_id; SELECT employee_id,last_name,department_nameFROM employees e JOIN departments dUSING (department_id);