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 , column2
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 INDEX 索引名 ON 表名 (列名1 , 列名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%H24 小时制小时 (00-23)14%M分钟 (00-59)30%S秒 (00-59)00%sUnix 时间戳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
BEGIN
INSERT 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);
性能优化
[*] 使用 WAL 模式
PRAGMA journal_mode = WAL;-- 提高并发写入性能
[*] 批量操作
将多个插入 / 更新操作放在一个事务中，镌汰磁盘 IO。
[*] 适当索引
为高频查询的字段创建索引，但制止过多索引影响写入性能。
注意事项
[*] 并发限定
支持多用户只读，但写入时会锁定整个数据库文件（WAL 模式可缓解）。
[*] 大文件性能
处理超大型数据库（>100GB）时性能大概下降，需谨慎设计。
[*] 安全考虑
制止直接从用户输入构建 SQL 语句，防止 SQL 注入（使用预编译语句）。
SQLite 以其简单、轻量和可靠性成为小型项目和嵌入式系统的理想选择。通过机动的数据范例系统、事务支持和丰富的 SQL 功能，开发者可以快速构建高效的数据库应用。虽然存在并发和大型数据处理的限定，但在其实用场景内，SQLite 是一个强大且易用的数据库办理方案。
sqlite官方下令行工具
[*] 访问 SQLite 官方下载页面：SQLite Download Page
[*] 在 Precompiled Binaries for Windows 部门，下载：
[*]sqlite-tools-win32-x86-*.zip（下令行工具）
[*] 解压文件
[*]将下载的 ZIP 文件解压到一个目录，例如 C:\sqlite
[*] 配置环境变量
[*]右键点击 “此电脑” → “属性” → “高级系统设置” → “环境变量”。
[*]在 系统变量 中找到 Path，点击 “编辑” → “新建”，添加 SQLite 解压目录的路径（例如 C:\sqlite）。
[*]确认所有窗口，完成环境变量配置
[*] 验证安装
[*]打开下令提示符（CMD），输入 sqlite3，假如看到 SQLite 版本信息，并进入sqlite下令行界面，则安装成功。
https://i-blog.csdnimg.cn/direct/7f169419d8b14ecc9ca9748ff1560a15.png
操作下令
-- 创建或打开数据库（命令行）
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 -- 设置列宽
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]