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