这也太看得起我了吧看你们了

通过python在 SQLite3中创建一个数据库。在该数据库中建一个表，表中包含了学号姓名，平时成绩，期莫成绩和总成绩等字段。读取包含成绩信息的文档，将文档中的数据插入表中。然后通过SQL语句获取平时成绩最好的学生信息，期莫成绩最好的学生信息，总成绩最好的学生信息，并学生根据总成绩进行排序以及需要获取总成绩的的平均值。对比任务二，检查结果是否相同。


import sqlite3

# 连接到数据库
conn = sqlite3.connect('students.db')

# 创建表
conn.execute('''CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    normal_grade REAL NOT NULL,
    mid_term_grade REAL NOT NULL,
    final_grade REAL NOT NULL,
    total_grade REAL NOT NULL);''')

# 插入数据
conn.execute("INSERT INTO students (id, name, normal_grade, mid_term_grade, final_grade, total_grade) VALUES (1, 'Tom', 85.5, 90.0, 92.0, 267.5)")
conn.execute("INSERT INTO students (id, name, normal_grade, mid_term_grade, final_grade, total_grade) VALUES (2, 'Jerry', 70.0, 80.0, 75.0, 225.0)")
conn.execute("INSERT INTO students (id, name, normal_grade, mid_term_grade, final_grade, total_grade) VALUES (3, 'Lucy', 60.0, 70.0, 80.0, 210.0)")

# 提交事务
conn.commit()

cursor = conn.execute("SELECT * FROM students ORDER BY final_grade DESC LIMIT 1")
best_final_grade_student = cursor.fetchone()
print("期末成绩最好的学生: ", best_final_grade_student)

cursor = conn.execute("SELECT * FROM students ORDER BY total_grade DESC LIMIT 1")
best_total_grade_student = cursor.fetchone()
print("总成绩最好的学生: ", best_total_grade_student)

cursor = conn.execute("SELECT AVG(total_grade) FROM students")
average_total_grade = cursor.fetchone()[0]
print("总成绩的平均值: ", average_total_grade)

cursor = conn.execute("SELECT * FROM students ORDER BY total_grade DESC")
sorted_students = cursor.fetchall()
print("按照总成绩排序后的学生信息: ", sorted_students)

# 关闭数据库连接
conn.close()

这也太看得起我了吧 看你们了

这也太看得起我了吧看你们了