SQLite – 快速指南
SQLite – 快速指南
SQLite – 概述
本章帮助您了解什么是 SQLite，它与 SQL 有何不同，为什么需要它以及它处理应用程序数据库的方式。
SQLite 是一个软件库，它实现了一个自包含、无服务器、零配置、事务性 SQL 数据库引擎。SQLite 是周围增长最快的数据库引擎之一，但它的流行程度正在增长，与其大小无关。SQLite 的源代码在公共领域。
什么是 SQLite？
SQLite 是一个进程内库，它实现了一个自包含、无服务器、零配置、事务性 SQL 数据库引擎。它是一个零配置的数据库，这意味着您不需要像其他数据库一样在系统中配置它。
SQLite 引擎不像其他数据库那样是一个独立的过程，您可以根据需要将它静态或动态地链接到您的应用程序。SQLite 直接访问其存储文件。
为什么是 SQLite？
-
SQLite 不需要单独的服务器进程或系统来运行（无服务器）。
-
SQLite 带有零配置，这意味着不需要设置或管理。
-
完整的 SQLite 数据库存储在单个跨平台磁盘文件中。
-
SQLite 非常小且重量轻，完全配置时小于 400KiB 或省略可选功能时小于 250KiB。
-
SQLite 是自包含的，这意味着没有外部依赖项。
-
SQLite 事务完全符合 ACID，允许从多个进程或线程安全访问。
-
SQLite 支持 SQL92 (SQL2) 标准中的大多数查询语言功能。
-
SQLite 是用 ANSI-C 编写的，并提供简单易用的 API。
-
SQLite 可用于 UNIX（Linux、Mac OS-X、Android、iOS）和 Windows（Win32、WinCE、WinRT）。
SQLite 简史
-
2000 – D. Richard Hipp 设计 SQLite 的目的是为了操作程序不需要管理。
-
2000 – 8 月，SQLite 1.0 与 GNU 数据库管理器一起发布。
-
2011 – Hipp 宣布将 UNQl 接口添加到 SQLite DB 并开发 UNQLite（面向文档的数据库）。
SQLite 限制
SQLite 中很少有 SQL92 不受支持的功能，如下表所示。
| Sr.No. | 功能和描述 |
|---|---|
| 1 |
RIGHT OUTER JOIN 只实现了 LEFT OUTER JOIN。 |
| 2 |
FULL OUTER JOIN 只实现了 LEFT OUTER JOIN。 |
| 3 |
ALTER TABLE 支持 ALTER TABLE 命令的 RENAME TABLE 和 ADD COLUMN 变体。不支持 DROP COLUMN、ALTER COLUMN、ADD CONSTRAINT。 |
| 4 |
Trigger support 支持 FOR EACH ROW 触发器，但不支持 FOR EACH STATEMENT 触发器。 |
| 5 |
VIEWs SQLite 中的视图是只读的。您不能对视图执行 DELETE、INSERT 或 UPDATE 语句。 |
| 6 |
GRANT and REVOKE 唯一可以应用的访问权限是底层操作系统的普通文件访问权限。 |
SQLite 命令
与关系数据库交互的标准 SQLite 命令类似于 SQL。它们是创建、选择、插入、更新、删除和删除。这些命令可以根据其操作性质分为几组 –
DDL – 数据定义语言
| Sr.No. | 命令和描述 |
|---|---|
| 1 |
CREATE 在数据库中创建新表、表视图或其他对象。 |
| 2 |
ALTER 修改现有的数据库对象，例如表。 |
| 3 |
DROP 删除整个表、表的视图或数据库中的其他对象。 |
DML – 数据操作语言
| Sr.No. | 命令和描述 |
|---|---|
| 1 |
INSERT 创建记录 |
| 2 |
UPDATE 修改记录 |
| 3 |
DELETE 删除记录 |
DQL – 数据查询语言
| Sr.No. | 命令和描述 |
|---|---|
| 1 |
SELECT 从一个或多个表中检索某些记录 |
SQLite – 安装
SQLite 以其强大的零配置特性而闻名，这意味着不需要复杂的设置或管理。本章将带您完成在 Windows、Linux 和 Mac OS X 上设置 SQLite 的过程。
在 Windows 上安装 SQLite
-
步骤 1 – 转到SQLite 下载页面，然后从 Windows 部分下载预编译的二进制文件。
-
第 2 步– 下载 sqlite-shell-win32-*.zip 和 sqlite-dll-win32-*.zip 压缩文件。
-
第 3 步– 创建一个文件夹 C:\>sqlite 并在此文件夹中解压上面两个压缩文件，这将为您提供 sqlite3.def、sqlite3.dll 和 sqlite3.exe 文件。
-
第 4 步– 在 PATH 环境变量中添加 C:\>sqlite，最后转到命令提示符并发出 sqlite3 命令，它应该显示以下结果。
C:\>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" 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>
如果你没有看到上面的结果，那么这意味着你的 Linux 机器上没有安装 SQLite。以下是安装 SQLite 的以下步骤 –
-
步骤 1 – 转到SQLite 下载页面并从源代码部分下载 sqlite-autoconf-*.tar.gz。
-
步骤 2 – 运行以下命令 –
$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install
上面的命令将在您的 Linux 机器上安装 SQLite 结束。您可以按照上面的说明进行验证。
在 Mac OS X 上安装 SQLite
虽然最新版本的 Mac OS X 预装了 SQLite，但如果您没有安装可用，那么只需按照以下步骤操作 –
-
步骤 1 – 转到SQLite 下载页面，然后从源代码部分下载 sqlite-autoconf-*.tar.gz。
-
步骤 2 – 运行以下命令 –
$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local $make $make install
上述过程将以在 Mac OS X 机器上安装 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>
最后，您有 SQLite 命令提示符，您可以在其中为您的练习发出 SQLite 命令。
SQLite – 命令
本章将带您了解 SQLite 程序员使用的简单而有用的命令。这些命令称为 SQLite 点命令，这些命令的例外是它们不应以分号 (;) 结尾。
让我们从在命令提示符下键入一个简单的sqlite3命令开始，它将为您提供 SQLite 命令提示符，您将在其中发出各种 SQLite 命令。
$sqlite3 SQLite version 3.3.6 Enter ".help" for instructions sqlite>
有关可用点命令的列表，您可以随时输入“.help”。例如 –
sqlite>.help
上面的命令将显示各种重要的 SQLite 点命令的列表，它们在下表中列出。
| Sr.No. | 命令和描述 |
|---|---|
| 1 |
.backup ?DB? FILE 备份数据库（默认“主”）到文件 |
| 2 |
.bail ON|OFF 遇到错误后停止。默认关闭 |
| 3 |
.databases 列出附加数据库的名称和文件 |
| 4 |
.dump ?TABLE? 以 SQL 文本格式转储数据库。如果指定了 TABLE，则仅转储与 LIKE 模式 TABLE 匹配的表 |
| 5 |
.echo ON|OFF 打开或关闭命令回显 |
| 6 |
.exit 退出 SQLite 提示 |
| 7 |
.explain ON|OFF 打开或关闭适合 EXPLAIN 的输出模式。没有 args，它打开 EXPLAIN |
| 8 |
.header(s) ON|OFF 打开或关闭标题显示 |
| 9 |
.help 显示此消息 |
| 10 |
.import FILE TABLE 将数据从 FILE 导入到 TABLE |
| 11 |
.indices ?TABLE? 显示所有索引的名称。如果指定了 TABLE，则仅显示匹配 LIKE 模式 TABLE 的表的索引 |
| 12 |
.load FILE ?ENTRY? 加载扩展库 |
| 13 |
.log FILE|off 打开或关闭日志记录。FILE 可以是 stderr/stdout |
| 14 |
.mode MODE 设置输出模式，其中 MODE 是其中之一 –
|
| 15 |
.nullvalue STRING 打印 STRING 代替 NULL 值 |
| 16 |
.output FILENAME 将输出发送到 FILENAME |
| 17 |
.output stdout 将输出发送到屏幕 |
| 18 |
.print STRING… 打印文字字符串 |
| 19 |
.prompt MAIN CONTINUE 替换标准提示 |
| 20 |
.quit 退出 SQLite 提示 |
| 21 |
.read FILENAME 在 FILENAME 中执行 SQL |
| 22 |
.schema ?TABLE? 显示 CREATE 语句。如果指定了 TABLE，则只显示匹配 LIKE 模式 TABLE 的表 |
| 23 |
.separator STRING 更改输出模式和 .import 使用的分隔符 |
| 24 |
.show 显示各种设置的当前值 |
| 25 |
.stats ON|OFF 打开或关闭统计信息 |
| 26 |
.tables ?PATTERN? 列出与 LIKE 模式匹配的表的名称 |
| 27 |
.timeout MS 尝试打开锁定的表 MS 毫秒 |
| 28 |
.width NUM NUM 为“列”模式设置列宽 |
| 29 |
.timer ON|OFF 打开或关闭 CPU 定时器测量 |
让我们尝试.show命令来查看 SQLite 命令提示符的默认设置。
sqlite>.show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite>
确保 sqlite> prompt 和 dot 命令之间没有空格，否则将不起作用。
格式化输出
您可以使用以下点命令序列来格式化输出。
sqlite>.header on sqlite>.mode column sqlite>.timer on sqlite>
上述设置将产生以下格式的输出。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 CPU Time: user 0.000000 sys 0.000000
sqlite_master 表
主表保存有关数据库表的关键信息，称为sqlite_master。您可以看到其架构如下 –
sqlite>.schema sqlite_master
这将产生以下结果。
CREATE TABLE sqlite_master ( type text, name text, tbl_name text, rootpage integer, sql text );
SQLite – 语法
SQLite 遵循一组独特的规则和指南，称为语法。本章列出了所有基本的 SQLite 语法。
区分大小写
需要注意的重要一点是 SQLite不区分大小写，但是有一些命令区分大小写，例如GLOB和glob在 SQLite 语句中具有不同的含义。
评论
SQLite 注释是额外的注释，您可以将其添加到 SQLite 代码中以提高其可读性，并且它们可以出现在任何地方；空格可以出现，包括表达式内部和其他 SQL 语句的中间，但它们不能嵌套。
SQL 注释以两个连续的“-”字符 (ASCII 0x2d) 开头，并一直延伸到并包括下一个换行符 (ASCII 0x0a) 或直到输入结束，以先到者为准。
您还可以使用 C 风格的注释，它以“/*”开头并扩展到并包括下一个“*/”字符对或直到输入结束，以先到者为准。C 风格的注释可以跨越多行。
sqlite> .help -- This is a single line comment
SQLite 语句
所有 SQLite 语句都以任何关键字开头，例如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，并且所有语句都以分号 (;) 结尾。
SQLite ANALYZE 语句
ANALYZE; or ANALYZE database_name; or ANALYZE database_name.table_name;
SQLite AND/OR 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;
SQLite ALTER TABLE 语句
ALTER TABLE table_name ADD COLUMN column_def...;
SQLite ALTER TABLE 语句（重命名）
ALTER TABLE table_name RENAME TO new_table_name;
SQLite ATTACH DATABASE 语句
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
SQLite BEGIN TRANSACTION 语句
BEGIN; or BEGIN EXCLUSIVE TRANSACTION;
SQLite BETWEEN 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;
SQLite COMMIT 语句
COMMIT;
SQLite CREATE INDEX 语句
CREATE INDEX index_name ON table_name ( column_name COLLATE NOCASE );
SQLite CREATE UNIQUE INDEX 语句
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);
SQLite CREATE TABLE 语句
CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
SQLite CREATE TRIGGER 语句
CREATE TRIGGER database_name.trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN stmt1; stmt2; .... END;
SQLite CREATE VIEW 语句
CREATE VIEW database_name.view_name AS SELECT statement....;
SQLite 创建虚拟表语句
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log ); or CREATE VIRTUAL TABLE database_name.table_name USING fts3( );
SQLite COMMIT TRANSACTION 语句
COMMIT;
SQLite 计数子句
SELECT COUNT(column_name) FROM table_name WHERE CONDITION;
SQLite DELETE 语句
DELETE FROM table_name
WHERE {CONDITION};
SQLite DETACH DATABASE 语句
DETACH DATABASE 'Alias-Name';
SQLite DISTINCT 子句
SELECT DISTINCT column1, column2....columnN FROM table_name;
SQLite DROP INDEX 语句
DROP INDEX database_name.index_name;
SQLite DROP TABLE 语句
DROP TABLE database_name.table_name;
SQLite DROP VIEW 语句
DROP INDEX database_name.view_name;
SQLite DROP TRIGGER 语句
DROP INDEX database_name.trigger_name;
SQLite EXISTS 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name EXISTS (SELECT * FROM table_name );
SQLite EXPLAIN 语句
EXPLAIN INSERT statement...; or EXPLAIN QUERY PLAN SELECT statement...;
SQLite GLOB 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };
SQLite GROUP BY 子句
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;
SQLite HAVING 子句
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);
SQLite INSERT INTO 语句
INSERT INTO table_name( column1, column2....columnN) VALUES ( value1, value2....valueN);
SQLite IN 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N);
SQLite Like 子句
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };
SQLite NOT IN 子句
SELECT column1, column2....columnN FROM table_name WHERE column_name NOT IN (val-1, val-2,...val-N);
SQLite ORDER BY 子句
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};
SQLite PRAGMA 语句
PRAGMA pragma_name; For example: PRAGMA page_size; PRAGMA cache_size = 1024; PRAGMA table_info(table_name);
SQLite RELEASE SAVEPOINT 语句
RELEASE savepoint_name;
SQLite REINDEX 语句
REINDEX collation_name; REINDEX database_name.index_name; REINDEX database_name.table_name;
SQLite ROLLBACK 语句
ROLLBACK; or ROLLBACK TO SAVEPOINT savepoint_name;
SQLite SAVEPOINT 语句
SAVEPOINT savepoint_name;
SQLite SELECT 语句
SELECT column1, column2....columnN FROM table_name;
SQLite 更新语句
UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN [ WHERE CONDITION ];
SQLite VACUUM 语句
VACUUM;
SQLite WHERE 子句
SELECT column1, column2....columnN FROM table_name WHERE CONDITION;
SQLite – 数据类型
SQLite 数据类型是一个属性，用于指定任何对象的数据类型。SQLite 中的每个列、变量和表达式都有相关的数据类型。
您将在创建表时使用这些数据类型。SQLite 使用更通用的动态类型系统。在 SQLite 中，值的数据类型与值本身相关联，而不是与其容器相关联。
SQLite 存储类
存储在 SQLite 数据库中的每个值都具有以下存储类之一 –
| Sr.No. | 存储类别和描述 |
|---|---|
| 1 |
NULL 该值为 NULL 值。 |
| 2 |
INTEGER 该值是一个有符号整数，根据值的大小存储在 1、2、3、4、6 或 8 个字节中。 |
| 3 |
REAL 该值是一个浮点值，存储为一个 8 字节的 IEEE 浮点数。 |
| 4 |
TEXT 该值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储 |
| 5 |
BLOB 该值是一组数据，完全按照输入的方式存储。 |
SQLite 存储类比数据类型更通用。例如，INTEGER 存储类包括 6 种不同长度的不同整数数据类型。
SQLite 关联类型
SQLite 支持列的类型关联概念。任何列仍然可以存储任何类型的数据，但列的首选存储类称为关联性。SQLite3 数据库中的每个表列都分配了以下类型亲和力之一 –
| Sr.No. | 亲和力和描述 |
|---|---|
| 1 |
TEXT 此列使用存储类 NULL、TEXT 或 BLOB 存储所有数据。 |
| 2 |
NUMERIC 此列可能包含使用所有五个存储类别的值。 |
| 3 |
INTEGER 行为与具有 NUMERIC 关联的列相同，但在 CAST 表达式中除外。 |
| 4 |
REAL 行为类似于具有 NUMERIC 关联的列，除了它强制整数值转换为浮点表示。 |
| 5 |
NONE 具有亲缘关系 NONE 的列不喜欢一种存储类而不是另一种，并且不会尝试将数据从一种存储类强制转换为另一种存储类。 |
SQLite 关联和类型名称
下表列出了在创建具有相应应用亲和性的 SQLite3 表时可以使用的各种数据类型名称。
| Data Type | 亲和力 |
|---|---|
|
整数 |
|
文本 |
|
没有任何 |
|
真实的 |
|
数字 |
布尔数据类型
SQLite 没有单独的布尔存储类。相反，布尔值存储为整数 0 (false) 和 1 (true)。
日期和时间数据类型
SQLite 没有单独的存储类来存储日期和/或时间，但 SQLite 能够将日期和时间存储为 TEXT、REAL 或 INTEGER 值。
| Sr.No. | 存储类别和日期格式 |
|---|---|
| 1 |
TEXT 格式为“YYYY-MM-DD HH:MM:SS.SSS”的日期 |
| 2 |
REAL 公元前 4714 年 11 月 24 日格林威治中午以来的天数 |
| 3 |
INTEGER 自 1970-01-01 00:00:00 UTC 以来的秒数 |
您可以选择以任何这些格式存储日期和时间，并使用内置的日期和时间函数在格式之间自由转换。
SQLite – 创建数据库
在 SQLite 中，sqlite3命令用于创建新的 SQLite 数据库。创建数据库不需要任何特殊权限。
句法
以下是用于创建数据库的 sqlite3 命令的基本语法： –
$sqlite3 DatabaseName.db
始终，数据库名称在 RDBMS 中应该是唯一的。
例子
如果你想创建一个新的数据库 <testDB.db>，那么 SQLITE3 语句如下：
$sqlite3 testDB.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
上述命令将在当前目录中创建一个文件testDB.db。该文件将被 SQLite 引擎用作数据库。如果您在创建数据库时注意到，成功创建数据库文件后，sqlite3 命令会提供一个sqlite>提示。
创建数据库后，您可以使用以下 SQLite .databases命令在数据库列表中对其进行验证。
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db
您将使用 SQLite .quit命令退出 sqlite 提示符，如下所示 –
sqlite>.quit $
.dump 命令
您可以使用.dump dot 命令在命令提示符下使用以下 SQLite 命令将完整的数据库导出到文本文件中。
$sqlite3 testDB.db .dump > testDB.sql
上面的命令会将testDB.db数据库的全部内容转换为 SQLite 语句并将其转储到 ASCII 文本文件testDB.sql 中。您可以通过简单的方式从生成的 testDB.sql 执行恢复，如下所示 –
$sqlite3 testDB.db < testDB.sql
此时你的数据库是空的，所以一旦你的数据库中的表和数据很少，你可以尝试以上两个过程。现在，让我们继续下一章。
SQLite – ATTACH 数据库
考虑一种情况，当您有多个可用的数据库并且您想一次使用其中的任何一个时。SQLite ATTACH DATABASE语句用于选择特定的数据库，执行此命令后，所有 SQLite 语句都将在附加数据库下执行。
句法
以下是 SQLite ATTACH DATABASE 语句的基本语法。
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';
如果数据库尚未创建，上述命令还将创建一个数据库，否则它只会将数据库文件名附加到逻辑数据库“别名”。
例子
如果要附加现有数据库testDB.db，则 ATTACH DATABASE 语句如下 –
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
使用 SQLite .database命令显示附加的数据库。
sqlite> .database seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db
数据库名称main和temp保留给主数据库和数据库以保存临时表和其他临时数据对象。这两个数据库名称都存在于每个数据库连接中，不应用于附件，否则您将收到以下警告消息。
sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP'; Error: database TEMP is already in use sqlite> ATTACH DATABASE 'testDB.db' as 'main'; Error: database TEMP is already in use
SQLite – DETACH 数据库
SQLite DETACH DATABASE语句用于将命名数据库与之前使用 ATTACH 语句连接的数据库连接分离和分离。如果同一个数据库文件附加了多个别名，则 DETACH 命令将仅断开给定的名称，其余的附件仍将继续。您不能分离主数据库或临时数据库。
如果数据库是内存数据库或临时数据库，数据库将被破坏，内容将丢失。
句法
以下是 SQLite DETACH DATABASE ‘Alias-Name’ 语句的基本语法。
DETACH DATABASE 'Alias-Name';
此处，’Alias-Name’ 与您在使用 ATTACH 语句附加数据库时使用的别名相同。
例子
假设您有一个数据库，它是您在上一章中创建的，并使用.database命令附加了“test”和“currentDB” 。
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db 3 currentDB /home/sqlite/testDB.db
让我们尝试使用以下命令从 testDB.db 中分离“currentDB”。
sqlite> DETACH DATABASE 'currentDB';
现在，如果您检查当前附件，您会发现 testDB.db 仍然与 ‘test’ 和 ‘main’ 相连。
sqlite>.databases seq name file --- --------------- ---------------------- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db
SQLite – 创建表
SQLite CREATE TABLE语句用于在任何给定数据库中创建新表。创建基本表涉及命名表并定义其列和每列的数据类型。
句法
以下是 CREATE TABLE 语句的基本语法。
CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype );
CREATE TABLE 是告诉数据库系统创建新表的关键字。表的唯一名称或标识符遵循 CREATE TABLE 语句。或者，您可以指定database_name和table_name。
例子
以下是创建以 ID 作为主键的 COMPANY 表的示例，NOT NULL 是约束，表明在此表中创建记录时这些字段不能为 NULL。
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
让我们再创建一张表，我们将在后续章节的练习中使用它。
sqlite> CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
您可以使用 SQLite 命令.tables命令验证您的表是否已成功创建，该命令将用于列出附加数据库中的所有表。
sqlite>.tables COMPANY DEPARTMENT
在这里，您可以看到 COMPANY 表两次，因为它显示了主数据库的 COMPANY 表和为 testDB.db 创建的“test”别名的 test.COMPANY 表。您可以使用以下 SQLite .schema命令获取有关表的完整信息。
sqlite>.schema COMPANY CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
SQLite – 删除表
SQLite DROP TABLE语句用于删除表定义以及该表的所有关联数据、索引、触发器、约束和权限规范。
使用此命令时必须小心，因为一旦表被删除，表中的所有可用信息也将永远丢失。
句法
以下是 DROP TABLE 语句的基本语法。您可以选择指定数据库名称和表名，如下所示 –
DROP TABLE database_name.table_name;
例子
让我们先验证 COMPANY 表，然后将其从数据库中删除。
sqlite>.tables COMPANY test.COMPANY
这意味着 COMPANY 表在数据库中可用，所以让我们按如下方式删除它 –
sqlite>DROP TABLE COMPANY; sqlite>
现在，如果您尝试 .TABLES 命令，那么您将不再找到 COMPANY 表。
sqlite>.tables sqlite>
它什么也没显示，这意味着您的数据库中的表已成功删除。
SQLite – 插入查询
SQLite INSERT INTO语句用于将新的数据行添加到数据库中的表中。
句法
以下是 INSERT INTO 语句的两种基本语法。
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
此处，column1、column2、…columnN 是表中要插入数据的列的名称。
如果要为表的所有列添加值，则可能不需要在 SQLite 查询中指定列名称。但是，请确保值的顺序与表中的列顺序相同。SQLite INSERT INTO 语法如下：
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
例子
考虑您已经在 testDB.db 中创建了 COMPANY 表，如下所示 –
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
现在，以下语句将在 COMPANY 表中创建六个记录。
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
您可以使用第二种语法在 COMPANY 表中创建记录，如下所示 –
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
以上所有语句都会在 COMPANY 表中创建以下记录。在下一章中，您将学习如何从表中显示所有这些记录。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
使用另一个表填充一个表
您可以通过另一个表的 select 语句将数据填充到一个表中，前提是另一个表具有一组字段，这些字段是填充第一个表所必需的。这是语法 –
INSERT INTO first_table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM second_table_name [WHERE condition];
现在，您可以跳过上面的语句。首先，让我们学习 SELECT 和 WHERE 子句，这些子句将在后续章节中介绍。
SQLite – SELECT 查询
SQLite SELECT语句用于从 SQLite 数据库表中获取数据，该表以结果表的形式返回数据。这些结果表也称为结果集。
句法
以下是 SQLite SELECT 语句的基本语法。
SELECT column1, column2, columnN FROM table_name;
这里， column1, column2 … 是表的字段，您要获取其值。如果要获取该字段中的所有可用字段，则可以使用以下语法 –
SELECT * FROM table_name;
例子
考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是使用 SELECT 语句获取和显示所有这些记录的示例。在这里，前三个命令已用于设置正确格式化的输出。
sqlite>.header on sqlite>.mode column sqlite> SELECT * FROM COMPANY;
最后，您将得到以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
如果您只想获取 COMPANY 表的选定字段，请使用以下查询 –
sqlite> SELECT ID, NAME, SALARY FROM COMPANY;
上述查询将产生以下结果。
ID NAME SALARY ---------- ---------- ---------- 1 Paul 20000.0 2 Allen 15000.0 3 Teddy 20000.0 4 Mark 65000.0 5 David 85000.0 6 Kim 45000.0 7 James 10000.0
设置输出列宽
有时，由于要显示的列的默认宽度而发生的.mode 列的情况下，您将面临与截断输出相关的问题。您可以做的是，您可以使用.width num, num….命令设置列可显示列宽，如下所示 –
sqlite>.width 10, 20, 10 sqlite>SELECT * FROM COMPANY;
上面的.width命令设置第一列宽度为 10，第二列宽度为 20，第三列宽度为 10。最后，上面的 SELECT 语句将给出以下结果。
ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
架构信息
由于所有点命令都可以在 SQLite 提示符下使用，因此在使用 SQLite 编程时，您将使用以下带有sqlite_master表的SELECT 语句列出数据库中创建的所有表。
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
假设您的 testDB.db 中只有 COMPANY 表，这将产生以下结果。
tbl_name ---------- COMPANY
您可以列出有关 COMPANY 表的完整信息，如下所示 –
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
假设您的 testDB.db 中只有 COMPANY 表，这将产生以下结果。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL )
SQLite – 运算符
SQLite 中的运算符是什么？
运算符是保留字或字符，主要用于 SQLite 语句的 WHERE 子句中以执行操作，例如比较和算术运算。
运算符用于在 SQLite 语句中指定条件并用作语句中多个条件的连接。
- 算术运算符
- 比较运算符
- 逻辑运算符
- 按位运算符
SQLite 算术运算符
假设变量a 为10，变量b 为20，那么 SQLite 算术运算符将按如下方式使用 –
| Operator | 描述 | 例子 |
|---|---|---|
| + (Addition) | 在运算符的任一侧添加值 | a + b 将给 30 |
| – (Subtraction) | 从左手操作数中减去右手操作数 | a – b 将给出 -10 |
| * (Multiplication) | 将运算符两侧的值相乘 | a * b 将给出 200 |
| / (Division) | 将左手操作数除以右手操作数 | b / a 会给 2 |
| % (Modulus) | 将左手操作数除以右手操作数并返回余数 | b % a 将给出 0 |
SQLite 比较运算符
假设变量a保存10个变量b保存20个，那么SQLite比较运算符的使用如下
| Operator | 描述 | 例子 |
|---|---|---|
| == | 检查两个操作数的值是否相等，如果相等则条件成立。 | (a == b) 不是真的。 |
| = | 检查两个操作数的值是否相等，如果相等则条件成立。 | (a = b) 不正确。 |
| != | 检查两个操作数的值是否相等，如果值不相等，则条件成立。 | (a != b) 是真的。 |
| <> | 检查两个操作数的值是否相等，如果值不相等，则条件成立。 | (a <> b) 是真的。 |
| > | 检查左操作数的值是否大于右操作数的值，如果是，则条件成立。 | (a > b) 不正确。 |
| < | 检查左操作数的值是否小于右操作数的值，如果是，则条件成立。 | (a < b) 是真的。 |
| >= | 检查左操作数的值是否大于或等于右操作数的值，如果是，则条件成立。 | (a >= b) 不是真的。 |
| <= | 检查左操作数的值是否小于或等于右操作数的值，如果是，则条件成立。 | (a <= b) 是真的。 |
| !< | 检查左操作数的值是否不小于右操作数的值，如果是则条件成立。 | (a !< b) 是假的。 |
| !> | 检查左操作数的值是否不大于右操作数的值，如果是，则条件成立。 | (a !> b) 是真的。 |
SQLite 逻辑运算符
这是 SQLite 中可用的所有逻辑运算符的列表。
| Sr.No. | 运算符和描述 |
|---|---|
| 1 |
AND The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause. |
| 2 |
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. |
| 3 |
EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria. |
| 4 |
IN The IN operator is used to compare a value to a list of literal values that have been specified. |
| 5 |
NOT IN The negation of IN operator which is used to compare a value to a list of literal values that have been specified. |
| 6 |
LIKE The LIKE operator is used to compare a value to similar values using wildcard operators. |
| 7 |
GLOB The GLOB operator is used to compare a value to similar values using wildcard operators. Also, GLOB is case sensitive, unlike LIKE. |
| 8 |
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is negate operator. |
| 9 |
OR The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause. |
| 10 |
IS NULL The NULL operator is used to compare a value with a NULL value. |
| 11 |
IS The IS operator work like = |
| 12 |
IS NOT The IS operator work like != |
| 13 |
|| Adds two different strings and make new one. |
| 14 |
UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates). |
SQLite 按位运算符
按位运算符作用于位并执行逐位运算。以下是&和|的真值表 .
| p | q | p&q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
假设如果A = 60; 并且B = 13，那么在二进制格式中，它们将如下 –
A = 0011 1100
乙 = 0000 1101
—————–
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
SQLite 语言支持的位运算符如下表所示。假设变量A 为60，变量B 为13，然后 –
| Operator | 描述 | 例子 |
|---|---|---|
| & | 如果两个操作数中都存在二进制 AND 运算符，则将其复制到结果中。 | (A & B) 将给出 12，即 0000 1100 |
| | | 二进制 OR 运算符复制一点，如果它存在于任一操作数中。 | (A | B) 将给出 61，即 0011 1101 |
| ~ | Binary Ones Complement Operator 是一元的，具有“翻转”位的效果。 | (~A ) 将给出 -61，它是 1100 0011 由于带符号的二进制数，以 2 的补码形式表示 |
| << | 二元左移运算符。左操作数值向左移动右操作数指定的位数。 | A << 2 将给出 240，即 1111 0000 |
| >> | 二元右移运算符。左操作数的值向右移动右操作数指定的位数。 | A >> 2 将给出 15，即 0000 1111 |
SQLite – 表达式
表达式是一个或多个值、运算符和计算结果的 SQL 函数的组合。
SQL 表达式就像公式，它们是用查询语言编写的。您还可以使用查询数据库中的特定数据集。
句法
考虑 SELECT 语句的基本语法如下 –
SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION];
以下是不同类型的 SQLite 表达式。
SQLite – 布尔表达式
SQLite 布尔表达式根据匹配的单个值获取数据。以下是语法 –
SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION;
考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个简单的例子，展示了 SQLite 布尔表达式的用法 –
sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 James 24 Houston 10000.0
SQLite – 数字表达式
这些表达式用于在任何查询中执行任何数学运算。以下是语法 –
SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;
这里， numeric_expression 用于数学表达式或任何公式。下面是一个简单的例子，展示了 SQLite 数字表达式的用法。
sqlite> SELECT (15 + 6) AS ADDITION ADDITION = 21
有几个内置函数，例如avg()、sum()、count()等，用于对表或特定表列执行所谓的聚合数据计算。
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY; RECORDS = 7
SQLite – 日期表达式
日期表达式返回当前系统日期和时间值。这些表达式用于各种数据操作。
sqlite> SELECT CURRENT_TIMESTAMP; CURRENT_TIMESTAMP = 2013-03-17 10:43:35
SQLite – WHERE 子句
SQLite WHERE子句用于在从一个表或多个表中获取数据时指定条件。
如果满足给定条件，则表示为真，则返回表中的特定值。您将不得不使用 WHERE 子句来过滤记录并仅获取必要的记录。
WHERE 子句不仅用于 SELECT 语句，还用于 UPDATE、DELETE 语句等，这些将在后续章节中介绍。
句法
以下是带有 WHERE 子句的 SQLite SELECT 语句的基本语法。
SELECT column1, column2, columnN FROM table_name WHERE [condition]
例子
您可以使用比较或逻辑运算符（例如 >、<、=、LIKE、NOT 等）指定条件。考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
下面是一个简单的例子，展示了 SQLite 逻辑运算符的用法。下面的SELECT语句列出了所有在那里的年龄大于或等于25的记录和工资大于或等于65000.00。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下 SELECT 语句列出所有 AGE 大于或等于 25或工资大于或等于 65000.00 的记录。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下 SELECT 语句列出所有 AGE 不为 NULL 的记录，这意味着所有记录，因为没有一条记录的 AGE 等于 NULL。
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下 SELECT 语句列出了 NAME 以 ‘Ki’ 开头的所有记录，与 ‘Ki’ 之后的内容无关。
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
以下 SELECT 语句列出了 NAME 以 ‘Ki’ 开头的所有记录，与 ‘Ki’ 之后的内容无关。
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
以下 SELECT 语句列出了 AGE 值为 25 或 27 的所有记录。
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下 SELECT 语句列出了所有 AGE 值既不是 25 也不是 27 的记录。
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 ); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下 SELECT 语句列出了 AGE 值在 25 和 27 之间的所有记录。
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下 SELECT 语句使用 SQL 子查询，其中子查询查找 AGE 字段 SALARY > 65000 的所有记录，随后使用 WHERE 子句与 EXISTS 运算符一起列出所有来自外部查询的 AGE 存在的记录在子查询返回的结果中 –
sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000); AGE ---------- 32 25 23 25 27 22 24
以下 SELECT 语句使用 SQL 子查询，其中子查询查找 AGE 字段具有 SALARY > 65000 的所有记录，随后将 WHERE 子句与 > 运算符一起使用以列出来自外部查询的 AGE 更大的所有记录比子查询返回的结果中的年龄。
sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
SQLite – AND & OR 运算符
SQLite AND & OR运算符用于编译多个条件以缩小 SQLite 语句中所选数据的范围。这两个运算符称为连接运算符。
这些运算符提供了一种在同一 SQLite 语句中与不同运算符进行多次比较的方法。
AND 运算符
在与运营商允许多个条件中存在一个SQLite语句的WHERE子句。使用 AND 运算符时，当所有条件都为真时，将假定完整条件为真。例如，只有当条件 1 和条件 2 都为真时，[条件 1] 和 [条件 2] 才会为真。
句法
以下是带有 WHERE 子句的 AND 运算符的基本语法。
SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];
您可以使用 AND 运算符组合N个条件。对于 SQLite 语句要执行的操作，无论是事务还是查询，由 AND 分隔的所有条件都必须为 TRUE。
例子
考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
下面的SELECT语句列出了所有在那里的年龄大于或等于25的记录和工资大于或等于65000.00。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
OR 运算符
OR 运算符还用于在 SQLite 语句的 WHERE 子句中组合多个条件。使用 OR 运算符时，当至少有任何条件为真时，将假定完整条件为真。例如，如果条件 1 或条件 2 为真，则 [condition1] OR [condition2] 将为真。
句法
以下是带有 WHERE 子句的 OR 运算符的基本语法。
SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]
您可以使用 OR 运算符组合N个条件。对于 SQLite 语句要执行的操作，无论是事务还是查询，只有任何一个由 OR 分隔的条件必须为 TRUE。
例子
考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下 SELECT 语句列出所有 AGE 大于或等于 25或工资大于或等于 65000.00 的记录。
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite – 更新查询
SQLite UPDATE Query 用于修改表中的现有记录。您可以将 WHERE 子句与 UPDATE 查询一起使用来更新选定的行，否则将更新所有行。
句法
以下是带有 WHERE 子句的 UPDATE 查询的基本语法。
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
您可以使用 AND 或 OR 运算符组合N个条件。
例子
考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将为 ID 为 6 的客户更新 ADDRESS。
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
现在，COMPANY 表将具有以下记录。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 Texas 45000.0 7 James 24 Houston 10000.0
如果要修改 COMPANY 表中的所有 ADDRESS 和 SALARY 列值，则不需要使用 WHERE 子句，UPDATE 查询如下 –
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;
现在，COMPANY 表将包含以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 Texas 20000.0 2 Allen 25 Texas 20000.0 3 Teddy 23 Texas 20000.0 4 Mark 25 Texas 20000.0 5 David 27 Texas 20000.0 6 Kim 22 Texas 20000.0 7 James 24 Texas 20000.0
SQLite – 删除查询
SQLite DELETE Query 用于从表中删除现有记录。您可以使用带有 DELETE 查询的 WHERE 子句删除所选行，否则将删除所有记录。
句法
以下是带有 WHERE 子句的 DELETE 查询的基本语法。
DELETE FROM table_name WHERE [condition];
您可以使用 AND 或 OR 运算符组合N个条件。
例子
考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将删除 ID 为 7 的客户。
sqlite> DELETE FROM COMPANY WHERE ID = 7;
现在 COMPANY 表将有以下记录。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0
如果要删除 COMPANY 表中的所有记录，则不需要在 DELETE 查询中使用 WHERE 子句，如下所示 –
sqlite> DELETE FROM COMPANY;
现在，COMPANY 表没有任何记录，因为所有记录都已被 DELETE 语句删除。
SQLite – LIKE 子句
SQLite LIKE运算符用于使用通配符将文本值与模式进行匹配。如果搜索表达式可以与模式表达式匹配，则 LIKE 运算符将返回 true，即 1。有两个通配符与 LIKE 运算符结合使用 –
- 百分号 (%)
- 下划线 (_)
百分号代表零、一或多个数字或字符。下划线代表单个数字或字符。这些符号可以组合使用。
句法
以下是 % 和 _ 的基本语法。
SELECT FROM table_name WHERE column LIKE 'XXXX%' or SELECT FROM table_name WHERE column LIKE '%XXXX%' or SELECT FROM table_name WHERE column LIKE 'XXXX_' or SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE '_XXXX_'
您可以使用 AND 或 OR 运算符组合N个条件。这里，XXXX 可以是任何数字或字符串值。
例子
下表列出了一些示例，显示 WHERE 部分具有不同的 LIKE 子句，其中包含 ‘%’ 和 ‘_’ 运算符。
| Sr.No. | 声明和说明 |
|---|---|
| 1 |
WHERE SALARY LIKE ‘200%’ 查找任何以 200 开头的值 |
| 2 |
WHERE SALARY LIKE ‘%200%’ 查找在任何位置具有 200 的任何值 |
| 3 |
WHERE SALARY LIKE ‘_00%’ 查找第二个和第三个位置为 00 的任何值 |
| 4 |
WHERE SALARY LIKE ‘2_%_%’ 查找任何以 2 开头且长度至少为 3 个字符的值 |
| 5 |
WHERE SALARY LIKE ‘%2’ 查找任何以 2 结尾的值 |
| 6 |
WHERE SALARY LIKE ‘_2%3’ 查找第二个位置为 2 并以 3 结尾的任何值 |
| 7 |
WHERE SALARY LIKE ‘2___3’ 查找以 2 开头并以 3 结尾的五位数中的任何值 |
让我们举一个真实的例子，考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将显示 COMPANY 表中 AGE 以 2 开头的所有记录。
sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将显示 COMPANY 表中的所有记录，其中 ADDRESS 将在文本中包含一个连字符 (-)。
sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0
SQLite – GLOB 子句
SQLite GLOB运算符用于仅将文本值与使用通配符的模式进行匹配。如果搜索表达式可以与模式表达式匹配，则 GLOB 运算符将返回 true，即 1。与 LIKE 运算符不同，GLOB 区分大小写，它遵循 UNIX 的语法来指定以下通配符。
- 星号 (*)
- 问号（？）
星号 (*) 代表零个或多个数字或字符。问号 (?) 表示单个数字或字符。
句法
以下是*和?的基本语法。.
SELECT FROM table_name WHERE column GLOB 'XXXX*' or SELECT FROM table_name WHERE column GLOB '*XXXX*' or SELECT FROM table_name WHERE column GLOB 'XXXX?' or SELECT FROM table_name WHERE column GLOB '?XXXX' or SELECT FROM table_name WHERE column GLOB '?XXXX?' or SELECT FROM table_name WHERE column GLOB '????'
您可以使用 AND 或 OR 运算符组合N个条件。这里，XXXX 可以是任何数字或字符串值。
例子
下表列出了一些示例，显示 WHERE 部分具有不同的 LIKE 子句，带有“*”和“?” 运营商。
| Sr.No. | 声明和说明 |
|---|---|
| 1 |
WHERE SALARY GLOB ‘200*’ 查找任何以 200 开头的值 |
| 2 |
WHERE SALARY GLOB ‘*200*’ 查找在任何位置具有 200 的任何值 |
| 3 |
WHERE SALARY GLOB ‘?00*’ 查找第二个和第三个位置为 00 的任何值 |
| 4 |
WHERE SALARY GLOB ‘2??’ 查找任何以 2 开头且长度至少为 3 个字符的值 |
| 5 |
WHERE SALARY GLOB ‘*2’ 查找任何以 2 结尾的值 |
| 6 |
WHERE SALARY GLOB ‘?2*3’ 查找第二个位置为 2 并以 3 结尾的任何值 |
| 7 |
WHERE SALARY GLOB ‘2???3’ 查找以 2 开头并以 3 结尾的五位数字中的任何值 |
让我们举一个真实的例子，考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将显示 COMPANY 表中的所有记录，其中 AGE 以 2 开头。
sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2*';
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将显示 COMPANY 表中的所有记录，其中 ADDRESS 在文本中带有连字符 (-) –
sqlite> SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*';
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0
SQLite – 限制条款
SQLite LIMIT子句用于限制 SELECT 语句返回的数据量。
句法
以下是带有 LIMIT 子句的 SELECT 语句的基本语法。
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows]
以下是 LIMIT 子句与 OFFSET 子句一起使用时的语法。
SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] OFFSET [row num]
SQLite 引擎将返回从下一行开始到给定 OFFSET 的行，如下面的最后一个示例所示。
例子
考虑具有以下记录的 COMPANY 表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它根据您要从表中提取的行数来限制表中的行。
sqlite> SELECT * FROM COMPANY LIMIT 6;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0
但是在某些情况下，您可能需要从特定偏移量中获取一组记录。这是一个示例，它从 3 个记录开始选取 3 个记录rd 位置。
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite – ORDER BY 子句
SQLite ORDER BY子句用于根据一列或多列按升序或降序对数据进行排序。
句法
以下是 ORDER BY 子句的基本语法。
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
您可以在 ORDER BY 子句中使用多个列。确保您使用的任何列进行排序，该列应该在列列表中可用。
例子
考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是一个示例，它将按 SALARY 降序对结果进行排序。
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下是一个示例，它将按 NAME 和 SALARY 按降序对结果进行排序。
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 5 David 27 Texas 85000.0 7 James 24 Houston 10000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0
以下是一个示例，它将按 NAME 按降序对结果进行排序。
sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 3 Teddy 23 Norway 20000.0 1 Paul 32 California 20000.0 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 5 David 27 Texas 85000.0 2 Allen 25 Texas 15000.0
SQLite – GROUP BY 子句
SQLite GROUP BY子句与 SELECT 语句配合使用，将相同的数据分组。
GROUP BY 子句在 SELECT 语句中的 WHERE 子句之后，并在 ORDER BY 子句之前。
句法
以下是 GROUP BY 子句的基本语法。GROUP BY 子句必须跟在 WHERE 子句中的条件之后，并且必须在 ORDER BY 子句之前（如果使用）。
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
您可以在 GROUP BY 子句中使用多个列。确保您使用的任何列进行分组，该列应该在列列表中可用。
例子
考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
如果您想知道每个客户的工资总额，那么 GROUP BY 查询将如下 –
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
这将产生以下结果 –
NAME SUM(SALARY) ---------- ----------- Allen 15000.0 David 85000.0 James 10000.0 Kim 45000.0 Mark 65000.0 Paul 20000.0 Teddy 20000.0
现在，让我们使用以下 INSERT 语句在 COMPANY 表中再创建三个记录。
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 ); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 ); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );
现在，我们的表有以下重复名称的记录。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0
同样，让我们使用相同的语句对使用 NAME 列的所有记录进行分组，如下所示 –
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
这将产生以下结果。
NAME SUM(SALARY) ---------- ----------- Allen 15000 David 85000 James 20000 Kim 45000 Mark 65000 Paul 40000 Teddy 20000
让我们使用 ORDER BY 子句和 GROUP BY 子句如下 –
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;
这将产生以下结果。
NAME SUM(SALARY) ---------- ----------- Teddy 20000 Paul 40000 Mark 65000 Kim 45000 James 20000 David 85000 Allen 15000
SQLite – HAVING 子句
HAVING 子句使您能够指定过滤哪些组结果出现在最终结果中的条件。
WHERE 子句在选定的列上放置条件，而 HAVING 子句将条件放置在由 GROUP BY 子句创建的组上。
句法
以下是 SELECT 查询中 HAVING 子句的位置。
SELECT FROM WHERE GROUP BY HAVING ORDER BY
HAVING 子句在查询中必须跟在 GROUP BY 子句之后，并且如果使用，还必须在 ORDER BY 子句之前。以下是 SELECT 语句的语法，包括 HAVING 子句。
SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2
例子
考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0
以下是示例，它将显示名称计数小于 2 的记录。
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000 5 David 27 Texas 85000 6 Kim 22 South-Hall 45000 4 Mark 25 Rich-Mond 65000 3 Teddy 23 Norway 20000
以下是示例，它将显示名称计数大于 2 的记录。
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 10 James 45 Texas 5000
SQLite – DISTINCT 关键字
SQLite DISTINCT关键字与 SELECT 语句结合使用以消除所有重复记录并仅获取唯一记录。
可能会出现一个表中有多个重复记录的情况。在获取此类记录时，只获取唯一记录而不是获取重复记录更有意义。
句法
以下是 DISTINCT 关键字消除重复记录的基本语法。
SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]
例子
考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 8 Paul 24 Houston 20000.0 9 James 44 Norway 5000.0 10 James 45 Texas 5000.0
首先，让我们看看以下 SELECT 查询如何返回重复的工资记录。
sqlite> SELECT name FROM COMPANY;
这将产生以下结果。
NAME ---------- Paul Allen Teddy Mark David Kim James Paul James James
现在，让我们在上面的 SELECT 查询中使用DISTINCT关键字并查看结果。
sqlite> SELECT DISTINCT name FROM COMPANY;
这将产生以下结果，其中没有重复条目。
NAME ---------- Paul Allen Teddy Mark David Kim James
SQLite – PRAGMA
SQLite PRAGMA命令是一个特殊的命令，用于控制 SQLite 环境中的各种环境变量和状态标志。可以读取 PRAGMA 值，也可以根据需要进行设置。
句法
要查询当前的 PRAGMA 值，只需提供 pragma 的名称。
PRAGMA pragma_name;
要为 PRAGMA 设置新值，请使用以下语法。
PRAGMA pragma_name = value;
设置模式可以是名称或等效的整数，但返回值将始终是整数。
auto_vacuum 编译指示
该auto_vacuum编译获取或设置自动真空模式。以下是简单的语法。
PRAGMA [database.]auto_vacuum; PRAGMA [database.]auto_vacuum = mode;
其中模式可以是以下任何一种 –
| Sr.No. | Pragma 值和描述 |
|---|---|
| 1 |
0 or NONE 自动真空已禁用。这是默认模式，这意味着数据库文件永远不会缩小，除非使用 VACUUM 命令手动清理它。 |
| 2 |
1 or FULL Auto-vacuum 已启用且全自动，这允许在从数据库中删除数据时缩小数据库文件。 |
| 3 |
2 or INCREMENTAL 自动真空已启用，但必须手动激活。在这种模式下，引用数据被保留，但空闲页面只是简单地放在空闲列表中。可以随时使用incremental_vacuum pragma恢复这些页面。 |
cache_size 编译指示
该CACHE_SIZE编译可以获取或临时设置在内存中的页面缓存的最大尺寸。以下是简单的语法。
PRAGMA [database.]cache_size; PRAGMA [database.]cache_size = pages;
该页面值代表的高速缓存中的页面数。内置页面缓存的默认大小为 2,000 页，最小大小为 10 页。
case_sensitive_like 编译指示
所述case_sensitive_like pragma控制内置LIKE表达的情况下，灵敏度。默认情况下，此编译指示为 false，这意味着内置 LIKE 运算符会忽略字母大小写。以下是简单的语法。
PRAGMA case_sensitive_like = [true|false];
无法查询此 pragma 的当前状态。
count_changes 编译指示
count_changes pragma 获取或设置数据操作语句如 INSERT、UPDATE 和 DELETE 的返回值。以下是简单的语法。
PRAGMA count_changes; PRAGMA count_changes = [true|false];
默认情况下，此 pragma 为 false，并且这些语句不返回任何内容。如果设置为 true，则每个提到的语句都将返回一个单列单行表，该表由一个整数值组成，指示受操作影响的行。
database_list 编译指示
该database_list编译将用于列表中上下所有的数据库连接。以下是简单的语法。
PRAGMA database_list;
该编译指示将返回一个三列表，每个打开或附加的数据库一行，给出数据库序列号、其名称和相关文件。
编码编译指示
该编码编译控制字符串是如何编码，并存储在数据库中的文件。以下是简单的语法。
PRAGMA encoding; PRAGMA encoding = format;
格式值可以是UTF-8、UTF-16le或UTF-16be 之一。
freelist_count 编译指示
该freelist_count编译返回一个整数，表示许多数据库页当前如何标记为空闲和可用的。以下是简单的语法。
PRAGMA [database.]freelist_count;
格式值可以是UTF-8、UTF-16le或UTF-16be 之一。
index_info 编译指示
该index_info有关数据库索引编译返回的信息。以下是简单的语法。
PRAGMA [database.]index_info( index_name );
结果集将为索引中包含的每一列包含一行，给出列序列、表中的列索引和列名。
index_list 编译指示
index_list pragma 列出与表关联的所有索引。以下是简单的语法。
PRAGMA [database.]index_list( table_name );
结果集将为每个索引包含一行，给出索引序列、索引名称和指示索引是否唯一的标志。
journal_mode 编译指示
该journal_mode编译获取或设置如何控制日志文件存储和处理的日志模式。以下是简单的语法。
PRAGMA journal_mode; PRAGMA journal_mode = mode; PRAGMA database.journal_mode; PRAGMA database.journal_mode = mode;
下表列出了五种受支持的日志模式。
| Sr.No. | Pragma 值和描述 |
|---|---|
| 1 |
DELETE 这是默认模式。在交易结束时，日志文件被删除。 |
| 2 |
TRUNCATE 日志文件被截断为零字节的长度。 |
| 3 |
PERSIST 日志文件保留在原处，但标题被覆盖以指示日志不再有效。 |
| 4 |
MEMORY 日志记录保存在内存中，而不是磁盘上。 |
| 5 |
OFF 不保留日志记录。 |
max_page_count 编译指示
该max_page_count编译获取或设置允许的最大页数为数据库。以下是简单的语法。
PRAGMA [database.]max_page_count; PRAGMA [database.]max_page_count = max_page;
默认值为 1,073,741,823，即 1 giga-page，这意味着如果默认 1 KB 页面大小，这允许数据库增长到 1 TB。
page_count 编译指示
该PAGE_COUNT编译回报的数据库的当前页数。以下是简单的语法 –
PRAGMA [database.]page_count;
数据库文件的大小应该是 page_count * page_size。
page_size 编译指示
该PAGE_SIZE编译获取或设置数据库页面的大小。以下是简单的语法。
PRAGMA [database.]page_size; PRAGMA [database.]page_size = bytes;
默认情况下，允许的大小为 512、1024、2048、4096、8192、16384 和 32768 字节。更改现有数据库页面大小的唯一方法是设置页面大小，然后立即 VACUUM 数据库。
parser_trace 编译指示
所述parser_trace pragma控制打印调试状态，因为它解析SQL命令。以下是简单的语法。
PRAGMA parser_trace = [true|false];
默认情况下，它设置为 false，但通过将其设置为 true 来启用时，SQL 解析器将在解析 SQL 命令时打印其状态。
recursive_triggers 编译指示
该RECURSIVE_TRIGGERS编译获取或设置递归触发功能。如果未启用递归触发器，则触发器操作将不会触发另一个触发器。以下是简单的语法。
PRAGMA recursive_triggers; PRAGMA recursive_triggers = [true|false];
schema_version 编译指示
该schema_version编译获取或设置存储在数据库头架构版本值。以下是简单的语法。
PRAGMA [database.]schema_version; PRAGMA [database.]schema_version = number;
这是一个 32 位有符号整数值，用于跟踪架构更改。每当执行模式更改命令（如 CREATE… 或 DROP…）时，该值就会增加。
secure_delete 编译指示
该secure_delete编译用于控制如何将内容从数据库中删除。以下是简单的语法。
PRAGMA secure_delete; PRAGMA secure_delete = [true|false]; PRAGMA database.secure_delete; PRAGMA database.secure_delete = [true|false];
安全删除标志的默认值通常是关闭的，但这可以通过 SQLITE_SECURE_DELETE 构建选项进行更改。
sql_trace 编译指示
该SQL_TRACE编译用于转储SQL跟踪结果到屏幕上。以下是简单的语法。
PRAGMA sql_trace; PRAGMA sql_trace = [true|false];
SQLite 必须使用 SQLITE_DEBUG 指令编译才能包含此编译指示。
同步编译指示
该同步编译获取或设置当前磁盘同步模式，控制如何积极的SQLite将一路写数据到物理存储。以下是简单的语法。
PRAGMA [database.]synchronous; PRAGMA [database.]synchronous = mode;
SQLite 支持表中列出的以下同步模式。
| Sr.No. | Pragma 值和描述 |
|---|---|
| 1 |
0 or OFF 根本没有同步 |
| 2 |
1 or NORMAL 在每个关键磁盘操作序列之后同步 |
| 3 |
2 or FULL 每次关键磁盘操作后同步 |
temp_store 编译指示
该TEMP_STORE编译获取或设置临时数据库文件所使用的存储模式。以下是简单的语法。
PRAGMA temp_store; PRAGMA temp_store = mode;
SQLite 支持以下存储模式。
| Sr.No. | Pragma 值和描述 |
|---|---|
| 1 |
0 or DEFAULT 使用编译时默认值。通常是文件。 |
| 2 |
1 or FILE 使用基于文件的存储。 |
| 3 |
2 or MEMORY 使用基于内存的存储。 |
temp_store_directory 编译指示
该temp_store_directory编译获取或设置用于临时数据库文件的位置。以下是简单的语法。
PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory_path';
user_version 编译指示
所述user_version编译指示获取或设置存储在数据库头中的用户定义的版本值。以下是简单的语法。
PRAGMA [database.]user_version; PRAGMA [database.]user_version = number;
这是一个 32 位有符号整数值，可由开发人员设置以进行版本跟踪。
writable_schema Pragma
该writable_schema编译获取或设置修改系统表的能力。以下是简单的语法。
PRAGMA writable_schema; PRAGMA writable_schema = [true|false];
如果设置了此 pragma，则可以创建和修改以 sqlite_ 开头的表，包括 sqlite_master 表。使用 pragma 时要小心，因为它可能导致数据库完全损坏。
SQLite – 约束
约束是对表上的数据列强制执行的规则。这些用于限制可以进入表的数据类型。这保证了数据库中数据的准确性和可靠性。
约束可以是列级或表级。列级约束仅应用于一列，而表级约束应用于整个表。
以下是 SQLite 中可用的常用约束。
-
NOT NULL Constraint – 确保列不能有 NULL 值。
-
DEFAULT Constraint – 当没有指定时，为列提供默认值。
-
UNIQUE Constraint – 确保列中的所有值都不同。
-
PRIMARY Key – 唯一标识数据库表中的每一行/记录。
-
检查约束– 确保列中的所有值都满足某些条件。
非空约束
默认情况下，列可以包含 NULL 值。如果您不希望某列具有 NULL 值，则需要在此列上定义此类约束，指定该列现在不允许使用 NULL。
NULL 与没有数据不同，它代表未知数据。
例子
例如，以下 SQLite 语句创建一个名为 COMPANY 的新表并添加五列，其中三列 ID、NAME 和 AGE 指定不接受 NULL。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
默认约束
当 INSERT INTO 语句未提供特定值时，DEFAULT 约束为列提供默认值。
例子
例如，以下 SQLite 语句创建一个名为 COMPANY 的新表并添加五列。此处，SALARY 列默认设置为 5000.00，因此如果 INSERT INTO 语句未为此列提供值，则默认情况下，此列将设置为 5000.00。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
唯一约束
UNIQUE 约束防止两个记录在特定列中具有相同的值。例如，在 COMPANY 表中，您可能希望防止两个或更多人的年龄相同。
例子
例如，以下 SQLite 语句创建一个名为 COMPANY 的新表并添加五列。在这里，AGE 列设置为 UNIQUE，因此您不能有两个相同年龄的记录 –
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
主键约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。可以有更多 UNIQUE 列，但一张表中只有一个主键。在设计数据库表时，主键很重要。主键是唯一的 ID。
我们使用它们来引用表行。在表之间创建关系时，主键成为其他表中的外键。由于“长期的编码监督”，SQLite 中的主键可以为 NULL。这不是其他数据库的情况。
主键是表中的一个字段，它唯一标识数据库表中的每一行/记录。主键必须包含唯一值。主键列不能有 NULL 值。
一张表只能有一个主键，主键可以由单个或多个字段组成。当多个字段用作主键时，它们被称为复合键。
如果表在任何字段上定义了主键，则不能有两个记录具有该字段的相同值。
例子
您已经在上面看到了各种示例，其中我们创建了以 ID 作为主键的 COMPANY 表。
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
检查约束
CHECK 约束使条件能够检查输入到记录中的值。如果条件评估为假，则记录违反约束并且不会输入到表中。
例子
例如，以下 SQLite 创建了一个名为 COMPANY 的新表并添加了五列。在这里，我们添加了一个带有 SALARY 列的 CHECK，因此您不能有任何 SALARY 零。
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
删除约束
SQLite 支持有限的 ALTER TABLE 子集。SQLite 中的 ALTER TABLE 命令允许用户重命名表或向现有表添加新列。无法重命名列、删除列或在表中添加或删除约束。
SQLite – 连接
SQLite Joins子句用于合并来自数据库中两个或多个表的记录。JOIN 是一种通过使用每个表的公共值来组合来自两个表的字段的方法。
SQL 定义了三种主要的连接类型 –
- 交叉连接
- 内连接
- 外连接
在我们继续之前，让我们考虑两个表 COMPANY 和 DEPARTMENT。我们已经看到 INSERT 语句填充 COMPANY 表。因此，让我们假设 COMPANY 表中可用的记录列表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
另一个表是具有以下定义的部门 –
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
这是填充 DEPARTMENT 表的 INSERT 语句列表 –
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );
最后，我们在 DEPARTMENT 表中有以下可用记录列表 –
ID DEPT EMP_ID ---------- ---------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7
交叉连接
CROSS JOIN 将第一个表的每一行与第二个表的每一行进行匹配。如果输入表分别有 x 和 y 行，则结果表将有 x*y 行。因为 CROSS JOIN 有可能生成非常大的表，所以必须注意只在适当的时候使用它们。
以下是 CROSS JOIN 的语法 –
SELECT ... FROM table1 CROSS JOIN table2 ...
根据上表，您可以编写一个 CROSS JOIN 如下 –
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
上述查询将产生以下结果 –
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Paul Engineering 7 Paul Finance 1 Allen IT Billing 2 Allen Engineering 7 Allen Finance 1 Teddy IT Billing 2 Teddy Engineering 7 Teddy Finance 1 Mark IT Billing 2 Mark Engineering 7 Mark Finance 1 David IT Billing 2 David Engineering 7 David Finance 1 Kim IT Billing 2 Kim Engineering 7 Kim Finance 1 James IT Billing 2 James Engineering 7 James Finance
内连接
INNER JOIN 通过基于连接谓词组合两个表（表 1 和表 2）的列值来创建新的结果表。该查询将 table1 的每一行与 table2 的每一行进行比较，以找到满足连接谓词的所有行对。当满足连接谓词时，A 和 B 的每对匹配的行的列值组合成一个结果行。
INNER JOIN 是最常见和默认的连接类型。您可以选择使用 INNER 关键字。
以下是 INNER JOIN 的语法 –
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...
为了避免冗余并保持措辞更短，可以使用 USING表达式声明 INNER JOIN 条件。此表达式指定一列或多列的列表。
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...
NATURAL JOIN 类似于JOIN…USING，只是它自动测试两个表中存在的每个列的值之间的相等性 –
SELECT ... FROM table1 NATURAL JOIN table2...
根据上表，您可以编写一个 INNER JOIN 如下 –
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上述查询将产生以下结果 –
EMP_ID NAME DEPT ---------- ---------- ---------- 1 Paul IT Billing 2 Allen Engineering 7 James Finance
外连接
OUTER JOIN 是 INNER JOIN 的扩展。尽管 SQL 标准定义了三种类型的 OUTER JOIN：LEFT、RIGHT 和 FULL，但 SQLite 仅支持LEFT OUTER JOIN。
OUTER JOIN 具有与 INNER JOIN 相同的条件，使用 ON、USING 或 NATURAL 关键字表示。初始结果表的计算方法相同。一旦计算出主 JOIN，OUTER JOIN 将从一个或两个表中获取任何未连接的行，用 NULL 填充它们，并将它们附加到结果表中。
以下是 LEFT OUTER JOIN 的语法 –
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
为了避免冗余并保持措辞更短，可以使用 USING 表达式声明 OUTER JOIN 条件。此表达式指定一列或多列的列表。
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...
根据上表，您可以按如下方式编写外连接 –
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
上述查询将产生以下结果 –
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
Teddy
Mark
David
Kim
7 James Finance
SQLite – UNION 子句
SQLite UNION子句/运算符用于组合两个或多个 SELECT 语句的结果而不返回任何重复的行。
要使用 UNION，每个 SELECT 必须选择相同数量的列、相同数量的列表达式、相同数据类型，并且它们的顺序相同，但它们的长度不必相同。
句法
以下是UNION的基本语法。
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
这里给定的条件可以是基于您的要求的任何给定表达式。
例子
考虑以下两个表，(a) COMPANY表如下 –
sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
(b) 另一个表是DEPARTMENT，如下所示 –
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
现在让我们使用 SELECT 语句和 UNION 子句连接这两个表，如下所示 –
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
这将产生以下结果。
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance
UNION ALL 条款
UNION ALL 运算符用于组合包括重复行在内的两个 SELECT 语句的结果。
适用于 UNION 的相同规则也适用于 UNION ALL 运算符。
句法
以下是UNION ALL的基本语法。
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION ALL SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
这里给定的条件可以是基于您的要求的任何给定表达式。
例子
现在，让我们在 SELECT 语句中加入上述两个表，如下所示 –
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
这将产生以下结果。
EMP_ID NAME DEPT ---------- -------------------- ---------- 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance 1 Paul IT Billing 2 Allen Engineering 3 Teddy Engineering 4 Mark Finance 5 David Engineering 6 Kim Finance 7 James Finance
SQLite – NULL 值
SQLite NULL是用于表示缺失值的术语。表中的 NULL 值是字段中显示为空白的值。
具有 NULL 值的字段是没有值的字段。了解 NULL 值不同于零值或包含空格的字段非常重要。
句法
以下是在创建表时使用NULL的基本语法。
SQLite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
此处，NOT NULL表示该列应始终接受给定数据类型的显式值。有两列我们没有使用 NOT NULL，这意味着这些列可能是 NULL。
具有 NULL 值的字段是在创建记录期间留空的字段。
例子
NULL 值在选择数据时可能会导致问题，因为在将未知值与任何其他值进行比较时，结果始终未知且不包含在最终结果中。在下表中，考虑公司与以下记录-
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
让我们使用 UPDATE 语句将一些可空值设置为 NULL，如下所示 –
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
现在，COMPANY 表将具有以下记录。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 7 James 24
接下来，让我们看看使用IS NOT NULL运算符列出所有 SALARY 不为 NULL 的记录。
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;
上面的 SQLite 语句将产生以下结果 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
以下是IS NULL运算符的用法，它将列出所有 SALARY 为 NULL 的记录。
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;
上面的 SQLite 语句将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 7 James 24
SQLite – ALIAS 语法
您可以通过提供另一个名称（称为ALIAS ）来临时重命名表或列。表别名的使用意味着在特定 SQLite 语句中重命名表。重命名是临时更改，数据库中的实际表名不会更改。
列别名用于为特定 SQLite 查询重命名表的列。
句法
以下是表别名的基本语法。
SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
以下是列别名的基本语法。
SELECT column_name AS alias_name FROM table_name WHERE [condition];
例子
考虑以下两个表，(a) COMPANY表如下 –
sqlite> select * from COMPANY; ID NAME AGE ADDRESS SALARY ---------- -------------------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
(b) 另一个表是DEPARTMENT，如下所示 –
ID DEPT EMP_ID ---------- -------------------- ---------- 1 IT Billing 1 2 Engineering 2 3 Finance 7 4 Engineering 3 5 Finance 4 6 Engineering 5 7 Finance 6
现在，以下是TABLE ALIAS的用法，其中我们分别使用 C 和 D 作为 COMPANY 和 DEPARTMENT 表的别名 –
sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
上面的 SQLite 语句将产生以下结果 –
ID NAME AGE DEPT ---------- ---------- ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineering 3 Teddy 23 Engineering 4 Mark 25 Finance 5 David 27 Engineering 6 Kim 22 Finance 7 James 24 Finance
考虑使用COLUMN ALIAS的示例，其中 COMPANY_ID 是 ID 列的别名，COMPANY_NAME 是名称列的别名。
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;
上面的 SQLite 语句将产生以下结果 –
COMPANY_ID COMPANY_NAME AGE DEPT ---------- ------------ ---------- ---------- 1 Paul 32 IT Billing 2 Allen 25 Engineering 3 Teddy 23 Engineering 4 Mark 25 Finance 5 David 27 Engineering 6 Kim 22 Finance 7 James 24 Finance
SQLite – 触发器
SQLite Triggers是数据库回调函数，当指定的数据库事件发生时会自动执行/调用。以下是有关 SQLite 触发器的要点 –
-
可以指定 SQLite 触发器在特定数据库表的 DELETE、INSERT 或 UPDATE 发生时或在表的一个或多个指定列上发生时触发。
-
目前，SQLite 只支持 FOR EACH ROW 触发器，不支持 FOR EACH STATEMENT 触发器。因此，明确指定 FOR EACH ROW 是可选的。
-
WHEN 子句和触发器操作都可以使用NEW.column-name和OLD.column-name形式的引用访问被插入、删除或更新的行的元素，其中 column-name 是来自触发器关联的表。
-
如果提供了 WHEN 子句，则仅对 WHEN 子句为真的行执行指定的 SQL 语句。如果未提供 WHEN 子句，则对所有行执行 SQL 语句。
-
BEFORE 或 AFTER 关键字确定何时执行与插入、修改或删除关联行相关的触发器操作。
-
当与触发器关联的表被删除时，触发器会自动删除。
-
要修改的表必须与触发器附加到的表或视图存在于同一数据库中，并且必须仅使用tablename而不是database.tablename。
-
可以在触发器程序中使用特殊的 SQL 函数 RAISE() 来引发异常。
句法
以下是创建触发器的基本语法。
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_name BEGIN -- Trigger logic goes here.... END;
这里，event_name可以是对上述表table_name 的INSERT、DELETE和UPDATE数据库操作。您可以选择在表名后指定 FOR EACH ROW。
以下是在表的一个或多个指定列上的 UPDATE 操作上创建触发器的语法。
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name ON table_name BEGIN -- Trigger logic goes here.... END;
例子
让我们考虑一种情况，我们希望对插入 COMPANY 表中的每条记录进行审计试验，我们按如下方式新创建（如果您已经拥有，则删除 COMPANY 表）。
sqlite> CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
为了保持审计试验，我们将创建一个名为 AUDIT 的新表，只要在 COMPANY 表中有新记录的条目，就会插入日志消息。
sqlite> CREATE TABLE AUDIT( EMP_ID INT NOT NULL, ENTRY_DATE TEXT NOT NULL );
这里，ID 是 AUDIT 记录 ID，EMP_ID 是来自 COMPANY 表的 ID，DATE 将在 COMPANY 表中创建记录时保留时间戳。现在让我们在 COMPANY 表上创建一个触发器，如下所示 –
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;
现在，我们将开始实际工作，让我们开始在 COMPANY 表中插入记录，这将导致在 AUDIT 表中创建审计日志记录。在 COMPANY 表中创建一个记录如下 –
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
这将在 COMPANY 表中创建一条记录，如下所示 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
同时，AUDIT 表中将创建一条记录。该记录是触发器的结果，我们在 COMPANY 表中的 INSERT 操作中创建了该触发器。同样，您可以根据您的要求在 UPDATE 和 DELETE 操作上创建触发器。
EMP_ID ENTRY_DATE ---------- ------------------- 1 2013-04-05 06:26:00
列出触发器
您可以列出sqlite_master表中的所有触发器，如下所示 –
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
上面的 SQLite 语句将只列出一个条目如下 –
name ---------- audit_log
如果要列出特定表上的触发器，请使用带有表名的 AND 子句，如下所示 –
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';
上面的 SQLite 语句也将只列出一个条目如下 –
name ---------- audit_log
删除触发器
以下是 DROP 命令，可用于删除现有触发器。
sqlite> DROP TRIGGER trigger_name;
SQLite – 索引
索引是数据库搜索引擎可以用来加速数据检索的特殊查找表。简单地说，索引是指向表中数据的指针。数据库中的索引与书后的索引非常相似。
例如，如果您想参考一本书中讨论某个主题的所有页面，您首先参考索引，该索引按字母顺序列出所有主题，然后参考一个或多个特定页码。
索引有助于加快 SELECT 查询和 WHERE 子句的速度，但它会减慢数据输入，使用 UPDATE 和 INSERT 语句。可以在不影响数据的情况下创建或删除索引。
创建索引涉及 CREATE INDEX 语句，该语句允许您命名索引、指定表和要索引的列，以及指示索引是按升序还是降序排列。
索引也可以是唯一的，类似于 UNIQUE 约束，因为索引可以防止存在索引的列或列组合中的重复条目。
CREATE INDEX 命令
以下是CREATE INDEX的基本语法。
CREATE INDEX index_name ON table_name;
单列索引
单列索引是仅基于一个表列创建的索引。基本语法如下 –
CREATE INDEX index_name ON table_name (column_name);
唯一索引
唯一索引不仅用于性能，还用于数据完整性。唯一索引不允许将任何重复值插入到表中。基本语法如下 –
CREATE UNIQUE INDEX index_name on table_name (column_name);
综合指数
复合索引是在表的两列或更多列上建立的索引。基本语法如下 –
CREATE INDEX index_name on table_name (column1, column2);
无论是创建单列索引还是复合索引，都要考虑在查询的 WHERE 子句中可能经常使用的列作为过滤条件。
如果只使用一列，则应该选择单列索引。如果 WHERE 子句中经常使用两列或更多列作为过滤器，则复合索引将是最佳选择。
隐式索引
隐式索引是在创建对象时由数据库服务器自动创建的索引。索引是为主键约束和唯一约束自动创建的。
例子
以下是我们将在COMPANY表中为工资列创建索引的示例–
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
现在，让我们使用.indices命令列出 COMPANY 表中可用的所有索引，如下所示 –
sqlite> .indices COMPANY
这将产生以下结果，其中sqlite_autoindex_COMPANY_1是在创建表本身时创建的隐式索引。
salary_index sqlite_autoindex_COMPANY_1
您可以列出数据库范围内的所有索引，如下所示 –
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
DROP INDEX 命令
可以使用 SQLite DROP命令删除索引。删除索引时应该小心，因为性能可能会降低或提高。
以下是基本语法如下 –
DROP INDEX index_name;
您可以使用以下语句删除以前创建的索引。
sqlite> DROP INDEX salary_index;
什么时候应该避免使用索引？
尽管索引旨在提高数据库的性能，但有时也应避免使用索引。以下指南指示何时应重新考虑使用索引。
索引不应用于 –
- 小桌子。
- 具有频繁、大批量更新或插入操作的表。
- 包含大量 NULL 值的列。
- 经常操作的列。
SQLite – 按子句索引
“INDEXED BY index-name”子句指定必须使用命名索引才能在前面的表中查找值。
如果 index-name 不存在或不能用于查询，则 SQLite 语句的准备失败。
“NOT INDEXED”子句指定在访问前表时不应使用任何索引，包括由 UNIQUE 和 PRIMARY KEY 约束创建的隐含索引。
但是，即使指定了“NOT INDEXED”，INTEGER PRIMARY KEY 仍可用于查找条目。
句法
以下是 INDEXED BY 子句的语法，它可以与 DELETE、UPDATE 或 SELECT 语句一起使用。
SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);
例子
考虑表COMPANY我们将创建一个索引并使用它来执行 INDEXED BY 操作。
sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite>
现在从表 COMPANY 中选择数据，您可以使用 INDEXED BY 子句如下 –
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite – ALTER TABLE 命令
SQLite ALTER TABLE命令在不执行完整转储和重新加载数据的情况下修改现有表。您可以使用 ALTER TABLE 语句重命名表，并且可以使用 ALTER TABLE 语句在现有表中添加其他列。
除了重命名表和在现有表中添加列之外，SQLite 中的 ALTER TABLE 命令不支持其他操作。
句法
以下是ALTER TABLE重命名现有表的基本语法。
ALTER TABLE database_name.table_name RENAME TO new_table_name;
以下是ALTER TABLE在现有表中添加新列的基本语法。
ALTER TABLE database_name.table_name ADD COLUMN column_def...;
例子
考虑具有以下记录的COMPANY表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们尝试使用 ALTER TABLE 语句重命名该表，如下所示 –
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;
上面的 SQLite 语句将 COMPANY 表重命名为 OLD_COMPANY。现在，让我们尝试在 OLD_COMPANY 表中添加一个新列，如下所示 –
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);
COMPANY 表现在已更改，以下将是 SELECT 语句的输出。
ID NAME AGE ADDRESS SALARY SEX ---------- ---------- ---------- ---------- ---------- --- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
需要注意的是，新添加的列填充了 NULL 值。
SQLite – TRUNCATE TABLE 命令
不幸的是，我们在 SQLite 中没有 TRUNCATE TABLE 命令，但您可以使用 SQLite DELETE命令从现有表中删除完整数据，但建议使用 DROP TABLE 命令删除完整表并重新创建它。
句法
以下是 DELETE 命令的基本语法。
sqlite> DELETE FROM table_name;
以下是 DROP TABLE 的基本语法。
sqlite> DROP TABLE table_name;
如果您使用 DELETE TABLE 命令删除所有记录，建议使用VACUUM命令清除未使用的空间。
例子
考虑具有以下记录的COMPANY表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是截断上表的示例 –
SQLite> DELETE FROM COMPANY; SQLite> VACUUM;
现在，COMPANY 表被完全截断，SELECT 语句的输出将不复存在。
SQLite – 视图
视图只不过是一条 SQLite 语句，它以关联的名称存储在数据库中。它实际上是以预定义的 SQLite 查询的形式组合一个表。
视图可以包含表的所有行或从一个或多个表中选择的行。可以从一个或多个表创建视图，这取决于编写的 SQLite 查询来创建视图。
视图是一种虚拟表，允许用户 –
-
以用户或用户类别认为自然或直观的方式构建数据。
-
限制对数据的访问，使用户只能看到有限的数据而不是完整的表格。
-
汇总各种表格中的数据，可用于生成报告。
SQLite 视图是只读的，因此您可能无法对视图执行 DELETE、INSERT 或 UPDATE 语句。但是，您可以在试图删除、插入或更新视图时触发的视图上创建触发器，并在触发器主体中执行您需要的操作。
创建视图
SQLite 视图是使用CREATE VIEW语句创建的。SQLite 视图可以从单个表、多个表或其他视图创建。
以下是基本的 CREATE VIEW 语法。
CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];
您可以按照在普通 SQL SELECT 查询中使用它们的类似方式在 SELECT 语句中包含多个表。如果存在可选的 TEMP 或 TEMPORARY 关键字，则将在临时数据库中创建视图。
例子
考虑具有以下记录的COMPANY表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
以下是从 COMPANY 表创建视图的示例。此视图将用于仅包含 COMPANY 表中的几列。
sqlite> CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
您现在可以以与查询实际表类似的方式查询 COMPANY_VIEW。以下是一个例子 –
sqlite> SELECT * FROM COMPANY_VIEW;
这将产生以下结果。
ID NAME AGE ---------- ---------- ---------- 1 Paul 32 2 Allen 25 3 Teddy 23 4 Mark 25 5 David 27 6 Kim 22 7 James 24
删除视图
要删除视图，只需使用带有view_name的 DROP VIEW 语句。基本的 DROP VIEW 语法如下 –
sqlite> DROP VIEW view_name;
以下命令将删除我们在上一节中创建的 COMPANY_VIEW 视图。
sqlite> DROP VIEW COMPANY_VIEW;
SQLite – 事务
事务是针对数据库执行的工作单元。事务是按逻辑顺序完成的工作单元或序列，无论是由用户手动完成还是由某种数据库程序自动完成。
事务是对数据库的一个或多个更改的传播。例如，如果您要从表中创建、更新或删除记录，那么您正在对表执行事务。控制事务以确保数据完整性和处理数据库错误非常重要。
实际上，您会将许多 SQLite 查询组合到一个组中，并将作为事务的一部分一起执行所有这些查询。
交易属性
事务具有以下四个标准属性，通常由首字母缩写词 ACID 表示。
-
原子性– 确保工作单元内的所有操作都成功完成；否则，事务将在故障点中止，并且之前的操作将回滚到它们之前的状态。
-
一致性– 确保数据库在成功提交的事务后正确更改状态。
-
隔离– 使交易能够独立运行且彼此透明。
-
持久性– 确保在系统故障的情况下提交的事务的结果或影响仍然存在。
交易控制
以下是用于控制事务的以下命令：
-
BEGIN TRANSACTION – 开始交易。
-
COMMIT – 要保存更改，您也可以使用END TRANSACTION命令。
-
ROLLBACK – 回滚更改。
事务控制命令仅与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。在创建表或删除表时不能使用它们，因为这些操作是在数据库中自动提交的。
开始事务命令
事务可以使用 BEGIN TRANSACTION 或简单的 BEGIN 命令启动。此类事务通常持续到遇到下一个 COMMIT 或 ROLLBACK 命令。但是，如果数据库关闭或发生错误，事务也会回滚。以下是启动事务的简单语法。
BEGIN; or BEGIN TRANSACTION;
提交命令
COMMIT 命令是用于将事务调用的更改保存到数据库的事务命令。
COMMIT 命令将自上次 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库中。
以下是 COMMIT 命令的语法。
COMMIT; or END TRANSACTION;
回滚命令
ROLLBACK 命令是用于撤消尚未保存到数据库的事务的事务性命令。
ROLLBACK 命令只能用于撤消自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。
以下是 ROLLBACK 命令的语法。
ROLLBACK;
例子
考虑具有以下记录的COMPANY表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们开始一个事务并从表中删除年龄 = 25 的记录。然后，使用 ROLLBACK 命令撤消所有更改。
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> ROLLBACK;
现在，如果您检查 COMPANY 表，它仍然有以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
让我们开始另一个事务并从表中删除年龄 = 25 的记录，最后我们使用 COMMIT 命令提交所有更改。
sqlite> BEGIN; sqlite> DELETE FROM COMPANY WHERE AGE = 25; sqlite> COMMIT;
如果您现在检查 COMPANY 表仍然有以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite – 子查询
子查询或内部查询或嵌套查询是另一个 SQLite 查询中的查询，并嵌入在 WHERE 子句中。
子查询用于返回将在主查询中用作条件的数据，以进一步限制要检索的数据。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句以及 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。
子查询必须遵循一些规则 –
-
子查询必须括在括号内。
-
一个子查询在 SELECT 子句中只能有一个列，除非子查询的主查询中有多个列来比较其选定的列。
-
尽管主查询可以使用 ORDER BY，但不能在子查询中使用 ORDER BY。GROUP BY 可用于执行与子查询中的 ORDER BY 相同的功能。
-
返回多行的子查询只能与多个值运算符一起使用，例如 IN 运算符。
-
BETWEEN 运算符不能与子查询一起使用；但是， BETWEEN 可以在子查询中使用。
带有 SELECT 语句的子查询
子查询最常与 SELECT 语句一起使用。基本语法如下 –
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
例子
考虑具有以下记录的COMPANY表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们用 SELECT 语句检查以下子查询。
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;
这将产生以下结果。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
带有 INSERT 语句的子查询
子查询也可以与 INSERT 语句一起使用。INSERT 语句使用从子查询返回的数据插入到另一个表中。子查询中选定的数据可以使用任何字符、日期或数字函数进行修改。
以下是基本语法如下 –
INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
例子
考虑一个表 COMPANY_BKP，其结构与 COMPANY 表相似，并且可以使用相同的 CREATE TABLE 来创建，并使用 COMPANY_BKP 作为表名。要将完整的 COMPANY 表复制到 COMPANY_BKP，以下是语法 –
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;
带有 UPDATE 语句的子查询
子查询可以与 UPDATE 语句结合使用。使用带有 UPDATE 语句的子查询时，可以更新表中的单列或多列。
以下是基本语法如下 –
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
例子
假设我们有 COMPANY_BKP 表可用，它是 COMPANY 表的备份。
以下示例将 COMPANY 表中所有 AGE 大于或等于 27 的客户的 SALARY 更新 0.50 倍。
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );
这将影响两行，最后 COMPANY 表将具有以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 10000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
带有 DELETE 语句的子查询
子查询可以与 DELETE 语句结合使用，就像上面提到的任何其他语句一样。
以下是基本语法如下 –
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
例子
假设我们有 COMPANY_BKP 表可用，它是 COMPANY 表的备份。
以下示例从 COMPANY 表中删除所有 AGE 大于或等于 27 的客户的记录。
sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
这将影响两行，最后 COMPANY 表将有以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 42500.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite – 自动增量
SQLite AUTOINCREMENT是用于自动递增表中字段值的关键字。我们可以在创建具有特定列名的表时使用AUTOINCREMENT关键字自动增加字段值以自动增加。
关键字AUTOINCREMENT只能与 INTEGER 字段一起使用。
句法
AUTOINCREMENT关键字的基本用法如下 –
CREATE TABLE table_name( column1 INTEGER AUTOINCREMENT, column2 datatype, column3 datatype, ..... columnN datatype, );
例子
考虑按如下方式创建 COMPANY 表 –
sqlite> CREATE TABLE COMPANY( ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
现在，将以下记录插入表 COMPANY –
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );
这将在表 COMPANY 中插入 7 个元组，COMPANY 将具有以下记录 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite – 注入
如果您通过网页获取用户输入并将其插入到 SQLite 数据库中，那么您就有可能对称为 SQL 注入的安全问题敞开心扉。在本章中，您将学习如何帮助防止这种情况发生并帮助您保护脚本和 SQLite 语句。
注入通常发生在您向用户询问输入（例如他们的姓名）时，他们给您一个 SQLite 语句而不是名称，而您将在不知不觉中在数据库上运行该语句。
永远不要相信用户提供的数据，只有在验证后才处理这些数据；通常，这是通过模式匹配完成的。在下面的示例中，用户名被限制为字母数字字符加下划线和 8 到 20 个字符之间的长度 – 根据需要修改这些规则。
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){
$db = new SQLiteDatabase('filename');
$result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
echo "username not accepted";
}
为了演示这个问题，请考虑以下摘录 –
$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");
函数调用应该从用户表中检索一条记录，其中名称列与用户指定的名称相匹配。在正常情况下，$name将只包含字母数字字符和空格，例如字符串 ilia。然而，在这种情况下，通过向 $name 附加一个全新的查询，对数据库的调用变成了一场灾难：注入的 DELETE 查询会删除用户的所有记录。
有些数据库接口不允许查询堆叠或在单个函数调用中执行多个查询。如果您尝试堆叠查询，调用会失败，但 SQLite 和 PostgreSQL 会愉快地执行堆叠查询，执行一个字符串中提供的所有查询并造成严重的安全问题。
防止 SQL 注入
您可以在 PERL 和 PHP 等脚本语言中巧妙地处理所有转义字符。编程语言 PHP 提供了函数string sqlite_escape_string()来转义 SQLite 特有的输入字符。
if (get_magic_quotes_gpc()) {
$name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");
尽管编码使插入数据变得安全，但它会使查询中的简单文本比较和LIKE子句对包含二进制数据的列不可用。
注意–不应该使用addlashes()来引用 SQLite 查询的字符串；检索数据时会导致奇怪的结果。
SQLite – 解释
SQLite 语句前面可以带有关键字“EXPLAIN”或用于描述表详细信息的短语“EXPLAIN QUERY PLAN”。
任何一种修改都会导致 SQLite 语句表现为查询，并返回有关 SQLite 语句在省略 EXPLAIN 关键字或短语的情况下将如何操作的信息。
-
EXPLAIN 和 EXPLAIN QUERY PLAN 的输出仅用于交互式分析和故障排除。
-
从 SQLite 的一个版本到下一个版本，输出格式的详细信息可能会发生变化。
-
应用程序不应使用 EXPLAIN 或 EXPLAIN QUERY PLAN，因为它们的确切行为是可变的并且仅部分记录。
句法
EXPLAIN 的语法如下 –
EXPLAIN [SQLite Query]
EXPLAIN QUERY PLAN 的语法如下 –
EXPLAIN QUERY PLAN [SQLite Query]
例子
考虑具有以下记录的COMPANY表 –
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
现在，让我们使用 SELECT 语句检查以下子查询 –
sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;
这将产生以下结果。
addr opcode p1 p2 p3 ---------- ---------- ---------- ---------- ---------- 0 Goto 0 19 1 Integer 0 0 2 OpenRead 0 8 3 SetNumColu 0 5 4 Rewind 0 17 5 Column 0 4 6 RealAffini 0 0 7 Integer 20000 0 8 Lt 357 16 collseq(BI 9 Rowid 0 0 10 Column 0 1 11 Column 0 2 12 Column 0 3 13 Column 0 4 14 RealAffini 0 0 15 Callback 5 0 16 Next 0 5 17 Close 0 0 18 Halt 0 0 19 Transactio 0 0 20 VerifyCook 0 38 21 Goto 0 1 22 Noop 0 0
现在，让我们用 SELECT 语句检查以下解释查询计划–
SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000; order from detail ---------- ---------- ------------- 0 0 TABLE COMPANY
SQLite – 真空
VACUUM 命令通过将主数据库的内容复制到临时数据库文件并从副本重新加载原始数据库文件来清理主数据库。这消除了空闲页，将表数据对齐为连续的，并以其他方式清理数据库文件结构。
VACUUM 命令可能会更改没有显式 INTEGER PRIMARY KEY 的表中条目的 ROWID。VACUUM 命令仅适用于主数据库。无法对附加的数据库文件进行 VACUUM。
如果存在活动事务，VACUUM 命令将失败。VACUUM 命令是内存数据库的空操作。由于 VACUUM 命令从头开始重建数据库文件，因此 VACUUM 还可用于修改许多特定于数据库的配置参数。
手动真空
以下是从命令提示符为整个数据库发出 VACUUM 命令的简单语法 –
$sqlite3 database_name "VACUUM;"
您可以从 SQLite 提示符运行 VACUUM 以及如下 –
sqlite> VACUUM;
您还可以在特定表上运行 VACUUM，如下所示 –
sqlite> VACUUM table_name;
自动真空吸尘器
SQLite Auto-VACUUM 与 VACUUM 的作用不同，它只是将空闲页移动到数据库的末尾，从而减小了数据库大小。通过这样做，它可以显着地将数据库碎片化，而 VACUUM 确保碎片整理。因此，Auto-VACUUM 只会使数据库变小。
您可以通过在 SQLite 提示符下运行的以下编译指示来启用/禁用 SQLite 自动清空 –
sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum
您可以从命令提示符运行以下命令来检查自动真空设置 –
$sqlite3 database_name "PRAGMA auto_vacuum;"
SQLite – 日期和时间
SQLite 支持五个日期和时间函数，如下所示 –
| Sr.No. | 功能 | 例子 |
|---|---|---|
| 1 | 日期（时间字符串，修饰符…） | 这将返回以下格式的日期：YYYY-MM-DD |
| 2 | 时间（时间字符串，修饰符…） | 这将时间返回为 HH:MM:SS |
| 3 | 日期时间（时间字符串，修饰符…） | 这将返回 YYYY-MM-DD HH:MM:SS |
| 4 | 朱利安日（时间字符串，修饰符…） | 这将返回自公元前 4714 年 11 月 24 日中午在格林威治的天数 |
| 5 | strftime（时间字符串，修饰符…） | 这将返回根据格式字符串格式化的日期，该格式字符串指定为第一个参数，按照下面解释的格式化程序进行格式化。 |
以上五个日期和时间函数都以时间字符串作为参数。时间字符串后跟零个或多个修饰符。strftime() 函数也将格式字符串作为它的第一个参数。以下部分将详细介绍不同类型的时间字符串和修饰符。
时间字符串
时间字符串可以是以下任何一种格式 –
| Sr.No. | 时间字符串 | 例子 |
|---|---|---|
| 1 | YYYY-MM-DD | 2010-12-30 |
| 2 | YYYY-MM-DD HH:MM | 2010-12-30 12:10 |
| 3 | YYYY-MM-DD HH:MM:SS.SSS | 2010-12-30 12:10:04.100 |
| 4 | MM-DD-YYYY HH:MM | 30-12-2010 12:10 |
| 5 | 时:分 | 12:10 |
| 6 | YYYY-MM-DD T HH:MM | 2010-12-30 12:10 |
| 7 | 时:分:秒 | 12:10:01 |
| 8 | YYYYMMDD HHMMSS | 20101230 121001 |
| 9 | 现在 | 2013-05-07 |
您可以使用“T”作为分隔日期和时间的文字字符。
修饰符
时间字符串后面可以跟零个或多个修饰符，这些修饰符将改变由上述五个函数中的任何一个返回的日期和/或时间。修饰符从左到右应用。
以下修饰符在 SQLite 中可用 –
- NNN天
- NNN 小时
- NNN分钟
- NNN.NNNN 秒
- NNN月
- NNN年
- 月初
- 年初
- 一天的开始
- 工作日
- unixepoch
- 当地时间
- 世界标准时间
格式化程序
SQLite 提供了一个非常方便的函数strftime()来格式化任何日期和时间。您可以使用以下替换来格式化您的日期和时间。
| Substitution | 描述 | ||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| %d | 每月的第几天，01-31 | ％F | 小数秒，SS.SSS | ％H | 小时，00-23 | %j | 一年中的哪一天，001-366 | %J | 儒略日数，DDDD.DDDD | %m | 月，00-12 | %M | 分钟，00-59 | %s | 自 1970-01-01 以来的秒数 | %S | 秒，00-59 | %w | 星期几，0-6（0 是星期日） | %W | 一年中的一周，01-53 | %Y | 年，YYYY | %% | ％ 象征 |
例子
现在让我们使用 SQLite 提示尝试各种示例。以下命令计算当前日期。
sqlite> SELECT date('now');
2013-05-07
以下命令计算当月的最后一天。
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31
以下命令计算给定 UNIX 时间戳 1092941466 的日期和时间。
sqlite> SELECT datetime(1092941466, 'unixepoch'); 2004-08-19 18:51:06
以下命令计算给定 UNIX 时间戳 1092941466 的日期和时间，并补偿您的本地时区。
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime'); 2004-08-19 13:51:06
以下命令计算当前的 UNIX 时间戳。
sqlite> SELECT strftime('%s','now');
1393348134
以下命令计算自签署美国独立宣言以来的天数。
sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023
以下命令计算自 2004 年特定时刻以来的秒数。
sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572
以下命令计算当年 10 月的第一个星期二的日期。
sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01
以下命令以秒为单位计算自 UNIX 纪元以来的时间（如 strftime(‘%s’,’now’) ，除了包括小数部分）。
sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598
要在格式化日期时在 UTC 和本地时间值之间进行转换，请使用 utc 或 localtime 修饰符如下 –
sqlite> SELECT time('12:00', 'localtime');
05:00:00
sqlite> SELECT time('12:00', 'utc');
19:00:00
SQLite – 有用的函数
SQLite 有许多内置函数来处理字符串或数字数据。以下是一些有用的 SQLite 内置函数的列表，它们都不区分大小写，这意味着您可以以小写形式或大写形式或混合形式使用这些函数。更多详细信息，您可以查看 SQLite 的官方文档。
| Sr.No. | 功能说明 |
|---|---|
| 1 |
SQLite COUNT Function SQLite COUNT 聚合函数用于计算数据库表中的行数。 |
| 2 |
SQLite MAX Function SQLite MAX 聚合函数允许我们为某个列选择最高（最大值）值。 |
| 3 |
SQLite MIN Function SQLite MIN 聚合函数允许我们为某个列选择最低（最小值）值。 |
| 4 |
SQLite AVG Function SQLite AVG 聚合函数选择某些表列的平均值。 |
| 5 |
SQLite SUM Function SQLite SUM 聚合函数允许选择数字列的总数。 |
| 6 |
SQLite RANDOM Function SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。 |
| 7 |
SQLite ABS Function SQLite ABS 函数返回数字参数的绝对值。 |
| 8 |
SQLite UPPER Function SQLite UPPER 函数将字符串转换为大写字母。 |
| 9 |
SQLite LOWER Function SQLite LOWER 函数将字符串转换为小写字母。 |
| 10 |
SQLite LENGTH Function SQLite LENGTH 函数返回字符串的长度。 |
| 11 |
SQLite sqlite_version Function SQLite sqlite_version 函数返回 SQLite 库的版本。 |
在我们开始举例说明上述函数之前，请考虑具有以下记录的 COMPANY 表。
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
SQLite 计数函数
SQLite COUNT 聚合函数用于计算数据库表中的行数。以下是一个例子 –
sqlite> SELECT count(*) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
count(*) ---------- 7
SQLite MAX 函数
SQLite MAX 聚合函数允许我们为某个列选择最高（最大值）值。以下是一个例子 –
sqlite> SELECT max(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
max(salary) ----------- 85000.0
SQLite MIN 函数
SQLite MIN 聚合函数允许我们为某个列选择最低（最小值）值。以下是一个例子 –
sqlite> SELECT min(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
min(salary) ----------- 10000.0
SQLite 平均函数
SQLite AVG 聚合函数选择某个表列的平均值。以下是一个例子 –
sqlite> SELECT avg(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
avg(salary) ---------------- 37142.8571428572
SQLite SUM 函数
SQLite SUM 聚合函数允许选择数字列的总数。以下是一个例子 –
sqlite> SELECT sum(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
sum(salary) ----------- 260000.0
SQLite 随机函数
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。以下是一个例子 –
sqlite> SELECT random() AS Random;
上面的 SQLite SQL 语句将产生以下内容。
Random ------------------- 5876796417670984050
SQLite ABS 函数
SQLite ABS 函数返回数字参数的绝对值。以下是一个例子 –
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
上面的 SQLite SQL 语句将产生以下内容。
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0
SQLite UPPER 函数
SQLite UPPER 函数将字符串转换为大写字母。以下是一个例子 –
sqlite> SELECT upper(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
upper(name) ----------- PAUL ALLEN TEDDY MARK DAVID KIM JAMES
SQLite LOWER 函数
SQLite LOWER 函数将字符串转换为小写字母。以下是一个例子 –
sqlite> SELECT lower(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
lower(name) ----------- paul allen teddy mark david kim james
SQLite LENGTH 函数
SQLite LENGTH 函数返回字符串的长度。以下是一个例子 –
sqlite> SELECT name, length(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下内容。
NAME length(name) ---------- ------------ Paul 4 Allen 5 Teddy 5 Mark 4 David 5 Kim 3 James 5
SQLite sqlite_version 函数
SQLite sqlite_version 函数返回 SQLite 库的版本。以下是一个例子 –
sqlite> SELECT sqlite_version() AS 'SQLite Version';
上面的 SQLite SQL 语句将产生以下内容。
SQLite Version -------------- 3.6.20
SQLite – C/C++
在本章中，您将学习如何在 C/C++ 程序中使用 SQLite。
安装
在我们的 C/C++ 程序中开始使用 SQLite 之前，您需要确保在机器上设置了 SQLite 库。您可以查看 SQLite 安装章节以了解安装过程。
C/C++ 接口 API
以下是重要的 C/C++ SQLite 接口例程，它们可以满足您从 C/C++ 程序使用 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 SQLite 官方文档。
| Sr.No. | API & 描述 |
|---|---|
| 1 |
sqlite3_open(const char *filename, sqlite3 **ppDb) 此例程打开与 SQLite 数据库文件的连接，并返回供其他 SQLite 例程使用的数据库连接对象。 如果文件名参数为 NULL 或 ‘:memory:’，sqlite3_open() 将在 RAM 中创建一个仅在会话期间持续的内存数据库。 如果文件名不为 NULL，sqlite3_open() 将尝试使用其值打开数据库文件。如果不存在该名称的文件，sqlite3_open() 将打开该名称的新数据库文件。 |
| 2 |
sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg) 该例程提供了一种快速、简单的方法来执行由 sql 参数提供的 SQL 命令，该参数可以包含多个 SQL 命令。 这里，第一个参数sqlite3是一个开放的数据库对象，sqlite_callback是一个回调，其中data是第一个参数，并且 errmsg 将被返回以捕获例程引发的任何错误。 SQLite3_exec() 例程解析并执行sql参数中给出的每个命令，直到它到达字符串的末尾或遇到错误。 |
| 3 |
sqlite3_close(sqlite3*) 此例程关闭先前通过调用 sqlite3_open() 打开的数据库连接。所有与连接相关的准备好的语句都应该在关闭连接之前完成。 如果仍有任何未完成的查询，sqlite3_close() 将返回 SQLITE_BUSY 并带有错误消息 Unable to close due to unfinalized statements。 |
连接到数据库
以下 C 代码段显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
#include <stdio.h>
#include <sqlite3.h>
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
sqlite3_close(db);
}
现在，让我们编译并运行上面的程序，在当前目录中创建我们的数据库test.db。您可以根据需要更改路径。
$gcc test.c -l sqlite3 $./a.out Opened database successfully
如果您打算使用 C++ 源代码，那么您可以按如下方式编译您的代码 –
$g++ test.c -l sqlite3
在这里，我们将我们的程序与 sqlite3 库链接起来，为 C 程序提供所需的功能。这将在您的目录中创建一个数据库文件 test.db，您将得到以下结果。
-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out -rw-r--r--. 1 root root 323 May 8 02:05 test.c -rw-r--r--. 1 root root 0 May 8 02:06 test.db
创建表
以下 C 代码段将用于在先前创建的数据库中创建表 –
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
int i;
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stdout, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Table created successfully\n");
}
sqlite3_close(db);
return 0;
}
当上面的程序被编译和执行时，它会在你的 test.db 中创建 COMPANY 表，文件的最终列表如下 –
-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out -rw-r--r--. 1 root root 1207 May 8 02:31 test.c -rw-r--r--. 1 root root 3072 May 8 02:31 test.db
插入操作
以下 C 代码段显示了如何在上述示例中创建的 COMPANY 表中创建记录 –
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
int i;
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Records created successfully\n");
}
sqlite3_close(db);
return 0;
}
当上面的程序被编译和执行时，它会在 COMPANY 表中创建给定的记录并显示以下两行 –
Opened database successfully Records created successfully
选择操作
在继续进行获取记录的实际示例之前，让我们看一下我们在示例中使用的回调函数的一些细节。此回调提供了一种从 SELECT 语句获取结果的方法。它有以下声明 –
typedef int (*sqlite3_callback)( void*, /* Data provided in the 4th argument of sqlite3_exec() */ int, /* The number of columns in row */ char**, /* An array of strings representing fields in the row */ char** /* An array of strings representing column names */ );
如果在 sqlite_exec() 例程中提供了上述回调作为第三个参数，SQLite 将为在 SQL 参数中执行的每个 SELECT 语句中处理的每个记录调用此回调函数。
以下 C 代码段显示了如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "SELECT * from COMPANY";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
上述程序编译执行后，会产生如下结果。
Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 Callback function called: ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
更新操作
以下 C 代码段显示了我们如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取和显示更新的记录。
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create merged SQL statement */
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
"SELECT * from COMPANY";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
上述程序编译执行后，会产生如下结果。
Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 Callback function called: ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
删除操作
以下 C 代码段显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录。
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName) {
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create merged SQL statement */
sql = "DELETE from COMPANY where ID=2; " \
"SELECT * from COMPANY";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}
上述程序编译执行后，会产生如下结果。
Opened database successfully Callback function called: ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 Callback function called: ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 Callback function called: ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
SQLite – Java
在本章中，您将学习如何在 Java 程序中使用 SQLite。
安装
在我们的 Java 程序中开始使用 SQLite 之前，您需要确保在机器上设置了 SQLite JDBC 驱动程序和 Java。您可以查看 Java 教程以在您的机器上安装 Java。现在，让我们检查如何设置 SQLite JDBC 驱动程序。
-
从sqlite-jdbc存储库下载最新版本的sqlite-jdbc-(VERSION).jar。
-
将下载的 jar 文件sqlite-jdbc-(VERSION).jar 添加到您的类路径中，或者您可以将它与 -classpath 选项一起使用，如以下示例中所述。
以下部分假设您对 Java JDBC 概念知之甚少。如果您不知道，那么建议您花半个小时学习JDBC 教程，以熟悉下面解释的概念。
连接到数据库
以下 Java 程序显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}
}
现在，让我们编译并运行上面的程序，在当前目录中创建我们的数据库test.db。您可以根据需要更改路径。我们假设当前版本的 JDBC 驱动程序sqlite-jdbc-3.7.2.jar在当前路径中可用。
$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC Open database successfully
如果你打算使用 Windows 机器，那么你可以编译和运行你的代码，如下所示 –
$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC Opened database successfully
创建表
下面的 Java 程序将用于在之前创建的数据库中创建一个表。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}
}
当上面的程序被编译和执行时，它会在你的test.db 中创建 COMPANY 表，文件的最终列表如下 –
-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar -rw-r--r--. 1 root root 1506 May 8 05:43 SQLiteJDBC.class -rw-r--r--. 1 root root 832 May 8 05:42 SQLiteJDBC.java -rw-r--r--. 1 root root 3072 May 8 05:43 test.db
插入操作
下面的 Java 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
}
当上述程序被编译和执行时，它会在 COMPANY 表中创建给定的记录，并将显示以下两行 –
Opened database successfully Records created successfully
选择操作
以下 Java 程序显示了如何从上面示例中创建的 COMPANY 表中获取和显示记录。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
上述程序编译执行后，会产生如下结果。
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
更新操作
以下 Java 代码显示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取并显示更新的记录。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
上述程序编译执行后，会产生如下结果。
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
删除操作
下面的 Java 代码展示了如何使用 DELETE 语句删除任何记录，然后从我们的 COMPANY 表中获取并显示剩余的记录。
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID=2;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}
上述程序编译执行后，会产生如下结果。
Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 25000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
SQLite – PHP
在本章中，您将学习如何在 PHP 程序中使用 SQLite。
安装
自 PHP 5.3.0 起默认启用 SQLite3 扩展。可以在编译时使用–without-sqlite3禁用它。
Windows 用户必须启用 php_sqlite3.dll 才能使用此扩展。自 PHP 5.3.0 起，此 DLL 包含在 PHP 的 Windows 发行版中。
有关详细的安装说明，请查看我们的 PHP 教程及其官方网站。
PHP 接口 API
以下是重要的 PHP 例程，它们可以满足您从 PHP 程序使用 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 PHP 官方文档。
| Sr.No. | API & 描述 |
|---|---|
| 1 |
public void SQLite3::open ( filename, flags, encryption_key ) 打开 SQLite 3 数据库。如果构建包含加密，那么它将尝试使用密钥。 如果文件名指定为‘:memory:’，SQLite3::open() 将在 RAM 中创建一个仅在会话期间持续的内存数据库。 如果文件名是实际设备文件名，则 SQLite3::open() 尝试使用其值打开数据库文件。如果不存在具有该名称的文件，则会创建具有该名称的新数据库文件。 用于确定如何打开 SQLite 数据库的可选标志。默认情况下，open 使用 SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE。 |
| 2 |
public bool SQLite3::exec ( string $query ) 该例程提供了一种快速、简单的方法来执行由 sql 参数提供的 SQL 命令，该参数可以包含多个 SQL 命令。此例程用于对给定数据库执行无结果查询。 |
| 3 |
public SQLite3Result SQLite3::query ( string $query ) 此例程执行 SQL 查询，如果查询返回结果，则返回SQLite3Result对象。 |
| 4 |
public int SQLite3::lastErrorCode ( void ) 此例程返回最近失败的 SQLite 请求的数字结果代码。 |
| 5 |
public string SQLite3::lastErrorMsg ( void ) 此例程返回描述最近失败的 SQLite 请求的英文文本。 |
| 6 |
public int SQLite3::changes ( void ) 此例程返回由最近的 SQL 语句更新、插入或删除的数据库行数。 |
| 7 |
public bool SQLite3::close ( void ) 此例程关闭先前通过调用 SQLite3::open() 打开的数据库连接。 |
| 8 |
public string SQLite3::escapeString ( string $value ) 此例程返回一个已正确转义以安全包含在 SQL 语句中的字符串。 |
连接到数据库
以下 PHP 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
?>
现在，让我们运行上面的程序在当前目录中创建我们的数据库test.db。您可以根据需要更改路径。如果数据库创建成功，那么它将显示以下消息 –
Open database successfully
创建表
下面的 PHP 程序将用于在之前创建的数据库中创建一个表。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\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 "Table created successfully\n";
}
$db->close();
?>
执行上述程序时，它将在您的test.db 中创建 COMPANY 表，并显示以下消息 –
Opened database successfully Table created successfully
插入操作
下面的 PHP 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db){
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;
$ret = $db->exec($sql);
if(!$ret) {
echo $db->lastErrorMsg();
} else {
echo "Records created successfully\n";
}
$db->close();
?>
执行上述程序时，它将在 COMPANY 表中创建给定的记录，并显示以下两行。
Opened database successfully Records created successfully
选择操作
以下 PHP 程序显示了如何从上面示例中创建的 COMPANY 表中获取和显示记录 –
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>
执行上述程序时，将产生以下结果。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
更新操作
以下 PHP 代码显示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取并显示更新的记录。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
$ret = $db->exec($sql);
if(!$ret) {
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record updated successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>
执行上述程序时，将产生以下结果。
Opened database successfully 1 Record updated successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
删除操作
以下 PHP 代码展示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录。
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB();
if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF
DELETE from COMPANY where ID = 2;
EOF;
$ret = $db->exec($sql);
if(!$ret){
echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record deleted successfully\n";
}
$sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>
执行上述程序时，将产生以下结果。
Opened database successfully 1 Record deleted successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
SQLite – Perl
在本章中，您将学习如何在 Perl 程序中使用 SQLite。
安装
SQLite3 可以使用 Perl DBI 模块与 Perl 集成，Perl DBI 模块是 Perl 编程语言的数据库访问模块。它定义了一组提供标准数据库接口的方法、变量和约定。
以下是在 Linux/UNIX 机器上安装 DBI 模块的简单步骤 –
$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625 $ perl Makefile.PL $ make $ make install
如果您需要为 DBI 安装 SQLite 驱动程序，则可以按如下方式安装 –
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz $ cd DBD-SQLite-1.11 $ perl Makefile.PL $ make $ make install
DBI 接口 API
以下是重要的 DBI 例程，它们可以满足您从 Perl 程序使用 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 Perl DBI 官方文档。
| Sr.No. | API & 描述 |
|---|---|
| 1 |
DBI->connect($data_source, “”, “”, \%attr) 建立到请求的 $data_source 的数据库连接或会话。如果连接成功，则返回数据库句柄对象。 数据源的格式如下 – DBI:SQLite:dbname = ‘test.db’其中 SQLite 是 SQLite 驱动程序名称，test.db 是 SQLite 数据库文件的名称。如果文件名指定为‘:memory:’，它将在 RAM 中创建一个仅在会话期间持续的内存数据库。 如果文件名是实际设备文件名，则它会尝试使用其值打开数据库文件。如果不存在具有该名称的文件，则会创建具有该名称的新数据库文件。 您将第二个和第三个参数保留为空字符串，最后一个参数用于传递各种属性，如下例所示。 |
| 2 |
$dbh->do($sql) 此例程准备并执行单个 SQL 语句。返回受影响的行数或 undef 出错。返回值 -1 表示行数未知、不适用或不可用。这里，$dbh 是 DBI->connect() 调用返回的句柄。 |
| 3 |
$dbh->prepare($sql) 该例程为数据库引擎稍后执行准备一条语句，并返回对语句句柄对象的引用。 |
| 4 |
$sth->execute() 该例程执行执行准备好的语句所需的任何处理。如果发生错误，则返回 undef。无论受影响的行数如何，成功的执行总是返回 true。这里，$sth 是 $dbh->prepare($sql) 调用返回的语句句柄。 |
| 5 |
$sth->fetchrow_array() 此例程获取下一行数据并将其作为包含字段值的列表返回。空字段作为列表中的 undef 值返回。 |
| 6 |
$DBI::err 这等效于 $h->err，其中 $h 是任何句柄类型，如 $dbh、$sth 或 $drh。这将从上次调用的驱动程序方法返回本机数据库引擎错误代码。 |
| 7 |
$DBI::errstr 这等效于 $h->errstr，其中 $h 是任何句柄类型，例如 $dbh、$sth 或 $drh。这将返回来自最后调用的 DBI 方法的本机数据库引擎错误消息。 |
| 8 |
$dbh->disconnect() 此例程关闭先前通过调用 DBI->connect() 打开的数据库连接。 |
连接到数据库
以下 Perl 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
现在，让我们运行上面的程序在当前目录中创建我们的数据库 test.db。您可以根据需要更改路径。将上面的代码保存在 sqlite.pl 文件中，并按如下所示执行它。如果数据库创建成功，那么它将显示以下消息 –
$ chmod +x sqlite.pl $ ./sqlite.pl Open database successfully
创建表
下面的 Perl 程序用于在之前创建的数据库中创建一个表。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL););
my $rv = $dbh->do($stmt);
if($rv < 0) {
print $DBI::errstr;
} else {
print "Table created successfully\n";
}
$dbh->disconnect();
执行上述程序时，它将在您的 test.db 中创建 COMPANY 表，并显示以下消息 –
Opened database successfully Table created successfully
注意– 如果您在任何操作中看到以下错误 –
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398
在这种情况下，打开DBD-SQLite 安装中可用的dbdimp.c 文件并找出sqlite3_prepare()函数并将其第三个参数更改为-1而不是 0。最后，使用make安装 DBD::SQLite并执行make install以解决问题。
插入操作
下面的 Perl 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();
执行上述程序时，它将在 COMPANY 表中创建给定的记录，并显示以下两行 –
Opened database successfully Records created successfully
选择操作
下面的 Perl 程序显示了如何从上面示例中创建的 COMPANY 表中获取和显示记录。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
执行上述程序时，将产生以下结果。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
更新操作
以下 Perl 代码显示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取并显示更新的记录。
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else {
print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
执行上述程序时，将产生以下结果。
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
删除操作
以下 Perl 代码显示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录 –
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database";
my $userid = "";
my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(DELETE from COMPANY where ID = 2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) {
print $DBI::errstr;
} else {
print "Total number of rows deleted : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) {
print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();
执行上述程序时，将产生以下结果。
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully
SQLite – Python
在本章中，您将学习如何在 Python 程序中使用 SQLite。
安装
SQLite3 可以使用由 Gerhard Haring 编写的 sqlite3 模块与 Python 集成。它提供了一个符合 PEP 249 描述的 DB-API 2.0 规范的 SQL 接口。你不需要单独安装这个模块，因为它默认随 Python 2.5.x 版一起提供。
要使用 sqlite3 模块，您必须首先创建一个表示数据库的连接对象，然后您可以选择创建一个游标对象，这将帮助您执行所有 SQL 语句。
Python sqlite3 模块 API
以下是重要的 sqlite3 模块例程，它们可以满足您从 Python 程序使用 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 Python sqlite3 模块的官方文档。
| Sr.No. | API & 描述 |
|---|---|
| 1 |
sqlite3.connect(database [,timeout ,other optional arguments]) 此 API 打开与 SQLite 数据库文件的连接。您可以使用 “:memory:” 来打开到驻留在 RAM 中而不是磁盘上的数据库的数据库连接。如果数据库打开成功，它返回一个连接对象。 当一个数据库被多个连接访问，并且其中一个进程修改了该数据库时，SQLite 数据库将被锁定，直到该事务被提交。timeout 参数指定连接应该等待锁定消失的时间，直到引发异常。timeout 参数的默认值为 5.0（五秒）。 如果给定的数据库名称不存在，则此调用将创建数据库。如果要在当前目录以外的任何其他位置创建数据库，也可以使用所需路径指定文件名。 |
| 2 |
connection.cursor([cursorClass]) 此例程创建一个游标，它将在您使用 Python 进行数据库编程的整个过程中使用。此方法接受单个可选参数 cursorClass。如果提供，这必须是扩展 sqlite3.Cursor 的自定义游标类。 |
| 3 |
cursor.execute(sql [, optional parameters]) 该例程执行 SQL 语句。SQL 语句可以参数化（即占位符而不是 SQL 文字）。sqlite3 模块支持两种占位符：问号和命名占位符（命名样式）。 例如– cursor.execute(“insert into people values (?, ?)”, (who, age)) |
| 4 |
connection.execute(sql [, optional parameters]) 该例程是游标对象提供的上述execute方法的快捷方式，它通过调用游标方法创建一个中间游标对象，然后使用给定的参数调用游标的execute方法。 |
| 5 |
cursor.executemany(sql, seq_of_parameters) 此例程针对在序列 sql 中找到的所有参数序列或映射执行 SQL 命令。 |
| 6 |
connection.executemany(sql[, parameters]) 这个例程是一个快捷方式，它通过调用游标方法创建一个中间游标对象，然后使用给定的参数调用 cursor.s 的 executemany 方法。 |
| 7 |
cursor.executescript(sql_script) 该例程一次执行多个以脚本形式提供的 SQL 语句。它首先发出 COMMIT 语句，然后执行它作为参数获取的 SQL 脚本。所有的 SQL 语句都应该用分号 (;) 分隔。 |
| 8 |
connection.executescript(sql_script) 这个例程是一个快捷方式，它通过调用游标方法创建一个中间游标对象，然后使用给定的参数调用游标的 executescript 方法。 |
| 9 |
connection.total_changes() 此例程返回自数据库连接打开以来已修改、插入或删除的数据库行总数。 |
| 10 |
connection.commit() 此方法提交当前事务。如果不调用此方法，则自上次调用 commit() 以来所做的任何事情都不会从其他数据库连接中看到。 |
| 11 |
connection.rollback() 此方法回滚自上次调用 commit() 以来对数据库的任何更改。 |
| 12 |
connection.close() 此方法关闭数据库连接。请注意，这不会自动调用 commit()。如果您在没有先调用 commit() 的情况下关闭数据库连接，您的更改将会丢失！ |
| 13 |
cursor.fetchone() 此方法获取查询结果集的下一行，返回单个序列，或者在没有更多数据可用时返回 None。 |
| 14 |
cursor.fetchmany([size = cursor.arraysize]) 该例程获取查询结果的下一组行，返回一个列表。当没有更多行可用时，将返回一个空列表。该方法尝试获取由 size 参数指示的尽可能多的行。 |
| 15 |
cursor.fetchall() 此例程获取查询结果的所有（剩余）行，返回一个列表。当没有可用行时，返回一个空列表。 |
连接到数据库
以下 Python 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
在这里，您还可以提供数据库名称作为特殊名称:memory:以在 RAM 中创建数据库。现在，让我们运行上面的程序在当前目录中创建我们的数据库test.db。您可以根据需要更改路径。将上面的代码保存在 sqlite.py 文件中，然后按如下所示执行。如果数据库创建成功，则会显示以下消息。
$chmod +x sqlite.py $./sqlite.py Open database successfully
创建表
下面的 Python 程序将用于在之前创建的数据库中创建一个表。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
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()
执行上述程序时，它将在您的test.db 中创建 COMPANY 表，并显示以下消息 –
Opened database successfully Table created successfully
插入操作
下面的 Python 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()
print "Records created successfully";
conn.close()
执行上述程序时，它将在 COMPANY 表中创建给定的记录，并显示以下两行 –
Opened database successfully Records created successfully
选择操作
以下 Python 程序显示了如何从上面示例中创建的 COMPANY 表中获取和显示记录。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
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 "Operation done successfully";
conn.close()
执行上述程序时，将产生以下结果。
Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
更新操作
以下 Python 代码显示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取并显示更新的记录。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes
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 "Operation done successfully";
conn.close()
执行上述程序时，将产生以下结果。
Opened database successfully Total number of rows updated : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000.0 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
删除操作
以下 Python 代码展示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余记录。
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
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 "Operation done successfully";
conn.close()
执行上述程序时，将产生以下结果。
Opened database successfully Total number of rows deleted : 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 20000.0 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully
