MySQL 增删改查

MySQL 增删改查全面指南：从基础到进阶

MySQL 是最流行的关系型数据库之一，掌握其增删改查(CRUD)操作是每个开发者的必备技能。本文将带你从最基础的 CRUD 操作开始，逐步深入到更高级的用法。

一、MySQL 基础 CRUD 操作

1. 创建数据库和表

在进行 CRUD 操作前，我们需要先创建数据库和表：

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 新增数据 (Insert into)

向表中新增新记录：

-- 插入单条记录
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 28);

-- 插入多条记录
INSERT INTO users (username, email, age) 
VALUES 
    ('jane_smith', 'jane@example.com', 32),
    ('bob_johnson', 'bob@example.com', 25),
    ('alice_williams', 'alice@example.com', 30);

3. 查询数据 (Select)

从表中检索数据：

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

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

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

-- 排序结果
SELECT * FROM users ORDER BY age DESC;

-- 限制返回数量
SELECT * FROM users LIMIT 2;

4. 修改数据 (Update)

修改表中的现有记录：

-- 更新单个记录
UPDATE users SET age = 29 WHERE username = 'john_doe';

-- 更新多个字段
UPDATE users 
SET email = 'new_john@example.com', age = 30 
WHERE username = 'john_doe';

-- 批量更新
UPDATE users SET age = age + 1 WHERE age < 30;

5. 删除数据 (Delete)

从表中删除记录：

-- 删除特定记录
DELETE FROM users WHERE username = 'bob_johnson';

-- 删除所有记录（慎用！）
DELETE FROM users;

-- 更快的清空表（不可回滚）
TRUNCATE TABLE users;

二、进阶 CRUD 操作

1. 高级查询技巧

连接查询 (JOIN)

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 内连接
SELECT u.username, o.amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.username, o.amount, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

分组和聚合

-- 分组统计
SELECT age, COUNT(*) as user_count
FROM users
GROUP BY age;

-- 带条件的分组
SELECT age, COUNT(*) as user_count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;

-- 常用聚合函数
SELECT 
    AVG(age) as avg_age,
    MAX(age) as max_age,
    MIN(age) as min_age,
    SUM(age) as total_age
FROM users;

子查询

-- WHERE 子句中的子查询
SELECT username, email
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- FROM 子句中的子查询
SELECT u.username, o.total_orders
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as total_orders
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

2. 高级插入技巧

插入查询结果

-- 创建用户备份表
CREATE TABLE users_backup LIKE users;

-- 插入查询结果
INSERT INTO users_backup
SELECT * FROM users WHERE age > 25;

ON DUPLICATE KEY UPDATE

-- 如果存在则更新，不存在则插入
INSERT INTO users (username, email, age)
VALUES ('john_doe', 'john_new@example.com', 31)
ON DUPLICATE KEY UPDATE email = VALUES(email), age = VALUES(age);

3. 批量操作优化

批量插入

-- 使用事务批量插入
START TRANSACTION;
INSERT INTO users (username, email, age) VALUES ('user1', 'user1@example.com', 21);
INSERT INTO users (username, email, age) VALUES ('user2', 'user2@example.com', 22);
-- 更多插入语句...
COMMIT;

-- 或者使用多值插入
INSERT INTO users (username, email, age) 
VALUES 
    ('user3', 'user3@example.com', 23),
    ('user4', 'user4@example.com', 24),
    ('user5', 'user5@example.com', 25);

批量更新

-- 使用 CASE 语句进行条件批量更新
UPDATE users
SET age = CASE
    WHEN username = 'john_doe' THEN 32
    WHEN username = 'jane_smith' THEN 33
    ELSE age
END
WHERE username IN ('john_doe', 'jane_smith');

4. 索引优化查询

-- 创建索引
CREATE INDEX idx_age ON users(age);

-- 多列索引
CREATE INDEX idx_username_email ON users(username, email);

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE age > 25;

三、实际应用示例

1. 分页查询

-- 每页5条记录，获取第2页
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 5 OFFSET 5;

-- MySQL 8.0+ 更简洁的写法
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 5, 5;

2. 全文搜索

-- 创建支持全文搜索的表
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
) ENGINE=InnoDB;

-- 全文搜索查询
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 教程');

3. 使用存储过程

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUsersByAgeRange(IN min_age INT, IN max_age INT)
BEGIN
    SELECT * FROM users
    WHERE age BETWEEN min_age AND max_age
    ORDER BY age;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUsersByAgeRange(25, 35);

四、最佳实践和安全注意事项

1. 始终使用参数化查询防止SQL注入

   -- 在应用程序中使用预处理语句
   -- PHP示例:
   $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
   $stmt->execute([$username]);
   

2. 合理使用事务保证数据一致性

   START TRANSACTION;
   -- 多个操作
   COMMIT; -- 或 ROLLBACK 如果出错
   

3. 定期备份重要数据

   # 使用mysqldump命令行工具
   mysqldump -u username -p mydb > mydb_backup.sql
   

4. **避免使用 SELECT ***，只查询需要的列

5. 为常用查询条件创建适当的索引，但不要过度索引