Mysql数据库基础系列

软件下载地址
提取码：7v7u
数据下载地址
提取码：e6p9
mysql数据库基础第一章：（一）数据库基本概念
mysql数据库基础第一章：（二）mysql环境搭建
mysql数据库基础第二章：（一）基础查询
mysql数据库基础第二章：（二）条件查询
mysql数据库基础第二章：（三）排序查询
mysql数据库基础第二章：（四）常见函数
mysql数据库基础第二章：（五）分组查询
mysql数据库基础第二章：（六）连接查询
mysql数据库基础第二章：（七）子查询
mysql数据库基础第二章：（八）子查询经典案例
mysql数据库基础第二章：（九）分页查询
mysql数据库基础第二章：（十）连接查询
mysql数据库基础第三章：DML语言
mysql数据库基础第四章：DDL（数据定义语言）：库表的管理、数据类型与约束条件
mysql数据库基础第五章：（一）事务
mysql数据库基础第五章：（二）视图
mysql数据库基础第六章：变量、存储过程与函数
mysql数据库基础第七章：流程控制结构
mysql数据库基础第八章：窗口函数和公用表达式（CTE）

@TOC

一、子查询基本概念

1.1含义：

出现在其他语句中的select语句，称为子查询或内查询。外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：

select后面：
        仅仅支持标量子查询
from后面：
        支持表子查询
where或having后面：用的较多
        标量子查询（单行） 
        列子查询  （多行） 
        行子查询
        
exists后面（相关子查询）
        表子查询
按结果集的行列数不同：
    标量子查询（结果集只有一行一列）
    列子查询（结果集只有一列多行）
    行子查询（结果集有一行多列）
    表子查询（结果集一般为多行多列）

1.2特点与注意事项：

1.子查询放在括号内
2.子查询放在条件语句的后面
3.标量子查询配合操作符使用 < > = <= >= <> 
4.如果是条件子查询，子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

二、 where或having后面

2.1 标量子查询

标量子查询的子查询结果是一个值，单行单列
因此，筛选条件时，要注意对应关系

1查询工资比Abel高的员工

SELECT *
FROM employees
WHERE salary>(

    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'

);

2返回job_id与141号员工相同，salary比143号员工多的员工 姓名，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查询最低工资大于50号部门最低工资的部门id和其最低工资

先查询50号部门的最低工资，再进行筛选

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
    SELECT  MIN(salary)
    FROM employees
    WHERE department_id = 50


);

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

列子查询的结果是多行一列，在进行筛选时要注意对应关系

返回location_id是1400或1700的部门中的所有员工姓名

先查询location_id是1400或1700所有部门，

SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

上述返回的结果是一列department_id值

*图5 列子查询结果图（多行一列）*

接下来查询员工信息

SELECT last_name
FROM employees
WHERE department_id  IN (
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)

);

2.返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,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 last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
    SELECT MIN( salary)
    FROM employees
    WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

2.3 行子查询（一行多列，或者多行多列）

1.查询员工编号最小且工资最高的员工信息

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
    SELECT MIN(employee_id),MAX(salary)
    FROM employees
);

三、select后面接子查询

效果类似连接查询，但仅仅支持标量子查询

1.查询每个部门的员工个数

SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

四、from后面的子查询

将子查询的结果当做 一章表，注意要起别名

案例：查询每个部门的平均工资的工资等级

SELECT  ag_dep.*,g.`grade_level`
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

五、exists后面（相关子查询）

exists(完整的查询语句)返回结果：1或0

1.查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS(
    SELECT *
    FROM employees e
    WHERE d.`department_id`=e.`department_id`

);

有员工的部门名相当于先在employees找到部门编号,再对应到departments表中。因此也可以用where子查询

SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
    SELECT department_id
    FROM employees

)

六、练习

1.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'

)

2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT last_name,employee_id,salary
FROM employees
WHERE salary>(

    SELECT AVG(salary)
    FROM employees
);

3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id

) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;

4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT last_name,employee_id
FROM employees
WHERE department_id IN(
    SELECT  DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);

5. 查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id =ANY(
    SELECT DISTINCT department_id
    FROM departments 
    WHERE location_id  = 1700
);

6.查询管理者是King的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE manager_id IN(
    SELECT employee_id
    FROM employees
    WHERE last_name  = 'K_ing'

);

7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名

SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
    SELECT MAX(salary)
    FROM employees
);