MySQL基础：从SQL语法到事务特性全面解析

程序员Bears

于 2025-05-01 08:00:00 发布

阅读量690

点赞数 17

分类专栏： Java全栈成长笔记文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_57755194/article/details/147624855

版权

Java全栈成长笔记专栏收录该内容

11 篇文章

订阅专栏

前言

MySQL作为最流行的开源关系型数据库管理系统，在Web应用开发中占据着举足轻重的地位。无论你是刚入门的新手，还是有一定经验的开发者，掌握MySQL的基础知识都是必不可少的。本文将系统性地介绍MySQL的核心概念，包括SQL基础语法、表设计原则以及事务特性，帮助你构建坚实的MySQL基础。

一、SQL基础语法（CRUD操作）

1.1 什么是CRUD

CRUD代表了数据库中最基本的四种操作：

Create（创建）
Read（读取）
Update（更新）
Delete（删除）

这些操作构成了我们对数据库进行交互的基础。

1.2 创建数据（INSERT）

-- 基本语法
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

-- 示例：向users表插入一条记录
INSERT INTO users (username, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- 批量插入
INSERT INTO users (username, email, age) 
VALUES 
('李四', 'lisi@example.com', 30),
('王五', 'wangwu@example.com', 28);

最佳实践：

明确指定列名，避免依赖表结构顺序
批量插入可显著提高性能
考虑使用预处理语句防止SQL注入

1.3 查询数据（SELECT）

-- 基本语法
SELECT 列1, 列2, ... FROM 表名 [WHERE 条件] [ORDER BY 排序] [LIMIT 限制];

-- 查询所有列
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 10 OFFSET 20;  -- 相当于第3页，每页10条

高级查询技巧：

-- 聚合函数
SELECT COUNT(*) as total_users FROM users;
SELECT AVG(age) as avg_age FROM users;

-- 分组查询
SELECT age, COUNT(*) as count FROM users GROUP BY age;

-- 连接查询
SELECT u.username, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;

-- 子查询
SELECT username FROM users WHERE age > (SELECT AVG(age) FROM users);

1.4 更新数据（UPDATE）

-- 基本语法
UPDATE 表名 SET 列1=值1, 列2=值2, ... WHERE 条件;

-- 示例：更新特定用户的邮箱
UPDATE users SET email = 'new_email@example.com' WHERE username = '张三';

-- 多列更新
UPDATE users SET email = 'new@example.com', age = 26 WHERE id = 1;

注意事项：

务必包含WHERE条件，否则会更新整张表
更新前最好先查询确认要更新的记录
大量更新时考虑分批进行

1.5 删除数据（DELETE）

-- 基本语法
DELETE FROM 表名 WHERE 条件;

-- 示例：删除特定用户
DELETE FROM users WHERE username = '张三';

-- 删除所有数据（慎用！）
DELETE FROM users;

重要提示：

生产环境中删除操作前务必备份数据
考虑使用软删除（添加is_deleted标记）而非物理删除
大量删除时考虑性能影响

二、表设计：主键、外键与索引

2.1 主键（Primary Key）

主键是表中唯一标识每条记录的列或列组合。

特性：

唯一性：不能有重复值
非空性：不能为NULL
一个表只能有一个主键

-- 创建表时指定主键
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

-- 已有表添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

主键选择建议：

自增整数（INT/BIGINT AUTO_INCREMENT）：简单高效
UUID：分布式系统适用
自然键：当有业务意义的唯一标识时

2.2 外键（Foreign Key）

外键用于建立表与表之间的关联关系，确保数据完整性。

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

外键约束行为：

ON DELETE CASCADE：主表记录删除时，从表相关记录自动删除
ON DELETE SET NULL：主表记录删除时，从表外键设为NULL
ON DELETE RESTRICT：阻止主表记录删除（默认）

-- 带约束行为的示例
CREATE TABLE orders (
    ...
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

2.3 索引（Index）

索引是提高查询性能的数据结构，类似于书籍的目录。

常见索引类型：

普通索引
唯一索引
复合索引
全文索引

-- 创建普通索引
CREATE INDEX idx_email ON users(email);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users(username, age);

-- 删除索引
DROP INDEX idx_email ON users;

索引最佳实践：

为经常出现在WHERE、ORDER BY、JOIN条件中的列创建索引
避免过度索引，因为索引会降低写入性能
复合索引注意列顺序（最左前缀原则）
定期分析查询性能，调整索引策略

如何选择合适的索引：

高选择性的列（唯一值多的列）更适合建索引
小表通常不需要索引
长字符串考虑前缀索引

-- 前缀索引示例
CREATE INDEX idx_username_prefix ON users(username(10));

三、事务与ACID特性

3.1 什么是事务

事务是一组原子性的SQL操作，要么全部执行成功，要么全部失败回滚。

事务的基本语法：

START TRANSACTION;
-- 执行一系列SQL操作
COMMIT;  -- 提交事务
-- 或
ROLLBACK;  -- 回滚事务

3.2 ACID特性

1. 原子性（Atomicity）

事务是不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。

2. 一致性（Consistency）

事务执行前后，数据库从一个一致状态变到另一个一致状态。

3. 隔离性（Isolation）

多个事务并发执行时，一个事务的执行不应影响其他事务。

4. 持久性（Durability）

一旦事务提交，其结果就是永久性的。

3.3 事务隔离级别

MySQL支持四种隔离级别：

READ UNCOMMITTED（读未提交）
- 可能读到未提交的数据（脏读）
READ COMMITTED（读已提交）
- 只能读到已提交的数据（解决脏读）
- 可能出现不可重复读
REPEATABLE READ（可重复读） - MySQL默认级别
- 同一事务中多次读取相同数据结果一致（解决不可重复读）
- 可能出现幻读
SERIALIZABLE（串行化）
- 最高隔离级别，完全串行执行（解决所有问题）
- 性能最差

设置隔离级别：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

3.4 事务实践示例

银行转账场景：

START TRANSACTION;

-- 检查账户余额是否足够
SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;

-- 从账户1扣款
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 向账户2加款
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 记录交易
INSERT INTO transactions (from_user, to_user, amount) VALUES (1, 2, 100);

COMMIT;

注意事项：