编辑代码

-- 创建数据库
DROP DATABASE IF EXISTS HospitalDB;
CREATE DATABASE HospitalDB;
USE HospitalDB;

-- 1. 创建Doctor表（首先创建，因为其他表依赖它）
CREATE TABLE Doctor (
    employeeID INT PRIMARY KEY,
    aphraRegNo VARCHAR(50) UNIQUE,
    employeeName VARCHAR(100) NOT NULL,
    specialisation VARCHAR(50)
);

-- 2. 创建Patient表
CREATE TABLE Patient (
    patientID INT PRIMARY KEY,
    firstName VARCHAR(50) NOT NULL,
    surname VARCHAR(50) NOT NULL,
    dateOfBirth DATE,
    gender CHAR(1),
    patientAddress VARCHAR(100),
    phoneNo VARCHAR(20) CHECK (phoneNo REGEXP '^[0-9]+$'),
    emailAddress VARCHAR(100),
    attendingDoctor INT,
    FOREIGN KEY (attendingDoctor) REFERENCES Doctor(employeeID)
);

-- 3. 创建Appointment表
CREATE TABLE Appointment (
    appointmentID INT PRIMARY KEY,
    patientID INT,
    doctorID INT,
    appointmentTime DATETIME,
    FOREIGN KEY (patientID) REFERENCES Patient(patientID),
    FOREIGN KEY (doctorID) REFERENCES Doctor(employeeID),
    UNIQUE (patientID, doctorID, appointmentTime)
);

-- 4. 创建Medication表
CREATE TABLE Medication (
    medicationID INT PRIMARY KEY,
    medicationName VARCHAR(100) NOT NULL,
    dosage VARCHAR(50),
    dispensation ENUM('非处方药', '处方药')
);

-- 5. 创建Prescription表
CREATE TABLE Prescription (
    prescriptionID INT PRIMARY KEY,
    prescribedTo INT,
    prescribedBy INT,
    medicationID INT,
    administrationFrequency VARCHAR(50),
    totalRepeats INT CHECK (totalRepeats BETWEEN 0 AND 5),
    remainingRepeats INT CHECK (remainingRepeats BETWEEN 0 AND 5),
    FOREIGN KEY (prescribedTo) REFERENCES Patient(patientID),
    FOREIGN KEY (prescribedBy) REFERENCES Doctor(employeeID),
    FOREIGN KEY (medicationID) REFERENCES Medication(medicationID)
);

-- 6. 插入医生数据
INSERT INTO Doctor (employeeID, aphraRegNo, employeeName, specialisation) VALUES 
(101, 'A12345', '李四', '内科'),
(102, 'A67890', '赵六', '外科'),
(103, 'B24680', '王医生', '儿科'),
(104, 'C13579', '张医生', '骨科');

-- 7. 插入患者数据
INSERT INTO Patient (patientID, firstName, surname, dateOfBirth, gender, patientAddress, phoneNo, emailAddress, attendingDoctor) VALUES 
(1, '张三', '张', '1990-01-01', 'M', '北京市朝阳区', '13800138000', 'zhangsan@example.com', 101),
(2, '王五', '王', '1995-01-01', 'M', '北京市海淀区', '13900139000', 'wangwu@example.com', 102),
(3, '李华', '李', '1985-05-15', 'F', '上海市浦东新区', '13700137000', 'lihua@example.com', 103),
(4, '赵敏', '赵', '1992-11-20', 'F', '广州市天河区', '13600136000', 'zhaomin@example.com', 104);

-- 8. 插入药品数据
INSERT INTO Medication (medicationID, medicationName, dosage, dispensation) VALUES 
(1001, '阿司匹林', '100mg', '处方药'),
(1002, '布洛芬', '200mg', '非处方药'),
(1003, '头孢氨苄', '250mg', '处方药'),
(1004, '维生素C', '500mg', '非处方药');

-- 9. 插入处方数据
INSERT INTO Prescription (prescriptionID, prescribedTo, prescribedBy, medicationID, administrationFrequency, totalRepeats, remainingRepeats) VALUES 
(1001, 1, 101, 1001, '每日三次', 3, 3),
(1002, 2, 102, 1002, '每日两次', 2, 2),
(1003, 3, 103, 1003, '每日一次', 1, 1),
(1004, 4, 104, 1004, '每日一次', 5, 5);

-- 10. 插入预约数据
INSERT INTO Appointment (appointmentID, patientID, doctorID, appointmentTime) VALUES 
(1, 1, 101, '2023-07-01 10:00:00'),
(2, 2, 102, '2023-07-02 11:00:00'),
(3, 3, 103, '2023-07-03 14:00:00'),
(4, 4, 104, '2023-07-04 09:30:00');

-- 11. 创建视图
CREATE VIEW PatientPrescriptionView AS
SELECT 
    P.patientID,
    P.firstName,
    P.surname,
    P.dateOfBirth,
    P.gender,
    D.employeeName AS doctorName,
    M.medicationName,
    M.dosage,
    PX.administrationFrequency,
    PX.totalRepeats,
    PX.remainingRepeats
FROM 
    Patient P
JOIN 
    Prescription PX ON P.patientID = PX.prescribedTo
JOIN 
    Medication M ON PX.medicationID = M.medicationID
JOIN
    Doctor D ON PX.prescribedBy = D.employeeID;

-- 12. 创建用户和权限（确保以root用户执行这部分）
-- 先检查是否以root登录，然后执行：
SET @current_user = CURRENT_USER();
SELECT CONCAT('当前用户: ', @current_user) AS UserCheck;

-- 创建用户（如果不存在）
CREATE USER IF NOT EXISTS 'admin'@'localhost' IDENTIFIED BY 'Admin@123';
CREATE USER IF NOT EXISTS 'doctor'@'localhost' IDENTIFIED BY 'Doctor@123';
CREATE USER IF NOT EXISTS 'pharmacist'@'localhost' IDENTIFIED BY 'Pharma@123';
CREATE USER IF NOT EXISTS 'receptionist'@'localhost' IDENTIFIED BY 'Recept@123';

-- 刷新权限
FLUSH PRIVILEGES;

-- 授予管理员权限
GRANT ALL PRIVILEGES ON HospitalDB.* TO 'admin'@'localhost';

-- 授予医生权限
GRANT SELECT ON HospitalDB.Patient TO 'doctor'@'localhost';
GRANT SELECT ON HospitalDB.Medication TO 'doctor'@'localhost';
GRANT SELECT ON HospitalDB.PatientPrescriptionView TO 'doctor'@'localhost';
GRANT INSERT, UPDATE, DELETE ON HospitalDB.Prescription TO 'doctor'@'localhost';
GRANT UPDATE ON HospitalDB.Doctor TO 'doctor'@'localhost';

-- 授予药剂师权限
GRANT SELECT, INSERT, UPDATE ON HospitalDB.Medication TO 'pharmacist'@'localhost';
GRANT SELECT, INSERT, UPDATE ON HospitalDB.Prescription TO 'pharmacist'@'localhost';
GRANT SELECT ON HospitalDB.Patient TO 'pharmacist'@'localhost';
GRANT SELECT ON HospitalDB.PatientPrescriptionView TO 'pharmacist'@'localhost';

-- 授予接待员权限
GRANT SELECT, INSERT, UPDATE, DELETE ON HospitalDB.Appointment TO 'receptionist'@'localhost';
GRANT SELECT, INSERT, UPDATE ON HospitalDB.Patient TO 'receptionist'@'localhost';

-- 最后刷新权限
FLUSH PRIVILEGES;

-- 验证语句（可选）
SELECT '数据库创建完成，权限设置成功！' AS Message;