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;
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');
3.DQL
#子查询
select courseId from score GROUP BY courseId ORDER BY avg(result) desc limit 1;
#完整查询
select courseName '课程名称',department'院系',number'人数' from course where id = (
select courseId from score GROUP BY courseId ORDER BY avg(result) desc limit 1
);