SQLLite的基礎知識點有哪些

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

SQLLite學習筆記

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

一、基本操作

建表：

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

刪除表：

向表中插入新數據

或者

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

查詢表

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

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

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

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

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

·        ORDER BY 不能用在子查詢中，雖然主查詢可以使用 ORDER BY。可以在子查詢中使用 GROUP BY，功能與 ORDER BY 相同。

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

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

修改表數據

刪除表數據

二、運算符

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

算數運算符

比較運算符

邏輯運算符

1、位運算符

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

三、不常用的子句

1、GLOB

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

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

GLOB的？與LIKE的_作用一致

2、HAVING

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

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

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

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

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

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

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

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

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

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

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

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

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

四、Distinct 關鍵字

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

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

五、約束

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

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

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

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

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

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

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

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

六、SQLite Joins

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

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

·          交叉連接 - CROSS JOIN

·          內連接 - INNER JOIN

·          外連接 - OUTER JOIN

1、交叉連接CROSS JOIN

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

2、內連接inner join

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

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

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

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

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

3、外連接

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

七、unions子句

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

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

2、UNIONALL 子句

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

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

八、觸發器

SQLite 觸發器（Trigger）是數據庫的回調函數，它會在指定的數據庫事件發生時自動執行/調用。以下是關于 SQLite 的觸發器（Trigger）的要點：

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

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

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

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

• BEFORE 或 AFTER 關鍵字決定何時執行觸發器動作，決定是在關聯行的插入、修改或刪除之前或者之后執行觸發器動作。

• 當觸發器相關聯的表刪除時，自動刪除觸發器（Trigger）。

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

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

示例：

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

for each row 是操作語句每影響到一行的時候就觸發一次，也就是刪了 10 行就觸發 10 次，而 for each state 一條操作語句就觸發一次，有時沒有被影響的行也執行。sqlite 只實現了 for each row 的觸發。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;

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

九、索引

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

單列索引

單列索引是一個只基于表的一個列上創建的索引。基本語法如下：

CREATE INDEX index_name

ON table_name (column_name);

唯一索引

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

CREATE UNIQUE INDEX index_name

on table_name (column_name);

組合索引

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

CREATE INDEX index_name

on table_name (column1, column2);

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

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

隱式索引

隱式索引是在創建對象時，由數據庫服務器自動創建的索引。索引自動創建為主鍵約束和唯一約束。

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

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

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

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

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

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

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

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

Indexed By

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

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

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

然而，即使指定了 "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）只不過是通過相關的名稱存儲在數據庫中的一個 SQLite 語句。視圖（View）實際上是一個以預定義的 SQLite 查詢形式存在的表的組合。

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

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

① 用戶或用戶組查找結構數據的方式更自然或直觀。

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

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

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

1、創建視圖

CREATE [TEMP | TEMPORARY] VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

2、刪除視圖

DROP VIEW view_name;

十二、日期時和時間

SQLite 支持以下五個日期和時間函數：

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

時間字符串

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

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

修飾符（Modifier）

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

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

·        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開始算起的秒數

·        unixepoch

(5)返回本地時間

·        localtime

（6）國際標準時間

·        utc

格式化

SQLite 提供了非常方便的函數 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相差多少天

十三、函數大全

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

如：select count（*）from k_user

2、max 選擇某列最大值

3、min 選擇某列最小值

4、avg 計算某列平均值

5、sum 允許一個數值列計算總和

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

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

8、upper 吧字符串轉換為大寫字母

9、lower 把字符串轉換為小寫字母

10、length 返回字符串長度

11、sqlite_version 返回數據庫版本

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

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

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

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

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

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

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

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

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

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

22、ltrim(X)

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

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

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

25、rtrim(X)

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

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

27、substr(X,Y,Z)

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

關于“SQLLite的基礎知識點有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。