创建表和插入数据

 

 

 

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 student106 -- ----------------------------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 student120 -- ----------------------------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 teacher140 -- ----------------------------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 teacher150 -- ----------------------------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 |+--------+--------+--------------+--------------+