MySQL练习题
创建下列表并创建相关约束
问题
1：查询出成绩表，而且student_id 后面要有对应的学生名，course_id 后面要有对应的课程名.
1 SELECT 2 score.sid, 3 score.student_id, 4 student.sname, 5 score.course_id, 6 course.cname, 7 score.number 8 FROM score 9 LEFT JOIN student 10 ON student.sid = score.student_id 11 LEFT JOIN course 12 ON course.cid = score.course_id
参考答案
2：查询平均成绩大于60分的同学的学号和平均成绩
1 SELECT student_id,SUM(number)/COUNT(student_id) AS avg_number FROM score GROUP BY student_id HAVING avg_number>60;
View Code
3：查询所有同学的学号、姓名、选课数、总成绩
1 SELECT student.sid,student.sname,COUNT(score.student_id),SUM(number) FROM student,score WHERE student.sid=score.student_id GROUP BY score.student_id
View Code
4：查询姓“李”的老师的个数
1 SELECT COUNT(*) FROM teacher WHERE tname LIKE "李%";
View Code
5：查询学过“李平”老师课的同学的学号、姓名
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id = (SELECT tid FROM teacher WHERE tname=\'李平老师\');
View Code
6：查询学过“1”并且也学过编号“2”课程的同学的学号、姓名
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND score.course_id IN (1,2) GROUP BY student.sname HAVING COUNT(*)=2;
View Code
7：查询没学过“叶平”老师课的同学的学号、姓名
1 SELECT 2 sid, 3 sname 4 FROM student 5 WHERE sid NOT IN(SELECT 6 student.sid 7 FROM score, 8 student 9 WHERE student.sid = score.student_id 10 AND score.course_id IN(SELECT 11 tid 12 FROM teacher 13 WHERE tname = \'李平老师\'));
View Code
8：查询有课程成绩小于60分的同学的学号、姓名
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id AND number<60 GROUP BY student.sname;
View Code
9：查询没有学全所有课的同学的学号、姓名
1 SELECT student.sid,student.sname FROM student,score WHERE student.sid=score.student_id GROUP BY student.sid HAVING COUNT(*)<(SELECT COUNT(*) FROM course);
View Code
10：查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
1 SELECT * 2 FROM student, 3 score 4 WHERE student.sid = score.student_id 5 GROUP BY student.sid 6 HAVING score.course_id IN(SELECT 7 score.course_id 8 FROM score, 9 student 10 WHERE student.sid = 1 11 AND student.sid = score.student_id) 12 AND student.sid != 1;
View Code
11：查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名
1 SELECT 2 student.sid, 3 student.sname 4 FROM student, 5 score 6 WHERE student.sid = score.student_id 7 GROUP BY student.sid 8 HAVING COUNT(student.sid) = (SELECT 9 COUNT(*) 10 FROM score 11 WHERE student_id = 2) 12 AND SUM(course_id) = (SELECT 13 SUM(course_id) 14 FROM score 15 WHERE student_id = 2) 16 AND student.sid != 2;
View Code
12：查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
1 SELECT course_id,MAX(number),MIN(number) FROM score GROUP BY course_id;
View Code
13：查询每门课程被选修的学生数
1 SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;
View Code
14：查询出只选修了一门课程的全部学生的学号和姓名
1 SELECT student.sid,student.sname FROM score,student WHERE student.sid=score.student_id GROUP BY student_id HAVING COUNT(course_id)=1;
View Code
15：查询男生、女生的人数
1 SELECT gender,COUNT(gender) FROM student GROUP BY gender;
View Code
16：查询姓“张”的学生名单
1 SELECT sid,sname FROM student WHERE sname LIKE \'张%\';
View Code
17：查询同名同姓学生名单，并统计同名人数
1 SELECT sid,sname,COUNT(sname) FROM student GROUP BY sname HAVING COUNT(sname)>1;
View Code
18：查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
1 SELECT course_id,AVG(number) FROM score GROUP BY course_id ORDER BY AVG(number) ASC ,course_id DESC;
View Code
19：查询平均成绩大于85的所有学生的学号、姓名和平均成绩
1 SELECT student.sid,student.sname,AVG(number) FROM score,student WHERE student.sid=score.student_id GROUP BY score.student_id HAVING AVG(number)>85;
View Code
20：查询课程名称为“生物”，且分数低于60的学生姓名和分数
1 SELECT 2 student.sid, 3 student.sname, 4 score.number 5 FROM score, 6 student 7 WHERE student.sid = score.student_id 8 AND score.course_id = (SELECT 9 cid 10 FROM course 11 WHERE cname = "生物") 12 AND score.number < 60;
View Code
21：查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
1 SELECT student.sid,student.sname,score.number FROM score,student WHERE student.sid=score.student_id AND score.course_id=3 AND number>80;
View Code
22：求选了课程的学生人数
1 SELECT COUNT(*) FROM (SELECT * FROM score GROUP BY student_id) AS e;
View Code
23：查询选修“刘海燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
1 SELECT student.sid,student.sname,MAX(number) FROM score,student WHERE student.sid=score.student_id AND score.course_id=(SELECT tid FROM teacher WHERE tname=\'刘海燕老师\')
View Code
24：查询不同课程但成绩相同的学生的学号、课程号、学生成绩*****
1 SELECT * FROM score AS sc WHERE EXISTS(SELECT 1 FROM score WHERE number=sc.number AND course_id<>sc.course_id) ORDER BY number DESC;
View Code
25：查询至少选修两门课程的学生学号
1 SELECT student_id FROM score GROUP BY student_id HAVING COUNT(student_id)>=2;
View Code
26：查询“生物”课程比“物理”课程成绩高的所有学生的学号
1 SELECT 2 a.student_id 3 FROM (SELECT * 4 FROM score 5 WHERE course_id = (SELECT 6 cid 7 FROM course 8 WHERE cname = \'生物\')) AS a, 9 (SELECT * 10 FROM score 11 WHERE course_id = (SELECT 12 cid 13 FROM course 14 WHERE cname = \'物理\')) AS b 15 WHERE a.student_id = b.student_id 16 and a.number > b.number;
View Code
创建表和数据
1 /* 2 Navicat Premium Data Transfer 3 4 Source Server : localhost 5 Source Server Type : MySQL 6 Source Server Version : 50624 7 Source Host : localhost 8 Source Database : sqlexam 9 10 Target Server Type : MySQL 11 Target Server Version : 50624 12 File Encoding : utf-8 13 14 Date: 10/21/2016 06:46:46 AM 15 */ 16 17 SET NAMES utf8; 18 SET FOREIGN_KEY_CHECKS = 0; 19 20 -- ---------------------------- 21 -- Table structure for `class` 22 -- ---------------------------- 23 DROP TABLE IF EXISTS `class`; 24 CREATE TABLE `class` ( 25 `cid` int(11) NOT NULL AUTO_INCREMENT, 26 `caption` varchar(32) NOT NULL, 27 PRIMARY KEY (`cid`) 28 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 29 30 -- ---------------------------- 31 -- Records of `class` 32 -- ---------------------------- 33 BEGIN; 34 INSERT INTO `class` VALUES (\'1\', \'三年二班\'), (\'2\', \'三年三班\'), (\'3\', \'一年二班\'), (\'4\', \'二年九班\'); 35 COMMIT; 36 37 -- ---------------------------- 38 -- Table structure for `course` 39 -- ---------------------------- 40 DROP TABLE IF EXISTS `course`; 41 CREATE TABLE `course` ( 42 `cid` int(11) NOT NULL AUTO_INCREMENT, 43 `cname` varchar(32) NOT NULL, 44 `teacher_id` int(11) NOT NULL, 45 PRIMARY KEY (`cid`), 46 KEY `fk_course_teacher` (`teacher_id`), 47 CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) 48 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 49 50 -- ---------------------------- 51 -- Records of `course` 52 -- ---------------------------- 53 BEGIN; 54 INSERT INTO `course` VALUES (\'1\', \'生物\', \'1\'), (\'2\', \'物理\', \'2\'), (\'3\', \'体育\', \'3\'), (\'4\', \'美术\', \'2\'); 55 COMMIT; 56 57 -- ---------------------------- 58 -- Table structure for `score` 59 -- ---------------------------- 60 DROP TABLE IF EXISTS `score`; 61 CREATE TABLE `score` ( 62 `sid` int(11) NOT NULL AUTO_INCREMENT, 63 `student_id` int(11) NOT NULL, 64 `course_id` int(11) NOT NULL, 65 `num` int(11) NOT NULL, 66 PRIMARY KEY (`sid`), 67 KEY `fk_score_student` (`student_id`), 68 KEY `fk_score_course` (`course_id`), 69 CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), 70 CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) 71 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8; 72 73 -- ---------------------------- 74 -- Records of `score` 75 -- ---------------------------- 76 BEGIN; 77 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\'); 78 COMMIT; 79 80 -- ---------------------------- 81 -- Table structure for `student` 82 -- ---------------------------- 83 DROP TABLE IF EXISTS `student`; 84 CREATE TABLE `student` ( 85 `sid` int(11) NOT NULL AUTO_INCREMENT, 86 `gender` char(1) NOT NULL, 87 `class_id` int(11) NOT NULL, 88 `sname` varchar(32) NOT NULL, 89 PRIMARY KEY (`sid`), 90 KEY `fk_class` (`class_id`), 91 CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) 92 ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; 93 94 -- ---------------------------- 95 -- Records of `student` 96 -- ---------------------------- 97 BEGIN; 98 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\', \'刘四\'); 99 COMMIT; 100 101 -- ---------------------------- 102 -- Table structure for `teacher` 103 -- ---------------------------- 104 DROP TABLE IF EXISTS `teacher`; 105 CREATE TABLE `teacher` ( 106 `tid` int(11) NOT NULL AUTO_INCREMENT, 107 `tname` varchar(32) NOT NULL, 108 PRIMARY KEY (`tid`) 109 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; 110 111 -- ---------------------------- 112 -- Records of `teacher` 113 -- ---------------------------- 114 BEGIN; 115 INSERT INTO `teacher` VALUES (\'1\', \'张磊老师\'), (\'2\', \'李平老师\'), (\'3\', \'刘海燕老师\'), (\'4\', \'朱云海老师\'), (\'5\', \'李杰老师\'); 116 COMMIT; 117 118 SET FOREIGN_KEY_CHECKS = 1;
View Code
版权声明：本文为sun-10387834原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。