想要使用sqlite3数据库 , 需要添加第三方库,FMDB ,然后添加系统库 libsqlite3.dylib
sqlite3 数据库的优点:很小(只有几兆),功能强大,弱类型
语法:
打开或者创建数据库: sqlite3 data.db
创建表: create table USER(uid , name , score);
不过一般使用这种方法: create table if not exists USER(uid , name , score)
创建主键、唯一键、自增 : create table if not exists USER ( uid integer primary key autoincrement , name , score);
删除表 : drop table USER;
添加数据: insert into USER ( uid , name , score ) values ( 1 , @“老罗” , 66);
insert into USER values (2 , @“郭靖” , 11) ;
查询: select * from USER;
条件查询: select name from USER;
select * from USER where uid=3;
select name from USER where score=49 or score=59;
select name from USER where score=49 and uid=3;
select * from USER limit 2; 两条数据
select * from USER order by score desc;
select * from USER order by score asc;
select * from USER order by score desc limit 3; 分数前三名
select count(*) from USER; 返回有多少条数据(返回的也是一个表,是个单行单列的表)
select sum(score) from USER; 总分
删除表中数据: delete from USER where uid=5;
更改数据: update USER set score=99 where uid=4;
两个表关键查询: select USER.uid , USER.name , WUGONG.score from USER , WUGONG where WUGONG.uid=USER.uid;
下面是程序中对数据库的基本操作:
//stringByAppendingPathComponent: 专门拼接路径的字符串方法,不管前面路径是否有“/”,后面要拼接的字符串都不需要添加"/"
NSString *path = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/data.db"];
_db = [[FMDatabase alloc] initWithPath:path];
//打开数据库
BOOL res = [_db open];
if (res == NO) {
NSLog(@"打开失败");
return;
}
//创建表
res = [_db executeUpdate:@"create table if not exists USER(id integer primary key autoincrement,name,score,image)"];
if (res == NO) {
NSLog(@"创建表失败");
}
//关闭数据库
[_db close];
//选择照片
_imageView.userInteractionEnabled = YES;
UITapGestureRecognizer *tap = [[UITapGestureRecognizer alloc] initWithTarget:self action:@selector(tap:)];
[_imageView addGestureRecognizer:tap];
[tap release];
轻触按钮触发的方法
-(void)tap:(UITapGestureRecognizer*)tap
{
//照片选择器 相册,相机
UIImagePickerController *ipc = [[UIImagePickerController alloc] init];
ipc.delegate = self;
//来源 默认是从从相册拿
ipc.sourceType = UIImagePickerControllerSourceTypePhotoLibrary;
[self presentViewController:ipc animated:YES completion:nil];
[ipc release];
}
//选择图片
-(void)imagePickerController:(UIImagePickerController *)picker didFinishPickingMediaWithInfo:(NSDictionary *)info
{
//拿到选择的图片
_imageView.image = [info objectForKey:UIImagePickerControllerOriginalImage];
[picker dismissViewControllerAnimated:YES completion:nil];
}
//取消调用
-(void)imagePickerControllerDidCancel:(UIImagePickerController *)picker
{
[picker dismissViewControllerAnimated:YES completion:nil];
}
//增
-(void)add:(id)sender
{
if (_imageView.image == nil || _nameField.text == nil || _scoreField.text == nil) {
return;
}
//FMDB支持类型 NSString NSNumber NSData
NSData *imageData = UIImageJPEGRepresentation(_imageView.image, 1);
//大量图片的时候,不会把image发在数据库中,而是把它们放在沙盒里,数据库里存的时image的路径
NSString *name = _nameField.text;
NSNumber *score = [NSNumber numberWithInt:[_scoreField.text intValue]];
//打开数据库
BOOL res = [_db open];
if (res == NO) {
NSLog(@"打开失败");
}
//插入数据
res = [_db executeUpdate:@"insert into USER(name,score,image) values(?,?,?)",name,score,imageData];
if (res == NO) {
NSLog(@"插入数据失败");
}
//关闭数据库
[_db close];
}
//删
-(void)del:(id)sender
{
BOOL res = [_db open];
if (res == NO) {
NSLog(@"打开失败");
return;
}
res = [_db executeUpdate:@"delete from USER where name=?",_nameField.text];
if (res == NO) {
NSLog(@"删除失败");
}
[_db close];
}
//查
-(void)fetch:(id)sender
{
BOOL res = [_db open];
if (res == NO) {
NSLog(@"打开失败");
return;
}
FMResultSet *set = [_db executeQuery:@"select * from USER"];
while ([set next]) {
//取值
NSString *name = [set stringForColumn:@"name"];
int score = [set intForColumn:@"score"];
// NSLog(@"%@",[set objectForColumnName:@"id"]);
NSLog(@"名字:%@,分数:%d",name,score);
//[set objectForColumnName:@"name"];
}
[_db close];
}
//改
-(void)update:(id)sender
{
BOOL res = [_db open];
if (res == NO) {
NSLog(@"打开失败");
return;
}
res = [_db executeUpdate:@"update USER set name=?,score=? where id=?",_nameField.text,_scoreField.text,[NSNumber numberWithInt:3]];
if (res == NO) {
NSLog(@"更新失败");
}
[_db close];
}