SQLlite数据库操作
采用FMDB作为第三方库,进行数据库操作
Demo地址
1 在沙盒目录下创建数据库 名为userInfo
NSString * path = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/userInfo.sqlite" ];
NSLog (@"%@" ,path);
_dataBase = [[FMDatabase alloc]initWithPath:path];
1 创建名为user的表
1.1 首先打开数据库,然后创建user表
id字段为自增字段 name char 256位 age char 3位 sex char 2位 phone char 13位 address char 100位的
if ([_dataBase open]) {
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))" ;
if ([_dataBase executeUpdate:creatDB]) {
NSLog (@"create success" );
}else
{
NSLog (@"create error : %@" ,[_dataBase lastErrorMessage]);
}
}
2 查找表
2.1
-(Person *)searchWithName:(NSString *)personName
{
static NSString * searchQuery = @"select * from user where name=?" ;
FMResultSet * resultSet = [_dataBase executeQuery:searchQuery,personName];
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 插入数据
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 删除数据
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 修改数据
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
{
static NSString * searchQuery = @"select * from user" ;
FMResultSet * resultSet = [_dataBase executeQuery:searchQuery];
NSMutableArray * array = [[NSMutableArray alloc]init];
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外边的引号,否则语句执行不成功
-(void )delectAll
{
static NSString * delQuery = @"delete from user" ;
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'