什麼是 SQL 建構器

Visual SQL Builder 讓您在表單中輸入資料表名稱、要取得的欄位、篩選條件與 JOIN 條件,以視覺化方式組出 SELECT 語句。比起每次都要從頭想語法,透過表單整理條件更適合正在學習 SQL 的人,也適合日常的資料抽取工作。

如何用 SQL 建構器以視覺化方式組出 SELECT、JOIN 查詢

輸入的資料表名稱與條件不會傳送到伺服器,SQL 是在瀏覽器內產生的。

逐步組出一個查詢

  1. 決定主要資料表
  2. 加入想取得的欄位(未輸入則為 *)
  3. 視需要指定 WHERE 條件
  4. 需要其他資料表時加入 JOIN
  5. 想做彙總時,指定彙總函式(COUNT / SUM / AVG / MIN / MAX)、GROUP BY 與 HAVING
  6. 視需要指定排序(ORDER BY)、LIMIT / OFFSET 與 DISTINCT
  7. 檢查產生的 SQL,需要的話用 SQL 格式化工具 整理

例如想確認每位使用者的訂單數量,可以結合 users 與 orders:

SELECT
  users.id,
  users.name,
  COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE users.deleted_at IS NULL
GROUP BY users.id, users.name
ORDER BY order_count DESC;

彙總、排序與子查詢

像上面範例這樣的彙總查詢,也能只靠表單組出來。

  • 彙總欄:可選 COUNT / SUM / AVG / MIN / MAX。別名(AS)留空時,會自動加上 count_all、sum_price 之類的名稱。
  • GROUP BY:有彙總欄時,未被彙總的選取欄會自動加進 GROUP BY(漏掉的話在多數資料庫會報錯)。
  • HAVING:可以對彙總結果指定條件。
  • ORDER BY:可依欄位或彙總欄的別名做升冪、降冪排序。
  • LIMIT / OFFSET:只會輸出 0 以上的整數。OFFSET 只有在有 LIMIT 時才會輸出(只指定 OFFSET 在 SQLite、MySQL 會報錯)。
  • DISTINCT:去除重複的列。
  • 子查詢(WHERE 的右側):搭配 IN / NOT IN 或 =、> 等比較,可在右側放一層 (SELECT … FROM … WHERE …)。選擇彙總函式時,會變成像 (SELECT AVG(price) FROM …) 這樣與純量值的比較。
  • AND / OR 與 BETWEEN:從第二列起,每個 WHERE 條件都可以選擇 AND 或 OR。AND 的結合優先於 OR,因此有 OR 時,以 AND 相連的條件在輸出時會用括號括起來(例如 (a = 1 AND b = 2) OR c = 3)。BETWEEN 需要填入下限與上限兩個值
  • EXISTS / NOT EXISTS:運算子選擇它之後,會變成檢查是否存在與外層資料表欄位相符之列的相關子查詢(EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id))。使用與外層相同的資料表時,內層會加上別名(users_2)
  • UNION / UNION ALL 與 WITH(CTE):從上方的分頁可以新增要連接的查詢與 CTE。每個分頁都能設定各自的資料表、條件與彙總,而在主查詢中指定的 ORDER BY、LIMIT、OFFSET 會套用到整個 UNION(因為是排序合併後的結果,ORDER BY 只會以欄位名稱輸出)
  • FROM 的子查詢:用「新增 FROM 的子查詢」建立的查詢,只要把它的名稱當作主查詢(或 JOIN)的資料表名稱,就會展開成 FROM (SELECT …) AS 名稱。不會輸出到 WITH
  • 視窗函式:選擇 ROW_NUMBER、RANK、DENSE_RANK、SUM、AVG、COUNT、MIN、MAX、LAG 或 LEAD,並輸入 PARTITION BY 與 ORDER BY(也能像 users.salary DESC 這樣指定方向)。排名函式、LAG、LEAD 沒有 ORDER BY 時不會輸出

SELECT 以外,INSERT / UPDATE / DELETE 可以在下一節的區塊中產生。預存程序,以及 MERGE 這類各資料庫寫法差異很大的語法則不支援。

數值型別如何自動判斷並加上引號

WHERE 的值是以文字輸入的,但若原封不動嵌進 SQL,連 age > 20 這樣的數值條件也會變成 age > '20',多出不必要的引號。工具會檢查值:是數字就維持原樣,其他則自動補上單引號。判斷規則如下。

輸入值 輸出 原因
20 / -1.5 / 1e3 原樣 可以寫成十進位數字
007 / 0x1F '007' / '0x1F' 以 0 開頭的數字視為字串,避免郵遞區號或代碼開頭的 0 消失;十六進位寫法在 MySQL 中會被當成字串
active 'active' 不是數字,因此自動加引號
'active' 'active' 自己已加上引號的值會原樣保留
O'Reilly 'O''Reilly' 值中的單引號會重複成 '' 來跳脫

運算子也會影響處理方式。LIKE / NOT LIKE 的模式即使看起來像數字,也一律視為字串;IN / NOT IN 會逐一判斷以逗號分隔輸入的值,組成像 (1, 2, 'a') 的清單;IS NULL / IS NOT NULL 不接受值。

思考 JOIN 該怎麼選

不確定該用哪種 JOIN 時,先決定要保留哪些列會比較好判斷。

想做的事 JOIN 種類
只想取得有訂單的使用者 INNER JOIN
想連沒有訂單的使用者也一併列出 LEFT JOIN
想附加主檔資料 LEFT JOIN
只想篩選出有關聯資料的列 INNER JOIN

INNER JOIN 只取得兩張表都有對應資料的列,LEFT JOIN 則保留左表的列並連接右表。在工具中切換 JOIN 種類並觀察產生的 SQL 變化,能實際感受這個差異。若要了解 RIGHT JOIN、FULL JOIN、SELF JOIN 等完整內容,可參考 SQL JOIN 完全參考。

連接多張資料表

要連接三張以上的資料表,只要在工具中持續新增 JOIN 即可。連接順序會從已連到 FROM 的資料表開始依序重新排列,所以不必在意輸入的順序。

  • 同一張資料表用兩次(自我連接):第二個以後會加上 <資料表名稱>_2 之類的別名來區分。
  • 沒有連接條件的資料表:不會被默默丟掉,而是依其字面意思(無條件的笛卡兒積)以 CROSS JOIN 保留。
  • 從 DDL 載入時:貼上 CREATE TABLE,就會由外鍵產生連接條件。同樣兩張資料表之間有兩個外鍵時,不會新增 JOIN,而是用 AND 補上條件。

每新增一個 JOIN 就多一行,所以不論在表單上或產生的結果中,都容易追蹤「現在哪些資料表與哪些資料表相連」。

檢查產生的 SQL 時要注意的重點

  • 資料表名稱與欄位名稱是否與實際資料庫一致
  • JOIN 條件是否有遺漏
  • WHERE 條件是否過嚴或過鬆
  • 進行彙總時,GROUP BY 的粒度是否正確
  • 需要的地方是否加上了 ORDER BY 或 LIMIT
  • 對正式環境的資料而言,這個查詢會不會太重

SQL 建構器產生的是查詢草稿。最終仍應搭配所使用資料庫的方言、索引與執行計畫一併確認。

為什麼很適合用來學 SQL

比起死背語法,理解每個子句各自負責什麼能更快掌握 SQL。SELECT 決定要取出的欄位、FROM 指定資料表、WHERE 負責篩選、JOIN 負責合併資料表、ORDER BY 負責排序——一邊操作表單一邊觀察產生的結果,這些對應關係會很自然地浮現出來。熟悉之後,也可以試著手動修改產生的查詢;用 SQL 格式化工具 整理過的結果,還能留作易讀的學習範例。另外有一點值得及早知道:撰寫子句的順序,並不是資料庫評估它們的順序——這正是 WHERE 看不到 SELECT 別名的原因。兩種順序的對照整理在 SQL 子句執行順序參考。

也能產生 INSERT、UPDATE、DELETE

頁面下方的「變更資料」區塊會以查詢中放入的資料表與欄位為候選,產生新增、更新、刪除資料列的語句。

「變更資料」區塊:產生多列 INSERT 與 upsert(ON CONFLICT)

  • INSERT:像表格一樣輸入欄位與值。「新增資料列」會變成多列的 VALUES,新增欄位時各列的對應也不會錯位。勾選「插入上方查詢的結果」,就會變成沿用上方 SELECT 的 INSERT … SELECT
  • 已有相同鍵的資料列時(upsert):可選「忽略」或「更新」。PostgreSQL、SQLite 輸出 ON CONFLICT (鍵) DO UPDATE SET … = EXCLUDED.… / DO NOTHING(SQLite 的忽略為 INSERT OR IGNORE),MySQL 輸出 ON DUPLICATE KEY UPDATE … = VALUES(…) / INSERT IGNORE
  • UPDATE / DELETE:指定 SET 的欄位與值,以及 WHERE(AND / OR、IN、LIKE、IS NULL)。沒有 WHERE 時,會警告所有資料列都會受影響
  • 帶 JOIN 的 UPDATE / DELETE:輸入要 JOIN 的資料表與欄位後,PostgreSQL、SQLite 輸出 UPDATE … FROM、DELETE … USING,MySQL 輸出 UPDATE … JOIN、DELETE t FROM … JOIN(SQLite 沒有帶 JOIN 的 DELETE,會顯示警告)。值寫成 o2.status 會視為欄位參照
  • CREATE VIEW / TABLE AS:把上方查詢變成 CREATE [OR REPLACE] VIEW、MATERIALIZED VIEW(PostgreSQL)或 CREATE TABLE … AS
  • 值會自動加上引號。NULL、TRUE、FALSE、NOW() 會直接保留;若要字串 'NULL',請用引號包起來

輸入內容會自動儲存在瀏覽器中,不會傳送到伺服器。

做到一半的查詢會自動儲存

正在組裝的查詢(資料表、聯結、條件、彙總、GROUP BY、HAVING、排序、LIMIT、CTE、UNION、視窗函數)會自動儲存在這個瀏覽器中。即使關閉頁面,下次開啟也能接著使用。按「清除」會回到初始狀態,也可以刪除已儲存的內容,或以 JSON 匯出、匯入(要換瀏覽器或裝置時很方便)。不會傳送到伺服器。

常見問題

只靠 SQL 建構器能完成複雜的 SQL 嗎?

除了基本的 SELECT、WHERE、JOIN,還能組出 AND / OR、BETWEEN、EXISTS、彙總(GROUP BY / HAVING)、ORDER BY、LIMIT / OFFSET、DISTINCT、一層的子查詢、FROM 的子查詢、視窗函式、UNION / UNION ALL 與 WITH(CTE)。各資料庫專屬的函式、需要效能調校的 SQL,以及 SELECT 以外的語句,請在產生後自行手動補寫並確認。

數值與字串的引號是怎麼判斷的?

能寫成十進位的數字(如 20 或 -1.5)會原樣輸出,其他則自動加上單引號。以 0 開頭的數字(007)視為字串,自己已加上引號的值不會被重複加引號。值中的 ' 會跳脫成 ''。LIKE 一律視為字串,IN 會逐一判斷以逗號分隔的值,IS NULL 則不使用值。

SQL 初學者也能使用嗎?

可以。一邊輸入項目一邊確認 SQL 的樣貌,很適合邊學邊用,特別是拿來比較不同 JOIN 種類與條件式的差異。

輸入的資料表名稱或條件會傳送到伺服器嗎?

不會。SQL 建構器在瀏覽器內運作,不會將輸入內容傳送到伺服器,SQL 是在本機產生的。

總結

  • SQL 建構器把 SQL 從「靠背誦寫出來」變成「整理條件後組合出來」
  • 值的引號會自動判斷是否為十進位數字(以 0 開頭的數字視為字串),並避免重複加引號
  • 在表單中切換 JOIN 種類並比較結果,是理解 JOIN 最快的方式
  • 可組出 AND / OR、BETWEEN、EXISTS、彙總(GROUP BY / HAVING)、ORDER BY、LIMIT / OFFSET、DISTINCT、子查詢、視窗函式、UNION 與 WITH(CTE)(不支援 SELECT 以外的語句)

當 SELECT 或 JOIN 語法卡住時,先用 Visual SQL Builder 組出查詢骨架,再視需要格式化、檢查後執行即可。