sqlite3数据库

想要使用sqlite3数据库 ， 需要添加第三方库，FMDB ，然后添加系统库 libsqlite3.dylib

sqlite3 数据库的优点：很小（只有几兆），功能强大，弱类型

语法：

打开或者创建数据库： sqlite3 data.db

创建表： create table USER（uid,name,score）;

不过一般使用这种方法： create table if not exists USER(uid,score)

创建主键、唯一键、自增 ： create table if not exists USER ( uid integer primary key autoincrement,score);

删除表 ： drop table USER;

添加数据： insert into USER ( uid,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,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,image) values(?,?,?)",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",score);

//[set objectForColumnName:@"name"];

}

[_db close];

}

//改

-(void)update:(id)sender

{

BOOL res = [_db open];

if (res == NO) {

NSLog(@" res = [_db executeUpdate:@"update USER set name=?,score=? where id=?",_nameField.text,_scoreField.text,[NSNumber numberWithInt:3]];

if (res == NO) {

NSLog(@"更新失败");

}

[_db close];

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!