SQLite数据库管理系统-我所认识的数据库引擎
SQLite是一款轻量级的、被设计用于嵌入式系统的关联式数据库管理系统。SQLite是一个实现自我依赖、纯客户端、零配置且支持事务的数据库引擎。它由D.RichardHipp首次开发,目前已是世界上最广泛部署的开源数据库引擎。
本文中,我们将介绍如下内容:
创建一个SQLite数据库
SQLiteConnectionconn=newSQLiteConnection("DataSource=mytest.s3db");
conn.Open();
SQLite数据插入
///<summary>
///AllowstheprogrammertoeasilyinsertintotheDB
///</summary>
///<paramname="tableName">Thetableintowhichweinsertthedata.</param>
///<paramname="data">Adictionarycontainingthecolumnnamesanddatafortheinsert.</param>
///<returns>Abooleantrueorfalsetosignifysuccessorfailure.</returns>
publicboolInsert(stringtableName,Dictionary<string,string>data)
{
BooleanreturnCode=true;
StringBuildercolumnBuilder=newStringBuilder();
StringBuildervalueBuilder=newStringBuilder();
foreach(KeyValuePair<string,string>valindata)
{
columnBuilder.AppendFormat("{0},",val.Key);
valueBuilder.AppendFormat(""{0}",",val.Value);
}
columnBuilder.Remove(columnBuilder.Length-1,1);
valueBuilder.Remove(valueBuilder.Length-1,1);
try
{
this.ExecuteNonQuery(string.Format("INSERTINTO{0}({1})VALUES({2});",
tableName,columnBuilder,valueBuilder));
}
catch(Exceptionex)
{
mLog.Warn(ex.ToString());
returnCode=false;
}
returnreturnCode;
}
DateTimeentryTime;
stringname=string.Empty,title=string.Empty;
GetSampleData(outname,outtitle,outentryTime);
intid=random.Next();
insertParameterDic.Add("Id",id.ToString());
insertParameterDic.Add("Name",name);
insertParameterDic.Add("Title",title);
insertParameterDic.Add("EntryTime",
entryTime.ToString("yyyy-MM-ddHH:mm:ss"));
db.Insert("Person",insertParameterDic);
SQLite的事务处理方式
BeginTransaction:
CommitTransaction:
RollbackTransaction:
索引是一种用来优化查询的特性,在数据中分为聚簇索引和非聚簇索引;前者是由数据库中数据组织方式决定的,比如我们在往数据库中一条一条插入数据时,聚簇索引能够保证按顺序插入,插入后数据的位置和结构不变。非聚簇索引是指我们手动、显式创建的索引,可以为数据库中的每个列创建索引,和字典中的索引类似,遵循的原则是对有分散性和组合型的列建立索引,以利于大数据和复杂查询情况下提高查询效率。 复杂查询情况下对查询效率的测试结果如下(~40,000条数据): SQLite的触发器(Trigger) 触发器是指当一个特定的数据库事件(DELETE,INSERT,orUPDATE)发生以后自动执行的数据库操作, 我们可以把触发器理解为高级语言中的事件(Event)。 假设我有两个表: Folder(GuidVCHAR(255)NOTNULL,DeletedBOOLEANDEFAULT0) File(ParentGuidVCHAR(255)NOTNULL,DeletedBOOLEANDEFAULT0) 在Folder表中创建一个触发器Update_Folder_Deleted: 视图可以是一个虚拟表,里面可以存储按照一定条件过滤出来的数据集合,这样我们再下次想得到这些特定数据集合的时候就不用通过复杂查询来获得,简单的查询指定视图就可以得到想要的数据。 在下个例子中,我们创建一个简单的视图: 基于上面的查询结果我们创建一个视图: SQLite命令行工具 SQLite库中包含了一个SQLite3.exe的命令行工具,它可以实现SQLite各项基本操作。这里只介绍一下如何使用它来分析我们的查询结果: 1.CMD->sqlite3.exeMySQLiteDbWithoutIndex.s3db 2.开启EXPLAIN功能并分析指定查询结果 3.重新使用命令行打开一个有索引的数据库并执行前两步 4.通过比较两个不同查询语句的分析结果,我们可以发现如果查询过程中使用了索引,SQLite会在detail列中提示我们。 5.要注意的是每条语句后面都要加分号“;” SQLite一些常见的使用限制 1.SQLite不支持Unicode字符的大小写比较,请看以下测试结果: 2.如何处理SQLite转义字符: 这里提到复合查询的原因是我们可以使用它来帮助我们快速插入大量数据:
try
{
db.OpenTransaction();
Insert4Native();
db.CommiteTransaction();
}
catch(System.Exceptionex)
{
mLog.Error(ex.ToString());
db.RollbackTransaction();
}
SQLite的索引
///<summary>
///Createindex
///</summary>
///<paramname="tableName">tablename</param>
///<paramname="columnName">columnname</param>
///<paramname="indexName">indexname</param>
publicvoidCreateIndex(stringtableName,stringcolumnName,stringindexName)
{
stringcreateIndexText=string.Format("CREATEINDEX{0}ON{1}({2});",
indexName,tableName,columnName);
ExecuteNonQuery(createIndexText);
}
简单查询、无关数据库大小情况下对查询效率的测试结果如下(700,000条数据):
stringsql="SELECTLeafNameFROMFileWHERELength>5000";
stringsql="SELECTfolder.LocationASFilePath"
+"FROMFolderfolderLEFTJOINFilefileONfile.ParentGuid=folder.Guid"
+"WHEREfile.Length>5000000GROUPBYFile.LeafName";
CREATETRIGGERUpdate_Folder_DeletedUPDATEDeletedONFolder
Begin
UPDATEFileSETDeleted=new.DeletedWHEREParentGuid=old.Guid;
END;
创建完触发器以后在执行以下语句:
UPDATEFolderSETDeleted=1WHEREGuid="13051a74-a09c-4b71-ae6d-42d4b1a4a7ae"
以上语句将会导致下面的语句自动执行:
UPDATEFileSETDeleted=1WHEREParentGuid="13051a74-a09c-4b71-ae6d-42d4b1a4a7ae"
SQLite的视图(View)
INSERTINTOxyzVALUES("5O""clock");
3.一条复合SELECT语句的条数限制:
一条复合查询语句是指多条SELECT语句由UNION,UNIONALL,EXCEPT,orINTERSECT连接起来.SQLite进程的代码生成器使用递归算法来组合SELECT语句。为了降低堆栈的大小,SQLite的设计者们限制了一条复合SELECT语句的条目数量。SQLITE_MAX_COMPOUND_SELECT的默认值是500.这个值没有严格限制,在实践中,几乎很难看到一条复合查询语句的条目数大于500的。
publicvoidInsert4SelectUnion()
{
boolnewQuery=true;
StringBuilderquery=newStringBuilder(4*ROWS4ACTION);
for(inti=0;i<ROWS4ACTION;i++)
{
if(newQuery)
{
query.Append("INSERTINTOPerson");
newQuery=false;
}
else
{
query.Append("UNIONALL");
}
DateTimeentryTime;
stringname=string.Empty,title=string.Empty;
GetSampleData(outname,outtitle,outentryTime);
intid=random.Next();
query.AppendFormat("SELECT"{0}","{1}","{2}","{3}"",id,name,title,entryTime.ToString("yyyy-MM-ddHH:mm:ss"));
if(i%499==0)
{
db.ExecuteNonQuery(query.ToString());
query.Remove(0,query.Length);
newQuery=true;
}
}
//executingremaininglines
if(!newQuery)
{
db.ExecuteNonQuery(query.ToString());
query.Remove(0,query.Length);
}
}
相关文章