Sqlite簡介01

資訊農夫 (山海醉客)
~
SQLite 官網 ;;; SQLite wikipedia 維基百科 (MySql wiki ;;; MsSql wiki ;;; PostgreSQL wiki ) ;;; SQLiteStudio ;;; SqliteSPY;;;  SQLite GUI工具 ;;; SQLiterManager(FireFox套件)
System.Data.SQLite ;;; ADO.NET 2.0 Provider for SQLite ;;;
SQLite簡體中文站 ;;; SQLite的SQL语法(簡體中文) ;;; 如何高效使用SQLite .net (C#) ;;;
SQLite 一般的 SQL-92 語法都能用，不需建立一個資料庫系統，使用時在編譯程式的時候將 SQLite 程式庫一起編入就可用。以單一檔案存於磁碟，不需安裝資料庫伺服器軟體。SQLite 是個很小的 C 語言程式庫，本身包含資料庫引擎，可嵌入至其他程式中。主要特性如：

• public domain，cross-platform ， 使用unicode，可免費自由使用。
• 支援大小至2 terabytes (2^41 bytes)。
• 簡單易用的API。
• 支援ACID (Atomic, Consistent, Isolated, Durable) transaction。
• serverless
• 不進行data type檢查

SQLite Version 3的API全部重新設計，由第二版的15個函數增加到88個函數。這些函數包括支持UTF-8和UTF-16編碼的功能函數。
.■■■ -More SQLite related projects and samples on CodePlex http://www.codeplex.com/site/search?query=sqlite&ac=8
.■■■ - Homepage, DB diagram, etc.. http://chinookdatabase.codeplex.com/
.■■■ - Northwind.Sqlite3.sql
.■■■ -
.■■■ -
.■■■ -
~
SQLite官方文檔提及的數據類型只有5種:
NULL ===== The value is a NULL value
INTEGER == The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
NUMERIC == The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT ====== The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB ====== The value is a blob of data, stored exactly as it was input.
你可能覺得太少了, 事實上 SQLite 是弱類型, 建表時定義的數據類型只是個標識, 對數據列的類型並沒有實質性的作用.你也可以把 TEXT 類型定義成更複雜的 NVARCHAR(50) 之類的, 這只是為了未來數據遷移的方便
~
SQLite 是無資料類型“ Typeless ”，你依然可以宣告資料類型，但資料庫內部都當成 ASCII 文字處理， SQLite 3 再從 TEXT 分出 INTEGER 、 REAL 和 BLOB 。
~
第二版把所有列的值都存儲成ASCII文本格式。第三版則可以把數據存儲成整數和實數,還可以存儲BLOB數據.
~
列之間的親和性
為了最大限度的增加SQLite數據庫和其他數據庫的兼容性,SQLite支持列的"類型親和性". 列的親和性是為該列所存儲的數據建議一個類型.我們要注意是建議而不是強迫.在理論上來講,任何列依然是可以存儲任何類型的數據的. 只是針對某些列,如果給建議類型的話,數據庫將按所建議的類型儲存.這個被優先使用的數據類型則被稱為"親和類型".
在SQLite3.0版中,數據庫中的每一列都被定義為以下親和類型中的一種:
____ * 文字(本) ;; * 數字的 ;; * 整數 ;; * 無
一個具有類型親和性的列按照無類型,文字(本),或BLOB存儲所有的數據.如果數字數據被插入一個具有文字(本)類型親和性的列,在儲存之前數字將被轉換成文字(本).
一個具有數字類型親和性的列也許使用所有的五個儲存類型儲存值.當文字(本)數據被插入一個數字列時,在存儲之前,數據庫將嘗試著把文字(本)轉換成整數或實數.如果能成功轉換的話,值將按實數的類型被存儲. 如果不能成功轉換的話,值則只能按文字(本)類型存儲了,而不會被轉換成無類型或BLOB類型來存儲.
一個具有整數親和力的列在轉換方面和具有數字親和力的列是一樣的,但也有些區別 ,比如沒有浮動量的實值(文字(本)值轉換的值)被插入具有整數親和力的列時,它將被轉換成整數並按整數類型存儲.一個具有無類型親和力的列不會優先選擇使用哪個類型.在數據被輸入前它不會強迫數據轉換類型.
~
列的親和性的決定, 一個列的親和類型是由該列所宣稱的類型決定的.遵守以下規則:

• 如果數據類型包括字符串"INT"那麼它被定義成具有整數親和性.
• 如果列中的數據類型包括以下任何的字符串 "CHAR", "CLOB", or "TEXT" 那麼這個列則具有文字(本)親和性.要注意VARCHAR類型包括字符串"CHAR"因此也具有文字(本)類型親和性.
• 如果一個列的數據類型包括字符串"BLOB"或者如果數據類型被具體化了,那麼這個列具有無類型親和性.
• 否則就具有數字類型親和性.

如果表格使用If "CREATE TABLE AS SELECT..."語句生成的,那麼所有的列則都沒有具體的數據類型,則沒有類型親和性.
~
列的親和性的例子
____ CREATE TABLE t1( t TEXT, nu NUMERIC, i INTEGER, no BLOB );
~
Autoincrement(自動編號) : (官網的說明)
SQLite 預設會有個 rowid , 但不能存取, 加入一個新的
____ `id` INTEGER PRIMARY KEY AUTOINCREMENT
rowid 就會不見(隱藏!?)
~
在 SQLite 中要用 autoincrement 有兩種方法:
方法一:
____ CREATE TABLE table_name ( id INTEGER PRIMARY KEY, name TEXT);
方法二:
____ CREATE TABLE table_name ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);
兩者差別於方法一在 INSERT 時，是從 table 裡挑下一個 autoincrement index (最大值 + 1)，方法二則會在 sqlite_sequence table 裡紀錄各個 table 目前的 autoincrement index。所以要達到 TRUNCATE 效果時，後者還要多一個指令把 sqlite_sequence 裡的值刪掉。
____ DELETE FROM table_name;
____ DELETE FROM sqlite_sequence WHERE name = table_name;
~
System.Data.SQLite DataTypeSQLite :
SQLite 是弱類型數據庫, 但對於 System.Data.SQLite (ADO.NET 2.0 Provider for SQLite) 來說, 為了更好的與 Ado.NET 2.0 互操作, 作者仍然建議在創建表的時候就指定類型, 以方便程序進行類型轉換, 下面是類型關鍵字列表:
DbType.Boolean
____ BIT ;; YESNO ;; LOGICAL ;; BOOL
DbType.Byte
____ TINYINT
DbType.Int16
____ SMALLINT
DbType.Int32
____ INT
DbType.Int64
____ INTEGER ;; BIGINT ;; COUNTER ;; AUTOINCREMENT ;; IDENTITY ;; LONG
DbType.Single
____ REAL
DbType.Double
____ DOUBLE FLOAT
DbType.Decimal
____ NUMERIC ;; DECIMAL ;; MONEY ;; CURRENCY
DbType.String
____ CHAR ;; NCHAR ;; VARCHAR ;; NVARCHAR ;; TEXT ;; NTEXT ;; LONGTEXT
____ LONGCHAR ;; LONGVARCHAR ;; STRING ;; MEMO ;; NOTE
DbType.Binary
____ BLOB ;; BINARY ;; VARBINARY ;; IMAGE ;; GENERAL ;; OLEOBJECT
DbType.Guid
____ GUID ;; UNIQUEIDENTIFIER
DbType.DateTime
____ TIME ;; DATE ;; DATETIME ;; SMALLDATE ;; SMALLDATETIME ;; TIMESTAMP
有兩點需要注意:
1.)SQLite 主鍵默認的 INTEGER 類型對應的是 Int64, 而 INT 對應的是 Int32.
2.)日期和時間類型最終存儲為字符串, 所以格式必須符合ISO8601標準:

• "yyyy-MM-dd HH:mm:ss"
• "yyyyMMddHHmmss"
• "yyyyMMddTHHmmssfffffff"
• "yyyy-MM-dd"
• "yy-MM-dd"
• "yyyyMMdd"
• "HH:mm:ss"
• "THHmmss"

SQLite內建函數表: (官網資料)
算術函數
abs(X) 返回給定數字表達式的絕對值。
max(X,Y[,...]) 返回表達式的最大值。
min(X,Y[,...]) 返回表達式的最小值。
random(*) 返回隨機數。
round(X[,Y]) 返回數字表達式並四捨五入為指定的長度或精度。
字符處理函數
length(X) 返回給定字符串表達式的字符個數。
lower(X) 將大寫字符數據轉換為小寫字符數據後返回字符表達式。
upper(X) 返回將小寫字符數據轉換為大寫的字符表達式。
substr(X,Y,Z) 返回表達式的一部分。
randstr()
quote(A)
like(A,B) 確定給定的字符串是否與指定的模式匹配。
glob(A,B) 條件判斷函數
coalesce(X,Y[,...])
ifnull(X,Y)
nullif(X,Y)
集合函數
avg(X) 返回組中值的平均值。
count(X) 返回組中項目的數量。
max(X) 返回組中值的最大值。
min(X) 返回組中值的最小值。
sum(X) 返回表達式中所有值的和。
其他函數
typeof(X) 返回數據的類型。
last_insert_rowid() 返回最後插入的數據的ID。
sqlite_version(*) 返回SQLite的版本。
change_count() 返回受上一語句影響的行數。
last_statement_change_count()
~
參考資料:
SQLite 簡介 (1)
SQLite 簡介 (2)
SQLite入門與分析(一)......(xx) 簡體
SQLite第三版中的數據類型(簡體)
System.Data.SQLite DataType