[TOC]

sqlite3

数据类型

基础的 (NULL INTEGER REAL TEXT BLOB)

亲和(Affinity)类型

运算符

算术运算符

+ - * / %

例子:

SELECT * FROM MineTable2 WHERE posGroup = (1 + 1)
SELECT * FROM MineTable2 WHERE posGroup = 2

上面两者等价

和其他语言OC Java 等都是相同的

比较运算符

• 相等: == =
• 不相等: != <>
• 大于: >
• 大于等于: >= !< (不小于)
• 小于: <
• 小于等于: <= !> (不大于)

和其他编程语言相通的有: == =(赋值) != > >= <``<=
OC Java 不支持: !< !> <>

但是在navicat中使用 !< !> <> 也会报错，所以不推荐使用

例子:

SELECT * FROM MineTable2 WHERE posGroup >= 2

逻辑运算符

• AND: 多个条件，“与”，都为真，结果为真
• OR: 多个条件，“或”，有一个为真，结果为真
• NOT: 取反，“非”
• IS: 和 = 相似
• IS NOT: 和 != 相似
• IS NULL: 是否为空NULL
• BETWEEN: 在范围内搜索，最大值和最小值，等价于 >= <=;下面两个语句是等价的
  • SELECT * FROM MineTable2 WHERE posGroup BETWEEN 1 AND 2
  • SELECT * FROM MineTable2 WHERE posGroup >=1 AND posGroup <= 2
• IN: 在范围内查找，类似多个 (OR) 相等性 = 条件查询 ;下面两个语句是等价的
  • SELECT * FROM MineTable2 WHERE posGroup IN (1, 2)
  • SELECT * FROM MineTable2 WHERE posGroup = 1 OR posGroup = 2
• NOT IN: 不在范围内，和 IN 相反
• EXISTS: 存在性判断，可以用于表table，也可以用在列coloum
  • CREATE TABLE IF NOT EXISTS .... 创建表的语句
• LIKE: 和通配符比较，大小写不敏感
  • %: 零个、一个、多个
  • _: 一个
  • 例子:
    • SELECT * FROM MineTable2 WHERE posGroup LIKE '%1' : 以1结尾的，可以是1位、2位、更多位
    • SELECT * FROM MineTable2 WHERE posGroup LIKE '_1' : 以1结尾的，2位
• GLOB: 和通配符比较，大小写敏感
  • *: 零个、一个、多个
  • ?: 一个
• ||: 连接两个字符串，得到一个新的字符串；下面两个语句查询结果相同
  • SELECT * FROM MineTable2 WHERE posGroup = ('1' || '1');
  • SELECT * FROM MineTable2 WHERE posGroup = '11';

通配符查找

• LIKE 的 % 类似 GLOB 的 *
• LIKE 的 _ 类似 GLOB 的 ?
• LIKE 不区分大小写，GLOB区分大小写
  GLOB 类似正则表达式的语法

语句1: SELECT * FROM MineTable2 WHERE pageID LIKE '%H%';
语句2: SELECT * FROM MineTable2 WHERE pageID LIKE '%h%';

语句3: SELECT * FROM MineTable2 WHERE pageID GLOB '*h*';
语句4: SELECT * FROM MineTable2 WHERE pageID GLOB '*H*';

语句1 2 3 结果相同: h H 都可以查找到
语句4只查找H内容

例子:

WHERE SALARY LIKE '200%'      查找以 200 开头的任意值
WHERE SALARY LIKE '%200%'     查找任意位置包含 200 的任意值
WHERE SALARY LIKE '_00%'      查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE '2_%_%'     查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE '%2'       查找以 2 结尾的任意值
WHERE SALARY LIKE '_2%3'      查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE '2___3'     查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

// -------------------------

WHERE SALARY GLOB '200*'    查找以 200 开头的任意值
WHERE SALARY GLOB '*200*'   查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*'    查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB '2??' 查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB '*2'  查找以 2 结尾的任意值
WHERE SALARY GLOB '?2*3'    查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB '2???3'   查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

位运算符 （真假）

• & : 与
• | : 或
• ~ : 取反
• <<: 左移位
• >>: 右移位

约束

约束是对表或列(字段)添加的条件限定

• PRIMARY Key: 唯一标识数据库表中的各行/记录。
• NOT NULL: 确保某列不能有 NULL 值
• UNIQUE: 确保某列中的所有值是不同的。
  • 如果有插入相同的值会报错 Unknown error finalizing or resetting statement (19: UNIQUE constraint failed: Introduce.status)
• DEFAULT: 当某列没有指定值时，为该列提供默认值。
  • 例子: DEFAULT 0
• CHECK: 确保某列中的所有值满足一定条件，才可以插入数据
  • 校验CHECK(status > 0)，如果插入的值检验结果不满足条件会报错 Unknown error calling sqlite3_step (19: CHECK constraint failed: Introduce)

例子:

CREATE TABLE IF NOT EXISTS Introduce (
    IntroduceID INTEGER PRIMARY KEY AUTOINCREMENT, 
    status INTEGER UNIQUE NOT NULL DEFAULT 0 CHECK(status > 0), 
    pageID TEXT UNIQUE NOT NULL DEFAULT 0, 
    age REAL UNIQUE NOT NULL DEFAULT 0, 
    posGroup BLOB UNIQUE NOT NULL DEFAULT 0, 
    configId INTEGER
);

条件 WHERE

附加操作的条件；

WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE 语句中，等等

SELECT {字段} FROM {表名称} WHERE {条件} ;

例子:

SELECT * FROM MineTable2 WHERE pageID = 'p_heal_20';

排序 ORDER BY

• ASC：升序
• DESC：降序

SELECT {字段} FROM {表名称} WHERE {条件} ORDER BY {字段1 ASC/DESC }, {字段2 ASC/DESC };

多个字段用逗号,分割，可以指定每个字段升序/降序排序;

例子:

SELECT * FROM MineTable2 WHERE pageID = 'p_heal_20' ORDER BY posGroup ASC , posID DESC;

创建数据库 path

FMDB code

- (instancetype)initWithPath:(NSString *)path {
    
    assert(sqlite3_threadsafe()); // whoa there big boy- gotta make sure sqlite it happy with what we're going to do.
    
    self = [super init];
    
    if (self) {
        _databasePath               = [path copy];
        _openResultSets             = [[NSMutableSet alloc] init];
        _db                         = nil;
        _logsErrors                 = YES;
        _crashOnErrors              = NO;
        _maxBusyRetryTimeInterval   = 2;
    }
    
    return self;
}

打开数据库 open

sqlite code

SQLITE_API int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
SQLITE_API int sqlite3_open16(
  const void *filename,   /* Database filename (UTF-16) */
  sqlite3 **ppDb          /* OUT: SQLite db handle */
);
SQLITE_API int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);

FMDB code

- (BOOL)open {
    if (_db) {
        return YES;
    }
    
    int err = sqlite3_open([self sqlitePath], (sqlite3**)&_db );
    if(err != SQLITE_OK) {
        NSLog(@"error opening!: %d", err);
        return NO;
    }
    
    if (_maxBusyRetryTimeInterval > 0.0) {
        // set the handler
        [self setMaxBusyRetryTimeInterval:_maxBusyRetryTimeInterval];
    }
    
    
    return YES;
}

------
- (const char*)sqlitePath {
    
    if (!_databasePath) {
        return ":memory:";
    }
    
    if ([_databasePath length] == 0) {
        return ""; // this creates a temporary database (it's an sqlite thing).
    }
    
    return [_databasePath fileSystemRepresentation];
    
}

关闭数据库 close

sqlite code

SQLITE_API int sqlite3_close(sqlite3*);

FMDB code

- (BOOL)close {
    
    [self clearCachedStatements];
    [self closeOpenResultSets];
    
    if (!_db) {
        return YES;
    }
    
    int  rc;
    BOOL retry;
    BOOL triedFinalizingOpenStatements = NO;
    
    do {
        retry   = NO;
        rc      = sqlite3_close(_db);
        if (SQLITE_BUSY == rc || SQLITE_LOCKED == rc) {
            if (!triedFinalizingOpenStatements) {
                triedFinalizingOpenStatements = YES;
                sqlite3_stmt *pStmt;
                while ((pStmt = sqlite3_next_stmt(_db, nil)) !=0) {
                    NSLog(@"Closing leaked statement");
                    sqlite3_finalize(pStmt);
                    retry = YES;
                }
            }
        }
        else if (SQLITE_OK != rc) {
            NSLog(@"error closing!: %d", rc);
        }
    }
    while (retry);
    
    _db = nil;
    return YES;
}

创建表 CREATE

语句格式:

单主键

CREATE TABLE IF NOT EXISTS {表名称} (
    {主键} {数据类型:INTEGER} PRIMARY KEY AUTOINCREMENT,
    {字段} {数据类型:INTEGER、REAL、TEXT} {约束: UNIQUE、NOT NULL、DEFAULT、CHECK}, 
    ...... 重复字段 ......
);

例子:

CREATE TABLE IF NOT EXISTS Introduce (id INTEGER PRIMARY KEY  AUTOINCREMENT, title TEXT, pageIdName TEXT, status INTEGER);

复合主键

CREATE TABLE IF NOT EXISTS {表名称} (
    {字段} {数据类型:INTEGER、REAL、TEXT} {约束: UNIQUE、NOT NULL、DEFAULT、CHECK}, 
    ...... 重复字段 ......
    PRIMARY KEY (字段1，字段2)
);

删除表 DROP

DROP TABLE {表名称}

例子:

DROP TABLE Introduce

插入 Insert

INSERT INTO TABLE_NAME (列名称1, 列名称2, ...) VALUES (值1, 值2, ...);

例子:

INSERT INTO Introduce (title, pageIdName, status) VALUES (?, ?, ?);

删除 Delete

DELETE FROM {表名称} WHERE 字段1 = ? AND 字段2 = ? AND (字段3 = ? OR 字段3 = 0);

条件判断：可以使用AND OR，也可以组合使用

例子:

DELETE FROM Introduce WHERE childId = ? AND kindId = ? ;

修改 Update

UPDATE {表名} SET 列1 = 值1, 列2 = 值2 .... WHERE 字段1 = ? AND 字段2 = ? AND (字段3 = ? OR 字段3 = 0);

条件判断：可以使用AND OR，也可以组合使用

例子:

UPDATE Introduce SET title = ?, pageIdName = ?, kindId = ? WHERE guidePageId = ? ;

查询 Select

单张表查询

SELECT * FROM {表的名称} WHERE 字段1 = ? AND 字段2 = ? AND (字段3 = ? OR 字段3 = 0);;

条件判断：可以使用AND OR，也可以组合使用

例子:

SELECT * FROM Introduce WHERE guidePageId = ? ;

查询有多少条数据 ，使用count()

SELECT count(*) FROM {表名称} WHERE {条件} ;

例子:

SELECT count(*) FROM IntroduceReadStatus WHERE guidePageId = 5 ;

如果是iOS的FMDB，获取结果的方法是longForQuery:

long count = [[SDBManager defaultDBManager].dataBase longForQuery:[querySQL copy]];

多表查询: 关键字 INNER JOIN ON

下面例子：从表1中查询，需要关联表2

• 多表条件使用 ON, 多个条件使用 AND OR 判断
• 查询表的字段格式: 可以直接写字段值
• 关联表的字段格式: {表名称}.{字段名称}

SELECT * FROM {表1} INNER JOIN {表2} ON {表1}.{字段} = {表2}.{字段} AND {表2}.{字段} = {值} WHERE childId = '12346' AND (kindId = '123' OR kindId = 0) AND status = 1;

例子:

SELECT * FROM Introduce INNER JOIN IntroduceReadStatus ON Introduce.guidePageId = IntroduceReadStatus.guidePageId WHERE childId = ? AND (kindId = ? OR kindId = 0)AND status = ? ;

其他的

ALTER TABLE

// 修改表名称
ALTER TABLE {表名称} RENAME TO {新表名};
// 增加字段
ALTER TABLE {表名称} ADD COLUMN {字段 数据类型};

Limit 限制

限制查询到的数据的数量

1、SELECT * FROM MineTable2 WHERE pageID = 'p_heal_20';
2、SELECT * FROM MineTable2 WHERE pageID = 'p_heal_20' LIMIT 2;
3、SELECT * FROM MineTable2 WHERE pageID = 'p_heal_20' LIMIT 2 OFFSET 3;

• 1查询出所有的符合条件的数据；
• 2查询出符号条件的前2条数据；
• 3查询出符合条件的前2条数据，去掉前3条数据，从3+1=4条数据开始计算数量；

别名 AS

给列coloum临时重命名一下，实际上数据库表中的列的名称不会发生改变

SELECT posGroup, posID, pageID AS pageId FROM MineTable2;

上面语句查询到的列有: posGroup posID pageId(数据库中的表的字段名称为pageID)

DISTINCT 筛除重复的数据

SELECT DISTINCT {字段} FROM {表名称} WHERE {条件};

分组 GROUP BY

在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

HAVING

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果

结构如下:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

常用函数

COUNT

获取查询结果的数量

SELECT count(*) FROM {表名称} WHERE {条件} ;

例子:

SELECT count(*) FROM MineTable2 WHERE pageID = 'p_heal_20';

MAX 最大值

获取查询结果中某列的最大值

SELECT max(字段名称) FROM {表名称} WHERE {条件};

例子:

SELECT max(configId) FROM MineTable2 WHERE pageID = 'p_heal_20';

MIN 最小值

某列的最小值

AVG 平均值

某列的平均值

SUM 求和

为一个数值列计算总和; 非数值列结果为0.0

ABS 绝对值

返回参数的绝对值；

针对字符串类型的数值排序可以使用ABS进行处理；

UPPER 字符串转化为大写字母

LOWER 字符串转化为小写字母

LENGTH 返回字符串的长度

事务

是数据库的执行单元；

有下面的命令:

• BEGIN TRANSACTION：开始事务处理。
• COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
• ROLLBACK：：回滚所做的更改。

格式:

BEGIN TRANSACTION;

执行语句

COMMIT; // 或者 ROLLBACK;

例子:

BEGIN TRANSACTION;
DELETE FROM MineTable2 WHERE MineTable2.configId = 130;
ROLLBACK;

附录

sqlite3状态码

#define SQLITE_OK           0   /* Successful result */
/* beginning-of-error-codes */
#define SQLITE_ERROR        1   /* SQL error or missing database */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */
#define SQLITE_INTERRUPT    9   /* Operation terminated by sqlite3_interrupt()*/
#define SQLITE_IOERR       10   /* Some kind of disk I/O error occurred */
#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
#define SQLITE_NOTFOUND    12   /* Unknown opcode in sqlite3_file_control() */
#define SQLITE_FULL        13   /* Insertion failed because database is full */
#define SQLITE_CANTOPEN    14   /* Unable to open the database file */
#define SQLITE_PROTOCOL    15   /* Database lock protocol error */
#define SQLITE_EMPTY       16   /* Database is empty */
#define SQLITE_SCHEMA      17   /* The database schema changed */
#define SQLITE_TOOBIG      18   /* String or BLOB exceeds size limit */
#define SQLITE_CONSTRAINT  19   /* Abort due to constraint violation */
#define SQLITE_MISMATCH    20   /* Data type mismatch */
#define SQLITE_MISUSE      21   /* Library used incorrectly */
#define SQLITE_NOLFS       22   /* Uses OS features not supported on host */
#define SQLITE_AUTH        23   /* Authorization denied */
#define SQLITE_FORMAT      24   /* Auxiliary database format error */
#define SQLITE_RANGE       25   /* 2nd parameter to sqlite3_bind out of range */
#define SQLITE_NOTADB      26   /* File opened that is not a database file */
#define SQLITE_NOTICE      27   /* Notifications from sqlite3_log() */
#define SQLITE_WARNING     28   /* Warnings from sqlite3_log() */
#define SQLITE_ROW         100  /* sqlite3_step() has another row ready */
#define SQLITE_DONE        101  /* sqlite3_step() has finished executing */