SQLLite相關(guān)內(nèi)容

SQLLite學習筆記

SQLite，是一款輕型的數(shù)據(jù)庫，是遵守ACID的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，它包含在一個相對小的C庫中。它能夠支持Windows/Linux/Unix等等主流的操作系統(tǒng)，同時能夠跟很多程序語言相結(jié)合，比如 Tcl、C#、PHP、Java等。它的處理速度非�？�。

一、基本操作

建表：

關(guān)鍵字：AUTOINCREMENT可用于某一列得值自動遞增，只能用于整形字段，可在建表得時候加入這個關(guān)鍵字

刪除表：

向表中插入新數(shù)據(jù)

或者

第二種寫法必須確保值得順序與列在表中得順序一致

查詢表

后面可以跟WHERE 進行條件查詢，“*”表示查詢表中所有列，也可以這樣

--子查詢或者內(nèi)部查詢或者嵌套查詢時在另一個SQL利特查詢內(nèi)嵌入在where子句中得查詢。

以下是子查詢必須遵循的幾個規(guī)則：

· 子查詢必須用括號括起來。

· 子查詢在 SELECT 子句中只能有一個列，除非在主查詢中有多列，與子查詢的所選列進行比較。

· ORDER BY 不能用在子查詢中，雖然主查詢可以使用 ORDER BY�？梢栽谧硬樵冎惺褂� GROUP BY，功能與 ORDER BY 相同。

· 子查詢返回多于一行，只能與多值運算符一起使用，如 IN 運算符。

· BETWEEN 運算符不能與子查詢一起使用，但是，BETWEEN 可在子查詢內(nèi)使用。

修改表數(shù)據(jù)

刪除表數(shù)據(jù)

二、運算符

假設變量 a=10，變量b=20，則：

算數(shù)運算符

比較運算符

邏輯運算符

1、位運算符

下表中列出了 SQLite 語言支持的位運算符。假設變量 A=60，變量 B=13，則：

三、不常用的子句

1、GLOB

在平時我們的sql中，并不常用GLOB，GLOB的作用與LIKE基本一致，最大的不同是GLOB區(qū)分大小寫。星號（*）代表零個、一個或多個數(shù)字或字符。問號（?）代表一個單一的數(shù)字或字符。

與LIKE對比：GLOB的*與LIKE的%作用一致

GLOB的？與LIKE的_作用一致

2、HAVING

指定條件過濾，就這樣看來我們可能就會聯(lián)想到WHERE，那么WHERE與HAVING有什么不同呢？其實二者區(qū)別其實挺大的。

（1）HAVING 子句允許指定條件來過濾將出現(xiàn)在最終結(jié)果中的分組結(jié)果。

（2）WHERE 子句在所選列上設置條件，而 HAVING 子句則在由 GROUP BY 子句創(chuàng)建的分組上設置條件。

(3)HAVING只能與SELECT語句一起使用。
(4)HAVING通常在GROUPBY子句中使用。
(5)如果不使用GROUPBY子句，則HAVING的行為與WHERE子句一樣。

(6)在一個查詢中，HAVING 子句必須放在 GROUP BY 子句之后，必須放在 ORDER BY 子句之前。

--舉例子說明：查詢table表查詢每一個班級中年齡大于20，性別為男的人數(shù)

selectCOUNT(*)as'>20歲人數(shù)',classidfromTable1wheresex='男'groupbyclassid,agehavingage>20

--需要注意說明：當同時含有where子句、groupby子句、having子句及聚集函數(shù)時，執(zhí)行順序如下：

--執(zhí)行where子句查找符合條件的數(shù)據(jù)；

--使用groupby子句對數(shù)據(jù)進行分組；對groupby子句形成的組運行聚集函數(shù)計算每一組的值；最后用having子句去掉不符合條件的組。

--having子句中的每一個元素也必須出現(xiàn)在select列表中。有些數(shù)據(jù)庫例外，如oracle.

--having子句和where子句都可以用來設定限制條件以使查詢結(jié)果滿足一定的條件限制。

--having子句限制的是組，而不是行。where子句中不能使用聚集函數(shù)，而having子句中可以。

四、Distinct 關(guān)鍵字

SQLite 的 DISTINCT 關(guān)鍵字與 SELECT 語句一起使用，來消除所有重復的記錄，并只獲取唯一一次記錄。

有可能出現(xiàn)一種情況，在一個表中有多個重復的記錄。當提取這樣的記錄時，DISTINCT 關(guān)鍵字就顯得特別有意義，它只獲取唯一一次記錄，而不是獲取重復記錄。

五、約束

約束是在表的數(shù)據(jù)列上強制執(zhí)行的規(guī)則。這些是用來限制可以插入到表中的數(shù)據(jù)類型。這確保了數(shù)據(jù)庫中數(shù)據(jù)的準確性和可靠性。

約束可以是列級或表級。列級約束僅適用于列，表級約束被應用到整個表。

以下是在 SQLite 中常用的約束。

· NOT NULL 約束：確保某列不能有 NULL 值。

· DEFAULT 約束：當某列沒有指定值時，為該列提供默認值。

· UNIQUE 約束：確保某列中的所有值是不同的。

· PRIMARY Key 約束：唯一標識數(shù)據(jù)庫表中的各行/記錄。

· CHECK 約束：CHECK 約束確保某列中的所有值滿足一定條件。

六、SQLite Joins

SQLite 的 Joins 子句用于結(jié)合兩個或多個數(shù)據(jù)庫中表的記錄。JOIN 是一種通過共同值來結(jié)合兩個表中字段的手段。

SQL 定義了三種主要類型的連接：

· 交叉連接 - CROSS JOIN

· 內(nèi)連接 - INNER JOIN

· 外連接 - OUTER JOIN

1、交叉連接CROSS JOIN

交叉連接(CROSSJOIN)沒有WHERE 子句，它返回連接表中所有數(shù)據(jù)行的笛卡爾積，其結(jié)果集合中的數(shù)據(jù)行數(shù)等于第一個表中符合查詢條件的數(shù)據(jù)行數(shù)乘以第二個表中符合查詢條件的數(shù)據(jù)行數(shù)。由于交叉連接（CROSS JOIN）有可能產(chǎn)生非常大的表，使用時必須謹慎，只在適當?shù)臅r候使用它們。

2、內(nèi)連接inner join

內(nèi)連接的結(jié)果是從兩個或者兩個以上的表的組合中挑選出符合連接條件的數(shù)據(jù)。如果數(shù)據(jù)無法滿足連接條件則將其丟棄。在內(nèi)連接中，參與連接的表的地位是平等的。

內(nèi)連接查詢操作列出與連接條件匹配的數(shù)據(jù)行，它使用比較運算符比較被連接列的列值。內(nèi)連接分三種：

1)等值連接：在連接條件中使用等于號(=)運算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的所有列，包括其中的重復列。

2)不等連接： 在連接條件使用除等于運算符以外的其它比較運算符比較被連接的列的列值。這些運算符包括>、>=、<=、<、!>、!&lt;和<>。

3)自然連接：在連接條件中使用等于(=)運算符比較被連接列的列值，但它使用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除連接表中的重復列。

3、外連接

外連接（OUTERJOIN）是內(nèi)連接（INNER JOIN）的擴展。雖然 SQL標準定義了三種類型的外連接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外連接（LEFT OUTER JOIN）。外連接（OUTER JOIN）聲明條件的方法與內(nèi)連接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 關(guān)鍵字來表達。最初的結(jié)果表以相同的方式進行計算。一旦主連接計算完成，外連接（OUTER JOIN）將從一個或兩個表中任何未連接的行合并進來，外連接的列使用 NULL值，將它們附加到結(jié)果表中。

七、unions子句

1、SQLite的 UNION 子句/運算符用于合并兩個或多個 SELECT 語句的結(jié)果，不返回任何重復的行。

為了使用UNION，每個 SELECT 被選擇的列數(shù)必須是相同的，相同數(shù)目的列表達式，相同的數(shù)據(jù)類型，并確保它們有相同的順序，但它們不必具有相同的長度。

2、UNIONALL 子句

UNION ALL 運算符用于結(jié)合兩個 SELECT 語句的結(jié)果，包括重復行。

適用于UNION 的規(guī)則同樣適用于 UNION ALL 運算符。

八、觸發(fā)器

SQLite 觸發(fā)器（Trigger）是數(shù)據(jù)庫的回調(diào)函數(shù)，它會在指定的數(shù)據(jù)庫事件發(fā)生時自動執(zhí)行/調(diào)用。以下是關(guān)于 SQLite 的觸發(fā)器（Trigger）的要點：

• SQLite 的觸發(fā)器（Trigger）可以指定在特定的數(shù)據(jù)庫表發(fā)生 DELETE、INSERT 或 UPDATE 時觸發(fā)，或在一個或多個指定表的列發(fā)生更新時觸發(fā)。

• SQLite 只支持 FOR EACH ROW 觸發(fā)器（Trigger），沒有 FOR EACH STATEMENT 觸發(fā)器（Trigger）。因此，明確指定 FOR EACH ROW 是可選的。

• WHEN 子句和觸發(fā)器（Trigger）動作可能訪問使用表單 NEW.column-name 和 OLD.column-name 的引用插入、刪除或更新的行元素，其中 column-name 是從與觸發(fā)器關(guān)聯(lián)的表的列的名稱。

• 如果提供 WHEN 子句，則只針對 WHEN 子句為真的指定行執(zhí)行 SQL 語句。如果沒有提供 WHEN 子句，則針對所有行執(zhí)行 SQL 語句。

• BEFORE 或 AFTER 關(guān)鍵字決定何時執(zhí)行觸發(fā)器動作，決定是在關(guān)聯(lián)行的插入、修改或刪除之前或者之后執(zhí)行觸發(fā)器動作。

• 當觸發(fā)器相關(guān)聯(lián)的表刪除時，自動刪除觸發(fā)器（Trigger）。

• 要修改的表必須存在于同一數(shù)據(jù)庫中，作為觸發(fā)器被附加的表或視圖，且必須只使用 tablename，而不是 database.tablename。

• 一個特殊的 SQL 函數(shù) RAISE() 可用于觸發(fā)器程序內(nèi)拋出異常。

示例：

SELECT name FROM sqlite_master WHEREtype = 'trigger';可查看所有的觸發(fā)器

for each row 是操作語句每影響到一行的時候就觸發(fā)一次，也就是刪了 10 行就觸發(fā) 10 次，而 for each state 一條操作語句就觸發(fā)一次，有時沒有被影響的行也執(zhí)行。sqlite 只實現(xiàn)了 for each row 的觸發(fā)。when 和 for each row 用法是這樣的：

CREATETRIGGER trigger_name

AFTERUPDATE OF id ON table_1

FOR EACHROW

WHEN new.id>30

BEGIN

UPDATEtable_2 SET id=new.id WHEREtable_2.id=old.id;

END;

上面的觸發(fā)器在 table_1 改 id 的時候如果新的 id>30 就把 表table_2 中和表table_1 id 相等的行一起改為新的 id

九、索引

索引（Index）是一種特殊的查找表，數(shù)據(jù)庫搜索引擎用來加快數(shù)據(jù)檢索。索引有助于加快 SELECT 查詢和WHERE 子句，但它會減慢使用 UPDATE 和INSERT 語句時的數(shù)據(jù)輸入。索引可以創(chuàng)建或刪除，但不會影響數(shù)據(jù)。

單列索引

單列索引是一個只基于表的一個列上創(chuàng)建的索引�；�本語法如下：

CREATE INDEX index_name

ON table_name (column_name);

唯一索引

使用唯一索引不僅是為了性能，同時也為了數(shù)據(jù)的完整性。唯一索引不允許任何重復的值插入到表中。基本語法如下：

CREATE UNIQUE INDEX index_name

on table_name (column_name);

組合索引

組合索引是基于一個表的兩個或多個列上創(chuàng)建的索引。基本語法如下：

CREATE INDEX index_name

on table_name (column1, column2);

是否要創(chuàng)建一個單列索引還是組合索引，要考慮到您在作為查詢過濾條件的 WHERE 子句中使用非常頻繁的列。

如果值使用到一個列，則選擇使用單列索引。如果在作為過濾的 WHERE 子句中有兩個或多個列經(jīng)常使用，則選擇使用組合索引。

隱式索引

隱式索引是在創(chuàng)建對象時，由數(shù)據(jù)庫服務器自動創(chuàng)建的索引。索引自動創(chuàng)建為主鍵約束和唯一約束。

SELECT * FROM sqlite_master WHEREtype = 'index';查看數(shù)據(jù)庫中的所有索引

DROP INDEX index_name;一個索引可以使用 SQLite 的 DROP 命令刪除

什么情況下要避免使用索引？

雖然索引的目的在于提高數(shù)據(jù)庫的性能，但這里有幾個情況需要避免使用索引。使用索引時，應重新考慮下列準則：

· 索引不應該使用在較小的表上。

· 索引不應該使用在有頻繁的大批量的更新或插入操作的表上。

· 索引不應該使用在含有大量的 NULL 值的列上。

· 索引不應該使用在頻繁操作的列上。

Indexed By

"INDEXEDBY index-name" 子句規(guī)定必須需要命名的索引來查找前面表中值。

如果索引名 index-name 不存在或不能用于查詢，然后 SQLite 語句的準備失敗。

"NOTINDEXED" 子句規(guī)定當訪問前面的表（包括由 UNIQUE 和 PRIMARYKEY 約束創(chuàng)建的隱式索引）時，沒有使用索引。

然而，即使指定了 "NOT INDEXED"，INTEGER PRIMARY KEY仍然可以被用于查找條目。

INDEXED BY可以與 DELETE、UPDATE 或SELECT 語句一起使用：

SELECT DELETE UPDATE column1, column2...

INDEXED BY (index_name)

table_name

WHERE (CONDITION);

十、重命名表或向表中插入新的字段

用來重命名已有的表的 ALTERTABLE 的基本語法如下：

ALTERTABLE database_name.table_name RENAME TOnew_table_name;

用來在已有的表中添加一個新的列的 ALTERTABLE 的基本語法如下：

ALTERTABLE database_name.table_name ADD COLUMNcolumn_def...;

十一、視圖

視圖（View）只不過是通過相關(guān)的名稱存儲在數(shù)據(jù)庫中的一個 SQLite 語句。視圖（View）實際上是一個以預定義的 SQLite 查詢形式存在的表的組合。

視圖（View）可以包含一個表的所有行或從一個或多個表選定行。視圖（View）可以從一個或多個表創(chuàng)建，這取決于要創(chuàng)建視圖的 SQLite 查詢。、

視圖（View）是一種虛表，允許用戶實現(xiàn)以下幾點：

① 用戶或用戶組查找結(jié)構(gòu)數(shù)據(jù)的方式更自然或直觀。

② 限制數(shù)據(jù)訪問，用戶只能看到有限的數(shù)據(jù)，而不是完整的表。

③ 匯總各種表中的數(shù)據(jù)，用于生成報告。

SQLite 視圖是只讀的，因此可能無法在視圖上執(zhí)行 DELETE、INSERT 或 UPDATE 語句。但是可以在視圖上創(chuàng)建一個觸發(fā)器，當嘗試 DELETE、INSERT 或 UPDATE 視圖時觸發(fā)，需要做的動作在觸發(fā)器內(nèi)容中定義。

1、創(chuàng)建視圖

CREATE [TEMP   TEMPORARY] VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

2、刪除視圖

DROP VIEW view_name;

十二、日期時和時間

SQLite 支持以下五個日期和時間函數(shù)：

上述五個日期和時間函數(shù)把時間字符串作為參數(shù)。時間字符串后跟零個或多個 modifier 修飾符。strftime() 函數(shù)也可以把格式字符串 format 作為其第一個參數(shù)。下面將為您詳細講解不同類型的時間字符串和修飾符。

時間字符串

一個時間字符串可以采用下面任何一種格式：

您可以使用 "T" 作為分隔日期和時間的文字字符。

修飾符（Modifier）

時間字符串后邊可跟著零個或多個的修飾符，這將改變有上述五個函數(shù)返回的日期和/或時間。任何上述五大功能返回時間。修飾符應從左到右使用，下面列出了可在 SQLite 中使用的修飾符：

(1)增加指定數(shù)值的日期和時間（正負數(shù)都可以）

· NNN days

· NNN hours

· NNN minutes

· NNN.NNNN seconds

· NNN months

· NNN years

(2)返回當前日期的開始

· start of month

· start of year

· start of day

(3)返回下一個星期是N的日期和時間

· weekday N

(4)返回從1970-01-01開始算起的秒數(shù)

· unixepoch

(5)返回本地時間

· localtime

（6）國際標準時間

· utc

格式化

SQLite 提供了非常方便的函數(shù) strftime() 來格式化任何日期和時間。您可以使用以下的替換來格式化日期和時間：

selectdatetime('now','localtime');--當前時間讀取本地得時間
selectdatetime('now','start of month');--本月第一天
selectdatetime('now','start of month','+1 month','-1 day');--本月最后一天
selectdatetime('now','start of year','+1 year','start of month','-1 day');--今年最后一天
SELECTjulianday(date('now','localtime')) - julianday('2018-03-20');；--計算今天到2018-03-20相差多少天

十三、函數(shù)大全

1、count 用于計算一個數(shù)據(jù)庫表中得行數(shù)

如：select count（*）from k_user

2、max 選擇某列最大值

3、min 選擇某列最小值

4、avg 計算某列平均值

5、sum 允許一個數(shù)值列計算總和

6、random 返回一個介于 -9223372036854775808 和 +9223372036854775807之間的偽隨機整數(shù)

7、abs 返回數(shù)值參數(shù)得絕對值

8、upper 吧字符串轉(zhuǎn)換為大寫字母

9、lower 把字符串轉(zhuǎn)換為小寫字母

10、length 返回字符串長度

11、sqlite_version 返回數(shù)據(jù)庫版本

12、coalesce(X,Y,……) 返回第一個非空參數(shù)的副本。若所有的參數(shù)均為NULL，返回NULL。至少2個參數(shù)。

13、ifnull(X,Y) 返回第一個非空參數(shù)的副本。若兩個參數(shù)均為NULL，返回NULL。

14、last_insert_rowid() 返回當前數(shù)據(jù)庫連接最后插入行的RowID。

15、nullif(X,Y) 當兩參數(shù)不同時返回X，否則返回NULL。

16、quote(X) 返回參數(shù)的適于插入其他SQL語句中的值。字符串會被添加單引號。

17、round(X)或round(X,Y) 將X四舍五入，保留小數(shù)點后Y位。若忽略Y參數(shù)，則默認其為0。

18、zeroblob(N) 返回一個 N 字節(jié)長、全部由 0x00 組成的 BLOB。SQLite 或以很有效的組織這些 zeroblob。它可以被用于為以后的使用預留空間。以后可以使用 incremental BLOB I/O 來寫入 BLOB 數(shù)據(jù)。

19、typeof(X) 返回表達式X的類型

20、change_count() 返回受上一語句影響的行數(shù)。（好像不能用）

21、total(X) 返回一組中所有非空值的數(shù)字和。若沒有非空行，sum()返回null而total()返回0.0。total()的返回值為浮點數(shù)，sum()可以為整數(shù)。

22、ltrim(X)

lrtrim(X,Y) 返回從 X 的左邊邊去除所有出現(xiàn)在 Y 中字符以后的字符串。如果省略 Y，則去除空格。

23、 replace(X,Y,Z) 返回一個將 X 字符串中每一個出現(xiàn) Y 的位置替換為 Z 后的字符串。它使用二進制對照序列進行比較。

24、randomblob(N) 返回一個 N 字節(jié)長的包含偽隨機字節(jié)的 BLOG。 N 應該是正整數(shù)

25、rtrim(X)

rtrim(X,Y) 返回從 X 的右邊去除所有出現(xiàn)在 Y 中字符以后的字符串。如果省略 Y，則去除空格。

26、soundex(X) 計算字符串 X的讀音編碼。如果參數(shù)為 NULL，則返回 "?000"。默認情況下 SQLite 忽略該函數(shù)。僅當在編譯時指定 -DSQLITE_SOUNDEX=1 時才有效。

27、substr(X,Y,Z)

substr(X,Y) 返回字符串 X 從第 Y 個字符開始，長度為 Z 的字符串。如果省略 Z， 將返回直到字符串結(jié)尾的字符串。 X 的第一個字符從 1 開始。如果 Y是負數(shù)，則從右邊開始數(shù)。如果 X 是 UTF-8 字符串，那么，下標值將指實際的 UTF-8 characters 字符，而不是字節(jié)。如果 X 是一個 BLOB，那么下標就是指字節(jié)。

本文講解了SQLLite相關(guān)內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注php中文網(wǎng)。

相關(guān)推薦：

Spark SQL實現(xiàn)日志離線批處理

關(guān)于MySQL-檢索數(shù)據(jù)的詳解

sql 相鄰2條記錄時間差比較

以上就是SQLLite相關(guān)內(nèi)容的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！