SQLLite相關內容

jacklove原創: 2018-06-14 16:25:302339瀏覽

嗯。。。前段時間整理的常用知識：

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，則：

算數運算子

#加法- 將運算子兩邊的值相加a * b ##/左運算元除以右邊運算元

運算子	描述	實例

a b ###將會得到30########################-######	減法- 左運算元減去右運算元	a - b 將得到-10
*	*
	把運算子兩邊的值相乘	將會得到200
除法-	#b / a	將得到2

%####### ############取模- ###左運算元除以右運算元後得到的餘數###############b % a 將 give 0##################

比較運算子

#檢查兩個運算元的值是否相等，若相等則條件為真。 (a == b) =#檢查兩個運算元的值是否相等，若相等則條件為真。 (a = b) !=#檢查兩個運算元的值是否相等，若不相等則條件為真。 (a != b) a8093152e673feb7aba1828c43532094 檢查兩個動作數的值是否相等，若不相等則條件為真。 (a a8093152e673feb7aba1828c43532094 b) > 檢查左運算元的值是否大於右操作數的值，如果是則條件為真。 (a > b) 705718e1b6d84787630f1edb19b871c8=#檢查左運算元的值是否大於等於右操作數的值，如果是則條件為真。 (a >= b) 19956b10b57fccfa2ed5c7c7c44955ff#######

運算子	描述	實例
==
		不為真。
		不為真。
		為真。
		為真。
		#不為真。
		#不為真。
檢查左運算元的值是否不大於右運算元的值，如果是則條件為真。	(a !> b) #為真。

邏輯運算子

運算子	描述
AND	#AND 運算符允許在一個SQL 語句的WHERE 子句中的多個條件的存在。
BETWEEN	#BETWEEN 運算子用於在給定最小值和最大值範圍內的一系列值中搜尋值。
EXISTS	EXISTS 運算子用於在滿足一定條件的指定表中搜尋行的存在。
IN	IN #運算符用於將某個值與一系列指定清單的值進行比較。
NOT IN	IN 運算子的對立面，用來把某個值與不在一系列指定清單的值做比較。
LIKE	LIKE 運算符用於將某個值與使用通配符運算符的相似值進行比較。
GLOB	#GLOB 運算符用於將某個值與使用通配符運算符的相似值進行比較。 GLOB 與 LIKE 不同之處在於，它是大小寫敏感的。
NOT	#NOT 運算子是所使用的邏輯運算子的對立面。例如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定運算符。
OR	#OR 運算子用於結合一個SQL 語句的WHERE 子句中的多個條件。
IS NULL	#NULL 運算符用於把某個值與NULL 值做比較。
IS	#IS 運算子與= 相似。
IS NOT	#IS NOT 運算符與!= 相似。
\|\|	#連接兩個不同的字串，得到一個新的字串。
UNIQUE #	UNIQUE 運算子搜尋指定表中的每一行，確保唯一性（無重複）。

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通常在GROUP##BY子句中使用。
(5)如果不使用GROUPBY子句，則HAVING的行為與WHERE子句一樣。

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

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

# selectCOUNT(*)as'>20歲人數',classid##fromTable1 wheresex='男'groupbyclassid,age## havingage>20--

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

執行where

子句尋找符合條件的資料；--

使用group

by子句將資料分組；對group by子句形成的群組執行聚集函數計算每一組的值；最後用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)不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、816ff14aff4448eec8a656c2ab5ef5de、!<和a8093152e673feb7aba1828c43532094。

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 支持以下五个日期和时间函数：

#運算子	##描述	#實例
&	如果同時存在於兩個運算元中，二進位AND 運算子複製一位到結果中。	(A & B) 將得到12，即為0000 1100
\|	如果存在於任一運算元中，二進位OR 運算子複製一位到結果中。	(A \| B) 將得到61，即為0011 1101
~	二進位補碼運算子是一元運算符，具有"翻轉"位元效應，即0變成1，1變成0。	(~A ) 將得到 -61，即為 1100 0011，一個有符號二進位數的補碼形式。
9a680553ed1198a74854ceac60675a42>	#二進位右移運算子。左操作數的值向右移動右操作數所指定的位數。	A >> 2 #將得到15，即為0000 1111

序号	函数	实例
1	date(timestring, modifier, modifier, ...)	以 YYYY-MM-DD 格式返回日期。
2	time(timestring, modifier, modifier, ...)	以 HH:MM:SS 格式返回时间。
3	datetime(timestring, modifier, modifier, ...)	以 YYYY-MM-DD HH:MM:SS 格式返回。
4	julianday(timestring, modifier, modifier, ...)	這將返回從格林尼治時間的公元前 4714 年 11 月 24 日正午算起的天數。
5	#strftime(format, timestring, modifier , modifier, ...)	這將根據第一個參數指定的格式字串傳回格式化的日期。具體格式請見下邊講解。

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

時間字串

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

#YYY-MM-DD

##序號	#時間字串	實例
#1	YYYY-MM-DD	#2010-12-30
#2	YYYY-MM-DD HH:MM	2010-12-30 12:10
#3
YYYY-MM-DD HH:MM:SS.SSS	2010-12-30 12:10:04.100	4
#MM-DD-YYYY HH:MM	30-12-2010 12:10
HH:MM	#12:10	6
T HH:MM	2010-12-30 12:10	7
HH:MM:SS	#12:10:01	8
#YYYYMMDD HHMMSS	#20101230 121001	9

########################################### ####now###################2013-05-07##################

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

修飾符（Modifier）

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

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

· NNN days

NNN days

NNN hours·

NNN # NNN.NNNN seconds

· NNN months

· # ##· #

##(2)

返回目前日期的開始

#start of month

#·

(3)返回下一個星期是##N

的日期和時間· # weekday N

(4)傳回從1970-01-01

#開始算起的秒數· #unixepoch

(5)

##(5)

· localtime

(#6

###################################################################### ####· ######utc######

格式化

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

#分，從 1970-01-01 秒，#一週的第幾天，一年中的第幾週，#年，

取代	描述
%d	#一月中的第幾天，01-31
%f	有小數部分的秒，SS.SSS
##%H	#時，00-23
%j	#一年中的第幾天，001 -366
%J	#儒略日數，DDDD.DDDD
#%m	月，00-12
.%M
00-59	%s
算起的秒數	%S
##00-59	%w
0-6 (0 is Sunday)	#%W
01-53	%Y
YYYY	%%

% symbol###################

selectdatetime(&#39;now&#39;,&#39;localtime&#39;);--当前时间读取本地得时间
selectdatetime(&#39;now&#39;,&#39;start of month&#39;);--本月第一天
selectdatetime(&#39;now&#39;,&#39;start of month&#39;,&#39;+1 month&#39;,&#39;-1 day&#39;);--本月最后一天
selectdatetime(&#39;now&#39;,&#39;start of year&#39;,&#39;+1 year&#39;,&#39;start of month&#39;,&#39;-1 day&#39;);--今年最后一天
SELECTjulianday(date(&#39;now&#39;,&#39;localtime&#39;)) - julianday(&#39;2018-03-20&#39;);；--计算今天到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相關內容，更多相關內容請關注php中文網。