iOS sqlite对数据库的各种操作(日常整理全)

--数据库的建立create table team(　-- 创建名字为team的table　　stu_id integer primary key autoincrement, 　　stu_name varchar(100), 　　stu_password varchar(100), 　　stu_login varchar(100) )--添加信息 insert into team(stu_name,stu_password,stu_login) values('xiaming','123456','xm') insert into team(stu_name,stu_password,stu_login) values('zhangsan','123456',' zs') --查询信息select *from team --删除信息delete from team where stu_id=3 

#import <UIKit/UIKit.h>#import <sqlite3.h>@interface ViewController : UIViewController@property(strong,nonatomic)UIButton *showbtn;@property(strong,nonatomic)UIButton *insertbtn;@property(strong,nonatomic)UIButton *updatebtn;@property(strong,nonatomic)UIButton *deletebtn;@end 

#import "ViewController.h"#define PATH [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"team.sqlite"]@interface ViewController ()@end@implementation ViewController- (void)viewDidLoad {[super viewDidLoad];// 获取沙盒 Documents文件路径NSLog(@"%@",[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]);[self button];}-(void)button{self.showbtn=[UIButton buttonWithType:UIButtonTypeSystem];self.showbtn.frame=CGRectMake(100, 50, 200, 50);[self.showbtn setTitle:@"数据库显示" forState:UIControlStateNormal];[self.showbtn addTarget:self action:@selector(showSql) forControlEvents:UIControlEventTouchUpInside];[self.view addSubview:self.showbtn];self.insertbtn=[UIButton buttonWithType:UIButtonTypeSystem];self.insertbtn.frame=CGRectMake(100, 100, 200, 50);[self.insertbtn setTitle:@"数据库添加" forState:UIControlStateNormal];[self.insertbtn addTarget:self action:@selector(insertSql) forControlEvents:UIControlEventTouchUpInside];[self.view addSubview:self.insertbtn];self.updatebtn=[UIButton buttonWithType:UIButtonTypeSystem];self.updatebtn.frame=CGRectMake(100, 150, 200, 50);[self.updatebtn setTitle:@"数据库修改" forState:UIControlStateNormal];[self.updatebtn addTarget:self action:@selector(updateSql) forControlEvents:UIControlEventTouchUpInside];[self.view addSubview:self.updatebtn];self.deletebtn=[UIButton buttonWithType:UIButtonTypeSystem];self.deletebtn.frame=CGRectMake(100, 200, 200, 50);[self.deletebtn setTitle:@"数据库删除" forState:UIControlStateNormal];[self.deletebtn addTarget:self action:@selector(deleteSql) forControlEvents:UIControlEventTouchUpInside];[self.view addSubview:self.deletebtn];}#pragma mark - 显示数据表中的所有信息-(void)showSql{NSLog(@"显示数据库信息");// 数据库sqlite3 *db;// 根据指定的数据库文件存储路径打开数据库int result=sqlite3_open([PATH UTF8String], &db);// 创建执行命令对象sqlite3_stmt *stmt;// 打开数据库成功if (result==SQLITE_OK) {NSLog(@"连接成功");// 执行预处理命令int res=sqlite3_prepare_v2(db, "select *from team", -1, &stmt, nil);if (res==SQLITE_OK) {// 循环遍历数据表的行信息while (sqlite3_step(stmt)==SQLITE_ROW) {// 获取数据表中整型列的信息int stu_id=sqlite3_column_int(stmt, 0);NSLog(@"stu_id is %d",stu_id);// 获取数据表中字符型的列的信息NSLog(@"%@",[NSString stringWithFormat:@"%s",sqlite3_column_text(stmt, 1)]);NSLog(@"%@",[NSString stringWithFormat:@"%s",sqlite3_column_text(stmt, 2)] );NSLog(@"%@",[NSString stringWithFormat:@"%s",sqlite3_column_text(stmt, 3)] ); }}}}#pragma mark -增加信息-(void)insertSql{sqlite3 *db;sqlite3_stmt *stmt;sqlite3_open([PATH UTF8String], &db);int rst=sqlite3_prepare_v2(db, "insert into team(stu_name,stu_password,stu_login) values(?,?,?)", -1, &stmt, nil);sqlite3_bind_text(stmt, 1, "wangwu", -1, nil);sqlite3_bind_text(stmt, 2, "123456", -1, nil);sqlite3_bind_text(stmt, 3, "ww", -1, nil);// 判断是否增加成功if (rst==SQLITE_OK) {if (SQLITE_DONE==sqlite3_step(stmt)) {NSLog(@"add ok");}else{NSLog(@"add fail");}}}#pragma mark - 修改数据库-(void)updateSql{sqlite3 *db;sqlite3_stmt *stmt;sqlite3_open([PATH UTF8String], &db);// 方法一/*int res = sqlite3_prepare_v2(db, "update team set stu_name=(?),stu_password=(?),stu_login=(?) where stu_id=2" , -1, &stmt, nil);sqlite3_bind_text(stmt, 1, "xiaoming", -1, nil);sqlite3_bind_text(stmt, 2, "123456", -1, nil);sqlite3_bind_text(stmt, 3, "xm", -1, nil);*/// 方法二int rst=sqlite3_prepare_v2(db, "update team setstu_name='zl',stu_password='zl123',stu_login='zhangsan' where stu_id=4", -1, &stmt, nil);// 判断是否修改成功if (rst==SQLITE_OK) {if (SQLITE_DONE == sqlite3_step(stmt)) {NSLog(@" update ok");}else{NSLog(@"update fail");}}}-(void)deleteSql{sqlite3 *db;sqlite3_stmt *stmt;sqlite3_open([PATH UTF8String], &db);int rst=sqlite3_prepare_v2(db, "delete from team where stu_id=9", -1, &stmt, nil);// 判断是否删除成功if (rst==SQLITE_OK) {if (SQLITE_DONE==sqlite3_step(stmt)) {NSLog(@" delete ok");}else{NSLog(@"delete fail");}}}- (void)didReceiveMemoryWarning {[super didReceiveMemoryWarning];// Dispose of any resources that can be recreated.}@end