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

嗯。。。前段時(shí)間整理的常用知識：

sqlLite學(xué)習(xí)筆記

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

一、基本操作

建表：

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

刪除表：

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

或者

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

查詢表

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

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

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

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

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

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

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

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

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

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

二、運(yùn)算符

假設(shè)變量 a=10，變量b=20，則：

算數(shù)運(yùn)算符

比較運(yùn)算符

邏輯運(yùn)算符

1、位運(yùn)算符

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

三、不常用的子句

1、GLOB

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

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

?????????? GLOB的？與LIKE的_作用一致

2、HAVING

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

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

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

(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

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

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

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

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

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

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

四、Distinct 關(guān)鍵字

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

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

五、約束

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

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

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

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

·??????????default 約束：當(dāng)某列沒有指定值時(shí)，為該列提供默認(rèn)值。

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

·??????????PRIMARY Key 約束：唯一標(biāo)識數(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í)必須謹(jǐn)慎，只在適當(dāng)?shù)臅r(shí)候使用它們。

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

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

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

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

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

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

3、外連接

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

七、unions子句

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

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

2、UNIONALL 子句

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

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

八、觸發(fā)器

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

• SQLite 的觸發(fā)器（Trigger）可以指定在特定的數(shù)據(jù)庫表發(fā)生 delete、INSERT 或 UPDATE 時(shí)觸發(fā)，或在一個或多個指定表的列發(fā)生更新時(shí)觸發(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)鍵字決定何時(shí)執(zhí)行觸發(fā)器動作，決定是在關(guān)聯(lián)行的插入、修改或刪除之前或者之后執(zhí)行觸發(fā)器動作。

• 當(dāng)觸發(fā)器相關(guān)聯(lián)的表刪除時(shí)，自動刪除觸發(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 是操作語句每影響到一行的時(shí)候就觸發(fā)一次，也就是刪了 10 行就觸發(fā) 10 次，而 for each state 一條操作語句就觸發(fā)一次，有時(shí)沒有被影響的行也執(zhí)行。sqlite 只實(shí)現(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 的時(shí)候如果新的 id>30 就把 表table_2 中和表table_1 id 相等的行一起改為新的 id

九、索引

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

單列索引

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

CREATE?INDEX?index_name

ON?table_name?(column_name);

唯一索引

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

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í)，由數(shù)據(jù)庫服務(wù)器自動創(chuàng)建的索引。索引自動創(chuàng)建為主鍵約束和唯一約束。

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

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

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

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

·????????索引不應(yīng)該使用在較小的表上。

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

·????????索引不應(yīng)該使用在含有大量的 NULL 值的列上。

·????????索引不應(yīng)該使用在頻繁操作的列上。

Indexed By

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

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

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

然而，即使指定了 “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）實(shí)際上是一個以預(yù)定義的 SQLite 查詢形式存在的表的組合。

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

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

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

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

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

SQLite 視圖是只讀的，因此可能無法在視圖上執(zhí)行 DELETE、INSERT 或 UPDATE 語句。但是可以在視圖上創(chuàng)建一個觸發(fā)器，當(dāng)嘗試 DELETE、INSERT 或 UPDATE 視圖時(shí)觸發(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;

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

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

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

時(shí)間字符串

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

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

修飾符（Modifier）

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

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

·????????NNN days

·????????NNN hours

·????????NNN minutes

·????????NNN.NNNN seconds

·????????NNN months

·????????NNN years

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

·????????start of month

·????????start of year

·????????start of day

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

·????????weekday N

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

·????????unixepoch

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

·????????localtime

（6）國際標(biāo)準(zhǔn)時(shí)間

·????????utc

格式化

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

selectdatetime('now','localtime');--當(dāng)前時(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');；--計(jì)算今天到2018-03-20相差多少天

十三、函數(shù)大全

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

如：select count（*）from k_user

2、max 選擇某列最大值

3、min 選擇某列最小值

4、avg 計(jì)算某列平均值

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

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

7、abs 返回?cái)?shù)值參數(shù)得絕對值

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

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

10、Length 返回字符串長度

11、sqlite_version 返回?cái)?shù)據(jù)庫版本

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

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

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

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

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

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

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

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

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

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

22、ltrim(X)

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

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

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

25、rtrim(X)

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

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

27、substr(X,Y,Z)

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

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

相關(guān)推薦：

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

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

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