学习进度条4.21

所花时间：6小时
代码量：400
搏客量：2
了解到的知识点：
今天进行了数据库原理实验一。
一、实验设计思想
本实验基于数据库结构图所示的"stumanage"数据库，围绕实体-关系模型构建了三个核心数据表。其中，student表独立存储学生实体信息，包含学号（sno）、姓名（sname）、性别（sex）和所属院系（sdept）字段；course表（图中蓝色高亮显示）记录课程实体信息，包含课程号（cno）和课程名称（cname）；sc表通过复合主键（sno+cno）维护学生与课程之间的多对多选课关系，并设置外键约束（sno指向student、cno指向course），确保数据完整性。设计严格遵循第三范式（3NF），消除冗余数据，通过物理表course_avg持久化存储课程平均成绩计算结果，体现业务逻辑与数据存储的分离思想。

二、主要技术问题处理

定长字段隐式填充问题
由于course表的课程号定义为CHAR(6)，插入"01"时系统自动补足为"01 "（含4个尾部空格）。为解决查询匹配问题，插入语句中显式补全空格（'01 '），查询时使用RTRIM(cno)去除尾部空格实现兼容性操作，避免因隐式填充导致的外键关联失效。
外键约束冲突
在向sc表插入选课记录时，需确保学生和课程信息已预先存在。通过控制执行顺序（先插入student和course表数据）并结合WHERE EXISTS子句进行存在性验证，从程序逻辑层面规避外键约束报错。例如，插入选课记录前动态检查学生和课程是否存在，仅当主表数据完整时才执行插入操作。
对象名无效错误
当出现"对象名'student'无效"错误时，定位原因为数据库上下文未正确切换或架构引用缺失。通过显式指定dbo.student格式的表名、强制使用USE stumanage;切换数据库上下文，并授予用户SELECT权限，确保跨会话操作时能准确识别表对象。
中文字符乱码
针对SQL Server默认字符集可能导致的汉字显示异常，所有中文字段插入时均采用N'王飞'格式，强制使用Unicode编码，避免因字符集不匹配造成的乱码问题。
三、实验过程概述
实验从创建数据库和表结构开始，严格遵循"先主表后从表"的原则。通过CREATE TABLE语句定义表结构时，明确主键约束和外键引用关系，特别对CHAR(6)类型的课程号字段预设存储空间。数据插入阶段采用事务控制（BEGIN TRANSACTION...COMMIT）确保原子性，批量插入语法配合N''处理中文字符集，实现基础数据的完整导入。

在数据查询与分析环节，通过JOIN操作实现三表关联查询，利用AVG()函数计算课程平均成绩，并结合RTRIM()函数处理定长字段的显示格式。更新和删除操作中，精确匹配CHAR字段长度（如'02 '），通过WHERE子句限定操作范围，避免误删或误改数据。高级操作阶段，创建物理表course_avg存储统计结果，验证级联删除对数据完整性的影响，最终通过DROP DATABASE命令完整清理实验环境。

四、实验说明与分析

结果验证方法
通过SELECT * FROM sc WHERE grade=0验证成绩清零操作的执行效果，结合INFORMATION_SCHEMA.TABLES视图检查表结构元数据，确保各字段类型、约束与设计一致。使用DATALENGTH(cno)验证CHAR(6)字段的实际存储长度，确认隐式填充机制符合预期。
性能优化建议
为提升查询效率，可为sc表的cno字段添加索引（CREATE INDEX IDX_sc_cno ON sc(cno)），缩短关联查询响应时间。针对高频访问的统计结果（如平均成绩），建议物化为视图或定期更新的物理表，减少实时计算的开销。
安全性增强措施
通过REVOKE DELETE ON sc FROM public限制普通用户的删除权限，敏感操作（如清空表数据）需在事务中执行并配备回滚机制。对于数据修改类操作，推荐封装为存储过程（如sp_update_grade），通过参数化输入降低SQL注入风险。
扩展性改进方案
引入CHECK约束（如ALTER TABLE student ADD CHECK (sex IN ('男','女'))）可增强数据录入的合法性校验。针对复杂业务逻辑，可通过触发器自动更新course_avg表，确保统计结果的实时性。
五、实验体会
事务机制的关键作用
在批量插入和级联删除操作中，事务控制（BEGIN TRANSACTION）有效防止了部分成功导致的数据不一致问题。例如，在清空表数据时，若未启用事务，可能出现sc表数据已删除而student表保留记录的外键冲突异常。
测试验证的必要性
CHAR字段的长度兼容性、外键约束的级联效应等细节需通过SELECT查询和系统函数（如DATALENGTH）反复验证。例如，课程号"01"的存储形式需通过SELECT QUOTENAME(cno)确认包含尾部空格，避免关联查询失效。
SQL Server特性适配
实验中发现，GO批处理分隔符和dbo默认架构是SQL Server特有的语法要求。在跨平台迁移时，需注意调整这些细节以兼容其他数据库系统（如MySQL的USE DATABASE无需GO）。