章节：
1、下载安装 2、数据类型 3.创建数据库 4.删除数据库 5、创建表 6、删除表 7.查询表结构 8、更改表名 9、增加列（字段） 读取创建表 SQL 语句 11、更改列名 12、删除列 13、插入数据 14、替换数据 15、更新数据 16、删除数据 17、查询数据 18.获取查询数据的行数(多少条记录) 19、事务 Transaction 整理数据库 正文：
一、下载安装
这段时间在学习 C# 编程中，想写一个简单的进销存程序，就想到了用数据库，需要一个简单便携的桌面数据库，想自己写个，功力太浅，可以做为以后练手学习的项目。原来会用的 Foxpro 不知道微软丢在哪个角落，在网上找了一下，发现只有 Access 和 Sqlite 可选，看了很多对比，决定学习使用。 Sqlite。
在 System.Data.SQLite 官网的 download 中的 Setups for 64-bit Windows (.NET Framework 4.6) sqlite-netFx46-setup-x64-2015-1.0.104.0.exe (17.99 MiB) 下载并运行安装。 更简单的方法是 Visual Studio 2017 的 NuGet 中，输入：install-package system.data.sqlite.x64。
sqlite在数据库的可视化工具中， SqliteExpert 不错，下载 SQLite Expert Personal 4.x 。
工具齐全，因为知道上面这个System data Sqlite 是用 C# 包装好的，我们打开Visual Studio 2017年，新项目在菜单项目开放→添加引用→“浏览” 中，去 Sqlite 选择安装目录 System.Data.SQLite.dll，才305k的链接库。引用后，在VS看右上角引用的解决方案资源管理器 System.Data.SQlite 引用属性中的复制到本地 是不是 true，如果没有，就做成 true。在工程中开头添加 using 语句：
using System.Data.SQLite;
这里有很多在线教程ok但而，在我的实际操作中，我发现我必须 SQLite.Interop.dll 也复制到当前程序运行目录(不能引用，只能直接复制)，不知道是否是新版本的要求。
(ps:在 sqlite 在安装目录下详细帮助文档 SQLite.NET.chm)
二、数据类型
存储的数据有五种类型：
存储类 描述 NULL 一个NULL值 INTERGER 根据值的大小，带符号的整数自动存储在1、2、3、4、5、8字节中 REAL 浮点数，存储为IEEE 8byte浮点数 TEXT 文字字符串，缺少编码utf-8 BLOG blob数据，不定长 注意了，SQLite 存储宽度是根据输入自动调整的，这和我用过的一样 foxpro 例如，即使你在那里 create 在数据表中设置了一个字段 varchar(4) 4byte宽字符串，但你输入hello当五个宽度的字符串被截取为hell相反，它被完全存储为hello”。数字类型也是如此。
更有趣的是，它有一个Type Affinity 如下：
CREATE TABLE t1(a INT, b VARCHAR(10)); INSERT INTO t1(a,b) VALUES(123，456)；
它会运用 Type Affinity 功能自动正确 "123" 转换成数字，把 456转换成456字符串。Type Affinity 请参考安装目录 帮助文件或 SQLite 亲和(Affinity)类型。
三、创建数据库
SQLite 它是一个文件数据库，创建非常简单，直接指定数据库文件名，后缀名不一定是.sqlite后缀随名后缀".db"也成。运行 SQLiteConnection.open 将创建一个指定名称的空数据库文件。因为它是文件型的，我们也可以直接使用 System.IO.File.Create() 创建空文件。
using System.Data.SQLite; ///-创建数据库 static void CreateDB() { string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" path); cn.Open(); cn.Close(); }
四、删除数据库
sqlite 命令中好像没有提供删除整个数据库的命令，但是由于它是个文件型的，我们直接用 System.IO.File.Delete(string path) 删除文件的方法。
// static void DeleteDB() { string path = @"d:\test\123.sqlite"; if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } }
五、创建表
开始要用到 SQL 命令。建立表的顺序如下(也可以使用可视化工具) SQLiteExpert 来创建）： 1.建立数据库连接； 2.打开数据库(如果没有数据库，Open 还将创建新的数据库)； 3、声明一个 SQLiteCommand 类主要用于放置和运行 SQL 命令的； 4、把 SQLiteCommand 的 Connection 和 SQLiteConnection 联系(记住，经常忘记^_^!）； 5、往 SQLiteCommand 的 CommandText 输入 SQL 语句 CREATE TABLE 具体请参考句子 在安装目录下 SQLite.NET.chm 或 SQLite 创建表； 6、调用 SQLiteCommand.ExcuteNonQuery() 方法运行。
//---添加表 static void CreateTable() { string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" path); if (cn.State!= System.Data.ConnectionState.Open) { cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; cmd.CommandText = "CREATE TABLE t1(id varchar(4),score int)"; //cmd.CommandText = "CREATE TABLE IF NOT EXISTS t1(id varchar(4),score int)"; cmd.ExecuteNonQuery(); } cn.Close(); }
注意上面注释的句子 CREATE TABEL IF NOT EXISTS ，一般来说，最好用这句话。如果原来有同名表，没有这句话就会出错。SQL 其实句子不需要全部大写，全部大写都是 SQL 语句约定俗成(让我想起读书时学的东西) COBOL），所有的小写都不会出错。
六、删除表
就像建立表的步骤一样，只是把手 SQL 只是语句变了。
//---删除表 static void DeleteTable() { string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" path); if (cn.State != System.Data.ConnectionState.Open) { cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; cmd.CommandText = "DROP TABLE IF EXISTS t1"; cmd.ExecueNonQuery(); } cn.Close(); }
七、查询表结构
需要用到 SQLite 特殊的 PRAGMA 命令， 具体参见 PRAGMA Statements PRAGMA table_info(tablename) ，tablename 用或不用单引号 ' ' 括起来都一样。 SQliteDataReader 读出来的数据顺序代表：
下标 名称 描述 0 cid 序号 1 name 名字 2 type 数据类型 3 notnull 能否null值，0不能，1 能 4 dflt_value 缺省值 5 pk 是否主键primary key,0否，1是 string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" + path); cn.Open(); SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText= "PRAGMA table_info('t1')";
//写法一：用DataAdapter和DataTable类，记得要 using System.Data SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); foreach(DataRow r in table.Rows) { Console.WriteLine($"{r["cid"]},{r["name"]},{r["type"]},{r["notnull"]},{r["dflt_value"]},{r["pk"]} "); } Console.WriteLine();
//写法二：用DataReader，这个效率高些 SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { for(int i = 0; i < reader.FieldCount; i++) { Console.Write($"{reader[i]},"); } Console.WriteLine(); } reader.Close();
如果不止一个表，要遍历所有表的结构如下，就要用到 SQLite 中的特殊表 sqlite_master，它的结构如下： 参考： 2.6. Storage Of The SQL Database Schema CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text ); 当 type = table 时，name 和 tbl_name 是一样的，其他比如 type =index 、view 之类时，tbl_name 才是表名。
//---遍历查询表结构 static void QueryAllTableInfo() { string path = @"d:\test\123.sqlite"; SQLiteConnection cn = new SQLiteConnection("data source=" + path); if (cn.State != System.Data.ConnectionState.Open) { cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; cmd.CommandText = "SELECT name FROM sqlite_master WHERE TYPE='table' "; SQLiteDataReader sr = cmd.ExecuteReader(); List<string> tables = new List<string>(); while (sr.Read()) { tables.Add(sr.GetString(0)); } //datareader 必须要先关闭，否则 commandText 不能赋值 sr.Close(); foreach (var a in tables) { cmd.CommandText = $"PRAGMA TABLE_INFO({a})"; sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine($"{sr[0]} {sr[1]} {sr[2]} {sr[3]}"); } sr.Close(); } } cn.Close(); }
八、更改表名
用 SQL 语句 ALTER TABLE 把 t1 表名改成 t3：
cmd.CommandText = "ALTER TABLE t1 RENAME TO t3"; cmd.ExecuteNonQuery();
注意，表名是不分大小写的，也不用加单引号括起来。
九、增添列（字段）
还是用 SQL 命令 ALTER TABLE ，下例中为 t1 表添加一个名为 age，数据类型为 int 的新列：
cmd.CommandText = "ALTER TABLE t1 ADD COLUMN age int"; cmd.ExecuteNonQuery();
十、读取创建表的 SQL 语句
读取创建表时的 SQL 语句，在 SqliteExpert 中的 DDL 可以查看到。读取这个是为下面增添删除列做准备。
cmd.CommandText = "SELECT sql FROM sqlite_master WHERE TYPE='table'"; SQLiteDataReader sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine(sr[0].ToString()); } sr.Close();
十一、更改列名
SQLite 中并没有提供直接更改列名与删除列的命令，有两种方式， 第一种是： 1、把目标表改名； 2、创建一个带有新列名的新表； 3、把旧表数据拷贝至新表（记得要 Connection.BeginTransaction())。
第二种是更改 sqlite_master 里面的 schema，很容易损坏数据库。 依据是 SQLite 每次连接时，其实都是依据 schema 里面的每个表创建时的 CREATE TABLE 语句来动态建立 column 的信息的，只要 column 的数据类型和位置不变，更改 CREATE TABLE 语句就能更改 column 的信息。具体参考 How do I rename a column in a SQLite database table?。以下我们两种方法都写来看看。
方式一：
//---更改列名1 //总思路：把旧表更名，建个带新列名的新表，拷贝数据 //params string[] 中：0 数据库名，1 表名，2 旧列名 3 新列名 static void RenameColumn1(params string[] str) { SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]); cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; //取得str[1]表名的表的建表SQL语句 cmd.CommandText = "SELECT name,sql FROM sqlite_master WHERE TYPE='table' ORDER BY name"; SQLiteDataReader sr = cmd.ExecuteReader();
string _sql = ""; while (sr.Read()) { if (string.Compare(sr.GetString(0), str[1], true) == 0) { _sql = sr.GetString(1); break; } } sr.Close();
//更改旧表名为 带 _old string _old = str[1] + "_old"; cmd.CommandText = $"ALTER TABLE {str[1]} RENAME TO {_old}"; cmd.ExecuteNonQuery();
//建立新表，假设输入的旧列名和表中的列名大小写等完全一致，不写能容错的了 _sql = _sql.Replace(str[2],str[3]); cmd.CommandText = _sql; cmd.ExecuteNonQuery();
//拷贝数据 using (SQLiteTransaction tr = cn.BeginTransaction()) { cmd.CommandText = $"INSERT INTO {str[1]} SELECT * FROM {_old}"; cmd.ExecuteNonQuery(); cmd.CommandText = $"DROP TABLE {_old}"; cmd.ExecuteNonQuery(); tr.Commit(); } cn.Close(); }
方式二：
//---更改列名2,改写schema里建表时的sql语句 //原理：sqlite 每次打开的时候，都是依据建表时的sql语句来动态建立column的信息的 static void RenameColumn2(params string[] str) { SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]); cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn;
//取得str[1]表名的表的建表SQL语句 cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'"; SQLiteDataReader sr = cmd.ExecuteReader(); sr.Read(); string _sql = sr.GetString(0); sr.Close(); //注意单引号 ' _sql =$"UPDATE sqlite_master SET sql='{_sql.Replace(str[2],str[3])}' WHERE name= '{str[1]}' ";
//设置 writable_schema 为 true，准备改写schema cmd.CommandText = "pragma writable_schema=1"; cmd.ExecuteNonQuery(); cmd.CommandText = _sql; cmd.ExecuteNonQuery(); //设置 writable_schema 为 false。 cmd.CommandText = "pragma writable_schema=0"; cmd.ExecuteNonQuery();
cn.Close(); }
十二、删除列
SQLite 也没有提供删除列的命令。和上面一样，也是两种方式。 其一，把目标表改名，建立没有要删除列（字段）的新表，然后把旧表的数据拷贝至新表。 其二，直接修改 schema 中建表的 SQL 语句。 其中最主要的是要把建表的列的所有信息都保存下来，比如索引、缺省值之类的。下面示例使用第二种方式。
//---删除列2，string[] ,0 数据库路径，1 表名，2 要删除的列名 static void DeleteColumn2(params string[] str) { SQLiteConnection cn = new SQLiteConnection("data source=" + str[0]); cn.Open(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cn; //取得str[1]表名的表的建表SQL语句 cmd.CommandText = $"SELECT sql FROM sqlite_master WHERE TYPE='table' AND name='{str[1]}'"; SQLiteDataReader sr = cmd.ExecuteReader(); sr.Read(); string _sql = sr.GetString(0); sr.Close();
//取得列的定义 //C#7.0的新特征，Tuple<>的语法糖，需要 NuGet install-package system.valuetuple List<(string name, string define)> list = GetColumnDefine(_sql); //取得要删除列的序号 int _index = list.IndexOf(list.Where(x => x.name == str[2]).First()); //建立新的sql语句 StringBuilder sb = new StringBuilder(); sb.Append($"CREATE TABLE {str[1]}("); for (int i = 0; i < list.Count; i++) { if (i != _index) { sb.Append($"{list[i].define},"); } } sb.Remove(sb.Length - 1, 1); sb.Append(")"); //改写schema _sql = $"UPDATE sqlite_master SET sql='{sb.ToString()}' WHERE name='{str[1]}'"; //设置 writable_schema 为 true，准备改写schema cmd.CommandText = "pragma writable_schema=1"; cmd.ExecuteNonQuery(); cmd.CommandText = _sql; cmd.ExecuteNonQuery(); //设置 writable_schema 为 false。 cmd.CommandText = "pragma writable_schema=0"; cmd.ExecuteNonQuery();
cn.Close(); } //---取得列的定义 static List<(string, string)> GetColumnDefine(string SqlStr) { int n = 0; int _start = 0; string _columnStr = ""; for (int i = 0; i < SqlStr.Length; i++) { if (SqlStr[i] == '(') { if (n++ == 0) { _start = i; } } else { if (SqlStr[i] == ')') { if (--n == 0) { _columnStr = SqlStr.Substring(_start + 1, i - _start - 1); break; } }
} } string[] ss = _columnStr.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); //C#7.0的新特征，Tuple<>的语法糖，需要 NuGet install-package system.valuetuple List<(string name, string define)> reslut = new List<(string name, string define)>(); foreach (var a in ss) { string s = a.Trim(); n = 0; for (int i = 0; i < s.Length; i++) { if (s[i] == ' ') { reslut.Add((s.Substring(0, i), s)); break; } } } return reslut; }
十三、插入数据
插入数据主要是用 SQL 语句 INSERT INTO
示例1（简单插入）：
cmd.CommandText = "INSERT INTO t1 VALUES('99999',11)"; cmd.ExecuteNonQuery();
示例2（变量插入，要引用 System.Data）：
using System.Data;
string s = "123456"; int n = 10; cmd.CommandText = "INSERT INTO t1(id,age) VALUES(@id,@age)"; cmd.Parameters.Add("id", DbType.String).Value = s; cmd.Parameters.Add("age", DbType.Int32).Value = n; cmd.ExecuteNonQuery();
十四、替换数据
SQL 命令 INSERT INTO。 下面示例中， t1 表中 id 为主键，相同主键值的就 UPDATE，否则就 INSERT
string s = "123456"; int n = 30; cmd.CommandText = "REPLACE INTO t1(id,age) VALUES(@id,@age)"; cmd.Parameters.Add("id", DbType.String).Value = s; cmd.Parameters.Add("age", DbType.Int32).Value = n; cmd.ExecuteNonQuery();
十五、更新数据
SQL 命令 UPDATE tablename SET column1=value,column2=value... WHERE 条件
string s = "333444"; int n = 30; cmd.CommandText = "UPDATE t1 SET id=@id,age=@age WHERE id='0123456789'"; cmd.Parameters.Add("id", DbType.String).Value = s; cmd.Parameters.Add("age", DbType.Int32).Value = n; cmd.ExecuteNonQuery();
十六、删除数据
SQL 命令：DELETE FROM tablename WHERE 条件
cmd.CommandText = "DELETE FROM t1 WHERE id='99999'"; cmd.ExecuteNonQuery();
十七、查询数据
SQL 命令：SELETE 语句，具体的请参考 SQL 教程。
//查询第1条记录，这个并不保险,rowid 并不是连续的，只是和当时插入有关 cmd.CommandText = "SELECT * FROM t1 WHERE rowid=1"; SQLiteDataReader sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}"); } sr.Close(); //运行以下的就能知道 rowid 并不能代表 行数 cmd.CommandText = "SELECT rowid FROM t1 "; sr = cmd.ExecuteReader(); while (sr.Read()) { Console.WriteLine($"{sr.GetString(0)} {sr.GetInt32(1).ToString()}"); } sr.Close();
十八、获取查询数据的行数（多少条记录）
从上面示例中我们得知，rowid 并不是正确的行数（记录数），而是 INSERT 的时候的B-Tree 的相关数。 如要知道表中的行数（记录数），要如下：
cmd.CommandText = "SELECT count(*) FROM t1"; sr = cmd.ExecuteReader(); sr.Read(); Console.WriteLine(sr.GetInt32(0).ToString()); sr.Close();
十九、事务
事务就是对数据库一组按逻辑顺序操作的执行单元。用事务的好处就是成熟的数据库都对 密集型的磁盘 IO 操作之类进行优化，而且还能进行撤回回滚操作。其实在上面改变列名的示例中就用过。
//---事务 static void TransActionOperate(SQLiteConnection cn,SQLiteCommand cmd) { using (SQLiteTransaction tr = cn.BeginTransaction()) { string s = ""; int n = 0; cmd.CommandText = "INSERT INTO t2(id,score) VALUES(@id,@score)"; cmd.Parameters.Add("id", DbType.String); cmd.Parameters.Add("score", DbType.Int32); for (int i = 0; i < 10; i++) { s = i.ToString(); n = i; cmd.Parameters[0].Value = s; cmd.Parameters[1].Value = n; cmd.ExecuteNonQuery(); } tr.Commit(); } }
二十、整理数据库
SQLite 的自带命令 VACUUM。用来重新整理整个数据库达到紧凑之用，比如把删除的彻底删掉等等。
cmd.CommandText = "VACUUM"; cmd.ExecuteNonQuery(); ———————————————— 版权声明：本文为CSDN博主「码界一棵葱」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/lyyybz/article/details/109223982