SQLite簡單教程
http://www.cnblogs.com/wildfish/archive/2006/03/25/358532.html
最早使用SQLite是因為sql2005實在是重量了,我的老機器跑的咯吱咯吱了。
而且,Access用得不習慣--指的是.Net的訪問。
後來Cnblogs搜尋了一下,覺得SQLite不錯,因為我的架構本身沒有使用預存程序。
廢話不多說,言歸正傳。
1。從www.sqlite.org下載SQLite 3.3.4的版本
為了方便,我把它解壓了,就一個SQLite3.exe,放入Windows目錄下。
Cmd 進入命令列
1)
建立資料庫檔案:
>SQLite3 d:\test.db 斷行符號
就產生了一個test.db在d盤。
這樣同時也SQLite3掛上了這個test.db
2)
用.help可以看看有什麼命令
>.help 斷行符號即可
3)可以在這裡直接輸入SQL語句建立表格 用;結束,然後斷行符號就可以看到了
4)看看有建立了多少表
>.tables
5)看錶結構
>.schema 表名
6)看看目前掛的資料庫
>.database
7)如果要把查詢輸出到檔案
>.output 檔案名稱
> 查詢語句;
查詢結果就輸出到了檔案c:\query.txt
把查詢結果用螢幕輸出
>.output stdout
8)把表結構輸出,同時索引也會輸出
.dump 表名
9)退出
>.exit 或者.quit
2。從http://sqlite.phxsoftware.com/下載Ado.net驅動。
下載了安裝,在安裝目錄中存在System.Data.SQLite.dll
我們只需要拷貝這個檔案到引用目錄,並添加引用即可對SQLite資料庫操作了
所有的Ado.net對象都是以SQLite開頭的,比如SQLiteConnection
串連串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--應用在和應用程式或者.net能夠自動找到的目錄
剩下的就很簡單了~~
3。SQL文法
由於以前用SQLServer或者ISeries,所以DDL的文法很汗顏
1)建立一個單個Primary Key的table
CREATE TABLE [Admin] (
[UserName] [nvarchar] (20) PRIMARY KEY NOT NULL ,
[Password] [nvarchar] (50) NOT NULL ,
[Rank] [smallint] NOT NULL ,
[MailServer] [nvarchar] (50) NOT NULL ,
[MailUser] [nvarchar] (50) NOT NULL ,
[MailPassword] [nvarchar] (50) NOT NULL ,
[Mail] [nvarchar] (50) NOT NULL
) ;
2)建立一個多個Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext] NOT NULL ,
[CdString2] [ntext] NOT NULL ,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;
3)建立索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
還可以視圖等等。
4.還有很有用的SQL
Select * from Sqlite_master
Select datetime('now')
Select date('now')
Select time('now')
以及很多函數,具體可以參考SQLite的wiki.
oh,還有就是看到有人說,好像成批插入的時候,啟動事務,比不啟動事務快n倍
還有就是盡量使用參數化的SQL,估計和商用DB一樣能夠自動Prepare.
===========
sqlite可以在shell/dos command底下直接執行命令:
sqlite3 film.db "select * from film;" (我在執行的時候發現斷行符號後要輸入go)
輸出 HTML 表格:
sqlite3 -html film.db "select * from film;"
將資料庫「倒出來」:
sqlite3 film.db ".dump" > output.sql
利用輸出的資料,建立一個一模一樣的資料庫(加上以上指令,就是標準的SQLDatabase Backup了):
sqlite3 film.db < output.sql
在大量插入資料時,你可能會需要先打這個指令:
begin;
插入完資料後要記得打這個指令,資料才會寫進資料庫中:
commit;
;
SQLite——只要3分鐘,你就可以在.NET上建立和運行它 -------------轉載
SQLite是一個開來源資料庫,現在已變得越來越流行,它的體積很小,被廣泛應用於各種不同類型的應用中。
什麼是SQLite?
SQLite的官方網站上是這樣定義SQLite的:
SQLite是一個軟體庫,用於實現自包含、非服務式、零配置、事務化的SQL資料庫引擎。
SQLite 是一個嵌入式SQL資料庫引擎,與其它大多數SQL資料庫不同的是,SQLite沒有獨立的服務進程。SQLite直接讀寫原始的磁碟檔案,一個擁有多個 表、索引、觸發器和視圖的完整SQL資料庫就包含在一個獨立的磁碟檔案中。資料庫檔案的格式是跨平台的,你可以在32位和64位系統之間、甚至在Big-Endian和Little-Endian(譯者註:這是兩種不同的位元組排序方式,Big-Endian是指一個word中的高位Byte是放在記憶體word地區的低地址處,而Little-Endian則與之相反)兩種不同的架構間自由地拷貝資料庫,這一特性讓SQLite成為應用檔案格式的一種流行選擇。SQLite不能替代Oracle,但可以考慮作為fopen()的替代方法。
SQLite已經是世界上布署得最廣泛的SQL資料庫引擎,被用在無以計數的案頭電腦應用中,還有消費電子裝置中,如行動電話、掌上型電腦和MP3播放器等。SQLite的源碼就放在公有領域(即WikiPedia的public domain)中。
SQLite最早是應用在Linux和OSX平台上的,但對資料庫需求較少的Windows應用而言,它是替代SQL Express和Access資料庫運行於.NET之上的一個可行且實用的選擇。
有一篇來自開發人員Mike Duncan的文章,給出了一個在3分鐘內就可將SQLite安裝到.NET上的指南。這個指南非常有用,讀完它你就可以使用一個輕量級的資料庫來處理你丟給它的許多任務。
3分鐘的指南
指南是從第一次下載SQLite開始的:
儘管你可以通過SQLite下載頁獲得Windows的通用庫,但我還是打算建議你從sourceforge擷取SQLite的ADO.NET 2.0資料提供者,我並不是說它是最高效的版本(它有一個ADO封裝層以及附帶的無用功能),但它確實是一個非常容易上手的版本,可能值得長期使用。
找出DLL:
將找到的DLL(System.Data.SQLite.DLL)拷貝到你的項目中,並添加引用。
下載和安裝一個SQLite GUI工具,SQLiteMan有一個非常出色的windows版本,指南上是這樣說的:
我一直在使用的工具名為“SQLite Administrator”(很合適的名字,它是免費的!)有一個“甜點”——有著一個和Query Analyzer很像的介面。如果你有興趣的話,可以從這裡http://www.sqlite.org/cvstrac/wiki?p=ManagementTools找到一個很大的SQLite GUI用戶端列表。
指南的最後一步就是建立一個SQLite資料庫:
通過GUI,建立一個資料庫並隨意建立一個測試表,就會出現一個以.s3db為尾綴的單獨檔案。
一旦System.Data.SQLite.dll被引用為.NET項目的一部分,那就可以像在你的應用頂部寫using System.Data.SQLite那樣容易地使用它。通過使用ADO.NET封裝層,一個參數化的查詢看上去會像是這樣:
string lookupValue;
using (SQLiteCommand cmd = cnn.CreateCommand())
{
for (int i = 0; i < 100; i++)
{
lookupValue = getSomeLookupValue(i);
cmd.CommandText = @"UPDATE [Foo] SET [Value] = [Value] + 1
WHERE [Customer] LIKE '" + lookupValue + "'";
cmd.ExecuteNonQuery();
}
}
資料提供者
SQLite已經實實在在地影響到.NET的開發,已經有很多資料提供器被用於流行的對像關係映射(O/RM, 即Object-Relational Mapper)架構中。
· SQLite NHibernate Provider
· SQLite Subsonic Provider
· SQLite LINQ Provider
LINQ提供器允許.NET 3.5的開發人員們利用新LINQ架構的優勢,並以SQLite作為後端資料存放區。
SQLite可以作為替代Access或SQL Express讓資料庫應用快速建立和運行起來的一個不錯選擇,而且因為資料庫還可以同時在Linux和Mac OSX平台上使用,所以建立一個可以跨平台使用的資料庫應用很容易。
查看英文原文:Up and Running with SQLite on .NET in 3 Minutes
SQLite外鍵的實現
http://www.sqlite.com.cn/MySqlite/6/403.Html
SQLite現在的版本還不支援外鍵功能,雖然外鍵約束會被解析,但執行的時候被忽略。但我們可以手動實現外鍵,實現的原理就是觸發器。下面是我的實現方法。主要是針對一個例子:
先看下面兩個表。
CREATE TABLE PLU (PluID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Property text,
Price double NOT NULL,
Left integer NOT NULL,
Department text,
Other text);
CREATE TABLE PluSuit (SuitID integer NOT NULL PRIMARY KEY,
Price double NOT NULL,
Property text,
Name text NOT NULL,
PluID integer NOT NULL CONSTRAINT fk_plu_id REFERENCES PLU(PluID) ON DELETE CASCADE,
Numbers integer NOT NULL)
這樣就為PluSuit表建立對PLU表的外鍵約束,這樣就可以實現CORE2資料需求中的要求,問題是SQLite不執行這個約束,所以這樣建立以後,我們還要再建立三個觸發器,INSERT,UPDATE,DELETE觸發器:
BEFORE INSERT ON PluSuit
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "PluSuit" violates foreign key constraint "fk_plu_id"')
WHERE (SELECT PluID FROM PLU WHERE PluID = NEW.PluID) IS NULL;
END;
BEFORE UPDATE ON PluSuit
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on table "PluSuit" violates foreign key constraint "fk_plu_id"')
WHERE (SELECT PluID FROM PLU WHERE PluID = NEW.PluID) IS NULL;
END;
CREATE TRIGGER fkd_plusuit_pluid
BEFORE DELETE ON PLU
FOR EACH ROW BEGIN
DELETE from PluSuit WHERE PluID = OLD.PluID;
END;
下面我們分別來作三個實驗:
一、插入實驗
首先我們在PLU裡面插入一個資料(一雙anta運動鞋的資訊):
insert into PLU values(1,'anta','sport',299,100,'sales','ok');
insert into PLU values(3,'nike','sport',699,200,'sales','ok');
然後我們開始在PluSuit裡面插入一個資料(兩雙一起打折賣):
insert into PluSuit values(100,350,'old','anta',1,2);成功了
insert into PluSuit values(100,350,'old','anta',2,2);失敗,得到正確的錯誤資訊
更新實驗
update PluSuit set PluID=2 where SuitID=100;失敗,得到正確的錯誤資訊
update PluSuit set PluID=3 where SuitID=100;成功
刪除實驗
delete from PLU where PluID=1;
查看PluSuit中資料被正確刪除。
實驗結果,觸發器的實現完全正確。
Using SQLite with Enterprise Library 3.1
http://www.eggheadcafe.com/tutorials/aspnet/ba7ea7a0-4e67-4863-941c-f4dadf9b7127/aspnet-using-sqlite-wit.aspx
Information and download of System.Data.SqLite:
http://sqlite.phxsoftware.com/
Enterprise Library 3.1 (May 2007):
http://www.microsoft.com/downloads/details.aspx?FamilyID=4c557c63-708f-4280-8f0c-637481c31718&displaylang=en
EntLibContrib Project at codeplex:
http://www.codeplex.com/entlibcontrib
I've extolled the virtues of the amazing SQLite database engine a number of times here (just use our Search widget with "SQLITE") so I won't go into much detail about it. Here are a few bullet points, though:
· Complete ADO.NET 2.0 Implementation
· Supports the Full and Compact .NET Framework as well as native C/C++
· Completely portable database files
· Incredibly fast, faster than most every other embedded database, including Sql Server Mobile
· Encryption Support
· Full Text Search
· Visual Studio 2005 Design-Time Support
· Single file redistributable under 500kb -- nothing to "install"!
· Extensive SQL support
· User-Defined Functions & Collating Sequences, Triggers
· Full Source Included. 100% Free
You can thank my friend Robert Simpson for all this effort and contribution. In sum, SQLite is very fast, requires no configuration or installation (only that the assembly be present), and implements most all of the SQL 92 spec. With the ADO.NET Provider, you are dealing with datareaders, DataSets and all the familiar programming tools you are used to. The SQL syntax is a bit of a variant from T-SQL, but the differences are quite minor.
At Codeplex, there is a Enterprise Library "contrib" project with a number of enhancements written by users like you and me. One in particular that caught my attention was the SQLite provider, written by Ken Scott http://freqken.net/.
The solution here shows how to wire up your Entlib configuration to use the ADO.NET 2.0 System.Data.SqLite engine with Enterprise Library 3.1
First you will need the required EntLib and Contrib assemblies:
Microsoft.Practices.EnterpriseLibrary.Data
Microsoft.Practices.EnterpriseLibrary.Common
EnterpriseLibrary.Contrib.Data.SqLite
EnterpriseLibrary.Contrib.Data
Then, you'll set up your configuration, which can be done with the EntLib Configuration Add-on from within Visual Studio:
My sample config looks like this:
<?xml version="1.0"?>
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
<dataConfiguration defaultDatabase="ConnectionString">
<providerMappings>
<add databaseType="Microsoft.Practices.EnterpriseLibrary.Contrib.Data.SqLite, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="System.Data.SqLite" />
</providerMappings>
</dataConfiguration>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=dbpath;New=True;UTF8Encoding=True;Version=3"
providerName="System.Data.SQLite" />
</connectionStrings>
<appSettings>
<add key="Setting" value="" />
</appSettings>
<system.web>
<compilation debug="true" />
<authentication mode="Windows" />
</system.web>
</configuration>
Finally, I've got two sample classes that I ripped right out of the test fixtures from the Contrib project, and massaged to fit this demo:
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
namespace SQLiteEntLib
{
public class TestConfigurationSource
{
public static DictionaryConfigurationSource CreateConfigurationSource()
{
DictionaryConfigurationSource source = new DictionaryConfigurationSource();
DatabaseSettings settings = new DatabaseSettings();
settings.DefaultDatabase = "Quotes";
ConnectionStringsSection section = new ConnectionStringsSection();
string dbPath = System.Web.HttpContext.Current.Server.MapPath("quotes.db3");
section.ConnectionStrings.Add(new ConnectionStringSettings("ConnectionString", @"Data Source=" + dbPath , "System.Data.SQLite"));
source.Add(DatabaseSettings.SectionName, settings);
source.Add("connectionStrings", section);
return source;
}
}
}
using System;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace SQLiteEntLib
{
public class SQLiteExecuteDatasetHelper
{
private const string queryString = "Select * from quotes where authorname like 'a%'";
private Database db;
public DataSet ExecuteDataSetFromSqlString()
{
DatabaseProviderFactory factory = new DatabaseProviderFactory(TestConfigurationSource.CreateConfigurationSource());
db = factory.Create("ConnectionString");
DataSet dataSet = db.ExecuteDataSet(CommandType.Text, queryString);
return dataSet;
}
}
}
And that's it! The Database object has most of the same familiar methods that are present for all the other Providers. The only thing you don't have is stored procedures.
However, based on all the "stored procs are evil" diatribe I've listened to and read, that could be a good thing!
Note that I am doing a string Replace on the "dbPath" string with Server.MapPath and the name of the database file to make this more portable for ASP.NET.
The download has a small SQLite database of quotations by famous authors (a subset, for size) and the ASP.NET app displays the results of a query in a GridView. You can run this without downloading anything as it already contains all the required assemblies. Just unzip, load into Visual Studio 2005, and press the green button!
Of course, I recommend that you download the three projects and sources from the links at the very top of this article and install each so you can look at code and learn more.
工具
SQLite Administrator
http://download.orbmu2k.de/download.php?id=19
如果你使用的是sqllite3,那麼你應該使用一下SqliteAdmin的一個”更新到sqlite3”的功能。
SQLite Expert
http://www.sqliteexpert.com/download.html
使用中發現SqliteAdmin做出來的中文資料,在.net訪問時候是亂碼(雖然用sqlitecommand查出來的時候沒有問題),後來在網上檢索發現如下:
關於SQLite亂碼問題解決辦法(介面亂碼、插入資料後管理工查詢亂碼)
1、更換建立資料庫工具,由原來的 SQLite Administrator 改成 SQLite Expert Professional,建立資料庫時選擇編碼為UTF8。
2、"Data Source=DBName;New=False;Compress=True;Synchronous=Off;UTF8Encoding=True;Version=3" 同樣注意UTF8Encoding 標
識改為True。
3、SQLLite Expert ,Tools菜單、Options、General項中、Encoding組,選中UTF-8。
SQLite不支援的SQL文法總結
http://hi.baidu.com/83925com/blog/item/7f430b366e305bd7a3cc2b90.html
1 TOP
這是一個大家經常問到的問題,例如在SQLSERVER中可以使用如下語句來取得記錄集中的前十條記錄:
SELECT TOP 10 * FROM [index] ORDER BY indexid DESC;
但是這條SQL語句在SQLite中是無法執行的,應該改為:
SELECT * FROM [index] ORDER BY indexid DESC limit 0,10;
其中limit 0,10表示從第0條記錄開始,往後一共讀取10條
2 建立視圖(Create View)
SQLite在建立多表視圖的時候有一個BUG,問題如下:
CREATE VIEW watch_single AS SELECT DISTINCTwatch_item.[watchid],watch_item.[itemid] FROM watch_item;
上面這條SQL語句執行後會顯示成功,但是實際上除了
SELECT COUNT(*) FROM [watch_single ] WHERE watch_ single.watchid = 1;
能執行之外是無法執行其他任何語句的。其原因在於建立視圖的時候指定了欄位所在的表名,而SQLite並不能正確地識別它。所以上面的建立語句要改為:
CREATE VIEW watch_single AS SELECT DISTINCT [watchid],[itemid] FROM watch_item;
但是隨之而來的問題是如果是多表的視圖,且表間有重名欄位的時候該怎麼辦?
3 COUNT(DISTINCT column)
SQLite在執行如下語句的時候會報錯:
SELECT COUNT(DISTINCT watchid) FROM [watch_item] WHERE watch_item.watchid = 1;
其原因是SQLite的所有內建函數都不支援DISTINCT限定,所以如果要統計不重複的記錄數的時候會出現一些麻煩。比較可行的做法是先建立一個不重複的記錄表的視圖,然後再對該視圖進行計數。
4 外串連
雖然SQLite官方已經聲稱LEFT OUTER JOIN 已經實現,但還沒有 RIGHT OUTER JOIN 和 FULL OUTER JOIN。但是實際測試表明似乎並不能夠正常的工作。以下三條語句在執行的時候均會報錯:
SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE tags.[tagid] = tag_rss.[tagid](*);
SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE LEFT OUTER JOIN tag_rss.[tagid] = tags.[tagid];
SELECT tags.[tagid] FROM [tags],[tag_rss] WHERE LEFT JOIN tag_rss.[tagid] = tags.[tagid];
此外經過測試用+號代替*號也是不可行的。
收集SQLite與Sql Server的文法差異
1.返回最後插入的標識值
返回最後插入的標識值sql server用@@IDENTITY
sqlite用純量涵式LAST_INSERT_ROWID()
返回通過當前的 SQLConnection 插入到資料庫的最後一行的行標識符(產生的主鍵)。此值與 SQLConnection.lastInsertRowID 屬性返回的值相同。
2.top n
在sql server中返回前2行可以這樣:
select top 2 * from aa
order by ids desc
sqlite中用LIMIT,語句如下:
select * from aa
order by ids desc
LIMIT 2
3.GETDATE ( )
在sql server中GETDATE ( )返回當前系統日期和時間
sqlite中沒有
4.EXISTS語句
sql server中判斷插入(不存在ids=5的就插入)
IF NOT EXISTS (select * from aa where ids=5)
BEGIN
insert into aa(nickname)
select 't'
END
在sqlite中可以這樣
insert into aa(nickname)
select 't'
where not exists(select * from aa where ids=5)
5.嵌套事務
sqlite僅允許單個活動的事務
6.RIGHT 和 FULL OUTER JOIN
sqlite不支援 RIGHT OUTER JOIN 或 FULL OUTER JOIN
7.可更新的視圖
sqlite視圖是唯讀。不能對視圖執行 DELETE、INSERT 或 UPDATE 語句,sql server是可以對視圖 DELETE、INSERT 或 UPDATE