My Knowledge Base

外观

MySQL 笔记

约 2706 字大约 9 分钟

2025-08-16

一、MySQL 简介

1.1 什么是 MySQL？

MySQL 是一款流行的开源关系型数据库管理系统（RDBMS），由 Oracle 公司开发和维护。它使用结构化查询语言（SQL）进行数据库管理，以其高性能、高可靠性和易用性而闻名。

1.2 MySQL 核心特点

  • 开源免费：社区版完全免费，企业版提供商业支持
  • 跨平台：支持 Windows、Linux、macOS 等多种操作系统
  • 高性能：优化的查询引擎，适合高并发场景
  • 可扩展性：支持主从复制、分片等扩展方案
  • 安全性：提供完善的身份验证和访问控制机制
  • 易用性：丰富的管理工具和客户端

1.3 MySQL 与其他数据库比较

数据库类型优势适用场景
MySQL关系型开源、高性能、易用Web应用、中小型系统
Oracle关系型功能强大、高可靠性大型企业级应用
SQL Server关系型与Windows集成好.NET应用、企业环境
MongoDBNoSQL灵活的文档模型大数据、实时分析

二、MySQL 安装与配置

2.1 安装方法

Windows：

  1. 下载 MySQL Installer
  2. 选择 "Server only" 或 "Full" 安装类型
  3. 按照向导完成安装
  4. 设置 root 密码

Linux (Ubuntu)：

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Mac：

brew install mysql
brew services start mysql
mysql_secure_installation

2.2 基本配置

主要配置文件：my.cnf（Linux/Mac）或 my.ini（Windows）

常用配置项：

[mysqld]
# 基本设置
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data

# 性能相关
max_connections = 150
innodb_buffer_pool_size = 1G
query_cache_size = 64M

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

2.3 连接 MySQL

命令行连接：

mysql -u root -p
# 输入密码后进入 MySQL 命令行

常用连接参数：

  • -h：服务器地址（默认 localhost）
  • -P：端口号（默认 3306）
  • -u：用户名
  • -p：密码

三、数据库与表操作

3.1 数据库操作

-- 创建数据库
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

3.2 表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESCRIBE users;
-- 或
SHOW COLUMNS FROM users;

-- 修改表（添加列）
ALTER TABLE users ADD COLUMN age TINYINT AFTER username;

-- 修改表（修改列）
ALTER TABLE users MODIFY COLUMN age SMALLINT;

-- 修改表（删除列）
ALTER TABLE users DROP COLUMN age;

-- 删除表
DROP TABLE users;

3.3 常用数据类型

类型描述示例
整数类型
TINYINT小整数-128~127
SMALLINT中等整数-32768~32767
INT标准整数-2147483648~2147483647
BIGINT大整数-9223372036854775808~9223372036854775807
浮点类型
FLOAT单精度浮点数
DOUBLE双精度浮点数
DECIMAL(M,D)精确小数DECIMAL(10,2)
字符串类型
CHAR(N)定长字符串CHAR(50)
VARCHAR(N)变长字符串VARCHAR(100)
TEXT长文本
BLOB二进制数据
日期时间类型
DATE日期2023-08-15
TIME时间14:30:00
DATETIME日期时间2023-08-15 14:30:00
TIMESTAMP时间戳2023-08-15 14:30:00
YEAR年份2023

3.4 约束

-- 主键约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    ...
);

-- 唯一约束
CREATE TABLE users (
    email VARCHAR(100) UNIQUE,
    ...
);

-- 非空约束
CREATE TABLE users (
    username VARCHAR(50) NOT NULL,
    ...
);

-- 默认值
CREATE TABLE users (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ...
);

-- 外键约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

四、SQL 基础操作

4.1 插入数据

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

-- 插入多条记录
INSERT INTO users (username, email, password)
VALUES 
('alice', 'alice@example.com', 'password123'),
('bob', 'bob@example.com', 'password123');

-- 插入查询结果
INSERT INTO active_users (user_id, username)
SELECT id, username FROM users WHERE status = 'active';

4.2 查询数据

-- 基本查询
SELECT * FROM users;
SELECT id, username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE username = 'john';
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age ASC, username DESC;

-- 限制结果
SELECT * FROM users LIMIT 10;          -- 前10条
SELECT * FROM users LIMIT 5, 10;       -- 从第6条开始，取10条（分页常用）

4.3 更新数据

-- 更新单条记录
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 更新多条记录
UPDATE users SET status = 'inactive' WHERE last_login < '2022-01-01';

-- 更新时使用表达式
UPDATE products SET price = price * 0.9 WHERE category = 'sale';

4.4 删除数据

-- 删除特定记录
DELETE FROM users WHERE id = 1;

-- 删除多条记录
DELETE FROM users WHERE last_login < '2022-01-01';

-- 标记删除（推荐）
UPDATE users SET status = 'deleted' WHERE id = 1;

五、高级查询

5.1 聚合函数

-- 计数
SELECT COUNT(*) AS total_users FROM users;

-- 求和
SELECT SUM(amount) AS total_sales FROM orders;

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

-- 最大值/最小值
SELECT MAX(created_at) AS latest_user, MIN(created_at) AS earliest_user 
FROM users;

-- 分组统计
SELECT status, COUNT(*) AS count 
FROM users 
GROUP BY status;

5.2 分组查询

-- 基本分组
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

-- 分组过滤（HAVING）
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

-- 多列分组
SELECT department, position, COUNT(*) AS count
FROM employees
GROUP BY department, position;

5.3 多表连接查询

-- 内连接（INNER JOIN）
SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 左连接（LEFT JOIN）
SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- 右连接（RIGHT JOIN）
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- 多表连接
SELECT users.username, orders.order_id, products.name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

5.4 子查询

-- WHERE 子句中的子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 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;

-- SELECT 子句中的子查询
SELECT 
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

六、索引与性能优化

6.1 索引基础

索引是什么：索引是数据库中用于快速查找记录的一种数据结构，类似于书籍的目录。

为什么需要索引：

  • 加速数据检索
  • 加速表连接
  • 优化排序和分组操作
  • 保证数据唯一性

6.2 常见索引类型

类型描述适用场景
主键索引唯一且非空的索引主键字段
唯一索引确保值唯一需要唯一约束的字段
普通索引最基本的索引类型经常用于查询条件的字段
组合索引多个字段组成的索引多个字段经常一起用于查询条件
全文索引用于全文搜索文本字段的关键词搜索

6.3 索引操作

-- 创建索引
CREATE INDEX idx_username ON users(username);

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

-- 创建组合索引
CREATE INDEX idx_name_status ON users(username, status);

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

6.4 查询优化技巧

  1. **避免 SELECT ***

    -- 不推荐
SELECT * FROM users;

-- 推荐
SELECT id, username, email FROM users;

  2. 合理使用 LIMIT

    -- 分页查询优化
SELECT * FROM large_table ORDER BY id LIMIT 10000, 20;
-- 优化为
SELECT * FROM large_table WHERE id > 10000 ORDER BY id LIMIT 20;

  3. 避免在 WHERE 子句中对字段进行表达式操作

    -- 不推荐（无法使用索引）
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- 推荐
SELECT * FROM users 
WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

  4. 使用 EXPLAIN 分析查询

    EXPLAIN SELECT * FROM users WHERE username = 'john';

七、事务与锁

7.1 事务基础

事务是什么：事务是一系列操作的集合，这些操作要么全部成功，要么全部失败。

ACID 特性：

  • Atomicity（原子性）：事务是一个不可分割的工作单位
  • Consistency（一致性）：事务必须使数据库从一个一致性状态变换到另一个一致性状态
  • Isolation（隔离性）：并发事务之间的操作互不干扰
  • Durability（持久性）：事务一旦提交，它对数据库的改变是永久的

7.2 事务控制语句

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT point1;
-- 回滚到保存点
ROLLBACK TO point1;

7.3 事务隔离级别

隔离级别脏读不可重复读幻读说明
READ UNCOMMITTED✓✓✓最低隔离级别，性能最好
READ COMMITTED✗✓✓大多数数据库默认级别
REPEATABLE READ✗✗✓MySQL 默认级别
SERIALIZABLE✗✗✗最高隔离级别，性能最差

设置隔离级别：

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

-- 设置会话级别隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

八、常用函数

8.1 字符串函数

-- 字符串拼接
SELECT CONCAT('Hello', ' ', 'MySQL'); -- 'Hello MySQL'

-- 大小写转换
SELECT UPPER('hello'); -- 'HELLO'
SELECT LOWER('HELLO'); -- 'hello'

-- 字符串长度
SELECT LENGTH('MySQL'); -- 5
SELECT CHAR_LENGTH('你好'); -- 2

-- 子字符串
SELECT SUBSTRING('Hello MySQL', 1, 5); -- 'Hello'

-- 填充
SELECT LPAD('1', 3, '0'); -- '001'
SELECT RPAD('1', 3, '0'); -- '100'

-- 去除空格
SELECT TRIM('  MySQL  '); -- 'MySQL'

8.2 数值函数

-- 向上取整
SELECT CEIL(1.1); -- 2

-- 向下取整
SELECT FLOOR(1.9); -- 1

-- 四舍五入
SELECT ROUND(2.344, 2); -- 2.34

-- 随机数
SELECT RAND(); -- 0到1之间的随机数

-- 绝对值
SELECT ABS(-10); -- 10

-- 幂运算
SELECT POW(2, 3); -- 8

8.3 日期函数

-- 当前日期时间
SELECT NOW(); -- '2023-08-15 14:30:00'

-- 当前日期
SELECT CURDATE(); -- '2023-08-15'

-- 当前时间
SELECT CURTIME(); -- '14:30:00'

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- '2023-08-15'

-- 日期加减
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 明天
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 上个月

-- 日期差
SELECT DATEDIFF('2023-12-31', '2023-01-01'); -- 364

8.4 流程控制函数

-- IF 函数
SELECT IF(1 > 0, 'Yes', 'No'); -- 'Yes'

-- IFNULL 函数
SELECT IFNULL(NULL, 'Default'); -- 'Default'
SELECT IFNULL('', 'Default'); -- ''

-- CASE 语句
SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 70 THEN '中等'
        ELSE '及格'
    END AS grade
FROM students;

九、最佳实践

9.1 表设计规范

  1. 命名规范

    • 数据库名、表名、字段名使用小写字母
    • 使用下划线分隔单词（如 user_info）
    • 避免使用 MySQL 保留字

  2. 字段选择

    • 选择合适的数据类型，优先选择更小的数据类型
    • 为字段设置合适的长度
    • 为经常查询的字段建立索引
    • 为外键字段建立索引

  3. 主键设计

    • 每个表都应该有一个主键
    • 优先考虑使用自增整数作为主键
    • 避免使用业务相关的字段作为主键

9.2 查询优化技巧

  1. 索引使用原则

    • 为 WHERE 子句中的条件字段创建索引
    • 为 ORDER BY 和 GROUP BY 字段创建索引
    • 避免在索引列上使用函数或表达式
    • 选择性高的列更适合创建索引

  2. 避免全表扫描

    • 确保查询条件能有效利用索引
    • 限制返回的字段数量
    • 合理使用 LIMIT

  3. 优化 JOIN 操作

    • 确保 JOIN 条件字段有索引
    • 小表驱动大表
    • 避免多表 JOIN

9.3 安全注意事项

  1. 权限管理

    • 为不同应用创建不同的数据库用户
    • 遵循最小权限原则
    • 定期审查用户权限

  2. 防止 SQL 注入

    • 使用参数化查询
    • 避免拼接 SQL 语句
    • 对用户输入进行验证和过滤

  3. 备份策略

    • 定期进行全量备份
    • 结合二进制日志进行增量备份
    • 测试备份恢复流程