mysql查询语句面试题二

先给出SQL语句正确的执行顺序:

from *tables*

where *predicae1*

group by *columns*

having *predicae1*

select *columns*

order by *columns*

limit *start*, *offset*;

三个表

• 学生表student(id,name)
• 课程表course(id,name)
• 学生课程表student_course(sid,cid,score)

 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '张三');
INSERT INTO `student` VALUES ('2', '李四');
INSERT INTO `student` VALUES ('3', '王五');
INSERT INTO `student` VALUES ('4', '李六');
INSERT INTO `student` VALUES ('6', '李六');
INSERT INTO `student` VALUES ('7', '张三');

-- ----------------------------
-- Table structure for student_course
-- ----------------------------
DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course` (
  `sid` int(10) unsigned NOT NULL,
  `cid` int(10) unsigned NOT NULL,
  `score` int(10) unsigned NOT NULL,
  PRIMARY KEY (`sid`,`cid`),
  KEY `cid` (`cid`),
  CONSTRAINT `student_course_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `student` (`id`),
  CONSTRAINT `student_course_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `course` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student_course
-- ----------------------------
INSERT INTO `student_course` VALUES ('1', '1', '80');
INSERT INTO `student_course` VALUES ('1', '2', '92');
INSERT INTO `student_course` VALUES ('2', '1', '90');
INSERT INTO `student_course` VALUES ('2', '2', '70');
INSERT INTO `student_course` VALUES ('3', '1', '90');
INSERT INTO `student_course` VALUES ('3', '2', '65');
INSERT INTO `student_course` VALUES ('4', '1', '95');
INSERT INTO `student_course` VALUES ('4', '2', '92');

 

 

查询题：

#1、查询student表中重名的学生，结果包含id和name，按name,id升序
SELECT id,name FROM student WHERE `name` in 
(SELECT name FROM student GROUP BY name HAVING(COUNT(*) > 1) ) 
ORDER BY `name`;

我们经常需要查询某一列重复的行，一般通过group by(有重复的列)然后取count>1的值。

#2、在student_course表中查询平均分>79的学生，列出学生id和平均分
SELECT sid,avg(score) avg_sc FROM student_course GROUP BY sid HAVING avg_sc > 79 ;

where子句中不能用聚集函数作为条件表达式，但是having短语可以，where和having的区别在于对用对象不同，where作用于记录，having作用于组。

#3、在student_course表中查询每门课成绩都不低于80的学生id
SELECT DISTINCT sid FROM student_course WHERE sid not in(
SELECT sid FROM student_course WHERE score < 80
);

#4、查询每个学生的总成绩，结果列出学生id,姓名和总成绩 
SELECT s.id, s.name,sum(sc.score) FROM student s LEFT JOIN student_course sc 
on s.id = sc.sid GROUP BY s.id;

#5、总成绩最高的学生，结果列出学生id和总成绩
SELECT sid,SUM(score) sum_sc FROM student_course GROUP BY sid ORDER BY  sum_sc desc LIMIT 1;

#6、在student_course表查询课程id为1成绩第2高的学生，如果第2高的不止一个则列出所有的学生
SELECT * FROM student_course WHERE score = (
SELECT score FROM student_course WHERE cid = 1 GROUP BY score  ORDER BY score DESC LIMIT 1,1
);

查询第N大数的问题,先查询出第二高的分数，然后再查第二高分数的学生

#7、在student_course表查询各科成绩最高的学生，结果列出学生id、课程id和对应的成绩
SELECT * FROM student_course x WHERE score >= (
SELECT MAX(score) FROM student_course y WHERE x.cid = y.cid
) ORDER BY cid;

有人可能会这样写 select sid,cid,max(score) from student_course group by cid;  
然而上面是不对的，因为 使用了group by的查询字段只能是group by中的字段或者聚集函数或者是每个分组内均相同的字段。 虽然不会报错，但是sid是无效的，如果去掉sid的话只能查出每门课程的最高分，不包含学生id。

#8、在student_course表中查询每门课的前2名，结果按课程id升序，同一课程按成绩降序 
SELECT * FROM student_course x WHERE 2>(
SELECT COUNT(*) FROM student_course y 
WHERE y.cid = x.cid AND
y.score > x.score
);

取每组的前N条纪录,相关嵌套查询，对于每一个分数，如果同一门课程下只有0个、1个分数比这个分数还高，那么这个分数肯定是前2名之一