以下是一个简单的学生考勤数据库功能实现代码，使用MySQL数据库和Python编程语言实现：
```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
```
以上代码实现了创建数据库表、插入数据、查询数据等基本功能，可以根据实际需求进行修改和扩展。
扫一扫
1363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
