1. MySQL简介与安装配置

什么是MySQL？

MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前属于Oracle公司。它使用结构化查询语言（SQL）进行数据库管理，具有以下特点：

• 开源免费：社区版完全免费
• 高性能：支持高并发访问
• 可扩展性：支持集群和分布式部署
• 跨平台：支持Windows、Linux、macOS等操作系统
• 安全性高：提供完善的权限管理和数据加密

安装MySQL（以Ubuntu为例）

# 更新包列表
sudo apt update

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

# 启动MySQL服务
sudo systemctl start mysql

# 设置开机自启
sudo systemctl enable mysql

# 运行安全配置脚本
sudo mysql_secure_installation

连接MySQL

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

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

2. 数据库基本操作

创建数据库

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

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

-- 使用数据库
USE school_db;

-- 删除数据库（谨慎操作）
-- DROP DATABASE school_db;

数据类型介绍

MySQL支持多种数据类型：

类型分类	常用类型	说明
数值类型	INT, DECIMAL, FLOAT	整数、小数
字符串类型	VARCHAR, CHAR, TEXT	变长、定长字符串
日期时间	DATE, TIME, DATETIME	日期和时间
二进制类型	BLOB	存储二进制数据

3. 数据表操作

创建学生表

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 100),
    gender ENUM('男', '女') DEFAULT '男',
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建课程表

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    teacher VARCHAR(50),
    credit DECIMAL(3,1) DEFAULT 2.0,
    start_date DATE,
    end_date DATE,
    INDEX idx_course_name (course_name)
);

创建选课关系表

CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score DECIMAL(5,2) CHECK (score >= 0 AND score <= 100),
    enroll_date DATE DEFAULT (CURDATE()),
    
    -- 外键约束
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    
    -- 复合唯一约束
    UNIQUE KEY uk_student_course (student_id, course_id)
);

表结构查看与修改

-- 查看表结构
DESC students;
SHOW CREATE TABLE students;

-- 添加列
ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改列
ALTER TABLE students MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- 删除列
ALTER TABLE students DROP COLUMN phone;

-- 重命名表
ALTER TABLE students RENAME TO student_info;

4. 数据增删改查（CRUD）

插入数据

-- 插入单条数据
INSERT INTO students (name, age, gender, email) 
VALUES ('张三', 20, '男', 'zhangsan@example.com');

-- 插入多条数据
INSERT INTO students (name, age, gender, email) VALUES
('李四', 21, '女', 'lisi@example.com'),
('王五', 22, '男', 'wangwu@example.com'),
('赵六', 19, '女', 'zhaoliu@example.com');

-- 插入课程数据
INSERT INTO courses (course_name, teacher, credit) VALUES
('数据库原理', '张老师', 3.0),
('数据结构', '李老师', 4.0),
('操作系统', '王老师', 3.5);

-- 插入选课记录
INSERT INTO student_courses (student_id, course_id, score) VALUES
(1, 1, 85.5),
(1, 2, 90.0),
(2, 1, 88.0),
(3, 3, 92.5);

查询数据

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

-- 查询指定列
SELECT id, name, age FROM students;

-- 条件查询
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE gender = '女' AND age < 22;

-- 模糊查询
SELECT * FROM students WHERE name LIKE '张%';
SELECT * FROM students WHERE email LIKE '%@example.com';

-- 排序
SELECT * FROM students ORDER BY age DESC;
SELECT * FROM students ORDER BY created_at DESC, name ASC;

-- 分页查询
SELECT * FROM students LIMIT 10 OFFSET 0;  -- 第1页
SELECT * FROM students LIMIT 10 OFFSET 10; -- 第2页

-- 聚合函数
SELECT COUNT(*) as total_students FROM students;
SELECT AVG(age) as avg_age FROM students;
SELECT MAX(age) as max_age, MIN(age) as min_age FROM students;
SELECT gender, COUNT(*) as count FROM students GROUP BY gender;

-- 分组查询
SELECT gender, AVG(age) as avg_age 
FROM students 
GROUP BY gender 
HAVING avg_age > 20;

更新数据

-- 更新单条记录
UPDATE students SET age = 21 WHERE id = 1;

-- 批量更新
UPDATE students SET updated_at = NOW() WHERE age > 20;

-- 使用CASE语句条件更新
UPDATE students 
SET age = CASE 
    WHEN age < 20 THEN age + 1
    WHEN age >= 20 THEN age
END;

删除数据

-- 删除指定记录
DELETE FROM students WHERE id = 5;

-- 删除所有记录（谨慎操作）
-- DELETE FROM students;

-- 清空表（重置自增ID）
-- TRUNCATE TABLE students;

5. 高级查询技巧

连接查询

-- 内连接
SELECT s.name, c.course_name, sc.score
FROM students s
INNER JOIN student_courses sc ON s.id = sc.student_id
INNER JOIN courses c ON sc.course_id = c.course_id;

-- 左连接
SELECT s.name, c.course_name, sc.score
FROM students s
LEFT JOIN student_courses sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id;

-- 右连接
SELECT s.name, c.course_name, sc.score
FROM students s
RIGHT JOIN student_courses sc ON s.id = sc.student_id
RIGHT JOIN courses c ON sc.course_id = c.course_id;

-- 全外连接（MySQL通过UNION实现）
SELECT s.name, c.course_name, sc.score
FROM students s
LEFT JOIN student_courses sc ON s.id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id
UNION
SELECT s.name, c.course_name, sc.score
FROM students s
RIGHT JOIN student_courses sc ON s.id = sc.student_id
RIGHT JOIN courses c ON sc.course_id = c.course_id
WHERE s.id IS NULL;

子查询

-- 标量子查询
SELECT name, age, 
    (SELECT AVG(age) FROM students) as avg_age
FROM students;

-- 列子查询
SELECT * FROM students 
WHERE age IN (SELECT age FROM students WHERE gender = '男');

-- 行子查询
SELECT * FROM students 
WHERE (age, gender) = (SELECT MAX(age), '男' FROM students);

-- 表子查询
SELECT s.name, c.course_name
FROM students s
JOIN (
    SELECT student_id, course_id 
    FROM student_courses 
    WHERE score > 90
) sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

窗口函数

-- 排名函数
SELECT 
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age DESC) as row_num,
    RANK() OVER (ORDER BY age DESC) as rank_num,
    DENSE_RANK() OVER (ORDER BY age DESC) as dense_rank_num
FROM students;

-- 聚合窗口函数
SELECT 
    name,
    age,
    AVG(age) OVER () as overall_avg_age,
    AVG(age) OVER (PARTITION BY gender) as gender_avg_age
FROM students;

-- 前后行比较
SELECT 
    name,
    age,
    LAG(age) OVER (ORDER BY age) as prev_age,
    LEAD(age) OVER (ORDER BY age) as next_age
FROM students;

6. 索引优化

创建索引

-- 创建普通索引
CREATE INDEX idx_student_name ON students(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_student_email ON students(email);

-- 创建复合索引
CREATE INDEX idx_student_age_gender ON students(age, gender);

-- 创建全文索引（适用于文本搜索）
CREATE FULLTEXT INDEX idx_student_name_fulltext ON students(name);

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

索引使用示例

-- 使用索引的查询
EXPLAIN SELECT * FROM students WHERE name = '张三';
EXPLAIN SELECT * FROM students WHERE age > 20 AND gender = '男';

-- 全文索引搜索
SELECT * FROM students 
WHERE MATCH(name) AGAINST('张*' IN BOOLEAN MODE);

-- 强制使用索引
SELECT * FROM students FORCE INDEX (idx_student_name) 
WHERE name LIKE '张%';

索引优化建议

1. 选择合适列：WHERE、JOIN、ORDER BY、GROUP BY中的列
2. 避免过多索引：每个索引都会增加写操作开销
3. 使用复合索引：遵循最左前缀原则
4. 定期分析索引：使用ANALYZE TABLE更新索引统计信息

7. 事务与锁机制

事务基本操作

-- 开始事务
START TRANSACTION;

-- 或使用
BEGIN;

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务隔离级别

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

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

-- 四种隔离级别
-- READ UNCOMMITTED   读未提交
-- READ COMMITTED     读已提交
-- REPEATABLE READ    可重复读（MySQL默认）
-- SERIALIZABLE       串行化

锁机制示例

-- 共享锁（读锁）
SELECT * FROM students WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）
SELECT * FROM students WHERE id = 1 FOR UPDATE;

-- 表级锁
LOCK TABLES students READ;  -- 读锁
LOCK TABLES students WRITE; -- 写锁
UNLOCK TABLES;              -- 释放锁

8. 存储过程与函数

创建存储过程

DELIMITER //

CREATE PROCEDURE GetStudentInfo(IN student_id INT)
BEGIN
    SELECT s.name, s.age, s.gender, c.course_name, sc.score
    FROM students s
    LEFT JOIN student_courses sc ON s.id = sc.student_id
    LEFT JOIN courses c ON sc.course_id = c.course_id
    WHERE s.id = student_id;
END //

DELIMITER ;

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

创建函数

DELIMITER //

CREATE FUNCTION CalculateGrade(score DECIMAL(5,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE grade VARCHAR(10);
    
    IF score >= 90 THEN SET grade = '优秀';
    ELSEIF score >= 80 THEN SET grade = '良好';
    ELSEIF score >= 70 THEN SET grade = '中等';
    ELSEIF score >= 60 THEN SET grade = '及格';
    ELSE SET grade = '不及格';
    END IF;
    
    RETURN grade;
END //

DELIMITER ;

-- 使用函数
SELECT name, score, CalculateGrade(score) as grade 
FROM student_courses sc
JOIN students s ON sc.student_id = s.id;

触发器示例

DELIMITER //

CREATE TRIGGER UpdateStudentCount
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    UPDATE statistics 
    SET student_count = student_count + 1,
        last_update = NOW()
    WHERE id = 1;
END //

DELIMITER ;

9. Python连接MySQL示例

安装MySQL驱动

pip install mysql-connector-python
# 或
pip install pymysql

基本连接与操作

import mysql.connector
from mysql.connector import Error

def connect_to_mysql():
    """连接MySQL数据库"""
    try:
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='your_password',
            database='school_db'
        )
        
        if connection.is_connected():
            print("成功连接到MySQL数据库")
            return connection
            
    except Error as e:
        print(f"连接失败: {e}")
        return None

def create_table(connection):
    """创建表"""
    try:
        cursor = connection.cursor()
        
        create_table_query = """
        CREATE TABLE IF NOT EXISTS employees (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            position VARCHAR(100),
            salary DECIMAL(10, 2),
            hire_date DATE
        )
        """
        
        cursor.execute(create_table_query)
        connection.commit()
        print("表创建成功")
        
    except Error as e:
        print(f"创建表失败: {e}")

def insert_data(connection):
    """插入数据"""
    try:
        cursor = connection.cursor()
        
        insert_query = """
        INSERT INTO employees (name, position, salary, hire_date)
        VALUES (%s, %s, %s, %s)
        """
        
        employees = [
            ('张三', '工程师', 15000.00, '2023-01-15'),
            ('李四', '经理', 25000.00, '2022-06-20'),
            ('王五', '设计师', 12000.00, '2023-03-10')
        ]
        
        cursor.executemany(insert_query, employees)
        connection.commit()
        print(f"插入了 {cursor.rowcount} 条记录")
        
    except Error as e:
        print(f"插入数据失败: {e}")

def query_data(connection):
    """查询数据"""
    try:
        cursor = connection.cursor(dictionary=True)  # 返回字典格式
        
        query = "SELECT * FROM employees WHERE salary > %s"
        cursor.execute(query, (13000,))
        
        results = cursor.fetchall()
        
        print("查询结果:")
        for row in results:
            print(f"ID: {row['id']}, 姓名: {row['name']}, 职位: {row['position']}, 薪资: {row['salary']}")
            
    except Error as e:
        print(f"查询失败: {e}")

def update_data(connection):
    """更新数据"""
    try:
        cursor = connection.cursor()
        
        update_query = "UPDATE employees SET salary = salary * 1.1 WHERE position = %s"
        cursor.execute(update_query, ('工程师',))
        connection.commit()
        
        print(f"更新了 {cursor.rowcount} 条记录")
        
    except Error as e:
        print(f"更新失败: {e}")

def main():
    """主函数"""
    connection = connect_to_mysql()
    
    if connection:
        try:
            create_table(connection)
            insert_data(connection)
            query_data(connection)
            update_data(connection)
            query_data(connection)  # 再次查询查看更新结果
            
        finally:
            if connection.is_connected():
                connection.close()
                print("数据库连接已关闭")

if __name__ == "__main__":
    main()

使用连接池

from mysql.connector import pooling

# 创建连接池
connection_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host='localhost',
    user='root',
    password='your_password',
    database='school_db'
)

# 从连接池获取连接
def get_connection_from_pool():
    try:
        connection = connection_pool.get_connection()
        return connection
    except Error as e:
        print(f"从连接池获取连接失败: {e}")
        return None

# 使用连接
connection = get_connection_from_pool()
if connection:
    # 执行数据库操作
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM students")
    results = cursor.fetchall()
    
    # 使用完毕后将连接返回连接池
    connection.close