在这个文档中，我们将讲解如何在SQLite中操作一个简单的 user表。通过这个示例，你将学会如何创建表、插入数据、查询、更新、删除数据等常见操作。

1. 创建 user表

首先，我们需要创建一个表来存储用户数据。这个表将包含以下列：

• id：用户ID，唯一标识每个用户。
• username：用户的用户名，不能为空。
• email：用户的电子邮件，不能为空，且不能重复。
• age：用户的年龄。
• created_at：记录创建时间，自动生成。

创建表的SQL语句：

CREATE TABLE user (  id INTEGER PRIMARY KEY AUTOINCREMENT,   -- 自动增长的主键  username TEXT NOT NULL,                  -- 用户名不能为空  email TEXT NOT NULL UNIQUE,              -- 邮箱不能为空且唯一  age INTEGER,                             -- 年龄可以为空  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 默认值为当前时间);/* CREATE TABLE user (    id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 自动递增的 id 字段    username TEXT NOT NULL,                -- 用户名    status TEXT CHECK(status IN ('active', 'inactive')),  -- 用户状态    age INTEGER CHECK(age >= 18),          -- 用户年龄，最小值为 18    created_at DATE DEFAULT (DATE('now'))  -- 创建日期，默认为当前日期); */

如何理解：

• id是主键，代表每个用户的唯一标识。
• username和 email是用户的基本信息，不能为空且邮箱唯一。
• created_at会自动记录每条记录创建的时间。

2. 向 user表插入数据

创建了表之后，我们需要往表中插入一些数据。

插入数据的SQL语句：

INSERT INTO user (username, email, age) VALUES     ('Alice', '[email protected]', 25),    ('Bob', '[email protected]', 30),    ('Charlie', '[email protected]', 35);INSERT INTO user (username, status, age, created_at) VALUES    ('user' || ABS(RANDOM() % 10000),      CASE WHEN RANDOM() % 2 = 0 THEN 'active' ELSE 'inactive' END,     ABS(RANDOM() % 50 + 18),  -- 随机生成18-67之间的年龄     date('now', '-' || ABS(RANDOM() % 365) || ' days'));  -- 随机生成过去一年内的日期*/     INSERT INTO user (username, status, age, created_at)     VALUES        ('user' || ABS(RANDOM() % 10000),          CASE WHEN RANDOM() % 2 = 0 THEN 'active' ELSE 'inactive' END,         ABS(RANDOM() % 50 + 18),          date('now', '-' || ABS(RANDOM() % 365) || ' days'));        INSERT INTO user (username, status, age, created_at) VALUES    ('user1', 'active', 25, '2024-11-12'),    ('user2', 'inactive', 30, '2024-11-11'),    ('user3', 'active', 40, '2024-11-10'),    ('user100', 'inactive', 29, '2024-10-01')INSERT INTO user (username, status, age, created_at);VALUES    ('user' || ABS(RANDOM() % 10000), CASE WHEN RANDOM() % 2 = 0 THEN 'active' ELSE 'inactive' END, ABS(RANDOM() % 50 + 18), date('now', '-' || ABS(RANDOM() % 365) || ' days')),    ('user' || ABS(RANDOM() % 10000), CASE WHEN RANDOM() % 2 = 0 THEN 'active' ELSE 'inactive' END, ABS(RANDOM() % 50 + 18), date('now', '-' || ABS(RANDOM() % 365) || ' days')),    -- ... 继续添加更多的 INSERT 行 ...    ('user' || ABS(RANDOM() % 10000), CASE WHEN RANDOM() % 2 = 0 THEN 'active' ELSE 'inactive' END, ABS(RANDOM() % 50 + 18), date('now', '-' || ABS(RANDOM() % 365) || ' days'));    --js循环生成数据  let sqlQuery = "INSERT INTO user (username, status, age, created_at) VALUES\n";for (let i = 0; i < 1000; i++) {     const username = `user${ Math.abs(Math.floor(Math.random() * 10000))}`;    const status = Math.random() < 0.5 ? 'active' : 'inactive';    const age = Math.floor(Math.random() * 50) + 18;    const createdAt = new Date(Date.now() - Math.floor(Math.random() * 365) * 24 * 60 * 60 * 1000)                        .toISOString().split('T')[0]; // 格式化为 YYYY-MM-DD    // 拼接一条插入语句    sqlQuery += `('${ username}', '${ status}', ${ age}, '${ createdAt}')`;        // 如果不是最后一条，加上逗号    if (i < 999) {         sqlQuery += ',\n';    }}console.log(sqlQuery);

如何理解：

• 每条数据包括 username、email和 age，这些字段对应于我们表中的列。
• 插入数据时不需要为 id和 created_at提供值，因为它们会自动生成。

3. 查询数据

查询数据是最常见的操作，SQLite提供了多种方式来检索数据。

查询所有用户：

SELECT * FROM user;select *from  user where age>20 and status ='active' and created_at='2024-07-28'

如何理解：

• SELECT *表示选择所有列的数据，FROM user表示从 user表中查询。

查询某些列：

SELECT username, email FROM user;

如何理解：

• SELECT username, email只选择用户名和邮箱列。

根据条件查询数据（例如查找年龄大于30的用户）：

SELECT * FROM user WHERE age > 30;

如何理解：

• WHERE子句用于指定条件，这里我们只选择 age > 30的用户。

4. 更新数据

如果某个用户的资料需要更改，我们可以使用 UPDATE语句。

更新 Bob的年龄：

UPDATE user SET age = 32 WHERE username = 'Bob';UPDATE  user set age=20 where age>45 and age<60 and status ='active' and created_at='2024-07-28'

如何理解：

• UPDATE user表示要更新 user表。
• SET age = 32表示把 Bob的年龄更新为 32。
• WHERE username = 'Bob'限定了只更新用户名为 Bob的记录。

5. 删除数据

删除某个用户或者清空表中的数据也很简单。

删除某个用户（例如 Charlie）：

DELETE FROM user WHERE username = 'Charlie';

如何理解：

• DELETE FROM user表示从 user表中删除数据。
• WHERE username = 'Charlie'确保只删除 Charlie的记录。

删除所有数据（清空表）：

DELETE FROM user;

如何理解：

• 这条命令会删除表中所有的记录，但表的结构仍然存在。

6. 查询数据的数量

你可以使用 COUNT()来统计表中的记录数。

查询用户数量：

SELECT COUNT(*) FROM user;

如何理解：

• COUNT(*)会返回 user表中的总行数，即用户的总数。

7. 使用 LIKE进行模糊查询

如果你想找到用户名中包含某些字符的用户，可以使用 LIKE。

查询用户名包含字母 a的所有用户：

SELECT * FROM user WHERE username LIKE '%a%';

如何理解：

• LIKE '%a%'表示匹配任何包含字母 a的用户名。

8. 排序查询结果

你可以按某一列对查询结果进行排序。

按年龄从小到大排序：

SELECT * FROM user ORDER BY age ASC;

如何理解：

• ORDER BY age按照 age列排序。
• ASC表示升序（从小到大），如果你想降序排列，可以用 DESC。

9. 创建索引（提高查询速度）

如果你经常按某列查询数据，可以创建索引来提高查询效率。

在 username列上创建索引：

CREATE INDEX idx_username ON user (username);

如何理解：

• 创建索引能加速按 username查询的速度。

10.更新表字段

ALTER TABLE silk_cart_db ADD COLUMN column TEXT

11. 删除表

如果你不再需要某个表，可以删除它。

删除 user表：

DROP TABLE user;

如何理解：

• DROP TABLE会删除整个表，包括所有数据和结构。

12.创建联表

-- 父表CREATE TABLE slick_cart_index_db (   uuid INTEGER PRIMARY KEY AUTOINCREMENT,   userId TEXT,   id TEXT,   username TEXT NOT NULL,   totalQty INTEGER,   boxCode TEXT);-- 子表CREATE TABLE slick_cart_details_db (    uuid INTEGER PRIMARY KEY AUTOINCREMENT,    boxCode TEXT,    userId TEXT,    barCode TEXT UNIQUE  -- 添加 UNIQUE 约束，确保 barCode 唯一)-- 插入父表数据INSERT INTO slick_cart_index_db (    userId,    id,    username,    totalQty,    boxCode) VALUES (    '700172_hs6z',     '1847075711110275072',     '700172',     0,     'X2410180007');-- 查询   select * from slick_cart_index_db 是不是有这个数据 -- 子表操作 添加数据 INSERT INTO slick_cart_details_db (       boxCode,    userId,barCode) VALUES (    'X2410180007', '700172_hs6z','JS241017012640'); --注入的时候需要把父表的totalQty +1 -- 更新 slick_cart_index_db 表中的 totalQty 字段UPDATE slick_cart_index_dbSET totalQty = totalQty + 1WHERE userId = '700172_hs6z' AND boxCode = 'X2410180007';-- 联表查询SELECT d.boxCode, d.userId, d.barCode,i.id,i.usernameFROM slick_cart_details_db dJOIN slick_cart_index_db i  ON d.boxCode = i.boxCode AND d.userId = i.userIdWHERE d.userId = '700172_hs6z' AND d.boxCode = 'X2410180007';-- 加了个总数量 还有一个 ROW_NUMBER 当前下标SELECT     d.boxCode,     d.userId,     d.barCode,     i.id,     i.username,     COUNT(*) OVER () AS total_count,    ROW_NUMBER() OVER (ORDER BY d.boxCode, d.userId) AS row_numFROM slick_cart_details_db dJOIN slick_cart_index_db i  ON d.boxCode = i.boxCode AND d.userId = i.userIdWHERE d.userId = '700172_hs6z' AND d.boxCode = 'X2410180007';

SQLite语句

1. ANALYZE 语句

ANALYZE;

生成数据库的统计信息，以优化查询的执行计划。

ANALYZE database_name;

分析特定数据库的性能。

ANALYZE database_name.table_name;

分析特定表的性能。

2. AND/OR 子句

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

使用 AND或 OR来连接多个查询条件。

3. ALTER TABLE 语句

ALTER TABLE table_name ADD COLUMN column_def...;

向已有表中添加新列。

ALTER TABLE table_name RENAME TO new_table_name;

重命名现有的表。

4. ATTACH DATABASE 语句

ATTACH DATABASE 'DatabaseName' AS 'Alias-Name';

将外部数据库附加到当前数据库，并为其指定别名。

5. BEGIN TRANSACTION 语句

BEGIN;

开始一个事务。

BEGIN EXCLUSIVE TRANSACTION;

开始一个独占事务。

6. BETWEEN 子句

SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;

用于查询在指定范围内的数据。

7. COMMIT 语句

COMMIT;

提交事务，永久保存对数据库的更改。

8. CREATE INDEX 语句

CREATE INDEX index_name ON table_name (column_name COLLATE NOCASE);

创建一个索引，以提高查询效率，COLLATE NOCASE用于忽略大小写。

9. CREATE UNIQUE INDEX 语句

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

创建一个唯一索引，确保指定列的值是唯一的。

10. CREATE TABLE 语句

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

创建一个新表，并可指定主键。

11. CREATE TRIGGER 语句

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

创建触发器，用于在插入数据之前执行特定的操作。

12. CREATE VIEW 语句

CREATE VIEW view_name AS SELECT statement;

创建视图，视图是基于查询结果的虚拟表。

13. CREATE VIRTUAL TABLE 语句

CREATE VIRTUAL TABLE table_name USING fts3;

创建一个虚拟表，支持特定类型的查询，例如全文搜索。

14. COUNT 子句

SELECT COUNT(column_name) FROM table_name WHERE CONDITION;

计算符合条件的记录数量。

15. DELETE 语句

DELETE FROM table_name WHERE CONDITION;

删除符合条件的记录。

16. DETACH DATABASE 语句

DETACH DATABASE 'Alias-Name';

卸载已附加的数据库。

17. DISTINCT 子句

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

查询唯一的记录，去除重复的行。

18. DROP INDEX 语句

DROP INDEX index_name;

删除指定的索引。

19. DROP TABLE 语句

DROP TABLE table_name;

删除指定的表及其所有数据。

20. DROP VIEW 语句

DROP VIEW view_name;

删除指定的视图。

21. DROP TRIGGER 语句

DROP TRIGGER trigger_name;

删除指定的触发器。

22. EXISTS 子句

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

检查子查询是否返回结果。

23. EXPLAIN 语句

EXPLAIN INSERT statement...;

分析 INSERT语句的执行计划。

EXPLAIN QUERY PLAN SELECT statement...;

分析 SELECT语句的执行计划。

24. GLOB 子句

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

使用 GLOB进行模式匹配。

25. GROUP BY 子句

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

按指定列分组并进行聚合操作。

26. HAVING 子句

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

用于对分组结果进行筛选。

27. INSERT INTO 语句

INSERT INTO table_name (column1, column2....columnN) VALUES (value1, value2....valueN);INSERT INTO table_name (ID,NAME,AGE,ADDRESS,SALARY)VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );-- 或者 INSERT INTO table_name VALUES (7, 'James', 24, 'Houston', 10000.00 );

向表中插入数据。

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

--您可以通过在一个有一组字段的表上使用 select 语句，填充数据到另一个表中INSERT INTO first_table_name [(column1, column2, ... columnN)]    SELECT column1, column2, ...columnN    FROM second_table_name   [WHERE condition];

28. IN 子句

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

用于检查列的值是否属于指定的集合。

29. LIKE 子句

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

用于模糊查询。

30. NOT IN 子句

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

用于排除某些值。

31. ORDER BY 子句

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

对查询结果进行排序。

32. PRAGMA 语句

PRAGMA pragma_name;

用于设置或查询 SQLite 配置。 示例：

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

33. RELEASE SAVEPOINT 语句

RELEASE savepoint_name;

释放保存点。

34. REINDEX 语句

REINDEX collation_name;

重新创建指定的排序规则的索引。

REINDEX database_name.index_name;

重新创建指定数据库的索引。

REINDEX database_name.table_name;

重新创建指定表的索引。

35. ROLLBACK 语句

ROLLBACK;

回滚事务，撤销对数据库的更改。

ROLLBACK TO SAVEPOINT savepoint_name;

回滚到指定的保存点。

36. SAVEPOINT 语句

SAVEPOINT savepoint_name;

创建一个保存点，用于事务管理。

37. SELECT 语句

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

用于从表中查询数据。

38. UPDATE 语句

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

更新表中的现有记录。

39. VACUUM 语句

VACUUM;

清理数据库，回收未使用的空间，压缩数据库文件。

40. WHERE 子句

SELECT column1, column2....columnN FROM table_name WHERE CONDITION;

指定查询的条件。

将SQLite的数据类型、存储类和创建表的内容整理成文档，可以参考以下的结构。以下是一个基于Yuque文档格式的简要说明，帮助你了解SQLite的存储类、类型亲和力、数据类型以及如何使用它们在表中定义列。

SQLite 数据类型与存储类文档

1. SQLite 存储类

SQLite 中每个存储在数据库中的值都属于以下存储类之一：

SQLite 存储类比数据类型更为普遍。举例来说，INTEGER存储类包含 6 种不同长度的整数数据类型。

2. SQLite Affinity 类型

SQLite 支持列上的类型亲和力（Affinity）概念。每一列可以存储任何类型的数据，但它的首选存储类由其亲和力（Affinity）决定。SQLite 为每个表的列分配了以下亲和力类型：

3. SQLite 支持的类型名称与 Affinity

下表列出了在创建 SQLite3 表时可使用的各种数据类型名称，以及其对应的亲和力类型：

备注：

• 布尔数据类型：SQLite 没有单独的 BOOLEAN存储类，布尔值被存储为整数 0（表示 false）和 1（表示 true）。
• 日期与时间：SQLite 没有单独的日期/时间存储类。你可以使用以下三种格式之一存储日期和时间：

• • TEXT：格式为 YYYY-MM-DD HH:MM:SS.SSS。
  • REAL：表示从公元前 4714 年 11 月 24 日格林尼治时间正午开始算起的天数。
  • INTEGER：表示从 1970-01-01 00:00:00 UTC 开始的秒数。

4. 数据类型与表创建示例

创建表示例

CREATE TABLE employee (  id INTEGER PRIMARY KEY,  name TEXT NOT NULL,  age INTEGER,  salary REAL,  hire_date TEXT,  is_active NUMERIC);

• id列使用 INTEGER类型和 PRIMARY KEY约束，意味着它是表的主键。
• name列使用 TEXT类型，存储员工的名字。
• age列使用 INTEGER类型，存储员工的年龄。
• salary列使用 REAL类型，存储员工的薪水。
• hire_date列使用 TEXT类型，存储员工的入职日期，格式为 YYYY-MM-DD HH:MM:SS.SSS。
• is_active列使用 NUMERIC类型，存储布尔值（0 或 1）表示员工是否活跃。

日期存储与查询示例

sql-- 插入数据（包含日期格式）INSERT INTO employee (id, name, age, salary, hire_date, is_active)VALUES (1, 'Alice', 30, 55000.00, '2024-05-15 09:00:00', 1);-- 查询日期格式SELECT name, hire_date FROM employee WHERE hire_date > '2024-01-01';

这里，hire_date列使用 TEXT类型存储日期，存储格式为 "YYYY-MM-DD HH:MM:SS.SSS"。

5. 存储 JSON 数据（使用 TEXT类型）

SQLite 并不原生支持复杂数据类型（如数组、对象、嵌套结构等），但你可以使用 JSON 格式将这些数据结构序列化并存储在 TEXT列中。SQLite 还提供了原生的 JSON 函数（自版本 3.9.0 起）来处理存储在文本字段中的 JSON 数据。

存储 JSON 数据

假设你有一个包含嵌套数组和对象的数据结构，例如：

{   "name": "John",  "age": 30,  "address": {     "street": "123 Main St",    "city": "New York"  },  "friends": [    { "name": "Alice", "age": 25},    { "name": "Bob", "age": 27}  ]}

你可以将这个 JSON 数据存储在 SQLite 的 TEXT列中。示例：

sqlCREATE TABLE users (  id INTEGER PRIMARY KEY,  name TEXT NOT NULL,  data TEXT);INSERT INTO users (name, data) VALUES ('John', '{ "name": "John", "age": 30, "address": { "street": "123 Main St", "city": "New York"}, "friends": [{ "name": "Alice", "age": 25}, { "name": "Bob", "age": 27}]}');

查询 JSON 数据

SQLite 允许你查询 JSON 数据并提取其中的字段。例如，获取 address中的 city字段：

SELECT json_extract(data, '$.address.city') AS cityFROM usersWHERE name = 'John';

此外，SQLite 还支持其他 JSON 函数，如 json_set（更新 JSON 数据）、json_insert（插入数据）、json_remove（删除字段）等。

6. 分表存储（Normalized 存储）

如果你希望能方便地查询嵌套的数据结构，并不希望将数据完全序列化为 JSON 或 BLOB，你还可以使用多个表来存储关系型数据。这种方法是传统的关系数据库设计（Normalization），适用于数据之间存在明确关系（如一对多、多对多）的情况。

例如，假设你有一个用户和多个朋友的关系，可以将用户和朋友分别存储在不同的表中，并使用外键将它们关联起来。

示例：分表存储用户及其朋友

CREATE TABLE users (  id INTEGER PRIMARY KEY,  name TEXT NOT NULL);CREATE TABLE friends (  user_id INTEGER,  friend_name TEXT,  FOREIGN KEY (user_id) REFERENCES users(id));-- 插入数据INSERT INTO users (name) VALUES ('John');INSERT INTO friends (user_id, friend_name) VALUES (1, 'Alice');INSERT INTO friends (user_id, friend_name) VALUES (1, 'Bob');

查询数据

SELECT users.name, friends.friend_nameFROM usersJOIN friends ON users.id = friends.user_idWHERE users.name = 'John';

这种方式的优势是数据结构清晰，查询操作可以直接进行，但可能会增加一些复杂性，尤其是在处理嵌套对象或数组时。

7. 结论

• SQLite 存储类决定了数据库中值的存储方式，而Affinity 类型决定了列的首选存储类型，SQLite 提供了灵活的存储方式。
• 对于日期和时间，可以选择 TEXT、REAL或 INTEGER格式来存储，根据需要进行转换。
• 布尔值在 SQLite 中以整数（0 和 1）表示，而没有单独的布尔数据类型。

SQLite 算术运算符

假设变量 a=10，变量 b=20，则：

SQLite 比较运算符

假设变量 a=10，变量 b=20，则：

SQLite 逻辑运算符

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

SQLite 位运算符

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

SQLite 常用函数

sqlite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情，请查看 SQLite 的官方文档：

在我们开始讲解这些函数实例之前，先假设 COMPANY 表有以下记录：

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

COUNT 函数

SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：

sqlite> SELECT count(*) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

count(*)----------7

MAX 函数

SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例：

sqlite> SELECT max(salary) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

max(salary)-----------85000.0

MIN 函数

SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例：

sqlite> SELECT min(salary) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

min(salary)-----------10000.0

AVG 函数

SQLite AVG 聚合函数计算某列的平均值。下面是实例：

sqlite> SELECT avg(salary) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

avg(salary)----------------37142.8571428572

SUM 函数

SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例：

sqlite> SELECT sum(salary) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

sum(salary)-----------260000.0

RANDOM 函数

SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例：

sqlite> SELECT random() AS Random;

上面的 SQLite SQL 语句将产生以下结果：

Random-------------------5876796417670984050

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

UPPER 函数

SQLite UPPER 函数把字符串转换为大写字母。下面是实例：

sqlite> SELECT upper(name) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

upper(name)-----------PAULALLENTEDDYMARKDAVIDKIMJAMES

LOWER 函数

SQLite LOWER 函数把字符串转换为小写字母。下面是实例：

sqlite> SELECT lower(name) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

lower(name)-----------paulallenteddymarkdavidkimjames

LENGTH 函数

SQLite LENGTH 函数返回字符串的长度。下面是实例：

sqlite> SELECT name, length(name) FROM COMPANY;

上面的 SQLite SQL 语句将产生以下结果：

NAME        length(name)----------  ------------Paul        4Allen       5Teddy       5Mark        4David       5Kim         3James       5

sqlite_version 函数

SQLite sqlite_version 函数返回 SQLite 库的版本

SQLite 日期 & 时间

sqlite 支持以下五个日期和时间函数：

上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifiers 修饰符。strftime() 函数也可以把格式字符串作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符

时间字符串

一个时间字符串可以采用下面任何一种格式：

您可以使用 "T" 作为分隔日期和时间的文字字符。

修饰符（Modifiers）

时间字符串后边可跟着零个或多个的修饰符，这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用，下面列出了可在 SQLite 中使用的修饰符：

• NNN days
• NNN hours
• NNN minutes
• NNN.NNNN seconds
• NNN months
• NNN years
• start of month
• start of year
• start of day
• weekday N
• unixepoch
• localtime
• utc

实例现在让我们使用 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 11:51:06下面是计算当前的 UNIX 时间戳：sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');1367926057下面是计算美国"独立宣言"签署以来的天数：sqlite> SELECT julianday('now') - julianday('1776-07-04');86504.4775830326下面是计算从 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:00sqlite>  SELECT time('12:00', 'utc');19:00:00

子查询

子查询或内部查询或嵌套查询是在另一个 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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

现在，让我们检查 SELECT 语句中的子查询使用：

sqlite> SELECT *      FROM COMPANY      WHERE ID IN (SELECT ID                   FROM COMPANY                   WHERE SALARY > 45000) ;

这将产生以下结果:

ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------4           Mark        25          Rich-Mond   65000.05           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 tableSET 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.02           Allen       25          Texas       15000.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       42500.06           Kim         22          South-Hall  45000.07           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.03           Teddy       23          Norway      20000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       42500.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

官方文档