iOS sqlite 的各種操作,iossqlite操作
iOS --SQL的增加、刪除、尋找、修改
iOS對於資料庫的操作:增加、刪除、尋找、修改
首先需要建立一個資料庫:本程式的資料庫是在Firefox瀏覽器裡的外掛程式裡寫的微量型資料庫
Firefox找尋找SQLite Manager的步驟:
第一步:在工具列找到附加組件,點擊進入
第二步:搜尋 SQP,找到並下載,安裝完成之後需要重啟瀏覽器
第三步:在工具只樂觀找到SQLite Manager,點擊開啟
SQLite Manager介面
註:SQLite Manager是微量型的資料庫編程軟體,所以一次只能執行一句代碼!!!
--資料庫的建立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
工程目錄檔案如下:
這裡需要匯入一個系統內建的檔案libsqlite3.0.tbd
步驟
ViewController.h
#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
ViewController.h
#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