SQLite数据库

关于SQLITE数据库的基础知识和SQL语句的整理归纳，欢迎大家修改使用。

SQLite

• 特性
  • 点命令
    • 注意事项
      • 以”.“开头
      • 结尾没”;“
      • 点前不要有空格
    • 可用命令
      • 显示所有命令
        • .help
      • 常用命令
        • .schema table_name:显示创建语句
        • .database .table .show .mode .timer .header .width
    • sqlite_master 表格
      • 保存数据库表的关键信息
      • .schema sqlite_master
  • 注释
    • SQL 注释以两个连续的 "-" 字符（ASCII 0x2d）开始，至换行符（ASCII 0x0a）结束
    • C 风格的注释，以 "/*" 开始，并扩展至下一个 "*/" 字符
  • 数据库操作
    • 创建数据库
      • 基本语法
        • sqlite3 DatabaseName.db
          • 后缀名随意，常用的有：.db .db3 .sqlite .sqlite3
      • 查看
        • .databases 命令
      • .dump 命令
        • 导出
          • sqlite3 testDB.db .dump > testDB.sql
        • 导入
          • sqlite3 testDB.db < testDB.sql
    • 附加数据库
      • ATTACH DATABASE file_name AS database_name;
    • 分离数据库
      • DETACH DATABASE 'Alias-Name';
      • 无法分离 main 或 temp 数据库
• 数据类型
  • 介绍
    • 值的数据类型与值本身是相关的，而不是与它的容器相关。
    • SQLite 使用一个更普遍的动态类型系统
  • 分类
    • 创建表时，用具体类型即可，不必指定亲和类。
    • 存储类
      • NULL
      • INTEGER
      • REAL
      • TEXT
      • BLOB
    • 亲和类（Affinity）
      • 任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。
    • Boolean类型
      • SQLite 没有单独的 Boolean 存储类
      • 布尔值被存储为整数 0（false）和 1（true）
    • Date与Time类型
      • SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。
• 补充
  • 因为所有的点命令只在 SQLite 提示符中可用，所以当您进行带有 SQLite 的编程时，您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表
  • 显示表
    • SELECT tbl_name FROM sqlite_master WHERE type = 'table';
  • 显示创建语句
    • SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY'
  • 在一些GUI管理工具中，where子句可以使用正则表达式
    • where column_name regexp 'regular_expression';
  • 时间日期
    • 修饰符 - modifier
      • NNN days NNN hours NNN minutes NNN.NNNN seconds NNN months NNN years start of month start of year start of day weekday N unixepoch localtime utc
    • 格式化 - strftime
  • SQLite常用函数
    • COUNT MAX MIN AVG SUM RANDOM(有负，随机整数) ABS UPPER(字符串大写) LOWER LENGTH(字符串长度) sqlite_version(SQLite 库的版本)
• 一般SQL语句
  • 设计表结构
    • 创建表
      • CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype, );
    • 删除表
      • DROP TABLE database_name.table_name;
    • 约束
      • NOT NULL 约束：确保某列不能有 NULL 值。 DEFAULT 约束：当某列没有指定值时，为该列提供默认值。 UNIQUE 约束：确保某列中的所有值是不同的。 PRIMARY Key 约束：唯一标识数据库表中的各行/记录。 CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
    • 自动递增
      • AUTOINCREMENT
    • ALTER修改表
      • 重命名表
        • ALTER TABLE database_name.table_name RENAME TO new_table_name;
      • 添加列
        • ALTER TABLE database_name.table_name ADD COLUMN column_def...;
      • 说明
        • 在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。
  • 数据操作
    • 数据增删改
      • 插入单条数据
        • INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
      • 用一个表填充另一个表
        • INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name [WHERE condition];
      • 更改记录
        • UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
      • 删除记录
        • DELETE FROM table_name WHERE [condition];
        • 递增归零
          • 当 SQLite 数据库中包含自增列时，会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列：name 和 seq。name 记录自增列所在的表，seq 记录当前序号（下一条记录的编号就是当前序号加 1）。如果想把某个自增列的序号归零，只需要修改 sqlite_sequence 表就可以了。
          • DELETE FROM sqlite_sequence WHERE name = 'table_name';
          • UPDATE sqlite_sequence SET seq = 0 WHERE name = 'table_name';
    • 数据查询
      • 最基础查询
        • SELECT column1, column2, columnN FROM table_name;
      • WHERE
        • 常见搭配
          • > < + <= >= <>
          • IN (a,b,c)
          • IS NULL/NOT NULL
          • LIKE
            • 百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符
          • BETWEEN start AND end
          • EXISTS
          • AND/OR
          • GLOB
            • 与LIKE的区别在于大小写敏感
            • 星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。
      • LIMIT
        • SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
      • ORDER BY
        • SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
      • GROUP BY
        • SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
      • HAVING
        • SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
      • DISTINCT
      • JOIN
        • 交叉连接 - CROSS JOIN
          • 返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
          • SELECT ... FROM table1 CROSS JOIN table2 ...
        • 内连接 - INNER JOIN
          • 根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
          • SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
          • SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
          • SELECT ... FROM table1 NATURAL JOIN table2...
          • 三种语法形式
        • 外连接 - OUTER JOIN
          • SQLite 只支持 左外连接（LEFT OUTER JOIN）
          • 外连接是内连接的扩展，主连接计算完成后，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。
          • SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
          • SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
      • UNION [ALL]
        • SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION/UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
      • 子查询
        • 子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
        • 以下是子查询必须遵循的几个规则： 子查询必须用括号括起来。 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。 ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。 BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
• 高级操作
  • PRAGMA
    • 说明
      • SQLite 的 PRAGMA 命令是一个特殊的命令，可以用在 SQLite 环境内控制各种环境变量和状态标志。
    • 查询PRAGMA
      • PRAGMA pragma_name;
    • 设置PRAGMA
      • PRAGMA pragma_name = value;
  • 别名
    • 表别名
    • 列别名
  • 视图
    • CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
  • 事务
    • 特点
      • 原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。 一致性（Consistency)：确保数据库在成功提交的事务上正确地改变状态。 隔离性（Isolation）：使事务操作相互独立和透明。 持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。
    • 事务控制
      • BEGIN TRANSACTION
        • BEGIN; or BEGIN TRANSACTION;
      • COMMIT
        • COMMIT; or END TRANSACTION;
      • ROLLBACK
        • ROLLBACK;
      • 注意
        • 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
  • 索引
    • 索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。 使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。 索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
    • 添加索引
      • 单列索引
        • CREATE INDEX index_name ON table_name (column_name);
      • 组合索引
        • CREATE INDEX index_name on table_name (column1, column2);
      • 唯一索引
        • CREATE UNIQUE INDEX index_name on table_name (column_name);
      • 隐式索引
        • 主键约束和唯一约束
    • 查看索引
      • .indices 或 .indexes
      • SELECT * FROM sqlite_master WHERE type = 'index';
    • 删除索引
      • DROP INDEX index_name;
    • 使用索引 - Indexed By
      • SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);
• WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。