SQLite这部分玩过数据库的只要学习一下常用的函数以及用法，SQL语法这块基本都差不多了解一下基本OK。以前做C#,对数据库还是蛮自信的。
一、SQLite使用准备
添加框架、引入头文件
二、SQLite 例子
//
// ViewController.m
// SQLite
//
// Created by cyw on 15-4-25.
// Copyright (c) 2015年 cyw. All rights reserved.
//
#import "ViewController.h"
#import <sqlite3.h>
@interface ViewController ()
@property(nonatomic,assign)sqlite3 *db;
@end
@implementation ViewController
- (void)viewDidLoad {
[super viewDidLoad];
//指定数据库路径,存放位置
NSString *documentPath=[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
NSString *filePath=[documentPath stringByAppendingPathComponent:@"CYW.sqlite"];
NSLog(@"%@",filePath);
// sqlite3是一种类型，db是数据库的句柄，就是数据库的象征，如果要进行增删改查，就得操作db这个实例
// 打开数据库
// SQLITE_API int sqlite3_open(
// const char *filename, /* Database filename (UTF-8) */
// sqlite3 **ppDb /* OUT: SQLite db handle */
// );
// 参数1:数据库路径名 参数类型const char 所以要将oc字符串转换一下 文件不存在时自动创建
// 参数2:数据库句柄
// 返回int类型:表示打开数据库成功失败 SQLITE_OK打开成功 否则失败
int result=sqlite3_open(filePath.UTF8String, &_db);
if (result==SQLITE_OK) {
NSLog(@"打开成功");
// [self createTable];
// [self insertData];
// [self SelectData];
// [self updateData];
// [self deletaData];
// [self insertImg];
// UIImageView *ImgView=[[UIImageView alloc]initWithImage:[self showImage]];
// ImgView.frame=CGRectMake(100, 100, 100, 100);
// [self.view addSubview:ImgView];
[self transition];
}
else
{
NSLog(@"打开失败");
}
sqlite3_close(_db);
}
//新建数据库表
-(void)createTable
{
//sqlite支持以下几种数据类型
//integer:整形值 real:浮点值 text:字符串 blob:二进制 NULL:空值
const char *sql="create table if not exists Person(id integer primary key autoincrement ,age integer not null,name text not null,photo blob,registerTime DATETIME DEFAULT (datetime(CURRENT_TIMESTAMP,'localtime')),sex char(1) default '0',money number(10,2),classId integer,constraint fk_Person_class foreign key (classId) references P_Class (CId));create table if not exists P_Class (CId integer primary key ,CName varchar(20));";
char *errmsg=NULL;
//执行sql语句
sqlite3_exec(_db, sql, NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@"%s",errmsg);
NSLog(@"新建表失败");
}
else
{
NSLog(@"新建表成功");
}
}
// 新增数据
-(void)insertData
{
//添加班级
// NSMutableString *stringSql=[NSMutableString stringWithFormat:@"insert into P_Class values(%d,'%@');insert into Person(age,name,money,classId) values(%d,'%@',%lf,%d)",1001,@"软件一班",23,@"CuiYw",200.99,1001];
NSString *stringSql=[NSMutableString stringWithFormat:@"insert into P_Class values(%d,'%@');",1001,@"软件一班"];
NSString *personSql=[NSString stringWithFormat:@"insert into Person(age,name,money,classId) values(%d,'%@',%lf,%d);",23,@"CuiYw",200.99,1001];
NSLog(@"%@",personSql);
stringSql = [personSql stringByAppendingString:personSql];
NSLog(@"%@",personSql);
char *errmsg=NULL;
//执行sql语句
sqlite3_exec(_db, [stringSql UTF8String], NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@"%s",errmsg);
NSLog(@"新增数据失败");
}
else
{
NSLog(@"新增数据成功");
}
}
//查询数据
-(void)SelectData
{
// stringWithFormat:中%是转义字符，两个%才表示一个%
NSString *selectSql=[NSString stringWithFormat:@"select id, age,name,money,photo from Person where name like '%%%@%%'",@"cuiyw"];
sqlite3_stmt *stmt;
//进行查询前的准备工作
//第三个参数为sql的长度（如果设置为-1，则代表系统会自动计算sql语句的长度），第四个参数用来取数据，第五个参数为尾部一般用不上可直接写NULL
if (sqlite3_prepare_v2(_db, [selectSql UTF8String], -1, &stmt, NULL) == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
int pId=sqlite3_column_int(stmt, 0);
int age=sqlite3_column_int(stmt, 1);
const unsigned char *name=sqlite3_column_text(stmt, 2);
double money=sqlite3_column_double(stmt, 3);
NSLog(@"%d %d %s %f ",pId,age,name,money);
}
}
else
{
NSLog(@"error");
}
}
//修改数据
-(void)updateData
{
[self SelectData];
//在执行sql语句时出现一个怪现象 模糊查询时能够不区分大小写 而在用=时就区分大小写这块很是郁闷, 在其他数据库中数据库默认是不区分的
NSString *updSql=[NSString stringWithFormat:@"update Person set money=%f where name='%@'", 200.99, @"CuiYw"];
NSLog(@"%@",updSql);
char *errmsg=NULL;
sqlite3_exec(self.db, updSql.UTF8String, NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@"%s",errmsg);
NSLog(@"更新数据库失败");
}
else
{
NSLog(@"%s", errmsg);
NSLog(@"更新数据库成功");
}
[self SelectData];
}
//删除数据
-(void)deletaData
{
NSString *delSql=[NSString stringWithFormat:@"delete from person where name='%@'",@"cuiyw"];
NSLog(@"%@",delSql);
char *errmsg=NULL;
sqlite3_exec(self.db, delSql.UTF8String, NULL, NULL, &errmsg);
if (errmsg) {
NSLog(@"%s",errmsg);
NSLog(@"删除数据库失败");
}
else
{
NSLog(@"删除数据库成功");
}
}
//插入图片
-(void)insertImg
{
char *sql="update Person set photo=? where id=1";
UIImage *img=[UIImage imageNamed:@"Img.png"];
NSData *data=UIImagePNGRepresentation(img);
sqlite3_stmt *stmt;
if (sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL) == SQLITE_OK) {
sqlite3_bind_blob(stmt, 1, [data bytes], [data length], NULL);
if (sqlite3_step(stmt)==SQLITE_DONE) {
NSLog(@"插入图片成功");
}
}
else
{
NSLog(@"error");
}
sqlite3_finalize(stmt);
}
//显示图片
-(UIImage *)showImage
{
UIImage *Img=NULL;
char *sql="select photo from Person where Id=1";
sqlite3_stmt *stmt=NULL;
if (sqlite3_prepare_v2(_db, sql, -1, &stmt, NULL) == SQLITE_OK) {
if (sqlite3_step(stmt)==SQLITE_ROW ) {
int bytes=sqlite3_column_bytes(stmt, 0);
Byte * value = (Byte*)sqlite3_column_blob(stmt, 0);
if (bytes !=0 && value != NULL)
{
NSLog(@"aa");
NSData * data = [NSData dataWithBytes:value length:bytes];
Img = [UIImage imageWithData:data];
}
}
}
return Img;
}
//事务
//模拟转账操作 将两个账户的money一加一减
-(void)transition
{
[self SelectData];
float updateMoney=20.0;
NSString *strsql1=[NSString stringWithFormat:@"update Person Set money=money+%f where id=%d;",updateMoney,1];
NSString *strsql2=[NSString stringWithFormat:@"update Person Set money=money-%f where id=%d;",updateMoney,2];
NSArray *arrayStr=[NSArray arrayWithObjects:strsql1,strsql2 ,nil];
@try {
char *errmsg=NULL;
if (sqlite3_exec(self.db, "begin", NULL, NULL, &errmsg)==SQLITE_OK) {
NSLog(@"开启事务");
sqlite3_free(errmsg);
sqlite3_stmt *stmt=NULL;
//执行事务
for (NSString *str in arrayStr) {
NSLog(@"%@",str);
if (sqlite3_prepare_v2(self.db, [str UTF8String], -1, &stmt, NULL)==SQLITE_OK) {
NSLog(@"bb");
if (sqlite3_step(stmt)!=SQLITE_DONE) {
sqlite3_finalize(stmt);
}
}
}
//提交事务
if (sqlite3_exec(self.db, "commit", NULL, NULL, &errmsg)==SQLITE_OK) {
NSLog(@"提交事务");
sqlite3_free(errmsg);
}
}
}
@catch (NSException *exception) {
char *errmsg=NULL;
if (sqlite3_exec(self.db, "rollback", NULL, NULL, &errmsg)==SQLITE_OK) {NSLog(@"回滚事务");}
}
@finally {
[self SelectData];
}
}
- (void)didReceiveMemoryWarning
{
[super didReceiveMemoryWarning];
// Dispose of any resources that can be recreated.
}
@end
