sql语法练习(没事就看看，捡捡基础，偏向于分组查询)

已于 2024-11-08 18:13:06 修改
阅读量491 收藏 8
点赞数 5
文章标签： sql 数据库
于 2024-10-30 21:51:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_45616758/article/details/143375188
版权

文章目录

表资源

学生表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 90001 (9.0.1)
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 90001 (9.0.1)
 File Encoding         : 65001

 Date: 30/10/2024 21:15:38
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`student_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 101 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '黄三');
INSERT INTO `students` VALUES (2, '吴五');
INSERT INTO `students` VALUES (3, '刘八');
INSERT INTO `students` VALUES (4, '黄六');
INSERT INTO `students` VALUES (5, '吴五');
INSERT INTO `students` VALUES (6, '刘十');
INSERT INTO `students` VALUES (7, '杨三');
INSERT INTO `students` VALUES (8, '李七');
INSERT INTO `students` VALUES (9, '陈八');
INSERT INTO `students` VALUES (10, '刘十');
INSERT INTO `students` VALUES (11, '刘四');
INSERT INTO `students` VALUES (12, '陈八');
INSERT INTO `students` VALUES (13, '王八');
INSERT INTO `students` VALUES (14, '周六');
INSERT INTO `students` VALUES (15, '黄五');
INSERT INTO `students` VALUES (16, '王六');
INSERT INTO `students` VALUES (17, '张六');
INSERT INTO `students` VALUES (18, '王十');
INSERT INTO `students` VALUES (19, '周三');
INSERT INTO `students` VALUES (20, '赵二');
INSERT INTO `students` VALUES (21, '陈五');
INSERT INTO `students` VALUES (22, '张三');
INSERT INTO `students` VALUES (23, '吴四');
INSERT INTO `students` VALUES (24, '杨五');
INSERT INTO `students` VALUES (25, '刘四');
INSERT INTO `students` VALUES (26, '杨二');
INSERT INTO `students` VALUES (27, '吴五');
INSERT INTO `students` VALUES (28, '周二');
INSERT INTO `students` VALUES (29, '吴十');
INSERT INTO `students` VALUES (30, '王七');
INSERT INTO `students` VALUES (31, '王三');
INSERT INTO `students` VALUES (32, '黄七');
INSERT INTO `students` VALUES (33, '刘四');
INSERT INTO `students` VALUES (34, '赵十');
INSERT INTO `students` VALUES (35, '李一');
INSERT INTO `students` VALUES (36, '周三');
INSERT INTO `students` VALUES (37, '吴九');
INSERT INTO `students` VALUES (38, '杨九');
INSERT INTO `students` VALUES (39, '刘九');
INSERT INTO `students` VALUES (40, '陈七');
INSERT INTO `students` VALUES (41, '黄二');
INSERT INTO `students` VALUES (42, '黄五');
INSERT INTO `students` VALUES (43, '李三');
INSERT INTO `students` VALUES (44, '刘六');
INSERT INTO `students` VALUES (45, '黄八');
INSERT INTO `students` VALUES (46, '吴七');
INSERT INTO `students` VALUES (47, '陈三');
INSERT INTO `students` VALUES (48, '李三');
INSERT INTO `students` VALUES (49, '黄四');
INSERT INTO `students` VALUES (50, '赵十');
INSERT INTO `students` VALUES (51, '陈六');
INSERT INTO `students` VALUES (52, '刘七');
INSERT INTO `students` VALUES (53, '杨十');
INSERT INTO `students` VALUES (54, '周六');
INSERT INTO `students` VALUES (55, '吴七');
INSERT INTO `students` VALUES (56, '李五');
INSERT INTO `students` VALUES (57, '李四');
INSERT INTO `students` VALUES (58, '杨二');
INSERT INTO `students` VALUES (59, '黄十');
INSERT INTO `students` VALUES (60, '王三');
INSERT INTO `students` VALUES (61, '李五');
INSERT INTO `students` VALUES (62, '刘十');
INSERT INTO `students` VALUES (63, '赵八');
INSERT INTO `students` VALUES (64, '周一');
INSERT INTO `students` VALUES (65, '周二');
INSERT INTO `students` VALUES (66, '陈八');
INSERT INTO `students` VALUES (67, '黄二');
INSERT INTO `students` VALUES (68, '赵十');
INSERT INTO `students` VALUES (69, '黄七');
INSERT INTO `students` VALUES (70, '刘一');
INSERT INTO `students` VALUES (71, '吴六');
INSERT INTO `students` VALUES (72, '黄三');
INSERT INTO `students` VALUES (73, '李三');
INSERT INTO `students` VALUES (74, '张三');
INSERT INTO `students` VALUES (75, '吴十');
INSERT INTO `students` VALUES (76, '王四');
INSERT INTO `students` VALUES (77, '杨七');
INSERT INTO `students` VALUES (78, '张七');
INSERT INTO `students` VALUES (79, '刘三');
INSERT INTO `students` VALUES (80, '杨九');
INSERT INTO `students` VALUES (81, '陈三');
INSERT INTO `students` VALUES (82, '黄五');
INSERT INTO `students` VALUES (83, '杨十');
INSERT INTO `students` VALUES (84, '杨四');
INSERT INTO `students` VALUES (85, '刘一');
INSERT INTO `students` VALUES (86, '刘十');
INSERT INTO `students` VALUES (87, '刘十');
INSERT INTO `students` VALUES (88, '黄二');
INSERT INTO `students` VALUES (89, '李七');
INSERT INTO `students` VALUES (90, '王四');
INSERT INTO `students` VALUES (91, '陈一');
INSERT INTO `students` VALUES (92, '周六');
INSERT INTO `students` VALUES (93, '李七');
INSERT INTO `students` VALUES (94, '刘六');
INSERT INTO `students` VALUES (95, '吴一');
INSERT INTO `students` VALUES (96, '黄十');
INSERT INTO `students` VALUES (97, '李八');
INSERT INTO `students` VALUES (98, '张五');
INSERT INTO `students` VALUES (99, '周四');
INSERT INTO `students` VALUES (100, '黄三');

SET FOREIGN_KEY_CHECKS = 1;

学科成绩表

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 90001 (9.0.1)
 Source Host           : localhost:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 90001 (9.0.1)
 File Encoding         : 65001

 Date: 30/10/2024 21:15:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`  (
  `score_id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NULL DEFAULT NULL,
  `subject` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `score` decimal(5, 2) NOT NULL,
  PRIMARY KEY (`score_id`) USING BTREE,
  INDEX `student_id`(`student_id` ASC) USING BTREE,
  CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 613 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES (1, 1, '历史', 81.06);
INSERT INTO `scores` VALUES (2, 1, '英语', 85.40);
INSERT INTO `scores` VALUES (3, 1, '物理', 60.83);
INSERT INTO `scores` VALUES (4, 1, '化学', 84.23);
INSERT INTO `scores` VALUES (5, 1, '政治', 89.43);
INSERT INTO `scores` VALUES (6, 1, '数学', 58.49);
INSERT INTO `scores` VALUES (7, 1, '生物', 65.20);
INSERT INTO `scores` VALUES (8, 1, '地理', 87.57);
INSERT INTO `scores` VALUES (9, 2, '数学', 99.87);
INSERT INTO `scores` VALUES (10, 2, '地理', 69.46);
INSERT INTO `scores` VALUES (11, 2, '历史', 89.39);
INSERT INTO `scores` VALUES (12, 2, '化学', 85.90);
INSERT INTO `scores` VALUES (13, 2, '生物', 59.37);
INSERT INTO `scores` VALUES (14, 2, '英语', 80.23);
INSERT INTO `scores` VALUES (15, 3, '政治', 77.61);
INSERT INTO `scores` VALUES (16, 3, '生物', 96.03);
INSERT INTO `scores` VALUES (17, 3, '历史', 92.37);
INSERT INTO `scores` VALUES (18, 3, '语文', 85.30);
INSERT INTO `scores` VALUES (19, 3, '地理', 95.82);
INSERT INTO `scores` VALUES (20, 3, '英语', 98.86);
INSERT INTO `scores` VALUES (21, 4, '英语', 53.85);
INSERT INTO `scores` VALUES (22, 4, '政治', 82.60);
INSERT INTO `scores` VALUES (23, 4, '历史', 68.62);
INSERT INTO `scores` VALUES (24, 4, '语文', 78.01);
INSERT INTO `scores` VALUES (25, 4, '数学', 90.11);
INSERT INTO `scores` VALUES (26, 4, '生物', 50.20);
INSERT INTO `scores` VALUES (27, 4, '地理', 88.06);
INSERT INTO `scores` VALUES (28, 4, '化学', 72.33);
INSERT INTO `scores` VALUES (29, 4, '物理', 56.31);
INSERT INTO `scores` VALUES (30, 5, '英语', 66.26);
INSERT INTO `scores` VALUES (31, 5, '地理', 69.65);
INSERT INTO `scores` VALUES (32, 5, '数学', 79.32);
INSERT INTO `scores` VALUES (33, 5, '历史', 88.18);
INSERT INTO `scores` VALUES (34, 5, '化学', 54.25);
INSERT INTO `scores` VALUES (35, 5, '生物', 74.44);
INSERT INTO `scores` VALUES (36, 5, '政治', 66.94);
INSERT INTO `scores` VALUES (37, 5, '物理', 93.51);
INSERT INTO `scores` VALUES (38, 6, '地理', 54.69);
INSERT INTO `scores` VALUES (39, 6, '数学', 95.75);
INSERT INTO `scores` VALUES (40, 6, '历史', 93.36);
INSERT INTO `scores` VALUES (41, 7, '物理', 53.15);
INSERT INTO `scores` VALUES (42, 7, '英语', 79.38);
INSERT INTO `scores` VALUES (43, 7, '生物', 80.41);
INSERT INTO `scores` VALUES (44, 7, '化学', 81.96);
INSERT INTO `scores` VALUES (45, 7, '语文', 60.17);
INSERT INTO `scores` VALUES (46, 8, '地理', 81.05);
INSERT INTO `scores` VALUES (47, 8, '数学', 55.09);
INSERT INTO `scores` VALUES (48, 8, '化学', 85.21);
INSERT INTO `scores` VALUES (49, 8, '语文', 77.31);
INSERT INTO `scores` VALUES (50, 8, '英语', 53.05);
INSERT INTO `scores` VALUES (51, 8, '物理', 68.25);
INSERT INTO `scores` VALUES (52, 8, '生物', 55.29);
INSERT INTO `scores` VALUES (53, 8, '政治', 62.07);
INSERT INTO `scores` VALUES (54, 9, '地理', 88.89);
INSERT INTO `scores` VALUES (55, 9, '政治', 94.14);
INSERT INTO `scores` VALUES (56, 9, '物理', 66.28);
INSERT INTO `scores` VALUES (57, 9, '生物', 92.31);
INSERT INTO `scores` VALUES (58, 9, '数学', 51.13);
INSERT INTO `scores` VALUES (59, 9, '语文', 65.20);
INSERT INTO `scores` VALUES (60, 9, '历史', 78.30);
INSERT INTO `scores` VALUES (61, 9, '化学', 54.26);
INSERT INTO `scores` VALUES (62, 10, '地理', 99.84);
INSERT INTO `scores` VALUES (63, 10, '语文', 80.66);
INSERT INTO `scores` VALUES (64, 10, '化学', 86.93);
INSERT INTO `scores` VALUES (65, 10, '历史', 62.35);
INSERT INTO `scores` VALUES (66, 11, '化学', 84.98);
INSERT INTO `scores` VALUES (67, 11, '语文', 85.83);
INSERT INTO `scores` VALUES (68, 11, '历史', 59.64);
INSERT INTO `scores` VALUES (69, 11, '物理', 88.42);
INSERT INTO `scores` VALUES (70, 11, '政治', 61.52);
INSERT INTO `scores` VALUES (71, 11, '生物', 90.53);
INSERT INTO `scores` VALUES (72, 11, '英语', 63.43);
INSERT INTO `scores` VALUES (73, 12, '数学', 56.49);
INSERT INTO `scores` VALUES (74, 12, '语文', 61.12);
INSERT INTO `scores` VALUES (75, 12, '政治', 60.40);
INSERT INTO `scores` VALUES (76, 12, '化学', 80.85);
INSERT INTO `scores` VALUES (77, 12, '地理', 78.98);
INSERT INTO `scores` VALUES (78, 13, '语文', 53.97);
INSERT INTO `scores` VALUES (79, 13, '英语', 80.28);
INSERT INTO `scores` VALUES (80, 13, '数学', 99.51);
INSERT INTO `scores` VALUES (81, 13, '化学', 50.17);
INSERT INTO `scores` VALUES (82, 13, '物理', 73.56);
INSERT INTO `scores` VALUES (83, 13, '历史', 74.93);
INSERT INTO `scores` VALUES (84, 13, '地理', 71.28);
INSERT INTO `scores` VALUES (85, 14, '政治', 79.91);
INSERT INTO `scores` VALUES (86, 14, '物理', 62.57);
INSERT INTO `scores` VALUES (87, 14, '地理', 70.90);
INSERT INTO `scores` VALUES (88, 14, '历史', 84.78);
INSERT INTO `scores` VALUES (89, 14, '化学', 66.11);
INSERT INTO `scores` VALUES (90, 14, '语文', 54.25);
INSERT INTO `scores` VALUES (91, 14, '数学', 97.26);
INSERT INTO `scores` VALUES (92, 14, '生物', 96.30);
INSERT INTO `scores` VALUES (93, 15, '化学', 93.06);
INSERT INTO `scores` VALUES (94, 15, '政治', 97.46);
INSERT INTO `scores` VALUES (95, 15, '生物', 52.27);
INSERT INTO `scores` VALUES (96, 15, '英语', 86.29);
INSERT INTO `scores` VALUES (97, 15, '历史', 54.93);
INSERT INTO `scores` VALUES (98, 15, '数学', 63.61);
INSERT INTO `scores` VALUES (99, 16, '英语', 88.51);
INSERT INTO `scores` VALUES (100, 16, '数学', 53.57);
INSERT INTO `scores` VALUES (101, 16, '生物', 89.54);
INSERT INTO `scores` VALUES (102, 16, '语文', 66.10);
INSERT INTO `scores` VALUES (103, 17, '地理', 79.48);
INSERT INTO `scores` VALUES (104, 17, '英语', 58.27);
INSERT INTO `scores` VALUES (105, 17, '化学', 56.51);
INSERT INTO `scores` VALUES (106, 17, '政治', 67.99);
INSERT INTO `scores` VALUES (107, 17, '物理', 92.73);
INSERT INTO `scores` VALUES (108, 18, '物理', 61.59);
INSERT INTO `scores` VALUES (109, 18, '化学', 86.88);
INSERT INTO `scores` VALUES (110, 18, '数学', 66.27);
INSERT INTO `scores` VALUES (111, 18, '政治', 93.49);
INSERT INTO `scores` VALUES (112, 18, '地理', 74.01);
INSERT INTO `scores` VALUES (113, 18, '语文', 56.76);
INSERT INTO `scores` VALUES (114, 18, '历史', 56.49);
INSERT INTO `scores` VALUES (115, 19, '化学', 56.54);
INSERT INTO `scores` VALUES (116, 19, '语文', 78.21);
INSERT INTO `scores` VALUES (117, 19, '地理', 55.13);
INSERT INTO `scores` VALUES (118, 19, '英语', 90.23);
INSERT INTO `scores` VALUES (119, 19, '政治', 80.87);
INSERT INTO `scores` VALUES (120, 20, '生物', 93.20);
INSERT INTO `scores` VALUES (121, 20, '地理', 80.05);
INSERT INTO `scores` VALUES (122, 20, '化学', 85.89);
INSERT INTO `scores` VALUES (123, 21, '地理', 87.83);
INSERT INTO `scores` VALUES (124, 21, '化学', 56.53);
INSERT INTO `scores` VALUES (125, 21, '生物', 58.96);
INSERT INTO `scores` VALUES (126, 22, '化学', 55.06);
INSERT INTO `scores` VALUES (127, 22, '物理', 82.53);
INSERT INTO `scores` VALUES (128, 22, '地理', 98.46);
INSERT INTO `scores` VALUES (129, 22, '历史', 58.29);
INSERT INTO `scores` VALUES (130, 22, '生物', 63.56);
INSERT INTO `scores` VALUES (131, 22, '数学', 94.54);
INSERT INTO `scores` VALUES (132, 22, '语文', 97.53);
INSERT INTO `scores` VALUES (133, 23, '化学', 80.94);
INSERT INTO `scores` VALUES (134, 23, '物理', 65.88);
INSERT INTO `scores` VALUES (135, 23, '政治', 95.22);
INSERT INTO `scores` VALUES (136, 23, '地理', 99.67);
INSERT INTO `scores` VALUES (137, 23, '语文', 64.19);
INSERT INTO `scores` VALUES (138, 23, '英语', 94.03);
INSERT INTO `scores` VALUES (139, 23, '数学', 97.89);
INSERT INTO `scores` VALUES (140, 24, '数学', 80.30);
INSERT INTO `scores` VALUES (141, 24, '政治', 58.46);
INSERT INTO `scores` VALUES (142, 24, '化学', 95.99);
INSERT INTO `scores` VALUES (143, 24, '地理', 58.79);
INSERT INTO `scores` VALUES (144, 24, '历史', 84.65);
INSERT INTO `scores` VALUES (145, 25, '物理', 86.21);
INSERT INTO `scores` VALUES (146, 25, '语文', 91.79);
INSERT INTO `scores` VALUES (147, 25, '英语', 82.98);
INSERT INTO `scores` VALUES (148, 25, '历史', 51.13);
INSERT INTO `scores` VALUES (149, 25, '生物', 81.09);
INSERT INTO `scores` VALUES (150, 25, '化学', 87.86);
INSERT INTO `scores` VALUES (151, 25, '政治', 69.85);
INSERT INTO `scores` VALUES (152, 26, '物理', 52.16);
INSERT INTO `scores` VALUES (153, 26, '地理', 65.75);
INSERT INTO `scores` VALUES (154, 26, '化学', 62.49);
INSERT INTO `scores` VALUES (155, 26, '生物', 62.13);
INSERT INTO `scores` VALUES (156, 26, '数学', 83.70);
INSERT INTO `scores` VALUES (157, 26, '语文', 54.06);
INSERT INTO `scores` VALUES (158, 26, '政治', 93.54);
INSERT INTO `scores` VALUES (159, 27, '语文', 73.02);
INSERT INTO `scores` VALUES (160, 27, '化学', 99.93);
INSERT INTO `scores` VALUES (161, 27, '物理', 89.84);
INSERT INTO `scores` VALUES (162, 27, '政治', 88.57);
INSERT INTO `scores` VALUES (163, 27, '数学', 84.02);
INSERT INTO `scores` VALUES (164, 27, '地理', 88.57);
INSERT INTO `scores` VALUES (165, 27, '生物', 66.41);
INSERT INTO `scores` VALUES (166, 28, '生物', 68.42);
INSERT INTO `scores` VALUES (167, 28, '历史', 90.43);
INSERT INTO `scores` VALUES (168, 28, '数学', 55.61);
INSERT INTO `scores` VALUES (169, 28, '政治', 88.44);
INSERT INTO `scores` VALUES (170, 28, '化学', 59.70);
INSERT INTO `scores` VALUES (171, 28, '语文', 67.51);
INSERT INTO `scores` VALUES (172, 28, '地理', 89.35);
INSERT INTO `scores` VALUES (173, 28, '英语', 83.49);
INSERT INTO `scores` VALUES (174, 28, '物理', 62.79);
INSERT INTO `scores` VALUES (175, 29, '化学', 89.66);
INSERT INTO `scores` VALUES (176, 29, '语文', 79.69);
INSERT INTO `scores` VALUES (177, 29, '政治', 54.79);
INSERT INTO `scores` VALUES (178, 29, '历史', 63.82);
INSERT INTO `scores` VALUES (179, 29, '物理', 68.29);
INSERT INTO `scores` VALUES (180, 30, '生物', 72.94);
INSERT INTO `scores` VALUES (181, 30, '政治', 51.35);
INSERT INTO `scores` VALUES (182, 30, '历史', 67.04);
INSERT INTO `scores` VALUES (183, 31, '物理', 50.87);
INSERT INTO `scores` VALUES (184, 31, '历史', 60.32);
INSERT INTO `scores` VALUES (185, 31, '生物', 50.20);
INSERT INTO `scores` VALUES (186, 31, '数学', 81.80);
INSERT INTO `scores` VALUES (187, 31, '化学', 97.94);
INSERT INTO `scores` VALUES (188, 31, '政治', 74.97);
INSERT INTO `scores` VALUES (189, 32, '地理', 56.96);
INSERT INTO `scores` VALUES (190, 32, '物理', 89.33);
INSERT INTO `scores` VALUES (191, 32, '生物', 70.49);
INSERT INTO `scores` VALUES (192, 32, '数学', 89.43);
INSERT INTO `scores` VALUES (193, 32, '英语', 84.67);
INSERT INTO `scores` VALUES (194, 32, '化学', 59.13);
INSERT INTO `scores` VALUES (195, 32, '语文', 71.68);
INSERT INTO `scores` VALUES (196, 33, '英语', 68.13);
INSERT INTO `scores` VALUES (197, 33, '政治', 59.46);
INSERT INTO `scores` VALUES (198, 33, '语文', 60.69);
INSERT INTO `scores` VALUES (199, 33, '生物', 92.93);
INSERT INTO `scores` VALUES (200, 33, '历史', 77.62);
INSERT INTO `scores` VALUES (201, 33, '化学', 93.34);
INSERT INTO `scores` VALUES (202, 33, '地理', 63.09);
INSERT INTO `scores` VALUES (203, 33, '物理', 68.00);
INSERT INTO `scores` VALUES (204, 33, '数学', 62.65);
INSERT INTO `scores` VALUES (205, 34, '物理', 65.55);
INSERT INTO `scores` VALUES (206, 34, '地理', 52.79);
INSERT INTO `scores` VALUES (207, 34, '政治', 93.29);
INSERT INTO `scores` VALUES (208, 34, '化学', 89.03);
INSERT INTO `scores` VALUES (209, 34, '数学', 67.69);
INSERT INTO `scores` VALUES (210, 34, '语文', 79.10);
INSERT INTO `scores` VALUES (211, 34, '英语', 91.53);
INSERT INTO `scores` VALUES (212, 34, '生物', 66.07);
INSERT INTO `scores` VALUES (213, 35, '政治', 95.17);
INSERT INTO `scores` VALUES (214, 35, '物理', 65.99);
INSERT INTO `scores` VALUES (215, 35, '语文', 81.12);
INSERT INTO `scores` VALUES (216, 35, '化学', 79.75);
INSERT INTO `scores` VALUES (217, 36, '语文', 90.27);
INSERT INTO `scores` VALUES (218, 36, '物理', 57.08);
INSERT INTO `scores` VALUES (219, 36, '数学', 77.36);
INSERT INTO `scores` VALUES (220, 36, '英语', 86.19);
INSERT INTO `scores` VALUES (221, 36, '生物', 66.30);
INSERT INTO `scores` VALUES (222, 36, '历史', 63.23);
INSERT INTO `scores` VALUES (223, 36, '政治', 94.50);
INSERT INTO `scores` VALUES (224, 37, '语文', 91.81);
INSERT INTO `scores` VALUES (225, 37, '数学', 88.86);
INSERT INTO `scores` VALUES (226, 37, '化学', 99.98);
INSERT INTO `scores` VALUES (227, 37, '地理', 92.51);
INSERT INTO `scores` VALUES (228, 37, '英语', 57.31);
INSERT INTO `scores` VALUES (229, 37, '历史', 68.64);
INSERT INTO `scores` VALUES (230, 37, '物理', 73.60);
INSERT INTO `scores` VALUES (231, 37, '生物', 66.41);
INSERT INTO `scores` VALUES (232, 38, '生物', 99.59);
INSERT INTO `scores` VALUES (233, 38, '英语', 85.63);
INSERT INTO `scores` VALUES (234, 38, '政治', 84.91);
INSERT INTO `scores` VALUES (235, 39, '物理', 87.88);
INSERT INTO `scores` VALUES (236, 39, '英语', 82.04);
INSERT INTO `scores` VALUES (237, 39, '生物', 58.23);
INSERT INTO `scores` VALUES (238, 40, '英语', 93.20);
INSERT INTO `scores` VALUES (239, 40, '地理', 72.40);
INSERT INTO `scores` VALUES (240, 40, '化学', 61.20);
INSERT INTO `scores` VALUES (241, 41, '生物', 87.22);
INSERT INTO `scores` VALUES (242, 41, '化学', 79.29);
INSERT INTO `scores` VALUES (243, 41, '英语', 90.11);
INSERT INTO `scores` VALUES (244, 41, '物理', 52.84);
INSERT INTO `scores` VALUES (245, 41, '政治', 84.29);
INSERT INTO `scores` VALUES (246, 42, '生物', 89.37);
INSERT INTO `scores` VALUES (247, 42, '英语', 69.63);
INSERT INTO `scores` VALUES (248, 42, '物理', 69.96);
INSERT INTO `scores` VALUES (249, 42, '政治', 89.18);
INSERT INTO `scores` VALUES (250, 42, '语文', 77.61);
INSERT INTO `scores` VALUES (251, 42, '地理', 81.39);
INSERT INTO `scores` VALUES (252, 43, '地理', 72.43);
INSERT INTO `scores` VALUES (253, 43, '物理', 92.86);
INSERT INTO `scores` VALUES (254, 43, '语文', 57.18);
INSERT INTO `scores` VALUES (255, 44, '政治', 71.46);
INSERT INTO `scores` VALUES (256, 44, '物理', 79.16);
INSERT INTO `scores` VALUES (257, 44, '数学', 54.13);
INSERT INTO `scores` VALUES (258, 44, '历史', 87.85);
INSERT INTO `scores` VALUES (259, 44, '化学', 62.60);
INSERT INTO `scores` VALUES (260, 45, '物理', 63.21);
INSERT INTO `scores` VALUES (261, 45, '历史', 96.80);
INSERT INTO `scores` VALUES (262, 45, '化学', 92.59);
INSERT INTO `scores` VALUES (263, 46, '语文', 56.97);
INSERT INTO `scores` VALUES (264, 46, '英语', 96.00);
INSERT INTO `scores` VALUES (265, 46, '物理', 71.21);
INSERT INTO `scores` VALUES (266, 47, '英语', 63.03);
INSERT INTO `scores` VALUES (267, 47, '物理', 61.90);
INSERT INTO `scores` VALUES (268, 47, '地理', 69.47);
INSERT INTO `scores` VALUES (269, 47, '化学', 89.95);
INSERT INTO `scores` VALUES (270, 47, '数学', 74.71);
INSERT INTO `scores` VALUES (271, 48, '政治', 94.74);
INSERT INTO `scores` VALUES (272, 48, '英语', 62.14);
INSERT INTO `scores` VALUES (273, 48, '化学', 63.65);
INSERT INTO `scores` VALUES (274, 48, '物理', 70.02);
INSERT INTO `scores` VALUES (275, 48, '地理', 89.06);
INSERT INTO `scores` VALUES (276, 48, '历史', 60.05);
INSERT INTO `scores` VALUES (277, 48, '生物', 55.63);
INSERT INTO `scores` VALUES (278, 49, '语文', 80.55);
INSERT INTO `scores` VALUES (279, 49, '历史', 80.64);
INSERT INTO `scores` VALUES (280, 49, '生物', 84.88);
INSERT INTO `scores` VALUES (281, 49, '化学', 66.45);
INSERT INTO `scores` VALUES (282, 49, '政治', 62.32);
INSERT INTO `scores` VALUES (283, 49, '物理', 57.43);
INSERT INTO `scores` VALUES (284, 50, '历史', 59.19);
INSERT INTO `scores` VALUES (285, 50, '化学', 63.03);
INSERT INTO `scores` VALUES (286, 50, '英语', 51.83);
INSERT INTO `scores` VALUES (287, 50, '物理', 66.08);
INSERT INTO `scores` VALUES (288, 50, '政治', 69.91);
INSERT INTO `scores` VALUES (289, 50, '数学', 83.24);
INSERT INTO `scores` VALUES (290, 50, '地理', 82.30);
INSERT INTO `scores` VALUES (291, 51, '生物', 75.88);
INSERT INTO `scores` VALUES (292, 51, '语文', 91.80);
INSERT INTO `scores` VALUES (293, 51, '地理', 92.82);
INSERT INTO `scores` VALUES (294, 51, '政治', 51.15);
INSERT INTO `scores` VALUES (295, 51, '历史', 84.82);
INSERT INTO `scores` VALUES (296, 52, '英语', 84.89);
INSERT INTO `scores` VALUES (297, 52, '物理', 77.79);
INSERT INTO `scores` VALUES (298, 52, '政治', 69.39);
INSERT INTO `scores` VALUES (299, 52, '地理', 60.96);
INSERT INTO `scores` VALUES (300, 52, '化学', 69.47);
INSERT INTO `scores` VALUES (301, 52, '语文', 77.70);
INSERT INTO `scores` VALUES (302, 52, '历史', 84.26);
INSERT INTO `scores` VALUES (303, 52, '数学', 53.09);
INSERT INTO `scores` VALUES (304, 52, '生物', 86.05);
INSERT INTO `scores` VALUES (305, 53, '化学', 65.13);
INSERT INTO `scores` VALUES (306, 53, '生物', 75.11);
INSERT INTO `scores` VALUES (307, 53, '数学', 57.87);
INSERT INTO `scores` VALUES (308, 53, '物理', 88.32);
INSERT INTO `scores` VALUES (309, 53, '政治', 64.06);
INSERT INTO `scores` VALUES (310, 54, '语文', 61.90);
INSERT INTO `scores` VALUES (311, 54, '数学', 87.58);
INSERT INTO `scores` VALUES (312, 54, '地理', 65.38);
INSERT INTO `scores` VALUES (313, 54, '历史', 85.74);
INSERT INTO `scores` VALUES (314, 54, '生物', 50.19);
INSERT INTO `scores` VALUES (315, 55, '地理', 75.95);
INSERT INTO `scores` VALUES (316, 55, '生物', 96.35);
INSERT INTO `scores` VALUES (317, 55, '语文', 51.35);
INSERT INTO `scores` VALUES (318, 55, '历史', 69.91);
INSERT INTO `scores` VALUES (319, 55, '化学', 58.73);
INSERT INTO `scores` VALUES (320, 55, '物理', 67.22);
INSERT INTO `scores` VALUES (321, 55, '政治', 55.30);
INSERT INTO `scores` VALUES (322, 55, '数学', 64.52);
INSERT INTO `scores` VALUES (323, 55, '英语', 75.57);
INSERT INTO `scores` VALUES (324, 56, '生物', 71.27);
INSERT INTO `scores` VALUES (325, 56, '数学', 87.12);
INSERT INTO `scores` VALUES (326, 56, '英语', 57.19);
INSERT INTO `scores` VALUES (327, 56, '语文', 88.21);
INSERT INTO `scores` VALUES (328, 57, '语文', 97.57);
INSERT INTO `scores` VALUES (329, 57, '政治', 67.05);
INSERT INTO `scores` VALUES (330, 57, '化学', 99.40);
INSERT INTO `scores` VALUES (331, 57, '英语', 82.90);
INSERT INTO `scores` VALUES (332, 58, '生物', 92.29);
INSERT INTO `scores` VALUES (333, 58, '英语', 82.03);
INSERT INTO `scores` VALUES (334, 58, '物理', 53.71);
INSERT INTO `scores` VALUES (335, 58, '政治', 93.87);
INSERT INTO `scores` VALUES (336, 58, '历史', 53.84);
INSERT INTO `scores` VALUES (337, 58, '地理', 53.42);
INSERT INTO `scores` VALUES (338, 58, '化学', 84.27);
INSERT INTO `scores` VALUES (339, 58, '语文', 98.44);
INSERT INTO `scores` VALUES (340, 59, '地理', 99.63);
INSERT INTO `scores` VALUES (341, 59, '政治', 98.25);
INSERT INTO `scores` VALUES (342, 59, '物理', 51.65);
INSERT INTO `scores` VALUES (343, 59, '数学', 86.88);
INSERT INTO `scores` VALUES (344, 60, '物理', 51.18);
INSERT INTO `scores` VALUES (345, 60, '政治', 86.27);
INSERT INTO `scores` VALUES (346, 60, '生物', 56.50);
INSERT INTO `scores` VALUES (347, 61, '化学', 90.94);
INSERT INTO `scores` VALUES (348, 61, '地理', 86.68);
INSERT INTO `scores` VALUES (349, 61, '语文', 74.67);
INSERT INTO `scores` VALUES (350, 61, '政治', 71.29);
INSERT INTO `scores` VALUES (351, 61, '历史', 62.31);
INSERT INTO `scores` VALUES (352, 61, '数学', 65.49);
INSERT INTO `scores` VALUES (353, 61, '生物', 63.39);
INSERT INTO `scores` VALUES (354, 61, '英语', 85.16);
INSERT INTO `scores` VALUES (355, 61, '物理', 99.03);
INSERT INTO `scores` VALUES (356, 62, '化学', 88.03);
INSERT INTO `scores` VALUES (357, 62, '历史', 94.80);
INSERT INTO `scores` VALUES (358, 62, '地理', 68.60);
INSERT INTO `scores` VALUES (359, 62, '生物', 90.95);
INSERT INTO `scores` VALUES (360, 62, '政治', 81.81);
INSERT INTO `scores` VALUES (361, 62, '数学', 91.02);
INSERT INTO `scores` VALUES (362, 62, '物理', 70.74);
INSERT INTO `scores` VALUES (363, 62, '语文', 93.56);
INSERT INTO `scores` VALUES (364, 63, '英语', 85.23);
INSERT INTO `scores` VALUES (365, 63, '政治', 87.11);
INSERT INTO `scores` VALUES (366, 63, '生物', 86.07);
INSERT INTO `scores` VALUES (367, 63, '语文', 80.98);
INSERT INTO `scores` VALUES (368, 63, '地理', 85.91);
INSERT INTO `scores` VALUES (369, 63, '历史', 55.59);
INSERT INTO `scores` VALUES (370, 63, '物理', 97.78);
INSERT INTO `scores` VALUES (371, 63, '化学', 66.80);
INSERT INTO `scores` VALUES (372, 64, '数学', 51.14);
INSERT INTO `scores` VALUES (373, 64, '政治', 59.87);
INSERT INTO `scores` VALUES (374, 64, '历史', 54.56);
INSERT INTO `scores` VALUES (375, 64, '物理', 96.91);
INSERT INTO `scores` VALUES (376, 65, '政治', 89.48);
INSERT INTO `scores` VALUES (377, 65, '语文', 78.14);
INSERT INTO `scores` VALUES (378, 65, '数学', 96.05);
INSERT INTO `scores` VALUES (379, 65, '化学', 77.48);
INSERT INTO `scores` VALUES (380, 65, '物理', 79.14);
INSERT INTO `scores` VALUES (381, 65, '历史', 54.77);
INSERT INTO `scores` VALUES (382, 65, '地理', 92.02);
INSERT INTO `scores` VALUES (383, 65, '生物', 52.73);
INSERT INTO `scores` VALUES (384, 65, '英语', 70.61);
INSERT INTO `scores` VALUES (385, 66, '语文', 60.13);
INSERT INTO `scores` VALUES (386, 66, '历史', 65.15);
INSERT INTO `scores` VALUES (387, 66, '地理', 75.46);
INSERT INTO `scores` VALUES (388, 66, '物理', 78.52);
INSERT INTO `scores` VALUES (389, 67, '化学', 98.15);
INSERT INTO `scores` VALUES (390, 67, '英语', 53.52);
INSERT INTO `scores` VALUES (391, 67, '生物', 79.32);
INSERT INTO `scores` VALUES (392, 67, '数学', 98.74);
INSERT INTO `scores` VALUES (393, 67, '地理', 90.26);
INSERT INTO `scores` VALUES (394, 67, '语文', 93.11);
INSERT INTO `scores` VALUES (395, 67, '物理', 81.89);
INSERT INTO `scores` VALUES (396, 67, '历史', 76.94);
INSERT INTO `scores` VALUES (397, 67, '政治', 96.57);
INSERT INTO `scores` VALUES (398, 68, '生物', 73.41);
INSERT INTO `scores` VALUES (399, 68, '历史', 81.86);
INSERT INTO `scores` VALUES (400, 68, '语文', 93.60);
INSERT INTO `scores` VALUES (401, 68, '物理', 95.97);
INSERT INTO `scores` VALUES (402, 68, '数学', 94.97);
INSERT INTO `scores` VALUES (403, 68, '政治', 96.18);
INSERT INTO `scores` VALUES (404, 68, '英语', 60.57);
INSERT INTO `scores` VALUES (405, 68, '地理', 97.51);
INSERT INTO `scores` VALUES (406, 68, '化学', 79.85);
INSERT INTO `scores` VALUES (407, 69, '语文', 97.14);
INSERT INTO `scores` VALUES (408, 69, '英语', 75.52);
INSERT INTO `scores` VALUES (409, 69, '历史', 57.87);
INSERT INTO `scores` VALUES (410, 69, '数学', 92.22);
INSERT INTO `scores` VALUES (411, 69, '地理', 89.18);
INSERT INTO `scores` VALUES (412, 69, '物理', 65.03);
INSERT INTO `scores` VALUES (413, 69, '生物', 68.26);
INSERT INTO `scores` VALUES (414, 70, '生物', 60.68);
INSERT INTO `scores` VALUES (415, 70, '政治', 63.76);
INSERT INTO `scores` VALUES (416, 70, '英语', 82.36);
INSERT INTO `scores` VALUES (417, 70, '化学', 96.17);
INSERT INTO `scores` VALUES (418, 70, '地理', 80.99);
INSERT INTO `scores` VALUES (419, 70, '数学', 78.70);
INSERT INTO `scores` VALUES (420, 70, '历史', 88.28);
INSERT INTO `scores` VALUES (421, 71, '语文', 76.33);
INSERT INTO `scores` VALUES (422, 71, '生物', 96.15);
INSERT INTO `scores` VALUES (423, 71, '物理', 83.73);
INSERT INTO `scores` VALUES (424, 71, '政治', 66.05);
INSERT INTO `scores` VALUES (425, 72, '历史', 98.05);
INSERT INTO `scores` VALUES (426, 72, '政治', 99.48);
INSERT INTO `scores` VALUES (427, 72, '英语', 61.36);
INSERT INTO `scores` VALUES (428, 72, '地理', 52.89);
INSERT INTO `scores` VALUES (429, 72, '物理', 98.89);
INSERT INTO `scores` VALUES (430, 72, '生物', 98.12);
INSERT INTO `scores` VALUES (431, 72, '语文', 78.18);
INSERT INTO `scores` VALUES (432, 72, '化学', 60.28);
INSERT INTO `scores` VALUES (433, 73, '历史', 83.45);
INSERT INTO `scores` VALUES (434, 73, '语文', 97.72);
INSERT INTO `scores` VALUES (435, 73, '地理', 75.20);
INSERT INTO `scores` VALUES (436, 73, '数学', 59.27);
INSERT INTO `scores` VALUES (437, 73, '生物', 61.01);
INSERT INTO `scores` VALUES (438, 73, '英语', 97.81);
INSERT INTO `scores` VALUES (439, 73, '政治', 88.94);
INSERT INTO `scores` VALUES (440, 74, '英语', 62.97);
INSERT INTO `scores` VALUES (441, 74, '地理', 59.41);
INSERT INTO `scores` VALUES (442, 74, '化学', 84.06);
INSERT INTO `scores` VALUES (443, 74, '历史', 86.34);
INSERT INTO `scores` VALUES (444, 75, '化学', 81.40);
INSERT INTO `scores` VALUES (445, 75, '英语', 87.22);
INSERT INTO `scores` VALUES (446, 75, '历史', 93.58);
INSERT INTO `scores` VALUES (447, 75, '生物', 52.87);
INSERT INTO `scores` VALUES (448, 75, '物理', 69.38);
INSERT INTO `scores` VALUES (449, 75, '语文', 86.42);
INSERT INTO `scores` VALUES (450, 75, '地理', 59.34);
INSERT INTO `scores` VALUES (451, 75, '政治', 57.73);
INSERT INTO `scores` VALUES (452, 76, '地理', 73.47);
INSERT INTO `scores` VALUES (453, 76, '生物', 90.74);
INSERT INTO `scores` VALUES (454, 76, '英语', 75.16);
INSERT INTO `scores` VALUES (455, 76, '历史', 93.16);
INSERT INTO `scores` VALUES (456, 77, '化学', 52.38);
INSERT INTO `scores` VALUES (457, 77, '历史', 50.01);
INSERT INTO `scores` VALUES (458, 77, '物理', 53.68);
INSERT INTO `scores` VALUES (459, 77, '政治', 86.40);
INSERT INTO `scores` VALUES (460, 77, '生物', 89.34);
INSERT INTO `scores` VALUES (461, 77, '语文', 78.12);
INSERT INTO `scores` VALUES (462, 77, '地理', 63.46);
INSERT INTO `scores` VALUES (463, 77, '英语', 69.11);
INSERT INTO `scores` VALUES (464, 77, '数学', 92.78);
INSERT INTO `scores` VALUES (465, 78, '历史', 80.81);
INSERT INTO `scores` VALUES (466, 78, '政治', 50.82);
INSERT INTO `scores` VALUES (467, 78, '物理', 96.73);
INSERT INTO `scores` VALUES (468, 78, '生物', 58.42);
INSERT INTO `scores` VALUES (469, 78, '英语', 55.26);
INSERT INTO `scores` VALUES (470, 79, '物理', 57.35);
INSERT INTO `scores` VALUES (471, 79, '历史', 51.03);
INSERT INTO `scores` VALUES (472, 79, '英语', 96.67);
INSERT INTO `scores` VALUES (473, 79, '政治', 59.14);
INSERT INTO `scores` VALUES (474, 79, '数学', 83.36);
INSERT INTO `scores` VALUES (475, 79, '地理', 54.43);
INSERT INTO `scores` VALUES (476, 79, '语文', 77.19);
INSERT INTO `scores` VALUES (477, 79, '化学', 83.64);
INSERT INTO `scores` VALUES (478, 79, '生物', 57.51);
INSERT INTO `scores` VALUES (479, 80, '历史', 50.46);
INSERT INTO `scores` VALUES (480, 80, '地理', 81.03);
INSERT INTO `scores` VALUES (481, 80, '数学', 66.76);
INSERT INTO `scores` VALUES (482, 80, '语文', 53.59);
INSERT INTO `scores` VALUES (483, 80, '政治', 70.15);
INSERT INTO `scores` VALUES (484, 80, '化学', 60.38);
INSERT INTO `scores` VALUES (485, 80, '生物', 94.18);
INSERT INTO `scores` VALUES (486, 80, '物理', 67.49);
INSERT INTO `scores` VALUES (487, 81, '数学', 83.84);
INSERT INTO `scores` VALUES (488, 81, '地理', 90.39);
INSERT INTO `scores` VALUES (489, 81, '语文', 68.91);
INSERT INTO `scores` VALUES (490, 81, '历史', 89.57);
INSERT INTO `scores` VALUES (491, 81, '化学', 55.13);
INSERT INTO `scores` VALUES (492, 82, '英语', 51.14);
INSERT INTO `scores` VALUES (493, 82, '化学', 57.76);
INSERT INTO `scores` VALUES (494, 82, '物理', 84.69);
INSERT INTO `scores` VALUES (495, 82, '生物', 60.55);
INSERT INTO `scores` VALUES (496, 82, '政治', 56.38);
INSERT INTO `scores` VALUES (497, 82, '数学', 97.21);
INSERT INTO `scores` VALUES (498, 82, '语文', 60.05);
INSERT INTO `scores` VALUES (499, 82, '地理', 93.02);
INSERT INTO `scores` VALUES (500, 83, '生物', 73.52);
INSERT INTO `scores` VALUES (501, 83, '政治', 68.43);
INSERT INTO `scores` VALUES (502, 83, '化学', 83.79);
INSERT INTO `scores` VALUES (503, 83, '物理', 80.36);
INSERT INTO `scores` VALUES (504, 83, '数学', 91.70);
INSERT INTO `scores` VALUES (505, 83, '地理', 54.09);
INSERT INTO `scores` VALUES (506, 83, '英语', 91.96);
INSERT INTO `scores` VALUES (507, 83, '语文', 58.34);
INSERT INTO `scores` VALUES (508, 84, '化学', 55.00);
INSERT INTO `scores` VALUES (509, 84, '数学', 91.18);
INSERT INTO `scores` VALUES (510, 84, '地理', 59.66);
INSERT INTO `scores` VALUES (511, 84, '物理', 90.13);
INSERT INTO `scores` VALUES (512, 84, '生物', 76.15);
INSERT INTO `scores` VALUES (513, 85, '生物', 91.03);
INSERT INTO `scores` VALUES (514, 85, '地理', 68.46);
INSERT INTO `scores` VALUES (515, 85, '政治', 99.92);
INSERT INTO `scores` VALUES (516, 86, '物理', 77.98);
INSERT INTO `scores` VALUES (517, 86, '语文', 72.24);
INSERT INTO `scores` VALUES (518, 86, '历史', 78.94);
INSERT INTO `scores` VALUES (519, 86, '地理', 78.53);
INSERT INTO `scores` VALUES (520, 86, '化学', 77.68);
INSERT INTO `scores` VALUES (521, 86, '英语', 62.81);
INSERT INTO `scores` VALUES (522, 86, '数学', 76.97);
INSERT INTO `scores` VALUES (523, 87, '政治', 50.76);
INSERT INTO `scores` VALUES (524, 87, '数学', 50.76);
INSERT INTO `scores` VALUES (525, 87, '英语', 57.91);
INSERT INTO `scores` VALUES (526, 87, '物理', 67.91);
INSERT INTO `scores` VALUES (527, 87, '化学', 63.37);
INSERT INTO `scores` VALUES (528, 87, '生物', 99.78);
INSERT INTO `scores` VALUES (529, 87, '历史', 67.78);
INSERT INTO `scores` VALUES (530, 88, '政治', 64.24);
INSERT INTO `scores` VALUES (531, 88, '历史', 96.22);
INSERT INTO `scores` VALUES (532, 88, '生物', 67.70);
INSERT INTO `scores` VALUES (533, 88, '物理', 97.17);
INSERT INTO `scores` VALUES (534, 89, '数学', 56.21);
INSERT INTO `scores` VALUES (535, 89, '生物', 56.08);
INSERT INTO `scores` VALUES (536, 89, '历史', 75.16);
INSERT INTO `scores` VALUES (537, 89, '语文', 92.80);
INSERT INTO `scores` VALUES (538, 89, '英语', 85.94);
INSERT INTO `scores` VALUES (539, 89, '地理', 67.06);
INSERT INTO `scores` VALUES (540, 89, '政治', 92.18);
INSERT INTO `scores` VALUES (541, 89, '物理', 79.13);
INSERT INTO `scores` VALUES (542, 89, '化学', 83.59);
INSERT INTO `scores` VALUES (543, 90, '历史', 88.92);
INSERT INTO `scores` VALUES (544, 90, '英语', 96.88);
INSERT INTO `scores` VALUES (545, 90, '物理', 77.03);
INSERT INTO `scores` VALUES (546, 90, '生物', 98.21);
INSERT INTO `scores` VALUES (547, 90, '化学', 68.80);
INSERT INTO `scores` VALUES (548, 90, '地理', 54.36);
INSERT INTO `scores` VALUES (549, 90, '语文', 50.66);
INSERT INTO `scores` VALUES (550, 91, '生物', 97.84);
INSERT INTO `scores` VALUES (551, 91, '政治', 60.34);
INSERT INTO `scores` VALUES (552, 91, '地理', 64.59);
INSERT INTO `scores` VALUES (553, 91, '化学', 70.92);
INSERT INTO `scores` VALUES (554, 91, '英语', 81.74);
INSERT INTO `scores` VALUES (555, 92, '物理', 96.64);
INSERT INTO `scores` VALUES (556, 92, '政治', 92.11);
INSERT INTO `scores` VALUES (557, 92, '英语', 94.32);
INSERT INTO `scores` VALUES (558, 92, '生物', 87.93);
INSERT INTO `scores` VALUES (559, 92, '化学', 96.32);
INSERT INTO `scores` VALUES (560, 92, '地理', 80.40);
INSERT INTO `scores` VALUES (561, 93, '物理', 89.73);
INSERT INTO `scores` VALUES (562, 93, '语文', 68.76);
INSERT INTO `scores` VALUES (563, 93, '化学', 64.65);
INSERT INTO `scores` VALUES (564, 93, '政治', 90.58);
INSERT INTO `scores` VALUES (565, 93, '地理', 74.85);
INSERT INTO `scores` VALUES (566, 93, '生物', 83.68);
INSERT INTO `scores` VALUES (567, 93, '英语', 97.43);
INSERT INTO `scores` VALUES (568, 94, '英语', 95.29);
INSERT INTO `scores` VALUES (569, 94, '政治', 70.73);
INSERT INTO `scores` VALUES (570, 94, '物理', 85.92);
INSERT INTO `scores` VALUES (571, 94, '数学', 72.08);
INSERT INTO `scores` VALUES (572, 94, '地理', 54.57);
INSERT INTO `scores` VALUES (573, 94, '化学', 88.79);
INSERT INTO `scores` VALUES (574, 94, '生物', 85.02);
INSERT INTO `scores` VALUES (575, 94, '历史', 90.01);
INSERT INTO `scores` VALUES (576, 95, '数学', 70.16);
INSERT INTO `scores` VALUES (577, 95, '语文', 95.88);
INSERT INTO `scores` VALUES (578, 95, '物理', 72.01);
INSERT INTO `scores` VALUES (579, 96, '化学', 90.94);
INSERT INTO `scores` VALUES (580, 96, '英语', 70.53);
INSERT INTO `scores` VALUES (581, 96, '地理', 63.21);
INSERT INTO `scores` VALUES (582, 96, '生物', 53.92);
INSERT INTO `scores` VALUES (583, 96, '语文', 69.29);
INSERT INTO `scores` VALUES (584, 96, '政治', 53.84);
INSERT INTO `scores` VALUES (585, 96, '数学', 93.64);
INSERT INTO `scores` VALUES (586, 97, '物理', 81.38);
INSERT INTO `scores` VALUES (587, 97, '生物', 51.47);
INSERT INTO `scores` VALUES (588, 97, '政治', 57.58);
INSERT INTO `scores` VALUES (589, 97, '地理', 75.78);
INSERT INTO `scores` VALUES (590, 97, '历史', 71.77);
INSERT INTO `scores` VALUES (591, 97, '化学', 97.71);
INSERT INTO `scores` VALUES (592, 98, '生物', 67.79);
INSERT INTO `scores` VALUES (593, 98, '数学', 51.74);
INSERT INTO `scores` VALUES (594, 98, '英语', 63.89);
INSERT INTO `scores` VALUES (595, 98, '地理', 73.69);
INSERT INTO `scores` VALUES (596, 98, '政治', 78.01);
INSERT INTO `scores` VALUES (597, 98, '语文', 81.11);
INSERT INTO `scores` VALUES (598, 98, '历史', 95.96);
INSERT INTO `scores` VALUES (599, 98, '化学', 56.64);
INSERT INTO `scores` VALUES (600, 99, '生物', 93.09);
INSERT INTO `scores` VALUES (601, 99, '语文', 94.23);
INSERT INTO `scores` VALUES (602, 99, '物理', 76.60);
INSERT INTO `scores` VALUES (603, 99, '英语', 85.57);
INSERT INTO `scores` VALUES (604, 99, '地理', 91.08);
INSERT INTO `scores` VALUES (605, 99, '政治', 91.57);
INSERT INTO `scores` VALUES (606, 99, '历史', 50.65);
INSERT INTO `scores` VALUES (607, 100, '地理', 74.68);
INSERT INTO `scores` VALUES (608, 100, '政治', 68.02);
INSERT INTO `scores` VALUES (609, 100, '生物', 97.37);
INSERT INTO `scores` VALUES (610, 100, '物理', 80.49);
INSERT INTO `scores` VALUES (611, 100, '数学', 64.42);
INSERT INTO `scores` VALUES (612, 100, '历史', 73.37);

SET FOREIGN_KEY_CHECKS = 1;

问题

1. 查询所有学生的基本信息

SELECT * FROM students;

2. 查询每个学生的所有科目及其对应的成绩

SELECT s.student_id, s.name, sc.subject, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
ORDER BY s.student_id, sc.subject;

3. 查询平均分大于 60 的学生姓名和 ID

SELECT s.student_id, s.name
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name
HAVING AVG(sc.score) > 60;

4. 查询平均分大于 80 的李姓同学的各科成绩

select a.`name`,c.`subject`,c.score from students a 
JOIN (
	select student_id from scores
	GROUP BY student_id
	HAVING AVG(score) > 80
) b
on a.student_id = b.student_id
LEFT JOIN scores c
on a.student_id = c.student_id

5. 查询某个学生的具体成绩

SELECT s.NAME, sc.subject, sc.score
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
WHERE s.NAME = "刘八";

6. 查询最高分的学生姓名及对应的科目

select a.name,b.`subject`,b.score from students a JOIN scores b
on a.student_id = b.student_id
where b.score = (select MAX(score) from scores)

7. 查询每门科目的平均成绩

select subject, AVG(score) from scores GROUP BY subject

8. 查询每个学生的最高成绩

第一种方法
select a.student_id,a.`name`,b.max_score FROM students a
JOIN (select student_id,MAX(score) as max_score from scores GROUP BY student_id) b 
on a.student_id = b.student_id

第二种方法
select a.student_id,a.name, MAX(b.score) from students a 
JOIN scores b on a.student_id = b.student_id GROUP BY a.`name`, a.student_id

9. 查询某个特定学生（例如张三）的平均成绩

第一种查法
select a.student_id ,a.name,AVG(b.score) from students a 
JOIN scores b 
on a.student_id = b.student_id WHERE a.`name` = "张三" GROUP BY a.name,a.student_id

第二种查法
select a.student_id ,a.name,AVG(b.score) from students a 
JOIN scores b 
on a.student_id = b.student_id GROUP BY a.name,a.student_id HAVING a.`name` = "张三"

10. 查询各个学生及其各科成绩，并按总成绩降序排列

select a.student_id,a.`name`,c.subject,c.score,b.total_score 
from students a 
JOIN (select student_id,sum(score) as total_score 
			from scores GROUP BY student_id ORDER BY total_score desc) b
on a.student_id = b.student_id
JOIN scores c on a.student_id = c.student_id

11. 查询所有学生的成绩排名（按照总成绩）

SELECT s.student_id, s.name, SUM(sc.score) AS total_score,
       RANK() OVER (ORDER BY SUM(sc.score) DESC) AS rank
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name
ORDER BY rank;

12. 查询每个科目的最高分和获得最高分的学生姓名

select  a.student_id,a.name, b.score from students a 
JOIN scores b 
ON a.student_id = b.student_id 
where (b.subject,b.score) in (select subject,MAX(score) from scores GROUP BY subject);

13. 查询各科成绩不及格的学生信息（<80不及格）

select a.student_id,a.`name`,b.`subject`,b.score 
from students a 
JOIN scores b on a.student_id = b.student_id
where b.score < 80

14. 查询每个学生的各科成绩与对应科目的平均成绩的差值

select a.student_id,a.`name`,b.`subject`,b.score ,
		 c.avg_score, b.score-c.avg_score as Difference_value 
from students a 
JOIN scores b on a.student_id = b.student_id 
JOIN (select subject,avg(score) as avg_score from scores GROUP BY subject) c 
on b.subject = c.subject

15. 查询各科成绩最高和最低分之间的差值

第一种
select max_score,min_score,max_score - min_score as Difference_value  
from (
	select MAX(score) as max_score,MIN(score) as min_score from scores GROUP BY subject
	) a

第二种
select MAX(score) as max_score, MIN(score) as min_score,
		 MAX(score)-MIN(score) as Difference_value  
from scores 
GROUP BY subject

16. 查询每个学生的成绩排名（按总成绩和每科成绩的综合权重）

SELECT s.student_id, s.name, 
       SUM(sc.score) AS total_score,
       RANK() OVER (ORDER BY SUM(sc.score) DESC) AS rank
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name
ORDER BY rank;

17. 查询每个科目成绩最高的前两名学生信息

select a.student_id,a.`name`,b.`subject`,b.score from students a 
JOIN scores b on a.student_id = b.student_id
where (b.subject,b.score) 
IN (select subject,score 
		from (select subject, score, RANK() OVER(PARTITION BY subject ORDER BY score desc)
		 as rank_solt from scores) c where c.rank_solt >=2)

18. 查询所有学生的科目成绩，按照总成绩进行排名，并显示每个学生高于平均成绩的科目数量

WITH student_total_scores AS (
    SELECT s.student_id, s.name, SUM(sc.score) AS total_score
    FROM students s
    JOIN scores sc ON s.student_id = sc.student_id
    GROUP BY s.student_id, s.name
),
average_scores AS (
    SELECT subject, AVG(score) AS avg_score
    FROM scores
    GROUP BY subject
)
SELECT sts.student_id, sts.name, sts.total_score,
       COUNT(sc.subject) AS subjects_above_average
FROM student_total_scores sts
JOIN scores sc ON sts.student_id = sc.student_id
JOIN average_scores avg ON sc.subject = avg.subject
WHERE sc.score > avg.avg_score
GROUP BY sts.student_id, sts.name, sts.total_score
ORDER BY sts.total_score DESC;

##19. 查询学生的成绩情况，并按总成绩进行分类

SELECT s.student_id, s.name, 
       SUM(sc.score) AS total_score,
       CASE
           WHEN SUM(sc.score) >= 270 THEN '优秀'
           WHEN SUM(sc.score) BETWEEN 180 AND 269 THEN '良好'
           WHEN SUM(sc.score) BETWEEN 120 AND 179 THEN '及格'
           ELSE '不及格'
       END AS performance
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name
ORDER BY total_score DESC;

20. 查询学生的各科成绩，添加成绩的等级（例如A、B、C、D、F）

SELECT s.student_id, s.name, sc.subject, sc.score,
       CASE
           WHEN sc.score >= 90 THEN 'A'
           WHEN sc.score >= 80 THEN 'B'
           WHEN sc.score >= 70 THEN 'C'
           WHEN sc.score >= 60 THEN 'D'
           ELSE 'F'
       END AS grade
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
ORDER BY s.student_id, sc.subject;

21. 查询每个学生缺少的科目

WITH all_subjects AS (
    SELECT DISTINCT subject FROM scores
),
student_subjects AS (
    SELECT s.student_id, s.name, subj.subject
    FROM students s
    CROSS JOIN all_subjects subj
    LEFT JOIN scores sc ON s.student_id = sc.student_id AND subj.subject = sc.subject
    WHERE sc.subject IS NULL
)
SELECT student_id, name, subject
FROM student_subjects
ORDER BY student_id, subject;

22. 查询成绩在全班的排名百分比

SELECT s.student_id, s.name, sc.subject, sc.score,
       PERCENT_RANK() OVER (PARTITION BY sc.subject ORDER BY sc.score DESC) * 100 AS percent_rank
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
ORDER BY sc.subject, percent_rank DESC;
X-⃢_⃢-X
关注
【AI大模型】Prompt 提示词工程使用详解
congge
07-28 2万+
Prompt 提示词工程使用详解
SQL练习
Arcgo的博客
04-05 346
题目及数据来源 https://blog.youkuaiyun.com/qq_45574891/article/details/102559547 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息 #根据返回的子查询中的SId来返回学生信息 select * from student where SId in( select SId from ( #将课程01的成绩和课程02的成绩单独做成...
sql 练习
09-28 446
--建表 --学生表 create table student ( --学号 sno varchar2(3) not null primary key, --姓名 sname varchar2(4) not null, --性别 ssex varchar2(2)
50道SQL练习题
最新发布
m0_74851380的博客
04-14 824
-1.学生表--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表--CId 课程编号,Cname 课程名称,TId 教师编号--3.教师表--TId 教师编号,Tname 教师姓名--4.成绩表--SId 学生编号,CId 课程编号,score 分数。
sql练习
qq_41609957的博客
12-11 533
use master create database Test go -------------- use Test --学生表 if exists (select *from sysobjects where name='Students') drop table Students,Score go create table Students( stuId int primar...
经典SQL练习题
热门推荐
qaz13177_58_的专栏
05-10 27万+
今天在网上找了几道经典的SQL练习题做了一下，虽然都不难，但是对打基础是很有好处的，在明白的基础上可以进一步做分析，来研究一下各种解法的优劣，甚至进行简单的优化。。现在将题目和答案分享一下。我使用的是MYSQL 5.0，但是绝大部分都是标准SQL。表结构：CREATE TABLE STUDENT(SNO VARCHAR(3) NOT NULL, SNAME VARCHAR(4) NOT NULL,
数据分析初学入门——22本SQL学习书籍推荐
Data_lukyo的博客
01-12 8757
很多数据分析初学者都被推荐学SQL，SQL是最基础且是必备的技能之一，但是不知道该从何学起，很多课程讲了很多知识点，但是初学的朋友没有对这一领域整体框架的认识，不知道该学哪些知识、有哪些知识、实际工作需要到什么程度，常常会担心自己该用的没学、学了的没理解或不会用等等。这里通过介绍21本学习SQL的经典书籍（可以说几乎所有人们经典书籍都囊括在内，对初学者、从业者都认可和检验的）。
【进大厂必学】3W字180张图学习Linux基础总结
L的存在的博客
05-26 4881
就不多说这段时间干啥去了吧，期间和很多的同学聊了天，有的童鞋已经开始工作，聊了聊工作上的事儿。有的是今年即将毕业的童鞋，有着自己的小目标，有的想尝试互联网，所以现在基本上都快进行二轮的复习了，有的同学备战公务员，凭着年轻这股劲儿向往自己理想的生活状态，无论怎么样，长路漫漫，走一步，算一步，每一步都算数。 今天分享的这篇文章是 Linux 相关的基础知识，深一点的内容基本上没有，不过对于刚需小伙伴来说，也就够了，有时间的话，最好按照这些命令去试一试，敲一敲，这样记忆更加深刻。 老规矩，先看目录，文章比较长，建
牛客网数据库SQL实战题库体验及刷题心得
"Actual-Combat"（实战）一词表明内容可能更偏向于实用技能的培养，旨在帮助用户将理论知识应用到实际的数据库管理任务中。 总体来看，该资源汇总了多道SQL练习题，并且提供了对使用sqlite数据库进行SQL实战的详细...
熟悉sql软考选什么
02-26
虽然该职位名称看起来更偏向于管理和运维方面的工作职责，但在实际工作中也涉及到大量的数据处理工作，因此同样会考察到SQL的应用能力[^5]。考生需具备一定的数据库操作经验和理解业务逻辑的能力。 #### 软件设计师...
MySQL数据库学习
Json111的博客
02-16 841
建表 学生表 create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))DEFAULT charset = utf8; insert into Student values('01' , '赵雷' , '1990-01-01' , '男') insert into Student...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值