04 - SQL 基础

04 - SQL 基础：DDL、DML、DQL 与类型亲和性

4.1 SQL 语句分类

类别	全称	说明	关键字
DDL	Data Definition Language	定义/修改表结构	CREATE, ALTER, DROP
DML	Data Manipulation Language	增删改数据	INSERT, UPDATE, DELETE
DQL	Data Query Language	查询数据	SELECT
TCL	Transaction Control Language	事务控制	BEGIN, COMMIT, ROLLBACK

4.2 DDL — 数据定义语言

4.2.1 CREATE TABLE

-- 基本语法
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER DEFAULT 0,
    balance REAL DEFAULT 0.0,
    is_active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now', 'localtime')),
    updated_at TEXT DEFAULT (datetime('now', 'localtime'))
);

-- CREATE TABLE IF NOT EXISTS（幂等操作）
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK(price >= 0),
    stock INTEGER DEFAULT 0
);

-- 从查询结果创建表
CREATE TABLE active_users AS
SELECT * FROM users WHERE is_active = 1;

-- 仅复制结构（无数据）
CREATE TABLE users_backup AS SELECT * FROM users WHERE 0;

完整建表示例

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    order_no TEXT NOT NULL UNIQUE,
    total_amount REAL NOT NULL DEFAULT 0,
    status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
    note TEXT,
    created_at TEXT DEFAULT (datetime('now', 'localtime')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK(quantity > 0),
    unit_price REAL NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

约束详解

约束	说明	示例
`PRIMARY KEY`	主键	`id INTEGER PRIMARY KEY`
`AUTOINCREMENT`	自增（仅 INTEGER 主键）	`id INTEGER PRIMARY KEY AUTOINCREMENT`
`NOT NULL`	非空	`name TEXT NOT NULL`
`UNIQUE`	唯一	`email TEXT UNIQUE`
`CHECK`	检查约束	`age INTEGER CHECK(age >= 0)`
`DEFAULT`	默认值	`status TEXT DEFAULT 'active'`
`FOREIGN KEY`	外键（需 PRAGMA 开启）	`FOREIGN KEY (uid) REFERENCES users(id)`

⚠️ SQLite 默认不检查外键约束，需要手动开启：
PRAGMA foreign_keys = ON;

4.2.2 ALTER TABLE

SQLite 的 ALTER TABLE 功能有限：

-- 重命名表
ALTER TABLE users RENAME TO members;

-- 添加列（SQLite 3.35.0+）
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN avatar_url TEXT DEFAULT '/image/default.png';

-- 重命名列（SQLite 3.25.0+）
ALTER TABLE users RENAME COLUMN username TO name;

⚠️ SQLite 不支持：
删除列（需重建表）
修改列类型
添加/删除约束
重建表示例：
-- 删除列的方式
CREATE TABLE users_new AS SELECT id, name, email FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

4.2.3 DROP TABLE

-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;  -- 幂等操作

-- 删除视图
DROP VIEW IF EXISTS active_user_view;

4.2.4 CREATE INDEX

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 唯一索引
CREATE INDEX idx_users_username ON users(username);

-- 条件索引（部分索引）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = 1;

-- 删除索引
DROP INDEX IF EXISTS idx_users_email;

4.3 DML — 数据操作语言

4.3.1 INSERT

-- 基本插入
INSERT INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25);

-- 批量插入
INSERT INTO users (username, email, age) VALUES
    ('李四', '[email protected]', 30),
    ('王五', '[email protected]', 28),
    ('赵六', '[email protected]', 35),
    ('孙七', '[email protected]', 22);

-- INSERT OR IGNORE（忽略冲突）
INSERT OR IGNORE INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25);

-- INSERT OR REPLACE（冲突时替换整行）
INSERT OR REPLACE INTO users (id, username, email, age)
VALUES (1, '张三', '[email protected]', 26);

-- INSERT ... ON CONFLICT（SQLite 3.24.0+）
INSERT INTO users (username, email, age)
VALUES ('张三', '[email protected]', 25)
ON CONFLICT(username) DO UPDATE SET
    email = excluded.email,
    age = excluded.age;

INSERT INTO users (username, email, age)
VALUES ('新用户', '[email protected]', 20)
ON CONFLICT DO NOTHING;

-- 从查询结果插入
INSERT INTO active_users (id, username, email)
SELECT id, username, email FROM users WHERE is_active = 1;

4.3.2 UPDATE

-- 基本更新
UPDATE users SET age = 26 WHERE id = 1;

-- 多列更新
UPDATE users SET
    age = age + 1,
    updated_at = datetime('now', 'localtime')
WHERE is_active = 1;

-- 带条件的更新
UPDATE orders SET status = 'completed'
WHERE status = 'shipped' AND created_at < '2025-01-01';

-- 使用子查询更新
UPDATE users SET balance = (
    SELECT COALESCE(SUM(total_amount), 0)
    FROM orders
    WHERE orders.user_id = users.id AND orders.status = 'completed'
);

-- 限制更新行数（通过子查询）
UPDATE users SET is_active = 0
WHERE id IN (
    SELECT id FROM users ORDER BY id LIMIT 10
);

4.3.3 DELETE

-- 基本删除
DELETE FROM users WHERE id = 5;

-- 带条件删除
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2024-01-01';

-- 删除所有数据（比 DELETE 快）
DELETE FROM users;
-- 或
-- DELETE FROM users WHERE 1=1;

-- 使用子查询删除
DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

-- 限制删除行数（通过 rowid）
DELETE FROM users WHERE rowid IN (
    SELECT rowid FROM users ORDER BY rowid LIMIT 100
);

4.4 DQL — 数据查询语言

4.4.1 SELECT 基础

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 别名
SELECT id AS 用户ID, username AS 用户名 FROM users;

-- 去重
SELECT DISTINCT age FROM users;

-- 限制行数
SELECT * FROM users LIMIT 10;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 或使用 WHERE + 游标
SELECT * FROM users WHERE id > 100 LIMIT 10;

4.4.2 WHERE 条件

-- 比较运算符
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE age IN (18, 25, 30);

-- 字符串匹配
SELECT * FROM users WHERE username LIKE '张%';       -- 以张开头
SELECT * FROM users WHERE username LIKE '%三%';       -- 包含三
SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- 以 @gmail.com 结尾

-- SQLite 支持的 LIKE 通配符：
-- % 匹配任意字符序列
-- _ 匹配单个字符

-- NULL 检查
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND is_active = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT is_active = 1;
SELECT * FROM users WHERE age NOT IN (18, 25);

-- GLOB（区分大小写的 shell 通配符匹配）
SELECT * FROM users WHERE email GLOB '*@gmail.com';

4.4.3 ORDER BY 排序

-- 单列排序
SELECT * FROM users ORDER BY age ASC;       -- 升序（默认）
SELECT * FROM users ORDER BY age DESC;      -- 降序

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

-- NULL 排序行为
-- NULL 在 SQLite 中被视为比其他值更小
SELECT * FROM users ORDER BY phone;         -- NULL 排在最前面
SELECT * FROM users ORDER BY phone DESC;    -- NULL 排在最后面

-- 使用表达式排序
SELECT * FROM users ORDER BY length(username);

-- 使用 CASE 排序
SELECT * FROM users ORDER BY
    CASE status
        WHEN 'active' THEN 1
        WHEN 'inactive' THEN 2
        ELSE 3
    END;

4.4.4 GROUP BY 与聚合函数

-- 常用聚合函数
SELECT
    COUNT(*) AS total,
    COUNT(DISTINCT age) AS distinct_ages,
    AVG(age) AS avg_age,
    SUM(balance) AS total_balance,
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM users;

-- GROUP BY
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age;

-- 带 HAVING 过滤
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age
HAVING count > 1
ORDER BY count DESC;

-- 多列分组
SELECT age, is_active, COUNT(*) AS count
FROM users
GROUP BY age, is_active;

4.4.5 JOIN 关联查询

-- INNER JOIN（内连接，只返回匹配的行）
SELECT u.username, o.order_no, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左连接，返回左表所有行）
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 自连接
SELECT a.username AS user1, b.username AS user2
FROM users a
INNER JOIN users b ON a.age = b.age AND a.id < b.id;

-- 多表连接
SELECT u.username, o.order_no, p.name AS product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

4.5 类型亲和性（Type Affinity）

SQLite 使用动态类型系统，但支持通过"类型亲和性"来影响存储行为。

4.5.1 五种亲和性

亲和性	匹配规则	示例类型
TEXT	类型名包含 “TEXT”	TEXT, CLOB, CHARACTER
NUMERIC	不匹配其他规则	NUMERIC, DECIMAL, BOOLEAN
INTEGER	包含 “INT”	INTEGER, INT, BIGINT, SMALLINT
REAL	包含 “REAL”, “FLOA”, “DOUB”	REAL, FLOAT, DOUBLE
BLOB	不指定类型或不匹配以上	(无类型)

-- 创建表演示类型亲和性
CREATE TABLE type_demo (
    a TEXT,          -- TEXT 亲和性
    b NUMERIC,       -- NUMERIC 亲和性
    c INTEGER,       -- INTEGER 亲和性
    d REAL,          -- REAL 亲和性
    e                -- BLOB 亲和性
);

-- 插入不同类型的数据
INSERT INTO type_demo VALUES (42, 42, 42, 42, 42);
INSERT INTO type_demo VALUES ('3.14', '3.14', '3.14', '3.14', '3.14');
INSERT INTO type_demo VALUES ('hello', 'hello', 'hello', 'hello', 'hello');

-- 查询实际存储的类型
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e)
FROM type_demo;
-- text|integer|integer|real|integer
-- text|real|integer|real|text
-- text|text|text|text|text

4.5.2 类型亲和性与 INSERT OR REPLACE 的陷阱

CREATE TABLE demo (
    id INTEGER PRIMARY KEY,
    val TEXT
);
INSERT INTO demo VALUES (1, '123');

-- typeof(val) 返回 'text'
-- 但如果使用 INSERT OR REPLACE:
INSERT OR REPLACE INTO demo VALUES (1, 456);
-- typeof(val) 返回 'integer'！

4.6 表达式与运算符

-- 算术运算符
SELECT 10 + 3;    -- 13
SELECT 10 - 3;    -- 7
SELECT 10 * 3;    -- 30
SELECT 10 / 3;    -- 3（整数除法）
SELECT 10.0 / 3;  -- 3.33333333333333
SELECT 10 % 3;    -- 1（取模）

-- 字符串运算
SELECT 'Hello' || ' ' || 'World';  -- Hello World
SELECT length('Hello');              -- 5
SELECT upper('hello');               -- HELLO
SELECT lower('HELLO');               -- hello
SELECT substr('Hello World', 1, 5); -- Hello
SELECT replace('Hello', 'l', 'L');  -- HeLLo
SELECT trim('  Hello  ');            -- Hello
SELECT ltrim('  Hello');             -- Hello
SELECT rtrim('Hello  ');             -- Hello
SELECT instr('Hello World', 'World');-- 7（位置）
SELECT printf('%s is %d years old', '张三', 25); -- 张三 is 25 years old

-- 条件表达式
SELECT
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

-- COALESCE（返回第一个非 NULL 值）
SELECT COALESCE(phone, email, '无联系方式') AS contact FROM users;

-- IFNULL
SELECT IFNULL(phone, '未填写') AS phone FROM users;

-- NULLIF（相等时返回 NULL）
SELECT NULLIF(1, 1);  -- NULL
SELECT NULLIF(1, 2);  -- 1

4.7 常用内置函数

函数	说明	示例
`abs(x)`	绝对值	`abs(-5)` → 5
`length(s)`	字符串长度	`length('你好')` → 2
`upper(s)`	转大写	`upper('abc')` → ABC
`lower(s)`	转小写	`lower('ABC')` → abc
`trim(s)`	去除首尾空格	`trim(' hi ')` → hi
`substr(s,i,n)`	子字符串	`substr('abc',1,2)` → ab
`replace(s,o,n)`	字符串替换	`replace('a-b','-','+')` → a+b
`hex(s)`	十六进制	`hex('ABC')` → 414243
`randomblob(n)`	随机字节	`randomblob(16)`
`typeof(x)`	值的类型	`typeof(42)` → integer
`ifnull(x,y)`	NULL 替换	`ifnull(NULL,0)` → 0
`coalesce(x,...)`	第一个非 NULL	`coalesce(NULL,2,3)` → 2
`quote(x)`	SQL 转义	`quote("it's")` → ‘it’’s’
`zeroblob(n)`	n 个零字节	`zeroblob(4)`
`total(x)`	聚合总和（含 NULL=0）	`total(col)`
`group_concat(x,s)`	分组连接	`group_concat(name,',')`
`max(x,...)`	最大值	`max(1,2,3)` → 3
`min(x,...)`	最小值	`min(1,2,3)` → 1

⚠️ 注意事项

SQLite 的 ALTER TABLE 非常有限——不支持删列、改类型，需要重建表
外键默认不检查——必须 PRAGMA foreign_keys = ON;（每次连接都要设置）
AUTOINCREMENT 有额外开销——如果不需要严格递增，仅用 INTEGER PRIMARY KEY 即可
LIKE 默认不区分大小写——对 ASCII 字母不区分大小写，但 GLOB 区分
整数除法会截断——10/3 结果为 3，不是 3.333
INSERT OR REPLACE 会删除再插入——触发器可能会意外触发

💡 技巧

批量插入时使用事务——性能可提升 100 倍以上
使用 INSERT ... ON CONFLICT 比 INSERT OR REPLACE 更安全
COALESCE 比 IFNULL 更灵活——支持多个备选值
typeof() 函数在调试类型问题时非常有用
使用 printf() 格式化输出——方便调试

📌 业务场景

场景一：用户注册系统

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
);

-- 注册（防止重复）
INSERT INTO users (username, email, password_hash)
VALUES ('zhangsan', '[email protected]', 'hashed_password_here');

场景二：订单系统

-- 创建订单
INSERT INTO orders (user_id, order_no, total_amount)
VALUES (1, 'ORD20250101001', 299.99);

-- 添加商品
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (last_insert_rowid(), 1, 2, 149.99);

-- 更新状态
UPDATE orders SET status = 'paid' WHERE order_no = 'ORD20250101001';

🔗 扩展阅读

📖 下一章：05 - SQL 进阶 —— CTE、窗口函数、JSON、FTS5

强曰为道

04 - SQL 基础

04 - SQL 基础：DDL、DML、DQL 与类型亲和性

4.1 SQL 语句分类

4.2 DDL — 数据定义语言

4.2.1 CREATE TABLE

完整建表示例

约束详解

4.2.2 ALTER TABLE

4.2.3 DROP TABLE

4.2.4 CREATE INDEX

4.3 DML — 数据操作语言

4.3.1 INSERT

4.3.2 UPDATE

4.3.3 DELETE

4.4 DQL — 数据查询语言

4.4.1 SELECT 基础

4.4.2 WHERE 条件

4.4.3 ORDER BY 排序

4.4.4 GROUP BY 与聚合函数

4.4.5 JOIN 关联查询

4.5 类型亲和性（Type Affinity）

4.5.1 五种亲和性

4.5.2 类型亲和性与 INSERT OR REPLACE 的陷阱

4.6 表达式与运算符

4.7 常用内置函数

⚠️ 注意事项

💡 技巧

📌 业务场景

场景一：用户注册系统

场景二：订单系统

🔗 扩展阅读