编辑代码

-- 创建数据库  
CREATE DATABASE IF NOT EXISTS service_management;  

-- 使用数据库  
USE service_management;  

-- 创建用户表  
CREATE TABLE User (  
    UserID INT AUTO_INCREMENT PRIMARY KEY,  
    Name VARCHAR(255) NOT NULL,  
    Phone VARCHAR(20) NOT NULL,  
    RegisterTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP  
);  

-- 创建工单表  
CREATE TABLE WorkOrder (  
    WorkOrderID INT AUTO_INCREMENT PRIMARY KEY,  
    UserID INT NOT NULL,  
    ProblemCategory VARCHAR(255),  
    OrderNumber VARCHAR(50) NOT NULL,  
    CurrentStatus VARCHAR(50) NOT NULL,  
    UrgencyLevel ENUM('normal', 'urgent') NOT NULL DEFAULT 'normal',  
    CreatedTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    FOREIGN KEY (UserID) REFERENCES User(UserID)  
);  

-- 创建公告表  
CREATE TABLE Announcement (  
    AnnouncementID INT AUTO_INCREMENT PRIMARY KEY,  
    Title VARCHAR(255) NOT NULL,  
    Content TEXT NOT NULL,  
    ReleaseDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    PublisherID INT NOT NULL,  
    FOREIGN KEY (PublisherID) REFERENCES CustomerServiceStaff(StaffID)  
);  

-- 创建服务评价表  
CREATE TABLE ServiceEvaluation (  
    EvaluationID INT AUTO_INCREMENT PRIMARY KEY,  
    UserID INT NOT NULL,  
    WorkOrderID INT NOT NULL,  
    Score TINYINT NOT NULL CHECK (Score BETWEEN 1 AND 5),  
    EvaluationTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    FOREIGN KEY (UserID) REFERENCES User(UserID),  
    FOREIGN KEY (WorkOrderID) REFERENCES WorkOrder(WorkOrderID)  
);  

-- 创建咨询记录表  
CREATE TABLE ConsultationRecord (  
    RecordID INT AUTO_INCREMENT PRIMARY KEY,  
    UserID INT NOT NULL,  
    StaffID INT NOT NULL,  
    Content TEXT NOT NULL,  
    Time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    FOREIGN KEY (UserID) REFERENCES User(UserID),  
    FOREIGN KEY (StaffID) REFERENCES CustomerServiceStaff(StaffID)  
);  

-- 创建客服人员表  
CREATE TABLE CustomerServiceStaff (  
    StaffID INT AUTO_INCREMENT PRIMARY KEY,  
    Name VARCHAR(255) NOT NULL,  
    Position VARCHAR(255) NOT NULL,  
    Number VARCHAR(50) NOT NULL  
);  

-- 插入示例数据  
INSERT INTO User (Name, Phone, RegisterTime) VALUES  
    ('张三', '13812345678', '2024-01-01'),  
    ('李四', '13998765432', '2024-02-02');  

INSERT INTO WorkOrder (UserID, ProblemCategory, OrderNumber, CurrentStatus, UrgencyLevel)  
VALUES   
    (1, '咨询问题', 'WO20240101', '处理中', 'normal'),  
    (2, '技术问题', 'WO20240102', '已解决', 'urgent');  

INSERT INTO CustomerServiceStaff (Name, Position, Number)  
VALUES   
    ('王五', '客服主管', 'CS001'),  
    ('赵六', '客服专员', 'CS002');  

INSERT INTO Announcement (Title, Content, ReleaseDate, PublisherID)  
VALUES   
    ('重要公告：服务暂停通知', '因系统维护，部分服务将暂停。', '2024-03-01 10:00:00', 1);  

INSERT INTO ServiceEvaluation (UserID, WorkOrderID, Score)  
VALUES   
    (1, 1, 4),  
    (2, 2, 5);  

INSERT INTO ConsultationRecord (UserID, StaffID, Content)  
VALUES   
    (1, 1, '询问服务进度'),  
    (2, 2, '报告技术问题');  

-- 显示所有表的数据  
SELECT '用户表(User)' AS 表名称;  
SELECT * FROM User;  

SELECT '工单表(WorkOrder)' AS 表名称;  
SELECT * FROM WorkOrder;  

SELECT '公告表(Announcement)' AS 表名称;  
SELECT * FROM Announcement;  

SELECT '服务评价表(ServiceEvaluation)' AS 表名称;  
SELECT * FROM ServiceEvaluation;  

SELECT '咨询记录表(ConsultationRecord)' AS 表名称;  
SELECT * FROM ConsultationRecord;  

SELECT '客服人员表(CustomerServiceStaff)' AS 表名称;  
SELECT * FROM CustomerServiceStaff;