CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
gender ENUM('M', 'F') NOT NULL,
phone VARCHAR(15),
email VARCHAR(100)
);
CREATE TABLE majors (
major_id INT AUTO_INCREMENT PRIMARY KEY,
major_name VARCHAR(100) NOT NULL,
department VARCHAR(100) NOT NULL
);
CREATE TABLE scores (
score_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
exam_type ENUM('GPA', 'Exam') NOT NULL,
score DECIMAL(5, 2),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
]]>
import mysql.connector
# 连接到数据库
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="admissions"
)
cursor = conn.cursor()
# 插入一条学生记录
sql = "INSERT INTO students (name, gender, phone, email) VALUES (%s, %s, %s, %s)"
val = ("张三", "M", "12345678901", "zhangsan@example.com")
cursor.execute(sql, val)
conn.commit()
print(cursor.rowcount, "record inserted.")
# 查询所有学生
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
for record in records:
print(record)
# 关闭连接
cursor.close()
conn.close()
]]>
