sqlite-sqlite3基本操作1

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

• INT

• INTEGER

• TINYINT

• SMALLINT

• MEDIUMINT

• BIGINT

• UNSIGNED BIG INT

• INT2

• INT8

INTEGER

• CHARACTER(20)

• VARCHAR(255)

• VARYING CHARACTER(255)

• NCHAR(55)

• NATIVE CHARACTER(70)

• NVARCHAR(100)

• TEXT

• CLOB

TEXT

• BLOB

• no datatype specified

NONE

• REAL

• DOUBLE

• DOUBLE PRECISION

• FLOAT

REAL

• NUMERIC

• DECIMAL(10,5)

• BOOLEAN

• DATE

• DATETIME

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 创建数据库

SQLite 的 sqlite3 命令被用来创建新的 SQLite 数据库。不需要任何特殊的权限即可创建一个数据。

语法

sqlite3 命令的基本语法如下：

$sqlite3 DatabaseName.db

通常情况下，数据库名称在 RDBMS 内应该是唯一的。

实例

如果想创建一个新的数据库 <testDB.db>，SQLITE3 语句如下所示：

$sqlite3 testDB.dbSQLite version 3.7.15.2 2013-01-09 11:53:05Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite>

上面的命令将在当前目录下创建一个文件 testDB.db。该文件将被 SQLite 引擎用作数据库。如果您已经注意到 sqlite3 命令在成功创建数据库文件之后，将提供一个sqlite> 提示符。

一旦数据库被创建，您就可以使用 SQLite 的 .databases 命令来检查它是否在数据库列表中，如下所示：

sqlite>.databasesseq  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> .databaseseq  name             file---  ---------------  ----------------------0    main             /home/sqlite/testDB.db2    test             /home/sqlite/testDB.db

数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息，如下所示：

sqlite>  ATTACH DATABASE 'testDB.db' as 'TEMP';Error: database TEMP is already in usesqlite>  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>.databasesseq  name             file---  ---------------  ----------------------0    main             /home/sqlite/testDB.db2    test             /home/sqlite/testDB.db3    currentDB        /home/sqlite/testDB.db

现在，让我们尝试把 'currentDB' 从 testDB.db 中分离出来，如下所示：

sqlite> DETACH DATABASE 'currentDB';

现在，如果检查当前附加的数据库，您会发现，testDB.db 仍与 'test' 和 'main' 保持连接。

sqlite>.databasesseq  name             file---  ---------------  ----------------------0    main             /home/sqlite/testDB.db2    test             /home/sqlite/testDB.db

SQLite 创建表

SQLite 的 CREATE TABLE 语句用于在任何给定的数据库创建一个新表。创建基本表，涉及到命名表、定义列及每一列的数据类型。

语法

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>.tablesCOMPANY     DEPARTMENT

在这里，可以看到 COMPANY 表出现两次，一个是主数据库的 COMPANY 表，一个是为 testDB.db 创建的 'test' 别名的 test.COMPANY 表。您可以使用 SQLite.schema 命令得到表的完整信息，如下所示：

sqlite>.schema COMPANYCREATE 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;

实例

让我们先确认 COMPANY 表已经存在，然后我们将其从数据库中删除。

sqlite>.tablesCOMPANY       test.COMPANY

这意味着 COMPANY 表已存在数据库中，接下来让我们把它从数据库中删除，如下：

sqlite>DROP TABLE COMPANY;sqlite>

现在，如果尝试 .TABLES 命令，那么将无法找到 COMPANY 表了：

sqlite>.tablessqlite>

显示结果为空，意味着已经成功从数据库删除表。

SQLite Insert 语句

SQLite 的 INSERT INTO 语句用于向数据库的某个表中添加新的数据行。

语法

INSERT INTO 语句有两种基本语法，如下所示：

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  VALUES (value1, value2, value3,...valueN);

在这里，column1, column2,...columnN 是要插入数据的表中的列的名称。

如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。SQLite 的 INSERT INTO 语法如下：

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

实例

假设您已经在 testDB.db 中创建了 COMPANY表，如下所示：

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

现在，下面的语句将在 COMPANY 表中创建六个记录：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (1, 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (2, 'Allen', 25, 'Texas', 15000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)VALUES (5, 'David', 27, 'Texas', 85000.00 );INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

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

您可以通过在一个有一组字段的表上使用 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;

实例

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

下面是一个实例，使用 SELECT 语句获取并显示所有这些记录。在这里，前三个命令被用来设置正确格式化的输出。

sqlite>.header onsqlite>.mode columnsqlite> SELECT * FROM COMPANY;

最后，将得到以下的结果：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

如果只想获取 COMPANY 表中指定的字段，则使用下面的查询：

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;

上面的查询会产生以下结果：

ID          NAME        SALARY----------  ----------  ----------1           Paul        20000.02           Allen       15000.03           Teddy       20000.04           Mark        65000.05           David       85000.06           Kim         45000.07           James       10000.0

设置输出列的宽度

有时，由于要显示的列的默认宽度导致 .mode column，这种情况下，输出被截断。此时，您可以使用 .width num, num.... 命令设置显示列的宽度，如下所示：

sqlite>.width 10, 20, 10sqlite>SELECT * FROM COMPANY;

上面的 .width 命令设置第一列的宽度为 10，第二列的宽度为 20，第三列的宽度为 10。因此上述 SELECT 语句将得到以下结果：

ID          NAME                  AGE         ADDRESS     SALARY----------  --------------------  ----------  ----------  ----------1           Paul                  32          California  20000.02           Allen                 25          Texas       15000.03           Teddy                 23          Norway      20000.04           Mark                  25          Rich-Mond   65000.05           David                 27          Texas       85000.06           Kim                   22          South-Hall  45000.07           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,   NAME           TEXT    NOT NULL,   AGE            INT     NOT NULL,   ADDRESS        CHAR(50),   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 linesqlite> select 10 + 20;10 + 20 = 30sqlite> select 10 - 20;10 - 20 = -10sqlite> select 10 * 20;10 * 20 = 200sqlite> select 10 / 5;10 / 5 = 2sqlite> 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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

下面的实例演示了各种 SQLite 比较运算符的用法。

在这里，我们使用 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.05           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.03           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.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.05           David       27          Texas       85000.0

SQLite 逻辑运算符

下面是 SQLite 中所有的逻辑运算符列表。

运算符描述ANDAND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。BETWEENBETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。EXISTSEXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。ININ 运算符用于把某个值与一系列指定列表的值进行比较。NOT ININ 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。LIKELIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOBGLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。NOTNOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。OROR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。IS NULLNULL 运算符用于把某个值与 NULL 值进行比较。ISIS 运算符与 = 相似。IS NOTIS NOT 运算符与 != 相似。||连接两个不同的字符串，得到一个新的字符串。UNIQUEUNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

实例

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.05           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.02           Allen       25          Texas       15000.04           Mark        25          Rich-Mond   65000.05           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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.04           Mark        25          Rich-Mond   65000.05           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.03           Teddy       23          Norway      20000.06           Kim         22          South-Hall  45000.07           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.04           Mark        25          Rich-Mond   65000.05           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----------32252325272224

下面的 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 位运算符

位运算符作用于位，并逐位执行操作。真值表 & 和 | 如下：

pqp & qp | q0000010111111001

假设如果 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 位运算符的用法：

sqlite> .mode linesqlite> select 60 | 13;60 | 13 = 61sqlite> select 60 & 13;60 & 13 = 12sqlite> select  60 ^ 13;10 * 20 = 200sqlite>  select  (~60);(~60) = -61sqlite>  select  (60 << 2);(60 << 2) = 240sqlite>  select  (60 >> 2);(60 >> 2) = 15

SQLite 表达式

表达式是一个或多个值、运算符和计算值的SQL函数的组合。

SQL 表达式与公式类似，都写在查询语言中。您还可以使用特定的数据集来查询数据库。

语法

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

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

有不同类型的 SQLite 表达式，具体讲解如下：

SQLite - 布尔表达式

SQLite 的布尔表达式在匹配单个值的基础上获取数据。语法如下：

SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

下面的实例演示了 SQLite 布尔表达式的用法：

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 ADDITIONADDITION = 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, column2, columnN FROM table_nameWHERE [condition]

实例

您还可以使用比较或逻辑运算符指定条件，比如 >、<、=、LIKE、NOT，等等。假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.05           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.02           Allen       25          Texas       15000.04           Mark        25          Rich-Mond   65000.05           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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.04           Mark        25          Rich-Mond   65000.05           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.03           Teddy       23          Norway      20000.06           Kim         22          South-Hall  45000.07           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.04           Mark        25          Rich-Mond   65000.05           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----------32252325272224

下面的 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, column2, columnN FROM table_nameWHERE [condition1] AND [condition2]...AND [conditionN];

您可以使用 AND 运算符来结合 N 个数量的条件。SQLite 语句需要执行的动作是，无论是事务或查询，所有由 AND 分隔的条件都必须为真（TRUE）。

实例

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.05           David       27          Texas       85000.0

OR 运算符

OR 运算符也用于结合一个 SQL 语句的 WHERE 子句中的多个条件。使用 OR 运算符时，只要当条件中任何一个为真（true）时，整个条件为真（true）。例如，只要当 condition1 或 condition2 有一个为真（true）时，[condition1] OR [condition2] 为真（true）。

语法

带有 WHERE 子句的 OR 运算符的基本语法如下：

SELECT column1, column2, columnN FROM table_nameWHERE [condition1] OR [condition2]...OR [conditionN]

您可以使用 OR 运算符来结合 N 个数量的条件。SQLite 语句需要执行的动作是，无论是事务或查询，只要任何一个由 OR 分隔的条件为真（TRUE）即可。

实例

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.02           Allen       25          Texas       15000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.0

SQLite Update 语句

SQLite 的 UPDATE 查询用于修改表中已有的记录。可以使用带有 WHERE 子句的 UPDATE 查询来更新选定行，否则所有的行都会被更新。

语法

带有 WHERE 子句的 UPDATE 查询的基本语法如下：

UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [condition];

您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。

实例

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          Texas       45000.07           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.02           Allen       25          Texas       20000.03           Teddy       23          Texas       20000.04           Mark        25          Texas       20000.05           David       27          Texas       20000.06           Kim         22          Texas       20000.07           James       24          Texas       20000.0

SQLite Delete 语句

SQLite 的 DELETE 查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。

语法

带有 WHERE 子句的 DELETE 查询的基本语法如下：

DELETE FROM table_nameWHERE [condition];

您可以使用 AND 或 OR 运算符来结合 N 个数量的条件。

实例

假设 COMPANY 表有以下记录：

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          California  20000.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.0

如果您想要从 COMPANY 表中删除所有记录，则不需要使用 WHERE 子句，DELETE 查询如下：

sqlite> DELETE FROM COMPANY;

现在，COMPANY 表中没有任何的记录，因为所有的记录已经通过 DELETE 语句删除。