mysql面接1

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