SQL语句以及在Python中的应用

本文记录一些自己使用过的SQL语句，以及在Python中的应用。

SQL语句

一、学生得分数据表

字段数据举例如下：

id	name	class_no	stu_id	lesson	score
1	张三	501	1	lesson1	10
2	张三	501	1	lesson2	9

表设计如下：

CREATE TABLE "stu_data" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" TEXT NOT NULL,
  "class_no" INTEGER NOT NULL,
  "stu_id" INTEGER NOT NULL,
  "lesson" TEXT,
  "score" INTEGER,
  UNIQUE ("id" ASC)
);

1.查询同一个班级每个学生所有lesson的score总和

• 按学号 stu_id 排序用 ORDER BY t1.stu_id；
• 按总分排序用 ORDER BY total_score DESC，DESC表示降序排序；
• 统计非0的个数用COUNT(CASE WHEN t1.score > 0 THEN 1 ELSE NULL END) AS non_zero_count。

-- 查询同一个班级每个学生所有lesson的score总和
-- ['班级', '学号', '姓名', '总分', '非0分的次数']
SELECT t1.class_no, t1.stu_id, t1.name, SUM(t1.score) AS total_score,
			COUNT(CASE WHEN t1.score > 0 THEN 1 ELSE NULL END) AS non_zero_count
FROM stu_data t1  
WHERE t1.class_no = 506
GROUP BY t1.name, t1.class_no
-- ORDER BY t1.stu_id
ORDER BY total_score DESC

2.查询同一个lesson每个班级class_no的平均分

-- 查询同一个项目lesson每个班级class_no的平均分
-- ['项目', '班级', '平均分', '去除0分后的平均分', '非0分的次数']
SELECT
    t1.lesson,
    t1.class_no,
    ROUND(AVG(t1.score), 2) AS avg_score,
    ROUND(SUM(CASE WHEN t1.score > 0 THEN t1.score ELSE NULL END) /  
          COUNT(CASE WHEN t1.score > 0 THEN t1.score ELSE NULL END), 2) AS avg_score_excluding0,
    COUNT(CASE WHEN t1.score > 0 THEN t1.score ELSE NULL END) AS non_zero_count
FROM stu_data t1 
WHERE t1.lesson = 'lesson3'
GROUP BY t1.lesson, t1.class_no
ORDER BY avg_score DESC

3.查询所有项目lesson每个班级class_no的平均分

-- 查询所有项目lesson每个班级class_no的平均分
SELECT  
    t1.lesson, t1.class_no,
    ROUND(AVG(t1.score), 2) AS avg_score
FROM stu_data t1
GROUP BY t1.lesson, t1.class_no

在Python中的应用

1.查询同一个班级每个学生所有lesson的score总和

import sqlite3
db_file = 'test.db'
conn = sqlite3.connect(db_file)
cursor = conn.cursor()

class_no = 501
query_sql = f"""
        SELECT t1.name, t1.class_no, t1.stu_id, SUM(t1.score) AS total_score,
        COUNT(CASE WHEN t1.score > 0 THEN 1 ELSE NULL END) AS non_zero_count
        FROM stu_data t1
        WHERE t1.class_no = {class_no}  
        GROUP BY t1.name, t1.class_no
        ORDER BY total_score DESC
        """
cursor.execute(query_sql)
query_results = cursor.fetchall()
print(query_results)
cursor.close()
conn.close()