MySQL数据库设计与应用项目化教程（微课版）-实训指导汇 项目实训1-8 创建网上商城数据库webshop-维护高校图书管理数据库的安全性

项目实训1创建网上商城数据库webshop实训内容1．安装MySQL服务器（1）下载并安装MySQL8.0数据库软件，注意安装过程的配置信息。提示：下载及安装过程参考课本第6-11页内容启动MySQL服务。提示：两种方法启动MySQL服务。Windows服务管理器启动，方法参照课本第12页命令方式启动输入命令netstartmysql80设置Path环境变量。提示：将MySQL安装路径的bin目录添加到path环境变量中，具体方法参照课本第13页，设置完成后，就可以在任意目录下输入mysql命令登录MySQL服务器。以命令行方式登录MySQL8.0服务器。提示：进入命令提示符窗口中，输入命令mysql-uroot-p回车换行后，输入root用户密码，即可启动MySQL服务器。2．安装图形化工具Navicat（1）安装NavicatforMySQL软件。（2）建立连接，登录到MySQL8.0服务器。（3）建立查询，输入命令，查看执行结果。3．创建网上商城数据库webShop（1）创建数据库webShop。提示：两种方法创建数据库，一种方法是使用命令createdatabasewebshop，第二种方法在Navicat工具中创建。查看webShop数据库的编码信息。提示：输入命令showcreatedatabasewebshop;选择该数据库。提示：输入命令usewebshop;项目实训2管理网上商城数据表实训内容1．按照表2-15的要求创建商品表goods。提示：建表语句CREATETABLEgoods(g_idintauto_incrementPRIMARYKEYCOMMENT'商品id',g_namevarchar(120)NOTNULLCOMMENT'商品名称',categoryvarchar(100)COMMENT'商品类别',pricedecimal(10,2)NOTNULLDEFAULT(0)COMMENT'商品价格',numberintUNSIGNEDNOTNULLDEFAULT(0)COMMENT'商品数量',descriptionVARCHAR(255)COMMENT'商品描述');2．按照表2-16的要求创建会员表customers。提示：建表语句CREATETABLEcustomers(c_idintauto_incrementPRIMARYKEYCOMMENT'会员id',c_nameVARCHAR(100)NOTNULLCOMMENT'会员名称',passwordVARCHAR(255)NOTNULLDEFAULT('')COMMENT'密码',genderTINYINTUNSIGNEDNOTNULLDEFAULT(1)COMMENT'性别',birthdateCOMMENT'出生日期',truenamevarchar(20)COMMENT'真实姓名',emailvarchar(100)COMMENT'邮箱',addressvarchar(100)COMMENT'地址',phonechar(11)COMMENT'手机号',levelTINYINTUNSIGNEDCOMMENT'会员等级');按照表2-17的要求创建订单表orders。提示：建表语句CREATETABLEorders(o_idintauto_incrementPRIMARYKEYCOMMENT'订单id',c_idintCOMMENT'会员id',o_sumDECIMAL(10,2)UNSIGNEDCOMMENT'订单总额',o_datedateCOMMENT'下单日期',o_statusvarchar(20)COMMENT'订单状态');按照表2-18的要求创建订单详情表orderDetail。提示：建表语句CREATETABLEorderDetail(od_idintauto_incrementPRIMARYkeyCOMMENT'订单详情id',o_idintCOMMENT'订单id',g_idintCOMMENT'商品id',od_numberintUNSIGNEDnotnullDEFAULT(0)COMMENT'购买数量',noteVARCHAR(255)COMMENT'会员备注');为订单表orders的c_id列添加外键，参照customers表的id列。为订单详情表orderDetail的订单id和商品id列添加外键。提示：添加外键语句ALTERTABLEordersADDCONSTRAINTfk_c_idFOREIGNKEY(c_id)REFERENCEScustomers(c_id);ALTERTABLEorderDetailADDCONSTRAINTfk_o_idFOREIGNKEY(o_id)REFERENCESorders(o_id);ALTERTABLEorderDetailADDCONSTRAINTfk_g_idFOREIGNKEY(g_id)REFERENCESgoods(g_id);为上述各表分别添加数据，各数据表的具体数据如表2-19～表2-22所示。提示：添加数据语句以商品表goods为例：INSERTintogoodsVALUES(null,'海尔滚筒洗衣机','生活电器',2500,10,null);INSERTintogoodsVALUES(null,'美的大口径原汁机','生活电器',300,20,null);INSERTintogoodsVALUES(null,'内蒙古风干牛肉干','食品',100,30,null);INSERTintogoodsVALUES(null,'三只松鼠每日坚果','食品',110,20,null);INSERTintogoodsVALUES(null,'海尔510升冰箱','生活电器',3800,5,null);INSERTintogoodsVALUES(null,'红星100%纯羊毛衫','女装',360,6,null);INSERTintogoodsVALUES(null,'红星猫罐头','食品',265,50,null);INSERTintogoodsVALUES(null,'特仑苏牛奶','食品',80,40,null);分别对上述表数据进行修改、删除、查询操作。提示：使用select、update、delete语句进行查询、修改、删除操作select*fromgoods；UPDATEgoodssetcategory='宠物食品'whereg_name='红星猫罐头';项目实训3查询网上商城数据表实训内容1．针对项目2中实训建立的商品表goods和会员表customers，完成如下查询。（1）查询商品表中所有商品的详细信息。SELECT*FROMgoods;查询商品表中所有商品的商品号、商品名称和商品总额（商品数量和单价的乘积），并以汉字标题显示列名。SELECTg_id商品号,g_name商品名称,price*number商品总额FROMgoods;查询商品类别为“生活电器类”、商品单价在2500元以上的商品的详细信息。SELECT*FROMgoodsWHEREcategory='生活电器'andprice>=2500;查询所有商品名称中包含“红星”的商品的详细信息。SELECT*FROMgoodsWHEREg_nameLIKE'%红星%';商品表中需要了解价格在2500元以上的商品的商品号、商品名称、商品类别和商品单价信息，并要求按类别升序排列，如果是同一类别的商品，则按价格降序排列。SELECTg_id,g_name,category,priceFROMgoodsWHEREprice>=2500ORDERBYcategory,pricedesc;查询商品价格高于平均价格的商品信息。SELECT*FROMgoodsWHEREprice>(selectavg(price)fromgoods);查询customers表中所有年龄在20～25岁的会员的会员名、籍贯和年龄。SELECTc_name,address,YEAR(NOW())-YEAR(birth)c_ageFROMcustomersWHEREYEAR(NOW())-YEAR(birth)BETWEEN20and25;查询customers表中“山东”省的所有男性会员或者是年龄在30岁以下的会员的会员号、会员名、性别、地址和年龄。SELECTc_id,c_name,gender,address,YEAR(NOW())-YEAR(birth)c_ageFROMcustomersWHEREaddresslike'%山东%'andgender=1orYEAR(NOW())-YEAR(birth)<30;查询customers表中不是来自“山东青岛”和“山东济南”两地会员的详细信息。SELECT*fromcustomersWHEREaddressnotlike'%山东%青岛%'ANDaddressnotlike'%山东%济南%';查询customers表中姓“黄”且名字中只有两个汉字的会员的会员名、真实姓名、电话和电子邮箱。SELECTc_name,truename,phone,emailfromcustomersWHEREtruenamelike'黄_';统计customers表中男、女会员的总人数。SELECTgender,count(*)fromcustomersGROUPBYgender;统计goods表中每个类别的商品总数。SELECTcategory,sum(number)fromgoodsGROUPBYcategory;2．针对会员表customers和订单表orders，完成如下查询。（1）统计每个会员的订单总额，显示会员名和总额。SELECTc_name,sum(o_sum)fromcustomersJOINordersoncustomers.c_id=orders.c_idGROUPBYc_name;（2）查询订单总额最高的会员的id和会员名。SELECTcustomers.c_id,c_namefromcustomersJOINordersoncustomers.c_id=orders.c_idGROUPBYcustomers.c_idHAVINGsum(o_sum)>=all(SELECTsum(o_sum)FROMordersGROUPBYc_id);（3）查询订单最多的会员编号。SELECTc_idfromordersGROUPBYc_idHAVINGcount(*)>=all(SELECTcount(*)fromordersGROUPBYc_id);或者SELECTc_idfromordersGROUPBYc_idORDERBYcount(*)descLIMIT1;（4）统计orders表中每个会员的订单总额，并显示大于平均总额的会员编号和订单总额。SELECTc_id,sum(o_sum)fromordersGROUPBYc_idHAVINGsum(o_sum)>(SELECTavg(sum_o_sum)from(selectsum(o_sum)sum_o_sumfromordersGROUPBYc_id)t);（5）查询所有会员的信息，按会员的消费次数降序排列。SELECTcustomers.*FROMcustomersJOINordersoncustomers.c_id=orders.c_idgroupbycustomers.c_idORDERBYcount(*)desc;（6）查询消费30次以上的会员信息。SELECTcustomers.*FROMcustomersJOINordersoncustomers.c_id=orders.c_idgroupbycustomers.c_idHAVINGcount(*)>=30;（7）查询消费金额最高的前三名会员的信息。SELECTcustomers.*FROMcustomersJOINordersoncustomers.c_id=orders.c_idgroupbycustomers.c_idORDERBYsum(o_sum)DESCLIMIT3;（8）统计orders表中每天的订单总额，并根据订单总额降序排列。SELECTo_date,sum(o_sum)fromordersgroupbyo_dateORDERBYsum(o_sum)desc;3．针对商品表goods、会员表customers、订单表orders和订单信息表order_detail，完成如下查询。（1）查询会员id是12的会员的订单下单日期及该订单的商品id、商品名称及购买数量。SELECTc_id,o_date,goods.g_id,g_name,od_numberfromorders,orderdetail,goodsWHEREorders.o_id=orderdetail.o_idANDorderdetail.g_id=goods.g_id;（2）查询牛奶这件商品被哪些会员购买过，查询结果显示会员id和下单时间。SELECTc_id,o_datefromgoods,orderdetail,ordersWHEREorders.o_id=orderdetail.o_idANDorderdetail.g_id=goods.g_idandg_namelike'%牛奶%';（3）查询会员的姓名、手机号及其购买的商品名称、商品类别和单价信息。selectc_name,phone,g_name,category,pricefromcustomers,orders,orderdetail,goodswhereorders.o_id=orderdetail.o_idANDorderdetail.g_id=goods.g_idandorders.c_id=customers.c_id;（4）查询orders表中订单总额大于5000的订单信息，并按订单总额升序排列。select*fromordersWHEREo_sum>5000ORDERBYo_sum;项目实训4网上商城数据库索引和视图的管理实训内容（1）在商品表goods的g_name列上建立前缀索引。CREATEINDEXidx_g_nameongoods(g_name(10));（2）分别在会员表customers的c_name列和phone列上建立唯一索引。CREATEUNIQUEINDEXidx_c_nameoncustomers(c_name);CREATEUNIQUEINDEXidx_phoneoncustomers(phone);（3）在订单表orders的订单总额和下单日期列上建立组合索引。CREATEINDEXidx_o_sum_dateonorders(o_sum,o_date);（4）在订单详情表orderDetail的购买数量列上建立普通索引。CREATEINDEXidx_od_numberonorderdetail(od_number);（5）执行查询操作：查询牛奶这件商品被哪些用户购买过，查询结果显示会员编号和下单时间，观看该查询使用的索引情况。EXPLAINSELECTc_id,o_datefromgoods,orderdetail,ordersWHEREorders.o_id=orderdetail.o_idANDorderdetail.g_id=goods.g_idandg_namelike'%牛奶%';（6）创建视图view_order，包括会员的会员名称、姓名、手机号及其所购买商品的ID、商品名称、类别、单价信息及购买时间。CREATEORREPLACEVIEWview_orderasselectc_name,truename,phone,goods.g_id,g_name,category,price,o_date,od_numberfromcustomers,orders,orderdetail,goodswhereorders.o_id=orderdetail.o_idANDorderdetail.g_id=goods.g_idandorders.c_id=customers.c_id;（7）基于视图view_order进行查询：查询商品名称包含“红星”字样的商品的销售情况，被哪些会员购买过及购买时间和数量。SELECTc_name,truename,g_id,g_name,o_date,od_numberfromview_orderwhereg_namelike'%红星%';（8）基于视图view_order进行查询：查询购买商品30次以上的会员信息，包括会员名称、姓名、手机号。SELECTc_name,truename,phoneFROMview_orderGROUPBYc_namehavingcount(*)>=30;（9）在视图view_order中修改某会员的手机号，查看基本表customers中数据的变化情况。UPDATEview_ordersetphone='28888222233'WHEREc_name='orange';修改后查看customers表，数据应该被修改了。（10）创建视图view_sales，统计该网站商品的销售排行，按照购买次数降序排列。CREATEVIEWview_salesasSELECTgoods.g_id,g_name,category,price,sum(od_number)fromgoods,orders,orderdetailwhereorders.o_id=orderdetail.o_idANDorderdetail.g_id=goods.g_idGROUPBYgoods.g_idorderbysum(od_number)desc;项目实训5设计高校图书管理数据库实训内容（1）下面是某高校图书馆的图书管理系统的需求描述，仔细阅读以下文字后，为该系统数据库设计出E-R图。某高校图书馆存储了上百万种不同分类的图书，为了更方便地管理、借阅这些图书，需要开发一套管理系统，以提高图书借阅管理水平，方便读者借阅。该系统提供以下功能。管理员可添加图书信息，如果不存在同名、同作者的图书，则新增图书，否则修改图书数量。管理员可管理图书类别。管理员可为读者办理一张借阅卡。普通借阅卡最多可借阅3本书，VIP卡最多可借阅5本书。如果已达到借阅卡借阅数量的限制，还想再借阅图书，则必须先归还已借图书。可按图书编号、图书名称、作者、图书类型等查询出相应的图书信息。图书编号、图书名称、作者、图书类型、价格、当前数量列出的图书信息包括等。可查询某个读者（借阅卡）当前的借阅情况：哪些图书未归还，还可借几本。可查询某个借阅卡的历史借阅情况。提示：该系统可以设计成E-R图如下：其中读者实体的属性可以有：读者编号、姓名、性别、专业、班级等图书实体的属性可以有：图书编号、图书名称、作者、图书类型、价格、当前数量等借阅卡实体的属性可以有：借阅卡号、借阅卡等级图书类型实体的属性可以有：类型号、类型名称等将设计出的E-R图转换成关系模型。将上题设计的E-R图转换成关系模型为：图书（图书编号，图书名称，图书类型号,作者，价格，数量）读者（读者编号，姓名，性别，专业，班级）借阅卡（卡号，读者编号，借阅卡等级，办卡时间）借阅（图书编号，借阅卡号，借阅时间、归还时间）图书类型（类型号，类型名称）字段名说明数据类型描述bookid图书编号字符串长度为5，主码bookname图书名称字符串长度为50typeid图书类型编号字符串引用图书类型表的外键，长度为2author作者字符串长度为50price价格数值浮点数amount数量数值不小于0的整数为该数据库设计物理结构，以表格形式列出数据库中各表的字段名、类型、主键及约束情况。图书表（book）图书类型表（booktype）字段名说明数据类型描述typeid图书类型编号字符串长度为2，主码typename类型名称字符串长度为5description类型描述字符串长度为50读者表（reader）字段名说明数据类型描述readerid读者编号字符串主键，长度为11readername姓名字符串长度为50，非空gender性别字符串长度为1class所在班级字符串长度为10借阅卡表（card）字段名说明数据类型描述cardid卡号字符串长度为8，主码readerid读者编号字符串引用读者表的外键，长度为6cardgrade借阅卡等级数值0--普通卡，

10--

VIP卡cardtime办卡时间DATE借阅情况表（lend）字段名说明数据类型描述bookid图书编号字符串组合主键，外键，长度为5cardid卡号字符串组合主键，外键，长度为8lendtime借阅时间DATE非空returntime归还时间DATE项目实训6为高校图书管理系统设计SQL编程实训内容1．在高校图书管理系统数据库中建立函数，实现功能：查询某读者的可借数量，已知该读者的借阅卡号。读者可参考以下提示。（1）要知道读者的借阅卡等级，0表示普通卡，10表示VIP卡。（2）普通卡最多可借3本，VIP卡最多可借5本。（3）求出当前读者已借但还未归还的图书数量。（4）当前读者最多可借数量减去目前已借但未归还的数量即为可借阅数量。提示：建立函数代码如下：CREATEFUNCTIONlendNumber(c_idchar(8))RETURNSINTREADSSQLDATABEGIN DECLAREc_gradeTINYINT;#定义变量存放借阅卡等级 DECLAREnumINT;#定义变量存放读者已借没还的图书数量 SELECTcardgradeINTOc_gradeFROMcardWHEREcardid=c_id;#求该卡的借阅等级 #求该读者已借但未归还的数量 SELECTcount(*)intonumfromlendWHEREcardid=c_idANDreturntimeisnull; #借阅卡等级为0表示普通卡，最多可借3本，不是0表示vip卡，可借5本IFc_grade=0 THENRETURN(3-num); ELSERETURN(5-num); ENDIF;END2．在高校图书管理系统数据库中建立存储过程，实现某个读者的借书操作。读者可参考以下提示。（1）判断当前读者可借阅数量是否大于0。（2）判断所借图书的库存量是否大于0。（3）若满足上述条件，则产生一条借阅记录，同时所借图书的库存数量减1。提示：建立存储过程代码如下：CREATEPROCEDURElendbook(INcard_idchar(8),INbook_idchar(5))BEGIN DECLAREnumint; #定义变量存放当前已借未还的图书数量 DECLAREc_gradeTINYINT;#定义变量存放借阅卡等级 DECLARElend_numberINT;#定义变量存放可借图书数量 DECLAREbook_numberINT;#定义变量存放该书库存数量 SELECTcount(*)intonumfromlendWHEREcardid=card_idANDreturntimeisnull; SELECTcardgradeINTOc_gradeFROMcardWHEREcardid=card_id;#求该卡的借阅等级 IF(c_grade=0) THENSETlend_number=3-num; ELSESETlend_number=5-num; ENDIF; SELECTamountintobook_numberfrombookwherebookid=book_id;#求该书库存数量 if(lend_number>0&&amount>0)#当可借图书数量和图书库存数量都大于0时才能借书 THEN insertintolendVALUES(book_id,card_id,now(),null);#向借阅表中添加记录 UPDATEbooksetamount=amount-1wherebookid=book_id;#将图书库存数量减1 ELSEselect'error,can\'tlendbook'; #输出不能借书的信息END3．在高校图书管理系统数据库中建立存储过程，实现某个读者归还图书。读者可参考以下提示。（1）找出该笔借阅记录。（2）更新记录中的归还时间，并且所归还图书的库存数量加1。提示：建立存储过程代码如下：CREATEPROCEDUREreturnbook(INcard_idchar(8),INbook_idchar(5))BEGIN UPDATElendSETreturntime=now()WHEREcardid=card_idandbookid=book_id; updatebookSETamount=amount+1WHEREbookid=book_id;END4．建立触发器。当新进图书，添加图书信息时，将添加图书操作的用户名、操作时间记录到图书日志表中。提示：首先创建日志表，其次创建触发器创建日志表，代码如下：CREATETABLEinstBookLog(idintauto_incrementPRIMARYkey,usernamechar(10),opertimedatetime);创建触发器，代码如下：CREATETRIGGERtrigger_bookAFTERINSERTONbookFOREACHROWBEGIN insertintoinstBookLog(username,opertime)VALUES(user(),NOW());END项目实训7为高校图书管理系统添加事务控制实训内容为项目6中建立的高校图书管理系统的借书操作添加事务控制。提示：在项目实训6中创建的存储过程lendbook中添加事务开始和提交事务语句。代码如下：CREATEPROCEDURElendbook(INcard_idchar(8),INbook_idchar(5))BEGIN DECLAREnumint; #定义变量存放当前已借未还的图书数量 DECLAREc_gradeTINYINT;#定义变量存放借阅卡等级 DECLARElend_numberINT;#定义变量存放可借图书数量 DECLAREbook_numberINT;#定义变量存放该书库存数量 STARTTRANSACTION; #开始事务 SELECTcount(*)intonumfromlendWHEREcardid=card_idANDreturntimeisnull; SELECTcardgradeINTOc_gradeFROMcardWHEREcardid=card_id;#求该卡的借阅等级 IF(c_grade=0) THENSETlend_number=3-num; ELSESETlend_number=5-num; ENDIF; SELECTamountintobook_numberfrombookwherebookid=book_id;#求该书库存数量 if(lend_number>0&&amount>0)#当可借图书数量和图书库存数量都大于0时才能借书 THEN insertintolendVALUES(book_id,card_id,now(),null);#向借阅表中添加记录 UPDATEbooksetamount=amount-1wherebookid=book_id; ELSEselect'error,can\'tlendbook'; #输出不能借书的信息 ENDIF; COMMIT; #提交事务END（2）为项目6中建立的高校图书管理系统的还书操作添加事务控制。提示：在项目实训6中创建的存储过程returnbook中添加事务开始和提交事务语句。代码如下：CREATEPROCEDUREreturnbook(INcard_idchar(8),INbook_idchar(5))BEGIN STARTTRANSACTION; #开始事务 UPDATElendSETreturntime=now()WHEREcardid=card_idandbookid=book_id; updatebookSETamount=amount+1WHEREbookid=book_id; COMMIT; #提交事务END项目实训8维护高校图书管理数据库的安全性实训内容（1）按照下列要求操作，为图书管理数据库创建用户，并设置权限。①创建用户reader，设置密码为123456。提示：命令代码如下：CREATEUSER'reader'@'localhost'IDENTIFIEDby'123456';②将用户reader的密码修改为'good_luck'。ALTERUSER'reader'@'localhost'IDENTIFIEDby'good_luck';③为用户reader授予查看图书表、读者表、类别表和借阅表的权限。提示：依次将上述权限授予给reader用户，假设数据库名为bookManage，命令代码如下：GRANTSELECTonbookManage.bookTO'reader'@'localhost';GRANTSELECTonbookMa