sql常用語句
創(chuàng)建表
CREATE TABLE IF NOT EXISTS "T_Person" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT,
"age" INTEGER,
"heigth" REAL
)
//下邊是sqllite編譯器里邊的語句
/*簡單約束*/
CREATE TABLE IF NOT EXISTS t_student
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER
);
CREATE TABLE IF NOT EXISTS t_student
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE,
age INTEGER
);
/*添加主鍵*/
CREATE TABLE IF NOT EXISTS t_student
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER,
score REAL
);
/*添加主鍵*/
CREATE TABLE IF NOT EXISTS t_student
(
id INTEGER,
name TEXT,
age INTEGER,
score REAL,
PRIMARY KEY(id)
);
查詢
/*分頁*/
SELECT * FROM t_student
ORDER BY id ASC LIMIT 30, 10;
/*排序*/
SELECT * FROM t_student
WHERE score > 50
ORDER BY age DESC;
SELECT * FROM t_student
WHERE score < 50
ORDER BY age ASC , score DESC;
/*計量*/
SELECT COUNT(*)
FROM t_student
WHERE age > 50;
/*別名*/
SELECT name as myName, age as myAge, score as myScore
FROM t_student;
SELECT name myName, age myAge, score myScore
FROM t_student;
SELECT s.name myName, s.age myAge, s.score myScore
FROM t_student s
WHERE s.age > 50;
/*查詢*/
SELECT name, age, score FROM t_student;
SELECT * FROM t_student;
修改
UPDATE t_student
SET name = 'MM'
WHERE age = 10;
UPDATE t_student
SET name = 'WW'
WHERE age is 7;
UPDATE t_student
SET name = 'XXOO'
WHERE age < 20;
UPDATE t_student
SET name = 'NNMM'
WHERE age < 50 and score > 10;
/*更新記錄的name*/
UPDATE t_student SET name = 'zhangsan';
刪除
DELETE FROM t_student;
DELETE FROM t_student WHERE age < 50;
插入
INSERT INTO t_student
(age, score, name)
VALUES
('28', 100, 'zhangsan');
INSERT INTO t_student
(name, age)
VALUES
('lisi', '28');
INSERT INTO t_student
(score)
VALUES
(100);
刪除表
/*刪除表*/
DROP TABLE IF EXISTS t_student;
FMDB
什么是FMDB
FMDB是iOS平臺的SQLite數(shù)據(jù)庫框架
FMDB以O(shè)C的方式封裝了SQLite的C語言API
FMDB的優(yōu)點
使用起來更加面向?qū)ο螅∪チ撕芏嗦闊?、冗余的C語言代碼
對比蘋果自帶的Core Data框架，更加輕量級和靈活
提供了多線程安全的數(shù)據(jù)庫操作方法，有效地防止數(shù)據(jù)混亂
FMDB 的github地址
FMDB有三個主要的類
FMDatabase
一個FMDatabase對象就代表一個單獨的SQLite數(shù)據(jù)庫
用來執(zhí)行SQL語句
FMResultSet
使用FMDatabase執(zhí)行查詢后的結(jié)果集
FMDatabaseQueue
用于在多線程中執(zhí)行多個查詢或更新，它是線程安全的
通過指定SQLite數(shù)據(jù)庫文件路徑來創(chuàng)建FMDatabase對象
FMDatabase *db = [FMDatabase databaseWithPath:path];
if (![db open]) {
NSLog(@"數(shù)據(jù)庫打開失??！");
}
文件路徑有三種情況
具體文件路徑
如果不存在會自動創(chuàng)建
空字符串@""
會在臨時目錄創(chuàng)建一個空的數(shù)據(jù)庫
當(dāng)FMDatabase連接關(guān)閉時，數(shù)據(jù)庫文件也被刪除
在FMDB中，除查詢以外的所有操作，都稱為“更新”
create、drop、insert、update、delete等
使用executeUpdate:方法執(zhí)行更新
- (BOOL)executeUpdate:(NSString*)sql, ...
- (BOOL)executeUpdateWithFormat:(NSString*)format, ...
- (BOOL)executeUpdate:(NSString*)sql withArgumentsInArray:(NSArray *)arguments
示例
[db executeUpdate:@"UPDATE t_student SET age = ? WHERE name = ?;", @20, @"Jack"]
查詢的方法
查詢方法
- (FMResultSet *)executeQuery:(NSString*)sql, ...
- (FMResultSet *)executeQueryWithFormat:(NSString*)format, ...
- (FMResultSet *)executeQuery:(NSString *)sql withArgumentsInArray:(NSArray *)arguments
示例
// 查詢數(shù)據(jù)
FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];
// 遍歷結(jié)果集
while ([rs next]) {
NSString *name = [rs stringForColumn:@"name"];
int age = [rs intForColumn:@"age"];
double score = [rs doubleForColumn:@"score"];
}
FMDatabaseQueue
FMDatabase這個類是線程不安全的，如果在多個線程同時使用一個FMDatabase實例,會造成數(shù)據(jù)混亂問題
為了保證線程安全,FMDB提供方便快捷的FMDatabaseQueue類
FMDatabaseQueue 的創(chuàng)建
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:path];
簡單使用
[queue inDatabase:^(FMDatabase *db) {
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];
FMResultSet *rs = [db executeQuery:@"select * from t_student"];
while ([rs next]) {
// …
}
}];
使用事務(wù)
[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jack"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Rose"];
[db executeUpdate:@"INSERT INTO t_student(name) VALUES (?)", @"Jim"];
FMResultSet *rs = [db executeQuery:@"select * from t_student"];
while ([rs next]) {
// …
}
}];
事務(wù)回滾
*rollback = YES;
總結(jié)：本博客主要學(xué)習(xí)了sql增刪改查語句，學(xué)習(xí)了FMDB的框架的三個類:
FMDatabase對象就代表一個單獨的SQLite數(shù)據(jù)庫,FMDatabase執(zhí)行查詢后的結(jié)果集,FMDatabaseQueued串行隊列，同步任務(wù);
下次學(xué)習(xí),感覺coreDate好強大，估計你用熟悉了，你就不想用sql語句
作者：董軍1990
鏈接：http://m.itdecent.cn/p/143c83362909
來源：簡書
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
