【数据库操作全指南：从表创建到高级查询】
一、 数据库与表操作 (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 实现）