45.数据库

1.基本概念

1. 什么是数据库 1.以一定方式储存在一起; 2.能为多个用户共享; 3.具有尽可能小得冗余度; 4.与程序彼此独立的数据集合;
2. 常见数据库1.SQL 2.My SQL 3.Oracle
3. 关系型数据库使用数据表管理和存储数据 1) 创建新的数据库 2) 创建新的数据库表
4. SQL语句
   1>>>- 数据插入命令(Insert)—————————————————————————
   INSERT INTO stuTable(name, age) VALUES (‘张三’,’22’)
   1) insert into …… values ……–关键字
   2) stuTable–表名
   3) name和age–表中的字段名称
   4) ‘张三’和’22’–要插入的数据
   2>>>- 数据更新命令(Update)————————————————————————–
   UPDATE stuTable SET name = ‘马云’,age = ‘33’ WHERE name = ‘张三’
   1) update …… set ……where……–关键字
   2) stuTable–表名
   3) name和age–表中的字段名称
   4) ‘马云’和’33’–要更新的数据内容
   5) ‘张三’–要更新的数据检索条件
   3>>>- 数据删除命令(Delete)—————————————————————————
   DELETE FROM stuTable WHERE name = ‘张阳阳’
   1) delete from ……where……–关键字
   2) stuTable–表名
   3) name—————表中的字段名称
   4) ‘张阳阳’–要删除的数据检索条件
   4>>>- 数据查询命令(Select)—————————————————————————
   //select * from stuTable 所有学生信息
   //select name from stuTable 所有姓名
   //select (name || age) from stuTable 所有姓名和年龄
   SELECT * FROM stuTable WHERE name = ‘张阳阳’
   1) select from ……where……–关键字
   2) stuTable–表名
   3) name–表中的字段名称
   4) ‘张阳阳’–要查询的数据检索条件
5. iOS的数据库技术的实现 , SQLite嵌入式数据库
   1) 支持事件,不需要配置,不需要安装,不需要管理员;
   2) 支持大部分SQL92;
   3) 完整的数据库保存在磁盘上⾯一个文件,同一个数据库文件可以在不同机器上面使用,最大支持数据库到2T
   4) 整个系统少于3万行代码,少于250KB的内存占用;
   5) 源代码开发,代码95%有较好的注释,简单易用的API;
6. 开始使用SQLite : Linux系统级的SQLite技术实现框架libsqlite3.0.dylib 1.引入系统sqlite3.h头⽂文件 2.打开数据库 3.执⾏行SQL命令 4.关闭数据库
7. SQLite常用系列函数 1.sqlite3_exec() 2.sqlite3_prepare_v2(),sqlite3_step(),sqlite3_finalize() 3.sqlite3_bind() 4.sqlite3_column()
8. 简单SQL语句
   

  create table if not exists stu(number integer primary key autoincrement, 
    name text, sex text, age integer, hobby text)
  insert into stu(name,sex,hobby,age) values ('张三','男','学习',22)
  update stu set  name = '马云', age= '33', hobby = '电商', sex = 男' where name = '张三'
  delete from stu where name = '张阳阳'
  在语句里*是通配符,通过*相当于代替了表里的所有的字段名
  select * from stu
  select name from stu
  select name,age from stu
  select * from stu where name = '张阳阳'

2.简单运用

Student文件

#import <Foundation/Foundation.h>
@interface Student : NSObject

@property(nonatomic,copy)NSString *name;
@property(nonatomic,copy)NSString *sex;
@property(nonatomic,copy)NSString *hobby;
@property(nonatomic,assign)NSInteger age;
- (instancetype)initWithName:(NSString *)name sex:(NSString *)sex hobby:(NSString *)hobby age:(NSInteger)age;
+ (instancetype)studentWithName:(NSString *)name sex:(NSString *)sex hobby:(NSString *)hobby age:(NSInteger)age;
@end

#import "Student.h"
@implementation Student
- (void)dealloc{
    [_name release];
    [_hobby release];
    [_sex release];
    [super dealloc];
}
- (instancetype)initWithName:(NSString *)name sex:(NSString *)sex hobby:(NSString *)hobby age:(NSInteger)age{
    self = [super init];
    if (self) {
        self.name = name;
        self.sex = sex;
        self.hobby = hobby;
        _age = age;
    }
    return self;
}
+ (instancetype)studentWithName:(NSString *)name sex:(NSString *)sex hobby:(NSString *)hobby age:(NSInteger)age{
    Student *student = [[Student alloc] initWithName:name sex:sex hobby:hobby age:age];
    return [student autorelease];
}
- (NSString *)description{
    return [NSString stringWithFormat:@"name = %@, age = %ld, sex = %@, hobby = %@", _name,_age,_sex,_hobby];
}
@end

DataBaseTool文件

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "Student.h"
@interface DataBaseTool : NSObject
{
    //创建一个数据库指针和跟随指针
    sqlite3 *dbPoint;
    sqlite3_stmt *stmt;
}
//为了保证当前数据库在工程里是唯一的,我们用单例方式创建一个数据库工具对象
+ (DataBaseTool *)shareDataBaseTool;
//打开数据库,数据库文件保存在沙盒的documents文件里面
- (void)openDB;
//给数据库创建张表格
- (void)createTable;
//插入一个学生信息
- (void)insertStr:(Student *)stu;
//更新表里的学生数据
- (void)updateStu:(Student *)stu;
//删除某个学生的数据
- (void)deleteStu:(NSString *)str;
//查询数据库中所有学生表里的数据
- (NSMutableArray *)selectAllStu;
//关闭数据库
- (void)closeDB;
@end

#import "DataBaseTool.h"
@implementation DataBaseTool
#if 0
//关键字@synchronized同步锁,表示同一时间只能有一个线程访问
+ (DataBaseTool *)shareDataBaseTool{
    //定义一个静态实例,并初始化为nil
    static DataBaseTool *tool;
    @synchronized(self){
        if(tool == nil){
            tool = [[DataBaseTool alloc] init];
        }
    }
    return tool;
}
#endif
+ (DataBaseTool *)shareDataBaseTool{
    static DataBaseTool *tool;
    static dispatch_once_t oneToken;
    dispatch_once(&oneToken, ^{
        tool = [[DataBaseTool alloc] init];
    });
    return tool;
}
- (void)openDB{//打开数据库
    NSArray *sandBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentPath = [sandBox[0] stringByAppendingPathComponent:@"Student.sqlite"];
    int result = sqlite3_open([documentPath UTF8String],&dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功:%@",documentPath);
    }else {
        NSLog(@"打开数据库失败:%d",result);
    }
}
- (void)createTable{//创建一张表
    // 1.primary key是主键,主键在当前表格里数据是唯一的,可以唯一标识一条数据,一般是整数 2.autoincrement自增,为了让主键不重复,主键采用自增的方式 3.if not exists如果没有表才会创建,防止重复覆盖之前的数据 4.数据库90%是sql语句出问题,所以先保证语句没问题再放到工程中使用
    NSString *sqlStr = @"create table if not exists stu(number integer primary key autoincrement, name text, sex text, age integer, hobby text)";
    //执行这条sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"表创建成功");
    }else {
        NSLog(@"表创建失败:%d",result);
    }
}
- (void)insertStr:(Student *)stu{//向表中插入一条数据
    //语句里值的位置要加上单引号
    NSString *sqlStr = [NSString stringWithFormat:@"insert into stu(name,age,sex,hobby) values ('%@','%ld','%@','%@')",stu.name,stu.age,stu.sex,stu.hobby];
    //执行sql语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加学生成功");
    }else {
        NSLog(@"添加学生失败:%d",result);
    }
}
- (void)updateStu:(Student *)stu{//更新某条数据
    NSString *sqlStr = [NSString stringWithFormat:@"update stu set sex = '%@', hobby = '%@', age = '%ld', name = '%@' where name = '张阳阳'",stu.sex,stu.hobby,stu.age,stu.name];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }else {
        NSLog(@"更新失败:%d",result);
    }
}
- (void)deleteStu:(NSString *)str{//删除某条数据
    NSString *sqlStr = [NSString stringWithFormat:@"delete from stu where name = '%@'",str];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else {
        NSLog(@"删除失败:%d",result);
    }
}
- (NSMutableArray *)selectAllStu{//选择某些数据
    //查询的逻辑 1.先从本地的数据库中读取某张表里的所有数据 2.然后逐条进行读取,对model进行赋值 3.把已经赋值好的model放到数组中,并且返回
    NSString *sqlStr = @"select * from stu";
    //第三个参数:查询语句字数限制,-1是没有限制
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    //这个方法相当于把数据库和跟谁指针相关联,一同完成查询功能
    //初始化一个用来装学生的数组
    NSMutableArray *stuArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //开始遍历查询数据库的每一行数据
        while(sqlite3_step(stmt) == SQLITE_ROW){
            //让跟随指针进行遍历查询,逐列的读取数据,如果没有行,才会停止循环
            //第二个参数表示当前这列数据在表的第几列
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *sex = sqlite3_column_text(stmt, 2);
            int age = sqlite3_column_int(stmt, 3);
            const unsigned char *hobby = sqlite3_column_text(stmt, 4);
            //把列里的数据在进行行类型的转换
            NSInteger stuAge = age;
            NSString *stuName = [NSString stringWithUTF8String:(const char *)name];
            NSString *stuSex = [NSString stringWithUTF8String:(const char *)sex];
            NSString *stuHobby = [NSString stringWithUTF8String:(const char *)hobby];
            Student *stu = [Student studentWithName:stuName sex:stuSex hobby:stuHobby age:stuAge];
            [stuArr addObject:stu];
        }
    }else {
        NSLog(@"查询失败:%d",result);
    }
    return stuArr;
}
- (void)closeDB{
    sqlite3_finalize(stmt);
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功");
    }else {
        NSLog(@"关闭数据库失败:%d",result);
    }
}
@end

ViewController.m文件

#import "ViewController.h"
#import "DataBaseTool.h"
#import "Student.h"
@interface ViewController ()
- (IBAction)openDataBase:(id)sender;
- (IBAction)createTable:(id)sender;
- (IBAction)insertData:(id)sender;
- (IBAction)updateData:(id)sender;
- (IBAction)deleteData:(id)sender;
- (IBAction)selectData:(id)sender;
- (IBAction)closeData:(id)sender;
@end
@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
}
- (IBAction)openDataBase:(id)sender {//打开数据库
    [[DataBaseTool shareDataBaseTool] openDB];
}
- (IBAction)createTable:(id)sender {//创建一张表
    [[DataBaseTool shareDataBaseTool] createTable];
}
//插入-->张阳阳--更新-->张三-->删除
- (IBAction)insertData:(id)sender {//向表中插入一条数据
    Student *student = [Student studentWithName:@"张阳阳" sex:@"男" hobby:@"鹰王" age:20];
    [[DataBaseTool shareDataBaseTool] insertStr:student];
}
- (IBAction)updateData:(id)sender {//更新数据
    Student *stu = [Student studentWithName:@"张三" sex:@"男" hobby:@"打球" age:33];
    [[DataBaseTool shareDataBaseTool] updateStu:stu];
}
- (IBAction)deleteData:(id)sender {//删除数据
    [[DataBaseTool shareDataBaseTool] deleteStu:@"张三"];
}
- (IBAction)selectData:(id)sender {//关闭数据库
    NSMutableArray *arr = [[DataBaseTool shareDataBaseTool] selectAllStu];
    for (Student *stu in arr) {
        NSLog(@"%@",stu);
    }
}
- (IBAction)closeData:(id)sender {
    [[DataBaseTool shareDataBaseTool] closeDB];
}
@end

3.用数据库进行本地收藏

模型Movie文件

#import <Foundation/Foundation.h>
@interface Movie : NSObject
@property(nonatomic,copy)NSString *movieName;
@property(nonatomic,copy)NSString *movieID;
@property(nonatomic,copy)NSString *pic_url;
@end

#import "Movie.h"
@implementation Movie
- (void)dealloc
{
    [_movieName release];
    [_movieID release];
    [_pic_url release];
    [super dealloc];
}
@end

单例和收藏工具DataBaseTool文件

#import <Foundation/Foundation.h>
#import <sqlite3.h>//引入系统的数据库框架sqlite3.h
@class Movie;
//写一个枚举用来表示是否收藏和查询失败
typedef NS_ENUM(NSUInteger, SelectInTable) {
    InIable,
    NotInTable,
    SelectError,
};
@interface DataBaseTool : NSObject
{
    //创建一个数据库指针和跟随指针
    sqlite3 *dbPoint;
    sqlite3_stmt *stmt;
}
+ (DataBaseTool *)shareDataBaseTool;
- (SelectInTable)isInTable:(Movie *)movie;//判断是否收藏
- (void)insertMovie:(Movie *)movie;
- (void)deleteMovie:(Movie *)movie;
@end

#import "DataBaseTool.h"
#import "Movie.h"
@implementation DataBaseTool
+ (DataBaseTool *)shareDataBaseTool{
    static DataBaseTool *tool;
    static dispatch_once_t oneToken;
    dispatch_once(&oneToken, ^{
        if (tool == nil) {
            tool = [[DataBaseTool alloc] init];
            //在单例的这里面只会调用一次,所以也只打开一次和创建一次数据库
            //这两个方法不详让外界用,所以声明为私有方法
            [tool openDB];//打开数据库
            [tool createTable];//创建数据库
        }
    });
    return tool;
}
- (void)openDB{
    NSArray *sandBoxPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentPath = [sandBoxPath[0] stringByAppendingPathComponent:@"Movie.sqlite"];
    //根据指定的路径打开数据库,并且把地址赋值给dbPoint,如果没有就直接创建,再给dbPoint赋值
    int result = sqlite3_open([documentPath UTF8String], &dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功:%@",documentPath);
    }else{
        NSLog(@"打开数据库失败:%d",result);
    }
}
- (void)createTable{
    NSString *sqlStr = @"create table if not exists movieTable(number integer primary key autoincrement, movieName text, movieID integer,pic_url text)";
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else {
        NSLog(@"创建表失败%d",result);
    }
}
- (SelectInTable)isInTable:(Movie *)movie{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from movieTable where movieID = '%@'",movie.movieID];
    int result = sqlite3_prepare_v2(dbPoint, [sqlStr UTF8String], -1, &stmt, nil);
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) {
            NSLog(@"已经收藏过了");
            return InIable;
        }else{
            NSLog(@"还没有收藏过");
            return NotInTable;
        }
    }
    else{
        NSLog(@"查询失败%d",result);
        return SelectError;
    }
}
- (void)insertMovie:(Movie *)movie{
    NSString *sqlStr = [NSString stringWithFormat:@"insert into movieTable(movieName,movieID,pic_url) values ('%@','%@','%@')",movie.movieName,movie.movieID,movie.pic_url];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
         NSLog(@"收藏成功");
    }else{
        NSLog(@"收藏失败:%d",result);
    }
}
- (void)deleteMovie:(Movie *)movie{
    NSString *sqlStr = [NSString stringWithFormat:@"delete from movieTable where movieID = '%@'",movie.movieID];
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败:%d",result);
    }
}
@end

MainViewController.m文件

- (void)saveMove{
    Movie *movie = [[Movie alloc] init];
    movie.movieID = @"1001";
    movie.movieName = @"终结者";
    movie.pic_url = @"zhongjiezhe.png";//先判断有没有收藏
    SelectInTable result = [[DataBaseTool shareDataBaseTool] isInTable:movie];
    if (result == NotInTable){//没有收藏就调用收藏方法
        [[DataBaseTool shareDataBaseTool] insertMovie:movie];
    }//然后再删除该收藏
    [[DataBaseTool shareDataBaseTool] deleteMovie:movie];
}

4.iOS sqlite3保存数组NSArray

四个注意点：  
1.创建表时，声明字段类型为二进制 blob  
NSString *INEditorTableName = @"create table In_Editor(picUrls blob not null)";  

2.插入sql语句，把NSArray转换为NSData类型  
NSData *dataImageUrls = [NSKeyedArchiver archivedDataWithRootObject:model.imageUrls];  

3.不能用 [NSString stringWithFormat: @"sql"];  
[_db executeUpdate: @"insert into In_Editor ( picUrls) values (?);",dataImageUrls];  

4.读取数据: NSArray *imageArr = [NSKeyedUnarchiver unarchiveObjectWithData:picUrls]  

// 如果不注意，上面几点，可能会出现下面错误：  
* Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: '-[__NSCFData objectForKey:]: unrecognized selector sent to instance 0x6b46c80'

    注：  
    NSArray 与 NSData的相互转换 二进制  
    将NSArray转化为NSData类型 ：NSData *data = [NSKeyedArchiver archivedDataWithRootObject:Array];  
    将NSData转化为NSArray类型 ：NSArray *array = [NSKeyedUnarchiver unarchiveObjectWithData:data];  

    Integer:值是signedinteger类型，大小可以是1,2,3,4,6,8bytes  
    REAL:浮点类型  
    TEXT:以UTF-8,UTF-16BEorUTF-16LE编码存储的字符类型  
    BLOB:二进制数据