MySQL数据库应用基础刘代雄习题答案

MySQL数据库应用基础刘代雄习题答案参考答案项目一一、选择题1.C2、B3、A4、B5、C二、填空题1.实体完整性2、数据库3、层次模型4、选择5、国产三、简答题1.关系模型采用二维表结构组织数据，数据间的关系通过外键体现；层次模型采用树形结构，数据间是父节点与子节点的从属关系，每个节点除根节点外仅有一个父节点。2、数据冗余指同一数据在多个地方重复存储。例如，订单表和商品表都存储“商品名称”，当商品名称变更时，需同时更新多个表，易导致数据不一致（更新异常）。3、数据库设计基本流程包括：需求分析、概念结构设计（如绘制E-R图）、逻辑结构设计（将E-R图转换为关系模型）、物理结构设计（确定存储结构和存取方法）、数据库实施、数据库运行与维护。四、思考题1.（1）不符合3NF。存在传递函数依赖，如“分类名称”依赖于“商品编号”（通过分类编号传递），“供应商名称”“供应商地址”依赖于“商品编号”（通过供应商编号传递）。（2）优化后包含4张表：商品表（商品编号，商品名称，分类编号，价格，库存），关键字：商品编号；商品分类表（分类编号，分类名称），关键字：分类编号；供应商表（供应商编号，名称，地址），关键字：供应商编号；商品-供应商关联表（商品编号，供应商编号），关键字：商品编号+供应商编号。2、各表结构如下：生鲜商品表（商品编号，名称，产地，保质期，存储温度），关键字：商品编号；订单表（订单编号，客户编号，下单时间，配送地址），关键字：订单编号，外键：客户编号；订单明细表（明细编号，订单编号，商品编号，购买数量，单价），关键字：明细编号，外键：订单编号、商品编号。3、（1）方案B更符合规范化要求。方案A中“商品名称”可通过“商品编号”从商品表获取，属于冗余属性，存在传递依赖；方案B消除了冗余。（2）方案A需更新所有包含该商品的订单明细记录；方案B只需更新商品表中的商品名称，订单明细表无需改动。项目二一、选择题1.B2、B3、B4、B5、C二、填空题1.关系型2、netstopMySQL803、数据库管理工具4、mysql-hlocalhost-P3306-uroot-p5、utf8mb4三、简答题1.步骤：在Windows搜索栏输入“services.msc”并回车，打开服务列表；找到服务名称为“MySQL80”（或自定义名称）的服务，右键点击选择“启动”即可。2、操作：打开命令行窗口，通过“cd”命令进入MySQL安装目录的bin文件夹（如cdC:\ProgramFiles\MySQL\MySQLServer8.0\bin）；然后执行连接命令（如mysql-hlocalhost-uroot-p）。3、步骤：打开Navicat并连接MySQL服务器；右键左侧连接名称，选择“新建数据库”；在弹出窗口中，“数据库名”填写“shop_db”，“字符集”选择“utf8mb4”，点击“确定”。四、操作题1.步骤：打开命令行窗口；输入命令“mysql-hlocalhost-P3306-uroot-p”并回车；系统提示“Enterpassword:”时，输入root用户的密码（输入时不显示明文），回车即可登录。2、步骤：①新增：双击“products”表进入数据视图，点击“+”图标，在对应字段输入p001、“笔记本电脑”、5999，回车保存；②修改：在数据视图中找到该记录，直接将价格改为5499，点击“保存”按钮；③删除：选中该记录，右键选择“删除记录”，点击“确定”。3、优缺点对比：命令行：优点是轻量、适合自动化脚本；缺点是操作复杂，不直观，不适合新手。Navicat：优点是图形化界面，操作直观，适合快速编辑和查询；缺点是需安装工具，资源占用相对较高。项目三一、选择题1.B2、C3、A4、B5、B二、填空题1.字段名INTPRIMARYKEYAUTO_INCREMENT2、UNIQUE3、参照4、DESC（或DESCRIBE）5、ALTERTABLEorderAUTO_INCREMENT=100三、简答题1.原因：一个订单可包含多个商品，拆分后可清晰记录每个商品的购买信息（如数量、单价），避免数据冗余。体现了“一事一地”的设计原则，即一个表只存储一类信息。2、作用：保证从表（订单表）的cust_id在主表（客户表）中存在，维护数据一致性。不设置外键可能出现“孤儿订单”（订单关联的客户不存在），导致数据混乱。3、优势：自动生成唯一主键，简化数据录入，避免手动输入重复。修改起始值场景：与其他系统对接时统一编号规则（如订单号从100开始）、避免与历史数据冲突。四、操作题1.①SQL命令：CREATEDATABASEproduct_order_dbCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci;②SQL命令：USEproduct_order_db;CREATETABLEproduct(prod_idINTPRIMARYKEYAUTO_INCREMENT,prod_nameVARCHAR(100)NOTNULL,priceDECIMAL(10,2)NOTNULLDEFAULT0.00,stockINTNOTNULLDEFAULT0,categoryVARCHAR(50));作用：主键（prod_id）唯一标识商品；默认值（price为0.00、stock为0）在未输入时自动填充，简化录入。2、①SQL命令：ALTERTABLEproductADDproduction_dateDATE;②插入语句：INSERTINTOcustomer(cust_name,cust_email,cust_phone)VALUES('张三','zhangsan@',),('李四','zhangsan@',);错误提示：Duplicateentry'zhangsan@'forkey'cust_email'（唯一约束冲突）。③插入语句：INSERTINTOorder(cust_id)VALUES(9999);错误提示：Cannotaddorupdateachildrow:aforeignkeyconstraintfails（外键约束冲突）。3、①SQL命令：ALTERTABLEorderAUTO_INCREMENT=100;②插入语句：INSERTINTOorder(cust_id)VALUES(1);（假设客户表中存在cust_id=1的记录）③查询命令：SELECT*FROMorder;结果说明：order_id的值为100，验证自动递增起始值修改生效。项目四一、选择题1.C2、C3、B4、A5、A二、填空题1.字段列表2、SET3、WHERE4、清空表中所有记录5、单引号（'）三、简答题1.原因：省略WHERE子句会导致表中所有记录被修改，引发数据错误。避免方法：执行前用SELECT查询验证范围；批量修改时先加LIMIT测试；开启事务，错误可回滚。2、区别：①DELETE支持WHERE子句（删除部分记录），TRUNCATE不支持（只能清空表）；②DELETE是DML操作（可回滚），TRUNCATE是DDL操作（不可回滚）。3、原因：插入的主键值已存在于表中。解决方法：修改插入的主键值为唯一值；若为自增主键，检查自增序列是否与现有数据冲突。四、操作题1.INSERTINTOcustomer(cust_id,cust_name,phone,address,reg_time)VALUES('CUST-005','赵六',,'广州市天河区','2025-08-0209:30:00');2、UPDATEproductSETprice=349.00,stock=stock+20WHEREprod_id='PROD-20240801-001';3、DELETEFROMordersWHEREcreate_time<'2023-06-01'ANDstatus='已关闭';项目五参考答案一、选择题1、B2、A3、C4、B5、D二、填空题1、ORDERBY字段名ASC2、LEFTOUTERJOIN3、NULL4、WHERE（或HAVING等合理子句）5、别名三、简答题1、区别：①WHERE用于分组前筛选行，不能跟聚合函数；HAVING用于分组后筛选分组，可跟聚合函数。②WHERE子句在GROUPBY之前执行，HAVING在GROUPBY之后执行。2、子查询是嵌套在其他查询中的查询。可嵌套在主查询的WHERE、SELECT、FROM、HAVING等子句中。3、原因：①增加数据传输量，降低查询效率；②表结构变更时易导致查询结果异常，不利于代码维护。四、操作题1、SELECT商品名称，单价，库存FROMcommodityWHERE类别='家电'AND库存>50ORDERBY库存DESC;2、SELECTc.客户姓名，o.订单编号，o.商品编号，o.订单日期FROMordersoINNERJOINcustomercONo.客户编号=c.客户编号WHEREc.所在城市='上海';3、SELECT商品编号，SUM(销售数量)AS总销量FROMsalesGROUPBY商品编号HAVING总销量>100ORDERBY总销量ASC;项目六参考答案一、选择题1、A2、B3、D4、B5、C二、填空题1、UNIQUEINDEX2、WITHCHECKOPTION（或更准确为“禁止修改可通过WITHREADONLY”）3、哈希索引（或全文索引等）4、预定义的查询语句5、联合索引（或复合索引）三、简答题1、影响：①查询操作：索引可加快查询速度，通过索引直接定位数据，减少全表扫描；②增删改操作：索引会增加操作开销，因为修改数据时需同步更新索引结构，降低操作效率。2、前缀索引是对字符串列的前N个字符创建的索引，适用于长字符串列（如商品名称、地址），可减少索引存储空间，提高索引效率。3、作用：视图可隐藏敏感字段，控制数据可见性。例如，员工表含“工资”敏感字段，创建视图时仅包含“工号、姓名、部门”，普通用户查询视图时无法看到工资，实现数据访问限制。四、操作题1、CREATEINDEXidx_category_priceONcommodity(类别，单价);2、CREATEVIEWv_valid_ordersASSELECT订单编号，客户编号，订单日期FROMordersWHERE状态='已完成'AND总金额>1000;3、创建视图命令：CREATEVIEWv_customer_publicASSELECT客户编号，姓名，会员等级FROMcustomer;查询视图命令：SELECT*FROMv_customer_public;项目七参考答案一、选择题1.C2.C3.B4.B5.B二、填空题1.输入输出参数（INOUT）；2.DELETE；3.CREATEPROCEDURE；4.全表；5.REVOKE三、简答题1、存储过程的优点包括：①提高执行效率（预编译存储）；②增强代码复用性（可重复调用）；③提升安全性（减少直接操作表的权限暴露）；④减少网络传输（仅传输调用指令而非完整SQL）。2、触发器的作用是在表发生特定事件（如INSERT、UPDATE、DELETE）时自动执行，用于维护数据完整性和实现自动化处理。例如：当订单表中插入新订单时，触发器自动减少对应商品的库存。3、保证安全性的方法：①遵循最小权限原则，仅授予必要权限；②限制敏感操作在存储过程中执行，避免直接暴露表权限；③定期审计存储过程和触发器的代码，排查恶意逻辑；④使用加密或权限隔离保护敏感数据。四、操作题1、创建存储过程查询客户所有订单信息：DELIMITER//CREATEPROCEDUREget_customer_orders(INc_idVARCHAR(20))BEGINSELECTo.order_id,o.order_date,o.order_status,calculate_order_total(o.order_id)AStotal_amountFROMordersoWHEREo.customer_id=c_id;END//DELIMITER;2、创建库存恢复日志表及触发器：--创建库存恢复日志表CREATETABLEstock_recovery_log(log_idINTAUTO_INCREMENTPRIMARYKEY,order_idVARCHAR(20),goods_idVARCHAR(20),recovery_quantityINT,recovery_timeDATETIME,FOREIGNKEY(order_id)REFERENCESorders(order_id),FOREIGNKEY(goods_id)REFERENCESgoods(goods_id));--创建触发器DELIMITER//CREATETRIGGERtrg_order_cancel_recover_stockAFTERUPDATEONordersFOREACHROWBEGINDECLAREg_idVARCHAR(20);DECLAREqtyINT;DECLAREdoneINTDEFAULT0;DECLAREcurCURSORFORSELECTgoods_id,quantityFROMorder_detailsWHEREorder_id=OLD.order_id;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;IFOLD.order_status!='已取消'ANDNEW.order_status='已取消'THENOPENcur;read_loop:LOOPFETCHcurINTOg_id,qty;IFdoneTHENLEAVEread_loop;ENDIF;UPDATEgoodsSETstock_quantity=stock_quantity+qtyWHEREgoods_id=g_id;INSERTINTOstock_recovery_log(order_id,goods_id,recovery_quantity,recovery_time)VALUES(OLD.order_id,g_id,qty,NOW());ENDLOOPread_loop;CLOSEcur;ENDIF;END//DELIMITER;3、授予并回收权限：--授予权限GRANTEXECUTEONPROCEDUREget_customer_ordersTO'user3'@'localhost';--回收权限REVOKEEXECUTEONPROCEDUREget_customer_ordersFROM'user3'@'localhost';项目八课后习题答案一、选择题1、C2、B3、B4、B5、A二、填空题1、字段分隔符2、log_bin=mysql-bin（或类似配置，核心为log_bin）3、全部（或完整）4、SELECT5、导入（或恢复）三、简答题1.SQL语句导出：适用场景为数据迁移、小批量数据备份等。优点是能精确控制导出的数据格式，可与其他数据库操作无缝结合；缺点是操作相对复杂，对操作人员的SQL技能要求较高。Navicat备份：适用于非技术人员或批量操作场景。优点是通过可视化界面操作，简单快捷，无需编写复杂SQL语句；缺点是依赖图形化工具，灵活性相对较弱。文件复制：仅适用于MyISAM存储引擎的数据库。优点是操作简单，速度快；缺点是适用范围受限，不适用于其他存储引擎。2.MySQL日志主要类型及作用：二进制日志用于数据增量恢复，记录数据变更操作；错误日志用于故障排查，记录MySQL启动、关闭和运行中的错误信息；查询日志记录所有用户执行的SQL语句，可用于分析用户操作；慢查询日志用于性能优化，记录执行时间超过设定阈值的查询语句。启用二进制日志：通过修改MySQL配置文件（my.ini或f），在[mysqld]节点下添加配置log_bin=mysql-bin，保存并重启MySQL服务。3.权限问题及解决：备份时可能缺少SELECT权限或FILE权限，解决办法是授予相应权限，如GRANTSELECT,FILEON数据库名.*TO'用户名'@'主