创建表和插入数据
1 DROP TABLE IF EXISTS `class`; 2 CREATE TABLE `class` ( 3 `cid` int(11) NOT NULL AUTO_INCREMENT, 4 `caption` varchar(32) NOT NULL, 5 PRIMARY KEY (`cid`) 6 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 7 8 -- ---------------------------- 9 -- Records of class 10 -- ---------------------------- 11 INSERT INTO `class` VALUES ('1', '三年二班'); 12 INSERT INTO `class` VALUES ('2', '三年三班'); 13 INSERT INTO `class` VALUES ('3', '一年二班'); 14 INSERT INTO `class` VALUES ('4', '二年九班'); 15 16 -- ---------------------------- 17 -- Table structure for course 18 -- ---------------------------- 19 DROP TABLE IF EXISTS `course`; 20 CREATE TABLE `course` ( 21 `cid` int(11) NOT NULL AUTO_INCREMENT, 22 `cname` varchar(32) NOT NULL, 23 `teacher_id` int(11) NOT NULL, 24 PRIMARY KEY (`cid`), 25 KEY `fk_course_teacher` (`teacher_id`), 26 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) 27 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 28 29 -- ---------------------------- 30 -- Records of course 31 -- ---------------------------- 32 INSERT INTO `course` VALUES ('1', '生物', '1'); 33 INSERT INTO `course` VALUES ('2', '物理', '2'); 34 INSERT INTO `course` VALUES ('3', '体育', '3'); 35 INSERT INTO `course` VALUES ('4', '美术', '2'); 36 37 -- ---------------------------- 38 -- Table structure for score 39 -- ---------------------------- 40 DROP TABLE IF EXISTS `score`; 41 CREATE TABLE `score` ( 42 `sid` int(11) NOT NULL AUTO_INCREMENT, 43 `student_id` int(11) NOT NULL, 44 `course_id` int(11) NOT NULL, 45 `num` int(11) NOT NULL, 46 PRIMARY KEY (`sid`), 47 KEY `fk_score_student` (`student_id`), 48 KEY `fk_score_course` (`course_id`), 49 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), 50 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) 51 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; 52 53 -- ---------------------------- 54 -- Records of score 55 -- ---------------------------- 56 INSERT INTO `score` VALUES ('1', '1', '1', '10'); 57 INSERT INTO `score` VALUES ('2', '1', '2', '9'); 58 INSERT INTO `score` VALUES ('5', '1', '4', '66'); 59 INSERT INTO `score` VALUES ('6', '2', '1', '8'); 60 INSERT INTO `score` VALUES ('8', '2', '3', '68'); 61 INSERT INTO `score` VALUES ('9', '2', '4', '99'); 62 INSERT INTO `score` VALUES ('10', '3', '1', '77'); 63 INSERT INTO `score` VALUES ('11', '3', '2', '66'); 64 INSERT INTO `score` VALUES ('12', '3', '3', '87'); 65 INSERT INTO `score` VALUES ('13', '3', '4', '99'); 66 INSERT INTO `score` VALUES ('14', '4', '1', '79'); 67 INSERT INTO `score` VALUES ('15', '4', '2', '11'); 68 INSERT INTO `score` VALUES ('16', '4', '3', '67'); 69 INSERT INTO `score` VALUES ('17', '4', '4', '100'); 70 INSERT INTO `score` VALUES ('18', '5', '1', '79'); 71 INSERT INTO `score` VALUES ('19', '5', '2', '11'); 72 INSERT INTO `score` VALUES ('20', '5', '3', '67'); 73 INSERT INTO `score` VALUES ('21', '5', '4', '100'); 74 INSERT INTO `score` VALUES ('22', '6', '1', '9'); 75 INSERT INTO `score` VALUES ('23', '6', '2', '100'); 76 INSERT INTO `score` VALUES ('24', '6', '3', '67'); 77 INSERT INTO `score` VALUES ('25', '6', '4', '100'); 78 INSERT INTO `score` VALUES ('26', '7', '1', '9'); 79 INSERT INTO `score` VALUES ('27', '7', '2', '100'); 80 INSERT INTO `score` VALUES ('28', '7', '3', '67'); 81 INSERT INTO `score` VALUES ('29', '7', '4', '88'); 82 INSERT INTO `score` VALUES ('30', '8', '1', '9'); 83 INSERT INTO `score` VALUES ('31', '8', '2', '100'); 84 INSERT INTO `score` VALUES ('32', '8', '3', '67'); 85 INSERT INTO `score` VALUES ('33', '8', '4', '88'); 86 INSERT INTO `score` VALUES ('34', '9', '1', '91'); 87 INSERT INTO `score` VALUES ('35', '9', '2', '88'); 88 INSERT INTO `score` VALUES ('36', '9', '3', '67'); 89 INSERT INTO `score` VALUES ('37', '9', '4', '22'); 90 INSERT INTO `score` VALUES ('38', '10', '1', '90'); 91 INSERT INTO `score` VALUES ('39', '10', '2', '77'); 92 INSERT INTO `score` VALUES ('40', '10', '3', '43'); 93 INSERT INTO `score` VALUES ('41', '10', '4', '87'); 94 INSERT INTO `score` VALUES ('42', '11', '1', '90'); 95 INSERT INTO `score` VALUES ('43', '11', '2', '77'); 96 INSERT INTO `score` VALUES ('44', '11', '3', '43'); 97 INSERT INTO `score` VALUES ('45', '11', '4', '87'); 98 INSERT INTO `score` VALUES ('46', '12', '1', '90'); 99 INSERT INTO `score` VALUES ('47', '12', '2', '77'); 100 INSERT INTO `score` VALUES ('48', '12', '3', '43'); 101 INSERT INTO `score` VALUES ('49', '12', '4', '87'); 102 INSERT INTO `score` VALUES ('52', '13', '3', '87'); 103 104 -- ---------------------------- 105 -- Table structure for student 106 -- ---------------------------- 107 DROP TABLE IF EXISTS `student`; 108 CREATE TABLE `student` ( 109 `sid` int(11) NOT NULL AUTO_INCREMENT, 110 `gender` char(1) NOT NULL, 111 `class_id` int(11) NOT NULL, 112 `sname` varchar(32) NOT NULL, 113 PRIMARY KEY (`sid`), 114 KEY `fk_class` (`class_id`), 115 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) 116 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; 117 118 -- ---------------------------- 119 -- Records of student 120 -- ---------------------------- 121 INSERT INTO `student` VALUES ('1', '男', '1', '理解'); 122 INSERT INTO `student` VALUES ('2', '女', '1', '钢蛋'); 123 INSERT INTO `student` VALUES ('3', '男', '1', '张三'); 124 INSERT INTO `student` VALUES ('4', '男', '1', '张一'); 125 INSERT INTO `student` VALUES ('5', '女', '1', '张二'); 126 INSERT INTO `student` VALUES ('6', '男', '1', '张四'); 127 INSERT INTO `student` VALUES ('7', '女', '2', '铁锤'); 128 INSERT INTO `student` VALUES ('8', '男', '2', '李三'); 129 INSERT INTO `student` VALUES ('9', '男', '2', '李一'); 130 INSERT INTO `student` VALUES ('10', '女', '2', '李二'); 131 INSERT INTO `student` VALUES ('11', '男', '2', '李四'); 132 INSERT INTO `student` VALUES ('12', '女', '3', '如花'); 133 INSERT INTO `student` VALUES ('13', '男', '3', '刘三'); 134 INSERT INTO `student` VALUES ('14', '男', '3', '刘一'); 135 INSERT INTO `student` VALUES ('15', '女', '3', '刘二'); 136 INSERT INTO `student` VALUES ('16', '男', '3', '刘四'); 137 138 -- ---------------------------- 139 -- Table structure for teacher 140 -- ---------------------------- 141 DROP TABLE IF EXISTS `teacher`; 142 CREATE TABLE `teacher` ( 143 `tid` int(11) NOT NULL AUTO_INCREMENT, 144 `tname` varchar(32) NOT NULL, 145 PRIMARY KEY (`tid`) 146 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 147 148 -- ---------------------------- 149 -- Records of teacher 150 -- ---------------------------- 151 INSERT INTO `teacher` VALUES ('1', '张磊老师'); 152 INSERT INTO `teacher` VALUES ('2', '李平老师'); 153 INSERT INTO `teacher` VALUES ('3', '刘海燕老师'); 154 INSERT INTO `teacher` VALUES ('4', '朱云海老师'); 155 INSERT INTO `teacher` VALUES ('5', '李杰老师'); 156 SET FOREIGN_KEY_CHECKS=1;
1、查询所有的课程的名称以及对应的任课老师姓名 SELECT cname 课程,tname 老师 from teacher,course WHERE teacher.tid=course.teacher_id; +--------+-----------------+ | 课程 | 老师 | +--------+-----------------+ | 生物 | 张磊老师 | | 物理 | 李平老师 | | 体育 | 刘海燕老师 | | 美术 | 李平老师 | +--------+-----------------+ 2、查询学生表中男女生各有多少人 SELECT gender 性别 ,COUNT(gender) 人数 from student GROUP BY gender; +--------+--------+ | 性别 | 人数 | +--------+--------+ | 女 | 6 | | 男 | 10 | +--------+--------+ 3、查询物理成绩等于100的学生的姓名 SELECT student.sname ,course.cname,score.num FROM score,student,course WHERE num=100 and course_id=2 AND score.student_id=student.sid AND course.cid=score.course_id; +--------+--------+-----+ | sname | cname | num | +--------+--------+-----+ | 张四 | 物理 | 100 | | 铁锤 | 物理 | 100 | | 李三 | 物理 | 100 | +--------+--------+-----+ 4、查询平均成绩大于八十分的同学的姓名和平均成绩 SELECT AVG(num),student.sname FROM score,student WHERE score.student_id=student.sid GROUP BY student_id HAVING AVG(num) > 80; +----------+--------+ | AVG(num) | sname | +----------+--------+ | 82.2500 | 张三 | | 87.0000 | 刘三 | +----------+--------+ 5、查询所有学生的学号，姓名，选课数，总成绩 SELECT student.sid 学号,COUNT(*) 课程数,student.sname 姓名,SUM(score.num) 总成绩 FROM score,student WHERE student.sid=score.student_id GROUP BY student_id ORDER BY 学号; +--------+-----------+--------+-----------+ | 学号 | 课程数 | 姓名 | 总成绩 | +--------+-----------+--------+-----------+ | 1 | 3 | 理解 | 85 | | 2 | 3 | 钢蛋 | 175 | | 3 | 4 | 张三 | 329 | | 4 | 4 | 张一 | 257 | | 5 | 4 | 张二 | 257 | | 6 | 4 | 张四 | 276 | | 7 | 4 | 铁锤 | 264 | | 8 | 4 | 李三 | 264 | | 9 | 4 | 李一 | 268 | | 10 | 4 | 李二 | 297 | | 11 | 4 | 李四 | 297 | | 12 | 4 | 如花 | 297 | | 13 | 1 | 刘三 | 87 | +--------+-----------+--------+-----------+ 6、查询姓李老师的个数 SELECT count(*) FROM teacher WHERE tname LIKE '李%'; +----------+ | count(*) | +----------+ | 2 | +----------+ 7、查询没有报李平老师课的学生姓名 SELECT sid 学号,sname 姓名 from student WHERE sid NOT IN (SELECT student_id FROM score RIGHT JOIN (SELECT cid FROM course LEFT JOIN teacher ON teacher.tid=course.teacher_id WHERE tname LIKE "李平%") AS A ON score.course_id=A.cid GROUP BY student_id); +--------+--------+ | 学号 | 姓名 | +--------+--------+ | 13 | 刘三 | | 14 | 刘一 | | 15 | 刘二 | | 16 | 刘四 | +--------+--------+ 8、 查询物理课程比生物课程高的学生的学号 SELECT * from (SELECT student_id ,cname,num FROM score LEFT JOIN course ON score.course_id=course.cid WHERE cname="物理") AS A INNER JOIN (SELECT student_id,cname,num FROM score LEFT JOIN course ON score.course_id=course.cid WHERE cname="生物")AS B ON A.student_id=B.student_id WHERE A.num > B.num; +------------+--------+-----+------------+--------+-----+ | student_id | cname | num | student_id | cname | num | +------------+--------+-----+------------+--------+-----+ | 6 | 物理 | 100 | 6 | 生物 | 9 | | 7 | 物理 | 100 | 7 | 生物 | 9 | | 8 | 物理 | 100 | 8 | 生物 | 9 | +------------+--------+-----+------------+--------+-----+ 9、 查询没有同时选修物理课程和体育课程的学生姓名 SELECT sid 学号,sname 学员 FROM student WHERE sid not in (SELECT student_id FROM score LEFT JOIN course on course.cid=score.course_id WHERE cname="物理" OR cname="体育" GROUP BY student_id HAVING count(cname) >1); +--------+--------+ | 学号 | 学员 | +--------+--------+ | 1 | 理解 | | 2 | 钢蛋 | | 13 | 刘三 | | 14 | 刘一 | | 15 | 刘二 | | 16 | 刘四 | +--------+--------+ 10、查询挂科超过两门(包括两门)的学生姓名和班级 SELECT sname,caption FROM (SELECT student_id,class_id,sname FROM score LEFT JOIN student ON student.sid=score.student_id WHERE num < 60 GROUP BY score.student_id HAVING COUNT(*) > 1) AS A LEFT JOIN class ON class.cid=A.class_id; +--------+--------------+ | sname | caption | +--------+--------------+ | 理解 | 三年二班 | +--------+--------------+ 11、查询选修了所有课程的学生姓名 SELECT student_id 学号,sname 姓名 FROM (SELECT * FROM score GROUP BY student_id HAVING COUNT(*) >= (SELECT COUNT(cname) FROM course )) AS A LEFT JOIN student ON A.student_id=student.sid; +--------+--------+ | 学号 | 姓名 | +--------+--------+ | 3 | 张三 | | 4 | 张一 | | 5 | 张二 | | 6 | 张四 | | 7 | 铁锤 | | 8 | 李三 | | 9 | 李一 | | 10 | 李二 | | 11 | 李四 | | 12 | 如花 | +--------+--------+ 12、查询李平老师教的课程的所有成绩记录 SELECT tname 老师, cname 课程,num 分数 FROM (SELECT cid,tname,cname FROM teacher LEFT JOIN course ON teacher.tid=course.teacher_id WHERE tname LIKE "李平%") AS A LEFT JOIN score ON score.course_id=A.cid; +--------------+--------+--------+ | 老师 | 课程 | 分数 | +--------------+--------+--------+ | 李平老师 | 物理 | 9 | | 李平老师 | 物理 | 66 | | 李平老师 | 物理 | 11 | | 李平老师 | 物理 | 11 | | 李平老师 | 物理 | 100 | | 李平老师 | 物理 | 100 | | 李平老师 | 物理 | 100 | | 李平老师 | 物理 | 88 | | 李平老师 | 物理 | 77 | | 李平老师 | 物理 | 77 | | 李平老师 | 物理 | 77 | | 李平老师 | 美术 | 66 | | 李平老师 | 美术 | 99 | | 李平老师 | 美术 | 99 | | 李平老师 | 美术 | 100 | | 李平老师 | 美术 | 100 | | 李平老师 | 美术 | 100 | | 李平老师 | 美术 | 88 | | 李平老师 | 美术 | 88 | | 李平老师 | 美术 | 22 | | 李平老师 | 美术 | 87 | | 李平老师 | 美术 | 87 | | 李平老师 | 美术 | 87 | +--------------+--------+--------+ 13、查询全部学生都选修了的课程号和课程名 SELECT course_id FROM score GROUP BY course_id HAVING COUNT(course_id)=(SELECT COUNT(sid) FROM student); Empty set (0.00 sec) 14、查询每门课程被选修的次数 SELECT cname 课程名称,COUNT(course_id)次数 FROM score LEFT JOIN course on course.cid=score.course_id GROUP BY course_id; +--------------+--------+ | 课程名称 | 次数 | +--------------+--------+ | 生物 | 12 | | 物理 | 11 | | 体育 | 12 | | 美术 | 12 | +--------------+--------+ 15、查询只选修了一门课程的学生姓名和学号 SELECT student.sid 学号 ,sname 姓名 FROM score LEFT JOIN student ON student.sid=score.student_id GROUP BY student_id HAVING COUNT(course_id)=1; +--------+--------+ | 学号 | 姓名 | +--------+--------+ | 13 | 刘三 | +--------+--------+ 16、查询所有学生考出的成绩并按从高到低排序（成绩去重） SELECT DISTINCT num 成绩 from score ORDER BY num DESC; +--------+ | 成绩 | +--------+ | 100 | | 99 | | 91 | | 90 | | 88 | | 87 | | 79 | | 77 | | 68 | | 67 | | 66 | | 43 | | 22 | | 11 | | 10 | | 9 | | 8 | +--------+ 17、查询平均成绩大于85的学生姓名和平均成绩 SELECT student.sname 姓名,avg(num) 平均成绩 FROM score LEFT JOIN student ON student.sid=score.student_id GROUP BY student_id HAVING avg(num) > 85; +--------+--------------+ | 姓名 | 平均成绩 | +--------+--------------+ | 刘三 | 87.0000 | +--------+--------------+ 18、查询生物成绩不及格的学生姓名和对应生物分数 SELECT sname 姓名,cname 课程,num 分数 FROM (SELECT student_id,cname,num FROM score LEFT JOIN course on course.cid=score.course_id WHERE cname="生物" and num<60) AS A LEFT JOIN student ON A.student_id=student.sid; +--------+--------+--------+ | 姓名 | 课程 | 分数 | +--------+--------+--------+ | 理解 | 生物 | 10 | | 钢蛋 | 生物 | 8 | | 张四 | 生物 | 9 | | 铁锤 | 生物 | 9 | | 李三 | 生物 | 9 | +--------+--------+--------+ 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名 SELECT sid 学号,sname 学生, tname 老师,平均成绩 from (SELECT student_id,tname,AVG(num) 平均成绩 FROM (SELECT cid,tid,tname FROM course RIGHT JOIN teacher ON teacher.tid=course.teacher_id WHERE tname LIKE "李平%") AS A LEFT JOIN score ON A.cid=score.course_id GROUP BY student_id ORDER BY AVG(num) DESC LIMIT 1) AS B LEFT JOIN student ON student.sid=B.student_id; +--------+--------+--------------+--------------+ | 学号 | 学生 | 老师 | 平均成绩 | +--------+--------+--------------+--------------+ | 6 | 张四 | 李平老师 | 100.0000 | +--------+--------+--------------+--------------+