SQL WHERE
SELECT “欄位”,”欄位” FROM “表格名稱” WHERE 條件(列 運算符 值);
WHERE子句中可以使用以下運算符：
| 操作符 |
描述 |
| = |
等於 |
| <> |
不等於 |
| > |
大於 |
| < |
小於 |
| >= |
大於等於 |
| <= |
小於等於 |
| BETWEEN |
在某個範圍內 |
| LIKE |
搜尋某種模式 |
| IN |
指定列可能有多個值 |
WHERE 子句使用:
如果希望透過居住的城市 "Mexico" 中的客戶，我們就需要向 SELECT 語句增加 WHERE 子句
SELECT * FROM Customers WHERE Country='Mexico';
| CustomerID |
CompanyName |
ContactName |
Address |
City |
PostalCode |
Country |
| ALFKI |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
| ANATR |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitucion 2222 |
Mexico D.F. |
05021 |
Mexico |
| ANTON |
Antonio Moreno Taqueria |
Antonio Moreno |
Mataderos 2312 |
Mexico D.F. |
05023 |
Mexico |
| AROUT |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
| BERGS |
Berglunds snabbkop |
Christina Berglund |
Berguvsvagen 8 |
Lulea |
S-958 22 |
Sweden |
| BLAUS |
Blauer See Delikatessen |
Hanna Moos |
Forsterstr. 57 |
Mannheim |
68306 |
Germany |
| BLONP |
Blondesddsl pere et fils |
Frederique Citeaux |
24, place Kleber |
Strasbourg |
67000 |
France |
| BOLID |
Bolido Comidas preparadas |
Martin Sommer |
C/ Araquil, 67 |
Madrid |
28023 |
Spain |
| BONAP |
Bon app' |
Laurence Lebihan |
12, rue des Bouchers |
Marseille |
13008 |
France |
| BOTTM |
Bottom-Dollar Markets |
Elizabeth Lincoln |
23 Tsawassen Blvd. |
Tsawassen |
T2F 8M4 |
Canada |
結果:
| CustomerID |
CompanyName |
ContactName |
Address |
City |
PostalCode |
Country |
| ANATR |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitucion 2222 |
Mexico D.F. |
05021 |
Mexico |
| ANTON |
Antonio Moreno Taqueria |
Antonio Moreno |
Mataderos 2312 |
Mexico D.F. |
05023 |
Mexico |
如果希望透過客戶 "CustomerID" 找出 "A" 開頭的客戶，向 WHERE 子句 添加 like
SELECT * FROM Customers WHERE CustomerID like 'A%';
結果:
| CustomerID |
CompanyName |
ContactName |
Address |
City |
PostalCode |
Country |
| ALFKI |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
| ANATR |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitucion 2222 |
Mexico D.F. |
05021 |
Mexico |
| ANTON |
Antonio Moreno Taqueria |
Antonio Moreno |
Mataderos 2312 |
Mexico D.F. |
05023 |
Mexico |
| AROUT |
Around the Horn |
Thomas Hardy |
120 Hanover Sq. |
London |
WA1 1DP |
UK |
如果希望透產品"Products"資料表中，找出小於< 10.00 (U.S)價錢，就須在 WHERE子句條件加入小於<
SELECT * FROM Products where UnitPrice < 10.00 ;
| ProductID |
ProductName |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
| 1 |
Chai |
10 boxes x 20 bags |
18.00 |
39 |
| 2 |
Chang |
24 - 12 oz bottles |
19.00 |
17 |
| 3 |
Aniseed Syrup |
12 - 550 ml bottles |
10.00 |
13 |
| 4 |
Queso Manchego La Pastora |
10 - 500 g pkgs. |
38.00 |
86 |
| 5 |
Konbu |
2 kg box |
6.00 |
24 |
| 6 |
Tofu |
40 - 100 g pkgs. |
23.25 |
35 |
| 7 |
Teatime Chocolate Biscuits |
10 boxes x 12 pieces |
9.20 |
25 |
| 8 |
Carnarvon Tigers |
16 kg pkg. |
62.50 |
42 |
| 9 |
Grandma's Boysenberry Spread |
12 - 8 oz jars |
25.00 |
120 |
| 10 |
Sir Rodney's Marmalade |
30 gift boxes |
81.00 |
40 |
結果:顯示兩筆符合條件下
| ProductID |
ProductName |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
| 5 |
Konbu |
2 kg box |
6.00 |
24 |
| 7 |
Teatime Chocolate Biscuits |
10 boxes x 12 pieces |
9.20 |
25 |
相反之，如果希望透產品"Products"資料表中，找出大於> 10.00 (U.S)價錢，就須在 WHERE子句條件加入大於> ，結果:顯示7筆符合條件
SELECT * FROM Products where UnitPrice > 10.00 ;
| ProductID |
ProductName |
QuantityPerUnit |
UnitPrice |
UnitsInStock |
| 1 |
Chai |
10 boxes x 20 bags |
18.00 |
39 |
| 2 |
Chang |
24 - 12 oz bottles |
19.00 |
17 |
| 4 |
Queso Manchego La Pastora |
10 - 500 g pkgs. |
38.00 |
86 |
| 6 |
Tofu |
40 - 100 g pkgs. |
23.25 |
35 |
| 8 |
Carnarvon Tigers |
16 kg pkg. |
62.50 |
42 |
| 9 |
Grandma's Boysenberry Spread |
12 - 8 oz jars |
25.00 |
120 |
| 10 |
Sir Rodney's Marmalade |
30 gift boxes |
81.00 |
40 |