学生选课系统
设计表关系
创建表和插入数据
/*
Navicat MySQL Data TransferSource Server : localhost_3306
Source Server Version : 50719
Source Host : localhost:3306
Source Database : oldboTarget Server Type : MYSQL
Target Server Version : 50719
File Encoding : 65001Date: 2017-07-26 15:46:16
*/SET FOREIGN_KEY_CHECKS=;-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int() NOT NULL AUTO_INCREMENT,
`caption` varchar() NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('', '三年二班');
INSERT INTO `class` VALUES ('', '三年三班');
INSERT INTO `class` VALUES ('', '一年二班');
INSERT INTO `class` VALUES ('', '二年九班');-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int() NOT NULL AUTO_INCREMENT,
`cname` varchar() NOT NULL,
`teacher_id` int() 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= DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('', '生物', '');
INSERT INTO `course` VALUES ('', '物理', '');
INSERT INTO `course` VALUES ('', '体育', '');
INSERT INTO `course` VALUES ('', '美术', '');-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int() NOT NULL AUTO_INCREMENT,
`student_id` int() NOT NULL,
`course_id` int() NOT NULL,
`num` int() 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= DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');
INSERT INTO `score` VALUES ('', '', '', '');-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int() NOT NULL AUTO_INCREMENT,
`gender` char() NOT NULL,
`class_id` int() NOT NULL,
`sname` varchar() NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('', '男', '', '理解');
INSERT INTO `student` VALUES ('', '女', '', '钢蛋');
INSERT INTO `student` VALUES ('', '男', '', '张三');
INSERT INTO `student` VALUES ('', '男', '', '张一');
INSERT INTO `student` VALUES ('', '女', '', '张二');
INSERT INTO `student` VALUES ('', '男', '', '张四');
INSERT INTO `student` VALUES ('', '女', '', '铁锤');
INSERT INTO `student` VALUES ('', '男', '', '李三');
INSERT INTO `student` VALUES ('', '男', '', '李一');
INSERT INTO `student` VALUES ('', '女', '', '李二');
INSERT INTO `student` VALUES ('', '男', '', '李四');
INSERT INTO `student` VALUES ('', '女', '', '如花');
INSERT INTO `student` VALUES ('', '男', '', '刘三');
INSERT INTO `student` VALUES ('', '男', '', '刘一');
INSERT INTO `student` VALUES ('', '女', '', '刘二');
INSERT INTO `student` VALUES ('', '男', '', '刘四');-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int() NOT NULL AUTO_INCREMENT,
`tname` varchar() NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('', '张磊老师');
INSERT INTO `teacher` VALUES ('', '李平老师');
INSERT INTO `teacher` VALUES ('', '刘海燕老师');
INSERT INTO `teacher` VALUES ('', '朱云海老师');
INSERT INTO `teacher` VALUES ('', '李杰老师');
SET FOREIGN_KEY_CHECKS=;
创建表和插入数据
查询条件
-- -- 1、将所有的课程的名称以及对应的任课老师姓名打印出来
SELECT cname,tname FROM course INNER JOIN teacher ON course.teacher_id=teacher.tid;-- -- 2、查询学生表中男女生各有多少人
SELECT gender,COUNT(sid ) FROM student GROUP BY gender;-- -- 3、查询物理成绩等于100的学生的姓名
-- -- 查询物理课程cid：SELECT cid FROM course WHERE cname='物理'
-- -- SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score
-- -- WHERE course_id=(SELECT cid FROM course WHERE cname='物理') AND num=100)
-- -- 简化
SELECT sid,sname FROM student WHERE sid in (SELECT student_id FROM score WHERE course_id=2 AND num=100);-- -- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT student_id,sname,avg(num) FROM score INNER JOIN student on score.student_id=student.sid GROUP BY student_id HAVING avg(num)>80;-- -- 5、查询所有学生的学号，姓名，选课数，总成绩
SELECT student_id,sname,COUNT(course_id) 选课数, SUM(num) 总成绩 FROM score INNER JOIN student on score.student_id=student.sid GROUP BY student_id ;-- -- 6、查询姓李老师的个数
SELECT COUNT(tname) FROM teacher WHERE tname LIKE '李%';-- -- 7、查询没有报李平老师课的学生姓名
-- -- 李平老师的tid：SELECT tid FROM teacher WHERE tname='李平老师';
-- -- 李平老师的课程cid：(2,4) SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师');
-- -- 选了李平老师课程的学生id：
SELECT DISTINCT student_id FROM score WHERE course_id in
(SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname LIKE '李平%'));
-- -- 简化
SELECT sid,sname FROM student WHERE sid NOT in (
SELECT DISTINCT student_id FROM score WHERE course_id in (2,4));
-- -- 方法二：
SELECT student.sid,sname from student left JOIN score on student.sid=score.student_id
WHERE student.sid NOT IN (SELECT DISTINCT student_id from score where course_id = 2 or course_id=4);-- 8、查询物理课程比生物课程高的学生的学号
-- 物理表：SELECT student_id,num FROM score WHERE course_id=2;
-- 生物表：SELECT student_id,num FROM score WHERE course_id=1;
--SELECT phy.student_id resid,phy.num phy,bio.num bio FROM
(SELECT student_id,num FROM score WHERE course_id=2) AS phy
INNER JOIN
(SELECT student_id,num FROM score WHERE course_id=1) AS bio
on phy.student_id=bio.student_id WHERE phy.num>bio.num;SELECT sid,sname,res.phy,res.bio FROM student
INNER JOIN (
SELECT phy.student_id resid,phy.num phy,bio.num bio FROM
(SELECT student_id,num FROM score WHERE course_id=2) AS phy
INNER JOIN
(SELECT student_id,num FROM score WHERE course_id=1) AS bio
on phy.student_id=bio.student_id WHERE phy.num>bio.num) AS res
ON student.sid=res.resid;-- -- 9、查询没有同时选修物理课程和体育课程的学生姓名
SELECT sid,sname FROM student WHERE sid NOT in (
SELECT phy.student_id FROM (SELECT student_id FROM score WHERE course_id=2) as phy INNER JOIN (SELECT student_id FROM score WHERE course_id=3) as spo ON phy.student_id=spo.student_id);
-- 方法二
SELECT sid,sname FROM student WHERE sid NOT in (
SELECT student_id FROM (SELECT student_id FROM score WHERE course_id=2) as phy WHERE phy.student_id in (SELECT student_id FROM score WHERE course_id=3));-- 10、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sid,sname,caption FROM student INNER JOIN class ON student.class_id=class.cid WHERE sid in (
SELECT student_id FROM score WHERE num<60 GROUP BY student_id HAVING count(sid)>=2 );-- 11 、查询选修了所有课程的学生姓名
SELECT sid,sname FROM student WHERE sid in (
SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course));-- 12、查询李平老师教的课程的所有成绩记录
SELECT course_id,num FROM score WHERE course_id in (
SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师')
) ORDER BY course_id;SELECT tid,tname,cid,cname,res.student_id,student.sname,course_id,num FROM (
SELECT tid,tname,cid,cname,student_id,course_id,num FROM (SELECT tid,tname,cid,cname FROM teacher INNER JOIN course on teacher.tid=course.teacher_id WHERE tname='李平老师') as tea
RIGHT JOIN (SELECT student_id,course_id,num FROM score WHERE course_id in (
SELECT cid FROM course WHERE teacher_id =(SELECT tid FROM teacher WHERE tname='李平老师')
) ORDER BY course_id) as cor ON tea.cid=cor.course_id) AS res INNER JOIN student ON res.student_id=student.sid;-- 13、查询全部学生都选修了的课程号和课程名
SELECT cid,cname FROM course INNER JOIN (
SELECT course_id FROM score GROUP BY course_id HAVING COUNT(student_id)=(SELECT COUNT(sid) FROM student )) as res on course.cid=res.course_id;SELECT course_id,COUNT(student_id) FROM score GROUP BY course_id;
SELECT student_id,COUNT(course_id) FROM score GROUP BY student_id;SELECT count(DISTINCT student_id) FROM score;-- 14、查询每门课程被选修的次数
SELECT course_id,cname,COUNT(student_id) FROM score INNER JOIN course on score.course_id=course.cid GROUP BY course_id-- 15、查询之选修了一门课程的学生姓名和学号
SELECT sid,sname FROM student WHERE sid in (
SELECT student_id FROM score GROUP BY student_id HAVING COUNT(course_id)=1);-- 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT num FROM score ORDER BY num DESC-- 17、查询平均成绩大于85的学生姓名和平均成绩
SELECT student_id,sname,AVG(num) FROM score INNER JOIN student on score.student_id=student.sid GROUP BY student_id HAVING AVG(num)>85-- 18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT student_id,sname,cname,num FROM (
SELECT student_id,cname,num FROM score INNER JOIN course on score.course_id=course.cid WHERE cname='生物' AND num<60) AS res
INNER JOIN
student
ON student.sid=res.student_id-- 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT res.student_id,sname,avg(num) FROM (
SELECT student_id,num FROM score WHERE course_id in (2,4)) as res
INNER JOIN
student
on res.student_id=student.sid
GROUP BY res.student_id ORDER BY avg(num) DESC LIMIT 1-- 20、查询每门课程成绩最好的前两名学生姓名
SELECT student_id,num FROM score WHERE course_id=2 ORDER BY num DESC LIMIT 2-- 21、查询不同课程但成绩相同的学号，课程号，成绩-- 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；
select res.id,sname,cname FROM course INNER JOIN
(
select student.sid id,sname,course_id FROM student INNER JOIN score ON student.sid=score.student_id
WHERE course_id NOT in(select cid FROM teacher INNER JOIN course on teacher.tid=course.teacher_id WHERE tname LIKE '李平%')
) AS res
ON course.cid=res.course_id-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
SELECT sid,sname FROM student WHERE sid in (
SELECT DISTINCT student_id FROM score WHERE course_id in (SELECT course_id FROM score WHERE student_id=1)
);-- 24、任课最多的老师中学生单科成绩最高的学生姓名# SELECT teacher_id,count(cid) FROM course GROUP BY teacher_id-- -- 10、查询有课程成绩小于60分的同学的学号、姓名
SELECT sid,sname,course_id,num FROM student INNER JOIN (
SELECT student_id,course_id,num FROM score WHERE num<60) AS res ON student.sid=res.student_id;
/*
Navicat MySQL Data TransferSource Server : localhost_3306
Source Server Version : 50719
Source Host : localhost:3306
Source Database : oldboTarget Server Type : MYSQL
Target Server Version : 50719
File Encoding : 65001Date: 2017-07-26 15:46:16
*/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,
