SQLite常用命令及编程接口介绍

sqlite常用命令及编程接口介绍一、常用命令介绍在终端下运行sqlite3*.db，出现如下提示符SQLiteversion3.7.2Enter“.help”forinstructionsEnterSQLstatementsterminatedwitha“;”sqlite*.db是要打开的数据库文件。若该文件不存在，则自动创建。显示所有命令sqlite.help退出sqlite3sqlite.quit显示当前打开的数据库文件sqlite.database显示数据库中所有表名sqlite.tables查看表的结构sqlite.schematable_name/*******************************************/以下为SQL命令，每个命令以;结束创建新表createtabletable_name(f1type1,f2type2,…);sqlitecreatetablestudent(nointprimarykey,nametext,scorereal);sqlitecreatetablestu(nointprimarykey,nametextnotnull,scorereal);备注：1）默认字段值可以为空；2）下面的命令，sqlite3不支持paper_name+author_id构成复合主键createtablepaper(paper_namevarchar(50)notnull,author_idchar(10)notnull,//…..constraintPK_paperprimarykey(paper_name,author_id)--复合主键)删除表sqlitedroptabletable_namesqlitedroptablestudent查询表中所有记录sqliteselect*fromtable_name;按指定条件查询表中记录sqliteselect*fromtable_namewhereexpression；sqliteselect*fromstudentsqliteselect*fromstudentwherescoreisnotnull;sqliteselect*fromstudentwherename=’zhao’sqliteselect*fromstudentwherename=’zhao’andscore=95sqliteselectid,namefromstudentwherename=’zhao’andscore=95sqliteselectcount(*)fromstudentwherescore90sqliteselect*fromstudentorderbyscoredesc;sqliteselect*fromstudentorderbyscoreasc;向表中添加新记录sqliteinsertintotable_namevalues(value1,value2,…);sqliteinsertintostudentvalues(1,‘zhao’,92);sqliteinsertintostudent（no,name）values(2,‘li’);按指定条件删除表中记录sqlitedeletefromtable_namewhereexpressionsqlitedeletefromstudentwherescore60;更新表中记录sqliteupdatetable_namesetf1=value1,f2=value2…whereexpression;sqliteupdatestudentsetscore=0;sqliteupdatestudentsetname=’sun’whereno=3;sqliteupdatestudentsetscore=100,name='chenyong'whereno=2;在表中添加字段sqlitealtertabletableaddcolumnfieldtype;sqlitealtertablestudentaddcolumngenderintegerdefault0;在表中删除字段Sqlite中不允许删除字段，可以通过下面步骤达到同样的效果sqlitecreatetablestuasselectno,name,scorefromstudentsqlitedroptablestudentsqlitealtertablesturenametostudent二、常用编程接口介绍1)intsqlite3_open(char*path,sqlite3**db)；功能：打开sqlite数据库path：数据库文件路径db：指向sqlite句柄的指针返回值：成功返回0，失败返回错误码(非零值)2)intsqlite3_close(sqlite3*db);功能：关闭sqlite数据库返回值：成功返回0，失败返回错误码3)constchar*sqlite3_errmg(sqlite3*db);返回值：返回错误信息4)typedefint(*sqlite3_callback)(void*,int,char**,char**);intsqlite3_exec(sqlite3*db,constchar*sql,sqlite3_callbackcallback,void*,char**errmsg);功能：执行SQL操作db：数据库句柄sql：SQL语句callback：回调函数errmsg：错误信息指针的地址返回值：成功返回0，失败返回错误码不需要回调函数的情况：有关插入或更新的sql语句。if(sqlite3_exec(db,“deletefromtable1whereid=1”,NULL,NULL,&errmsg)!=SQLITE_OK){printf(“error:%s\n”,errmsg);exit(-1);}需要回调函数的情况：有关查询的sql语句。intcallback(void*para,intf_num,char**f_value,char**f_name){inti;printf(“*****************************\n”);for(i=0;if_num;i++){printf(“%s:%s\n”,f_name[i],f_value[i]);}return0;}if(sqlite3_exec(db,“select*fromtable”,callback,NULL,&errmsg)!=SQLITE_OK){printf(“error:%s\n”,errmsg);exit(-1);}代码输出如下：no:2name:zhaoscore:86no:3name:wangscore:86不使用回调函数执行SQL语句:if(sqlite3_get_table(db,“select*fromtable”,&resultp,&nrow,&ncolumn,&errmsg)!=SQLITE_OK){printf(“error:%s\n”,errmsg);exit(-1);}index=ncolumn;//第一条记录的第一个字段的下标for(i=0;inrow;i++){for(j=0;jncolumn;j++){printf(“%s:%s\n”,resultp[j],resultp[index++]);}}for(i=0;i(nrow+1)*ncolumn;i++){printf(“%s”,resultp[i];}Printf(“\n”);代码输出如下：no:2name:zhaoScore:86no:3name:wangScore:86nonamescore2zhao863wang86EXECsp_rename'customers.[contacttitle]','title','COLUMN'