iOS开发之SQLite
在实际工作中用得最多的是FMDB
SQLite
数据类型
NULL
Integer
Real 包括- float-double
Text 字符串
BLOB 二进制
增删改查
创建一个表
creat table strdent(ID integer primary key,name text,phone text,gender text);
creat table(表名)
插入数据
insert into student(name,phone) values('kallen','15101178513')
更新数据
update student set name = 'Alex' where s_id = 1
删除数据
delete from student where s_id = 2
查询分 单表查询和多表查询
查找数据
select 字段1,字段2,… from 表名 where 条件
select name,phone from student where s_id = 1
查找有多少数据
select count(s_id) from student
起别名
select count(s_id) as count from student
查询某个字有多少个 '%某个字%'
select name from student where name like '%e%'
新建工程,添加SQLite框架. 选择3.0.dylib... 为什么不选另一个这里不讨论
把数据库添加到工程里,,,
详细代码如下
1 // 2 // AppDelegate.h 3 // SQlite 4 // 5 // Created by VincentXue on 12-8-24. 6 // Copyright (c) 2012年 VincentXue. All rights reserved. 7 // 8 9 #import <UIKit/UIKit.h> 10 11 @interface AppDelegate : UIResponder <UIApplicationDelegate> 12 13 @property (strong, nonatomic) UIWindow *window; 14 15 @end 16 17 18 // 19 // AppDelegate.m 20 // SQlite 21 // 22 // Created by VincentXue on 12-8-24. 23 // Copyright (c) 2012年 VincentXue. All rights reserved. 24 // 25 26 #import "AppDelegate.h" 27 #import "StuListViewController.h" 28 @implementation AppDelegate 29 30 - (void)dealloc 31 { 32 [_window release]; 33 [super dealloc]; 34 } 35 36 - (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions 37 { 38 self.window = [[[UIWindow alloc] initWithFrame:[[UIScreen mainScreen] bounds]] autorelease]; 39 // Override point for customization after application launch. 40 StuListViewController *stuViewController = [[StuListViewController alloc] initWithStyle:UITableViewStylePlain]; 41 self.window.rootViewController = stuViewController; 42 [stuViewController release]; 43 self.window.backgroundColor = [UIColor whiteColor]; 44 [self.window makeKeyAndVisible]; 45 return YES; 46 } 47 48 @end 49 50 51 // 52 // StuListViewController.h 53 // SQlite 54 // 55 // Created by VincentXue on 12-8-24. 56 // Copyright (c) 2012年 VincentXue. All rights reserved. 57 // 58 59 #import <UIKit/UIKit.h> 60 61 @interface StuListViewController : UITableViewController 62 { 63 NSArray *stuList; 64 } 65 @end 66 67 68 69 // 70 // StuListViewController.m 71 // SQlite 72 // 73 // Created by VincentXue on 12-8-24. 74 // Copyright (c) 2012年 VincentXue. All rights reserved. 75 // 76 77 #import "StuListViewController.h" 78 #import "student.h" 79 @interface StuListViewController () 80 81 @end 82 83 @implementation StuListViewController 84 85 - (id)initWithStyle:(UITableViewStyle)style 86 { 87 self = [super initWithStyle:style]; 88 if (self) { 89 // Custom initialization 90 stuList = [Student findAllStu]; 91 [stuList retain]; 92 NSLog(@"stuList = %@",stuList); 93 } 94 return self; 95 } 96 97 - (void)viewDidLoad 98 { 99 [super viewDidLoad]; 100 101 // Uncomment the following line to preserve selection between presentations. 102 // self.clearsSelectionOnViewWillAppear = NO; 103 104 // Uncomment the following line to display an Edit button in the navigation bar for this view controller. 105 // self.navigationItem.rightBarButtonItem = self.editButtonItem; 106 } 107 108 - (void)viewDidUnload 109 { 110 [super viewDidUnload]; 111 // Release any retained subviews of the main view. 112 // e.g. self.myOutlet = nil; 113 } 114 - (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section 115 { 116 return [stuList count]; 117 } 118 - (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath 119 { 120 static NSString *CellIndentifier = @"Cell"; 121 UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIndentifier]; 122 if (cell == nil) { 123 cell = [[[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIndentifier]autorelease]; 124 } 125 Student *s = [stuList objectAtIndex:indexPath.row]; 126 cell.textLabel.text = s.name; 127 return cell; 128 } 129 - (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation 130 { 131 return (interfaceOrientation == UIInterfaceOrientationPortrait); 132 } 133 134 #pragma mark - Table view data source 135 136 - (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView 137 { 138 139 // Return the number of sections. 140 return 1; 141 } 142 143 144 145 // 146 // Student.h 147 // SQlite 148 // 149 // Created by VincentXue on 12-8-24. 150 // Copyright (c) 2012年 VincentXue. All rights reserved. 151 // 152 153 #import <Foundation/Foundation.h> 154 155 @interface Student : NSObject 156 { 157 int _s_id; 158 NSString *_name; 159 NSString *_phone; 160 } 161 @property (assign,nonatomic)NSInteger s_id; 162 @property (retain,nonatomic)NSString *name; 163 @property (retain,nonatomic)NSString *phone; 164 165 - (id)initWithID:(int)aID Name:(NSString *)aName Phone:(NSString *)aPhone; 166 167 + (NSArray *) findAllStu; 168 + (BOOL)deleteFronStudent:(int)aID; 169 @end 170 171 172 // 173 // Student.m 174 // SQlite 175 // 176 // Created by VincentXue on 12-8-24. 177 // Copyright (c) 2012年 VincentXue. All rights reserved. 178 // 179 180 #import "Student.h" 181 #include <sqlite3.h> 182 #import "DBHelper.h" 183 184 @implementation Student 185 @synthesize name = _name; 186 @synthesize phone = _phone; 187 @synthesize s_id = _s_id; 188 - (id)initWithID:(int)aID Name:(NSString *)aName Phone:(NSString *)aPhone 189 { 190 self = [super init]; 191 if (self) { 192 self.s_id = aID; 193 self.name = aName; 194 self.phone = aPhone; 195 196 } 197 return self; 198 } 199 + (NSArray *) findAllStu 200 { 201 sqlite3 *db = [DBHelper DBOpen]; 202 sqlite3_stmt *stmt = nil; //数据集 203 int result; 204 result = sqlite3_prepare(db, "select * from student", -1, &stmt, NULL); 205 206 if (result == SQLITE_OK) 207 { 208 NSMutableArray *stuArray = [NSMutableArray array]; 209 while (sqlite3_step(stmt) == SQLITE_ROW) //当还有下一行继续执行 210 { 211 int aID = sqlite3_column_int(stmt, 0); //提取第1列 212 const unsigned char *aName = sqlite3_column_text(stmt, 1); 213 const unsigned char *aPhone = sqlite3_column_text(stmt, 2); 214 215 NSString *_name = [NSString stringWithCString:(const char *)aName encoding:NSUTF8StringEncoding]; 216 NSString *_phone = [NSString stringWithCString:(const char *)aPhone encoding:NSUTF8StringEncoding]; 217 218 Student *s = [[Student alloc] initWithID:aID Name:_name Phone:_phone]; 219 220 [stuArray addObject:s]; 221 [s release]; 222 } 223 sqlite3_finalize(stmt); //关闭sqlite3 224 return stuArray; 225 } 226 sqlite3_finalize(stmt);//如果不成功,返回空; 227 return nil; 228 } 229 230 + (BOOL)deleteFronStudent:(int)aID //从student表里面删除数据 231 { 232 sqlite3 *db = [DBHelper DBOpen]; 233 sqlite3_stmt *stmt = nil; 234 int result; 235 result = sqlite3_prepare(db, "delete from student where s_id = ?", -1, &stmt, NULL); 236 BOOL seccess = NO; 237 if (result == SQLITE_OK) 238 { 239 //绑定sql语句需要的参数; 240 sqlite3_bind_int(stmt, aID, 1);// ?从1开始拍,1赋给? 241 if (sqlite3_step(stmt) == SQLITE_DONE) { 242 seccess = YES; 243 } 244 } 245 return YES; 246 } 247 248 - (void)dealloc 249 { 250 [_phone release],_phone = nil; 251 [_name release],_name = nil; 252 [super dealloc]; 253 } 254 @end 255 256 257 258 // 259 // DBHelper.h 260 // SQlite 261 // 262 // Created by VincentXue on 12-8-24. 263 // Copyright (c) 2012年 VincentXue. All rights reserved. 264 // 265 266 #import <Foundation/Foundation.h> 267 #include <sqlite3.h> 268 @interface DBHelper : NSObject 269 270 + (sqlite3 *)DBOpen; 271 + (void)close; 272 @end 273 274 275 // 276 // DBHelper.m 277 // SQlite 278 // 279 // Created by VincentXue on 12-8-24. 280 // Copyright (c) 2012年 VincentXue. All rights reserved. 281 // 282 283 #import "DBHelper.h" 284 #include <sqlite3.h> 285 286 static sqlite3 *db = nil; 287 @implementation DBHelper 288 289 + (sqlite3 *)DBOpen 290 { 291 if (db) { 292 return db; 293 } 294 // bundle路径 295 NSString *bundlePath = [[NSBundle mainBundle] pathForResource:@"32StuSB" ofType:@"sqlite"]; 296 297 NSString *documentPath = [NSHomeDirectory()stringByAppendingPathComponent:@"Documents"]; 298 //目标路径 299 NSString *targetPath = [documentPath stringByAppendingPathComponent:@"32StuSB.sqlite"]; 300 NSFileManager *fm = [NSFileManager defaultManager]; 301 BOOL success = NO; 302 303 if ([fm fileExistsAtPath:targetPath] == NO) 304 { 305 success = [fm copyItemAtPath:bundlePath toPath:targetPath error:nil]; 306 307 if (success == YES) 308 { 309 sqlite3_open([targetPath UTF8String], &db); 310 } 311 } 312 sqlite3_open([targetPath UTF8String], &db); 313 return db; 314 } 315 //关闭数据库 316 + (void)close 317 { 318 if (db) { 319 sqlite3_close(db); 320 } 321 } 322 @end