mysql测试题蔡铜_mysql练习题

MYSQL练习题详解

对于我来说，这是一次不轻松的作业。

如果对我给出的答案有不同的见解，欢迎指教

在评论区留下您的建议，我会及时回复

1、自行创建测试数据；

# 创建本次作业的数据库create databasemysql_homework;

# 首先创建老师表usemysql_homework;create tableteacher(

tidint primary keyauto_increment,

tnamevarchar(30)

);insert into teacher(tname) values('张三'),

('李四'),

('王五')

;

# 首先创建学生表create tablestudent(

sidint primary keyauto_increment,

snamevarchar(30),

gender enum("男","女")not null default"男"

);insert into student(sname) values('乔丹'),

('艾弗森'),

('科比')

;

# 创建年级表create tableclass_grade(

gidint primary keyauto_increment,

gnamevarchar(30)

);insert into class_grade(gname) values('一年级'),

('二年级'),

('三年级')

;

# 创建班级表create tableclass(

cidint primary keyauto_increment,

captionvarchar(20),

grade_idint,foreign key(grade_id) referencesclass_grade(gid)on delete cascade

on update cascade)engine=innodb;insert into class(caption,grade_id) values('一年级一班',1),

('二年级一班',2),

('三年级二班',3)

;

# 创建课程表create tablecourse(

cidint primary keyauto_increment,

cnamevarchar(20),

teacher_idint,foreign key(teacher_id) referencesteacher(tid)on delete cascade

on update cascade)engine=innodb;insert into course(cname,teacher_id) values('生物',1),

('体育',1),

('物理',2)

;

# 创建班级任职表create tableteach2cls(

tcidint not null uniqueauto_increment,

tidint not null,

cidint not null,foreign key(tid) referencesteacher(tid)on delete cascade

on update cascade,foreign key(cid) referencesclass(cid)on delete cascade

on update cascade,primary key(tid,cid)

);insert into teach2cls(tid,cid) values(1,1),

(1,2),

(2,1),

(3,2)

;

# 创建成绩表create tablescore(

sidint not null uniqueauto_increment,

student_idint not null,

course_idint not null,

scoreint not null,foreign key(student_id) referencesstudent(sid)on delete cascade

on update cascade,foreign key(course_id) referencescourse(cid)on delete cascade

on update cascade,primary key(student_id,course_id)

);insert into score(student_id,course_id,score) values(1,1,60),

(1,2,59),

(2,2,99)

;

View Code

2、查询学生总人数；

select count(*) as '学生总数' from student sid;

View Code

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；

select sid as '学号',sname as '姓名' fromstudentwhere sid in(selectstudent_idfromscorewhere score>60 and course_id in(select cid fromcoursewhere cname in ('生物','物理')

)group bystudent_idhaving count(student_id)= 2) ;

View Code

4、查询每个年级的班级数，取出班级数最多的前三个年级；

select class_grade.gname as'年级',count(caption) as '班级数'

from class left join class_grade on grade_id =gidgroup bygrade_idorder by count(caption) desclimit3;

View Code

5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；

select t3.student_id as "学号", t4.sname as "姓名", t3.avg_score as"平均成绩"from(select *

from(select student_id, avg(score) asavg_scorefromscoregroup bystudent_idorder by avg(score) desclimit1)ast1union

select *

from(select student_id,avg(score)fromscoregroup bystudent_idorder by avg(score)

limit1)ast2

)ast3inner join student ast4on t3.student_id =t4.sid

;

View Code

6、查询每个年级的学生人数；

select gname as '年级', num as '总人数' from (select class.grade_id as cg_id,count(sid) as num fromstudentleft join class on class_id =cidgroup by class.grade_id)ast2left join class_grade on t2.cg_id = class_grade.gid;

View Code

7、查询每位学生的学号，姓名，选课数，平均成绩；

select student.sid as '学号',student.sname as '姓名',count(score.course_id) as '选课数',avg(score.score) as '平均分' fromscoreleft join student onscore.student_id=student.sidgroup by student_id;

View Code

8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；

select sname as '姓名',cname as '课程名',score as '分数'

from (select sname,course_id,score from score left joinstudenton score.student_id =student.sidwhere student.sid = 2) as t3 left joincourseon t3.course_id=course.cidwhere t3.score in ((select max(score) fromt3),

(select min(score) from t3));

View Code

9、查询姓“李”的老师的个数和所带班级数；

select count(teacher.tid) as '李老师人数', GROUP_CONCAT(teach2cls.cid) as '班级数' fromteacherright jointeach2clson teacher.tid =teach2cls.tidwhere teacher.tname like '李%'

group by teacher.tid;

View Code

10、查询班级数小于5的年级id和年级名；

select gid ,class_grade.gname as'年级'

from class left join class_grade on grade_id =gidgroup bygrade_idhaving count(caption) <5;

View Code

11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，示例结果如下；

班级id班级名称年级年级级别

1

一年一班

一年级

低

selectclass.cidas '班级id',

class.captionas '班级名称',

class_grade.gnameas '年级',case

when class_grade.gid between 1 and 2 then '低'

when class_grade.gid between 3 and 4 then '中'

when class_grade.gid between 5 and 6 then '高' else 0

end as '年级级别'

fromclassleft joinclass_gradeon class.grade_id = class_grade.gid;

View Code

12、查询学过“张三”老师2门课以上的同学的学号、姓名；

select sid,sname fromstudentwhere sid in(select score.student_id fromscoreleft joincourseon score.course_id =course.cidwhere course.teacher_id in(select tid fromteacherwhere tname = '张三')group bystudent_idhaving count(course.cid) >2);

View Code

13、查询教授课程超过2门的老师的id和姓名；

select teacher.tid, teacher.tname as '姓名'

fromcourseleft jointeacheron course.teacher_id =teacher.tidgroup byteacher_idhaving count(cid) > 2;

View Code

14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；

select student.sid as '学号', student.sname as '姓名' fromscoreleft joinstudenton score.student_id =student.sidwhere course_id in (1,2)group bystudent_idhaving count(course_id) =2;

View Code

15、查询没有带过高年级的老师id和姓名；

select teach2cls.tid,teacher.tname fromteach2clsleft jointeacheron teach2cls.tid =teacher.tidwhere cid not in(select cid fromclasswhere grade_id > 4)group by tid;

View Code

16、查询学过“张三”老师所教的所有课的同学的学号、姓名；

select score.student_id, student.sname fromscoreleft joinstudenton score.student_id =student.sidwhere score.course_id in(select cid fromcoursewhere teacher_id =(select tid fromteacherwhere tname = '张三')

)group by student_id;

View Code

17、查询带过超过2个班级的老师的id和姓名；

select teach2cls.tid,teacher.tname fromteach2clsleft jointeacheron teach2cls.tid =teacher.tidgroup bytidhaving count(cid)> 2;

View Code

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

select student.sid,student.sname fromstudentwhere student.sid in(select t1.student_id from(select student_id,score fromscorewhere course_id = 1

group bystudent_id

)ast1,

(select student_id,score fromscorewhere course_id = 2

group bystudent_id

)ast2where t1.student_id =t2.student_idand t2.score

);

View Code

19、查询所带班级数最多的老师id和姓名；

select teacher.tid, teacher.tname fromteacherwhere teacher.tid =(select teach2cls.tid fromteach2clsgroup byteach2cls.tidorder by count(cid) desclimit1);

View Code

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

select student.sid,student.sname fromstudentwhere student.sid in(select distinct score.student_id fromscorewhere score.score < 60);

View Code

21、查询没有学全所有课的同学的学号、姓名；

select concat('学号:',student.sid,'>>>姓名：',student.sname) as '没有学全所有课的同学'fromstudentwhere student.sid in(select distinct score.student_id fromscoregroup bystudent_idhaving count(course_id)

)

);

View Code

22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；

select concat('学号:',student.sid,'姓名：',student.sname)as '至少有一门课与学号为“1”的同学所学相同的同学'

fromstudentwhere student.sid in(select distinct score.student_id fromscorewhere score.course_id in(select course_id fromscorewhere student_id = 1)

);

View Code

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；

select concat('学号:',student.sid,'姓名：',student.sname)as '至少学过学号为“1”同学所选课程中任意一门课的其他同学'

fromstudentwhere student.sid in(select distinct score.student_id fromscorewhere score.course_id in(select course_id fromscorewhere student_id = 1)having student_id != 1);

View Code

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

select concat('学号:',student.sid,'姓名：',student.sname)as '和“2”号同学学习的课程完全相同的其他同学'

fromstudentwhere student.sid in(select score.student_id fromscore,(select course_id fromscorewhere student_id = 2)ast1where score.course_id = t1.course_id and score.student_id !=2

group byscore.student_idhaving count(score.course_id) =(select count(course_id) fromscorewhere student_id = 2)

);

View Code

25、删除学习“张三”老师课的score表记录；

delete fromscorewhere course_id in(select cid fromcoursewhere teacher_id =(select tid fromteacherwhere tname = '张三')

);

View Code

26、向score表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；

insert intoscore(student_id,course_id,score)select t1.sid,2,t2.avg from(select sid fromstudentwhere sid not in(select student_id fromscorewhere course_id = 2)

)ast1,

(select avg(score) as avg fromscoregroup bycourse_idhaving course_id = 2)as t2;

View Code

27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

select *,(case when 数学 is null then 0 else 1 end)+(case when 英语 is null then 0 else 1 end)+(case when 语文 is null then 0 else 1 end)as有效成绩数,

(语文+数学+英语)/(

(case when 数学 is null then 0 else 1 end)+(case when 英语 is null then 0 else 1 end)+(case when 语文 is null then 0 else 1 end)

)as有效平均分from(SELECT student_id AS学生ID,

(SELECT score FROM score LEFT JOIN course on score.course_id=course.cidWHERE course.cname="语文" AND student_id=s1.student_id) AS语文,

(SELECT score FROM score LEFT JOIN course on score.course_id=course.cidWHERE course.cname="数学" AND student_id=s1.student_id) AS数学,

(SELECT score FROM score LEFT JOIN course on score.course_id=course.cidWHERE course.cname="英语" AND student_id=s1.student_id) AS英语FROM score as s1 GROUP BYstudent_id

)as t2;

View Code

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

select course_id,max(score) as max_score,min(score) asmin_scorefromscoregroup by course_id；

View Code

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

select course_id,avg(score) asavg_score,sum(case when score.score>60 then 1 else 0 end)/count(1)*100 as percent

fromscoregroup bycourse_idorder by

avg(score) asc,percent desc;

View Code

30、课程平均分从高到低显示(现实任课老师)；

select course.cname, avg(score) as'平均分' fromcourseright joinscoreon course.cid =score.course_idgroup byscore.course_idorder by avg(score) desc;

View Code

31、查询各科成绩前三名的记录(不考虑成绩并列情况)

SELECTt1.student_id,t1.course_id,t1.scoreFROM score ast1WHERE(SELECT COUNT(course_id)FROMscoreWHERE t1.course_id= course_id AND t1.score

ORDER BY t1.course_id,score DESC；

View Code

32、查询每门课程被选修的学生数；

select course.cname as '课程名', t1.sum as '学生数' fromcourse,

(select course_id,count(student_id) as sum fromscoregroup bycourse_id

)ast1where course.cid = t1.course_id;

View Code

33、查询选修了2门以上课程的全部学生的学号和姓名；

select student.sid,student.sname fromstudentwhere student.sid in(select student_id fromscoregroup bystudent_idhaving count(course_id) >2);

View Code

34、查询男生、女生的人数，按倒序排列；

select gender,count(sid) as sum fromstudentgroup bygenderorder by sum desc;

View Code

35、查询姓“张”的学生名单；

select sname fromstudentwhere sname like "张%";

View Code

36、查询同名同姓学生名单，并统计同名人数；

select sname,count(sid)as'同名人数' fromstudentgroup bysnamehaving count(sid)>1;

View Code

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

select course.cname, avg(score) as'平均分' fromcourseright joinscoreon course.cid =score.course_idgroup byscore.course_idorder by avg(score), course.cid desc;

View Code

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

select student.sname,score.score fromscoreleft joinstudenton score.student_id =student.sidwhere score.course_id = (select cid from course where cname = '数学')and score.score<60;

View Code

39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；

select student.sname,score.score fromscoreleft joinstudenton score.student_id =student.sidwhere score.course_id = 3

and score.score>80;

View Code

40、求选修了课程的学生人数

select course_id,count(student_id) as '学生人数' fromscoregroup by course_id;

View Code

41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；

selectstudent.sname,score.scorefromscoreleft joinstudenton score.student_id =student.sidwhere course_id in(select cid fromcoursewhere teacher_id in(select tid fromteacherwhere tname ="王五"

)

)order by score.score desclimit1;

View Code

42、查询各个课程及相应的选修人数；

select course.cname, count(student_id) as'选修人数' fromscoreleft joincourseon score.course_id =course.cidgroup by course_id;

View Code

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select score.student_id,score.course_id,score.score from score,(select score,student_id fromscoregroup byscore,student_idhaving count(course_id)>1)ast1where score.score =t1.score and score.student_id =t1.student_id;

View Code

44、查询每门课程成绩最好的前两名学生id和姓名；

selectstudent.sid,

student.sname,

t2.course_id,

t2.score,

t2.first_score,

t2.second_scorefromstudentinner join(selectscore.student_id,

score.course_id,

score.score,

t1.first_score,

t1.second_scorefromscoreinner join(selects1.sid,

(select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) asfirst_score,

(select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) assecond_scorefromscoreass1

)as t1 on score.sid =t1.sidwherescore.scorein(

t1.first_score,

t1.second_score

)

)as t2 on student.sid = t2.student_id;

View Code

45、检索至少选修两门课程的学生学号；

select student_id fromscoregroup bystudent_idhaving count(course_id)>1;

View Code

46、查询没有学生选修的课程的课程号和课程名；

select cid,cname fromcoursewhere cid not in(select distinct course_id fromscore

);

View Code

47、查询没带过任何班级的老师id和姓名；

select tid,tname fromteacherwhere tid not in(select distinct tid fromteach2cls

);

View Code

48、查询有两门以上课程超过80分的学生id及其平均成绩；

select student_id,avg(score)as '平均成绩' fromscorewhere score >80

group bystudent_idhaving count(course_id)>1;

View Code

49、检索“3”课程分数小于60，按分数降序排列的同学学号；

select student_id fromscorewhere course_id =3 and score <60

order by score desc;

View Code

50、删除编号为“2”的同学的“1”课程的成绩；

delete fromscorewhere student_id =2 and course_id=1;

View Code

51、查询同时选修了物理课和生物课的学生id和姓名；

select sid as '学号',sname as '姓名' fromstudentwhere sid in(selectstudent_idfromscorewhere course_id in(select cid fromcoursewhere cname in ('生物','物理')

)group bystudent_idhaving count(student_id)= 2) ;

View Code