SQL多表数据关联与查询：构建高效用户与管理系统

-- 创建用户组表 (user_group)
CREATE TABLE user_group (
    id MEDIUMINT NOT NULL AUTO_INCREMENT, -- 主键，唯一标识用户组
    name VARCHAR(100) NOT NULL,          -- 用户组名称
    description VARCHAR(100) NOT NULL,   -- 用户组描述
    PRIMARY KEY (id)
);

-- 插入示例用户组数据
INSERT INTO user_group (name, description) VALUES('Super User', 'Complete Access');
INSERT INTO user_group (name, description) VALUES('Moderator', 'Limited Access');
INSERT INTO user_group (name, description) VALUES('Guest', 'Web Users');

-- 创建用户表 (users)
CREATE TABLE users (
    id MEDIUMINT NOT NULL AUTO_INCREMENT, -- 主键，唯一标识用户
    name VARCHAR(100) NOT NULL,          -- 用户名
    password VARCHAR(100) NOT NULL,      -- 用户密码
    user_group_id INT NOT NULL,          -- 外键，引用 user_group 表的 id
    PRIMARY KEY (id),
    FOREIGN KEY (user_group_id) REFERENCES user_group(id) -- 定义外键约束
);

-- 插入示例用户数据
INSERT INTO users (name, password, user_group_id) VALUES('god', 'aaa666', 1);
INSERT INTO users (name, password, user_group_id) VALUES('davo', 'xyx123', 2);
INSERT INTO users (name, password, user_group_id) VALUES('prole', 'abc101', 3);

SELECT
    u.name AS user_name,
    ug.name AS group_name,
    ug.description AS group_description
FROM
    users AS u
INNER JOIN
    user_group AS ug ON u.user_group_id = ug.id;

-- 用户表 (users_account)
CREATE TABLE users_account (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_fullname VARCHAR(255) NOT NULL,
    user_email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    facility VARCHAR(255),
    terms_and_conditions BOOLEAN DEFAULT FALSE,
    isApproved BOOLEAN DEFAULT FALSE, -- 用户是否被批准
    date_registered DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 超级管理员表 (super_admin)
CREATE TABLE super_admin (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

-- 审批日志表 (approval_logs)
CREATE TABLE approval_logs (
    log_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,              -- 外键，引用 users_account 表的 user_id
    admin_id INT NOT NULL,             -- 外键，引用 super_admin 表的 id
    approval_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users_account(user_id),
    FOREIGN KEY (admin_id) REFERENCES super_admin(id)
);

-- 假设管理员ID为1，用户ID为101被批准
-- 更新用户状态
UPDATE users_account SET isApproved = TRUE WHERE user_id = 101;

-- 记录审批日志
INSERT INTO approval_logs (user_id, admin_id) VALUES (101, 1);

SELECT
    ua.user_fullname,
    sa.username AS admin_username,
    al.approval_date
FROM
    approval_logs AS al
INNER JOIN
    users_account AS ua ON al.user_id = ua.user_id
INNER JOIN
    super_admin AS sa ON al.admin_id = sa.id
ORDER BY
    al.approval_date DESC;