一、MySQL简介

1. MySQL是什么？

• 定义：MySQL是一个开源的关系型数据库管理系统（RDBMS）
• 特点：性能高、成本低、可靠性强、易用性好
• 适用场景：Web应用、中小型系统、数据存储与分析
• 版本选择：MySQL 8.0（最新稳定版）或 MariaDB（MySQL分支）

2. 数据库基本概念

• 数据库：存储数据的仓库，由多个表组成
• 表：数据的二维结构，由行（记录）和列（字段）组成
• 字段：表的列，定义数据的类型和约束
• 记录：表的行，一条具体的数据
• 主键：唯一标识每条记录的字段
• 外键：关联两个表的字段

二、MySQL安装与配置

1. 安装方法

Windows系统

1. 官方安装包：从MySQL官网下载安装程序
2. 使用安装器：MySQL Installer（推荐）
3. 配置选项：
   • 选择安装类型：Developer Default（开发者默认）
   • 设置root用户密码（务必记住）
   • 配置端口（默认3306）

macOS系统

        1.使用Homebrew（推荐）：

brew install mysql
brew services start mysql

       2. 官方DMG安装包：从官网下载安装

Linux系统（Ubuntu/Debian）

# 安装
sudo apt update
sudo apt install mysql-server

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

# 安全配置
sudo mysql_secure_installation

2. 启动与连接

# 启动MySQL服务
sudo systemctl start mysql

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

# 查看服务状态
sudo systemctl status mysql

3. 配置文件

• Windows：my.ini 或 my.cnf
• Linux/macOS：/etc/my.cnf 或 /etc/mysql/my.cnf
• 常用配置：

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

三、数据库基本操作

1. 数据库管理

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

-- 创建数据库
CREATE DATABASE mydb;
CREATE DATABASE IF NOT EXISTS mydb;

-- 选择数据库
USE mydb;

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

-- 查看当前数据库
SELECT DATABASE();

-- 查看数据库创建语句
SHOW CREATE DATABASE mydb;

2. 数据类型

数值类型

-- 整数类型
TINYINT     -- 1字节 (-128~127 或 0~255)
SMALLINT    -- 2字节 (-32768~32767)
INT         -- 4字节 (-2^31~2^31-1)
BIGINT      -- 8字节 (-2^63~2^63-1)

-- 小数类型
DECIMAL(M,D)  -- 精确小数（如DECIMAL(10,2)）
FLOAT         -- 单精度浮点
DOUBLE        -- 双精度浮点

字符串类型

-- 定长字符串（填充空格）
CHAR(10)     -- 固定长度10字符

-- 变长字符串
VARCHAR(255) -- 最大255字符（推荐）

-- 文本类型
TINYTEXT     -- 最大255字符
TEXT         -- 最大65,535字符
MEDIUMTEXT   -- 最大16MB
LONGTEXT     -- 最大4GB

日期时间类型

DATE         -- 日期（YYYY-MM-DD）
TIME         -- 时间（HH:MM:SS）
DATETIME     -- 日期时间（YYYY-MM-DD HH:MM:SS）
TIMESTAMP    -- 时间戳（自动更新）
YEAR         -- 年份（1901-2155）

其他类型

BOOLEAN     -- 布尔值（实际存储为TINYINT(1)）
ENUM        -- 枚举（单选）
SET         -- 集合（多选）
JSON        -- JSON数据（MySQL 5.7+）

3. 表操作

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    age TINYINT UNSIGNED,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username)
);

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

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

-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改字段
ALTER TABLE users MODIFY COLUMN age SMALLINT;
ALTER TABLE users CHANGE COLUMN old_name new_name VARCHAR(50);

-- 删除字段
ALTER TABLE users DROP COLUMN phone;

-- 重命名表
ALTER TABLE users RENAME TO user_info;
RENAME TABLE users TO user_info;

-- 删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;

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

四、数据操作语言（DML）

1. 插入数据

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

-- 插入多条记录
INSERT INTO users (username, password, email, age) VALUES
('lisi', '123456', 'lisi@example.com', 30),
('wangwu', '123456', 'wangwu@example.com', 28);

-- 插入全部字段
INSERT INTO users VALUES (NULL, 'zhaoliu', '123456', 'zhaoliu@example.com', 35, NOW());

-- 插入时忽略重复（基于唯一索引）
INSERT IGNORE INTO users (username, password) VALUES ('zhangsan', '123456');

-- 插入时更新重复数据（基于唯一索引）
INSERT INTO users (username, password, email)
VALUES ('zhangsan', 'newpass', 'new@example.com')
ON DUPLICATE KEY UPDATE password='newpass', email='new@example.com';

2. 更新数据

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

-- 更新多条记录
UPDATE users SET age = age + 1 WHERE age < 18;

-- 更新多个字段
UPDATE users
SET email = 'new@example.com', age = 25
WHERE username = 'zhangsan';

-- 使用表达式更新
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- 安全更新（防止全表更新）
UPDATE users SET status = 1 WHERE id = 1;
-- 先执行SELECT确认影响行数
SELECT * FROM users WHERE id = 1;

3. 删除数据

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

-- 删除多条记录
DELETE FROM users WHERE age < 18;

-- 删除所有记录（谨慎使用！）
DELETE FROM users;

-- 使用TRUNCATE（更快，重置自增ID）
TRUNCATE TABLE users;

-- 使用JOIN删除（删除关联记录）
DELETE orders FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.status = 'deleted';

4. 查询数据（SELECT）

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

-- 条件查询（WHERE）
SELECT * FROM users WHERE age > 20;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE username LIKE 'zhang%';
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE age IN (20, 25, 30);

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

-- 限制结果
SELECT * FROM users LIMIT 10;           -- 前10条
SELECT * FROM users LIMIT 10, 20;       -- 跳过10条，取20条（分页）

-- 聚合函数
SELECT COUNT(*) AS total_users FROM users;
SELECT AVG(age) AS avg_age FROM users;
SELECT MAX(age) AS max_age, MIN(age) AS min_age FROM users;
SELECT SUM(salary) AS total_salary FROM employees;

-- 分组查询（GROUP BY）
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

-- 分组后筛选（HAVING）
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- 连接查询（JOIN）
-- 内连接
SELECT u.username, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

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

-- 自连接
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- 子查询
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 联合查询（UNION）
SELECT name FROM employees
UNION
SELECT name FROM managers;

-- 条件表达式（CASE WHEN）
SELECT
    username,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年人'
        ELSE '老年人'
    END AS age_group
FROM users;

五、查询优化基础

1. 索引管理

-- 查看索引
SHOW INDEX FROM users;

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

-- 复合索引
CREATE INDEX idx_age_email ON users(age, email);

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';

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

2. EXPLAIN分析

2. EXPLAIN分析

3. 常见优化技巧

-- 1. 避免SELECT *
SELECT id, username FROM users WHERE age > 20;

-- 2. 使用LIMIT分页
SELECT * FROM users LIMIT 0, 20;

-- 3. 避免在WHERE子句中使用函数
-- 不推荐：WHERE DATE(create_time) = '2024-01-01'
-- 推荐：WHERE create_time >= '2024-01-01 00:00:00'
--       AND create_time < '2024-01-02 00:00:00'

-- 4. 使用覆盖索引
SELECT username FROM users WHERE age > 20;  -- 如果有索引(age, username)

-- 5. 避免全表扫描
-- 确保WHERE条件字段有索引

六、事务与并发控制

1. 事务基础

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点
SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT savepoint_name;

-- 示例：银行转账
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

2. 事务隔离级别

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

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

-- 四种隔离级别
-- READ UNCOMMITTED: 读未提交（脏读、不可重复读、幻读）
-- READ COMMITTED:   读已提交（不可重复读、幻读）【MySQL默认】
-- REPEATABLE READ:  可重复读（幻读）【InnoDB默认】
-- SERIALIZABLE:     串行化（无并发问题）

-- 示例：设置可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

3. 锁机制

-- 查看锁信息
SHOW ENGINE INNODB STATUS;

-- 显式加锁
LOCK TABLES users READ, orders WRITE;
UNLOCK TABLES;

-- 行锁演示（InnoDB）
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 排他锁
-- 其他会话无法修改id=1的记录
COMMIT;

-- 共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

七、用户与权限管理

1. 用户管理

-- 查看用户
SELECT user, host FROM mysql.user;

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password123';  -- 允许任何主机

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

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

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

2. 权限管理

-- 授予权限
GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'newuser'@'localhost';
GRANT SELECT ON mydb.* TO 'readuser'@'%';

-- 回收权限
REVOKE INSERT, UPDATE ON mydb.users FROM 'newuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 常用权限
-- ALL PRIVILEGES: 所有权限
-- SELECT: 查询
-- INSERT: 插入
-- UPDATE: 更新
-- DELETE: 删除
-- CREATE: 创建数据库/表
-- DROP: 删除数据库/表
-- GRANT OPTION: 转授权限

3. 安全建议

-- 1. 为每个应用创建专用用户
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'strong_password';

-- 2. 限制最小权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'webapp'@'localhost';

-- 3. 禁用root远程登录（生产环境）
UPDATE mysql.user SET host='localhost' WHERE user='root';
FLUSH PRIVILEGES;

-- 4. 定期检查用户
SELECT user, host, authentication_string FROM mysql.user;

八、备份与恢复

1. 备份方法

使用mysqldump（推荐）

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

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

# 备份特定表
mysqldump -u root -p mydb users orders > tables_backup.sql

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

# 带时间戳的备份
mysqldump -u root -p mydb > mydb_$(date +%Y%m%d_%H%M%S).sql

使用MySQL Enterprise Backup（商业版）

# 物理备份
mysqlbackup --user=root --password=backup \
    --backup-dir=/backup/full \
    backup-to-image

2. 恢复方法

# 从SQL文件恢复
mysql -u root -p mydb < mydb_backup.sql

# 从压缩文件恢复
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# 恢复所有数据库
mysql -u root -p < all_backup.sql

# 恢复特定表
mysql -u root -p mydb < tables_backup.sql

3. 自动备份脚本（Linux）

#!/bin/bash
# backup.sh

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
USER="root"
PASSWORD="your_password"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $USER -p$PASSWORD $DB_NAME > $BACKUP_DIR/$DB_NAME_$DATE.sql

# 压缩
gzip $BACKUP_DIR/$DB_NAME_$DATE.sql

# 保留最近7天的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

# 日志记录
echo "[$(date)] MySQL备份完成: $DB_NAME_$DATE.sql.gz" >> /var/log/mysql_backup.log

九、PHP连接MySQL示例

1. 使用MySQLi扩展

<?php
// 连接数据库
$servername = "localhost";
$username = "root";
$password = "your_password";
$dbname = "mydb";

// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 设置字符集
$conn->set_charset("utf8mb4");

// 查询数据
$sql = "SELECT id, username, email FROM users WHERE age > ? ORDER BY id LIMIT ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $minAge, $limit);

$minAge = 20;
$limit = 10;

$stmt->execute();
$result = $stmt->get_result();

// 处理结果
while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . " - Name: " . $row["username"] . "<br>";
}

// 插入数据
$insert_sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
$stmt = $conn->prepare($insert_sql);
$stmt->bind_param("ssi", $username, $email, $age);

$username = "newuser";
$email = "new@example.com";
$age = 25;

if ($stmt->execute()) {
    echo "新记录插入成功，ID: " . $stmt->insert_id;
} else {
    echo "错误: " . $stmt->error;
}

// 关闭连接
$stmt->close();
$conn->close();
?>

2. 使用PDO扩展（推荐）

<?php
try {
    // 创建PDO连接
    $dsn = "mysql:host=localhost;dbname=mydb;charset=utf8mb4";
    $username = "root";
    $password = "your_password";

    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 查询数据
    $stmt = $pdo->prepare("SELECT * FROM users WHERE age > :age");
    $stmt->execute(['age' => 20]);

    // 获取结果
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($users as $user) {
        echo $user['username'] . "<br>";
    }

    // 插入数据（事务示例）
    $pdo->beginTransaction();

    $stmt = $pdo->prepare("INSERT INTO users (username, email, age) VALUES (?, ?, ?)");
    $stmt->execute(['testuser', 'test@example.com', 30]);

    $stmt = $pdo->prepare("INSERT INTO logs (action, user_id) VALUES (?, ?)");
    $stmt->execute(['注册', $pdo->lastInsertId()]);

    $pdo->commit();

} catch (PDOException $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "数据库错误: " . $e->getMessage();
}
?>

十、常见问题与调试

1. 连接问题

// 错误诊断
try {
    $pdo = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    echo "连接错误: " . $e->getMessage();

    // 常见原因：
    // 1. MySQL服务未启动
    // 2. 用户名/密码错误
    // 3. 防火墙阻止3306端口
    // 4. 数据库不存在
}

2. 查询性能问题

-- 检查慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（临时）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录

-- 查看当前进程
SHOW PROCESSLIST;

-- 查看锁信息
SHOW ENGINE INNODB STATUS;

3. 数据一致性检查

-- 检查表是否损坏
CHECK TABLE users;

-- 修复表（MyISAM）
REPAIR TABLE users;

-- 分析表（更新统计信息）
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

十一、学习路径与建议

1. 学习顺序建议

1. 基础操作（1-2周）
   • 数据库/表创建与管理
   • 增删改查（CRUD）
   • 数据类型与约束
2. 查询进阶（1-2周）
   • 多表连接（JOIN）
   • 子查询与聚合函数
   • 分组与排序
3. 高级特性（2-3周）
   • 事务与锁机制
   • 索引与查询优化
   • 用户权限管理
4. 实战应用（持续）
   • PHP/Python连接数据库
   • 备份恢复策略
   • 性能调优

2. 推荐练习项目

1. 用户管理系统：注册、登录、权限管理
2. 博客系统：文章、评论、分类标签
3. 电商系统：商品、订单、购物车
4. 任务管理系统：任务分配、进度跟踪

3. 常用工具

• 图形界面：MySQL Workbench、phpMyAdmin、Navicat
• 命令行：mysql、mysqladmin
• 备份工具：mysqldump、Percona XtraBackup
• 监控工具：Prometheus + Grafana、MySQL Enterprise Monitor

总结

MySQL是Web开发的基石，掌握基础操作是第一步。记住：

1. 多动手：每个命令都亲自执行一遍
2. 多思考：理解为什么这样设计，而不是死记硬背
3. 多实践：通过项目巩固知识
4. 多总结：遇到问题记录解决方案

从创建数据库开始，一步步实践，遇到问题查阅文档，你一定能成为MySQL高手！