编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE admin (
    admin_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    pwd VARCHAR(32) NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE user (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    pwd VARCHAR(32) NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE session (
    session_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_content TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);
CREATE TABLE knowledge_base (
    kb_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    document_id INT NOT NULL,
    document_content TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);
CREATE TABLE document (
    document_id INT AUTO_INCREMENT PRIMARY KEY,
    document_name VARCHAR(100) NOT NULL,
    document_path VARCHAR(255) NOT NULL,
    document_size INT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE schedule (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    schedule_content TEXT,
    schedule_time DATETIME NOT NULL,
    reminder_status TINYINT(1) DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);
CREATE TABLE reminder (
    reminder_id INT AUTO_INCREMENT PRIMARY KEY,
    schedule_id INT NOT NULL,
    reminder_time DATETIME NOT NULL,
    reminder_content TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (schedule_id) REFERENCES schedule(schedule_id)
);
CREATE TABLE system_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action TEXT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);
INSERT INTO admin (username, pwd) VALUES
('admin1', 'password1'),
('admin2', 'password2'),
('admin3', 'password3'),
('admin4', 'password4'),
('admin5', 'password5');

INSERT INTO user (username, pwd) VALUES
('user1', 'password1'),
('user2', 'password2'),
('user3', 'password3'),
('user4', 'password4'),
('user5', 'password5'),
('user6', 'password6'),
('user7', 'password7'),
('user8', 'password8');

INSERT INTO user (username, pwd) VALUES
('user1', 'password1'),
('user2', 'password2'),
('user3', 'password3'),
('user4', 'password4'),
('user5', 'password5'),
('user6', 'password6'),
('user7', 'password7'),
('user8', 'password8');

INSERT INTO session (user_id, session_content) VALUES
(1, 'Hello, how are you?'),
(2, 'I am fine, thank you.'),
(3, 'What are you doing today?'),
(4, 'I am working on a project.'),
(5, 'That sounds interesting.'),
(6, 'Yes, it is.'),
(7, 'Can you help me with something?'),
(8, 'Sure, what do you need?');

INSERT INTO knowledge_base (user_id, document_id, document_content) VALUES
(1, 1, 'This is the content of document 1.'),
(2, 2, 'This is the content of document 2.'),
(3, 3, 'This is the content of document 3.'),
(4, 4, 'This is the content of document 4.'),
(5, 5, 'This is the content of document 5.');

INSERT INTO document (document_name, document_path, document_size) VALUES
('Document1.pdf', '/path/to/doc1.pdf', 1024),
('Document2.pdf', '/path/to/doc2.pdf', 2048),
('Document3.pdf', '/path/to/doc3.pdf', 3072),
('Document4.pdf', '/path/to/doc4.pdf', 4096),
('Document5.pdf', '/path/to/doc5.pdf', 5120);

INSERT INTO schedule (user_id, schedule_content, schedule_time, reminder_status) VALUES
(1, 'Meeting with team', '2025-06-01 10:00:00', 0),
(2, 'Doctor appointment', '2025-06-02 14:00:00', 0),
(3, 'Project deadline', '2025-06-03 18:00:00', 0),
(4, 'Lunch with friends', '2025-06-04 12:00:00', 0),
(5, 'Gym session', '2025-06-05 16:00:00', 0),
(6, 'Conference call', '2025-06-06 09:00:00', 0),
(7, 'Dinner with family', '2025-06-07 19:00:00', 0),
(8, 'Shopping', '2025-06-08 11:00:00', 0);

INSERT INTO reminder (schedule_id, reminder_time, reminder_content) VALUES
(1, '2025-06-01 09:00:00', 'Reminder for meeting with team'),
(2, '2025-06-02 13:00:00', 'Reminder for doctor appointment'),
(3, '2025-06-03 17:00:00', 'Reminder for project deadline'),
(4, '2025-06-04 11:00:00', 'Reminder for lunch with friends'),
(5, '2025-06-05 15:00:00', 'Reminder for gym session');

INSERT INTO system_log (user_id, action) VALUES
(1, 'Logged in'),
(2, 'Updated profile'),
(3, 'Created new schedule'),
(4, 'Deleted document'),
(5, 'Viewed knowledge base'),
(6, 'Added new reminder'),
(7, 'Changed password'),
(8, 'Logged out');