查询所有学生的snamecno和degree列的数据

一、数据库介绍

数据库一共右五个表，分别是class，course，score，student，teacher表。

数据表

1、class表：包含两个字段，班级的ID：cid，班级：caption。

class表信息

2、course表：包含三个字段，课程ID：cid，课程名称：cname，教师ID：teacher_id。

course表信息

3、score表：包含四个字段：每条分数信息ID：sid，学生ID：student_id，课程ID:corse_id，考试分数：number。

score表信息

4、student表：包含四个字段，学生ID：sid，学生姓名：name，性别：gender，学生所在班级ID:class_id。

student表信息

5、teacher表：包含两个字段，教师ID:tid，教师姓名：tname。

其中score为主表关联student表和course表，course表关联teacher表，student表关联class表。

二、查询问题

总结：对复杂查询，可通过理清思路将其分解成简单查询，再进行组合。

1、查询所有授课老师的姓名。

SELECT tname FROM teacher;

查询结果

2、查询班级有多少种性别。

SELECT	COUNT(DISTINCT(gender)) AS "性别数" FROM student;

查询结果

3、查询“生物”课程比“物理”课程成绩高的所有学生的学号。

SELECT A.student_id ,sw_score , wl_score FROM 
(SELECT student_id , number AS sw_score FROM  score LEFT JOIN course ON corse_id = cid WHERE cname = "生物") AS A
INNER JOIN 
(SELECT student_id , number AS wl_score FROM  score LEFT JOIN course ON corse_id = cid WHERE cname = "物理") AS B
ON A.student_id = B.student_id
WHERE sw_score > IF(ISNULL(wl_score),0,wl_score);

查询结果

4、查询平均成绩大于60分的同学的学号和平均成绩。

SELECT student_id , AVG(number) FROM score
GROUP BY student_id HAVING AVG(number)>60;

查询结果

4-1、增加显示学生名

SELECT t.name , s.student_id , AVG(number) FROM student AS t
JOIN score AS s ON t.sid = s.student_id 
GROUP BY student_id HAVING avg(number)>60;

查询结果

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

SELECT t.sid , t.name, COUNT(corse_id) , SUM(number) FROM student AS t 
JOIN score AS s ON t.sid = s.student_id
GROUP BY t.sid;

查询结果

6、查询姓“赵”的老师的个数。

SELECT count(tname) FROM teacher WHERE tname LIKE "赵%";

查询结果

7、查询没学过钱老师课的同学的学号、姓名。

SELECT sid , name FROM student WHERE sid NOT IN 
(SELECT DISTINCT(student_id) FROM score WHERE corse_id IN 
(
SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = tid WHERE tname = "钱老师")
);

查询结果

8、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名。

answer1：SELECT sid , name FROM student WHERE sid IN 
(SELECT A.student_id FROM(SELECT corse_id , student_id FROM score  WHERE corse_id = 1) AS A 
JOIN (SELECT corse_id , student_id FROM score WHERE corse_id = 2) AS B
ON A.student_id = B.student_id);

查询结果

answer2：SELECT sid , name FROM 
(SELECT student_id , corse_id FROM score WHERE corse_id = 1 OR corse_id = 2) AS B 
LEFT JOIN student ON B.student_id = sid GROUP BY B.student_id HAVING COUNT(B.student_id)>1;

查询结果

9、查询学过孙老师所教的所有课的同学的学号、姓名。

SELECT sid , name FROM student WHERE sid IN 
(SELECT DISTINCT(student_id) FROM score WHERE corse_id IN 
(SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = tid WHERE tname = "孙老师"));

查询结果

10、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名。

SELECT sid , name , A.number AS num_1 , B.number AS num_2 FROM student LEFT JOIN 
(SELECT student_id , corse_id , number FROM score WHERE corse_id = 1) AS A ON sid = A.student_id 
LEFT JOIN 
(SELECT student_id , corse_id , number FROM score WHERE corse_id = 2) AS B ON sid = B.student_id
WHERE A.number > B.number;

查询结果

11、查询有课程成绩小于60分的同学的学号、姓名。

SELECT sid , name FROM student WHERE sid IN 
(SELECT DISTINCT(student_id) FROM score WHERE number < 60);

12、查询没有学全所有课的同学的学号、姓名。

分解过程：SELECT COUNT(DISTINCT(cid)) FROM course;-----全部课程数量
SELECT COUNT(DISTINCT(corse_id)) FROM score GROUP BY student_id;-------每个学生学的课程数量
SELECT student_id FROM score GROUP BY student_id HAVING COUNT(DISTINCT(corse_id)) = (SELECT COUNT(DISTINCT(cid)) FROM course);-----学过全部课程学生的学号

answer:

SELECT sid , name FROM student WHERE sid IN 
(
SELECT student_id FROM score GROUP BY student_id 
HAVING COUNT(DISTINCT(corse_id)) = (SELECT COUNT(DISTINCT(cid)) FROM course)
) ;

查询结果

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

分解过程：SELECT GROUP_CONCAT(corse_id) FROM score GROUP BY student_id HAVING student_id = 2;------2号同学学过的课程
SELECT student_id , GROUP_CONCAT(corse_id) AS A FROM score GROUP BY student_id;----每个同学学过的课程

answer:

SELECT student_id ,name, GROUP_CONCAT(corse_id ORDER BY corse_id)  AS corse_sum FROM student LEFT JOIN score ON  student.sid = student_id
GROUP BY student_id
HAVING corse_sum = (SELECT GROUP_CONCAT(corse_id ORDER BY corse_id)  FROM score GROUP BY student_id HAVING student_id = 2) 
AND student_id != 2;

查询结果

14、删除学习孙老师课的SC表记录。

分解过程：SELECT * FROM score WHERE corse_id IN 
(SELECT	cid FROM course LEFT JOIN teacher ON teacher_id = tid WHERE tname = "孙老师")------学过孙老师课程的记录

answer：

DELETE FROM score WHERE corse_id IN 
(SELECT cid FROM course LEFT JOIN teacher ON teacher_id = tid WHERE tname = "孙老师");

分解过程查询结果

15、按平均成绩从低到高显示所有学生的“生物”、“体育”、“物理”三门的课程成绩，按如下形式显示： 学生ID,生物，体育，物理,有效课程数,有效平均分。

分解过程：SELECT student_id , AVG(number) , COUNT(corse_id) FROM score GROUP BY student_id;---每个学生的平均成绩等信息
SELECT student_id , number AS wl_num FROM score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "物理";---学生的物理成绩
SELECT student_id , number AS sw_num FROM score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "生物";---学生的生物成绩
SELECT student_id , number AS ty_num FROM score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "体育";---学生的体育成绩
SELECT WL.student_id , wl_num , sw_num ,ty_num from 
(SELECT student_id , number AS wl_num from score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "物理") AS WL
LEFT JOIN 
(SELECT student_id , number AS sw_num from score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "生物") AS SW
ON WL.student_id = SW.student_id
LEFT JOIN
(SELECT student_id , number AS ty_num from score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "体育") AS TY
ON WL.student_id = TY.student_id;------学生的生物、物理、体育成绩

answer:

SELECT score.student_id , sw_num , ty_num , wl_num ,COUNT(corse_id),AVG(number) FROM score LEFT JOIN 
(SELECT WL.student_id , wl_num , sw_num ,ty_num FROM 
(SELECT student_id , number AS wl_num FROM score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "物理") AS WL
LEFT JOIN
(SELECT student_id , number AS sw_num FROM score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "生物") AS SW
ON WL.student_id = SW.student_id
LEFT JOIN
(SELECT student_id , number AS ty_num FROM score LEFT JOIN course ON score.corse_id = course.cid WHERE cname = "体育") AS TY
ON WL.student_id = TY.student_id) AS cor 
ON score.student_id = cor.student_id
GROUP BY student_id
ORDER BY AVG(number) DESC;

查询结果

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

SELECT corse_id , MAX(number) AS max_num , MIN(number) AS min_num FROM score 
GROUP BY corse_id;

查询结果

17、按各科平均成绩从低到高和及格率的百分数从高到低顺序。

SELECT corse_id,AVG(number) AS avg_num,
SUM(CASE WHEN score.number >= 60 THEN 1 ELSE 0 END)/COUNT(*)*100 AS percent FROM score 
GROUP BY corse_id ORDER BY avg_num ASC ,percent DESC;

查询结果

18、课程平均分从高到低显示（现实任课老师）。

SELECT AVG(IF(ISNULL(score.number),0,score.number)) AS avg_num , tname FROM score LEFT JOIN 
(SELECT cid , tname FROM course LEFT JOIN teacher ON course.teacher_id = tid) AS TH
ON score.corse_id = TH.cid
GROUP BY corse_id ORDER BY avg_num DESC;

查询结果

19、查询每门课程被选修的学生数。

SELECT corse_id , COUNT(DISTINCT(student_id)) AS num FROM score GROUP BY corse_id；

查询结果

20、查询男生、女生的人数。

SELECT DISTINCT(gender) , COUNT(gender) FROM student GROUP BY gender;

查询结果

21、查询姓“张”的学生名单。

SELECT name FROM student WHERE name LIKE "张%";

查询结果

22、查询同名同姓学生名单，并统计同名人数。

SELECT name , COUNT(name) AS num FROM student GROUP BY	name;

查询结果

23、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。

SELECT corse_id,ROUND(AVG(number),2) AS avg_num FROM score 
GROUP BY corse_id ORDER BY avg_num ASC ,corse_id DESC;

查询结果

24、求选了课程的学生人数。

SELECT COUNT(student_id) FROM score WHERE corse_id IS NOT NULL;

查询结果

25、查询各个课程及相应的选修人数。

SELECT cname ,COUNT(DISTINCT(student_id)) AS num FROM score 
LEFT JOIN course ON score.corse_id = course.cid
GROUP BY corse_id;

查询结果

26、检索至少选修两门课程的学生学号。

SELECT DISTINCT(student_id) FROM score 
GROUP BY student_id HAVING COUNT(corse_id) >=2;

查询结果

27、查询没学过赵老师讲授的任一门课程的学生姓名。

分解过程：SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = "赵老师";-----赵老师讲授的课程id
SELECT DISTINCT(student_id) FROM score WHERE corse_id IN
(SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = "赵老师");-----选过找李老师课程的学生id

answer:

SELECT name FROM student WHERE sid NOT IN 
(SELECT DISTINCT(student_id) FROM score WHERE corse_id IN 
(SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = "赵老师")
);

查询结果

28、查询两门及以上不及格课程的同学的学号及其平均成绩。

SELECT student_id , AVG(number) FROM score WHERE student_id IN 
(SELECT DISTINCT(student_id) FROM score WHERE number <60 GROUP BY student_id HAVING COUNT(student_id) >=2);

查询结果

29、查询全部学生都选修的课程的课程号和课程名。

分解过程：SELECT cid ,cname , tname  FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid;
SELECT * FROM score LEFT JOIN 
(SELECT cid ,cname , tname  FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid)  AS A 
ON score.corse_id = A.cid;

answer：

SELECT corse_id,tname FROM score LEFT JOIN 
(SELECT cid ,cname , tname  FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid) AS A 
ON score.corse_id = A.cid GROUP BY cid 
HAVING COUNT(cid) = (SELECT COUNT(sid) FROM student);

查询结果

30、查询出只选修了一门课程的全部学生的学号和姓名。

SELECT sid , name FROM student WHERE sid IN 
(SELECT DISTINCT(student_id) FROM score GROUP BY corse_id HAVING COUNT(corse_id) = 1);

查询结果

31、查询不同课程但成绩相同的学生的学号、课程号、学生成绩。

分解过程：SELECT DISTINCT s1.corse_id,s2.corse_id,s1.number,s2.number FROM score AS s1, score AS s2 WHERE s1.number = s2.number AND s1.corse_id != s2.corse_id;----分数相同课程不同

SELECT DISTINCT s1.corse_id,s2.corse_id,s1.number,s2.number FROM score AS s1, score AS s2 WHERE s1.number = s2.number ORDER BY s1.corse_id;----分数相同

SELECT DISTINCT s1.student_id , s2.student_id , s1.corse_id,s2.corse_id,s1.number,s2.number FROM score AS s1, score AS s2 WHERE s1.number = s2.number AND s1.corse_id < s2.corse_id;----分数相同课程不同，且进行了去重

answer:

SELECT DISTINCT s1.student_id , s2.student_id , s1.corse_id,s2.corse_id,s1.number,s2.number FROM
score AS s1, score AS s2 WHERE
(s1.student_id >s2.student_id AND s1.number = s2.number AND s1.corse_id != s2.corse_id)#不同学生不同课程
OR (s1.student_id = s2.student_id AND s1.number = s2.number AND s1.corse_id > s2.corse_id); #相同学生不同课程

查询结果

32、查询每门课程成绩最好的前两名。

分解过程：SELECT a.corse_id,  b.corse_id ,a.student_id , b.student_id , a.number ,  b.number FROM score AS A 
LEFT JOIN score AS B ON A.corse_id = B.corse_id  WHERE a.corse_id = 1 ;
-----自关联查询课程编号1号的课程、学生id和分数信息
SELECT a.corse_id,  b.corse_id ,a.student_id , b.student_id , a.number ,  b.number FROM score AS A 
LEFT JOIN score AS B on A.corse_id = B.corse_id AND A.number < B.number WHERE a.corse_id = 1 ;
-----对自关联查询课程编号1号的课程、学生id和分数信息去重
SELECT a.corse_id,  b.corse_id ,a.student_id , b.student_id , a.number ,  b.number FROM score AS A 
LEFT JOIN score AS B ON A.corse_id = B.corse_id AND A.number < B.number WHERE a.corse_id = 1 
GROUP BY A.corse_id , A.student_id;
-----自关联查询课程编号1号的课程、学生id和分数信息去重后根据课程编号和学生id进行分组

answer:

SELECT a.student_id , a.corse_id , a.number FROM score AS A 
LEFT JOIN score AS B ON A.corse_id = B.corse_id AND A.number < B.number
GROUP BY A.corse_id , A.student_id HAVING COUNT(A.student_id) < 2
ORDER BY A.corse_id , number DESC;

查询结果

33、查询选修赵老师所授课程的学生中，成绩最高的学生姓名及其成绩。

分解过程：SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = "赵老师";
-----查询赵老师教授课程的课程id
SELECT student_id , MAX(score.number) FROM score WHERE corse_id IN 
(SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = "赵老师");
-----查询学习过赵老师教授课程成绩最高的成绩和学生id

answer:

SELECT student.name , A.max_num FROM
(SELECT student_id , MAX(score.number) AS max_num FROM score WHERE corse_id IN 
(SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname = "赵老师")) AS A
LEFT JOIN student ON A.student_id = student.sid;

查询结果

34、查询平均成绩大于60分的所有学生的学号、姓名和平均成绩。

SELECT student_id , name ,AVG(score.number) AS avg_num FROM score 
LEFT JOIN student ON score.student_id = student.sid
GROUP BY student_id HAVING avg_num > 60;

查询结果

35、查询课程名称为“生物”，且分数低于60的学生姓名和分数。

SELECT student.name, score.number FROM score 
LEFT JOIN student ON score.student_id = student.sid 
LEFT JOIN course ON score.corse_id = course.cid
WHERE cname = "生物"  AND score.number <60;

查询结果

36、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名。

SELECT sid , name FROM student WHERE sid IN 
(SELECT score.student_id FROM score WHERE corse_id = 3 AND number >80);

查询结果

37、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名。

分解过程：SELECT score.student_id , score.corse_id FROM score WHERE student_id = 1;-----学号1号的学生学过的课程id
SELECT DISTINCT(score.student_id) FROM score WHERE corse_id IN (SELECT score.corse_id FROM score WHERE student_id = 1) AND score.student_id != 1;
------选择除了学号1号的所有和1号学生学过相同课程的学生id

answer:

SELECT sid , name FROM student WHERE sid IN 
(SELECT DISTINCT(score.student_id) FROM score 
WHERE corse_id IN (SELECT score.corse_id FROM score WHERE student_id = 1) 
AND score.student_id != 1);

查询结果