sqlite学习笔记2
内连接 - 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 值。 实例 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 语句没有提供一个特定的值时，为列提供一个默认值。 CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL DEFAULT 50000.00 ); UNIQUE 约束UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。 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 值。 实例 CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL ); CHECK 约束CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。 实例 CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL,SALARY REAL CHECK(SALARY > 0) ); SQLite NULL 值SQLite 的 NULL 是用来表示一个缺失值的项。表中的一个 NULL 值是在字段中显示为空白的一个值。 SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL ); 在这里，NOT NULL 表示列总是接受给定数据类型的显式值。这里有两个列我们没有使用 NOT 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 语句中重命名表。重命名是临时的改变，在数据库中实际的表的名称不会改变。 列别名用来为某个特定的 SQLite 语句重命名表中的列。 语法 SELECT column1,column2.... FROM table_name AS alias_name WHERE [condition]; 列 别名的基本语法如下： SELECT column_name AS alias_name FROM table_name WHERE [condition]; 实例 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 语句还可以在已有的表中添加额外的列。 实例 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
SELECT 语句中的子查询使用子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
实例 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
实例
假设 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 语句使用子查询时，表中单个或多个列被更新。
实例
假设，我们有 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 语句结合使用，就像上面提到的其他语句一样。 实例 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; 上面的 SQLite SQL 语句将产生以下结果： count(*) ---------- 7 SQLite MAX 函数SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例： sqlite> SELECT max(salary) FROM COMPANY; 上面的 SQLite SQL 语句将产生以下结果： max(salary) ----------- 85000.0 SQLite MIN 函数SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例： sqlite> SELECT min(salary) FROM COMPANY; 上面的 SQLite SQL 语句将产生以下结果： min(salary) ----------- 10000.0 SQLite AVG 函数SQLite AVG 聚合函数计算某列的平均值。下面是实例： sqlite> SELECT avg(salary) FROM COMPANY; 上面的 SQLite SQL 语句将产生以下结果： avg(salary) ---------------- 37142.8571428572 SQLite SUM 函数SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例： sqlite> SELECT sum(salary) FROM COMPANY; 上面的 SQLite SQL 语句将产生以下结果： sum(salary) ----------- 260000.0 SQLite RANDOM 函数SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例： sqlite> SELECT random() AS Random; 上面的 SQLite SQL 语句将产生以下结果： Random ------------------- 5876796417670984050 SQLite ABS 函数SQLite ABS 函数返回数值参数的绝对值。下面是实例： sqlite> SELECT abs(5),abs(-15),abs(NULL),abs(0),abs("ABC");
上面的 SQLite SQL 语句将产生以下结果： 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; 上面的 SQLite SQL 语句将产生以下结果： upper(name) ----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES SQLite LOWER 函数SQLite LOWER 函数把字符串转换为小写字母。下面是实例： sqlite> SELECT lower(name) FROM COMPANY; 上面的 SQLite SQL 语句将产生以下结果： lower(name) ----------- paul allen teddy mark david kim james SQLite LENGTH 函数SQLite LENGTH 函数返回字符串的长度。下面是实例： sqlite> SELECT name,length(name) FROM COMPANY; 上面的 SQLite SQL 语句将产生以下结果： 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 SQL 语句将产生以下结果： SQLite Version -------------- 3.6.20 更多详细内容参考：http://www.runoob.com/sqlite/sqlite-operators.html （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |