子查询

子查询允许把一个查询嵌套在另一个查询当中。子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询。

子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、 limit 、 join 等；但是对应的外部查询必须是以下语句之一：select、insert、update或者delete。

子查询的位置：select 中、from 后、where 中group by和order by中无实用意义。

带比较运算符的子查询

任务描述

本关任务：查询大于所有平均年龄的员工姓名与年龄。

相关知识

为了完成本关任务，你需要掌握：
1.什么叫子查询，
2.比较运算符结合标量子查询的使用。

子查询

子查询是指出现在其他SQL语句内的SELECT子句。

例如：
SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);
子查询指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以分为四类：

  • 标量子查询：返回单一值的标量，最简单的形式；
  • 列子查询：返回的结果集是 N 行一列；
  • 行子查询：返回的结果集是一行 N 列；
  • 表子查询：返回的结果集是 N 行 N 列。

带比较运算符的子查询

运算符 说明
> 大于
>= 大于或等于
= 等于
!= 或 <> 不等于
< 小于
<= 小于或等于
  • 带有比较运算符的子查询是指父查询和子查询使用比较运算符连接的嵌套查询；
  • 使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符。

举个例子，现在有一张employee表数据：

id name dept_id
1 Mary 101
2 Allen 102
3 kevin 101
4 Tom 102
5 Nancy 101

我们查询与Tom在同一个部门的同事id和姓名以及部门id。
1.首先，可以查询Tom所在部门id

2.然后查询dept_id=102的同事id和姓名

将上面两条简单的的SQL语句合并成一个嵌套查询：

编程要求

在右侧编辑器补充代码，查询大于所有平均年龄的员工姓名与年龄。

我们为你提供了tb_emp表，数据如下：

id name age
1 Mary 23
2 Allen 21
3 kevin 25
4 Tom 33
5 Nancy 28

测试说明
平台会对你编写的代码进行测试：

预期输出：

1
2
3
name     age
Tom      33
Nancy    28

我的代码

1
2
3
4
5
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age from tb_emp where age > (select avg(age) from tb_emp);

########## End ##########

关键字子查询

任务描述

本关任务：根据要求使用关键字进行查询。

相关知识

由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = 、>、<、>=、<=、<>这些比较标量结果的操作符。在列子查询中可以使用 ALL、ANY、SOME 和 IN关键字操作符。

为了完成本关任务，你需要掌握：如何在子查询中使用关键字进行查询。

ALL关键字

ALL必须接在一个比较运算符的后面，表示与子查询返回的所有值比较都为 TRUE则返回TRUE。

table1表数据：

col1
2
10

table2表数据：

col2
5
12
20

举个例子：
SELECT col1 FROM table1 WHERE col1 > ALL (SELECT col2 FROM table2)
该查询语句不会返回任何结果，因为col1中没有比col2所有值都大的值。

ANY和SOME关键字

ANY与比较操作符联合使用，表示与子查询返回的任何值比较为TRUE ，则返回TRUE。
SOME是ANY的别名，一般用的比较少。

仍使用上面二张表的数据：

在子查询中，返回的是table2的所有col2列的结果（5,12,20），然后将table1中col1的值与之进行比较，只要大于col2的任何值则为TRUE，因此查询结果为10。

IN关键字

  • IN的意思就是指定的一个值是否在这个集合中，如果在就返回TRUE；否则就返回FALSE了，同IN(项1，项2，…)；
  • IN 是 = ANY 的别名，二者相同，但 NOT IN 的别名却不是 <> ANY 而是 <> SOME。

编程要求

我们为你提供了如下数据表：
tb_salary表数据：

id position salary
1 Java 8000
2 Java 8400
3 Java 9000
4 Python 6500
5 Python 10000

根据提供的数据，在右侧编辑器中补充代码：

  1. 查询薪资表中比Java最高工资高的所有员工职位名称和薪资；

  2. 查询薪资表中比Java最低工资高的所有员工职位名称和薪资；

  3. 查询薪资表中职位为Java的所有员工职位名称和薪资。

测试说明

平台会对你编写的代码进行测试：

预期输出：

1
2
3
4
5
6
7
8
9
10
position    salary
 Python     10000
position    salary
  Java       8400
  Java       9000
  Python     10000
position    salary
  Java       8000
  Java       8400
  Java       9000

我的代码

1
2
3
4
5
6
7
8
9
10
11
USE Company;
#请在此处添加实现代码
########## Begin ##########

#1.使用 ALL 关键字进行查询
select position,salary from tb_salary where salary > all (select salary from tb_salary where position = 'Java');
#2.使用 ANY 关键字进行查询
select position,salary from tb_salary where salary > any (select salary from tb_salary where position = 'Java');
#3.使用 IN 关键字进行查询
select position,salary from tb_salary where position in ('Java');
########## End ##########