J2EE社区

Example 1-4. 带有条件的简单查询

要从表 PART 里面把字段 PRICE 大于 10 的所有记录找出来， 我们写出下面查询：

SELECT * FROM PART
WHERE PRICE > 10;

然后得到表：

 PNO |  PNAME  |  PRICE
-----+---------+--------
3  |  Bolt   |   15
4  |  Cam    |   25

 

在 SELECT语句里使用 "*" 将检索出表中的所有属性。 如果我们只希望从表 PART 中检索出属性 PNAME 和 PRICE， 我们使用下面的语句：

SELECT PNAME, PRICE
FROM PART
WHERE PRICE > 10;

这回我们的结果是：

                      PNAME  |  PRICE
--------+--------
Bolt   |   15
Cam    |   25

请注意 SQL 的 SELECT 语句对应关系演算里面的 "projection" （映射），而不是 "selection"（选择）（参阅 关系演算 获取详细信息）。

 

WHERE 子句里的条件也可以用关键字 OR，AND，和 NOT 逻辑地连接起来：

SELECT PNAME, PRICE
FROM PART
WHERE PNAME = 'Bolt' AND
(PRICE = 0 OR PRICE <= 15);

这样将生成下面的结果：

 PNAME  |  PRICE
--------+--------
Bolt   |   15

 

目标列表和 WHERE 子句里可以使用算术操作。例如， 如果我们想知道如果我们买两个部件的话要多少钱， 我们可以用下面的查询：

SELECT PNAME, PRICE * 2 AS DOUBLE
FROM PART
WHERE PRICE * 2 < 50;

这样我们得到：

 PNAME  |  DOUBLE
--------+---------
Screw  |    20
Nut    |    16
Bolt   |    30

请注意在关键字 AS 后面的 DOUBLE 是第二个列的新名字。 这个技巧可以用于目标列表里的每个元素， 给它们赋予一个在结果列中显示的新的标题。 这个新的标题通常称为别名。这个别名不能在该查询的其他地方使用。

 

Example 1-5. 聚集

果我们想知道表 PART 里面所有部件的平均价格，我们可以使用下面查询：

SELECT AVG(PRICE) AS AVG_PRICE
FROM PART;

 

结果是：

 AVG_PRICE
-----------
14.5

 

如果我们想知道在表 PART 里面存储了多少部件，我们可以使用语句：

SELECT COUNT(PNO)
FROM PART;

得到：

 COUNT
-------
4

 

Example 1-6. 聚集

如果我们想知道每个供应商销售多少个部件，我们可以这样写查询：

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME;

得到：

 SNO | SNAME | COUNT
-----+-------+-------
1  | Smith |   2
2  | Jones |   1
3  | Adams |   2
4  | Blake |   3

 

然后我们看一看发生了什么事情。首先生成表 SUPPLIER 和 SELLS 的连接：

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1   |  Smith  |   1
1   |  Smith  |   2
2   |  Jones  |   4
3   |  Adams  |   1
3   |  Adams  |   3
4   |  Blake  |   2
4   |  Blake  |   3
4   |  Blake  |   4

 

然后我们把那些属性 S.SNO 和 S.SNAME 相同的记录放在组中：

 S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1   |  Smith  |   1
|   2
--------------------------
2   |  Jones  |   4
--------------------------
3   |  Adams  |   1
|   3
--------------------------
4   |  Blake  |   2
|   3
|   4

 

在我们的例子里，我们有四个组并且现在我们可以对每个组应用聚集操作符 COUNT，生成上面给出的查询的最终结果。

Example 1-7. Having

如果我们想知道那些销售超过一个部件的供应商，使用下面查询：

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME
HAVING COUNT(SE.PNO) > 1;

and get:

 SNO | SNAME | COUNT
-----+-------+-------
1  | Smith |   2
3  | Adams |   2
4  | Blake |   3

 

Example 1-8. 子查询

如果我们想知道所有比名为 'Screw' 的部件贵的部件，我们可以用下面的查询：

SELECT *
FROM PART
WHERE PRICE > (SELECT PRICE FROM PART
WHERE PNAME='Screw');

 

结果是：

 PNO |  PNAME  |  PRICE
-----+---------+--------
3  |  Bolt   |   15
4  |  Cam    |   25

 

当我们检查上面的查询时会发现出现了两次 SELECT 关键字。 第一个在查询的开头 - 我们将称之为外层 SELECT - 而另一个在 WHERE 子句里面，成为一个嵌入的查询 - 我们将称之为内层 SELECT。 对外层 SELECT 的每条记录都必须先计算内层 SELECT。在完成所有计算之后， 我们得知名为 'Screw' 部件的记录的价格， 然后我们就可以检查那些价格更贵的记录了。 (实际上，在本例中，内层查询只需要执行一次， 因为它不依赖于外层查询高等状态．)

如果我们想知道那些不销售任何部件的供应商 （比如说，我们想把这些供应商从数据库中删除），我们用：

SELECT *
FROM SUPPLIER S
WHERE NOT EXISTS
(SELECT * FROM SELLS SE
WHERE SE.SNO = S.SNO);

 

在我们的例子里，结果列将是空的，因为每个供应商至少销售一个部件。 请注意我们在 WHERE 子句的内层 SELECT 里使用了来自外层 SELECT 的 S.SNO。 正如前面所说的，子查询为每个外层查询计算一次，也就是说， S.SNO 的值总是从外层 SELECT 的实际记录中取得的。

Example 1-9. FROM 里面的子查询

如果我们想知道在所有我们的供应商中的最高平均部件价格的那家， 我们不能用 MAX(AVG(PRICE))，但我们可以这么写：

SELECT MAX(subtable.avgprice)
FROM (SELECT AVG(P.PRICE) AS avgprice
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO
GROUP BY S.SNO) subtable;

这个子查询为每个供应商返回一行（因为它的 GROUP BY） 然后我们在外层查询对所有行进行聚集．

 

Example 1-10. Union, Intersect, Except

下面的例子是 UNION 的例子：

SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Jones'
UNION
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Adams';

产生结果：

 SNO | SNAME |  CITY
-----+-------+--------
2  | Jones | Paris
3  | Adams | Vienna

 

下面是相交（ INTERSECT）的例子：

SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
INTERSECT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO < 3;

产生结果：

 SNO | SNAME |  CITY
-----+-------+--------
2  | Jones | Paris

两个查询都会返回的元组是那条 SNO=2 的

 

最后是一个 EXCEPT 的例子：

SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
EXCEPT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 3;

结果是：

 SNO | SNAME |  CITY
-----+-------+--------
2  | Jones | Paris
3  | Adams | Vienna