数据技术 答案

数据库技术参考答案项目1数据库技术概论与职业素养参考答案：1.C2.B3.C4.D5.B6.A7.B项目2数据库环境搭建与实践入门参考答案：1.mysql-uroot-p2.在命令行中检查MySQL服务是否正常运行。对于Windows系统，使用以下命令：netstartMySQL3.右击"此电脑"→"属性"→"高级系统设置"→"环境变量"→在"系统变量"中编辑Path，添加MySQL的bin目录路径。4.使用root用户登录MySQL后，创建一个新数据库：CREATEDATABASEtest_db;5.创建新用户user1并为其分配对test_db数据库的访问权限：CREATEUSER'user1'@'localhost'IDENTIFIEDBY'password123';GRANTALLPRIVILEGESONtest_db.*TO'user1'@'localhost';FLUSHPRIVILEGES;6.退出root用户后，使用新创建的user1用户登录：mysql-uuser1-p输入密码password123登录成功后，选择test_db数据库：USEtest_db;7.使用以下命令查看user1用户的权限：SHOWGRANTSFOR'user1'@'localhost';8.修改user1用户的密码：修改user1用户的密码：ALTERUSER'user1'@'localhost'IDENTIFIEDBY'newpassword123';修改后，使用新密码再次登录：mysql-uuser1-p输入新密码newpassword123验证修改是否成功。项目3数据库设计参考答案：略2.

题目一答案:

关系模型：

员工表(员工ID,姓名,职位)

员工档案表(档案编号,入职日期,合同期限,员工ID)

题目二答案:

关系模型：

部门表(部门ID,部门名称,部门经理)

员工表(员工ID,姓名,职位,工资,部门ID)

题目三答案:

关系模型：

学生表(学号,姓名,班级)

课程表(课程编号,课程名称,学分)

选课表(学号,课程编号,成绩)3.参考答案：表中包含了与学生无关的班级信息，违反了第二范式（2NF）。规范化：学生表：学生(学号,姓名,班级编号)班级表：班级(班级编号,班级名称,班级老师,班级教室)项目4数据类型与数据完整性题目1：字段名推荐数据类型选型理由用户IDINTUNSIGNEDAUTO_INCREMENT①INTUNSIGNED：无符号整数，取值范围0~4294967295，满足海量用户存储；②AUTO_INCREMENT：实现自增、唯一，符合“唯一、自增”需求；③比BIGINT更节省存储空间（4字节vs8字节）。用户名VARCHAR(50)①VARCHAR：可变长字符串，仅存储实际字符数+1字节，比CHAR(50)节省空间；②长度50满足“不超过50字符”的要求；③支持中文/英文混合存储。密码哈希值CHAR(64)①哈希值（如SHA-256）是固定64字符，CHAR(64)存储固定长度字符串，查询效率高于VARCHAR；②无长度浪费，存储效率最优。注册时间DATETIME①DATETIME：精确到秒，格式YYYY-MM-DDHH:MM:SS，满足“精确到秒”需求；②无需时区转换，适合大部分业务场景（若需时区可改用TIMESTAMP，但范围更小）。最后登录时间TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP①TIMESTAMP：4字节存储（比DATETIME省空间）；②ONUPDATECURRENT_TIMESTAMP：更新记录时自动刷新该字段，完美实现“自动更新最后登录时间”；③DEFAULTCURRENT_TIMESTAMP：首次创建时自动记录当前时间。题目2：字段名推荐数据类型选型理由商品IDBIGINTUNSIGNED①电商商品量可能超亿，INT（最大4294万）不够，BIGINTUNSIGNED（最大184亿）满足海量商品；②无符号类型充分利用取值范围，4字节/8字节按需选择（中小电商可用INT）。商品名称VARCHAR(100)①可变长字符串，适配“最长100字符”，节省存储空间；②支持中文/特殊符号，utf8mb4字符集下兼容emoji。商品描述TEXT①平均500字（约1500字符），VARCHAR(65535)虽可行，但TEXT（最大65535字符）更适配长文本场景；②若描述超65535字符，可改用MEDIUMTEXT（最大16MB）。售价DECIMAL(10,2)①DECIMAL(10,2)：总长度10位，小数位2位，满足“精确到小数点后2位”（如99999999.99元）；②避免浮点数（FLOAT/DOUBLE）的精度丢失问题，适合金额存储。库存量SMALLINTUNSIGNED①取值范围0~50000，SMALLINTUNSIGNED最大值65535，完全覆盖且仅占2字节；②比INT（4字节）节省50%存储空间，比TINYINT（最大255）范围足够。题目3：字段名推荐数据类型选型理由帖子ID（补充）BIGINTUNSIGNEDAUTO_INCREMENT必选唯一标识，社交媒体帖子量极大，BIGINT满足亿级存储（若省略需补充，否则无主键）。帖子内容MEDIUMTEXT①长文本+emoji，MEDIUMTEXT（最大16MB）适配超长内容（如万字长文）；②utf8mb4字符集支持多语言、emoji，避免乱码；③比TEXT范围更大，比LONGTEXT更节省空间。图片路径VARCHAR(255)①文件路径通常不超过255字符（如/uploads/2024/03/10/abc123.jpg）；②VARCHAR(255)是文件路径的经典选型，兼容各类存储路径格式；③若多图路径，可改用JSON存储数组。发布时间DATETIMEDEFAULTCURRENT_TIMESTAMP①DATETIME：精确到秒，自动记录发布时间（DEFAULTCURRENT_TIMESTAMP）；②若需毫秒级，改用DATETIME(6)（MySQL5.6+支持）。附加属性JSON①动态存储点赞数、分享数等可变属性，JSON无需修改表结构即可扩展字段；②支持数值、字符串等类型，可直接查询（如JSON_EXTRACT(attrs,'$.like_count')）；③比冗余字段更灵活，比VARCHAR更易解析。题目4：字段名推荐数据类型选型理由日志ID（补充）BIGINTUNSIGNEDAUTO_INCREMENT日志量极大，BIGINT满足亿级存储，自增保证唯一。操作时间DATETIME(6)①DATETIME(6)：精确到微秒（百万分之一秒），满足“毫秒级精度”（6位小数覆盖毫秒）；②比TIMESTAMP(6)范围更大（TIMESTAMP仅到2038年），适合长期日志存储。操作人员IDINTUNSIGNED参考用户表的用户ID（通常为INT），类型保持一致，避免隐式转换；无符号充分利用取值范围。操作详情TEXT操作详情长度不固定，TEXT适配可变长文本（最大65535字符），比VARCHAR更灵活（无需预估长度）。IP地址VARBINARY(39)①IPv6地址最长39字符（如2001:0db8:85a3:0000:0000:8a2e:0370:7334）；②VARBINARY存储二进制，比VARCHAR节省空间（字符集无开销）；③也可改用INET6_ATON()转换为VARBINARY(16)，查询时用INET6_NTOA()还原，更省空间。题目5：字段名推荐数据类型选型理由地点ID（补充）BIGINTUNSIGNEDAUTO_INCREMENT唯一标识，适配海量地点存储。地点名称VARCHAR(100)①VARCHAR(100)适配中文、特殊符号，utf8mb4字符集保证兼容性；②可变长节省空间，满足大部分地点名称长度。坐标数据POINT①MySQL空间数据类型POINT：专门存储经纬度（X=经度，Y=纬度），支持空间计算（如距离、范围查询）；②比DECIMAL(10,6)存储两个字段更高效，且原生支持空间函数（如ST_Distance_Sphere()计算两点距离）。创建时间TIMESTAMPWITHTIMEZONE①仅MySQL8.0.19+支持，带时区的时间类型，精准记录不同时区的创建时间；②若版本较低，改用DATETIME+单独的time_zoneVARCHAR(10)字段（如+08:00）。开放状态ENUM('启用','维护','关闭')①状态仅3种固定值，ENUM存储效率最高（仅1字节）；②限制输入值，避免非法状态（如输入“停用”会报错）；③比CHAR/VARCHAR更节省空间，比TINYINT更易读（无需映射状态码）。项目5数据库操作与管理参考答案‌‌题目1答案‌--创建数据库CREATEDATABASEschool_dbCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;--使用数据库USEschool_db;--创建学生表CREATETABLEstudents(student_idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(50)NOTNULL,genderCHAR(1),birthdateDATE);--创建课程表CREATETABLEcourses(course_idINTAUTO_INCREMENTPRIMARYKEY,course_nameVARCHAR(100)UNIQUE,creditINTDEFAULT1CHECK(creditBETWEEN1AND5))ENGINE=InnoDB;‌题目2答案‌sqlCopyCodeALTERTABLEstudents--新增email列ADDCOLUMNemailVARCHAR(100),--修改gender字段类型MODIFYCOLUMNgenderENUM('男','女'),--添加birthdate非空约束MODIFYCOLUMNbirthdateDATENOTNULL,--添加email唯一约束ADDUNIQUE(email);‌题目3答案‌sqlCopyCodeCREATETABLEenrollments(enrollment_idINTAUTO_INCREMENTPRIMARYKEY,student_idINT,course_idINT,enrollment_dateDATEDEFAULT(CURRENT_DATE),--定义外键约束FOREIGNKEY(student_id)REFERENCESstudents(student_id)ONDELETECASCADE,FOREIGNKEY(course_id)REFERENCEScourses(course_id)ONUPDATECASCADE)ENGINE=InnoDB;‌题目4答案‌sqlCopyCode--删除course_name唯一约束ALTERTABLEcoursesDROPINDEXcourse_name;--添加credit检查约束（MySQL8.0+支持）ALTERTABLEcoursesADDCONSTRAINTchk_creditCHECK(creditBETWEEN1AND5);--添加birthdate检查约束ALTERTABLEstudentsADDCONSTRAINTchk_birthdateCHECK(birthdate>'1990-01-01');‌题目5答案‌sqlCopyCode--删除表DROPTABLEIFEXISTSenrollments;--删除数据库DROPDATABASEIFEXISTSschool_db;项目6数据的基本操作参考答案：（1）INSERTINTOstudents(stu_id,stu_name,stu_gender,stu_nation,stu_birth,stu_entrancedate,cls_id,stu_address,stu_phone,stu_resume)VALUES('24610210710101','张珊','女','汉','2006-05-12','2024-09-01','51020171','云南省宜良县','15*****5632',NULL),('24610210710102','王武','男','汉','2005-01-22','2024-09-01','51020171','昆明','15*****5632',NULL);（2）UPDATEscoresSETsc_grade=sc_grade+5WHEREcrs_id='040901';（3）DELETEFROMscoresWHEREsc_grade=(SELECTmin_gradeFROM(SELECTMIN(sc_grade)ASmin_gradeFROMscoresWHEREsc_gradeISNOTNULL)AStemp);（4）SHOWVARIABLESLIKE'transaction_isolation';项目7数据查询参考答案：1、SELECTstu_idAS学号,stu_nameAS姓名FROMstudents;2、SELECTstu_idAS学号,stu_nameAS姓名,stu_nationAS民族FROMstudentsWHEREstu_nameLIKE'黄%'ANDstu_nation='藏';3、SELECTCOUNT(*)AS学生人数FROMstudentsWHEREcls_id='51020171';4、SELECT*FROMscoresWHEREsc_gradeIN(100,90,80);5、SELECTs.stu_idAS学号,s.stu_nameAS姓名,sc.crs_idAS课程号,sc.sc_gradeAS成绩FROMstudentssJOINscoresscONs.stu_id=sc.stu_id;6、SELECTs.stu_idAS学号,s.stu_nameAS姓名FROMstudentssJOINscoresscONs.stu_id=sc.stu_idWHEREsc.crs_id='230202'ANDsc.sc_grade>(SELECTAVG(sc_grade)FROMscoresWHEREcrs_id='230202');7、SELECTstu_nameAS姓名,YEAR(NOW())-YEAR(stu_birth)AS年龄FROMstudentsWHEREcls_id='51010271'8、SELECTstu_idAS学号,stu_nameAS姓名,YEAR(NOW())-YEAR(stu_birth)AS年龄FROMstudentsWHEREYEAR(stu_birth)=(SELECTYEAR(stu_birth)FROMstudentsWHEREstu_id='23510102710101');9、SELECTCOUNT(*)AS女生人数FROMstudentssJOINclassescONs.cls_id=c.cls_idWHEREc.cls_name='物联网技术'ANDs.stu_gender='女';10、SELECTstu_idAS学号FROMscoresWHEREsc_gradeISNOTNULL--排除无成绩的记录GROUPBYstu_idHAVINGMIN(sc_grade)>75ANDMAX(sc_grade)<100;项目8视图、索引与数据优化1、CREATEVIEW学生选课情况ASSELECTs.stu_idAS学号,s.stu_nameAS姓名,c.crs_nameAS课程名称FROMstudentssJOINscoresscONs.stu_id=sc.stu_id--关联学生和选课成绩JOINcoursescONsc.crs_id=c.crs_id;--关联选课成绩和课程信2、CREATEORREPLACEVIEW学生选课情况ASSELECTs.stu_idAS学号,s.stu_nameAS姓名,c.crs_nameAS课程名称,sc.sc_gradeAS成绩FROMstudentssJOINscoresscONs.stu_id=sc.stu_idJOINcoursescONsc.crs_id=c.crs_idORDERBYs.stu_idASC;--按学号升序排列3、SELECT学号,姓名,课程名称,成绩FROM学生选课情况WHERE课程名称='面向对象程序设计';4、DROPVIEWIFEXISTS学生选课情况;5、--步骤1：备份表（复制表结构+数据）CREATETABLEstudents_copyASSELECT*FROMstudents;--步骤2：在stu_name列创建普通索引（提高姓名查询性能）CREATEINDEXidx_stu_nameONstudents_copy(stu_name);6、--步骤1：备份表CREATETABLEscores_copyASSELECT*FROMscores;--步骤2：创建复合索引（包含stu_id和crs_id）CREATEINDEXidx_stu_crsONscores_copy(stu_id,crs_id);7、SHOWINDEXFROMscores_copy;8、DROPINDEXidx_stu_crsONscores_copy;‌项目9数据库编程与自动化1、DELIMITER//CREATEPROCEDUREcalculate_bonus(INbase_salaryDECIMAL(10,2),INperformance_levelINT,OUTbonusDECIMAL(10,2))BEGINIFperformance_level=1THENSETbonus=base_salary*0.2;ELSEIFperformance_level=2THENSETbonus=base_salary*0.1;ELSESETbonus=0;ENDIF;END//DELIMITER;‌2、DELIMITER//CREATETRIGGERorder_insert_checkBEFOREINSERTONordersFOREACHROWBEGINIFNEW.order_amount<=0THENSIGNALSQLSTATE'45000'SETMESSAGE_TEXT='订单金额必须大于零';ENDIF;END//DELIMITER;3、DELIMITER//CREATEEVENTclean_user_logsONSCHEDULEEVERY1DAYSTARTSCURRENT_DATE+INTERVAL1DAYDOBEGININSERTINTOarchive_logsSELECT*FROMuser_logsWHERElog_date<NOW()-INTERVAL30DAY;DELETEFROMuser_logsWHERElog_date<NOW()-INTERVAL30DAY;END//DELIMITER;4、DELIMITER//CREATEPROCEDUREadd_employee(INemp_nameVARCHAR(50),INemp_salaryDECIMAL(10,2))BEGININSERTINTOemployees(emp_name,emp_salary)VALUES(emp_name,emp_salary);END//DELIMITER;--调用存储过程CALLadd_employee('张三',5000);5DELIMITER//CREATETRIGGERproduct_update_chec