MySQL数据库基础练习系列50、站点访问量统计系统

-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    email VARCHAR(255) NOT NULL UNIQUE COMMENT '邮箱地址',
    role ENUM('管理员', '普通用户') DEFAULT '普通用户' COMMENT '用户角色'
);


-- 访问记录表
CREATE TABLE VisitRecords (
    record_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '记录ID',
    user_id INT NOT NULL COMMENT '用户ID',
    visit_time DATETIME NOT NULL COMMENT '访问时间',
    page_url VARCHAR(255) NOT NULL COMMENT '页面URL',
    referrer VARCHAR(255) COMMENT '来源页面',
    ip_address VARCHAR(45) NOT NULL COMMENT 'IP地址',
    user_agent VARCHAR(255) NOT NULL COMMENT '用户代理',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);


-- 访问统计表（按日统计）
CREATE TABLE DailyStats (
    date DATE PRIMARY KEY COMMENT '日期',
    total_visits INT NOT NULL DEFAULT 0 COMMENT '总访问次数',
    unique_visitors INT NOT NULL DEFAULT 0 COMMENT '独立访客数',
    total_pages_viewed INT NOT NULL DEFAULT 0 COMMENT '总页面浏览量'
);


-- 访问统计表（按页面统计）
CREATE TABLE PageStats (
    page_url VARCHAR(255) PRIMARY KEY COMMENT '页面URL',
    total_views INT NOT NULL DEFAULT 0 COMMENT '页面总浏览次数'
);


-- 用户自定义统计条件表（可选）
CREATE TABLE CustomStatsConditions (
    condition_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '条件ID',
    user_id INT NOT NULL COMMENT '用户ID',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    page_url VARCHAR(255) COMMENT '页面URL',
    referrer VARCHAR(255) COMMENT '来源页面',
    ip_range VARCHAR(45) COMMENT 'IP范围',
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);


-- 自定义统计结果表（可选）
CREATE TABLE CustomStatsResults (
    result_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '结果ID',
    condition_id INT NOT NULL COMMENT '条件ID',
    total_visits INT NOT NULL COMMENT '总访问次数',
    unique_visitors INT NOT NULL COMMENT '独立访客数',
    total_pages_viewed INT NOT NULL COMMENT '总页面浏览量',
    FOREIGN KEY (condition_id) REFERENCES CustomStatsConditions(condition_id)
);

-- 插入用户表数据
INSERT INTO Users (username, password, name, email, role) VALUES
('至尊宝', '123', '孙悟空', 'sunwukong@example.com', '普通用户'),
('紫霞', '123', '紫霞仙子', 'zixia@example.com', '普通用户'),
('唐僧', '123', '唐三藏', 'tangseng@example.com', '管理员');


-- 插入访问记录表数据（确保user_id引用Users表中已存在的ID）
INSERT INTO VisitRecords (user_id, visit_time, page_url, referrer, ip_address, user_agent) VALUES
(1, '2023-04-01 10:00:00', 'http://example.com/home', 'http://google.com', '192.168.1.1', 'Mozilla/5.0'),
(2, '2023-04-01 11:30:00', 'http://example.com/about', '', '10.0.0.1', 'Chrome/80'),
(3, '2023-04-01 14:15:00', 'http://example.com/contact', 'http://example.com/home', '127.0.0.1', 'Safari/12');


-- 插入访问统计表（按日统计）数据
INSERT INTO DailyStats (date, total_visits, unique_visitors, total_pages_viewed) VALUES
('2023-04-01', 10, 5, 20),
('2023-04-02', 15, 7, 35),
('2023-04-03', 20, 8, 45);


-- 插入访问统计表（按页面统计）数据
INSERT INTO PageStats (page_url, total_views) VALUES
('http://example.com/home', 50),
('http://example.com/about', 30),
('http://example.com/contact', 20);


-- 插入用户自定义统计条件表数据（可选，确保user_id引用Users表中已存在的ID）
INSERT INTO CustomStatsConditions (user_id, start_date, end_date, page_url, referrer, ip_range) VALUES
(1, '2023-04-01', '2023-04-01', 'http://example.com/home', '', '192.168.1.*'),
(2, '2023-04-02', '2023-04-02', '', 'http://google.com', ''),
(3, '2023-04-01', '2023-04-03', 'http://example.com/about', '', '10.0.*.*');


-- 插入自定义统计结果表数据（可选，确保condition_id引用CustomStatsConditions表中已存在的ID）
INSERT INTO CustomStatsResults (condition_id, total_visits, unique_visitors, total_pages_viewed) VALUES
(1, 3, 2, 5),
(2, 2, 1, 3),
(3, 5, 3, 10);