SQLite / 語法筆記
ProgrammingSQLite

SQLite / 語法筆記

目錄

▍如何在 SQLite 實現 Truncate Table

TRUNCATE 指令在 SQL Server 中是清除整張 table 的所有 records，但不會刪除整張表單也不會移除 table schema。SQLite 不是使用 TRUNCATE，而是使用 DELETE 這個指令。

DELETE FROM table_name;
Reference

▍SQLite 的時間與日期函數

在 SQLite 中有五個關於時間和日期的函數：

  1. date(time-value, ...) : The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS.
  2. time(time-value, ...) : The time() function returns the time as HH:MM:SS.
  3. datetime(time-value, ...) : The datetime() function returns “YYYY-MM-DD HH:MM:SS”.
  4. julianday(time-value, ...) : The julianday() function returns the Julian day – the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).
  5. strftime(time-value, ...) : The strftime() routine returns the date formatted according to the format string specified as the first argument.

詳細的語法可參考 SQLite 的教學文件：SQLite Date And Time Functions

▍取得當下時間(在地時區)
SELECT datetime(strftime('%s','now'), 'unixepoch', 'localtime')

上列指令回傳的時間格式為 2021-04-07 06:59:00

▍建立 temp table

SQLite 的 temp table 預設是在 temp schema 中，使用方法和一般 table 是一樣的：

--寫入temp table
INSERT INTO temp.temp_table_name
SELECT
    column_name_a,
    column_name_b
FROM table_name

--刪除temp table
DROP TABLE temp.temp_table_name

▍SQLite 只支援 INNER 及 LEFT JOIN，使用 FULL OUTER JOIN 需分兩段執行

因為 SQLite 只支援 INNER JOIN 和 LEFT JOIN 兩種合併方式，所以要實現其他的合併方式就必須換個方法。RIGHT JOIN 還很容易，把左右倒過來就可以了。但如果要實現 FULL OUTER JOIN (聯集) 的話，就必須分兩段執行在用 UNION ALL 把結果合併成同一張 table。

例如，要將 table_a 和 table_b 取聯集合併，需先將 table_a LEFT JOIN table_b，再 UNION ALL 合併存在 table_b 但不存在 table_a 的資料：

SELECT
    a.*,
    b.*
FROM table_a a
LEFT JOIN table_b b
ON <conditions>
UNION ALL
SELECT
    a.*,
    b.*
FROM table_b b
LEFT JOIN table_a a
ON <conditions>
WHERE a.column_name IS NULL --取talbe_a中任一欄位為null，表示資料存在table_b但不存在talbe_a

Copyright announcement:
the featured image: Photo by Sigmund on Unsplash