sqlite3 简单使用
- 安装
- 数据类型
- 创建数据库
- 创建表 CREATE
- 删除表 DROP
- 插入数据 INSERT
- 更新数据 UPDATE
- 删除数据 DELETE
- 查询
- 运算符
- 其他表达式 - 个数 时间
- 获取部分数据 LIMIT
- 排序 ORDER BY
- 分组（计算流水总额） GROUP BY
- WHERE 子句上的二次过滤语句 HAVING
- 去重复数据 DISTINCT
安装
mac 终端直接进入 sqlite3：
sqlite3
数据类型
创建数据库
创建（进入）数据库
$sqlite3 DatabaseName.db检查数据库是否存在
sqlite>.databases退出数据库
sqlite>.quit.db 与 .sql 互转
\(sqlite3 testDB.db .dump > testDB.sql
\)sqlite3 testDB.db < testDB.sql对应 fmdb
self.dataBase = [FMDatabase databaseWithPath:dbPath];
BOOL isOpen = [self.dataBase open];
if (isOpen) {
NSLog(@"db open ok ");
}
[self.dataBase close];
创建表 CREATE
CREATE TABLE tebleName (
id INTEGER PRIMARY KEY(one or more columns),
name TEXT,
age INTEGER,
mobile datatype,
);
- 对应 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;
- 对应 fmdb
NSString *deleteTableSql = [NSString stringWithFormat:@"DROP TABLE %@",tableName];
BOOL isDeleteTable = [self.dataBase executeUpdate:deleteTableSql];
if (isDeleteTable) {
NSLog(@"删表 ok");
}
插入数据 INSERT
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);
或者
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
- 对应 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 个数要与创建时一致，下面的 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");
}
更新数据 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(@"更新 成功");
}
删除数据 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;
// 下面几个是输出格式设置
sqlite>.header on
sqlite>.mode column
sqlite>.width 10, 20, 10
sqlite> SELECT * FROM COMPANY;
- 对应 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, @"张%"];
// 模糊搜索 张* （区分大小写）,（*：多个，？：一个）
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];
其他表达式 - 个数 时间
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;
}];
获取部分数据 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
获取的数据进行排序，对表本身没有变化
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
数据表内是不同人的，花费流水，使用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];
去重复数据 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];