WCDB的使用--增删改查

首先Message表的字段如下：

typedef NS_ENUM(NSInteger, MessageType) {
    MessageTypeOne = 1,
    MessageTypeTwo = 2,
    MessageTypeThree = 3,
};

@interface Message : NSObject

@property(nonatomic, assign) int32_t localID;
@property(nonatomic, assign) uint64_t count;
@property(nonatomic, strong) NSString *content;
@property(nonatomic, strong) NSDate *createTime;
@property(nonatomic, strong) NSDate *modifiedTime;
@property(nonatomic, assign) MessageType type;
@property(nonatomic, strong) NSArray *array;
@property(nonatomic, assign) uint64_t msgId;
@property(nonatomic, strong) NSString *uuid;

@end

一、增

1.插入一条数据

SQL: INSERT INTO message(localID,count,content,createTime,modifiedTime,type,array,msgId,uuid) VALUES(?,?,?,?,?,?,?,?,?)

BOOL result = [self.database insertObject:message into:@"message"];

2.插入批量数据

NSArray <Message *> *dbArr;
BOOL result = [self.database insertObjects:dbArr into:@"message"];

3.插入，如果存在则替换

SQL: INSERT OR REPLACE INTO message(localID,count,content,createTime,modifiedTime,type,array,msgId,uuid) VALUES(?,?,?,?,?,?,?,?,?)

BOOL result = [self.database insertOrReplaceObjects:dbArr into:@"message"];

二、删

SQL: DELETE FROM message WHERE localID = 11;

BOOL result = [self.database deleteObjectsFromTable:@"message" where:Message.localID == 11];

三、改

1.修改某列数据（只修改部分数据加个where语句）

SQL: UPDATE message SET content="Hello, Wechat!";

有两种方法：

第一种：
BOOL result = [self.database updateAllRowsInTable:@"message" onProperty:Message.content withValue:@"Hello, Wechat!"];

第二种：
Message *message = [[Message alloc] init];
message.content = @"Hello, Wechat!";

BOOL result = [self.database updateAllRowsInTable:@"message" onProperty:Message.content withObject:message];

2.修改多列数据：

SQL: UPDATE message SET content="Hello, WCDB! 2", count=100 WHERE localID=2;

同样有两种方法：

第一种：
Message *message = [[Message alloc] init];
message.content = @"Hello, WCDB! 2";
message.count = 100;

NSArray *row = @[message.content, @(message.count)];
BOOL result = [self.database updateRowsInTable:@"message"
                                  onProperties:{Message.content, Message.count}
                                       withRow:row
                                         where:Message.localID == 2];
                                         
                                         
第二种：
BOOL result = [self.database updateRowsInTable:@"message"
                                  onProperties:{Message.content, Message.count}
                                    withObject:message
                                         where:Message.localID == 2];

四、查

1.查询一条数据

QSL：SELECT * FROM message WHERE msgId = 6301664026019662621;

Message *msg = [self.database getOneObjectOfClass:Message.class fromTable:@"message" where:Message.msgId == 6301664026019662621];

2.查询多行数据

QSL：SELECT * FROM message WHERE localID > 10 AND localID < 100 ORDER BY localID DESC;

NSArray<Message *> *message = [self.database getObjectsOfClass:Message.class
                                                         fromTable:@"message"
                                                             where:Message.localID > 10 && Message.localID < 100
                                                           orderBy:Message.localID.order(WCTOrderedDescending)];

3.查询行数

QSL：SELECT COUNT(*) FROM message WHERE localID > 10 AND localID < 100;

NSNumber *count = [self.database getOneValueOnResult:Message.AnyProperty.count()
                                               fromTable:@"message"
                                                   where:Message.localID > 10 && Message.localID < 100];

4.函数计算(单个)

QSL：SELECT SUM(localID) FROM message;

NSNumber *sum = [self.database getOneValueOnResult:Message.localID.sum()
                                             fromTable:@"message"];

5.函数计算(多个)

QSL：SELECT MAX(localID), MIN(localID) FROM Message;

WCTOneRow *row = [self.database getOneRowOnResults:{Message.localID.max(), Message.localID.min()}
                                             fromTable:@"message"];
NSNumber *intValueMax = (NSNumber *) row[0];
NSNumber *intValueMin = (NSNumber *) row[1];

6.SELECT IN 查询

QSL：SELECT * FROM message WHERE localID IN (1,6,3);

NSArray *localIDs = @[@(1), @(6), @(3)];
NSArray<Message *> *message = [self.database getObjectsOfClass:Message.class fromTable:@"message" where:Message.localID.in(localIDs)];

QSL：SELECT * FROM message WHERE localID NOT IN (SELECT localID FROM message WHERE localID > 100);

NSArray<Message *> *msgs = [self.database getObjectsOfClass:Message.class fromTable:@"message" where:Message.localID.notIn([self.database getOneColumnOnResult:Message.localID fromTable:@"message"  where:Message.localID > 100])];

7.LIKE查询

QSL：SELECT * FROM message WHERE content like "%WCDB%";

NSString *param = @"%%WCDB%%";
NSArray<Message *> *messages = [self.database getObjectsOfClass:Message.class
                                                fromTable:@"message"
                                                    where:Message.content.like(param)];

结尾：

以上列举了WCDB的一些常用语句操作，如果有疑问可以在下面留言，WCDB后续我会再写一篇关于一些特殊语句需要用到核心层接口的文章，如果本篇文章对你有所帮助或者启发，麻烦点个赞👍，谢谢~