//sqlite 常用语句

1. [selfopenDataBase];
2. [selfcreateTable];
3. [selfinsertTable];
4. [selfqueryTable];
5. [selfdeleteTable];
6. [selfqueryTable];

实现：

//opendatabase

1. -(void)openDataBase
2. {
3. NSArray*documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES);
4. NSString*databaseFilePath=[[documentsPathsobjectAtIndex:0]stringByAppendingPathComponent:@"db.sql"];
5. if(sqlite3_open([databaseFilePathUTF8String],&database)==sqlITE_OK)
6. {
7. NSLog(@"opensqlitedbok.");
8. }
9. else
10. {
11. NSLog(@"cannotopensqlitedb");
13. //closedatabase
14. sqlite3_close(database);
15. }
16. }

//createtable

1. -(void)createTable
2. {
3. char*errorMsg;
4. constchar*createsql="createtableifnotexistspersons(idintegerprimarykeyautoincrement,nametext)";
6. if(sqlite3_exec(database,createsql,NULL,&errorMsg)==sqlITE_OK)
7. {
8. NSLog(@"createok.");
9. }
10. else
11. {
12. NSLog(@"cannotcreatetable");
13. [selfErrorReport:(NSString*)createsql];
14. }
15. }

//inserttable

1. -(void)insertTable
2. {
3. char*errorMsg;
5. constchar*insertsql="insertintopersons(name)values('田周辉')";
6. if(sqlite3_exec(database,insertsql,&errorMsg)==sqlITE_OK)
7. {
8. NSLog(@"insertok.");
9. }
10. else
11. {
12. NSLog(@"cannotinsertittotable");
13. [selfErrorReport:(NSString*)insertsql];
14. }
15. }

//error

1. -(void)ErrorReport:(NSString*)item
2. {
3. char*errorMsg;
5. if(sqlite3_exec(database,(constchar*)item,&errorMsg)==sqlITE_OK)
6. {
7. NSLog(@"%@ok.",item);
8. }
9. else
10. {
11. NSLog(@"error:%s",errorMsg);
12. sqlite3_free(errorMsg);
13. }
14. }

//query

//delete

命令行：

1. cratetabletableFile(idsmallint,file_namevarchar(256),up_statesmallint,file_sizesmallint);//创建一个表格
2. insertintotableFilevalues(1,"200110101.rcu",100,4500);//插入数据
3. 如何访问sqlite3数据库 ？
4. @H_403_783@@H_403_783@sqlite3_open//打开数据库
5. @H_403_783@@H_403_783@sqlite3_prepare//将UTF－8格式的sql语句转换为指向已备语句的指针
6. @H_403_783@@H_403_783@sqlite3_column_string//返回某一行的字符串
7. @H_403_783@@H_403_783@sqlite3_finalize//删除一条已备语句
8. @H_403_783@@H_403_783@sqlite3_close//关闭数据库

2. -(id)lookupSingularsql:(NSString*)sqlforType:(NSString*)rettype{
3. @H_403_783@sqlite3_stmt*statement;
4. @H_403_783@idresult;
5. @H_403_783@if(statement=[selfprepare:sql]){
6. @H_403_783@@H_403_783@if(sqlite3_step(statement)==sqlITE_ROW){
7. @H_403_783@@H_403_783@if([rettypecompare:@"text"]==NSOrderedSame){
8. @H_403_783@@H_403_783@chartemp_buf[256];
9. @H_403_783@@H_403_783@memset(temp_buf,256);
10. @H_403_783@@H_403_783@sprintf(temp_buf,"%s%s%s%s",(char*)sqlite3_column_text(statement,0),(char*)sqlite3_column_text(statement,1),
11. @H_403_783@ @H_403_783@(char*)sqlite3_column_text(statement,2),3));
12. @H_403_783@@H_403_783@result=[NSStringstringWithUTF8String:temp_buf];
13. @H_403_783@@H_403_783@//result=[NSStringstringWithUTF8String:(char*)sqlite3_column_text(statement,0)];
14. @H_403_783@@H_403_783@}elseif([rettypecompare:@"integer"]==NSOrderedSame){
15. @H_403_783@@H_403_783@result=(id)sqlite3_column_int(statement,0);
16. @H_403_783@@H_403_783@}
17. @H_403_783@}
18. @H_403_783@}
19. @H_403_783@@H_403_783@sqlite3_finalize(statement);
20. @H_403_783@@H_403_783@returnresult;
21. @H_403_783@}

应该有个成员变量，比如我的代码：

1. @interfaceDetailViewController:UIViewController{
2. @H_403_783@UIPopoverController*popoverController;
3. @H_403_783@UIToolbar*toolbar;
4. @H_403_783@iddetailItem;
5. @H_403_783@UILabel*detailDescriptionLabel;
6. @H_403_783@sqlite3*database;

打开数据库

sqlite数据库是文件数据库，是保存在文件系统中的。因此需要知道文件保存到哪里，可参见iOS中对文件的操作。比如本文保存到Documents目录下。代码：

1. NSArray*documentsPaths=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,YES);
2. NSString*databaseFilePath=[[documentsPathsobjectAtIndex:0]stringByAppendingPathComponent:@"mydb"];
3. if(sqlite3_open([databaseFilePathUTF8String],&database)==sqlITE_OK)
4. {
5. @H_403_783@@H_403_783@NSLog(@"opensqlitedbok.");
6. }

通过ssh查看Documents目录，发现mydb文件已经创建。sqlite的策略是如果有该文件就打开，如果没有就创建文件，也就是创建数据库。

这里要注意，使用的是C语法，sqlite3_open传入的是database的地址。

关闭数据库

数据库使用完毕后，要关闭，比如退出应用的时候：

1. -(void)viewDidUnload{
2. @H_403_783@@H_403_783@//Releaseanyretainedsubviewsofthemainview.
3. @H_403_783@@H_403_783@//e.g.self.myOutlet=nil;
4. @H_403_783@@H_403_783@sqlite3_close(database);
5. @H_403_783@@H_403_783@self.popoverController=nil;
6. }

建表语句

数据库打开以后，如果没有表，建表：

1. char*errorMsg;
2. constchar*createsql="createtableifnotexistspersons(idintegerprimarykeyautoincrement,nametext)";
3. if(sqlite3_exec(database,&errorMsg)==sqlITE_OK){
4. @H_403_783@@H_403_783@NSLog(@"createok.");
5. }

这里要特别注意errorMsg传的是地址，因为该函数要通过地址引用来写报错字符信息。

向表中插入记录

和建表语句类似：

1. constchar*insertsql="insertintopersons(name)values(‘张三’)";
2. if(sqlite3_exec(database,&errorMsg)==sqlITE_OK){
3. @H_403_783@@H_403_783@NSLog(@"insertok.");
4. }

错误信息的处理

如果在多个地方使用errorMsg，那么每次使用完毕要清空一下字串，比如这样：

1. if(sqlite3_exec(database,&errorMsg)==sqlITE_OK){
2. @H_403_783@@H_403_783@NSLog(@"createok.");
3. }else{
4. @H_403_783@@H_403_783@NSLog(@"error:%s",errorMsg);
5. @H_403_783@@H_403_783@sqlite3_free(errorMsg);
6. }

查询结果集

结果集的查询，需要用到statement：

1. constchar*selectsql="selectid,namefrompersons";
2. sqlite3_stmt*statement;
3. if(sqlite3_prepare_v2(database,nil)==sqlITE_OK){
4. @H_403_783@@H_403_783@NSLog(@"selectok.");
5. }
6. while(sqlite3_step(statement)==sqlITE_ROW){
7. @H_403_783@@H_403_783@int_id=sqlite3_column_int(statement,0);
8. @H_403_783@@H_403_783@char*name=(char*)sqlite3_column_text(statement,1);
9. @H_403_783@@H_403_783@NSLog(@"row>>id%i,name%s",name);
10. }
11. sqlite3_finalize(statement);

不过这里有个问题，看看打印的日志：