1.0 SQlite3数据库
1.1 终端的命令行使用
sqlite3 name;
.database ;
.table ;
create table name(id integer primary key autoincretment, name varchar(20 ), age smallint);
select * from name;
select name,age from name;
select * from name where id >= 4 ;
insert into name(1 , 'name', 20);
//删除name列表中的数据(where后面加条件)
delete from name where id = 0;
//更新name列表中的数据(set后加修改的内容,where后加条件)
update name set id = 10 where id = 0;
1.2代码
@interface Sqlite3ViewController ()
@property (nonatomic ) sqlite3 *dataBase;
@end
@implementation Sqlite3ViewController
- (void )viewDidLoad {
[super viewDidLoad];
[self .view setBackgroundColor:[UIColor whiteColor]];
NSArray *nameArr = @[@"创建数据列表" , @"插入数据" , @"查询数据" , @"删除数据" , @"修改数据" ];
for (int i=0 ; i<nameArr.count ; i++){
UIButton *btn = [UIButton buttonWithType:UIButtonTypeSystem];
btn.frame = CGRectMake((10 + 100 * (i % 4 )), (80 + i / 4 * 40 ), 50 , 40 );
[btn setTitle:nameArr[i] forState:UIControlStateNormal];
[btn addTarget:self action:@selector (btnAction:) forControlEvents:UIControlEventTouchDown];
[btn sizeToFit];
btn.tag = 1000 +i;
[self .view addSubview: btn];
}
}
- (void )btnAction: (UIButton *)btn{
switch (btn.tag ) {
case 1000 :
[self createTable];
break ;
case 1001 :
[self inserData];
break ;
case 1002 :
[self queryData];
break ;
case 1003 :
[self deleteData];
break ;
case 1004 :
[self updata];
default :
break ;
}
}
-(void )createTable{
if (sqlite3_open([[self path] UTF8String], &(_dataBase)) != SQLITE_OK){
NSLog (@"创建数据数据库失败" );
return ;
}
char *error;
const char *createSQL = "create table if not exists list(id integer primary key autoincrement,name text,sex text)" ;
int tabelResult = sqlite3_exec(self .dataBase , createSQL, NULL , NULL , &error);
if (tabelResult != SQLITE_OK){
NSLog (@"创建列表失败 %i" , tabelResult);
}
else {
NSLog (@"成功创建数据列表" );
}
sqlite3_close(self .dataBase );
}
- (void )inserData{
if (sqlite3_open([[self path] UTF8String], &(_dataBase)) != SQLITE_OK){
NSLog (@"创建数据数据库失败" );
return ;
}
const char *insertSQL = "insert into list (name,sex) values ('iosRunner','male')" ;
sqlite3_stmt *stmt;
int insertResult = sqlite3_prepare_v2(self .dataBase , insertSQL, -1 , &stmt, nil );
if (insertResult != SQLITE_OK){
NSLog (@"插入数据失败 %i" , insertResult);
}
else {
NSLog (@"插入数据成功 %i" , insertResult);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(self .dataBase );
}
-(void )queryData{
if (sqlite3_open([[self path] UTF8String], &(_dataBase)) != SQLITE_OK){
NSLog (@"创建数据数据库失败" );
return ;
}
const char *querySQL = "select * from list" ;
sqlite3_stmt *stmt;
int queryResult = sqlite3_prepare_v2(self .dataBase , querySQL, -1 , &stmt, nil );
if (queryResult != SQLITE_OK){
NSLog (@"查询数据失败 %i" , queryResult);
}
else {
NSLog (@"查询数据中 %i" , queryResult);
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSLog (@"%i %s %s" , sqlite3_column_int(stmt, 0 ), sqlite3_column_text(stmt, 1 ), sqlite3_column_text(stmt, 2 ));
}
}
sqlite3_finalize(stmt);
sqlite3_close(self .dataBase );
}
-(void )updata{
if (sqlite3_open([[self path] UTF8String], &(_dataBase)) != SQLITE_OK){
NSLog (@"创建数据数据库失败" );
return ;
}
const char *updateSQL = "update list set name = 'buhui' where name = 'iosRunner'" ;
sqlite3_stmt *stmt;
int updateResult = sqlite3_prepare_v2(self .dataBase , updateSQL, -1 , &stmt, nil );
if (updateResult != SQLITE_OK){
NSLog (@"修改数据失败 %i" , updateResult);
}
else {
NSLog (@"修改成功" );
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(self .dataBase );
}
-(void )deleteData{
if (sqlite3_open([[self path] UTF8String], &(_dataBase)) != SQLITE_OK){
NSLog (@"创建数据数据库失败" );
return ;
}
const char *deleteSQL = "delete from list where name = 'iosRunner'" ;
sqlite3_stmt *stmt;
int deleteResult = sqlite3_prepare_v2(self .dataBase , deleteSQL, -1 , &stmt, nil );
if (deleteResult != SQLITE_OK){
NSLog (@"删除数据失败 %i" , deleteResult);
}
else {
NSLog (@"删除数据成功" );
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_close(self .dataBase );
}
- (void )didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
}
-(NSString *)path{
NSString *documentStr = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES ) firstObject];
NSString *path = [NSString stringWithFormat:@"%@/crylown.db" , documentStr];
return path;
}