数据库技术及应用（MySQL）企业真实数据库项目3.电商信息管理数据库

单元7综合案例：电商平台数据库设计与优化在MySQL中创建一个新的数据库用于存储电商平台数据信息。CREATEDATABASEecommerce_dbCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;--选择使用ecommerce_db数据库USEecommerce_db创建用户表（Users）CREATETABLEuser(user_idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULLUNIQUE,passwordVARCHAR(255)NOTNULL,emailVARCHAR(100)NOTNULLUNIQUE,phone_numberVARCHAR(20),registration_dateDATE,last_login_timeTIMESTAMP,account_statusENUM('Active','Inactive','Banned'));创建日志表（log）CREATETABLElog(log_idINTPRIMARYKEYAUTO_INCREMENT,user_idINT,operation_typeVARCHAR(255),operation_timeTIMESTAMP,operation_detailsTEXT,FOREIGNKEY(user_id)REFERENCESuser(user_id));创建订单表（order）CREATETABLE`order`(order_idINTPRIMARYKEYAUTO_INCREMENT,user_idINT,total_amountDECIMAL(10,2),creation_timeTIMESTAMP,order_statusENUM('Pending','Processing','Shipped','Delivered','Cancelled'),payment_methodVARCHAR(50),shipping_addressVARCHAR(255),delivery_timeTIMESTAMP,FOREIGNKEY(user_id)REFERENCESuser(user_id));创建商品表（product）CREATETABLEproduct(product_idINTPRIMARYKEYAUTO_INCREMENT,product_nameVARCHAR(255)NOTNULL,product_descriptionTEXT,stock_quantityINT,priceDECIMAL(10,2),product_statusENUM('Available','OutofStock','Discontinued'));创建订单商品表（Order_Product）CREATETABLEOrder_Product(order_idINT,product_idINT,quantityINT,FOREIGNKEY(order_id)REFERENCES`order`(order_id),FOREIGNKEY(product_id)REFERENCESproduct(product_id));创建评价表（Review）CREATETABLEReview(review_idINTPRIMARYKEYAUTO_INCREMENT,user_idINT,product_idINT,review_contentTEXT,ratingINT,review_dateDATE,FOREIGNKEY(user_id)REFERENCESuser(user_id),FOREIGNKEY(product_id)REFERENCESproduct(product_id));（1）为user表添加记录。INSERTINTOuser(username,password,email,phone_number,registration_date,last_login_time,account_status)VALUES('张三','password1','zhangsan@',,'2023-01-01','2023-06-0110:00:00','Active'),('李四','password2','lisi@',,'2023-01-02','2023-06-0210:00:00','Active'),('王五','password3','wangwu@',,'2023-01-03','2023-06-0310:00:00','Inactive');（2）为product表添加记录。INSERTINTOproduct(product_name,product_description,stock_quantity,price,product_status)VALUES('手机','高性能智能手机',100,3999.99,'Available'),('笔记本电脑','轻薄便携笔记本电脑',50,5999.99,'Available'),('平板电脑','便携式平板电脑',75,2999.99,'OutofStock');（3）为order表添加记录。INSERTINTO`order`(user_id,total_amount,creation_time,order_status,payment_method,shipping_address,delivery_time)VALUES(1,3999.99,'2023-05-0109:00:00','Shipped','CreditCard','北京市海淀区','2023-05-0209:00:00'),(2,5999.99,'2023-05-0209:00:00','Processing','Alipay','上海市浦东新区',NULL),(3,2999.99,'2023-05-0309:00:00','Cancelled','WeChatPay','深圳市南山区',NULL);（4）为order_product表添加记录。INSERTINTOOrder_Product(order_id,product_id,quantity)VALUES(1,1,1),(2,2,1),(3,3,1);（5）为review表添加记录。INSERTINTOreview(user_id,product_id,review_content,rating,review_date)VALUES(1,1,'手机很好用，性价比高',5,'2023-05-05'),(2,2,'笔记本电脑非常轻便，适合办公使用',4,'2023-05-06'),(3,3,'平板电脑还不错，但是目前缺货',3,'2023-05-07');（6）为log表添加记录。INSERTINTOlog(user_id,operation_type,operation_time,operation_details)VALUES(1,'Login','2023-06-0110:00:00','用户张三登录系统'),(2,'Order','2023-05-0209:00:00','用户李四下单购买笔记本电脑'),(3,'CancelOrder','2023-05-0309:00:00','用户王五取消订单');（1）将用户“张三”的邮箱地址更新为新的邮箱地址“new_email@”。UPDATEuserSETemail='new_email@'WHEREusername='张三';（2）在销售过程中，商品的库存数量需要实时更新。将商品“手机”的库存数量减少1。UPDATEproductSETstock_quantity=stock_quantity-1WHEREproduct_name='手机';（3）订单状态的更新是订单处理流程中的重要环节。将订单ID为1的订单状态更新为“已发货”。UPDATE`order`SETorder_status='Shipped'WHEREorder_id=1;（1）查看用户名为“张三”的账号状态。SELECTusername,account_statusFROMuserWHEREusername='张三';（2）查看订单总金额大于100元的订单数量。SELECTCOUNT(*)ASorder_countFROM`order`WHEREtotal_amount>100;（3）查看价格在2000元到3000元之间的商品记录。SELECT*FROMproductWHEREpriceBETWEEN2000AND3000;（4）查询用户表和订单表中的数据，以获取每个用户的所有订单信息。SELECTuser.user_id,user.username,`order`.order_id,`order`.total_amount,`order`.creation_timeFROMuserJOIN`order`ONuser.user_id=`order`.user_id;（5）查询用户的每个最新订单的详细信息。SELECTuser_id,order_id,total_amount,creation_timeFROM`order`WHEREcreation_time=(SELECTMAX(creation_time)FROM`order`ASinner_orderWHEREinner_order.user_id=`order`.user_id);（6）查询所有总金额高于平均订单金额的订单。SELECTorder_id,user_id,total_amountFROM`order`WHEREtotal_amount>(SELECTAVG(total_amount)FROM`order`);若用户注销账号后，需要删除用户信息。删除用户表中用户ID为5的记录。DELETEFROMuserWHEREuser_id=5;（1）创建一个存储过程，用于插入新订单，并在插入订单的同时减少商品库存数量。DELIMITER//CREATEPROCEDUREAddNewOrder(INp_user_idINT,INp_total_amountDECIMAL(10,2),INp_payment_methodVARCHAR(50),INp_shipping_addressVARCHAR(255),INp_delivery_timeTIMESTAMP,INp_product_idINT,INp_quantityINT)BEGINDECLAREv_order_idINT;DECLAREv_stock_quantityINT;--开始事务STARTTRANSACTION;--将新订单数据插入order表INSERTINTO`order`(user_id,total_amount,creation_time,order_status,payment_method,shipping_address,delivery_time)VALUES(p_user_id,p_total_amount,NOW(),'Pending',p_payment_method,p_shipping_address,p_delivery_time);--使用LAST_INSERT_ID()获取刚插入的订单IDSETv_order_id=LAST_INSERT_ID();--将订单和商品数据插入Order_Product表INSERTINTOOrder_Product(order_id,product_id,quantity)VALUES(v_order_id,p_product_id,p_quantity);--查询当前商品库存量并存储在v_stock_quantity中SELECTstock_quantityINTOv_stock_quantityFROMproductWHEREproduct_id=p_product_id;--如果库存足够，则更新商品表中的库存量，并提交事务IFv_stock_quantity>=p_quantityTHEN--减少库存量UPDATEproductSETstock_quantity=stock_quantity-p_quantityWHEREproduct_id=p_product_id;--提交事务COMMIT;ELSE--如果库存不足，则回滚事务，并触发错误信息ROLLBACK;SIGNALSQLSTATE'45000'SETMESSAGE_TEXT='Notenoughstockavailable';ENDIF;END//DELIMITER;调用存储过程AddNewOrder()插入新订单。CALLAddNewOrder(1,--user_id150.00,--total_amount'CreditCard',--payment_method'123MainSt,Anytown',--shipping_address'2024-07-0110:00:00',--delivery_time2,--product_id3--quantity);--验证订单插入记录SELECT*FROM`order`;订单表中插入了一条新记录，继续验证订单商品表的插入记录，具体如下。SELECT*FROM`Order_Product`;验证商品表库存减少的记录，具体如下。SELECT*FROMproduct;（2）创建一个触发器，当插入新的评价时，自动更新商品表中的平均评分和评价数量。DELIMITER//CREATETRIGGERafter_review_insertAFTERINSERTONreviewFOREACHROWBEGINDECLAREavg_ratingDECIMAL(10,2);DECLAREreview_countINT;--计算平均评分SELECTAVG(rating),COUNT(*)INTOavg_rating,review_countFROMreviewWHEREproduct_id=NEW.product_id;--更新商品表中的平均评分和评价数量UPDATEproductSETaverage_rating=avg_rating,review_count=review_countWHEREproduct_id=NEW.product_id;END//DELIMITER;为产品表product添加两个新字段：average_rating和review_count，具体如下。ALTERTABLEproductADDCOLUMNaverage_ratingDECIMAL(10,2)DEFAULT0;ALTERTABLEproductADDCOLUMNreview_countINTDEFAULT0;插入评价记录到评价表并触发触发器，具体如下。INSERTINTOreview(user_id,product_id,review_content,rating,review_date)VALUES(1,1,'Greatproduct!',5,'2024-07-01');INSERTINTOreview(user_id,product_id,review_content,rating,review_date)VALUES(2,1,'Goodquality.',4,'2024-07-02');查询product表中是否存在平均评分以及评价数量，具体如下。SELECTproduct_name,average_rating,review_countFROMproductWHEREproduct_id=1;（1）为用户名创建唯一索引，提高用户名查询的速度，具体如下。CREATEUNIQUEINDEXidx_usernameONuser(username);（2）为订单状态创建索引，加快按订单状态查询的速度，具体如下。CREATEINDEXidx_order_statusON`order`(order_status);（3）创建用户订单视图，用于展示用户及其订单的详细信息。CREATEVIEWuser_ordersASSELECTu.user_id,u.username,u.email,o.order_id,o.total_amount,o.creation_time,o.order_statusFROMuseruJOIN`order`oONu.user_id=o.user_id;查询user_orders视图结果集，具体如下。SELECT*FROMuser_orders;（4）创建订单商品视图，用于展示订单及其包含的商品的详细信息。CREATEVIEWorder_productsASSELECTo.order_id,o.user_id,duct_id,duct_name,op.quantity,p.priceFROM`order`oJOINorder_productopONo.order_id=op.order_idJOINproductpONduct_id=duct_id;查询order_products视图结果集，具体如下。SELECT*FROMorder_products;（5）创建商品评价视图，用于展示商品及其评价的详细信息，具体如下。CREATEVIEWproduct_reviewsASSELECTduct_id,duct_name,r.review_id,r.user_id,r.review_content,r.rating,r.review_dateFROMproductpJOINreviewrONduct_id=duct_id;查询product_reviews视图结果集，具体如下。SELECT*FROMproduct_reviews;调整InnoDB缓冲池大小，innodb_buffer_pool_size决定了InnoDB存储引擎可以使用的内存量，设置为2G（2147483648bytes），具体如下。SETGLOBALinnodb_buffer_pool_size=2147483648;innodb_log_buffer_size影响InnoDB日志缓冲区的大小，设置为64M（67108864bytes）。SETGLOBALinnodb_log_buffer_size=67108864;（2）配置数据库的连接参数，以支持更多的并发连接，提高连接的响应速度，请完成以下配置。max_connections决定了允许的最大连接数。设置最大连接数为500，具体如下。SETGLOBALmax_connections=500;调整连接超时时间为300s，具体如下。SETGLOBALwait_timeout=300;innodb_thread_concurrency决定了InnoDB处理并发查询的线程数。SETGLOBALinnodb_thread_concurrency=16;（3）日志记录是数据库维护和恢复的重要手段，合理配置日志记录参数，可以提高数据库的持久性和恢复能力，请完成以下配置。innodb_log_file_size决定了每个InnoDB日志文件的大小，设置为1G（1073741824bytes），但需要在配置文件中设置并重启MySQL服务，具体如下。[mysqld]innodb_log_file_size=1073741824;（4）调整查询优化相关参数，以提高查询执行的效率，请完成以下配置。调整优化器搜索深度为5，具体如下。SETGLOBALoptimizer_search_depth=5;开启慢查询日志，以识别性能瓶颈。SETGLOBALslow_query_log=1;SETGLOBALlong_query_time=1;--记录超过1秒的查询创建用户并分配合适的权限，遵循最小权限原则，具体如下。--创建只读用户CREATEUSER'readonly_user'@'localhost'IDENTIFIEDBY'password';GRANTSELECTONecommerce_db.*TO'readonly_user'@'localhost';--创建读写用户CREATEUSER'readwrite_user'@'localhost'I