MySQL学习总结（五）——DQL(数据查询语言总结【三】）

所有数据库学习总结文章均是根据B站尚硅谷视频总结而来，视频链接如下：
https://www.bilibili.com/video/BV12b411K7Zu.

一、子查询

1、含义：

出现在其他语句中的select语句，称为子查询或内查询。

2、分类：

①按结果集的行列数不同：

标量子查询：又称为单行子查询，结果集只有一行一列；
列子查询：多行子查询，结果集只有一行多列；
行子查询：结果集只有一行多列；
表子查询：结果集一般为多行多列；

②按子查询出现的位置：

select 后边----仅支持标量子查询；
from后边----支持表子查询；
where或having后边----标量子查询，列子查询、行子查询；
exists后边----相关子查询（表子查询）。

3、特点

①子查询放在括号内；
②子查询一般放在条件的右侧（即单行或多行操作符右边）；
③标量子查询一般搭配单行操作符（<,>,>=,<= ,<>）使用，列子查询一般搭配多行操作符使用；
④子查询优先于主查询。

4、where或having后边的子查询

①标量子查询

案例1：查询比Abel工资高的员工信息？

#先查询Abel的工资
SELECT  salary 
FROM   employees 
WHERE last_name = 'Abel';
#再查询salary>①结果的员工信息
SELECT   * 
FROM  employees 
WHERE salary >   (
  SELECT     salary 
  FROM    employees 
  WHERE last_name = 'Abel') ;#结果有10行

案例2：返回job_id与141号员工相同，salary比143号员工多的员工的姓名，job_id和工资。

#先查询141号员工的job_id.
SELECT job_id
FROM employees
WHERE employee_id=141;
#再差询143号员工的salary。
SELECT salary
FROM employees
WHERE employee_id=143;
#最后查询job_id=①，salary>②的员工的姓名，job_id和工资。
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id=141
)AND
salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);

案例3：返回公司工资最少的员工的last_name,job_id,salary。

查询公司的最低工资。
SELECT MIN(salary)
 FROM employees ;
查询salary=①的员工信息。
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资。

查询50号部门的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id=50;
查询最低工资大于①的部门id和其最低工资。
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
#需要查询每个部门的最低工资，所以采用分组后子查询
HAVING MIN(salary)>( 
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
);

②列子查询（多行子查询）

多行子查询操作符：
IN（或NOT IN）------等于（或不等于）列表中的任意一个。
ANY,SOME-------和子查询返回的某一个值比较（前边必须有一个比较操作符）,只要有一条数据满足条件即可，等价于大于min。
ALL------和子查询返回的所有值比较（前边必须有一个比较操作符）,对所有数据都满足条件，等价于小于max。

案例1：返回location_id是1400或1700的部门中的所有员工姓名。

#查询location_id是1400或1700的部门编号。
SELECT DISTINCT department_id
FROM `departments`
WHERE location_id IN(1400,1700)；# in等价于location_id = 1400 OR location_id = 1700;
#查询员工姓名，要求部门号是①列表中的某一个。
SELECT last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM `departments`
WHERE location_id IN(1400,1700)
);

案例2：返回其它工种,比job_id为‘IT_PROG’部门,任一工资低的员工的员工号、姓名、job_id以及salary。

#查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id ='IT_PROG';
#查询比job_id为‘IT_PROG’部门任一工资最低的员工的员工号、姓名、job_id以及salary。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY (
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id <> 'IT_PROG';

案例3：返回其它工种中,比job_id为‘IT_PROG’部门，所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < all (
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id <> 'IT_PROG';
或
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
	SELECT MAX(salary)
	FROM employees
	WHERE job_id ='IT_PROG'
)AND job_id <> 'IT_PROG';

③行子查询

案例1:查询员工编号最小并且工资最高的员工信息。

SELECT *
FROM employees
WHERE (employees,salary)=(#注意此格式只适合条件为等于的查询
	SELECT MIN(employee_id)，
	MAX(salary)
	FROM employees
)
或
SELECT   * 
FROM  employees 
WHERE employee_id =(
      SELECT MIN(employee_id) 
      FROM  employees) 
   AND salary = (
      SELECT MAX(salary) 
      FROM employees 
);

5、放在select后面

仅仅支持标量子查询。

案例1：查询每个部门的员工个数。

select count(*),
(
select department_id
from employees
group by department_id
)
from employees;

6、放在from后面

注意：将子查询的结果集作为一个表，放在from后面的表必须起别名，不然没办法查询。
案例1：查询每个部门的平均工资的工资等级。

#查询每个部门的平均工资。
SELECT  AVG(salary) ag, department_id 
FROM    employees e 
GROUP BY department_id;
#连接①的结果集和job_grades表，筛选条件为平均工资在lowest_sal AND highest_sal之间
SELECT av.*,g.grade_level
FROM
  (SELECT  AVG(salary) ag, department_id 
  FROM    employees e 
  GROUP BY department_id
) av
INNER JOIN job_grades g
ON av.ag BETWEEN lowest_sal AND highest_sal;

7、放在exists后面（相关子查询）

这个关键字一般都可以用其他关键字代替达到查询效果。

语法：
exists(完整的查询语句)
结果：1或0
例如：SELECT EXISTS(SELECT employee_id FROM employees); ---------------------结果为1。
相关子查询：我的查询结果涉及到了要查询的字段

案例1：查询有员工的部门名。

方式一：使用IN
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT e.`department_id`
	FROM employees e
);
方式二：使用EXISTS。
SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT * 
    FROM employees e
    WHERE d.`department_id`=e.`department_id`
    );

案例2：查询没有女朋友的男神信息。

#方式一：使用in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
	SELECT boyfriend_id
	FROM beauty 
	);
#方式二：使用exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE b.`boyfriend_id`=bo.`id`
	);

二、分页查询

1、应用场景

当要显示的数据，一页显示不全，需要分页提交sql请求时可以使用分页查询。

2、语法：

SELECT 查询列表
FROM 表1
【join type】  表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选
ORDER BY 排序字段
LIMIT offset,size
-----offset要显示条目的起始索引（起始索引从0开始，要是所需索引从第一条数据开始，0可省略）
-----size要显示的条目个数

3、特点

①limit语句放在查询语句最后，执行步骤也在最后。
②公式：
例如：固定size=10,

要显示的页数	起始索引
1	0
2	10
3	20

由上表可发现，要显示的页数是page，每页显示的条目数size，则公式为： limit （page-1）*size，size；

4、案例编写

案例1：查询前五条员工信息。

SELECT *
FROM employees
LIMIT 0,5;#(当起始索引为0是可以省略)

#案例2：查询11条到25条员工信息。

SELECT *
FROM employees
LIMIT 10,15;

#案例3：查询有奖金的员工信息，并且工资较高的前10名显示出来。

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

三、联合查询（union）

1、联合查询的定义

联合查询即将多个查询语句的结果合并成一个结果。

2、语法

查询语句1
union
查询语句2
union
…；

3、应用场景

要查询的结果来自多个表，且多个表没有连接的关系，且查询的信息都一样时（即查询不同表的同一字段）。

案例1：查询部门编号大于90或邮箱中包含a的员工信息。
SELECT * FROM employees	WHERE email LIKE '%a%'
UNION
SELECT * FROM employees	WHERE department_id>90;

4、特点

①要求多条查询语句的查询列数是一致的；
②要求多条查询语句的查询的每一列的类型和顺序是一致的；
③使用union关键字默认去重，使用union all可以取消去重。

四、查询关键字总结

查询所涉及到的关键字机器执行顺序如下：

关键语句	执行顺序
SELECT 查询字段	6
FROM 表1	1
连接类型 JOIN 表2 （或多表） ON 连接条件	2
WHERE 分组前筛选条件	3
GROUP BY 分子字段1, 分组字段2,······	4
HAVING 分组后筛选	5
ORDER BY 排序字段	7
LIMIT 起始索引，显示条数	8