GF-MySQL

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

1.1 连接MySQL服务器

# 使用默认端口连接
mysql -u root -p

# 指定主机和端口连接
mysql -h localhost -P 3306 -u root -p

# 直接连接到指定数据库
mysql -u root -p testdb
        

1.2 数据库操作

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

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

-- 使用数据库
USE testdb;

-- 删除数据库
DROP DATABASE IF EXISTS testdb;
        

Part02-生产环境规划与建议

2.1 创建表

-- 创建用户表
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,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        

2.2 修改表结构

-- 添加列
ALTER TABLE users ADD COLUMN age INT;

-- 修改列
ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;

-- 删除列
ALTER TABLE users DROP COLUMN age;

-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);

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

2.3 查看表结构

-- 查看表结构
DESCRIBE users;

-- 查看表创建语句
SHOW CREATE TABLE users;

-- 查看表索引
SHOW INDEX FROM users;
        

Part03-生产环境项目实施方案

3.1 插入数据

-- 插入单条数据
INSERT INTO users (username, email, password) 
VALUES ('admin', 'admin@fgedu.net.cn', 'password123');

-- 插入多条数据
INSERT INTO users (username, email, password) 
VALUES 
('user1', 'user1@fgedu.net.cn', 'pass123'),
('user2', 'user2@fgedu.net.cn', 'pass456'),
('user3', 'user3@fgedu.net.cn', 'pass789');

-- 插入订单数据
INSERT INTO orders (user_id, total_amount, status) 
VALUES (1, 100.50, 'completed'),
       (2, 200.75, 'pending'),
       (1, 50.25, 'completed');
        

3.2 查询数据

-- 查询所有数据
SELECT * FROM users;

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

-- 带条件查询
SELECT * FROM users WHERE id > 1;

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 限制结果数量
SELECT * FROM users LIMIT 2;

-- 分页查询
SELECT * FROM users LIMIT 0, 10; -- 第1页
SELECT * FROM users LIMIT 10, 10; -- 第2页
        

3.3 连接查询

-- 内连接
SELECT u.id, u.username, o.id AS order_id, o.total_amount, o.status 
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接
SELECT u.id, u.username, o.id AS order_id, o.total_amount 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接
SELECT u.id, u.username, o.id AS order_id, o.total_amount 
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
        

3.4 更新数据

-- 更新单条数据
UPDATE users SET email = 'newemail@fgedu.net.cn' WHERE id = 1;

-- 批量更新
UPDATE users SET password = 'newpassword' WHERE id > 2;

-- 更新订单状态
UPDATE orders SET status = 'completed' WHERE id = 2;
        

3.5 删除数据

-- 删除单条数据
DELETE FROM users WHERE id = 3;

-- 批量删除
DELETE FROM orders WHERE status = 'pending';

-- 清空表数据（保留表结构）
TRUNCATE TABLE orders;
        

Part04-生产案例与实战讲解

4.1 聚合函数

-- 统计用户数量
SELECT COUNT(*) AS user_count FROM users;

-- 计算订单总金额
SELECT SUM(total_amount) AS total_revenue FROM orders;

-- 计算平均订单金额
SELECT AVG(total_amount) AS avg_order_amount FROM orders;

-- 查找最大和最小订单金额
SELECT MAX(total_amount) AS max_order, MIN(total_amount) AS min_order FROM orders;
        

4.2 分组查询

-- 按状态分组统计订单数量
SELECT status, COUNT(*) AS order_count 
FROM orders 
GROUP BY status;

-- 按用户分组统计订单总金额
SELECT user_id, SUM(total_amount) AS user_total 
FROM orders 
GROUP BY user_id 
HAVING SUM(total_amount) > 100;
        

4.3 子查询

-- 子查询获取订单金额大于平均金额的订单
SELECT * FROM orders 
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

-- 子查询获取有订单的用户
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders);
        

4.4 联合查询

-- 联合两个查询结果
SELECT id, username, 'user' AS type FROM users
UNION
SELECT id, CONCAT('Order-', id), 'order' AS type FROM orders;
        

Part05-风哥经验总结与分享

-- 开始事务
START TRANSACTION;

-- 执行操作
INSERT INTO users (username, email, password) VALUES ('transuser', 'trans@fgedu.net.cn', 'transpass');
INSERT INTO orders (user_id, total_amount, status) VALUES (LAST_INSERT_ID(), 150.00, 'pending');

-- 提交事务
COMMIT;

-- 回滚事务（如果需要）
-- ROLLBACK;
        

6. 索引优化示例

6.1 查看执行计划

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'admin';

-- 查看带索引的执行计划
EXPLAIN SELECT * FROM users WHERE email = 'admin@fgedu.net.cn';
        

6.2 索引使用建议

-- 为频繁查询的列创建索引
CREATE INDEX idx_email ON users(email);

-- 为外键列创建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 创建复合索引
CREATE INDEX idx_status_amount ON orders(status, total_amount);
        

7. 存储过程示例

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
    SELECT o.id, o.total_amount, o.status, o.created_at
    FROM orders o
    WHERE o.user_id = user_id
    ORDER BY o.created_at DESC;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserOrders(1);

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

8. 触发器示例

-- 创建触发器
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.password = MD5(NEW.password);
END //
DELIMITER ;

-- 测试触发器
INSERT INTO users (username, email, password) VALUES ('triggeruser', 'trigger@fgedu.net.cn', 'plainpassword');

-- 查看结果
SELECT id, username, password FROM users WHERE username = 'triggeruser';

-- 删除触发器
DROP TRIGGER IF EXISTS before_user_insert;
        

9. 视图示例

-- 创建视图
CREATE VIEW user_order_summary AS
SELECT u.id AS user_id,
       u.username,
       COUNT(o.id) AS order_count,
       SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

-- 查询视图
SELECT * FROM user_order_summary;

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

10. 导入导出示例

10.1 导出数据

# 导出整个数据库
mysqldump -u root -p testdb > testdb.sql

# 导出特定表
mysqldump -u root -p testdb users orders > testdb_tables.sql

# 导出数据（不包含结构）
mysqldump -u root -p testdb --no-create-info > testdb_data.sql
        

10.2 导入数据

# 导入数据库
mysql -u root -p testdb < testdb.sql

# 导入数据到现有表
mysql -u root -p testdb --execute="LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;"
        

11. 备份恢复示例

11.1 逻辑备份

# 使用mysqldump备份
mysqldump -u root -p --single-transaction --routines --triggers testdb > testdb_backup.sql

# 恢复备份
mysql -u root -p testdb < testdb_backup.sql
        

11.2 物理备份

# 使用xtrabackup备份
xtrabackup --backup --target-dir=/backup/testdb --user=root --password

# 恢复备份
xtrabackup --prepare --target-dir=/backup/testdb
xtrabackup --copy-back --target-dir=/backup/testdb --datadir=/var/lib/mysql
        

12. 性能优化示例

12.1 慢查询分析

-- 启用慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志
SELECT * FROM mysql.slow_log;

-- 使用pt-query-digest分析慢查询
pt-query-digest /var/lib/mysql/slow.log
        

12.2 配置优化

-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

-- 临时修改配置
SET GLOBAL innodb_buffer_pool_size = 1024*1024*1024; -- 1GB
SET GLOBAL max_connections = 1000;

-- 永久修改（在my.cnf中）
[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 1000
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
        

13. 安全管理示例

13.1 用户管理

-- 创建用户
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'apppassword';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON testdb.* TO 'appuser'@'localhost';

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

-- 撤销权限
REVOKE UPDATE ON testdb.* FROM 'appuser'@'localhost';

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

13.2 密码管理

-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

-- 密码过期策略
ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 锁定用户
ALTER USER 'appuser'@'localhost' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'appuser'@'localhost' ACCOUNT UNLOCK;
        

14. 监控与维护示例

14.1 服务器状态

-- 查看服务器状态
SHOW GLOBAL STATUS;

-- 查看连接数
SHOW GLOBAL STATUS LIKE 'Threads%';

-- 查看缓冲区使用情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
        

14.2 表维护

-- 检查表
CHECK TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 修复表
REPAIR TABLE users;

-- 分析表
ANALYZE TABLE users;
        

15. 常见问题解决方案

15.1 连接问题

-- 查看连接状态
SHOW PROCESSLIST;

-- 杀死长时间运行的查询
KILL [process_id];

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';
        

15.2 性能问题

-- 查看当前运行的查询
SHOW FULL PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS;

-- 查看慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
        

16. 实战案例

16.1 电商系统数据库操作

-- 创建产品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建分类表
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT
);

-- 创建订单详情表
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- 查询用户订单详情
SELECT o.id AS order_id,
       o.created_at,
       o.total_amount,
       o.status,
       p.name AS product_name,
       oi.quantity,
       oi.price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 1
ORDER BY o.created_at DESC;
        

16.2 博客系统数据库操作

-- 创建文章表
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id)
);

-- 创建评论表
CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查询文章及其评论
SELECT p.id,
       p.title,
       p.content,
       u.username AS author,
       c.id AS comment_id,
       c.content AS comment_content,
       cu.username AS commenter,
       c.created_at AS comment_time
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
LEFT JOIN users cu ON c.user_id = cu.id
WHERE p.id = 1
ORDER BY c.created_at ASC;