一个demo测试Sqlite的增删改查
- (void) createDatabase {
NSString *dbName = @"/Users/chance/Desktop/userinfo.db";
// ios数据库要包含2个
// #import <sqlite3.h>
// libsqlite3.dylib
// 1. 打开数据库
sqlite3 *dbPointer = NULL;
int ret = sqlite3_open(
[dbName UTF8String], &dbPointer);
// 打开数据dbName 如果这个数据不存在就创建，如果存在就直接打开
if (ret != SQLITE_OK) {
NSLog(@"打开数据库失败");
}
// 2. 创建一张表
// 创建和 创建表 插入记录 删除记录 修改记录操作一模一样
// 只有查询记录不一样
NSString *sql = @"create table if not exists userinfo (ID int, Name varchar(64), Age int, City varchar(64)); ";
char *errMsg = NULL;
ret = sqlite3_exec(dbPointer, [sql UTF8String], NULL, NULL, &errMsg);
// 如果执行sql语句错误了 把错误的字符串errMsg字符串中
if (ret != SQLITE_OK) {
NSLog(@"sql语句有错误 errMsg is %s", errMsg);
sqlite3_free(errMsg);
}
// 3. 加入一条记录
NSString *insertSQL = @"insert into userinfo (ID, Name, Age, City) values ('10024', '张三', '88', '北京');";
ret = sqlite3_exec(dbPointer, [insertSQL UTF8String], NULL, NULL, NULL);
if (ret != SQLITE_OK) {
NSLog(@"插入记录错误");
}
// 4.再加几条
insertSQL = @"insert into userinfo (ID, Name, Age, City) values ('10025', '李四', '19', '上海');";
ret = sqlite3_exec(dbPointer, [insertSQL UTF8String], NULL, NULL, NULL);
if (ret != SQLITE_OK) {
NSLog(@"插入记录错误");
}
insertSQL = @"insert into userinfo (ID, Name, Age, City) values ('10027', '李胡', '21', '武汉');";
ret = sqlite3_exec(dbPointer, [insertSQL UTF8String], NULL, NULL, NULL);
if (ret != SQLITE_OK) {
NSLog(@"插入记录错误");
}
insertSQL = @"insert into userinfo (ID, Name, Age, City) values ('10099', '赵美双', '28', '郑州');";
ret = sqlite3_exec(dbPointer, [insertSQL UTF8String], NULL, NULL, NULL);
if (ret != SQLITE_OK) {
NSLog(@"插入记录错误");
}
// 4. 修改一条记录
sqlite3_stmt *statement;//这相当一个容器，放转化OK的sql语句
//组织SQL语句
char *updateSQL = "update userinfo set Name = '李春桂' WHERE ID = '10099' ";
//将SQL语句放入sqlite3_stmt中
int success = sqlite3_prepare_v2(dbPointer, updateSQL, -1, &statement, NULL);
if (success != SQLITE_OK) {
NSLog(@"Error: failed to update:testTable");
}
//执行SQL语句。这里是更新数据库
success = sqlite3_step(statement);
//释放statement
sqlite3_finalize(statement);
// //如果执行失败
// if (success == SQLITE_ERROR) {
// NSLog(@"Error: failed to update the database with message.");
// }
//
// 5. 查询所有的记录
NSString *selectSQL = @"select ID, Name, Age, City from userinfo;";
sqlite3_stmt *result = NULL;
sqlite3_prepare_v2(dbPointer, [selectSQL UTF8String], -1, &result, NULL);
// 去数据库dbPointer中做selectSQL查询语句 然后返回值放在result中
// result游标在-1
int row = 0;
while (1) {
ret = sqlite3_step(result);
// 在result游标往下走一步，
if (ret == SQLITE_ROW) {
// 这一行确实有数据
// 读取当前行的0, 1, 2, 3
int ID = sqlite3_column_int(result, 0);
const unsigned char *cname = sqlite3_column_text(result, 1);
NSString *name = [NSString stringWithUTF8String:
(const char *)cname];
int age = sqlite3_column_int(result, 2);
const unsigned char *ccity = sqlite3_column_text(result, 3);
NSString *city = [NSString stringWithUTF8String:(const char *)ccity];
NSLog(@"第%d行 ID %d name %@ age %d city %@",
row, ID, name, age, city);
row++;
} else {
break;
}
}
// 释放结果 释放内存
sqlite3_finalize(result);
// 6. 要获取数据库userinfo表一共有多少行
NSString *sqlCount = @"select count(*) from userinfo;";
sqlite3_stmt *countResult = NULL;
sqlite3_prepare_v2(dbPointer, [sqlCount UTF8String], -1, &countResult, NULL);
sqlite3_step(countResult); //走一步
int count = sqlite3_column_int(countResult, 0);
NSLog(@"数据库userinfo表一共 %d", count);
sqlite3_finalize(countResult);
//删除
NSString *deleteSQL = @"delete from userinfo where id ='10025'"; //删除userid为4的表
if (sqlite3_exec(dbPointer, [deleteSQL UTF8String], NULL, NULL, &errMsg) == SQLITE_OK) {
printf("delete ok.\n");
}
else {
printf("can't delete it\n");
}
// 4. 关闭数据库
sqlite3_close(dbPointer);
}同样的例子用FMDB做
- (void) createDatabaseFMDB {
NSString *dbName = @"/Library/WebServer/Documents/1206/8-16/userinfo.db";
FMDatabase *database = [FMDatabase databaseWithPath:dbName];
if (![database open]) {
NSLog(@"open database error");
}
NSString *sql = @"create table if not exists userinfo (ID integer primary key autoincrement, Name varchar(64), Age int, City varchar(64)); ";
[database executeUpdate:sql];
NSString *insertSQL = @"insert into userinfo (Name, Age, City) values ('张三', '22', '北京');";
[database executeUpdate:insertSQL];
[database executeUpdate:@"insert into userinfo (Name, Age, City) values (?, ?, ?);",
@"fmdb", @"34", @"北京"];
[database executeUpdate:@"UPDATE User SET Name = ? WHERE ID = ? ",@"修改名字", @"1"];
[database executeUpdate:@"DELETE FROM userinfo WHERE ID = ?", @"2"];
// 查询
NSString *firstRecord = [database stringForQuery:@"SELECT Name FROM userinfo WHERE ID = ?", @"1"];
NSLog(@"first record is %@", firstRecord);
FMResultSet *rs = [database executeQuery:@"SELECT * FROM userinfo"];
//rs=[database executeQuery:@"SELECT * FROM userinfo WHERE Age > ?", @"20"];
while ([rs next]){
NSLog(@"%@ %@",[rs stringForColumn:@"Name"], [rs stringForColumn:@"Age"]);
}
/*
intForColumn:
longForColumn:
longLongIntForColumn:
boolForColumn:
doubleForColumn:
stringForColumn:
dateForColumn:
dataForColumn:
dataNoCopyForColumn:
UTF8StringForColumnIndex:
objectForColumn:
*/
/* 创建图片
一个人有多张照片
create table if not exists photoinfo (ID integer primary key autoincrement, UserID integer, Photo blob);
*/
sql = @"create table if not exists photoinfo (ID integer primary key autoincrement, UserID integer, Photo blob);";
[database executeUpdate:sql];
UIImage *image = [UIImage imageNamed:@"battle_bg4.png"];
NSData *photoData = UIImagePNGRepresentation(image);
[database executeUpdate:@"insert into photoinfo (UserID, Photo) values (?, ?);", @"3", photoData];;
rs = [database executeQuery:@"SELECT * FROM photoinfo;"];
while ([rs next]){
NSLog(@"%d %d",[rs intForColumn:@"UserID"], [rs intForColumn:@"UserID"]);
NSData *d = [rs dataForColumn:@"Photo"];
[d writeToFile:@"/tmp/test.png" atomically:YES];
}
[database close];
}代码连接:
https://github.com/leechance/SQLite
资料:
SQLite 数据库学习教程
http://wenku.baidu.com/view/11279ae108a1284ac9504313.html?re=view
全面解析SQLite
http://wenku.baidu.com/view/40d17cd149649b6648d74753.html
MySql 完全教程
http://wenku.baidu.com/view/11279ae108a1284ac9504313.html?re=view
6420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
