内连接 - INNER JOIN
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。 查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。 当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。 内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。INNER 关键字是可选的。 表COMPANY ： ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 表DEPARTMENT ： ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineerin 2 3 Finance 7 demo: sqlite> SELECT EMP_ID,NAME,DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; 结果如下： EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin 7 James Finance
外连接 - OUTER JOIN
外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 sql 标准定义了三种类型的外连接：LEFT、RIGHT、FULL， 但 sqlite 只支持 左外连接（LEFT OUTER JOIN）。 外连接（OUTER JOIN）声明条件的方法与内连接（INNER JOIN）是相同的，使用 ON、USING 或 NATURAL 关键字来表达。 最初的结果表以相同的方式进行计算。一旦主连接计算完成，外连接（OUTER JOIN）将从一个或两个表中任何未连接的行合并进来， 外连接的列使用 NULL 值，将它们附加到结果表中。 demo: sqlite> SELECT EMP_ID,DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; 结果： EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineerin Teddy Mark David Kim 7 James Finance
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,SALARY REAL DEFAULT 50000.00 );
UNIQUE 约束
UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。
实例
例如，下面的 sqlite 语句创建一个新的表 COMPANY，并增加了列。在这里，AGE 列设置为 UNIQUE，所以不能有两个相同年龄的记录：
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,AGE INT NOT NULL UNIQUE,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,SALARY REAL );
CHECK 约束
CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。
实例
例如，下面的 sqlite 创建一个新的表 COMPANY，并增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL,SALARY REAL CHECK(SALARY > 0) );
sqlite NULL 值
sqlite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。
带有 NULL 值的字段是一个不带有值的字段。NULL 值与零值或包含空格的字段是不同的，理解这点是非常重要的。
语法
创建表时使用 NULL 的基本语法如下：
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL );
在这里，NOT NULL 表示列总是接受给定数据类型的显式值。这里有两个列我们没有使用 NOT NULL，这意味着这两个列不能为 NULL。
带有 NULL 值的字段在记录创建的时候可以保留为空。
实例
NULL 值在选择数据时会引起问题，因为当把一个未知的值与另一个值进行比较时，结果总是未知的，且不会包含在最后的结果中。假设有下面的表，
COMPANY 的记录如下所示：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
让我们使用 UPDATE 语句来设置一些允许空值的值为 NULL，如下所示：
sqlite> UPDATE COMPANY SET ADDRESS = NULL,SALARY = NULL where ID IN(6,7);
现在，COMPANY 表的记录如下所示：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 7 James 24
接下来，让我们看看 IS NOT NULL 运算符的用法，它用来列出所有 SALARY 不为 NULL 的记录：
sqlite> SELECT ID,AGE,ADDRESS,SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
上面的 sqlite 语句将产生下面的结果：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面是 IS NULL 运算符的用法，将列出所有 SALARY 为 NULL 的记录：
sqlite> SELECT ID,SALARY FROM COMPANY WHERE SALARY IS NULL;
上面的 sqlite 语句将产生下面的结果：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24
sqlite 别名
您可以暂时把表或列重命名为另一个名字，这被称为别名。使用表别名是指在一个特定的 sqlite 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。
语法
表 别名的基本语法如下：
SELECT column1,column2.... FROM table_name AS alias_name WHERE [condition];
列 别名的基本语法如下：
SELECT column_name AS alias_name FROM table_name WHERE [condition];
实例
假设有下面两个表，（1）COMPANY 表如下所示：
sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
（2）另一个表是 DEPARTMENT，如下所示：
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
现在，下面是 表别名 的用法，在这里我们使用 C 和 D 分别作为 COMPANY 和 DEPARTMENT 表的别名：
sqlite> SELECT C.ID,C.NAME,C.AGE,D.DEPT FROM COMPANY AS C,DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
上面的 sqlite 语句将产生下面的结果：
ID NAME AGE DEPT ---------- ---------- ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
让我们看一个 列别名 的实例，在这里 COMPANY_ID 是 ID 列的别名，COMPANY_NAME 是 name 列的别名：
sqlite> SELECT C.ID AS COMPANY_ID,C.NAME AS COMPANY_NAME,DEPARTMENT AS D WHERE C.ID = D.EMP_ID;
上面的 sqlite 语句将产生下面的结果：
COMPANY_ID COMPANY_NAME AGE DEPT ---------- ------------ ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineerin 3 Teddy 23 Engineerin 4 Mark 25 Finance 5 David 27 Engineerin 6 Kim 22 Finance 7 James 24 Finance
sqlite Alter 命令
使用 ALTER TABLE 语句重命名表，使用 ALTER TABLE 语句还可以在已有的表中添加额外的列。
在 sqlite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作。
实例
假设我们的 COMPANY 表有如下记录：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们尝试使用 ALTER TABLE 语句重命名该表，如下所示：
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
上面的 sqlite 语句将重命名 COMPANY 表为 OLD_COMPANY。现在，让我们尝试在 OLD_COMPANY 表中添加一个新的列，如下所示：
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
现在，COMPANY 表已经改变，使用 SELECT 语句输出如下：
ID NAME AGE ADDRESS SALARY SEX ---------- ---------- ---------- ---------- ---------- --- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
请注意，新添加的列是以 NULL 值来填充的。
SELECT 语句中的子查询使用
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则：
- 子查询必须用括号括起来。
- 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
- ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
- 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
- BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
实例
假设 COMPANY 表有以下记录：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们检查 SELECT 语句中的子查询使用：
sqlite> SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
这将产生以下结果:
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
INSERT 语句中的子查询使用
子查询也可以与 INSERT 语句一起使用。INSERT 语句使用子查询返回的数据插入到另一个表中。在子查询中所选择的数据可以用任何字符、
日期或数字函数修改。
实例 假设 COMPANY_BKP 的结构与 COMPANY 表相似，且可使用相同的 CREATE TABLE 进行创建，只是表名改为 COMPANY_BKP。 现在把整个 COMPANY 表复制到 COMPANY_BKP，语法如下： sqlite> INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;
UPDATE 语句中的子查询使用
子查询可以与 UPDATE 语句结合使用。当通过 UPDATE 语句使用子查询时，表中单个或多个列被更新。 实例 假设，我们有 COMPANY_BKP 表，是 COMPANY 表的备份。 下面的实例把 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新为原来的 0.50 倍： sqlite> UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );
这将影响两行，最后 COMPANY 表中的记录如下：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 10000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
DELETE 语句中的子查询使用子查询可以与 DELETE 语句结合使用，就像上面提到的其他语句一样。
实例
假设，我们有 COMPANY_BKP 表，是 COMPANY 表的备份。
下面的实例删除 COMPANY 表中所有 AGE 大于或等于 27 的客户记录：
sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
这将影响两行，最后 COMPANY 表中的记录如下：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
sqlite 常用函数
sqlite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 sqlite 内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情，请查看 sqlite 的官方文档：
序号 函数 & 描述
1 sqlite COUNT 函数 sqlite COUNT 聚集函数是用来计算一个数据库表中的行数。 2 sqlite MAX 函数 sqlite MAX 聚合函数允许我们选择某列的最大值。 3 sqlite MIN 函数 sqlite MIN 聚合函数允许我们选择某列的最小值。 4 sqlite AVG 函数 sqlite AVG 聚合函数计算某列的平均值。 5 sqlite SUM 函数 sqlite SUM 聚合函数允许为一个数值列计算总和。 6 sqlite RANDOM 函数 sqlite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。 7 sqlite ABS 函数 sqlite ABS 函数返回数值参数的绝对值。 8 sqlite UPPER 函数 sqlite UPPER 函数把字符串转换为大写字母。 9 sqlite LOWER 函数 sqlite LOWER 函数把字符串转换为小写字母。 10 sqlite LENGTH 函数 sqlite LENGTH 函数返回字符串的长度。 11 sqlite sqlite_version 函数 sqlite sqlite_version 函数返回 sqlite 库的版本。
在我们开始讲解这些函数实例之前，先假设 COMPANY 表有以下记录：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
sqlite COUNT 函数
sqlite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：
sqlite> SELECT count(*) FROM COMPANY;
count(*) ---------- 7
sqlite MAX 函数
sqlite MAX 聚合函数允许我们选择某列的最大值。下面是实例：
sqlite> SELECT max(salary) FROM COMPANY;
max(salary) ----------- 85000.0
sqlite MIN 函数
sqlite MIN 聚合函数允许我们选择某列的最小值。下面是实例：
sqlite> SELECT min(salary) FROM COMPANY;
min(salary) ----------- 10000.0
sqlite AVG 函数
sqlite AVG 聚合函数计算某列的平均值。下面是实例：
sqlite> SELECT avg(salary) FROM COMPANY;
avg(salary) ---------------- 37142.8571428572
sqlite SUM 函数
sqlite SUM 聚合函数允许为一个数值列计算总和。下面是实例：
sqlite> SELECT sum(salary) FROM COMPANY;
sum(salary) ----------- 260000.0
sqlite RANDOM 函数
sqlite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例：
sqlite> SELECT random() AS Random;
Random ------------------- 5876796417670984050
sqlite ABS 函数
sqlite ABS 函数返回数值参数的绝对值。下面是实例：
sqlite> SELECT abs(5),abs(-15),abs(NULL),abs(0),abs("ABC");
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0
sqlite UPPER 函数
sqlite UPPER 函数把字符串转换为大写字母。下面是实例：
sqlite> SELECT upper(name) FROM COMPANY;
upper(name) ----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES
sqlite LOWER 函数
sqlite LOWER 函数把字符串转换为小写字母。下面是实例：
sqlite> SELECT lower(name) FROM COMPANY;
lower(name) ----------- paul allen teddy mark david kim james
sqlite LENGTH 函数
sqlite LENGTH 函数返回字符串的长度。下面是实例：
sqlite> SELECT name,length(name) FROM COMPANY;
NAME length(name) ---------- ------------ Paul 4 Allen 5 Teddy 5 Mark 4 David 5 Kim 3 James 5
sqlite sqlite_version 函数
sqlite sqlite_version 函数返回 sqlite 库的版本。下面是实例：
sqlite> SELECT sqlite_version() AS 'sqlite Version';
sqlite Version -------------- 3.6.20
