SQL DML 语句

CREATE TABLE `classes`  (`ClassID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '班级ID',`ClassName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '班级名称',`TeacherID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '老师ID',PRIMARY KEY (`ClassID`) USING BTREE,INDEX `TeacherID`(`TeacherID`) USING BTREE,CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`TeacherID`) REFERENCES `teachers` (`TeacherID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;INSERT INTO `classes` VALUES ('001', '计算机科学', 'T001');
INSERT INTO `classes` VALUES ('002', '物理学', 'T002');
INSERT INTO `classes` VALUES ('003', '化学', 'T003');
-- ----------------------------CREATE TABLE `students`  (`StudentID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生ID',`StudentName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',`ClassID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '班级ID',PRIMARY KEY (`StudentID`) USING BTREE,INDEX `ClassID`(`ClassID`) USING BTREE,CONSTRAINT `students_ibfk_1` FOREIGN KEY (`ClassID`) REFERENCES `classes` (`ClassID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;INSERT INTO `students` VALUES ('S001', '小明', '001');
INSERT INTO `students` VALUES ('S002', '小红', '002');
INSERT INTO `students` VALUES ('S003', '小刚', '003');
INSERT INTO `students` VALUES ('S004', '小李', NULL);
INSERT INTO `students` VALUES ('S006', '小张', '002');
-- ----------------------------CREATE TABLE `subjects`  (`SubjectID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '科目ID',`SubjectName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '科目名称',`TeacherID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '老师ID',PRIMARY KEY (`SubjectID`) USING BTREE,INDEX `TeacherID`(`TeacherID`) USING BTREE,CONSTRAINT `subjects_ibfk_1` FOREIGN KEY (`TeacherID`) REFERENCES `teachers` (`TeacherID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
INSERT INTO `subjects` VALUES ('SUB001', '计算机编程', 'T001');
INSERT INTO `subjects` VALUES ('SUB002', '高级物理', 'T002');
INSERT INTO `subjects` VALUES ('SUB003', '有机化学', 'T003');
INSERT INTO `subjects` VALUES ('SUB004', '数据结构', 'T001');-- ----------------------------CREATE TABLE `teachers`  (`TeacherID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '老师ID',`TeacherName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '老师姓名',`Specialty` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '专业领域',PRIMARY KEY (`TeacherID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;-- ----------------------------
INSERT INTO `teachers` VALUES ('T001', '李老师', '计算机科学');
INSERT INTO `teachers` VALUES ('T002', '王老师', '物理学');
INSERT INTO `teachers` VALUES ('T003', '张老师', '化学');
INSERT INTO `teachers` VALUES ('T004', '刘校长', NULL);

1. 查询所有班级名称和对应的老师姓名

SELECT c.ClassName, t.TeacherName FROM classes c 
LEFT JOIN teachers t ON c.TeacherID = t.TeacherID;

2. 查询某个老师 (T001) 教授的所有科目名称

SELECT s.SubjectName FROM subjects s WHERE s.TeacherID = 'T001';

3. 查询某个班级 (例如班级ID为 '892') 的所有学生姓名

SELECT st.StudentName FROM students st WHERE st.ClassID = '892';

4. 查询教授科目数量最多的老师姓名

SELECT t.TeacherName 
FROM teachers t 
JOIN subjects s ON t.TeacherID = s.TeacherID 
GROUP BY t.TeacherName ORDER BY COUNT(s.SubjectID) DESC LIMIT 1;

5. 查询每个班级的学生数量并按学生数量降序排列

SELECT c.ClassName, COUNT(s.StudentID) AS student_count 
FROM classes c 
LEFT JOIN students s ON c.ClassID = s.ClassID 
GROUP BY c.ClassName ORDER BY student_count DESC;

6. 查询每个老师所教授的科目名称，结果以老师姓名和科目名称的形式展示

SELECT t.TeacherName, s.SubjectName 
FROM teachers t JOIN subjects s ON t.TeacherID = s.TeacherID;

7. 查询没有教授任何科目的老师的姓名

SELECT t.TeacherName
FROM teachers t 
LEFT JOIN subjects s ON t.TeacherID = s.TeacherID 
WHERE s.SubjectID IS NULL;

 8. 查询没有分配到班级的学生

SELECT StudentName
FROM students
WHERE ClassID IS NULL;

• LEFT JOIN 用于包含左表所有数据，即使右表没有匹配项，也会返回结果。
• COUNT() 用于计算班级中学生的数量。
• GROUP BY 用于按班级或老师分组。
• ORDER BY 用于按学生数量降序排列。
• LIMIT 1 用于限制返回结果为最多一个记录。