MySQL教程

第一部分：MySQL 基础入门

1.1 MySQL 简介与安装

什么是 MySQL？

MySQL 是一个开源的关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。它使用 SQL 语言进行数据库管理。

MySQL 安装

Windows 安装：

下载 MySQL Installer
运行安装程序，选择"Developer Default"
配置 root 用户密码
完成安装

Linux 安装 (Ubuntu)：

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Mac 安装：

brew install mysql
brew services start mysql

1.2 数据库基本概念

核心概念：

数据库：数据的集合
表：数据以表格形式存储
列：表的字段
行：表的记录
主键：唯一标识每行的字段
外键：建立表间关系的字段

1.3 MySQL 基本操作

连接 MySQL：

mysql -u root -p

基本数据库操作：

-- 显示所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE mydatabase;

-- 使用数据库
USE mydatabase;

-- 删除数据库
DROP DATABASE mydatabase;

第二部分：SQL 语言基础

2.1 数据定义语言 (DDL)

创建表：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表结构：

-- 添加列
ALTER TABLE students ADD COLUMN phone VARCHAR(15);

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

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

-- 删除表
DROP TABLE students;

2.2 数据操作语言 (DML)

插入数据：

INSERT INTO students (name, age, email) 
VALUES ('张三', 20, 'zhangsan@email.com');

-- 批量插入
INSERT INTO students (name, age, email) VALUES
('李四', 22, 'lisi@email.com'),
('王五', 21, 'wangwu@email.com');

查询数据：

-- 基本查询
SELECT * FROM students;

-- 选择特定列
SELECT name, age FROM students;

-- 带条件的查询
SELECT * FROM students WHERE age > 20;

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

-- 限制结果数量
SELECT * FROM students LIMIT 5;

更新数据：

UPDATE students 
SET age = 23, email = 'new_email@email.com'
WHERE name = '张三';

删除数据：

DELETE FROM students WHERE id = 1;

-- 清空表
TRUNCATE TABLE students;

2.3 数据查询语言 (DQL) 进阶

聚合函数：

SELECT 
    COUNT(*) as total_students,
    AVG(age) as average_age,
    MAX(age) as max_age,
    MIN(age) as min_age,
    SUM(age) as total_age
FROM students;

分组查询：

-- 按年龄分组统计
SELECT age, COUNT(*) as count
FROM students
GROUP BY age
HAVING COUNT(*) > 1;

连接查询：

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

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

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

子查询：

-- 子查询作为条件
SELECT name 
FROM students 
WHERE age = (SELECT MAX(age) FROM students);

-- IN 子查询
SELECT name 
FROM students 
WHERE id IN (SELECT student_id FROM courses);

第三部分：MySQL 高级特性

3.1 索引优化

创建索引：

-- 单列索引
CREATE INDEX idx_name ON students(name);

-- 复合索引
CREATE INDEX idx_name_age ON students(name, age);

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

-- 查看索引
SHOW INDEX FROM students;

-- 删除索引
DROP INDEX idx_name ON students;

3.2 事务处理

事务基本操作：

START TRANSACTION;

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

-- 提交事务
COMMIT;

-- 或回滚事务
ROLLBACK;

事务隔离级别：

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

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

3.3 存储过程和函数

创建存储过程：

DELIMITER //

CREATE PROCEDURE GetStudentCount()
BEGIN
    SELECT COUNT(*) as total FROM students;
END //

DELIMITER ;

-- 调用存储过程
CALL GetStudentCount();

带参数的存储过程：

DELIMITER //

CREATE PROCEDURE GetStudentsByAge(IN min_age INT, IN max_age INT)
BEGIN
    SELECT * FROM students 
    WHERE age BETWEEN min_age AND max_age;
END //

DELIMITER ;

-- 调用
CALL GetStudentsByAge(20, 25);

创建函数：

DELIMITER //

CREATE FUNCTION GetStudentNameById(student_id INT)
RETURNS VARCHAR(100)
READS SQL DATA
BEGIN
    DECLARE student_name VARCHAR(100);
    SELECT name INTO student_name FROM students WHERE id = student_id;
    RETURN student_name;
END //

DELIMITER ;

-- 使用函数
SELECT GetStudentNameById(1);

3.4 触发器

创建触发器：

-- 创建日志表
CREATE TABLE student_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(10),
    student_id INT,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_logs (action, student_id)
    VALUES ('INSERT', NEW.id);
END //

DELIMITER ;

第四部分：数据库设计与优化

4.1 数据库规范化

第一范式 (1NF)：

每个字段都是原子的，不可再分
每行数据有唯一标识

第二范式 (2NF)：

满足 1NF
所有非主键字段完全依赖于主键

第三范式 (3NF)：

满足 2NF
所有非主键字段不传递依赖于主键

4.2 性能优化技巧

查询优化：

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM students WHERE age > 20;

-- 避免 SELECT *
SELECT id, name FROM students;

-- 使用 LIMIT 限制结果集
SELECT * FROM students LIMIT 100;

索引优化策略：

-- 为经常查询的字段创建索引
CREATE INDEX idx_created_at ON students(created_at);

-- 为外键创建索引
CREATE INDEX idx_student_id ON courses(student_id);

第五部分：高级应用与安全管理

5.1 用户和权限管理

用户管理：

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

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

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

权限管理：

-- 授予权限
GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';

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

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

-- 撤销权限
REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';

5.2 备份与恢复

备份数据库：

# 使用 mysqldump 备份
mysqldump -u root -p mydatabase > backup.sql

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

恢复数据库：

# 恢复数据库
mysql -u root -p mydatabase < backup.sql

5.3 视图

创建和使用视图：

-- 创建视图
CREATE VIEW student_course_view AS
SELECT s.name, s.age, c.course_name
FROM students s
JOIN courses c ON s.id = c.student_id;

-- 使用视图
SELECT * FROM student_course_view;

-- 修改视图
ALTER VIEW student_course_view AS
SELECT s.name, s.age, s.email, c.course_name
FROM students s
JOIN courses c ON s.id = c.student_id;

-- 删除视图
DROP VIEW student_course_view;

第六部分：实战项目

6.1 学生管理系统数据库设计

-- 创建数据库
CREATE DATABASE student_management;
USE student_management;

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_number VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender ENUM('男', '女'),
    birth_date DATE,
    phone VARCHAR(15),
    email VARCHAR(100),
    address TEXT,
    enrollment_date DATE,
    status ENUM('在读', '毕业', '休学') DEFAULT '在读',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建院系表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_code VARCHAR(10) UNIQUE NOT NULL,
    dept_name VARCHAR(100) NOT NULL,
    dean VARCHAR(100),
    phone VARCHAR(15)
);

-- 创建专业表
CREATE TABLE majors (
    major_id INT PRIMARY KEY AUTO_INCREMENT,
    major_code VARCHAR(10) UNIQUE NOT NULL,
    major_name VARCHAR(100) NOT NULL,
    dept_id INT,
    duration INT, -- 学制年限
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_code VARCHAR(20) UNIQUE NOT NULL,
    class_name VARCHAR(100) NOT NULL,
    major_id INT,
    advisor VARCHAR(100), -- 班主任
    start_year YEAR,
    FOREIGN KEY (major_id) REFERENCES majors(major_id)
);

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credit DECIMAL(3,1), -- 学分
    hours INT, -- 学时
    course_type ENUM('必修', '选修', '实践'),
    description TEXT
);

-- 创建成绩表
CREATE TABLE grades (
    grade_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    score DECIMAL(5,2), -- 成绩
    semester VARCHAR(20), -- 学期
    academic_year VARCHAR(20), -- 学年
    exam_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE KEY unique_grade (student_id, course_id, semester)
);

-- 创建学生-班级关联表
CREATE TABLE student_class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    class_id INT,
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

-- 插入测试数据
INSERT INTO departments (dept_code, dept_name, dean) VALUES
('CS', '计算机科学学院', '张教授'),
('MA', '数学学院', '李教授');

INSERT INTO majors (major_code, major_name, dept_id, duration) VALUES
('CS01', '计算机科学与技术', 1, 4),
('MA01', '应用数学', 2, 4);

INSERT INTO classes (class_code, class_name, major_id, start_year) VALUES
('CS202001', '计算机2020级1班', 1, 2020),
('MA202001', '数学2020级1班', 2, 2020);

INSERT INTO students (student_number, name, gender, enrollment_date) VALUES
('2020001', '张三', '男', '2020-09-01'),
('2020002', '李四', '女', '2020-09-01');

INSERT INTO courses (course_code, course_name, credit, hours, course_type) VALUES
('CS001', '数据结构', 4.0, 64, '必修'),
('MA001', '高等数学', 6.0, 96, '必修');

6.2 常用查询示例

-- 查询学生基本信息及班级
SELECT s.student_number, s.name, s.gender, c.class_name, m.major_name, d.dept_name
FROM students s
JOIN student_class sc ON s.student_id = sc.student_id
JOIN classes c ON sc.class_id = c.class_id
JOIN majors m ON c.major_id = m.major_id
JOIN departments d ON m.dept_id = d.dept_id;

-- 查询学生成绩
SELECT s.name, c.course_name, g.score, g.semester
FROM students s
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE s.student_number = '2020001';

-- 统计各课程平均分
SELECT c.course_name, 
       AVG(g.score) as avg_score,
       MAX(g.score) as max_score,
       MIN(g.score) as min_score,
       COUNT(*) as student_count
FROM courses c
JOIN grades g ON c.course_id = g.course_id
GROUP BY c.course_id, c.course_name;

-- 查询挂科学生
SELECT s.name, c.course_name, g.score
FROM students s
JOIN grades g ON s.student_id = g.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE g.score < 60;

第七部分：性能监控与故障排查

7.1 监控工具使用

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看系统变量
SHOW VARIABLES LIKE '%buffer%';

-- 查看状态变量
SHOW STATUS LIKE 'Innodb%';

-- 查看表状态
SHOW TABLE STATUS LIKE 'students';

7.2 慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用慢查询日志（在配置文件中设置）
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 2

学习建议

学习路径：

初级阶段（1-2周）：
- 掌握基本 SQL 语法
- 熟悉 DDL、DML、DQL
- 练习简单的增删改查操作
中级阶段（2-3周）：
- 学习复杂查询、连接查询
- 理解事务和索引
- 掌握基本的数据库设计原则
高级阶段（3-4周）：
- 学习存储过程、触发器
- 掌握性能优化技巧
- 学习备份恢复和安全管理
实战阶段（持续）：
- 完成实际项目
- 参与开源项目
- 持续学习新技术

Hello，小拾!

第一部分：MySQL 基础入门

1.1 MySQL 简介与安装

什么是 MySQL？

MySQL 安装

1.2 数据库基本概念

核心概念：

1.3 MySQL 基本操作

连接 MySQL：

基本数据库操作：

第二部分：SQL 语言基础

2.1 数据定义语言 (DDL)

创建表：

修改表结构：

2.2 数据操作语言 (DML)

插入数据：

查询数据：

更新数据：

删除数据：

2.3 数据查询语言 (DQL) 进阶

聚合函数：

分组查询：

连接查询：

子查询：

第三部分：MySQL 高级特性

3.1 索引优化

创建索引：

3.2 事务处理

事务基本操作：

事务隔离级别：

3.3 存储过程和函数

创建存储过程：

带参数的存储过程：

创建函数：

3.4 触发器

创建触发器：

第四部分：数据库设计与优化

4.1 数据库规范化

第一范式 (1NF)：

第二范式 (2NF)：

第三范式 (3NF)：

4.2 性能优化技巧

查询优化：

索引优化策略：

第五部分：高级应用与安全管理

5.1 用户和权限管理

用户管理：

权限管理：

5.2 备份与恢复

备份数据库：

恢复数据库：

5.3 视图

创建和使用视图：

第六部分：实战项目

6.1 学生管理系统数据库设计

6.2 常用查询示例

第七部分：性能监控与故障排查

7.1 监控工具使用

7.2 慢查询日志

学习建议

学习路径：

支持与分享

评论

欢迎来到我的博客！