SQLLite的基礎知識點(diǎn)有哪些

這篇文章將為大家詳細講解有關(guān)SQLLite的基礎知識點(diǎn)有哪些，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

SQLLite學(xué)習筆記

SQLite，是一款輕型的數據庫，是遵守ACID的管理系統，它包含在一個(gè)相對小的C庫中。它能夠支持Windows/Linux/Unix等等主流的操作系統，同時(shí)能夠跟很多程序語(yǔ)言相結合，比如 Tcl、C#、PHP、Java等。它的處理速度非?？?。

一、基本操作

建表：

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

刪除表：

向表中插入新數據

或者

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

查詢(xún)表

后面可以跟WHERE 進(jìn)行條件查詢(xún)，“*”表示查詢(xún)表中所有列，也可以這樣

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

以下是子查詢(xún)必須遵循的幾個(gè)規則：

·        子查詢(xún)必須用括號括起來(lái)。

·        子查詢(xún)在 SELECT 子句中只能有一個(gè)列，除非在主查詢(xún)中有多列，與子查詢(xún)的所選列進(jìn)行比較。

·        ORDER BY 不能用在子查詢(xún)中，雖然主查詢(xún)可以使用 ORDER BY?？梢栽谧硬樵?xún)中使用 GROUP BY，功能與 ORDER BY 相同。

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

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

修改表數據

刪除表數據

二、運算符

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

算數運算符

比較運算符

邏輯運算符

1、位運算符

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

三、不常用的子句

1、GLOB

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

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

GLOB的？與LIKE的_作用一致

2、HAVING

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

（1）HAVING 子句允許指定條件來(lái)過(guò)濾將出現在最終結果中的分組結果。

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

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

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

--舉例子說(shuō)明：查詢(xún)table表查詢(xún)每一個(gè)班級中年齡大于20，性別為男的人數

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

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

--執行where子句查找符合條件的數據；

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

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

--having子句和where子句都可以用來(lái)設定限制條件以使查詢(xún)結果滿(mǎn)足一定的條件限制。

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

四、Distinct 關(guān)鍵字

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

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

五、約束

約束是在表的數據列上強制執行的規則。這些是用來(lái)限制可以插入到表中的數據類(lèi)型。這確保了數據庫中數據的準確性和可靠性。

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

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

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

·          DEFAULT 約束：當某列沒(méi)有指定值時(shí)，為該列提供默認值。

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

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

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

六、SQLite Joins

SQLite 的 Joins 子句用于結合兩個(gè)或多個(gè)數據庫中表的記錄。JOIN 是一種通過(guò)共同值來(lái)結合兩個(gè)表中字段的手段。

SQL 定義了三種主要類(lèi)型的連接：

·          交叉連接 - CROSS JOIN

·          內連接 - INNER JOIN

·          外連接 - OUTER JOIN

1、交叉連接CROSS JOIN

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

2、內連接inner join

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

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

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

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

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

3、外連接

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

七、unions子句

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

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

2、UNIONALL 子句

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

適用于UNION 的規則同樣適用于 UNION ALL 運算符。

八、觸發(fā)器

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

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

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

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

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

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

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

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

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

示例：

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

for each row 是操作語(yǔ)句每影響到一行的時(shí)候就觸發(fā)一次，也就是刪了 10 行就觸發(fā) 10 次，而 for each state 一條操作語(yǔ)句就觸發(fā)一次，有時(shí)沒(méi)有被影響的行也執行。sqlite 只實(shí)現了 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 的時(shí)候如果新的 id>30 就把 表table_2 中和表table_1 id 相等的行一起改為新的 id

九、索引

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

單列索引

單列索引是一個(gè)只基于表的一個(gè)列上創(chuàng )建的索引?；菊Z(yǔ)法如下：

CREATE INDEX index_name

ON table_name (column_name);

唯一索引

使用唯一索引不僅是為了性能，同時(shí)也為了數據的完整性。唯一索引不允許任何重復的值插入到表中?；菊Z(yǔ)法如下：

CREATE UNIQUE INDEX index_name

on table_name (column_name);

組合索引

組合索引是基于一個(gè)表的兩個(gè)或多個(gè)列上創(chuàng )建的索引?；菊Z(yǔ)法如下：

CREATE INDEX index_name

on table_name (column1, column2);

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

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

隱式索引

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

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

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

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

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

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

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

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

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

Indexed By

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

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

"NOTINDEXED" 子句規定當訪(fǎng)問(wèn)前面的表（包括由 UNIQUE 和 PRIMARYKEY 約束創(chuàng )建的隱式索引）時(shí)，沒(méi)有使用索引。

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

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

SELECT|DELETE|UPDATE column1, column2...

INDEXED BY (index_name)

table_name

WHERE (CONDITION);

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

用來(lái)重命名已有的表的 ALTERTABLE 的基本語(yǔ)法如下：

ALTERTABLE database_name.table_name RENAME TOnew_table_name;

用來(lái)在已有的表中添加一個(gè)新的列的 ALTERTABLE 的基本語(yǔ)法如下：

ALTERTABLE database_name.table_name ADD COLUMNcolumn_def...;

十一、視圖

視圖（View）只不過(guò)是通過(guò)相關(guān)的名稱(chēng)存儲在數據庫中的一個(gè) SQLite 語(yǔ)句。視圖（View）實(shí)際上是一個(gè)以預定義的 SQLite 查詢(xún)形式存在的表的組合。

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

視圖（View）是一種虛表，允許用戶(hù)實(shí)現以下幾點(diǎn)：

① 用戶(hù)或用戶(hù)組查找結構數據的方式更自然或直觀(guān)。

② 限制數據訪(fǎng)問(wèn)，用戶(hù)只能看到有限的數據，而不是完整的表。

③ 匯總各種表中的數據，用于生成報告。

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

1、創(chuàng )建視圖

CREATE [TEMP | TEMPORARY] VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

2、刪除視圖

DROP VIEW view_name;

十二、日期時(shí)和時(shí)間

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

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

時(shí)間字符串

一個(gè)時(shí)間字符串可以采用下面任何一種格式：

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

修飾符（Modifier）

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

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

·        NNN days

·        NNN hours

·        NNN minutes

·        NNN.NNNN seconds

·        NNN months

·        NNN years

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

·        start of month

·        start of year

·        start of day

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

·        weekday N

(4)返回從1970-01-01開(kāi)始算起的秒數

·        unixepoch

(5)返回本地時(shí)間

·        localtime

（6）國際標準時(shí)間

·        utc

格式化

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

selectdatetime('now','localtime');--當前時(shí)間讀取本地得時(shí)間
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相差多少天

十三、函數大全

1、count 用于計算一個(gè)數據庫表中得行數

如：select count（*）from k_user

2、max 選擇某列最大值

3、min 選擇某列最小值

4、avg 計算某列平均值

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

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

7、abs 返回數值參數得絕對值

8、upper 吧字符串轉換為大寫(xiě)字母

9、lower 把字符串轉換為小寫(xiě)字母

10、length 返回字符串長(cháng)度

11、sqlite_version 返回數據庫版本

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

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

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

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

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

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

18、zeroblob(N)     返回一個(gè) N 字節長(cháng)、全部由 0x00 組成的 BLOB。SQLite 或以很有效的組織這些 zeroblob。它可以被用于為以后的使用預留空間。以后可以使用 incremental BLOB I/O 來(lái)寫(xiě)入 BLOB 數據。

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

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

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

22、ltrim(X)

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

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

24、randomblob(N)     返回一個(gè) N 字節長(cháng)的包含偽隨機字節的 BLOG。 N 應該是正整數

25、rtrim(X)

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

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

27、substr(X,Y,Z)

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