SQLite3包的使用（基础）-SQLite3增删改查，打开，关闭函数

常见的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 Sucessn");

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 Successn");

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:%sn",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);

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!