【SQLite】数据库指令

一、连接数据库

connStr = string.Format("Data Source={0};Vertion=3", dataBasePath);

eg：Data Source=D:\mm.db;Vertion=3

二、表格操作

1、新建表

string cmdStr = "CREATE TABLE " + tableName + " (" + colStr + ")";

eg：CREATE TABLE tableName (colName1 integer NOT Null PRIMARY KEY,colName1 integer NOT Null)

2、删除表格

DROP TABLE tableName

3、修改表名

ALTER TABLE tableName RENAME to tableName1

三、列操作

1、往表格新增列

string cmdStr = "ALTER TABLE " + tableName + " ADD " + colName + " " + colType;
if (!allowNull)
{
   cmdStr += " NOT Null";
}
if (isKey)
{
   cmdStr += " PRIMARY KEY";
}

eg：ALTER TABLE tableName ADD colName integer NOT Null PRIMARY KEY

2、删除表格指定列（不支持）

https://blog.csdn.net/Stan1989/article/details/8570187

 

3、修改字段属性（不支持）

https://blog.csdn.net/m0_38059938/article/details/120566402

 

四、数据操作

1、插入数据

string cmdStr = string.Format("insert into {0}({1}) values({2}); select @@IDENTITY as 'Identity'", tableName, colStr, valueStr);

eg：insert into T_Users (username,password,creatdatetime,powerlevel) values('mm','ttt,'2021/12/9 13:51:51',1)

返回自增ID(注意键值内容大小写)：
String sql = "Select seq From sqlite_sequence Where name = '" + tableName + "'";

eg：Select seq From sqlite_sequence Where name = 'T_Users'

2、删除数据

string cmdStr = "delete from " + tableName + " where " + mainKeyName + " = '" + mainKeyValue + "'";

eg：delete from T_Users where username = 'mm'

3、修改数据

string cmdStr = string.Format("update {0} set {1} where {2}" + " = " + "{3};", tableName, colStr, mainKeyName, keyValue);

eg：update T_Users set powerlevel = 2,creatoruid=26 where username = 'b'

4、查询数据

string cmdStr = string.Format("select {0} from {1} {2};", columns, tableName, conditionStr);

eg：select powerlevel from T_Users where powerlevel = 2
eg：select * from T_Users where powerlevel = 2