Mysql 巩固提升 (学生表_课程表_成绩表_教师表)

方便Mysql 巩固提升

创建表并插入数据：

-- ----------------------------
-- Table structure for student 
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(32) DEFAULT NULL,
  `sage` int(11) DEFAULT NULL,
  `ssex` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '刘一', '18', '男');
INSERT INTO `student` VALUES ('2', '钱二', '19', '女');
INSERT INTO `student` VALUES ('3', '张三', '17', '男');
INSERT INTO `student` VALUES ('4', '李四', '18', '女');
INSERT INTO `student` VALUES ('5', '王五', '17', '男');
INSERT INTO `student` VALUES ('6', '赵六', '19', '女');

-- ----------------------------
-- Table structure for teacher  
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) DEFAULT NULL,
  `tname` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '叶平');
INSERT INTO `teacher` VALUES ('2', '贺高');
INSERT INTO `teacher` VALUES ('3', '杨艳');
INSERT INTO `teacher` VALUES ('4', '周磊');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) DEFAULT NULL,
  `cname` varchar(32) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '1');
INSERT INTO `course` VALUES ('2', '数学', '2');
INSERT INTO `course` VALUES ('3', '英语', '3');
INSERT INTO `course` VALUES ('4', '物理', '4');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `sid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('1', '1', '56');
INSERT INTO `sc` VALUES ('1', '2', '78');
INSERT INTO `sc` VALUES ('1', '3', '67');
INSERT INTO `sc` VALUES ('1', '4', '58');
INSERT INTO `sc` VALUES ('2', '1', '79');
INSERT INTO `sc` VALUES ('2', '2', '81');
INSERT INTO `sc` VALUES ('2', '3', '92');
INSERT INTO `sc` VALUES ('2', '4', '68');
INSERT INTO `sc` VALUES ('3', '1', '91');
INSERT INTO `sc` VALUES ('3', '2', '47');
INSERT INTO `sc` VALUES ('3', '3', '88');
INSERT INTO `sc` VALUES ('3', '4', '56');
INSERT INTO `sc` VALUES ('4', '2', '88');
INSERT INTO `sc` VALUES ('4', '3', '90');
INSERT INTO `sc` VALUES ('4', '4', '93');
INSERT INTO `sc` VALUES ('5', '1', '46');
INSERT INTO `sc` VALUES ('5', '3', '78');
INSERT INTO `sc` VALUES ('5', '4', '53');
INSERT INTO `sc` VALUES ('6', '1', '35');
INSERT INTO `sc` VALUES ('6', '2', '68');
INSERT INTO `sc` VALUES ('6', '4', '71');

1. 查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT
    a1.sid
FROM
    (SELECT * FROM sc WHERE cid = 1) a1,
    (SELECT * FROM sc WHERE cid = 2) a2
WHERE
    a1.score > a2.score
AND a1.sid = a2.sid

---

select sid,GROUP_CONCAT(score),count(sid)from sc GROUP BY sid;

2. 查询平均成绩大于60分的学生的平均成绩

select sid,AVG(score) from sc GROUP BY sid HAVING AVG(score)>60;

3. 查询所有同学的学号、姓名、选课数、总成绩

select a1.id,a1.sname from student a1;

select sid,count(cid),sum(score) from sc GROUP BY sid;


SELECT
    a1.id,
    a1.sname,
    a2.number,
    a2.score
FROM
    student a1,
    (
        SELECT
            sid,
            count(cid) AS number,
            sum(score) AS score
        FROM
            sc
        GROUP BY
            sid
    ) a2
WHERE
    a1.id = a2.sid;

4. 查询姓“周”的老师的个数

SELECT
    count(DISTINCT(tname))
FROM
    teacher
WHERE
    tname LIKE '周%';

5.查询没学过“叶平”老师课的同学的学号、姓名

select id from course a where a.tid = (SELECT id from teacher where tname='叶平');

select sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid；

SELECT
    sid
FROM
    (
        SELECT
            sid,
            GROUP_CONCAT(cid ORDER BY cid ASC) AS cids
        FROM
            sc
        GROUP BY
            sid
    ) a
WHERE
    ! FIND_IN_SET('1', cids);

6. 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

方法一：

select sid from (select sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid) a where FIND_IN_SET('1',cids) and FIND_IN_SET('2',cids);

SELECT id,sname from student where student.id in (select sid from (select sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid) a where FIND_IN_SET('1',cids) and FIND_IN_SET('2',cids));

方法二：

select student.id,student.sname from student,sc where student.id = sc.sid and sc.cid = '001' and EXISTS(select * from sc sc2 where sc.sid = sc2.sid and sc2.cid='002');

7.查询学过“叶平”老师所教的所有课的同学的学号、姓名

SELECT student.id,student.sname from student where student.id in(SELECT DISTINCT(sid) from sc,course,teacher WHERE sc.cid = course.id and course.id = teacher.id and teacher.tname = '叶平');

8. 查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名

select a2.sid from (select * from sc where sc.cid ='002') a1,(select * from sc where sc.cid ='001') a2 where a1.score < a2.score and a1.sid = a2.sid;
select student.id,student.sname from student where student.id in(select a2.sid from (select * from sc where sc.cid ='002') a1,(select * from sc where sc.cid ='001') a2 where a1.score < a2.score and a1.sid = a2.sid)

9. 查询所有课程成绩小于60分的同学的学号、姓名

select GROUP_CONCAT(score ORDER BY score asc) from sc GROUP BY sid

select * from student where student.id not in(select sid from student,sc where student.id = sc.sid and sc.score>60);

10.查询没有学全所有课的同学的学号、姓名

SELECT count(1) from course;

SELECT sid from sc GROUP BY sid HAVING count(cid)<(SELECT count(1) from course);

select a.id,a.sname from student a,(SELECT sid from sc GROUP BY sid HAVING count(cid)<(SELECT count(1) from course)) a2 where a.id = a2.sid;

select a.id,a.sname from student a,sc b where a.id = b.sid GROUP BY a.id HAVING count(a.id)<4;

11.查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名

select DISTINCT(student.id),student.sname from student,sc where student.id = sc.sid and sc.cid in(SELECT sc.cid from sc where sc.sid='1');

12.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩

select course.id from course,teacher where course.tid = teacher.id and teacher.tname ='叶平';
select sc.cid,avg(score) from sc  where sc.cid= 1 GROUP BY cid;
UPDATE sc set sc.score = 12 where sc.cid =5;

13.查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名

SELECT sc.sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid;

SELECT cids from (SELECT sc.sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid) a where a.sid = 1;

select * from (SELECT sc.sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid) a where a.cids = (SELECT cids from (SELECT sc.sid,GROUP_CONCAT(cid ORDER BY cid ASC) as cids from sc GROUP BY sid) a where a.sid = 1);

14.删除学习“叶平”老师课的SC表记录

SELECT * from sc,course,teacher where sc.cid = course.id and course.id = teacher.id and teacher.tname = '叶平';

15.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cid,MAX(score) as '最高分',MIN(score) as '最低分' from sc GROUP BY sc.cid

select cid,GROUP_CONCAT(score) from sc GROUP BY cid;