- 本代码是通过老男孩的mysql试题进行实际操作,只写了个人认为精华的部分,答案也是个人测试,如有问题或者好的答案可以回复,大家共同进步
- 测试数据是他们完整的数据,链接如下:http://www.cnblogs.com/wupeiqi/articles/5748496.html
- 测试代码如下:
-
/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50624 Source Host : localhost Source Database : sqlexam Target Server Type : MySQL Target Server Version : 50624 File Encoding : utf-8 Date: 10/21/2016 06:46:46 AM */ SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for `class` -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `class` -- ---------------------------- BEGIN; INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班'); COMMIT; -- ---------------------------- -- Table structure for `course` -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `course` -- ---------------------------- BEGIN; INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2'); COMMIT; -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `score` -- ---------------------------- BEGIN; INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87'); COMMIT; -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `student` -- ---------------------------- BEGIN; INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四'); COMMIT; -- ---------------------------- -- Table structure for `teacher` -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of `teacher` -- ---------------------------- BEGIN; INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
- 测试题如下:
-
1、查询“生物”课程比“物理”课程成绩高的所有学生的姓名 SELECT student.sname FROM ( SELECT W.student_id FROM ( SELECT * FROM course LEFT JOIN score ON course.cid = score.course_id WHERE course.cid = 1 ) AS S INNER JOIN ( SELECT * FROM course LEFT JOIN score ON course.cid = score.course_id WHERE course.cid = 2 ) AS W ON S.student_id = W.student_id WHERE S.num > W.num ) AS E LEFT JOIN student ON E.student_id = student.sid; 2、查询平均成绩大于60分的同学的学号和平均成绩; SELECT student_id,AVG(num) FROM `score` GROUP BY student_id HAVING AVG(num)>60; 3、查询所有同学的学号、姓名、选课数、总成绩 SELECT student.sname, sid, C.N, C.CI FROM (SELECT student_id, sum( num ) as N, COUNT( course_id ) AS CI FROM score GROUP BY student_id )AS C LEFT JOIN student ON C.student_id = student.sid; 4、查询姓“李”的老师的个数; SELECT COUNT(tid) from teacher WHERE tname like '李%'; 5、查询没学过“李平老师“课的同学的学号、姓名;SELECT student.sid, student.sname FROM ( SELECT student_id FROM score WHERE score.student_id NOT IN ( SELECT tid FROM teacher WHERE tname = '李平老师' ) GROUP BY student_id) AS T LEFT JOIN student ON T.student_id = student.sid; 6、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名 SELECT student_id, sname FROM ( SELECT student_id, COUNT( course_id ) FROM `score` WHERE course_id IN ( 1, 2 ) GROUP BY student_id HAVING COUNT( course_id ) > 1 ) AS B LEFT JOIN student ON B.student_id=student.sid; 7、查询有课程成绩小于60分的同学的学号、姓名 SELECT student_id, sname FROM ( SELECT student_id FROM `score` WHERE num < 60 GROUP BY student_id ) AS B LEFT JOIN student ON B.student_id = student.sid; 8、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分; SELECT course_id, MAX( num ) AS sMAX, min( num ) AS sMIN, student_id AS sstudent FROM `score` GROUP BY course_id
转载于:https://www.cnblogs.com/tengfei520/p/10205504.html