SQLite常用的全部SQL语句及与MySQL/MariaDB的的差异

其它 wes 26 days ago (2026-03-17) 27 views

        SQLite 常用全套 SQL 语句 + 与 MySQL/MariaDB 核心差异，整理成最实用、能直接背、能直接用的版本，覆盖日常开发 99% 场景，同时把和 MySQL 的区别讲得清清楚楚，作为笔记供参考。

一、SQLite 最常用全套 SQL 语句

1. 库操作

sql 
-- 打开/创建数据库（文件形式）
.open test.db
-- 备份数据库
.backup test_backup.db
-- 查看当前打开的库
.database

2. 表操作（最核心）

创建表

sql 
CREATE TABLE user (
 id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增主键
 name TEXT NOT NULL,
 age INTEGER DEFAULT 0,
 email TEXT UNIQUE,
 create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

查看所有表

sql 
.tables -- 命令行
SELECT name FROM sqlite_master WHERE type='table';

查看表结构

sql 
PRAGMA table_info(user);

修改表（SQLite 限制较多，记这个就够）

sql 
-- 重命名表
ALTER TABLE user RENAME TO member;
-- 添加字段
ALTER TABLE user ADD COLUMN phone TEXT;
-- 重命名字段
ALTER TABLE user RENAME COLUMN age TO user_age;

删除表

sql 
DROP TABLE user;

3. 数据增删改查

插入数据

sql 
INSERT INTO user (name, age) VALUES ('张三', 20);
INSERT INTO user (name, age) VALUES ('李四', 25), ('王五', 30); -- 批量插入

查询数据

sql 
SELECT * FROM user;
SELECT name, age FROM user WHERE age > 20;
SELECT * FROM user ORDER BY age DESC LIMIT 10;
SELECT * FROM user WHERE name LIKE '%张%';

更新数据

sql 
UPDATE user SET age=26 WHERE name='李四';

删除数据

sql 
DELETE FROM user WHERE age < 18;

4. 高级查询

分组统计

sql 
SELECT age, COUNT(*) FROM user GROUP BY age;

去重

sql 
SELECT DISTINCT name FROM user;

多表连接

sql 
SELECT * FROM user
JOIN order ON user.id = order.user_id;

分页

sql 
SELECT * FROM user LIMIT 10 OFFSET 20;

5. 索引（加速查询）

sql 
-- 创建索引
CREATE INDEX idx_user_name ON user(name);
-- 删除索引
DROP INDEX idx_user_name;

6. 视图

sql 
CREATE VIEW v_user AS SELECT name, age FROM user;

7. 事务（保证数据安全）

sql 
BEGIN;
UPDATE user SET age=30 WHERE id=1;
COMMIT; -- 提交
-- ROLLBACK; -- 回滚

8. 清空表数据

sql 
DELETE FROM user; -- 保留结构
VACUUM; -- 优化空间

9. 常用函数

sql 
SELECT COUNT(*) FROM user; -- 总数
SELECT MAX(age) FROM user; -- 最大
SELECT MIN(age) FROM user; -- 最小
SELECT AVG(age) FROM user; -- 平均
SELECT datetime('now'); -- 当前时间

二、SQLite 与 MySQL/MariaDB 核心差异

1. 数据库本质区别

  • SQLite：文件型数据库，一个文件 = 一个库，无服务、无端口、无账号密码。
  • MySQL：服务型数据库，需要启动服务，有账号密码、端口、权限体系。

2. 数据类型差异（最大坑点）

SQLite 只有 5 种基础类型：

  • NULL
  • INTEGER（整数）
  • REAL（浮点数）
  • TEXT（字符串）
  • BLOB（二进制）

MySQL 类型非常多：

INT、VARCHAR、DATETIME、CHAR、TEXT、TINYINT、BIGINT、JSON 等
重点： SQLite 不强制校验类型，你往 INTEGER 里存字符串也能存进去！ MySQL 会严格校验，类型不对直接报错。

3. 自增主键写法不同

SQLite

sql 
id INTEGER PRIMARY KEY AUTOINCREMENT

MySQL

sql 
id INT PRIMARY KEY AUTO_INCREMENT

4. 字符串引号不同

  • SQLite：推荐单引号 ‘name’
  • MySQL：单双引号都支持

5. 分页语法一样

sql 
LIMIT 10 OFFSET 20
两者完全相同。

6. 表修改功能差异（非常大）

MySQL 可以随便改表：

  • 修改字段类型
  • 修改字段长度
  • 删除字段
  • 修改约束

SQLite 限制极多：

  • 不能直接删除字段
  • 不能直接修改字段类型
  • 只能：添加字段、重命名表、重命名字段
要改结构只能重建表。

7. 布尔值

  • SQLite：用 0 / 1
  • MySQL：用 TRUE / FALSE

8. 时间日期

  • SQLite：没有 DATETIME 类型，用 TEXT 或 REAL 存储
  • MySQL：专用 DATETIME、TIMESTAMP

9. 权限与账号

  • SQLite：无用户、无密码、无权限，谁拿到文件谁就能操作
  • MySQL：完整权限系统，账号密码管理

10. 性能与场景

  • SQLite：轻量、嵌入式、单文件、低并发 适合：APP 本地库、小程序、小工具、内网小系统
  • MySQL：高并发、大数据、多用户、分布式 适合：网站后台、大型系统、生产环境

三、最简单的差异（小结）

  1. SQLite = 文件数据库，MySQL = 服务数据库3y4.net
  2. SQLite 类型少、不严格；MySQL 类型多、严格
  3. SQLite 不能随便改字段；MySQL 可以随便改
  4. SQLite 无账号无权限；MySQL 有账号有权限
  5. 增删改查语句 90% 通用
  6. 自增、时间、布尔写法略有不同

↑