SQLite 常用 SQL

主鍵自增

創(chuàng)建表時將主鍵列設(shè)置為 INTEGER PRIMARY KEY ， 例如：

CREATE TABLE [Test] (
  [Id] INTEGER PRIMARY KEY, 
  [Name] NVARCHAR(20) NOT NULL
);

最后插入的行號

查看最后插入的行號是用 last_insert_rowid() 函數(shù)， 與 SQL Server 的 @@Identity 類似，例如：

INSERT INTO [Test] (Name)
VALUES ('Test')

SELECT LAST_INSERT_ROWID()

連接兩個字符串

將兩個字符串連接起來用 || ， 例如：

INSERT INTO [Test] (Name)
VALUES ('Test_' || CAST(LAST_INSERT_ROWID() AS NVARCHAR(10)));

日期與時間

SQLite 支持下面 5 個日期與時間函數(shù)：

• date(timestring, modifier, modifier, ...)
• time(timestring, modifier, modifier, ...)
• datetime(timestring, modifier, modifier, ...)
• julianday(timestring, modifier, modifier, ...)
• strftime(format, timestring, modifier, modifier, ...)

這 5 個函數(shù)都接受一個時間字符串作為參數(shù)， 緊跟著的是可選的修飾符。 strftime 函數(shù)還接受一個格式化字符串作為它的第一個參數(shù)。

這些時間日期函數(shù)使用 ISO-8601 規(guī)定的時間日期格式的子集， date 函數(shù)返回 YYYY-MM-DD 格式的日期， time 函數(shù)返回 HH:MM:SS 格式的時間， 而 datetime 則返回 "YYYY-MM-DD HH:MM:SS" 格式的日期時間； julianday 函數(shù)則返回自儒略日（公元前4713年1月1日）的天數(shù)； strftime 返回的格式則由其參數(shù)決定， 下面是 strftime 可以接受的參數(shù)格式：

• %d    day of month: 00
• %f    fractional seconds: SS.SSS
• %H    hour: 00-24
• %j    day of year: 001-366
• %J    Julian day number
• %m    month: 01-12
• %M    minute: 00-59
• %s    seconds since 1970-01-01
• %S    seconds: 00-59
• %w    day of week 0-6 with Sunday==0
• %W    week of year: 00-53
• %Y    year: 0000-9999
• %%    %

所有的日期時間函數(shù)都可以用 strftime 函數(shù)表示， 提供這些函數(shù)的唯一原因是為了方便和高效， 例如：

可接受的時間字符串

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

在第 5 至第 7 個格式中， "T" 是 ISO-8601 規(guī)定的日期時間分隔符； 第 8 至 第 10 個格式中只聲明了時間， 其對應(yīng)的日期為 2000-01-01 ； 第 11 個格式 ‘now’ 表示從系統(tǒng)獲取的當(dāng)前 UTC 時間； 第 12 個格式則表示從儒略日起的日期數(shù)。

修飾符

時間字符串可以跟多個可選的修飾符來對時間進(jìn)行調(diào)整， 每個修飾符是對其左邊時間值得一個轉(zhuǎn)換， 從左到右依次應(yīng)用， 順序很重要。 可用的修飾符如下：

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc

前 6 個修飾符用于向時間字符串或修飾符指定的日期時間添加指定數(shù)量的時間。 注意 "±NNN months" 的工作方式是： 先將原來的日期渲染成 YYYY-MM-DD 格式， 對月份添加 ±NNN 的值， 然后再對結(jié)果進(jìn)行修正。 如果 2001-03-31 添加了 '+1 month' 修飾符， 則應(yīng)先得到 2001-04-31 ， 由于 4 月只有 30 天， 所以結(jié)果會被修正為 2001-05-01 。 如果向閏年的 2 月 29 日添加了 '±N years' 修飾符， 當(dāng) N 不是 4 的倍數(shù)時， 也會發(fā)生這樣的修正。

第 7 到 9 的那些 "start of" 修飾符將日期轉(zhuǎn)換為當(dāng)前日、 月、 年的開始時間。

修飾符 "weekday" 將日期向后推進(jìn)直到下一周的指定天數(shù)。 星期天是 0 ， 星期一是 1 ， 依此類推。

第 11 個修飾符 "unixepoch" 只有在日期格式為 "DDDDDDDDDD" 時才有效， 將 unix 時間戳轉(zhuǎn)換成時間， 由于參數(shù)是 64 位整數(shù)， 因此， 有效地時間范圍是 0000-01-01 00:00:00 至 5352-11-01 10:52:47 (Unix 時間 -62167219200 至 10675199167)

修飾符 "localtime" 將 utc 時間轉(zhuǎn)換為本地時間， "utc" 則相反。

示例

計(jì)算當(dāng)前日期：

SELECT date('now');

計(jì)算當(dāng)前月的最后一天：

SELECT date('now','start of month','+1 month','-1 day');

計(jì)算給定 unix 時間戳對應(yīng)的日期時間：

SELECT datetime(1092941466, 'unixepoch');

計(jì)算給定 unix 時間戳對應(yīng)的日期時間并轉(zhuǎn)換成本地時間：

SELECT datetime(1092941466, 'unixepoch', 'localtime');

計(jì)算當(dāng)前的 unix 時間戳：

SELECT strftime('%s','now');

計(jì)算從美國獨(dú)立到現(xiàn)在的天數(shù)：

SELECT julianday('now') - julianday('1776-07-04');

計(jì)算從2004年某個時刻到現(xiàn)在的秒數(shù)：

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

計(jì)算今年10月份的第一個星期三：

SELECT date('now','start of year','+9 months','weekday 2');

計(jì)算 unix 時間紀(jì)元到現(xiàn)在的秒數(shù)（與 strftime('%s','now') 類似， 包含小數(shù)部分）：

SELECT (julianday('now') - 2440587.5)*86400.0;