五、DQL语言学习

5.8 子查询

5.8.1 含义

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

5.8.2 分类

  • 按子查询出现的位置：
    1. select后面：仅仅支持标量子查询
    2. from后面：支持表子查询
    3. where或having后面：支持标量子查询、列子查询、行子查询
    4. exists后面（相关子查询）：表子查询
  • 按结果集的行列数不同：
    1. 标量子查询（结果集只有一行一列）
    2. 列子查询（结果集只有一列多行）
    3. 行子查询（结果集有一行多列）
    4. 表子查询（结果集一般为多行多列）

      5.8.3 示例

      1. select first_name from employees 
      2. where department_id in (
      3. select department_id from departments
      4. where location_id=1700
      5. )
      image.png

      5.8.4 where或having后面

  1. 标量子查询（单行子查询）
  2. 列子查询（多行子查询）
  3. 行子查询（多行多列）

特点：

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

    标量子查询

    要求：左后返回的结果为一行一列

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

  1. # 1. 查询Abel的工资
  2. # select salary from employees where last_name='Abel';
  3. # 2. 查询员工的信息，满足salary>1的结果
  4. select * from employees where salary>(select salary from employees where last_name='Abel');

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

  1. # 1.查询141号员工的job_id
  2. # select job_id from employees where employee_id=141
  4. # 2.查询143号员工的salary
  5. # select salary from employees where employee_id=143
  7. # 3.查询员工的姓名，job_id和工资，要求job_id= 1的结果 并且salary>2的结果
  9. select last_name,job_id,salary
  10. from employees
  11. where job_id=(
  12.             select job_id from employees where employee_id=141
  13. )
  14. and salary>(
  15.         select salary from employees where employee_id=143
  16. )

image.png
案例3：返回公司工资最少的员工的last_name，job_id和salary

  1. # 1.查询公司的最低工资
  2. # select min(salar) from employees
  4. # 2.查询last_name,job_id和salary，要求salary=1的结果
  5. select last_name,job_id,salary
  6. from employees
  7. where salary=(select min(salary) from employees)

image.png
案例4：查询最低工资>50号部门最低工资的部门id和其最低工资

  1. # 1.查询50号部门的最低工资
  2. -- select min(salary) from employees where department_id=50
  4. # 2.查询每个部门的最低工资
  5. -- select min(salary),department_id
  6. -- from employees
  7. -- group by department_id
  9. # 3.在2.结果基础上筛选，满足min(salary)>1的结果
  10. select min(salary),department_id
  11. from employees
  12. group by department_id
  13. having min(salary)>(
  14.                     select min(salary) 
  15.                     from employees 
  16.                     where department_id=50
  17. )

image.png

列子查询（多行子查询）

  • 返回多行
  • 使用多行比较操作符 | 操作符 | 含义 | | —- | —- | | IN / NOT IN | 等于列表中的任意一个 | | ANY | SOME | 和子查询返回的某一个值比较 | | ALL | 和子查询返回的所有值比较 |

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

  1. # 1. 查询location_id是1400或1700的部门编号
  2. -- select distinct department_id 
  3. -- from departments 
  4. -- where location_id in (1400,1700)
  5. -- 
  6. # 2. 查询员工姓名，要求部门编号是1的结果中的某一个
  7. select last_name
  8. from employees
  9. where department_id in(
  10.             select distinct department_id 
  11.             from departments 
  12.             where location_id in (1400,1700)
  13. )

image.png
案例2：返回其他工种中比job_id为’IT_PROG’工种任一工资低的员工号、姓名、job_id以及salary

  1. # 1.查询job_id为`IT_PROG`部门任一工资
  2. -- select distinct salary 
  3. -- from employees
  4. -- where job_id='IT_PROG'
  6. # 2.查询员工号、姓名、job_id以及salary,salary<(1的结果)的任意一个
  7. select last_name,employee_id,job_id,salary
  8. from employees
  9. where salary<any(
  10.             select distinct salary 
  11.             from employees
  12.             where job_id='IT_PROG'
  13. )
  14. and job_id<>'IT_PROG';

image.png
案例3：返回其他部门中比job_id为’IT_PROG’部门所有工资都低的员工大的员工号、姓名
、job_id以及salary

  1. # 1.查询job_id为`IT_PROG`部门任一工资
  2. -- select distinct salary 
  3. -- from employees
  4. -- where job_id='IT_PROG'
  6. # 2.查询员工号、姓名、job_id以及salary,salary<(1的结果)的所有
  7. select last_name,employee_id,job_id,salary
  8. from employees
  9. where salary<all(
  10.             select distinct salary 
  11.             from employees
  12.             where job_id='IT_PROG'
  13. )
  14. and job_id<>'IT_PROG';

image.png

行子查询（多列子查询）

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

按照之前的思路解法

  1. # 1.查询最小的员工编号
  2. -- select min(employee_id)
  3. -- from employees
  5. # 2.查询最高工资
  6. -- select max(salary)
  7. -- from employees
  9. # 3.查询员工信息
  10. select *
  11. from employees
  12. where employee_id=(
  13.             select min(employee_id)
  14.             from employees
  15. )
  16. and salary=(
  17.         select max(salary)
  18.         from employees
  19. )

现在换用列子查询：

  1. # 列子查询有其局限性，操作符只能为同一个，下面的就为 = 操作符
  2. select *
  3. from employees
  4. where (employee_id,salary)=(
  5.             select min(employee_id),max(salary)
  6.             from employees
  7. )

5.8.5 select后面

select后面仅支持标量子查询

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

  1. select d.*, (select count(*) 
  2.                          from employees e 
  3.                          where e.department_id=d.department_id
  4.                         ) as 个数
  5. from departments d;

image.png

5.8.6 from后面

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

  1. # 1.查询每个部门的平均工资
  2. -- select avg(salary),department_id
  3. -- from employees
  4. -- group by department_id;
  6. # 2.连接1的结果和job_grades表，筛选平均工资
  7. select ag_dep.*,g.grade_level
  8. from (
  9.         select avg(salary) ag,department_id
  10.         from employees
  11.         group by department_id
  12. ) as ag_dep
  13. inner join job_grades g
  14. on ag_dep.ag between lowest_sal and highest_sal;

image.png

5.8.7 exists后面（相关子查询）

exis用来查询结果是否存在：

  1. /*语法：
  2. exists(完整的查询语句)
  4. 结果：
  5. 1或0
  6. */
  7. SELECT EXISTS(SELECT employee_id FROM employees) AS 是否存在;

image.png
案例1：查询有员工的部门名

  1. # 方法1：使用exists来实现
  2. SELECT department_name
  3. FROM departments d
  4. WHERE EXISTS(
  5.     SELECT *
  6.     FROM employees e
  7.     WHERE d.`department_id`=e.`department_id`
  8. );
  10. # 方法2：使用in关键字来实现
  11. SELECT department_name
  12. FROM departments d
  13. WHERE d.`department_id` IN(
  14.     SELECT department_id
  15.     FROM employees
  16. );

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

  1. # 方法1：使用in关键字
  2. USE `girls`;
  3. SELECT bo.*
  4. FROM boys bo
  5. WHERE bo.id NOT IN(
  6.     SELECT boyfriend_id
  7.     FROM beauty
  8. );
  10. # 方法2：使用exists关键字
  11. SELECT bo.*
  12. FROM boys bo
  13. WHERE NOT EXISTS(
  14.     SELECT boyfriend_id
  15.     FROM beauty b
  16.     WHERE bo.`id`=b.`boyfriend_id`
  17. );

5.8.8 子查询测试

  1. 查询和Zlotkey 相同部门的员工姓名和工资
  2. 查询工资比公司平均工资高的员工的员工号，姓名和工资
  3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
  4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
  5. 查询在部门的location_id 为1700 的部门工作的员工的员工号
  6. 查询管理者是King 的员工姓名和工资

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

    5.8.9 子查询经典案例题目

  8. 查询工资最低的员工信息: last_name, salary

  9. 查询平均工资最低的部门信息
  10. 查询平均工资最低的部门信息和该部门的平均工资
  11. 查询平均工资最高的 job 信息
  12. 查询平均工资高于公司平均工资的部门有哪些?
  13. 查询出公司中所有 manager 的详细信息.
  14. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
  15. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

    5.9 分页查询

    应用场景：我们的数据一次加载不完，所以要分成几页来显示，类似如下
    image.png ```sql /*语法： select 查询列表 from 表 [join type join 表2 on 连接条件 where 筛选条件 group by 分组字段] limit offset,size;

offset 要显示条目的起始索引（起始索引从0开始） size 要显示的条目个数 */

  1. **案例1**：查询前5条员工的信息
  2. ```sql
  3. SELECT * FROM employees LIMIT 0,5;
  5. # 或者
  6. SELECT * FROM employees LIMIT 5;

image.png
案例2：查询第11条-第25条的信息

  1. SELECT * FROM employees LIMIT 10,15;

image.png
案例3：有奖金的员工信息，并且工资较高的前10名显示出来

  1. SELECT *
  2. FROM employees
  3. WHERE commission_pct IS NOT NULL
  4. ORDER BY salary DESC
  5. LIMIT 10;

image.png

5.10 Union联合查询

联合查询：将多条查询语句联合起来

应用场景：要用到的结果来自于多个表，且多个表之间没有连接关系，但是所要查询的信息是一样的。

  1. /*语法：
  2. 查询语句1
  3. union
  4. 查询语句2
  5. union
  6. ...
  7. */

案例：查询部门编号>90或邮箱包含a的员工信息

  1. # 方法1：在where后改变条件
  2. SELECT * 
  3. FROM employees 
  4. WHERE email LIKE '%a%'
  5. OR department_id>90; 
  7. # 方法2：使用连接查询
  8. SELECT * FROM employees WHERE email LIKE '%a%'
  9. UNION
  10. SELECT * FROM employees WHERE department_id>90;

image.png
注意：

  1. UNION关键字会将结果进行去重，所以有时候为了避免去重，需要使用UNION ALL关键字
  2. 要求多条查询语句的查询列数是一致的
  3. 要求多条查询语句的查询的每一列的类型和顺序最好一致