数据库期末大作业，三天现学的，可能会有疏漏或者错误的地方，欢迎指正~
目录
3.2数据库基本表设计（根据关系模式的结构和字段及约束，撰写SQL数据库定义语言）
3.3 数据库完整性分析（实体完整性、参照完整性及用户定义完整性）
3.4数据库规范化理论简述（简要分析并指出数据库满足的范式）
4.2数据库脚本（利用基本表和视图，提出至少20个典型查询应用SQL代码）
1 需求分析
1.1 系统目标（整体业务目标、系统边界的理解总结）
为了能够更好地调配学校的疫情防控工作，需要掌握并处理大量防疫相关信息，因此本系统的业务目标是，通过数据库的建立，完成对教师和学生防疫相关信息的存储和更新，以及对学生出离校申请的管理。
本系统提供对数据库中所存储的信息按指定要求进行增删查改的服务，系统的使用者是管理学校防疫事务的管理员，学生以及老师，学生和教师主要为系统提供相关防疫信息，而管理员对系统的修改权限较高，可以从系统中获取，查找并处理如出离校审批，核酸检测情况，学生个人信息等大多数信息。
1.2 功能需求及数据需求分析（利用数据字典，数据流图）
数据字典：
数据项：
| 含义说明 | 类型 | 长度 | 取值范围 | 取值含义 | 与其他数据项的逻辑关系 | |
| ID | 身份证号 | 字符型 | 18 | 18位 | 身份证号是18位的 | 每个身份证号只对应一个人。 |
| Name | 姓名 | 字符型 | 10 | 无 | 无 | 每个身份证号对应唯一姓名 |
| Age | 年龄 | 整型 | 10~100 | 年龄不可能过大或过小 | 每个身份证号对应唯一年龄 | |
| PhoneNum | 手机号 | 字符型 | 11 | 11位 | 手机号是11位 | 每个身份证号对应一串手机号 |
| VBrand | 疫苗品牌 | 字符型 | 20 | 无 | 疫苗种类较多，无法限定范围 | 每个疫苗接种时间对应一种疫苗，每个针次对应一个品牌的疫苗。 |
| VTime | 接种时间 | 字符型 | 20 | 无 | 无 | 每个接种时间对应一个针次以及一种品牌。 |
| VNTime | 接种针次 | 整型 | 1-3 | 目前同一次疫苗接种最少1次，最多3次 | 每个针次对应一个接种时间以及一种品牌。 | |
| NATTime | 核酸检测时间 | 字符型 | 16 | 无 | 无 | 每个核酸检测时间对应一个检测地点，检测结果，检测报告。 |
| NATPlace | 核酸检测地点 | 字符型 | 100 | 无 | 无 | 每个核酸检测地点对应一个检测时间，检测结果。 |
| NATResult | 核酸检测结果 | 字符型 | 4 | 阴性/阳性 | 无 | 每个核酸检测结果对应一个检测时间，检测地点。 |
| PlaceType | 所在区域类型 | 字符型 | 4 | 封控/管控/隔离/解封 | 只有这四种情况。 | 每个教师对应一个所在区域类型。 |
| Temperature | 体温 | 浮点型 | 30~45 | 正常人不会超过这个范围。 | 每个人对应一个体温。 | |
| Symptom | 症状 | 字符型 | 500 | 无 | 无 | |
| Trip | 行程 | 字符型 | 500 | 无 | 无 | |
| HCode | 健康码 | 字符型 | 2 | 绿/黄/红/灰 | 健康码颜色不超过此范围 | 每个人对应一个健康码 |
| LSProgress | 离校审批进度 | 字符型 | 12 | 辅导员/导师/负责人审批/通过 | 学生申请后的审批流程是固定的。 | 每个离校审批对应一个进度。 |
| LSCdoe | 离校申请编号 | 字符型 | 4 | 无 | 无 | 唯一标识离校申请 |
| LSDest | 离校目的地 | 字符型 | 100 | 无 | 无 | 每个离校审批对应一个目的地。 |
| LSTime | 离校时间 | 字符型 | 100 | 无 | 无 | 每个离校审批对应一个离校时间 |
| TrainNumber | 所乘车次 | 字符型 | 50 | 无 | 无 | |
| LSApplyTime | 离校审批申请时间 | 字符型 | 50 | 无 | 无 | 每个离校审批对应一个离校审批申请时间。 |
| OSProgress | 出校审批进度 | 字符型 | 12 | 辅导员/导师/负责人审批/通过 | 学生申请后的审批流程是固定的。 | 每个出校审批对应一个进度。 |
| OSCdoe | 出校申请编号 | 字符型 | 4 | 无 | 无 | 唯一标识出校申请 |
| OSApplyTime | 出校审批申请时间 | 字符型 | 50 | 无 | 无 | 每个出校审批对应一个出校审批申请时间。 |
| OSDest | 出校目的地 | 字符型 | 100 | 无 | 无 | 每个出校审批对应一个出校目的地。 |
| OSReason | 出校原因 | 字符型 | 200 | 无 | 无 | 每个出校审批对应一个出校原因。 |
| HRTime | 健康上报时间 | 字符型 | 50 | 无 | 无 | 每个健康上报都对应一个健康上报时间。 |
| TRTime | 行程日报时间 | 字符型 | 50 | 无 | 无 | 每个行程日报都对应一个行程日报时间。 |
| IsOS | 是否出校 | 字符型 | 2 | 是/否 | 无 | 每个行程日报对应一个是否出校。 |
| IsOC | 是否出市 | 字符型 | 2 | 是/否 | 无 | 每个行程日报对应一个是否出市。 |
| IsOP | 是否出省 | 字符型 | 2 | 是/否 | 无 | 每个行程日报对应一个是否出省。 |
| QSTime | 开始隔离时间 | 字符型 | 50 | 无 | 无 | 每条隔离信息都对应一个开始隔离时间。 |
| QETime | 结束隔离时间 | 字符型 | 50 | 无 | 无 | 每条隔离信息都对应一个结束隔离时间。 |
| QPlace | 隔离地点 | 字符型 | 200 | 无 | 无 | 每条隔离信息都对应一个开始隔离地点。 |
数据存储：
学生表
组成：身份证号，姓名，年龄，手机号，离校申请编号，出校申请编号，疫苗品牌，接种时间，接种针次，核酸检测时间，核酸检测地点，核酸检测结果
教师表
组成：身份证号，姓名，年龄，手机号，疫苗品牌，接种时间，接种针次，核酸检测时间，核酸检测地点，核酸检测结果
教师日报表
组成：身份证号，所在区域类型，体温，症状，行程，绿码
离校审批表
组成：离校审批编号，离校审批进度，离校目的地，离校时间，所乘车次，离校审批申请时间，离校审批进度
出校审批表
组成：出校审批编号，出校审批进度，出校目的地，出校时间，出校目的地，出校审批申请时间，出校审批进度
行程日报表
组成：身份证号，是否出校，是否出市，是否出省，目的地，开始隔离时间，结束隔离时间，隔离地点
健康日报表：
组成：身份证号，体温，症状
数据流图：
图1-1：顶层数据流图
图1-2：（1） 疫情防控管理系统
1.3 规则分析（业务中各种约束的整理）
本系统中存在的业务规则约束如下：
①身份证号是18位的。
②手机号是11位。
③同一批疫苗接种最少0次，最多3次。
④核酸检测的结果只有阴性/阳性两个选项。
⑤所在区域类型只能选择：封控/管控/隔离/解封
⑥健康码的颜色只有如下四个选项：绿/黄/红/灰
⑦出离校审配进度只有空或如下几种：辅导员/导师/负责人审批/通过
⑧是否出校/市/省的选项都只有是或否。
2 概念结构设计
2.1 实体集及属性分析（通过数据字典列出）
实体集属性如下：
学生：身份证号，姓名，年龄，手机号
教师：身份证号，姓名，年龄，手机号
健康信息：身份证号，体温，症状
行程日报：身份证号，隔离情况，是否出校，是否出市，是否出省，目的地
出校申请：出校申请编号，出校审批进度，出校原因，行程
离校申请：离校申请编号，离校审批进度，佐证材料，行程
教师日报：身份证号，体温，症状，行程，所在区域类型，健康码
疫苗：疫苗品牌
2.2 联系集及属性分析（通过业务分析列出）
学生与健康信息之间的联系为：健康上报
学生与出校申请之间的联系为：申请出校，其属性为：申请时间
学生与离校申请之间的联系为：申请离校，其属性为：申请时间
教师与教师日报之间的联系为：日报健康
学生与行程日报之间的联系为：日报行程
教师，学生与疫苗之间的联系为：接种疫苗，其属性为：接种时间，接种针次
2.3 分ER图（并进一步指出冲突和冗余，进行合并与重构）
图2-1：分ER图1
图2-2：分ER图2
图2-3：分ER图3
2.4 总ER图集成
图2-4：总ER图
3 逻辑结构设计
3.1关系模型转换（根据转换规则将ER模型转换为关系模式）
可将学生与健康信息，学生与离校申请等1对1的关系相互合并，将其中一个的主码放入另一个中成为外码，联系本身的属性均转换为关系的属性，将学生与疫苗等1对n联系中，联系1方的实体的主码纳入n方对应实体关系中成为外码，并将联系的属性一并加入n方关系中：
学生：学生（身份证号，姓名，年龄，手机号，出校申请编号，离校申请编号，出校申请时间，离校申请时间，疫苗品牌，接种时间，接种针次)
教师：教师（身份证号，姓名，年龄，手机号，疫苗品牌，接种时间，接种针次）
3.2数据库基本表设计（根据关系模式的结构和字段及约束，撰写SQL数据库定义语言）
根据对关系模式的分析，最终确定基本表以及相应SQL定义语言如下：
表3-1学生基本信息表
| 列名 | 数据类型 | 是否可空 | 说明 |
| ID | varchar(18) | not null | 身份证号 |
| Name | varchar(10) | not null | 姓名 |
| Age | int | not null | 年龄 |
| PhoneNum | varchar(11) | not null | 手机号 |
| LSCdoe | varchar(4) | 离校申请编号 | |
| OSCdoe | varchar(4) | 出校申请编号 | |
| VBrand | varchar(8) | 疫苗品牌 | |
| VTime | varchar(50) | 接种时间 | |
| VNTime | int | 接种针次 | |
| NATTime | varchar(50) | 核酸检测时间 | |
| NATPlace | varchar(100) | 核酸检测地点 | |
| NATResult | varchar(4) | 核酸检测结果 |
表3-1对应SQL数据库定义语言：
create Table StuInfo(
ID varchar(18) primary key,
check(len(ID)=18),
Name varchar(10) not null,
Age int not null,
PhoneNum varchar(11) not null,
check(len(PhoneNum)=11),
LSCdoe varchar(4),
OSCdoe varchar(4),
VBrand varchar(8),
VTime varchar(50),
VNTime int,
NATTime varchar(50),
NATPlace varchar(100),
NATResult varchar(4),
check(NATTime like('____-__-__')),
check(VTime like('____-__-__')),
check(VNTime>=0 and VNTime <=3),
check(NATResult in('阳性','阴性'))
)
表3-2教师基本信息表
| 列名 | 数据类型 | 是否可空 | 说明 |
| ID | varchar(18) | not null | 身份证号 |
| Name | varchar(10) | not null | 姓名 |
| Age | int | not null | 年龄 |
| PhoneNum | varchar(11) | not null | 手机号 |
| VBrand | varchar(8) | 疫苗品牌 | |
| VTime | varchar(50) | 接种时间 | |
| VNTime | int | 接种针次 | |
| NATTime | varchar(50) | 核酸检测时间 | |
| NATPlace | varchar(100) | 核酸检测地点 | |
| NATResult | varchar(4) | 核酸检测结果 |
表3-2对应SQL数据库定义语言：
create Table TeacherInfo(
ID varchar(18) primary key,
check(len(ID)=18),
Name varchar(10) not null,
Age int not null,
PhoneNum varchar(11) not null,
check(len(PhoneNum)=11),
VBrand varchar(8),
VTime varchar(50),
VNTime int,
NATTime varchar(50),
NATPlace varchar(100),
NATResult varchar(4),
check(NATTime like('____-__-__')),
check(VTime like('____-__-__')),
check(VNTime>=0 and VNTime <=3),
check(NATResult in('阳性','阴性'))
)
表3-3教师日报信息表
| 列名 | 数据类型 | 是否可空 | 说明 |
| ID | varchar(18) | not null | 身份证号 |
| PlaceType | varchar(4) | not null | 所在区域类型 |
| Temperature | float | not null | 体温 |
| Symptom | varchar(500) | 症状 | |
| Trip | varchar(500) | 行程 | |
| HCode | varchar(2) | not null | 健康码 |
表3-3对应SQL数据库定义语言：
create Table TeacherDailyReport(
ID varchar(18) primary key,
Temperature float not null,
Symptom varchar(500),
check(len(ID)=18),
Trip varchar(500),
HCode varchar(2) not null,
check(Trip like('%-%')),
check(HCode in('绿','红','黄','灰'))
)
表3-4学生离校审批表
| 列名 | 数据类型 | 是否可空 | 说明 |
| LSCdoe | varchar(4) | not null | 离校申请编号 |
| LSProgress | varchar(12) | not null | 离校审批进度 |
| LSDest | varchar(100) | not null | 离校目的地 |
| LSTime | varchar(100) | not null | 离校时间 |
| LSApplyTime | varchar(50) | not null | 离校申请时间 |
| HCode | varchar(2) | not null | 健康码 |
| TrainNumber | varchar(50) | 所乘车次 |
表3-4对应SQL数据库定义语言：
create Table StuLSApproval (
LSCdoe varchar(4) primary key,
LSProgress varchar(12) not null,
LSApplyTime varchar(50) not null,
LSDest varchar(100) not null,
LSTime varchar(100) not null,
HCode varchar(2) not null,
TrainNumber varchar(50),
check(LSApplyTime like('____-__-__')),
check(LSTime like('____-__-__')),
check(LSProgress in('辅导员审批','导师审批','负责人审批','通过')),
check(HCode in('绿','红','黄','灰'))
)
表3-5学生出校审批表
| 列名 | 数据类型 | 是否可空 | 说明 |
| OSCdoe | varchar(4) | not null | 出校申请编号 |
| OSProgress | varchar(12) | not null | 出校审批进度 |
| OSApplyTime | varchar(50) | not null | 出校申请时间 |
| OSDest | varchar(100) | not null | 出校目的地 |
| OSTime | varchar(100) | not null | 出校时间 |
| OSReason | varchar(200) | not null | 出校原因 |
| HCode | varchar(2) | not null | 健康码 |
表3-5对应SQL数据库定义语言：
create Table StuOSApproval (
OSCdoe varchar(4) primary key,
OSProgress varchar(12) not null,
OSApplyTime varchar(50) not null,
OSDest varchar(100) not null,
OSTime varchar(100) not null,
OSReason varchar(200) not null,
HCode varchar(2) not null,
check(OSApplyTime like('____-__-__')),
check(OSTime like('____-__-__')),
check(OSProgress in('辅导员审批','导师审批','负责人审批','通过')),
check(HCode in('绿','红','黄','灰'))
)
表3-6学生行程日报信息表
| 列名 | 数据类型 | 是否可空 | 说明 |
| ID | varchar(18) | not null | 身份证号 |
| IsOS | varchar(2) | not null | 是否出校 |
| IsOPC | varchar(2) | not null | 是否出市 |
| IsOP | varchar(2) | not null | 是否出省 |
| QSTime | varchar(50) | 开始隔离时间 | |
| QETime | varchar(50) | 结束隔离时间 | |
| QPlace | varchar(200) | 隔离地点 |
表3-6对应SQL数据库定义语言：
create Table StuDailyTripReport (
ID varchar(18) primary key,
IsOS varchar(2) not null,
IsOC varchar(2) not null,
IsOP varchar(2) not null,
QSTime varchar(50),
QETime varchar(50),
QPlace varchar(200),
check(len(ID)=18),
check(IsOS in('是','否')),
check(IsOC in('是','否')),
check(IsOP in('是','否')),
)
表3-7学生健康日报信息表
| 列名 | 数据类型 | 是否可空 | 说明 |
| ID | varchar(18) | not null | 身份证号 |
| Temperature | float | not null | 体温 |
| Symptom | varchar(500) | 症状 |
表3-7对应SQL数据库定义语言：
create Table StuDailyHealthReport (
ID varchar(18) primary key,
Temperature float not null,
Symptom varchar(500),
check(len(ID)=18),
)
各表创建结束后（写入数据前），为各表添加外键约束条件：
ALTER TABLE TeacherDailyReport
ADD CONSTRAINT FK_TID
FOREIGN KEY (ID) REFERENCES TeacherInfo (ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE StuDailyHealthReport
ADD CONSTRAINT FK_SHID
FOREIGN KEY (ID) REFERENCES StuInfo (ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE StuDailyTripReport
ADD CONSTRAINT FK_STID
FOREIGN KEY (ID) REFERENCES StuInfo (ID)
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE StuInfo
ADD CONSTRAINT FK_LSC
FOREIGN KEY (LSCdoe) REFERENCES StuLSApproval (LSCdoe);
ON DELETE CASCADE
ON UPDATE CASCADE;
ALTER TABLE StuInfo
ADD CONSTRAINT FK_OSC
FOREIGN KEY (OSCdoe) REFERENCES StuOSApproval (OSCdoe);
ON DELETE CASCADE
ON UPDATE CASCADE;
3.3 数据库完整性分析（实体完整性、参照完整性及用户定义完整性）
1、实体完整性分析：
实体完整性是关系模型必须满足的完整性约束条件，项条件要求每个数据表都必须有主键，而作为主键的所有字段，其属性必须是独一及非空值。在实际数据库实现的过程中，数据库管理系统会保证主键不为空且唯一，因此，本系统的实体完整性只要保证每个基本表都有主键即可。
本系统中实体完整性分析：
表3-1学生基本信息表，有主键，主键为：ID（身份证号）
表3-2教师基本信息表，有主键，主键为：ID（身份证号）
表3-3教师日报信息表，有主键，主键为：ID（身份证号）
表3-4学生离校审批表，有主键，主键为：LSCdoe（离校申请编号）
表3-5学生出校审批表，有主键，主键为：OSCdoe（出校申请编号）
表3-6学生行程日报信息表，有主键，主键为：ID（身份证号）
表3-7学生健康日报信息表，有主键，主键为：ID（身份证号）
综上，本系统符合实体完整性约束条件。
2、参照完整性分析：
参照的完整性要求关系中不允许引用不存在的实体。若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应，则对于R中每个元组在F上的值必须等于S中某个元组的主码值或者取空值。
本系统中参照完整性分析：
①教师日报信息表中的ID（身份证号）既是主键又是外键对应教师基本信息表中的主键ID。
②学生健康日报信息表中的ID（身份证号）既是主键又是外键对应学生基本信息表中的主键ID。
③学生行程日报信息表中的ID（身份证号）既是主键又是外键对应学生基本信息表中的主键ID。
④学生基本信息表中的LSCdoe（离校申请编号）和OSCdoe（出校申请编号）是外键，分别对应学生离校审批表中的主键LSCdoe，学生出校审批表中的主键OSCdoe。
3、用户定义的完整性分析：
用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
各表中非主属性也不能取空值的属性已在3.2数据库基本表设计中详细列出，以下只整理有特定取值规则的用户定义完整性：
①各表中的ID（身份证号）都必须是18位。
②各表中的PhoneNum（手机号）都必须是11位。
③各表中的时间，包括：NATTime（核酸检测时间），VTime（接种疫苗时间），LSApplyTime（离校申请提交时间），OSApplyTime（出校申请提交时间），
LSTime（离校时间），OSTime（出校时间）都必须写为yyyy-mm-dd的格式。
④各表中的Trip（行程），都需写为起点-终点的格式。
⑤各表中的HCode（健康码）都只能从'绿','红','黄','灰'中国取值。
⑥各表中的审批状态，包括：OSProgress（出校审批状态），LSProgress（离校审批状态）都只能从'辅导员审批','导师审批','负责人审批','通过'中取值。
⑦学生行程日报信息表中的IsOS（是否出校），IsOC（是否出市），IsOP（是否出省）都只能从'是','否'中取值。
3.4数据库规范化理论简述（简要分析并指出数据库满足的范式）
第一范式（1NF）的要求是表的每一列都不可分，以学生基本信息表为例，每列分别存储的是身份证号，姓名，年龄，手机号，离校申请编号，出校申请编号，疫苗品牌，接种时间，接种针次，核酸检测时间，核酸检测地点，核酸检测结果，显然每列都是不可分的客观信息，所以满足第一范式。
第二范式（2NF）的要求是不存在非主属性对主码的部分函数依赖，也就是说，第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关，还是以学生基本信息表为例，它的主键为身份证号，本身就不可分，不会存在部分相关的情况，所以满足第二范式。
第三范式（3NF）要求是不存在非主属性对主码的传递函数依赖，这说明在要在满足第二范式的基础上更进一步,确保每列都和主键列直接相关,而不是间接相关，在学生基本信息表中，每一列都直接对应身份证号，不存在通过传递间接对应的情况，所以也满足第三范式。
以同样的方法容易验证出本数据库中的其他基本表也都满足第三范式，所以本数据库满足第三范式，另外因为满足到第三范式就可以基本解决数据冗余、插入异常、删除异常的情况，已经能够符合本数据库需求，因此不再向上验证其他范式。
4 数据库应用
4.1视图的设计（根据不同的用户角色设计至少三个视图）
1、出校申请学生信息视图
从基本表学生基本信息和学生出校申请中导出，包含：出校申请编号,学生姓名,联系方式,出校时间,出校目的地,出校原因,健康码,核酸检测距今日天数,接种疫苗日期,审批进度，可供管理员及学生查看，方便管理员管理审批结果，方便学生查看审批进度
create view OApplySSInfo(出校申请编号,学生姓名,联系方式,出校时间,
出校目的地,出校原因,健康码,核酸检测距今日_天,
接种疫苗日期,审批进度)
as
select StuOSApproval.OSCdoe, StuInfo.Name,StuInfo.PhoneNum,
StuOSApproval.OSTime,StuOSApproval.OSDest,StuOSApproval.OSReason,
StuOSApproval.HCode,DATEDIFF(DAY,StuInfo.NATTime,CONVERT(varchar(15),GETDATE(),23)),
StuInfo.VTime,StuOSApproval.OSProgress
from StuInfo,StuOSApproval
where StuInfo.OSCdoe=StuOSApproval.OSCdoe;
2、离校申请学生信息视图
从基本表学生基本信息和学生离校申请中导出，包含：离校申请编号,学生姓名,联系方式,离校时间,离校目的地,所乘车次,健康码,核酸检测距今日天数,接种疫苗日期,审批进度，可供管理员及学生查看，方便管理员管理审批结果，方便学生查看审批进度。
create view LApplySSInfo(离校申请编号,学生姓名,联系方式,离校时间,
离校目的地,所乘车次,健康码,核酸检测距今日_天,
接种疫苗日期,审批进度)
as
select StuLSApproval.LSCdoe, StuInfo.Name,StuInfo.PhoneNum,
StuLSApproval.LSTime,StuLSApproval.LSDest,StuLSApproval.TrainNumber,
StuLSApproval.HCode,DATEDIFF(DAY,StuInfo.NATTime,CONVERT(varchar(15),GETDATE(),23)),
StuInfo.VTime,StuLSApproval.LSProgress
from StuInfo,StuLSApproval
where StuInfo.LSCdoe=StuLSApproval.LSCdoe;
3、出校学生信息视图
从基本表学生基本信息和学生日报行程信息中导出，包含：学生姓名,联系方式,到访地点,核酸检测结果,核酸检测距今日天数，可供管理员查看，方便管理员了解学生出校情况，并及时联系途经风险地区的学生。
create view OSStuInfo(学生姓名,联系方式,到访地点,核酸检测结果,核酸检测距今日_天)
as
select StuInfo.Name,StuInfo.PhoneNum,StuDailyTripReport.Dest,
StuInfo.NATResult,DATEDIFF(DAY,StuInfo.NATTime,CONVERT(varchar(15),GETDATE(),23))
from StuDailyTripReport,StuInfo
where StuInfo.ID = StuDailyTripReport.ID and StuInfo.ID
IN(select ID
From StuDailyTripReport
where StuDailyTripReport.IsOS='是')
4、核酸结果超过时限学生视图：
从基本表学生基本信息中导出，包含：学生姓名,联系方式,核酸检测结果,核酸检测日期，可供管理员查看，方便管理员了解学生核酸检测情况，并及时联系联结果无效的学生。
create view StuNATInfo(学生姓名,联系方式,核酸检测结果,核酸检测日期)
as
select StuInfo.Name,StuInfo.PhoneNum,
StuInfo.NATResult,StuInfo.NATTime
from StuInfo
where StuInfo.NATResult is NULL or DATEDIFF(DAY,StuInfo.NATTime,CONVERT(varchar(15),GETDATE(),23))>2
4.2数据库脚本（利用基本表和视图，提出至少20个典型查询应用SQL代码）
1、与核酸检测结果为阳性同学到过同一地点的学生姓名，联系方式，到访地点：
其中的OSStuInfo为出校的学生信息视图
select OSStuInfo.学生姓名,OSStuInfo.联系方式,OSStuInfo.到访地点
from OSStuInfo
where OSStuInfo.核酸检测结果!='阳性' and 到访地点 in(
select OSStuInfo.到访地点
from OSStuInfo
where OSStuInfo.核酸检测结果='阳性'
)
2、去过“校外饭店A”的教师姓名，联系方式，核酸检测结果：
select TeacherInfo.Name,TeacherInfo.PhoneNum,TeacherInfo.NATResult
from TeacherInfo
where TeacherInfo.ID in(
select TeacherDailyReport.ID
from TeacherDailyReport
where TeacherDailyReport.Trip in(
select TeacherDailyReport.Trip
from TeacherDailyReport
where TeacherDailyReport.Trip like concat('%-','校外饭店A')
)
)
3、查询没有健康日报还提出出校申请学生姓名，出校目的地，联系方式：
select StuInfo.Name,StuLSApproval.LSDest,StuInfo.PhoneNum
from StuInfo,StuLSApproval
where StuInfo.LSCdoe=StuLSApproval.LSCdoe and StuInfo.ID in(
select StuInfo.ID
where not exists(
select *
from StuDailyHealthReport
where ID = StuInfo.ID
)
)
4、既没有接种疫苗，核酸检测也超过时限的学生姓名，联系方式：
select StuInfo.Name,StuInfo.PhoneNum
from StuInfo
where StuInfo.VTime is NULL and
(StuInfo.NATResult is NULL or
DATEDIFF(DAY,StuInfo.NATTime,CONVERT(varchar(15),GETDATE(),23))>2 )
5、核酸检测超过时限，并且出校的学生姓名，联系方式，访问地点，核酸检测日期（这里的今天是2022-6-15）：
select StuInfo.Name,StuInfo.PhoneNum,StuDailyTripReport.Dest,StuInfo.NATTime
from StuInfo,StuDailyTripReport
where StuInfo.ID=StuDailyTripReport.ID and
StuInfo.ID in(
select StuInfo.ID
where exists(
select StuDailyTripReport.ID
where StuDailyTripReport.ID =StuInfo.ID and
StuDailyTripReport.Dest is not NULL
)
)and
(StuInfo.NATResult is NULL
or DATEDIFF(DAY,StuInfo.NATTime,CONVERT(varchar(15),GETDATE(),23))>2)
6、年龄大于平均年龄的同学疫苗接种情况
select StuInfo.Name,StuInfo.Age,StuInfo.VBrand,StuInfo.VTime,StuInfo.VNTime
from StuInfo
where StuInfo.Age>(select AVG(Age)
from StuInfo)
7、申请的离校时间是两天内但还没审批完成的学生姓名，申请离校时间，审批进度（这里的今天是2022-6-15）：
select StuInfo.Name,StuLSApproval.LSTime,StuLSApproval.LSProgress
from StuInfo,StuLSApproval
where StuInfo.LSCdoe=StuLSApproval.LSCdoe and
DATEDIFF(DAY,CONVERT(varchar(15),GETDATE(),23),StuLSApproval.LSTime)<=2 and
StuLSApproval.LSProgress!='通过' and StuLSApproval.LSProgress!='不通过'
8、与学生十接种同一种疫苗的教师姓名，疫苗品牌，接种时间：
select TeacherInfo.Name,TeacherInfo.VBrand,TeacherInfo.VTime
from TeacherInfo
where TeacherInfo.VBrand =(
select StuInfo.VBrand
from StuInfo
where StuInfo.Name='学生十'
)
9、既提出出校审批又提出离校审批的同学的姓名，出校审批编号，离校审批编号，出校时间，离校时间：
select StuInfo.Name,StuInfo.LSCdoe,StuInfo.OSCdoe,
StuLSApproval.LSTime,StuOSApproval.OSTime
from StuInfo,StuLSApproval,StuOSApproval
where StuInfo.LSCdoe=StuLSApproval.LSCdoe and
StuInfo.OSCdoe =StuOSApproval.OSCdoe
10、查找学生日报的出校地点去的人次排名：
select DISTINCT StuDailyTripReport.Dest as 地点,count(StuDailyTripReport.ID) as 访问人次
from StuDailyTripReport
where StuDailyTripReport.Dest is not null
group by StuDailyTripReport.Dest
order by count(StuDailyTripReport.ID)
11、提交离校申请的学生中，乘坐车次是G开头的，学生姓名，乘坐车次：
select StuInfo.Name,StuLSApproval.TrainNumber
from StuInfo,StuLSApproval
where StuInfo.LSCdoe=StuLSApproval.LSCdoe and
StuLSApproval.TrainNumber like('G%')
12、查询体温高于36.8度的学生姓名，联系方式，体温，症状：
select StuInfo.Name as 姓名,StuInfo.PhoneNum as 联系方式,
StuDailyHealthReport.Temperature as 体温,StuDailyHealthReport.Symptom as 症状
from StuInfo,StuDailyHealthReport
where StuInfo.ID=StuDailyHealthReport.ID and
StuDailyHealthReport.Temperature>36.8
13、到访校外饭店A的学生的平均年龄：
select AVG(StuInfo.Age) as 平均年龄
from StuInfo
where StuInfo.ID in
(select StuDailyTripReport.ID
from StuDailyTripReport
where StuDailyTripReport.Dest ='校外饭店A'
)
14、查找学生接种各疫苗疫苗的平均针次：
select DISTINCT StuInfo.VBrand as 疫苗品牌,AVG(StuInfo.VNTime) as 平均针次
from StuInfo
where StuInfo.VBrand is not null
group by StuInfo.VBrand
15、查找与老师吴在同一地点核酸检测的学生姓名，检测地点：
select StuInfo.Name,StuInfo.NATPlace
from StuInfo
where StuInfo.NATPlace =(
select TeacherInfo.NATPlace
from TeacherInfo
where TeacherInfo.Name='教师吴')
16、查找学生健康日报中有症状的学生的姓名，症状，联系方式，核酸检测情况
select StuInfo.Name as 学生姓名,StuDailyHealthReport.Symptom as 症状,
StuInfo.PhoneNum as 联系方式,StuInfo.NATTime as 核酸检测时间
from StuInfo,StuDailyHealthReport
where StuInfo.ID = StuDailyHealthReport.ID and
StuDailyHealthReport.Symptom is not null
17、查找审批不通过学生的姓名，出校原因，健康码，核酸检测距今天数：
select OApplySSInfo.学生姓名,OApplySSInfo.出校原因,
OApplySSInfo.健康码,OApplySSInfo.核酸检测距今日_天 as 核酸检测距今天数
from OApplySSInfo
where OApplySSInfo.审批进度='不通过'
18、查询接种其他疫苗的学生中比任何接种北京科兴疫苗的学生都要早的学生姓名，接种疫苗时间：
select StuInfo.Name as 姓名,StuInfo.VBrand as 疫苗品牌,StuInfo.VTime as 接种时间
from StuInfo
where StuInfo.VTime< any(select StuInfo.VTime
from StuInfo
where StuInfo.VBrand ='北京科兴')
and StuInfo.VBrand !='北京科兴'
19、查询行程日报中每个隔离学生姓名，隔离开始日期，剩余隔离天数：
select StuInfo.Name as 学生姓名,StuDailyTripReport.QSTime as 隔离开始日期,
DATEDIFF(DAY,CONVERT(varchar(15),GETDATE(),23),StuDailyTripReport.QETime)
as 剩余隔离天数
from StuInfo,StuDailyTripReport
where StuInfo.ID = StuDailyTripReport.ID and
StuDailyTripReport.QSTime is not null
20、所有完成日报且健康码为绿的教师姓名，联系方式：
select TeacherInfo.Name as 姓名, TeacherInfo.PhoneNum as 联系方式
from TeacherInfo
where exists
(select *
from TeacherDailyReport
where TeacherDailyReport.ID=TeacherInfo.ID and
TeacherDailyReport.HCode='绿')
1093
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
