简介

• SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite是一个增长最快的数据库引擎，这是在普及方面的增长，与它的尺寸大小无关。SQLite 源代码不受版权限制
• SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库一样，您不需要在系统中配置。
  就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件

SQLite 命令

• 数据定义语言
  • CREATE 创建一个新的表,一个表的视图, 或者数据库中的其他对象
  • ALTER 修改数据库中的某个已有的数据库对象,比如一个表
  • DROP 删除整个表,或者表的视图,或者数据库中的其他对象
• 数据操作语言
  • INSERT 创建一条记录
  • DELETE 删除记录
  • UPDATE 修改记录
  • SELECT 从一个或多个表中检索某些记录

SQLite数据类型

• SQLite 数据类型是一个用来指定任何对象的数据类型的属性。SQLite 中的每一列，每个变量和表达式都有相关的数据类型
• 您可以在创建表的同时使用这些数据类型。SQLite 使用一个更普遍的动态类型系统。在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关
  • NULL 值是一个NULL值
  • INTEGER 值是一个带符号的整数,根据值的大小存储在1,2,3,4,6或者8字节中
  • REAL 值是一个浮点值,存储为8字节的IEEE浮点数字
  • TEXT 值是一个文本字符串,使用数据库编码(UTF-8,UTF-16BE或者UTF-16LE)存储
  • BLOB 值是一个blob数据,完全根据他的输入存储

SQLite亲和类型

• SQLite'支持列的亲和类型概念,任何列仍然可以存储任何类型的数据,当数据插入时,该字段的数据会优先采用亲缘类型作为该值的存储方式,SQLite目前的版本支持一下5种亲缘类型
  • TEXT 数值型数据在被插入之前,需先被转换为文本格式,之后再插入到目标字段中
  • NUMERIC 当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据格式转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据.对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据.需要额外说明的是,对于浮点格式的常量文本,如"30000.0",如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式
  • INTEGER 对于亲缘型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时.
  • REAL 其规则基本等同于NUMERIC,唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式
  • NONE 不做任何的转换,直接以该数据所属的数据类型进行存储.

创建数据库

//app内数据库文件存放路径-一般存放在沙盒中
NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
NSString *DBPath = [documentPath stringByAppendingPathComponent:@"appDB.sqlite"];
//创建(指定路径不存在数据库文件)/打开(已存在数据库文件) 数据库文件
//sqlite3_open(<#const char *filename#>, <#sqlite3 **ppDb#>)  filename:数据库路径  ppDb:数据库对象
if (sqlite3_open(DBPath.UTF8String, &_db) != SQLITE_OK) {
    //数据库打开失败
}else{
    //打开成功创建表
}

创建表

• SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。

  -(BOOL)creatTable{
  //创建表的SQL语句
  //用户 表
  NSString *creatUserTable = @"CREATE TABLE IF NOT EXISTS 't_User' ( 
  'ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'name' TEXT,
  'age' INTEGER,
  'icon' TEXT);";
  //车 表
  NSString *creatCarTable = @"CREATE TABLE IF NOT EXISTS 't_Car' (
  'ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  'type' TEXT,
  'output' REAL,
  'master' TEXT);";
  //项目中一般不会只有一个表
  NSArray *SQL_ARR = [NSArray arrayWithObjects:creatUserTable,creatCarTable, nil];
  return [self creatTableExecSQL:SQL_ARR];
  }
  
  
  -(BOOL)creatTableExecSQL:(NSArray *)SQL_ARR{
  for (NSString *SQL in SQL_ARR) {
    //参数一:数据库对象  参数二:需要执行的SQL语句  其余参数不需要处理
    if (![self execSQL:SQL]) {
        return NO;
    }
  }
  return YES;
  }
  
  
  
  #pragma 执行SQL语句
  -(BOOL)execSQL:(NSString *)SQL{
  char *error;
  if (sqlite3_exec(self.db, SQL.UTF8String, nil, nil, &error) == SQLITE_OK) {
    return YES;
  }else{
    NSLog(@"SQLiteManager执行SQL语句出错:%s",error);
    return NO;
  }
  }
  

删除表

• SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
  • 使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。

DROP TABLE database_name.table_name;

SQLite运算符

• 算符是一个保留字或字符，主要用于 SQLite 语句的 WHERE 子句中执行操作，如比较和算术运算。
  运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。

算数运算符

比较运算符

假设变量a=10,变量b=20,则:

==   检查两个操作数的值是否相等，如果相等则条件为真。           (a == b) 不为真。
=    检查两个操作数的值是否相等，如果相等则条件为真。           (a = b) 不为真。
!=   检查两个操作数的值是否相等，如果不相等则条件为真。         (a != b) 为真。
<>   检查两个操作数的值是否相等，如果不相等则条件为真。          (a <> b) 为真。
>    检查左操作数的值是否大于右操作数的值，如果是则条件为真。   (a > b) 不为真。
<    检查左操作数的值是否小于右操作数的值，如果是则条件为真。   (a < b) 为真。
>=   检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。 (a >= b) 不为真。
<=   检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。 (a <= b) 为真。
!<   检查左操作数的值是否不小于右操作数的值，如果是则条件为真。  (a !< b) 为假。
!>   检查左操作数的值是否不大于右操作数的值，如果是则条件为真。  (a !> b) 为真。

SQLite表达式

• 表达式是一个或多个值、运算符和计算值的SQL函数的组合。
  SQL 表达式与公式类似，都写在查询语言中。您还可以使用特定的数据集来查询数据库。

布尔表达式

选择年龄等于18的用户

SELECT * FROM 't_User' WHERE SALARY = 18;

数值表达式

这些表达式用来执行查询中的任何数学运算。语法如下：

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

在这里，numerical_expression 用于数学表达式或任何公式。下面的实例演示了 SQLite 数值表达式的用法：

SELECT (15 + 6) AS ADDITION
ADDITION = 21

有几个内置的函数，比如 avg()、sum()、count()，等等，执行被称为对一个表或一个特定的表列的汇总数据计算。

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
RECORDS = 7

Where子句

下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：

SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录，结果显示所有的记录，意味着没有一个记录的 AGE 等于 NULL：

SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

下面的 SELECT 语句列出了 NAME 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制：

SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

下面的 SELECT 语句列出了 NAME 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制：

SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';

下面的 SELECT 语句列出了 AGE 的值为 25 或 27 的所有记录：

SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

下面的 SELECT 语句列出了 AGE 的值既不是 25 也不是 27 的所有记录：

SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

下面的 SELECT 语句列出了 AGE 的值在 25 与 27 之间的所有记录：

SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

Insert语句(增)

• INSERT INTO 语句有两种基本语法，如下所示：

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

• 在这里，column1, column2,...columnN 是要插入数据的表中的列的名称。
  如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

iOS代码

-(BOOL)insertSelfToDB{
//插入对象的SQL语句
NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO 't_User' (name,age,icon) VALUES ('%@',%ld,'%@');",self.name,self.age,self.icon];
return [self execSQL:insertSQL];
}

-(BOOL)execSQL:(NSString *)SQL{
char *error;
if (sqlite3_exec(self.db, SQL.UTF8String, nil, nil, &error) == SQLITE_OK) {
    return YES;
}else{
    NSLog(@"SQLiteManager执行SQL语句出错:%s",error);
    return NO;
}
}

Delete(删)

• SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除

带有 WHERE 子句的 DELETE 查询的基本语法如下：

DELETE FROM table_name
WHERE [condition];

删除年龄大于18的人员

+ (void)deleteUser {
NSString *sql = @"DELETE FROM 't_User' WHERE AGE > 18";
[self execSQL:sql];
}

-(BOOL)execSQL:(NSString *)SQL{
char *error;
if (sqlite3_exec(self.db, SQL.UTF8String, nil, nil, &error) == SQLITE_OK) {
    return YES;
}else{
    NSLog(@"SQLiteManager执行SQL语句出错:%s",error);
    return NO;
}
}

如果您想要从 COMPANY 表中删除所有记录，则不需要使用 WHERE 子句，DELETE 查询如下：

DELETE FROM COMPANY;

Update(改)

• SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新

带有 WHERE 子句的 UPDATE 查询的基本语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

把ID为6的用户的年龄改为17,姓名改为chai

+ (void)updateUser {
NSString *sql = @"UPDATE 't_User' SET AGE = 17, NAME = 'chai' WHERE ID = 6";
[self execSQL:sql];
}
-(BOOL)execSQL:(NSString *)SQL{
char *error;
if (sqlite3_exec(self.db, SQL.UTF8String, nil, nil, &error) == SQLITE_OK) {
    return YES;
}else{
    NSLog(@"SQLiteManager执行SQL语句出错:%s",error);
    return NO;
}
}

Select(查)

• SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集

SQLite 的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN FROM table_name;

在这里，column1, column2...是表的字段，他们的值即是您要获取的。如果您想获取所有可用的字段，那么可以使用下面的语法

SELECT * FROM table_name;

iOS代码,读取年龄大于18的

-(BOOL)selectUser{
//插入对象的SQL语句
NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM 't_User' WHERE AGE > 18];
return [self querySQL:insertSQL];
}

#pragma mark - 查询数据库中数据
-(NSArray *)querySQL:(NSString *)SQL{
//准备查询
// 1> 参数一:数据库对象
// 2> 参数二:查询语句
// 3> 参数三:查询语句的长度:-1
// 4> 参数四:句柄(游标对象)
//    sqlite3_prepare_v2(<#sqlite3 *db#>, <#const char *zSql#>, <#int nByte#>, <#sqlite3_stmt **ppStmt#>, <#const char **pzTail#>)
sqlite3_stmt *stmt = nil;
if (sqlite3_prepare_v2(self.db, SQL.UTF8String, -1, &stmt, nil) != SQLITE_OK) {
    NSLog(@"准备查询失败!");
    return NULL;
}
//准备成功,开始查询数据
//定义一个存放数据字典的可变数组
NSMutableArray *dictArrM = [[NSMutableArray alloc] init];
while (sqlite3_step(stmt) == SQLITE_ROW) {
    //一共获取表中所有列数(字段数)
    int columnCount = sqlite3_column_count(stmt);
    //定义存放字段数据的字典
    NSMutableDictionary *dict = [[NSMutableDictionary alloc] init];
    for (int i = 0; i < columnCount; i++) {
        // 取出i位置列的字段名,作为字典的键key
        const char *cKey = sqlite3_column_name(stmt, i);
        NSString *key = [NSString stringWithUTF8String:cKey];
        
        //取出i位置存储的值,作为字典的值value
        const char *cValue = (const char *)sqlite3_column_text(stmt, i);
        NSString *value = [NSString stringWithUTF8String:cValue];
        
        //将此行数据 中此字段中key和value包装成 字典
        [dict setObject:value forKey:key];
    }
    [dictArrM addObject:dict];
}
return dictArrM;
}

Like 子句

• SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。这里有两个通配符与 LIKE 运算符一起使用:
  • 百分号(%)
  • 下划线(_)
• 百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。

• 下面一些实例演示了 带有 '%' 和 '_' 运算符的 LIKE 子句不同的地方：

  • 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 结尾的任意值

• 您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXXX 可以是任何数字或字符串值。

查找年龄包含8的所有用户

+ (NSArray *)likeUser {
NSString *sql = @"SELECT * FROM 't_User' WHERE AGE LIKE '%8%'";
return [self querySQL:sql];
}

Glob 子句

• SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。

  • 星号(*)
  • 问号(?)

• 星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。

• 下面一些实例演示了 带有 '*' 和 '?' 运算符的 GLOB 子句不同的地方：

  • 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 结尾的任意值

• 您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXXX 可以是任何数字或字符串值。

查找年龄包含8的所有用户

+ (NSArray *)likeUser {
NSString *sql = @"SELECT * FROM 't_User' WHERE AGE GLOB '*8*'";
return [self querySQL:sql];
}

Limit 子句

• SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。

带有 LIMIT 子句的 SELECT 语句的基本语法如下：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

从表中提取6行

+ (NSArray *)limitUser {
NSString *sql = @"SELECT * FROM 't_User' LIMIT 6";
return [[SQLiteManager shareInstance] querySQL:sql];
}

获取从第三个位置开始提取3个记录

+ (NSArray *)limitUser {
NSString *sql = @"SELECT * FROM 't_User' LIMIT 3 OFFSET 3";
return [[SQLiteManager shareInstance] querySQL:sql];
}

Order By(排序)

• SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据

ORDER BY 子句的基本语法如下：

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

用户按年龄升序排列

+ (NSArray *)orderByAge; {
NSString *sql = @"SELECT * FROM 't_User' ORDER BY AGE ASC";
return [self querySQL:sql];
}

在年龄大于18的用户中按姓名和年龄升序排列

+ (NSArray *)orderByAge; {
NSString *sql = @"SELECT * FROM 't_User' WHERE AGE > 18 ORDER BY NAME, AGE ASC";
return [self querySQL:sql];
}

Group By

• SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。
  在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。

下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN 

如果您想了解每个客户的工资总额，则可使用 GROUP BY 查询，如下所示：

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

获取每个用户的工资总额.并按姓名降序排列

SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

Having 子句

• HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
  WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法：

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

下面是一个实例，它将显示名称计数小于 2 的所有记录：

SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

Distinct 关键字

• SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
  有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，它只获取唯一一次记录，而不是获取重复记录。

用于消除重复记录的 DISTINCT 关键字的基本语法如下：

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

用户重名的只获取一次

SELECT DISTINCT name FROM COMPANY;