SQL套餐
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數據庫框架
FMDB以OC的方式封裝了SQLite的C語言APIFMDB的優(yōu)點
使用起來更加面向對象抵碟，省去了很多麻煩鉴逞、冗余的C語言代碼
對比蘋果自帶的Core Data框架豫领，更加輕量級和靈活
提供了多線程安全的數據庫操作方法零渐，有效地防止數據混亂FMDB 的github地址
https://github.com/ccgus/fmdb-
FMDB有三個主要的類
FMDatabase一個FMDatabase對象就代表一個單獨的SQLite數據庫
用來執(zhí)行SQL語句
FMResultSet
使用FMDatabase執(zhí)行查詢后的結果集FMDatabaseQueue
用于在多線程中執(zhí)行多個查詢或更新，它是線程安全的
通過指定SQLite數據庫文件路徑來創(chuàng)建FMDatabase對象
FMDatabase *db = [FMDatabase databaseWithPath:path];
if (![db open]) {
NSLog(@"數據庫打開失敯砥稹芍阎！");
}
文件路徑有三種情況
具體文件路徑
如果不存在會自動創(chuàng)建
空字符串@""
會在臨時目錄創(chuàng)建一個空的數據庫
當FMDatabase連接關閉時，數據庫文件也被刪除
在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
示例
// 查詢數據
FMResultSet *rs = [db executeQuery:@"SELECT * FROM t_student"];
// 遍歷結果集
while ([rs next]) {
NSString *name = [rs stringForColumn:@"name"];
int age = [rs intForColumn:@"age"];
double score = [rs doubleForColumn:@"score"];
}
FMDatabaseQueue
FMDatabase這個類是線程不安全的，如果在多個線程同時使用一個FMDatabase實例,會造成數據混亂問題
為了保證線程安全,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]) {
// …
}
}];
使用事務
[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]) {
// …
}
}];
事務回滾
*rollback = YES;
總結：本博客主要學習了sql增刪改查語句藕施，學習了FMDB的框架的三個類:
FMDatabase對象就代表一個單獨的SQLite數據庫,FMDatabase執(zhí)行查詢后的結果集,FMDatabaseQueued串行隊列寇损，同步任務;
下次學習,感覺coreDate好強大，估計你用熟悉了裳食，你就不想用sql語句
