使用SQL server实现教务处管理系统功能

设计目的 ：

数据库原理的应用； 概念结构设计（ER图）； 逻辑结构设计与物理结构设计； 数据库DDL基本操作； 数据的增、删、改、查； 数据库数据集合函数查询与复杂的数据查询方式； 创建与使用索引； 创建与使用视图； 创建与使用数据库完整性约束； 创建与使用存储过程； 创建与使用触发器； 创建数据库用户及用户权限控制技术；

运行代码：

--创建学生表
CREATE TABLE student (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    gender VARCHAR(10) NOT NULL,
    major VARCHAR(50) NOT NULL,
    grade INT NOT NULL,
    avg_score FLOAT
);

--为学生表中的age字段创建检查约束
ALTER TABLE student ADD CONSTRAINT chk_student_age CHECK (age >= 18 and age <= 70);

--创建教师表
CREATE TABLE teacher (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    gender VARCHAR(10) NOT NULL,
    department VARCHAR(50) NOT NULL,
    title VARCHAR(50) NOT NULL
);

--创建课程表
CREATE TABLE course (
    id INT PRIMARY KEY NOT NULL,
    course_name VARCHAR(50) NOT NULL,
    credit INT NOT NULL,
    teacher_id INT NOT NULL,
    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher(id)
);

--创建选课表
CREATE TABLE enrollment (
    id INT PRIMARY KEY NOT NULL,
    course_id INT NOT NULL,
    student_id INT NOT NULL,
    score INT NOT NULL,
    CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(id),
    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(id)
);

--为选课表中的score字段创建非空约束
--为选课表中的score字段创建非空约束
--为选课表中的score字段创建非空约束
ALTER TABLE enrollment ALTER COLUMN score INT NOT NULL;

--数据插入
INSERT INTO student (id, name, age, gender, major, grade) VALUES (1, '张三', 20, '男', '计算机科学', 2019);
INSERT INTO student (id, name, age, gender, major, grade) VALUES (2, '李四', 21, '女', '信息工程', 2018);

INSERT INTO teacher (id, name, age, gender, department, title) VALUES (1, '王老师', 35, '男', '计算机系', '副教授');
INSERT INTO teacher (id, name, age, gender, department, title) VALUES (2, '赵老师', 40, '女', '信息工程系', '教授');

INSERT INTO course (id, course_name, credit, teacher_id) VALUES (1, '数据结构', 4, 1);
INSERT INTO course (id, course_name, credit, teacher_id) VALUES (2, '编译原理', 4, 2);

INSERT INTO enrollment (id, course_id, student_id, score) VALUES (1, 1, 1, 80);
INSERT INTO enrollment (id, course_id, student_id, score) VALUES (2, 2, 2, 85);
----------------查询表记录----------------
SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM enrollment;
--数据删除
DELETE FROM enrollment WHERE id = 1;

--数据更新
UPDATE student SET grade = 2020 WHERE id = 1;

--数据查询
SELECT * FROM student WHERE id = 2;

--查询选课表中总分数
SELECT SUM(score) FROM enrollment;

--查询选课表中最高分数
SELECT MAX(score) FROM enrollment;

--查询选课表中每门课程的选课人数和平均成绩
SELECT course.course_name, COUNT(enrollment.student_id), AVG(enrollment.score)
FROM enrollment
JOIN course ON enrollment.course_id = course.id
GROUP BY course.course_name;

--查询选课表中选修了所有课程的学生
SELECT student.id, student.name
FROM student
WHERE NOT EXISTS (
    SELECT course.id
    FROM course
    WHERE NOT EXISTS (
        SELECT enrollment.course_id
        FROM enrollment
        WHERE enrollment.student_id = student.id AND enrollment.course_id = course.id
    )
);

--为学生表的id字段创建索引
CREATE INDEX idx_student_id ON student (id);

--查询语句中使用索引
SELECT * FROM student WHERE id = 1;

--创建选课汇总视图
GO
CREATE VIEW enrollment_summary AS
SELECT course.course_name, COUNT(enrollment.student_id) AS num_students, AVG(enrollment.score) AS avg_score
FROM enrollment
JOIN course ON enrollment.course_id = course.id
GROUP BY course.course_name;
GO
--查询选课汇总视图
SELECT * FROM enrollment_summary;
-- 创建计算学生选修课总学分的存储过程
GO
CREATE PROCEDURE calculate_credit (@student_id INT, @total_credit INT OUTPUT)
AS
BEGIN
    SELECT @total_credit = SUM(course.credit) FROM course JOIN enrollment ON course.id = enrollment.course_id WHERE enrollment.student_id = @student_id;
END;
GO

-- 调用存储过程
DECLARE @total_credit INT;
EXEC calculate_credit 1, @total_credit OUTPUT;
SELECT @total_credit;
------------创建触发器-----------
GO
CREATE TRIGGER update_avg_score
ON enrollment
AFTER INSERT
AS
BEGIN
    UPDATE student
    SET avg_score = (SELECT AVG(score) FROM enrollment WHERE student_id = inserted.student_id)
    FROM student JOIN inserted ON student.id = inserted.student_id;
END;
-- 插入一条选课记录
INSERT INTO enrollment (id, course_id, student_id, score) VALUES (3, 1, 1, 85);

-- 查询学生表中更新后的平均分数
SELECT * FROM student WHERE id = 1;

-- 创建新用户
CREATE LOGIN newuser WITH PASSWORD = 'password';

-- 创建用户
CREATE USER newuser FOR LOGIN newuser;

-- 授权新用户访问选课表和课程表
GRANT SELECT ON enrollment TO newuser;
GRANT SELECT ON course TO newuser;

-- 查看新用户权限
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('newuser');

-- 撤销新用户访问选课表和课程表的权限
REVOKE SELECT ON enrollment FROM newuser;
REVOKE SELECT ON course FROM newuser;

-- 删除新用户
DROP USER newuser;
DROP LOGIN newuser;