MySQL进阶题库题解

_{（为方便阅读，已将建表添加数据等数据准备内容置于文章末尾）}

题组 数据准备完成

建表，添加数据，各表连接后效果：

题目

案例【1.1】查询学员列表，显示学员姓名，电话，班级名称；
案例【1.2】查询学员成绩列表，显示班级类型名称，班级名称，学员姓名，课程名称，考试分数；
案例【1.3】查询学员成绩列表，显示学员姓名，考试分数，如果该学员没有考试记录也要列出姓名；
案例【1.4】统计每门课程的平均分；
案例【1.5】上例的基础上显示课程名称；
案例【1.6】继续在上例的基础上统计平均分大于60且小于80的课程，并按平均分由高到低排序；
案例【1.7】查询唐浩然的同班同学；
案例【1.8】查询唐浩然的同班同学并且学历一样的同学；
案例【1.9】查询每门科目最高分数；
案例【2.0】分数在90分以上的同学；
案例【2.1】查询曾经缺考的同学；
案例【2.2】查询成绩排名表的第二页（按每页5条数据）；

解题 _{(推荐解题代码或思路会进行标注)}

对查询语句或多表联查不熟练可对照博主另文对照阅读
_{点击以下文字即可：}

• 查询语句
• 多表联查

案例【1.1】查询学员列表，显示学员姓名，电话，班级名称；
select s.name 学员姓名, s.tele 电话, cla.name 班级名称 from t_student s,t_class cla where cla.id=s.classid;
select s.name 学员姓名, s.tele 电话, cla.name 班级名称 from t_student s inner join t_class cla on cla.id=s.classid;

双表联查更建议使用隐式内连接方式，减少代码量

案例【1.2】查询学员成绩列表，显示班级类型名称，班级名称，学员姓名，课程名称，考试分数；
select t.name 班级类型名称,cla.name 班级名称,s.name 学员姓名,cou.name 课程名称,m.score 考试分数 from t_class_type t
inner join t_class cla on t.id=cla.type
inner join t_student s on cla.id=s.classid
inner join t_mark m on s.id=m.studentid
inner join t_course cou on cou.id=m.courseid;

案例【1.3】查询学员成绩列表，显示学员姓名，考试分数，如果该学员没有考试记录也要列出姓名；
select s.name 学员姓名,m.score 考试分数 from t_student s,t_mark m where s.id=m.studentid;

案例【1.4】统计每门课程的平均分；
案例【1.5】上例的基础上显示课程名称；
select cou.name 课程名,avg(m.score) 平均分 from t_mark m,t_course cou where cou.id=m.courseid group by cou.id;相对平均分
select cou.name 课程名,sum(m.score)/count(*) 平均分 from t_mark m,t_course cou where cou.id=m.courseid group by cou.id;绝对平均分

由于题目数据原因此处两种解法结果相同

• 相对平均分：计算时忽略缺考人员
• 绝对平均分：计算时将缺考人员也算上

案例【1.6】继续在上例的基础上统计平均分大于60且小于80的课程，并按平均分由高到低排序；
select cou.name 课程名,avg(m.score) 平均分 from t_mark m,t_course cou where cou.id=m.courseid group by cou.id having avg(m.score) between 60 and 80 order by avg(m.score) desc;

案例【1.7】查询唐浩然的同班同学；

select s.name 学员姓名,cla.name 班级名称 from t_class cla,t_student s where cla.id=s.classid and s.name != ‘唐浩然’ and s.classid!=(select classid from t_student where name =‘唐浩然’);
select s.name 学员姓名,cla.name 班级名称 from t_class cla inner join t_student s on cla.id=s.classid where s.name != ‘唐浩然’ and s.classid!=(select classid from t_student where name =‘唐浩然’);
_{顺带查询班级名称为了方便读者验证正确性}

尽量避免一条语句使用太多and进行判断语句连接，降低人为识别难度

案例【1.8】查询唐浩然的同班同学并且学历一样的同学；
select s.name 学员姓名,cla.name 班级名称,s.edu 学历 from t_class cla inner join t_student s on cla.id=s.classid where s.name != ‘唐浩然’ and s.classid!=(select classid from t_student where name =‘唐浩然’) and s.edu=(select edu from t_student where name =‘唐浩然’);
_{顺带查询学历同样是为了方便读者验证正确性}

案例【1.9】查询每门科目最高分数；
select cou.name 学科名,max(m.score) 学科最高分 from t_course cou,t_mark m where cou.id=m.courseid group by cou.id;

案例【2.0】分数在90分以上的同学；
select s.name 学员姓名,cou.name 学科名,m.score 成绩 from t_student s
inner join t_mark m on s.id=m.studentid
inner join t_course cou on cou.id=m.courseid
where m.score>90 order by s.name;
_{排序为了方便查看数据}

案例【2.1】查询曾经缺考的同学；
select s.name 学员姓名,count(m.score) 考试科数 from t_student s
inner join t_mark m on s.id=m.studentid group by s.name having count(m.score) < 9;

先写出查询条件，最后补充条件 having count(m.score) < 9
即having条件的添加是为了进行数据的二次处理（查询）

案例【2.2】查询成绩排名表的第二页（按每页5条数据）；
select * from t_mark limit 5,5;

数据准备

题组 建表

#班级类型
CREATE TABLE T_CLASS_TYPE(
 `ID` int  PRIMARY KEY, 
 `NAME` VARCHAR(30) NOT NULL UNIQUE 
);
#班级
CREATE  TABLE T_CLASS
(`ID` int  PRIMARY KEY, 
 `NAME` VARCHAR(30) NOT NULL UNIQUE,
 `OPENDATE` DATE,
 `TYPE` int, 	#班级类型                      
 `STATE` CHAR(1) NOT NULL CHECK(STATE IN ('0','1','2')) #开班状态 0未开班  1已开班  2已毕业
);
#学生
CREATE TABLE T_STUDENT
( `ID` int   PRIMARY KEY, 
  `NAME` VARCHAR(30) NOT NULL, 
  `SEX` int(1) CHECK(SEX IN (0,1)),
  `EDU` VARCHAR(30),   #学历
  `TELE` VARCHAR(30), 
  `CARDNO` VARCHAR(18) UNIQUE CHECK(length(CARDNO) =15 or length(CARDNO) =18),#身份证号
  `CLASSID` int    #所属班级
);
#课程
CREATE TABLE T_COURSE 
(
  `ID` int   PRIMARY KEY, 
  `NAME` VARCHAR(50),
  `TYPE` int   #所属的班级类型  比如mysql这门课程属于基础班
);
#成绩
CREATE TABLE T_MARK 
(
  `ID` int PRIMARY KEY, 
  `COURSEID` int,		#课程编号
  `STUDENTID` int,		#学生编号
  `SCORE` int			#分数
);
#添加外键约束
alter table t_class_type add foreign key (id) references t_class(`type`);
alter table t_student add foreign key (classid) references t_class(id);
alter table t_mark add foreign key (studentid) references t_student(id);
alter table t_mark add foreign key (courseid) references t_course(id);

题组 数据添加

insert into T_CLASS_TYPE VALUES(1,'基础班');
insert into T_CLASS_TYPE VALUES(2,'就业班');

insert into T_COURSE VALUES(1,'java编程基础',1);
insert into T_COURSE VALUES(2,'面向对象',1);
insert into T_COURSE VALUES(3,'javaAPI',1);
insert into T_COURSE VALUES(4,'综合练习-管家婆',1);

insert into T_COURSE VALUES(5,'javaWEB开发',2);
insert into T_COURSE VALUES(6,'ORACLE',2);
insert into T_COURSE VALUES(7,'Hibernate',2);
insert into T_COURSE VALUES(8,'Struts2',2);
insert into T_COURSE VALUES(9,'Spring',2);


insert into T_CLASS VALUES (1,'javaEE基础30期',curdate(),1,'1' );
insert into T_CLASS VALUES (2,'javaEE基础31期',curdate(),1,'1' );
insert into T_CLASS VALUES (3,'javaEE基础32期',curdate(),1,'1' );
insert into T_CLASS VALUES (4,'javaEE基础33期',curdate(),1,'1' );
insert into T_CLASS VALUES (5,'javaEE就业200期',curdate(),2,'1' );
insert into T_CLASS VALUES (6,'javaEE就业201期',curdate(),2,'1' );
insert into T_CLASS VALUES (7,'javaEE就业202期',curdate(),2,'1' );
insert into T_CLASS VALUES (8,'javaEE就业203期',curdate(),2,'1' );



insert into T_STUDENT VALUES(1,'能好宇',1,'大专','13071111107','510501197509026752',2 );
insert into T_STUDENT VALUES(2,'酆哲先',1,'本科','18610205888','441427198501198395',1 );
insert into T_STUDENT VALUES(3,'鲍辰淋',0,'本科','15101060107','510726198511304101',2 );
insert into T_STUDENT VALUES(4,'祝又青',0,'本科','13901122211','371427198410299264',1 );
insert into T_STUDENT VALUES(5,'江夜梦',0,'本科','13801922414','620524199012293483',2 );
insert into T_STUDENT VALUES(6,'能水凡',0,'本科','15010221145','130202198803319082',1 );
insert into T_STUDENT VALUES(7,'唐浩然',1,'本科',null,'522634199002252377',2 );
insert into T_STUDENT VALUES(8,'严安青',0,'本科',null,'130107197903287403',2 );
insert into T_STUDENT VALUES(9,'苍涛荣',0,'本科','','370102198411092201',1 );
insert into T_STUDENT VALUES(10,'郎山彤',0,'大专','','210321199206285266',2 );
insert into T_STUDENT VALUES(11,'魏含玉',0,'大专','','210124198503306784',1 );


-- 成绩数据

insert into T_MARK VALUES(1,1,1,80 );
insert into T_MARK VALUES(2,2,1,83 );
insert into T_MARK VALUES(3,3,1,99 );
insert into T_MARK VALUES(4,4,1,24 );
insert into T_MARK VALUES(5,5,1,64 );
insert into T_MARK VALUES(6,6,1,88 );
insert into T_MARK VALUES(7,7,1,82 );
insert into T_MARK VALUES(8,8,1,87 );
insert into T_MARK VALUES(9,9,1,24 );


insert into T_MARK VALUES(10,1,2,82 );
insert into T_MARK VALUES(11,2,2,85 );
insert into T_MARK VALUES(12,3,2,77 );
insert into T_MARK VALUES(13,4,2,55 );
insert into T_MARK VALUES(14,5,2,63 );
insert into T_MARK VALUES(15,6,2,53 );
insert into T_MARK VALUES(16,7,2,88 );
insert into T_MARK VALUES(17,8,2,81 );
insert into T_MARK VALUES(18,9,2,34 );


insert into T_MARK VALUES(19,1,3,70 );
insert into T_MARK VALUES(20,2,3,23 );
insert into T_MARK VALUES(21,3,3,49 );
insert into T_MARK VALUES(22,4,3,64 );
insert into T_MARK VALUES(23,5,3,14 );
insert into T_MARK VALUES(24,6,3,48 );
insert into T_MARK VALUES(25,7,3,72 );
insert into T_MARK VALUES(26,8,3,27 );
insert into T_MARK VALUES(27,9,3,84 );

insert into T_MARK VALUES(28,1,4,77 );
insert into T_MARK VALUES(29,2,4,22 );
insert into T_MARK VALUES(30,3,4,95 );
insert into T_MARK VALUES(31,4,4,44 );
insert into T_MARK VALUES(32,5,4,24 );
insert into T_MARK VALUES(33,6,4,88 );
insert into T_MARK VALUES(34,7,4,11 );
insert into T_MARK VALUES(35,8,4,37 );
insert into T_MARK VALUES(36,9,4,44 );


insert into T_MARK VALUES(37,1,5,30 );
insert into T_MARK VALUES(38,2,5,63 );
insert into T_MARK VALUES(39,3,5,95 );
insert into T_MARK VALUES(40,4,5,14 );
insert into T_MARK VALUES(41,5,5,64 );
insert into T_MARK VALUES(42,6,5,88 );
insert into T_MARK VALUES(43,7,5,22 );
insert into T_MARK VALUES(44,8,5,47 );
insert into T_MARK VALUES(45,9,5,74 );

insert into T_MARK VALUES(46,1,6,80 );
insert into T_MARK VALUES(47,2,6,13 );
insert into T_MARK VALUES(48,3,6,59 );
insert into T_MARK VALUES(49,4,6,74 );
insert into T_MARK VALUES(50,5,6,94 );
insert into T_MARK VALUES(51,6,6,28 );
insert into T_MARK VALUES(52,7,6,52 );
insert into T_MARK VALUES(53,8,6,87 );
insert into T_MARK VALUES(54,9,6,54 );


insert into T_MARK VALUES(55,1,7,20 );
insert into T_MARK VALUES(56,2,7,81 );
insert into T_MARK VALUES(57,3,7,92 );
insert into T_MARK VALUES(58,4,7,44 );
insert into T_MARK VALUES(59,5,7,64 );
insert into T_MARK VALUES(60,6,7,38 );
insert into T_MARK VALUES(61,7,7,86 );
insert into T_MARK VALUES(62,8,7,57 );
insert into T_MARK VALUES(63,9,7,24 );

insert into T_MARK VALUES(64,1,8,40 );
insert into T_MARK VALUES(65,2,8,23 );
insert into T_MARK VALUES(66,3,8,99 );
insert into T_MARK VALUES(67,4,8,34 );
insert into T_MARK VALUES(68,5,8,54 );
insert into T_MARK VALUES(69,6,8,78 );
insert into T_MARK VALUES(70,7,8,82 );
insert into T_MARK VALUES(71,8,8,17 );

insert into T_MARK VALUES(73,1,9,70 );
insert into T_MARK VALUES(74,2,9,23 );
insert into T_MARK VALUES(75,3,9,59 );
insert into T_MARK VALUES(76,4,9,24 );
insert into T_MARK VALUES(77,5,9,34 );
insert into T_MARK VALUES(78,6,9,78 );
insert into T_MARK VALUES(79,7,9,92 );
insert into T_MARK VALUES(80,8,9,83 );