sql高校学籍管理系统 功能要求： ?实现学生信息、班级、院系、专业等的管理； ?实现课程、学生成绩信息管理； ?实现学生的奖惩信息管理； ?创建规则用于限制性别项只能输入“男”或“女”； ?创建视图查询各个学生的学号、姓名、班级、专业、院系； ?创建存储过程查询指定学生的成绩单； ?创建触发器当增加、删除学生和修改学生班级信息时自动修改相应班级学生人数； ?建立数据库相关表之间的参照完整性约束。 建立S-T课本数据库*/

好的，下面是根据您的要求所建立的S-T课本数据库的详细步骤：

1. 学生信息表(Student)：该表用于存储学生的基本信息。

CREATE TABLE Student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    birthdate DATE NOT NULL,
    class_id INT NOT NULL,
    major_id INT NOT NULL,
    FOREIGN KEY (class_id) REFERENCES Class(id),
    FOREIGN KEY (major_id) REFERENCES Major(id)
);

2. 班级信息表(Class)：该表用于存储班级的基本信息。

CREATE TABLE Class(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    major_id INT NOT NULL,
    department_id INT NOT NULL,
    student_num INT,
    FOREIGN KEY (major_id) REFERENCES Major(id),
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

3. 专业信息表(Major)：该表用于存储专业的基本信息。

CREATE TABLE Major(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    manager VARCHAR(50) NOT NULL,
    FOREIGN KEY (department_id) REFERENCES Department(id)
);

4. 课程信息表(Course)：该表用于存储课程的基本信息。

CREATE TABLE Course(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    credit INT NOT NULL,
    teacher VARCHAR(50) NOT NULL
);

5. 学生成绩信息表(Score)：该表用于存储学生成绩信息。

CREATE TABLE Score(
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score FLOAT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Student(id),
    FOREIGN KEY (course_id) REFERENCES Course(id)
);

6. 学生奖惩信息表(RewardPunish)：该表用于存储学生的奖惩信息。

CREATE TABLE RewardPunish(
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    type VARCHAR(10) NOT NULL,
    reason VARCHAR(200) NOT NULL,
    date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Student(id)
);

7. 院系信息表(Department)：该表用于存储院系的基本信息。

CREATE TABLE Department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

8. 视图(View)：该视图用于查询各个学生的学号、姓名、班级、专业、院系等信息。

CREATE VIEW StudentInfo AS 
SELECT s.id, s.name, s.gender, s.birthdate, c.name AS class_name, m.name AS major_name, d.name AS department_name
FROM Student s 
LEFT JOIN Class c ON s.class_id = c.id 
LEFT JOIN Major m ON s.major_id = m.id 
LEFT JOIN Department d ON m.department_id = d.id;

9. 存储过程(Procedure)：该存储过程用于查询指定学生的成绩单。

CREATE PROCEDURE GetScore(IN stu_id INT)
BEGIN
    SELECT s.id, s.name, c.name AS course_name, sc.score
    FROM Student s, Course c, Score sc
    WHERE s.id = stu_id AND s.id = sc.student_id AND c.id = sc.course_id;
END;

10. 触发器(Trigger)：该触发器用于当增加、删除学生或修改学生班级信息时，自动更新相应班级学生人数。

CREATE TRIGGER UpdateClassStudentNum AFTER INSERT ON Student FOR EACH ROW
UPDATE Class SET student_num = student_num + 1 WHERE id = NEW.class_id;

CREATE TRIGGER DeleteClassStudentNum AFTER DELETE ON Student FOR EACH ROW
UPDATE Class SET student_num = student_num - 1 WHERE id = OLD.class_id;

CREATE TRIGGER UpdateClassStudentNum2 AFTER UPDATE ON Student FOR EACH ROW
IF NEW.class_id <> OLD.class_id THEN
    UPDATE Class SET student_num = student_num + 1 WHERE id = NEW.class_id;
    UPDATE Class SET student_num = student_num - 1 WHERE id = OLD.class_id;
END IF;

11. 参照完整性约束(Referential Integrity Constraint)：该约束用于保持数据的完整性和一致性。

ALTER TABLE Student ADD CONSTRAINT FK_Class FOREIGN KEY (class_id) REFERENCES Class(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Student ADD CONSTRAINT FK_Major FOREIGN KEY (major_id) REFERENCES Major(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Class ADD CONSTRAINT FK_Major FOREIGN KEY (major_id) REFERENCES Major(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Class ADD CONSTRAINT FK_Department FOREIGN KEY (department_id) REFERENCES Department(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Major ADD CONSTRAINT FK_Department FOREIGN KEY (department_id) REFERENCES Department(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Score ADD CONSTRAINT FK_Student FOREIGN KEY (student_id) REFERENCES Student(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Score ADD CONSTRAINT FK_Course FOREIGN KEY (course_id) REFERENCES Course(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE RewardPunish ADD CONSTRAINT FK_Student FOREIGN KEY (student_id) REFERENCES Student(id) ON DELETE CASCADE ON UPDATE CASCADE;

以上是根据您的要求所建立的S-T课本数据库的详细步骤，您可以根据具体需求进行适当的修改和调整。