程式設計

請選擇下方類別,查看您想了解的「程式設計」知識。

  • 資料庫程式設計
  • import_contacts WHERE
    130
適用範圍

在指定結果中,需要某些資料的時候,這時候可使用條件式WHERE,這個指令來做選擇性的抓取 SELECT 欄位, 欄位, ... FROM 資料表 WHERE 條件1 ;

實用性:
重要性:

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