SQLite学习笔记

一、SQLite中常用的常规查询和条件查询。

1.常规查询：
查询所有字段：SELECT * FROM 表名；
查询指定字段：SELECT 列1,列2,… FROM 表名；
使用 AS 给字段起别名：SELECT 字段 AS 名字… FROM 表名；
查询某个表的某个字段：SELECT 表名.字段 … FROM 表名；
通过 AS 给表起别名：SELECT 别名.字段 … FROM 表名 AS 别名；
消除重复行：SELECT DISTINCT 字段 FROM 表名；
2.条件查询：
比较运算符：如 >, <, >=, <=, =, !=, <>。例如 SELECT * FROM 表名 WHERE 条件；
逻辑运算符：如 AND, OR, NOT。例如 SELECT * FROM 表名 WHERE 条件1 AND 条件2;
模糊查询：使用 LIKE 或 GLOB。LIKE 使用百分号（%）代表零个、一个或多个数字或字符，下划线（_）代表一个单一的数字或字符；GLOB 使用星号（*）代表零个、一个或多个数字或字符，问号（？）代表一个单一的数字或字符。例如 SELECT * FROM 表名 WHERE 字段 LIKE ‘模式’;

二、SQLite中一些更高级和复杂的查询语句：

1.聚合函数：
COUNT(): 计算行数。例如 SELECT COUNT() FROM 表名；
SUM(): 计算某列的总和。例如 SELECT SUM(字段) FROM 表名；
AVG(): 计算某列的平均值。例如 SELECT AVG(字段) FROM 表名；
MIN(): 返回某列的最小值。例如 SELECT MIN(字段) FROM 表名；
MAX(): 返回某列的最大值。例如 SELECT MAX(字段) FROM 表名；
2.分组查询：
GROUP BY: 根据某个或多个字段对结果进行分组。例如 SELECT 字段1, COUNT() FROM 表名 GROUP BY 字段1;
HAVING: 在分组后对结果进行筛选。例如 SELECT 字段1, COUNT() FROM 表名 GROUP BY 字段1 HAVING COUNT() > 5;
3.排序查询：
ORDER BY: 对结果进行排序。例如 SELECT * FROM 表名 ORDER BY 字段1 ASC, 字段2 DESC;
LIMIT: 限制结果的数量。例如 SELECT * FROM 表名 LIMIT 10;
4.子查询：
IN: 判断某个值是否在一组值中。例如 SELECT * FROM 表名 WHERE 字段 IN (SELECT 字段 FROM 表名);
EXISTS: 判断是否存在满足条件的记录。例如 SELECT * FROM 表名 WHERE EXISTS (SELECT * FROM 表名 WHERE 条件);

三、连接（Joins）、窗口函数（Window Functions）、更复杂的子查询和组合条件。

1.连接（Joins）:

内连接（INNER JOIN）： 返回两个表中存在匹配的行。

SELECT * FROM 表1
INNER JOIN 表2 ON 表1.关联字段 = 表2.关联字段；

左外连接（LEFT OUTER JOIN）： 返回左表所有行，即使右表没有匹配。

SELECT * FROM 表1
LEFT OUTER JOIN 表2 ON 表1.关联字段 = 表2.关联字段；

右外连接（RIGHT OUTER JOIN）： 返回右表所有行，即使左表没有匹配。

SELECT * FROM 表1
RIGHT OUTER JOIN 表2 ON 表1.关联字段 = 表2.关联字段；

全外连接（FULL OUTER JOIN）： 当没有匹配时，返回左表和右表的所有行。

SELECT * FROM 表1
FULL OUTER JOIN 表2 ON 表1.关联字段 = 表2.关联字段；

2.窗口函数（Window Functions）:

ROW_NUMBER(): 为结果集中的每一行分配一个唯一的数字。

SELECT *, ROW_NUMBER() OVER(ORDER BY 列名) as row_num
FROM 表名；

RANK() 和 DENSE_RANK(): 为结果集中的行分配一个排名，具有相同值的行将获得相同的排名。
NTILE(n): 将结果集分成n个大致相等的排名。
LEAD() 和 LAG(): 访问当前行之前的或之后的行的值。
FIRST_VALUE(), LAST_VALUE(), MAX(), MIN(), SUM(), AVG(): 在窗口函数中执行聚合计算。
3.复杂的子查询和组合条件:
使用多个子查询进行复杂查询。
使用 CASE 语句进行条件逻辑。
使用 UNION 和 UNION ALL 合并多个查询的结果集。
使用 EXCEPT 找出在一个查询结果中但不在另一个查询结果中的记录。
使用 INTERSECT 找出同时存在于两个查询结果中的记录。
4.自连接（Self Joins）:
表自己与自己进行连接，常用于处理具有层次或树状结构的数据。
5.递归查询（Recursive Queries）:
SQLite支持通过WITH RECURSIVE来执行递归查询，这通常用于处理具有递归关系的数据，如组织结构、路径查找等。
6.事务控制:
使用BEGIN, COMMIT, ROLLBACK, SAVEPOINT等语句来管理事务。
7.触发器（Triggers）:
定义在特定操作（插入、更新、删除）发生时自动执行的数据库操作。
8.索引（Indexes）:
创建和使用索引以加速查询。
9.视图（Views）:
创建视图以保存复杂的查询，便于重用和简化数据库操作。
10.优化和分析:
使用 EXPLAIN QUERY PLAN 来理解查询如何被执行，从而进行优化。

四、更加深入和专业的数据库查询技术

1.性能优化:
执行计划分析（EXPLAIN QUERY PLAN）来理解并优化查询。
索引优化，包括创建和使用临时索引来加速特定查询。
分析表的使用，如 ANALYZE 命令来更新统计信息以改善查询计划的性能。
2.数据完整性与约束:
使用各种约束（主键、外键、唯一、检查）来维护数据的完整性。
利用触发器来实施复杂的数据验证或在多个表之间同步数据。
3.存储过程和用户定义函数:
创建存储过程和用户定义函数（UDFs）来封装复杂的逻辑，并在多个地方重用。
4.分区和分片:
虽然SQLite不支持内建的表分区，但可以通过手动分片技术来实现一定程度的数据分割。
5.全文搜索:
使用虚拟表 fts（Full Text Search）模块进行全文搜索。
6.JSON和扩展的数据处理:
SQLite具有处理JSON数据的能力，可以使用特殊的JSON函数来查询和操作JSON字段。
7.高级查询改写:
通过组合不同的连接类型、子查询、集合操作等，实现复杂的数据集合操作。
8.数据迁移与备份策略:
设计和实施数据迁移方案以确保数据安全和一致性。
9.安全性措施:
保护数据库的访问控制，防止未授权的数据访问和注入攻击。
10.错误处理和事务管理:
使用保存点（savepoints）来管理复杂的事务，允许在出现问题时回滚到特定的状态。
11.国际化和本地化:
处理多语言数据，考虑字符集和排序规则。
12.监控和日志:
配置和使用日志来监控数据库活动和性能问题。