学生信息管理系统oracle

学生信息管理系统

1. 需求分析

1.1 学生模块 学生信息管理：提供学生个人信息的录入、查询、更新和删除功能，包括姓名、学号、联系方式等。 选课管理：学生可以查看可选课程，进行选课与退课操作。系统需记录选课历史，并能够查询学期内的选课情况。 1.2 教师模块 教师信息管理：教师可以录入、更新和删除个人信息，包括姓名、工号、教学科目及联系方式等。 授课管理：教师可以查看自己所教授的课程信息，并录入学生的期中与期末成绩。 1.3 课程模块 课程信息管理：管理员及教师可以添加、修改和删除课程信息，如课程名称、学分、授课教师和教学大纲等。 1.4 教务模块 教学计划管理：教务管理人员可以制定和维护每学期的教学计划，包括课程安排、考试安排及其他相关活动。 1.5 系统管理模块 用户权限管理：系统允许超级管理员设置和管理用户的权限，确保不同角色（如学生、教师、管理员）能访问相应的功能。 基础数据管理：管理员可管理系统的基础数据，包括教学资源、学科信息和用户信息等。

2. 系统设计

2.1. 系统结构功能图

根据需求分析，确定系统结构功能如图2-1所示：

图2-1 系统功能结构图

2.2. 功能流程图

系统最主要的功能就是管理员登录之后对各种功能的使用。管理员登录过程流程如图4-2所示。

图2-2 管理员管理流程

2.3. 数据库设计

整体功能的E-R图如图2-3所示。

图2-3 系统E-R图

本系统包含的数据表如下：

用户表用来存储用户的一些相关信息，如表2-1所示。

2.3.1. 1. **专业 **(Major)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
专业ID	NUMBER	NUMBER		NO		专业的唯一标识符
专业名称	VARCHAR2	VARCHAR	255	NO		专业名称
人数	NUMBER	NUMBER		YES	0	注册学生人数

2.3.2. 2. **班级 **(Class)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
班级ID	NUMBER	NUMBER		NO		班级的唯一标识符
班级名称	VARCHAR2	VARCHAR	255	NO		班级名称
人数	NUMBER	NUMBER		YES	0	班级注册人数
专业ID	NUMBER	NUMBER		NO		关联的专业ID

2.3.3. 3. **学生 **(Student)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
学生ID	NUMBER	NUMBER		NO		学生的唯一标识符
姓名	VARCHAR2	VARCHAR	255	NO		学生姓名
身份证号	VARCHAR2	VARCHAR	18	YES		学生身份证号
性别	VARCHAR2	VARCHAR	10	YES		学生性别
出生日期	DATE	DATE		YES		学生出生日期
入学日期	DATE	DATE		NO		学生入学日期
联系电话	VARCHAR2	VARCHAR	20	YES		学生联系电话
邮箱	VARCHAR2	VARCHAR	255	YES		学生邮箱
班级ID	NUMBER	NUMBER		NO		关联的班级ID

2.3.4. 4. **教师 **(Teacher)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
教师ID	NUMBER	NUMBER		NO		教师的唯一标识符
姓名	VARCHAR2	VARCHAR	255	NO		教师姓名
职称	VARCHAR2	VARCHAR	50	YES		教师职称
联系电话	VARCHAR2	VARCHAR	20	YES		教师联系电话
邮箱	VARCHAR2	VARCHAR	255	YES		教师邮箱

2.3.5. 5. **课程 **(Course)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
课程ID	NUMBER	NUMBER		NO		课程的唯一标识符
课程名称	VARCHAR2	VARCHAR	255	NO		课程名称
学时	NUMBER	NUMBER		NO		课程学时
考核方式	VARCHAR2	VARCHAR	50	YES		考核方式
学分	NUMBER(3,1)	NUMBER		NO		课程学分
教授教师ID	NUMBER	NUMBER		NO		关联的教师ID
课程描述	CLOB	CLOB		YES		课程描述

2.3.6. 6. **学生选课 **(Student Course Selection)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
学生ID	NUMBER	NUMBER		NO		关联的学生ID
课程ID	NUMBER	NUMBER		NO		关联的课程ID
成绩	NUMBER(5,2)	NUMBER		YES		学生成绩
学期	VARCHAR2	VARCHAR	50	YES		学期名称
修课时间	DATE	DATE		YES	SYSDATE	选课时间

2.3.7. 7. **管理员 **(Administrator)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
管理员ID	NUMBER	NUMBER		NO		管理员的唯一标识符
姓名	VARCHAR2	VARCHAR	255	NO		管理员姓名
工号	VARCHAR2	VARCHAR	20	NO		管理员工号
联系电话	VARCHAR2	VARCHAR	20	YES		管理员联系电话
邮箱	VARCHAR2	VARCHAR	255	YES		管理员邮箱
创建时间	DATE	DATE		YES	SYSDATE	管理员创建时间

2.3.8. 8. **系统用户 **(System User)

列名	数据类型	字段类型	长度	是否为空	默认值	备注
用户ID	NUMBER	NUMBER		NO		系统用户的唯一标识符
用户名	VARCHAR2	VARCHAR	50	NO		用户名
密码	VARCHAR2	VARCHAR	255	NO		用户密码
用户类型	VARCHAR2	VARCHAR	10	NO		用户类型
关联学生ID	NUMBER	NUMBER		YES		关联的学生ID
关联教师ID	NUMBER	NUMBER		YES		关联的教师ID
关联管理员ID	NUMBER	NUMBER		YES		关联的管理员ID

3. 3 SQL**实现**

3.1. 3.1创建数据库表对象

- 创建序列
CREATE SEQUENCE 班级_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE 教师_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE 课程_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE 学生_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE 专业_SEQ  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;
CREATE SEQUENCE 管理员_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

-- 创建表（按依赖顺序）
CREATE TABLE 专业 (
专业ID NUMBER NOT NULL,
专业名称VARCHAR2(255) NOT NULL,
人数NUMBER DEFAULT 0,
PRIMARY KEY (专业ID)
);

CREATE TABLE 班级 (
班级ID NUMBER NOT NULL,
班级名称VARCHAR2(255) NOT NULL,
人数NUMBER DEFAULT 0,
专业ID NUMBER NOT NULL,
PRIMARY KEY (班级ID),
CONSTRAINT FK_班级_专业 FOREIGN KEY (专业ID) REFERENCES 专业 (专业ID)
);

CREATE TABLE 学生 (
学生ID NUMBER NOT NULL,
姓名VARCHAR2(255) NOT NULL,
身份证号VARCHAR2(18) UNIQUE,
性别VARCHAR2(10) CHECK (性别 IN ('男', '女', '其他')),
出生日期DATE,
入学日期DATE NOT NULL,
联系电话VARCHAR2(20),
邮箱VARCHAR2(255),
班级ID NUMBER NOT NULL,
PRIMARY KEY (学生ID),
CONSTRAINT FK_学生_班级 FOREIGN KEY (班级ID) REFERENCES 班级 (班级ID)
);

CREATE TABLE 教师 (
教师ID NUMBER NOT NULL,
姓名VARCHAR2(255) NOT NULL,
职称VARCHAR2(50),
联系电话VARCHAR2(20),
邮箱VARCHAR2(255),
PRIMARY KEY (教师ID)
);

CREATE TABLE 课程 (
课程ID NUMBER NOT NULL,
课程名称VARCHAR2(255) NOT NULL,
学时NUMBER NOT NULL,
考核方式VARCHAR2(50) CHECK (考核方式 IN ('考试', '考查', '其他')),
学分NUMBER(3,1) NOT NULL,
教授教师ID NUMBER NOT NULL,
课程描述CLOB,
PRIMARY KEY (课程ID),
CONSTRAINT FK_课程_教师 FOREIGN KEY (教授教师ID) REFERENCES 教师 (教师ID)
);

-- 学生选课关系表
CREATE TABLE 学生选课 (
学生ID NUMBER NOT NULL,
课程ID NUMBER NOT NULL,
成绩NUMBER(5,2) CHECK (成绩 BETWEEN 0 AND 100),
学期VARCHAR2(50),
修课时间DATE DEFAULT SYSDATE,
PRIMARY KEY (学生ID, 课程ID),
CONSTRAINT FK_选课_学生 FOREIGN KEY (学生ID) REFERENCES 学生 (学生ID),
CONSTRAINT FK_选课_课程 FOREIGN KEY (课程ID) REFERENCES 课程 (课程ID)
);

-- 管理员表
CREATE TABLE 管理员 (
  管理员ID NUMBER NOT NULL,
  姓名VARCHAR2(255) NOT NULL,
  工号VARCHAR2(20) UNIQUE NOT NULL,
  联系电话VARCHAR2(20),
  邮箱VARCHAR2(255),
  创建时间DATE DEFAULT SYSDATE,
  PRIMARY KEY (管理员ID)
);

-- 系统用户表（统一登录入口）
CREATE TABLE 系统用户 (
  用户ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  用户名VARCHAR2(50) UNIQUE NOT NULL,
  密码VARCHAR2(255) NOT NULL,
  用户类型VARCHAR2(10) NOT NULL 
  CHECK (用户类型 IN ('学生', '教师', '管理员')),
  关联学生ID NUMBER REFERENCES 学生(学生ID),
  关联教师ID NUMBER REFERENCES 教师(教师ID),
  关联管理员ID NUMBER REFERENCES 管理员(管理员ID),
  -- 确保每种用户类型只关联一个ID
  CONSTRAINT CHK_单一关联 CHECK (
    (用户类型 = '学生' AND 关联学生ID IS NOT NULL AND 关联教师ID IS NULL AND 关联管理员ID IS NULL) OR
    (用户类型 = '教师' AND 关联教师ID IS NOT NULL AND 关联学生ID IS NULL AND 关联管理员ID IS NULL) OR
    (用户类型 = '管理员' AND 关联管理员ID IS NOT NULL AND 关联学生ID IS NULL AND 关联教师ID IS NULL)
  )
);

3.2. 3.2**创建索引**

-- 创建索引优化查询性能
CREATE INDEX IDX_系统用户_用户名 ON 系统用户(用户名);
CREATE INDEX IDX_管理员_工号 ON 管理员(工号);
CREATE INDEX IDX_学生_身份证号 ON 学生(身份证号);
CREATE INDEX IDX_班级_专业ID ON 班级(专业ID);
CREATE INDEX IDX_学生选课_课程ID ON 学生选课(课程ID);

3.3. 3.3**创建视图**

学生信息视图，包含班级和专业信息
CREATE OR REPLACE VIEW 学生信息视图 AS
SELECT 
s.学生ID, 
s.姓名, 
s.性别, 
s.出生日期, 
s.入学日期, 
s.联系电话, 
s.邮箱,
c.班级名称,
m.专业名称
FROM 
学生s
JOIN 
班级c ON s.班级ID = c.班级ID
JOIN 
专业m ON c.专业ID = m.专业ID;

课程信息视图，包含教师信息
CREATE OR REPLACE VIEW 课程信息视图 AS
SELECT 
k.课程ID, 
k.课程名称, 
k.学时, 
k.考核方式, 
k.学分, 
k.课程描述,
t.姓名 AS 教师姓名,
t.职称,
t.联系电话,
t.邮箱
FROM 
课程k
JOIN 
教师t ON k.教授教师ID = t.教师ID;

学生成绩视图，包含课程和教师信息
CREATE OR REPLACE VIEW 学生成绩视图 AS
SELECT 
s.学生ID, 
s.姓名, 
c.班级名称,
k.课程ID, 
k.课程名称, 
x.成绩,
x.学期,
t.姓名 AS 教师姓名
FROM 
学生s
JOIN 
学生选课x ON s.学生ID = x.学生ID
JOIN 
课程k ON x.课程ID = k.课程ID
JOIN 
教师t ON k.教授教师ID = t.教师ID
JOIN 
班级c ON s.班级ID = c.班级ID;

专业统计视图，包含班级和学生人数
CREATE OR REPLACE VIEW 专业统计视图 AS
SELECT 
m.专业ID, 
m.专业名称, 
m.人数 AS 专业总人数,
COUNT(c.班级ID) AS 班级数量,
AVG(c.人数) AS 平均班级人数
FROM 
专业m
LEFT JOIN 
班级c ON m.专业ID = c.专业ID
GROUP BY 
m.专业ID, m.专业名称, m.人数;

教师授课统计视图
CREATE OR REPLACE VIEW 教师授课统计视图 AS
SELECT 
t.教师ID, 
t.姓名, 
t.职称,
COUNT(k.课程ID) AS 授课数量,
SUM(k.学时) AS 总学时,
AVG(k.学分) AS 平均学分
FROM 
教师t
LEFT JOIN 
课程k ON t.教师ID = k.教授教师ID
GROUP BY 
t.教师ID, t.姓名, t.职称;

3.4. 3.4**创建存储过程、函数**

学生注册过程（带班级人数同步）
CREATE OR REPLACE PROCEDURE 注册学生(
  p_姓名 VARCHAR2,
  p_身份证号 VARCHAR2,
  p_性别 VARCHAR2,
  p_出生日期 DATE,
  p_入学日期 DATE,
  p_联系电话 VARCHAR2,
  p_邮箱 VARCHAR2,
  p_班级ID NUMBER,
  p_用户名 VARCHAR2,
  p_密码 VARCHAR2
)
IS
v_学生ID NUMBER;
BEGIN
-- 插入学生记录
INSERT INTO 学生(姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (p_姓名, p_身份证号, p_性别, p_出生日期, p_入学日期, p_联系电话, p_邮箱, p_班级ID)
RETURNING 学生ID INTO v_学生ID;

-- 创建系统账号
INSERT INTO 系统用户(用户名, 密码, 用户类型, 关联学生ID)
VALUES (p_用户名, p_密码, '学生', v_学生ID);

-- 触发器会自动更新班级/专业人数
COMMIT;
DBMS_OUTPUT.PUT_LINE('学生注册成功! ID: ' || v_学生ID);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001, '身份证号或用户名已存在');
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

课程开设过程
CREATE OR REPLACE PROCEDURE 开设课程(
  p_课程名称 VARCHAR2,
  p_学时 NUMBER,
  p_考核方式 VARCHAR2,
  p_学分 NUMBER,
  p_教师ID NUMBER,
  p_课程描述 CLOB DEFAULT NULL
)
IS
v_课程ID NUMBER;
BEGIN
-- 验证教师存在
IF NOT 教师存在(p_教师ID) THEN
RAISE_APPLICATION_ERROR(-20002, '教师ID不存在');
END IF;

-- 创建课程
INSERT INTO 课程(课程名称, 学时, 考核方式, 学分, 教授教师ID, 课程描述)
VALUES (p_课程名称, p_学时, p_考核方式, p_学分, p_教师ID, p_课程描述)
RETURNING 课程ID INTO v_课程ID;

COMMIT;
DBMS_OUTPUT.PUT_LINE('课程开设成功! ID: ' || v_课程ID);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

成绩录入过程
CREATE OR REPLACE PROCEDURE 录入成绩(
  p_学生ID NUMBER,
  p_课程ID NUMBER,
  p_成绩 NUMBER,
  p_学期 VARCHAR2
)
IS
v_记录数 NUMBER;
BEGIN
-- 检查选课记录是否存在
SELECT COUNT(*) INTO v_记录数
FROM 学生选课
WHERE 学生ID = p_学生ID AND 课程ID = p_课程ID;

IF v_记录数 = 0 THEN
-- 不存在则创建新记录
INSERT INTO 学生选课(学生ID, 课程ID, 成绩, 学期)
VALUES (p_学生ID, p_课程ID, p_成绩, p_学期);
ELSE
-- 存在则更新成绩
UPDATE 学生选课
SET 成绩 = p_成绩, 学期 = p_学期
WHERE 学生ID = p_学生ID AND 课程ID = p_课程ID;
END IF;

COMMIT;
DBMS_OUTPUT.PUT_LINE('成绩录入成功!');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

学生转班过程
CREATE OR REPLACE PROCEDURE 学生转班(
  p_学生ID NUMBER,
  p_新班级ID NUMBER
)
IS
v_原班级ID NUMBER;
BEGIN
-- 获取原班级
SELECT 班级ID INTO v_原班级ID
FROM 学生 WHERE 学生ID = p_学生ID;

-- 更新班级
UPDATE 学生 SET 班级ID = p_新班级ID
WHERE 学生ID = p_学生ID;

COMMIT;
DBMS_OUTPUT.PUT_LINE('学生 ' || p_学生ID || ' 已从班级 ' || v_原班级ID || ' 转到 ' || p_新班级ID);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20003, '学生不存在');
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/

函数
计算学生总学分（通过课程）
CREATE OR REPLACE FUNCTION 计算学生总学分(
  p_学生ID NUMBER
) RETURN NUMBER
IS
v_总学分 NUMBER := 0;
BEGIN
SELECT SUM(学分) INTO v_总学分
FROM 学生选课 sc
JOIN 课程 k ON sc.课程ID = k.课程ID
WHERE sc.学生ID = p_学生ID
AND sc.成绩 >= 60;  -- 成绩合格

RETURN NVL(v_总学分, 0);
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/

检查教师是否存在
CREATE OR REPLACE FUNCTION 教师存在(
  p_教师ID NUMBER
) RETURN BOOLEAN
IS
v_计数 NUMBER;
BEGIN
SELECT COUNT(*) INTO v_计数
FROM 教师
WHERE 教师ID = p_教师ID;

RETURN (v_计数 > 0);
END;
/

-- 3. 获取班级平均成绩
CREATE OR REPLACE FUNCTION 获取班级平均成绩(
  p_班级ID NUMBER
) RETURN NUMBER
IS
v_平均成绩 NUMBER;
BEGIN
SELECT AVG(sc.成绩) INTO v_平均成绩
FROM 学生选课 sc
JOIN 学生 s ON sc.学生ID = s.学生ID
WHERE s.班级ID = p_班级ID;

RETURN ROUND(NVL(v_平均成绩, 0), 2);
END;
/

生成学生成绩单(JSON格式)
CREATE OR REPLACE FUNCTION 生成成绩单(
  p_学生ID NUMBER
) RETURN CLOB
IS
v_json CLOB;
BEGIN
SELECT JSON_OBJECT(
  'studentId' VALUE s.学生ID,
  'studentName' VALUE s.姓名,
  'className' VALUE c.班级名称,
'major' VALUE m.专业名称,
'totalCredits' VALUE 计算学生总学分(p_学生ID),
'courses' VALUE (
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
'courseId' VALUE sc.课程ID,
'courseName' VALUE k.课程名称,
'credit' VALUE k.学分,
'score' VALUE sc.成绩,
'semester' VALUE sc.学期
)
)
FROM 学生选课 sc
JOIN 课程 k ON sc.课程ID = k.课程ID
WHERE sc.学生ID = s.学生ID
)
)
INTO v_json
FROM 学生 s
JOIN 班级 c ON s.班级ID = c.班级ID
JOIN 专业 m ON c.专业ID = m.专业ID
WHERE s.学生ID = p_学生ID;

RETURN v_json;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN JSON_OBJECT('error' VALUE '学生不存在');
WHEN OTHERS THEN
RETURN JSON_OBJECT('error' VALUE SQLERRM);
END;
/

验证用户凭据
CREATE OR REPLACE FUNCTION 验证用户(
p_用户名 VARCHAR2,
p_密码 VARCHAR2
) RETURN BOOLEAN
IS
v_计数 NUMBER;
BEGIN
SELECT COUNT(*) INTO v_计数
FROM 系统用户
WHERE 用户名 = p_用户名
AND 密码 = p_密码;  -- 实际应用中密码应加密比较

RETURN (v_计数 = 1);
END;
/

3.5. 3.5**创建触发器**

主键生成触发器：在INSERT操作之前，自动生成主键ID的触发器，自动从对应序列获取下一个值，将序列值赋给新记录的主键ID字段
CREATE OR REPLACE TRIGGER 班级_BI
BEFORE INSERT ON 班级
FOR EACH ROW
BEGIN
SELECT 班级_SEQ.NEXTVAL INTO :NEW.班级ID FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER 教师_BI
BEFORE INSERT ON 教师
FOR EACH ROW
BEGIN
SELECT 教师_SEQ.NEXTVAL INTO :NEW.教师ID FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER 课程_BI
BEFORE INSERT ON 课程
FOR EACH ROW
BEGIN
SELECT 课程_SEQ.NEXTVAL INTO :NEW.课程ID FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER 学生_BI
BEFORE INSERT ON 学生
FOR EACH ROW
BEGIN
SELECT 学生_SEQ.NEXTVAL INTO :NEW.学生ID FROM DUAL;
END;
/

CREATE OR REPLACE TRIGGER 专业_BI
BEFORE INSERT ON 专业
FOR EACH ROW
BEGIN
SELECT 专业_SEQ.NEXTVAL INTO :NEW.专业ID FROM DUAL;
END;
/

班级人数更新触发器：学生记录插入、删除或班级ID更新(AFTER/INSERT/DELETE/UPDATE)，保持班级表中"人数"字段的实时准确性
CREATE OR REPLACE TRIGGER 班级人数更新
AFTER INSERT OR DELETE OR UPDATE OF 班级ID ON 学生
FOR EACH ROW
BEGIN
-- 新增学生：班级人数+1
IF INSERTING THEN
UPDATE 班级 SET 人数 = 人数 + 1 WHERE 班级ID = :NEW.班级ID;

-- 删除学生：班级人数-1
ELSIF DELETING THEN
UPDATE 班级 SET 人数 = 人数 - 1 WHERE 班级ID = :OLD.班级ID;

-- 学生转班：原班级-1，新班级+1
ELSIF UPDATING('班级ID') AND :OLD.班级ID <> :NEW.班级ID THEN
UPDATE 班级 SET 人数 = 人数 - 1 WHERE 班级ID = :OLD.班级ID;
UPDATE 班级 SET 人数 = 人数 + 1 WHERE 班级ID = :NEW.班级ID;
END IF;
END;
/

专业人数更新触发器：班级记录插入、删除或专业ID/人数更新后，实现三级数据一致性（学生→班级→专业）
CREATE OR REPLACE TRIGGER 专业人数更新
AFTER INSERT OR DELETE OR UPDATE OF 专业ID ON 班级
FOR EACH ROW
DECLARE
人数变化NUMBER;
BEGIN
-- 新增班级：专业人数增加（新班级初始人数）
IF INSERTING THEN
UPDATE 专业 SET 人数 = 人数 + :NEW.人数 WHERE 专业ID = :NEW.专业ID;

-- 删除班级：专业人数减少（被删班级最后人数）
ELSIF DELETING THEN
UPDATE 专业 SET 人数 = 人数 - :OLD.人数 WHERE 专业ID = :OLD.专业ID;

-- 班级转专业：原专业减少，新专业增加
ELSIF UPDATING('专业ID') AND :OLD.专业ID <> :NEW.专业ID THEN
UPDATE 专业 SET 人数 = 人数 - :OLD.人数 WHERE 专业ID = :OLD.专业ID;
UPDATE 专业 SET 人数 = 人数 + :NEW.人数 WHERE 专业ID = :NEW.专业ID;

-- 班级人数变化：同步更新专业人数
ELSIF UPDATING('人数') THEN
人数变化:= :NEW.人数 - :OLD.人数;
UPDATE 专业 SET 人数 = 人数 + 人数变化 WHERE 专业ID = :NEW.专业ID;
END IF;
END;
/

3.6. 3.6 数据的操纵

数据插入
-- 1. 插入专业数据
INSERT INTO 专业 (专业ID, 专业名称) VALUES (专业_SEQ.NEXTVAL, '计算机科学与技术');
INSERT INTO 专业 (专业ID, 专业名称) VALUES (专业_SEQ.NEXTVAL, '软件工程');
INSERT INTO 专业 (专业ID, 专业名称) VALUES (专业_SEQ.NEXTVAL, '人工智能');
INSERT INTO 专业 (专业ID, 专业名称) VALUES (专业_SEQ.NEXTVAL, '数据科学与大数据技术');
COMMIT;

-- 2. 插入班级数据
-- 计算机专业班级
INSERT INTO 班级 (班级ID, 班级名称, 专业ID) VALUES (班级_SEQ.NEXTVAL, '计科1班', 1);
INSERT INTO 班级 (班级ID, 班级名称, 专业ID) VALUES (班级_SEQ.NEXTVAL, '计科2班', 1);
-- 软件工程专业班级
INSERT INTO 班级 (班级ID, 班级名称, 专业ID) VALUES (班级_SEQ.NEXTVAL, '软工1班', 2);
INSERT INTO 班级 (班级ID, 班级名称, 专业ID) VALUES (班级_SEQ.NEXTVAL, '软工2班', 2);
-- 人工智能专业班级
INSERT INTO 班级 (班级ID, 班级名称, 专业ID) VALUES (班级_SEQ.NEXTVAL, '智能1班', 3);
-- 大数据专业班级
INSERT INTO 班级 (班级ID, 班级名称, 专业ID) VALUES (班级_SEQ.NEXTVAL, '数据1班', 4);
COMMIT;

-- 3. 插入教师数据
INSERT INTO 教师 (教师ID, 姓名, 职称, 联系电话, 邮箱) 
VALUES (教师_SEQ.NEXTVAL, '张教授', '教授', '13800138001', 'zhang@edu.cn');

INSERT INTO 教师 (教师ID, 姓名, 职称, 联系电话, 邮箱) 
VALUES (教师_SEQ.NEXTVAL, '李副教授', '副教授', '13900139002', 'li@edu.cn');

INSERT INTO 教师 (教师ID, 姓名, 职称, 联系电话, 邮箱) 
VALUES (教师_SEQ.NEXTVAL, '王老师', '讲师', '13700137003', 'wang@edu.cn');

INSERT INTO 教师 (教师ID, 姓名, 职称, 联系电话, 邮箱) 
VALUES (教师_SEQ.NEXTVAL, '赵教授', '教授', '13600136004', 'zhao@edu.cn');
COMMIT;

-- 4. 插入学生数据
-- 计科1班学生
INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '张三', '110101200001011234', '男', DATE '2000-01-01', DATE '2023-09-01', '13800000001', 'zhangsan@stu.edu.cn', 1);

INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '李四', '110102200002022345', '女', DATE '2000-02-02', DATE '2023-09-01', '13800000002', 'lisi@stu.edu.cn', 1);

-- 计科2班学生
INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '王五', '110103200003033456', '男', DATE '2000-03-03', DATE '2023-09-01', '13800000003', 'wangwu@stu.edu.cn', 2);

-- 软工1班学生
INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '赵六', '110104200004044567', '女', DATE '2000-04-04', DATE '2023-09-01', '13800000004', 'zhaoliu@stu.edu.cn', 3);

-- 软工2班学生
INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '钱七', '110105200005055678', '男', DATE '2000-05-05', DATE '2023-09-01', '13800000005', 'qianqi@stu.edu.cn', 4);

-- 智能1班学生
INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '孙八', '110106200006066789', '女', DATE '2000-06-06', DATE '2023-09-01', '13800000006', 'sunba@stu.edu.cn', 5);

-- 数据1班学生
INSERT INTO 学生 (学生ID, 姓名, 身份证号, 性别, 出生日期, 入学日期, 联系电话, 邮箱, 班级ID)
VALUES (学生_SEQ.NEXTVAL, '周九', '110107200007077890', '男', DATE '2000-07-07', DATE '2023-09-01', '13800000007', 'zhoujiu@stu.edu.cn', 6);
COMMIT;

-- 5. 插入课程数据
INSERT INTO 课程 (课程ID, 课程名称, 学时, 考核方式, 学分, 教授教师ID, 课程描述)
VALUES (课程_SEQ.NEXTVAL, '数据库系统', 64, '考试', 4.0, 1, '关系数据库原理与设计');

INSERT INTO 课程 (课程ID, 课程名称, 学时, 考核方式, 学分, 教授教师ID, 课程描述)
VALUES (课程_SEQ.NEXTVAL, '数据结构', 64, '考试', 4.0, 2, '基本数据结构与算法');

INSERT INTO 课程 (课程ID, 课程名称, 学时, 考核方式, 学分, 教授教师ID, 课程描述)
VALUES (课程_SEQ.NEXTVAL, '人工智能导论', 48, '考查', 3.0, 3, '人工智能基本原理与应用');

INSERT INTO 课程 (课程ID, 课程名称, 学时, 考核方式, 学分, 教授教师ID, 课程描述)
VALUES (课程_SEQ.NEXTVAL, '机器学习', 48, '考试', 3.0, 4, '机器学习基本算法与实践');
COMMIT;

-- 6. 插入学生选课数据
-- 张三选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (1, 1, 85.5, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (1, 2, 92.0, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (1, 3, 78.0, '2024春季');

-- 李四选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (2, 1, 90.5, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (2, 4, 88.0, '2024春季');

-- 王五选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (3, 2, 76.5, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (3, 3, 92.5, '2024春季');

-- 赵六选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (4, 1, 82.0, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (4, 4, 95.0, '2024春季');

-- 钱七选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (5, 3, 79.5, '2024春季');

-- 孙八选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (6, 2, 87.0, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (6, 4, 91.5, '2024春季');

-- 周九选课
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (7, 1, 94.5, '2023秋季');
INSERT INTO 学生选课 (学生ID, 课程ID, 成绩, 学期) VALUES (7, 3, 85.0, '2024春季');
COMMIT;

-- 7. 插入管理员数据
INSERT INTO 管理员 (管理员ID, 姓名, 工号, 联系电话, 邮箱) 
VALUES (管理员_SEQ.NEXTVAL, '张校长', 'ADM001', '13888888888', 'headmaster@edu.cn');

INSERT INTO 管理员 (管理员ID, 姓名, 工号, 联系电话, 邮箱) 
VALUES (管理员_SEQ.NEXTVAL, '李主任', 'ADM002', '13999999999', 'director@edu.cn');
COMMIT;

-- 8. 插入系统用户数据
-- 学生用户
INSERT INTO 系统用户 (用户名, 密码, 用户类型, 关联学生ID) 
VALUES ('zhangsan', 'student_pass1', '学生', 1);

INSERT INTO 系统用户 (用户名, 密码, 用户类型, 关联学生ID) 
VALUES ('lisi', 'student_pass2', '学生', 2);

-- 教师用户
INSERT INTO 系统用户 (用户名, 密码, 用户类型, 关联教师ID) 
VALUES ('prof_zhang', 'teacher_pass1', '教师', 1);

INSERT INTO 系统用户 (用户名, 密码, 用户类型, 关联教师ID) 
VALUES ('prof_li', 'teacher_pass2', '教师', 2);

-- 管理员用户
INSERT INTO 系统用户 (用户名, 密码, 用户类型, 关联管理员ID) 
VALUES ('admin_zhang', 'admin_pass1', '管理员', 1);

INSERT INTO 系统用户 (用户名, 密码, 用户类型, 关联管理员ID) 
VALUES ('admin_li', 'admin_pass2', '管理员', 2);
COMMIT;

数据更新
-- 1. 更新专业人数
UPDATE 专业 SET 人数 = (SELECT SUM(人数) FROM 班级 WHERE 专业ID = 1) WHERE 专业ID = 1;
UPDATE 专业 SET 人数 = (SELECT SUM(人数) FROM 班级 WHERE 专业ID = 2) WHERE 专业ID = 2;
UPDATE 专业 SET 人数 = (SELECT SUM(人数) FROM 班级 WHERE 专业ID = 3) WHERE 专业ID = 3;
UPDATE 专业 SET 人数 = (SELECT SUM(人数) FROM 班级 WHERE 专业ID = 4) WHERE 专业ID = 4;
COMMIT;

-- 2. 更新学生信息
UPDATE 学生 SET 邮箱 = 'zhangsan_updated@stu.edu.cn' WHERE 学生ID = 1;
UPDATE 学生 SET 联系电话 = '13800001111' WHERE 姓名 = '李四';
COMMIT;

-- 3. 更新课程信息
UPDATE 课程 SET 学时 = 72 WHERE 课程名称 = '数据库系统';
UPDATE 课程 SET 学分 = 4.5 WHERE 课程ID = 2;
COMMIT;

-- 4. 更新成绩
UPDATE 学生选课 SET 成绩 = 88.5 
WHERE 学生ID = (SELECT 学生ID FROM 学生 WHERE 姓名 = '张三') 
AND 课程ID = (SELECT 课程ID FROM 课程 WHERE 课程名称 = '数据库系统');
COMMIT;

数据删除
-- 1. 删除无效选课记录（成绩低于60分）
DELETE FROM 学生选课 WHERE 成绩 < 60;
-- 示例：假设王五的数据结构成绩低于60
DELETE FROM 学生选课 
WHERE 学生ID = (SELECT 学生ID FROM 学生 WHERE 姓名 = '王五') 
AND 课程ID = (SELECT 课程ID FROM 课程 WHERE 课程名称 = '数据结构');
COMMIT;

-- 2. 删除未分配课程的教师（演示用，实际无此类数据）
-- DELETE FROM 教师 
-- WHERE 教师ID NOT IN (SELECT 教授教师ID FROM 课程);

数据查询
-- 1. 查询所有学生及其班级信息
SELECT s.学生ID, s.姓名, s.性别, c.班级名称, m.专业名称
FROM 学生 s
JOIN 班级 c ON s.班级ID = c.班级ID
JOIN 专业 m ON c.专业ID = m.专业ID;

-- 2. 查询课程及授课教师信息
SELECT k.课程ID, k.课程名称, k.学分, t.姓名 AS 授课教师, t.职称
FROM 课程 k
JOIN 教师 t ON k.教授教师ID = t.教师ID;

-- 3. 查询学生成绩单（张三）
SELECT s.姓名, k.课程名称, sc.成绩, sc.学期, t.姓名 AS 授课教师
FROM 学生选课 sc
JOIN 学生 s ON sc.学生ID = s.学生ID
JOIN 课程 k ON sc.课程ID = k.课程ID
JOIN 教师 t ON k.教授教师ID = t.教师ID
WHERE s.姓名 = '张三';

-- 4. 查询班级平均成绩
SELECT c.班级名称, ROUND(AVG(sc.成绩), 2) AS 平均成绩
FROM 学生选课 sc
JOIN 学生 s ON sc.学生ID = s.学生ID
JOIN 班级 c ON s.班级ID = c.班级ID
GROUP BY c.班级名称;

-- 5. 查询教师授课情况
SELECT t.姓名, t.职称, COUNT(k.课程ID) AS 授课课程数
FROM 教师 t
LEFT JOIN 课程 k ON t.教师ID = k.教授教师ID
GROUP BY t.姓名, t.职称;

-- 6. 查询系统用户
SELECT u.用户名, u.用户类型, 
COALESCE(s.姓名, t.姓名, a.姓名) AS 姓名
FROM 系统用户 u
LEFT JOIN 学生 s ON u.关联学生ID = s.学生ID AND u.用户类型 = '学生'
LEFT JOIN 教师 t ON u.关联教师ID = t.教师ID AND u.用户类型 = '教师'
LEFT JOIN 管理员 a ON u.关联管理员ID = a.管理员ID AND u.用户类型 = '管理员';

-- 7. 查询专业人数统计
SELECT m.专业名称, SUM(c.人数) AS 学生总数
FROM 专业 m
JOIN 班级 c ON m.专业ID = c.专业ID
GROUP BY m.专业名称;

-- 8. 查询学分超过3.5的课程
SELECT 课程名称, 学分, 考核方式 
FROM 课程 
WHERE 学分 > 3.5;

-- 9. 查询成绩优秀的学生（平均分>90）
SELECT s.学生ID, s.姓名, ROUND(AVG(sc.成绩), 2) AS 平均分
FROM 学生 s
JOIN 学生选课 sc ON s.学生ID = sc.学生ID
GROUP BY s.学生ID, s.姓名
HAVING AVG(sc.成绩) > 90;

-- 10. 查询2023秋季学期课程选修情况
SELECT k.课程名称, COUNT(sc.学生ID) AS 选课人数
FROM 课程 k
LEFT JOIN 学生选课 sc ON k.课程ID = sc.课程ID AND sc.学期 = '2023秋季'
GROUP BY k.课程名称;
/

学生信息管理系统.zip