3.8.2 集合的比较

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 4.2k 阅读时长 ≈ 4 分钟

3.8.2 集合的比较

SQL查询 找出满足下面条件的所有教师的姓名,他们的工资至少比Biology系某一个教师的工资要高

作为一个说明嵌套子查询能够对集合进行比较的例子,考虑查询”找出满足下面条件的所有教师的姓名,他们的工资至少比Biology系某一个教师的工资要高“。

写法1

在3.4.1节,我们将此查询写作:

1
2
3
select distinct T.name
from instructor as T,instructor as s
where T.salary >S.salary and S.dept_name = 'Biology';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select distinct T.name
from instructor as T,instructor as s
where T.salary >S.salary and S.dept_name = 'Biology';
+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Brandt   |
| Kim      |
+----------+
7 rows in set

写法2

子查询 产生Biology系所有教师的所有工资值的集合

1
2
3
4
5
(
select salary
    from instructor
    where dept_name ='Biology'
)

产生Biology系所有教师的所有工资值的集合。

至少大于其中一个>some

短语”至少比某一个要大“在SQL中用>some表示。>some短语允许我们用一种更贴近此查询的文字表达的形式重写上面的查询:

1
2
3
4
5
6
7
select name
from instructor
where salary > some(
    select salary
        from instructor
        where dept_name='Biology'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select name
from instructor
where salary > some(
    select salary
        from instructor
        where dept_name='Biology'
);
+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Brandt   |
| Kim      |
+----------+
7 rows in set

当元组的salary值至少比Biology系教师的所有工资值集合中某一成员高时,外层select的where子句中>some的比较为真。

some详解

其他运算符和some组合的情况

  • 至少小于其中一个(<some),
  • 至少小于等于其中一个(<=some),
  • 至少大于等于其中一个(>=some),
  • 至少等于其中一个(=some)
  • 至少不等于其中一个(<>some),SQL中<>符号是不等于的意思

=some等价于in

因为=some的意思是至少等于其中一个,而in关键字,也是至少等于其中一个的意思,所以=some等价于in。

<>some不等价与not in

  • <>some的意思是至少不等于其中任意一个,
  • not in的意思是都不等于其中任意一个.

可见not in要求比<>some要严格,所以<>some不等价于not in。

SQL查询 找出工资值比Biology系每个教师的工资都高的所有教师的姓名

结构>all对应于词组”比所有的都大“。使用该结构,我们写出査询如下:

1
2
3
4
5
6
7
select name
from instructor
where salary >all(
    select salary
        from instructor
            where dept_name ='Biology'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select name
from instructor
where salary >all(
    select salary
        from instructor
            where dept_name ='Biology'
);
+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Brandt   |
| Kim      |
+----------+
7 rows in set

all详解

  • 大于其中所有的<all,
  • 小于等于其中所有的<=all,
  • 大于等于其中所有的>=all,
  • 等于其中所有的=all,
  • 不等于其中所有的<>al,

<>all等价于not in

<>all的意思是不等于其中所有的,和not in的意思是一样的,两者等价。

=all不等价与in

  • =all的意思是等于其中所有的,
  • in的意思是等于其中一个

=all比in要求要严格,=all不等价于in

SQL查询 找出平均工资最高的系

作为集合比较的另一个例子,考虑查询”找出平均工资最高的系“。我们首先写一个查询来找出每个系的平均工资,然后把它作为子查询嵌套在一个更大的查询中,以找出那些平均工资大于等于所有系平均工资的系。

1
2
3
4
5
6
7
8
select dept_name
from instructor
group by dept_name
having avg(salary) >= all(
    select avg(salary)
        from instructor
        group by dept_name
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select dept_name,avg(salary)
from instructor
group by dept_name;
+------------+--------------+
| dept_name  | avg(salary)  |
+------------+--------------+
| Biology    | 72000        |
| Comp. Sci. | 77333.333333 |
| Elec. Eng. | 80000        |
| Finance    | 85000        |
| History    | 61000        |
| Music      | 40000        |
| Physics    | 91000        |
+------------+--------------+
7 rows in set

mysql> select dept_name
from instructor
group by dept_name
having avg(salary) >= all(
    select avg(salary)
        from instructor
        group by dept_name
);
+-----------+
| dept_name |
+-----------+
| Physics   |
+-----------+
1 row in set

any

在SQL中关键词any同义于some。早期SQL版本中仅允许使用any,后来的版本为了避免和英语中any一词在语言上的混淆,又添加了另一个可选择的关键词some。

  • any是任意一个
  • some是一些,也就是至少一个
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> select name
from instructor
where salary > any(
    select salary
        from instructor
        where dept_name='Biology'
);
+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Brandt   |
| Kim      |
+----------+
7 rows in set

mysql> select name
from instructor
where salary > some(
    select salary
        from instructor
        where dept_name='Biology'
);
+----------+
| name     |
+----------+
| Wu       |
| Einstein |
| Gold     |
| Katz     |
| Singh    |
| Brandt   |
| Kim      |
+----------+
7 rows in set