你的位置:首页 > 操作系统

[操作系统]iOS sqlite 的各种操作


iOS --SQL的增加、删除、查找、修改

iOS对于数据库的操作:增加、删除、查找、修改

首先需要创建一个数据库:本程序的数据库是在火狐浏览器里的插件里写的微量型数据库

火狐找查找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
复制代码