一. SQLite数据库实际操作

1. 什么是SQLite

• 嵌入式的关系型数据库
• 轻量化
• 存储大量数据
• 支持语言多

2. 常用SQLite操作

• 创建数据表
  CREATE TABLE CONTACTS (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  NAME TEXT,
  ADDRESS TEXT,
  PHONE TEXT
  );
  AUTOINCREMENT 是自动增加
  PRIMARY KEY 是主码
  INTEGER 数字类型
  TEXT 字符串类型

• 向数据表添加数据
  INSERT INTO CONTACTS (NAME,ADDRESS,PHONE)
  VALUES("张三","南京路","18612345678");
  ID字段会自动增加，无需插入

• 从数据表中删除数据
  DELETE FROM CONTACTS
  WHERE NAME = "张三";

• 修改数据表中的数据
  UPDATE CONTACTS
  SET NAME = "王五"
  WHERE NAME = "张三";

• 读取数据表
  SELECT *
  FROM CONTACTS;

3. SQLite实际操作

二. 在iOS端实现SQLite操作

添加libsqlite3.tbd

//
//  ViewController.m
//  SQLiteDemo

#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()

- (IBAction)createTableAction:(id)sender;
- (IBAction)addRowAction:(id)sender;
- (IBAction)deleteRowAction:(id)sender;
- (IBAction)modifyRowAction:(id)sender;
- (IBAction)readRowsAction:(id)sender;
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
}


- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

- (NSString *)sqliteFilePath {
    //获取cache目录
    NSString *cachePath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) firstObject];
    //拼接文件名
    NSString *databasePath = [cachePath stringByAppendingPathComponent:@"contacts.sqlite"];
    return databasePath;
}

//创建数据表
- (void)createSqliteTable {
    //获取数据库文件路径
    NSString *dbFilePath = [self sqliteFilePath];
    //将路径字符串转换成UTF-8
    char *dbPath = [dbFilePath UTF8String];
    //声明sqlite数据库结构体的指针
    sqlite3 *contactDB;
    //sqlite3_open将会打开数据库文件，如果文件不存在，它会创建该数据库文件
    if (sqlite3_open(dbPath, &contactDB) == SQLITE_OK) {
        NSLog(@"数据库打开/创建成功");
    } else {
        NSLog(@"数据库打开/创建失败");
    }
    
    //生成指令
    char sqlite_stmt[1000];
    snprintf(sqlite_stmt, 1000, "CREATE TABLE CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,ADDRESS TEXT,PHONE TEXT)");
    
    //执行语句
    //第三个参数为NULL表示不使用回调
    if (sqlite3_exec(contactDB, sqlite_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
    
    //关闭数据库
    sqlite3_close(contactDB);
}

//增加数据
- (void)addRow {
    //获取数据库文件路径
    NSString *dbFilePath = [self sqliteFilePath];
    //将路径字符串转换成UTF-8
    char *dbPath = [dbFilePath UTF8String];
    //声明sqlite数据库结构体的指针
    sqlite3 *contactDB;
    //sqlite3_open将会打开数据库文件，如果文件不存在，它会创建该数据库文件
    if (sqlite3_open(dbPath, &contactDB) == SQLITE_OK) {
        NSLog(@"数据库打开/创建成功");
    } else {
        NSLog(@"数据库打开/创建失败");
    }
    
    //生成指令
    char sqlite_stmt[1000];
    //双引号使用转义字符\
    
    snprintf(sqlite_stmt, 1000, "INSERT INTO CONTACTS (NAME,ADDRESS,PHONE)VALUES(\"张三\",\"南京路\",\"18612345678\")");
    
    //执行语句
    //第三个参数为NULL表示不使用回调
    if (sqlite3_exec(contactDB, sqlite_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"插入数据成功");
    } else {
        NSLog(@"插入数据失败");
    }
    
    //关闭数据库
    sqlite3_close(contactDB);
}

//删除数据
- (void)deleteRow {
    //获取数据库文件路径
    NSString *dbFilePath = [self sqliteFilePath];
    //将路径字符串转换成UTF-8
    char *dbPath = [dbFilePath UTF8String];
    //声明sqlite数据库结构体的指针
    sqlite3 *contactDB;
    //sqlite3_open将会打开数据库文件，如果文件不存在，它会创建该数据库文件
    if (sqlite3_open(dbPath, &contactDB) == SQLITE_OK) {
        NSLog(@"数据库打开/创建成功");
    } else {
        NSLog(@"数据库打开/创建失败");
    }
    
    //生成指令
    char sqlite_stmt[1000];
    //双引号使用转义字符\
    
    snprintf(sqlite_stmt, 1000, "DELETE FROM CONTACTS WHERE NAME = \"张三\"");
    
    //执行语句
    //第三个参数为NULL表示不使用回调
    if (sqlite3_exec(contactDB, sqlite_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"删除数据成功");
    } else {
        NSLog(@"删除数据失败");
    }
    
    //关闭数据库
    sqlite3_close(contactDB);
}

//修改数据
- (void)modifyRow {
    //获取数据库文件路径
    NSString *dbFilePath = [self sqliteFilePath];
    //将路径字符串转换成UTF-8
    char *dbPath = [dbFilePath UTF8String];
    //声明sqlite数据库结构体的指针
    sqlite3 *contactDB;
    //sqlite3_open将会打开数据库文件，如果文件不存在，它会创建该数据库文件
    if (sqlite3_open(dbPath, &contactDB) == SQLITE_OK) {
        NSLog(@"数据库打开/创建成功");
    } else {
        NSLog(@"数据库打开/创建失败");
    }
    
    //生成指令
    char sqlite_stmt[1000];
    //双引号使用转义字符\
    
    snprintf(sqlite_stmt, 1000, "UPDATE CONTACTS SET NAME = \"李四\" WHERE NAME = \"张三\"");
    
    //执行语句
    //第三个参数为NULL表示不使用回调
    if (sqlite3_exec(contactDB, sqlite_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"修改数据成功");
    } else {
        NSLog(@"修改数据失败");
    }
    
    //关闭数据库
    sqlite3_close(contactDB);
}

//查询数据库
- (void)readRows {
    //获取数据库文件路径
    NSString *dbFilePath = [self sqliteFilePath];
    //将路径字符串转换成UTF-8
    char *dbPath = [dbFilePath UTF8String];
    //声明sqlite数据库结构体的指针
    sqlite3 *contactDB;
    //sqlite3_open将会打开数据库文件，如果文件不存在，它会创建该数据库文件
    if (sqlite3_open(dbPath, &contactDB) == SQLITE_OK) {
        NSLog(@"数据库打开/创建成功");
    } else {
        NSLog(@"数据库打开/创建失败");
    }
    
    //生成指令
    char sqlite_stmt[1000];
    snprintf(sqlite_stmt, 1000, "SELECT * FROM CONTACTS");
    //准备一个SQLite语句，用于执行，目的是优化执行时间 
    sqlite3_stmt *statement;
    //初始化statement实例,-1表示不指定长度
    sqlite3_prepare_v2(contactDB, sqlite_stmt, -1, &statement, NULL);
    
    //执行一条准备的语句，如果找到一行匹配的数据，则返回SQLITE_ROW
    while (sqlite3_step(statement) == SQLITE_ROW) {
        //获取到一行数据
        NSInteger index = sqlite3_column_int(statement, 0);
        NSString *name = [[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(statement , 1)];
        NSString *address = [[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(statement , 2)];
        NSString *phone = [[NSString alloc]initWithUTF8String:(const char*)sqlite3_column_text(statement , 3)];
        NSLog(@"%ld | %@ | %@ | %@",index,name,address,phone);
    }
    NSLog(@"数据库读取完毕");
    //在内存中，清理之前准备的语句
    sqlite3_finalize(statement);
    
    //关闭数据库
    sqlite3_close(contactDB);
}
- (IBAction)createTableAction:(id)sender {
    [self createSqliteTable];
}

- (IBAction)addRowAction:(id)sender {
    [self addRow];
}

- (IBAction)deleteRowAction:(id)sender {
    [self deleteRow];
}

- (IBAction)modifyRowAction:(id)sender {
    [self modifyRow];
}

- (IBAction)readRowsAction:(id)sender {
    [self readRows];
}
@end

三. SQLite应用实例-蔬菜报价表

//
//  Vegetable.h
//  VegManagement

#import <Foundation/Foundation.h>

@interface Vegetable : NSObject

@property(nonatomic, strong) NSString *name;
@property(nonatomic, assign) double price;

@end

//
//  VegetableManager.h
//  VegManagement

#import <Foundation/Foundation.h>
#import "Vegetable.h"

@interface VegetableManager : NSObject

+ (instancetype)sharedInstance;
- (NSArray *)vegetables;
- (BOOL)addVegetable:(Vegetable *)vegtable;
- (BOOL)removeVegetableWithName:(NSString *)vegetableName;
- (BOOL)changePrice:(double)price forVegetableName:(NSString *)vegetableName;

@end

//
//  VegetableManager.m
//  VegManagement

#import "VegetableManager.h"
#import <sqlite3.h>

@interface VegetableManager()

@property(nonatomic, strong) NSArray *vegetablesArray;

@end

@implementation VegetableManager

+ (instancetype)sharedInstance {
    static VegetableManager *shared = nil;
    //VegetableManager是一个单例类
    //dispatch_once可以让其中的方法只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        shared = [[super alloc] initWithUniqueInstance];
    });
    return shared;
}

//单例模式的初始化
- (instancetype)initWithUniqueInstance {
    if (self = [super init]) {
        //进行必要初始化
        NSString *sqlitePath = [self sqlitePath];
        BOOL fileExist = [[NSFileManager defaultManager]
                          fileExistsAtPath:sqlitePath];
        if (!fileExist) {
            //文件不存在，创建数据库文件，并且添加数据表
            const char *dbPath = [sqlitePath UTF8String];
            //声明一个sqlite结构体指针
            sqlite3 *vegetableDB;
            if (sqlite3_open(dbPath, &vegetableDB) == SQLITE_OK) {
                NSLog(@"数据库文件/打开创建成功");
            } else {
                NSLog(@"数据库文件/打开创建失败");
                return nil;
            }
            //REAL是浮点类型
            char sql_stmt[1000] = "CREATE TABLE VEGETABLES (ID INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,PRICE REAL)";
            if (sqlite3_exec(vegetableDB, sql_stmt, NULL, NULL, NULL) == SQLITE_OK) {
                NSLog(@"创建数据表成功");
                sqlite3_close(vegetableDB);
            } else {
                NSLog(@"创建数据表失败");
                sqlite3_close(vegetableDB);
                return nil;
            }
            
            
        } else {
            //数据库文件存在，读取数据库文件
            [self readVegetablesListFromSqlite];
        }
        
    }
    return self;
}

//返回数据库文件路径
- (NSString *)sqlitePath {
    //获取cache目录
    NSString *cachePath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) firstObject];
    NSString *dbPath = [cachePath stringByAppendingPathComponent:@"vegetables.sqlite"];
    return dbPath;
}

- (void)readVegetablesListFromSqlite {
    NSString *sqlitePath = [self sqlitePath];
    const char *dbPath = [sqlitePath UTF8String];
    //声明一个sqlite结构体指针
    sqlite3 *vegetableDB;
    if (sqlite3_open(dbPath, &vegetableDB) == SQLITE_OK) {
        NSLog(@"数据库文件/打开创建成功");
    } else {
        NSLog(@"数据库文件/打开创建失败");
        return;
    }
    
    NSMutableArray *vegetableMutableArray = [NSMutableArray array];
    sqlite3_stmt *statement;
    char sql_stmt[1000] = "SELECT ID, NAME, PRICE FROM VEGETABLES";
    //准备sql语句
    sqlite3_prepare_v2(vegetableDB, sql_stmt, -1, &statement, NULL);
    while (sqlite3_step(statement) == SQLITE_ROW) {
        NSInteger index = sqlite3_column_int(statement, 0);
        NSString *vegName = [[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statement, 1)];
        double price = sqlite3_column_double(statement, 2);
        
        Vegetable *veg = [[Vegetable alloc]init];
        veg.name = vegName;
        veg.price = price;
        [vegetableMutableArray addObject:veg];
    }
    
    NSLog(@"数据库读取完毕");
    
    _vegetablesArray = [vegetableMutableArray copy];
    sqlite3_finalize(statement);
    sqlite3_close(vegetableDB);
}

//返回蔬菜列表
- (NSArray *)vegetables {
    return [_vegetablesArray copy];
}

//判断蔬菜名称是否在列表中
- (BOOL)isInListForVegetableName:(NSString *)name {
    for (Vegetable *veg in _vegetablesArray) {
        if ([veg.name isEqualToString:name]) {
            return YES;
        }
    }
    return NO;
}

//添加蔬菜
- (BOOL)addVegetable:(Vegetable *)vegtable {
    if ([self isInListForVegetableName:vegtable.name]) {
        NSLog(@"已经存在这个蔬菜");
        return NO;
    }
    
    if (vegtable.name.length == 0 || vegtable.price <= 0) {
        NSLog(@"蔬菜信息有误");
        return NO;
    }
    NSString *sqlitePath = [self sqlitePath];
    const char *dbPath = [sqlitePath UTF8String];
    //声明一个sqlite结构体指针
    sqlite3 *vegetableDB;
    if (sqlite3_open(dbPath, &vegetableDB) == SQLITE_OK) {
        NSLog(@"数据库文件/打开创建成功");
    } else {
        NSLog(@"数据库文件/打开创建失败");
        return NO;
    }
    
    NSString *command = [NSString stringWithFormat:@"INSERT INTO VEGETABLES (NAME, PRICE) VALUES (\"%@\",%lf)",vegtable.name,vegtable.price];
    const char *sql_stmt = [command UTF8String];
    if (sqlite3_exec(vegetableDB, sql_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"添加蔬菜成功");
        sqlite3_close(vegetableDB);
        [self readVegetablesListFromSqlite];
        return YES;
    } else {
        NSLog(@"添加蔬菜失败");
        sqlite3_close(vegetableDB);
        return NO;
    }
}

//删除蔬菜
- (BOOL)removeVegetableWithName:(NSString *)vegetableName {
    if (! [self isInListForVegetableName:vegetableName]) {
        NSLog(@"找不到要删除的蔬菜");
        return NO;
    }
   
    NSString *sqlitePath = [self sqlitePath];
    const char *dbPath = [sqlitePath UTF8String];
    //声明一个sqlite结构体指针
    sqlite3 *vegetableDB;
    if (sqlite3_open(dbPath, &vegetableDB) == SQLITE_OK) {
        NSLog(@"数据库文件/打开创建成功");
    } else {
        NSLog(@"数据库文件/打开创建失败");
        return NO;
    }
    
    NSString *command = [NSString stringWithFormat:@"DELETE FROM VEGETABLES WHERE NAME = \"%@\"",vegetableName];
    const char *sql_stmt = [command UTF8String];
    if (sqlite3_exec(vegetableDB, sql_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"删除蔬菜成功");
        sqlite3_close(vegetableDB);
        [self readVegetablesListFromSqlite];
        return YES;
    } else {
        NSLog(@"删除蔬菜失败");
        sqlite3_close(vegetableDB);
        return NO;
    }
}

//修改蔬菜价格
- (BOOL)changePrice:(double)price forVegetableName:(NSString *)vegetableName {
    if (! [self isInListForVegetableName:vegetableName]) {
        NSLog(@"找不到要修改的蔬菜");
        return NO;
    }
    
    NSString *sqlitePath = [self sqlitePath];
    const char *dbPath = [sqlitePath UTF8String];
    //声明一个sqlite结构体指针
    sqlite3 *vegetableDB;
    if (sqlite3_open(dbPath, &vegetableDB) == SQLITE_OK) {
        NSLog(@"数据库文件/打开创建成功");
    } else {
        NSLog(@"数据库文件/打开创建失败");
        return NO;
    }
    
    NSString *command = [NSString stringWithFormat:@"UPDATE VEGETABLES SET PRICE = %lf WHERE NAME = \"%@\"",price,vegetableName];
    const char *sql_stmt = [command UTF8String];
    if (sqlite3_exec(vegetableDB, sql_stmt, NULL, NULL, NULL) == SQLITE_OK) {
        NSLog(@"修改蔬菜价格成功");
        sqlite3_close(vegetableDB);
        [self readVegetablesListFromSqlite];
        return YES;
    } else {
        NSLog(@"修改蔬菜价格失败");
        sqlite3_close(vegetableDB);
        return NO;
    }

}
@end

//
//  ViewController.m
//  VegManagement

#import "ViewController.h"
#import "VegetableManager.h"

@interface ViewController ()<UITableViewDelegate,UITableViewDataSource>
@property (weak, nonatomic) IBOutlet UITableView *vegListView;
@property (weak, nonatomic) UIAlertController *alertController;
@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    _vegListView.delegate = self;
    _vegListView.dataSource = self;
    
    UIBarButtonItem *rightBarButton = [[UIBarButtonItem alloc]initWithTitle:@"增加" style:UIBarButtonItemStylePlain target:self action:@selector(addVegetables)];
    [self.navigationItem setRightBarButtonItem:rightBarButton];
}

- (void)addVegetables {
    //提示框
    UIAlertController *alertController = [UIAlertController alertControllerWithTitle:@"增加蔬菜" message:nil preferredStyle:UIAlertControllerStyleAlert];
    _alertController = alertController;
    [alertController addTextFieldWithConfigurationHandler:^(UITextField * _Nonnull textField) {
        textField.placeholder = @"蔬菜名";
        textField.tag = 1;
    }];
    [alertController addTextFieldWithConfigurationHandler:^(UITextField * _Nonnull textField) {
        textField.placeholder = @"菜价";
        textField.tag = 2;
    }];
    //增加确认按钮
    UIAlertAction *confirmAction = [UIAlertAction actionWithTitle:@"确定" style:UIAlertActionStyleDefault handler:^(UIAlertAction * _Nonnull action) {
        NSArray *textFields =_alertController.textFields;
        NSString *vegName = nil;
        double vegPrice = 0;
        for (UITextField *tf in textFields) {
            if (tf.tag == 1) {
                vegName = tf.text;
            } else if (tf.tag == 2) {
                vegPrice = [tf.text doubleValue];
            }
        }
        Vegetable *veg = [[Vegetable alloc]init];
        veg.name = vegName;
        veg.price = vegPrice;
        [[VegetableManager sharedInstance] addVegetable:veg];
        [_vegListView reloadData];
        _alertController = nil;
    }];
    
    [alertController addAction:confirmAction];
    
    //增加取消按钮
    UIAlertAction *cancelAction = [UIAlertAction actionWithTitle:@"取消" style:UIAlertActionStyleCancel handler:nil];
    [alertController addAction:cancelAction];
    
    //显示提示框
    [self presentViewController:alertController animated:YES completion:nil];
}

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tableView {
    return 1;
}

- (NSInteger)tableView:(UITableView *)tableView numberOfRowsInSection:(NSInteger)section {
    return [[VegetableManager sharedInstance] vegetables].count;
}

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
    Vegetable *veg = [[[VegetableManager sharedInstance] vegetables] objectAtIndex:indexPath.row];
    //UITableViewCellStyleValue1 左边显示主标题 右边显示右标题
    UITableViewCell *cell = [[UITableViewCell alloc]initWithStyle:UITableViewCellStyleValue1 reuseIdentifier:@"cell"];
    cell.textLabel.text = veg.name;
    cell.detailTextLabel.text = [NSString stringWithFormat:@"￥%.2f",veg.price];
    return cell;
}

- (BOOL)tableView:(UITableView *)tableView canEditRowAtIndexPath:(NSIndexPath *)indexPath {
    return YES;
}

- (NSArray<UITableViewRowAction *> *)tableView:(UITableView *)tableView editActionsForRowAtIndexPath:(NSIndexPath *)indexPath {
    //点击 菜价涨一元
    UITableViewRowAction *actionEdit = [UITableViewRowAction rowActionWithStyle:UITableViewRowActionStyleNormal title:@"涨一元" handler:^(UITableViewRowAction * _Nonnull action, NSIndexPath * _Nonnull indexPath) {
        Vegetable *veg = [[[VegetableManager sharedInstance] vegetables]objectAtIndex:indexPath.row];
        [[VegetableManager sharedInstance] changePrice:veg.price + 1 forVegetableName:veg.name];
        [_vegListView reloadData];
    }];
    //点击删除
    UITableViewRowAction *actionDelete = [UITableViewRowAction rowActionWithStyle:UITableViewRowActionStyleDestructive title:@"删除" handler:^(UITableViewRowAction * _Nonnull action, NSIndexPath * _Nonnull indexPath) {
        Vegetable *veg = [[[VegetableManager sharedInstance] vegetables]objectAtIndex:indexPath.row];
        [[VegetableManager sharedInstance] removeVegetableWithName:veg.name];
        [_vegListView reloadData];
    }];
    return @[actionEdit,actionDelete];
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}


@end

storyboard.jpeg