数据库初级 2025-06-21 预计阅读: 54 分钟

SQLite 基本语句

标签: #SQLite

SQLite 基本语句参考

数据库操作

连接数据库

-- 连接到数据库文件（如果不存在会自动创建）
sqlite3 database_name.db

查看数据库信息

-- 查看所有表
.tables

-- 查看数据库结构
.schema

-- 查看特定表的结构
.schema table_name

表操作

创建表

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype NOT NULL,
    column3 datatype DEFAULT value,
    column4 datatype
);

-- 示例
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

修改表结构

-- 添加列
ALTER TABLE table_name ADD COLUMN column_name datatype;

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 重命名列（SQLite 3.25.0+）
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

删除表

DROP TABLE table_name;

-- 如果表存在则删除
DROP TABLE IF EXISTS table_name;

查看表信息

-- 查看表结构
PRAGMA table_info(table_name);

-- 查看表的索引
PRAGMA index_list(table_name);

数据插入

基本插入

-- 插入所有列的数据
INSERT INTO table_name VALUES (value1, value2, value3, ...);

-- 插入指定列的数据
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);

-- 示例
INSERT INTO users (username, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

批量插入

-- 插入多行数据
INSERT INTO table_name (column1, column2) VALUES 
    (value1, value2),
    (value3, value4),
    (value5, value6);

条件插入

-- 如果不存在则插入
INSERT OR IGNORE INTO table_name (column1, column2) VALUES (value1, value2);

-- 如果存在则替换
INSERT OR REPLACE INTO table_name (column1, column2) VALUES (value1, value2);

数据查询

基本查询

-- 查询所有数据
SELECT * FROM table_name;

-- 查询指定列
SELECT column1, column2 FROM table_name;

-- 条件查询
SELECT * FROM table_name WHERE condition;

条件操作符

-- 比较操作符
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE username = '张三';
SELECT * FROM users WHERE age BETWEEN 18 AND 65;

-- 逻辑操作符
SELECT * FROM users WHERE age > 18 AND email IS NOT NULL;
SELECT * FROM users WHERE username = '张三' OR username = '李四';

-- 模糊查询
SELECT * FROM users WHERE username LIKE '张%';  -- 以'张'开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以'@gmail.com'结尾
SELECT * FROM users WHERE username LIKE '%三%';  -- 包含'三'

-- IN 操作符
SELECT * FROM users WHERE age IN (20, 25, 30);

排序和限制

-- 排序
SELECT * FROM users ORDER BY age ASC;  -- 升序
SELECT * FROM users ORDER BY age DESC; -- 降序
SELECT * FROM users ORDER BY age, username; -- 多列排序

-- 限制结果数量
SELECT * FROM users LIMIT 10;  -- 前10条
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 跳过前20条，取10条

聚合函数

-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;  -- 统计非NULL的email数量

-- 求和、平均值、最大值、最小值
SELECT SUM(age) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;

-- 分组
SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

连接查询

-- 内连接
SELECT u.username, p.title 
FROM users u 
INNER JOIN posts p ON u.id = p.user_id;

-- 左连接
SELECT u.username, p.title 
FROM users u 
LEFT JOIN posts p ON u.id = p.user_id;

-- 右连接（SQLite不直接支持，可用LEFT JOIN实现）
SELECT u.username, p.title 
FROM posts p 
LEFT JOIN users u ON u.id = p.user_id;

数据更新

基本更新

-- 更新所有行（危险操作）
UPDATE table_name SET column1 = value1, column2 = value2;

-- 条件更新
UPDATE table_name SET column1 = value1 WHERE condition;

-- 示例
UPDATE users SET email = 'newemail@example.com' WHERE username = '张三';
UPDATE users SET age = age + 1 WHERE age < 30;

复杂更新

-- 基于子查询更新
UPDATE users SET age = (
    SELECT AVG(age) FROM users WHERE age > 0
) WHERE age IS NULL;

数据删除

基本删除

-- 删除所有数据（危险操作）
DELETE FROM table_name;

-- 条件删除
DELETE FROM table_name WHERE condition;

-- 示例
DELETE FROM users WHERE age < 18;
DELETE FROM users WHERE email IS NULL;

索引操作

创建索引

-- 创建普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

-- 创建复合索引
CREATE INDEX index_name ON table_name (column1, column2);

-- 示例
CREATE INDEX idx_username ON users (username);
CREATE INDEX idx_age_email ON users (age, email);

删除索引

DROP INDEX index_name;

查看索引

-- 查看表的所有索引
PRAGMA index_list(table_name);

-- 查看索引详细信息
PRAGMA index_info(index_name);

约束

主键约束

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT
);

-- 复合主键
CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id)
);

外键约束

-- 启用外键约束
PRAGMA foreign_keys = ON;

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

检查约束

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT,
    age INTEGER CHECK (age >= 0 AND age <= 150),
    email TEXT CHECK (email LIKE '%@%')
);

唯一约束

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT,
    UNIQUE (username, email)
);

事务

基本事务

-- 开始事务
BEGIN TRANSACTION;
-- 或简写为
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务示例

BEGIN TRANSACTION;

INSERT INTO users (username, email) VALUES ('张三', 'zhangsan@example.com');
INSERT INTO posts (title, user_id) VALUES ('我的第一篇文章', last_insert_rowid());

-- 如果一切正常
COMMIT;

-- 如果出现错误
-- ROLLBACK;

保存点

BEGIN TRANSACTION;

INSERT INTO users (username) VALUES ('用户1');

SAVEPOINT sp1;

INSERT INTO users (username) VALUES ('用户2');

-- 回滚到保存点
ROLLBACK TO sp1;

COMMIT;

实用命令

SQLite 命令行工具命令

-- 显示帮助
.help

-- 退出
.quit
-- 或
.exit

-- 显示当前设置
.show

-- 设置输出格式
.mode csv        -- CSV格式
.mode column     -- 列格式
.mode html       -- HTML格式
.mode insert     -- INSERT语句格式
.mode json       -- JSON格式
.mode table      -- 表格格式

-- 设置列宽
.width 10 20 15

-- 显示列标题
.headers on
.headers off

-- 导出数据
.output filename.txt
SELECT * FROM users;
.output stdout

-- 导入数据
.import filename.csv table_name

-- 备份数据库
.backup backup_name.db

-- 恢复数据库
.restore backup_name.db

-- 执行SQL文件
.read script.sql

-- 显示执行时间
.timer on
.timer off

数据库优化

-- 分析数据库
ANALYZE;

-- 重建数据库（压缩空间）
VACUUM;

-- 检查数据库完整性
PRAGMA integrity_check;

-- 查看数据库统计信息
PRAGMA database_list;
PRAGMA table_list;

常用PRAGMA命令

-- 查看SQLite版本
PRAGMA version;

-- 查看数据库大小
PRAGMA page_count;
PRAGMA page_size;

-- 设置同步模式
PRAGMA synchronous = FULL;  -- 完全同步
PRAGMA synchronous = NORMAL; -- 正常同步
PRAGMA synchronous = OFF;    -- 关闭同步

-- 设置日志模式
PRAGMA journal_mode = WAL;   -- Write-Ahead Logging
PRAGMA journal_mode = DELETE; -- 默认模式

-- 启用/禁用外键约束
PRAGMA foreign_keys = ON;
PRAGMA foreign_keys = OFF;

-- 设置缓存大小
PRAGMA cache_size = 10000;

数据类型

SQLite支持以下数据类型：

NULL: 空值
INTEGER: 整数
REAL: 浮点数
TEXT: 文本字符串
BLOB: 二进制数据

类型亲和性

-- 整数类型
INTEGER, INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT

-- 文本类型
TEXT, CHARACTER(20), VARCHAR(255), CLOB

-- 实数类型
REAL, DOUBLE, FLOAT

-- 数值类型
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME

-- 二进制类型
BLOB

常用函数

字符串函数

-- 字符串长度
SELECT LENGTH('Hello World'); -- 11

-- 字符串连接
SELECT 'Hello' || ' ' || 'World'; -- Hello World

-- 大小写转换
SELECT UPPER('hello'); -- HELLO
SELECT LOWER('WORLD'); -- world

-- 字符串截取
SELECT SUBSTR('Hello World', 1, 5); -- Hello

-- 字符串替换
SELECT REPLACE('Hello World', 'World', 'SQLite'); -- Hello SQLite

-- 去除空格
SELECT TRIM('  Hello World  '); -- Hello World

数学函数

-- 绝对值
SELECT ABS(-5); -- 5

-- 四舍五入
SELECT ROUND(3.14159, 2); -- 3.14

-- 随机数
SELECT RANDOM(); -- 随机整数
SELECT ABS(RANDOM() % 100); -- 0-99的随机数

日期时间函数

-- 当前时间
SELECT DATETIME('now'); -- 当前日期时间
SELECT DATE('now'); -- 当前日期
SELECT TIME('now'); -- 当前时间

-- 日期计算
SELECT DATE('now', '+1 day'); -- 明天
SELECT DATE('now', '-1 month'); -- 上个月
SELECT DATE('now', '+1 year'); -- 明年

-- 格式化日期
SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now'); -- 2024-01-01 12:00:00
SELECT STRFTIME('%Y年%m月%d日', 'now'); -- 2024年01月01日

示例：完整的用户管理系统

-- 1. 创建用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE,
    password TEXT NOT NULL,
    role TEXT DEFAULT 'user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. 创建用户资料表
CREATE TABLE user_profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    first_name TEXT,
    last_name TEXT,
    phone TEXT,
    address TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 3. 插入示例数据
INSERT INTO users (username, email, password, role) VALUES 
    ('admin', 'admin@example.com', 'admin123', 'admin'),
    ('zhangsan', 'zhangsan@example.com', '123456', 'user'),
    ('lisi', 'lisi@example.com', '123456', 'user');

-- 4. 插入用户资料
INSERT INTO user_profiles (user_id, first_name, last_name, phone) VALUES 
    (2, '三', '张', '13800138000'),
    (3, '四', '李', '13900139000');

-- 5. 查询用户及其资料
SELECT 
    u.id,
    u.username,
    u.email,
    u.role,
    p.first_name,
    p.last_name,
    p.phone
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
ORDER BY u.created_at;

-- 6. 更新用户信息
UPDATE users 
SET email = 'new_email@example.com', 
    updated_at = CURRENT_TIMESTAMP 
WHERE username = 'zhangsan';

-- 7. 删除用户（会级联删除资料）
DELETE FROM users WHERE username = 'lisi';

-- 8. 统计信息
SELECT 
    role,
    COUNT(*) as user_count
FROM users 
GROUP BY role;

性能优化建议

使用索引: 为经常查询的列创建索引
避免SELECT *: 只查询需要的列
使用LIMIT: 限制查询结果数量
使用事务: 批量操作时使用事务
定期VACUUM: 清理数据库碎片
使用EXPLAIN: 分析查询执行计划

-- 分析查询执行计划
EXPLAIN QUERY PLAN SELECT * FROM users WHERE username = 'zhangsan';

本文档涵盖了SQLite的基本语句和常用操作

阅读进度

滚动页面查看进度

快速操作

教程统计

浏览量

10721

字符数

反馈

详细反馈

SQLite 基本语句

目录

SQLite 基本语句参考

目录

数据库操作

连接数据库

查看数据库信息

表操作

创建表

修改表结构

删除表

查看表信息

数据插入

基本插入

批量插入

条件插入

数据查询

基本查询

条件操作符

排序和限制

聚合函数

连接查询

数据更新

基本更新

复杂更新

数据删除

基本删除

索引操作

创建索引

删除索引

查看索引

约束

主键约束

外键约束

检查约束

唯一约束

事务

基本事务

事务示例

保存点

实用命令

SQLite 命令行工具命令

数据库优化

常用PRAGMA命令

数据类型

类型亲和性

常用函数

字符串函数

数学函数

日期时间函数

示例：完整的用户管理系统

性能优化建议

阅读进度

快速操作

教程统计

反馈