一、SQLite介绍
(一)基本介绍
1. 数据库(database,DB):一个以某种有组织的方式存储的数据集合。数据库中的数据按一定的数学模型组织、描述和存储,具有较小的冗余,较高的数据独立性和易扩展性,并可为各种用户共享。
SQLite:是一个进程内的库,是遵守ACID的关联式数据库管理系统,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它是一个零配置的数据库,这意味着与其他数据库不一样,您不需要在系统中配置。
2. ACID具有:
(1) 原子性:同一个事物的2个步骤同时进行,一荣俱荣,一损俱损;一致性:一个事务操作前后状态一致;持久性:事务结束后数据不随外界原因导致数据丢失;隔离性:多个事务同时进行互不影响。
(2) 隔离级别:脏读:一个事务读取另一个事务未提交的数据;
不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同(这个不一定是错误,只是某些场合不对);虚读:是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致(一般是行影响,多了一行)。
优点:嵌入式,关系型数据库(采用了关系模型来组织数据的数据库),速度快。
(二)存储数据类型
1. 存储类型
SQLite将数据值的存储划分为以下几种存储类型:
存储类 | 描述 |
---|---|
NULL | 值是一个 NULL 值。 |
INTEGER | 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。 |
REAL | 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字。 |
TEXT | 值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储。 |
BLOB | 值是一个 blob 数据,完全根据它的输入存储。 |
2. 亲和类型
SQLite支持列的亲和类型概念,任何列仍然可以存储任何类型的数据,当数据插入时该字段的数据将会优先采用亲和类型作为该值的存储方式。创建 SQLite3 表时可使用的各种数据类型名称及相应的亲和类型,如下:
数据类型 | 亲和类型 |
---|---|
INT,INTEGER,TINYINT,SMALLINT,MEDIUMINT,BIGINT,UNSIGNED BIG INT,INT2,INT8 | INTEGER:对于亲缘类型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时。 |
CHARACTER(20),VARCHAR(255),VARYING CHARACTER(255),NCHAR(55),NATIVE CHARACTER(70),NVARCHAR(100),TEXT,CLOB | TEXT:数值型数据在被插入之前,需要先被转换为文本格式,之后再插入到目标字段中 |
BLOB,no datatype specified | NONE:不做任何的转换,直接以该数据所属的数据类型进行存储。 |
REAL,DOUBLE,DOUBLE PRECISION,FLOAT | REAL:其规则基本等同于NUMERIC,唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。 |
NUMERIC,DECIMAL(10,5),BOOLEAN,DATE,DATETIME | NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时:如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据;如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据。注:对于浮点格式的常量文本,如"30000.0",如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式。 |
二、可视化管理工具SQLiteStudio使用
对于sqlite数据库的可视化管理工具,我推荐使用sqlitestudio,绿色单文件,体积小,功能强大,比起其它SQLite管理工具,我喜欢用这个。很方便易用,不用安装的单个可执行文件,支持中文。
(一)下载
首先去官网下载(官网下载地址:https://sqlitestudio.pl/),如图1;
图1
下载并解压,直接运行SQLiteStudio.exe文件,界面如图2所示。
图2
(二)新建数据库
单击菜单Database->Add a database(如图3),
图3
弹出数据库属性对话框,如图4,
图4
单击绿色加好,选择数据库保存位置,并添加数据名称为MythDiary.db,结果如图5所示。
图5
(三)新建表格
步骤2完成后,在左侧的列表中会出现我们刚才新建的数据库,如图6所示。
图6
在数据库上单击右键,选择Connect to the database,链接数据库,如图7所示;
图7
展开数据库,会出现连个菜单,一是Tables,一个是Views,在Tables上单击右键选择Create Table新建表格,如图8所示。
图8
在右侧会出现表格属性设置界面,如图9所示,在这个界面我们可以设置表格的字段和表格的名称。
图9
先在表格名称里面输入adminuser,然后单击操作工具栏上的绿色打钩按钮进行保存(如图10),这样表格就已经保存成功,这里特别说明一下:
“WITHOUT ROWID”这个复选框:在SQLiteStudio中,我们新建一个表格,如果这个复选框不被选择,SQLiteStudio会自动给表格添加一个字段RowID,这个字段是一个隐含的字段,自动加一;如果选择不会添加这个隐形字段。
(四)添加字段。
保存完成后,单击操作工具栏上的添加字段按钮,弹出字段属性对话框,如图11所示,在这里我们可以设置字段名称,数据类型、大小等。目前我们需要完成3个表格的设计,一个是adminuser表格,一个是DiaryType,最后一个是Articles,具体字段如下:
1. DiaryType 数据字段
名称 | 数据类型 | 默认值 | 备注 |
---|---|---|---|
TypeName | VARCHAR | 分类名称 | |
ParentID | INT | 父类ID | |
TypeIcon | VARCHAR | 分类小图标 | |
CreateTime | DATETIME | DEFAULT (datetime(‘now’, ‘localtime’) ) | 创建时间 |
2. Articles数据字段
名称 | 数据类型 | 默认值 | 备注 |
---|---|---|---|
Title | VARCHAR | 标题 | |
Content | TEXT | 内容 | |
Pic | VARCHAR | 文章图片 | |
KeyWord | VARCHAR | 文章关键字 | |
Desc | VARCHAR | 文章描述 | |
UserID | INT | 0 | 作者ID |
CreateTime | DATETIME | DEFAULT (datetime(‘now’, ‘localtime’) ) | 创建时间 |
EditTime | DATETIME | DEFAULT (datetime(‘now’, ‘localtime’) ) | 修改时间 |
ViewCount | INT | 0 | 查看次数 |
State | INT | 0 | 文章状态,0表示不公开;1表示公开 |
IsBackUp | INT | 0 | 是否备份到服务器:0未备份;1已备份 |
3. Adminuser 数据字段
名称 | 数据类型 | 默认值 | 备注 |
---|---|---|---|
UserName | VARCHAR | 用户名称 | |
PassWord | VARCHAR | 用户密码 | |
HeadPic | VARCHAR | 用户头像照片地址 | |
OneKey | VARCHAR | 一句话宣言 | |
CreateTime | DATETIME | DEFAULT (datetime(‘now’, ‘localtime’) ) | 创建时间 |
LastLoginTime | DATETIME | DEFAULT (datetime(‘now’, ‘localtime’) ) | 最后登录时间 |
三、c#操作sqlite数据库
在C#中使用SQLite数据库需要引用System.Data.SQLite.dll,下载链接:http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
注:System.Data.SQLite是SQLite的ADO.NET提供程序,两者是两个不同的开源项目,现在System.Data.SQLite的开发和维护工作大部分由SQLite开发团队执行。
(一)操作基础类
public class SQLiteHelper
{
/// <summary>
/// 数据库列表
/// </summary>
public static Dictionary<string, SQLiteHelper> DataBaceList = new Dictionary<string, SQLiteHelper>();
/// <summary>
/// 构造函数
/// </summary>
/// <param name="filename">数据库文件名</param>
public SQLiteHelper(string filename=null)
{
DataSource = filename;
}
/// <summary>
/// 数据库地址
/// </summary>
public string DataSource { get; set; }
/// <summary>
/// 创建数据库,如果数据库文件存在则忽略此操作
/// </summary>
public void CreateDataBase()
{
string path = Path.GetDirectoryName(DataSource);
if ((!string.IsNullOrWhiteSpace(path)) && (!Directory.Exists(path))) Directory.CreateDirectory(path);
if (!File.Exists(DataSource)) SQLiteConnection.CreateFile(DataSource);
}
/// <summary>
/// 获得连接对象
/// </summary>
/// <returns>SQLiteConnection</returns>
public SQLiteConnection GetSQLiteConnection()
{
string connStr =string.Format("Data Source={0}", DataSource);
var con = new SQLiteConnection(connStr);
return con;
}
/// <summary>
/// 准备操作命令参数
/// </summary>
/// <param name="cmd">SQLiteCommand</param>
/// <param name="conn">SQLiteConnection</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
if (data != null && data.Count >= 1)
{
foreach (KeyValuePair<String, String> val in data)
{
cmd.Parameters.AddWithValue(val.Key, val.Value);
}
}
}
/// <summary>
/// 查询,返回DataSet
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data = null)
{
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
da.Fill(ds);
}
return ds;
}
/// <summary>
/// 查询,返回DataTable
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data = null)
{
var dt = new DataTable();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = command.ExecuteReader();
dt.Load(reader);
}
return dt;
}
/// <summary>
/// 返回一行数据
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">参数数组</param>
/// <returns>DataRow</returns>
public DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data = null)
{
DataSet ds = ExecuteDataset(cmdText, data);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
return ds.Tables[0].Rows[0];
return null;
}
/// <summary>
/// 执行数据库操作
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteNonQuery(string cmdText, Dictionary<string, string> data=null)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 返回SqlDataReader对象
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>SQLiteDataReader</returns>
public SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data = null)
{
var command = new SQLiteCommand();
SQLiteConnection connection = GetSQLiteConnection();
try
{
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch
{
connection.Close();
command.Dispose();
throw;
}
}
/// <summary>
/// 返回结果集中的第一行第一列,忽略其他行或列
/// </summary>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="data">传入的参数</param>
/// <returns>object</returns>
public object ExecuteScalar(string cmdText, Dictionary<string, string> data = null)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
PrepareCommand(cmd, connection, cmdText, data);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <param name="recordCount">总记录数</param>
/// <param name="pageIndex">页牵引</param>
/// <param name="pageSize">页大小</param>
/// <param name="cmdText">Sql命令文本</param>
/// <param name="countText">查询总记录数的Sql文本</param>
/// <param name="data">命令参数</param>
/// <returns>DataSet</returns>
public DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data = null)
{
if (recordCount < 0)
recordCount = int.Parse(ExecuteScalar(countText, data).ToString());
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
}
return ds;
}
/// <summary>
/// 重新组织数据库:VACUUM 将会从头重新组织数据库
/// </summary>
public void ResetDataBass()
{
using (SQLiteConnection conn = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = "vacuum";
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
cmd.ExecuteNonQuery();
}
}
}
(二)类的使用
static void Main(string[] args)
{
SQLiteHelper testDb = new SQLiteHelper("test.db");
SQLiteHelper.DataBaceList.Add("TEST", testDb);
//建库
testDb.CreateDataBase();
//建表
StringBuilder sbr = new StringBuilder();
sbr.AppendLine("CREATE TABLE IF NOT EXISTS `test_table`(");
sbr.AppendLine("`id` INTEGER PRIMARY KEY AUTOINCREMENT,");//自增id主键
sbr.AppendLine("`name` VARCHAR(100) NOT NULL,");
sbr.AppendLine("`password` VARCHAR(40) NOT NULL,");
sbr.AppendLine("`create_time` datetime DEFAULT CURRENT_TIMESTAMP,");
sbr.AppendLine("`update_time` datetime DEFAULT CURRENT_TIMESTAMP );");
sbr.AppendLine();
sbr.AppendLine("CREATE TRIGGER IF NOT EXISTS `trigger_test_table_update_time` ");//触发器-自动更新update_time
sbr.AppendLine("AFTER UPDATE ON `test_table` ");
sbr.AppendLine("FOR EACH ROW ");
sbr.AppendLine("BEGIN ");
sbr.AppendLine("UPDATE `test_table` SET `update_time` = CURRENT_TIMESTAMP WHERE id = old.id; ");
sbr.AppendLine("END;");
string cmdText = sbr.ToString();
int val = testDb.ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数:" + val);
//增
sbr.Clear();
sbr.Append("INSERT INTO test_table (name,password) VALUES ");
sbr.Append("(11,111), ");
sbr.Append("(12,222); ");
cmdText = sbr.ToString();
val = testDb.ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数:" + val);
//删
sbr.Clear();
sbr.Append("DELETE FROM test_table ");
sbr.Append("WHERE id=1;");
cmdText = sbr.ToString();
val = testDb.ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数:" + val);
//改
sbr.Clear();
sbr.Append("UPDATE test_table SET ");
sbr.Append("name='13', ");
sbr.Append("password='333' ");
sbr.Append("WHERE id=@id;");
cmdText = sbr.ToString();
Dictionary<string, string> data = new Dictionary<string, string>();
data.Add("@id", "2");
val = testDb.ExecuteNonQuery(cmdText, data);
Console.WriteLine("影响行数:" + val);
//查
sbr.Clear();
sbr.Append("SELECT name,password FROM test_table ");
sbr.Append("WHERE id=@id;");
cmdText = sbr.ToString();
DataTable dt = testDb.ExecuteDataTable(cmdText, data);
Console.WriteLine("结果行数:" + dt.Rows.Count);
//删除表
sbr.Clear();
sbr.Append("DROP TABLE test_table;");
cmdText = sbr.ToString();
val = SQLiteHelper.DataBaceList["TEST"].ExecuteNonQuery(cmdText);
Console.WriteLine("影响行数:" + val);
//重组数据库
SQLiteHelper.DataBaceList["TEST"].ResetDataBass();
Console.ReadKey();
}
(三)sqlite事务
事务定义了一组 SQL 命令,这组命令或者作为一个整体被全部执行,或者都不执行,这被称为数据库完整性的原子性原则。这种关系的典型例子就是银行转账,假设银行程序从一个账户向另一个账户转账,转账程序通过如下方式进行:首先将第一个账户的钱转入第二个账户,然后从第一个账户删除对应的数目;或者首先从第一个账户删除要转账的数目,然后向第二个账户插入对应的数目。无论哪种方式,都是通过两步完成的:先插入,后删除;或者先删除,后插入。但是在转账期间,如果数据库服务器突然奔溃或电力中断,第二个操作没有完成怎么办?要么这笔钱存在于两个账户(第一种方式),要么这笔钱在两个账户中都不存在(第二种方式)。无论发生哪种情况,总有人无法接受。数据库也处于不一致的状态,关键是这两步操作必须要同时被执行或者一步都不执行。这就是事务的本质。
事务使用代码:
SQLiteTransaction tr = dbConnection.BeginTransaction();//事务开始
try
{
SQLiteCommand cmd = new SQLiteCommand();
cmd.CommandText = "DROP TABLE IF EXISTS Company1";
cmd.Connection = dbConnection;
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE Company1(ID INTEGER PRIMARY KEY NOT NULL,NAME TEXT NOT NULL)";//创建一个表,ID为主键,NOT NULL 表示这个不能为空
cmd.Connection = dbConnection;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('ALBABA')";//表插入内容
cmd.Connection = dbConnection;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('HKWS')";
cmd.Connection = dbConnection;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('HUAW')";
cmd.Connection = dbConnection;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Company1(NAME) VALUES('GSYH')";
cmd.Connection = dbConnection;
cmd.ExecuteNonQuery();
Console.Write("FINALLY");
tr.Commit();//把事务调用的更改保存到数据库中,事务结束
dbConnection.Close();
}
catch(Exception ex)
{
Console.Write(ex.Message);
tr.Rollback();//回滚
}
}
ok,本节就到这里
我的日记开发系列手记目录
1.我的日记本开发手记——概述
2.我的日记本开发手记(2)——配色
3.我的日记本开发手记(3)—— 布局
4.我的日记本开发手记(4)—— UI效果图
5.我的日记本开发手记(5)—— 效果图转HTML
6.我的日记本开发手记(6)——Winform运行HTML
7.第七节_我的日记本开发手记(7)——Javascript与c#交互