http://www.runoob.com/sqlite/sqlite-tutorial.html
sqlite基礎(chǔ)教程蠢古，菜鳥網(wǎng)站柒傻。
iOS開發(fā)用到fmdb比較多，其實也就是執(zhí)行SQL語句；這里簡單使用了基礎(chǔ)教程的SQL語句胀茵。
sqlite3
mac 終端直接進(jìn)入 sqlite3：
sqlite3
數(shù)據(jù)類型
創(chuàng)建數(shù)據(jù)庫
創(chuàng)建（進(jìn)入）數(shù)據(jù)庫
$sqlite3 DatabaseName.db檢查數(shù)據(jù)庫是否存在
sqlite>.databases退出數(shù)據(jù)庫
sqlite>.quit.db 與 .sql 互轉(zhuǎn)
$sqlite3 testDB.db .dump > testDB.sql
$sqlite3 testDB.db < testDB.sql對應(yīng) fmdb
self.dataBase = [FMDatabase databaseWithPath:dbPath];
BOOL isOpen = [self.dataBase open];
if (isOpen) {
NSLog(@"db open ok ");
}
[self.dataBase close];
創(chuàng)建表 CREATE
CREATE TABLE tebleName (
id INTEGER PRIMARY KEY(one or more columns),
name TEXT,
age INTEGER,
mobile datatype,
);
- 對應(yīng) fmdb
NSString *tableName = @"tableName";
NSString *nameKey = @"name";
NSString *ageKey = @"age";
NSString *createTableSql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (id INTEGER PRIMARY KEY AUTOINCREMENT, %@ TEXT, %@ INTEGER)",tableName, nameKey, ageKey];
BOOL isCreateTable = [self.dataBase executeUpdate:createTableSql];
if (isCreateTable) {
NSLog(@"建表 ok");
}
刪除表 DROP
DROP TABLE tableName;
- 對應(yīng) fmdb
NSString *deleteTableSql = [NSString stringWithFormat:@"DROP TABLE %@",tableName];
BOOL isDeleteTable = [self.dataBase executeUpdate:deleteTableSql];
if (isDeleteTable) {
NSLog(@"刪表 ok");
}
插入數(shù)據(jù) INSERT
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
或者
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
- 對應(yīng) fmdb
// 插法1
NSString *insertNameValue = @"張三";
NSInteger insertAgeValue = 19;
NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@ (%@, %@) VALUES ('%@', %zi)", tableName, nameKey, ageKey, insertNameValue, insertAgeValue];
BOOL isInsert = [self.dataBase executeUpdate:insertSql];
if (isInsert) {
NSLog(@"插入 ok");
}
// 插法2，注意 Value 個數(shù)要與創(chuàng)建時一致挟阻，下面的 9999 就是上面的 ID
NSString *insertSql2 = [NSString stringWithFormat:@"INSERT INTO %@ VALUES (9999, '%@', %zi)" , tableName, insertNameValue, insertAgeValue];
BOOL isInsert2 = [self.dataBase executeUpdate:insertSql2];
if (isInsert2) {
NSLog(@"插入2 ok");
}
更新數(shù)據(jù) UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
NSString *updateSql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = %zi WHERE %@ = '%@'",tableName, ageKey, 12, nameKey, @"張三"];
BOOL isUpdate = [self.dataBase executeUpdate:updateSql];
if (isUpdate) {
NSLog(@"更新 成功");
}
刪除數(shù)據(jù) DELETE
DELETE FROM table_name
WHERE [condition];
NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ != %zi", tableName, ageKey, 12];
BOOL isDelete = [self.dataBase executeUpdate:deleteSql];
if (isDelete) {
NSLog(@"刪除 ok");
}
查詢
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;
// 下面幾個是輸出格式設(shè)置
sqlite>.header on
sqlite>.mode column
sqlite>.width 10, 20, 10
sqlite> SELECT * FROM COMPANY;
- 對應(yīng) fmdb
NSString *searchSql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ = '%@' AND %@ = %zi ",tableName, nameKey, @"張三", ageKey, 25];
FMResultSet *result = [self.dataBase executeQuery:searchSql];
while ([result next]) {
NSString *name = [result stringForColumn:@"name"];
NSInteger age = [result intForColumn:@"age"];
NSLog(@"%@ - %zi",name, age);
}
[result close];
運算符
比較運算符：=,!=,>,>=........
邏輯運算符：AND, BETWEEN, EXISTS, IN, LIKE, GLOB, NOT......
舉幾個栗子
// 模糊搜索 張%(%：多個琼娘，_：一個)
NSString *searchSql1 = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ LIKE '%@'", tableName, nameKey, @"張%"];
// 模糊搜索 張* （區(qū)分大小寫）,（*：多個峭弟，？：一個）
NSString *searchSql2 = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ GLOB '%@'", tableName, nameKey, @"張*"];
// 搜索 年齡是 19 和 29 的
NSString *searchSql3 = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ IN %@", tableName, ageKey, @"(19,29)"];
// 搜索 年齡 20-30 之間的
NSString *searchSql4 = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE %@ BETWEEN %zi AND %zi",tableName, ageKey, 20,30];
其他表達(dá)式 - 個數(shù) 時間
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
SELECT CURRENT_TIMESTAMP;
NSString *searchSql11 = [NSString stringWithFormat:@"SELECT COUNT(*) AS COUNT FROM %@",tableName];
NSString *searchSql12 = [NSString stringWithFormat:@"SELECT CURRENT_TIMESTAMP"];
[self.dataBase executeStatements:searchSql12 withResultBlock:^int(NSDictionary *resultsDictionary) {
NSLog(@"%@",resultsDictionary);
return 0;
}];
獲取部分?jǐn)?shù)據(jù) LIMIT
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
// 2開始脱拼，取3個
NSString *limitSql = [NSString stringWithFormat:@"SELECT * FROM %@ LIMIT %zi OFFSET %zi",tableName, 3,2];
排序 ORDER BY
獲取的數(shù)據(jù)進(jìn)行排序瞒瘸，對表本身沒有變化
SELECT * FROM COMPANY ORDER BY SALARY ASC;// DESC
NSString *orderSql = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY %@ ASC",tableName, nameKey];
FMResultSet *re = [self.dataBase executeQuery:orderSql];
while ([re next]) {
NSString *name = [re stringForColumn:@"name"];
NSInteger age = [re intForColumn:@"age"];
NSInteger iid = [re intForColumn:@"id"];
NSLog(@"%zi :::%@ - %zi",iid,name, age);
}
[re close];
分組（計算流水總額） GROUP BY
數(shù)據(jù)表內(nèi)是不同人的，花費流水挪拟，使用group挨务，對name合并，計算SUM（cost）總額
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
NSString *orderSql = [NSString stringWithFormat:@"SELECT name, SUM(cost) FROM %@ GROUP BY %@",tableName, nameKey];
FMResultSet *re = [self.dataBase executeQuery:orderSql];
while ([re next]) {
NSString *name = [re stringForColumn:@"name"];
NSInteger cost = [re intForColumn:@"SUM(cost)"];
NSLog(@"%@ - %zi",name, cost);
}
[re close];
WHERE 子句上的二次過濾語句 HAVING
NSString *orderSql = [NSString stringWithFormat:@"SELECT name, SUM(age) FROM %@ GROUP BY %@ HAVING COUNT(name) > 2 ORDER BY %@ ASC",tableName, nameKey,nameKey];
FMResultSet *re = [self.dataBase executeQuery:orderSql];
while ([re next]) {
NSString *name = [re stringForColumn:@"name"];
NSInteger sumage = [re intForColumn:@"SUM(age)"];
NSLog(@":::%@ - %zi",name, sumage);
}
[re close];
去重復(fù)數(shù)據(jù) DISTINCT
SELECT DISTINCT name FROM COMPANY;
NSString *orderSql = [NSString stringWithFormat:@"SELECT Distinct name FROM %@",tableName];
FMResultSet *re = [self.dataBase executeQuery:orderSql];
while ([re next]) {
NSString *name = [re stringForColumn:@"name"];
NSLog(@":::%@ - ",name);
}
[re close];
