Skip to main content
Version: v1

基礎 SELECT 語法教學

Chinese Version Only

For Canner Enterprise v1.x.x Documentation, we only provide Chinese version available.

所有操作都為 ANSI SQL compliant 的語法。ANSI SQL 相關介紹可以參考 W3School SQL Tutorial。

進階查看完整的支援項目，請點這 完整 SELECT 語法

SELECT​

選擇特定欄位

SELECT column1, column2
FROM table_name;

選擇所有欄位

SELECT *
FROM table_name;

WHERE​

依照條件選擇資料

SELECT *
FROM table_name
WHERE column1 > value1;

AND, OR​

連接多個條件

SELECT *
FROM table_name
WHERE column1 > value1 AND column2 > value1 OR column3 > value1;

Operators​

可允許的比較運算符號以及語法

  • < 小於

    SELECT *
    FROM table_name
    WHERE column1 < value1;

  • > 大於

    SELECT *
    FROM table_name
    WHERE column1 > value1;

  • <= 小於或是等於

    SELECT *
    FROM table_name
    WHERE column1 <= value1;

  • >= 大於或是等於

    SELECT *
    FROM table_name
    WHERE column1 >= value1;

  • = 等於

    SELECT *
    FROM table_name
    WHERE column1 = value1;

  • <> 不等於

    SELECT *
    FROM table_name
    WHERE column1 <> value1;

  • != 不等於

    SELECT *
    FROM table_name
    WHERE column1 != value1;

  • BETWEEN a AND b 介於 a 與 ｂ 

    SELECT *
    FROM table_name
    WHERE column1 BETWEEN value1 AND value2;

  • IN (a, b, c, ...) 為 a, b, c 其中之一 

    SELECT *
    FROM table_name
    WHERE column IN (12, 15, 18)

  • IS NULL 判斷是否為 Null

    SELECT *
    FROM table_name
    WHERE column1 IS NULL;

Operators for Specified Types​

以下是一些常見用法提供你針對不同型別的欄位做搜尋。

  • UUID

    使用 uuid 前綴轉型

    SELECT * FROM table
    WHERE id = uuid 'affa0ba3-2744-47c6-9f2a-35b8e2d56581'

  • Timestamp

    使用 timestamp 前綴轉型

    SELECT * FROM table
    WHERE create_at > timestamp '2019-08-01 00:00'

    使用 date 前綴轉型

    SELECT * FROM table
    WHERE create_at > date '2019-08-01'

    搭配 interval 取得其他時間

    SELECT * FROM table
    WHERE create_at > date '2019-08-01'
    AND create_at < date '2019-08-01' + interval '1' month

  • VARCHAR

    使用 LIKE 搭配 % 去匹配無或是任意字元

    SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
    WHERE column1 LIKE '%b%'
    --returns 'abc' AND  'bcd'

    使用 LIKE 搭配 _，匹配單一字元

    SELECT * FROM (VALUES ('abc'), ('bcd'), ('cde')) AS table_name (column1)
    WHERE column1 like '_b%'
    --returns 'abc'

ORDER BY​

由小至大排序

SELECT *
FROM table_name
ORDER BY column1 ASC

由大至小排序

SELECT *
FROM table_name
ORDER BY column1 DESC

NULL 排序在最前

SELECT *
FROM table_name
ORDER BY column1 ASC NULLS FIRST

NULL 排序在最後

SELECT *
FROM table_name
ORDER BY column1 ASC NULLS LAST

GROUP BY​

依照特定欄位分組

SELECT *
FROM table_name
GROUP BY column1

依照特定欄位分組

SELECT count(*)
FROM table_name
GROUP BY column1

HAVING​

篩選分組過後的資料。

SELECT count(column1), column2
FROM table_name
GROUP BY column2
HAVING count(column1) > value1

JOIN​

依照特定欄位組合兩張表

SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column1

UNION​

合併兩張表

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

LIMIT, OFFSET​

  • LIMIT number 最多取得幾筆資料
  • OFFSET number 忽略前幾筆資料

取得第 5 筆之後的 10 筆資料

SELECT *
FROM table_name
OFFSET 5
LIMIT 10