MySQL数据库基础练习系列10、访客登记系统

红目香薰

发布于 2024-06-07 16:00:20

750

发布于 2024-06-07 16:00:20

MySQL数据库基础练习系列目标

很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8 排序规则：utf8_general_ci

使用工具：Navicat Premium 15，可以在下面的连接中下载

https://download.csdn.net/download/feng8403000/89403778

项目名称与项目简介

访客登记系统是一个用于管理来访者信息的系统。它允许管理员记录访客的姓名、性别、联系方式、来访原因、来访时间以及接待人员等信息，并提供了查询和统计功能，方便管理人员了解访客的动态和统计来访数据。

该系统主要具有以下功能：

访客信息录入：管理员可以录入访客的基本信息，如姓名、性别、联系方式等。
来访原因和目的记录：记录访客来访的原因和目的，方便后续管理和查询。
接待人员分配：管理员可以为访客分配接待人员，并记录接待情况。
访客信息查询：支持按姓名、来访时间等条件查询访客信息。
来访数据统计：统计来访次数、访客数量等，为管理决策提供数据支持。

数据库DDL（注意创建顺序）

为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。

CREATE TABLE receptionists (
    receptionist_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '接待人员ID',
    name VARCHAR(50) NOT NULL COMMENT '接待人员姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    phone VARCHAR(20) COMMENT '联系电话',
    email VARCHAR(100) COMMENT '电子邮箱'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接待人员信息表';
CREATE TABLE visitors (
    visitor_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '访客ID',
    name VARCHAR(50) NOT NULL COMMENT '访客姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    identity_card VARCHAR(20) COMMENT '身份证号码',
    phone VARCHAR(20) NOT NULL COMMENT '联系电话',
    address VARCHAR(255) COMMENT '地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='访客信息表';
CREATE TABLE visits (
    visit_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '来访记录ID',
    visitor_id INT NOT NULL COMMENT '访客ID',
    receptionist_id INT NOT NULL COMMENT '接待人员ID',
    visit_date DATE NOT NULL COMMENT '来访日期',
    visit_time TIME NOT NULL COMMENT '来访时间',
    purpose TEXT COMMENT '来访目的',
    FOREIGN KEY (visitor_id) REFERENCES visitors(visitor_id) ON DELETE CASCADE,
    FOREIGN KEY (receptionist_id) REFERENCES receptionists(receptionist_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='来访记录表';
CREATE TABLE reception_details (
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '接待情况ID',
    visit_id INT NOT NULL COMMENT '来访记录ID',
    reception_result ENUM('成功', '失败', '未处理') NOT NULL COMMENT '接待结果',
    remarks TEXT COMMENT '备注',
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接待情况表';
CREATE TABLE system_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '日志时间',
    log_type ENUM('访客登记', '接待处理', '其他') NOT NULL COMMENT '日志类型',
    log_content TEXT COMMENT '日志内容',
    operator_name VARCHAR(50) NOT NULL COMMENT '操作员姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统日志表';

插入数据DML（注意插入数据顺序）

插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。

INSERT INTO receptionists (name, gender, phone, email) VALUES
('张三', '男', '13800138000', 'zhangsan@example.com'),
('李四', '女', '13900139000', 'lisi@example.com'),
('王五', '男', '13700137000', 'wangwu@example.com'),
('赵六', '女', '13600136000', 'zhaoliu@example.com'),
('孙七', '男', '13500135000', 'sunqi@example.com');
INSERT INTO visitors (name, gender, identity_card, phone, address) VALUES
('访客A', '男', '110101199001010001', '18600000001', '北京市朝阳区'),
('访客B', '女', '220202198505050002', '18700000002', '上海市黄浦区'),
('访客C', '男', '330303199503030003', '18800000003', '广州市天河区'),
('访客D', '女', '440404198808080004', '18900000004', '深圳市福田区'),
('访客E', '男', '550505199202020005', '18500000005', '成都市武侯区');
INSERT INTO visits (visitor_id, receptionist_id, visit_date, visit_time, purpose) VALUES
(1, 1, '2023-10-25', '09:00:00', '咨询业务'),
(2, 2, '2023-10-26', '10:30:00', '提交资料'),
(3, 3, '2023-10-27', '14:15:00', '面试'),
(4, 4, '2023-10-28', '11:00:00', '参观公司'),
(5, 5, '2023-10-29', '08:45:00', '商务洽谈');
INSERT INTO reception_details (visit_id, reception_result, remarks) VALUES
(1, '成功', '接待顺利，解答了客户的问题'),
(2, '成功', '资料已接收，等待审核'),
(3, '失败', '面试未通过，已通知候选人'),
(4, '成功', '参观过程愉快，客户表示满意'),
(5, '未处理', '商务洽谈预约改期，待重新安排');
INSERT INTO system_logs (log_time, log_type, log_content, operator_name) VALUES
(NOW(), '访客登记', '访客A已登记', '张三'),
(NOW(), '接待处理', '成功接待访客B', '李四'),
(NOW(), '访客登记', '访客C已登记', '王五'),
(NOW(), '接待处理', '成功接待访客D', '赵六'),
(NOW(), '其他', '系统维护完成', '系统管理员');

遵循的数据库三范式

数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：

第一范式（1NF, First Normal Form）

定义：