1.可视化软件中
//创建表 create table (表名,字段名,字段类型)(integer:有符号整型,text:文本类型,real:浮点型)
//primary key :主键(唯一+自增)
//create tableif not exists需要添加后面的 (如果表不存在,就创建;否则不创建)
//create table people (id integer, name text, age integer, class text);
create table if not exists people (id integer primary key, name text, age integer, class text);
// where 选择条件; and 与; or 或;
//增:insert intoinsert into people (id,name,age,class) values (1,'Jonny',20,'class1512');
insert into people (id,name,age,class) values (2,'Maggie',19,'class1501');
insert into people (id,name,age,class) values (3,'Bob',21,'class1511');
//改:update ... set
update people set class='class1510' where name='Jonny';
//查:select from
select * from people;
select name from people;
select * from people where age>19 and age<22;
//删: delete from
delete from people where name='Maggie';
2.代码中
//用于创建连接
sqlite3_open(<#const char *filename#>, <#sqlite3 **ppDb#>);
//用于创建sql文件、增、删、改
sqlite3_exec(<#sqlite3 *#>, <#const char *sql#>, <#int (*callback)(void *, int, char **, char **)#>, <#void *#>, <#char **errmsg#>);
//用于查询
sqlite3_prepare_v2(<#sqlite3 *db#>, <#const char *zSql#>, <#int nByte#>, <#sqlite3_stmt **ppStmt#>, <#const char **pzTail#>);
- (void)createDBAndDataOperation {
//1.创建数据库文件(保存在Document路径下)
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask,YES)firstObject];
NSString *databasePath = [documentPathstringByAppendingPathComponent:@"text.db"];
/**
* 如果不存在test.db ,创建并打开;否则打开
* 参数1:数据库文件所在的路径
* 参数2:指向已经创建并打开的数据库对象(打开连接)
*/
sqlite3 *db =NULL;
int ret =sqlite3_open([databasePathcStringUsingEncoding:NSUTF8StringEncoding], &db);
//ret != 0
if (ret !=SQLITE_OK) {
NSLog(@"创建数据库文件失败:%s",sqlite3_errmsg(db));
}
//2.创建表(主键primary key:唯一+自增)
constchar *createTable ="create table if not exists people (id integer primary key,name text,age integer,height real)";
char *errmsg =NULL;
ret = sqlite3_exec(db, createTable,NULL,NULL, &errmsg);
if (ret !=SQLITE_OK) {
NSLog(@"创建表失败:%s",errmsg);
}
//增、删、改、查
//id是主键,自增不需要给值
//增(代码每执行一次都会添加一条相同的记录,id自增)
constchar *insertRecord ="insert into people (name,age,height) values ('Jonny',19,1.85)";
ret = sqlite3_exec(db, insertRecord,NULL,NULL, &errmsg);
if (ret !=SQLITE_OK) {
NSLog(@"插入数据失败:%s",errmsg);
}
//改
constchar *updateRecord ="update people set height= 1.70 where id=1";
ret = sqlite3_exec(db, updateRecord,NULL,NULL, &errmsg);
if (ret !=SQLITE_OK) {
NSLog(@"修改数据失败:%s",errmsg);
}
//删
constchar *deleteRecord ="delete from people where height=1.85";
ret = sqlite3_exec(db, deleteRecord,NULL,NULL, &errmsg);
if (ret !=SQLITE_OK) {
NSLog(@"删除数据失败:%s",errmsg);
}
//查
constchar *selectRecord ="select * from people";
sqlite3_stmt *stmt;
//第3个参数: -1 从头执行到尾
ret = sqlite3_prepare_v2(db, selectRecord, -1, &stmt,NULL);
if (ret ==SQLITE_OK) {
//查询成功
//循环取出相应的值
while (sqlite3_step(stmt) ==SQLITE_ROW) {
//第2个参数:所要查询的参数的下标
constunsignedchar *name =sqlite3_column_text(stmt, 1);
constint age =sqlite3_column_int(stmt, 2);
constdouble height =sqlite3_column_double(stmt, 3);
NSLog(@"name:%s,age:%d,height:%.2f",name,age,height);
}
}
//收尾工作:释放stmt内存、断开数据库的连接
sqlite3_finalize(stmt);
sqlite3_close(db);
}
3.使用第三方(FMDB)
- (void)createDataBaseAndDataOperation {
//1.创建数据库
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
NSString *dbFilePath = [documentPath stringByAppendingPathComponent:@"fmdb.db"];
//仅仅创建数据库文件,并没有打开
FMDatabase *database = [FMDatabase databaseWithPath:dbFilePath];
//2.创建表
if ([database open]) {
BOOL isSucess = [database executeUpdate:@"create table if not exists people (id integer primary key, name text, height real)"];
if (!isSucess) {
NSLog(@"创建表失败:%@",database.lastError);
}
}
//open,close 可以只写一次,在database开头与末尾执行,此处模拟单个方法(创建,增、删、改、查)
[database close];
//3.插入数据
if ([database open]) {
BOOL isSucess = [database executeUpdate:@"insert into people (name,height) values ('Bob',1.85)"];
if (!isSucess) {
NSLog(@"插入数据失败:%@",database.lastError);
}
}
[database close];
//4.查询数据
if ([database open]) {
FMResultSet *resultSet = [database executeQuery:@"select * from people"];
while ([resultSet next]) {
//从记录中获取每个字段(根据不同的字段类型选取不同的方法)
//根据字段名查询
NSString *nameStr = [resultSet stringForColumn:@"name"];
//根据下标查询
//[resultSet stringForColumnIndex:1]
double height = [resultSet doubleForColumn:@"height"];
NSLog(@"name:%@,height:%.2f",nameStr,height);
}
}
[database close];