import sqlite3
def create_tables():
conn = sqlite3.connect('university.db')
c = conn.cursor()
# 创建学生表
c.execute('''CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
major TEXT
)''')
# 创建教师表
c.execute('''CREATE TABLE teachers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department TEXT
)''')
# 创建课程表
c.execute('''CREATE TABLE courses (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
teacher_id INTEGER,
FOREIGN KEY(teacher_id) REFERENCES teachers(id)
)''')
# 创建成绩表
c.execute('''CREATE TABLE grades (
student_id INTEGER,
course_id INTEGER,
grade INTEGER,
PRIMARY KEY(student_id, course_id),
FOREIGN KEY(student_id) REFERENCES students(id),
FOREIGN KEY(course_id) REFERENCES courses(id)
)''')
conn.commit()
conn.close()
create_tables()
]]>
def insert_data():
conn = sqlite3.connect('university.db')
c = conn.cursor()
# 插入学生数据
c.execute("INSERT INTO students (name, age, major) VALUES ('Alice', 20, 'Computer Science')")
c.execute("INSERT INTO students (name, age, major) VALUES ('Bob', 22, 'Physics')")
# 插入教师数据
c.execute("INSERT INTO teachers (name, department) VALUES ('Dr. Smith', 'Mathematics')")
c.execute("INSERT INTO teachers (name, department) VALUES ('Dr. Johnson', 'Biology')")
# 插入课程数据
c.execute("INSERT INTO courses (name, teacher_id) VALUES ('Calculus', 1)")
c.execute("INSERT INTO courses (name, teacher_id) VALUES ('Genetics', 2)")
# 插入成绩数据
c.execute("INSERT INTO grades (student_id, course_id, grade) VALUES (1, 1, 95)")
c.execute("INSERT INTO grades (student_id, course_id, grade) VALUES (2, 2, 88)")
conn.commit()
conn.close()
insert_data()
]]>