[TOC]

sqlite3

數(shù)據(jù)類型

基礎(chǔ)的 (NULL INTEGER REAL TEXT BLOB)

親和(Affinity)類型

運(yùn)算符

算術(shù)運(yùn)算符

+ - * / %

例子:

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

上面兩者等價(jià)

和其他語(yǔ)言OC Java 等都是相同的

比較運(yùn)算符

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

和其他編程語(yǔ)言相通的有: == =(賦值) != > >= <``<=
OC Java 不支持: !< !> <>

但是在navicat中使用 !< !> <> 也會(huì)報(bào)錯(cuò)，所以不推薦使用

例子:

SELECT * FROM MineTable2 WHERE posGroup >= 2

邏輯運(yùn)算符

• AND: 多個(gè)條件，“與”，都為真，結(jié)果為真
• OR: 多個(gè)條件，“或”，有一個(gè)為真，結(jié)果為真
• NOT: 取反，“非”
• IS: 和 = 相似
• IS NOT: 和 != 相似
• IS NULL: 是否為空NULL
• BETWEEN: 在范圍內(nèi)搜索，最大值和最小值，等價(jià)于 >= <=;下面兩個(gè)語(yǔ)句是等價(jià)的
  • SELECT * FROM MineTable2 WHERE posGroup BETWEEN 1 AND 2
  • SELECT * FROM MineTable2 WHERE posGroup >=1 AND posGroup <= 2
• IN: 在范圍內(nèi)查找，類似多個(gè) (OR) 相等性 = 條件查詢 ;下面兩個(gè)語(yǔ)句是等價(jià)的
  • SELECT * FROM MineTable2 WHERE posGroup IN (1, 2)
  • SELECT * FROM MineTable2 WHERE posGroup = 1 OR posGroup = 2
• NOT IN: 不在范圍內(nèi)，和 IN 相反
• EXISTS: 存在性判斷，可以用于表table，也可以用在列coloum
  • CREATE TABLE IF NOT EXISTS .... 創(chuàng)建表的語(yǔ)句
• LIKE: 和通配符比較，大小寫(xiě)不敏感
  • %: 零個(gè)、一個(gè)、多個(gè)
  • _: 一個(gè)
  • 例子:
    • SELECT * FROM MineTable2 WHERE posGroup LIKE '%1' : 以1結(jié)尾的，可以是1位、2位、更多位
    • SELECT * FROM MineTable2 WHERE posGroup LIKE '_1' : 以1結(jié)尾的，2位
• GLOB: 和通配符比較，大小寫(xiě)敏感
  • *: 零個(gè)、一個(gè)、多個(gè)
  • ?: 一個(gè)
• ||: 連接兩個(gè)字符串，得到一個(gè)新的字符串；下面兩個(gè)語(yǔ)句查詢結(jié)果相同
  • SELECT * FROM MineTable2 WHERE posGroup = ('1' || '1');
  • SELECT * FROM MineTable2 WHERE posGroup = '11';

通配符查找

• LIKE 的 % 類似 GLOB 的 *
• LIKE 的 _ 類似 GLOB 的 ?
• LIKE 不區(qū)分大小寫(xiě)，GLOB區(qū)分大小寫(xiě)
  GLOB 類似正則表達(dá)式的語(yǔ)法

語(yǔ)句1: SELECT * FROM MineTable2 WHERE pageID LIKE '%H%';
語(yǔ)句2: SELECT * FROM MineTable2 WHERE pageID LIKE '%h%';

語(yǔ)句3: SELECT * FROM MineTable2 WHERE pageID GLOB '*h*';
語(yǔ)句4: SELECT * FROM MineTable2 WHERE pageID GLOB '*H*';

語(yǔ)句1 2 3 結(jié)果相同: h H 都可以查找到
語(yǔ)句4只查找H內(nèi)容

例子:

WHERE SALARY LIKE '200%'      查找以 200 開(kāi)頭的任意值
WHERE SALARY LIKE '%200%'     查找任意位置包含 200 的任意值
WHERE SALARY LIKE '_00%'      查找第二位和第三位為 00 的任意值
WHERE SALARY LIKE '2_%_%'     查找以 2 開(kāi)頭，且長(zhǎng)度至少為 3 個(gè)字符的任意值
WHERE SALARY LIKE '%2'       查找以 2 結(jié)尾的任意值
WHERE SALARY LIKE '_2%3'      查找第二位為 2，且以 3 結(jié)尾的任意值
WHERE SALARY LIKE '2___3'     查找長(zhǎng)度為 5 位數(shù)，且以 2 開(kāi)頭以 3 結(jié)尾的任意值

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

WHERE SALARY GLOB '200*'    查找以 200 開(kāi)頭的任意值
WHERE SALARY GLOB '*200*'   查找任意位置包含 200 的任意值
WHERE SALARY GLOB '?00*'    查找第二位和第三位為 00 的任意值
WHERE SALARY GLOB '2??' 查找以 2 開(kāi)頭，且長(zhǎng)度至少為 3 個(gè)字符的任意值
WHERE SALARY GLOB '*2'  查找以 2 結(jié)尾的任意值
WHERE SALARY GLOB '?2*3'    查找第二位為 2，且以 3 結(jié)尾的任意值
WHERE SALARY GLOB '2???3'   查找長(zhǎng)度為 5 位數(shù)，且以 2 開(kāi)頭以 3 結(jié)尾的任意值

位運(yùn)算符 （真假）

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

約束

約束是對(duì)表或列(字段)添加的條件限定

• PRIMARY Key: 唯一標(biāo)識(shí)數(shù)據(jù)庫(kù)表中的各行/記錄。
• NOT NULL: 確保某列不能有 NULL 值
• UNIQUE: 確保某列中的所有值是不同的。
  • 如果有插入相同的值會(huì)報(bào)錯(cuò) Unknown error finalizing or resetting statement (19: UNIQUE constraint failed: Introduce.status)
• DEFAULT: 當(dāng)某列沒(méi)有指定值時(shí)，為該列提供默認(rèn)值。
  • 例子: DEFAULT 0
• CHECK: 確保某列中的所有值滿足一定條件，才可以插入數(shù)據(jù)
  • 校驗(yàn)CHECK(status > 0)，如果插入的值檢驗(yàn)結(jié)果不滿足條件會(huì)報(bào)錯(cuò) 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 語(yǔ)句中，它也可用在 UPDATE、DELETE 語(yǔ)句中，等等

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 };

多個(gè)字段用逗號(hào),分割，可以指定每個(gè)字段升序/降序排序;

例子:

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

創(chuàng)建數(shù)據(jù)庫(kù) 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;
}

打開(kāi)數(shù)據(jù)庫(kù) 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];
    
}

關(guān)閉數(shù)據(jù)庫(kù) 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;
}

創(chuàng)建表 CREATE

語(yǔ)句格式:

單主鍵

CREATE TABLE IF NOT EXISTS {表名稱} (
    {主鍵} {數(shù)據(jù)類型:INTEGER} PRIMARY KEY AUTOINCREMENT,
    {字段} {數(shù)據(jù)類型:INTEGER、REAL、TEXT} {約束: UNIQUE、NOT NULL、DEFAULT、CHECK}, 
    ...... 重復(fù)字段 ......
);

例子:

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

復(fù)合主鍵

CREATE TABLE IF NOT EXISTS {表名稱} (
    {字段} {數(shù)據(jù)類型:INTEGER、REAL、TEXT} {約束: UNIQUE、NOT NULL、DEFAULT、CHECK}, 
    ...... 重復(fù)字段 ......
    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 = ? ;

查詢有多少條數(shù)據(jù) ，使用count()

SELECT count(*) FROM {表名稱} WHERE {條件} ;

例子:

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

如果是iOS的FMDB，獲取結(jié)果的方法是longForQuery:

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

多表查詢: 關(guān)鍵字 INNER JOIN ON

下面例子：從表1中查詢，需要關(guān)聯(lián)表2

• 多表?xiàng)l件使用 ON, 多個(gè)條件使用 AND OR 判斷
• 查詢表的字段格式: 可以直接寫(xiě)字段值
• 關(guān)聯(lián)表的字段格式: {表名稱}.{字段名稱}

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 {字段 數(shù)據(jù)類型};

Limit 限制

限制查詢到的數(shù)據(jù)的數(shù)量

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查詢出所有的符合條件的數(shù)據(jù)；
• 2查詢出符號(hào)條件的前2條數(shù)據(jù)；
• 3查詢出符合條件的前2條數(shù)據(jù)，去掉前3條數(shù)據(jù)，從3+1=4條數(shù)據(jù)開(kāi)始計(jì)算數(shù)量；

別名 AS

給列coloum臨時(shí)重命名一下，實(shí)際上數(shù)據(jù)庫(kù)表中的列的名稱不會(huì)發(fā)生改變

SELECT posGroup, posID, pageID AS pageId FROM MineTable2;

上面語(yǔ)句查詢到的列有: posGroup posID pageId(數(shù)據(jù)庫(kù)中的表的字段名稱為pageID)

DISTINCT 篩除重復(fù)的數(shù)據(jù)

SELECT DISTINCT {字段} FROM {表名稱} WHERE {條件};

分組 GROUP BY

在 SELECT 語(yǔ)句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

HAVING

HAVING 子句允許指定條件來(lái)過(guò)濾將出現(xiàn)在最終結(jié)果中的分組結(jié)果

結(jié)構(gòu)如下:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

常用函數(shù)

COUNT

獲取查詢結(jié)果的數(shù)量

SELECT count(*) FROM {表名稱} WHERE {條件} ;

例子:

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

MAX 最大值

獲取查詢結(jié)果中某列的最大值

SELECT max(字段名稱) FROM {表名稱} WHERE {條件};

例子:

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

MIN 最小值

某列的最小值

AVG 平均值

某列的平均值

SUM 求和

為一個(gè)數(shù)值列計(jì)算總和; 非數(shù)值列結(jié)果為0.0

ABS 絕對(duì)值

返回參數(shù)的絕對(duì)值；

針對(duì)字符串類型的數(shù)值排序可以使用ABS進(jìn)行處理；

UPPER 字符串轉(zhuǎn)化為大寫(xiě)字母

LOWER 字符串轉(zhuǎn)化為小寫(xiě)字母

LENGTH 返回字符串的長(zhǎng)度

事務(wù)

是數(shù)據(jù)庫(kù)的執(zhí)行單元；

有下面的命令:

• BEGIN TRANSACTION：開(kāi)始事務(wù)處理。
• COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
• ROLLBACK：：回滾所做的更改。

格式:

BEGIN TRANSACTION;

執(zhí)行語(yǔ)句

COMMIT; // 或者 ROLLBACK;

例子:

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

附錄

sqlite3狀態(tài)碼

#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 */