MySQL数据库技术与项目应用教程（微课版）（AI助学）（第3版）-习题答案 项目7

MySQL数据库技术与项目应用教程（微课版）(AI助学)（第3版）习题答案项目七维护网上商城系统数据库安全单选题：CDACDDADDB思考题(参考)：1.在计算机中，当多个进程竞争同一个资源时，即在数据库中多个用户并发存取数据，发生的多个进程相互等待，如果没有外力作用都无法推进下去时，就发生了死锁。那么除了教材给出的死锁的示例，你是否还能举出数据库发生死锁的示例，并尝试给出该死锁的解决办法。答：只要举例中符合，两个进程因为竞争同一个数据，而引起的进程相互等待，无法推进即可。解决方法，MYSQL中innodb_lock_wait_timeout参数可以设置超实时长，当等待时间过长，进程无法推进之时，会强制中断进程，以解决死锁问题。2.为了保障数据安全，我们在数据库中可以创建用户，也可以创建角色，同时可以为它们授予权限，或者回收权限。那么用户和角色有哪些异同点呢?请简述你的理解。答：数据库用户，是使用和共享数据库资源的人。数据库系统内部记录着所有合法用户的标识和口令，以鉴定用户是否为数据库合法用户。用户可以获得对于数据库某些特定部分（如一个关系或视图）上某些权限（如插入数据，更新数据等），获得权限的合法用户，可以在权限范围内对数据库进行操作数据库角色，是被命名的一组与数据库相关的权限，角色是权限的集合。因此，可以为一组具有相同权限的用户创建一个角色。例如，在大学中，不同的人具有不一样的角色，教师作为一个角色，会在同一组关系上具有相同类型的权限，无论何时制定以为新教师，她都必须被授予这些权限。那么我们可以指明每个教师应该被授予的权限，并单独标示出哪些数据库用户是教师。当雇佣一位新教师时，这时我们为他分配一个用户标示符，新增了一个用户，并且同时将他标示成为一名教师，他将获得所有教师应有的权限，不再需要单独授予他权限了。项目实践：用户权限(1)使用SQL语句创建一个用户zhao,密码为123456。createuser'zhao'@'localhost'identifiedby'123456';(2)使用SQL语句创建一个用户zhang，密码为123456。createuser'zhang'@'localhost'identifiedby'123456';(3)使用SQL语句创建一个用户wang，密码是123456，同时授予该用户对onlinedb数据库中users表的SELECT权限。createuser'wang'@'localhost'identifiedby'123456';grantselectononlinedb.usersto'wang'@'localhost';(4)使用SQL语句回收用户wang在users表上的SELECT权限。revokeselectononlinedb.usersfrom'wang'@'localhost';(5)使用SQL语句修改用户zhang的登录密码，登录密码修改zhang123456。alteruser'zhang'@'localhost'identifiedby'zhang123456';(6)使用SQL语句创建两个角色userAdmin和goodsAdmin。createuser'userAdmin'@'localhost';createuser'goodsAdmin'@'localhost';(7)使用SQL语句将onlinedb数据库中users表上的UPDATE、SELECT和DELETE权限授予角色userAdmin，将goods表上的INSERT、UPDATE、DELETE和SELECT权限授予角色goodsAdmin。grantupdate,select,deleteononlinedb.usersto'userAdmin'@'localhost';grantinsert,update,select,deleteononlinedb.goodsto'goodsAdmin'@'localhost';事务(8)使用事务实现，当更改category表中某个商品的类别id时，同时将goods表对应的类别id全部更新。delimiter//createprocedureproc_updateType(oldTypeIDint,newTypeIDint)beginstarttransaction; updatecategory setcid=newTypeIDwherecid=oldTypeID; ifrow_count()<1then rollback; else updategoods setcid=newTypeID wherecid=oldTypeID; commit; endif; end//(9)在网上商城系统的数据库中，创建存储过程，实现用户从购物车中下单购买商品业务(使用事务机制实现)。(提示:用户从购物车中下单购买商品时，需要在订单表和订单详情表中完成相应的数据插入操作，将该商品从购物车中删除，同时，更新商品的销售数量及库存数量)delimiter//createprocedureproc_addOrder(userIDint,goodsIDint)begindeclareodTotalfloat;--用户当前下单商品总金额 declareodidint;--用户下单后生成的订单id号 declaregoodsNumint;--用户当前下单商品数量 declareaffectRowint;--记录当前数据库操作影响的行数以判断数据库操作是否成功 --指定事务的起始位置 loop_lable:LOOP--启动事务 starttransaction; --获取当前购物车中待下单商品总金额selectsum(cNum*(selectgpricefromgoodswheregid=goodsID))intoodTotal fromcart whereuid=userIDandgid=goodsID; --生成订单insertintoorders(uid,ocode,oamount) values(userID,concat('O',ceil(CURRENT_TIMESTAMP+0+rand()*1000)),odTotal); --获取执行行数 selectrow_count()intoaffectRow; --获取订单ID setodid=LAST_INSERT_ID(); --如果生产订单失败，则回滚ifaffectRow<1then rollback; leaveloop_lable;endif; --统计此单购买商品数量 selectcnumintogoodsNum fromcart whereuid=userIDandgid=goodsID; --将用户下单商品信息插入订单详情表 insertintoOrdersItem(oid,gid,inum) values(odid,goodsid,goodsNum); --获取执行行数 selectrow_count()intoaffectRow; --如果插入数据失败，则回滚ifaffectRow<1then rollback; leaveloop_lable;endif; --在购物车中删除已下单商品 deletefromcartwhereuid=userIDandgid=goodsID; --获取执行行数 selectrow_count()intoaffectRow; --如果删除数据失败，则回滚ifaffectRow<1then rollback; leaveloop_lable;endif; --在商品表中对当前下单商品已售数量和库存量进行更新 updategoods setgquantity=gquantity-goodsNum,gSale_Qty=gSale_Qty+goodsNum wheregid=goodsID; --获取执行行数 selectrow_count()intoaffectRow; --如果更新数据失败，则回滚ifaffectRow<1then rollback;else commit;endif; leaveloop_lable; endloop;end//(10)根据【例7.32】，在事务A中设置隔离级别为“REPEATABLEREAD”，并在事务B中进行数据修改操作，查看事务A中读数据的情况。1）在事务A窗口中，设置隔离级别为READ-COMMITTED。并且，开启事务，并读取cid为1的商品，显示商品id、商品名称、价格。2）在事务B窗口中，设置隔离级别为READ-COMMITTED。并且，开启事务，并修改gid为1的商品价格为26，并提交事务。3)回到事务A窗口，再次查看数据，此时可以发现，gid为1的商品，价格还是29，同一事务中两次查询同一条数据，没有出现数据不一致的问题，解决了不可重复读的错误。4）事务A提交之后，查询gid为1的商品，价格为26。(11)根据【例7.32】，在事务A中设置隔离级别为“SERIALIZABLE”，并在事务B中进行数据修改操作，查看事务A中读数据的情况。1）在事务A窗口中，设置隔离级别为SERIALIZABLE。并且，开启事务，并读取cid为1的商品，显示商品id、商品名称、价格。2）在事务B窗口中，设置隔离级别为SERIALIZABLE。并且，开启事务，并修改gid为1的商品价格为29，此时，我们发现数据修改因超时而失败。当一个开启一个事务读数据是，被读的数据，无法被另一个事务所修改拓展实训：(1)使用SQL语句创建一个名为poemAdmin的用户，密码为123456。createuser'poemAdmin'@'localhost'identifiedby'123456';(2)使用SQL语句为已经创建的用户poemAdmin，授予对数据库poemGameDB中的poemType表和poemLing表的UPDATE、INSERT及DELETE权限grantupdate,insert,deleteonpoemGameDB.poemTypeto'poemAdmin'@'localhost';grantupdate,insert,deleteonpoemGameDB.poemLingto'poemAdmin'@'localhost';（3）使用事务实现，当更改poet表中某个诗人的id时，同时将poem表对应的诗人id全部更新。delimiter//createprocedureproc_updatePoetID(oldPIDint,newPIDint)beginstarttransaction; updatepoet setpid=newPIDwherepid=oldPID; ifrow_count()<1then rollback; else updatepoem setpid=newPID wherepid=oldPID; commit; endif; end//(4)使用事务实现，当某一首诗歌的热度值pmHot发生改变时，创作该诗歌的诗人的热度值pHot也相应地被更新delimiter//createprocedure