SQLite详解

SQLite简介

SQLite是嵌入式关系数据库管理系统。 它是独立的，无服务器的，零配置和事务性SQL数据库引擎。

SQLite官方网站是：http://www.sqlite.org/ ，有关所有SQLite的资料都可以从这个网站上找到。打开如下图

为什么要用SQLite？

• SQLite不需要一个单独的服务器进程或系统操作（服务器）。
• SQLite 不需要配置，这意味着它不需要安装或管理。
• 一个完整的SQLite数据库可存储在跨平台的磁盘文件中。
• SQLite是非常小，重量轻，小于400KB完全配置或小于250KB的省略可选功能。
• SQLite是自配置的，独立的，这意味着它不需要任何外部的依赖。
• SQLite的交易是完全符合ACID，允许多个进程或线程安全访问。
• SQLite支持大多数（SQL2）符合SQL92标准的查询语言功能。
• SQLite是在ANSI-C编写的，并提供了简单和易于使用的API。
• SQLite可在UNIX（在Linux，Mac OS-X，Android，IOS）和Windows（Win32中，WINCE，WinRT的）中运行。

SQLite快速入门

1. SQLite是什么？

SQLite是一种开源，零配置，独立的，独立的，事务关系数据库引擎，旨在嵌入到应用程序中。

2. SQLite入门步骤

如果这是您第一次使用SQLite，应该先学习这一部分。 按照这3个简单的步骤，快速开始使用SQLite。

1. 首先，第一个重要的问题：什么是SQLite？ 在开始使用SQLite之前，简要了解一下SQLite。
2. 其次，知道如何在您的计算机上下载和安装SQLite GUI工具。
3. 第三，介绍如何创建SQLite示例数据库，并引导完成使用示例数据库进行练习操作的步骤。

2.1 安装SQLite数据库

SQLite以其零配置而闻名，所以不需要复杂的设置或管理。 下面来看看如何在系统上安装SQLite。

在Windows上安装SQLite

按照以下步骤进行：

• 打开SQLite官方网站，转到下载页面 - http://www.sqlite.org/download.html 并下载预编译的Windows二进制文件。
• 下载sqlite-dll和sqlite-shell的zip文件以及sqlite-tools-win32-x86-3170000.zip文件。
  
• 创建一个文件夹：D:/software/sqlite并放置这些文件。

• 进入D:/software/sqlite目录并打开sqlite3命令。它将如下所示：

D:\software\sqlite> sqlite3
SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Shell

上述方法有助于永久创建数据库，附加数据库和分离数据库。在SQLite中执行CRUD操作有另一种方法。在这种方法中，不需要设置路径。下面我们来看看如何操作 -

• 只需下载SQlite预编译的二进制zip文件：sqlite-tools-win32-x86-3170000.zip。
• 解压到目录：D:/software/sqlite。
• 直接双击运行sqlite3.exe应用程序，得到如下结果 -

现在就可以在这里执行SQLite查询。 但是在这里，数据是暂时的，一旦你关闭了电脑，就将失去操作过的所有数据记录。因为使用这种方法不能创建，附加或分离数据库。

在Linux上安装SQLite

当前，几乎所有的Linux操作系统都将SQLite作为一部分一起发布。可使用以下命令来检查你的机器上是否安装了SQLite。

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

如果没有看到上面的结果，那么你的Linux机器上就还没有安装SQLite。可以按照以下步骤安装SQLite：

打开转到SQLite下载页面( http://www.sqlite.org/download.html )，并从源代码部分下载文件：sqlite-autoconf-*.tar.gz 。

按照以下步骤操作：

$ tar xvfz sqlite-autoconf-3071502.tar.gz
$ cd sqlite-autoconf-3071502
$ ./configure --prefix=/usr/local
$ make
$ make install
Shell

2.2. SQLite语法大全

语法是一组独特的规则和约定。 以下是SQLite的语法列表。

区分大小写：

• SQLite不区分大小写。但是，有一些区分大小写的命令。例如：GLOB和glob在SQLite语句中有不同的含义。

注释：

• 注释用于在SQLite代码中增加代码的可读性。
• 注释不能嵌套。
• 注释以两个连续的“ - ”字符。
• 也可使用“/*”字符开始，并延伸至下一个“*/”字符对所包括的内容视为注释。

SQLite语句

所有的SQLite语句都是以关键字(如：SELECT，INSERT，UPDATE，DELETE，ALTER，DROP等)开始的。所有语句都以分号(;)结尾。

SQLite ANALYZE语句的语法：

ANALYZE;  
-- or  
ANALYZE database_name;  
-- or  
ANALYZE database_name.table_name;
SQL

SQLite AND/OR子句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION-1 {AND|OR} CONDITION-2;
SQL

SQLite ALTER TABLE语句的语法

ALTER TABLE table_name ADD COLUMN column_def...;
SQL

SQLite ALTER TABLE语句(Rename)语句的语法

ALTER TABLE table_name RENAME TO new_table_name;
SQL

SQLite ATTACH DATABASE语句的语法：

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQL

SQLite BEGIN TRANSACTION语句的语法：

BEGIN;  
-- or  
BEGIN EXCLUSIVE TRANSACTION;
SQL

SQLite BETWEEN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name BETWEEN val-1 AND val-2;  
SQLite COMMIT Statement:  
COMMIT;
SQL

SQLite CREATE INDEX语句的语法：

CREATE INDEX index_name  
ON table_name ( column_name COLLATE NOCASE );
SQL

SQLite CREATE UNIQUE INDEX语句的语法：

CREATE UNIQUE INDEX index_name  
ON table_name ( column1, column2,...columnN);
SQL

SQLite CREATE TABLE语句的语法：

CREATE TABLE table_name(  
   column1 datatype,  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
   PRIMARY KEY( one or more columns ));
SQL

SQLite CREATE TRIGGER语句的语法：

CREATE TRIGGER database_name.trigger_name   
BEFORE INSERT ON table_name FOR EACH ROW  
BEGIN   
   stmt1;   
   stmt2;  
   ....  
END;
SQL

SQLite CREATE VIEW语句的语法：

CREATE VIEW database_name.view_name  AS  
SELECT statement....;
SQL

SQLite CREATE VIRTUAL TABLE语句的语法：

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );  
-- or  
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
SQL

SQLite COMMIT TRANSACTION语句的语法：

COMMIT;
SQL

SQLite COUNT语句的语法：

SELECT COUNT(column_name)  
FROM   table_name  
WHERE  CONDITION;
SQL

SQLite DELETE语句的语法：

DELETE FROM table_name  
WHERE  {CONDITION};
SQL

SQLite DETACH DATABASE语句的语法：

DETACH DATABASE 'Alias-Name';
SQL

SQLite DISTINCT语句的语法：

SELECT DISTINCT column1, column2....columnN  
FROM   table_name;
SQL

SQLite DROP INDEX语句的语法：

DROP INDEX database_name.index_name;
SQL

SQLite DROP TABLE语句的语法：

DROP TABLE database_name.table_name;
SQL

SQLite DROP VIEW语句的语法：

DROP INDEX database_name.view_name;
SQL

SQLite DROP TRIGGER 语句的语法：

DROP INDEX database_name.trigger_name;
SQL

SQLite EXISTS语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name EXISTS (SELECT * FROM   table_name );
SQL

SQLite EXPLAIN语句的语法：

EXPLAIN INSERT statement...;  
-- or   
EXPLAIN QUERY PLAN SELECT statement...;
SQL

SQLite GLOB语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name GLOB { PATTERN };
SQL

SQLite GROUP BY语句的语法：

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name;
SQL

SQLite HAVING语句的语法：

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name  
HAVING (arithematic function condition);
SQL

SQLite INSERT INTO语句的语法：

INSERT INTO table_name( column1, column2....columnN)  
VALUES ( value1, value2....valueN);
SQL

SQLite IN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name IN (val-1, val-2,...val-N);
SQL

SQLite Like语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name LIKE { PATTERN };
SQL

SQLite NOT IN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name NOT IN (val-1, val-2,...val-N);
SQL

SQLite ORDER BY语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION  
ORDER BY column_name {ASC|DESC};
SQL

SQLite PRAGMA语句的语法：

PRAGMA pragma_name;
SQL

有关pragma的几个示例：

PRAGMA page_size;  
PRAGMA cache_size = 1024;  
PRAGMA table_info(table_name);
SQL

SQLite RELEASE SAVEPOINT语句的语法：

RELEASE savepoint_name;
SQL

SQLite REINDEX语句的语法：

REINDEX collation_name;  
REINDEX database_name.index_name;  
REINDEX database_name.table_name;
SQL

SQLite ROLLBACK语句的语法：

ROLLBACK;  
-- or  
ROLLBACK TO SAVEPOINT savepoint_name;
SQL

SQLite SAVEPOINT语句的语法：

SAVEPOINT savepoint_name;
SQL

SQLite SELECT语句的语法：

SELECT column1, column2....columnN  
FROM   table_name;
SQL

SQLite UPDATE语句的语法：

UPDATE table_name  
SET column1 = value1, column2 = value2....columnN=valueN  
[ WHERE  CONDITION ];
SQL

SQLite VACUUM语句的语法：

VACUUM;  
SQLite WHERE Clause:  
SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION;
SQL

3. SQLite基础

本节介绍可用于SQLite的基本SQL语句。首先将学习如何创建数据库，并执行CURD操作数据。 如果您已经熟悉SQL(那最好不过，不熟悉也没有关系)，则会注意到SQLite中SQL方言和SQL标准之间的差异。

• SQLite查询数据 – 使用SELECT语句从单个表或多个表中查询数据。
• SQLite Order By – 以升序或降序对结果集进行排序。
• SQLite Select Distinct – 使用DISTINCT子句从表查询唯一行(去除重复行)。
• SQLite Where – 使用各种条件过滤行结果集。
• SQLite Limit – 限制要返回的行数。LIMIT子句可用于获取查询返回的必要数据。
• SQLite IN – 检查值是否匹配值或子查询列表中的任何值。
• SQLite Like – 基于使用通配符的模式匹配查询数据：百分号(％)和下划线(_)。
• SQLite Glob – 确定字符串是否匹配指定的UNIX模式。
• SQLite Group By – 根据指定的条件将数据集合分组。 GROUP BY子句可用于分组总结数据以进行报告。
• SQLite Having – 指定过滤由GROUP BY子句分组汇总的组的条件。
• SQLite内连接 – 使用inner join子句查询来自多个表的数据。
• SQLite左连接 – 使用left join子句组合查询来自多个表的数据。
• SQLite交叉连接 – 演示如何使用交叉连接子句来生成连接中涉及的表的结果集的笛卡尔乘积。
• SQLite自连接 – 将表连接到其自身以创建将行与其他行连接在同一个表中的结果集。
• SQLite Union – 将多个查询的结果集合合到单个结果集中。还讨论了UNION和UNION ALL子句之间的差异。
• SQLite全外连接 – 显示如何使用left join和union子句来模拟SQLite中的完整外连接。
• SQLite Case – 为查询添加条件逻辑。
• SQLite子查询 – 介绍SQLite子查询和相关子查询。

4. SQLite数据更新

这部分将介绍如何使用insert，update和delete语句更新表中的数据。

SQLite insert语句 – 将行数据插入到表中
SQLite update语句 – 更新表中存在行数据记录。
SQLite delete语句 – 从表中删除存在行数据记录。

5. 使用数据库对象

在本节中，将演示如何使用SQL数据定义语言创建数据库对象，如：表，视图，索引。

• SQLite数据类型 – 介绍SQLite动态类型系统及其重要概念：存储类，表现类型和类型关联。
• SQLite创建表 – 演示如何使用CREATE TABLE语句在数据库中创建新表。
• SQLite主键 – 演示如何使用PRIMARY KEY约束来定义表的主键。
• SQLite AUTOINCREMENT – 演示如何使用AUTOINCREMENT属性的工作原理以及为什么情况下应该避免使用它。
• SQLite修改表定义 – 演示如何使用ALTER TABLE语句将新行添加到现有表中并重命名表。 还提供执行其他操作的步骤，例如：删除列，重命名列等。
• SQLite删除表 – 演示如何从数据库中删除指定表。
• SQLite创建视图 – 介绍视图概念，并演示如何在数据库中创建视图。
• SQLite索引 – 演示如何使用和创建索引，以及如何利用索引来加快查询。
• SQLite表达式索引 – 演示如何使用基于表达式的索引
• SQLite VACUUM – 优化数据库文件。
• SQLite触发器 – 在SQLite数据库中管理触发器。

6. SQLite函数

SQLite聚合函数

聚合函数将多行的值组合成一个值，该值可以作为分组的度量，例如：最小值，最大值，平均值，总数等。SQLite支持以下聚合函数：

• SQLite AVG()函数 - 返回一个分组中所有值的平均值。
• SQLite COUNT()函数 - 计算一个分组中的总数量/数目。
• SQLite MAX()函数 - 返回在一个分组中所有值的最大值。
• SQLite MIN()函数 - 返回在一个分组中所有值最小值。
• SQLite SUM()函数 - 返回一个分组中所有值的总和。

SQLite是什么？

SQLite是嵌入式关系数据库管理系统。 它是独立的，无服务器的，零配置和事务性SQL数据库引擎。

SQLite可以自由地用于商业或私有的任何目的。 换句话说，“SQLite是一种开源，零配置，独立的，独立的，旨在嵌入到应用程序中的事务关系数据库引擎”。

SQLite与其他SQL数据库不同，SQLite没有单独的服务器进程。 它直接读取和写入普通磁盘文件。 具有多个表，索引，触发器和视图的完整SQL数据库包含在单个磁盘文件中。

SQLite历史

SQLite最初是在2000年8月设计的。它被命名为SQLite，因为它与其他数据库管理系统(如SQL Server或Oracle)不同，它是非常轻量的(小于500Kb大小)。

年份	有关事件
2000	SQLite由D. Richard Hipp设计，目的是不需要管理程序的管理。
2000	在8月份，SQLite 1.0与GNU数据库管理器一起发布。
2011	Hipp宣布将UNQl接口添加到SQLite数据库并开发UNQLite(面向文档的数据库)。

SQLite特性

以下是为什么SQLite比其他轻量级数据库受欢迎的功能列表：

• SQLite是完全免费的：SQLite是开源的。 因此，不需要许可证就可以自由地使用它。
• SQLite是无服务器的：SQLite不需要服务器进程或系统来操作。
• SQLite非常灵活：它可以在同一个会话上同时处理多个数据库。
• SQLite不需要配置：SQLite无需设置或管理。
• SQLite是一个跨平台的数据库系统：除了在大多数平台，如Windows，Mac OS，Linux和Unix。 它也可以用于许多嵌入式操作系统，如Symbian,Android和Windows CE上使用。
• 存储数据很容易：SQLite提供了一种有效的数据存储方式。
• 列长度可变：列的长度是可变的，不是固定的。 它有助于您只分配一个字段所需的空间。 例如，如果您有一个varchar(200)的列，并且在其上放置了一个10个字符的长度值，那么SQLite将仅为该值分配20个字符的空间，而不是整个200个空间。
• 提供大量的API：SQLite为大多数的编程语言提供了API。 例如：.Net语言(Visual Basic，C＃)，PHP，Java，Objective C，Python和许多其他编程语言提供了相应的API。
• SQLite是用ANSI-C编写的，提供简单易用的API。
• SQLite在UNIX(Linux，Mac OS-X，Android，iOS)和Windows(Win32，WinCE，WinRT)上均可用。

SQLite优点和缺点

SQLite的优点

• SQLite是一个非常轻量级的数据库。 因此在电脑，手机，相机，家用电子设备等设备的嵌入式软件是非常好的选择。
• SQLite的数据存储非常简单高效。 当您需要存储文件存档时，SQLite可以生成较小数据量的存档，并且包含常规ZIP存档的大量元数据。
• SQLite可以用作临时数据集，以对应用程序中的一些数据进行一些处理。
• 在SQLite数据库中，数据查询非常简单。 您可以将数据加载到SQLite内存数据库中，并随时提取数据。可以按照您想要的方式提取数据。
• SQLite提供了一种简单有效的方式来处理数据，而不是以内存变量来做数据处理。 例如：如果您正在开发一个程序，并且有一些记录要对其进行一些计算。 然后，您可以创建一个SQLite数据库并在其中插入记录，查询，可以选择记录并直接进行所需的计算。
• SQLite非常容易学习和使用。它不需要任何安装和配置。只需复制计算机中的SQLite库，就可以创建数据库了。

SQLite的缺点

• SQLite一般用于处理小到中型数据存储，对于高并发高流量的应用不适用。

SQLite命令大全

SQLite命令与SQL命令类似。 有三种类型的SQLite命令：

• DDL：数据定义语言
• DML：数据操作语言
• DQL：数据查询语言

数据定义语言

数据定义语言中主要有三个命令：

• CREATE：此命令用于创建表，数据库中的表或其他对象的视图。
• ALTER：此命令用于修改现有的数据库对象，如表。
• DROP：DROP命令用于删除整个表，数据库中的表或其他对象的视图。

数据操作语言

数据操作语言中主要有三个命令：

• INSERT：此命令用于创建记录。
• UPDATE：用于修改记录。
• DELETE：用于删除记录。

数据查询语言

• SELECT：此命令用于从一个或多个表中检索某些记录。

SQLite的点命令

以下是SQLite点(.)命令的列表。 这些命令不会以分号(;)终止。

.help

可在任何时候使用“.help”检查点命令列表。

例如：

QLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help
.auth ON|OFF           Show authorizer callbacks
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.binary on|off         Turn binary output on or off.  Default OFF
.changes on|off        Show number of rows changed by SQL
.check GLOB            Fail if output since .testcase does not match
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dbinfo ?DB?           Show status information about the database
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo on|off           Turn command echo on or off
.eqp on|off|full       Enable or disable automatic EXPLAIN QUERY PLAN
.exit                  Exit this program
.explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.imposter INDEX TABLE  Create imposter table TABLE on index INDEX
.indexes ?TABLE?       Show names of all indexes
                         If TABLE specified, only show indexes for tables
                         matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL?   Display or change the value of an SQLITE_LIMIT
.lint OPTIONS          Report potential schema issues. Options:
                         fkey-indexes     Find missing foreign key indexes
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         ascii    Columns/rows delimited by 0x1F and 0x1E
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by "|"
                         quote    Escape answers as for SQL
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?--new? ?FILE?   Close existing database and reopen FILE
                         The --new starts with an empty file
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
.scanstats on|off      Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?      Show the CREATE statements matching PATTERN
                          Add --indent for pretty-printing
.selftest ?--init?     Run tests defined in the SELFTEST table
.separator COL ?ROW?   Change the column separator and optionally the row
                         separator for both the output mode and .import
.sha3sum ?OPTIONS...?  Compute a SHA3 hash of database content
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats ?on|off?        Show stats or turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.testcase NAME         Begin redirecting output to 'testcase-out.txt'
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsinfo ?AUX?         Information about the top-level VFS
.vfslist               List all available VFSes
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify

以上是各种SQLite的点(.)命令的列表。 命令及其描述如下表所示：

命令	描述说明
.backup ?db? file	备份数据库(默认“main”)到文件中
.bail on/off	遇到错误后停止,默认为off
.databases	附件数据库的列表名称和文件
.dump ?table?	以sql文本格式转储数据库。如果指定表，则只转储表匹配像模式表。
.echo on/off	打开或关闭echo命令
.exit	退出sqlite提示符
.explain on/off	转向输出模式适合说明on/off。如没有参参数，则它为on。
.header(s) on/off	打开或关闭标题的显示
.help	显示指定帮助消息
.import file table	将数据从文件导入表
.indices ?table?	显示所有索引的名称。如果指定表，则只显示匹配的表的索引，如模式表。
.load file ?entry?	加载扩展库
.log file/off	打开或关闭日志记录。文件可以是stderr/stdout
.mode mode	设置输出模式
.nullvalue string	打印字符串代替空值
.output filename	发送输出到文件名
.output stdout	发送输出到屏幕
.print string...	打印文字字符串
.prompt main continue	替换标准提示
.quit	退出sqlite提示符
.read filename	在文件名中执行sql
.schema ?table?	显示创建语句。如果指定表，则只显示与模式表匹配的表。
.separator string	更改分隔符由输出模式和.import使用
.show	显示各种设置的当前值
.stats on/off	打开或关闭统计信息
.tables ?pattern?	列出匹配类似模式的表的名称
.timeout ms	尝试打开锁定的表毫秒
.width num num	设置“列”模式的列宽
.timer on/off	打开或关闭cpu定时器测量

.show命令

可以使用.show命令查看SQLite命令提示符的默认设置。

注意：不要在sqlite>提示符和.命令之间放置空格，否则将不起作用。

其它特殊点命令

有一些点(.)命令用于格式化输出。这些命令是：

.header on

.mode column

.timer on

SQLite语法大全

语法是一组独特的规则和约定。 以下是SQLite的语法列表。

区分大小写：

• SQLite不区分大小写。但是，有一些区分大小写的命令。例如：GLOB和glob在SQLite语句中有不同的含义。

注释：

• 注释用于在SQLite代码中增加代码的可读性。
• 注释不能嵌套。
• 注释以两个连续的“ - ”字符。
• 也可使用“/*”字符开始，并延伸至下一个“*/”字符对所包括的内容视为注释。

SQLite语句

所有的SQLite语句都是以关键字(如：SELECT，INSERT，UPDATE，DELETE，ALTER，DROP等)开始的。所有语句都以分号(;)结尾。

SQLite ANALYZE语句的语法：

ANALYZE;  
-- or  
ANALYZE database_name;  
-- or  
ANALYZE database_name.table_name;
SQL

SQLite AND/OR子句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION-1 {AND|OR} CONDITION-2;
SQL

SQLite ALTER TABLE语句的语法

ALTER TABLE table_name ADD COLUMN column_def...;
SQL

SQLite ALTER TABLE语句(Rename)语句的语法

ALTER TABLE table_name RENAME TO new_table_name;
SQL

SQLite ATTACH DATABASE语句的语法：

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQL

SQLite BEGIN TRANSACTION语句的语法：

BEGIN;  
-- or  
BEGIN EXCLUSIVE TRANSACTION;
SQL

SQLite BETWEEN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name BETWEEN val-1 AND val-2;  
SQLite COMMIT Statement:  
COMMIT;
SQL

SQLite CREATE INDEX语句的语法：

CREATE INDEX index_name  
ON table_name ( column_name COLLATE NOCASE );
SQL

SQLite CREATE UNIQUE INDEX语句的语法：

CREATE UNIQUE INDEX index_name  
ON table_name ( column1, column2,...columnN);
SQL

SQLite CREATE TABLE语句的语法：

CREATE TABLE table_name(  
   column1 datatype,  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
   PRIMARY KEY( one or more columns ));
SQL

SQLite CREATE TRIGGER语句的语法：

CREATE TRIGGER database_name.trigger_name   
BEFORE INSERT ON table_name FOR EACH ROW  
BEGIN   
   stmt1;   
   stmt2;  
   ....  
END;
SQL

SQLite CREATE VIEW语句的语法：

CREATE VIEW database_name.view_name  AS  
SELECT statement....;
SQL

SQLite CREATE VIRTUAL TABLE语句的语法：

CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );  
-- or  
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
SQL

SQLite COMMIT TRANSACTION语句的语法：

COMMIT;
SQL

SQLite COUNT语句的语法：

SELECT COUNT(column_name)  
FROM   table_name  
WHERE  CONDITION;
SQL

SQLite DELETE语句的语法：

DELETE FROM table_name  
WHERE  {CONDITION};
SQL

SQLite DETACH DATABASE语句的语法：

DETACH DATABASE 'Alias-Name';
SQL

SQLite DISTINCT语句的语法：

SELECT DISTINCT column1, column2....columnN  
FROM   table_name;
SQL

SQLite DROP INDEX语句的语法：

DROP INDEX database_name.index_name;
SQL

SQLite DROP TABLE语句的语法：

DROP TABLE database_name.table_name;
SQL

SQLite DROP VIEW语句的语法：

DROP INDEX database_name.view_name;
SQL

SQLite DROP TRIGGER 语句的语法：

DROP INDEX database_name.trigger_name;
SQL

SQLite EXISTS语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name EXISTS (SELECT * FROM   table_name );
SQL

SQLite EXPLAIN语句的语法：

EXPLAIN INSERT statement...;  
-- or   
EXPLAIN QUERY PLAN SELECT statement...;
SQL

SQLite GLOB语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name GLOB { PATTERN };
SQL

SQLite GROUP BY语句的语法：

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name;
SQL

SQLite HAVING语句的语法：

SELECT SUM(column_name)  
FROM   table_name  
WHERE  CONDITION  
GROUP BY column_name  
HAVING (arithematic function condition);
SQL

SQLite INSERT INTO语句的语法：

INSERT INTO table_name( column1, column2....columnN)  
VALUES ( value1, value2....valueN);
SQL

SQLite IN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name IN (val-1, val-2,...val-N);
SQL

SQLite Like语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name LIKE { PATTERN };
SQL

SQLite NOT IN语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  column_name NOT IN (val-1, val-2,...val-N);
SQL

SQLite ORDER BY语句的语法：

SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION  
ORDER BY column_name {ASC|DESC};
SQL

SQLite PRAGMA语句的语法：

PRAGMA pragma_name;
SQL

有关pragma的几个示例：

PRAGMA page_size;  
PRAGMA cache_size = 1024;  
PRAGMA table_info(table_name);
SQL

SQLite RELEASE SAVEPOINT语句的语法：

RELEASE savepoint_name;
SQL

SQLite REINDEX语句的语法：

REINDEX collation_name;  
REINDEX database_name.index_name;  
REINDEX database_name.table_name;
SQL

SQLite ROLLBACK语句的语法：

ROLLBACK;  
-- or  
ROLLBACK TO SAVEPOINT savepoint_name;
SQL

SQLite SAVEPOINT语句的语法：

SAVEPOINT savepoint_name;
SQL

SQLite SELECT语句的语法：

SELECT column1, column2....columnN  
FROM   table_name;
SQL

SQLite UPDATE语句的语法：

UPDATE table_name  
SET column1 = value1, column2 = value2....columnN=valueN  
[ WHERE  CONDITION ];
SQL

SQLite VACUUM语句的语法：

VACUUM;  
SQLite WHERE Clause:  
SELECT column1, column2....columnN  
FROM   table_name  
WHERE  CONDITION;

SQLite数据类型

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	表示值是一个数据块，与输入的数据完全相同。

注意：SQLite存储类比数据类型更通用一些。 例如：INTEGER存储类包括不同长度的6种不同的整数数据类型。

SQLite的近似类型

SQLite支持列的类型近似性。列可以存储任何类型的数据，但是列的首选存储类称为它的近似性类型。

在SQLite3数据库中有以下类型近似可用于分配。

存储类	描述
TEXT	此列可使用存储类为NULL，TEXT或BLOB来存储所有数据。
NUMERIC	此列可包含使用所有五个存储类的值。
INTEGER	它的行为与带有转换表达式异常的具有数字近似的列相同。
REAL	它的行为类似于具有数字近似的列(除了它将整数值强制以浮点表示)
NONE	具有近似性NONE的列不会将一个存储类转为另一个存储类型

SQLite近似和类型名称

以下是可以在创建SQLite表时使用的各种数据类型名称的列表。

数据类型	相应的近似类型
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 - 未指定数据类型	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

日期和时间数据类型

在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中，没有一个单独的布尔存储类。一个代替办法是将布尔值存储为整数0(假)和1(真)。

SQLite运算符

当使用WHERE子句执行比较和算术运算时需要使用到运算符，SQLite运算符是SQLite语句中使用的保留字或字符。

可以使用运算符来指定条件和SQLite语句中多个条件的连接。

SQLite中主要有4种类型的运算符：

• 算术运算符
• 比较运算符
• 逻辑运算符
• 按位运算符

SQLite算术运算符

下表指定了SQLite中的算术运算符。 在这个表中，有两个变量“a”和“b”，它们的值分别是50和100。

运算符	说明	示例
+	加法运算符：用于将运算符两侧的值相加	a+b = 150
-	减法运算符：用于从左操作数减去右操作数。	a-b = -50
*	乘法运算符：用于将运算符两边的操作数相乘。	a*b = 5000
/	除法运算符：它是将左操作数除以右操作数。	a/b = 0.5
%	模数运算符：用于通过左操作数除以右操作数并返回余数。	b/a = 0

SQLite比较运算符

下表中列出了SQLite中的比较运算符。 在这个表中，有两个变量“a”和“b”，它们的值分别是50和100。

运算符	说明	示例
==	它用于检查两个操作数的值是否相等，如果是，则条件求值结果为true，否则返回false。	(a == b)的结果为false。
=	它用于检查两个操作数的值是否相等，如果是，则条件求值结果为true，否则返回false。	(a = b)的结果为false。
!=	它用于检查两个操作数的值是否相等，如果两个值不相等则条件求值结果为true。	(a != b)的结果为true。
<>	它用于检查两个操作数的值是否相等，如果两个值不相等则条件求值结果为true。	(a <> b)的结果为true。
>	它用于检查左操作数的值是否大于右操作数的值，如果是，则条件求值结果为true。	(a > b)结果为false
<	它用于检查左操作数的值是否小于右操作数的值，如果是，则条件求值结果为true。	(a < b)结果为true
>=	用于检查左操作数的值是否大于或等于右操作数的值，如果是，则条件求值结果为true。	(a >= b)结果为false
<=	它用于检查左操作数的值是否小于或等于右操作数的值，如果是，则条件求值结果为true。	(a <= b)结果为true
!<	它用于检查左操作数的值是否不小于右操作数的值，如果是，则条件求值结果为true。	(a !< b)结果为false
!>	它用于检查左操作数的值是否不大于右操作数的值，如果是，则条件求值结果为true。	(a !> b)结果为true

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位运算符工作在位上并执行逐位操作。

下面是二进制AND(＆)和二进制OR(|)的真值表：

p	q	p&q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

假设有两个变量“a”和“b”，两个变量的值分别是：60和13。那么a和b的二进制值是：

a= 0011 1100

b= 0000 1101

a&b = 0000 1100

a|b = 0011 1101

~a = 1100 0011
Shell

运算符	描述	示例
&	如果二进制AND运算符应用在两个操作数上，则二进制AND运算符将对该结果复制一位。	(a & b) 将会得到 12也就是 0000 1100
	二进制OR运算符如果存在于任一操作数中，则复制一位。	(a)b) 将会得到 61也就是 0011 1101
~	二进制补码运算符是一元的，具有“翻转”位的作用。	(~a) 将会得到-61 也就是 1100 0011
<<	二进制左移操作符，左操作数值按右操作数指定的位数向左移动。	a << 2 将会得到 240 也就是 1111 0000
>>	二进制右移操作符，左操作数值按右操作数指定的位数向右移动。	a >> 2 将会得到 15 也就是 0000 1111

SQLite表达式

SQLite表达式是一个或多个值，运算符和SQL函数的组合。表达式用于评估示值。

SQLite表达式用查询语言(SQL)编写，并与SELECT语句一起使用。

语法：

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

SQLite中主要有三种类型的表达式：

1. SQLite布尔表达式

SQLite布尔表达式用于在匹配单个值的基础上获取数据。

语法：

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

示例：

假设有一个名称为“STUDENT”的表，具有以下数据：

sqlite> select * from student;
1|Max|27|Delhi|20000.0
2|Min|25|Patna|15000.0
3|Mark|23|USA|2000.0
4|Avg Lee|25|China|65000.0
5|Curry|26|China|25000.0
sqlite>
Shell

使用SQLite布尔表达式的简单示例，如下所示 -

SELECT * FROM STUDENT WHERE FEES = 20000;
SQL

执行上面查询，结果如下 -

2. SQLite数字表达式

SQLite数字表达式用于在查询中用来执行数学运算。

语法：

SELECT numerical_expression as  OPERATION_NAME  
[FROM table_name WHERE CONDITION] ;
SQL

示例

SELECT (25 + 15) AS ADDITION;
SELECT (250 + 255) AS ADDITION;
SQL

执行上面查询，结果如下 -

数字表达式包含一些内置函数，如avg()，sum()，count()等。这些函数称为聚合数据计算函数。

例如

SELECT COUNT(*) AS "number of students" FROM STUDENT;
SELECT SUM(FEES) AS "Sum FEES of students" FROM STUDENT;
SQL

执行上面查询，结果如下 -

3. SQlite日期表达式

SQlite日期表达式用于获取当前系统日期和时间值。

语法：

SELECT CURRENT_TIMESTAMP;
SQL

执行上面查询，结果如下 -

数据库和表

SQLite创建数据库

在SQLite中，sqlite3命令用于创建新的数据库。

语法

sqlite3 DatabaseName.db
SQL

数据库名称(DatabaseName.db)在RDBMS中应该是唯一的。

注意：sqlite3命令用于创建数据库。 但是，如果数据库不存在，则将自动创建具有给定名称的新数据库文件。

如何创建数据库：

首先打开命令提示符并进入创建数据库的目录。之后，可以使用“dir”命令查看sqlite目录。

示例：

创建一个名称为“ybai.db”的数据库：

sqlite3 ybai.db

执行上面命令后，应该就创建了数据库。 可以使用“.databases”命令检查创建的数据库。

.databases

也可以在SQLite根文件夹中看到创建的数据库。

.quit命令

SQLite .quit命令用于从sqlite提示符中退出来。

.dump命令

.dump命令用于在命令提示符下使用SQlite命令导出完整数据库在文本文件中。

例如：

sqlite3 ybai.db .dump > ybai.sql
Shell

执行结果如下

SQLite附加/选择数据库

什么是附加数据库？

假设有多个数据库，但是一次只能使用其中的一个。 这就要使用ATTACH DATABASE语句了。 它有助于您选择特定的数据库，并且在使用此命令后，所有SQLite语句将在附加/选择的数据库下执行。

语法

ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQL

注意：如果数据库不存在，上述语法也将创建一个数据库，否则它只会将数据库文件名与逻辑数据库Alias-Name相连接。

下面来举个例子，假设已以有一个存在的数据库：ybai.db 。

使用以下语句：

ATTACH DATABASE 'ybai.db' as 'yiibai';
SQL

现在可以通过使用.databases命令看到数据库：

SQLite分离数据库

SQLite DETACH DATABASE语句用于将别名命名的数据库与先前使用ATTACH语句附加的数据库连接进行分离。

如果同一数据库文件已附加多个别名，则DETACH命令将仅断开给定的名称，其余的附件仍将继续。 主数据库和临时数据库无法分离。

注意：内存或临时数据库中的数据库将被完全销毁，内容将丢失。

语法：

DETACH DATABASE 'Alias-Name'
SQL

下面举个例子来演示如何分离附加的别名数据库。 在这里，假设有一个附加的数据库：“yiibai”。

从ybai.db分离“yiibai”：

DETACH DATABASE 'yiibai';

执行上面命令后，数据库就分离了。可以使用“.databases”命令查看数据库的情况。

SQLite创建表

在SQLite中，CREATE TABLE语句用于创建新表。 在创建表时，需要为表指定一个名称并定义表的每列和数据类型。

语法：

CREATE TABLE database_name.table_name(  
   column1 datatype  PRIMARY KEY(one or more columns),  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
);
SQL

下面举个例子来创建SQLite数据库中的表：

CREATE TABLE student(  
   ID INT PRIMARY KEY     NOT NULL,  
   NAME           TEXT    NOT NULL,  
   AGE            INT     NOT NULL,  
   ADDRESS        CHAR(50),  
   FEES         REAL  
);
SQL

使用SQLite的.tables命令查看表是否已成功创建。

下面再创建另一个表：department。

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

执行结果如下所示

现在数据库中有两个表：“department”和“student”。现在查看所创建的表：

创建另外一个表：class，存储学生的班级信息 -

CREATE TABLE class(  
  id INT PRIMARY KEY      NOT NULL,  
  class_name           CHAR(50) NOT NULL,  
  student_id         INT      NOT NULL  
);

SQLite删除表

在SQLite中，DROP TABLE语句用于删除表定义以及与该表关联的所有关联数据，索引，触发器，约束和权限规范。

语法

DROP TABLE database_name.table_name;
SQL

注意：使用DROP TABLE命令时必须非常小心，因为一旦删除了表，那么表中的所有可用信息都将被破坏，再无法恢复了。

下面举个例子来演示如何在SQLite中删除一个表。在上一篇创建表的教程文章中，我们成功地创建了有两个表：department和student。下面使用.tables命令来查看数据库现有表信息。

如上图中，可以看到所述的两个表：department和student。

现在，使用以下语句来删除student表 -

DROP TABLE STUDENT;
SQL

在执行上面命令后，使用.tables命令查看student表是否真的被删除了。如下所示

如上图中，所列出的表中并没有student ， 说明 student 确实被删除了。

CURD操作

SQLite插入查询

在SQLite中，INSERT INTO语句用于将新的数据行添加/插入到表中。 创建表后，该命令用于将数据插入到表中。

INSERT INTO语句有两种类型的基本语法：

语法

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

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

如果要向表中的所有列添加值，则不需要在SQlite查询中指定列名称。 但是，应该确保值的顺序与表中列的顺序相同。

那么，语法如下所示：

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

看下面一个例子，用来演示如何向SQLite数据库中执行INSERT查询语句。前面的文章中，已经创建了一个名为“student”的表。 现在向student表中插入一些数据记录。

使用第一种方法插入值：

INSERT INTO student (ID,NAME,AGE,ADDRESS,FEES)  
VALUES (1, 'Maxsu', 27, 'Shengzhen', 20000.00);  
INSERT INTO student (ID,NAME,AGE,ADDRESS,FEES)  
VALUES (2, 'Minsu', 25, 'Beijing', 15000.00 );  
INSERT INTO student (ID,NAME,AGE,ADDRESS,FEES)  
VALUES (3, 'Avgsu', 23, 'Shanghai', 2000.00 );  
INSERT INTO student (ID,NAME,AGE,ADDRESS,FEES)  
VALUES (4, 'Linsu', 25, 'Guangzhou', 65000.00 );  
INSERT INTO student (ID,NAME,AGE,ADDRESS,FEES)  
VALUES (5, 'Sqlsu', 26, 'Haikou', 25000.00 );
SQL

执行上面代码，结果如下

使用第二种方法：

也可以通过第二种方法将数据插入到表中。

INSERT INTO student VALUES (6, 'Javasu', 21, 'Shengzhen', 18000.00 );

可以使用SELECT语句查看student表中的数据：

SELECT * FROM student;

为了方便后面学习使用，这里也准备了一些 department表的数据 -

INSERT INTO department (ID,DEPT,EMP_ID)  VALUES (1,'财务部', 1);  
INSERT INTO department (ID,DEPT,EMP_ID)  VALUES (2,'技术部', 2);  
INSERT INTO department (ID,DEPT,EMP_ID)  VALUES (3,'技术部', 3);  
INSERT INTO department (ID,DEPT,EMP_ID)  VALUES (4,'市场部', 4);  
INSERT INTO department (ID,DEPT,EMP_ID)  VALUES (5,'市场部', 5);
SQL

为了方便后面学习使用，这里也准备了一些 class表的数据 -

INSERT INTO class (id,class_name,student_id)  VALUES (1,'网络工程-001班', 1);  
INSERT INTO class (id,class_name,student_id) VALUES (2,'网络工程-002班', 2);  
INSERT INTO class (id,class_name,student_id)  VALUES (3,'网络工程-003班', 3);  
INSERT INTO class (id,class_name,student_id)  VALUES (4,'网络工程-004班', 4);

SQLite选择查询

在SQLite数据库中，SELECT语句用于从表中获取数据。 当创建一个表并插入一些数据之后，我们在需要时查询提取数据。这就是为什么需要使用选择查询。

语法：

SELECT column1, column2, columnN FROM table_name;
SQL

这里，column1，column2 …是表的字段，指定想要获取哪些值。 如果要获取字段中可用的所有字段，则可以使用以下语法(使用 * 号表所有列)：

SELECT * FROM table_name;
SQL

选择所有列例子：

SELECT * FROM student;
SQL

选择部分列例子：

SELECT name,fees FROM student;
SQL

SQLite更新查询

在SQLite中，UPDATE查询用于修改表中的现有记录。 它与WHERE子句一起作为条件使用以选择特定行，否则所有行将被更新。

语法

UPDATE table_name  
SET column1 = value1, column2 = value2...., columnN = valueN  
WHERE [condition];
SQL

示例：

假设有一个名为“student”的表，具有以下数据：

sqlite> SELECT * FROM student;
1|Maxsu|27|Shengzheng|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
SQL

示例1：

更新ID值等于1的学生的地址 -

UPDATE STUDENT SET ADDRESS = 'Haikou' WHERE ID = 1;
SQL

现在ID值等于1的学生的地址已经更新，可以使用SELECT语句检查更新结果：

SELECT * FROM STUDENT;
SQL

输出结果如下 -

示例2：

更新ID值等于2的学生的地址和费用 -

UPDATE STUDENT SET ADDRESS = 'Zhongshan Road No. 1233, Guangzhou', fees=28800 WHERE ID = 2;
SQL

现在ID值等于2的学生的地址和费用已经更新，可以使用SELECT语句检查更新结果：

SELECT * FROM STUDENT where id=2;
SQL

输出结果如下 -

示例3：

如果不使用WHERE子句，它将修改student表中的所有地址：

UPDATE STUDENT SET ADDRESS = 'Renmin Road No.123456, Haikou ';

SQLite删除查询

在SQLite中，DELETE查询语句用于从表中删除已经存在的记录。 可以将其与WHERE子句或不与WHERE子句一起使用。 WHERE子句用于指定删除特定记录(选定的行)，否则所有记录将被删除。

语法

DELETE FROM table_name  
WHERE [conditions....................];;
SQL

注意：可以使用多个“AND”或“OR”运算符在“WHERE”子句中。

示例：

有一个名为“STUDENT”的表，具有以下数据：

sqlite> SELECT * FROM student;
1|Maxsu|27|Haikou|20000.0
2|Minsu|25|Zhongshan Road No. 1233, Guangzhou|28800.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
SQL

示例1：

从STUDENT表中，删除ID为4的学生信息记录。

DELETE FROM STUDENT WHERE ID = 4;
SQL

在执行上面语句后，学生ID为4的记录将被删除; 可以使用SELECT语句检查它：

SELECT * FROM STUDENT;
SQL

执行结果如下所示 -

示例2：

删除表中那些年龄小于 25 的所有记录，那么可在WHERE子句后指定条件。

DELETE FROM STUDENT WHERE age < 25;
SQL

执行结果如下所示 -

示例3：

如果要删除student表中的所有记录，则不需要指定WHERE子句。

DELETE FROM STUDENT;
SQL

执行结果如下所示 -

如上图所示，“STUDENT”表中没有任何数据了

子句和条件

SQLite WHERE子句

SQLite WHERE子句通常与SELECT，UPDATE和DELETE语句一起使用，以便作为指定条件从一个表或多个表中获取数据。

如果条件满足或正确，则返回表中的特定值。 可使用WHERE子句来过滤记录并仅获取满足指定条件的记录。

WHERE子句还用于过滤记录并仅获取特定数据。

语法

SELECT column1, column2, columnN   
FROM table_name  
WHERE [condition]
SQL

示例：

在这个例子中，将使用WHERE子句与几个比较和逻辑运算符。如：>，<，=，like，NOT等等

假设有一个表student，并具有以下数据：

示例1：

选择年龄(age)大于或等于25，并且费用(fees)大于或等于10000.00的学生信息记录，如下 -

SELECT * FROM STUDENT WHERE AGE >= 25 AND FEES >= 10000.00;
SQL

执行上面查询，得到以下结果 -

示例2：

从STUDENT表中选择查询名字以’M‘字母开头的学生信息记录。

SELECT * FROM STUDENT WHERE NAME LIKE 'M%';
SQL

执行上面查询，得到以下结果 -

示例3：

从STUDENT表中选择所有年龄为25或27岁的学生信息。

SELECT * FROM STUDENT WHERE AGE IN ( 25, 27 );
SQL

执行上面查询，得到以下结果 -

示例4：

从STUDENT表中选择所有年龄不是25，也不是27岁的学生信息。

SELECT * FROM STUDENT WHERE AGE NOT IN ( 25, 27 );
SQL

执行上面查询，得到以下结果 -

SQLite OR子句

SQLite AND运算符通常与SELECT，UPDATE和DELETE语句一起使用以组合多个条件。 它是一个联合运算符，OR运算符始终与WHERE子句一起使用，如果两个条件之一为真，则完整条件为真。

语法：

SELECT column1, column2, columnN   
FROM table_name  
WHERE [condition1] OR [condition2]...OR [conditionN]
SQL

可以使用OR运算符组合多个条件。

示例：

假设有一个名为STUDENT的表，并具有以下数据：

sqlite> SELECT * FROM STUDENT ;
1|Maxsu|27|Shengzheng|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
SQL

从STUDENT表中选择所有AGE大于等于25，或者费用大于等于15000的学生的信息。

SELECT * FROM STUDENT WHERE AGE >= 25 OR FEES >= 15000;
SQL

输出结果如下 -

SQLite LIKE子句

SQLite LIKE运算符用于使用通配符将文本值与模式进行匹配。 在搜索表达式与模式表达式匹配的情况下，LIKE运算符将返回真，即：1。

与LIKE操作符一起使用的两个通配符：

• 百分号(％)
• 下划线(_)

百分号(％)表示零个，一个或多个数字或字符。 下划线(_)表示一个数字或字符。

语法

SELECT FROM table_name  
WHERE column LIKE 'XXXX%'
SQL

或者

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

或者

SELECT FROM table_name  
WHERE column LIKE 'XXXX_'
SQL

或者

SELECT FROM table_name  
WHERE column LIKE '_XXXX'
SQL

或者

SELECT FROM table_name  
WHERE column LIKE '_XXXX_'
SQL

这里，XXXX可以是任何数字或字符串值。

示例：

假设有一个名为STUDENT的表，并具有以下数据：

sqlite> SELECT * FROM STUDENT ;
1|Maxsu|27|Shengzheng|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
SQL

在这些示例中，在WHERE语句的LIKE子句中，在’FEES‘字段上使用’％‘和’_‘运算符，对应结果如下：

语句	结果说明
Where FEES like '200%'	查找以200开头的任何值
Where FEES like '%200%'	查找包含200开头的任何值
Where FEES like '_00%'	查找第二个位置和第三个位置是0的任何值
Where FEES like '2_%_%'	查找以2开头并且长度至少为3个字符的值
Where FEES like '%2'	查找以2结尾的任何值
Where FEES like '_2%3'	查找任何在第二个位置值为2，并以3结尾的值
Where FEES like '2___3'	查找以2开头，以3结尾的一个五位数字值

示例1：

从STUDENT表中查询age以5结尾的所有记录。

SELECT * FROM STUDENT WHERE AGE  LIKE '%5';
SQL

执行上面语句，得到以下结果 -

示例2：

从STUDENT表中查询地址值具有“an”字符的所有记录：

SELECT * FROM STUDENT WHERE ADDRESS LIKE '%an%';
SQL

执行上面语句，得到以下结果 -

SQLite GLOB子句

SQLite GLOB操作符通过使用通配符将模式表达式与文本值匹配， 当搜索表达式与模式表达式匹配时，GLOB运算符将返回真，该值为：1。

GLOB运算符遵循UNIX的语法，使用指定以下通配符。

• 星号(*)： 符号表示零个或多个数字或字符。
• 问号(?)： 符号表示单个数字或字符。

语法：

星号(*)符号的语法：

SELECT FROM table_name  
WHERE column GLOB 'XXXX*'  
-- 或者
SELECT FROM table_name  
WHERE column GLOB '*XXXX*'
SQL

• 问号(?)符号的语法：

SELECT FROM table_name  
WHERE column GLOB 'XXXX?'  
-- 或者
SELECT FROM table_name  
WHERE column GLOB '?XXXX'  
-- 或者
SELECT FROM table_name  
WHERE column GLOB '?XXXX?'  
-- 或者
SELECT FROM table_name  
WHERE column GLOB '????'
SQL

示例：

假设有一个名为“STUDENT”的表，并具有以下数据：

sqlite> SELECT * FROM STUDENT;
1|Maxsu|27|Shengzheng|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
SQL

在下面这些示例中，WHERE语句具有不同的BLOB子句，带有’*‘和’?‘运算符：

语句	描述
WHERE FEES GLOB '200*'	查找以200开头的任何值
WHERE FEES GLOB '*200*'	查找包含200的任何值
WHERE FEES GLOB '?00*'	查找在第二和第三个位置是00的任何值
WHERE FEES GLOB '2??'	查找以2开头并且长度至少为3个字符的值
WHERE FEES GLOB'*2'	查找以2结尾的任何值
WHERE FEES GLOB '?2*3'	查找具有第二个位置是2并以3结尾的任何值
WHERE FEES GLOB '2???3'	查找以2开头并以3结尾的五位数字的任何值

例1：

从student表中选择fees以2开头所有记录：

SELECT * FROM student WHERE fees  GLOB '2*';
SQL

执行上面代码，输出结果如下 -

例2：

从student表中选择address包含an字符所有记录：

SELECT * FROM student WHERE address  GLOB '*an*';
SQL

执行上面代码，输出结果如下 -

SQLite LIMIT子句

SQLite LIMIT子句用于限制通过SELECT命令从表中获取的记录的数量。

语法：

SELECT column1, column2, columnN   
FROM table_name  
LIMIT [no of rows]
SQL

LIMIT子句也可以与OFFSET子句一起使用。

SELECT column1, column2, columnN   
FROM table_name  
LIMIT [no of rows] OFFSET [row num]
SQL

示例：

下面举个例子来演示SQLite LIMIT子句的用法。 假设有一个名为“student”的表，并具有以下数据：

sqlite> SELECT * FROM student;
1|Maxsu|27|Shengzheng|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
SQL

示例1：

通过使用LIMIT子名，根据需要的行数从student表中返回指定记录数量。例如，以下语句只返回3条记录 -

SELECT * FROM STUDENT LIMIT 3;
SQL

执行上面语句，得到以下结果 -

示例2：

OFFSET用于不从表中检索偏移记录。 在某些情况下，必须从某一点开始检索记录：

从student表中的第2位开始选择2条记录。

SELECT * FROM STUDENT LIMIT 2 OFFSET 1;
SQL

执行上面语句，得到以下结果 -

SQLite ORDER BY子句

SQLite ORDER BY子句用于根据一个或多个列对所获取的数据按升序或降序进行排序(排序)。

语法

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

可以在ORDER BY子句中使用一个或多个列。所使用的列必须在列的列表中显示。

下面举个例子来演示如何使用ORDER BY子句。有一个名为student的表，具有以下数据：

示例1：

从student表中选择所有记录，按fees字段升序排序：

SELECT * FROM student ORDER BY fees ASC;
SQL

执行上面语句，得到以下结果 -

示例2：

从student表获取所有数据，并按ADDRESS和FEES对结果进行降序排序：

SELECT * FROM student ORDER BY address, fees DESC;
SQL

执行上面语句，得到以下结果

注意：address值为Shenzheng的记录的fees字段的排序顺序。

SQLite GROUP BY子句

SQLite GROUP BY子句与SELECT语句一起使用，将相同的相同元素合并成一个组。

GROUP BY子句与SELECT语句中的WHERE子句一起使用，并且WHERE子句在ORDER BY子句之前。

语法：

SELECT column-list  
FROM table_name  
WHERE [ conditions ]  
GROUP BY column1, column2....columnN  
ORDER BY column1, column2....columnN
SQL

下面举个例子来说明如何使用GROUP BY子句。 假设有一个名为student的表，具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
sqlite>
SQL

使用GROUP BY查询每位学生的费用总额：

SELECT NAME, SUM(FEES) FROM STUDENT GROUP BY NAME;
SQL

执行上面代码，得到以下结果 -

现在，使用以下INSERT语句向student表中创建一些记录，为了更好演示，插入的部分列的数据值是相同的：

INSERT INTO STUDENT VALUES (7, 'Linsu', 27, 'Haikou', 10000.00 );  
INSERT INTO STUDENT VALUES (8, 'Minsu', 23, 'Guangzhou', 5000.00 );  
INSERT INTO STUDENT VALUES (9, 'Maxsu', 23, 'Shenzhen', 9000.00 );
SQL

执行上面语句插入数据后，现在表中存在的数据如下 -

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
Shell

如上所示，现在有几个字段：name,age和city中的值是相同的。

现在，使用GROUP BY语句按NAME列来分组并对同分组内的所有的记录的fees列求和：

 select name, sum(fees) from student group by name;
SQL

执行上面代码，得到以下结果 -

可以使用ORDER BY子句和GROUP BY按升序或降序排列数据。

SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY NAME DESC;
-- 或者
SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY total_fees DESC;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY NAME DESC;
Sqlsu|25000.0
Minsu|20000.0
Maxsu|29000.0
Linsu|75000.0
Javasu|18000.0
Avgsu|2000.0
sqlite>
sqlite> SELECT NAME, SUM(FEES) AS total_fees FROM STUDENT GROUP BY NAME ORDER BY total_fees DESC;
Linsu|75000.0
Maxsu|29000.0
Sqlsu|25000.0
Minsu|20000.0
Javasu|18000.0
Avgsu|2000.0
sqlite>

SQLite HAVING子句

SQLite HAVING子句用于指定过滤分组的结果，并作为最终查询结果的条件。 WHERE子句将条件放在选定的列上，而HAVING子句指定的条件是由GROUP BY子句创建的列分组上(使用HAVING子句条件一定要作用在由GROUP BY子句指定列上)。

SELECT查询中HAVING子句的位置：

SELECT  
FROM  
WHERE  
GROUP BY  
HAVING  
ORDER BY
SQL

语法

SELECT column1, column2  
FROM table1, table2  
WHERE [ conditions ]  
GROUP BY column1, column2  
HAVING [ conditions ]  
ORDER BY column1, column2
SQL

下面举个例子来演示如何使用HAVING子句。假设有一个名为student的表，并具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
SQL

示例1：

下面查询name的数量小于2的所有记录，在查询之前，先来查询看看每个名字的数量 -

-- 名字的数量
SELECT name, count(name) as total_number FROM student GROUP BY name;

-- `name`的数量小于`2`的所有记录
SELECT name, count(name) as total_number FROM student GROUP BY NAME HAVING COUNT(NAME) < 2;
SQL

执行上面语句，得到结果如下 -

-- 所有记录
sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
sqlite> 
-- 每个名字的数量
sqlite> SELECT name, count(name) as total_number FROM student GROUP BY name;
Avgsu|1
Javasu|1
Linsu|2
Maxsu|2
Minsu|2
Sqlsu|1
-- 查询数量小于2的名字
sqlite> SELECT name, count(name) as total_number FROM student GROUP BY NAME HAVING total_number < 2;
Avgsu|1
Javasu|1
Sqlsu|1
sqlite>
SQL

示例2：

下面查询address的数量大于等于2的所有记录，在查询之前，先来查询看看每个地址的数量 -

sqlite> SELECT address, count(address) as total_number FROM student GROUP BY address ;
Beijing|1
Guangzhou|2
Haikou|2
Shanghai|1
Shengzhen|2
Shenzhen|1
sqlite> SELECT address, count(address) as total_number FROM student GROUP BY address HAVING total_number >= 2;
Guangzhou|2
Haikou|2
Shengzhen|2
sqlite>

SQLite DISTINCT子句

SQLite DISTINCT子句与SELECT语句一起使用，用来消除所有重复记录，并仅获取唯一记录。

当在表中有多个重复记录时可使用它来过滤重复的记录。

语法：

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

示例：

假设有一个名为student的表，具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
Shell

首先，从student表中选择NAME，但是先不使用DISTINCT关键字。 它将显示重复的记录：

sqlite> select id, name from student;
1|Maxsu
2|Minsu
3|Avgsu
4|Linsu
5|Sqlsu
6|Javasu
7|Linsu
8|Minsu
9|Maxsu
sqlite>
Shell

如上面结果中，有几个名字是重复的，比如：Linsu, Minsu 和 Maxsu 都是有一个以上的名字。

现在，使用DISTINCT关键字从STUDENT表中选择NAME字段，并过滤掉重复的名字。

SELECT DISTINCT NAME FROM STUDENT;
SQL

执行上面语句，得到以下结果

作为一个练习：可以自行在adress上使用DISTINCT关键字过滤重复数据。

SQLite Union操作符

SQLite UNION运算符用于使用SELECT语句组合两个或多个表的结果集。 UNION操作符仅显示唯一的行(删除重复的行)。

在使用UNION运算符时，每个SELECT语句必须在结果集中具有相同数量的字段。

语法：

SELECT expression1, expression2, ... expression_n  
FROM tables  
[WHERE conditions]  
UNION  
SELECT expression1, expression2, ... expression_n  
FROM tables  
[WHERE conditions];
SQL

假设有两个表：student和department。

sqlite> .tables
department  student
sqlite>
SQL

student表中具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
Shell

department表中具有以下数据：

sqlite> select * from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
SQL

示例1：

使用union操作符返回单个字段 -

SELECT ID FROM STUDENT  
UNION  
SELECT ID FROM DEPARTMENT;
SQL

执行上面代码，得到以下结果

示例2：

联合内部和外部连接，按照以下条件和UNION子句，将上述两个表：student和department作为内部联接和外部联接。

SELECT EMP_ID, NAME, DEPT FROM STUDENT JOIN DEPARTMENT  
ON STUDENT.ID = DEPARTMENT.EMP_ID  
UNION  
SELECT EMP_ID, NAME, DEPT FROM STUDENT LEFT OUTER JOIN DEPARTMENT  
ON STUDENT.ID = DEPARTMENT.EMP_ID;
SQL

执行上面代码，得到以下结果

SQLite Union All操作符

SQLite UNION ALL运算符用于组合两个或多个SELECT语句的结果，但是不忽略重复的行(含有重复的行记录)。

在SQLite UNION ALL中，查询结果表还包括重复值。 其它规则适用于Union操作符。

语法：

SELECT expression1, expression2, ... expression_n  
FROM tables  
[WHERE conditions]  
UNION ALL  
SELECT expression1, expression2, ... expression_n  
FROM tables  
[WHERE conditions];
SQL

假设有两个表：student和department。

sqlite> .tables
department  student
sqlite>
SQL

student表中具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
Shell

department表中具有以下数据：

sqlite> select * from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
SQL

示例1：

使用union all操作符返回单个字段，这个简单示例只返回来自两个字段具有相同数据类型的多个SELECT语句中的一个字段。
下面来看看上面的两个表：student和department，并在UNION ALL操作符从两个表中选择一个id。

SELECT ID FROM STUDENT  
UNION ALL   
SELECT ID FROM DEPARTMENT;
SQL

执行上面代码，得到以下结果 -

示例2：

UNION ALL内部和外部连接，按照以下条件和UNION ALL子句，将上述两个表：student和department作为内部联接和外部联接。

SELECT EMP_ID, NAME, DEPT FROM STUDENT INNER JOIN DEPARTMENT  
ON STUDENT.ID = DEPARTMENT.EMP_ID  
UNION ALL  
SELECT EMP_ID, NAME, DEPT FROM STUDENT LEFT OUTER JOIN DEPARTMENT  
ON STUDENT.ID = DEPARTMENT.EMP_ID;
SQL

执行上面代码，得到以下结果 -

SQLite IN运算符

SQLite IN运算符用于确定值是否匹配列表或子查询中的任何值。 IN运算符的语法如下：

expression [NOT] IN (value_list|subquery);
SQL

expression可以是任何有效的表达式。 它可以是表中的某一列。

值的列表(value_list)是固定值列表或子查询返回的一列的结果集。表的返回类型和列表中的值必须相同。

IN运算符根据表达式是否匹配值列表中的任何值，返回true或false。要查询非列表中的值匹配，请使用NOT IN运算符。

SQLite IN运算符示例

假设有一个名为STUDENT的表，并具有以下数据：

sqlite> SELECT * FROM STUDENT ;
1|Maxsu|27|Shengzheng|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Hainan|25000.0
6|Javasu|21|Shengzheng|18000.0
sqlite>
Shell

若要查询ID为1，3，5的学生信息，可参考以下语句 -

SELECT ID,AGE,NAME,ADDRESS FROM student WHERE ID IN(1,3,5);
SQL

执行上面查询语句，得到以下结果 -

上面的查询语句，与下面的OR条件语句效果一样 -

SELECT ID,AGE,NAME,ADDRESS FROM student WHERE ID = 1 OR ID=3 OR ID=5;
SQL

IN语句和子查询

假设有一个名称为：department的表，记录每个学生的所在的部门。

sqlite> select id ,dept,emp_id from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
SQL

其中，emp_id字段引用student表的ID字段，现在查询每个分配了部门的学生的信息，参考以下语句 -

SELECT ID,AGE,NAME,ADDRESS FROM student WHERE ID IN (
    SELECT emp_id FROM  department );
SQL

执行上面语句，得到如下结果 -

sqlite> select id ,dept,emp_id from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
sqlite> SELECT ID,AGE,NAME,ADDRESS FROM student WHERE ID IN (
   ...>     SELECT emp_id FROM  department );
1|27|Maxsu|Shengzhen
2|25|Minsu|Beijing
3|23|Avgsu|Shanghai
4|25|Linsu|Guangzhou
5|26|Sqlsu|Haikou
sqlite>

SQLite NOT IN示例

查询那些ID不是1，3，5学生的信息，参考以下语句 -

SELECT ID,AGE,NAME,ADDRESS FROM student WHERE ID NOT IN(1,3,5);
SQL

查询那些未分配部门的学生的信息，参考以下语句 -

SELECT ID,AGE,NAME,ADDRESS FROM student WHERE ID NOT IN (
    SELECT emp_id FROM  department );
SQL

执行上面查询语句，得到以下结果 -

连接操作

SQLite连接（JOIN子句）

在SQLite中，JOIN子句用于组合数据库中两个或多个表的记录。 它通过使用两个表的公共值来组合来自两个表的字段。

SQLite中主要有三种类型的连接：

• SQLite内连接
• SQLite外连接
• SQLite交叉连接

示例

假设有两个表：department和student。

student表具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
SQL

department表具有以下数据：

sqlite> select * from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>

SQLite内连接（Inner Join）

SQLite内连接(inner join)是最常见的连接类型。 它用于组合满足连接条件的多个表中的所有行记录。

SQlite内连接是默认的连接类型。

语法：

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
SQL

或者：

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

或者：

SELECT ... FROM table1 NATURAL JOIN table2...
SQL

内连接如下图所表示，蓝色阴影部分为内连接的交集 -

示例

假设有两个表：department和student。

student表具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
SQL

department表具有以下数据：

sqlite> select * from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
SQL

示例：

查询每个学生所在的部门，如下查询语句 -

SELECT EMP_ID, NAME, DEPT FROM STUDENT INNER JOIN DEPARTMENT  
 ON STUDENT.ID = DEPARTMENT.EMP_ID;
SQL

执行上面查询，得到以下结果 -

注：没有指定部门的学生并不会查询出来。因为这里是基于条件 STUDENT.ID = DEPARTMENT.EMP_ID 来查询的。

SQLite外连接（Outer Join）

在SQL标准中，有三种类型的外连接：

• 左外连接
• 右外连接
• 全外连接

但是，SQLite仅支持左外连接。

SQlite的左外连接

SQLite左外连接用于从ON条件中指定的左侧表中获取所有行，并且仅右表中满足连接条件的那些行记录。

语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression
SQL

或者：

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,......

左外连接如下图所表示，蓝色阴影部分为左外连接的交集 -

示例

假设有两个表：department和student。

student表具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
SQL

department表具有以下数据：

sqlite> select * from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
SQL

示例：

查询每个学生所在的部门，并按照以下条件进行左外连接，如下查询语句 -

SELECT EMP_ID, NAME, DEPT FROM STUDENT LEFT OUTER JOIN DEPARTMENT  
ON STUDENT.ID = DEPARTMENT.EMP_ID;
SQL

执行上面查询，得到以下结果 -

注：没有指定部门的学生也会查询出来。因为左连接是基于左表，不管右表条件是否满足。

SQLite交叉连接（Cross Join）

SQLite 交叉连接用于将第一个表的每一行与第二个表的每一行进行匹配。 如果第一个表包含x列，而第二个表包含y列，则所得到的交叉连接表的结果将包含x * y列。

语法：

SELECT ... FROM table1 CROSS JOIN table2
SQL

交叉连接如下图所表示 -

示例

假设有两个表：department和student。

student表具有以下数据：

sqlite> select * from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
SQL

department表具有以下数据：

sqlite> select * from department;
1|财务部|1
2|技术部|2
3|技术部|3
4|市场部|4
5|市场部|5
sqlite>
SQL

示例：

在交叉连接后从表department和student中选择所有记录，如下查询语句 -

SELECT * FROM student CROSS JOIN DEPARTMENT;
SQL

执行上面查询，得到以下结果 -

sqlite> SELECT * FROM student CROSS JOIN DEPARTMENT;
1|Maxsu|27|Shengzhen|20000.0|1|财务部|1
1|Maxsu|27|Shengzhen|20000.0|2|技术部|2
1|Maxsu|27|Shengzhen|20000.0|3|技术部|3
1|Maxsu|27|Shengzhen|20000.0|4|市场部|4
1|Maxsu|27|Shengzhen|20000.0|5|市场部|5
2|Minsu|25|Beijing|15000.0|1|财务部|1
2|Minsu|25|Beijing|15000.0|2|技术部|2
2|Minsu|25|Beijing|15000.0|3|技术部|3
2|Minsu|25|Beijing|15000.0|4|市场部|4
2|Minsu|25|Beijing|15000.0|5|市场部|5
3|Avgsu|23|Shanghai|2000.0|1|财务部|1
3|Avgsu|23|Shanghai|2000.0|2|技术部|2
3|Avgsu|23|Shanghai|2000.0|3|技术部|3
3|Avgsu|23|Shanghai|2000.0|4|市场部|4
3|Avgsu|23|Shanghai|2000.0|5|市场部|5
4|Linsu|25|Guangzhou|65000.0|1|财务部|1
4|Linsu|25|Guangzhou|65000.0|2|技术部|2
4|Linsu|25|Guangzhou|65000.0|3|技术部|3
4|Linsu|25|Guangzhou|65000.0|4|市场部|4
4|Linsu|25|Guangzhou|65000.0|5|市场部|5
5|Sqlsu|26|Haikou|25000.0|1|财务部|1
5|Sqlsu|26|Haikou|25000.0|2|技术部|2
5|Sqlsu|26|Haikou|25000.0|3|技术部|3
5|Sqlsu|26|Haikou|25000.0|4|市场部|4
5|Sqlsu|26|Haikou|25000.0|5|市场部|5
6|Javasu|21|Shengzhen|18000.0|1|财务部|1
6|Javasu|21|Shengzhen|18000.0|2|技术部|2
6|Javasu|21|Shengzhen|18000.0|3|技术部|3
6|Javasu|21|Shengzhen|18000.0|4|市场部|4
6|Javasu|21|Shengzhen|18000.0|5|市场部|5
7|Linsu|27|Haikou|10000.0|1|财务部|1
7|Linsu|27|Haikou|10000.0|2|技术部|2
7|Linsu|27|Haikou|10000.0|3|技术部|3
7|Linsu|27|Haikou|10000.0|4|市场部|4
7|Linsu|27|Haikou|10000.0|5|市场部|5
8|Minsu|23|Guangzhou|5000.0|1|财务部|1
8|Minsu|23|Guangzhou|5000.0|2|技术部|2
8|Minsu|23|Guangzhou|5000.0|3|技术部|3
8|Minsu|23|Guangzhou|5000.0|4|市场部|4
8|Minsu|23|Guangzhou|5000.0|5|市场部|5
9|Maxsu|23|Shenzhen|9000.0|1|财务部|1
9|Maxsu|23|Shenzhen|9000.0|2|技术部|2
9|Maxsu|23|Shenzhen|9000.0|3|技术部|3
9|Maxsu|23|Shenzhen|9000.0|4|市场部|4
9|Maxsu|23|Shenzhen|9000.0|5|市场部|5
sqlite>

SQLite左连接

类似于INNER JOIN子句，LEFT JOIN子句是SELECT语句的可选子句。可以使用LEFT JOIN子句来查询来自多个相关表的数据。

假设有两个表：A和B

• A表有m和f字段。
• B表有n和f字段。

要使用LEFT JOIN子句执行A和B之间的连接，请使用以下语句：

SELECT
 m, n
FROM A
LEFT JOIN B ON A.f = B.f
WHERE search_condition;
SQL

表达式A.f = B.f是条件表达式。 除了等于(=)运算符之外，还可以使用大于(>)，小于(<)等的其他比较运算符。

该语句返回一个结果集，其中包含：

• A表中的行(左表)在B表中具有相应的行。
• A表中的行存在，但在B表中不存的行使用NULL值填充。

换句话说，A表中的所有行都包含在结果集中，无论B表中是否有匹配的行，如果B表中不匹配则使用NULL值填充。

如果语句中有WHERE子句，那么在LEFT JOIN子句的匹配完成之后，WHERE子句中的search_condition将被应用。

请参考A表和B表之间的LEFT JOIN子句，如下图所示 -

A表中的所有行都包含在结果集中。

因为第二行(a2,2)在B表中没有相应的行，所以LEFT JOIN子句创建一个填充有NULL值的假行。

以下图说明了LEFT JOIN子句，黄色部分为最终查询结果集 -

假设有两个表：class 和 student，分别表示班级和学生。其数据如下 -

sqlite> select id,class_name,student_id from class;
1|网络工程-001班|1
2|网络工程-002班|2
3|网络工程-003班|3
4|网络工程-004班|4
sqlite>
sqlite> select id,name,age,address,fees from student;
1|Maxsu|27|Shengzhen|20000.0
2|Minsu|25|Beijing|15000.0
3|Avgsu|23|Shanghai|2000.0
4|Linsu|25|Guangzhou|65000.0
5|Sqlsu|26|Haikou|25000.0
6|Javasu|21|Shengzhen|18000.0
7|Linsu|27|Haikou|10000.0
8|Minsu|23|Guangzhou|5000.0
9|Maxsu|23|Shenzhen|9000.0
sqlite>
SQL

现在使用 left join查询每个学生所在的班级信息 -

select name,age,address,fees,class_name from student left join class on student.id=class.student_id where student.id > 0;
SQL

执行上面语句，得到以下结果 -

sqlite>  select student.id,name,age,address,fees,class_name from student left join class on student.id=class.student_id where student.id > 0;
1|Maxsu|27|Shengzhen|20000.0|网络工程-001班
2|Minsu|25|Beijing|15000.0|网络工程-002班
3|Avgsu|23|Shanghai|2000.0|网络工程-003班
4|Linsu|25|Guangzhou|65000.0|网络工程-004班
5|Sqlsu|26|Haikou|25000.0|
6|Javasu|21|Shengzhen|18000.0|
7|Linsu|27|Haikou|10000.0|
8|Minsu|23|Guangzhou|5000.0|
9|Maxsu|23|Shenzhen|9000.0|
sqlite>

SQLite时间日期

SQLite日期和时间

在SQLite中，date()和time()函数用于检索当前日期和时间。日期和时间函数使用IS0-8601日期和时间格式的子集。

在SQLite中有6种不同的日期和时间函数返回，并以不同格式进行日期和时间的计算：

• SQLite date()函数
• SQLite datetime()函数
• SQLite julianday()函数
• SQLite now()函数
• SQLite strftime()函数
• SQLite time()函数

编号	函数	描述
1	date()函数	SQLite date()函数用于获取日期并以“YYYY-MM-DD”格式返回。
2	datetime()函数	SQLite datetime()函数用于计算日期/时间值，并以“YYYY-MM-DD HH:MM:SS”格式返回。
3	julianday()函数	SQLite julianday()函数根据儒略日返回日期。是公元前4714年11月24日以后的天数(在格林尼治时间)。它将日期作为浮点数返回。
4	now()函数	严格来说它不是一个函数。只是一个作为时间字符串参数用于在各种SQLite函数来检索当前的日期和时间。一些典型用法：date('now'),time('now')
5	strftime()函数	SQLite strftime()函数用于以格式化的方式返回日期，并且还有助于您在日期上进行计算。
6	time()函数	SQLite time()函数用于获取时间并以“HH-MM-SS”格式返回。

SQLite date()函数

SQLite date()函数用于检索日期并以“YYYY-MM-DD”格式返回。

语法

date(timestring, [ modifier1, modifier2, ... modifier_n ] )
SQL

这里，timestring是一个日期值，可以是以下任何一个：

编号	timestring的值	描述
1	now	用来返回当前日期的字面值
2	YYYY-MM-DD	指定格式为YYYY-MM-DD的日期值
3	YYYY-MM-DD HH:MM	指定格式为YYYY-MM-DD HH:MM的日期值
4	YYYY-MM-DD HH:MM:SS	指定格式为YYYY-MM-DD HH:MM:SS的日期值
5	YYYY-MM-DD HH:MM:SS.SSS	指定格式为YYYY-MM-DD HH:MM:SS.SSS的日期值
6	HH:MM	指定格式为HH:MM的日期值
7	HH:MM:SS	指定格式为HH:MM:SS的日期值
8	HH:MM:SS.SSS	指定格式为HH:MM:SS.SSS的日期值
9	YYYY-MM-DDTHH:MM	指定格式化为YYYY-MM-DDTHH：MM的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
10	YYYY-MM-DDTHH:MM:SS	指定格式化为YYYY-MM-DDTHH:MM:SS的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
11	YYYY-MM-DDTHH:MM:SS.SSS	指定格式化为YYYY-MM-DDTHH:MM:SS.SSS的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
12	DDDDDDDDDD	指定儒略日的日期数

• modifier1, modifier2, … modifier_n: 这些修饰符是可选的。这些字符与时间字符串一起使用来添加或减少时间，日期或年份。

编号	修辞符	描述
1	[+-]NNN years	用于指定添加/减去日期的年数
2	[+-]NNN months	用于指定添加/减去日期的月数
3	[+-]NNN days	用于指定添加/减去日期的天数
4	[+-]NNN hours	用于指定添加/减去日期的小时数
5	[+-]NNN minutes	用于指定添加/减去日期的分钟数
6	[+-]NNN seconds	用于指定添加/减去日期的秒数
7	[+-]NNN.NNNN seconds	用于指定添加/减去日期的秒数(和小数秒)
8	start of year	用于将日期重新转移到年初
9	start of month	用于将日期重新转移到月初
10	start of day	用于将日期重新移动到一天的开始
11	weekday N	用于将日期向前移动到工作日数为N的下一个日期(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
12	unixepoch	它与DDDDDDDDDD时间字符串一起用于将日期解释为UNIX时间(即：自1970-01-01以来的秒数)
13	localtime	用于将日期调整为本地时间，假设时间戳以UTC表示
14	utc	它用于将日期调整为utc，假设时间戳以本地时间表达

示例1：

获取当前日期：

sqlite> SELECT date('now');
2017-05-23
sqlite>
SQL

示例2：

检索本月的第一天，有四种方法可以查询一个月的第一天：

SELECT date('2017-12-17', 'start of month');  
SELECT date('now', 'start of month');  
SELECT date('2019-10-16', '-15 days');   
SELECT date('now', '-23 days');
SQL

执行上面查询，得到以下结果

示例3：

检索本月的最后一天，date()函数可用于检索月份的最后一天。有四种方法可以获取一个月的最后一天：

SELECT date('2019-04-13', 'start of month','+1 month', '-1 day');  

SELECT date('now', 'start of month','+1 month', '-1 day');  

SELECT date('2018-04-13', '+17 days');  

SELECT date('now', '+8 days');
SQL

执行上面查询，得到以下结果 -

sqlite> SELECT date('2019-04-13', 'start of month','+1 month', '-1 day');
2019-04-30
sqlite>
sqlite> SELECT date('now', 'start of month','+1 month', '-1 day');
2017-05-31
sqlite>
sqlite> SELECT date('2018-04-13', '+17 days');
2018-04-30
sqlite>
sqlite> SELECT date('now', '+8 days');
2017-05-31
sqlite>
Shell

示例4：

在当前日期上添加/减去年份，在当前日期加上/减去5个年数：

SELECT date('now','+5 years');  

SELECT date('2018-06-13','+5 years');  

SELECT date('now','-5 years');  

SELECT date('2018-06-13','-5 years');
SQL

执行上面查询，得到以下结果

示例5：

将天数添加到当前日期，通过上述方式，可以在当前日期上添加和减去天数，有以下几种方法可以实现：

SELECT date('now','+5 days');  

SELECT date('2018-05-13','+5 days');  

SELECT date('now','-5 days');  

SELECT date('2018-06-13','-5 days');
SQL

执行上面查询，得到以下结果

SQLite datetime()函数

SQLite datetime()函数用于以不同的格式检索/查询日期和时间。 日期时间函数的结果格式为“YYYY-MM-DD HH:MM:SS” 。

语法

datetime(timestring, [ modifier1, modifier2, ... modifier_n ] )
SQL

示例1：

检索当前日期和时间：

sqlite> SELECT datetime('now');
2017-12-24 12:54:53
sqlite>
SQL

示例2：

添加/减去当前日期和时间的年数：

SELECT datetime('2017-08-13','+5 years');  
SELECT datetime('now','+5 years');
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT datetime('2017-08-13','+5 years');
2022-08-13 00:00:00
sqlite> SELECT datetime('now','+5 years');
2022-05-24 12:56:29
sqlite>
SQL

示例3：

在当前日期和时间上添加/减去天数：

SELECT datetime('2017-09-13','+6 days');  
SELECT datetime('now','+3 days');  
SELECT datetime('now','-5 days');
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT datetime('2017-08-13','+5 years');
2022-08-13 00:00:00
sqlite> SELECT datetime('now','+5 years');
2022-05-24 12:56:29
sqlite>
sqlite>
sqlite> SELECT datetime('2017-09-13','+6 days');
2017-09-19 00:00:00
sqlite> SELECT datetime('now','+3 days');
2017-05-27 14:10:14
sqlite> SELECT datetime('now','-5 days');
2017-05-19 14:10:14
sqlite>
SQL

示例4：

在当前日期和时间上添加/减去小时数：

SELECT datetime('2017-09-13','+5 hours');  
SELECT datetime('now','+3 hours');  
SELECT datetime('now','-5 hours');
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT datetime('2017-09-13','+5 hours');
2017-09-13 05:00:00
sqlite> SELECT datetime('now','+3 hours');
2017-05-24 17:12:36
sqlite> SELECT datetime('now','-5 hours');
2017-05-24 09:12:36
sqlite>
Shell

示例5：

在当前日期和时间上添加/减去分钟数：

SELECT datetime('now');  
SELECT datetime('now','+30 minutes');  
SELECT datetime('now','-30 minutes');
SQL

执行上面语句，得到以下结果 -

-- 当前时间
sqlite> SELECT datetime('now');
2017-05-24 14:15:45
sqlite> SELECT datetime('now','+30 minutes');
2017-05-24 14:45:45
sqlite> SELECT datetime('now','-30 minutes');
2017-05-24 13:45:46
sqlite>

SQLite juliandday()函数

SQLite julianday()函数应用修饰符，然后将日期作为输入日期后的儒略日(Julian day,JD)返回。

儒略日(Julian day，JD)是指由公元前4713年1月1日，协调世界时中午12时开始所经过的天数，多为天文学家采用，用以作为天文学的单一历法，把不同历法的年表统一起来。如果计算相隔若干年的两个日期之间间隔的天数，利用儒略日就比较方便。

语法：

julianday(timestring [, modifier1, modifier2, ... modifier_n ] )
SQL

示例1：

检索当前日期：

SELECT julianday('2017-09-13');  
SELECT julianday('2017-09-13 16:45');  
SELECT julianday('2017-09-13 16:45:30');  
SELECT julianday('now');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT julianday('2017-09-13');
2458009.5
sqlite> SELECT julianday('2017-09-13 16:45');
2458010.19791667
sqlite> SELECT julianday('2017-09-13 16:45:30');
2458010.19826389
sqlite> SELECT julianday('now');
2457898.09934488
sqlite>
Shell

示例2：

检索本月的第一天：

SELECT julianday('2017-07-13', 'start of month');  
SELECT julianday('now', 'start of month');  
SELECT julianday('2017-08-15', '-6 days');  
SELECT julianday('now', '-6 days');
SQL

执行上面查询语句，得到以下结果 -

sqlite> SELECT julianday('2017-07-13', 'start of month');
2457935.5
sqlite> SELECT julianday('now', 'start of month');
2457874.5
sqlite> SELECT julianday('2017-08-15', '-6 days');
2457974.5
sqlite> SELECT julianday('now', '-6 days');
2457892.10142766
sqlite>
Shell

示例3：

检索本月的最后一天：

SELECT julianday('2017-09-07', 'start of month', '+1 month', '-1 day');  
SELECT julianday('now', 'start of month', '+1 month', '-1 day');  
SELECT julianday('2017-09-07', '+24 days');  
SELECT julianday('now', '+24 days');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT julianday('2017-09-07', 'start of month', '+1 month', '-1 day'); 
2458026.5
sqlite> SELECT julianday('now', 'start of month', '+1 month', '-1 day');
2457904.5
sqlite> SELECT julianday('2017-09-07', '+24 days');
2458027.5
sqlite> SELECT julianday('now', '+24 days');
2457922.10232523
sqlite>
Shell

示例4：

在当前日期上添加/减去年数和天数：

SELECT julianday('2017-09-14', '+2 years');  
SELECT julianday('now', '+5 years');  
SELECT julianday('now', '-7 days');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT julianday('2017-09-14', '+2 years');
2458740.5
sqlite> SELECT julianday('now', '+5 years');
2459724.10358212
sqlite> SELECT julianday('now', '-7 days');
2457891.10358736
sqlite>

SQLite now函数和时间格式化

SQLite “now”实际上并不是一个函数，但是“now”是一个时间字符串参数，用于各种SQLite函数来获取当前的日期和时间。

语法：

SQLite中now函数有三种类型的语法：

date('now')
SQL

或者 -

time('now')
SQL

或者 -

strftime(format, 'now')  
-- 其它用法
strftime('%Y-%m-%d','now')  
strftime('%Y-%m-%d %H-%M','now')  
strftime('%Y-%m-%d %H-%M-%S','now')
SQL

使用strftime()函数表达当前日期/时间时，是使用第三种语法。 这里的“format”可以是以下任何一个：

编号	索引	解释/描述
1	%Y	4位数表示年份(0000至9999)
2	%W	表示一年之中的第几周(00至53)
3	%w	星期几(0到6，其中0表示星期日)
4	%m	表示一年之中的第几月(01至12)
5	%d	表示一个月之中的第几天(00至31)
6	%H	小时 (00 至 24)
7	%M	分钟 (00 至 60)
8	%S	秒(00至59)
9	%s	自1970-01-01以来的秒数
10	%f	小数秒(SS.SSS)
11	%j	一年之中的第几天(001 至 366)
12	%J	儒略日的数字值

示例-1：

检索当前日期：

SELECT date('now');  
SELECT strftime('%Y-%m-%d','now');
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT date('now');
2017-05-24
sqlite> SELECT strftime('%Y-%m-%d','now');
2017-05-24
sqlite>
SQL

示例-2：

检索当前时间：

SELECT strftime('%Y-%m-%d %H:%M:%S','now');
SELECT time('now'); -- (HH-MM-SS Format)  
SELECT strftime('%H-%M-%S','now'); --  (HH-MM-SS Format)   
SELECT strftime('%H-%M-%f','now'); -- (HH-MM-SS.SSS Format)   
SELECT strftime('%H-%M','now');  --(HH-MM Format)
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S','now');
2017-05-24 14:53:55
sqlite> SELECT time('now'); -- (HH-MM-SS Format)
14:53:55
sqlite> SELECT strftime('%H-%M-%S','now'); --  (HH-MM-SS Format)
14-53-55
sqlite> SELECT strftime('%H-%M-%f','now'); -- (HH-MM-SS.SSS Format)
14-53-55.214
sqlite> SELECT strftime('%H-%M','now');  --(HH-MM Format)
14-53
sqlite>

SQLite strftime()函数日期日间格式化

SQLite strftime()是一个功能非常强大的函数，可以用来获取日期和时间，并且还可以执行日期计算。

语法：

strftime(format, timestring [, modifier1, modifier2, ... modifier_n ] )
SQL

这里，format可以是以下任何一种：

编号	格式	解释/描述
1	%Y	4位数表示年份(0000至9999)
2	%W	表示一年之中的第几周(00至53)
3	%w	星期几(0到6，其中0表示星期日)
4	%m	表示一年之中的第几月(01至12)
5	%d	表示一个月之中的第几天(00至31)
6	%H	小时 (00 至 24)
7	%M	分钟 (00 至 60)
8	%S	秒(00至59)
9	%s	自1970-01-01以来的秒数
10	%f	小数秒(SS.SSS)
11	%j	一年之中的第几天(001 至 366)
12	%J	儒略日的数字值

这里，timestring是一个日期值，可以是以下任何一个：

编号	timestring的值	描述
1	now	用来返回当前日期的字面值
2	YYYY-MM-DD	指定格式为YYYY-MM-DD的日期值
3	YYYY-MM-DD HH:MM	指定格式为YYYY-MM-DD HH:MM的日期值
4	YYYY-MM-DD HH:MM:SS	指定格式为YYYY-MM-DD HH:MM:SS的日期值
5	YYYY-MM-DD HH:MM:SS.SSS	指定格式为YYYY-MM-DD HH:MM:SS.SSS的日期值
6	HH:MM	指定格式为HH:MM的日期值
7	HH:MM:SS	指定格式为HH:MM:SS的日期值
8	HH:MM:SS.SSS	指定格式为HH:MM:SS.SSS的日期值
9	YYYY-MM-DDTHH:MM	指定格式化为YYYY-MM-DDTHH：MM的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
10	YYYY-MM-DDTHH:MM:SS	指定格式化为YYYY-MM-DDTHH:MM:SS的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
11	YYYY-MM-DDTHH:MM:SS.SSS	指定格式化为YYYY-MM-DDTHH:MM:SS.SSS的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
12	DDDDDDDDDD	指定儒略日的日期数

• modifier1, modifier2, … modifier_n: 这些修饰符是可选的。这些字符与时间字符串一起使用来添加或减少时间，日期或年份。

编号	修辞符	描述
1	[+-]NNN years	用于指定添加/减去日期的年数
2	[+-]NNN months	用于指定添加/减去日期的月数
3	[+-]NNN days	用于指定添加/减去日期的天数
4	[+-]NNN hours	用于指定添加/减去日期的小时数
5	[+-]NNN minutes	用于指定添加/减去日期的分钟数
6	[+-]NNN seconds	用于指定添加/减去日期的秒数
7	[+-]NNN.NNNN seconds	用于指定添加/减去日期的秒数(和小数秒)
8	start of year	用于将日期重新转移到年初
9	start of month	用于将日期重新转移到月初
10	start of day	用于将日期重新移动到一天的开始
11	weekday N	用于将日期向前移动到工作日数为N的下一个日期(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
12	unixepoch	它与DDDDDDDDDD时间字符串一起用于将日期解释为UNIX时间(即：自1970-01-01以来的秒数)
13	localtime	用于将日期调整为本地时间，假设时间戳以UTC表示
14	utc	它用于将日期调整为utc，假设时间戳以本地时间表达

示例1：

检索当前日期：

SELECT strftime('%Y %m %d', 'now');  
SELECT strftime('%Y-%m-%d %H:%M', 'now');
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT strftime('%Y %m %d', 'now');
2017 05 24
sqlite> SELECT strftime('%Y-%m-%d %H:%M', 'now');
2017-05-24 19:04
sqlite>
Shell

示例2：

检索本月的第一天：

SELECT strftime('%Y-%m-%d', '2017-09-14', 'start of month');  
SELECT strftime('%Y-%m-%d', 'now', 'start of month');  
SELECT strftime('%Y-%m-%d', '2017-03-07', '-6 days');  
SELECT strftime('%Y-%m-%d', 'now', '-13 days');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT strftime('%Y-%m-%d', '2017-09-14', 'start of month');
2017-09-01
sqlite> SELECT strftime('%Y-%m-%d', 'now', 'start of month');
2017-05-01
sqlite> SELECT strftime('%Y-%m-%d', '2017-03-07', '-6 days');
2017-03-01
sqlite> SELECT strftime('%Y-%m-%d', 'now', '-13 days');
2017-05-11
sqlite>
SQL

示例3：

检索本月的最后一天：

SELECT strftime('%Y-%m-%d', '2017-12-07', 'start of month', '+1 month', '-1 day');   
SELECT strftime('%Y-%m-%d', 'now', 'start of month', '+1 month', '-1 day');  
SELECT strftime('%Y-%m-%d', '2017-08-07', '+24 days');  
SELECT strftime('%Y-%m-%d', 'now', '+24 days');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT strftime('%Y-%m-%d', '2017-12-07', 'start of month', '+1 month', '-1 day');
2017-12-31
sqlite> SELECT strftime('%Y-%m-%d', 'now', 'start of month', '+1 month', '-1 day');
2017-05-31
sqlite> SELECT strftime('%Y-%m-%d', '2017-08-07', '+24 days');
2017-08-31
sqlite> SELECT strftime('%Y-%m-%d', 'now', '+24 days');
2017-06-17
sqlite>
SQL

示例4：

在当前日期时间上添加/减去年数和天数：

SELECT strftime('%Y-%m-%d', '2017-11-14', '+2 years');  
SELECT strftime('%Y-%m-%d', 'now', '-2 years');  
SELECT strftime('%Y-%m-%d', '2017-10-14', '+7 days');  
SELECT strftime('%Y-%m-%d', 'now', '-10 days');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT strftime('%Y-%m-%d', '2017-11-14', '+2 years');
2019-11-14
sqlite> SELECT strftime('%Y-%m-%d', 'now', '-2 years');
2015-05-24
sqlite> SELECT strftime('%Y-%m-%d', '2017-10-14', '+7 days');
2017-10-21
sqlite> SELECT strftime('%Y-%m-%d', 'now', '-10 days');
2017-05-14
sqlite>

SQLite time()函数

SQLite time()函数用于计算“HH-MM-SS”格式的时间并返回时间。

语法：

time(timestring, [ modifier1, modifier2, ... modifier_n ] )
SQL

这里，timestring是一个日期值，可以是以下任何一个：

编号	timestring的值	描述
1	now	用来返回当前日期的字面值
2	YYYY-MM-DD	指定格式为YYYY-MM-DD的日期值
3	YYYY-MM-DD HH:MM	指定格式为YYYY-MM-DD HH:MM的日期值
4	YYYY-MM-DD HH:MM:SS	指定格式为YYYY-MM-DD HH:MM:SS的日期值
5	YYYY-MM-DD HH:MM:SS.SSS	指定格式为YYYY-MM-DD HH:MM:SS.SSS的日期值
6	HH:MM	指定格式为HH:MM的日期值
7	HH:MM:SS	指定格式为HH:MM:SS的日期值
8	HH:MM:SS.SSS	指定格式为HH:MM:SS.SSS的日期值
9	YYYY-MM-DDTHH:MM	指定格式化为YYYY-MM-DDTHH：MM的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
10	YYYY-MM-DDTHH:MM:SS	指定格式化为YYYY-MM-DDTHH:MM:SS的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
11	YYYY-MM-DDTHH:MM:SS.SSS	指定格式化为YYYY-MM-DDTHH:MM:SS.SSS的日期值，其中T是一个文本字符分隔符，用于分隔日期和时间。
12	DDDDDDDDDD	指定儒略日的日期数

• modifier1, modifier2, … modifier_n: 这些修饰符是可选的。这些字符与时间字符串一起使用来添加或减少时间，日期或年份。

编号	修辞符	描述
1	[+-]NNN years	用于指定添加/减去日期的年数
2	[+-]NNN months	用于指定添加/减去日期的月数
3	[+-]NNN days	用于指定添加/减去日期的天数
4	[+-]NNN hours	用于指定添加/减去日期的小时数
5	[+-]NNN minutes	用于指定添加/减去日期的分钟数
6	[+-]NNN seconds	用于指定添加/减去日期的秒数
7	[+-]NNN.NNNN seconds	用于指定添加/减去日期的秒数(和小数秒)
8	start of year	用于将日期重新转移到年初
9	start of month	用于将日期重新转移到月初
10	start of day	用于将日期重新移动到一天的开始
11	weekday N	用于将日期向前移动到工作日数为N的下一个日期(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
12	unixepoch	它与DDDDDDDDDD时间字符串一起用于将日期解释为UNIX时间(即：自1970-01-01以来的秒数)
13	localtime	用于将日期调整为本地时间，假设时间戳以UTC表示
14	utc	它用于将日期调整为utc，假设时间戳以本地时间表达

示例1：

检索当前时间值：

SELECT time('now');
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT time('now');
19:14:05
sqlite>
Shell

示例2：

在当前时间上添加/减去小时数和分钟数：

SELECT time('now');
SELECT time('11:23:02','-2 hours');  
SELECT time('now','+5 hours');  
SELECT time('11:15:02','+15 minutes');  
SELECT time('now','-30 minutes');
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT time('now');
15:15:33
sqlite> SELECT time('11:23:02','-2 hours');
09:23:02
sqlite> SELECT time('now','+5 hours');
20:15:33
sqlite> SELECT time('11:15:02','+15 minutes');
11:30:02
sqlite> SELECT time('now','-30 minutes');
14:45:34
sqlite>

聚合函数

SQLite聚合函数是将多行的值组合在一起作为某些条件的输入并形成单个值作为输出结果的函数。 以下是SQLite中的一些聚合函数的列表：

• SQLite MIN()函数
• SQLite MAX()函数
• SQLite AVG()函数
• SQLite COUNT()函数
• SQLite SUM()函数
• SQLite RANDOM()函数
• SQLite ABS()函数
• SQLite UPPER()函数
• SQLite LOWER()函数
• SQLite LENGTH()函数
• SQLite sqlite_version()函数

序号	函数	描述说明
1	MIN()函数	SQLite MIN()函数用于查询某列的最低(最小)值。
2	MAX()函数	SQLite MAX()函数用于查询某列的最高(最大)值。
3	AVG()函数	SQLite MAX()函数用于查询某列的平均值。
4	COUNT()函数	SQLite COUNT()函数用于计算数据库表中的行数。
5	SUM()函数	SQLite SUM()函数用于查询指定数字列的总数(相加的总和)。
6	RANDOM()函数	SQLite RANDOM()函数返回-9223372036854775808与+9223372036854775807之间的伪随机整数。
7	ABS()函数	SQLite ABS()函数用于获取给定参数的绝对值。
8	UPPER()函数	SQLite UPPER()函数用于将给定字符串参数转换为大写字母。
9	LOWER()函数	SQLite LOWER()函数用于将给定字符串参数转换为小写字母。
10	LENGTH()函数	SQLite LENGTH()函数用于获取给定字符串的长度。
11	sqlite_version()函数	SQLite sqlite_version()函数用于获取SQLite库的版本。

SQLite MIN()函数

SQLite MIN()函数用于获取表达式或给定列的最小值。

语法

SELECT MIN(aggregate_expression)  
FROM tables  
[WHERE conditions];
SQL

在GROUP BY子句中使用Min()函数时的语法：

SELECT expression1, expression2, ... expression_n  
MIN(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;
SQL

示例1：

假设有一个名为student的表，具有以下数据：

从student表中检索学生的最低费用(fees)：

SELECT MIN(FEES) AS "Lowest Fees"  FROM STUDENT; 

-- 最小年龄
SELECT MIN(age) AS "Lowest age"  FROM STUDENT;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT MIN(FEES) AS "Lowest Fees"  FROM STUDENT;
2000.0
sqlite>
sqlite> SELECT MIN(FEES) AS "Lowest Fees"  FROM STUDENT;
2000.0
sqlite>
sqlite> -- 最小年龄
sqlite> SELECT MIN(age) AS "Lowest age"  FROM STUDENT;
21
sqlite>
SQL

示例2：

使用具有的GROUP BY子句的MIN()函数：

从student表中检索NAME和MIN FEES，并按NAME的数据排序：

SELECT NAME, MIN(FEES) AS "Lowest Fees"  
FROM STUDENT  
WHERE ID <= 8  
GROUP BY NAME;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, MIN(FEES) AS "Lowest Fees"
   ...> FROM STUDENT
   ...> WHERE ID <= 8
   ...> GROUP BY NAME;
Avgsu|2000.0
Javasu|18000.0
Linsu|10000.0
Maxsu|20000.0
Minsu|5000.0
Sqlsu|25000.0
sqlite>

SQLite MAX()函数

SQLite MAX()函数用于获取表达式或给定列的最大值。

语法

SELECT MAX(aggregate_expression)  
FROM tables  
[WHERE conditions];
SQL

在GROUP BY子句中使用Max()函数时的语法：

SELECT expression1, expression2, ... expression_n  
MAX(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;
SQL

示例1：

假设有一个名为student的表，具有以下数据：

从student表中检索学生的最高费用(fees)：

SELECT MAX(FEES) AS "Highest Fees"  FROM STUDENT; 

-- 最大年龄
SELECT MAX(age) AS "Highest age"  FROM STUDENT;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT MAX(FEES) AS "Highest Fees"  FROM STUDENT;
65000.0
sqlite>
sqlite> -- 最大年龄
sqlite> SELECT MAX(age) AS "Highest age"  FROM STUDENT;
27
sqlite>
SQL

示例2：

使用具有的GROUP BY子句的MAX()函数：

从student表中检索NAME,ADDRESS和MAX(FEES)，并按ADDRESS的数据排序：

SELECT NAME, ADDRESS, MAX(FEES) AS "Highest FEES"  
FROM STUDENT  
WHERE ID <= 8  
GROUP BY ADDRESS;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, ADDRESS, MAX(FEES) AS "Highest FEES"
   ...> FROM STUDENT
   ...> WHERE ID <= 8
   ...> GROUP BY ADDRESS;
Minsu|Beijing|15000.0
Linsu|Guangzhou|65000.0
Sqlsu|Haikou|25000.0
Avgsu|Shanghai|2000.0
Maxsu|Shengzhen|20000.0
sqlite>

SQLite AVG()函数

SQLite AVG()函数用于检索表达式或给定列的平均值。

语法

SELECT AVG(aggregate_expression)  
FROM tables  
[WHERE conditions];
SQL

在GROUP BY子句中使用AVG()函数时的语法：

SELECT expression1, expression2, ... expression_n  
AVG(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;
SQL

示例1：

假设有一个名为student的表，具有以下数据：

从student表中检索学生的平均费用(fees)：

SELECT AVG(FEES) AS "Avg Fees"  
FROM STUDENT  
WHERE ID > 0;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT AVG(FEES) AS "Avg Fees"
   ...> FROM STUDENT
   ...> WHERE ID > 0;
18777.7777777778
sqlite>
SQL

示例2：

使用带有DISTINCT子句的AVG()函数，从student表中获取FEES大于10000并且平均不同费用。

SELECT AVG(DISTINCT  FEES) AS "Avg Fees"  
FROM STUDENT  
WHERE FEES > 10000;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT AVG(DISTINCT  FEES) AS "Avg Fees"
   ...> FROM STUDENT
   ...> WHERE FEES > 10000;
28600.0
sqlite>
SQL

示例3：

使用数学公式在AVG()函数中计算求平均值。

可以使用数学公式根据您的要求检索平均值，如下求每个月的平均值 -

SELECT AVG(FEES / 12) AS "Average Monthly Fees"  
FROM STUDENT;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT AVG(FEES / 12) AS "Average Monthly Fees"
   ...> FROM STUDENT;
1564.81481481481
sqlite>
Shell

示例4：

使用具有的GROUP BY子句的AVG()函数：

从student表中检索NAME和FEES，并按AGE分组数据：

SELECT NAME, SUM(FEES) AS "Avg Fees by Name"  
FROM STUDENT  
GROUP BY AGE;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, SUM(FEES) AS "Avg Fees by Name"
   ...> FROM STUDENT
   ...> GROUP BY AGE;
Javasu|18000.0
Maxsu|16000.0
Linsu|80000.0
Sqlsu|25000.0
Linsu|30000.0
sqlite>

SQLite COUNT()函数

SQLite COUNT()函数用于检索表达式或给定列的行数。

语法

SELECT COUNT(aggregate_expression)  
FROM tables  
[WHERE conditions];
SQL

在GROUP BY子句中使用COUNT()函数时的语法：

SELECT expression1, expression2, ... expression_n  
COUNT(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;
SQL

示例1：

假设有一个名为student的表，具有以下数据：

从student表中检索AGE大于22的学生人数：

SELECT COUNT(*) AS "Number of students"  
FROM STUDENT  
WHERE AGE > 22;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT COUNT(*) AS "Number of students"
   ...> FROM STUDENT
   ...> WHERE AGE > 22;
8
sqlite>
SQL

示例2：

计算AGE大于22岁的学生人数，并按学生名字分组。

SELECT NAME, COUNT(*) AS "Number Of Students"  
FROM STUDENT  
WHERE AGE > 22  
GROUP BY NAME;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, COUNT(*) AS "Number Of Students"
   ...> FROM STUDENT
   ...> WHERE AGE > 22
   ...> GROUP BY NAME;
Avgsu|1
Linsu|2
Maxsu|2
Minsu|2
Sqlsu|1
sqlite>
SQL

示例3：

计算每个地址的学生总人数，按学生地址(Address)分组。

SELECT ADDRESS, COUNT(*) AS "Number Of Students"  
FROM STUDENT  
GROUP BY ADDRESS;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT ADDRESS, COUNT(*) AS "Number Of Students"
   ...> FROM STUDENT
   ...> GROUP BY ADDRESS;
Beijing|1
Guangzhou|2
Haikou|2
Shanghai|1
Shengzhen|2
Shenzhen|1
sqlite>

SQLite SUM()函数

SQLite SUM()函数用于返回表达式或给定数字列的总和。

语法

SELECT SUM(aggregate_expression)  
FROM tables  
[WHERE conditions];
SQL

在SUM()函数中使用GROUP BY子句时的语法：

SELECT expression1, expression2, ... expression_n  
SUM(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;
SQL

示例1：

假设有一个名为student的表，具有以下数据：

从student表中检索AGE小于24的学生总费用：

SELECT SUM(FEES) AS "Total Fees"  
FROM STUDENT  
WHERE AGE < 24;
SQL

执行上面查询代码，得到以下结果 -

sqlite> SELECT SUM(FEES) AS "Total Fees"
   ...> FROM STUDENT
   ...> WHERE AGE < 24;
34000.0
sqlite>
SQL

示例2：

使用具有数学公式的SUM()函数，求每个月的学生总费用 -

SELECT SUM(FEES / 12) AS "Total Monthly Fees"  
FROM STUDENT;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT SUM(FEES / 12) AS "Total Monthly Fees"
   ...> FROM STUDENT;
14083.3333333333
sqlite>
SQL

示例3：

计算每个地址的学生总人数，按学生地址(Address)分组。

从student表中检索地址，并按地址分组并查找相应费用的总和。

SELECT ADDRESS, SUM(FEES) AS "Total Salary"  
FROM STUDENT  
WHERE ID > 0
GROUP BY ADDRESS;
SQL

执行上面代码，得到以下结果 -

sqlite> SELECT ADDRESS, SUM(FEES) AS "Total Salary"
   ...> FROM STUDENT
   ...> WHERE ID > 0
   ...> GROUP BY ADDRESS;
Beijing|15000.0
Guangzhou|70000.0
Haikou|35000.0
Shanghai|2000.0
Shengzhen|38000.0
Shenzhen|9000.0
sqlite>

SQLite触发器

SQLite触发器是一种事件驱动的动作或数据库回调函数，它在对指定的表执行INSERT，UPDATE和DELETE语句时自动调用。

触发器的主要任务就是执行业务规则，验证输入数据和保持审计跟踪。

触发器的使用：

• 触发器用于实施业务规则。
• 验证输入数据。
• 为不同文件中/表的新插入行生成唯一值。
• 写入其他文件/表以进行审计跟踪。
• 从其他文件/表中查询用于交叉引用目的。
• 用于访问系统功能。
• 将数据复制到不同的文件以实现数据一致性。

使用触发器的优点：

• 触发器使应用程序开发更快。 因为数据库存储触发器，所以不必将触发器操作编码到每个数据库应用程序中。
• 定义触发器一次，可以将其重用于许多使用数据库的应用程序。
• 维护方便。 如果业务策略发生变化，则只需更改相应的触发程序，而不是每个应用程序。

如何创建触发器？

CREATE TRIGGER语句用于在SQLite中创建一个新的触发器。 此语句也用于向数据库模式添加触发器。

语法

CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name   
ON table_name  
BEGIN  
 -- Trigger logic goes here....  
END;
SQL

这里，trigger_name是要创建的触发器的名称。

event_name可以是INSERT，DELETE和UPDATE数据库操作。

table_name是要进行操作的表。

SQLite触发器(插入之前/之后）

SQLite插入之前或之后触发器指定了如何在插入数据后创建触发器。 假设有两个表：COMPANY和AUDIT，在这里要对向COMPANY表中插入的每条记录进行审计。如果您已经有创建过一个COMPANY表，请将其删除并重新创建。

COMPANY的创建语句 -

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

创建一个名为AUDIT的新表，只要在向COMPANY表中插入新记录，就会插入日志消息：

AUDIT的创建语句 -

CREATE TABLE audit(  
    EMP_ID INT NOT NULL,
    ACTION_TYPE TEXT NOT NULL,
    ENTRY_DATE TEXT NOT NULL  
);
SQL

创建以上两个表，如下图所示

SQLite触发器：AFTER INSERT

在插入操作后，使用以下语法是在COMPANY表上创建名为“audit_log”的触发器。

CREATE TRIGGER audit_log AFTER INSERT   
ON COMPANY  
BEGIN  
INSERT INTO AUDIT(EMP_ID, ACTION_TYPE ,ENTRY_DATE) VALUES (new.ID, 'AFTER INSERT',datetime('now'));  
END;
SQL

这里，ID是AUDIT表行记录的ID，EMP_ID是来自COMPANY表的ID，DATE字段是用于在COMPANY表中创建记录时保留时间戳。

现在向COMPANY表中插入一些记录，触发器将自动在AUDIT表中创建审核日志记录。

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)  
VALUES (1, 'Maxsu', 22, 'Haikou', 40000.00);
SQL

执行上面语句后，同时会在AUDIT表中创建一条记录。 这只是因为在COMPANY表上的INSERT操作上创建的触发器。现在查询AUDIT表中的记录数据。

SELECT * FROM AUDIT;
SQL

执行上面语句，结果如下所示

如何列出/查看触发器？

可以使用查询语句从sqlite_master表中来查询列出/查看触发器。

SELECT name FROM sqlite_master  
WHERE type = 'trigger';
SQL

执行上面语句，得到以下结果 -

从上面结果中，可以看到触发器的名称。还可以使用AND子句列出特定表上的触发器。

SELECT name FROM sqlite_master  
WHERE type = 'trigger' AND tbl_name = 'COMPANY';
SQL

执行上面查询，得到以下结果

SQLite触发器：BEFORE INSERT

如果要在插入数据之前创建触发器，可以使用以下语句 -

CREATE TRIGGER befor_ins BEFORE INSERT   
ON COMPANY  
BEGIN  
INSERT INTO AUDIT(EMP_ID, ACTION_TYPE ,ENTRY_DATE) VALUES (new.ID, 'BEFORE INSERT', datetime('now'));  
END;
SQL

创建完成上面的触发器后，现在向COMPANY表中插入一条数据记录。

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)  
VALUES (2, 'Minsu', 28, 'Guangzhou', 35000.00);
``

现在，已经创建了两个触发器，一个是**BEFORE INSERT**，另一个是：**AFTER INSERT**，因此在向`COMPANY`表插入第二条记录时，`AUDIT`表中有生成两个记录。

```sql
SELECT * FROM AUDIT;

检查创建的触发器：

SELECT name FROM sqlite_master  
WHERE type = 'trigger' AND tbl_name = 'COMPANY';
SQL

执行上面语句，得到以下结果 -

sqlite> SELECT name FROM sqlite_master
   ...> WHERE type = 'trigger' AND tbl_name = 'COMPANY';
audit_log
befor_ins
sqlite>
Shell

以上面查询的结果中，可以看到已创建的两个触发器。

SQLite触发器(更新之前/之后）

SQLite触发器(更新之前/之后)指定了如何在更新数据后执行触发器操作。 假设有两个表company和audit，在这里要对在company表中的每个记录更新时进行审核。

创建company表的语句 -

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

创建一个名为audit的新表，用于在company表中有更新时插入日志消息。

CREATE TABLE audit(  
    EMP_ID INT NOT NULL,
    ACTION_TYPE TEXT NOT NULL,
    ENTRY_DATE TEXT NOT NULL  
);
SQL

创建更新后的触发器：

使用以下语法创建名为“after_up”的触发器，在COMPANY表上更新操作后触发此触发器。

CREATE TRIGGER after_up AFTER UPDATE   
ON COMPANY  
BEGIN  
INSERT INTO AUDIT(EMP_ID, ACTION_TYPE, ENTRY_DATE) VALUES (new.ID, 'AFTER UPDATE', datetime('now'));  
END;
SQL

现在更新一条记录数据，如下：

UPDATE COMPANY SET ADDRESS = 'Shenzhen' WHERE ID = 1;
SQL

查看已创建的触发器 -

SELECT name FROM sqlite_master  WHERE type = 'trigger';
SQL

执行上面语句，看到以下结果

SQLite触发器：在UPDATE之前

如果要创建在更新数据之前的触发器，请参考以下语句 -

CREATE TRIGGER befor_up BEFORE UPDATE   
ON COMPANY  
BEGIN  
INSERT INTO AUDIT(EMP_ID, ACTION_TYPE, ENTRY_DATE) VALUES (new.ID, old.ADDRESS , datetime('now'));  
END;
SQL

注意：上面的两个关键字：new和old，它们分别表示新插入的行记录和表中已存在行记录。

现在更新一条记录数据，如下：

UPDATE COMPANY SET ADDRESS = 'Beijing' WHERE ID = 1;
SQL

查询审计表：audit中的记录信息，如下所示 -

sqlite> select * from audit;
1|AFTER INSERT|2017-05-25 13:39:32
2|BEFORE INSERT|2017-05-25 13:41:50
2|AFTER INSERT|2017-05-25 13:41:50
1|AFTER UPDATE|2017-05-25 14:14:00
1|Shenzhen|2017-05-25 14:18:19 -- 使用旧行的Address值写入
1|AFTER UPDATE|2017-05-25 14:18:19
sqlite>
SQL

执行上面语句创建触发器，查看上面创建的触发器 -

SELECT name FROM sqlite_master  WHERE type = 'trigger';
SQL

执行上面语句，得到以下结果

SQLite触发器(删除之前/之后）

SQLite触发器(删除之前/之后)指定如何在删除数据后执行触发器。假设有两个表：company和audit。

创建company表的语句如下 -

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

创建audit表的语句如下 -

CREATE TABLE audit(  
    EMP_ID INT NOT NULL,  
    BEFORE_VAL TEXT NULL,
    ACTION_TYPE TEXT NULL,
    ENTRY_DATE TEXT NOT NULL  
);
SQL

创建删除后触发器：

在删除操作后，使用以下语法在company表上创建名为“after_del”的触发器，当删除company表中的一条记录成功之后，就会将这条删除的名称记录到audit表中。

CREATE TRIGGER after_del  AFTER DELETE    
ON COMPANY  
BEGIN  
INSERT INTO AUDIT(EMP_ID, BEFORE_VAL, ACTION_TYPE, ENTRY_DATE) VALUES (old.ID, old.name, 'AFTER DELETE', datetime('now'));  
END;
SQL

先插入一条数据，以供后面删除使用 -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)  VALUES (1, 'Maxsu', 24, 'Haikou', 40000.00);  

-- 删除记录，触发上面定义的触发器 
delete from company where id=1;

-- 查询删除记录的执行结果
select * from company;

-- 查询删除记录之后的审计记录情况
select * from audit;
SQL

完整的执行过程如下图中所示 -

SQLite删除触发器

在SQLite中，DROP TRIGGER命令用于从表中删除一个指定触发器。

语法

DROP TRIGGER trigger_name;
SQL

示例

可以使用以下语句，创建一个触发器 -

CREATE TRIGGER audit_log AFTER INSERT   
ON COMPANY  
BEGIN  
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));  
END;
SQL

现在查询表中存在的触发器，如下所示 -

SELECT name FROM sqlite_master  WHERE type = 'trigger';
SQL

执行上面是查询，它有2个触发器：

sqlite> SELECT name FROM sqlite_master  WHERE type = 'trigger';
after_del
audit_log
sqlite>
SQL

现在，演示如何删除一个名为“audit_log”的触发器。

DROP TRIGGER audit_log;
SQL

再次查询数据库中的触发器，现在可以看到“audit_log”被删除了 -

SELECT name FROM sqlite_master  WHERE type = 'trigger' AND tbl_name = 'COMPANY';
SQL

完整的执行过程如下 -

SQLite主键

SQLite主键是用于唯一定义行记录的一个简单字段或多个字段的组合。一个表只能有一个主键。

主键的值不可以是一个NULL值。

创建主键

主键通常在创建表时一同创建。在执行CREATE TABLE语句时可以直接定义主键。

语法：

CREATE TABLE table_name  
(  
    column1 datatype [ NULL | NOT NULL ],  
    column2 datatype [ NULL | NOT NULL ],  
    ......  
    CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)  
);
SQL

参数说明：

• table_name：指定要创建的表的名称。
• column1，column2：指定要在表中创建的列。
• constraint_name：指定主键的名称。
• pk_col1，pk_col2，… pk_col_n：它指定构成主键的列。

示例：

创建一个“workers”表，其中worker_id列是表的主键。

CREATE TABLE workers  
(
    worker_id INTEGER PRIMARY KEY,  
    last_name VARCHAR NOT NULL,  
    first_name VARCHAR,  
    join_date DATE  
);
SQL

添加主键

当没有在CREATE TABLE语句中定义主键时，也可以在创建表后再添加主键。

需要注意的是，不能使用ALTER TABLE语句来创建主键。在SQLite中需要先创建一个与原表一样的新表，并在这个新表上创建主键，然后复制旧表中的所有数据到新表中就可以了。

语法

PRAGMA foreign_keys=off;  
BEGIN TRANSACTION;  
ALTER TABLE table_name RENAME TO old_table;  
CREATE TABLE table_name  
(  
    column1 datatype [ NULL | NOT NULL ],  
    column2 datatype [ NULL | NOT NULL ],  
    ...  
    CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)  
);  
INSERT INTO table_name SELECT * FROM old_table;  
COMMIT;  
PRAGMA foreign_keys=on;
SQL

参数说明：

• table_name：指定要创建含有主键的表的名称。
• old_table：指定要被修表的名称。
• column1，column2：指定要在表中创建的列。
• constraint_name：指定主键的名称。
• pk_col1，pk_col2，… pk_col_n：它指定构成主键的列。

示例：

首先创建一个没有主键的表：employees，如下语句 -

CREATE TABLE employees  
(
    employee_id INTEGER,  
    last_name VARCHAR NOT NULL,  
    first_name VARCHAR,  
    hire_date DATE  
);
SQL

现在，运行以下命令将“employee_id”列设置成为主键。

PRAGMA foreign_keys=off;  
BEGIN TRANSACTION;  
ALTER TABLE employees RENAME TO old_employees;  
CREATE TABLE employees  
(  
    employee_id INTEGER,  
    last_name VARCHAR NOT NULL,  
    first_name VARCHAR,  
    hire_date DATE,  
    CONSTRAINT employees_pk PRIMARY KEY (employee_id)  
);  
INSERT INTO employees SELECT * FROM old_employees;  
COMMIT;  
PRAGMA foreign_keys=on;
SQL

现在，它会将employees表重命名为old_employees，然后创建一个新表employees并创建主键，然后从old_employees表中将所有数据传输到新表 employees中。

最后删除旧表：old_employees。

DROP TABLE old_employees;
SQL

删除主键

与添加主键一样，不能使用ALTER TABLE语句来删除主键。需要创建一个没有(删除)主键的新表，并将数据复制到此新表中。

语法

PRAGMA foreign_keys=off;  
BEGIN TRANSACTION;  
ALTER TABLE table_name RENAME TO old_table;  
CREATE TABLE table_name  
(  
    column1 datatype [ NULL | NOT NULL ],  
    column2 datatype [ NULL | NOT NULL ],  
    ...  
);  
INSERT INTO table_name SELECT * FROM old_table;  
COMMIT;  
PRAGMA foreign_keys=on;
SQL

参数说明

• table_name - 指定要从中删除主键的表的名称。
• old_table - 指定在删除主键后再创建新表时，将要删除拥有主键的表名称。

示例：

假设有一个表engineers，并有一个主键：engineer_id，现在要删除这个engineer_id主键。

CREATE TABLE engineers  
(
    engineer_id INTEGER,  
    engineerr_name VARCHAR NOT NULL,  
    address VARCHAR,  
    city VARCHAR,  
    CONSTRAINT engineers_pk PRIMARY KEY (engineer_id)  
);
SQL

现在，运行以下命令删除主键。

PRAGMA foreign_keys=off;  
BEGIN TRANSACTION;  
ALTER TABLE engineers RENAME TO old_engineers;  
CREATE TABLE engineers  
(  
    engineer_id INTEGER,  
    engineer_name VARCHAR NOT NULL,  
    address VARCHAR,  
    city VARCHAR  
);  
INSERT INTO engineers SELECT * FROM old_engineers;  
COMMIT;  
PRAGMA foreign_keys=on;
SQL

执行上面语句后，主键现在从engineers表中删除。 但是原来的表现在被重命名为old_engineers。

现在删除old_engineers表，如下语句 -

DROP TABLE old_engineers;

SQLite外键

SQLite Foreign Key语句用于指定一个表中的值也出现在另一个表中。 它在SQLite数据库中执行强制引用完整性。 被引用的表称为父表，而具有外键(或引用父表)的表称为子表。 子表中的外键通常会引用父表中的主键。

在SQLite中，只能在CREATE TABLE语句中定义外键。

创建外键

语法

CREATE TABLE table_name  
(  
    column1 datatype [ NULL | NOT NULL ],  
    column2 datatype [ NULL | NOT NULL ],  
    ...  
    CONSTRAINT fk_column  
    FOREIGN KEY (column1, column2, ... column_n)  
    REFERENCES parent_table (column1, column2, ... column_n)  
);
SQL

示例：

创建一个以department表作为引用表(父表)拥有外键的 employees 表， employees 表的department_id列引用父表department的department_id列作为外键。

-- 父表
CREATE TABLE departments  
(
    department_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    department_name VARCHAR  
);  

-- 拥有外键的表
CREATE TABLE employees  
(
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,  
    last_name VARCHAR NOT NULL,  
    first_name VARCHAR,  
    department_id INTEGER,  
    CONSTRAINT fk_departments  
    FOREIGN KEY (department_id)  
    REFERENCES departments(department_id)  
);
SQL

这里在department表中创建一个主键department_id。 然后，在employees表上创建一个名为fk_departments的外键，该department_id字段引用department表的department_id字段。

SQLite导出导入

SQLite导入数据

可以使用sqlite3工具和.import命令将CSV文件导入SQLite表。此命令接受文件名和表名。

这里，文件名是用于指定从哪里获取数据，表名是要导入数据的表。在没有指定表名的情况下，它将根据CSV文件中的数据自动创建表。

从CSV文件导入到SQLite表

看下面一个例子，将一个CSV文件：data.csv的内容导入到不存在的表中，在导入时指定新表名称为“employee”。 它将根据CSV文件的数据创建一个表。

文件：data.csv的内容如下 -

ID,NAME,AGE,ADDRESS,FEES
1,Maxsu,26,Haikou,36000
2,Minwang,25,Guangzhou,29900
Shell

执行以下导入命令 -

.mode csv  
.import D:/software/sqlite/data.csv employee
Shell

注意： .mode csv在.import语句之前使用，以防止命令行实用程序尝试将输入文件文本解释为其他格式。

现在检查表是否创建：

可以看到创建了employee表，现在查看employee表中的数据：

.mode column  
SELECT * FROM EMPLOYEE;
SQL

完整执行结果如下 -

sqlite> .mode csv
sqlite> .import D:/software/sqlite/data.csv employee
sqlite>
sqlite> .tables
audit       company     department  employee    student
sqlite>
sqlite> select * from employee;
1,Maxsu,26,Haikou,36000
2,Minwang,25,Guangzhou,29900
sqlite>

SQLite导出数据

SQLite可以将数据从SQLite数据库导出到CSV文件。也可以根据查询导出整个表或表中符合条件的数据。

.once命令用于将数据导出到CSV文件，后跟要编写文件的文件路径/名称。

SQLite将表导到CSV文件

下面来看看一个例子，将STUDENT表的所有内容导出为CSV文件：

STUDENT表具有以下数据：

sqlite> select * from student;
1,Maxsu,27,Shengzhen,20000.0
2,Minsu,25,Beijing,15000.0
3,Avgsu,23,Shanghai,2000.0
4,Linsu,25,Guangzhou,65000.0
5,Sqlsu,26,Haikou,25000.0
6,Javasu,21,Shengzhen,18000.0
7,Linsu,27,Haikou,10000.0
8,Minsu,23,Guangzhou,5000.0
9,Maxsu,23,Shenzhen,9000.0
sqlite>
SQL

执行导出操作，如下所示 -

.header on  
.mode csv  
.once D:/software/sqlite/student.csv  
SELECT * FROM student;
Shell

代码说明：

• .header on: - 它指定头(标题)已启用。 这是可选的。 如果禁用标题，CSV文件将不会包含任何数据。
• .mode csv: - 它指定启用CSV模式。
• .once: - 它指定要输出写入到CSV文件的确切位置。

在执行上面语句后，找开文件：D:/software/sqlite/student.csv，应该可以看到导出的表的内容如下所示 -

如何自动打开CSV文件：

.system 命令用于自动打开CSV文件。

例如：

以下命令在Windows中自动打开CSV文件：

sqlite> .system D:/software/sqlite/student.csv
Shell

上面代码可能要根据操作系统的不同而进行更改：

• 在Windows上，使用.system命令后跟文件名。
• 在Mac上，使用.system open后跟文件名。
• 在Linux和Unix系统上，使用.system后跟程序的名称打开文件，后跟文件名。 例如，.system libreoffice /home/yiibai/file.csv

SQLite连接程序

Java连接SQLite数据库

要使用java程序连接SQLite，并与SQLite进行数据存取操作，必须在系统上设置SQLite JDBC驱动程序和安装Java JDK。按照以下步骤进行：

• 从sqlite-jdbc存储库下载最新版本的sqlite-jdbc-(VERSION).jar。
• 将下载的jar文件添加到类路径。
• 使用java连接到SQLite数据库。

连接到SQLite数据库

打开Eclipse IDE，创建一个JAVA工程：JavaWithSQLite，下载所需的sqlite-jdbc-(VERSION).jar(下载地址：http://bitbucket.org/xerial/sqlite-jdbc/downloads/ 或者 http://mvnrepository.com/artifact/org.xerial/sqlite-jdbc/3.18.0 )，并将它放入到项目的类库中。

使用以下代码使用Java编程语言连接到SQLite数据库，首先创建一个类：ConnectSQLite.java，其代码如下所示 -

package com.yiibai;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectSQLite {
    /**
     * Connect to a sample database
     */
    public static void connect() {
        Connection conn = null;
        try {
            // db parameters
            String url = "jdbc:sqlite:D:/software/sqlite/java-sqlite.db";
            // create a connection to the database
            conn = DriverManager.getConnection(url);

            System.out.println("Connection to SQLite has been established.");

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
    }

    /**
     * @param args
     *            the command line arguments
     */
    public static void main(String[] args) {
        connect();
    }
}
Java

执行上面代码后，应该会创建一个文件：D:/software/sqlite/java-sqlite.db，并与数据库java-sqlite.db连接。

使用java创建数据库

还可以使用java编程语言在SQLite中创建一个新的数据库。假设要使用Java来创建一个名为java_sqlite.db的数据库。创建一个公共类：CreateDB.java并使用以下代码：

package com.yiibai;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;

public class CreateDB {

    public static void createNewDatabase(String fileName) {

        String url = "jdbc:sqlite:" + fileName;

        try {
            Connection conn = DriverManager.getConnection(url);
            if (conn != null) {
                DatabaseMetaData meta = conn.getMetaData();
                System.out.println("The driver name is " + meta.getDriverName());
                System.out.println("A new database has been created.");
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void main(String[] args) {
        createNewDatabase("D:/software/sqlite/create-db.db");
    }
}
Java

执行上面代码，得到以下结果 -

The driver name is SQLiteJDBC
A new database has been created.
Shell

现在创建一个名为“create-db.db”的新数据库。可以看到对应创建目录有一个文件：create-db.db存在。

使用java创建一个表

假设要通过Java程序在SQLite中创建一个名为tb_emp的表，tb_emp表具有id,name和capacity这三列。首先创建一个Java类：CreateTable.java，并使用以下代码：

package com.yiibai;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {

    public static void createNewTable() {
        // SQLite connection string
        String url = "jdbc:sqlite:D:/software/sqlite/java-sqlite.db";

        // SQL statement for creating a new table
        String sql = "CREATE TABLE IF NOT EXISTS employees (\n" + " id integer PRIMARY KEY,\n"
                + " name text NOT NULL,\n" + " capacity real\n" + ");";

        try {
            Connection conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            System.out.println("Create table finished.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * @param args
     *            the command line arguments
     */
    public static void main(String[] args) {
        createNewTable();
    }

}
Java

执行上面代码，得到以下结果 -

Create table finished.
Shell

执行上面代码后，将在java-sqlite.db数据库中创建一个名称为：employees 的表。

向表中插入记录

创建表后，使用以下代码在表中插入一些记录。 创建一个新的Java类：InsertRecords，具有以下代码：

package com.yiibai;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertRecords {

    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:D:/software/sqlite/java-sqlite.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    public void insert(String name, double capacity) {
        String sql = "INSERT INTO employees(name, capacity) VALUES(?,?)";

        try {
            Connection conn = this.connect();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.setDouble(2, capacity);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void main(String[] args) {

        InsertRecords app = new InsertRecords();
        // insert three new rows
        app.insert("Maxsu", 30000);
        app.insert("Minsu", 40000);
        app.insert("Miswong", 50000);
        System.out.println("Insert data finished.");
    }

}
Java

现在记录被插入到表中。 可以使用SELECT命令查看它：

sqlite> .open java-sqlite.db
sqlite> .tables
employees
sqlite> select * from employees;
id,name,capacity
1,Maxsu,30000.0
2,Minsu,40000.0
3,Miswong,50000.0
sqlite>
sqlite>
Shell

查询/选择记录

要使用Java程序从表中选择/查询记录，请使用以下代码。 创建一个新的Java类 - SelectRecords.java，使用以下代码 -

package com.yiibai;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectRecords {

    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:D:/software/sqlite/java-sqlite.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    public void selectAll() {
        String sql = "SELECT * FROM employees";

        try {
            Connection conn = this.connect();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            // loop through the result set
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getDouble("capacity"));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    /**
     * @param args
     *            the command line arguments
     */
    public static void main(String[] args) {
        SelectRecords app = new SelectRecords();
        app.selectAll();
    }

}
Java

执行上面代码，得到以下结果 -

注意： 因为都是通过使用相同的编程过程，所以您可自己编写其它数据库操作，比如：更新和删除表中的数据等等。

PHP连接SQLite数据库

SQLite3扩展名在PHP 5.3.0+以上都会默认启用。可以在编译时使用--without-sqlite3来禁用它。

Windows用户可通过启用php_sqlite3.dll才能使用此扩展。 php_sqlite3.dll默认包含在PHP 5.3.0之后的PHP发行版中。

连接到SQLite数据库

以下PHP代码显示如何连接到SQLite数据库。 如果数据库不存在，那么它将创建一个新的数据库，最后将返回一个数据库对象。

<?php
   class SQLiteDB extends SQLite3
   {
      function __construct()
      {
         $this->open('phpdb.db');
      }
   }
   $db = new SQLiteDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Yes, Opened database successfully\n";
   }
?>
PHP

现在，运行上面的程序在当前目录中创建指定数据库：phpdb.db。可以根据需要更改路径。 如果数据库成功创建，则会提供以下消息：

在执行上面语句后，应该也会在相同的目录下自动创建一个名称为：phpdb.db的数据库文件。如下所示 -

创建表

以下PHP程序将用于在上面创建的数据库(phpdb.db)中创建一个表：

<?php
class SQLiteDB extends SQLite3
{
  function __construct()
  {
     $this->open('phpdb.db');
  }
}
$db = new SQLiteDB();
if(!$db){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Opened database successfully<br/>\n";
}

$sql =<<<EOF
      CREATE TABLE company
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Table created successfully<br/>\n";
}
$db->close();
PHP

当执行上述程序时，它将在数据库(phpdb.db)中创建一个名称为：company的表，并显示以下消息：

插入数据操作

以下PHP程序显示了如何在上述示例中创建的company表中插入数据记录：

<?php
class SQLiteDB extends SQLite3
{
  function __construct()
  {
     $this->open('phpdb.db');
  }
}
$db = new SQLiteDB();
if(!$db){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Opened database successfully<br/>\n";
}

// 先删除后创建表
$sql = "DROP table company";
$ret = $db->exec($sql);

// 创建表语句

$sql =<<<EOF
      CREATE TABLE if not exists company
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Table created successfully<br/>\n";
}

// $db->close();

$sql =<<<EOF
      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
EOF;

   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Yes, Some Records has Inserted successfully<br/>\n";
   }
   $db->close();
PHP

当执行上述程序时，将向company表中插入给定的记录，并显示如下：

SELECT操作

以下PHP程序显示了如何从上述示例中创建的company表中获取并显示数据记录：

<?php
class SQLiteDB extends SQLite3
{
  function __construct()
  {
     $this->open('phpdb.db');
  }
}
$db = new SQLiteDB();
if(!$db){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Opened database successfully<br/>\n";
}

// 先删除后创建表
$sql = "DROP table company";
$ret = $db->exec($sql);

// 创建表

$sql =<<<EOF
      CREATE TABLE if not exists company
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Table created successfully<br/>\n";
}

// $db->close();

$sql =<<<EOF
      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Some Records has Inserted successfully<br/>\n";
}

// 查询表中的数据

echo "<b> Select Data from company table :</b><hr/>";

$sql =<<<EOF
  SELECT * from COMPANY;
EOF;

$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
  echo "ID = ". $row['ID'] . "<br/>\n";
  echo "NAME = ". $row['NAME'] ."<br/>\n";
  echo "ADDRESS = ". $row['ADDRESS'] ."<br/>\n";
  echo "SALARY =  ".$row['SALARY'] ."<br/>\n\n";
  echo '----------------------------------<br/>';
}

echo "Operation done successfully\n";

$db->close();
PHP

执行上述程序时，会产生以下结果：

更新操作

以下PHP代码显示了如何使用UPDATE语句来更新记录，然后从company表中获取并显示更新的记录：

<?php
class SQLiteDB extends SQLite3
{
  function __construct()
  {
     $this->open('phpdb.db');
  }
}
$db = new SQLiteDB();
if(!$db){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Opened database successfully<br/>\n";
}

// 先删除后创建表
$sql = "DROP table company";
$ret = $db->exec($sql);

// 创建表

$sql =<<<EOF
      CREATE TABLE if not exists company
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Table created successfully<br/>\n";
}

// $db->close();

$sql =<<<EOF
      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Some Records has Inserted successfully<br/>\n";
}

// 更新ID=1的薪水为：29999

$sql = 'UPDATE COMPANY set SALARY = 29999.00 where ID=1';
$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo $db->changes(), " Record(ID=1) updated successfully<br/>\n";
}


// 查询表中的数据

echo "<b> Select Data from company table :</b><hr/>";

$sql =<<<EOF
  SELECT * from COMPANY;
EOF;

$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
  echo "ID = ". $row['ID'] . "<br/>\n";
  echo "NAME = ". $row['NAME'] ."<br/>\n";
  echo "ADDRESS = ". $row['ADDRESS'] ."<br/>\n";
  echo "SALARY =  ".$row['SALARY'] ."<br/>\n\n";
  echo '----------------------------------<br/>';
}

echo "Operation done successfully\n";

$db->close();
PHP

执行上述程序时，会产生以下结果：

删除操作

以下PHP代码显示了如何使用DELETE语句删除任何记录，然后从company表中获取并显示剩余的记录：

<?php
class SQLiteDB extends SQLite3
{
  function __construct()
  {
     $this->open('phpdb.db');
  }
}
$db = new SQLiteDB();
if(!$db){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Opened database successfully<br/>\n";
}

// 先删除后创建表
$sql = "DROP table company";
$ret = $db->exec($sql);

// 创建表

$sql =<<<EOF
      CREATE TABLE if not exists company
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Table created successfully<br/>\n";
}

// $db->close();

$sql =<<<EOF
      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );

      INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
EOF;

$ret = $db->exec($sql);
if(!$ret){
  echo $db->lastErrorMsg();
} else {
  echo "Yes, Some Records has Inserted successfully<br/>\n";
}

// 更新ID小于等于2的数据记录

$sql =<<<EOF
  DELETE from COMPANY where ID<=2;
EOF;
$ret = $db->exec($sql);
if(!$ret){
 echo $db->lastErrorMsg();
} else {
  echo $db->changes(), " Record(ID<=2) deleted successfully<br/>\n";
}

// 查询表中的数据

echo "<b> Select Data from company table :</b><hr/>";

$sql =<<<EOF
  SELECT * from COMPANY;
EOF;

$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
  echo "ID = ". $row['ID'] . "<br/>\n";
  echo "NAME = ". $row['NAME'] ."<br/>\n";
  echo "ADDRESS = ". $row['ADDRESS'] ."<br/>\n";
  echo "SALARY =  ".$row['SALARY'] ."<br/>\n\n";
  echo '----------------------------------<br/>';
}

echo "Operation done successfully\n";

$db->close();
PHP

执行上述程序时，会产生以下结果：

Python连接SQLite数据库

由Gerhard Haring编写的sqlite3模块与Python进行集成。 它提供了符合由PEP 249描述的DB-API 2.0规范的SQL接口。所以不需要单独安装此模块，因为默认情况下随着Python 2.5.x以上版本一起发布运行。

要使用sqlite3模块，必须首先创建一个表示数据库的连接对象，然后可以选择创建的游标对象来执行SQL语句。

连接到数据库

以下Python代码显示了如何连接到一个指定的数据库。 如果数据库不存在，那么它将被创建，最后将返回一个数据库对象。

注意：在本示例中，使用的是 python 3.5.1

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('pydb.db')
print ("Opened database successfully");
Python

在这里，还可以提供数据库名称作为特殊名称:memory:, 在RAM中创建数据库。 现在，运行上面的程序在当前目录中创建数据库：pydb.db。

可以根据需要更改路径。 在F:\worksp\sqlite\py-sqlite.py文件中保留以上代码，并按如下所示执行。 如果数据库成功创建，则会提供以下消息：

创建表

以下Python程序将用于在先前创建的数据库(py-sqlite.py)中创建一个表：

#!/usr/bin/python

import sqlite3

## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 创建一个表 - company
conn.execute('''CREATE TABLE company
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");

conn.close()
Python

当执行上述程序后，将在py-sqlite.db中创建company表，并显示以下消息：

#!/usr/bin/python

import sqlite3

## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 创建一个表 - company
conn.execute('''CREATE TABLE company
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");

conn.close()
Python

当执行上述程序时，它将在py-sqlite.db数据库中创建company表，并显示以下消息：

Opened database successfully
Table created successfully
Shell

插入操作

以下Python程序显示如何在上述示例中创建的COMPANY表中插入数据记录：

#!/usr/bin/python

import sqlite3

## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 创建一个表 - company
conn.execute('''CREATE TABLE company
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");

conn.commit()

## 插入数据
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()
print ("Records Insert successfully");

conn.close()
Python

当执行上述程序时，它将在COMPANY表中插入给定的数据记录，并显示以下结果：

Opened database successfully
Table created successfully
Records Insert successfully
Shell

SELECT/查询操作

以下Python程序显示如何从上述示例中创建的COMPANY表中获取并显示数据记录：

#!/usr/bin/python

import sqlite3

## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 创建一个表 - company
conn.execute('''CREATE TABLE company
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");

conn.commit()

## 插入数据
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()
print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Select Operation done successfully.");

conn.close()
Python

执行上述程序时，会产生以下结果：

Opened database successfully
Table created successfully
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID =  1
NAME =  Maxsu
ADDRESS =  Haikou
SALARY =  20000.0 

ID =  2
NAME =  Allen
ADDRESS =  Shenzhen
SALARY =  35000.0 

ID =  3
NAME =  Weiwang
ADDRESS =  Guangzhou
SALARY =  22000.0 

ID =  4
NAME =  Marklee
ADDRESS =  Beijing
SALARY =  45000.0 

Select Operation done successfully.
Shell

更新操作

以下Python代码演示如何使用UPDATE语句来更新指定记录，然后再从COMPANY表中获取并显示更新的记录：

#!/usr/bin/python

import sqlite3

## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 创建一个表 - company
conn.execute('''CREATE TABLE company
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");

conn.commit()

## 插入数据
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()

## 更新数据
conn.execute("UPDATE COMPANY set SALARY = 29999.00 where ID=1")
conn.commit()
print ("Total number of rows updated :", conn.total_changes)


print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Select Operation done successfully.");

conn.close()
Python

执行上述程序时，会产生以下结果：

Opened database successfully
Table created successfully
Total number of rows updated : 5
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID =  1
NAME =  Maxsu
ADDRESS =  Haikou
SALARY =  29999.0 

ID =  2
NAME =  Allen
ADDRESS =  Shenzhen
SALARY =  35000.0 

ID =  3
NAME =  Weiwang
ADDRESS =  Guangzhou
SALARY =  22000.0 

ID =  4
NAME =  Marklee
ADDRESS =  Beijing
SALARY =  45000.0 

Select Operation done successfully.
Shell

删除操作

以下Python代码演示如何使用DELETE语句来删除记录，然后从COMPANY表中获取并显示剩余的记录：

#!/usr/bin/python

import sqlite3

## 打开数据库连接
conn = sqlite3.connect('py-sqlite.db')
print ("Opened database successfully");

## 清除已存在的表 - company
conn.execute('''DROP TABLE company''');
conn.commit()

## 创建一个表 - company
conn.execute('''CREATE TABLE company
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print ("Table created successfully");

conn.commit()

## 插入数据
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Maxsu', 27, 'Haikou', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 26, 'Shenzhen', 35000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Weiwang', 23, 'Guangzhou', 22000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Marklee', 25, 'Beijing', 45000.00 )");

conn.commit()

## 删除ID值小于等于2的数据
conn.execute("DELETE from COMPANY where ID<=2;")
conn.commit()

print ("Total number of rows updated :", conn.total_changes)


print ("Records Insert successfully");
print ('--------------------------- start fetch data from company --------------------------');

cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print ("ID = ", row[0])
   print ("NAME = ", row[1])
   print ("ADDRESS = ", row[2])
   print ("SALARY = ", row[3], "\n")

print ("Select Operation done successfully.");

conn.close()
Python

执行上面语句后，得到以下结果 -

Opened database successfully
Table created successfully
Total number of rows updated : 6
Records Insert successfully
--------------------------- start fetch data from company --------------------------
ID =  3
NAME =  Weiwang
ADDRESS =  Guangzhou
SALARY =  22000.0 

ID =  4
NAME =  Marklee
ADDRESS =  Beijing
SALARY =  45000.0 

Select Operation done successfully.

参考资料

易百教程

菜鸟教程

SQLite API