常用的sql查询语句-CSDN博客

本文链接：https://blog.csdn.net/memories5977/article/details/49787407

在成绩管理系统中用到的sql语句使用范例

分页查询
多表联合查询
使用聚合函数对表处理
项目中用到的数据库表的ER模型如下图所示：

数据库ER模型

首先，多表联合查询在显示学生成绩列表时需要使用，成绩列表需包含学生编号、学生姓名、学生班级、所修科目及对应的成绩，这几个信息都应包括在内。但这几个信息来自三张表的不同字段，为实现一起显示，可利用多表联合查询。
可以看出，学生姓名在学生表中，班级名称在班级表中，课程名称在课程表中，成绩在成绩表中，四者通过学生编号、班级编号、课程编号进行关联。
先说一下sql92语法的实现方式：

select g.student_id, s.student_name, cls.class_name, c.course_name, g.grade
from t_grade g,t_student s,t_class cls,t_course c
where g.student_id = s.student_id and s.class_id = cls.class_id and g.course_id = c.course_id

sql92语法的中，条件写在最后面形如：select…from…where…。先写字段，再写字段所属的表，最后写连接条件。
sql99语法实现如下所示：

select g.student_id, s.student_name, cls.class_name, c.course_name, g.grade
from t_grade g join t_student s on g.student_id = s.student_id 
join t_class cls on s.class_id = cls.class_id
join t_course c on g.course_id = c.course_id

sql99 采用join…on…的语法格式，select 字段… from 字段所属的表…join 另一个表 on 连接条件

其次是分页查询，当数据库中的信息量很大时，需要用到分页查询，分页查询在数据库端的形式为查询数据库中某一范围的数据信息，具体语句如下：

select B.* from
(
    select ROW_NUMBER() over(order by STUDENT_ID) as rn,A.* from
    (
    select g.student_id, s.student_name, cls.class_name, c.course_name, g.grade
    from t_grade g join t_student s on g.student_id = s.student_id 
    join t_class cls on s.class_id = cls.class_id
    join t_course c on g.course_id = c.course_id 
    ) A
) B where rn > 1 and rn <3

采用两层嵌套的形式，最里层是所有学生的成绩信息，首先加入ROW_NUMBER()，对每条数据信息编号，ROW_NUMBER()的使用格式必须为ROW_NUMBER() over(order by 字段)，即按照特定字段排序后编号。加入编号后，表中就增加了一个rn字段，该字段便是分页的依据，最后，将包括rn在内的所有字段作为一张表，并按照rn的取值范围进行条件查询。

比如，表内共有5000条数据，要分为50页，则每页有100条数据，要查询第2页的数据，则条件为rn>10 and rn<=20。

最后是聚合函数的使用，查询每个学生的各科总成绩需要用到SUM()函数，具体实现为：

select s.STUDENT_ID,s.STUDENT_NAME,c.CLASS_NAME,SUM(g.GRADE) as total_grade
from T_STUDENT s join T_CLASS c on s.CLASS_ID = c.CLASS_ID
join T_GRADE g on s.STUDENT_ID = g.STUDENT_ID
group by s.STUDENT_ID,s.STUDENT_NAME,c.CLASS_NAME

聚合函数前的所有字段都必须包含在group by 子句中，group by 即按照特定的字段分组，字段值相等的放在一起。

聚合函数MAX()的使用：取得各科成绩的最高分，以及取得最高分的学生姓名和所属班级：

select s.STUDENT_ID,s.STUDENT_NAME,cls.CLASS_NAME,c.COURSE_NAME,g.GRADE
from T_GRADE g join T_STUDENT s on g.STUDENT_ID = s.STUDENT_ID
join T_CLASS cls on cls.CLASS_ID = s.CLASS_ID
join T_COURSE c on c.COURSE_ID = g.COURSE_ID
where g.GRADE in(select MAX(gg.GRADE) from T_GRADE gg where gg.COURSE_ID = c.COURSE_ID)

先将所有学生的成绩信息查出来，最后在where子句中写查询条件，由于是查询每科的最高分，故查询条件中选取课程代码与上表中的课程表的课程代码进行关联。即在课程代码相等的成绩信息中选取一个最大值。

查询总成绩前三名：

select * from
(
    select  ROW_NUMBER() over(order by total_grade desc) rn,A.* from
    (
        select s.STUDENT_ID,s.STUDENT_NAME,c.CLASS_NAME,SUM(g.GRADE) as total_grade
        from T_STUDENT s join T_CLASS c on s.CLASS_ID = c.CLASS_ID
        join T_GRADE g on s.STUDENT_ID = g.STUDENT_ID
        group by s.STUDENT_ID,s.STUDENT_NAME,c.CLASS_NAME
    ) A
) B where rn<4