iOS开发 ----- FMDB使用

SQLlite数据库操作

采用FMDB作为第三方库,进行数据库操作

Demo地址

1 在沙盒目录下创建数据库 名为userInfo

NSString * path = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/userInfo.sqlite"];

NSLog(@"%@",path);

_dataBase = [[FMDatabase alloc]initWithPath:path];

1 创建名为user的表

1.1 首先打开数据库,然后创建user表

1. id字段为自增字段
2. name char 256位
3. age char 3位
4. sex char 2位
5. phone char 13位
6. address char 100位的

if ([_dataBase open]) {
    //sql语句
    static NSString * creatDB = @"create table if not exists user(id integer primary key autoincrement,name varchar(256),age varchar(3),sex varchar(2),phone varchar(13),address varchar(100))";
    //创建表,返回bool值,成功打印下,不成功打印报错信息
    if ([_dataBase executeUpdate:creatDB]) {
        NSLog(@"create success");
    }else
    {
        NSLog(@"create error : %@",[_dataBase lastErrorMessage]);
    }

}

2 查找表

2.1

-(Person *)searchWithName:(NSString *)personName
{
     //sql 语句
    static NSString * searchQuery = @"select * from user where name=?";
    //返回FMResultSet类型,类似一个结果集
    FMResultSet * resultSet = [_dataBase executeQuery:searchQuery,personName];
    //如果找到的话,那么next返回为真,则存到模型中,然后返回,否则返回nil
    if ([resultSet next]) {

        Person * person = [[Person alloc]init];
        person.personName = [resultSet stringForColumn:@"name"];
        person.personAge =[resultSet stringForColumn:@"age"];
        person.personSex = [resultSet stringForColumn:@"sex"];
        person.personPhone = [resultSet stringForColumn:@"phone"];
        person.personAddress = [resultSet stringForColumn:@"address"];
        return person;
    }else
    {
        return nil;
    }
}

3 插入数据

//sql语句
static NSString * insterQuery = @"insert into user (name,age,sex,phone,address) values(?,?,?,?,?)";

//首先查找,如果数据不存在,则插入    
if ([self searchWithName:persoon.personName]) {
    NSLog(@"user alerdy exists");
}else
{
//插入数据,如果不成功,打印报错信息
    if ([_dataBase executeUpdate:insterQuery,persoon.personName,persoon.personAge,persoon.personSex,persoon.personPhone,persoon.personAddress]) {

        NSLog(@"insert success");
    }else
    {
        NSLog(@"insert error : %@",[_dataBase lastErrorMessage]);
    }
}

4 删除数据

//sql语句,根据名字删除
static NSString * delQuery = @"delete from user where name=?";
//如果存在的话,进行删除    
if ([self searchWithName:personName]) {


    if ([_dataBase executeUpdate:delQuery,personName]) {
        NSLog(@"del success ");
    }else
    {
        NSLog(@"del error : %@",[_dataBase lastErrorMessage]);
    }
}else
{
    NSLog(@"user is no exists");
}

5 修改数据

//sql语句
static NSString * modifyQuery = @"update user set name=?,age=?,sex=?,phone=?,address=? where name=?";
//如果信息存在,则修改,否则不修改
if ([self searchWithName:person.personName]) {
    if ([_dataBase executeUpdate:modifyQuery,person.personName,person.personAge,person.personSex,person.personPhone,person.personAddress,person.personName]) {
        NSLog(@"modify success");
    }else
    {
        NSLog(@"modify error : %@",[_dataBase lastErrorMessage]);
    }
}else
{
    NSLog(@"user is not exists");
}

6 查找所有数据

-(NSArray *)serachAll
{
    //sql 语句
    static NSString * searchQuery = @"select * from user";
    //返回一个结果集
    FMResultSet * resultSet = [_dataBase executeQuery:searchQuery];
    NSMutableArray * array = [[NSMutableArray alloc]init];
    //当next位真的时候,根据字段取值,然后存入模型,然后返回
    while ([resultSet next]) {

        Person * person = [[Person alloc]init];
        person.personName = [resultSet stringForColumn:@"name"];
        person.personAge =[resultSet stringForColumn:@"age"];
        person.personSex = [resultSet stringForColumn:@"sex"];
        person.personPhone = [resultSet stringForColumn:@"phone"];
        person.personAddress = [resultSet stringForColumn:@"address"];
        [array addObject:person];

    }
    return array;
}

7 删除数据

这里解释一下,举个例子,在userInfo.sqlite中创建了一个user表是这样的

字段	类型	长度
id	自动增长	主键
name	varchar	256
age	varchar	3
sex	varchar	2
phone	varchar	13
address	varchar	100

创建之后,系统其实还会创建一个表式名为sqlite_sequence,结构式这样的,其中name的值,就是的数据就是上边user表,seq的值就是就是user中id的最新值,比如增删改查之后user表中最后一条数据的id是9,那么这里的seq也是9,如果要清空user表的话,单单delete from user 这一句是不够的,要把这里的seq字段置为0,确保id以后新创建的还是从0开始自动增长,所以要执行update sqlite_sequence set seq=0 where name='user',别忘了user外边的引号,否则语句执行不成功

字段	值
name	user
seq	0

-(void)delectAll
{
    //sql语句,清空表
    static NSString * delQuery = @"delete from user";
    //sql语句自动增长位0
    static NSString * setZero = @"update sqlite_sequence set seq=0 where name='user'";
    if ([_dataBase executeUpdate:setZero] && [_dataBase executeUpdate:delQuery]) {
        NSLog(@"del success");
    }else
    {
        NSLog(@"del error : %@",[_dataBase lastErrorMessage]);
    }

}

8 总结

8.1 创建表

create table if not exists user(id integer primary key autoincrement,name varchar(256),age varchar(3),sex varchar(2),phone varchar(13),address varchar(100))

8.2 删除数据

delete from user where name=?

8.3 修改数据

update user set name=?,age=?,sex=?,phone=?,address=? where name=?

8.4 查找单个数据

select * from user where name=?

8.5 查找全部数据

select * from user

8.6 删除表

delete from user
update sqlite_sequence set seq=0 where name='user'