MySQL数据库完全指南：从入门到精通

一、MySQL基础入门

什么是MySQL？

MySQL是由瑞典MySQL AB公司开发的关系型数据库管理系统，现在是Oracle公司的产品。作为开源数据库的代表，MySQL具有以下特点：

• 开源免费：采用GPL协议，可免费使用
• 性能优异：支持高并发访问，查询效率高
• 易于使用：SQL语法简洁，学习曲线平缓
• 生态完善：配套工具丰富，社区活跃
• 跨平台：支持Windows、Linux、macOS等系统

安装MySQL

Ubuntu/Debian：

bash

# 安装MySQL服务器
sudo apt update
sudo apt install mysql-server

# 启动服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 安全初始化
sudo mysql_secure_installation

CentOS/RHEL：

bash

# 安装MySQL
sudo yum install mysql-server

# 启动服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 安全初始化
sudo mysql_secure_installation

Docker方式：

bash

# 拉取MySQL镜像
docker pull mysql:8.0

# 运行容器
docker run -d \
  --name mysql \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -e MYSQL_DATABASE=myapp \
  -v mysql_data:/var/lib/mysql \
  mysql:8.0

# 连接MySQL
docker exec -it mysql mysql -u root -p

基本命令连接

bash

# 命令行连接
mysql -u root -p

# 连接指定数据库
mysql -u root -p mydatabase

# 远程连接
mysql -h 192.168.1.100 -P 3306 -u root -p

# 使用SQL文件导入
mysql -u root -p < backup.sql

二、数据库和数据表操作

创建数据库

sql

-- 创建数据库
CREATE DATABASE myapp;

-- 指定字符集和排序规则
CREATE DATABASE myapp 
    DEFAULT CHARACTER SET utf8mb4 
    DEFAULT COLLATE utf8mb4_unicode_ci;

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

-- 选择数据库
USE myapp;

-- 删除数据库（谨慎使用）
DROP DATABASE myapp;

创建数据表

sql

-- 创建用户表
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,
    age INT DEFAULT 0,
    status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 创建文章表
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    views INT DEFAULT 0,
    likes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

-- 查看表结构
DESCRIBE users;
SHOW CREATE TABLE users;

数据类型详解

sql

-- 数值类型
TINYINT      -- 1字节，-128到127
SMALLINT     -- 2字节，-32768到32767
INT          -- 4字节，约21亿
BIGINT       -- 8字节，极大数值
FLOAT        -- 4字节浮点
DOUBLE       -- 8字节高精度浮点
DECIMAL(10,2) -- 定点数，适合金额

-- 字符串类型
CHAR(10)     -- 固定长度，不足补空格
VARCHAR(255) -- 可变长度，最大255
TEXT         -- 大文本，最大65535字节
MEDIUMTEXT   -- 中文本，最大16MB
LONGTEXT     -- 大文本，最大4GB

-- 日期时间类型
DATE         -- 日期 '2026-04-16'
TIME         -- 时间 '12:30:00'
DATETIME     -- 日期时间 '2026-04-16 12:30:00'
TIMESTAMP    -- 时间戳，自动更新
YEAR         -- 年份

-- ENUM和SET类型
ENUM('active', 'inactive', 'pending')
SET('news', 'sports', 'tech')

三、SQL查询语句

基础查询

sql

-- 查询所有记录
SELECT * FROM users;

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

-- 别名查询
SELECT username AS '用户名', email AS '邮箱' FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE status = 1 AND age >= 18;
SELECT * FROM users WHERE username LIKE '张%';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;  -- 降序
SELECT * FROM users ORDER BY age ASC;          -- 升序
SELECT * FROM users ORDER BY status, created_at DESC;

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT * FROM users LIMIT 10, 20;  -- 从第10条开始，取20条
SELECT * FROM users LIMIT 20;      -- 前20条

聚合函数

sql

-- COUNT 统计数量
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 1;

-- SUM 求和
SELECT SUM(views) FROM articles;

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

-- MAX 最大值
SELECT MAX(views) FROM articles;

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

-- 分组统计
SELECT status, COUNT(*) as total FROM users GROUP BY status;
SELECT user_id, COUNT(*) as article_count 
FROM articles 
GROUP BY user_id 
HAVING article_count > 5;

-- 统计函数组合
SELECT 
    COUNT(*) as total_users,
    AVG(age) as avg_age,
    MAX(age) as max_age,
    MIN(age) as min_age
FROM users;

多表查询

sql

-- 内连接
SELECT u.username, a.title, a.created_at
FROM users u
INNER JOIN articles a ON u.id = a.user_id;

-- 左连接
SELECT u.username, a.title
FROM users u
LEFT JOIN articles a ON u.id = a.user_id;

-- 右连接
SELECT u.username, a.title
FROM users u
RIGHT JOIN articles a ON u.id = a.user_id;

-- 多表连接
SELECT 
    u.username,
    a.title,
    c.content as comment
FROM users u
INNER JOIN articles a ON u.id = a.user_id
INNER JOIN comments c ON a.id = c.article_id
WHERE u.status = 1;

-- 子查询
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM articles);

SELECT * FROM articles 
WHERE views > (SELECT AVG(views) FROM articles);

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM articles a WHERE a.user_id = u.id
);

高级查询技巧

sql

-- UNION合并查询
SELECT username FROM users WHERE status = 1
UNION
SELECT username FROM admin_users;

-- UNION ALL（保留重复）
SELECT username FROM users
UNION ALL
SELECT username FROM admin_users;

-- IF条件判断
SELECT 
    username,
    IF(age >= 18, '成年', '未成年') as age_group
FROM users;

-- CASE语句
SELECT 
    username,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 30 THEN '青年'
        WHEN age BETWEEN 31 AND 50 THEN '中年'
        ELSE '老年'
    END as age_category
FROM users;

-- COALESCE处理NULL
SELECT 
    username,
    COALESCE(email, phone, '无联系方式') as contact
FROM users;

-- CONCAT拼接
SELECT CONCAT(username, ' - ', email) as user_info FROM users;

四、数据操作（CRUD）

插入数据

sql

-- 插入单条记录
INSERT INTO users (username, email, password, age)
VALUES ('张三', 'zhangsan@example.com', 'hashed_pass', 25);

-- 插入多条记录
INSERT INTO users (username, email, password, age) VALUES
('李四', 'lisi@example.com', 'pass1', 30),
('王五', 'wangwu@example.com', 'pass2', 28),
('赵六', 'zhaoliu@example.com', 'pass3', 35);

-- 插入查询结果
INSERT INTO articles (user_id, title, content)
SELECT id, CONCAT('欢迎 ', username), '欢迎加入我们！'
FROM users WHERE status = 1;

-- REPLACE（存在则替换）
REPLACE INTO users (id, username, email)
VALUES (1, '新张三', 'new@example.com');

-- ON DUPLICATE KEY UPDATE
INSERT INTO article_stats (article_id, views)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE views = views + 1;

更新数据

sql

-- 更新单条记录
UPDATE users SET age = 26 WHERE id = 1;

-- 更新多条记录
UPDATE users SET status = 0, updated_at = NOW() WHERE age < 18;

-- 使用表达式更新
UPDATE articles SET views = views + 1 WHERE id = 1;

-- 条件更新
UPDATE users 
SET email = CONCAT(username, '@example.com')
WHERE email IS NULL;

-- 更新前备份
UPDATE articles 
SET content = OLD_CONTENT
WHERE id = 1;

删除数据

sql

-- 删除单条记录
DELETE FROM users WHERE id = 1;

-- 批量删除
DELETE FROM users WHERE status = 0 AND created_at < '2025-01-01';

-- 清空表（慎用）
DELETE FROM articles;           -- 可回滚，有日志
TRUNCATE TABLE articles;       -- 快速清空，不可回滚

-- 级联删除（配合外键）
DELETE FROM users CASCADE;  -- 外键设置后自动级联

五、索引与性能优化

索引类型

sql

-- 主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 普通索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 复合索引
ALTER TABLE articles ADD INDEX idx_user_date (user_id, created_at);

-- 全文索引（InnoDB/MyISAM）
ALTER TABLE articles ADD FULLTEXT INDEX idx_fulltext (title, content);

-- 使用全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL 优化' IN NATURAL LANGUAGE MODE);

-- 查看表的所有索引
SHOW INDEX FROM users;

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

SQL性能优化

sql

-- 避免SELECT *
SELECT id, username, email FROM users WHERE id = 1;

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = '张三';
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- 优化示例
-- 优化前
SELECT * FROM articles a, users u 
WHERE a.user_id = u.id AND u.status = 1;

-- 优化后（确保有索引）
SELECT a.id, a.title, u.username 
FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE u.status = 1;

-- 使用LIMIT限制结果
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10;

-- 分页优化（大数据量）
-- 低效
SELECT * FROM articles LIMIT 1000000, 10;

-- 高效
SELECT a.* 
FROM articles a
INNER JOIN (SELECT id FROM articles ORDER BY created_at DESC LIMIT 1000000, 10) b
ON a.id = b.id;

慢查询日志

sql

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询
mysqldumpslow -s t /var/log/mysql/slow.log

六、事务处理

事务基础

sql

-- 开启事务
START TRANSACTION;

-- 或者
BEGIN;

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务隔离级别

sql

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

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

-- 示例：读已提交（Read Committed）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 示例：可重复读（Repeatable Read）- MySQL默认
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

--  Serializable（串行化）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

保存点

sql

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
SAVEPOINT after_item;

-- 回滚到保存点
ROLLBACK TO after_item;

-- 提交（只提交保存点之前的内容）
COMMIT;

七、视图和存储过程

视图

sql

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, age
FROM users
WHERE status = 1;

-- 创建复杂视图
CREATE VIEW user_articles_stats AS
SELECT 
    u.id,
    u.username,
    COUNT(a.id) as article_count,
    SUM(COALESCE(a.views, 0)) as total_views
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
WHERE u.status = 1
GROUP BY u.id, u.username;

-- 使用视图
SELECT * FROM active_users;
SELECT * FROM user_articles_stats WHERE article_count > 5;

-- 修改视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;

-- 删除视图
DROP VIEW IF EXISTS active_users;

存储过程

sql

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE get_user_stats(IN user_id INT, OUT total_articles INT)
BEGIN
    SELECT COUNT(*) INTO total_articles
    FROM articles
    WHERE user_id = user_id;
END //

DELIMITER ;

-- 调用存储过程
CALL get_user_stats(1, @total);
SELECT @total;

-- 创建带事务的存储过程
DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_user INT,
    IN to_user INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction failed' as message;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts SET balance = balance - amount WHERE user_id = from_user;
    UPDATE accounts SET balance = balance + amount WHERE user_id = to_user;
    
    COMMIT;
    SELECT 'Transfer successful' as message;
END //

DELIMITER ;

-- 调用
CALL transfer_money(1, 2, 100.00);

-- 删除存储过程
DROP PROCEDURE IF EXISTS get_user_stats;

八、用户权限管理

用户操作

sql

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

-- 重命名用户
RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';

-- 删除用户
DROP USER 'newuser'@'localhost';

-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'newuser'@'localhost' = 'new_password';

权限管理

sql

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'newuser'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'localhost';
GRANT EXECUTE ON PROCEDURE myapp.transfer_money TO 'newuser'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'newuser'@'localhost';
SHOW GRANTS;

-- 撤销权限
REVOKE INSERT, UPDATE ON myapp.* FROM 'newuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

九、主从复制配置

主服务器配置

ini

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp
expire_logs_days = 7
max_binlog_size = 100M

sql

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- 查看主服务器状态
SHOW MASTER STATUS;

从服务器配置

ini

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
read_only = 1

sql

-- 配置主从复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'repl',
    MASTER_PASSWORD = 'repl_password',
    MASTER_LOG_FILE = 'mysql-bin.000001',
    MASTER_LOG_POS = 154;

-- 启动复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G;

-- 停止复制
STOP SLAVE;

十、备份与恢复

逻辑备份

bash

# 备份单个数据库
mysqldump -u root -p myapp > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份指定表
mysqldump -u root -p myapp users articles > tables.sql

# 压缩备份
mysqldump -u root -p myapp | gzip > backup.sql.gz

# 备份时锁定表（MyISAM）
mysqldump -u root -p --lock-tables myapp > backup.sql

# 不锁定表（InnoDB）
mysqldump -u root -p --single-transaction myapp > backup.sql

数据恢复

bash

# 恢复数据库
mysql -u root -p myapp < backup.sql

# 恢复压缩文件
gunzip < backup.sql.gz | mysql -u root -p myapp

# 从备份文件中恢复指定表
mysql -u root -p myapp < tables.sql

物理备份（XtraBackup）

bash

# 安装XtraBackup
apt install percona-xtrabackup-80

# 全量备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=xxx

# 准备备份
xtrabackup --prepare --target-dir=/backup/full

# 恢复备份
xtrabackup --copy-back --target-dir=/backup/full

十一、常见问题与解决方案

连接问题

sql

-- 解决：允许远程连接
GRANT ALL PRIVILEGES ON myapp.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

-- 检查用户主机限制
SELECT user, host FROM mysql.user WHERE user = 'root';

性能问题

sql

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 查看查询缓存
SHOW VARIABLES LIKE 'query_cache%';

-- 优化表
OPTIMIZE TABLE users;

-- 分析表统计信息
ANALYZE TABLE users;

-- 检查表
CHECK TABLE users;

字符集问题

sql

-- 查看字符集设置
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- 批量修改字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

十二、总结

本教程全面介绍了MySQL数据库的各个方面：

• 安装配置和环境搭建
• 数据库和数据表操作
• SQL查询语句（基础和高级）
• 数据增删改查操作
• 索引创建和性能优化
• 事务处理和隔离级别
• 视图和存储过程
• 用户权限管理
• 主从复制配置
• 备份恢复策略

掌握这些知识后，你已经能够独立完成MySQL数据库的设计、开发和管理工作。推荐进一步学习MySQL 8.0的新特性、分库分表方案、数据库中间件（如MyCat、ShardingSphere）以及云数据库服务等内容。

相关资源：

• Shell脚本编程教程
• Nginx配置与优化实战
• Linux命令行实战指南

阅读更多：

• RESTful API接口设计规范
• Docker容器化部署教程