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));
    }
    
    
    
}