Fork me on GitHub

MySQL：测试题

一，表关系的练习测试

请创建如下表关系，并建立相关约束

一，创建表结构数据：

创建的话肯定先创建没有关联的表，老师，课程（关联老师），年级，班级（关联年级），学生（关联班级），
	班级任职表 （关联老师，课堂） 
 


create table teacher(
    tid int primary key auto_increment,
    tname varchar(16) not null
);

create table class_grade(
    gid int primary key auto_increment,
    gname varchar(16) not null unique
);

create table course(
    cid int primary key auto_increment,
    cname varchar(16) not null,
    teacher_id int not null,
    foreign key(teacher_id) references teacher(tid)
);


create table class(
    cid int primary key auto_increment,
    caption varchar(16) not null,
    grade_id int not null,
    foreign key(grade_id) references class_grade(gid)
    on update cascade
    on delete cascade
);

create table student(
    sid int primary key auto_increment,
    sname varchar(16) not null,
    gender enum('女','男') not null default '男',
    class_id int not null,
    foreign key(class_id) references class(cid)
    on update cascade
    on delete cascade
);


create table score(
    sid int not null unique auto_increment,
    student_id int not null,
    course_id int not null,
    score int not null,
    primary key(student_id,course_id),
    foreign key(student_id) references student(sid)
    on delete cascade
    on update cascade,
    foreign key(course_id) references course(cid)
    on delete cascade
    on update cascade
);

create table teach2cls(
    tcid int not null unique auto_increment,
    tid int not null,
    cid int not null,
    primary key(tid,cid),
    foreign key(tid) references teacher(tid)
    on delete cascade
    on update cascade,
    foreign key(cid) references class(cid)
    on delete cascade
    on update cascade
);

2，插入表数据

    插入数据

	老师的数据
	年级的数据
	班级的数据
	课程的数据
	学生的数据
	成绩的数据
	老师班级的数据


insert into teacher(tname) values
    ('张三'),
    ('李四'),
    ('王五');

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

insert into class(caption,grade_id) values
    ('一年一班',1),
    ('一年二班',1),
    ('一年三班',1),
    ('二年一班',2),
    ('二年二班',2),
    ('二年三班',2),
    ('三年一班',3),
    ('三年二班',3),
    ('三年三班',3);

insert into course(cname,teacher_id) values
    ('生物',1),
    ('体育',1),
    ('物理',2),
    ('数学',2),
    ('马克思',3),
    ('外语',3),
    ('计算机',3);

insert into student(sname,gender,class_id) values
    ('乔丹','男',1),
    ('艾弗森','男',1),
    ('科比','男',2);

insert into score(student_id,course_id,score) values
    (1,1,60),
    (1,2,59),
    (1,3,58),
    (2,1,99),
    (2,2,99),
    (2,3,89),
    (3,1,59),
    (3,3,30);

insert into teach2cls(tid,cid) values
    (1,1),
    (1,2),
    (1,3),
    (1,5),
    (2,4),
    (2,6),
    (2,8),
    (2,9),
    (2,1),
    (2,5),
    (3,7),
    (3,1),
    (3,3),
    (3,5),
    (3,9);


    补充数据
insert into teacher(tname) values   
    ('赵六'),
    ('苗七');
insert into class_grade(gname) values 
    ('四年级');
insert into class(caption,grade_id) values  
    ('四年一班',4),
    ('四年二班',4),
    ('四年三班',4),
    ('四年四班',4);

insert into course(cname,teacher_id) values 
    ('线性代数',4);

insert into student(sname,gender,class_id) values  
    ('张一','女',3),
    ('詹姆斯','男',3),
    ('荷花','女',3),
    ('杜兰特','男',3),
    ('哈登','男',4),
    ('尼克','男',4),
    ('青青','女',4),
    ('阿里扎','男',4);

insert into score(student_id,course_id,score) values
    (3,4,60),
    (4,1,59),
    (4,2,100),
    (4,3,90),
    (4,4,80),
    (5,1,59),
    (5,2,33),
    (5,3,12),
    (5,4,88),
    (6,1,100),
    (6,2,60),
    (6,3,59),
    (6,4,100),
    (7,1,20),
    (7,2,36),
    (7,3,57),
    (7,4,60),
    (8,1,61),
    (8,2,59),
    (8,3,62),
    (8,4,59),
    (9,1,60),
    (9,2,61),
    (9,3,21);

insert into teach2cls(tid,cid) values
    (4,1),
    (4,2),
    (4,3),
    (4,4),
    (5,1),
    (5,2),
    (5,3),
    (5,4);

　　

二，操作表格内容

1、自行创建测试数据；

　　上面已经完成。

2、查询学生总人数；

select count(sid) from student;

　　

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

思路：获取所有生物课程的人（学号，成绩）--临时表
		  获取所有物理课程的人（学号，成绩）--临时表
		  根据学号连接两个临时表：学号，物理成绩，生物成绩
		  然后筛选及格的
	select  sid,sname
	from student
	where sid in(
	select score.student_id from score inner join course on score.course_id=course.cid
	where course.cname in('生物','物理') and score.score >=60
	group by score.student_id having count(course_id) = 2);

　　

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

思路：首先分析班级前三的情况，分为班级数相同的情况和班级数不同的情况
	如果班级数相同，那么只需要考虑在班级里面统计班级数量即可，
		然后在班级年级表中取出对应的年级数目
	如果班级数不相同，那么首先班级里面统计班级数量，
		然后在按照降序排列，取前三即可
		
	#包含班级数不相同的排名前三年级
	select class_grade.gname from class_grade inner join(
		select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)
		as t1 on class_grade.gid = t1.grade_id;
	#包含了班级数相同的排名前三年级
	select gname from class_grade where gid in (
		select grade_id from class group by grade_id having count(cid) in (

　　

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

create view t1 as 
	 select  student_id avg(score) as avg_score from score group by student_id;
    select sname,avg_score from t1 left join student on t1.student_id =student.sid 
     where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score = 
     (select min(t1.avg_score) from t1);

　　

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

思路：先在学生表和班级表对应一下
		然后在对应班级表中查找学生人数
	select t1.grade_id,count(t1.sid) as count_student from (
	 select student.sid ,class.grade_id from student,class 
	where student.class_id =class.cid) as t1 group by t1.grade_id;

　　

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

思路：学生表中有学生学号，姓名，性别，班级 成绩表中对应成绩，所以
		我们可以联立成绩表和学生表，并按学生id分类，直接查找即可。
	select score.student_id,student.sname,sum(score.course_id),avg(score.score)
		from score left join student on score.student_id = student.sid 
		group by score.student_id;

　　

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

思路：首先在成绩表中查找学生编号为2 的学生的最大最小成绩，学生id，课程id，
		   然后在课程表和学生表中找到对应的学生姓名和课程名称，
		   最后联立表格得出学生姓名，课程名称，分数
	select student.sname,course.cname,t1.score from (
	select student_id,course_id,score from score where student_id = 2 and score in((
	select max(score) from score where student_id = 2),
	(select min(score) from score where student_id = 2))) as t1 
	inner join student on t1.student_id = student.sid 
	inner join course on t1.course_id = course.cid;

　　

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

思路：首先在老师表中寻找姓李老师的id
		  然后在teach2cls中找到老师和班级的联系，并统计姓李老师所带的班级数
		  最后在老师表中查询老师id和姓名。
	select  teacher.tid as '姓李id', teacher.tname as '老师姓名' ,GROUP_CONCAT(teach2cls.cid) as '班级数' 
	from teacher left join teach2cls on teacher.tid = teach2cls.tid
	where teacher.tname like '李%' group by teacher.tid;

　　

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

思路：首先查询班级表中班级小于5的年级id号码
		  然后在年级表中查找对应班级表中的年级id即可
	select gid,gname from class_grade where gid in (
		select grade_id from  class group by grade_id having count(caption)<5
		);

　　

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

班级id班级名称年级年级级别
1 一年一班 一年级 低 
select
		class.cid as '班级id',
		class.caption as '班级名称',
		class_grade.gname as '年级',
	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 '年级级别'
    from  class
	left join class_grade on class.grade_id = class_grade.gid;

　　

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

首先找到张三老师的id，
	 然后联立成绩表和课程表，并在成绩表中查看选修张三老师课程数量大于2的学生id
	 最后在学生表中查找学生的学号，姓名。
	select sid,sname from student
	where sid in 
	(
		select score.student_id from score 
		left join course
		on score.course_id = course.cid
		where course.teacher_id in 
		(
			select tid from teacher
			where tname = '张三'
		)
		group by student_id
		having count(course.cid) >2
	);

　　

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

思路：先在course中按照老师的id进行分组，并统计代课大于2门的老师id的总数---临时表
			然后在teacher表中查找老师的id和姓名
	select tid,tname from teacher where tid in (
		select teacher_id from course group by teacher_id having count(cid)>2);

　　

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

思路：创建一个虚拟表，用于查找课程中的编号1和编号2课程
		然后在学生表中查找学生的学号和姓名
	select sid,sname from student where sid in (
		select distinct student_id from score where course_id in (1,2));

　　

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

思路：在班级表中设定高年级为五六年级，---虚拟表
		然后在teach2cls中找到老师和班级的联系 ---虚拟表
	    最后在老师表中查询老师id和姓名
	select tid,tname from teacher where tid not in (select tid from teach2cls
		where cid in (select cid from class where grade_id in (5,6)));

　　

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

思路：首先将张三老师的id从课程表中和老师表中对应起来，并找出他教的课程id ————虚拟表
		然后在成绩表中查找与上面表对应的课程id所对应的学生的id
		最后在学生表中查找学生的学号，姓名。
	select sid,sname from student where sid in (
		select student_id from score where course_id in (
		  select cid from course inner join teacher on teacher.tid = course.teacher_id 
		    where teacher.tname = '张三'));

　　

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

思路： 先在teac2cls中找到班级cid大于2的老师id（ｔｉｄ）－－－－虚拟表
			然后在老师表中找老师ｉｄ和姓名对应的ｉｄ
	select tid,tname from teacher where tid in (
	  select tid from teach2cls group by tid having count(cid)>2);

　　

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

思路：先在成绩表中查找课程2 的学生id和成绩，设为表1 ，
		  再查找课程编号为1的学生id和成绩，设为表二
		  最后在学生表中查找课程2比课程1的成绩低的学生的学号和姓名。
	select sid,sname from student where sid in (
        select t1.student_id from (
        select student_id, score from score where course_id = 2 group by student_id) as t1,
		select student_id, score from score where course_id = 1 group by student_id) as t2
		where t1.student_id = t2.student_id and t1.score < t2.score);

　　

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

思路：首先在老师-课程表中统计老师所带的课程数量，并按照老师id分类，并取一个
	    然后在老师表中查找对应老师id和姓名
	select tid,tname from teacher where tid =(
	 select tid from teach2cls  group by tid order by count(cid) desc limit 1);

　　

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

思路：先在成绩表中查找成绩小于６０分的学生ｉｄ
		然后学生表中查找学生id与成绩表中的学生id对应的学生学号，姓名
	select sid,sname from student where sid in (
	 select distinct student_id from score where score<60 );

　　

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

思路：首先分析题目意思，是没有学完所有课的同学
	  那么考虑学生应该是选完课程，没有考试，视为没有学完
	  所以首先查找学生选择的课程，在成绩表中是否有对应的成绩，如果有则学完，如果没有则没有学完
	  
	select sid ,sname from student where sid not in(
	select student_id from score group by student_id having count(course_id)=
	(select count(cid) from course)
	);

　　

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

思路：首先查找学号为1的学生的成绩id，
		  然后在成绩表中按照学号对应上面的成绩id
		  最后在学生表中查找学生的学号，姓名。
	select sid,sname from student where sid in (
	 select student_id from score where course_id in (
	 select course_id from score where student_id =1) group by student_id);

　　

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

思路：首先查找学号为1的学生所选的课程id，
	    然后再对应其他学生所选的课程id，
		最后在学生表中查找学生的学号，姓名。
		select sid,sname from student where sid in (
		 select student_id from score where course_id in (
		  select course_id from score where student_id = 1)
		  group by student_id) and sid !=1;

　　

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

思路：首先在成绩表中查询学生2的课程id，
	      然后进行筛选其他人的课程id和姓名id，不包含2号学生，
		  最后在学生表中查找学生的学号，姓名。
	select sid,sname from student where sid in (
	 select score.student_id from score ,(
	 select course_id from score where student_id = 2) as t1
	 where score.course_id = t1.course_id and score.student_id !=2
	 group by score.student_id 
	 having count(score.course_id) =(
	 select count(course_id) from score where student_id = 2));

　　

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

思路：首先在score表中找到对应张三老师课程，
		  然后删除即可
	delete from score where course_id in (
	 select course.cid from course,teacher where 
	  course.teacher_id =teacher.tid and teacher.tname = '张三');

　　

26、向score表中插入一些记录，这些记录要求符合以下条件：

        ①没有上过编号“2”课程的同学学号；

        ②插入“2”号课程的平均成绩；

思路：首先在score找出没有上过编号2课程的同学id，
		然后在成绩表中找到编号2的学生的所有成绩，取平均值
		最后插入数据即可。
	insert into score(student_id,course_id,score) 
	 select t1.sid,2,t2.avg from (
	 select sid from student where sid not in (
	 select student_id from score where course_id = 2)) as t1,
	 (select avg(score) as avg from score group by course_id having course_id =2) as t2;

　　

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

思路：注意平均成绩是由低到高desc
	     最重要的是查询各科成绩，
		 在课程表中找到成绩表中对应的课程id，然后在成绩表中查找对应的成绩
	select sc.student_id,
		(select score.score from score left join course on score.course_id = course.cid 
		  where course.cname = '语文' and score.student_id = sc.student_id) as Chinese,
		(select score.score from score left join course on score.course_id = course.cid 
		  where course.cname = '数学' and score.student_id = sc.student_id) as Math,
		(select score.score from score left join course on score.course_id = course.cid 
		  where course.cname = '外语' and score.student_id = sc.student_id) as English,
		count(sc.course_id),avg(sc.score)
    from score as sc group by sc.student_id order by avg(sc.score) asc;

　　

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

思路：直接在score中查找课程id，最高分数，最低分数
	select course_id ,max(score),min(score) from score
	 group by course_id;

　　

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

思路：平均成绩asc  及格率desc
		在score中找到学生的平均成绩，并求出及格率。
	select course_id,avg(score) as avg_score, 
	 sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent
	 from score group by course_id order by avg(score) asc,percent desc;

　　

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

思路：查找成绩表中的各科平均分数，并让分数对应课程表中的课程id
		然后把课程表中的课程id对应的老师课程表的老师id
		最后在老师表中查询老师id和姓名
	select t1.course_id,t1.avg_score,teacher.tname from course,teacher,
	(select course_id,avg(score) as avg_score from score group by course_id ) as t1 
	 where course.cid = t1.course_id and course.teacher_id = teacher.tid 
	 order by avg_score desc;

　　

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

select score.sid,score.student_id, score.course_id,score.score,
	 t1.first_score,t1.second_score,t1.third_score
	 from score inner join (
	 select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id 
	  order by score desc limit 0,1) as first_score,
	 (select score from score as s3 where s1.course_id = s3.course_id 
	  order by score desc limit 1,1) as second_score,
	 (select score from score as s4 where s1.course_id = s4.course_id 
	  order by score desc limit 2,1) as third_score
	from score as s1) as t1 on score.sid = t1.sid
	where score.score in (t1.first_score,t1.second_score,t1.third_score);

　　

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

思路：在成绩表中查找课程id，每门课的学生总数，
		最后在课程表中找到对应的课程名称
	select course.cname as '课程名称',t1.student_num as '学生数量' from course,
	 (select course_id,count(student_id) as student_num from score
	  group by course_id) as t1 where course.cid = t1.course_id;

　　

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

思路：在成绩表中查找课程id大于2们的学生id 
	   然后在学生表中查找对应的学生的学号和姓名
	select sid,sname from student where sid in (
	 select student_id from score group by student_id having count(course_id)>2);

　　

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

思路： 在学生表中按照性别分类 按照数量排序desc
	select gender,count(sid) as num from student
	group by gender order by num desc;

　　

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

思路：在学生表中查找姓张的学生名单
	select sid,sname,gender from student where sname like '张%';

　　

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

思路：直接在学生表中查看学生姓名相同的学生，并统计人数
	select sname,count(sname) from student group by sname having count(sname)>1;

　　

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

思路：在成绩表中按照课程id 查找学生的平均成绩
	select  course_id,avg(score) as avg_score from score 
	group by course_id order by avg_score,course_id desc;

　　

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

思路：先在course中查找课程为数学的课程id号码，
			然后在score中查找数学分数低于60的学生id,分数
			最后在学生表中查找对于id 的学生姓名
	select student.sname,score.score from score left join student 
	 on score.student_id = student.sid where score.course_id = (
     select cid from course where cname ='数学') and score.score <+60;

　　

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

思路：先创建一个课程编号为3 且成绩在80分以上的学生id表，在score中 --虚拟表
		  然后在student中查找对应id的学生姓名
	select sid,sname from student where sid in (
		select student_id from score where score> 80 and course_id = 3
		);

　　

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

思路：直接在成绩表中按照课程id排序，并统计学生id即可
	select course_id,count(student_id) from score group by course_id;

　　

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

思路：首先在老师表中寻找姓王老师的id，
	      然后对应课程表中对应的所教课程id，
		  然后在score中查找课程所对应的成绩和学生id
		  最后在学生表中查找学生的学号，姓名。
	select student.sname,score,score from score 
	 left join student on score.student_id = student.sid where course_id in (
	  select cid from course where teacher_id in (
	  select tid from teacher where tname = '王五'))
	  order by score.score desc limit 1;

　　

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

思路：联立课程表中的课程id和成绩表中的课程id，
	     然后查找各个课程对应的选修人数
	select course.cname,count(student_id) from score 
	 left join course on score.course_id = course.cid  group by course_id;

　　

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

思路：查找不同学生之间，课程不同成绩相同
			查找同一个学生，课程不同成绩相同
			的学生，课程号，学生成绩
	#1，不同学生之间
	select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
		from score as s1,score as s2
		where s1.course_id != s2.course_id and s1.score = s2.score;
	#2，同一个学生
	select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
		from score as s1,score as s2
		where s1.student_id = s2.student_id and 
			s1.course_id != s2.course_id and s1.score = s2.score;

　　

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

 select
                student.sid,
                student.sname,
                t2.course_id,
                t2.score,
                t2.first_score,
                t2.second_score
            from
                student
            inner join (                    
                select
                    score.student_id,
                    score.course_id,
                    score.score,
                    t1.first_score,
                    t1.second_score
                from
                    score
                inner join (
                    select
                        s1.sid,
                        (select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
                        (select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
                    from
                        score as s1
                ) as t1 on score.sid = t1.sid
                where
                    score.score in (          
                        t1.first_score,
                        t1.second_score
                    )
            ) as t2 on student.sid = t2.student_id;

　　

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

思路：在score表中直接查找大于2门课程的学生id
	select student_id from score  group by student_id having 
	 count(course_id)>=2;

　　

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

思路：在成绩表中按照课程id分组作为一个临时表
		   如果在课程表中，id没有在上面的临时表中，则就是没有学生选修
	select cid,cname from course where cid not in 
	(select course_id from score group by course_id);

　　

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

思路：在老师-课程表中按照老师分组作为一个临时表
		   如果在老师表中，id没有在这个临时表，则就是没有带过任何班级
	select tid tname  from teacher where tid not in (
	select tid from teach2cls group by tid);

　　

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

思路：首先，在成绩表中获取有两门课程成绩大于80分的学生id，---临时表
		  然后在成绩表中查找其id和平均成绩
		  或者在score表中直接查找大于2门课程的学生id和平均成绩
	select student_id,avg(score) from score
        where student_id in (
        select student_id from score where score > 80 group by student_id
        having count(course_id) > 2);

	
	select student_id,avg(score) from score  
	where score >80 group by student_id having count(course_id) >2;

　　

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

思路：查找成绩表中课程三而且分数小于60的学生学号，并按照分数降序排列desc
	select student_id,score from score where course_id = 3 and score<60 
		order by score desc;

　　

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

思路：首先在成绩表中，先把编号为2和课程为1的找到，
		  然后删除在成绩表中对应学生的成绩
	delete from score where sid = 
	(select sid from score where student_id = 2 and course_id=1
	);

　　

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

思路：在课程中首先找到物理，生物的id，
		  然后在成绩表中对应课程的id，此时找到了生物课和物理课的id
		  最后在学生表中，找到学生id和姓名
	select sid,sname from student where sid in(
		select student_id from score where course_id in (
		select cid from course where course.cname in('物理','生物'))
		group by student_id having count(course_id) = 2
	);

　　所有的题目代码*的意思：

  题目中打***    表示难，掌握不深
   题目中打*****  表示复制别人的


 补充：对难点的详细研究，见此博客：https://www.cnblogs.com/wj-1314/p/9219649.html

 

1、自行创建测试数据；
    见create_tabledata.txt
    insert_tabledata.txt
    
    
2、查询学生总人数；
    select count(sid) from student;

3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名；
    思路：获取所有生物课程的人（学号，成绩）--临时表
          获取所有物理课程的人（学号，成绩）--临时表
          根据学号连接两个临时表：学号，物理成绩，生物成绩
          然后筛选及格的
    select  sid,sname
    from student
    where sid in(
    select score.student_id from score inner join course on score.course_id=course.cid
    where course.cname in('生物','物理') and score.score >=60
    group by score.student_id having count(course_id) = 2);

***4、查询每个年级的班级数，取出班级数最多的前三个年级；
    思路：首先分析班级前三的情况，分为班级数相同的情况和班级数不同的情况
    如果班级数相同，那么只需要考虑在班级里面统计班级数量即可，
        然后在班级年级表中取出对应的年级数目
    如果班级数不相同，那么首先班级里面统计班级数量，
        然后在按照降序排列，取前三即可
        
    #包含班级数不相同的排名前三年级
    select class_grade.gname from class_grade inner join(
        select grade_id,count(id) as count_cid from class group by grade_id order by count_cid desc limit 3)
        as t1 on class_grade.gid = t1.grade_id;
    #包含了班级数相同的排名前三年级
    select gname from class_grade where gid in (
        select grade_id from class group by grade_id having count(cid) in (
        
*****5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
    create view t1 as 
     select  student_id avg(score) as avg_score from score group by student_id;
    select sname,avg_score from t1 left join student on t1.student_id =student.sid 
     where t1.avg_score = ( select max(t1.avg_score) from t1) or t1.avg_score = 
     (select min(t1.avg_score) from t1);     
    
6、查询每个年级的学生人数；
    思路：先在学生表和班级表对应一下
        然后在对应班级表中查找学生人数
    select t1.grade_id,count(t1.sid) as count_student from (
     select student.sid ,class.grade_id from student,class 
    where student.class_id =class.cid) as t1 group by t1.grade_id;

    
7、查询每位学生的学号，姓名，选课数，平均成绩；
    思路：学生表中有学生学号，姓名，性别，班级 成绩表中对应成绩，所以
        我们可以联立成绩表和学生表，并按学生id分类，直接查找即可。
    select score.student_id,student.sname,sum(score.course_id),avg(score.score)
        from score left join student on score.student_id = student.sid 
        group by score.student_id;
        
***8、查询学生编号为“2”的学生的姓名、该学生成绩最高的课程名、成绩最低的课程名及分数；
    思路：首先在成绩表中查找学生编号为2 的学生的最大最小成绩，学生id，课程id，
           然后在课程表和学生表中找到对应的学生姓名和课程名称，
           最后联立表格得出学生姓名，课程名称，分数
    select student.sname,course.cname,t1.score from (
    select student_id,course_id,score from score where student_id = 2 and score in((
    select max(score) from score where student_id = 2),
    (select min(score) from score where student_id = 2))) as t1 
    inner join student on t1.student_id = student.sid 
    inner join course on t1.course_id = course.cid;
    
9、查询姓“李”的老师的个数和所带班级数；
    思路：首先在老师表中寻找姓李老师的id
          然后在teach2cls中找到老师和班级的联系，并统计姓李老师所带的班级数
          最后在老师表中查询老师id和姓名。
    select  teacher.tid as '姓李id', teacher.tname as '老师姓名' ,GROUP_CONCAT(teach2cls.cid) as '班级数' 
    from teacher left join teach2cls on teacher.tid = teach2cls.tid
    where teacher.tname like '李%' group by teacher.tid;


10、查询班级数小于5的年级id和年级名；
    思路：首先查询班级表中班级小于5的年级id号码
          然后在年级表中查找对应班级表中的年级id即可
    select gid,gname from class_grade where gid in (
        select grade_id from  class group by grade_id having count(caption)<5
        );

*****11、查询班级信息，包括班级id、班级名称、年级、年级级别(12为低年级，34为中年级，56为高年级)，
    示例结果如下；
                班级id    班级名称         年级    年级级别
                    1      一年一班        一年级       低
    select
        class.cid as '班级id',
        class.caption as '班级名称',
        class_grade.gname as '年级',
    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 '年级级别'
    from  class
    left join class_grade on class.grade_id = class_grade.gid;

12、查询学过“张三”老师2门课以上的同学的学号、姓名；
     首先找到张三老师的id，
     然后联立成绩表和课程表，并在成绩表中查看选修张三老师课程数量大于2的学生id
     最后在学生表中查找学生的学号，姓名。
    select sid,sname from student
    where sid in 
    (
        select score.student_id from score 
        left join course
        on score.course_id = course.cid
        where course.teacher_id in 
        (
            select tid from teacher
            where tname = '张三'
        )
        group by student_id
        having count(course.cid) >2
    );
    
13、查询教授课程超过2门的老师的id和姓名；
    思路：先在course中按照老师的id进行分组，并统计代课大于2门的老师id的总数---临时表
            然后在teacher表中查找老师的id和姓名
    select tid,tname from teacher where tid in (
        select teacher_id from course group by teacher_id having count(cid)>2);
        
14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名；
    思路：创建一个虚拟表，用于查找课程中的编号1和编号2课程
        然后在学生表中查找学生的学号和姓名
    select sid,sname from student where sid in (
        select distinct student_id from score where course_id in (1,2));

15、查询没有带过高年级的老师id和姓名；
    思路：在班级表中设定高年级为五六年级，---虚拟表
        然后在teach2cls中找到老师和班级的联系 ---虚拟表
        最后在老师表中查询老师id和姓名
    select tid,tname from teacher where tid not in (select tid from teach2cls
        where cid in (select cid from class where grade_id in (5,6)));

16、查询学过“张三”老师所教的所有课的同学的学号、姓名；
    思路：首先将张三老师的id从课程表中和老师表中对应起来，并找出他教的课程id ————虚拟表
        然后在成绩表中查找与上面表对应的课程id所对应的学生的id
        最后在学生表中查找学生的学号，姓名。
    select sid,sname from student where sid in (
        select student_id from score where course_id in (
          select cid from course inner join teacher on teacher.tid = course.teacher_id 
            where teacher.tname = '张三'));

17、查询带过超过2个班级的老师的id和姓名；
    思路： 先在teac2cls中找到班级cid大于2的老师id（ｔｉｄ）－－－－虚拟表
            然后在老师表中找老师ｉｄ和姓名对应的ｉｄ
    select tid,tname from teacher where tid in (
      select tid from teach2cls group by tid having count(cid)>2);

18、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
    思路：先在成绩表中查找课程2 的学生id和成绩，设为表1 ，
          再查找课程编号为1的学生id和成绩，设为表二
          最后在学生表中查找课程2比课程1的成绩低的学生的学号和姓名。
    select sid,sname from student where sid in (
        select t1.student_id from (
        select student_id, score from score where course_id = 2 group by student_id) as t1,
        select student_id, score from score where course_id = 1 group by student_id) as t2
        where t1.student_id = t2.student_id and t1.score < t2.score);
19、查询所带班级数最多的老师id和姓名；
    思路：首先在老师-课程表中统计老师所带的课程数量，并按照老师id分类，并取一个
        然后在老师表中查找对应老师id和姓名
    select tid,tname from teacher where tid =(
     select tid from teach2cls  group by tid order by count(cid) desc limit 1);

20、查询有课程成绩小于60分的同学的学号、姓名；
    思路：先在成绩表中查找成绩小于６０分的学生ｉｄ
        然后学生表中查找学生id与成绩表中的学生id对应的学生学号，姓名
    select sid,sname from student where sid in (
     select distinct student_id from score where score<60 );

21、查询没有学全所有课的同学的学号、姓名；
    思路：首先分析题目意思，是没有学完所有课的同学
      那么考虑学生应该是选完课程，没有考试，视为没有学完
      所以首先查找学生选择的课程，在成绩表中是否有对应的成绩，如果有则学完，如果没有则没有学完
      
    select sid ,sname from student where sid not in(
    select student_id from score group by student_id having count(course_id)=
    (select count(cid) from course)
    );

22、查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
    思路：首先查找学号为1的学生的成绩id，
          然后在成绩表中按照学号对应上面的成绩id
          最后在学生表中查找学生的学号，姓名。
    select sid,sname from student where sid in (
     select student_id from score where course_id in (
     select course_id from score where student_id =1) group by student_id);

23、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名；
    思路：首先查找学号为1的学生所选的课程id，
        然后再对应其他学生所选的课程id，
        最后在学生表中查找学生的学号，姓名。
        select sid,sname from student where sid in (
         select student_id from score where course_id in (
          select course_id from score where student_id = 1)
          group by student_id) and sid !=1;
    
    
***24、查询和“2”号同学学习的课程完全相同的其他同学的学号和姓名；
    思路：首先在成绩表中查询学生2的课程id，
          然后进行筛选其他人的课程id和姓名id，不包含2号学生，
          最后在学生表中查找学生的学号，姓名。
    select sid,sname from student where sid in (
     select score.student_id from score ,(
     select course_id from score where student_id = 2) as t1
     where score.course_id = t1.course_id and score.student_id !=2
     group by score.student_id 
     having count(score.course_id) =(
     select count(course_id) from score where student_id = 2));
                
25、删除学习“张三”老师课的score表记录；
    思路：首先在score表中找到对应张三老师课程，
          然后删除即可
    delete from score where course_id in (
     select course.cid from course,teacher where 
      course.teacher_id =teacher.tid and teacher.tname = '张三');

26、向score表中插入一些记录，这些记录要求符合以下条件：
    ①没有上过编号“2”课程的同学学号；②插入“2”号课程的平均成绩；
    思路：首先在score找出没有上过编号2课程的同学id，
        然后在成绩表中找到编号2的学生的所有成绩，取平均值
        最后插入数据即可。
    insert into score(student_id,course_id,score) 
     select t1.sid,2,t2.avg from (
     select sid from student where sid not in (
     select student_id from score where course_id = 2)) as t1,
     (select avg(score) as avg from score group by course_id having course_id =2) as t2;

27、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
    按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
    思路：注意平均成绩是由低到高desc
         最重要的是查询各科成绩，
         在课程表中找到成绩表中对应的课程id，然后在成绩表中查找对应的成绩
    select sc.student_id,
        (select score.score from score left join course on score.course_id = course.cid 
          where course.cname = '语文' and score.student_id = sc.student_id) as Chinese,
        (select score.score from score left join course on score.course_id = course.cid 
          where course.cname = '数学' and score.student_id = sc.student_id) as Math,
        (select score.score from score left join course on score.course_id = course.cid 
          where course.cname = '外语' and score.student_id = sc.student_id) as English,
        count(sc.course_id),avg(sc.score)
    from score as sc group by sc.student_id order by avg(sc.score) asc;



28、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
    思路：直接在score中查找课程id，最高分数，最低分数
    select course_id ,max(score),min(score) from score
     group by course_id;

29、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
    思路：平均成绩asc  及格率desc
        在score中找到学生的平均成绩，并求出及格率。
    select course_id,avg(score) as avg_score, 
     sum(case when score.score > 60 then 1 else 0 end) / count(1) * 100 as percent
     from score group by course_id order by avg(score) asc,percent desc;

30、课程平均分从高到低显示（显示任课老师）；
    思路：查找成绩表中的各科平均分数，并让分数对应课程表中的课程id
        然后把课程表中的课程id对应的老师课程表的老师id
        最后在老师表中查询老师id和姓名
    select t1.course_id,t1.avg_score,teacher.tname from course,teacher,
    (select course_id,avg(score) as avg_score from score group by course_id ) as t1 
     where course.cid = t1.course_id and course.teacher_id = teacher.tid 
     order by avg_score desc; 

     
*****31、查询各科成绩前三名的记录(不考虑成绩并列情况）
    
    select score.sid,score.student_id, score.course_id,score.score,
     t1.first_score,t1.second_score,t1.third_score
     from score inner join (
     select s1.sid,(select score from score as s2 where s1.course_id = s2.course_id 
      order by score desc limit 0,1) as first_score,
     (select score from score as s3 where s1.course_id = s3.course_id 
      order by score desc limit 1,1) as second_score,
     (select score from score as s4 where s1.course_id = s4.course_id 
      order by score desc limit 2,1) as third_score
    from score as s1) as t1 on score.sid = t1.sid
    where score.score in (t1.first_score,t1.second_score,t1.third_score);
                
                

32、查询每门课程被选修的学生数；
    思路：在成绩表中查找课程id，每门课的学生总数，
        最后在课程表中找到对应的课程名称
    select course.cname as '课程名称',t1.student_num as '学生数量' from course,
     (select course_id,count(student_id) as student_num from score
      group by course_id) as t1 where course.cid = t1.course_id;

33、查询选修了2门以上课程的全部学生的学号和姓名；
    思路：在成绩表中查找课程id大于2们的学生id 
       然后在学生表中查找对应的学生的学号和姓名
    select sid,sname from student where sid in (
     select student_id from score group by student_id having count(course_id)>2);

34、查询男生、女生的人数，按倒序排列；
    思路： 在学生表中按照性别分类 按照数量排序desc
    select gender,count(sid) as num from student
    group by gender order by num desc;

35、查询姓“张”的学生名单；
    思路：在学生表中查找姓张的学生名单
    select sid,sname,gender from student where sname like '张%';

36、查询同名同姓学生名单，并统计同名人数；
    思路：直接在学生表中查看学生姓名相同的学生，并统计人数
    select sname,count(sname) from student group by sname having count(sname)>1;

37、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
    思路：在成绩表中按照课程id 查找学生的平均成绩
    select  course_id,avg(score) as avg_score from score 
    group by course_id order by avg_score,course_id desc;
    
38、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
    思路：先在course中查找课程为数学的课程id号码，
            然后在score中查找数学分数低于60的学生id,分数
            最后在学生表中查找对于id 的学生姓名
    select student.sname,score.score from score left join student 
     on score.student_id = student.sid where score.course_id = (
     select cid from course where cname ='数学') and score.score <+60;     

39、查询课程编号为“3”且课程成绩在80分以上的学生的学号和姓名；
    思路：先创建一个课程编号为3 且成绩在80分以上的学生id表，在score中 --虚拟表
          然后在student中查找对应id的学生姓名
    select sid,sname from student where sid in (
        select student_id from score where score> 80 and course_id = 3
        );
        
40、求选修了课程的学生人数
    思路：直接在成绩表中按照课程id排序，并统计学生id即可
    select course_id,count(student_id) from score group by course_id;
    
41、查询选修“王五”老师所授课程的学生中，成绩最高和最低的学生姓名及其成绩；
    思路：首先在老师表中寻找姓王老师的id，
          然后对应课程表中对应的所教课程id，
          然后在score中查找课程所对应的成绩和学生id
          最后在学生表中查找学生的学号，姓名。
    select student.sname,score,score from score 
     left join student on score.student_id = student.sid where course_id in (
      select cid from course where teacher_id in (
      select tid from teacher where tname = '王五'))
      order by score.score desc limit 1;

42、查询各个课程及相应的选修人数；
    思路：联立课程表中的课程id和成绩表中的课程id，
         然后查找各个课程对应的选修人数
    select course.cname,count(student_id) from score 
     left join course on score.course_id = course.cid  group by course_id;

43、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
    思路：查找不同学生之间，课程不同成绩相同
            查找同一个学生，课程不同成绩相同
            的学生，课程号，学生成绩
    #1，不同学生之间
    select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
        from score as s1,score as s2
        where s1.course_id != s2.course_id and s1.score = s2.score;
    #2，同一个学生
    select distinct s1.student_id,s2.student_id,s1.course_id,s2.course_id,s1.score,s2.score
        from score as s1,score as s2
        where s1.student_id = s2.student_id and 
            s1.course_id != s2.course_id and s1.score = s2.score;

*****44、查询每门课程成绩最好的前两名学生id和姓名；
     select
                student.sid,
                student.sname,
                t2.course_id,
                t2.score,
                t2.first_score,
                t2.second_score
            from
                student
            inner join (                    
                select
                    score.student_id,
                    score.course_id,
                    score.score,
                    t1.first_score,
                    t1.second_score
                from
                    score
                inner join (
                    select
                        s1.sid,
                        (select s2.score from score as s2 where s1.course_id = s2.course_id order by s2.score desc limit 0,1) as first_score,
                        (select s3.score from score as s3 where s1.course_id = s3.course_id order by s3.score desc limit 1,1) as second_score
                    from
                        score as s1
                ) as t1 on score.sid = t1.sid
                where
                    score.score in (          
                        t1.first_score,
                        t1.second_score
                    )
            ) as t2 on student.sid = t2.student_id;

    
45、检索至少选修两门课程的学生学号；
    思路：在score表中直接查找大于2门课程的学生id
    select student_id from score  group by student_id having 
     count(course_id)>=2; 

46、查询没有学生选修的课程的课程号和课程名；
    思路：在成绩表中按照课程id分组作为一个临时表
           如果在课程表中，id没有在上面的临时表中，则就是没有学生选修
    select cid,cname from course where cid not in 
    (select course_id from score group by course_id);
    
47、查询没带过任何班级的老师id和姓名；
    思路：在老师-课程表中按照老师分组作为一个临时表
           如果在老师表中，id没有在这个临时表，则就是没有带过任何班级
    select tid tname  from teacher where tid not in (
    select tid from teach2cls group by tid);

48、查询有两门以上课程超过80分的学生id及其平均成绩；
    思路：首先，在成绩表中获取有两门课程成绩大于80分的学生id，---临时表
          然后在成绩表中查找其id和平均成绩
          或者在score表中直接查找大于2门课程的学生id和平均成绩
    select student_id,avg(score) from score
        where student_id in (
        select student_id from score where score > 80 group by student_id
        having count(course_id) > 2);

    
    select student_id,avg(score) from score  
    where score >80 group by student_id having count(course_id) >2;
    

49、检索“3”课程分数小于60，按分数降序排列的同学学号；
    思路：查找成绩表中课程三而且分数小于60的学生学号，并按照分数降序排列desc
    select student_id,score from score where course_id = 3 and score<60 
        order by score desc;
    
50、删除编号为“2”的同学的“1”课程的成绩；
    思路：首先在成绩表中，先把编号为2和课程为1的找到，
          然后删除在成绩表中对应学生的成绩
    delete from score where sid = 
    (select sid from score where student_id = 2 and course_id=1
    );

51、查询同时选修了物理课和生物课的学生id和姓名；
    思路：在课程中首先找到物理，生物的id，
          然后在成绩表中对应课程的id，此时找到了生物课和物理课的id
          最后在学生表中，找到学生id和姓名
    select sid,sname from student where sid in(
        select student_id from score where course_id in (
        select cid from course where course.cname in('物理','生物'))
        group by student_id having count(course_id) = 2
    );
View Code

 

posted @ 2018-06-22 16:28  战争热诚  阅读(1378)  评论(0)  编辑  收藏  举报