SQLite3和SQLite数据库的操作

1.格式化sqlite3的显示：
sqlite>.header on
sqlite>.mode column (设置输出模式，column 左对齐的列；line 每行一个值)
sqlite>.timer on
sqlite>.width column1_width column2_width ... (为 "column" 模式设置列宽度。)
2.SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义
3.sqlite语句要以分号";"结尾
4.$sqlite3 DatabaseName.db 创建一个新的数据库
5.sqlite>.databases 查看数据库列表
6.$sqlite3 testDB.db .dump > testDB.sql (将整个 testDB.db 数据库的内容转换为 SQLite 的语句，并将其转储到 ASCII 文本文件 testDB.sql 中)
7.$sqlite3 testDB.db < testDB.sql (将sql文件中的SQLite语句还原成数据库)
8.sqlite> ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
(数据库DatabaseName尚未被创建，上面的命令将创建一个数据库DatabaseName,并且数据库名为Alias-Name；如果数据库已存在，则把数据库文件名称与逻辑数据库 'Alias-Name' 绑定在一起。)
(数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息)
9.sqlite> DETACH DATABASE 'Alias-Name'; 把命名数据库从一个数据库连接分离和游离出来，连接是之前使用 ATTACH 语句附加的。无法分离 main 或 temp 数据库
10.sqlite> CREATE TABLE (database_name.)table_name( 用于在任何给定的数据库创建一个新表
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype,
);
11.sqlite>.tables 列出附加数据库中的所有表
12.sqlite>.schema table_name （得到表的完整信息，即create table 语句）
13.sqlite> DROP TABLE (database_name.)table_name; 删除表定义及其所有相关数据
14. 向数据库的某个表中添加新的数据行
1.sqlite> INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) 为指定列添加数据
VALUES (value1, value2, value3,...valueN);
2.sqlite> INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); 为所有列添加数据
15. SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
1.SELECT column1, column2, columnN FROM table_name1, table_name2, ...; 获取指定列组成的结果表
2.SELECT * FROM table_name1, table_name2, ...; 获取所有列组成的结果表
16. 获取sqlite3的schema信息：（相当于sqlite3中有一个sqlite_master表，此表中有type、tbl_name、sql等字段），于是：
1. sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table'; 列出所有在数据库中创建的表
2. sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'; 列出关于

COMPANY 表的完整信息
17.sqlite> UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
如果您想修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值，则不需要使用 WHERE 子句
18.sqlite> DELETE FROM table_name
WHERE [condition];
使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
19.SQLite Like 子句：
1. 这里有两个通配符与 LIKE 运算符一起使用：百分号（%）代表零个、一个或多个数字或字符；下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。
20.SQLite Glob 子句：
1. 与 LIKE 运算符不同的是，GLOB 是大小写敏感的
2. 星号（*）代表零个、一个或多个数字或字符；问号（?）代表一个单一的数字或字符。这些符号可以被组合使用。
21.sqlite> SELECT column1, column2, columnN
FROM table_name
LIMIT count(amount of rows) OFFSET num(row num)
从表中自上而下，偏移num行，取count行出来
22.sqlite> SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
1.SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据
2.在排序过程中，当column1的2个数据相同时，才按column2的来排，以此类推

23.sqlite> SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN
1.GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组
2.GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前
3.GROUP BY 常与合计函数，如SUM(column)一起使用，表示在分组后的集合内执行此函数

24.sqlite> SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
1.在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用
2.WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件
3.HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前(HAVING 之前必要要有GROUP BY 子句)
25. 对select 语句的关键字 小结:
1.书写顺序: s...f....w....g....h....o
2.解析顺序: f...w....g....h....s....o
26.sqlite> SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]
DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。即获取的结果集没有完全重复的多条记录
27.SQLite 约束：约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 SQLite 中常用的约束：
1.NOT NULL 约束：确保某列不能有 NULL 值。

2.DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
3.UNIQUE 约束：确保某列中的所有值是不同的。
4.PRIMARY Key 约束：唯一标识数据库表中的各行/记录。 一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键
5.CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
28.SQLite Joins： 用于结合两个或多个数据库中表的记录
SQL 定义了三种主要类型的连接：
1. 交叉连接 - CROSS JOIN ： sqlite> SELECT ... FROM table1 CROSS JOIN table2 ...
1.把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 列，则结果表有 x+y 列。
2.对结果集的每条记录有：table1中的每一行依次与table2的所有行结合，设table1有a行，table2有b行，则交叉连接后的结果集有a*b行
2.由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们
2. 内连接 - INNER JOIN ： sqlite> SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

1.根据conditional_expression比较table1与table2的每一行，条件满足的2个表对应记录中的所有列值组成一条结果集记录
2.内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
3. 外连接 - OUTER JOIN ： sqlite> SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

1. 虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）
2. LEFT JOIN 关键字会从左表 (table1) 那里返回所有的行，即使在右表 (table2) 中没有匹配的行
29.sqlite> SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION (ALL)
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
1.SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
2.为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度
3.UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
4.适用于 UNION 的规则同样适用于 UNION ALL 运算符。
30.SQLite NULL 值:
1.SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。
2.带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的
3.sqlite> SELECT ... FROM table WHERE cloumn IS NOT NULL; "IS NOT NULL"用于选取指定列所在的记录中的列值不为NULL的记录。
4.sqlite> SELECT ... FROM table WHERE c

loumn IS NULL; "IS NULL"用于选取指定列所在的记录中的列值为NULL的记录。
31.sqlite> SELECT column1 AS c_alias_name1, column2 AS C_alias_name2, ...
FROM table_name1 AS t_alias_name1, table_name2 AS t_alias_name2, ...
WHERE [condition];
SQLite 别名: 重命名是临时的改变，在数据库中实际的表的名称不会改变
32.SQLite Alter 命令 : 在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作
1. sqlite> ALTER TABLE (database_name.)table_name RENAME TO new_table_name;
2. sqlite> ALTER TABLE (database_name.)table_name ADD COLUMN column_def...; 新添加的列是以 NULL 值来填充的
33.SQLite 子查询:
1.子查询或内部查询或嵌套查询是在另一个 SQLite 查询内嵌入在 WHERE 子句中的查询。
使用子查询返回的数据将被用在主查询中作为条件，以进一步限制要检索的数据。
2.子查询必须用括号括起来。
3.ORDER BY 不能用在子查询中。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
4.BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用
5.子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
34.SQLite Autoincrement（自动递增）：
1.SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增
2.关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段。
35. SQLite函数：
1. sqlite> SELECT count(*) | count(column_name) FROM COMPANY; SQLite COUNT 聚集函数是用来计算一个数据库表中的行数
2. sqlite> SELECT max(column) | min(column) FROM COMPANY; SQLite MAX 聚合函数允许我们选择某列的最大值 | 最小值
3. sqlite> SELECT avg(column) FROM COMPANY; SQLite AVG 聚合函数计算某列的平均值
4. sqlite> SELECT sum(column) FROM COMPANY; SQLite SUM 聚合函数允许为一个数值列计算总和
5. sqlite> SELECT random() AS Random; SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数
6. sqlite> SELECT upper(column) | lower(column) FROM COMPANY; SQLite UPPER 函数把字符串转换为大写字母 | 小写字母
7. sqlite> SELECT length(column) FROM COMPANY; SQLite LENGTH 函数返回字符串的长度
36.SQLite PRAGMA ： 用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置
1. sqlite> PRAGMA pragma_name;
2. sqlite> PRAGMA pragma_name = value;
实例：
1. encoding Pragma 控制字符串如何编码及存储在数据库文件中：
sqlite> PRAGMA encoding;
sqlite> PRAGMA encoding = format;
2. sqlite> PRAGMA table_info(table-name);
返回的每一行记录代表一个字段信息集，包括该字段的名称(name)，数据类型(type)，是否有非null约束(notnull)

，默认值(dflt_value)，是否有主键约束(pk)
37. SQLite 事务：
1. 事务的特性可以保证让某一系列的操作要么全部完成要么一个都不会完成
2. 事务（Transaction）是一个对数据库执行工作单元

3. 事务（Transaction）具有以下四个标准属性，通常根据首字母缩写为 ACID：

1.原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
2.一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。
3.隔离性（Isolation）：使事务操作相互独立和透明。
4.持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。
4. 使用下面的命令来控制事务：
1.BEGIN TRANSACTION：开始事务处理。
事务（Transaction）可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。
此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。
不过在数据库关闭或发生错误时，事务处理也会回滚
https://www.wendangku.net/doc/743167852.html,MIT：保存更改，或者可以使用 END TRANSACTION 命令。
COMMIT 命令把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。
3.ROLLBACK：回滚所做的更改。
ROLLBACK 命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。
5. 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用（没有select！）。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
Android中关于SQLite的API：

1. SQLiteDatabase:
1. public void execSQL(String sql) throws SQLException :
public void execSQL(String sql, Object[] bindArgs) throws SQLException :
sql字符串只能包含一条SQL语句，当有多条时，第一条的分号"；"后的语句会被忽略；
execSQL首要用途是创建新的数据库，对insert,delete,select,update语句，SQLiteDatabase中有专门的方法
使用execSQL会存在SQL注入的安全问题
2. rawQuery ： 此方法最好不用，用query方法代替
3. public int delete(String table, String whereClause, String[] whereArgs)
4. public int update(String table, ContentValues values, String whereClause, String[] whereArgs)
5. public long insert(String table, String nullColumnHack, ContentValues values)
public long replace(String table, String nullColumnHack, ContentValues initialValues)
replace解决了违反约束的问题，如当某字段约束为unique，replace插入一新行（某字段有与之前记录字段数据重复的记录），
则会将之前已有重复数据的字段所在的记录的其它字段替换为新值。
nullColumHack参数是指：在initialValues集合是空的时，设置nullColumHack中的字段为NULL值，

以保证插入一个新行。nullColumHack可以为null