MySQL 详细教程

MySQL 是最流行的开源关系型数据库管理系统之一，广泛应用于Web应用开发。以下是MySQL的全面教程：

1. MySQL 简介

主要特点

• 关系型数据库：基于表结构存储数据
• 开源免费：社区版可免费使用
• 跨平台：支持多种操作系统
• 高性能：优化的查询引擎
• 可扩展：支持大型数据库
• 安全性：完善的用户权限系统
• 事务支持：ACID兼容

核心概念

• 数据库(Database)：数据容器
• 表(Table)：数据以行列形式存储
• 列(Column/Field)：表的字段
• 行(Row/Record)：表中的记录
• 主键(Primary Key)：唯一标识记录的字段
• 外键(Foreign Key)：关联其他表的字段

2. MySQL 安装

Linux 安装(Ubuntu/Debian)

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql
sudo mysql_secure_installation  # 安全配置

Linux 安装(CentOS/RHEL)

sudo yum install mysql-server
sudo systemctl start mysqld
sudo systemctl enable mysqld
sudo mysql_secure_installation

Mac 安装

brew install mysql
brew services start mysql
mysql_secure_installation

Windows 安装

1. 从官网下载MySQL Installer
2. 运行安装向导
3. 选择"Developer Default"安装类型
4. 完成安装并配置root密码

Docker 运行

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

3. MySQL 命令行客户端

连接MySQL

mysql -u username -p  # 本地连接
mysql -h hostname -u username -p  # 远程连接

常用命令

SHOW DATABASES;       -- 显示所有数据库
USE database_name;    -- 选择数据库
SHOW TABLES;          -- 显示当前数据库的所有表
DESCRIBE table_name;  -- 显示表结构
SOURCE file.sql;      -- 执行SQL文件
EXIT;                 -- 退出客户端

4. 数据库操作

创建数据库

CREATE DATABASE dbname;
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

删除数据库

DROP DATABASE dbname;

选择数据库

USE dbname;

5. 表操作

创建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常见数据类型

• 整数：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• 小数：FLOAT, DOUBLE, DECIMAL
• 字符串：CHAR, VARCHAR, TEXT, BLOB
• 日期时间：DATE, TIME, DATETIME, TIMESTAMP, YEAR
• 布尔：BOOLEAN(TINYINT(1)的别名)

修改表结构

ALTER TABLE users ADD COLUMN phone VARCHAR(20);  -- 添加列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);  -- 修改列
ALTER TABLE users DROP COLUMN phone;  -- 删除列
ALTER TABLE users RENAME TO customers;  -- 重命名表

删除表

DROP TABLE users;

清空表

TRUNCATE TABLE users;  -- 快速清空(不记录日志)

6. CRUD 操作

1. 插入数据(INSERT)

-- 插入单行
INSERT INTO users (username, password, email, age)
VALUES ('john_doe', 'secure123', 'john@example.com', 30);

-- 插入多行
INSERT INTO users (username, password, email, age) VALUES
('jane_doe', 'pass123', 'jane@example.com', 28),
('bob_smith', 'bobpass', 'bob@example.com', 35);

2. 查询数据(SELECT)

-- 查询所有列
SELECT * FROM users;

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

-- 条件查询
SELECT * FROM users WHERE age > 25;

-- 模糊查询
SELECT * FROM users WHERE username LIKE 'j%';

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

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 等价于 LIMIT 20, 10

-- 分组
SELECT age, COUNT(*) FROM users GROUP BY age;

-- 连接查询
SELECT u.username, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id;

3. 更新数据(UPDATE)

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

-- 更新多行
UPDATE users SET age = age + 1 WHERE age < 30;

4. 删除数据(DELETE)

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

-- 删除所有行(慎用)
DELETE FROM users;

7. 索引

创建索引

-- 单列索引
CREATE INDEX idx_username ON users(username);

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

-- 复合索引
CREATE INDEX idx_name_age ON users(username, age);

-- 主键索引(创建表时自动创建)

查看索引

SHOW INDEX FROM users;

删除索引

DROP INDEX idx_username ON users;

8. 约束

常用约束

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE,
    price DECIMAL(10,2) CHECK (price > 0),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

约束类型

• PRIMARY KEY - 主键约束
• UNIQUE - 唯一约束
• NOT NULL - 非空约束
• CHECK - 检查约束(MySQL 8.0+)
• DEFAULT - 默认值约束
• FOREIGN KEY - 外键约束

9. 事务处理

基本事务

START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 100.00);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
COMMIT;  -- 或 ROLLBACK;

事务隔离级别

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

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

10. 存储过程和函数

创建存储过程

DELIMITER //
CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END //
DELIMITER ;

-- 调用存储过程
CALL GetUserCount(@count);
SELECT @count;

创建函数

DELIMITER //
CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2)) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10,2);
    IF price > 100 THEN
        SET discount = price * 0.9;
    ELSE
        SET discount = price;
    END IF;
    RETURN discount;
END //
DELIMITER ;

-- 使用函数
SELECT name, price, CalculateDiscount(price) AS discounted_price FROM products;

11. 视图

创建视图

CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

使用视图

SELECT * FROM active_users;

修改视图

ALTER VIEW active_users AS
SELECT id, username, email, last_login
FROM users 
WHERE last_login > DATE_SUB(NOW(), INTERVAL 60 DAY);

删除视图

DROP VIEW active_users;

12. 触发器

创建触发器

DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

触发器类型

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

13. 用户和权限管理

创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

授予权限

-- 授予所有权限
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT ON dbname.* TO 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

撤销权限

REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';

删除用户

DROP USER 'username'@'localhost';

14. 备份与恢复

使用mysqldump备份

# 备份整个数据库
mysqldump -u username -p dbname > dbname_backup.sql

# 备份特定表
mysqldump -u username -p dbname table1 table2 > tables_backup.sql

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

恢复数据库

mysql -u username -p dbname < dbname_backup.sql

15. 性能优化

1. EXPLAIN分析查询

EXPLAIN SELECT * FROM users WHERE age > 25;

2. 优化索引

• 为常用查询条件创建索引
• 避免过多索引(影响写入性能)
• 使用复合索引优化多条件查询

3. 查询优化技巧

• 只查询需要的列
• 避免使用SELECT *
• 合理使用LIMIT分页
• 避免使用OR条件(考虑使用UNION)
• 避免在WHERE子句中使用函数

4. 配置优化

-- 查看配置变量
SHOW VARIABLES LIKE '%buffer%';

-- 临时设置变量
SET GLOBAL key_buffer_size = 1024*1024*64;

-- 永久配置(修改my.cnf/my.ini)
[mysqld]
key_buffer_size = 64M
innodb_buffer_pool_size = 1G

16. 常用客户端工具

1. MySQL Workbench - 官方GUI工具
2. phpMyAdmin - Web界面管理工具
3. HeidiSQL - Windows轻量级客户端
4. DBeaver - 跨平台数据库工具
5. Sequel Pro - Mac平台MySQL客户端

17. 编程语言连接示例

Python (PyMySQL)

import pymysql

connection = pymysql.connect(
    host='localhost',
    user='username',
    password='password',
    database='dbname'
)

try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM users WHERE age > %s"
        cursor.execute(sql, (25,))
        results = cursor.fetchall()
        for row in results:
            print(row)
finally:
    connection.close()

Node.js (mysql2)

const mysql = require('mysql2/promise');

async function queryDatabase() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'dbname'
  });

  const [rows] = await connection.execute('SELECT * FROM users WHERE age > ?', [25]);
  console.log(rows);

  await connection.end();
}

queryDatabase();

Java (JDBC)

import java.sql.*;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/dbname";
        String username = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            String sql = "SELECT * FROM users WHERE age > ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 25);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("username") + " " + rs.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

18. 常见应用场景实现

1. 分页查询

-- 传统分页(性能随offset增大而降低)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- 优化分页(使用WHERE子句)
SELECT * FROM products WHERE id > 20 ORDER BY id LIMIT 10;

2. 树形结构存储(邻接表)

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 查询子节点
SELECT * FROM categories WHERE parent_id = 1;

-- 递归查询(MySQL 8.0+)
WITH RECURSIVE category_tree AS (
    SELECT * FROM categories WHERE id = 1
    UNION ALL
    SELECT c.* FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

3. 全文搜索

-- 创建全文索引
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content)
) ENGINE=InnoDB;

-- 全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库教程' IN NATURAL LANGUAGE MODE);

4. 数据统计报表

-- 每日订单统计
SELECT 
    DATE(order_date) AS day,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE(order_date)
ORDER BY day DESC;

-- 月度销售趋势
SELECT 
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    SUM(amount) AS monthly_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

MySQL 是一个功能强大且灵活的关系型数据库，适用于各种规模的应用程序。通过本教程，您应该能够掌握MySQL的核心概念和基本操作，并能够在实际项目中应用这些知识。