【mysql子查询&组合查询 05】

#进阶7：子查询
含义：出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句（内部嵌套其他select语句的查询），称为主查询或外查询
分类：
按子查询出现的位置：

select 后面（仅仅支持标量子查询）
from 后面（支持表子查询）
where或having 后面
      支持标量子查询
      列子查询
      行子查询
exists后面(相关子查询)
      表子查询

按结果集的行列数不同：

标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

#一、where或having后面
标量子查询 (单行子查询)
列子查询（多行子查询）
行子查询 （多列多行）
特点：
1）子查询放在小括号内
2）子查询一般放在条件的右侧
3）标量子查询，一般搭配着单行操作符使用
> < >= <= <>
4）列子查询，一般搭配着多行操作符使用
in、any/some、all
5）子查询的执行优先于主查询的执行，原因在于主查询的条件用到了子查询的结果
#1、标量子查询
# 案例1：谁的工资比Abel高？
-- 1) 查询Abel的工资

SELECT salary FROM employees WHERE last_name = 'Abel';

-- 2) 查询员工的信息，满足salary>1)的结果

SELECT
  *
FROM
  employees
WHERE salary >
  (SELECT
    salary
  FROM
    employees
  WHERE last_name = 'Abel') ;

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

-- 1）141号员工的job_id
SELECT job_id FROM employees WHERE employee_id=141;
-- 2）143号员工的salary
SELECT salary FROM employees WHERE employee_id =143;
-- 3) 查询员工的姓名，工资和job_id，要求job_id=1) 并且salary=2)
SELECT
  job_id,
  last_name,
  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 last_name,job_id,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees);

#子查询中having子句（首先执行子查询，向主查询中的having子句返回结果）
#案例4：查询最低工资大于50号部门最低工资的部门ID和其最低工资

-- 1) 查询50号部门的最低工资
SELECT MIN(salary) FROM employees WHERE department_id=50;
-- 2) 查询每个部门的最低公司，然后查询来的部门最低工资>1）的结果
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary) FROM employees WHERE department_id=50);

#非法使用标量子查询
#2、列子查询（多行子查询）
使用多行比较操作符
操作符        含义
in/not in     等于列表中的任意一个
any/some    和子查询返回的某一个值比较
all        和子查询返回的所有值比较 
#案例1：返回location_id是1400或1700的部门中的所有员工信息

SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400,1700);
SELECT
  *
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 DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ;
 
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' );

#3、行子查询（结果集一行多列或多行多列）
 #案例：查询员工编号最小并且工资最好的员工信息

SELECT MIN(employee_id) FROM employees
 SELECT MAX(salary) FROM employees
 -- 常规写法
 SELECT * FROM employees WHERE employee_id=(SELECT MIN(employee_id) FROM employees) AND salary=(SELECT MAX(salary) FROM employees);
 -- 行子查询写法
 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;

#案例2：查询员工号=102的部门名

 -- 子查询方式
 SELECT department_name,department_id FROM departments WHERE department_id=(SELECT department_id FROM employees WHERE employee_id='102');
 -- 链接查询方式
 SELECT department_name ,d.`department_id`FROM employees e JOIN departments d ON e.`department_id`=d.`department_id` WHERE e.`employee_id`='102';

# 三、子查询案例
#案例1、查询和Zlotkey相同部门的员工姓名和工资

SELECT
  last_name,
  salary ,department_id
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  AVG(salary),department_id FROM employees GROUP BY department_id;  -- 部门的平均工资
 SELECT salary,department_id FROM employees;  -- 各个部门的工资
 
SELECT
  employee_id,
  last_name,
  salary,
  department_id
FROM
  employees
WHERE salary >
  (SELECT
    AVG(salary)
  FROM
    employees)
GROUP BY department_id;  -- 合在一起比较，已department_id进行分组

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

SELECT
    employee_id,
    last_name
  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 department_id FROM departments WHERE location_id='1700');

#进阶8 分页查询
应用场景：当要显示的数据，一页显示不全们需要分页提交sql请求
语法：

select 查询列表
from 表
    【join type join 表2
on 链接条件
where 筛选条件
groupby 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset,size;

特点：

    公式：要显示的页数page,每页的条目数size
    limit (page-1)*size,size;
    
    offset 要显示条目的起始索引（起始索引从0开始）
    size 要显示的条目个数
#案例1：查询前5条员工信息

SELECT * FROM employees LIMIT 5

#案例2：查询第11条到第25条

SELECT * FROM employees LIMIT 10,15;

案例：

#查询工资最低的员工信息：last_name,salary

SELECT last_name,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

#查询平均工资最低的部门信息

-- 先找出平均工资的所有部门，然后按平均工资升序，取1
SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ORDER BY ag LIMIT 1 ;

SELECT *
FROM departments
WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1);

#查询平均工资最高的job信息

SELECT AVG(salary) ,job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1;

SELECT * FROM jobs WHERE job_id = (SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1);

#查询平均工资高于公司平均工资的部门有哪些

-- 公司的平均工资
SELECT AVG(salary) FROM employees;
--  部门的平均工资
SELECT AVG(salary) 部门工资 FROM employees GROUP BY department_id;
-- 部门平均工资高于公司平均工资的部门
SELECT
  department_id,AVG(salary)
FROM
  employees
GROUP BY department_id
HAVING(AVG(salary)) >
  (SELECT
    AVG(salary)
  FROM
    employees) ;

# 查询出公司中所有manager的详细信息

SELECT * FROM employees WHERE manager_id =ANY(SELECT DISTINCT manager_id FROM employees);

#各个部门中，最高工资中最低的那个部门的最低工资是多少

SELECT MAX(salary),department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1;

SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1)

#查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary

SELECT AVG(salary) ,department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC  LIMIT 1;
 
SELECT
  last_name,
  d.department_id,
  email,
  salary
FROM
  employees e
  JOIN departments d
    ON e.`employee_id` = d.`manager_id`
WHERE d.department_id =
  (SELECT
    department_id
  FROM
    employees
  GROUP BY department_id
  ORDER BY AVG(salary) DESC
  LIMIT 1) ;

 #进阶9：联合查询
union 联合 合并 将多条查询语句的结果合并成一个结果
语法：

查询语句1
union
查询语句2
union
...

应用场景：要查询的结果来自于多个表，且多个表没有直接的链接关系但查询的信息一致时就可以用联合查询
特点：
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重， 如果使用union all 可以包含重复项

#案例1：查询部门编号大于90或邮箱中包含a的员工信息

SELECT * FROM employees WHERE department_id > '90'
UNION
SELECT * FROM employees WHERE email LIKE '%a%';