基礎 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 與 bSELECT *
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' monthVARCHAR
使用
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