mysql查询面试_mysql查询面试一

Teacher(Tid,Tname) 教师表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------

-- Table structure for course

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`Cid` int(11) NOT NULL AUTO_INCREMENT,

`Cname` varchar(255) DEFAULT NULL,

`Tid` int(11) DEFAULT NULL,

PRIMARY KEY (`Cid`),

KEY `fk_tid` (`Tid`),

CONSTRAINT `fk_tid` FOREIGN KEY (`Tid`) REFERENCES `teacher` (`Tid`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', '001', '1');

INSERT INTO `course` VALUES ('2', '002', '2');

INSERT INTO `course` VALUES ('3', '003', '3');

-- ----------------------------

-- Table structure for sc

-- ----------------------------

DROP TABLE IF EXISTS `sc`;

CREATE TABLE `sc` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`Sid` int(11) DEFAULT NULL,

`Cid` int(11) DEFAULT NULL,

`score` varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_sid` (`Sid`),

KEY `fk_cid` (`Cid`),

CONSTRAINT `fk_cid` FOREIGN KEY (`Cid`) REFERENCES `course` (`Cid`),

CONSTRAINT `fk_sid` FOREIGN KEY (`Sid`) REFERENCES `student` (`Sid`)

) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Records of sc

-- ----------------------------

INSERT INTO `sc` VALUES ('1', '1', '1', '80');

INSERT INTO `sc` VALUES ('2', '1', '2', '85');

INSERT INTO `sc` VALUES ('3', '1', '3', '90');

INSERT INTO `sc` VALUES ('4', '2', '1', '35');

INSERT INTO `sc` VALUES ('5', '2', '2', '92');

INSERT INTO `sc` VALUES ('6', '2', '3', '80');

INSERT INTO `sc` VALUES ('7', '3', '1', '90');

INSERT INTO `sc` VALUES ('8', '3', '2', '50');

INSERT INTO `sc` VALUES ('9', '3', '3', '80');

INSERT INTO `sc` VALUES ('10', '4', '1', '48');

INSERT INTO `sc` VALUES ('11', '5', '2', '60');

INSERT INTO `sc` VALUES ('12', '5', '3', '45');

INSERT INTO `sc` VALUES ('13', '4', '2', '59');

-- ----------------------------

-- Table structure for student

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`Sid` int(11) NOT NULL AUTO_INCREMENT,

`Sname` varchar(20) DEFAULT NULL,

`Sage` int(11) DEFAULT NULL,

`Ssex` varchar(2) DEFAULT NULL,

PRIMARY KEY (`Sid`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', 'student1', '1', '1');

INSERT INTO `student` VALUES ('2', 'student2', '2', '1');

INSERT INTO `student` VALUES ('3', 'student3', '3', '1');

INSERT INTO `student` VALUES ('4', 'student4', '4', '2');

INSERT INTO `student` VALUES ('5', 'student5', '5', '2');

INSERT INTO `student` VALUES ('6', 'student6', '6', '2');

-- ----------------------------

-- Table structure for teacher

-- ----------------------------

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

`Tid` int(11) NOT NULL AUTO_INCREMENT,

`Tname` varchar(20) DEFAULT NULL,

PRIMARY KEY (`Tid`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Records of teacher

-- ----------------------------

INSERT INTO `teacher` VALUES ('1', 'mike');

INSERT INTO `teacher` VALUES ('2', 'male');

INSERT INTO `teacher` VALUES ('3', 'jom');

查询:

#1、查询“001”课程比“002”课程成绩高的所有学生的学号；

SELECT a.sid FROM (SELECT sid,score FROM sc WHERE sc.Cid = '001') a,

(SELECT sid,score FROM sc WHERE sc.Cid = '002') b WHERE

a.sid = b.sid AND a.score > b.score;

#2、查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sid,avg(score) vv FROM sc GROUP BY Sid HAVING vv > 60;

#3、查询所有同学的学号、姓名、选课数、总成绩；

SELECT s.sid,s.sname,COUNT(sc.Cid),SUM(sc.score) FROM student s LEFT JOIN sc on s.Sid = sc.Sid GROUP BY s.Sid;

#4、查询m开头的老师的个数

SELECT COUNT(*) FROM teacher WHERE tname like "m%";

#5、查询没学过“mike”老师课的同学的学号、姓名；

SELECT * FROM student WHERE sid not in(

SELECT sid FROM sc WHERE cid in(

SELECT cid FROM course WHERE tid in(SELECT tid FROM teacher WHERE tname = 'mike')

)

);

--

SELECT * FROM student WHERE sid not in(

SELECT sc.sid FROM sc,course c,teacher t WHERE t.tname = 'mike' and t.tid = c.tid AND c.cid = sc.Cid

);

#6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

SELECT sid,Sname FROM student WHERE sid in(

SELECT a.sid FROM

(SELECT sc.sid FROM sc,course c WHERE c.Cname = '001' and c.cid =sc.cid) a,

(SELECT sc.sid FROM sc,course c WHERE c.Cname = '002' and c.cid =sc.cid) b

where a.sid = b.sid

);

--

SELECT s.sid,s.sname FROM student s,sc,course c where s.sid = sc.sid and c.cname='001'

and c.cid = sc.cid and EXISTS

(SELECT * FROM course c2,sc as sc2 WHERE c2.cname = '002' and c2.cid = sc2.cid and sc2.sid = s.sid);

#7、查询学过“jom”老师所教的所有课的同学的学号、姓名；

SELECT sid,sname FROM student WHERE sid in(

SELECT Sid FROM sc WHERE cid in(

SELECT cid FROM course WHERE tid in(SELECT tid FROM teacher WHERE Tname = 'jom')

)

);

--

SELECT sid,sname FROM student WHERE sid in(

SELECT sc.Sid FROM sc,course c,teacher t WHERE t.tname = 'jom' and t.tid = c.tid AND sc.cid = c.cid;

)

#8、查询所有课程成绩小于60分的同学的学号、姓名

SELECT sid,sname FROM student WHERE sid not in(

SELECT sid FROM sc where score > 60;

)

#9、查询没有学全所有课的同学的学号、姓名；

SELECT s.sid,s.sname FROM student s LEFT JOIN sc on s.sid = sc.Sid GROUP BY s.sid

HAVING COUNT(sc.cid) < (SELECT COUNT(*) FROM course);

#10、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT s.sid,s.Sname FROM student s,sc WHERE s.Sid = sc.Sid

AND sc.cid in(SELECT cid FROM sc WHERE Sid = 5);

#11、查出所有课程成绩最高和最低的学生id及其分数

SELECT 'hight',sc.* FROM sc,(SELECT MAX(score) as score,cid FROM sc GROUP BY Cid) b

WHERE sc.score = b.score AND sc.Cid = b.cid

UNION

SELECT 'low',sc.* FROM sc,(SELECT min(score) as score,cid FROM sc GROUP BY Cid) b

WHERE sc.score = b.score AND sc.Cid = b.cid;

#12、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cid,max(score),min(score) FROM sc GROUP BY sc.Cid;

--

SELECT l.cid,l.score max,r.score min FROM sc l,sc r WHERE l.Cid = r.Cid

AND l.score = (SELECT MAX(il.score) FROM sc il,student s WHERE il.cid = l.Cid and s.sid = il.sid GROUP BY il.cid)

and r.score = (SELECT MIN(ir.score) FROM sc ir WHERE ir.cid = r.Cid GROUP BY ir.cid)

ORDER BY l.cid ASC;

#13、查询学生平均成绩及其名次

SELECT sid,AVG(score) avg_sc FROM sc GROUP BY sid ORDER BY avg_sc DESC;

--

SELECT 1+(SELECT COUNT(DISTINCT avg_sc) FROM (SELECT sid,AVG(score) avg_sc FROM sc GROUP BY sid)T1

WHERE avg_sc > T2.avg_sc) '名次',sid, avg_sc FROM (SELECT sid,AVG(score) avg_sc FROM sc GROUP BY sid)T2

ORDER BY avg_sc desc;

--

SELECT @rownum:=@rownum+1 as ind,obj.sid,obj.avg_sc FROM

(SELECT sid,AVG(score) avg_sc FROM sc GROUP BY sid ORDER BY avg_sc DESC) as obj,

(SELECT @rownum:=0) r;

#14、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT * FROM sc x WHERE 3 >(

SELECT COUNT(*) FROM sc y WHERE x.score < y.score and x.cid=y.cid)

ORDER BY cid,score DESC