编辑代码

CREATE DATABASE 曾金玲;

USE 曾金玲;

CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender CHAR(1) CHECK (gender IN ('M', 'F')),
birth_date DATE,
major VARCHAR(50),
enrollment_date DATE
);

CREATE TABLE teachers (
  teacher_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
   ender CHAR(1) CHECK (gender IN ('M'', 'F')),
   title VARCHAR(20),
   department VARCHAR(50),
   hire_date DATE
);

-- 创建课程表
CREATE TABLE courses (
   course_id INT PRIMARY KEY AUTO_INCREMENT,
   course_name VARCHAR(100) NOT NULL,
   credit FLOAT,
   classroom VARCHAR(20),
   teacher_id INT,
   FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

-- 创建选课表
CREATE TABLE course_selection (
   selection_id INT PRIMARY KEY AUTO_INCREMENT,
   student_id INT,
   course_id INT,
   selection_date DATE,
   score FLOAT,
   FOREIGN KEY (student_id) REFERENCES students(student_id),
   FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 创建成绩表
CREATE TABLE scores (
   score_id INT PRIMARY KEY AUTO_INCREMENT,
   selection_id INT,
   exam_type VARCHAR(20),
   score FLOAT,
   exam_date DATE,
   FOREIGN KEY (selection_id) REFERENCES course_selection(selection_id)
);
-- 插入学生数据
INSERT INTO students (name, gender, birth_date, major, enrollment_date) VALUES
('张三', 'M', '2000-05-15', '计算机科学', '2019-09-01'),
('李四', 'F', '2001-02-20', '软件工程', '2020-09-01'),
('王五', 'M', '1999-11-10', '数据科学', '2018-09-01'),
('赵六', 'F', '2000-08-25', '人工智能', '2019-09-01'),
('钱七', 'M', '2001-03-30', '网络安全', '2020-09-01');

-- 插入教师数据
INSERT INTO teachers (name, gender, title, department, hire_date) VALUES
('王教授', 'M', '教授', '计算机学院', '2005-08-15'),
('李副教授', 'F', '副教授', '软件学院', '2010-03-20'),
('张讲师', 'M', '讲师', '数据科学学院', '2015-07-10'),
('刘教授', 'F', '教授', '人工智能学院', '2008-09-01'),
('陈副教授', 'M', '副教授', '网络空间安全学院', '2012-06-15');

-- 插入课程数据
INSERT INTO courses (course_name, credit, classroom, teacher_id) VALUES
('数据库原理', 4, 'A101', 1),
('Java程序设计', 3, 'B205', 2),
('数据结构', 4, 'C302', 3),
('机器学习', 3, 'D410', 4),
('网络安全基础', 3, 'E105', 5);

-- 插入选课数据
INSERT INTO course_selection (student_id, course_id, selection_date, score) VALUES
(1, 1, '2023-02-15', NULL),
(1, 3, '2023-02-15', NULL),
(2, 2, '2023-02-16', NULL),
(3, 4, '2023-02-16', NULL),
(4, 5, '2023-02-17', NULL),
(5, 1, '2023-02-17', NULL),
(2, 3, '2023-02-18', NULL),
(3, 5, '2023-02-18', NULL);

-- 插入成绩数据
INSERT INTO scores (selection_id, exam_type, score, exam_date) VALUES
(1, '期中考试', 85.5, '2023-04-10'),
(1, '期末考试', 92.0, '2023-06-20'),
(2, '期中考试', 78.0, '2023-04-12'),
(3, '期末考试', 88.5, '2023-06-22'),
(4, '期中考试', 90.0, '2023-04-15');
-- 查询所有学生信息
SELECT * FROM students;

-- 查询所有课程信息
SELECT * FROM courses;

-- 查询学生选课情况
SELECT s.name AS student_name, c.course_name, t.name AS teacher_name
FROM students s
JOIN course_selection cs ON s.student_id = cs.student_id
JOIN courses c ON cs.course_id = c.course_id
JOIN teachers t ON c.teacher_id = t.teacher_id;

-- 查询学生成绩
SELECT s.name AS student_name, c.course_name, sc.exam_type, sc.score
FROM students s
JOIN course_selection cs ON s.student_id = cs.student_id
JOIN courses c ON cs.course_id = c.course_id
JOIN scores sc ON cs.selection_id = sc.selection_id;

-- 查询每门课程的平均分
SELECT c.course_name, AVG(sc.score) AS average_score
FROM courses c
JOIN course_selection cs ON c.course_id = cs.course_id
JOIN scores sc ON cs.selection_id = sc.selection_id
GROUP BY c.course_name;