MySQL 单表操作查询------子查询

悠然小居

已于 2024-04-19 15:02:56 修改

阅读量221

点赞数 7

文章标签： java 前端 javascript

于 2024-04-19 15:01:09 首次发布

本文链接：https://blog.csdn.net/2301_80160500/article/details/137964711

版权

1. 创建数据表------DDL语句

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseName` varchar(20) NOT NULL COMMENT '课程名称',
  `department` varchar(30) NOT NULL,
  `lv` int(11) DEFAULT NULL COMMENT '年级',
  `number` int(11) DEFAULT NULL COMMENT '课程人数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseId` int(11) NOT NULL COMMENT '课程编号',
  `studentId` int(11) NOT NULL,
  `result` float(5,2) NOT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=81 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `createDate` datetime DEFAULT NULL COMMENT '创建时间',
  `modifyDate` datetime DEFAULT NULL COMMENT '修改时间',
  `userName` varchar(30) NOT NULL COMMENT '学生名称',
  `pwd` varchar(36) DEFAULT NULL COMMENT '密码',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别',
  `className` varchar(20) DEFAULT NULL,
  `addRess` varchar(255) DEFAULT NULL COMMENT '地址',
  `introduce` varchar(255) DEFAULT NULL COMMENT '简介',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

2、插入数据--------DML

INSERT INTO `course` VALUES (1, '计算机基础', '信息工程系', 1, 800);
INSERT INTO `course` VALUES (2, 'MySQL应用基础', '信息工程系', 2, 567);
INSERT INTO `course` VALUES (3, 'Java基础', '信息工程系', 1, 567);
INSERT INTO `course` VALUES (4, '专业导论', '信息工程系', 1, 645);
INSERT INTO `course` VALUES (5, 'Excel实战训练', '信息工程系', 1, 863);
INSERT INTO `course` VALUES (6, '大学英语', '教务处', 1, 432);
INSERT INTO `course` VALUES (7, '大学语文', '教务处', 1, 533);
INSERT INTO `course` VALUES (8, '高等数学(一)', '教务处', 2, 456);

INSERT INTO `score` VALUES (1, 1, 1, 89.00);
INSERT INTO `score` VALUES (2, 1, 2, 89.50);
INSERT INTO `score` VALUES (3, 1, 3, 98.00);
INSERT INTO `score` VALUES (4, 1, 4, 82.00);
INSERT INTO `score` VALUES (5, 1, 5, 59.00);
INSERT INTO `score` VALUES (6, 1, 6, 22.00);
INSERT INTO `score` VALUES (7, 1, 7, 68.00);
INSERT INTO `score` VALUES (8, 1, 8, 7.00);
INSERT INTO `score` VALUES (9, 1, 9, 91.00);
INSERT INTO `score` VALUES (10, 1, 10, 69.00);
INSERT INTO `score` VALUES (11, 2, 1, 99.00);
INSERT INTO `score` VALUES (12, 2, 2, 69.50);
INSERT INTO `score` VALUES (13, 2, 3, 58.00);
INSERT INTO `score` VALUES (14, 2, 4, 72.00);
INSERT INTO `score` VALUES (15, 2, 5, 89.00);
INSERT INTO `score` VALUES (16, 2, 6, 82.00);
INSERT INTO `score` VALUES (17, 2, 7, 58.00);
INSERT INTO `score` VALUES (18, 2, 8, 77.00);
INSERT INTO `score` VALUES (19, 2, 9, 17.00);
INSERT INTO `score` VALUES (20, 2, 10, 79.00);
INSERT INTO `score` VALUES (21, 3, 1, 55.00);
INSERT INTO `score` VALUES (22, 3, 2, 77.50);
INSERT INTO `score` VALUES (23, 3, 3, 88.00);
INSERT INTO `score` VALUES (24, 3, 4, 12.00);
INSERT INTO `score` VALUES (25, 3, 5, 88.00);
INSERT INTO `score` VALUES (26, 3, 6, 71.00);
INSERT INTO `score` VALUES (27, 3, 7, 36.00);
INSERT INTO `score` VALUES (28, 3, 8, 94.00);
INSERT INTO `score` VALUES (29, 3, 9, 66.00);
INSERT INTO `score` VALUES (30, 3, 10, 34.00);
INSERT INTO `score` VALUES (31, 4, 1, 55.00);
INSERT INTO `score` VALUES (32, 4, 2, 87.50);
INSERT INTO `score` VALUES (33, 4, 3, 88.00);
INSERT INTO `score` VALUES (34, 4, 4, 82.00);
INSERT INTO `score` VALUES (35, 4, 5, 88.00);
INSERT INTO `score` VALUES (36, 4, 6, 31.00);
INSERT INTO `score` VALUES (37, 4, 7, 86.00);
INSERT INTO `score` VALUES (38, 4, 8, 94.00);
INSERT INTO `score` VALUES (39, 4, 9, 86.00);
INSERT INTO `score` VALUES (40, 4, 10, 34.00);
INSERT INTO `score` VALUES (41, 5, 1, 95.00);
INSERT INTO `score` VALUES (42, 5, 2, 27.50);
INSERT INTO `score` VALUES (43, 5, 3, 88.00);
INSERT INTO `score` VALUES (44, 5, 4, 82.60);
INSERT INTO `score` VALUES (45, 5, 5, 88.00);
INSERT INTO `score` VALUES (46, 5, 6, 31.50);
INSERT INTO `score` VALUES (47, 5, 7, 86.50);
INSERT INTO `score` VALUES (48, 5, 8, 94.70);
INSERT INTO `score` VALUES (49, 5, 9, 86.00);
INSERT INTO `score` VALUES (50, 5, 10, 34.00);
INSERT INTO `score` VALUES (51, 6, 1, 75.00);
INSERT INTO `score` VALUES (52, 6, 2, 77.50);
INSERT INTO `score` VALUES (53, 6, 3, 88.00);
INSERT INTO `score` VALUES (54, 6, 4, 72.60);
INSERT INTO `score` VALUES (55, 6, 5, 88.00);
INSERT INTO `score` VALUES (56, 6, 6, 71.50);
INSERT INTO `score` VALUES (57, 6, 7, 76.50);
INSERT INTO `score` VALUES (58, 6, 8, 94.70);
INSERT INTO `score` VALUES (59, 6, 9, 76.00);
INSERT INTO `score` VALUES (60, 6, 10, 74.00);
INSERT INTO `score` VALUES (61, 7, 1, 75.00);
INSERT INTO `score` VALUES (62, 7, 2, 67.50);
INSERT INTO `score` VALUES (63, 7, 3, 68.00);
INSERT INTO `score` VALUES (64, 7, 4, 72.60);
INSERT INTO `score` VALUES (65, 7, 5, 88.00);
INSERT INTO `score` VALUES (66, 7, 6, 61.50);
INSERT INTO `score` VALUES (67, 7, 7, 76.50);
INSERT INTO `score` VALUES (68, 7, 8, 64.70);
INSERT INTO `score` VALUES (69, 7, 9, 76.00);
INSERT INTO `score` VALUES (70, 7, 10, 64.00);
INSERT INTO `score` VALUES (71, 8, 1, 95.00);
INSERT INTO `score` VALUES (72, 8, 2, 97.00);
INSERT INTO `score` VALUES (73, 8, 3, 98.00);
INSERT INTO `score` VALUES (74, 8, 4, 92.00);
INSERT INTO `score` VALUES (75, 8, 5, 98.00);
INSERT INTO `score` VALUES (76, 8, 6, 91.00);
INSERT INTO `score` VALUES (77, 8, 7, 96.00);
INSERT INTO `score` VALUES (78, 8, 8, 94.00);
INSERT INTO `score` VALUES (79, 8, 9, 96.00);
INSERT INTO `score` VALUES (80, 8, 10, 94.00);

INSERT INTO `student` VALUES (1, '2023-09-16 09:22:22', '2024-04-15 10:12:12', '张三', '123456', '15612345678', 19, '男', '信息1班', '石家庄', '一首张三的歌送给大家');
INSERT INTO `student` VALUES (2, '2023-09-17 09:22:22', '2024-04-15 08:11:12', '李四', '123156', '15612345178', 19, '女', '信息1班', '石家庄', '一曲美丽的舞蹈送给大家');
INSERT INTO `student` VALUES (3, '2023-09-18 09:23:22', '2024-04-15 11:12:12', '王五', '123256', '15612345278', 20, '男', '信息2班', '沈阳', '大刀王五也是英雄');
INSERT INTO `student` VALUES (4, '2023-09-19 09:24:22', '2024-04-15 12:13:12', '赵六', '123356', '15612345378', 18, '男', '信息1班', '甘肃', '六六大顺');
INSERT INTO `student` VALUES (5, '2023-09-10 09:25:22', '2024-04-15 13:14:12', '阮小七', '143456', '15612345678', 19, '男', '信息2班', '石家庄', '阮氏三雄，小七最霸气');
INSERT INTO `student` VALUES (6, '2023-09-21 09:26:22', '2024-04-15 14:15:12', '朱重八', '153456', '15612355678', 18, '男', '信息2班', '浙江', '开局一个碗。');
INSERT INTO `student` VALUES (7, '2023-09-22 09:27:22', '2024-04-15 15:16:12', '苏老九', '163456', '15612365678', 20, '男', '信息2班', '石家庄', '武状元');
INSERT INTO `student` VALUES (8, '2023-09-23 09:28:22', '2024-04-15 16:17:12', '王石', '123476', '15612345778', 22, '男', '信息1班', '徐蚌', '钻石王老五');
INSERT INTO `student` VALUES (9, '2023-09-24 19:29:22', '2024-04-15 17:18:12', '萧十一', '128456', '15612385678', 21, '男', '信息1班', '石家庄', '帅气逼人，英雄也。');
INSERT INTO `student` VALUES (10, '2023-09-25 09:20:22', '2024-04-15 18:19:12', '宫十二', '129456', '15612395678', 22, '女', '信息1班', '杭州', '十二条舔狗和一位绿茶的故事');

子查询------------DQL

select courseName "课程名称",department "院系",number "课程人数" from course 
where id = (
 select  courseId 
 from  score GROUP BY courseId ORDER BY AVG(result) desc LIMIT  1
)