表 (table)
记录(row)
字段(column)
NULL: 表示该值为NULL值
INTEGER: 无符号整型值
REAL: 浮点值
TEXT: 文本字符串
BLOB: 二进制数据（比如文件）
sqlite3基本语句：
（1）增：insert into 表名（字段1，字段2。。。。）values（字段1的值，字段2的值。。。。）；
例如:INSERT INTO user(name, age) VALUES ('%@', %zd)
（2）删：delete from 表名 条件；//若没条件则会删除整个表里所有的记录
例如：delete from user where id = %zd
（3）改：update 表名 set 字段1 = 字段1的值， 字段2 = 字段2的值，。。。。。条件；//若没条件则会讲整个表相对应得字段全部更新
例如：update user set name = '%@', age = %zd where id = %zd
（4）查：select * from 表名；
select * from 表名 order by 字段
例子：查询所有数据：select * from user
查询所有数据，根据年龄降序查询：select * from user order by age desc//升序只需把desc改成asc；若有多个条件，及优先级从左往右依次降低
where用法：
where 字段 = 某个值and字段 > 某个值； //and相当于&&
where 字段 = 某个值 or 字段 = 某个值; //or相当于 ||
例如1：where age = 10 and name = 'ljl';
例如2：where age > 10 and age <20;
例如3：where age > 10 or name != 'ljl';
本地数据库实例:
//=======================ViewController.h===================
#import <UIKit/UIKit.h>
@interface ViewController : UIViewController
@end
//=======================ViewController.m==================
#import "ViewController.h"
#import "sqlite3Controller.h"
@interface ViewController ()
@property (nonatomic, strong)sqlite3Controller *sqlite;
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
// Do any additional setup after loading the view, typically from a nib.
UIButton *btnCreatDB = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnCreatDB.frame = CGRectMake(110, 100, 100, 30);
[btnCreatDB setTitle:@"创建数据库"forState:UIControlStateNormal];
[btnCreatDB addTarget:self action:@selector(btnCreatDB) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnCreatDB];
UIButton *btnCreatTable = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnCreatTable.frame = CGRectMake(110, 150, 100, 30);
[btnCreatTable setTitle:@"创建数据表"forState:UIControlStateNormal];
[btnCreatTable addTarget:self action:@selector(btnCreatTable) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnCreatTable];
UIButton *btnInsert = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnInsert.frame = CGRectMake(110,200, 100, 30);
[btnInsert setTitle:@"插入记录"forState:UIControlStateNormal];
[btnInsert addTarget:self action:@selector(btnInsert) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnInsert];
UIButton *btnSelect = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnSelect.frame = CGRectMake(110, 250, 100, 30);
[btnSelect setTitle:@"查询记录"forState:UIControlStateNormal];
[btnSelect addTarget:self action:@selector(btnSelect) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnSelect];
UIButton *btnAlert = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnAlert.frame = CGRectMake(110, 300, 100, 30);
[btnAlert setTitle:@"修改记录"forState:UIControlStateNormal];
[btnAlert addTarget:self action:@selector(btnAlert) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnAlert];
UIButton *btnRemove = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnRemove.frame = CGRectMake(110, 350, 100, 30);
[btnRemove setTitle:@"删除记录"forState:UIControlStateNormal];
[btnRemove addTarget:self action:@selector(btnRemove) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnRemove];
UIButton *btnRemoveTable = [UIButton buttonWithType:UIButtonTypeRoundedRect];
btnRemoveTable.frame = CGRectMake(110, 400, 100, 30);
[btnRemoveTable setTitle:@"删除表"forState:UIControlStateNormal];
[btnRemoveTable addTarget:self action:@selector(btnRemoveTable) forControlEvents:UIControlEventTouchUpInside];
[self.view addSubview:btnRemoveTable];
}
-(void)btnAlert
{
Person *person = [Person personWithName:@"ljl" andAge:20];
person.ID = 2;
[self.sqlite alertDB:person];
}
-(void)btnCreatDB
{
self.sqlite = [sqlite3Controller getInstance];
[self.sqlite openDB];
}
-(void)btnInsert
{
int i = arc4random_uniform(18)+10;
NSString *name = [NSString stringWithFormat:@"江龙%d",i];
Person *p = [Person personWithName:name andAge:i];
[self.sqlite addPerson:p];
}
-(void)btnCreatTable
{
[self.sqlite creatDB];
}
-(void)btnSelect
{
[self.sqlite selectAllPerson];
// NSArray *arr = [self.sqlite selectAllPerson];
// NSLog(@"arr = %@",arr);
}
-(void)btnRemove
{
[self.sqlite remove:2];
}
-(void)btnRemoveTable
{
[self.sqlite removeTable];
}
- (void)didReceiveMemoryWarning {
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
//==================================Person.h================================
#import <Foundation/Foundation.h>
@interface Person : NSObject
@property (nonatomic, assign) NSInteger ID;
@property (nonatomic, strong) NSString *name;
@property (nonatomic, assign) NSInteger age;
+ (id)personWithName:(NSString *) name andAge:(NSInteger)age;
//==================================Person.m===============================
#import "Person.h"
@implementation Person
+(id)personWithName:(NSString *)name
andAge:(NSInteger)age
{
Person *p = [[Person alloc]init];
p.name = name;
p.age = age;
return p;
}
-(NSString *)description
{
return [NSString stringWithFormat:@"person:%p ID:%zd name:%@ age:%zd", self, self.ID, self.name, self.age];
}
@end
//==================================sqlite3Controller.h============================
#import <UIKit/UIKit.h>
#import <sqlite3.h>
#import "Person.h"
@interface sqlite3Controller : UIViewController
{
sqlite3 *db;
}
+(sqlite3Controller *)getInstance;
-(void)openDB;
-(void)creatDB;
-(void)addPerson:(Person *)person;
-(void)sqlExec:(NSString *)sql andDescription:(NSString *)description;
-(void)remove:(NSInteger)ID;
-(void)removeTable;
-(void)alertDB:(Person *)person;
-(NSArray *)selectAllPerson;
@end
@end
//=============================sqlite3Controller.m============================
#import "sqlite3Controller.h"
#import "Person.h"
@interface sqlite3Controller ()
@end
@implementation sqlite3Controller
/**
* 实现单例
*/
static sqlite3Controller *instance = nil;
+(sqlite3Controller *)getInstance
{
if (instance == nil) {
instance = [[sqlite3Controller alloc]init];
}
return instance;
}
+(id)allocWithZone:(struct _NSZone *)zone
{
if (instance == nil) {
instance = [super allocWithZone:zone];
}
return instance;
}
/**
* 初始化时便新建打开数据库
*
*/
-(id)init
{
if ([super init]) {
[self openDB];
[self creatDB];
}
return self;
}
- (void)viewDidLoad
{
[super viewDidLoad];
[self.view setBackgroundColor:[UIColor redColor]];
// Do any additional setup after loading the view.
// //打开数据库
// [self openDB];
// //创建数据库
// [self creatDB];
}
#pragma mark - 创建/打开数据库
-(void)openDB
{
NSString *dock = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
NSString *dbName =[dock stringByAppendingPathComponent:@"first.db"];
NSLog(@"%@",dock);
// db = NULL;
if (sqlite3_open(dbName.UTF8String, &db) == SQLITE_OK) {
NSLog(@"创建/打开数据库成功");
}else
{
NSLog(@"创建/打开数据库失败 ");
}
}
#pragma mark -创建数据表
-(void)creatDB
{
NSString *sql = @"CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER )";
[self sqlExec:sql andDescription:@"创建表"];
}
#pragma mark -单步执行
-(void)sqlExec:(NSString *)sql andDescription:(NSString *)description
{
char *errmsg;
if (SQLITE_OK == sqlite3_exec(db, sql.UTF8String, nil, nil, &errmsg)) {
NSLog(@"%@成功", description);
}else
{
NSLog(@"%@失败", description);
}
}
#pragma mark -添加记录
-(void)addPerson:(Person *)person
{
NSString *sql = [NSString stringWithFormat:@"INSERT INTO user(name, age) VALUES ('%@', %zd)",person.name,person.age];
[self sqlExec:sql andDescription:@"添加记录"];
// sqlite3_close(db);
}
#pragma mark -查询记录
-(NSArray *)selectAllPerson{
NSString *sql = @"select * from";
//设置一个句柄
sqlite3_stmt *stmt;
//声明一个数组，在判断查询语句成功后初始化
NSMutableArray *allPersons = nil;
//评估sql语句语法是否正确
if (SQLITE_OK == sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL)) {
//查询语句正确后进行初始化数组
allPersons = [NSMutableArray array];
//单步执行方法，依次取得结果
while(SQLITE_ROW == sqlite3_step(stmt)) {
int ID = sqlite3_column_int(stmt, 0);
const unsigned char *name = sqlite3_column_text(stmt, 1);
int age = sqlite3_column_int(stmt, 2);
NSString *nameUTF8 = [NSString stringWithUTF8String:(const char *)name];
// NSLog(@"id = %d\n name = %@\n age = %d",ID, nameUTF8,age);
Person *p = [Person personWithName:nameUTF8 andAge:age];
p.ID = ID;
[allPersons addObject:p];
}
}else {
NSLog(@"sql语法错误");
}
NSLog(@"allPersons = %@", allPersons);
return allPersons;
}
#pragma mark -修改个人记录
-(void)alertDB:(Person *)person
{
NSString *sql = [NSString stringWithFormat:@"update user set name = '%@', age = %zd where id = %zd", person.name, person.age, person.ID];
[self sqlExec:sql andDescription:@"更新数据"];
[self selectAllPerson];
}
#pragma mark -删除个人记录
-(void)remove:(NSInteger)ID
{
NSString *sql = [NSString stringWithFormat:@"delete from user where id = %zd",ID];
[self sqlExec:sql andDescription:@"删除数据"];
}
#pragma mark -删除表
-(void)removeTable{
NSString *sql = @"drop table user";
char *errmsg;
if (SQLITE_OK == sqlite3_exec(db, sql.UTF8String, nil, nil, &errmsg)) {
NSLog(@"删除数据库成功!");
}else
{
NSLog(@"删除数据库失败!");
}
}
@end
