MySQL之学生成绩表查询语句解析

立个flag,重新做一栏关于STL和算法的博客

• STL+简单算法

言归正传 , 上文讲了MySQL语句的嵌套与连接 , 下面分析分析嵌套和内连接的作业
准备工作先建表:

* 插入
 * create table student(Code int,Name char(10),College char(10));
 *  insert into student values('101','李二','数学');
 *  insert into student values('102','张三','数学');
 *  insert into student values('103','李四','计算机');
 *  insert into student values('104','王五','计算机');
 *  insert into student values('105','薛六','软件');
 *  insert into student values('106','赵七','计算机');
 * 	 insert into scores values('1','101','线性代数','68');
 * insert into scores values('2','103','离散数学','78');
 * insert into scores values('3','101','高等数学','55');
 *  insert into scores values('4','104','离散数学','98');
 *  insert into scores values('5','102','离散数学','45');
 *  insert into scores values('6','105','离散数学','59');
 *  insert into scores values('7','103','高等数学','83');
 *   insert into scores values('8','101','离散数学','60');
 *   insert into scores values('9','106','高等数学','68');

分析 : 表一共两张,成绩和学生 ,考虑到Code字段和stuCode字段意义相同,所以采用内连接以以上两字段作为条件连接

(1)查询【高等数学】成绩在60分以上的学生姓名 :

代码示范与分析:

select Name from student stu inner join scores sco 
on stu.Code =sco.stuCode //内连接
where score>60 and Course='高等数学' //约束条件: >60 && 高数  
group by Name ; //要求输出学生姓名 ,就按名字分组

结果:

查询【计算机】院的学生成绩信息

 select Name,Score,Course 
 from student stu inner join scores sco 
 on stu.Code =sco.stuCode //内连接 
 where College='计算机' ; //约束条件

结果:

(3)查询【李二】的各科成绩信息

select Name ,Score,Course 
from student stu inner join scores sco on stu.Code =sco.stuCode 
where Name='李二' ;

结果:

(4)查询低于60分的学生姓名、科目及院系

 select Name,College,Course 
 from student stu inner join scores sco on stu.Code =sco.stuCode 
 where Score<60 ;

结果:

(5)查询【离散数学】最低分的学生姓名和所在院系

select Name,College ,MIN(Score)from 
(select * from student stu inner join scores sco 
on stu.Code=sco.stuCode )tmp where Course ='离散数学'group by Course;

结果

分析:①嵌套查询必须给出子表的别名
②使用MIN(),…等聚合函数时需要对相关字段进行分组,如上例所示

(6) 统计各个科目下的最低分出现在哪个院系，能否得到对应学生的姓名

×

select College ,Name,MIN(Score) from student stu inner join scores sco on stu.Code=sco.stuCode where Course ='离散数学' group by College;