当前位置： 首页 > ds >正文

10.2sql

ds 2025/8/27 21:50:15

基本操作语法

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
%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
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         -- 设置列宽
http://www.xdnf.cn/news/9355.html

相关文章：

  • python-正则表达式
  • 与 PyCharm 官方沟通解决开发环境问题记录（进展：官方已推出2个新的修复版本）
  • C语言-文件
  • IEEE Journal on Selected Areas in Communications 2025年论文整理2（中英文摘要）
  • IPv4地址的主要配置项介绍
  • 14. LayUI与Bootstrap框架使用
  • JS手写代码篇---手写promise.all
  • Python条件语句完全指南：从if-else到模式匹配
  • 鸿蒙OSUniApp 实现带搜索功能的下拉菜单#三方框架 #Uniapp
  • 实验设计与分析（第6版，Montgomery)第3章单因子实验：方差分析3.11思考题3.1 R语言解题
  • PIO 也有并发喔，巧用SIDE-SET
  • 解决Qt 打包的软件缺少dll问题
  • 《计算机组成原理》第 4 章 - 存储器​
  • 零基础设计模式——结构型模式 - 装饰器模式
  • 国芯思辰| 同步降压转换器CN2020应用于智能电视，替换LMR33620
  • 在ubuntu手动分区时，730GB的总空间，建议划分多少给根目录
  • AI应用 Markdown 渲染对比与原生实现方案
  • 书籍品读：《逆水行舟话幸福》（陈州）
  • 【Python】2. 基础语法(2)
  • 【运维_日常报错解决方案_docker系列】一、docker系统不起来
  • 从自动化到智能化：制造业数字化转型的五个关键跃迁
  • Vue-03 （调试工具Vue Devtools ）
  • delphi xe 12.3下解决错误 ld.exe: unrecognized option ‘--no-rosegment‘
  • 试验台铁地板：技术创新与品质保障
  • 【观成科技】Ymir勒索软件组织窃密木马RustyStealer加密通信分析
  • STM32的HAL编码流程总结（上部）
  • 大模型——MCP 深度解析
  • vue2使用element中多选组件el-checkbox-group，数据与UI更新不同步
  • 【React】jsx 从声明式语法变成命令式语法
  • 5G NTN卫星通信发展现状(截止2025年3月)