学生成绩数据库复习：

学生表S(SID,SNAME,SAGE,SSEX)

课程表C(CID,CNAME,TID)

教师表T(TID,TNAME)

成绩表SC(SID,CID,SCORE)

1、熟悉查询语句模板：

SELECT  查询属性

FROM   表名称

WHERE  条件

GROUP BY  分组列名称

HAVING  条件聚合函数

ORDER BY  排序列名称

LIMIT  限制查询数目条数

 

2、查询01课程成绩比02课程成绩高的学生学号：

思路：

查询01课程的成绩：SELECT SID,SCORE FROM SC WHERE ID=01

查询02课程的成绩: SELECT SID,SCORE FROM SC WHERE ID=02

最终：

SELECT SID FROM (SELECT SID,SCORE FROM SC WHERE ID=01

) A,( SELECT SID,SCORE FROM SC WHERE ID=02) B  WHERE A.SCORE>B.SCORE AND A.SID=B.SID

 

3、查询平均成绩大于60分的学生编号和姓名：

思路：

查询平均成绩大于60的学生编号:SELECT SID FROM SC GROUP BY SID HAVING AVG(SCORE)>60; 

最终：SELECT SID,SNAME FROM S WHERE SID IN (SELECT SID FROM SC GROUP BY(SID) HAVING AVG(SCORE)>60;)

 

4、查询所有同学的学号，姓名，选课数，总成绩

思路：根据表关系，查询内容只需要在S,SC表内查询

SELECT SID,SNAME,COUNT(SC.CID),SUM(SC.SCORE) FROM S LEFT JOIN SC ON S.SID=SC.SID GROUP BY SID,SNAME;

 

5、查询没学过JOJO老师科的同学的学号和姓名

思路：查询学过JOJO老师的课程同学和姓名

根据表关系，查询内容有教师表，成绩表，学生表  (通过老师表与课程表关联SC.CID=C.CID，老师表与学生表关联SC.SID=S.SID)

查询学习过JOJO老师所教课程学生的编号：

SELECT DISTINCT(SID)  FROM S,SC,C WHERE SC.CID=C.CID AND SC.SID=S.SID AND T.TNAME=”JOJO”;

最终：

SELECT SID,SNAME FROM S WHERE S.SID NOT IN (SELECT DISTINCT(SID)  FROM S,SC,C WHERE SC.CID=C.CID AND SC.SID=S.SID AND T.TNAME=”JOJO”;)

6、统计各科成绩，各个分数段的人数：课程编号，课程名称

思路：统计各个分数段的个数

根据表关系，查询内容与成绩表与学生表关联

COUNT(CASE WHEN SCORE<100 AND SCORE>80 THEN SCORE END) ‘[80,100]’

COUNT(CASE WHEN SCORE<80 AND SCORE>60 THEN SCORE END) ‘[60,80]’

COUNT(CASE WHEN SCORE<60 THEN SCORE END) ‘[<60]’

最终：

SELECT C.CID,C.CNAME 

COUNT(CASE WHEN SC.SCORE<100 AND SC.SCORE>80 THEN SC.SCORE END) ‘[80,100]’

COUNT(CASE WHEN SC.SCORE<80 AND SC.SCORE>60 THEN SC.SCORE END) ‘[60,80]’

COUNT(CASE WHEN SC.SCORE<60 THEN SC.SCORE END) ‘[<60]’

FROM C,SC WHERE SC.CID=C.CID GROUP BY C.CID,C.CNAME;

 

7、查询姓‘张’的学生名单

SELECT * FROM S WHERE S.SNAME LIKE “张%”;

 

8、查询同名同姓的学生，并统计个数

SELECT S.SNAME,COUNT(*) FROM S GROUP BY S.SNAME HAVING COUNT(*)>1;

 

9、查询两门课以上都不及格的学生姓名，课程名称和成绩

思路：根据表关系，涉及到学生表，课程表，成绩表

查询两门课以上不及格的学生编号

SELECT SID FROM SC WHERE SCORE<60 GROUP BY SID HAVING COUNT(*)>2

最终：

SELECT S.SNAME,C.CNAME,SC.SCORE FROM S,C,SC WHERE S.SID=( SELECT SID FROM SC WHERE SCORE<60 GROUP BY SID HAVING COUNT(*)>2) AND S.SID=SC.SID AND C.CID=SC.CID GROUP BY S.SID;

 

10、查询每门课都大于80的学生编号，学生姓名

思路1:查询学生最低分数大于80的学生编号：

SELECT SID FROM SC GROUP BY SID HAVING MIN(SCORE)>80

最终;

SELECT SID,SNAME FROM S WHERE SID IN (SELECT SID FROM SC GROUP BY SID HAVING MIN(SCORE)>80);

思路2：查询分数小于等于80的学生编号：

SELECT DISTINCT(SID) FROM SC WHERE SCORE<80;

最终：

SELECT SID,SNAME FROM S WHERE SID NOT IN(SELECT DISTINCT(SID) FROM SC WHERE SCORE<80;) 

 

在做调查、实验、统计学生成绩及个人信息等时，我们会将提前定制好的填写表格模板发放给对象，定制好的统计格式可以减少后续分析的汇总工作量，确保所有的填写都是规范的。
样本量必须要有一定的规模才能得到相对可靠的分析结论，对于成百上千张excel表格如何快速将所有的数据汇总到一张表上呢？另外excel表的格式可能是xlsx，也可能是xlsx，甚至可能是csv格式，如下图所示。不过不用担心，下面的这套简短代码能解决这些问题。
import os
import pandas as pd

#指定excel读取的路径
file_path=r'C:UsersAdministratorDesktopexcel'

#读取指定路径下的所有excel表绝对路径
file_list = []
for i, j, k in os.walk(file_path):
    for f in k:
        file_list.append(os.path.join(i, f))

#创建一个空的dataframe作为容器
df1=pd.DataFrame()
        
for file in file_list:
    if os.path.split(file)[1].split('.')[1] == 'xlsx' or os.path.split(file)[1].split('.')[1] == 'xls':
        data = pd.read_excel(file)
    elif os.path.split(file)[1].split('.')[1] == 'csv':   
        data = pd.read_excel(file)
    else:
        continue
    #将读取数据传进容器
    if df1.shape[0]==0:#如果df1是空的
        df1=data
    else:
        df1=pd.concat([df1,data],axis=0)
    
df1=df1.reset_index().drop(['index'],axis=1)
df1
读者除了要自行更改file_path路径外，其他的直接运行就可以得到那11张表格汇总后的数据，如需单独存储可直接导出到指定路径，效果如下：
本文所用案例素材获取方式：打开后找到名为“excel”的文件夹下载即可获取
提取码： fxd5​pan.baidu.com
写在最后：
希望以上的内容能对你汇总excel数据有所帮助，请关注我，将持续更新python应用。