MySQL面试题解析-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_53619214/article/details/128140054

这篇博客提供了多个关于MySQL的面试题目，包括查询所有学生信息、最高分、班级平均分、特定课程高分学生、平均成绩、教师授课平均分等复杂查询。涉及到JOIN、聚合函数、HAVING、LIMIT等SQL操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*
Navicat Premium Data Transfer

Source Server : localhost_3306
Source Server Type : MySQL
Source Server Version : 50738
Source Host : localhost:3306
Source Schema : table4

Target Server Type : MySQL
Target Server Version : 50738
File Encoding : 65001

Date: 30/11/2022 17:22:37
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`Cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Cname` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Tno` int(11) DEFAULT NULL,
PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('3-105', '计算机导论', 825);
INSERT INTO `course` VALUES ('3-245', '操作系统', 804);
INSERT INTO `course` VALUES ('6-166', '数字电路', 856);
INSERT INTO `course` VALUES ('9-888', '高等数学', 831);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sno` int(10) UNSIGNED NOT NULL,
`cno` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Degree` int(10) DEFAULT 0
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (103, '3-245', 86);
INSERT INTO `score` VALUES (105, '3-245', 75);
INSERT INTO `score` VALUES (109, '3-245', 68);
INSERT INTO `score` VALUES (103, '3-105', 92);
INSERT INTO `score` VALUES (105, '3-105', 88);
INSERT INTO `score` VALUES (109, '3-105', 76);
INSERT INTO `score` VALUES (101, '3-105', 64);
INSERT INTO `score` VALUES (107, '3-105', 91);
INSERT INTO `score` VALUES (108, '3-105', 78);
INSERT INTO `score` VALUES (101, '6-166', 85);
INSERT INTO `score` VALUES (107, '6-166', 79);
INSERT INTO `score` VALUES (108, '6-166', 81);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`Sno` int(10) UNSIGNED NOT NULL,
`Sname` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Ssex` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '男',
`Sbirthday` date DEFAULT NULL,
`class` int(11) DEFAULT NULL,
PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (101, '李军', '男', '1976-02-20', 95033);
INSERT INTO `student` VALUES (103, '陆君', '男', '1974-06-03', 95031);
INSERT INTO `student` VALUES (105, '匡明', '男', '1975-10-02', 95031);
INSERT INTO `student` VALUES (107, '王丽', '女', '1976-01-23', 95033);
INSERT INTO `student` VALUES (108, '曾华', '男', '1977-09-01', 95033);
INSERT INTO `student` VALUES (109, '王芳', '男', '1975-02-10', 95031);
INSERT INTO `student` VALUES (110, '王芳', '男', NULL, 95033);

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`Tno` int(10) UNSIGNED NOT NULL,
`Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Tsex` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '男',
`Tbirthday` date DEFAULT NULL,
`Prof` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Depart` char(10)