(一)常用一些SQL语句
1.创建表
CREATE TABLE IF NOT EXISTS tableName (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
date TEXT,
city TEXT,
weather TEXT,
max REAL,
min REAL,
image BLOB
other NULL)
参数说明:
(1)数据类型
1.NULL:空值。
2.INTEGER:带符号的整型,具体取决有存入数字的范围大小。
3.REAL:浮点数字,存储为8-byte IEEE浮点数。
4.TEXT:字符串文本。
5.BLOB:二进制对象
(2)主索引
PRIMARY KEY
(3)自增
AUTOINCREMENT
(4)不能为空
NOT NULL
2.查询语句
SELECT [*/fieldName] FROM tableName
WHERE 条件
ORDER BY date desc
LIMIT 1
参数说明:
(1)[*/fieldName] *表示所有字段,也可不写,多个字段之间用,隔开
注意:
从数据库取数据时,下标从0开始
char *strChar = (char *)sqlite3_column_text(_stmt, 0);
3.插入语句
INSERT OR REPLACE INTO weather (date, city, weather, max, min, image) VALUES(?,?,?,?,?,?)
注意:当绑定数据时,从下标1开始
sqlite3_bind_text(_stmt, 1, [valueUTF8String], -1,NULL);
4.更新语句
UPDATE tableName SET date = '%@', city = '%@', weather = '%@', max = '%@', min = '%@', image = '%@' WHERE id = 1
5.删除语句
DELETE FROM weather WHERE 条件
参考教程网址:http://www.1keydata.com/cn/sql/
(二)关于sqlite常用一些操作总结
1.打开数据库
sqlite3_open(
constchar *filename, /* Database filename (UTF-8) */ 数据库路径
sqlite3 **ppDb /* OUT: SQLite db handle */ 句柄
);
例子
NSString *dbPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES)objectAtIndex:0]stringByAppendingPathComponent:DBName];
result = sqlite3_open([dbPath UTF8String], &_database);
if (result != SQLITE_OK) {
NSLog(@"打开表失败 %d",result);
}
2.创建表
SQLITE_APIint sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);
例子
char *error;
result = sqlite3_exec(_database, [SQL UTF8String], NULL, NULL, &error);
if (result != SQLITE_OK) {
NSLog(@"创建表失败:%s",error);
return;
}
3.对数据库进行常用操作
(1)查询
//SQL语句
char * sql ="select * from t_expenseHistory;";
//SQL语句编译
sqlite3_stmt * stmt;
if (!sqlite3_prepare_v2(db, sql, -1, &stmt,NULL) == SQLITE_OK) {
NSLog(@"从数据库调出数据,prepare发生了错误");
}
//从数据库取记录(注意从0开始)
while(sqlite3_step(stmt) ==SQLITE_ROW){
int _id =sqlite3_column_int(stmt, 0);
char * _date = (char *)sqlite3_column_text(stmt,1);
char * _mileage = (char *)sqlite3_column_text(stmt,2);
char * _price = (char *)sqlite3_column_text(stmt,3);
char * _iolmass = (char *)sqlite3_column_text(stmt,4);
char * _totalCharge = (char *)sqlite3_column_text(stmt,5);
NSString * date = [NSStringstringWithUTF8String:_date];
NSString * mileage = [NSStringstringWithUTF8String:_mileage];
NSString * price = [NSStringstringWithUTF8String:_price];
NSString * iolmass = [NSStringstringWithUTF8String:_iolmass];
NSString * totalCharge = [NSStringstringWithUTF8String:_totalCharge];
}
//关闭数据库
sqlite3_finalize(stmt);
sqlite3_close(db);
(2)插入
//SQL语句编译
result = sqlite3_prepare_v2(_database, [SQLUTF8String], -1, &_stmt,NULL);
if (result !=SQLITE_OK) {
NSLog(@"编译失败 %d",result);
sqlite3_close(_database);
return NO;
}
//绑定数据(从1开始)
sqlite3_bind_text(_stmt, 1, [value1 UTF8String], -1, NULL);
sqlite3_bind_text(_stmt, 2, [value2 UTF8String], -1, NULL);
//执行语句
result = sqlite3_step(_stmt);
if (result ==SQLITE_ERROR || result ==SQLITE_MISUSE) {
NSLog(@"执行语句错误");
sqlite3_close(_database);
return NO;
}