SQL第3篇 子查询很重要

进阶7 子查询

含义：
子查询（内查询）：出现在其他语句中的select语句
主查询（外查询）：外部的查询语句
分类：
一、按子查询出现的位置：
1.select后面：仅仅支持标量子查询
2.from后面：表子查询
3.where或者having后面：❤
（1）标量子查询 √
（2）列子查询 √
（3）行子查询（较少）
4.exsits后面（相关查询）：表子查询

二、按结果集的行列数：
标量子查询（一行一列）单行子查询
列子查询（一列多行）多行子查询
行子查询（一行多列）多行多列
表子查询（多行多列）

一、where 或者 having 后面

1.单行子查询
2.多行子查询
3.多列多行
特点：
1.子查询放在小括号后面
2.子查询一般放在条件的右侧
3.标量子查询搭配着单行操作符使用（>,<,>=,<=,<>,=)
列子查询搭配着多行操作符使用（in，any/some,all)

1.标量子查询

案例1：谁的工资比Abel高

 

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

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

 

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：返回公司工资最少的员工的姓名，job_id，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

②每个部门的最低工资

 

SELECT MIN(salary) FROM employees GROUP BY department_id 

③ ②的最低>①最低 的部门id 和最低工资

 

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

2.列子查询

in|not in:等于列表中的任何一个 in==any not in== <>all
any|some：跟某一个值比较
all：跟所有值比较

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

法一：外连接

 

SELECT last_name ,location_id, d.department_id  
FROM employees e
LEFT JOIN departments d ON e.`department_id`=d.`department_id`
WHERE location_id IN (1400,1700);

法二：子查询
①location_id是1400或者1700的部门

 

SELECT department_id FROM departments WHERE location_id IN (1400,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、 salary
法一：any

 

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'

法二：max

 

SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM employees 
WHERE  salary < (SELECT DISTINCT MAX(salary) FROM employees WHERE job_id = "IT_PROG")
AND job_id <> 'IT_PROG'

3.行子查询（一行多列）要求查询的筛选条件一样

案例1：员工编号最小，工资最高的员工信息

法一：普通的and连接条件

 

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：查询每个部门的员工个数 (有部门但却没有员工departments)

 

SELECT d.*, (
    SELECT COUNT(*) FROM `employees` e
    WHERE e.`department_id`= d.department_id
    ) 员工个数
FROM departments d GROUP BY `department_id` 

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

 

SELECT department_name
FROM `departments` d
WHERE d.department_id = (
    SELECT `department_id` FROM `employees` e
    WHERE `employee_id`=102 
    ) 

三、from 后面 (子查询的结果表必须起别名)

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

 

SELECT avg_d.* ,`grade_level`
FROM (
    SELECT AVG(salary)  ag ,`department_id` FROM employees
    GROUP BY `department_id` 
    ) AS avg_d 
JOIN `job_grades` j
ON avg_d.ag BETWEEN `lowest_sal` AND `highest_sal`

四、exists（相关查询 存在与否返回bull值。）

基本用法

 

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary =30000);   

查询有员工的部门名 （先去查询外查询，再通过exists筛选）

 

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

能用exists的也可以用in实现

 

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

查询没有女朋友的男神的信息

in

 

SELECT bo.* FROM boys bo
WHERE bo.id NOT IN (
    SELECT `boyfriend_id` FROM beauty
)

exists (总有一个连接条件，而且直接写exists 它之前不用写变量啥的）

 

SELECT bo.* FROM boys bo
WHERE NOT EXISTS (
    SELECT * FROM beauty b
    WHERE b.`boyfriend_id` = bo.id
)

----做题练习----

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

 

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

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

 

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

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

①本部门平均工资

 

SELECT AVG(salary) FROM employees GROUP BY department_id

②连接虚拟表和employees表， 条件工资>①

 

SELECT employee_id ,last_name,salary
FROM  employees e
JOIN (SELECT AVG(salary) ag ,department_id  FROM employees  GROUP BY department_id) avg_s 
ON e.department_id = avg_s.department_id 
WHERE e.salary > avg_s.ag

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

姓名中包含字母u的员工 的部门

 

SELECT department_id FROM employees WHERE last_name LIKE '%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的部门工作的员工工号

location_id 为1700的部门

 

SELECT DISTINCT department_id FROM `departments` WHERE `location_id`=1700

 

SELECT `employee_id` FROM `employees` 
WHERE `department_id` IN (
    SELECT DISTINCT department_id FROM `departments` WHERE `location_id`=1700
)

6、查询管理者是K_ing的员工姓名和工资

 

SELECT last_name ,salary
FROM employees
WHERE `manager_id` IN (
#K_ing 的员工号
    SELECT `employee_id` FROM employees
    WHERE last_name = 'K_ing'
)

King 的员工号

 

SELECT `employee_id` FROM employees
WHERE last_name = 'K_ing'

进阶八 分页查询 limit

场景：一页显示不全，分页提交sql语句
语法：
select 查询列表
from 表
【join type】 表
where 筛选条件
group by 分组条件
having 分组后的筛选条件
order by 排序
limit offset,size

 

offset:要查询的起始索引，从0开始
如果查询共第一条开始，offset可以省略
size：要显示的条目 

执行顺序：
from 表 join 表 on 字段
where 筛选 group by having 筛选
select 字段 order by 排序 limit 分页

公式 page第几页，size 一页显示多少条 limit (page-1)*size,size

 

size = 10

page   起始索引
1   0
2   10
3   20

案例1、第11条到第25条的员工信息

 

SELECT * FROM employees LIMIT 10,15;

案例2、有奖金的员工信息，返回工资较高的前10名

 

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

案例3、平均工资最低的部门信息

平均工资最低的部门ID

 

SELECT department_id 
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1

查询部门信息

 

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

案例4：查询平均工资最低的部门信息以及该部门的平均工资

①平均工资最低的部门id

 

select avg(salary) ag,department_id
from employees 
group by department_id
order by avg(salary)
limit 1

②部门信息

 

select d.*,ag
from departments d
join (
    SELECT AVG(salary) ag,department_id
    FROM employees 
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1
    ) ag_dep  on ag_dep.department_id = d.department_id

案例5：查询平均工资最高的job信息

 

select j.*
from jobs j
where job_id = (
    select  job_id from employees 
    group by job_id
    order by AVG(salary) desc limit 1
)

案例6：查询平均工资高于公司平均工资的部门

 

select d.*
from departments d
where department_id in  (
    select department_id from employees 
    group by department_id
    having avg(salary) >(select avg(salary) from employees )
)

案例7：查询公司中manager的详细信息

 

select *
from employees e
where `employee_id` in (
    select distinct manager_id from employees
)

案例8：各部门中，最高工资中最低的那个部门的 最低工资

各部门中，最高工资中最低的那个部门

 

select department_id 
from employees 
group by department_id
order by max(salary)
limit 1 

该部门的最低工资

 

select min(salary),department_id
from employees
where department_id = (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id
    ORDER BY MAX(salary)
    LIMIT 1 

)

案例9：查询平均工资最高的部门manager的详细信息

平均工资最高的部门

 

SELECT department_id FROM employees
GROUP BY department_id 
ORDER BY AVG(salary) DESC LIMIT 1

部门的领导编号

 

select `manager_id` from`departments`
where `department_id` =(
    SELECT department_id FROM employees
    GROUP BY department_id 
    ORDER BY AVG(salary) DESC LIMIT 1

)

领导的详细信息

 

select *
from employees
where employee_id = (

    SELECT `manager_id` FROM`departments`
    WHERE `department_id` =(
        SELECT department_id FROM employees   
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC LIMIT 1

        )   
)

 

 

贴一种两个查询方式不同的查询结果