很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了,但是不接触项目根本部知道需求,我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息,让大家更好的深入项目进行实战式的练习,可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。
MySQL版本:5.7.31-log
数据库字符集,所有数据库通用字符集与排序规则,支持中文数据。
字符集:utf8 排序规则:utf8_general_ci
使用工具:Navicat Premium 15,可以在下面的连接中下载
https://download.csdn.net/download/feng8403000/89403778
站点访问量统计系统是一个用于记录、分析和展示网站访问量的系统。主要功能包括记录用户的访问信息(如访问时间、访问页面、来源地址等),统计访问量数据(如总访问量、日访问量、页面访问量等),以及为用户提供访问数据的查询和可视化展示。系统支持多用户管理,每个用户可以查看自己相关的访问统计信息。
为了直接运行DDL语句并创建表,我们需要确保在创建含有外键约束的表之前,相关的被引用表(即外键指向的表)已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建,否则就会出现没有外键关系导致的创建异常。
-- 用户表
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);
遵循的数据库三范式
数据库建表的三范式(3NF,Third Normal Form)是关系型数据库设计的基本原则,用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的,每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式:
第一范式(1NF, First Normal Form)
定义:
解释:
第二范式(2NF, Second Normal Form)
定义:
解释:
第三范式(3NF, Third Normal Form)
定义:
解释: