iOS数据库汇总-----更新不间断

手机通用数据库类型：  sqlite3   

 用sql语句操作数据，不区分大小写

Sqlite3:

1.建表：

         create table if not exists student（stu_No varchar(200),  stu_Name  varchar(300),su_Sex varchar(100),stu_Age  integer） 

 如果不存在学生信息表则创建 表student

creat table+表名(字段名称 字段类型，字段名称 字段类型，)

2.添加数据：

           insert into student（stu_No , stu_Name,stu_Sex,stu_age,stu_Chengji）

 values(‘1000001’,’李雷’，‘F’，18，60)

【注】添加的数据类型如果是字符串类型则要加单引号

3.删除：

        delete from student where stu_No = ’1000001’

删除某一条数据时需要添加where 条件判断

如果不加where条件判断 则 删除表中的全部数据，表仍在，可以继续添加数据操作。

delete from student  

4.修改：

        update student set stu_Sex = ‘M’where stu_No = ’1000001’

修改一条数据需要添加where判断，进行定位数据对象

5.查询：

       select * from student 

查询表中的全部数据

select from student where stu_No = ‘1000001’

按条件查询一行数据

select stu_Name from student where stu_No = ‘1000001’

按条件查询某个字段值

代码如下：

导库：

              

1.创建：

-(void)creatDB{    //沙盒路径 app 所在的位置 都是相对路径    //沙盒目录下 3个 文件夹   Documents Library tmp    //Documents  一般存放数据库或者plist文件，一般用来存放用户信息，    //library/caches  存放一些缓存文件，临时性文件    //tmp   存放临时性文件 app、iphone重启 tmp中文件销毁        //1. 确定数据库文件的路径    // 在沙盒目录下 Documents 下 名称为student.db    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject] stringByAppendingPathComponent:@"student.db"];    //    NSString *dbStr = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/student.db"];    //2.声明数据库对象    sqlite3 *stuSql = nil;    //3.打开数据库    //parameter1:数据库路径    //parameter2:数据库对象    if (sqlite3_open([dbPath UTF8String], &stuSql) == SQLITE_OK) {       //如果打开数据库成功，则继续 数据库操作                NSString *str = @"create table if not exists student (id integer primary key autoincrement,stu_No varchar(200),stu_Name varchar(200),stu_Sex varchar(200),stu_age integer)";        //sqlite3_exec 执行数据库操作                char *errMsg = nil;        int result =  sqlite3_exec(stuSql, [str UTF8String], NULL, NULL, &errMsg);        if (result == SQLITE_OK) {            NSLog(@"建表成功");        }        //关闭数据库        sqlite3_close(stuSql);    }else{        NSLog(@"数据库打开失败 %s",sqlite3_errmsg(stuSql));        sqlite3_close(stuSql);    }}

2.增：

-(void)addNewData:(UIButton*)btn{    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:@"student.db"];    sqlite3 *stusql = nil;        //打开数据库成功    if (sqlite3_open([dbPath UTF8String], &stusql) == SQLITE_OK) {        NSString *insertStr = @"insert into student(stu_No,stu_Name,stu_Sex,stu_age) values('151601','小明','女',46)";        if (sqlite3_exec(stusql, [insertStr UTF8String], NULL, NULL, NULL) == SQLITE_OK) {            NSLog(@"插入一条数据成功");        }else{            NSLog(@"%s",sqlite3_errmsg(stusql));        }    }    sqlite3_close(stusql);}

3.删：

-(void)delData:(UIButton*)btn{    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject] stringByAppendingPathComponent:@"student.db"];            sqlite3 *stusql = nil;            if (sqlite3_open([dbPath UTF8String], &stusql) == SQLITE_OK) {        NSString *delStr = @"delete from student where stu_No= '100001'";        if (sqlite3_exec(stusql, [delStr UTF8String], NULL, NULL, NULL) == SQLITE_OK) {                    }else{            NSLog(@"%s",sqlite3_errmsg(stusql));        }    }    sqlite3_close(stusql);}

4.改：

-(void)updateData:(UIButton*)btn{    //数据路径    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject]stringByAppendingPathComponent:@"student.db"];    //声明数据库对象    sqlite3 *stuSql = nil;        //打开数据库    if (sqlite3_open([dbPath UTF8String], &stuSql) == SQLITE_OK) {        NSString *updateStr = @"update student set stu_Sex = 'unkown' where stu_No = '100001'";        if (sqlite3_exec(stuSql, [updateStr UTF8String], NULL, NULL, NULL) == SQLITE_OK) {            NSLog(@"更新完成");        }else{            NSLog(@"%s",sqlite3_errmsg(stuSql));        }    }else{        NSLog(@"%s",sqlite3_errmsg(stuSql));    }    sqlite3_close(stuSql);}

5.查：

-(void)queryData:(UIButton*)btn{    NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject] stringByAppendingPathComponent:@"student.db"];        sqlite3 *stuSql = nil;        if (sqlite3_open([dbPath UTF8String], &stuSql) == SQLITE_OK) {        NSString *queryStr = @"select stu_Name from student where stu_Sex = 'unkown'";//        NSString *queryStr = @"select * from student";        //先声明查询句柄        sqlite3_stmt *stmt = nil;        //先做查询前的准备        sqlite3_prepare_v2(stuSql, [queryStr UTF8String], -1, &stmt, nil);        //查询数据时，每行查询一次，放在while中        while (sqlite3_step(stmt) == SQLITE_ROW) {            //sqlite3_column_text 查询当前属性值            //parameter1:查询句柄            //parameter2:当前取得字段在结果集中的列是第几列            char *number = (char*)sqlite3_column_text(stmt, 1);            NSString *numberStr = [NSString stringWithUTF8String:number];            NSLog(@" number: %@",numberStr);                                    char *nameChar = (char*)sqlite3_column_text(stmt,2);            NSString *nameStr = [NSString stringWithUTF8String:nameChar];            NSLog(@"name: %@",nameStr);                        char *sexChar = (char*)sqlite3_column_text(stmt, 3);            NSString *sexStr = [NSString stringWithUTF8String:sexChar];            NSLog(@"sex: %@",sexStr);                    }                                    }else{        NSLog(@"%s",sqlite3_errmsg(stuSql));    }            }