Sqlite3的函數(shù)介紹
1.打開(kāi)數(shù)據(jù)庫(kù)
int sqlite3_open(
const char *filename, // 數(shù)據(jù)庫(kù)的文件路徑
sqlite3 **ppDb // 數(shù)據(jù)庫(kù)實(shí)例
);
2.執(zhí)行任何SQL語(yǔ)句
int sqlite3_exec(
sqlite3*, // 一個(gè)打開(kāi)的數(shù)據(jù)庫(kù)實(shí)例
const char *sql, // 需要執(zhí)行的SQL語(yǔ)句
int (*callback)(void*,int,char**,char**), // SQL語(yǔ)句執(zhí)行完畢后的回調(diào)
void *, // 回調(diào)函數(shù)的第1個(gè)參數(shù)
char **errmsg // 錯(cuò)誤信息
);
3.檢查SQL語(yǔ)句的合法性（查詢前的準(zhǔn)備）
int sqlite3_prepare_v2(
sqlite3 *db, // 數(shù)據(jù)庫(kù)實(shí)例
const char *zSql, // 需要檢查的SQL語(yǔ)句
int nByte, // SQL語(yǔ)句的最大字節(jié)長(zhǎng)度
sqlite3_stmt **ppStmt, // sqlite3_stmt實(shí)例，用來(lái)獲得數(shù)據(jù)庫(kù)數(shù)據(jù)
const char **pzTail
);
4.查詢一行數(shù)據(jù)
int sqlite3_step(sqlite3_stmt*); // 如果查詢到一行數(shù)據(jù)，就會(huì)返回SQLITE_ROW
5.利用stmt獲得某一字段的值（字段的下標(biāo)從0開(kāi)始）
double sqlite3_column_double(sqlite3_stmt*, int iCol); // 浮點(diǎn)數(shù)據(jù)
int sqlite3_column_int(sqlite3_stmt*, int iCol); // 整型數(shù)據(jù)
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); // 長(zhǎng)整型數(shù)據(jù)
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); // 二進(jìn)制文本數(shù)據(jù)
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); // 字符串?dāng)?shù)據(jù)
SQL語(yǔ)句介紹
------------------SQL語(yǔ)句初級(jí)-------------------
SQL語(yǔ)句格式記錄《重點(diǎn)內(nèi)容》
1.創(chuàng)建表
create table 表的名字(字段的名字 字段的類型,...)
create table if not exists 表的名字(字段的名字 字段的類型,...)
2.刪除表
drop table 表的名字
drop table if exists 表的名字
3.往表中插入數(shù)據(jù)
insert into 表的名字(字段的名字1,字段的名字2,...) values(內(nèi)容1,內(nèi)容2,...)
4.查詢表中的數(shù)據(jù)
select * from 表的名字 （*）表示查看表中所有的字段
select 字段名字1,字段名字2,... from 表的名字 （查詢指定字段信息）
5.條件語(yǔ)句
關(guān)鍵字 where
條件類型：＝邪蛔、is钳宪、!=、is not攻晒、>=绒净、<=餐抢、like
多條件連接的時(shí)候：and(&&)霍转、or(||)
結(jié)構(gòu):
where 字段的名字 ＝（條件的類型） 比較內(nèi)容 and(多條件連接) 字段的名字 like '%張%'
6.修改
update 表的名字 set 字典的名字＝內(nèi)容,... where...
7.刪除
delete from 表的名字 where 字段 ＝ 內(nèi)容
------------------SQL語(yǔ)句提高-------------------
1.排序
order by 字段名字,... asc(默認(rèn)升序)
order by 字段名字,... desc(降序)
2.翻頁(yè)（為了限制數(shù)據(jù)亮過(guò)大荐绝，造成的讀取過(guò)慢，內(nèi)存過(guò)大的問(wèn)題）
limit 從第幾個(gè)條內(nèi)容后開(kāi)始讀取,讀取多少條數(shù)據(jù)
3.多表查詢
select * from 表的名字1谴忧，表的名字2 where 表1.字段名字 ＝ 表2.字典名字
事例
select student.stu_name,class.class_name from student,class where student.classid = class.class_id
4.重命名
select s.stu_name,c.class_name from student as s,class as c where s.classid = c.class_id
------------------SQL語(yǔ)句進(jìn)階--------------------
1.不能為空
not null
2.唯一約束
unique
3.設(shè)置默認(rèn)值
default
4.自定增長(zhǎng)（必須是integer）
autoincrement
5.主鍵約束
primary key (由not null 和unique兩個(gè)約束的結(jié)合體)
例子：CREATE TABLE class (id integer primary key autoincrement,name not null,num integer default 1)
6.主外鍵約束
foreign key(classid) references class(id)
外鍵（字段）關(guān)聯(lián) 表2（主鍵字典）
例子：create table student(id integer primary key autoincrement,name text not null,classid integer not null, foreign key(classid) references class(id))
SQL自學(xué)網(wǎng)址：http://www.yiibai.com/sqlite/sqlite_overview.html
自己寫(xiě)了個(gè)Sqlite3Manager很泊，練練手角虫。
//
// Sqlite3Manager.h
// 數(shù)據(jù)庫(kù)demo
//
// Created by wyb on 2017/3/21.
// Copyright ? 2017年 xxx. All rights reserved.
//
#import <Foundation/Foundation.h>
typedef void(^Myblock) (NSArray * array);
@interface Sqlite3Manager : NSObject
+ (instancetype)shareManager;
/**
創(chuàng)建manager
@param name 數(shù)據(jù)庫(kù)的名字
@return manager
*/
- (instancetype)initWithDatabaseNamed:(NSString *)name;
/**
設(shè)置數(shù)據(jù)庫(kù)的名字
@param name 數(shù)據(jù)庫(kù)的名字
*/
- (void)setDataBaseName:(NSString *)name;
/**
創(chuàng)建表
@param sql sql語(yǔ)句
@return 如果執(zhí)行成功返回YES沾谓，否返回NO
*/
- (BOOL)createTableWithSql:(NSString *)sql;
/**
執(zhí)行數(shù)據(jù)庫(kù)表的（增，刪戳鹅，改）操作
@param sql sql語(yǔ)句
@param params sql語(yǔ)句對(duì)應(yīng)參數(shù)的添加
@return 如果執(zhí)行成功返回YES均驶，否返回NO
*/
- (BOOL)execTableWithSql:(NSString *)sql params:(NSArray *)params;
/**
執(zhí)行數(shù)據(jù)庫(kù)表的（查詢）操作異步操作
@param sql sql語(yǔ)句
@param params sql語(yǔ)句對(duì)應(yīng)參數(shù)的添加
@param block 返回的數(shù)據(jù)
*/
- (void) selectTableWithSql:(NSString *)sql params:(NSArray *)params finshBlock:(Myblock)block;
@end
//
// Sqlite3Manager.m
// 數(shù)據(jù)庫(kù)demo
//
// Created by wyb on 2017/3/21.
// Copyright ? 2017年 xxx. All rights reserved.
//
#import "Sqlite3Manager.h"
#import <sqlite3.h>
typedef void(^Myblock) (NSArray *);
@interface Sqlite3Manager ()
{
sqlite3 *db;
}
@property(nonatomic,strong)NSString *databaseName;
@end
@implementation Sqlite3Manager
+ (instancetype)shareManager
{
static Sqlite3Manager *manager = nil;
static dispatch_once_t onceToken;
dispatch_once(&onceToken, ^{
manager = [[Sqlite3Manager alloc]init];
});
return manager;
}
- (instancetype)initWithDatabaseNamed:(NSString *)name
{
self = [super init];
if (self) {
self.databaseName = name;
}
return self;
}
- (void)setDataBaseName:(NSString *)name
{
self.databaseName = name;
}
- (NSString *)getDatabaseFilePath
{
NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSString *dataBaseFilePath = [docPath stringByAppendingPathComponent:self.databaseName];
return dataBaseFilePath;
}
- (BOOL)openDataBase
{
NSString *filePath = [self getDatabaseFilePath];
int result = sqlite3_open(filePath.UTF8String, &db);
if (result == SQLITE_OK) {
NSLog(@"數(shù)據(jù)庫(kù)打開(kāi)成功");
return YES;
}else{
NSLog(@"數(shù)據(jù)庫(kù)打開(kāi)失敗");
return NO;
}
}
- (BOOL)createTableWithSql:(NSString *)sql
{
BOOL result = [self openDataBase];
if (result == YES) {
//定義編譯sql語(yǔ)句的變量（數(shù)據(jù)句柄）
sqlite3_stmt *stmt = NULL;
//開(kāi)始編譯sql語(yǔ)句
sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
//執(zhí)行sql語(yǔ)句
result = sqlite3_step(stmt);
if (result == SQLITE_ERROR) {
const char *errorMsg = sqlite3_errmsg(db);
NSString *error = [NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding];
NSLog(@"%@",error);
return NO;
}
//關(guān)閉數(shù)據(jù)句柄和數(shù)據(jù)庫(kù)
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return result;
}
}
- (BOOL)execTableWithSql:(NSString *)sql params:(NSArray *)params
{
BOOL result = [self openDataBase];
if (result == YES) {
//定義編譯sql語(yǔ)句的變量（數(shù)據(jù)句柄）
sqlite3_stmt *stmt = NULL;
//開(kāi)始編譯sql語(yǔ)句
sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
//綁定參數(shù)
for (int i = 0; i < params.count; i++) {
// 獲取參數(shù)內(nèi)容
id param = params[i];
if ([param isKindOfClass:[NSString class]] )
sqlite3_bind_text(stmt, i+1, [param UTF8String], -1, SQLITE_TRANSIENT);
if ([param isKindOfClass:[NSNumber class]] ) {
if (!strcmp([param objCType], @encode(float)))
sqlite3_bind_double(stmt, i+1, [param doubleValue]);
else if (!strcmp([param objCType], @encode(int)))
sqlite3_bind_int(stmt, i+1, [param intValue]);
else if (!strcmp([param objCType], @encode(BOOL)))
sqlite3_bind_int(stmt, i+1, [param intValue]);
else
NSLog(@"unknown NSNumber");
}
if ([param isKindOfClass:[NSDate class]]) {
sqlite3_bind_double(stmt, i+1, [param timeIntervalSince1970]);
}
if ([param isKindOfClass:[NSData class]] ) {
sqlite3_bind_blob(stmt, i+1, [param bytes], (int)[param length], SQLITE_STATIC);
}
}
//執(zhí)行sql語(yǔ)句
result = sqlite3_step(stmt);
if (result == SQLITE_ERROR) {
const char *errorMsg = sqlite3_errmsg(db);
NSString *error = [NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding];
NSLog(@"%@",error);
return NO;
}
//關(guān)閉數(shù)據(jù)句柄和數(shù)據(jù)庫(kù)
sqlite3_finalize(stmt);
sqlite3_close(db);
return YES;
}else{
return result;
}
}
- (NSArray *)selectTableWithSql:(NSString *)sql params:(NSArray *)params
{
BOOL result = [self openDataBase];
if (result == YES) {
//定義編譯sql語(yǔ)句的變量（數(shù)據(jù)句柄）
sqlite3_stmt *stmt = NULL;
//開(kāi)始編譯sql語(yǔ)句
BOOL prepareResult = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
if (prepareResult == SQLITE_ERROR) {
const char *errorMsg = sqlite3_errmsg(db);
NSString *error = [NSString stringWithCString:errorMsg encoding:NSUTF8StringEncoding];
NSLog(@"%@",error);
}
//綁定參數(shù)
for (int i = 0; i < params.count; i++) {
// 獲取參數(shù)內(nèi)容
id param = params[i];
if ([param isKindOfClass:[NSString class]] )
sqlite3_bind_text(stmt, i+1, [param UTF8String], -1, SQLITE_TRANSIENT);
if ([param isKindOfClass:[NSNumber class]] ) {
if (!strcmp([param objCType], @encode(float)))
sqlite3_bind_double(stmt, i+1, [param doubleValue]);
else if (!strcmp([param objCType], @encode(int)))
sqlite3_bind_int(stmt, i+1, [param intValue]);
else if (!strcmp([param objCType], @encode(BOOL)))
sqlite3_bind_int(stmt, i+1, [param intValue]);
else
NSLog(@"unknown NSNumber");
}
if ([param isKindOfClass:[NSDate class]]) {
sqlite3_bind_double(stmt, i+1, [param timeIntervalSince1970]);
}
if ([param isKindOfClass:[NSData class]] ) {
sqlite3_bind_blob(stmt, i+1, [param bytes], (int)[param length], SQLITE_STATIC);
}
}
NSMutableArray *resultsArray = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
int columns = sqlite3_column_count(stmt);
NSMutableDictionary *result = [[NSMutableDictionary alloc] initWithCapacity:columns];
for (int i = 0; i<columns; i++) {
const char *name = sqlite3_column_name(stmt, i);
NSString *columnName = [NSString stringWithCString:name encoding:NSUTF8StringEncoding];
int type = sqlite3_column_type(stmt, i);
switch (type) {
case SQLITE_INTEGER:
{
int value = sqlite3_column_int(stmt, i);
[result setObject:[NSNumber numberWithInt:value] forKey:columnName];
break;
}
case SQLITE_FLOAT:
{
float value = sqlite3_column_double(stmt, i);
[result setObject:[NSNumber numberWithFloat:value] forKey:columnName];
break;
}
case SQLITE_TEXT:
{
const char *value = (const char*)sqlite3_column_text(stmt, i);
[result setObject:[NSString stringWithCString:value encoding:NSUTF8StringEncoding] forKey:columnName];
break;
}
case SQLITE_BLOB:
{
int bytes = sqlite3_column_bytes(stmt, i);
if (bytes > 0) {
const void *blob = sqlite3_column_blob(stmt, i);
if (blob != NULL) {
[result setObject:[NSData dataWithBytes:blob length:bytes] forKey:columnName];
}
}
break;
}
case SQLITE_NULL:
[result setObject:[NSNull null] forKey:columnName];
break;
default:
{
const char *value = (const char *)sqlite3_column_text(stmt, i);
[result setObject:[NSString stringWithCString:value encoding:NSUTF8StringEncoding] forKey:columnName];
break;
}
}
}
[resultsArray addObject:result];
}
//關(guān)閉數(shù)據(jù)句柄和數(shù)據(jù)庫(kù)
sqlite3_finalize(stmt);
sqlite3_close(db);
return resultsArray;
}else{
return nil;
}
}
- (void) selectTableWithSql:(NSString *)sql params:(NSArray *)params finshBlock:(Myblock)block
{
dispatch_async(dispatch_get_global_queue(0, 0), ^{
NSArray *array =[self selectTableWithSql:sql params:params];
dispatch_async(dispatch_get_main_queue(), ^{
if (block) {
block(array);
}
});
});
}
@end
