MySQL数据库从入门到实战：核心概念与代码示例详解

1. MySQL简介与安装配置

什么是MySQL？

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

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

安装MySQL（以Ubuntu为例）

1. # 更新包列表
2. sudo apt update
4. # 安装MySQL服务器
5. sudo apt install mysql-server
7. # 启动MySQL服务
8. sudo systemctl start mysql
10. # 设置开机自启
11. sudo systemctl enable mysql
13. # 运行安全配置脚本
14. sudo mysql_secure_installation

复制代码

连接MySQL

1. # 命令行连接
2. mysql -u root -p
4. # 使用指定主机和端口连接
5. mysql -h localhost -P 3306 -u root -p

复制代码

2. 数据库基本操作

创建数据库

1. -- 创建数据库
2. CREATE DATABASE IF NOT EXISTS school_db;
4. -- 查看所有数据库
5. SHOW DATABASES;
7. -- 使用数据库
8. USE school_db;
10. -- 删除数据库（谨慎操作）
11. -- DROP DATABASE school_db;

复制代码

数据类型介绍

MySQL支持多种数据类型：

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

3. 数据表操作

创建学生表

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

复制代码

创建课程表

1. CREATE TABLE courses (
2. course_id INT PRIMARY KEY AUTO_INCREMENT,
3. course_name VARCHAR(100) NOT NULL,
4. teacher VARCHAR(50),
5. credit DECIMAL(3,1) DEFAULT 2.0,
6. start_date DATE,
7. end_date DATE,
8. INDEX idx_course_name (course_name)
9. );

复制代码

创建选课关系表

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

复制代码

表结构查看与修改

1. -- 查看表结构
2. DESC students;
3. SHOW CREATE TABLE students;
5. -- 添加列
6. ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER email;
8. -- 修改列
9. ALTER TABLE students MODIFY COLUMN name VARCHAR(100) NOT NULL;
11. -- 删除列
12. ALTER TABLE students DROP COLUMN phone;
14. -- 重命名表
15. ALTER TABLE students RENAME TO student_info;

复制代码

4. 数据增删改查（CRUD）

插入数据

1. -- 插入单条数据
2. INSERT INTO students (name, age, gender, email)
3. VALUES ('张三', 20, '男', 'zhangsan@example.com');
5. -- 插入多条数据
6. INSERT INTO students (name, age, gender, email) VALUES
7. ('李四', 21, '女', 'lisi@example.com'),
8. ('王五', 22, '男', 'wangwu@example.com'),
9. ('赵六', 19, '女', 'zhaoliu@example.com');
11. -- 插入课程数据
12. INSERT INTO courses (course_name, teacher, credit) VALUES
13. ('数据库原理', '张老师', 3.0),
14. ('数据结构', '李老师', 4.0),
15. ('操作系统', '王老师', 3.5);
17. -- 插入选课记录
18. INSERT INTO student_courses (student_id, course_id, score) VALUES
19. (1, 1, 85.5),
20. (1, 2, 90.0),
21. (2, 1, 88.0),
22. (3, 3, 92.5);

复制代码

查询数据

1. -- 查询所有列
2. SELECT * FROM students;
4. -- 查询指定列
5. SELECT id, name, age FROM students;
7. -- 条件查询
8. SELECT * FROM students WHERE age > 20;
9. SELECT * FROM students WHERE gender = '女' AND age < 22;
11. -- 模糊查询
12. SELECT * FROM students WHERE name LIKE '张%';
13. SELECT * FROM students WHERE email LIKE '%@example.com';
15. -- 排序
16. SELECT * FROM students ORDER BY age DESC;
17. SELECT * FROM students ORDER BY created_at DESC, name ASC;
19. -- 分页查询
20. SELECT * FROM students LIMIT 10 OFFSET 0; -- 第1页
21. SELECT * FROM students LIMIT 10 OFFSET 10; -- 第2页
23. -- 聚合函数
24. SELECT COUNT(*) as total_students FROM students;
25. SELECT AVG(age) as avg_age FROM students;
26. SELECT MAX(age) as max_age, MIN(age) as min_age FROM students;
27. SELECT gender, COUNT(*) as count FROM students GROUP BY gender;
29. -- 分组查询
30. SELECT gender, AVG(age) as avg_age
31. FROM students
32. GROUP BY gender
33. HAVING avg_age > 20;

复制代码

更新数据

1. -- 更新单条记录
2. UPDATE students SET age = 21 WHERE id = 1;
4. -- 批量更新
5. UPDATE students SET updated_at = NOW() WHERE age > 20;
7. -- 使用CASE语句条件更新
8. UPDATE students
9. SET age = CASE
10. WHEN age < 20 THEN age + 1
11. WHEN age >= 20 THEN age
12. END;

复制代码

删除数据

1. -- 删除指定记录
2. DELETE FROM students WHERE id = 5;
4. -- 删除所有记录（谨慎操作）
5. -- DELETE FROM students;
7. -- 清空表（重置自增ID）
8. -- TRUNCATE TABLE students;

复制代码

5. 高级查询技巧

连接查询

1. -- 内连接
2. SELECT s.name, c.course_name, sc.score
3. FROM students s
4. INNER JOIN student_courses sc ON s.id = sc.student_id
5. INNER JOIN courses c ON sc.course_id = c.course_id;
7. -- 左连接
8. SELECT s.name, c.course_name, sc.score
9. FROM students s
10. LEFT JOIN student_courses sc ON s.id = sc.student_id
11. LEFT JOIN courses c ON sc.course_id = c.course_id;
13. -- 右连接
14. SELECT s.name, c.course_name, sc.score
15. FROM students s
16. RIGHT JOIN student_courses sc ON s.id = sc.student_id
17. RIGHT JOIN courses c ON sc.course_id = c.course_id;
19. -- 全外连接（MySQL通过UNION实现）
20. SELECT s.name, c.course_name, sc.score
21. FROM students s
22. LEFT JOIN student_courses sc ON s.id = sc.student_id
23. LEFT JOIN courses c ON sc.course_id = c.course_id
24. UNION
25. SELECT s.name, c.course_name, sc.score
26. FROM students s
27. RIGHT JOIN student_courses sc ON s.id = sc.student_id
28. RIGHT JOIN courses c ON sc.course_id = c.course_id
29. WHERE s.id IS NULL;

复制代码

子查询

1. -- 标量子查询
2. SELECT name, age,
3. (SELECT AVG(age) FROM students) as avg_age
4. FROM students;
6. -- 列子查询
7. SELECT * FROM students
8. WHERE age IN (SELECT age FROM students WHERE gender = '男');
10. -- 行子查询
11. SELECT * FROM students
12. WHERE (age, gender) = (SELECT MAX(age), '男' FROM students);
14. -- 表子查询
15. SELECT s.name, c.course_name
16. FROM students s
17. JOIN (
18. SELECT student_id, course_id
19. FROM student_courses
20. WHERE score > 90
21. ) sc ON s.id = sc.student_id
22. JOIN courses c ON sc.course_id = c.course_id;

复制代码

窗口函数

1. -- 排名函数
2. SELECT
3. name,
4. age,
5. ROW_NUMBER() OVER (ORDER BY age DESC) as row_num,
6. RANK() OVER (ORDER BY age DESC) as rank_num,
7. DENSE_RANK() OVER (ORDER BY age DESC) as dense_rank_num
8. FROM students;
10. -- 聚合窗口函数
11. SELECT
12. name,
13. age,
14. AVG(age) OVER () as overall_avg_age,
15. AVG(age) OVER (PARTITION BY gender) as gender_avg_age
16. FROM students;
18. -- 前后行比较
19. SELECT
20. name,
21. age,
22. LAG(age) OVER (ORDER BY age) as prev_age,
23. LEAD(age) OVER (ORDER BY age) as next_age
24. FROM students;

复制代码

6. 索引优化

创建索引

1. -- 创建普通索引
2. CREATE INDEX idx_student_name ON students(name);
4. -- 创建唯一索引
5. CREATE UNIQUE INDEX idx_student_email ON students(email);
7. -- 创建复合索引
8. CREATE INDEX idx_student_age_gender ON students(age, gender);
10. -- 创建全文索引（适用于文本搜索）
11. CREATE FULLTEXT INDEX idx_student_name_fulltext ON students(name);
13. -- 查看表索引
14. SHOW INDEX FROM students;

复制代码

索引使用示例

1. -- 使用索引的查询
2. EXPLAIN SELECT * FROM students WHERE name = '张三';
3. EXPLAIN SELECT * FROM students WHERE age > 20 AND gender = '男';
5. -- 全文索引搜索
6. SELECT * FROM students
7. WHERE MATCH(name) AGAINST('张*' IN BOOLEAN MODE);
9. -- 强制使用索引
10. SELECT * FROM students FORCE INDEX (idx_student_name)
11. WHERE name LIKE '张%';

复制代码

索引优化建议

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

7. 事务与锁机制

事务基本操作

1. -- 开始事务
2. START TRANSACTION;
4. -- 或使用
5. BEGIN;
7. -- 执行SQL操作
8. UPDATE accounts SET balance = balance - 100 WHERE id = 1;
9. UPDATE accounts SET balance = balance + 100 WHERE id = 2;
11. -- 提交事务
12. COMMIT;
14. -- 回滚事务
15. ROLLBACK;

复制代码

事务隔离级别

1. -- 查看当前隔离级别
2. SELECT @@transaction_isolation;
4. -- 设置隔离级别
5. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
6. SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
8. -- 四种隔离级别
9. -- READ UNCOMMITTED 读未提交
10. -- READ COMMITTED 读已提交
11. -- REPEATABLE READ 可重复读（MySQL默认）
12. -- SERIALIZABLE 串行化

复制代码

锁机制示例

1. -- 共享锁（读锁）
2. SELECT * FROM students WHERE id = 1 LOCK IN SHARE MODE;
4. -- 排他锁（写锁）
5. SELECT * FROM students WHERE id = 1 FOR UPDATE;
7. -- 表级锁
8. LOCK TABLES students READ; -- 读锁
9. LOCK TABLES students WRITE; -- 写锁
10. UNLOCK TABLES; -- 释放锁

复制代码

8. 存储过程与函数

创建存储过程

1. DELIMITER //
3. CREATE PROCEDURE GetStudentInfo(IN student_id INT)
4. BEGIN
5. SELECT s.name, s.age, s.gender, c.course_name, sc.score
6. FROM students s
7. LEFT JOIN student_courses sc ON s.id = sc.student_id
8. LEFT JOIN courses c ON sc.course_id = c.course_id
9. WHERE s.id = student_id;
10. END //
12. DELIMITER ;
14. -- 调用存储过程
15. CALL GetStudentInfo(1);

复制代码

创建函数

1. DELIMITER //
3. CREATE FUNCTION CalculateGrade(score DECIMAL(5,2))
4. RETURNS VARCHAR(10)
5. DETERMINISTIC
6. BEGIN
7. DECLARE grade VARCHAR(10);
9. IF score >= 90 THEN SET grade = '优秀';
10. ELSEIF score >= 80 THEN SET grade = '良好';
11. ELSEIF score >= 70 THEN SET grade = '中等';
12. ELSEIF score >= 60 THEN SET grade = '及格';
13. ELSE SET grade = '不及格';
14. END IF;
16. RETURN grade;
17. END //
19. DELIMITER ;
21. -- 使用函数
22. SELECT name, score, CalculateGrade(score) as grade
23. FROM student_courses sc
24. JOIN students s ON sc.student_id = s.id;

复制代码

触发器示例

1. DELIMITER //
3. CREATE TRIGGER UpdateStudentCount
4. AFTER INSERT ON students
5. FOR EACH ROW
6. BEGIN
7. UPDATE statistics
8. SET student_count = student_count + 1,
9. last_update = NOW()
10. WHERE id = 1;
11. END //
13. DELIMITER ;

复制代码

9. Python连接MySQL示例

安装MySQL驱动

1. pip install mysql-connector-python
2. # 或
3. pip install pymysql

复制代码

基本连接与操作

1. import mysql.connector
2. from mysql.connector import Error
4. def connect_to_mysql():
5. """连接MySQL数据库"""
6. try:
7. connection = mysql.connector.connect(
8. host='localhost',
9. user='root',
10. password='your_password',
11. database='school_db'
12. )
14. if connection.is_connected():
15. print("成功连接到MySQL数据库")
16. return connection
18. except Error as e:
19. print(f"连接失败: {e}")
20. return None
22. def create_table(connection):
23. """创建表"""
24. try:
25. cursor = connection.cursor()
27. create_table_query = """
28. CREATE TABLE IF NOT EXISTS employees (
29. id INT AUTO_INCREMENT PRIMARY KEY,
30. name VARCHAR(100) NOT NULL,
31. position VARCHAR(100),
32. salary DECIMAL(10, 2),
33. hire_date DATE
34. )
35. """
37. cursor.execute(create_table_query)
38. connection.commit()
39. print("表创建成功")
41. except Error as e:
42. print(f"创建表失败: {e}")
44. def insert_data(connection):
45. """插入数据"""
46. try:
47. cursor = connection.cursor()
49. insert_query = """
50. INSERT INTO employees (name, position, salary, hire_date)
51. VALUES (%s, %s, %s, %s)
52. """
54. employees = [
55. ('张三', '工程师', 15000.00, '2023-01-15'),
56. ('李四', '经理', 25000.00, '2022-06-20'),
57. ('王五', '设计师', 12000.00, '2023-03-10')
58. ]
60. cursor.executemany(insert_query, employees)
61. connection.commit()
62. print(f"插入了 {cursor.rowcount} 条记录")
64. except Error as e:
65. print(f"插入数据失败: {e}")
67. def query_data(connection):
68. """查询数据"""
69. try:
70. cursor = connection.cursor(dictionary=True) # 返回字典格式
72. query = "SELECT * FROM employees WHERE salary > %s"
73. cursor.execute(query, (13000,))
75. results = cursor.fetchall()
77. print("查询结果:")
78. for row in results:
79. print(f"ID: {row['id']}, 姓名: {row['name']}, 职位: {row['position']}, 薪资: {row['salary']}")
81. except Error as e:
82. print(f"查询失败: {e}")
84. def update_data(connection):
85. """更新数据"""
86. try:
87. cursor = connection.cursor()
89. update_query = "UPDATE employees SET salary = salary * 1.1 WHERE position = %s"
90. cursor.execute(update_query, ('工程师',))
91. connection.commit()
93. print(f"更新了 {cursor.rowcount} 条记录")
95. except Error as e:
96. print(f"更新失败: {e}")
98. def main():
99. """主函数"""
100. connection = connect_to_mysql()
102. if connection:
103. try:
104. create_table(connection)
105. insert_data(connection)
106. query_data(connection)
107. update_data(connection)
108. query_data(connection) # 再次查询查看更新结果
110. finally:
111. if connection.is_connected():
112. connection.close()
113. print("数据库连接已关闭")
115. if __name__ == "__main__":
116. main()

复制代码

使用连接池

1. from mysql.connector import pooling
3. # 创建连接池
4. connection_pool = pooling.MySQLConnectionPool(
5. pool_name="mypool",
6. pool_size=5,
7. host='localhost',
8. user='root',
9. password='your_password',
10. database='school_db'
11. )
13. # 从连接池获取连接
14. def get_connection_from_pool():
15. try:
16. connection = connection_pool.get_connection()
17. return connection
18. except Error as e:
19. print(f"从连接池获取连接失败: {e}")
20. return None
22. # 使用连接
23. connection = get_connection_from_pool()
24. if connection:
25. # 执行数据库操作
26. cursor = connection.cursor()
27. cursor.execute("SELECT * FROM students")
28. results = cursor.fetchall()
30. # 使用完毕后将连接返回连接池
31. connection.close

复制代码