schooldb库——utf8字符集——utf8_general_ci排序规则
先创建库，再去使用下列的DDL语句。
DDL
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 DEFAULT CHARSET=utf8; 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 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 DEFAULT CHARSET=utf8;
MDL
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班', '杭州', '十二条舔狗和一位绿茶的故事'); 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');
DQL
查询平均分最高的前3名科目名称以及所属院系与总人数?
# 子查询结果，找到了平均分最高的结果 SELECT courseId FROM score group by courseId order by avg(result) desc limit 3; # 父子查询 select courseName '科目名称',department '院系',number '人数' from course where id in (SELECT courseId FROM score group by courseId order by avg(result) desc )limit 3;
查询结果:
总结
子查询，多条信息查询的时候可以使用in关键字，但是子查询不能使用limit作为限制器，limit可以使用在父层结果查询的限制中。
