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）以及云数据库服务等内容。

相关资源：

阅读更多：

一、MySQL基础入门

什么是MySQL？

安装MySQL

基本命令连接

二、数据库和数据表操作

创建数据库

创建数据表

数据类型详解

三、SQL查询语句

基础查询

聚合函数

多表查询

高级查询技巧

四、数据操作（CRUD）

插入数据

更新数据

删除数据

五、索引与性能优化

索引类型

SQL性能优化

慢查询日志

六、事务处理

事务基础

事务隔离级别

保存点

七、视图和存储过程

视图

存储过程

八、用户权限管理

用户操作

权限管理

九、主从复制配置

主服务器配置

从服务器配置

十、备份与恢复

逻辑备份

数据恢复

物理备份（XtraBackup）

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

连接问题

性能问题

字符集问题

十二、总结

评论

发表回复 取消回复

更多文章

币安为何持续扩大全球合规牌照布局？2026年深度解析

动画设计入门自学完全指南：从零基础到创作你的第一部动画作品

美国加密监管新框架会影响哪些交易所？2026年深度解析

币安最新Launchpool项目参与教程：从零开始挖新币

发表回复取消回复