常见的sqlite3 语句
sqliteDataBase
使用数据库:sqlite3 db_name
创建表： create table table_name(filed1Name filed1Property,filed2Name filed2Property);
显示数据库中的表：.table
显示表结构：.schema table_name
删除表: .drop table table_name
退出：.exit
导入数据：将txt文件中的数据到导入到表中,data.txt中每一列用”|”分隔,|两边不要空格,文件末尾不要有空行，例如：1|rainkey|tencent
注意：在android 上面执行的时候将text_name.txt存放到databases所在目录
.import text_name.txt table_name
插入记录：insert into table_name values(‘key’,’value’);
查找记录：select * from table_name;
从第三行开始查询10条记录：select * from table_name limit 3,10;
删除记录：delete from table_name where field1=value;
更新操作：update table_name set name=’rainkey’ where id=1;
int OpenDB(sqlite3** db)
{
int result = sqlite3_open(PATH,db);
if(result!=0)
printf("Open DataBase Fail,Result Code:%d \n",result);
else
printf("Open DataBase Sucess\n");
return result;
}
int CloseDB(sqlite3* db)
{
int result =sqlite3_close(db);
if(result!=0)
printf("Close DataBase fail,ResultCode:%d \n",result);
else
printf("Close DataBase Success\n");
return result;
}
//创建表:
void CreateTable()
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
char * ErrMessage = NULL;
char * str_CreateTable="Create Table Student ( studentID INTEGER,studentName Text,HeadImage Blob)";//创建表的sql语句
int rc =0;
if(OpenDB(&db)!=sqlITE_OK)
return;
rc =sqlite3_exec(db,str_CreateTable,&ErrMessage);//执行
if(rc!=sqlITE_OK)
{
printf("创建数据库失败!ErrorMessge:%s\n",ErrMessage);
return;
}
sqlite3_finalize(stmt);
CloseDB(db);
}
//Insert操作
void InsertOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
const char * Error =NULL;
char * str_Insert="Insert Into Student ( [studentID],[studentName],[HeadImage]) values(1,'Alex',?)";//sql语句
int rc =0;
if(OpenDB(&db)!=sqlITE_OK)
return;
rc =sqlite3_prepare(db,str_Insert,-1,&stmt,&Error);
if(rc!=sqlITE_OK)
{
printf("准备执行插入语句失败！Result Code:%d \n ErrorMessage:%s",rc,Error);
return;
}
char _headImage[3] = {0x41,0x42,0x43};
sqlite3_bind_blob(stmt,1,&_headImage,3,NULL);//二进制数据 //进行数据绑定
rc =sqlite3_step(stmt);
if(rc!=sqlITE_DONE)
{
printf("插入失败！Result Code:%d ErrorMessage:%s",Error);
return;
}
printf("插入成功!\n");
CloseDB(db);
}
//update操作
void UpdateOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
const char * Error =NULL;
char * str_Update="update Student set [HeadImage] = ? where [studentID] = 1";//sql语句
int rc =0;
if(OpenDB(&db)!=sqlITE_OK)
return;
rc =sqlite3_prepare(db,str_Update,&Error);
if(rc!=sqlITE_OK)
{
printf("准备执行更新语句失败！Result Code:%d \n ErrorMessage:%s",Error);
return;
}
char _headImage[3] = {0x41,0)">二进制数据
rc =sqlite3_step(stmt);
if(rc!=sqlITE_DONE)
{
printf("Update失败！Result Code:%d ErrorMessage:%s",Error);
return;
}
printf("Update成功!\n");
CloseDB(db);
}
//删除操作
void DeleteOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
const char * Error =NULL;
char * str_Update="Delete From Student where [studentID] =1";//sql printf("准备执行删除语句失败！Result Code:%d \n ErrorMessage:%s",Error);
return;
}
rc =sqlite3_step(stmt);
printf("删除失败！Result Code:%d ErrorMessage:%s",26)"> printf("删除成功!\n");
CloseDB(db);
}
void SelectOperation(void)
{
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
const char * Error =NULL;
char * str_Update="select * From Student ";//sql语句
int rc =0;
if(OpenDB(&db)!=sqlITE_OK)
return;
rc =sqlite3_prepare(db,255)"> if(rc!=sqlITE_OK)
{
printf("准备执行查询语句失败！Result Code:%d \n ErrorMessage:%s",Error);
return;
}
while(1)
{
if(sqlite3_step(stmt)!=sqlITE_ROW)
{
sqlite3_finalize(stmt);
break;
}
int studentid =sqlite3_column_int(stmt,0);
const unsignedchar * studentName = sqlite3_column_text(stmt,1);
const void * image =sqlite3_column_blob(stmt,2);
printf("studentID is %d,Name is %s,HeadImage is %s \n",studentid,studentName,image);
}
CloseDB(db);
}
原文链接：https://www.f2er.com/sqlite/200877.html