mysql中如何实现数据插入和更新及删除

发布时间：2021-10-25 14:25:28 作者：小新
来源：亿速云阅读：162

# MySQL中如何实现数据插入、更新及删除

## 目录
1. [数据插入操作](#数据插入操作)
   - [INSERT基础语法](#insert基础语法)
   - [批量插入数据](#批量插入数据)
   - [INSERT IGNORE与ON DUPLICATE KEY UPDATE](#insert-ignore与on-duplicate-key-update)
2. [数据更新操作](#数据更新操作)
   - [UPDATE基础语法](#update基础语法)
   - [多表更新](#多表更新)
   - [使用JOIN更新](#使用join更新)
3. [数据删除操作](#数据删除操作)
   - [DELETE基础语法](#delete基础语法)
   - [TRUNCATE与DELETE区别](#truncate与delete区别)
   - [多表删除](#多表删除)
4. [事务中的DML操作](#事务中的dml操作)
5. [性能优化建议](#性能优化建议)
6. [总结](#总结)

---

## 数据插入操作

### INSERT基础语法

```sql
-- 单行插入基础语法
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,...);

-- 示例：向users表插入数据
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', 'john@example.com', NOW());

注意事项： 1. 列名与值必须一一对应 2. 可省略列名（需提供所有列的值） 3. 自动递增列通常不需要指定

批量插入数据

-- 多行插入语法（MySQL特有）
INSERT INTO products (name, price) VALUES
('Laptop', 999.99),
('Phone', 699.99),
('Tablet', 399.99);

-- 从其他表插入数据
INSERT INTO user_backup
SELECT * FROM users WHERE created_at < '2023-01-01';

性能优势： - 减少网络往返开销 - 单次事务提交 - InnoDB的批量插入优化

INSERT IGNORE与ON DUPLICATE KEY UPDATE

-- 忽略重复键错误
INSERT IGNORE INTO unique_users (user_id, username)
VALUES (1, 'admin');

-- 存在则更新，不存在则插入
INSERT INTO inventory (product_id, quantity)
VALUES (1001, 10)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

应用场景： - 避免重复数据插入 - 实现”存在即更新”逻辑 - 计数器场景特别有用

数据更新操作

UPDATE基础语法

UPDATE table_name
SET column1 = value1, column2 = value2,...
[WHERE condition]
[ORDER BY ...]
[LIMIT count];

-- 示例：修改用户邮箱
UPDATE users SET email = 'new@example.com'
WHERE user_id = 1001;

关键要点： 1. 必须使用WHERE子句避免全表更新 2. 支持表达式计算（如：price = price * 0.9） 3. LIMIT子句可控制影响行数

多表更新

-- 标准多表更新语法
UPDATE orders o, customers c
SET o.status = 'shipped', c.last_order = NOW()
WHERE o.customer_id = c.customer_id
AND o.order_id = 5001;

使用JOIN更新

-- 使用JOIN语法更新
UPDATE products p
JOIN inventory i ON p.product_id = i.product_id
SET p.stock_flag = IF(i.quantity > 0, 'IN_STOCK', 'OUT_OF_STOCK')
WHERE p.category = 'electronics';

复杂更新示例：

UPDATE employees e
SET e.salary = e.salary * 
    CASE 
        WHEN e.performance > 90 THEN 1.15
        WHEN e.performance > 75 THEN 1.10
        ELSE 1.05
    END
WHERE e.department = 'Engineering';

数据删除操作

DELETE基础语法

DELETE FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

-- 示例：删除30天前的日志
DELETE FROM access_log
WHERE access_time < NOW() - INTERVAL 30 DAY;

注意事项： 1. 无WHERE子句将清空整个表 2. 支持事务回滚 3. 大表删除建议分批进行

TRUNCATE与DELETE区别

特性	DELETE	TRUNCATE
语法	DML语句	DDL语句
事务	可回滚	不可回滚
性能	逐行删除，较慢	直接删除表数据，极快
触发器	会触发	不会触发
自增值	不重置	重置为初始值

-- 快速清空表数据
TRUNCATE TABLE temp_data;

多表删除

-- 删除关联数据（方式一）
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.status = 'inactive';

-- 删除关联数据（方式二）
DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers 
    WHERE status = 'inactive'
);

事务中的DML操作

START TRANSACTION;

-- 执行系列操作
INSERT INTO orders (...) VALUES (...);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 1001;
INSERT INTO order_log (...) VALUES (...);

-- 根据条件提交或回滚
IF everything_ok THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

事务特性： - 原子性：所有操作要么全部成功，要么全部失败 - 一致性：保持数据库完整性约束 - 隔离性：并发事务互不干扰 - 持久性：提交后修改永久保存

性能优化建议

插入优化：
- 批量插入代替单行插入
- 使用LOAD DATA INFILE导入大数据
- 临时禁用索引和约束
更新优化：
- 避免全表更新
- 为WHERE条件列创建索引
- 大表更新分批进行
删除优化：
- 优先使用TRUNCATE清空表
- 大表删除使用LIMIT分批次
- 考虑先DROP索引再重建

示例：高效批量操作

-- 批量插入优化
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

INSERT INTO big_table (...) VALUES (...),(...),...;

SET unique_checks=1;
SET foreign_key_checks=1;
COMMIT;

总结

MySQL的数据操作语言（DML）提供了灵活的数据管理能力： 1. 插入操作支持单行、批量及条件插入 2. 更新操作可实现复杂业务逻辑修改 3. 删除操作需特别注意数据安全 4. 事务保证操作的原子性和一致性 5. 合理的优化策略可显著提升性能

掌握这些核心操作后，可以高效安全地管理MySQL数据库中的数据，为应用开发提供坚实的数据持久层支持。 “`

这篇文章共计约2400字，采用Markdown格式编写，包含： - 清晰的层级结构 - 语法高亮的代码示例 - 对比表格等可视化元素 - 实用性能优化建议 - 完整的操作场景覆盖

可根据需要进一步扩展特定部分的详细说明或添加更多实际案例。