MySQL数据库基础练习系列46、积分管理系统

-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,                     -- 用户ID
    username VARCHAR(50) NOT NULL,                               -- 用户名
    password VARCHAR(255) NOT NULL,                             -- 密码
    name VARCHAR(100) NOT NULL,                                 -- 姓名
    email VARCHAR(100),                                         -- 邮箱
    gender ENUM('男', '女') NOT NULL,                          -- 性别
    phone VARCHAR(20),                                          -- 电话号码
    integral INT DEFAULT 0 NOT NULL COMMENT '用户当前积分'     -- 用户当前积分
);


-- 积分类型表
CREATE TABLE IntegralTypes (
    type_id INT AUTO_INCREMENT PRIMARY KEY,                     -- 积分类型ID
    type_name VARCHAR(50) NOT NULL,                             -- 积分类型名称
    description VARCHAR(255) COMMENT '积分类型描述'             -- 积分类型描述
);


-- 积分赚取规则表
CREATE TABLE EarningRules (
    rule_id INT AUTO_INCREMENT PRIMARY KEY,                     -- 赚取规则ID
    type_id INT NOT NULL,                                       -- 积分类型ID
    action_name VARCHAR(100) NOT NULL,                         -- 赚取行为名称
    integral_amount INT NOT NULL,                               -- 赚取积分数量
    FOREIGN KEY (type_id) REFERENCES IntegralTypes(type_id)
);


-- 积分交易记录表
CREATE TABLE Transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,             -- 交易记录ID
    user_id INT NOT NULL,                                       -- 用户ID
    type_id INT NOT NULL,                                       -- 积分类型ID
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     -- 交易日期
    integral_change INT NOT NULL COMMENT '积分变化量',         -- 积分变化量，可以为正数（增加）或负数（减少）
    description VARCHAR(255) COMMENT '交易描述',               -- 交易描述
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (type_id) REFERENCES IntegralTypes(type_id)
);


-- 积分使用记录表（可选，记录用户具体如何使用积分）
CREATE TABLE UsageRecords (
    record_id INT AUTO_INCREMENT PRIMARY KEY,                  -- 使用记录ID
    transaction_id INT NOT NULL,                               -- 交易记录ID
    usage_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,            -- 使用日期
    usage_details VARCHAR(255) COMMENT '使用详情',             -- 使用详情
    FOREIGN KEY (transaction_id) REFERENCES Transactions(transaction_id)
);


-- 积分兑换商品表（可选，如果有商品兑换功能）
CREATE TABLE ExchangeItems (
    item_id INT AUTO_INCREMENT PRIMARY KEY,                    -- 商品ID
    item_name VARCHAR(100) NOT NULL,                          -- 商品名称
    description VARCHAR(255) COMMENT '商品描述',               -- 商品描述
    price_in_integral INT NOT NULL COMMENT '兑换所需积分'      -- 兑换所需积分
);


-- 积分兑换记录表（可选，记录用户兑换商品的记录）
CREATE TABLE ExchangeRecords (
    record_id INT AUTO_INCREMENT PRIMARY KEY,                  -- 兑换记录ID
    user_id INT NOT NULL,                                      -- 用户ID
    item_id INT NOT NULL,                                      -- 商品ID
    exchange_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,        -- 兑换日期
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (item_id) REFERENCES ExchangeItems(item_id)
);

-- 用户表
INSERT INTO Users (username, password, name, email, gender, phone, integral) VALUES
('孙悟空', '123', '孙悟空', 'sunwukong@example.com', '男', '13800138001', 1000),
('诸葛亮', '123', '诸葛亮', 'zhugeliang@example.com', '男', '13900139002', 1500),
('林黛玉', '123', '林黛玉', 'lindaiyu@example.com', '女', '13700137003', 800);


-- 积分类型表
INSERT INTO IntegralTypes (type_name, description) VALUES
('日常签到', '每天签到获得的积分'),
('任务完成', '完成指定任务获得的积分'),
('活动奖励', '参与活动获得的额外积分');


-- 积分赚取规则表
INSERT INTO EarningRules (type_id, action_name, integral_amount) VALUES
(1, '每日签到', 10), -- 假设日常签到每天赚取10积分
(2, '完成新手任务', 50), -- 假设完成新手任务赚取50积分
(2, '完成日常任务', 20), -- 假设完成日常任务赚取20积分
(3, '参与春节活动', 100); -- 假设参与春节活动赚取100积分


-- 积分交易记录表
INSERT INTO Transactions (user_id, type_id, transaction_date, integral_change, description) VALUES
(1, 1, NOW(), 10, '日常签到积分'), -- 孙悟空日常签到获得10积分
(2, 2, NOW(), 20, '完成日常任务积分'), -- 诸葛亮完成日常任务获得20积分
(3, 3, NOW(), 100, '春节活动奖励积分'); -- 林黛玉参与春节活动获得100积分


-- 积分使用记录表（可选）

-- 假设第一条交易记录是孙悟空使用积分购买虚拟商品
INSERT INTO UsageRecords (transaction_id, usage_date, usage_details) VALUES
(1, NOW(), '购买虚拟商品');


-- 积分兑换商品表（可选）
INSERT INTO ExchangeItems (item_name, description, price_in_integral) VALUES
('虚拟道具A', '游戏中的道具', 50),
('会员月卡', '网站会员权益', 200);


-- 积分兑换记录表（可选）

-- 假设诸葛亮使用积分兑换了虚拟道具A
INSERT INTO ExchangeRecords (user_id, item_id, exchange_date) VALUES
(2, 1, NOW());