【python练习】Mysql综合练习50题

表关系

班级表：class				学生表：student
cid	caption	grade_id		sid	sname	gender	class_id
1	一年一班	1		1	乔丹	女	1
2	二年一班	2		2	艾弗森	女	1
3	三年二班	3		3	科比	男	2

老师表：teacher				课程表：course
tid	tname			cid	cname	teacher_id
1	张三			1	生物	1
2	李四			2	体育	1
3	王五			3	物理	2

成绩表：score					年级表：class_grade
sid	student_id	course_id	score		gid	gname
1	1	1	60		1	一年级
2	1	2	59		2	二年级
3	2	2	99		3	三年级

班级任职表：teach2cls
tcid	tid	cid
1	1	1
2	1	2
3	2	1
4	3	2

操作表

1. 自行创建测试数据；

建库

create database homework charset utf8;
use homework;

建表：

# 年级表
create table class_grade(
gid int auto_increment,
gname char(6) not null,
primary key(gid)
);

# 班级表
create table class(
cid int auto_increment,
caption char(6) not null,
grade_id int,
foreign key(grade_id) references class_grade(gid),
primary key(cid)
);

# 学生表
create table student(
sid int auto_increment,
sname char(6) not null,
gender enum('男','女') not null default '男',
class_id int,
foreign key(class_id) references class(cid),
primary key(sid)
);

# 老师表
create table teacher(
tid int auto_increment,
tname char(6) not null,
primary key(tid)
);

# 课程表
create table course(
cid int auto_increment,
cname char(10) not null,
teacher_id int,
foreign key(teacher_id) references teacher(tid),
primary key(cid)
);

# 成绩表
create table score(
sid int auto_increment,
student_id int,
foreign key(student_id) references student(sid),
course_id int,
foreign key(course_id) references course(cid),
score int not null,
primary key(sid)
);

# 班级任职表
create table teacher2cls(
tcid int auto_increment,
tid int,
foreign key(tid) references teacher(tid),
cid int,
foreign key(cid) references class(cid),
primary key(tcid)
);

插入数据：

# 年级表
insert into class_grade(gname) values
('一年级'),('二年级'),('三年级'),
('四年级'),('五年级'),('六年级')
;

# 班级表
insert into class(caption,grade_id) values
('一年一班',1),('一年二班',1),('一年三班',1),
('二年一班',2),('二年二班',2),
('三年一班',3),
('四年一班',4),('四年二班',4),
('五年一班',5),
('六年一班',6)
;

# 学生表
insert into student(sname,gender,class_id) values
('乔丹','女',1),('艾弗森','女',2),('科比','女',3),
('奥尔尼','男',4),('姚明','男',5),('麦迪','男',6),
('斯科拉','男',1),('詹姆斯','男',2),('韦德','女',3),
('费舍尔','男',1),('保罗','男',4),('邓肯','男',4),
('吉诺比利','女',5),('罗斯','女',6),('霍华德','女',5),
('梅西','男',2),('刘翔','男',3),('张三','男',4),
('张四','女',4)
;

# 老师表
insert into teacher(tname) values
('Alex'),('张三'),('李四'),('王五'),('李明')
;

# 课程表
insert into course(cname,teacher_id) values
('语文',1),('数学',2),('英语',3),('生物',4),
('物理',1),('化学',2),('政治',4),('体育',4)
;

# 成绩表
insert into score(student_id,course_id,score) values
(1,1,60),(1,2,80),(1,3,89),(1,4,90),(2,1,80),(2,3,90),
(3,2,81),(4,3,98),(5,1,90),(5,2,100),(5,3,98),(5,4,97),
(5,5,98),(5,6,99),(6,1,72),(6,5,80),(7,5,40),(7,6,87),
(8,5,80),(9,1,81),(10,2,30),(10,3,65),(10,4,80),(11,1,67),
(11,2,81),(11,3,38),(11,4,78),(12,2,28),(12,3,98),(13,5,95),
(14,4,81),(14,5,82),(15,1,78),(15,1,78),(16,4,79),(17,1,83)
;

# 班级任职表
insert into teacher2cls(tid,cid) values
(1,1),(1,2),(2,1),(3,2),(3,4),(5,6);

2. 查询学生总人数

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

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

select distinct(student.sid) as '学号',sname as '姓名' from student 
right join
(
    select * from score 
    right join 
    (select cid from course group by cname having cname = '生物' or cname = '物理') as t
    on score.course_id = t.cid 
    where score > 60
) as t2
on student.sid = t2.student_id;

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

select gname as '年级',class_count as '班级数' from class_grade
right join
(select grade_id,count(grade_id) as class_count from class group by grade_id order by class_count desc limit 3) as t
on class_grade.gid = t.grade_id;

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

select sid as '平均成绩最低的学生学号',sname as '姓名',minimum_avg_score as '平均成绩' from student
right join
(
    select student_id,avg(score) as minimum_avg_score from score group by student_id order by score asc limit 1
) as t
on student.sid = t.student_id;

select sid as '平均成绩最高的学生学号',sname as '姓名',highest_avg_score as '平均成绩' from student
right join
(
    select student_id,avg(score) as highest_avg_score from score group by student_id order by score desc limit 1
) as t
on student.sid = t.student_id;

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

select gname as '年级',student_count as '学生人数'from class_grade
left join
(
    select grade_id,sum(student_count) as student_count from class
    left join
    (select class_id,count(sid) as student_count from student group by class_id) as t
    on class.cid = t.class_id
    group by grade_id
) as t2
on class_grade.gid = t2.grade_id;

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

select sid as '学号',sname as '姓名',course_count as '选课数',avg_score as '平均成绩'from student
left join
(
select student_id,count(course_id) as course_count,avg(score) as avg_score from score group by student_id
) as t
on student.sid = t.student_id;

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

select * from 
(select sname as '姓名' from student where sid = 2) as t1,
(
    select cname as '成绩最低课程',score as '分数' from course
    right join
    (select course_id,score from score where student_id = 2 order by score asc limit 1) as t2
    on course.cid = t2.course_id
) as t3,
(
    select cname as '成绩最高课程',score as '分数' from course
    right join
    (select course_id,score from score where student_id = 2 order by score desc limit 1) as t4
    on course.cid = t4.course_id
) as t5
;

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

select t_count as '姓“李”的老师个数',count(cid) as '所带班级数' from teacher2cls
right join 
(select tid,count(tid) as t_count from teacher where tname like '李%') as t
on teacher2cls.tid = t.tid;

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

select  gid as '班级数小于5的年级id',gname as '年级名' from class_grade
where gid in
(select grade_id from class group by grade_id having count(cid) < 5);

11. 查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)

select cid as '班级ID',caption as '班级名称',gname as '年级',
case 
    when gid between 1 and 2 then '低年级'
    when gid between 3 and 4 then '中年级'
    when gid between 5 and 6 then '高年级'
    else null
end as '年级级别'
from class,class_grade where class.grade_id = class_grade.gid;

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

select sid as '学号',sname as '姓名' from student
where sid in
(
    select student_id from score
    where course_id in
    (
        select cid from course
        where teacher_id in
        (select tid from teacher where tname = '张三')
    )
    group by student_id having count(student_id) > 1
) ;

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

select tid as 'id',tname as '姓名' from teacher
where tid in
(select teacher_id from course group by teacher_id having count(cid) > 1);

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

select sid as '学号',sname as '姓名' from student
where sid in
(select student_id from score where course_id = 1 or course_id = 2);

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

select tid as 'id',tname as '姓名' from teacher
where tid in(
    select tid from teacher2cls
    where cid in (
        select cid from class
        where grade_id not in 
        (select gid from class_grade where gid = 5 or gid = 6)
        )
 );

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

select sid as '学号',sname as '姓名' from student
where sid in(
    select student_id from score
    where course_id in(
        select cid from course 
        where teacher_id in
        (select tid from teacher where tname = '张三')
        )
    );

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

select tid as 'id',tname as '姓名' from teacher
where tid in
(select tid from teacher2cls group by tid having count(cid) > 1);

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

select sid as '学号',sname as '姓名' from student
where sid in(
    select score.student_id as sid from score
    right join
    (select * from score where course_id = 1) as t
    on score.student_id = t.student_id and score.course_id = 2
    where score.score < t.score);

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

select tid as 'id',tname as '姓名' from teacher
where tid in
(select tid from teacher2cls group by tid order by count(cid) desc)
limit 1;

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

select distinct(sid) as '学号',sname as '姓名' from student
where sid in
(select student_id from score where score < 60);

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

select sid as '学号',sname as '姓名' from student
where sid in(
    select student_id from score group by student_id
    having count(course_id) not in
    (select count(cid) from course)
    );

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

select sid as '学号',sname as '姓名' from student
where sid in(
    select student_id from score 
    where course_id in
    (select course_id from score where student_id = 1)
    );

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

select sid as '学号',sname as '姓名' from student
where sid in(
    select student_id from score 
    where course_id in
    (select course_id from score where student_id = 1)
    having student_id != 1
    );

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

select sid as '学号',sname as '姓名' from student
where sid in(
    select distinct(student_id) from score
    where course_id in
    (select course_id from score where student_id = 2)
    having student_id != 2
    );

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

delete from score
where course_id in(
    select cid from course 
    where teacher_id in
    (select tid from teacher where tname = '张三')
    );

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

select * from score;

insert into score(student_id,score)
select student_id,avg(score) from score where course_id != 2 group by student_id;

select * from score;

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

 select ttt.student_id,c1,c2,c3,avg_score from 
    (select tt.student_id,c1,c2,avg_score from
        (select t.student_id,c1,avg_score from
            (select student_id,avg(score) as avg_score from score group by student_id order by avg(score)) as t
            left join
            (select student_id,score as c1,cname from score,course where score.course_id = course.cid having cname = '语文') as t1
            on t.student_id = t1.student_id) as tt
    left join
        (select student_id,score as c2,cname from score,course where score.course_id = course.cid having cname = '数学') as t2
    on tt.student_id = t2.student_id) as ttt
    left join

    (select student_id,score as c3,cname from score,course where score.course_id = course.cid having cname = '英语') as t3
    on ttt.student_id = t3.student_id;

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

select course_id as '课程ID',max(score) as '最高分',min(score) as '最低分' from score group by course_id ;

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

select course_id as '课程ID',avg(score) as '平均成绩' from score group by course_id order by avg(score) asc;

select course_id as '课程ID',100*sum(case when score>=60 then 1 else 0 end)/count(score) as '及格率' from score 
group by course_id 
order by sum(case when score>=60 then 1 else 0 end)/count(score) desc;

30. 课程平均分从高到低显示（现实任课老师）

select course_id as '课程ID',tid as '老师ID',tname as '姓名',avg(score) as '平均成绩' from teacher
right join
(select * from course right join score on course.cid = score.course_id) as t
on teacher.tid = t.teacher_id
group by course_id order by avg(score) desc;

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

select t1.course_id as '课程号',t1.student_id as '学号',t1.score as '成绩' from score as t1 
where 3 > 
(select count(*) from score as t2 where t1.course_id = t2.course_id and t2.score > t1.score ) 
order by t1.course_id asc,t1.score desc;

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

select cid as '课程号',cname as '课程名',s_count as '学生数' from course
right join
(select course_id,count(student_id) as s_count from score group by course_id) as t
on course.cid = t.course_id

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

select sid as '学号',sname as '姓名' from student
where sid in
(select student_id from score group by student_id having count(course_id) >= 2)；

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

select gender as '性别',count(sid) as '人数' from student group by gender order by count(sid) desc;

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

select sid as '学号',sname as '姓名' from student where sname like '张%';

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

select group_concat(sid) as '学号',sname as '姓名',count(sid) as '人数' from student group by sname having count(sid) > 1;

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

select course_id,avg(score) from score group by course_id order by avg(score) asc,course_id desc;

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

select sname as '姓名',score as '分数' from student
right join(
    select * from score 
    where course_id in
    (select cid from course where cname = '数学')
    having score < 60) as t
on student.sid = t.student_id;

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

select student.sid as '学号',sname as '姓名' from student
right join
(select * from score where course_id = 3 having score > 80) as t
on student.sid = t.student_id;

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

select count(student_id) as '人数' from (select student_id from score group by student_id) as t;

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

select distinct(cname) as '课程',sname as '学生姓名',max_score as '最高成绩',min_score as '最低成绩' from 
(select sname,course_id,score from score left join student on score.student_id = student.sid) as score2student,
(select course_id,cname,max(score) as max_score,min(score) as min_score from score
right join(
        select cid,cname from course
        where teacher_id in
        (select tid from teacher where tname = '王五') 
        ) as t
    on score.course_id = t.cid
    group by course_id) as t1
where score2student.course_id = t1.course_id and score2student.score = t1.max_score;

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

select course_id as '课程ID',cname as '课程名',count(student_id) as '选修人数' from course left join score on course.cid = score.course_id
group by course_id;

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

select distinct(score.student_id) as '学号',score.course_id as '课程号1',t1.course_id as '课程号2',score.score as '成绩' from score,score as t1
where score.score = t1.score and score.course_id != t1.course_id
order by score.score;

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

select cid as '课程号',student.sid as '学号',sname as '姓名',sc as '分数' from student
right join
(select t1.student_id as sid,t1.score as sc,t1.course_id as cid from score as t1 
where 2 > 
(select count(*) from score as t2 where t1.course_id = t2.course_id and t2.score > t1.score ) 
order by t1.course_id asc,t1.score desc) as tt
on student.sid = tt.sid
;

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

select student_id as '学号' from score group by student_id having count(course_id) >= 2;

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

select cid as '课程号',cname as '课程' from course
where cid in
(select course_id from score group by course_id having count(student_id) = 0);

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

select tid as '老师ID',tname as '姓名' from teacher
where tid in
(select tid from teacher2cls group by tid having count(cid) = 0);

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

select student_id as '学号',avg(score) as '平均成绩' from score
where student_id in
(select student_id from score where score > 80 group by student_id having count(score) >= 2)
group by student_id;

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

select student_id as '学号',course_id as '课程号',score as '分数' from score where course_id = 3 and score < 60 order by score desc;

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

delete from score where student_id = 2 and course_id = 1

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

select sid as '学号',sname as '姓名' from student where sid in(    
    select student_id from score
    where course_id in
    (select cid from course where cname = '物理' or cname = '生物')
    group by student_id 
    having count(course_id) = 2
    );

转载于:https://www.cnblogs.com/q1ang/p/9678008.html