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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库基础练习系列10、访客登记系统

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

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

数据库环境

MySQL版本:5.7.31-log

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

字符集:utf8

排序规则:utf8_general_ci

使用工具:Navicat Premium 15

项目名称与项目简介

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

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

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

数据库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)

定义

  1. 列不可分割,即数据库表的每一列都是不可分割的原子数据项。
  2. 每一列都是不可再分的最小数据单元(也称为最小的原子单元)。

解释

  • 在第一范式中,主要关注的是列的原子性。也就是说,表中的每一列都应该只包含一个值,而不能包含集合、数组或其他复合数据类型。
  • 例如,如果有一个“地址”列,它包含了街道、城市、省份和国家等信息,那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列,如“街道”、“城市”、“省份”和“国家”。

第二范式(2NF, Second Normal Form)

定义

  1. 满足1NF。
  2. 非主键列必须完全依赖于主键,而不能只依赖于主键的一部分(针对复合主键而言)。

解释

  • 第二范式建立在第一范式的基础上,主要关注于主键与非主键列之间的依赖关系。
  • 在第二范式中,一个表只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
  • 如果表中的某一列只与复合主键的一部分有关,那么它就不应该存在于这个表中,而应该被分离出去形成另外一张新表。

第三范式(3NF, Third Normal Form)

定义

  1. 满足2NF。
  2. 非主键列必须直接依赖于主键,不能存在传递依赖。即非主键列必须直接依赖于整个主键,而不能依赖于主键的一部分。

解释

  • 第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖,即非主键列不应该依赖于主键的某一部分,而应该直接依赖于整个主键。
  • 如果存在传递依赖,那么应该考虑将这个非主键列分离出去,形成新的表,并通过主键或外键与原表进行关联。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
12天前
|
存储 SQL 关系型数据库
现代数据库管理系统的发展与挑战
数据库管理系统(DBMS)作为现代软件开发的核心组成部分,其在数据存储、管理和访问方面的演进至关重要。本文探讨了现代DBMS的发展历程、关键技术特点以及面临的挑战,包括大数据处理、云计算环境下的优化和安全性等方面。 【7月更文挑战第7天】
|
10天前
|
存储 自然语言处理 DataWorks
通过云上数据库三步构建RAG系统
本文介绍了如何使用OpenSearch LLM智能问答版通过云上数据库一站式构建RAG系统。
18518 10
|
14天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
131 1
|
1月前
|
SQL 缓存 监控
✅系统日活递增,如何优化提升大规模数据库
数据库性能优化涵盖硬件升级(如SSD、内存)、数据库设计简化、SQL查询优化、索引管理、缓存利用(如Redis)、负载均衡(读写分离、集群)、分区分片、备份恢复策略及性能监控。综合调整这些方面可提升系统性能和可用性。[MySQL索引设计][1]和[SQL优化实践][2]是深入学习的好资源。
|
27天前
|
存储 搜索推荐 数据库
软件系统【标签tag功能】的两种数据库设计
软件系统中的标签功能可采用两种数据库设计。方案一,文章和Tag各一表,Tag信息存储在文章表内(`tags`和`tagids`字段),优点是模型简单,但查询效率低且易引发数据冗余和一致性问题。方案二,增加Tagmap表,用于存储标签-文章映射,利于索引查询和数据更新,适用于高效率需求,但结构更复杂。
48 0
软件系统【标签tag功能】的两种数据库设计
|
28天前
|
存储 关系型数据库 MySQL
系统数据库
【6月更文挑战第20天】系统数据库。
12 1
|
1月前
|
关系型数据库 MySQL 数据挖掘
MySQL窗口函数详解(概念+练习+实战)
MySQL窗口函数详解(概念+练习+实战)
102 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库基础练习系列15、电子邮件管理系统
MySQL数据库基础练习系列15、电子邮件管理系统
20 1
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库基础练习系列14、博客后台管理系统
MySQL数据库基础练习系列14、博客后台管理系统
20 1