iT邦幫忙

2022 iThome 鐵人賽

DAY 13
0
Software Development

新手小白的每天一點SQL系列 第 13

Day 13 選取資料 - WHERE

我們在 Day 12 學習了如何選取資料表的資料,並且了解可以選擇特定欄位輸出。

這次我們使用 racehorse 當作範例,我們先把所有資料撈出來:

SELECT * FROM racehorse;

我們可以選擇想看的欄位,比如這次我們只想看id, name, birth_date, coat_color,我們可以這樣寫:

SELECT id, name, birth_date, coat_color FROM racehorse;
 id |     name      | birth_date | coat_color
----+---------------+------------+------------
  1 | Gold Ship     | 2009-03-06 | Grey
  2 | Vodka         | 2004-04-04 | Bay
  3 | Daiwa Scarlet | 1995-02-18 | Chestnut
  4 | Grass Wonder  | 1995-02-18 | Chestnut
  5 | Oguri Cap     | 1985-03-27 | Gray

使用 WHERE 設定條件

但我們有時候並不需要全部的資料,有時候我們只想看特定條件的資料。
比如,我只對毛色(coat_color)為栗色(Chestnut)的賽馬有興趣,我們就可以使用 WHERE 定義我們想要的條件。

先來看 WHERE 的 SQL 語法:

SELECT * FROM table_name WHERE <condition>;

我們可以這樣寫:

SELECT id, name, birth_date, coat_color
FROM racehorse
WHERE coat_color = 'Chestnut';

這樣就會輸出我們想看的資料了。

當然,WHERE 當然不是只有 = 可以使用。
如同其他程式語言, SQL 有許多比較運算子可以運用。


參考如下,引用自How to filter query results in PostgreSQL

  • = equal to
  • > greater than
  • < less than
  • >= greater than or equal to
  • <= less than or equal to
  • <> or != not equal
  • AND the logical "and" operator — joins two conditions and returns TRUE if both of the conditions are TRUE
  • OR logical "or" operator — joins two conditions and returns TRUE if at least one of the conditions are TRUE
  • IN value is contained in the list, series, or range that follows
  • BETWEEN value is contained within the range the minimum and maximum values that follow, inclusive
  • IS NULL matches if value is NULL
  • NOT negates the boolean value that follows
  • EXISTS the query that follows contains results
  • LIKE matches against a pattern (using the wildcards % to match 0 or more characters and _ to match a single character)
  • ILIKE matches against a pattern (using the wildcards % to match 0 or more characters and _ to match a single character), case insensitive
  • SIMILAR TO matches against a pattern using SQL's regular expression dialect
  • ~ matches against a pattern using POSIX regular expressions, case sensitive
  • ~* matches against a pattern using POSIX regular expressions, case insensitive
  • !~ does not match against a pattern using POSIX regular expressions, case sensitive
  • !~* does not match against a pattern using POSIX regular expressions, case insensitive

參考資料:


上一篇
Day 12 選取資料 - SELECT
下一篇
Day 14 選取資料 - ORDER BY、LIMIT
系列文
新手小白的每天一點SQL31
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言