平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“SQLite数据库管理系统-我所认识的数据库引擎”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。
在这个场景下，SQLite 是一款轻量级的、被设计用来嵌入式系统的关联式数据库管理系统。SQLite 是一个实现自我依赖、纯客户端、零设置且兼容事务的数据库引擎。它由D. Richard Hipp首次开发，目前已是世界上最广泛部署的开源数据库引擎。
本下文，我们将介绍如下所示内容：
新建一个SQLite 数据库
SQLiteConnection conn = new SQLiteConnection("Data Source=mytest.s3db");
conn.Open();
SQLite 数据插入
///
/// Allows the programmer to easily insert into the DB
///
/// The table into which we insert the data.
/// A dictionary containing the column names and data for the insert.
///
public bool Insert(string tableName, Dictionary
{
Boolean returnCode = true;
StringBuilder columnBuilder = new StringBuilder();
StringBuilder valueBuilder = new StringBuilder();
foreach (KeyValuePair
{
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("INSERT INTO {0}({1}) VALUES({2});",
tableName, columnBuilder, valueBuilder));
}
catch (Exception ex)
{
mLog.Warn(ex.ToString());
returnCode = false;
}
return returnCode;
}
DateTime entryTime;
string name = string.Empty, title = string.Empty;
GetSampleData(out name, out title, out entryTime);
int id = random.Next();
insertParameterDic.Add("Id", id.ToString());
insertParameterDic.Add("Name", name);
insertParameterDic.Add("Title", title);
insertParameterDic.Add("EntryTime",
entryTime.ToString("yyyy-MM-dd HH:mm:ss"));
db.Insert("Person", insertParameterDic);
SQLite 的事务处理方式
Begin Transaction:
Commit Transaction:
Rollback Transaction:
try
{
db.OpenTransaction();
Insert4Native();
db.CommiteTransaction();
}
catch (System.Exception ex)
{
mLog.Error(ex.ToString());
db.RollbackTransaction();
}
SQLite 的索引
实际处理时，索引是一种用来优化查询的特性，在数据中分为聚簇索引和非聚簇索引；前者是由数据库中数据组织方式决定的，比如我们在往数据库中一条一条插入数据时，聚簇索引能够保证按顺序插入，插入后数据的位置和结构不变。非聚簇索引是指我们手动、显式新建的索引，能够为数据库中的每个列新建索引，和字典中的索引类似，遵循的原则是对有分散性和组合型的列建立索引，以利于大数据和复杂查询情况下提高查询效率。
///
/// Create index
///
/// table name
/// column name
/// index name
public void CreateIndex(string tableName, string columnName, string indexName)
{
string createIndexText = string.Format("CREATE INDEX {0} ON {1} ({2});",
indexName, tableName, columnName);
ExecuteNonQuery(createIndexText);
}
轻松查询、无关数据库大小情况下对查询效率的测试结果如下所示(700,000条数据)：
string sql = "SELECT LeafName FROM File WHERE Length > 5000";
复杂查询情况下对查询效率的测试结果如下所示(~40,000条数据)：
string sql = "SELECT folder.Location AS FilePath"
+ "FROM Folder folder LEFT JOIN File file ON file.ParentGuid=folder.Guid"
+"WHERE file.Length > 5000000 GROUP BY File.LeafName";
SQLite 的触发器(Trigger)
落到代码里，触发器是指当一个特定的数据库事件(DELETE, INSERT, or UPDATE)发生以后自动执行的数据库操作, 我们能够把触发器理解为高级语言中的事件(Event)。
假设我有两个表：
Folder(Guid VCHAR(255) NOT NULL, Deleted BOOLEAN DEFAULT 0)
File(ParentGuid VCHAR(255) NOT NULL, Deleted BOOLEAN DEFAULT 0)
在Folder 表中新建一个触发器Update_Folder_Deleted:
CREATE TRIGGER Update_Folder_Deleted UPDATE Deleted ON Folder
Begin
UPDATE File SET Deleted=new.Deleted WHERE ParentGuid=old.Guid;
END;
新建完触发器以后在执行以下语句：
UPDATE Folder SET Deleted=1 WHERE Guid='13051a74-a09c-4b71-ae6d-42d4b1a4a7ae'
以上语句将会导致下面的语句自动执行：
UPDATE File SET Deleted=1 WHERE ParentGuid='13051a74-a09c-4b71-ae6d-42d4b1a4a7ae'
SQLite 的视图(View)
结合项目来看，视图能够是一个虚拟表，里面能够存储按照一定条件过滤出来的数据集合，这样我们再下次想得到这些特定数据集合的时候就不用借助复杂查询来获得，轻松的查询指定视图就能够得到想要的数据。
在下个例子里，我们新建一个轻松的视图：
基于上面的查询结果我们新建一个视图：
SQLite 命令行工具
实际处理时，SQLite 库中包含了一个SQLite3.exe 的命令行工具，它能够完成SQLite 各项基本操作。这里只介绍一下如何采用它来分析我们的查询结果：
1. CMD->sqlite3.exe MySQLiteDbWithoutIndex.s3db
2. 开启EXPLAIN 功能并分析指定查询结果
3. 重新采用命令行打开一个有索引的数据库并执行前两步
4. 借助比较两个不同查询语句的分析结果，我们能够发现如果查询过程中采用了索引，SQLite 会在detail 列中提示我们。
5. 需留意的是每条语句后面都要加分号“；”
SQLite一些常用的采用限制
1. SQLite 不兼容Unicode 字符的大小写比较，请看以下测试结果：
2. 如何处理SQLite 转义字符：
INSERT INTO xyz VALUES('5 O''clock');
3. 一条复合SELECT语句的条数限制：
在这个场景下，一条复合查询语句是指多条SELECT语句由 UNION, UNION ALL, EXCEPT, or INTERSECT 连接起来. SQLite进程的代码生成器采用递归算法来组合SELECT语句。为了降低堆栈的大小，SQLite 的设计者们限制了一条复合SELECT语句的条目数量。 SQLITE_MAX_COMPOUND_SELECT的默认值是500. 这个值没有严格限制，在实践中，几乎很难看到一条复合查询语句的条目数大于500的。
这里提到复合查询的原因是能够采用它来帮助我们更快插入大量数据：
public void Insert4SelectUnion()
{
bool newQuery = true;
StringBuilder query = new StringBuilder(4 * ROWS4ACTION);
for (int i = 0; i < ROWS4ACTION; i++)
{
if (newQuery)
{
query.Append("INSERT INTO Person");
newQuery = false;
}
else
{
query.Append(" UNION ALL");
}
DateTime entryTime;
string name = string.Empty, title = string.Empty;
GetSampleData(out name, out title, out entryTime);
int id = random.Next();
query.AppendFormat(" SELECT '{0}','{1}','{2}','{3}'", id, name, title, entryTime.ToString("yyyy-MM-dd HH:mm:ss"));
if (i % 499 == 0)
{
db.ExecuteNonQuery(query.ToString());
query.Remove(0, query.Length);
newQuery = true;
}
}
//executing remaining lines
if (!newQuery)
{
db.ExecuteNonQuery(query.ToString());
query.Remove(0, query.Length);
}
}
- 30 个很棒的PHP开源CMS内容管理系统小结
- Swift中的Access Control权限控制介绍
- php结合ACCESS的跨库查询功能
- C#借助oledb访问access数据库的方法
- C#操作Access通用类实例
- Apache服务器中.htaccess的基本设置总结
- mysql Access denied for user ‘root’@’localhost’ (using password: YES)解决方法
- Javascript连接Access数据库完整实例
- Access转成SQL数据库的方法
- SQL Server数据复制到的Access两步走
- Access新建一个轻松MIS管理系统
