在这个文档中，我们将讲解如何在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', 'alice@example.com', 25),
('Bob', 'bob@example.com', 30),
('Charlie', 'charlie@example.com', 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 TEXT11. 删除表
如果你不再需要某个表，可以删除它。
删除 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_db
SET totalQty = totalQty + 1
WHERE userId = '700172_hs6z' AND boxCode = 'X2410180007';
-- 联表查询
SELECT d.boxCode, d.userId, d.barCode,i.id,i.username
FROM slick_cart_details_db d
JOIN slick_cart_index_db i
ON d.boxCode = i.boxCode AND d.userId = i.userId
WHERE 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_num
FROM slick_cart_details_db d
JOIN slick_cart_index_db i
ON d.boxCode = i.boxCode AND d.userId = i.userId
WHERE 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 中每个存储在数据库中的值都属于以下存储类之一：
| 存储类 | 描述 |
| NULL | 值是一个 值。 |
| INTEGER | 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。 |
| REAL | 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。 |
| TEXT | 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。 |
| BLOB | 值是一个 数据，完全根据它的输入存储。 |
SQLite 存储类比数据类型更为普遍。举例来说，INTEGER 存储类包含 6 种不同长度的整数数据类型。
2. SQLite Affinity 类型
SQLite 支持列上的类型亲和力（Affinity）概念。每一列可以存储任何类型的数据，但它的首选存储类由其亲和力（Affinity）决定。SQLite 为每个表的列分配了以下亲和力类型：
| Affinity 类型 | 描述 |
| TEXT | 该列使用存储类 、 或 存储所有数据。 |
| NUMERIC | 该列可以包含使用所有五个存储类的值。 |
| INTEGER | 与带有 亲和力的列相同，但会在 表达式中带有异常。 |
| REAL | 与带有 亲和力的列相似，不同之处在于，它会强制将整数值转换为浮点表示。 |
| NONE | 带有 亲和力的列，不会优先使用哪个存储类，也不会尝试强制转换数据类型。 |
3. SQLite 支持的类型名称与 Affinity
下表列出了在创建 SQLite3 表时可使用的各种数据类型名称，以及其对应的亲和力类型：
| 数据类型 | Affinity 类型 |
|
, , , , , , , , | INTEGER |
|
, , , , , | TEXT |
|
| TEXT |
|
| NONE |
|
, , , | REAL |
|
, | NUMERIC |
|
| NUMERIC |
|
, | NUMERIC |
备注：
- 布尔数据类型：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 开始的秒数。
- TEXT：格式为
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 city
FROM users
WHERE 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_name
FROM users
JOIN friends ON users.id = friends.user_id
WHERE users.name = 'John';这种方式的优势是数据结构清晰，查询操作可以直接进行，但可能会增加一些复杂性，尤其是在处理嵌套对象或数组时。
7. 结论
- SQLite 存储类决定了数据库中值的存储方式，而Affinity 类型决定了列的首选存储类型，SQLite 提供了灵活的存储方式。
- 对于日期和时间，可以选择
TEXT、REAL或INTEGER格式来存储，根据需要进行转换。 - 布尔值在 SQLite 中以整数（0 和 1）表示，而没有单独的布尔数据类型。
SQLite 算术运算符
假设变量 a=10，变量 b=20，则：
| 运算符 | 描述 | 实例 |
| + | 加法 - 把运算符两边的值相加 | a + b 将得到 30 |
| - | 减法 - 左操作数减去右操作数 | a - b 将得到 -10 |
| * | 乘法 - 把运算符两边的值相乘 | a * b 将得到 200 |
| / | 除法 - 左操作数除以右操作数 | b / a 将得到 2 |
| % | 取模 - 左操作数除以右操作数后得到的余数 | b % a will give 0 |
SQLite 比较运算符
假设变量 a=10，变量 b=20，则：
| 运算符 | 描述 | 实例 |
| == | 检查两个操作数的值是否相等，如果相等则条件为真。 | (a == b) 不为真。 |
| = | 检查两个操作数的值是否相等，如果相等则条件为真。 | (a = b) 不为真。 |
| != | 检查两个操作数的值是否相等，如果不相等则条件为真。 | (a != b) 为真。 |
| <> | 检查两个操作数的值是否相等，如果不相等则条件为真。 | (a <> b) 为真。 |
| > | 检查左操作数的值是否大于右操作数的值，如果是则条件为真。 | (a > b) 不为真。 |
| < | 检查左操作数的值是否小于右操作数的值，如果是则条件为真。 | (a < b) 为真。 |
| >= | 检查左操作数的值是否大于等于右操作数的值，如果是则条件为真。 | (a >= b) 不为真。 |
| <= | 检查左操作数的值是否小于等于右操作数的值，如果是则条件为真。 | (a <= b) 为真。 |
| !< | 检查左操作数的值是否不小于右操作数的值，如果是则条件为真。 | (a !< b) 为假。 |
| !> | 检查左操作数的值是否不大于右操作数的值，如果是则条件为真。 | (a !> b) 为真。 |
SQLite 逻辑运算符
下面是 SQLite 中所有的逻辑运算符列表。
| 运算符 | 描述 |
| AND | AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。 |
| BETWEEN | BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。 |
| EXISTS | EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。 |
| IN | IN 运算符用于把某个值与一系列指定列表的值进行比较。 |
| NOT IN | IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。 |
| LIKE | LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。 |
| GLOB | GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。 |
| NOT | NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。 |
| OR | OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。 |
| IS NULL | NULL 运算符用于把某个值与 NULL 值进行比较。 |
| IS | IS 运算符与 = 相似。 |
| IS NOT | IS NOT 运算符与 != 相似。 |
| || | 连接两个不同的字符串，得到一个新的字符串。 |
| uniqUE | UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。 |
SQLite 位运算符
位运算符作用于位，并逐位执行操作。真值表 & 和 | 如下：
| p | q | p & q | p | q |
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
| 运算符 | 描述 | 实例 |
| & | 如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中。 | (A & B) 将得到 12，即为 0000 1100 |
| | | 如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。 | (A | B) 将得到 61，即为 0011 1101 |
| ~ | 二进制补码运算符是一元运算符，具有"翻转"位效应。 | (~A ) 将得到 -61，即为 1100 0011，2 的补码形式，带符号的二进制数。 |
| << | 二进制左移运算符。左操作数的值向左移动右操作数指定的位数。 | A << 2 将得到 240，即为 1111 0000 |
| >> | 二进制右移运算符。左操作数的值向右移动右操作数指定的位数。 | A >> 2 将得到 15，即为 0000 1111 |
SQLite 常用函数
sqlite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，这意味着您可以使用这些函数的小写形式或大写形式或混合形式。欲了解更多详情，请查看 SQLite 的官方文档：
| 序号 | 函数 & 描述 |
| 1 | SQLite COUNT 函数 表中的行数。 |
| 2 | SQLite MAX 函数 |
| 3 | SQLite MIN 函数 |
| 4 | SQLite AVG 函数 |
| 5 | SQLite SUM 函数 |
| 6 | SQLite RANDOM 函数 |
| 7 | SQLite ABS 函数 |
| 8 | SQLite UPPER 函数 |
| 9 | SQLite LOWER 函数 |
| 10 | SQLite LENGTH 函数 |
| 11 | SQLite sqlite_version 函数 |
在我们开始讲解这些函数实例之前，先假设 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.0COUNT 函数
SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：
sqlite> SELECT count(*) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
count(*)
----------
7MAX 函数
SQLite MAX 聚合函数允许我们选择某列的最大值。下面是实例：
sqlite> SELECT max(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
max(salary)
-----------
85000.0MIN 函数
SQLite MIN 聚合函数允许我们选择某列的最小值。下面是实例：
sqlite> SELECT min(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
min(salary)
-----------
10000.0AVG 函数
SQLite AVG 聚合函数计算某列的平均值。下面是实例：
sqlite> SELECT avg(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
avg(salary)
----------------
37142.8571428572SUM 函数
SQLite SUM 聚合函数允许为一个数值列计算总和。下面是实例：
sqlite> SELECT sum(salary) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
sum(salary)
-----------
260000.0RANDOM 函数
SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。下面是实例：
sqlite> SELECT random() AS Random;
上面的 SQLite SQL 语句将产生以下结果：
Random
-------------------
5876796417670984050ABS 函数
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.0UPPER 函数
SQLite UPPER 函数把字符串转换为大写字母。下面是实例：
sqlite> SELECT upper(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMESLOWER 函数
SQLite LOWER 函数把字符串转换为小写字母。下面是实例：
sqlite> SELECT lower(name) FROM COMPANY;
上面的 SQLite SQL 语句将产生以下结果：
lower(name)
-----------
paul
allen
teddy
mark
david
kim
jamesLENGTH 函数
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 5sqlite_version 函数
SQLite sqlite_version 函数返回 SQLite 库的版本
SQLite 日期 & 时间
sqlite 支持以下五个日期和时间函数：
| 序号 | 函数 | 实例 |
| 1 | date(timestring, modifiers...) | 以 YYYY-MM-DD 格式返回日期。 |
| 2 | time(timestring, modifiers...) | 以 HH:MM:SS 格式返回时间。 |
| 3 | datetime(timestring, modifiers...) | 以 YYYY-MM-DD HH:MM:SS 格式返回。 |
| 4 | julianday(timestring, modifiers...) | 这将返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数。 |
| 5 | strftime(timestring, modifiers...) | 这将根据第一个参数指定的格式字符串返回格式化的日期。具体格式见下边讲解。 |
上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifiers 修饰符。strftime() 函数也可以把格式字符串作为其第一个参数。下面将为您详细讲解不同类型的时间字符串和修饰符
时间字符串
一个时间字符串可以采用下面任何一种格式：
| 序号 | 时间字符串 | 实例 |
| 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 | HH:MM | 12:10 |
| 6 | YYYY-MM-DDTHH:MM | 2010-12-30 12:10 |
| 7 | HH:MM:SS | 12:10:01 |
| 8 | YYYYMMDD HHMMSS | 20101230 121001 |
| 9 | now | 2013-05-07 |
您可以使用 "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:00
sqlite> 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.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.0INSERT 语句中的子查询使用
子查询也可以与 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.0DELETE 语句中的子查询使用
子查询可以与 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
扫一扫
1713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
