数据库增删改查（CRUD）一文入门到精通

一、引言：数据库核心操作

CRUD是数据库操作的四大基础：

• Create（增） - 插入数据
• Read（查） - 查询数据
• Update（改） - 更新数据
• Delete（删） - 删除数据

二、环境准备（以MySQL为例）

-- 创建数据库
CREATE DATABASE crud_demo;
USE crud_demo;

-- 创建示例表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

三、增（Create）操作详解

基础插入

-- 插入单条数据
INSERT INTO users (name, email, age) 
VALUES ('张三', 'zhangsan@example.com', 25);

-- 插入多条数据
INSERT INTO users (name, email, age) VALUES
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28);

高级插入技巧

-- 插入查询结果
INSERT INTO users (name, email, age)
SELECT name, email, age FROM old_users WHERE age > 20;

-- 插入时处理冲突（MySQL）
INSERT INTO users (id, name, email) 
VALUES (1, '赵六', 'zhaoliu@example.com')
ON DUPLICATE KEY UPDATE name='赵六', email='zhaoliu@example.com';

四、查（Read）操作大全

基础查询

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

-- 查询特定列
SELECT name, email FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 25;

高级查询技巧

-- 多表连接查询
SELECT u.name, o.order_id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 聚合函数与分组
SELECT 
    age_group,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age
FROM (
    SELECT 
        CASE
            WHEN age < 20 THEN 'Under 20'
            WHEN age BETWEEN 20 AND 30 THEN '20-30'
            ELSE 'Over 30'
        END AS age_group,
        age
    FROM users
) grouped
GROUP BY age_group;

-- 窗口函数（MySQL 8.0+）
SELECT 
    name, 
    age,
    RANK() OVER (ORDER BY age DESC) AS age_rank
FROM users;

五、改（Update）操作指南

-- 基础更新
UPDATE users SET age = 26 WHERE name = '张三';

-- 多条件更新
UPDATE users SET age = age + 1 
WHERE created_at < '2023-01-01';

-- 关联更新
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.amount = o.amount * 0.9
WHERE u.age > 60;  -- 给老年用户9折优惠

六、删（Delete）操作精要

-- 条件删除
DELETE FROM users WHERE email IS NULL;

-- 关联删除
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.name = '张三';

-- 清空表（不可恢复）
TRUNCATE TABLE audit_log;

七、事务管理（ACID原则）

START TRANSACTION;

-- 一系列操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交或回滚
COMMIT; 
-- 或 ROLLBACK;

八、性能优化进阶

1. 索引优化

-- 创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_age ON users(age);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

2. EXPLAIN分析

EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

3. 查询优化技巧

• 避免 SELECT *，只获取必要字段
• 使用 LIMIT 分页
• 避免在 WHERE 子句中使用函数

九、安全最佳实践

防SQL注入（Python示例）

# 错误做法（易受注入攻击）
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# 正确做法（参数化查询）
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

最小权限原则

-- 创建只读用户
CREATE USER 'report_user'@'%' IDENTIFIED BY 'securepass';
GRANT SELECT ON crud_demo.* TO 'report_user'@'%';

十、不同数据库差异速查

十一、实战练习

-- 创建员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

/* 练习：
1. 插入3条员工记录
2. 查询市场部薪资>5000的员工
3. 给技术部员工加薪10%
4. 删除测试部门的记录
5. 统计各部门平均薪资
*/

十二、学习路径推荐

1. 入门：基础CRUD操作 → 单表查询
2. 进阶：多表连接 → 子查询 → 聚合函数
3. 高级：窗口函数 → 索引优化 → 事务管理
4. 精通：执行计划分析 → 存储过程 → 分布式事务

推荐工具：MySQL Workbench、pgAdmin、SQL Server Management Studio

掌握CRUD是数据库操作的基石，结合事务管理和性能优化技巧，即可应对90%的日常数据库操作需求！

附：CRUD速查表

| 操作  | 基础语法                                | 常用场景                     |
|-------|----------------------------------------|----------------------------|
| 增    | INSERT INTO table (col) VALUES (val)   | 添加新记录                 |
| 查    | SELECT col FROM table WHERE condition  | 数据检索/报表生成         |
| 改    | UPDATE table SET col=val WHERE cond    | 数据修正/状态更新         |
| 删    | DELETE FROM table WHERE condition      | 数据清理/合规删除         |
| 事务  | BEGIN; ... COMMIT;/ROLLBACK;           | 保证数据一致性操作        |