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 数据，完全根据它的输入存储。 |
sqlite 的存储类稍微比数据类型更普遍。INTEGER 存储类，例如，包含 6 种不同的不同长度的整数数据类型。
sqlite Affinity 类型
sqlite 支持列上的类型 affinity 概念。任何列仍然可以存储任何类型的数据，但列的首选存储类是它的 affinity。在 sqlite3 数据库中，每个表的列分配为以下类型的 affinity 之一：
| Affinity | 描述 |
|---|---|
| TEXT | 该列使用存储类 NULL、TEXT 或 BLOB 存储所有数据。 |
| NUMERIC | 该列可以包含使用所有五个存储类的值。 |
| INTEGER | 与带有 NUMERIC affinity 的列相同，在 CAST 表达式中带有异常。 |
| REAL | 与带有 NUMERIC affinity 的列相似，不同的是，它会强制把整数值转换为浮点表示。 |
| NONE | 带有 affinity NONE 的列，不会优先使用哪个存储类，也不会尝试把数据从一个存储类强制转换为另一个存储类。 |
sqlite Affinity 及类型名称
下表列出了当创建 sqlite3 表时可使用的各种数据类型名称，同时也显示了相应的应用 Affinity：
| 数据类型 | Affinity |
|---|---|
|
INTEGER |
|
TEXT |
|
NONE |
|
REAL |
|
NUMERIC |
Boolean 数据类型
sqlite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）。
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 创建数据库
语法
sqlite3 命令的基本语法如下：
$sqlite3 DatabaseName.db
实例
如果想创建一个新的数据库 <testDB.db>，sqlITE3 语句如下所示：
$sqlite3 testDB.db sqlite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter sql statements terminated with a ";" sqlite>
上面的命令将在当前目录下创建一个文件 testDB.db。该文件将被 sqlite 引擎用作数据库。如果您已经注意到 sqlite3 命令在成功创建数据库文件之后，将提供一个 sqlite> 提示符。
一旦数据库被创建，您就可以使用 sqlite 的 .databases 命令来检查它是否在数据库列表中，如下所示：
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db
可以使用 sqlite .quit 命令退出 sqlite 提示符，如下所示：
sqlite>.quit $
.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 DTABASE 语句是用来选择一个特定的数据库，使用该命令后，所有的 sqlite 语句将在附加的数据库下执行。
语法
sqlite 的 ATTACH DATABASE 语句的基本语法如下：
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
如果数据库尚未被创建，上面的命令将创建一个数据库，如果数据库已存在，则把数据库文件名称与逻辑数据库 'Alias-Name' 绑定在一起。
实例
如果想附加一个现有的数据库 testDB.db，则 ATTACH DATABASE 语句将如下所示：
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
使用 sqlite .database 命令来显示附加的数据库。
sqlite> .database seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db
数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息，如下所示：
sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP'; Error: database TEMP is already in use sqlite> ATTACH DATABASE 'testDB.db' as 'main'; Error: database TEMP is already in use
sqlite 分离数据库
sqlite的 DETACH DTABASE 语句是用来把命名数据库从一个数据库连接分离和游离出来，连接是之前使用 ATTACH 语句附加的。如果同一个数据库文件已经被附加上多个别名，DETACH 命令将只断开给定名称的连接，而其余的仍然有效。您无法分离 main 或 temp 数据库。
如果数据库是在内存中或者是临时数据库，则该数据库将被摧毁，且内容将会丢失。
语法
sqlite 的 DETACH DATABASE 'Alias-Name' 语句的基本语法如下：
DETACH DATABASE 'Alias-Name';
在这里，'Alias-Name' 与您之前使用 ATTACH 语句附加数据库时所用到的别名相同。
实例
假设在前面的章节中您已经创建了一个数据库，并给它附加了 'test' 和 'currentDB'，使用 .database 命令，我们可以看到：
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db 3 currentDB /home/sqlite/testDB.db
现在，让我们尝试把 'currentDB' 从 testDB.db 中分离出来，如下所示：
sqlite> DETACH DATABASE 'currentDB';
现在，如果检查当前附加的数据库，您会发现，testDB.db 仍与 'test' 和 'main' 保持连接。
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db
sqlite 创建表
语法
CREATE TABLE 语句的基本语法如下：
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 命令来验证表是否已成功创建，该命令用于列出附加数据库中的所有表。
sqlite>.tables COMPANY DEPARTMENT
在这里，可以看到 COMPANY 表出现两次，一个是主数据库的 COMPANY 表，一个是为 testDB.db 创建的 'test' 别名的 test.COMPANY 表。您可以使用 sqlite .schema 命令得到表的完整信息，如下所示：
sqlite>.schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL );
sqlite 删除表
语法
DROP TABLE 语句的基本语法如下。您可以选择指定带有表名的数据库名称，如下所示：
DROP TABLE database_name.table_name;
实例
让我们先确认 COMPANY 表已经存在，然后我们将其从数据库中删除。
sqlite>.tables COMPANY test.COMPANY
这意味着 COMPANY 表已存在数据库中，接下来让我们把它从数据库中删除，如下：
sqlite>DROP TABLE COMPANY; sqlite>
现在，如果尝试 .TABLES 命令，那么将无法找到 COMPANY 表了：
sqlite>.tables sqlite>
sqlite Insert 语句
语法
INSERT INTO 语句有两种基本语法，如下所示：
INSERT INTO TABLE_NAME (column1,column2,column3,...columnN)] VALUES (value1,value2,value3,...valueN);
在这里，column1,...columnN 是要插入数据的表中的列的名称。
如果要为表中的所有列添加值，您也可以不需要在 sqlite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。sqlite 的 INSERT INTO 语法如下：
INSERT INTO TABLE_NAME VALUES (value1,...valueN);
实例
假设您已经在 testDB.db 中创建了 COMPANY表，如下所示：
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL );
现在，下面的语句将在 COMPANY 表中创建六个记录：
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,'Paul',32,'California',20000.00 ); INSERT INTO COMPANY (ID,SALARY) VALUES (2,'Allen',25,'Texas',15000.00 ); INSERT INTO COMPANY (ID,SALARY) VALUES (3,'Teddy',23,'Norway',SALARY) VALUES (4,'Mark','Rich-Mond ',65000.00 ); INSERT INTO COMPANY (ID,SALARY) VALUES (5,'David',27,85000.00 ); INSERT INTO COMPANY (ID,SALARY) VALUES (6,'Kim',22,'South-Hall',45000.00 );
您也可以使用第二种语法在 COMPANY 表中创建一个记录，如下所示：
INSERT INTO COMPANY VALUES (7,'James',24,'Houston',10000.00 );
上面的所有语句将在 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 语句，填充数据到另一个表中。下面是语法：
INSERT INTO first_table_name [(column1,... columnN)] SELECT column1,...columnN FROM second_table_name [WHERE condition];
sqlite Select 语句
语法
sqlite 的 SELECT 语句的基本语法如下：
SELECT column1,columnN FROM table_name;
在这里，column1,column2...是表的字段，他们的值即是您要获取的。如果您想获取所有可用的字段，那么可以使用下面的语法：
SELECT * FROM table_name;
实例
假设 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>.header on sqlite>.mode column 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
如果只想获取 COMPANY 表中指定的字段，则使用下面的查询：
sqlite> SELECT ID,SALARY FROM COMPANY;
上面的查询会产生以下结果：
ID NAME SALARY ---------- ---------- ---------- 1 Paul 20000.0 2 Allen 15000.0 3 Teddy 20000.0 4 Mark 65000.0 5 David 85000.0 6 Kim 45000.0 7 James 10000.0
设置输出列的宽度
有时，由于要显示的列的默认宽度导致 .mode column，这种情况下，输出被截断。此时，您可以使用 .width num,num.... 命令设置显示列的宽度，如下所示：
sqlite>.width 10,20,10 sqlite>SELECT * FROM COMPANY;
上面的 .width 命令设置第一列的宽度为 10，第二列的宽度为 20，第三列的宽度为 10。因此上述 SELECT 语句将得到以下结果：
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
Schema 信息
因为所有的点命令只在 sqlite 提示符中可用，所以当您进行带有 sqlite 的编程时，您要使用下面的带有 sqlite_master 表的 SELECT 语句来列出所有在数据库中创建的表：
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
假设在 testDB.db 中已经存在唯一的 COMPANY 表，则将产生以下结果：
tbl_name ---------- COMPANY
您可以列出关于 COMPANY 表的完整信息，如下所示：
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
假设在 testDB.db 中已经存在唯一的 COMPANY 表，则将产生以下结果：
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL,SALARY REAL )
sqlite 运算符
sqlite 运算符是什么？
运算符是一个保留字或字符，主要用于 sqlite 语句的 WHERE 子句中执行操作，如比较和算术运算。
运算符用于指定 sqlite 语句中的条件，并在语句中连接多个条件。
-
算术运算符
-
比较运算符
-
逻辑运算符
-
位运算符
sqlite 算术运算符
假设变量 a=10，变量 b=20，则：
| 运算符 | 描述 | 实例 |
|---|---|---|
| + | 加法 - 把运算符两边的值相加 | a + b 将得到 30 |
| - | 减法 - 左操作数减去右操作数 | a - b 将得到 -10 |
| * | 乘法 - 把运算符两边的值相乘 | a * b 将得到 200 |
| / | 除法 - 左操作数除以右操作数 | b / a 将得到 2 |
| % | 取模 - 左操作数除以右操作数后得到的余数 | b % a will give 0 |
实例
下面是 sqlite 算术运算符的简单实例：
sqlite> .mode line sqlite> select 10 + 20; 10 + 20 = 30 sqlite> select 10 - 20; 10 - 20 = -10 sqlite> select 10 * 20; 10 * 20 = 200 sqlite> select 10 / 5; 10 / 5 = 2 sqlite> select 12 % 5; 12 % 5 = 2
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) 为真。 |
实例
假设 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
在这里，我们使用 WHERE 子句，这将会在后边单独的一个章节中讲解，但现在您需要明白，WHERE 子句是用来设置 SELECT 语句的条件语句。
下面的 SELECT 语句列出了 SALARY 大于 50,000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 SALARY 等于 20,000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE SALARY = 20000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0
下面的 SELECT 语句列出了 SALARY 不等于 20,000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE SALARY != 20000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.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 语句列出了 SALARY 不等于 20,000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.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 语句列出了 SALARY 大于等于 65,000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
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 运算符搜索指定表中的每一行，确保唯一性（无重复）。 |
实例
假设 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 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录，结果显示所有的记录，意味着没有一个记录的 AGE 等于 NULL：
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; 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 语句列出了 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 存在于子查询返回的结果中的所有记录：
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 位运算符
位运算符作用于位，并逐位执行操作。真值表 & 和 | 如下：
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
假设如果 A = 60，且 B = 13，现在以二进制格式，它们如下所示：
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
下表中列出了 sqlite 语言支持的位运算符。假设变量 A=60，变量 B=13，则：
| 运算符 | 描述 | 实例 |
|---|---|---|
| & | 如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。 | (A & B) 将得到 12，即为 0000 1100 |
| | | 如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。 | (A | B) 将得到 61，即为 0011 1101 |
| ~ | 二进制补码运算符是一元运算符，具有"翻转"位效应。 | (~A ) 将得到 -61，即为 1100 0011，2 的补码形式，带符号的二进制数。 |
| << | 二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 | A << 2 将得到 240，即为 1111 0000 |
| >> | 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 | A >> 2 将得到 15，即为 0000 1111 |
实例
sqlite> .mode line sqlite> select 60 | 13; 60 | 13 = 61 sqlite> select 60 & 13; 60 & 13 = 12 sqlite> select 60 ^ 13; 10 * 20 = 200 sqlite> select (~60); (~60) = -61 sqlite> select (60 << 2); (60 << 2) = 240 sqlite> select (60 >> 2); (60 >> 2) = 15
sqlite 表达式
语法
假设 SELECT 语句的基本语法如下：
SELECT column1,columnN FROM table_name WHERE [CONTION | EXPRESSION];
有不同类型的 sqlite 表达式，具体讲解如下：
sqlite - 布尔表达式
sqlite 的布尔表达式在匹配单个值的基础上获取数据。语法如下：
SELECT column1,columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;
假设 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> SELECT * FROM COMPANY WHERE SALARY = 10000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 James 24 Houston 10000.0
sqlite - 数值表达式
这些表达式用来执行查询中的任何数学运算。语法如下：
SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;
在这里，numerical_expression 用于数学表达式或任何公式。下面的实例演示了 sqlite 数值表达式的用法：
sqlite> SELECT (15 + 6) AS ADDITION ADDITION = 21
有几个内置的函数，比如 avg()、sum()、count()，等等，执行被称为对一个表或一个特定的表列的汇总数据计算。
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; RECORDS = 7
sqlite - 日期表达式
日期表达式返回当前系统日期和时间值，这些表达式将被用于各种数据操作。
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 语句的基本语法如下：
SELECT column1,columnN FROM table_name WHERE [condition]
实例
您还可以使用比较或逻辑运算符指定条件，比如 >、<、=、LIKE、NOT，等等。假设 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 逻辑运算符的用法。下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录，结果显示所有的记录，意味着没有一个记录的 AGE 等于 NULL：
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; 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 语句列出了 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 ---------- ---------- ---------- ---------- ---------- 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 存在于子查询返回的结果中的所有记录：
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 运算符的基本语法如下：
SELECT column1,columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];
您可以使用 AND 运算符来结合 N 个数量的条件。sqlite 语句需要执行的动作是，无论是事务或查询，所有由 AND 分隔的条件都必须为真（TRUE）。
实例
假设 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 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
OR 运算符
OR 运算符也用于结合一个 sql 语句的 WHERE 子句中的多个条件。使用 OR 运算符时，只要当条件中任何一个为真（true）时，整个条件为真（true）。例如，只要当 condition1 或 condition2 有一个为真（true）时，[condition1] OR [condition2] 为真（true）。
语法
带有 WHERE 子句的 OR 运算符的基本语法如下：
SELECT column1,columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]
您可以使用 OR 运算符来结合 N 个数量的条件。sqlite 语句需要执行的动作是，无论是事务或查询，只要任何一个由 OR 分隔的条件为真（TRUE）即可。
实例
假设 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 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
sqlite Update 语句
语法
带有 WHERE 子句的 UPDATE 查询的基本语法如下：
UPDATE table_name SET column1 = value1,column2 = value2....,columnN = valueN WHERE [condition];
您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。
实例
假设 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
下面是一个实例，它会更新 ID 为 6 的客户地址：
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
现在，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 Texas 45000.0 7 James 24 Houston 10000.0
如果您想修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值，则不需要使用 WHERE 子句，UPDATE 查询如下：
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas',SALARY = 20000.00;
现在，COMPANY 表有以下记录：
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 Texas 20000.0 2 Allen 25 Texas 20000.0 3 Teddy 23 Texas 20000.0 4 Mark 25 Texas 20000.0 5 David 27 Texas 20000.0 6 Kim 22 Texas 20000.0 7 James 24 Texas 20000.0
sqlite Delete 语句
语法
带有 WHERE 子句的 DELETE 查询的基本语法如下：
DELETE FROM table_name WHERE [condition];
您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。
实例
假设 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
下面是一个实例，它会删除 ID 为 7 的客户：
sqlite> DELETE FROM COMPANY WHERE ID = 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 South-Hall 45000.0
如果您想要从 COMPANY 表中删除所有记录，则不需要使用 WHERE 子句，DELETE 查询如下：
sqlite> DELETE FROM COMPANY;
现在，COMPANY 表中没有任何的记录，因为所有的记录已经通过 DELETE 语句删除。
