iOS中的数据库

来源：互联网发布：自动建站系统源码编辑：程序博客网时间：2024/06/04 00:39

主键：唯一且不为空

外键：表与表之间一一对应的关系称为外键

插入

insert into 。。。。。values。。。。关键字

Demo_Table。。。。。表名

demo_id和demo_name 。。。。。。。表中的字段名称

1和xiaohao 。。。。。。。。。。要插入的数据

修改uodate

update ….set……where…关键字

删除

delete from。。。。where 关键字

查询

select... from where

进入终端

sqlite3 包名

创建一个表

create table Novel (nid int,author text,bookname text,type text);

查询表名: 。table

插入数据

insert into Novel (nid,author,bookname) values (1,'天婵土豆','都破苍穹',);

创建以后工程将数据库导入工程中的Supporting Files包下然后选中工程 TARGETS下的工程－》 Build Phases->Link Binary With Librares 添加libsqlite3.dylib

#import <UIKit/UIKit.h>

@interface MainViewController : UIViewController

@end

#import "MainViewController.h"

#import "Db.h"

#import "NovelDataBase.h"

@interface MainViewController ()

@end

@implementation MainViewController

- (void)dealloc

{

[super dealloc];

}

- (id)initWithNibName:(NSString *)nibNameOrNil bundle:(NSBundle *)nibBundleOrNil

{

self = [super initWithNibName:nibNameOrNil bundle:nibBundleOrNil];

if (self) {

// Custom initialization

}

return self;

}

- (void)loadView

{

[super loadView];

}

- (void)viewDidLoad

{

[super viewDidLoad];

// Do any additional setup after loading the view.

self.navigationController.navigationBar.translucent = NO;

[self door];

}

- (void)didReceiveMemoryWarning

{

[super didReceiveMemoryWarning];

// Dispose of any resources that can be recreated.

}

#pragma mark程序入口

- (void)door

{

[Db openDB]; //将数据从数据库中存储到本地

// NSLog(@"%@",[NovelDataBase selectAllNovel]); //查询数据

// NSArray *array = [NovelDataBase selectAllNovel];

// for (NovelModel *model in array) {

// NSLog(@"%d,%@,%@,%f",model.nid,model.nauthor,model.nbookname,model.nscore);

// }

// NSLog(@"%@",[NovelDataBase selectWithId:1]); //根据id查询数据

// //主键不能唯一

// NovelModel *model = [NovelModel novelModelWithNid:2 nauthor:@"lovelin" nbookname:@"我的君" nscore:100];//创建一个添加的对象

// [NovelDataBase insertWithModel:model];//添加数据

// NSLog(@"%@",[NovelDataBase selectAllNovel]); //查询数据

// [NovelDataBase updateNovelModelWithId:1 name:@"我的君"];

// NSLog(@"%@",[NovelDataBase selectAllNovel]); //查询数据

// NSArray *array = [NovelDataBase selectAllNovel];

// for (NovelModel *model in array) {

// NSLog(@"%d,%@,%@,%f",model.nid,model.nauthor,model.nbookname,model.nscore);

// }

[NovelDataBase deleteNovelWithId:1];

NSArray *array = [NovelDataBase selectAllNovel];

for (NovelModel *model in array) {

NSLog(@"%d,%@,%@,%f",model.nid,model.nauthor,model.nbookname,model.nscore);

}

#pragma mark - Navigation

// In a storyboard-based application, you will often want to do a little preparation before navigation

- (void)prepareForSegue:(UIStoryboardSegue *)segue sender:(id)sender

{

// Get the new view controller using [segue destinationViewController].

// Pass the selected object to the new view controller.

}

@end

#import <Foundation/Foundation.h>

#import <sqlite3.h>

@interface Db : NSObject

+ (sqlite3 *)openDB;

@end

#import "Db.h"

#import <sqlite3.h> // 1 引入头文件

static sqlite3 *dbPoint = nil; // 2 定义数据库的指针

@implementation Db

+ (sqlite3 *)openDB // 3 打开数据库

{

//如果已经获得了数据库的指针直接返回指针

if (dbPoint) {

return dbPoint;

}

NSArray *arrayPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

NSString *strPath = [arrayPath firstObject];

strPath = [NSString stringWithFormat:@"%@/db",strPath];

NSLog(@"strpath =======%@",strPath);

//判断文件是否存在存在不拷贝否则拷贝

if (![[NSFileManager defaultManager] fileExistsAtPath:strPath]) {

//获得bundle包的路径第一个参数是数据库名字第二个是数据库类型

NSString *sourcePath = [[NSBundle mainBundle]pathForResource:@"Datavase" ofType:@"sqlite"];

NSLog(@"sourcePath======%@",sourcePath);

NSError *error = nil;

[[NSFileManager defaultManager] copyItemAtPath:sourcePath toPath:strPath error:&error];

if (error) {

NSLog(@"error===%@",error);

}

//创建数据库的指针对象

sqlite3_open([strPath UTF8String], &dbPoint);

return dbPoint;

}

@end

#import <Foundation/Foundation.h>

#import "NovelModel.h"

@interface NovelDataBase : NSObject

+ (NSArray *)selectAllNovel;

+ (NovelModel *)selectWithId:(NSInteger)num;

+ (void)insertWithModel : (NovelModel *)model;

+ (void)updateNovelModelWithId : (NSInteger)num name : (NSString *)name;

+ (void)deleteNovelWithId : (NSInteger)num;

@end

#import "NovelDataBase.h"

#import "Db.h"

#import "NovelModel.h"

@implementation NovelDataBase

#pragma mark查询所有数据

+ (NSArray *)selectAllNovel

{

//创建一个数组用于存放数据

NSMutableArray *results = [NSMutableArray array];

//获得数据库指针

sqlite3 *db = [Db openDB];

//创建一个数据库的替身

sqlite3_stmt *stmt = nil;

//数据库查询语句

NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel"];

//通过sql语句进行查询并且将查询结果赋值给替身第一个参数是数据库指针第二个参数是sql语句第三个参数是对sql语句的长度限制第四个参数是替身地址第五个参数写null

int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);//作用：检验sql语句是否正确！

if (result == SQLITE_OK) {

while (sqlite3_step(stmt) == SQLITE_ROW) {

int nid = sqlite3_column_int(stmt, 0); //第一个参数是替身第二个参数是sql语句中要查的列的序号从0开始

const unsigned char *nauthor = sqlite3_column_text(stmt, 1);

const unsigned char *nbookname = sqlite3_column_text(stmt, 2);

float nscore = sqlite3_column_double(stmt, 3);

//转换数据类型

NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];

NSString *bookname = [NSString stringWithUTF8String:(const char *)nbookname];

//将数据库返回来的数据封装到一个model对象中

NovelModel *model = [NovelModel novelModelWithNid:nid nauthor:author nbookname:bookname nscore:nscore];

//将model对象添加到数组中

[results addObject:model];

}

//销毁替身

sqlite3_finalize(stmt);

return results;

}

#pragma mark根据id查询相应的数据

+ (NovelModel *)selectWithId:(NSInteger)num

{

//获得数据库指针

sqlite3 *db = [Db openDB];

//创建一个替身

sqlite3_stmt *stmt = nil;

//数据库语句

NSString *sqlStr = [NSString stringWithFormat:@"select nv_id,nv_author,nv_bookname,nv_score from Novel where nv_id = %d",num];

int result = sqlite3_prepare_v2(db, [sqlStr UTF8String], -1, &stmt, NULL);

if (result == SQLITE_OK) {

if (sqlite3_step(stmt) == SQLITE_ROW) {

int nid = sqlite3_column_int(stmt, 0); //第一个参数是替身第二个参数是sql语句中要查的列的序号从0开始

const unsigned char *nauthor = sqlite3_column_text(stmt, 1);

const unsigned char *nbookname = sqlite3_column_text(stmt, 2);

float nscore = sqlite3_column_double(stmt, 3);

//转换数据类型

NSString *author = [NSString stringWithUTF8String:(const char *)nauthor];

NSString *bookname = [NSString stringWithUTF8String:(const char *)nbookname];

//将数据库返回来的数据封装到一个model对象中

NovelModel *model = [NovelModel novelModelWithNid:nid nauthor:author nbookname:bookname nscore:nscore];

sqlite3_finalize(stmt);

return model;

}

return nil;

}

#pragma mark插入

+ (void)insertWithModel : (NovelModel *)model

{

sqlite3 *db = [Db openDB];

NSString *sqlStr = [NSString stringWithFormat:@"insert into Novel (nv_id,nv_author,nv_bookname,nv_score) values (%d,'%@','%@',%f)",model.nid,model.nauthor,model.nbookname,model.nscore];

int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

if (result == SQLITE_OK) {

NSLog(@"添加成功");

}

#pragma mark修改

+ (void)updateNovelModelWithId : (NSInteger)num name : (NSString *)name

{

//获得数据库指针

sqlite3 *db = [Db openDB];

//数据库语句

NSString *sqlStr = [NSString stringWithFormat:@"update Novel set nv_bookname = '%@' where nv_id = %d",name,num];

int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

if (result == SQLITE_OK) {

NSLog(@"修改成功");

}

#pragma mark删除

+ (void)deleteNovelWithId : (NSInteger)num

{

//获得数据库指针

sqlite3 *db = [Db openDB];

//数据库语句

NSString *sqlStr = [NSString stringWithFormat:@"delete from Novel where nv_id = %d",num];

int result = sqlite3_exec(db, [sqlStr UTF8String], NULL, NULL, NULL);

if (result == SQLITE_OK) {

NSLog(@"删除成功");

}

@end

#import <Foundation/Foundation.h>

//用于接收从数据库返回的数据

@interface NovelModel : NSObject

@property (nonatomic,assign) NSInteger nid;

@property (nonatomic,retain) NSString *nauthor;

@property (nonatomic,retain) NSString *nbookname;

@property (nonatomic,assign) CGFloat nscore;

- (id)initWithNid :(NSInteger)nid nauthor : (NSString *)nauthor nbookname : (NSString *)nbookname nscore : (CGFloat)nscore;

+ (id)novelModelWithNid :(NSInteger)nid nauthor : (NSString *)nauthor nbookname : (NSString *)nbookname nscore : (CGFloat)nscore;

@end

#import "NovelModel.h"

@implementation NovelModel

- (void)dealloc

{

[_nauthor release];

[_nbookname release];

_nbookname = nil;

_nauthor = nil;

[super dealloc];

}

- (id)initWithNid :(NSInteger)nid nauthor : (NSString *)nauthor nbookname : (NSString *)nbookname nscore : (CGFloat)nscore

{

self = [super init];

if (self) {

self.nid = nid;

self.nauthor = nauthor;

self.nbookname = nbookname;

self.nscore = nscore;

}

return self;

}

+ (id)novelModelWithNid :(NSInteger)nid nauthor : (NSString *)nauthor nbookname : (NSString *)nbookname nscore : (CGFloat)nscore

{

NovelModel *novelModel = [[NovelModel alloc]initWithNid:nid nauthor:nauthor nbookname:nbookname nscore:nscore];

return [novelModel autorelease];

}

@end

0 0