10.2sql

基本操作语法

1. 查看信息

SELECT SQLITE_VERSION();  -- 返回当前 SQLite 引擎的版本号-- 查看表结构 PRAGMA 命令
PRAGMA table_info(表名);-- 示例：查看 users 表结构
PRAGMA table_info(users);-- 查看创建表的 SQL 语句
SELECT sql FROM sqlite_schema WHERE name = '表名';--创建会话级临时表，会话结束后自动删除
CREATE TEMPORARY TABLE temp_users AS
SELECT * FROM users WHERE age > 25;
--在执行操作前验证表是否存在
SELECT COUNT(*) FROM sqlite_schema WHERE type = 'table' AND name = 'users';

2. 表操作

-- 创建表
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,age INTEGER CHECK (age >= 0),email TEXT UNIQUE
);-- 插入数据
INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@example.com');-- 查询数据
SELECT * FROM users WHERE age > 25 ORDER BY name LIMIT 10;-- 更新数据
UPDATE users SET age = 31 WHERE name = 'Alice';-- 删除数据
DELETE FROM users WHERE age < 18;-- 删除表
DROP TABLE IF EXISTS users;

3.基本分页语法

SELECT * FROM table_name
ORDER BY column1 [ASC/DESC], column2 [ASC/DESC]
LIMIT page_size OFFSET offset_value;

• LIMIT：指定每页返回的记录数（即 page_size）。
• OFFSET：指定跳过的记录数，计算公式为 (页码-1) * page_size。
• ORDER BY：必须使用，否则分页结果可能不稳定（尤其在数据更新频繁时）。

示例：分页查询用户表

-- 第1页（每页10条）
SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 0;  -- OFFSET 0 可省略，简写为 LIMIT 10-- 第2页
SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 10;-- 第3页
SELECT * FROM users
ORDER BY id ASC
LIMIT 10 OFFSET 20;

4. 索引

- 语法格式  
CREATE [UNIQUE] INDEX 索引名 ON 表名 (列名1 [ASC/DESC], 列名2, ...);  -- 示例：为users表的name列创建普通索引  
CREATE INDEX idx_users_name ON users (name);  -- 示例：为age和email列创建组合索引  
CREATE INDEX idx_users_age_email ON users (age, email);  -- 示例：创建唯一索引（确保列值唯一）  
CREATE UNIQUE INDEX idx_users_email ON users (email);  

• 普通索引（Normal Index）：最常用的索引，加速查询但允许重复值。
• 唯一索引（Unique Index）：确保列值唯一，同时加速查询。
• 组合索引（Composite Index）：基于多个列创建的索引，适用于多条件查询。

实用 SQL 函数

1. 字符串函数

SELECT UPPER(name) FROM users;        -- 转为大写
SELECT LENGTH(email) FROM users;      -- 获取字符串长度
SELECT SUBSTR(name, 1, 3) FROM users; -- 截取子串

2. 数值函数

SELECT ABS(-10);          -- 绝对值
SELECT ROUND(3.1415, 2);  -- 四舍五入
SELECT RANDOM();         -- 随机数

3. 聚合函数

SELECT COUNT(*) FROM users;        -- 计数
SELECT AVG(age) FROM users;        -- 平均值
SELECT MAX(age) FROM users;        -- 最大值

 日期和时间函数

SELECT DATE('now');                -- 当前日期
SELECT TIME('now', 'localtime');   -- 当前时间（本地）
SELECT DATETIME('now', '+1 day');  -- 明天的日期

一、DATE () 函数

功能

返回格式化的 日期字符串（YYYY-MM-DD 格式）。

语法

DATE(timestamp [, modifier, ...])

• timestamp：日期时间值（如 'now'、'2023-05-27'）。
• modifier：可选修饰词（如 '+1 day'、'-1 week'），用于时间偏移计算。

示例

-- 当前日期
SELECT DATE('now');  -- 输出：2023-05-27-- 明天的日期
SELECT DATE('now', '+1 day');  -- 输出：2023-05-28-- 上个月的第一天
SELECT DATE('now', '-1 month', 'start of month');  -- 输出：2023-04-01

常用修饰词

修饰词	作用
'+N days'	增加 N 天
'-N weeks'	减少 N 周
'+N months'	增加 N 月
'+N years'	增加 N 年
'start of month'	当月第一天
'start of year'	当年第一天
'weekday N'	下一个星期 N（0 = 周日）

二、DATETIME () 函数

功能

返回格式化的 日期时间字符串（YYYY-MM-DD HH:MM:SS 格式）。

语法

DATETIME(timestamp [, modifier, ...])

参数与 DATE() 相同，但输出包含时间部分。

示例

-- 当前日期时间（UTC）
SELECT DATETIME('now');  -- 输出：2023-05-27 14:30:00-- 当前本地时间
SELECT DATETIME('now', 'localtime');  -- 输出：2023-05-27 06:30:00（假设 UTC-8 时区）-- 3小时后的时间
SELECT DATETIME('now', '+3 hours');  -- 输出：2023-05-27 17:30:00

三、STRFTIME () 函数

功能

返回 自定义格式 的日期时间字符串，支持更多格式化选项。

语法

STRFTIME(format, timestamp [, modifier, ...])

• format：格式化字符串（如 '%Y-%m-%d %H:%M'）。
• timestamp 和 modifier 与前两个函数相同。

常用格式说明符

符号	含义	示例
%Y	四位数年份	2023
%m	两位数月份 (01-12)	05
%d	两位数日期 (01-31)	27
%H	24 小时制小时 (00-23)	14
%M	分钟 (00-59)	30
%S	秒 (00-59)	00
%s	Unix 时间戳	1685178600
%w	星期 (0-6, 0 = 周日)	6
%W	年份中的第几周	21
%j	年份中的第几天	147

示例

-- 格式化为 MM/DD/YYYY
SELECT STRFTIME('%m/%d/%Y', 'now');  -- 输出：05/27/2023-- 获取当前月份和星期几
SELECT STRFTIME('%m', 'now');  -- 输出：05
SELECT STRFTIME('%w', 'now');  -- 输出：6（周六）-- 计算年龄（假设 birth_date 为 '1990-01-15'）
SELECT STRFTIME('%Y', 'now') - STRFTIME('%Y', birth_date) - (STRFTIME('%m-%d', 'now') < STRFTIME('%m-%d', birth_date)) 
FROM users;  -- 输出：33（如果生日已过）或 32（如果生日未过）

四、对比与最佳实践

函数	输出格式	适用场景
DATE()	YYYY-MM-DD	仅需要日期部分
DATETIME()	YYYY-MM-DD HH:MM:SS	需要完整日期时间
STRFTIME()	自定义格式	复杂格式需求（如 MM/DD/YYYY）

性能建议：

• 避免在 WHERE 子句中对日期字段使用函数（如 DATE(column) > '2023-01-01'），会导致索引失效。
• 优先将日期存储为 YYYY-MM-DD 格式，以便直接比较大小。

高级特性

1. 事务处理

BEGIN TRANSACTION;  -- 开始事务
INSERT INTO users VALUES (...);
UPDATE accounts SET balance = balance - 100;
COMMIT;  -- 提交事务（或 ROLLBACK 回滚）

2. 触发器（Trigger）

-- 在删除用户前记录日志
CREATE TRIGGER before_delete_user
BEFORE DELETE ON users
BEGININSERT INTO user_logs (user_id, action, timestamp) VALUES (OLD.id, 'DELETE', CURRENT_TIMESTAMP);
END;

3. 视图（View）

-- 创建只读视图
CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = 1;

4. 索引优化

-- 为经常查询的列创建索引
CREATE INDEX idx_email ON users (email);

性能优化

1. 使用 WAL 模式

   PRAGMA journal_mode = WAL;  -- 提高并发写入性能
   

2. 批量操作
   将多个插入 / 更新操作放在一个事务中，减少磁盘 IO。

3. 适当索引
   为高频查询的字段创建索引，但避免过多索引影响写入性能。

注意事项

1. 并发限制
   支持多用户只读，但写入时会锁定整个数据库文件（WAL 模式可缓解）。

2. 大文件性能
   处理超大型数据库（>100GB）时性能可能下降，需谨慎设计。

3. 安全考虑
   避免直接从用户输入构建 SQL 语句，防止 SQL 注入（使用预编译语句）。

SQLite 以其简单、轻量和可靠性成为小型项目和嵌入式系统的理想选择。通过灵活的数据类型系统、事务支持和丰富的 SQL 功能，开发者可以快速构建高效的数据库应用。虽然存在并发和大型数据处理的限制，但在其适用场景内，SQLite 是一个强大且易用的数据库解决方案。

sqlite官方命令行工具

1. 访问 SQLite 官方下载页面：SQLite Download Page

2. 在 Precompiled Binaries for Windows 部分，下载：

   • sqlite-tools-win32-x86-*.zip（命令行工具）

3. 解压文件

   • 将下载的 ZIP 文件解压到一个目录，例如 C:\sqlite

4. 配置环境变量

   • 右键点击 “此电脑” → “属性” → “高级系统设置” → “环境变量”。
   • 在 系统变量 中找到 Path，点击 “编辑” → “新建”，添加 SQLite 解压目录的路径（例如 C:\sqlite）。
   • 确认所有窗口，完成环境变量配置

5. 验证安装

   • 打开命令提示符（CMD），输入 sqlite3，如果看到 SQLite 版本信息，并进入sqlite命令行界面，则安装成功。

操作命令 

-- 创建或打开数据库（命令行）
sqlite3 mydatabase.db-- 查看所有表
.tables-- 查看表结构
.schema table_name.open test.db        -- 打开或创建数据库
.databases           -- 显示当前连接的数据库
.backup 'backup.db'  -- 备份数据库.pragma table_info(table_name) -- 显示表的列信息.import data.csv users -- 从 CSV 导入数据
.output backup.sql     -- 将输出重定向到文件
.dump                 -- 导出整个数据库为 SQL 脚本
.output stdout        -- 恢复标准输出.help                -- 显示帮助信息
.quit                -- 退出 SQLite
.timeout 5000        -- 设置操作超时时间（毫秒）.header ON/OFF       -- 显示/隐藏列名
.mode column         -- 列格式输出
.mode csv            -- CSV 格式输出
.width 10 20         -- 设置列宽