子查询

  • 含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询
  • 嵌套在其他语句内部的select语句成为子查询或内查询
  • 外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
  • 外面如果为select语句，则此语句称为外查询或主查询
  • 分类：
    • 按子查询出现的位置：
      • select后面：仅仅支持标量子查询
      • from后面：支持表子查询
      • where或having后面：支持标量子查询，列子查询，行子查询（较少）
      • exists后面（相关子查询）：支持表子查询
    • 按功能、结果集的行列数不同：
      • 标量子查询（结果集只有一行一列）
      • 列子查询（结果集只有一列多行）
      • 行子查询（结果集有一行多列）
      • 表子查询（结果集一般为多行多列）

where或having后面

  • 标量子查询（单行子查询）
  • 列子查询（多行子查询）
  • 行子查询（多列多行）
  • 特点：
    • 子查询放在小括号内
    • 子查询一般放在条件的右侧，where，having
    • 标量子查询，一般搭配着单行操作符使用（> < >= <= = <>）
    • 列子查询，一般搭配着多行操作符使用（IN、ANY/SOME、ALL）
    • 子查询的执行优选与主查询执行，主查询的条件用到了子查询的结果

标量子查询

  • 案例1：谁的工资比Abel高？

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：返回公司工资最少的员工的last_name, job_id和salary

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

 

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

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


列子查询

  • （多行子查询）
  • 多行比较操作符：

IN/NOT IN：等于列表中的任意一个

ANY|SOME：和子查询返回的某一个值比较，用的较少

ALL：和子查询返回的所有值比较

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

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
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代替any

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' ;

 

  • 案例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' ;

  • 或者用min代替all

SELECT
employee_id,
last_name,
job_id,
salary
FROM
employees
WHERE salary <
(SELECT
MIN(salary)
FROM
employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG' ;


行子查询

  • 结果集一行多列或多行多列
  • 案例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 ;

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

SELECT
(SELECT
department_name
FROM
departments d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id = 102) 部门名 ;


from后面

  • 将子查询结果充当一张表，要求必须起别名
  • 案例1：查询每个部门的平均工资的工资等级

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 g.`lowest_sal`
AND g.`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`) ;

用in更简单

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

  • 习题集
    • 查询和zlotkey相同部门的员工姓名和工资

SELECT
last_name,
salary
FROM
employees
WHERE department_id =
(SELECT
department_id
FROM
employees e
WHERE e.`last_name` = 'Zlotkey') ;

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

SELECT
employee_id,
last_name,
salary
FROM
employees e
WHERE e.`salary` >
(SELECT
AVG(salary)
FROM
employees) ;

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

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

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

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

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

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

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

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

 

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

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


原文链接：https://blog.csdn.net/qq_21579045/article/details/98111827

posted @ 2021-03-24 21:19  小码农2  阅读(136)  评论(0)    收藏  举报