MySQL查询（四）——子查询&联合查询

MySQL查询（四）——子查询

1. 子查询

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

外部的查询语句称为主查询或外查询。

• 按子查询出现的位置分类

  • select后面，仅支持标量子查询
  • from后面，支持表子查询
  • where或having后面，支持标量子查询☆☆☆、列子查询☆☆☆、行子查询
  • exists后面（相关子查询），支持表子查询

• 按结果集的行列数不同分类

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

• 下面例子中使用的数据表

  • employees 员工表

  • departments 部门表

  • jobs 工种表

  • locations 地点表

    员工表分别通过部门id、工种id与部门表、工种表关联，部门表通过地点id与地点表关联。

1.1. 子查询在where或having后面

• 特点
  • 子查询放在小括号内
  • 子查询一般放在条件的右侧
  • 子查询一般优于主查询执行
  • 标量子查询一般搭配单行操作符使用，如> < >= <= = <>
  • 列子查询一般搭配多行操作符使用，如in any some all

1.1.1. 标量子查询（单行子查询）

• 示例

  #查询工种编号与员工141的相同，工资高于员工143工资的员工名字、工种编号、工资
  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
  );
  

  #查询最低工资高于50号部门最低工资的部门编号和对应的最低工资
  select department_id,min(salary)
  from employees
  group by department_id
  having min(salary)>(
  	select min(salary) from employees where department_id=50
  );
  

1.1.2. 列子查询（多行子查询）

• 常见的多行操作符

  操作符	含义
  in	等于列表中的任意一个就可以
  not in	不等于列表中的所有值
  any或者some	和子查询返回的某一个值比较满足条件即可，不常用
  all	和子查询返回的所有值比较都需要满足条件

• 示例

  #查询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)
  );
  

  加distinct去重可以优化执行效率。

1.1.3. 行子查询（多行多列），较少使用

• 示例

  #查询员工编号最小，并且工资最高的员工信息
  select *
  from employees
  where (employee_id,salary)=(
  	select min(employee_id),max(salary) from employees
  );
  

1.2. 子查询在select后面

• 示例

  #查询每个部门的员工个数
  select d.*,(
  	select count(*) from employees e where e.department_id=d.department_id
  ) 个数
  from departments d;
  

  一般可以用别的方法代替。

1.3. 子查询在from后面

将子查询的结果充当一张表，要求必须起别名

• 示例

  #查询每个部门的平均工资的工资等级
  select ag_s.*,grade_level
  from (
  	select avg(salary) 平均工资,department_id
      from employees e 
      group by department_id) ag_s
  join job_grades j
  on ag_s.平均工资 between j.lowest_sal and j.highest_sal;
  

1.4. 子查询在exists后面

• exists的基本语法（使用较少，可以用其它查询代替）

  exists(完整的查询语句)
  

  得到的结果是1或0，查询结果有值时为1，没有值时为0

• 示例

  #查询有员工的部门名
  select department_name
  from departments d
  where exists (
  	select * from employees e where d.department_id=e.department_id
  );
  

  或者可以用下面的语句代替：

  #查询有员工的部门名
  select department_name
  from departments
  where department_id in (
  	select department_id from employees
  );
  

2. 分页查询

• 应用场景：当要显示的数据一页显示不全，需要分页提交sql请求。

• 基本语法

  select 查询列表
  from 表
  [连接类型 join 表2
  on 连接条件
  where 筛选条件
  group by 分组字段
  having 分组后的筛选
  order by 排序的字段]
  limit offset,size;
  

  中括号中是可选的内容，offset是要显示条目的起始索引（起始索引从0开始），size是要显示的条目个数。若要查询从头开始的数据，起始索引可以省略。

• 特点

  • limit语句放在查询语句的最后

  • 上述执行顺序为：from 表$\to$join 表2$\to$on 连接条件$\to$where 筛选条件$\to$group by 分组字段$\to$having 分组后的筛选$\to$select 查询列表$\to$order by 排序的字段$\to$limit 语句

  • 若已知要显示的页数page，以及每页的条目数size，查询语句可以写为

    select 查询列表
    from 表
    limit (page-1)*size,size;
    

• 示例

  #查询有奖金并且工资较高的员工信息，并显示前5条
  select * from employees
  where commission_pct is not null
  order by salary desc
  limit 5;
  

3. 联合查询

• 语法

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

  将多个查询结果合并起来，适用于查询多个表的信息，表之间没有连接条件，但查询信息一致。

• 特点

  • 要求多条查询语句的查询列数是一致的
  • 要求多条查询语句查询的每一列的类型和顺序最好一致
  • union默认去重，如果使用union all可以包含重复项