编辑代码

-- 一、表创建及完整性约束
-- 1. 病人信息表（Sick）
CREATE TABLE Sick (
    SNo VARCHAR(10) NOT NULL PRIMARY KEY,
    SName VARCHAR(10) NOT NULL,
    SAge INT NOT NULL,
    SSex VARCHAR(2) NOT NULL,
    DNo VARCHAR(12) NOT NULL,
    RNo VARCHAR(4) NOT NULL,
    CONSTRAINT FK_Sick_Doctor FOREIGN KEY (DNo) REFERENCES Doctor(DNb),
    CONSTRAINT FK_Sick_Room FOREIGN KEY (RNo) REFERENCES Room(RNo)
);

-- 2. 医生信息表（Doctor）
CREATE TABLE Doctor (
    DNo VARCHAR(12) NOT NULL PRIMARY KEY,
    DName VARCHAR(20) NOT NULL,
    DAge INT NOT NULL,
    DDept VARCHAR(20) NOT NULL,
    DDuty VARCHAR(10) NOT NULL,
    CONSTRAINT FK_Doctor_Office FOREIGN KEY (DDept) REFERENCES Office(OName)
);

-- 3. 病房信息表（Room）
CREATE TABLE Room (
    RNo VARCHAR(4) NOT NULL PRIMARY KEY,
    RBed VARCHAR(4) NOT NULL,
    OName VARCHAR(20) NOT NULL,
    CONSTRAINT FK_Room_Office FOREIGN KEY (OName) REFERENCES Office(OName)
);

-- 4. 科室信息表（Office）
CREATE TABLE Office (
    OName VARCHAR(20) NOT NULL PRIMARY KEY,
    OAdd VARCHAR(50) NOT NULL,
    OTel VARCHAR(20) NOT NULL UNIQUE
);

-- 二、视图创建
-- 1. 内科医生信息视图（V1）
CREATE VIEW V1 AS
SELECT DNo, DName, DAge, DDuty
FROM Doctor
WHERE OName = '内科';

-- 2. 内科病人信息视图（V2）
CREATE VIEW V2 AS
SELECT SNo, SName, SSex
FROM Sick
WHERE OName = '内科' AND Sick.DNo = Doctor.DNo;

-- 三、索引创建
-- 1. 医生表按年龄唯一索引
CREATE UNIQUE INDEX idx_age ON Doctor(DAge);

-- 2. 病人表按年龄索引
CREATE INDEX idx_sick_age ON Sick(SAge);

-- 四、存储过程创建
-- 1. 根据病人编号查询病人信息存储过程（bingren）
CREATE PROCEDURE bingren
    @SNo VARCHAR(12)
AS
BEGIN
    SELECT *
    FROM Sick
    WHERE Sick.SNo = @SNo;
END;

-- 2. 根据医生编号查询医生信息存储过程（yisheng）
CREATE PROCEDURE yisheng
    @DNo VARCHAR(4)
AS
BEGIN
    SELECT *
    FROM Doctor
    WHERE Doctor.DNo = @DNo;
END;

-- 五、功能调试相关（增删改查）
-- （一）病人管理模块
-- 1. 查询指定病人信息（以 SNo='002' 为例 ）
SELECT *
FROM Sick
WHERE Sick.SNo = '002';

-- 2. 查询病人所在病房信息（关联 Sick 和 Room 表，以 Sick.RNo='90' 为例 ）
SELECT Room.RNo, Room.RBed, Room.OName
FROM Room
JOIN Sick ON Room.RNo = Sick.RNo
WHERE Sick.RNo = '90';

-- 3. 查询病人所属医生信息（关联 Sick 和 Doctor 表，以 Sick.DNo='160018' 为例 ）
SELECT Doctor.DNo, Doctor.DName, Doctor.DAge, Doctor.OName, Doctor.DDuty
FROM Doctor
JOIN Sick ON Doctor.DNo = Sick.DNo
WHERE Sick.DNo = '160018';

-- 4. 插入病人信息
INSERT INTO Sick (SNo, SName, SAge, SSex, DNo, RNo)
VALUES ('003', '包胜慧', 30, '男', '16018', '90');

-- 5. 删除病人信息（以 SNo='001' 为例 ）
DELETE FROM Sick
WHERE Sick.SNo = '001';

-- 6. 修改病人信息（将 SName 为 '曹林升' 的姓名修改，这里示例改回，实际按需调整 ）
UPDATE Sick
SET SName = '曹林升'
WHERE SName = '曹林丹';

-- （二）医生管理模块
-- 1. 查询医生信息
SELECT *
FROM Doctor;

-- 2. 插入医生信息
INSERT INTO Doctor (DNo, DName, DAge, OName, DDuty)
VALUES ('160030', '陈民安', 29, '内科', '主任医师');

-- 3. 删除医生信息（以 DNo='160030' 为例 ）
DELETE FROM Doctor
WHERE Doctor.DNo = '160030';

-- 4. 修改医生信息（以修改年龄为例，将 DNo='160018' 对应的 DAge 调整 ，实际按需改 ）
UPDATE Doctor
SET DAge = DAge + 1
WHERE Doctor.DNo = '160018';

-- （三）病房管理相关（示例操作）
-- 1. 插入病房信息
INSERT INTO Room (RNo, RBed, OName)
VALUES ('901', '9001', '内科');

-- 2. 删除病房信息（以 RNo='901' 为例 ）
DELETE FROM Room
WHERE Room.RNo = '901';

-- 3. 修改病房信息（以修改 RBed 为例，将 RNo='90' 对应的 RBed 调整 ）
UPDATE Room
SET RBed = '9001'
WHERE Room.RNo = '90';

-- （四）科室管理相关（示例操作）
-- 1. 插入科室信息
INSERT INTO Office (OName, OAdd, OTel)
VALUES ('精神科', '住院楼 5 楼', '85829811');

-- 2. 删除科室信息（以 OName='精神科' 为例 ，注意先处理关联表数据 ）
DELETE FROM Office
WHERE Office.OName = '精神科';

-- 3. 修改科室信息（以修改 OAdd 为例，将 OName='内科' 对应的 OAdd 调整 ）
UPDATE Office
SET OAdd = '康复楼 5 楼'
WHERE Office.OName = '内科';