李洪强iOS开发之-sql数据库的使用
一,创建工程
二: 导入头文件
三:导入
四: 数据库增删改查
//因为是结构体类型,所以用assign
//1.创建数据库(保存路径)
@property(nonatomic,assign)sqlite3 *db;
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
//结构体类型的指针
// sqlite3 *db = NULL;
//打开数据库,如果没有就创建
NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject] stringByAppendingPathComponent:@"dasta.sqlite"];
NSLog(@"%@",path);
int success = sqlite3_open(path.UTF8String, &_db);
if(success == SQLITE_OK){
NSLog(@"创建数据库成功!");
//2.创建表(指定字段,需求,保存学生信息 id name score)
//用sql语句
NSString *str = @"CREATE TABLE IF NOT EXISTS t_student (id INTEGER PRIMARY KEY AUTOINCREMENT ,name TEXT NOT NULL,score REAL NOT NULL)";
//执行
int success_t = sqlite3_exec(_db, str.UTF8String, NULL, NULL, NULL);
if(success_t == SQLITE_OK){
NSLog(@"创建表成功!");
}else{
NSLog(@"创建表失败!");
}
}else{
NSLog(@"创建数据库失败!");
}
//关闭数据库,至于在什么时候关闭,取决于自己的需求
sqlite3_close(_db);
}
//3.增加 (100条数据 数据随机)
- (IBAction)insertData:(UIButton *)sender {
for(int i = 0; i < 100 ; i++){
NSString *strName = [NSString stringWithFormat:@"ming-yuexing-%d",i];
NSString *sqlStr = [NSString stringWithFormat:@"INSERT INTO t_student (name , score) VALUES ('%@',%.2f)",strName,arc4random_uniform(1000)/10.0];
//执行
int success = sqlite3_exec(_db, sqlStr.UTF8String, NULL, NULL, NULL);
if(success == SQLITE_OK){
NSLog(@"添加成功");
}else{
NSLog(@"添加失败");
}
}
}
//4.删除 (70-80分数)
- (IBAction)deleteData:(UIButton *)sender {
NSString *sqlStr = @"DELETE FROM t_student WHERE score > 70.0 AND score < 80.0";
//执行
int success = sqlite3_exec(_db, sqlStr.UTF8String, NULL, NULL, NULL);
if(success == SQLITE_OK){
NSLog(@"删除成功");
}else{
NSLog(@"删除失败");
}
}
//5.修改 (修改小于60.0的 为60.0)
- (IBAction)updateData:(UIButton *)sender {
NSString *sqlStr = @"UPDATE t_student SET score = 60.0 WHERE score < 60.0";
//执行
int success = sqlite3_exec(_db, sqlStr.UTF8String, NULL, NULL, NULL);
if(success == SQLITE_OK){
NSLog(@"修改成功");
}else{
NSLog(@"修改失败");
}
}
//6.查询
- (IBAction)selectData:(UIButton *)sender {
// (查成绩 >= 60)
// NSString *sqlStr = @"SELECT * FROM t_student WHERE score > 60.0 ORDER BY score DESC";
//查询数据 (name中带 8 的)
//8% 意思是以8开头
//%8 意思是以8结尾
NSString *sqlStr = @"SELECT * FROM t_student WHERE name LIKE '%8'";
//查询之后,把结果放在stmt对象
//保存所有的结果集
sqlite3_stmt *stmt = nil;
sqlite3_prepare(_db, sqlStr.UTF8String, -1, &stmt, NULL);
//获取到所有的结果 每一步 查询到一条记录
sqlite3_step(stmt);
while (sqlite3_step(stmt) == SQLITE_ROW) {
//取出一条记录
//name TEXT score REAL
//从第几列告诉我
const unsigned char * name = sqlite3_column_text(stmt, 1);
NSString *strName = [NSString stringWithCString:(const char *)name encoding:NSUTF8StringEncoding];
//score
double score = sqlite3_column_double(stmt, 2);
NSLog(@"name = %@ score = %f",strName,score);
}
}
@end
