学生考勤数据库功能实现代码

以下是一个简单的学生考勤数据库功能实现代码，使用MySQL数据库和Python编程语言实现：

import mysql.connector

# 连接数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="attendance"
)

# 创建游标对象
mycursor = mydb.cursor()

# 创建学生表
mycursor.execute("CREATE TABLE students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), gender VARCHAR(255), age INT)")

# 创建教师表
mycursor.execute("CREATE TABLE teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), gender VARCHAR(255), age INT)")

# 创建课程表
mycursor.execute("CREATE TABLE courses (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), teacher_id INT, FOREIGN KEY (teacher_id) REFERENCES teachers(id))")

# 创建考勤记录表
mycursor.execute("CREATE TABLE attendance_records (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, course_id INT, attendance_time DATETIME, status VARCHAR(255), FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id))")

# 创建成绩表
mycursor.execute("CREATE TABLE scores (id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, course_id INT, score INT, FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id))")

# 插入学生数据
sql = "INSERT INTO students (name, gender, age) VALUES (%s, %s, %s)"
val = [
  ('Alice', 'Female', 18),
  ('Bob', 'Male', 19),
  ('Charlie', 'Male', 20),
  ('David', 'Male', 19),
  ('Eve', 'Female', 18)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "students inserted.")

# 插入教师数据
sql = "INSERT INTO teachers (name, gender, age) VALUES (%s, %s, %s)"
val = [
  ('Mr. Smith', 'Male', 35),
  ('Ms. Johnson', 'Female', 30),
  ('Mr. Brown', 'Male', 40)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "teachers inserted.")

# 插入课程数据
sql = "INSERT INTO courses (name, teacher_id) VALUES (%s, %s)"
val = [
  ('Math', 1),
  ('Physics', 2),
  ('English', 3)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "courses inserted.")

# 插入考勤记录数据
sql = "INSERT INTO attendance_records (student_id, course_id, attendance_time, status) VALUES (%s, %s, %s, %s)"
val = [
  (1, 1, '2022-01-01 08:00:00', 'Present'),
  (2, 1, '2022-01-01 08:00:00', 'Late'),
  (3, 1, '2022-01-01 08:00:00', 'Absent'),
  (4, 2, '2022-01-01 08:00:00', 'Present'),
  (5, 2, '2022-01-01 08:00:00', 'Present')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "attendance records inserted.")

# 插入成绩数据
sql = "INSERT INTO scores (student_id, course_id, score) VALUES (%s, %s, %s)"
val = [
  (1, 1, 90),
  (2, 1, 85),
  (3, 1, 75),
  (4, 2, 80),
  (5, 2, 90)
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "scores inserted.")

# 查询某个学生的考勤记录
sql = "SELECT students.name, courses.name, attendance_records.attendance_time, attendance_records.status FROM attendance_records INNER JOIN students ON attendance_records.student_id = students.id INNER JOIN courses ON attendance_records.course_id = courses.id WHERE students.name = %s"
val = ('Alice',)
mycursor.execute(sql, val)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

# 查询某个课程的成绩排名
sql = "SELECT students.name, scores.score FROM scores INNER JOIN students ON scores.student_id = students.id WHERE scores.course_id = %s ORDER BY scores.score DESC"
val = (1,)
mycursor.execute(sql, val)
myresult = mycursor.fetchall()
rank = 1
for x in myresult:
  print(str(rank) + ". " + x[0] + ": " + str(x[1]))
  rank += 1

以上代码实现了创建数据库表、插入数据、查询数据等基本功能，可以根据实际需求进行修改和扩展。