sqlite3_1_理论基础

简介

sqlite3 理论知识相关笔记

SQLite 简介

SQLite是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite是一个增长最快的数据库引擎，这是在普及方面的增长，与它的尺寸大小无关。SQLite 源代码不受版权限制
什么是SQLite
- SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。
- 就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件
为什么要用SQLite
- 不需要一个单独的服务器进程或操作的系统（无服务器的）。
- SQLite 不需要配置，这意味着不需要安装或管理。
- 一个完整的 SQLite 数据库是存储在一个单一的跨平台的磁盘文件。
- SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。
- SQLite 是自给自足的，这意味着不需要任何外部的依赖。
- SQLite 事务是完全兼容 ACID 的，允许从多个进程或线程安全访问。
- SQLite 支持 SQL92（SQL2）标准的大多数查询语言的功能。
- SQLite 使用 ANSI-C 编写的，并提供了简单和易于使用的 API。
- SQLite 可在 UNIX（Linux, Mac OS-X, Android, iOS）和 Windows（Win32, WinCE, WinRT）中运行。
SQLite 命令
- 与关系数据库进行交互的标准 SQLite 命令类似于 SQL。命令包括 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP。这些命令基于它们的操作性质可分为以下几种：
- DDL - 数据定义语言
  - CREATE : 创建一个新的表，一个表的视图，或者数据库中的其他对象。
  - ALERT : 修改数据库中的某个已有的数据库对象，比如一个表。
  - DROP : 删除整个表，或者表的视图，或者数据库中的其他对象。
- DML - 数据操作语言
  - INSERT : 创建一条记录
  - UPDATE : 修改记录
  - DELETE : 删除记录
- DQL - 数据查询语言
  - SELECT : 从一个或多个表中检索某些记录

SQLite 命令

本章将向您讲解 SQLite 编程人员所使用的简单却有用的命令。这些命令被称为 SQLite 的点命令，这些命令的不同之处在于它们不以分号 ; 结束。
如需获取可用的点命令的清单，可以在任何时候输入 “.help”。例如：
- sqlite>.help
.backup ?DB? FILE
- 备份 DB 数据库（默认是 “main”）到 FILE 文件。
.bail ON|
- OFF
- 发生错误后停止。默认为 OFF。
.databases
- 列出数据库的名称及其所依附的文件。
.dump ?TABLE?
- 以 SQL 文本格式转储数据库。如果指定了 TABLE 表，则只转储匹配 LIKE 模式的 TABLE 表。
.echo ON|OFF
- 开启或关闭 echo 命令。
.exit
- 退出 SQLite 提示符。
.explain ON|OFF
- 开启或关闭适合于 EXPLAIN 的输出模式。如果没有带参数，则为 EXPLAIN on，即开启 EXPLAIN。
.header(s) ON|OFF
- 开启或关闭头部显示。
.help
- 显示消息。
.import FILE TABLE
- 导入来自 FILE 文件的数据到 TABLE 表中。
.indices ?TABLE?
- 显示所有索引的名称。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表的索引。
.load FILE ?ENTRY?
- 加载一个扩展库。
.log FILE|off
- 开启或关闭日志。FILE 文件可以是 stderr（标准错误）/stdout（标准输出）。

.mode MODE

设置输出模式，MODE 可以是下列之一：
- csv 逗号分隔的值
- column 左对齐的列
- html HTML 的代码
  insert TABLE 表的 SQL 插入（insert）语句
  line 每行一个值
  list 由 .separator 字符串分隔的值
  tabs 由 Tab 分隔的值
  tcl TCL 列表元素
  .nullvalue STRING
  - 在 NULL 值的地方输出 STRING 字符串。
  .output FILENAME
  - 发送输出到 FILENAME 文件。
  .output stdout
  - 发送输出到屏幕。
  .print STRING…
  - 逐字地输出 STRING 字符串。
  .prompt MAIN CONTINUE
  - 替换标准提示符。
  .quit
  - 退出 SQLite 提示符。
  .read FILENAME
  - 执行 FILENAME 文件中的 SQL。
  .schema ?TABLE?
  - 显示 CREATE 语句。如果指定了 TABLE 表，则只显示匹配 LIKE 模式的 TABLE 表。
  .separator STRING
  - 改变输出模式和 .import 所使用的分隔符。
  .show
  - 显示各种设置的当前值。
  .stats ON|OFF
  - 开启或关闭统计。
  .tables ?PATTERN?
  - 列出匹配 LIKE 模式的表的名称。
  .timeout MS
  - 尝试打开锁定的表 MS 毫秒。
  .width NUM NUM
  - 为 “column” 模式设置列宽度。
  .timer ON|OFF
  - 开启或关闭 CPU 定时器。
  SQLite 语法
  - SQLite 是遵循一套独特的称为语法的规则和准则。
  - 大小写敏感性
    - 有个重要的点值得注意，SQLite 是不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。
  - 注释
    - SQLite 注释是附加的注释，可以在 SQLite 代码中添加注释以增加其可读性，他们可以出现在任何空白处，包括在表达式内和其他 SQL 语句的中间，但它们不能嵌套。
    - SQL 注释以两个连续的 “-“ 字符（ASCII 0x2d）开始，并扩展至下一个换行符（ASCII 0x0a）或直到输入结束，以先到者为准。
    - 您也可以使用 C 风格的注释，以 “/“ 开始，并扩展至下一个 “/“ 字符对或直到输入结束，以先到者为准。SQLite的注释可以跨越多行。
    - sqlite>.help – 这是一个简单的注释
  - SQLite 语句
    - 所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束。
  SQLite 数据类型
  - SQLite 数据类型是一个用来指定任何对象的数据类型的属性。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 数据，完全根据它的输入存储。
  - Date与Time数据类型
    - SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。
    - TEXT : 格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。
    - REAL : 从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
    - INTEGER : 从 1970-01-01 00:00:00 UTC 算起的秒数。
    - 您可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。
  SQLite 创建数据库
  - SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库。您不需要任何特殊的权限即可创建一个数据。
  - 语法
    - sqlite3 命令的基本语法如下：
      - $ sqlite3 DatabaseName.db
    - 通常情况下，数据库名称在 RDBMS 内应该是唯一的。
    - 另外我们也可以使用 .open 来建立新的数据库文件：
      - sqlite>.open test.db
    - 上面的命令创建了数据库文件 test.db，位于 sqlite3 命令同一目录下。
    - 打开已存在数据库也是用 .open 命令，以上命令如果 test.db 存在则直接会打开，不存在就创建它。
  - .dump 命令
    - 您可以在命令提示符中使用 SQLite .dump 点命令来导出完整的数据库在一个文本文件中，如下所示：
      - $sqlite3 testDB.db .dump > testDB.sql
    - 上面的命令将转换整个 testDB.db 数据库的内容到 SQLite 的语句中，并将其转储到 ASCII 文本文件 testDB.sql 中。您可以通过简单的方式从生成的 testDB.sql 恢复，如下所示：
      - $sqlite3 testDB.db < testDB.sql
    - 此时的数据库是空的，一旦数据库中有表和数据，您可以尝试上述两个程序。
  SQLite 附加数据库
  - 假设这样一种情况，当在同一时间有多个数据库可用，您想使用其中的任何一个。SQLite 的 ATTACH DATABASE 语句是用来选择一个特定的数据库，使用该命令后，所有的 SQLite 语句将在附加的数据库下执行。
  - 语法
    - SQLite 的 ATTACH DATABASE 语句的基本语法如下：
      - ATTACH DATABASE file_name AS database_name;
    - 如果数据库尚未被创建，上面的命令将创建一个数据库，如果数据库已存在，则把数据库文件名称与逻辑数据库 ‘Alias-Name’ 绑定在一起。
    - 打开的数据库和使用 ATTACH附加进来的数据库的必须位于同一文件夹下。
  - 实例
    - 如果想附加一个现有的数据库 testDB.db，则 ATTACH DATABASE 语句将如下所示：
      - sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
    - 使用 SQLite .database 命令来显示附加的数据库。
  1
  2
  3
  4
  5
  sqlite> .database
  seq name file
  --- --------------- ----------------------
  0 main /home/sqlite/testDB.db
  2 test /home/sqlite/testDB.db
数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息，如下所示
1
2
3
4
sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database main is already in use；

SQLite 分离数据库

SQLite 的 DETACH DATABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来，连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名，DETACH 命令将只断开给定名称的连接，而其余的仍然有效。您无法分离 main 或 temp 数据库。
如果数据库是在内存中或者是临时数据库，则该数据库将被摧毁，且内容将会丢失。
语法
- SQLite 的 DETACH DATABASE ‘Alias-Name’ 语句的基本语法如下：
  - DETACH DATABASE 'Alias-Name';
- 在这里，’Alias-Name’ 与您之前使用 ATTACH 语句附加数据库时所用到的别名相同。

SQLite 创建表

SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。
语法
- CREATE TABLE 语句的基本语法如下：
  1
  2
  3
  4
  5
  6
  7
  CREATE TABLE database_name.table_name(
  column1 datatype PRIMARY KEY(one or more columns),
  column2 datatype,
  column3 datatype,
  .....
  columnN datatype,
  );
- CREATE TABLE 是告诉数据库系统创建一个新表的关键字。CREATE TABLE 语句后跟着表的唯一的名称或标识。您也可以选择指定带有 table_name 的 database_name。

实例

下面是一个实例，它创建了一个 COMPANY 表，ID 作为主键，NOT NULL 的约束表示在表中创建纪录时这些字段不能为 NULL：

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

让我们再创建一个表，我们将在随后章节的练习中使用：

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

您可以使用 SQLIte 命令中的 .tables 命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。
1
2
sqlite>.tables
COMPANY DEPARTMENT

您可以使用 SQLite .schema 命令得到表的完整信息，如下所示：

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

SQLite 删除表

SQLite 的 DROP TABLE 语句用来删除表定义及其所有相关数据、索引、触发器、约束和该表的权限规范。
使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。
语法
- DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称，如下所示
  - DROP TABLE database_name.table_name;

SQLite Insert 语句

SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。
语法
- INSERT INTO 语句有两种基本语法，如下所示：
  1
  2
  INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
  VALUES (value1, value2, value3,...valueN);
- 在这里，column1, column2,…columnN 是要插入数据的表中的列的名称。
- 如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下：
  1
  INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

使用一个表来填充另一个表

您可以通过在一个有一组字段的表上使用 select 语句，填充数据到另一个表中。下面是语法：

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

SQLite Select 语句

SQLite 的 SELECT 语句用于从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。
语法
- SQLite 的 SELECT 语句的基本语法如下：
  - SELECT column1, column2, columnN FROM table_name;
- 在这里，column1, column2…是表的字段，他们的值即是您要获取的。如果您想获取所有可用的字段，那么可以使用下面的语法：
  - SELECT * FROM table_name;

SQLite 运算符

SQLite 运算符是什么？
- 运算符是一个保留字或字符，主要用于 SQLite 语句的 WHERE 子句中执行操作，如比较和算术运算。
- 运算符用于指定 SQLite 语句中的条件，并在语句中连接多个条件。
  - 算术运算符
  - 比较运算符
  - 逻辑运算符
  - 位运算符
SQLite 算术运算符
- 假设变量 a=10，变量 b=20，则：
- + : 加法 : 把运算符两边的值相加 a + b 将得到 30
- - : 减法 : 左操作数减去右操作数 a - b 将得到 -10
- * : 乘法 : 把运算符两边的值相乘 a * b 将得到 200
- / : 除法 : 左操作数除以右操作数 b / a 将得到 2
- % : 取模 : 左操作数除以右操作数后得到的余数 b % a 将得到 0
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 逻辑运算符
- 下面是 SQLite 中所有的逻辑运算符列表。
- AND AND : 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
- BETWEEN : BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
- EXISTS : EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
- IN : IN 运算符用于把某个值与一系列指定列表的值进行比较。
- NOT IN : IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
- LIKE : LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
- GLOB : GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
- NOT : NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
- OR : OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
- IS NULL : NULL 运算符用于把某个值与 NULL 值进行比较。
- IS : IS 运算符与 = 相似。
- IS NOT : IS NOT 运算符与 != 相似。
- || : 连接两个不同的字符串，得到一个新的字符串。
- UNIQUE : UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。
SQLite 位运算符
- 下表中列出了 SQLite 语言支持的位运算符。假设变量 A=60，变量 B=13，则：
- & : 如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。 (A & B) 将得到 12，即为 0000 1100
- | : 如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。 (A | B) 将得到 61，即为 0011 1101
- ~ : 二进制补码运算符是一元运算符，具有”翻转”位效应，即0变成1，1变成0。 (~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
- << : 二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 A << 2 将得到 240，即为 1111 0000
- >> : 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 A >> 2 将得到 15，即为 0000 1111

SQLite 表达式

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

语法

假设 SELECT 语句的基本语法如下：

1
2
3

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION | EXPRESSION];

SQLite - 布尔表达式
- SQLite 的布尔表达式在匹配单个值的基础上获取数据。语法如下：
  1
  2
  3
  SELECT column1, column2, columnN
  FROM table_name
  WHERE SINGLE VALUE MATCHING EXPRESSION;
SQLite - 数值表达式
- 这些表达式用来执行查询中的任何数学运算。语法如下：
  1
  2
  SELECT numerical_expression as OPERATION_NAME
  [FROM table_name WHERE CONDITION] ;
- 在这里，numerical_expression 用于数学表达式或任何公式。下面的实例演示了 SQLite 数值表达式的用法：
  1
  2
  sqlite> SELECT (15 + 6) AS ADDITION
  ADDITION = 21
- 有几个内置的函数，比如 avg()、sum()、count()，等等，执行被称为对一个表或一个特定的表列的汇总数据计算。
  1
  2
  sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
  RECORDS = 7
SQLite - 日期表达式
- 日期表达式返回当前系统日期和时间值，这些表达式将被用于各种数据操作。
  1
  2
  sqlite> SELECT CURRENT_TIMESTAMP;
  CURRENT_TIMESTAMP = 2013-03-17 10:43:35

SQLite Where 子句

SQLite的 WHERE 子句用于指定从一个表或多个表中获取数据的条件。
如果满足给定的条件，即为真（true）时，则从表中返回特定的值。您可以使用 WHERE 子句来过滤记录，只获取需要的记录。
WHERE 子句不仅可用在 SELECT 语句中，它也可用在 UPDATE、DELETE 语句中，等等，这些我们将在随后的章节中学习到。
语法：
- SQLite 的带有 WHERE 子句的 SELECT 语句的基本语法如下：
  1
  2
  3
  SELECT column1, column2, columnN
  FROM table_name
  WHERE [condition]
- 您还可以使用比较或逻辑运算符指定条件，比如 >、<、=、LIKE、NOT，等等

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

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

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

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

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

sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

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

sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

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

sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0

下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 EXISTS 运算符一起使用，列出了外查询中的 AGE 存在于子查询返回的结果中的所有记录：
1
2
3
4
5
6
7
8
9
10
11
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24

下面的 SELECT 语句使用 SQL 子查询，子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 > 运算符一起使用，列出了外查询中的 AGE 大于子查询返回的结果中的年龄的所有记录：

sqlite> SELECT * FROM COMPANY 
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0

SQLite AND/OR 运算符

SQLite 的 AND 和 OR 运算符用于编译多个条件来缩小在 SQLite 语句中所选的数据。这两个运算符被称为连接运算符。
这些运算符为同一个 SQLite 语句中不同的运算符之间的多个比较提供了可能。
AND 运算符
- AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。使用 AND 运算符时，只有当所有条件都为真（true）时，整个条件为真（true）。
- 例如，只有当 condition1 和 condition2 都为真（true）时，[condition1] AND [condition2] 为真（true）。
语法
- 带有 WHERE 子句的 AND 运算符的基本语法如下：
  1
  2
  3
  SELECT column1, column2, columnN
  FROM table_name
  WHERE [condition1] AND [condition2]...AND [conditionN];
- 您可以使用 AND 运算符来结合 N 个数量的条件。SQLite 语句需要执行的动作是，无论是事务或查询，所有由 AND 分隔的条件都必须为真（TRUE）。
OR 运算符
- OR 运算符也用于结合一个 SQL 语句的 WHERE 子句中的多个条件。使用 OR 运算符时，只要当条件中任何一个为真（true）时，整个条件为真（true）。
- 例如，只要当 condition1 或 condition2 有一个为真（true）时，[condition1] OR [condition2] 为真（true）。
语法
- 带有 WHERE 子句的 OR 运算符的基本语法如下：
  1
  2
  3
  SELECT column1, column2, columnN
  FROM table_name
  WHERE [condition1] OR [condition2]...OR [conditionN]
- 您可以使用 OR 运算符来结合 N 个数量的条件。SQLite 语句需要执行的动作是，无论是事务或查询，只要任何一个由 OR 分隔的条件为真（TRUE）即可。

SQLite Update 语句

SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。
语法
- 带有 WHERE 子句的 UPDATE 查询的基本语法如下：
  1
  2
  3
  UPDATE table_name
  SET column1 = value1, column2 = value2...., columnN = valueN
  WHERE [condition];
- 您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。
如果您想修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值，则不需要使用 WHERE 子句，UPDATE 查询如下：
- sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

SQLite Delete 语句

SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
语法
- 带有 WHERE 子句的 DELETE 查询的基本语法如下：
  1
  2
  DELETE FROM table_name
  WHERE [condition];
- 您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。

SQLite Like 子句

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

语法

% 和 _ 的基本语法如下：

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX%'

or 

SELECT column_list 
FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT column_list 
FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT column_list 
FROM table_name
WHERE column LIKE '_XXXX_'

您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXXX 可以是任何数字或字符串值。
实例
- 下面一些实例演示了带有 ‘%’ 和 ‘_’ 运算符的 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 结尾的任意值
下面是一个实例，它显示 COMPANY 表中 AGE 以 2 开头的所有记录：
- sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';
下面是一个实例，它显示 COMPANY 表中 ADDRESS 文本里包含一个连字符（-）的所有记录：
- sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';

SQLite Glob 子句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。
- * ：匹配零个、一个或多个数字或字符。
- ? ：代表一个单一的数字或字符。
- [...] ：匹配方括号内指定的字符之一。例如，[abc] 匹配 “a”、”b” 或 “c” 中的任何一个字符。
- [^...] ：匹配不在方括号内指定的字符之一。例如，[^abc] 匹配不是 “a”、”b” 或 “c” 中的任何一个字符的字符。
以上这些符号可以被组合使用

语法

和 ? 的基本语法如下：

SELECT FROM table_name
WHERE column GLOB 'XXXX*'

or 

SELECT FROM table_name
WHERE column GLOB '*XXXX*'

or

SELECT FROM table_name
WHERE column GLOB 'XXXX?'

or

SELECT FROM table_name
WHERE column GLOB '?XXXX'

or

SELECT FROM table_name
WHERE column GLOB '?XXXX?'

or

SELECT FROM table_name
WHERE column GLOB '????'

您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。在这里，XXXX 可以是任何数字或字符串值。
实例
- 下面一些实例演示了带有 ‘*’ 和 ‘?’ 运算符的 GLOB 子句不同的地方：
  - WHERE SALARY GLOB '200*' 查找以 200 开头的任意值
  - WHERE SALARY GLOB '*200*' 查找任意位置包含 200 的任意值
  - WHERE SALARY GLOB '?00*' 查找第二位和第三位为 00 的任意值
  - WHERE SALARY GLOB '2??' 查找以 2 开头，且长度为 3 个字符的任意值，例如，它可能匹配 “200”、”2A1”、”2B2” 等值。
  - WHERE SALARY GLOB '*2' 查找以 2 结尾的任意值
  - WHERE SALARY GLOB '?2*3' 查找第二位为 2，且以 3 结尾的任意值
  - WHERE SALARY GLOB '2???3' 查找长度为 5 位数，且以 2 开头以 3 结尾的任意值
[...] 通配符
- [...] 表达式用于匹配方括号内指定的字符集中的任何一个字符。
实例 1：匹配以 “A” 或 “B” 开头的产品名称。
- SELECT * FROM products WHERE product_name LIKE '[AB]%';
这将匹配以 “A” 或 “B” 开头的产品名称。
实例 2：匹配以 “1”、”2” 或 “3” 开头的电话号码。
- SELECT * FROM customers WHERE phone_number LIKE '[123]%';
这将匹配以 “1”、”2” 或 “3” 开头的电话号码。
[^...] 通配符
- [^...] 表达式用于匹配不在方括号内指定的字符集中的任何字符。
实例 1：匹配不以 “X” 或 “Y” 开头的产品代码。
- SELECT * FROM products WHERE product_code LIKE '[^XY]%';
这将匹配不以 “X” 或 “Y” 开头的产品
实例 2：匹配不包含数字字符的用户名。
- SELECT * FROM users WHERE username LIKE '[^0-9]%';
这将匹配不以数字字符开头的用户名。

SQLite Limit 子句

SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。
语法
- 带有 LIMIT 子句的 SELECT 语句的基本语法如下：
  1
  2
  3
  SELECT column1, column2, columnN
  FROM table_name
  LIMIT [no of rows]
- 下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：
  1
  2
  3
  SELECT column1, column2, columnN
  FROM table_name
  LIMIT [no of rows] OFFSET [row num]
- SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行，如下面的最后一个实例所示
但是，在某些情况下，可能需要从一个特定的偏移开始提取记录。下面是一个实例，从第三位开始提取 3 个记录：
- sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

SQLite Order By

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

语法

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

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

ASC 默认值，从小到大，升序排列
DESC 从大到小，降序排列

您可以在 ORDER BY 子句中使用多个列，确保您使用的排序列在列清单中：

SELECT
   select_list
FROM
   table
ORDER BY
    column_1 ASC,
    column_2 DESC;

column_1 与 column_2 如果后面不指定排序规则，默认为 ASC 升序，以上语句按 column_1 升序，column_2 降序读取，等价如下语句：
1
2
3
4
5
6
7
SELECT
select_list
FROM
table
ORDER BY
column_1,
column_2 DESC;
下面是一个实例，它会将结果按 NAME 和 SALARY 升序排序：
- sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

SQLite Group By

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。
在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前。
语法
- 下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
  1
  2
  3
  4
  5
  SELECT column-list
  FROM table_name
  WHERE [ conditions ]
  GROUP BY column1, column2....columnN
  ORDER BY column1, column2....columnN
- 您可以在 GROUP BY 子句中使用多个列。确保您使用的分组列在列清单中。

SQLite Having 子句

HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。
WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。
语法
- 下面是 HAVING 子句在 SELECT 查询中的位置：
  1
  2
  3
  4
  5
  6
  SELECT
  FROM
  WHERE
  GROUP BY
  HAVING
  ORDER BY
- 在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法：
  1
  2
  3
  4
  5
  6
  SELECT column1, column2
  FROM table1, table2
  WHERE [ conditions ]
  GROUP BY column1, column2
  HAVING [ conditions ]
  ORDER BY column1, column2
下面是一个实例，它将显示名称计数大于 2 的所有记录：
- sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

SQLite Distinct 关键字

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

语法

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

1
2
3

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

现在，让我们在上述的 SELECT 查询中使用 DISTINCT 关键字：
- sqlite> SELECT DISTINCT name FROM COMPANY;

SQLite 约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。
以下是在 SQLite 中常用的约束
- NOT NULL 约束：确保某列不能有 NULL 值。
- DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
- UNIQUE 约束：确保某列中的所有值是不同的。
- PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
- CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。
NOT NULL 约束
- 默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。
- NULL 与没有数据是不一样的，它代表着未知的数据。

实例

例如，下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列，其中 ID、NAME 和 AGE 三列指定不接受 NULL 值：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

DEFAULT 约束
- DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值

实例

例如，下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列。在这里，SALARY 列默认设置为 5000.00。所以当 INSERT INTO 语句没有为该列提供值时，该列将被设置为 5000.00。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

UNIQUE 约束
- UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。

实例

例如，下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列。在这里，AGE 列设置为 UNIQUE，所以不能有两个相同年龄的记录：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

PRIMARY KEY 约束
- PRIMARY KEY 约束唯一标识数据库表中的每个记录。在一个表中可以有多个 UNIQUE 列，但只能有一个主键。在设计数据库表时，主键是很重要的。主键是唯一的 ID。
- 我们使用主键来引用表中的行。可通过把主键设置为其他表的外键，来创建表之间的关系。由于”长期存在编码监督”，在 SQLite 中，主键可以是 NULL，这是与其他数据库不同的地方。
- 主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值。主键列不能有 NULL 值。
- 一个表只能有一个主键，它可以由一个或多个字段组成。当多个字段作为主键，它们被称为复合键。
- 如果一个表在任何字段上定义了一个主键，那么在这些字段上不能有两个记录具有相同的值。

实例

已经看到了我们创建以 ID 作为主键的 COMAPNY 表的各种实例：

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

CHECK 约束
- CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表

实例

例如，下面的 SQLite 创建一个新的表 COMPANY，并增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

删除约束
- SQLite 支持 ALTER TABLE 的有限子集。在 SQLite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。

SQLite Join

SQLite 的 Join 子句用于结合两个或多个数据库中表的记录。JOIN 是一种通过共同值来结合两个表中字段的手段。
SQL 定义了三种主要类型的连接：
- 交叉连接 - CROSS JOIN
- 内连接 - INNER JOIN
- 外连接 - OUTER JOIN
交叉连接 - CROSS JOIN
- 交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。
- 交叉连接的操作，它们都返回被连接的两个表所有数据行的笛卡尔积，返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
- 下面是交叉连接（CROSS JOIN）的语法：
  - SELECT ... FROM table1 CROSS JOIN table2 ...
- 基于上面的表，我们可以写一个交叉连接（CROSS JOIN），如下所示：
  - sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
内连接 - INNER JOIN
- 内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。
- 内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。
- 下面是内连接（INNER JOIN）的语法：
  - SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
- 为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明内连接（INNER JOIN）条件。这个表达式指定一个或多个列的列表：
  - SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
- 自然连接（NATURAL JOIN）类似于 JOIN…USING，只是它会自动测试存在两个表中的每一列的值之间相等值：
  - SELECT ... FROM table1 NATURAL JOIN table2...
- 基于上面的表，我们可以写一个内连接（INNER JOIN），如下所示：
  1
  2
  sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
  ON COMPANY.ID = DEPARTMENT.EMP_ID;
外连接 - OUTER JOIN
- 外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持左外连接（LEFT OUTER JOIN）。
- 外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来，外连接的列使用 NULL 值，将它们附加到结果表中。
- 下面是左外连接（LEFT OUTER JOIN）的语法：
  - SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
- 为了避免冗余，并保持较短的措辞，可以使用 USING 表达式声明外连接（OUTER JOIN）条件。这个表达式指定一个或多个列的列表：
  - SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
- 基于上面的表，我们可以写一个外连接（OUTER JOIN），如下所示：
  1
  2
  sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
  ON COMPANY.ID = DEPARTMENT.EMP_ID;

SQLite Unions 子句

SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序，但它们不必具有相同的长度

语法

UNION 的基本语法如下：

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

这里给定的条件根据需要可以是任何表达式。

UNION ALL 子句
- UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
- 适用于 UNION 的规则同样适用于 UNION ALL 运算符。
- UNION ALL 的基本语法如下：
  1
  2
  3
  4
  5
  6
  7
  8
  9
  SELECT column1 [, column2 ]
  FROM table1 [, table2 ]
  [WHERE condition]
  
  UNION ALL
  
  SELECT column1 [, column2 ]
  FROM table1 [, table2 ]
  [WHERE condition]
- 这里给定的条件根据需要可以是任何表达式。

SQLite NULL 值

SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。
带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。
语法
- 创建表时使用 NULL 的基本语法如下：
  1
  2
  3
  4
  5
  6
  7
  SQLite> CREATE TABLE COMPANY(
  ID INT PRIMARY KEY NOT NULL,
  NAME TEXT NOT NULL,
  AGE INT NOT NULL,
  ADDRESS CHAR(50),
  SALARY REAL
  );
- 在这里，NOT NULL 表示列总是接受给定数据类型的显式值。这里有两个列我们没有使用 NOT NULL，这意味着这两个列可以为 NULL。
- 带有 NULL 值的字段在记录创建的时候可以保留为空。
NULL 值在选择数据时会引起问题，因为当把一个未知的值与另一个值进行比较时，结果总是未知的，且不会包含在最后的结果中。
实例
- 让我们使用 UPDATE 语句来设置一些允许空值的值为 NULL，如下所示：
  - sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
- 接下来，让我们看看 IS NOT NULL 运算符的用法，它用来列出所有 SALARY 不为 NULL 的记录：
  1
  2
  3
  sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
  FROM COMPANY
  WHERE SALARY IS NOT NULL;

SQLite 别名

您可以暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 SQLite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。
列别名用来为某个特定的 SQLite 语句重命名表中的列。

语法

表别名的基本语法如下：

1
2
3

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

列别名的基本语法如下：

1
2
3

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

SQLite 触发器(Trigger)

SQLite 触发器（Trigger）是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。以下是关于 SQLite 的触发器（Trigger）的要点：
- SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
- SQLite 只支持 FOR EACH ROW 触发器（Trigger），没有 FOR EACH STATEMENT 触发器（Trigger）。因此，明确指定 FOR EACH ROW 是可选的。
- WHEN 子句和触发器（Trigger）动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。
- 如果提供 WHEN 子句，则只针对 WHEN 子句为真的指定行执行 SQL 语句。如果没有提供 WHEN 子句，则针对所有行执行 SQL 语句。
- BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
- 当触发器相关联的表删除时，自动删除触发器（Trigger）。
- 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。
- 一个特殊的 SQL 函数 RAISE() 可用于触发器程序内抛出异常。
语法
- 创建触发器（Trigger）的基本语法如下：
  1
  2
  3
  4
  5
  CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
  ON table_name
  BEGIN
  -- 触发器逻辑....
  END;
- 在这里，event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。您可以在表名后选择指定 FOR EACH ROW。
- 以下是在 UPDATE 操作上在表的一个或多个指定列上创建触发器（Trigger）的语法：
  1
  2
  3
  4
  5
  CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
  ON table_name
  BEGIN
  -- 触发器逻辑....
  END;
列出触发器（TRIGGERS）
- 您可以从 sqlite_master 表中列出所有触发器，如下所示：
  1
  2
  sqlite> SELECT name FROM sqlite_master
  WHERE type = 'trigger';
删除触发器（TRIGGERS）
- 下面是 DROP 命令，可用于删除已有的触发器：
  1
  sqlite> DROP TRIGGER trigger_name;

SQLite 索引(Index)

索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。
使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
CREATE INDEX 命令
- CREATE INDEX 的基本语法如下：
- CREATE INDEX index_name ON table_name;
单列索引
- 单列索引是一个只基于表的一个列上创建的索引。基本语法如下：
  1
  2
  CREATE INDEX index_name
  ON table_name (column_name);
唯一索引
- 使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。基本语法如下：
  1
  2
  CREATE UNIQUE INDEX index_name
  on table_name (column_name);
组合索引
- 组合索引是基于一个表的两个或多个列上创建的索引。基本语法如下：
  1
  2
  CREATE INDEX index_name
  on table_name (column1, column2);
是否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。
如果只使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。
隐式索引
- 隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。
DROP INDEX 命令
- 一个索引可以使用 SQLite 的 DROP 命令删除。当删除索引时应特别注意，因为性能可能会下降或提高。
- 基本语法如下：
  - DROP INDEX index_name;
什么情况下要避免使用索引？
- 虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则：
  - 索引不应该使用在较小的表上。
  - 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
  - 索引不应该使用在含有大量的 NULL 值的列上。
  - 索引不应该使用在频繁操作的列上。

SQLite Indexed By

“INDEXED BY index-name” 子句规定必须需要命名的索引来查找前面表中值。
如果索引名 index-name 不存在或不能用于查询，然后 SQLite 语句的准备失败。
“NOT INDEXED” 子句规定当访问前面的表（包括由 UNIQUE 和 PRIMARY KEY 约束创建的隐式索引）时，没有使用索引。
然而，即使指定了 “NOT INDEXED”，INTEGER PRIMARY KEY 仍然可以被用于查找条目。
语法
- 下面是 INDEXED BY 子句的语法，它可以与 DELETE、UPDATE 或 SELECT 语句一起使用：
  1
  2
  3
  4
  SELECT|DELETE|UPDATE column1, column2...
  INDEXED BY (index_name)
  table_name
  WHERE (CONDITION);

SQLite Alter 命令

SQLite 的 ALTER TABLE 命令不通过执行一个完整的转储和数据的重载来修改已有的表。您可以使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。
在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。
语法
- 用来重命名已有的表的 ALTER TABLE 的基本语法如下：
  1
  ALTER TABLE database_name.table_name RENAME TO new_table_name;
- 用来在已有的表中添加一个新的列的 ALTER TABLE 的基本语法如下：
  1
  ALTER TABLE database_name.table_name ADD COLUMN column_def...;

SQLite Truncate Table

在 SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据。
语法
- DELETE 命令的基本语法如下：
  - sqlite> DELETE FROM table_name;
- 但这种方法无法将递增数归零。
- 如果要将递增数归零，可以使用以下方法：
  - sqlite> DELETE FROM sqlite_sequence WHERE name = 'table_name';
- 当 SQLite 数据库中包含自增列时，会自动建立一个名为 sqlite_sequence 的表。这个表包含两个列：name 和 seq。name 记录自增列所在的表，seq 记录当前序号（下一条记录的编号就是当前序号加 1）。如果想把某个自增列的序号归零，只需要修改 sqlite_sequence 表就可以了。

SQLite 视图(View)

视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。视图（View）实际上是一个以预定义的 SQLite 查询形式存在的表的组合。
视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。
视图（View）是一种虚表，允许用户实现以下几点：
- 用户或用户组查找结构数据的方式更自然或直观。
- 限制数据访问，用户只能看到有限的数据，而不是完整的表。
- 汇总各种表中的数据，用于生成报告。
SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

SQLite 事务(Transaction)

事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成。
事务（Transaction）是指一个或多个更改数据库的扩展。例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误。
实际上，您可以把许多的 SQLite 查询联合成一组，把所有这些放在一起作为事务的一部分进行执行。
事务的属性
- 事务（Transaction）具有以下四个标准属性，通常根据首字母缩写为 ACID：
  - 原子性（Atomicity）：确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
  - 一致性（Consistency）：确保数据库在成功提交的事务上正确地改变状态。
  - 隔离性（Isolation）：使事务操作相互独立和透明。
  - 持久性（Durability）：确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。
事务控制
- 使用下面的命令来控制事务：
  - BEGIN TRANSACTION：开始事务处理。
  - COMMIT：保存更改，或者可以使用 END TRANSACTION 命令。
  - ROLLBACK：回滚所做的更改。
- 事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。
BEGIN TRANSACTION 命令
- 事务（Transaction）可以使用 BEGIN TRANSACTION 命令或简单的 BEGIN 命令来启动。此类事务通常会持续执行下去，直到遇到下一个 COMMIT 或 ROLLBACK 命令。不过在数据库关闭或发生错误时，事务处理也会回滚。以下是启动一个事务的简单语法：
  1
  2
  3
  4
  5
  BEGIN;
  
  or
  
  BEGIN TRANSACTION;
COMMIT 命令
- COMMIT 命令是用于把事务调用的更改保存到数据库中的事务命令。
- COMMIT 命令把自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库。
- COMMIT 命令的语法如下：
  1
  2
  3
  4
  5
  COMMIT;
  
  or
  
  END TRANSACTION;
ROLLBACK 命令
- ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务命令。
- ROLLBACK 命令只能用于撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。
- ROLLBACK 命令的语法如下：
  1
  ROLLBACK;

SQLite 子查询

子查询或称为内部查询、嵌套查询，指的是在 SQLite 查询中的 WHERE 子句中嵌入查询语句。
一个 SELECT 语句的查询结果能够作为另一个语句的输入值。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则：
- 子查询必须用括号括起来。
- 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
- ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
- 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
- BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

SELECT 语句中的子查询使用

子查询通常与 SELECT 语句一起使用。基本语法如下：

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

INSERT 语句中的子查询使用
- 子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。在子查询中所选择的数据可以用任何字符、日期或数字函数修改。
- 基本语法如下：
  1
  2
  3
  4
  INSERT INTO table_name [ (column1 [, column2 ]) ]
  SELECT [ *|column1 [, column2 ]
  FROM table1 [, table2 ]
  [ WHERE VALUE OPERATOR ]
UPDATE 语句中的子查询使用
- 子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新
- 基本语法如下：
  1
  2
  3
  4
  5
  6
  UPDATE table
  SET column_name = new_value
  [ WHERE OPERATOR [ VALUE ]
  (SELECT COLUMN_NAME
  FROM TABLE_NAME)
  [ WHERE) ]
DELETE 语句中的子查询使用
- 子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。
- 基本语法如下：
  1
  2
  3
  4
  5
  DELETE FROM TABLE_NAME
  [ WHERE OPERATOR [ VALUE ]
  (SELECT COLUMN_NAME
  FROM TABLE_NAME)
  [ WHERE) ]

SQLite Autoincrement (自动递增)

SQLite 的 AUTOINCREMENT 是一个关键字，用于表中的字段值自动递增。我们可以在创建表时在特定的列名称上使用 AUTOINCREMENT 关键字实现该字段值的自动增加。
关键字 AUTOINCREMENT 只能用于整型（INTEGER）字段。

语法

AUTOINCREMENT 关键字的基本用法如下：

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

实例

假设要创建的 COMPANY 表如下所示：

sqlite> CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

SQLite Explain (解释)

在 SQLite 语句之前，可以使用 “EXPLAIN” 关键字或 “EXPLAIN QUERY PLAN” 短语，用于描述表的细节。
如果省略了 EXPLAIN 关键字或短语，任何的修改都会引起 SQLite 语句的查询行为，并返回有关 SQLite 语句如何操作的信息。
- 来自 EXPLAIN 和 EXPLAIN QUERY PLAN 的输出只用于交互式分析和排除故障。
- 输出格式的细节可能会随着 SQLite 版本的不同而有所变化。
- 应用程序不应该使用 EXPLAIN 或 EXPLAIN QUERY PLAN，因为其确切的行为是可变的且只有部分会被记录。
语法
- EXPLAIN 的语法如下：
  - EXPLAIN [SQLite Query]
- EXPLAIN QUERY PLAN 的语法如下：
  - EXPLAIN QUERY PLAN [SQLite Query]

SQLite Vacuum

VACUUM 命令通过复制主数据库中的内容到一个临时数据库文件，然后清空主数据库，并从副本中重新载入原始的数据库文件。这消除了空闲页，把表中的数据排列为连续的，另外会清理数据库文件结构。
如果表中没有明确的整型主键（INTEGER PRIMARY KEY），VACUUM 命令可能会改变表中条目的行 ID（ROWID）。VACUUM 命令只适用于主数据库，附加的数据库文件是不可能使用 VACUUM 命令。
如果有一个活动的事务，VACUUM 命令就会失败。VACUUM 命令是一个用于内存数据库的任何操作。由于 VACUUM 命令从头开始重新创建数据库文件，所以 VACUUM 也可以用于修改许多数据库特定的配置参数。

SQLite 日期 & 时间

SQLite 支持以下五个日期和时间函数：
date(timestring, modifier, modifier, ...) 以 YYYY-MM-DD 格式返回日期。
time(timestring, modifier, modifier, ...) 以 HH:MM:SS 格式返回时间。
datetime(timestring, modifier, modifier, ...) 以 YYYY-MM-DD HH:MM:SS 格式返回。
julianday(timestring, modifier, modifier, ...) 这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。
strftime(format, timestring, modifier, modifier, ...) 这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。
上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。strftime() 函数也可以把格式字符串 format 作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符。
时间字符串
- 一个时间字符串可以采用下面任何一种格式：
  - YYYY-MM-DD 2010-12-30
  - YYYY-MM-DD HH:MM 2010-12-30 12:10
  - YYYY-MM-DD HH:MM:SS.SSS 2010-12-30 12:10:04.100
  - MM-DD-YYYY HH:MM 12-30-2010 12:10
  - HH:MM 12:10
  - YYYY-MM-DDTHH:MM 2010-12-30 12:10
  - HH:MM:SS 12:10:01
  - YYYYMMDD HHMMSS 20101230 121001
  - now 2013-05-07
- 您可以使用 “T” 作为分隔日期和时间的文字字符。
修饰符（Modifier）
- 时间字符串后边可跟着零个或多个的修饰符，这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用，下面列出了可在 SQLite 中使用的修饰符：
  - 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
格式化
- SQLite 提供了非常方便的函数 strftime() 来格式化任何日期和时间。您可以使用以下的替换来格式化日期和时间：
- %d 一月中的第几天，01-31
- %f 带小数部分的秒，SS.SSS
- %H 小时，00-23
- %j 一年中的第几天，001-366
- %J 儒略日数，DDDD.DDDD
- %m 月，00-12
- %M 分，00-59
- %s 从 1970-01-01 算起的秒数
- %S 秒，00-59
- %w 一周中的第几天，0-6 (0 is Sunday)
- %W 一年中的第几周，01-53
- %Y 年，YYYY
- %% % symbol
实例
- 现在让我们使用 SQLite 提示符尝试不同的实例。下面是计算当前日期：
  1
  2
  sqlite> SELECT date('now');
  2013-05-07
- 下面是计算当前月份的最后一天：
  1
  2
  sqlite> SELECT date('now','start of month','+1 month','-1 day');
  2013-05-31
- 下面是计算给定 UNIX 时间戳 1092941466 的日期和时间：
  1
  2
  sqlite> SELECT datetime(1092941466, 'unixepoch');
  2004-08-19 18:51:06
- 下面是计算给定 UNIX 时间戳 1092941466 相对本地时区的日期和时间：
  1
  2
  sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
  2004-08-19 11:51:06
- 下面是计算当前的 UNIX 时间戳：
  1
  2
  sqlite> SELECT strftime('%s','now');
  1367926057
- 下面是计算美国”独立宣言”签署以来的天数：
  1
  2
  sqlite> SELECT julianday('now') - julianday('1776-07-04');
  86504.4775830326
- 下面是计算从 2004 年某一特定时刻以来的秒数：
  1
  2
  sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
  295001572
- 下面是计算当年 10 月的第一个星期二的日期：
  1
  2
  sqlite> SELECT date('now','start of year','+9 months','weekday 2');
  2013-10-01
- 下面是计算从 UNIX 纪元算起的以秒为单位的时间（类似 strftime(‘%s’,’now’) ，不同的是这里有包括小数部分）：
  1
  2
  sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
  1367926077.12598
- 在 UTC 与本地时间值之间进行转换，当格式化日期时，使用 utc 或 localtime 修饰符，如下所示：
  1
  2
  3
  4
  sqlite> SELECT time('12:00', 'localtime');
  05:00:00
  sqlite> SELECT time('12:00', 'utc');
  19:00:00

SQLite 常用函数

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情，请查看 SQLite 的官方文档：
SQLite COUNT 函数
- SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
SQLite MAX 函数
- SQLite MAX 聚合函数允许我们选择某列的最大值。
SQLite MIN 函数
- SQLite MIN 聚合函数允许我们选择某列的最小值。
SQLite AVG 函数
- SQLite AVG 聚合函数计算某列的平均值。
SQLite SUM 函数
- SQLite SUM 聚合函数允许为一个数值列计算总和。
SQLite RANDOM 函数
- SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
SQLite ABS 函数
- SQLite ABS 函数返回数值参数的绝对值。
SQLite UPPER 函数
- SQLite UPPER 函数把字符串转换为大写字母。
SQLite LOWER 函数
- SQLite LOWER 函数把字符串转换为小写字母。
SQLite LENGTH 函数
- SQLite LENGTH 函数返回字符串的长度。
SQLite sqlite_version 函数
- SQLite sqlite_version 函数返回 SQLite 库的版本。
实例
- SQLite COUNT 函数
- SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
  1
  sqlite> SELECT count(*) FROM COMPANY;
- SQLite MAX 函数
- SQLite MAX 聚合函数允许我们选择某列的最大值。
  1
  sqlite> SELECT max(salary) FROM COMPANY;
- SQLite MIN 函数
- SQLite MIN 聚合函数允许我们选择某列的最小值。
  1
  sqlite> SELECT min(salary) FROM COMPANY;
- SQLite AVG 函数
- SQLite AVG 聚合函数计算某列的平均值。
  1
  sqlite> SELECT avg(salary) FROM COMPANY;
- SQLite SUM 函数
- SQLite SUM 聚合函数允许为一个数值列计算总和。
  1
  sqlite> SELECT sum(salary) FROM COMPANY;
- SQLite RANDOM 函数
- SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
  1
  sqlite> SELECT random() AS Random;
- SQLite ABS 函数
- SQLite ABS 函数返回数值参数的绝对值。
  1
  sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
- SQLite UPPER 函数
- SQLite UPPER 函数把字符串转换为大写字母。
  1
  sqlite> SELECT upper(name) FROM COMPANY;
- SQLite LOWER 函数
- SQLite LOWER 函数把字符串转换为小写字母。
  1
  sqlite> SELECT lower(name) FROM COMPANY;
- SQLite LENGTH 函数
- SQLite LENGTH 函数返回字符串的长度。
  1
  sqlite> SELECT name, length(name) FROM COMPANY;
- SQLite sqlite_version 函数
- SQLite sqlite_version 函数返回 SQLite 库的版本。
  1
  sqlite> SELECT sqlite_version() AS 'SQLite Version';

简介