7、创建一个数据库表,存储学生的姓名、学号、语文、数学、英语成绩,编写程序,实现对数据库的记录添加、删除、修改和查询功能。
时间: 2023-08-23 17:05:06 浏览: 118
好的,这是一个较为复杂的问题,需要结合具体的编程语言和数据库来实现。以下是一个示例的 SQL 语句和 Python 代码,用于实现这个功能:
首先,我们需要创建一个名为 `students` 的表,包含学生的姓名、学号、语文、数学、英语成绩:
```sql
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
student_id VARCHAR(255) NOT NULL,
chinese_score INT,
math_score INT,
english_score INT
);
```
接下来,我们可以使用 Python 的 `mysql-connector` 模块来连接数据库,并实现对 `students` 表的记录添加、删除、修改和查询功能:
```python
import mysql.connector
# 连接数据库
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
# 添加记录
def add_student(name, student_id, chinese_score, math_score, english_score):
mycursor = mydb.cursor()
sql = "INSERT INTO students (name, student_id, chinese_score, math_score, english_score) VALUES (%s, %s, %s, %s, %s)"
val = (name, student_id, chinese_score, math_score, english_score)
mycursor.execute(sql, val)
mydb.commit()
# 删除记录
def delete_student(student_id):
mycursor = mydb.cursor()
sql = "DELETE FROM students WHERE student_id = %s"
val = (student_id,)
mycursor.execute(sql, val)
mydb.commit()
# 修改记录
def update_student(student_id, field, value):
mycursor = mydb.cursor()
sql = "UPDATE students SET " + field + " = %s WHERE student_id = %s"
val = (value, student_id)
mycursor.execute(sql, val)
mydb.commit()
# 查询记录
def search_student(student_id):
mycursor = mydb.cursor()
sql = "SELECT * FROM students WHERE student_id = %s"
val = (student_id,)
mycursor.execute(sql, val)
result = mycursor.fetchall()
return result
```
使用示例:
```python
# 添加学生记录
add_student("张三", "001", 80, 90, 70)
# 查询学生记录
result = search_student("001")
print(result) # 输出 [(1, '张三', '001', 80, 90, 70)]
# 修改学生成绩
update_student("001", "chinese_score", 85)
# 删除学生记录
delete_student("001")
```
阅读全文