四张表
课程表---学生表---成绩表--老师表
sql语句如下:
/*
Navicat MySQL Data Transfer
Source Server : zhulang
Source Server Version : 50727
Source Host : localhost:3306
Source Database : exams
Target Server Type : MYSQL
Target Server Version : 50727
File Encoding : 65001
Date: 2019-10-18 17:20:44
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL COMMENT '课程名字',
`tid` int(11) DEFAULT NULL COMMENT '教师id',
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'java', '1');
INSERT INTO `course` VALUES ('2', 'php', '2');
INSERT INTO `course` VALUES ('3', '前端', '3');
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
`score` int(11) DEFAULT NULL COMMENT '考试分数',
PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '52');
INSERT INTO `sc` VALUES ('1', '2', '84');
INSERT INTO `sc` VALUES ('2', '1', '87');
INSERT INTO `sc` VALUES ('2', '2', '87');
INSERT INTO `sc` VALUES ('3', '1', '84');
INSERT INTO `sc` VALUES ('3', '2', '99');
INSERT INTO `sc` VALUES ('4', '1', '73');
INSERT INTO `sc` VALUES ('4', '2', '76');
INSERT INTO `sc` VALUES ('5', '1', '79');
INSERT INTO `sc` VALUES ('5', '2', '66');
INSERT INTO `sc` VALUES ('6', '1', '69');
INSERT INTO `sc` VALUES ('6', '2', '60');
INSERT INTO `sc` VALUES ('6', '3', '89');
INSERT INTO `sc` VALUES ('7', '1', '70');
INSERT INTO `sc` VALUES ('7', '2', '68');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL COMMENT '学生姓名',
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小花');
INSERT INTO `student` VALUES ('2', '小牛');
INSERT INTO `student` VALUES ('3', '小婷');
INSERT INTO `student` VALUES ('4', '小凯');
INSERT INTO `student` VALUES ('5', '小辉');
INSERT INTO `student` VALUES ('6', '小张');
INSERT INTO `student` VALUES ('7', '小强');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL COMMENT '教师名字',
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '老王');
INSERT INTO `teacher` VALUES ('2', '小李');
INSERT INTO `teacher` VALUES ('3', '小朱');
-- 题目如下
-- 查询平均成绩大于80分的同学的学号和平均成绩;
-- 查两门以上不及格课程的同学的学号及其平均成绩;
-- 查询编号“1”课程比编号“2”课程成绩高的所有学生的学号;
-- 查询所有课程成绩小于80分的同学的学号、姓名;
-- 查询所有课程成绩小于80分的同学的学号、姓名、最低分数;
-- 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
-- 查询和“1”号的同学学习的课程完全相同同学学号和姓名;
-- 查学过1并且也学过编号2课程的同学的学号、姓名;
-- 查询所有同学的学号、姓名、选课数、总成绩;
-- 查询(没)学过“老王”老师课的同学的学号、姓名;
-- 查全部学生都选修了的课程的课程号和课程名;
答案:
-- 查询平均成绩大于80分的同学的学号和平均成绩;
SELECT sc.sid, AVG(score) avg_s from sc GROUP BY sc.sid ;
SELECT sc.sid, AVG(score) avg_s from sc GROUP BY sc.sid HAVING avg_s>80;
-- 查两门及以上不及格(等于80)课程的同学的学号及其平均成绩;
SELECT sc.sid, AVG(score) avg_s from sc WHERE sc.score<80 GROUP BY sc.sid HAVING COUNT(cid)>1;
-- 查询编号“1”课程比编号“2”课程成绩高的所有学生的学号;
SELECT sc.sid, sc.score from sc WHERE cid=1 GROUP BY sid;
SELECT sc.sid, sc.score from sc WHERE cid=2 GROUP BY sid;
SELECT sc1.sid from sc sc1,sc sc2 WHERE sc1.sid=sc2.sid AND sc1.cid=1 AND sc2.cid=2 AND sc1.score>sc2.score;
-- 查询所有课程成绩小于80分的同学的学号、姓名;
SELECT sc.sid from sc GROUP BY sid HAVING MAX(score)<80;
SELECT sc.sid ,student.`name` from sc,student WHERE sc.sid=student.sid GROUP BY sid HAVING MAX(score)<80;
-- 查询所有课程成绩小于80分的同学的学号、姓名、最低分数;
SELECT sc.sid ,student.`name`,MIN(score) from sc,student WHERE sc.sid=student.sid GROUP BY sid HAVING MAX(score)<80;
-- 查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名
SELECT cid from sc WHERE sc.sid=1;-- 查询结果为多行单列可以作为in条件使用
SELECT sc.sid,student.`name` from sc,student WHERE sc.sid=student.sid AND sc.cid in(SELECT cid from sc WHERE sc.sid=1) GROUP BY sid;
-- 查询和“1”号的同学学习的课程完全相同同学学号和姓名;
SELECT cid from sc WHERE sc.sid=1;-- 查询结果为多行单列可以作为in条件使用
SELECT sc.sid,student.`name` from sc,student WHERE sc.sid=student.sid AND sc.cid not in(SELECT cid from sc WHERE sc.sid=1) GROUP BY sid;
-- 分析思路:1号同学学的是1和2课程,把学习了其它课程的同学排除掉。限制其它同学课程数量和一号同学一样。
SELECT cid from course;
SELECT COUNT(*) from sc WHERE sc.sid=1;-- 一号同学所学课程数量
SELECT course.cid from course WHERE course.cid NOT in( SELECT cid from sc WHERE sc.sid=1);-- 查询其它课程,也就是和一号同学不一样的课程
SELECT sc.sid from sc WHERE sc.cid in(SELECT course.cid from course WHERE course.cid NOT in( SELECT cid from sc WHERE sc.sid=1));
SELECT sc.sid from sc where sc.sid NOT in(SELECT sc.sid from sc WHERE sc.cid in(SELECT course.cid from course WHERE course.cid NOT in( SELECT cid from sc WHERE sc.sid=1))) GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) from sc WHERE sc.sid=1);
SELECT sc.sid ,student.`name` from sc ,student where sc.sid=student.sid AND sc.sid NOT in(SELECT sc.sid from sc WHERE sc.cid in(SELECT course.cid from course WHERE course.cid NOT in( SELECT cid from sc WHERE sc.sid=1))) GROUP BY sc.sid HAVING COUNT(*)=(SELECT COUNT(*) from sc WHERE sc.sid=1);
-- 查学过1并且也学过编号2课程的同学的学号、姓名;
SELECT sc1.sid from sc sc1,sc sc2 WHERE sc1.sid=sc2.sid AND sc1.cid=1 AND sc2.cid=2;
-- 查询所有同学的学号、姓名、选课数、总成绩;
SELECT sc.sid ,student.`name`,COUNT(cid),SUM(score) from sc,student WHERE sc.sid = student.sid GROUP BY sc.sid;
-- 查询(没)学过“老王”老师课的同学的学号、姓名;
-- 学过老王课程的同学先查出来
SELECT course.cid from course,teacher WHERE course.tid=teacher.tid AND teacher.`name`='老王';
SELECT sc.sid from sc WHERE sc.cid in(SELECT course.cid from course,teacher WHERE course.tid=teacher.tid AND teacher.`name`='老王');
SELECT sc.sid,student.name from sc , student WHERE sc.sid=student.sid AND sc.sid NOT in(SELECT sc.sid from sc WHERE sc.cid in(SELECT course.cid from course,teacher WHERE course.tid=teacher.tid AND teacher.`name`='老王'));
-- 查全部学生都选修了的课程的课程号和课程名;
SELECT cid,COUNT(cid) from sc GROUP BY sc.cid;-- 查询没门课程被选修的数量
-- 查询班级总人数
SELECT COUNT(*) from student;
-- 选修的数量等于总人数即说明这门课程被所以学生都选修了
SELECT sc.cid,course.`name` FROM sc,course WHERE sc.cid=course.cid GROUP BY sc.cid HAVING COUNT(*)=(SELECT COUNT(*) from student);