mysql查詢選課最少成績最高

最近在學習MYSQL?數據庫，在此mark?一下做過的sql?相關練習

表結構如下：

teacher表

tid

tname

class表

cid

caption

course表

cid

cname

teacher_id

student表

sid

gender

class_id

sname

score表

sid

student_id

course_id

num

class :

?teacher :?

?course :??

student :

score :?

根據以上圖片建表并進行SQL?語句練習：? (本次練習使用的MySQL?版本為 8.0.21，系統為windows 10?系統)

1.自行創建測試數據庫

/*Navicat Premium Data Transfer

Source Server : localhost

Source Server Type : MySQL

Source Server Version : 50624

Source Host : localhost

Source Database : sqlexam

Target Server Type : MySQL

Target Server Version : 50624

File Encoding : utf-8

Date: 2020年10月8日09:02:02*/

SETNAMES utf8;SET FOREIGN_KEY_CHECKS = 0;--------------------------------Table structure for `class`------------------------------

DROP TABLE IF EXISTS`class`;CREATE TABLE`class` (

`cid`int(11) NOT NULLAUTO_INCREMENT,

`caption`varchar(32) NOT NULL,PRIMARY KEY(`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;--------------------------------Records of `class`------------------------------

BEGIN;INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');COMMIT;--------------------------------Table structure for `course`------------------------------

DROP TABLE IF EXISTS`course`;CREATE TABLE`course` (

`cid`int(11) NOT NULLAUTO_INCREMENT,

`cname`varchar(32) NOT NULL,

`teacher_id`int(11) 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=5 DEFAULT CHARSET=utf8;--------------------------------Records of `course`------------------------------

BEGIN;INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '體育', '3'), ('4', '美術', '2');COMMIT;--------------------------------Table structure for `score`------------------------------

DROP TABLE IF EXISTS`score`;CREATE TABLE`score` (

`sid`int(11) NOT NULLAUTO_INCREMENT,

`student_id`int(11) NOT NULL,

`course_id`int(11) NOT NULL,

`num`int(11) 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=53 DEFAULT CHARSET=utf8;--------------------------------Records of `score`------------------------------

BEGIN;INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');COMMIT;--------------------------------Table structure for `student`------------------------------

DROP TABLE IF EXISTS`student`;CREATE TABLE`student` (

`sid`int(11) NOT NULLAUTO_INCREMENT,

`gender`char(1) NOT NULL,

`class_id`int(11) NOT NULL,

`sname`varchar(32) NOT NULL,PRIMARY KEY(`sid`),KEY`fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES`class` (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;--------------------------------Records of `student`------------------------------

BEGIN;INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '鋼蛋'), ('3', '男', '1', '張三'), ('4', '男', '1', '張一'), ('5', '女', '1', '張二'), ('6', '男', '1', '張四'), ('7', '女', '2', '鐵錘'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '劉三'), ('14', '男', '3', '劉一'), ('15', '女', '3', '劉二'), ('16', '男', '3', '劉四');COMMIT;--------------------------------Table structure for `teacher`------------------------------

DROP TABLE IF EXISTS`teacher`;CREATE TABLE`teacher` (

`tid`int(11) NOT NULLAUTO_INCREMENT,

`tname`varchar(32) NOT NULL,PRIMARY KEY(`tid`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;--------------------------------Records of `teacher`------------------------------

BEGIN;INSERT INTO `teacher` VALUES ('1', '張磊老師'), ('2', '李平老師'), ('3', '劉海燕老師'), ('4', '朱云海老師'), ('5', '李杰老師');COMMIT;SET FOREIGN_KEY_CHECKS = 1;

View Code

2.查詢生物課程比物理課程成績高的所有學生的學號

--我的寫法：

select A.student_id from score asAleft join score as B on A.student_id =B.student_idwhere A.course_id in (select cid from course where cname = '生物')and B.course_id in (select cid from course where cname = '物理')and A.num >B.num;--參考寫法：

select A.student_id from(select score.sid, student_id,course.cname,num from score left join course on course.cid = score.course_id where course.cname = '生物') asAinner join(select score.sid,student_id,course.cname,num from score left join course on course.cid = score.course_id where course.cname = '物理') asBon A.student_id =B.student_idwhere A.num > B.num;

View Code

3.查詢平均成績大于60分的同學的學號和平均成績

select b.student_id,student.sname,B.avgnum from(select student_id ,AVG( num) as avgnum fromscoreGROUP BY student_id having AVG(num) > 60

ORDER BY avg(num) desc) asBLEFT JOIN student on B.student_id = student.sid ;

View Code

4.查詢所有同學的學號，姓名，選課數，總成績

select student.sid,student.sname,Count(score.course_id),sum(score.num) fromstudentleft join score on score.student_id =student.sidGROUP BY student.sid;

View Code

5.查詢姓李的老師的個數

select '姓李的老師個數',count(1) from teacher where tname like '李%';

View Code

6.查詢沒學過葉平老師課的同學的學號和姓名

select sa.sid,sa.sname from student assaWHERE sa.sid not in(select score.student_id fromscoreleft join student on score.student_id =student.sidleft join course on course.cid =score.course_idleft join teacher on teacher.tid =course.teacher_idwhere teacher.tname like '李平%');

View Code

7.查詢學過 001 并且也學過編號 002 課程的同學的學號和姓名

select score.student_id,student.sname fromscoreleft join student on student.sid =score.student_idwhere score.course_id = 1 or score.course_id = 2

GROUP BY score.student_id having count(score.course_id) > 1 ;

View Code

8.查詢學過?李平老師所有課程的同學的學號和姓名

select A.student_id,student.sname from score asAleft join student on student.sid =A.student_idwhere A.course_id in(SELECT course.cid fromcourseleft join teacher on teacher.tid =course.teacher_idwhere teacher.tname like '李平%')group by A.student_id having count(A.course_id) =(SELECT count(course.cid) fromcourseleft join teacher on teacher.tid =course.teacher_idwhere teacher.tname like '李平%');

View Code

9.查詢課程編號 002 的成績比課程編號 001 課程低的所有同學的學號和姓名

select A.student_id,student.sname from score asAinner join score as B on A.student_id =B.student_idleft join student on student.sid =A.student_idwhere A.course_id = 1 and B.course_id = 2 and A.num < B.num;

View Code

10 查詢有課程成績小于 60 分的同學的學號和姓名

select student_id,sname fromscoreleft join student on student.sid =score.student_idwhere score.num < 60

group byscore.student_id;select sid,sname from student where sid in(select student_id from score where num < 60 GROUP BYstudent_id);select sid,sname from student where sid in(select distinct student_id from score where num < 60) ; --distinct 的效率不高，能不用就不用，用其他方法實現去重

View Code

11.查詢沒有學全所有課程的同學的學號和姓名

--解答分析：--1.查出所有課程ID--2.查出所有學過所有課程的學生ID--3.從所有學生中去掉上面查詢出來的學生

--主要：這道題有一個題目理解的問題，就是"沒有學全所有課程的學生"包不包括一門課沒選的學生?--如果理解為包括，則是答案1的解法--如果理解為不包括，則是答案2、3 的解法

--答案一：

select sid,sname fromstudentwhere sid not in(select student_id fromscoreGROUP BY student_id having count(1) = ( select count(1) fromcourse)

);--答案二:

select student.sid,student.sname fromstudentleft JOIN score on score.student_id =student.sidGROUP BY student.sid having count(score.course_id) >= 1 and count(score.course_id) < ( select count(1) fromcourse) ;--答案三:

select student_id ,sname fromscoreleft join student on student.sid =score.student_idGROUP BY student_id HAVING count(1)

View Code

12.查詢至少有一門課與學號 001 的同學所學相同的同學的學號和姓名

--解答思路:--1.先查出001 的同學所學的課程ID--2.查score 表中student_id != 1 的學生的課程ID 在上面查出的范圍內的數據

select student.sid,student.sname fromscoreleft join student on student.sid =score.student_idwhere student_id != 1 and score.course_id in (select B.course_id from score as B where B.student_id = 1)GROUP BY score.student_id;

View Code

13.查詢至少學過學號為 001 同學所選課程中任意一門課的其他同學學號和姓名

select student.sid,student.sname fromscoreleft join student on student.sid =score.student_idwhere student_id != 1 and course_id in (SELECT course_id from score where student_id = 1)GROUP BY score.student_id having count(score.course_id) >= (select count(1) from score where student_id = 1);

View Code

14.查詢和 002 號同學學習的課程完全相同的其他同學的學號和姓名

select score.student_id,student.sname fromscoreleft join student on student.sid =score.student_idwhere student_id in(select student_id fromscorewhere student_id != 2

GROUP BY student_id having count(course_id ) = (select count(1) from score where student_id = 2))and course_id in (select course_id from score where student_id = 2)GROUP BY student_id having count(course_id) = (select count(1) from score where student_id = 2);

View Code

15.刪除學習 '葉平' 老師課的score表記錄

delete fromscorewhere course_id in(select course.cid fromcourseleft join teacher on teacher.tid =course.teacher_idwhere tname like '葉平%');

View Code

16.向score 表插入一些記錄，這些記錄要求符合以下條件：1.沒有上過編號 002 課程的同學學號 2.插入 002 號課程的平均成績

select sid,2,(select avg(num) from score where course_id = 2 ) fromstudentwhere sid not in (select student_id from score where course_id = 2);

View Code

17、按平均成績從低到高顯示所有學生的生物,物理,體育三門的課程成績，按如下形式顯示：學生ID,生物,物理,體育,有效課程數,有效平均分；

select A.sid as學生ID,

(SELECT num from score left JOIN course on course.cid = score.course_id where course.cname = '生物' and score.student_id = A.sid) as生物,

(SELECT num from score left JOIN course on course.cid = score.course_id where course.cname = '物理' and score.student_id = A.sid) as物理,

(SELECT num from score left JOIN course on course.cid = score.course_id where course.cname = '體育' and score.student_id = A.sid) as體育,

(SELECT count(1) from score where score.student_id =A.sidand num is not null and score.course_id in(select cid from course where cname in ('生物','物理','體育'))) as有效課程數,

(SELECT avg(num) from score where score.student_id =A.sidand num is not null and score.course_id in(select cid from course where cname in ('生物','物理','體育'))) as三門成績有效平均分from student asAleft join score on score.student_id =A.sidGROUP BY A.sid;

View Code

18.查詢各科成績最高分和最低分，以如下形式顯示：課程ID，最高分，最低分

selectcourse.cidas課程ID ,max(score.num) as最高分,min(score.num) as最低分 .fromcourseleft JOIN score on score.course_id =course.cidGROUP BY course.cid;

View Code

19.按各科平均成績從低到高和及格率的百分比從高到低順序

selectA.cidas課程ID,avg(num) as課程平均分,AVG(A.passed ) 及格率from (select course_id as cid,num,case when num >=60 then 1 else 0 end as passed from score ) asAGROUP BYA.cidORDER BY avg(num) asc ,AVG(A.passed ) desc;selectcourse_idas課程ID,avg(num) as課程平均分,AVG(case when num >=60 then 1 else 0 end ) as及格率fromscoreGROUP BYcourse_idORDER BY avg(num) asc ,AVG(case when num >=60 then 1 else 0 end ) desc;

View Code

20.課程平均分從高到低顯示(顯示任課老師)

selectcourse.cidas課程ID,avg(score.num) as平均分,

teacher.tnameas授課老師fromcourseleft join score on score.course_id =course.cidleft join teacher on teacher.tid =course.teacher_idGROUP BYcourse.cidorder by avg(score.num) desc;

View Code

21.查詢個課程成績前三名的記錄(不考慮成績并列情況)

selectA.course_id,

A.student_id,

A.numfrom score asAwhere num >= (select num from score where course_id = A.course_id ORDER BY num desc LIMIT 2,1)ORDER BY A.course_id asc ,A.num desc;SELECTscore.course_id,score.student_id,score.numfromscoreleft JOIN(selectA.course_id,

(select num from score where score.course_id = A.course_id ORDER BY num desc LIMIT 2,1) asnumfrom score asAGROUP BYcourse_id

)as B on score.course_id =B.course_idwhere score.num >=B.numORDER BY score.course_id asc ,score.num desc;

View Code

22.查詢每門課程被選修的學生數

SELECTcourse.cid ,count(score.course_id)fromcourseleft JOIN score on score.course_id =course.cidGROUP BY course.cid;

View Code

23.查詢出只選修了一門課程的全部學生的學號和姓名

select student.sid,student.sname fromscoreleft JOIN student on student.sid =score.student_idgroup by score.student_id having count(course_id) = 1;

View Code

24.查詢男生、女生人數

SELECT gender,count(1) from student group by gender;

View Code

25.查詢姓張的學生名單

select * from student where sname like '張%';

View Code

26.查詢同名同姓學生名單，并統計同名人數

select sname ,COUNT(1) from student GROUP BY sname ;

View Code

27.查詢每門課程的平均分，結果按平均成績升序排列，平均成績相同時，按課程號降序排列

select course.cid,avg( case when isnull(score.num) then 0 else score.num end) fromcourseLEFT JOIN score on score.course_id =course.cidGROUP BYcourse.cidORDER BY avg( case when isnull(score.num) then 0 else score.num END) asc ,score.course_id;

View Code

28.查詢平均成績大于 85 的所有學生的學號和姓名

select student.sid,student.sname,avg(num) fromscoreleft JOIN student on student.sid =score.student_idGROUP BY student_id having avg(num) > 60

ORDER BY avg(num) desc;

View Code

29 查詢課程為?物理，且分數低于60 的學生的姓名和分數

SELECT course_id,student.sname,num fromscoreLEFT JOIN student on student.sid =score.student_idwhere course_id = (select cid from course where cname = "物理") and num < 60;

View Code

30.查詢課程編號為003 且課程成績在80 分以上的學生的學號和姓名

SELECT course_id,student.sname,num fromscoreLEFT JOIN student on student.sid =score.student_idwhere course_id = 3 and num >80;

View Code

31.求選了課程的學生人數

select count(1) from score GROUP BY student_id;

View Code

32.查詢選修?李平?老師所教授的學生中，成績最高的學生姓名和成績

select score.course_id,score.student_id,student.sname,score.num fromscoreleft join(select course_id ,max(num) as num from score where course_id in(select cid from course where teacher_id = (select tid FROM teacher where tname like '李平%'))group by course_id ) asAon score.course_id =A.course_idleft join student on student.sid =score.student_idwhere score.num = A.num;

View Code

33.查詢各個課程及相應的選修人數

select course.cid,course.cname,count(score.student_id) fromcourseleft JOIN score on score.course_id =course.cidGROUP BY course.cid;

View Code

34.查詢不同課程但成績相同的學生的學號，課程號，學生成績

--不要在意這道題的結果，因為這個查詢需求根本就是個奇葩

select a1.student_id, a1.course_id,a2.student_id,a2.course_id ,a2.num from score as a1 ,score asa2where (a1.student_id = a2.student_id and a1.course_id < a2.course_id and a1.num = a2.num )or(a1.student_id!= a2.student_id and a1.course_id != a2.course_id and a1.num =a2.num)ORDER BY a1.course_id asc;

View Code

35.查詢每門課程成績最好的前兩名

selectA.course_id,

A.student_id,

A.numfrom score asAwhere num >= (select num from score where course_id = A.course_id ORDER BY num desc LIMIT 1,1)ORDER BY A.course_id asc ,A.num desc;SELECTscore.course_id,score.student_id,score.numfromscoreleft JOIN(selectA.course_id,

(select num from score where score.course_id = A.course_id ORDER BY num desc LIMIT 1,1) asnumfrom score asAGROUP BYcourse_id

)as B on score.course_id =B.course_idwhere score.num >=B.numORDER BY score.course_id asc ,score.num desc;

View Code

36.檢索至少選修兩門課程的學生學號

select student_id fromscoreGROUP BY student_id having count(course_id) >=2;

View Code

37.查詢全部學生都選修的課程的課程號和課程名

select score.course_id,course.cname fromscoreleft JOIN course on course.cid =score.course_idgroup by course_id having count(student_id) = (select count(1) from student);

View Code

38.查詢沒學過葉平老師講授的任意一門課程的學生姓名

select sname fromstudentwhere sid not in(select student_id fromscorewhere course_id in(select cid fromcourseleft JOIN teacher on teacher.tid =course.teacher_idwhere tname like '李平%')

);

View Code

39.查詢兩門以上不及格課程的學生的學號及平均成績

select student_id,avg(num) fromscorewhere num <60

GROUP BY student_id having count(1)>=2 ;

View Code

40.檢索 004 課程分數小于60，按分數降序排列的同學學號

select student_id,num fromscorewhere course_id = 4 and num < 60

order by num desc;

View Code

41刪除 002 同學的 001 課程成績

delete fromscorewhere student_id = 2 and course_id = 1;