增删改查操作（CRUD）

嗨！我是长安。今天我们学习数据库最核心的操作：增删改查，也叫CRUD（Create、Read、Update、Delete）。

掌握了这四个操作，你就能操控数据了！🚀

🎯 什么是CRUD？

想象你在管理一个笔记本：

  • 📝 Create（增）：写一条新笔记
  • 📖 Read（查）：看笔记内容
  • ✏️ Update（改）：修改笔记
  • 🗑️ Delete（删）：删除笔记

数据库也是一样的！

📝 CREATE - 插入数据（INSERT）

基本语法

INSERT INTO 表名 (列1, 列2, 列3) 
VALUES (值1, 值2, 值3);

准备工作：创建测试表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入一条数据

-- 完整写法
INSERT INTO users (username, email, age, city) 
VALUES ('长安', 'changan@email.com', 25, '北京');

-- 如果列顺序和表定义一致，可以省略列名
INSERT INTO users 
VALUES (DEFAULT, '小明', 'xiaoming@email.com', 22, '上海', DEFAULT);
-- DEFAULT表示使用默认值（id自增，created_at自动生成）

插入多条数据

INSERT INTO users (username, email, age, city) 
VALUES 
    ('小红', 'xiaohong@email.com', 23, '广州'),
    ('小李', 'xiaoli@email.com', 28, '深圳'),
    ('小王', 'xiaowang@email.com', 26, '杭州');

长安的技巧：一次插入多条数据，比循环插入快很多！

插入并返回数据

-- 返回插入的数据
INSERT INTO users (username, email, age, city) 
VALUES ('测试用户', 'test@email.com', 20, '成都')
RETURNING *;

-- 只返回ID
INSERT INTO users (username, email, age, city) 
VALUES ('测试用户2', 'test2@email.com', 21, '武汉')
RETURNING id;

-- 返回多列
INSERT INTO users (username, email, age, city) 
VALUES ('测试用户3', 'test3@email.com', 22, '西安')
RETURNING id, username, created_at;

长安提醒：RETURNING超好用！插入后直接得到结果，不用再查一次。

如果存在则忽略（避免重复）

-- ON CONFLICT DO NOTHING：遇到冲突就忽略
INSERT INTO users (username, email, age) 
VALUES ('长安', 'changan@email.com', 25)
ON CONFLICT (email) DO NOTHING;
-- 如果email已存在，就不插入

-- ON CONFLICT DO UPDATE：遇到冲突就更新
INSERT INTO users (username, email, age, city) 
VALUES ('长安', 'changan@email.com', 26, '北京')
ON CONFLICT (email) 
DO UPDATE SET 
    age = EXCLUDED.age,
    city = EXCLUDED.city;
-- 如果email存在，就更新age和city

实际应用场景：用户注册时，如果邮箱已存在，更新信息而不是报错。

📖 READ - 查询数据（SELECT）

基本语法

SELECT 列1, 列2, ... 
FROM 表名 
WHERE 条件;

查询所有数据

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

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

-- 查询并重命名列
SELECT 
    username AS 用户名,
    email AS 邮箱,
    age AS 年龄
FROM users;

长安的建议：生产环境别用SELECT *，明确写出需要的列，性能更好！

查询不重复的数据

-- 查询所有城市（去重）
SELECT DISTINCT city FROM users;

-- 查询不重复的城市和年龄组合
SELECT DISTINCT city, age FROM users;

限制返回数量

-- 只返回前5条
SELECT * FROM users LIMIT 5;

-- 跳过前10条，返回接下来的5条（分页）
SELECT * FROM users OFFSET 10 LIMIT 5;

-- 第2页，每页10条
SELECT * FROM users OFFSET 10 LIMIT 10;

长安的分页公式：

OFFSET = (页码 - 1) × 每页数量
LIMIT = 每页数量

排序

-- 按年龄升序
SELECT * FROM users ORDER BY age ASC;

-- 按年龄降序
SELECT * FROM users ORDER BY age DESC;

-- 多列排序：先按城市，再按年龄
SELECT * FROM users 
ORDER BY city ASC, age DESC;

-- 空值排序
SELECT * FROM users 
ORDER BY age NULLS LAST;  -- NULL放最后

统计查询

-- 统计总数
SELECT COUNT(*) FROM users;

-- 统计某列非空的数量
SELECT COUNT(age) FROM users;

-- 统计不重复的数量
SELECT COUNT(DISTINCT city) FROM users;

-- 最大值、最小值
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;

-- 平均值
SELECT AVG(age) FROM users;

-- 求和
SELECT SUM(age) FROM users;

分组查询

-- 按城市分组，统计每个城市的人数
SELECT city, COUNT(*) as user_count 
FROM users 
GROUP BY city;

-- 按城市分组，计算平均年龄
SELECT city, AVG(age) as avg_age 
FROM users 
GROUP BY city;

-- 分组后筛选（HAVING）
SELECT city, COUNT(*) as user_count 
FROM users 
GROUP BY city 
HAVING COUNT(*) >= 2;  -- 只显示人数>=2的城市

WHERE vs HAVING：

  • WHERE：在分组之前过滤
  • HAVING：在分组之后过滤
-- 先筛选age>20的，再按城市分组
SELECT city, COUNT(*) 
FROM users 
WHERE age > 20 
GROUP BY city;

-- 先按城市分组，再筛选人数>2的
SELECT city, COUNT(*) as cnt 
FROM users 
GROUP BY city 
HAVING COUNT(*) > 2;

✏️ UPDATE - 修改数据

基本语法

UPDATE 表名 
SET 列1 = 新值1, 列2 = 新值2 
WHERE 条件;

更新单条数据

-- 更新ID为1的用户
UPDATE users 
SET age = 26, city = '上海' 
WHERE id = 1;

-- 更新并返回
UPDATE users 
SET age = 27 
WHERE id = 1 
RETURNING *;

⚠️ 长安的警告：一定要加WHERE！否则会更新所有数据！

更新多条数据

-- 将所有北京的用户年龄+1
UPDATE users 
SET age = age + 1 
WHERE city = '北京';

-- 将年龄小于25的用户标记为年轻人
UPDATE users 
SET category = '年轻人' 
WHERE age < 25;

根据其他表的数据更新

-- 创建一个积分表
CREATE TABLE user_points (
    user_id INT PRIMARY KEY,
    points INT DEFAULT 0
);

-- 根据积分更新用户等级
UPDATE users 
SET level = CASE 
    WHEN user_points.points >= 1000 THEN 'VIP'
    WHEN user_points.points >= 500 THEN '高级会员'
    ELSE '普通会员'
END
FROM user_points 
WHERE users.id = user_points.user_id;

批量更新不同值

-- 方法1：使用CASE WHEN
UPDATE users 
SET city = CASE 
    WHEN id = 1 THEN '北京'
    WHEN id = 2 THEN '上海'
    WHEN id = 3 THEN '广州'
    ELSE city
END
WHERE id IN (1, 2, 3);

-- 方法2：从临时数据更新
UPDATE users 
SET city = tmp.new_city 
FROM (VALUES 
    (1, '北京'),
    (2, '上海'),
    (3, '广州')
) AS tmp(id, new_city) 
WHERE users.id = tmp.id;

🗑️ DELETE - 删除数据

基本语法

DELETE FROM 表名 
WHERE 条件;

删除单条数据

-- 删除ID为1的用户
DELETE FROM users WHERE id = 1;

-- 删除并返回
DELETE FROM users 
WHERE id = 2 
RETURNING *;

删除多条数据

-- 删除所有年龄小于18的用户
DELETE FROM users WHERE age < 18;

-- 删除北京的用户
DELETE FROM users WHERE city = '北京';

删除所有数据

-- 方法1：DELETE（慢，但可以回滚）
DELETE FROM users;

-- 方法2：TRUNCATE（快，但不能回滚）
TRUNCATE TABLE users;

-- TRUNCATE并重置自增ID
TRUNCATE TABLE users RESTART IDENTITY;

DELETE vs TRUNCATE：

特性DELETETRUNCATE
速度较慢很快
可回滚可以不可以
WHERE条件支持不支持
重置自增不重置可重置
触发器触发不触发

长安建议：

  • 删除部分数据：用DELETE
  • 清空整个表：用TRUNCATE

软删除（推荐）

生产环境中，建议用"软删除"而不是真删除！

-- 添加删除标记字段
ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE;
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- 软删除：标记为删除
UPDATE users 
SET is_deleted = TRUE, deleted_at = NOW() 
WHERE id = 1;

-- 查询时排除已删除的
SELECT * FROM users WHERE is_deleted = FALSE;

-- 或者创建视图
CREATE VIEW active_users AS 
SELECT * FROM users WHERE is_deleted = FALSE;

-- 之后就查视图
SELECT * FROM active_users;

软删除的好处：

  • ✅ 可以恢复
  • ✅ 保留历史记录
  • ✅ 数据完整性更好

🎯 综合实战：博客系统

让我们创建一个简单的博客系统，练习所有CRUD操作！

第一步：创建表

CREATE TABLE blog_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES blog_users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    views INT DEFAULT 0,
    likes INT DEFAULT 0,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE blog_comments (
    id SERIAL PRIMARY KEY,
    post_id INT NOT NULL REFERENCES blog_posts(id),
    user_id INT NOT NULL REFERENCES blog_users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

第二步：插入测试数据

-- 插入用户
INSERT INTO blog_users (username, email, password, bio) 
VALUES 
    ('长安', 'changan@email.com', 'hashed_password_1', '编程指南创始人'),
    ('小明', 'xiaoming@email.com', 'hashed_password_2', '热爱编程的学生'),
    ('小红', 'xiaohong@email.com', 'hashed_password_3', '全栈开发工程师')
RETURNING id, username;

-- 插入文章
INSERT INTO blog_posts (user_id, title, content, published) 
VALUES 
    (1, 'PostgreSQL入门教程', '这是一篇很棒的教程...', TRUE),
    (1, 'SQL高级技巧', '分享一些高级技巧...', TRUE),
    (2, '我的学习笔记', '今天学习了...', FALSE)
RETURNING id, title;

-- 插入评论
INSERT INTO blog_comments (post_id, user_id, content) 
VALUES 
    (1, 2, '写得太好了！'),
    (1, 3, '学到很多，感谢分享！'),
    (2, 3, '期待更多内容');

第三步：各种查询

-- 查询所有已发布的文章
SELECT * FROM blog_posts 
WHERE published = TRUE 
ORDER BY created_at DESC;

-- 查询某个用户的所有文章
SELECT * FROM blog_posts 
WHERE user_id = 1;

-- 查询最受欢迎的文章（按浏览量）
SELECT * FROM blog_posts 
WHERE published = TRUE 
ORDER BY views DESC 
LIMIT 10;

-- 查询文章及作者信息
SELECT 
    p.id,
    p.title,
    p.views,
    p.likes,
    u.username AS author,
    u.email
FROM blog_posts p
JOIN blog_users u ON p.user_id = u.id
WHERE p.published = TRUE;

-- 统计每个用户的文章数
SELECT 
    u.username,
    COUNT(p.id) AS post_count
FROM blog_users u
LEFT JOIN blog_posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

-- 查询文章及其评论数
SELECT 
    p.title,
    COUNT(c.id) AS comment_count
FROM blog_posts p
LEFT JOIN blog_comments c ON p.id = c.post_id
GROUP BY p.id, p.title
ORDER BY comment_count DESC;

第四步：更新操作

-- 更新文章内容
UPDATE blog_posts 
SET 
    content = '更新后的内容...',
    updated_at = NOW()
WHERE id = 1;

-- 发布文章
UPDATE blog_posts 
SET published = TRUE 
WHERE id = 3 
RETURNING id, title, published;

-- 增加浏览量
UPDATE blog_posts 
SET views = views + 1 
WHERE id = 1 
RETURNING views;

-- 点赞
UPDATE blog_posts 
SET likes = likes + 1 
WHERE id = 1;

-- 更新用户信息
UPDATE blog_users 
SET bio = '编程指南创始人，专注PostgreSQL教学' 
WHERE username = '长安';

第五步：删除操作

-- 删除评论
DELETE FROM blog_comments 
WHERE id = 1;

-- 删除文章（注意外键约束）
DELETE FROM blog_posts 
WHERE id = 3;

-- 删除用户（需要先删除相关数据）
-- 先删除评论
DELETE FROM blog_comments WHERE user_id = 2;
-- 再删除文章
DELETE FROM blog_posts WHERE user_id = 2;
-- 最后删除用户
DELETE FROM blog_users WHERE id = 2;

💡 CRUD最佳实践

1. 安全性

-- ❌ 危险！容易SQL注入
-- 在应用代码中：
-- query = "SELECT * FROM users WHERE username = '" + input + "'"

-- ✅ 使用参数化查询（在应用代码中）
-- 伪代码示例
query = "SELECT * FROM users WHERE username = $1"
execute(query, [username])

2. 性能优化

-- ❌ 不好
SELECT * FROM blog_posts;  -- 查询所有列

-- ✅ 好
SELECT id, title, created_at FROM blog_posts;  -- 只查需要的列

-- ❌ 不好：N+1查询问题
-- 先查文章，再循环查作者

-- ✅ 好：使用JOIN一次查出
SELECT p.*, u.username 
FROM blog_posts p 
JOIN blog_users u ON p.user_id = u.id;

3. 批量操作

-- ❌ 不好：循环插入
INSERT INTO users (username, email) VALUES ('user1', 'user1@email.com');
INSERT INTO users (username, email) VALUES ('user2', 'user2@email.com');
-- ... 1000次

-- ✅ 好：批量插入
INSERT INTO users (username, email) 
VALUES 
    ('user1', 'user1@email.com'),
    ('user2', 'user2@email.com'),
    -- ... 一次插入多条
    ('user1000', 'user1000@email.com');

4. 事务处理

-- 开始事务
BEGIN;

-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 如果出错，回滚
-- ROLLBACK;

🎮 练习题

任务1：学生管理系统

创建学生管理系统，并完成以下操作：

  1. 创建students表（id, name, age, grade, class）
  2. 插入至少5个学生
  3. 查询所有学生
  4. 查询年龄大于15的学生
  5. 更新某个学生的班级
  6. 删除某个学生
  7. 统计每个年级的学生数
点击查看参考答案
-- 1. 创建表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 6 AND age <= 20),
    grade VARCHAR(20),
    class VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. 插入数据
INSERT INTO students (name, age, grade, class) 
VALUES 
    ('张三', 16, '高一', '1班'),
    ('李四', 17, '高二', '2班'),
    ('王五', 15, '高一', '1班'),
    ('赵六', 18, '高三', '3班'),
    ('孙七', 16, '高一', '2班');

-- 3. 查询所有学生
SELECT * FROM students;

-- 4. 查询年龄大于15的学生
SELECT * FROM students WHERE age > 15;

-- 5. 更新班级
UPDATE students 
SET class = '3班' 
WHERE name = '张三';

-- 6. 删除学生
DELETE FROM students WHERE name = '孙七';

-- 7. 统计每个年级的学生数
SELECT grade, COUNT(*) as student_count 
FROM students 
GROUP BY grade 
ORDER BY grade;

📚 下一步

恭喜！你已经掌握了CRUD的所有基本操作！现在你可以：

  • ✅ 插入数据（INSERT）
  • ✅ 查询数据（SELECT）
  • ✅ 更新数据（UPDATE）
  • ✅ 删除数据（DELETE）

接下来，让我们学习查询条件WHERE，掌握更复杂的查询技巧！

长安的总结：

CRUD是数据库操作的核心，务必掌握！记住几个要点：

  1. INSERT：一次插入多条更快
  2. SELECT：只查需要的列，用WHERE过滤
  3. UPDATE/DELETE：一定要加WHERE条件！
  4. 安全第一：永远使用参数化查询

多练习，这些操作很快就能熟练掌握！💪

有问题来编程指南找我！

最近更新: