一、 数据库与表操作 (DDL)

1. 创建表 (CREATE TABLE)

使用 CREATE TABLE 语句定义表结构，指定字段名、类型、注释以及引擎和字符集

创建学生表 (students）

CREATE TABLE `students` ( `stu_id` bigint(10) DEFAULT NULL COMMENT '学生学号', `stu_name` varchar(255) DEFAULT NULL COMMENT '学生姓名', `gender` varchar(255) DEFAULT NULL COMMENT '学生性别', `age` int(10) DEFAULT NULL COMMENT '学生年龄', `major` varchar(255) DEFAULT NULL COMMENT '学生专业', `clazz` varchar(255) DEFAULT NULL COMMENT '学生班级', `year` bigint(20) DEFAULT NULL COMMENT '入学年份' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建成绩（scores）

CREATE TABLE `scores` ( `stu_id` bigint(10) DEFAULT NULL COMMENT '学生学号', `subject_id` int(10) DEFAULT NULL COMMENT '科目编号', `score` int(10) DEFAULT NULL COMMENT '考试分数', `type` varchar(255) DEFAULT NULL COMMENT '考试类型' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

创建科目表 (subjects）

CREATE TABLE `subjects` ( `subject_id` int(10) DEFAULT NULL COMMENT '科目编号', `subject_name` varchar(255) DEFAULT NULL COMMENT '科目名称', `credit` int(10) DEFAULT NULL COMMENT '学分', `semester` int(10) DEFAULT NULL COMMENT '授课学期' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ENGINE=InnoDB：指定数据库存储引擎为 InnoDB（支持事务、行级锁）。
DEFAULT CHARSET=utf8mb4：指定表字符集为 utf8mb4（支持 emoji 等特殊字符）。
COMMENT：用于添加字段或表的注释。

二、 数据导入导出

1. 数据插入 (INSERT)

插入单条数据

INSERT INTO students VALUES(20250101,'张三','男',20,'计算机科学与技术','计科2501',2025);

插入多行数据

INSERT INTO students(stu_id, stu_name, gender, age, major, clazz, year) VALUES (20250101,'张三','男',20,'计算机科学与技术','计科2501',2025), (20250102,'李四','女',20,'计算机科学与技术','计科2501',2025), (20250103,'王五','男',19,'计算机科学与技术','计科2501',2025);

2. 文件导入 (LOAD DATA)

使用 LOAD DATA LOCAL INFILE 从文本文件快速导入数据。

Windows 路径

LOAD DATA LOCAL INFILE 'D:\\Desktop\\mysqlmd\\data\\students.txt' INTO TABLE students FIELDS TERMINATED BY ',';

Linux 路径

LOAD DATA LOCAL INFILE '/root/data/students.txt' INTO TABLE students FIELDS TERMINATED BY ',';

FIELDS TERMINATED BY ','：指定字段分隔符为逗号。

3. 数据备份与导出 (MYSQLDUMP)

使用mysqldump命令行工具进行逻辑备份。

基本导出命令

# 格式: mysqldump -u用户名 -p密码 数据库名 表名 > 导出文件路径 # 注意：不建议在命令行直接明文输入密码(-p123456)，不安全。 mysqldump -uroot -p123456 stu students > /root/data/students.sql

设置别名简化备份 (建议加入 .bashrc)

基本备份别名

alias db_backup='mysqldump -uroot -p --single-transaction stu students > /root/data/mydb_$(date +%Y%m%d).sql'

备份并压缩别名 (生成 .sql.gz，节省空间)

alias db_backup='mysqldump -uroot -p --single-transaction stu students | gzip > /root/data/mydb_$(date +%Y%m%d).sql.gz'

single-transaction：该选项对于 InnoDB 表很有用，它会在导出时创建一个一致性的快照，不需要锁定表，保证备份数据的一致性。
date +%Y%m%d：Shell 命令，用于生成当前日期字符串（如 20251205），便于文件归档。

三、 基础查询 (DQL)

1. 基本查询

-- 查询所有学生 SELECT * FROM students; -- 查询指定列 SELECT name, age FROM students;

2. 条件查询 (WHERE)

-- 查询年龄等于20岁的学生 SELECT name, age FROM students WHERE age = 20; -- 查询特定专业的学生 (注意：数据中可能包含隐藏字符如换行符\r) SELECT * FROM students WHERE major='人工智能\r';

3. 模糊查询 (LIKE)

-- 查询专业名称包含"计算机科学"的学生 -- % 表示任意多个字符 SELECT * FROM students WHERE major LIKE '%计算机科学%';

4. 排序 (ORDER BY)

-- 查询年龄等于20岁的学生，按ID倒序排列 -- DESC 表示降序，ASC 表示升序（默认） SELECT id, name FROM students WHERE age = 20 ORDER BY id DESC;

5. 聚合函数与分组 (GROUP BY)

-- 查询每个专业的最大年龄 -- GROUP BY 用于结合合计函数，根据一个或多个列对结果集进行分组 SELECT major, MAX(age) as max_age FROM students GROUP BY major;

常用聚合函数：
• MAX(): 最大值
• MIN(): 最小值
• AVG(): 平均值
• SUM(): 求和
• COUNT(): 计数

四、 高级查询 (连接查询 JOIN)

1. 内连接 (INNER JOIN)

只返回两个表中连接字段匹配的行。

-- 查询学生信息、对应的科目ID及科目名称 -- 链接了 students, scores, subjects 三张表 SELECT s.*, sc.subject_id, su.subject_name FROM ( students s JOIN scores sc JOIN subjects su ON s.id = sc.stu_id AND sc.subject_id = su.subject_id );

2. 外连接 (OUTER JOIN)

返回左表或右表中的所有记录，即使另一张表中没有匹配的记录。
右连接 (RIGHT JOIN)

-- 即使学生表中没有对应ID，也会返回 scores 表中的所有记录 -- 示例：在 score 末行添加了一个不存在的学生ID(如2000)的数据，也会被查出 SELECT * FROM( students as s RIGHT JOIN scores as sc ON s.id = sc.stu_id );

• INNER JOIN (JOIN): 取交集。
• LEFT JOIN: 以左表为主，左表全显示，右表无匹配则为 NULL。
• RIGHT JOIN: 以右表为主，右表全显示，左表无匹配则为 NULL。
• FULL JOIN: 全连接（MySQL 不直接支持，通常使用 UNION 实现）