sql数据库

sqlData.h
#import <Foundation/Foundation.h>

#import <sqlite3.h>//数据库
@interface ClassName : NSObject
//数据库必须得有一个主键id
//assign 用来修饰基本数据类型的 int float double
@property(nonatomic,assign)NSInteger classid;

@property(nonatomic,strong)NSString *name,*age,*sex,*height,*weitht;

@end

//重要:数据库的增删改查的具体实现
@interface SqlData : NSObject

{

//定义数据库成员变量
sqlite3 *db;

}
//单利方法
+(instancetype)initData;
//初始化数据库
-(void)initSql;
//添加数据
-(void)addData:(ClassName *)data;
//删除数据
-(void)deleteData:(NSInteger)data;
//修改数据
-(void)upData:(ClassName *)data;
//查询数据
-(NSMutableArray *)array;
//关闭数据库
-(void)closeSql;

@end

sqlData.m
#import “SqlData.h”

@implementation ClassName

@end

//定义静态变量
static SqlData *sqlData;

@implementation SqlData

//单利方法

• (instancetype)initData{

  if (!sqlData) {
  //创建sqlData对象
  sqlData = [[SqlData alloc] init];

  }
  return sqlData;

}
//初始化数据库

• (void)initSql{

  //获取Documents目录
  NSString *str = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)firstObject];
  //拼接路径
  NSString *strName = [str stringByAppendingString:@"/1607E.db"];
  NSLog(@"%@",strName);
  //创建数据库
  if (sqlite3_open([strName UTF8String], &db)== SQLITE_OK) {

    NSLog(@"数据库打开成功");
    //初始化数据库表格的格式：create table if not exists 表名(主键id integer primary key,所有的数据类型);
    const char *sql = "create table if not exists ClassNum(classid integer primary key,name text,age text,sex text,height text,weight text)";
    //创建预编译数据库指针
    sqlite3_stmt *stmt;
    //绑定数据库指针的一个接口， -1自动匹配长度  Byte字节
    sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
    
    //一行一行执行  判断是否执行完成
    if (sqlite3_step(stmt) == SQLITE_DONE) {
        
        NSLog(@"数据库表格创建成功");
    }else{
        NSLog(@"数据库表格创建失败");
    }
    //销毁预编译指针
    sqlite3_finalize(stmt);
  

  }else{

    NSLog(@"数据库打开失败");
  

  }

}
//添加数据

• (void)addData:(ClassName *)data{

  //添加数据的sql语句：insert into 表名 values(null,?,?);
  const char *sql = “insert into ClassNum values(null,?,?,?,?,?)”;
  //创建预编译指针
  sqlite3_stmt *stmt;
  //绑定数据库指针 -1代表自动匹配
  sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
  //调用添加数据接口 绑定数据库接口
  sqlite3_bind_text(stmt, 1, [data.name UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 2, [data.age UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 3, [data.sex UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 4, [data.height UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 5, [data.weitht UTF8String], -1, SQLITE_TRANSIENT);
  //执行预编译指针
  sqlite3_step(stmt);
  //销毁
  sqlite3_finalize(stmt);

}
//修改数据

• (void)upData:(ClassName *)data{

  //sql 语句的格式：update 表名 set 所有数据 where 主键id = ?
  const char *sql = “update ClassNum set name = ?,age = ?,sex = ?,height = ?,weight = ? where classid = ?”;
  //创建预编译指针
  sqlite3_stmt *stmt;
  //绑定数据库指针 -1代表自动匹配
  sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
  //绑定接口
  sqlite3_bind_text(stmt, 1, [data.name UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 2, [data.age UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 3, [data.sex UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 4, [data.height UTF8String], -1, SQLITE_TRANSIENT);

  sqlite3_bind_text(stmt, 5, [data.weitht UTF8String], -1, SQLITE_TRANSIENT);
  //绑定主键id
  sqlite3_bind_int(stmt, 6, (int)(data.classid));
  //执行预编译指针
  sqlite3_step(stmt);
  //销毁
  sqlite3_finalize(stmt);

}
//删除数据

• (void)deleteData:(NSInteger )data{

  //sql 语句： delete from 表名 where 表名的主键id = ?
  const char *sql = “delete from ClassNum where classid = ?”;
  //创建预编译指针
  sqlite3_stmt *stmt;
  //绑定数据库指针 -1代表自动匹配
  sqlite3_prepare_v2(db, sql, -1, &stmt, nil);

  //删除绑定主键id
  sqlite3_bind_int(stmt, 1, (int)data);
  //执行预编译指针
  sqlite3_step(stmt);
  //销毁
  sqlite3_finalize(stmt);

}
查询数据

• (NSMutableArray *)array{

  //sql 语句格式：select *from 表名
  const char *sql = “select *from ClassNum”;
  //预编译指针（链接到数据库）
  sqlite3_stmt *stmt;
  //绑定数据库指针的接口
  sqlite3_prepare_v2(db, sql, -1, &stmt, nil);
  NSMutableArray *array = [NSMutableArray array];
  //执行数据库中的预编译接口
  //SQLITE_ROW 一行一行去查询
  while (sqlite3_step(stmt) == SQLITE_ROW) {

    //创建className对象
    ClassName *classData = [[ClassName alloc] init];
    //找到表格中的主键
    classData.classid = sqlite3_column_int(stmt, 0);
    classData.name = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
  
    classData.age = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
  
    classData.sex = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 3)];
  
    classData.height = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 4)];
  
    classData.weitht = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 5)];
  
    [array addObject:classData];
  

  }
  //销毁
  sqlite3_finalize(stmt);

  //返回数组
  return array;

}
//关闭数据库

• (void)closeSql{

  sqlite3_close(db);

}

@end