基于‘学工系统’与‘厦门’的违纪处分数据处理与分析
CREATE TABLE student (
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
student_number VARCHAR(20) UNIQUE,
major VARCHAR(100),
class VARCHAR(50)
);
CREATE TABLE incident (
incident_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
type VARCHAR(50),
description TEXT,
date DATETIME,
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
CREATE TABLE punishment (
punishment_id INT PRIMARY KEY AUTO_INCREMENT,
incident_id INT,
punishment_type VARCHAR(50),
details TEXT,
effective_date DATE,
FOREIGN KEY (incident_id) REFERENCES incident(incident_id)
);
import mysql.connector
# 连接数据库
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="student_management"
)
cursor = conn.cursor()
# 查询违纪类型统计
query = """
SELECT type, COUNT(*) AS count
FROM incident
GROUP BY type;
"""
cursor.execute(query)
results = cursor.fetchall()
print("违纪类型统计:")
for row in results:
print(f"类型: {row[0]}, 数量: {row[1]}")
cursor.close()
conn.close()
query = """
SELECT s.student_id, s.name, COUNT(i.incident_id) AS total_incidents
FROM student s
LEFT JOIN incident i ON s.student_id = i.student_id
GROUP BY s.student_id
ORDER BY total_incidents DESC;
"""
cursor.execute(query)
results = cursor.fetchall()
print("高频违纪学生统计:")
for row in results:
print(f"学号: {row[0]}, 姓名: {row[1]}, 违纪次数: {row[2]}")
本站知识库部分内容及素材来源于互联网，如有侵权，联系必删！