綜合實(shí)訓(xùn)：電商平臺1.5綜合實(shí)訓(xùn)：安裝MySQL數(shù)據(jù)庫1.5.1在Windows環(huán)境下安裝MySQL1.5.2在Linux環(huán)境下安裝MySQL2.8綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺商品根據(jù)以上數(shù)據(jù)類型的選擇，可以創(chuàng)建一個(gè)名為products的表來存儲商品信息，代碼如下。CREATETABLEproducts(product_idINTAUTO_INCREMENTPRIMARYKEY,product_nameVARCHAR(100),descriptionTEXT,priceDECIMAL(10,2),stock_quantityINT,publish_dateDATETIME);向products表中插入數(shù)據(jù)，代碼如下。INSERTINTOproducts(product_name,description,price,stock_quantity,

publish_date)VALUES('HUAWEIMate60Pro','HUAWEIMate60Pro白沙銀12GB+512GB衛(wèi)星通話超可靠玄武架構(gòu)華為鴻蒙智能手機(jī)',6199.00,100,'2025-08-2910:30:00'),('Xiaomi15Ultra','小米Xiaomi15Ultra5G手機(jī)白色12GB+256GB官方標(biāo)配',5899.00,80,'2025-02-0214:45:00'),('vivoX200Pro','vivoX200Pro16GB+512GB辰夜黑',5999.00,50,'2025-03-2009:15:00');3.7綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺訂單表1．表操作（1）創(chuàng)建表：使用CREATETABLE語句創(chuàng)建訂單表orders，包括訂單編號、用戶編號、商品編號、數(shù)量、訂單日期等字段。CREATETABLEorders(order_idINTAUTO_INCREMENTPRIMARYKEY,user_idINT,product_idINT,quantityINT,order_dateDATE);（2）修改表：使用ALTERTABLE語句修改表結(jié)構(gòu)，如添加新的字段用于記錄訂單狀態(tài)。ALTERTABLEordersADDCOLUMNstatusVARCHAR(20);（3）查詢表：使用SELECT語句查詢訂單表中的數(shù)據(jù)，可以按照特定條件檢索訂單數(shù)據(jù)。SELECT*FROMordersWHEREuser_id=1001;（4）刪除表：使用DROPTABLE語句刪除訂單表orders（當(dāng)不再需要該表時(shí)）。DROPTABLEorders;2．?dāng)?shù)據(jù)操作（1）插入數(shù)據(jù)：使用INSERTINTO語句向訂單表orders中插入訂單數(shù)據(jù)，每條插入語句對應(yīng)一條訂單的數(shù)據(jù)。INSERTINTOorders(order_id,user_id,product_id,quantity,order_date,status)VALUES(1,1001,2001,2,'2023-06-01','Pending'),(2,1002,2002,1,'2023-06-02','Shipped'),(3,1001,2003,3,'2023-06-03','Delivered');（2）查詢數(shù)據(jù)：使用SELECT語句從訂單表orders中檢索訂單數(shù)據(jù)，可以按照特定條件過濾和排序數(shù)據(jù)。SELECT*FROMordersWHEREstatus='Delivered';（3）更新數(shù)據(jù)：使用UPDATE語句更新訂單表中的訂單數(shù)據(jù)，如根據(jù)訂單編號更新特定訂單的狀態(tài)。UPDATEordersSETstatus='Cancelled'WHEREorder_id=2;（4）刪除數(shù)據(jù)：使用DELETEFROM語句從訂單表orders中刪除訂單數(shù)據(jù)，可以根據(jù)條件刪除特定訂單的數(shù)據(jù)。DELETEFROMordersWHEREorder_id=3;3．字段操作（1）添加字段：使用ALTERTABLE語句的ADDCOLUMN子句向訂單表orders中添加新的字段，如添加shipping_address字段用于記錄訂單的收貨地址。ALTERTABLEordersADDCOLUMNshipping_addressVARCHAR(100);（2）修改字段：使用ALTERTABLE語句的MODIFYCOLUMN子句修改訂單表orders中已有字段的定義，如修改quantity字段的數(shù)據(jù)類型。ALTERTABLEordersMODIFYCOLUMNquantityINTUNSIGNED;（3）重命名字段：使用ALTERTABLE語句的CHANGECOLUMN子句將訂單表orders中的字段重命名，如將order_date字段重命名為created_at。ALTERTABLEordersCHANGECOLUMNorder_datecreated_atDATE;（4）刪除字段：使用ALTERTABLE語句的DROPCOLUMN子句從訂單表orders中刪除不再需要的字段，如刪除shipping_address字段。ALTERTABLEordersDROPCOLUMNshipping_address;4.6綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺查詢（1）準(zhǔn)備商品表products、訂單表orders、用戶表user的數(shù)據(jù)。#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢商品表productsmysql>SELECTproduct_id,product_name,priceFROMproducts;+------------+--------------------+---------+|product_id|product_name|price|+------------+--------------------+---------+|1|HUAWEIMate60Pro|6199.00||2|Xiaomi15Ultra|5899.00||3|vivoX200Pro|5999.00|+------------+--------------------+---------+3rowsinset(0.00sec)mysql>INSERTINTOorders(order_id,user_id,product_id,quantity)VALUES(1001,1,1,2),(1002,2,2,1),(1003,3,3,3);QueryOK,3rowsaffected(0.02sec)Records:3Duplicates:0Warnings:0#查詢訂單表ordersmysql>SELECT*FROMorders;+----------+---------+------------+----------+------------+|order_id|user_id|product_id|quantity|order_date|+----------+---------+------------+----------+------------+|1001|1|1|2|NULL||1002|2|2|1|NULL||1003|3|3|3|NULL|+----------+---------+------------+----------+------------+3rowsinset(0.00sec)#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11|大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)（2）商品查詢：查詢商品表products中所有商品的信息。SELECT*FROMproducts;上述查詢語句將返回商品表products中所有商品的信息。（3）訂單查詢：查詢某個(gè)用戶的所有訂單信息。SELECTo.order_id,duct_name,o.quantity,p.priceFROMordersoINNERJOINproductspONduct_id=duct_idWHEREo.user_id=1;上述查詢語句將返回用戶編號為1的用戶的訂單信息，包括訂單編號、商品名稱、購買數(shù)量和商品價(jià)格。（4）用戶查詢：查詢購買了特定商品的所有用戶信息。SELECTDISTINCTo.user_id,FROMordersoINNERJOINuseruONo.user_id=u.idWHEREduct_id=1;上述查詢語句將返回購買了商品編號為1的商品的所有用戶的信息，包括用戶編號、用戶姓名。（5）查詢所有商品的名稱和價(jià)格信息。SELECTproduct_name,priceFROMproducts;上述查詢語句將在商品表products中選擇product_name列和price列，返回所有商品的名稱和價(jià)格信息。（6）查詢某個(gè)用戶的訂單列表及總金額。SELECTo.order_id,p.product_name,p.price,o.quantity,o.quantity*p.price

AStotal_amountFROMordersoINNERJOINproductspONduct_id=duct_idWHEREo.user_id='123';上述查詢語句將使用INNERJOIN將訂單表orders和商品表products連接，通過WHERE子句指定用戶編號，返回相應(yīng)用戶的訂單列表及總金額。（7）查詢訂單數(shù)量最多的用戶。SELECTo.user_id,COUNT(*)ASorder_countFROMordersoGROUPBYo.user_idORDERBYorder_countDESCLIMIT1;上述查詢語句將在訂單表orders中按用戶分組，并使用COUNT()函數(shù)計(jì)算每個(gè)用戶的訂單數(shù)量，通過ORDERBY子句和LIMIT子句獲取訂單數(shù)量最多的用戶。（8）計(jì)算訂單總數(shù)。SELECTCOUNT(*)AStotal_ordersFROMorders;上述查詢語句將返回訂單表orders中的總訂單數(shù)。（9）計(jì)算商品總數(shù)。SELECTCOUNT(*)AStotal_productsFROMproducts;上述查詢語句將返回商品表products中的總商品數(shù)。（10）計(jì)算某個(gè)商品的平均價(jià)格。SELECTAVG(price)ASaverage_priceFROMproductsWHEREproduct_id='123';上述查詢語句將返回商品編號為123的商品在商品表products中的平均價(jià)格。（11）計(jì)算每個(gè)用戶的總訂單金額。SELECTuser_id,SUM(total_amount)AStotal_order_amountFROM(SELECTo.user_id,o.quantity*p.priceAStotal_amountFROMordersoJOINproductspONduct_id=duct_id)ASorder_summaryGROUPBYuser_id;上述查詢語句將返回每個(gè)用戶在訂單表orders中的總訂單金額，通過子查詢計(jì)算每個(gè)訂單的金額，并以用戶編號進(jìn)行分組。（12）計(jì)算每個(gè)商品的最高價(jià)格。SELECTproduct_id,MAX(price)ASmax_priceFROMproductsGROUPBYproduct_id;上述查詢語句將返回每個(gè)商品在商品表products中的最高價(jià)格。（13）計(jì)算每個(gè)商品的最低價(jià)格。SELECTproduct_id,MIN(price)ASmin_priceFROMproductsGROUPBYproduct_id;5.6綜合實(shí)訓(xùn)：設(shè)計(jì)電商平臺函數(shù)和存儲過程1．函數(shù)：計(jì)算訂單總金額（1）創(chuàng)建一個(gè)函數(shù)calculate_total_amount()，接收參數(shù)order_id。（2）在函數(shù)內(nèi)部，使用查詢語句計(jì)算指定訂單的總金額。（3）可以根據(jù)訂單表orders和商品表products的關(guān)聯(lián)關(guān)系，計(jì)算訂單中每個(gè)商品的金額，并進(jìn)行求和。（4）返回計(jì)算后的總金額作為函數(shù)的返回值。CREATEFUNCTIONcalculate_total_amount(order_idINT)RETURNSDECIMAL(10,2)BEGINDECLAREtotal_amountDECIMAL(10,2);SELECTSUM(p.price*o.quantity)INTOtotal_amountFROMordersoJOINproductspONduct_id=duct_idWHEREo.order_id=order_id;RETURNtotal_amount;END2．存儲過程：下單操作（1）創(chuàng)建一個(gè)存儲過程place_order()，接收參數(shù)user_id、product_id和quantity。（2）在存儲過程中，使用事務(wù)處理來確保訂單和庫存的一致性。（3）查詢商品表products，檢查庫存是否充足，如果不足，則拋出異?；蚍祷劐e(cuò)誤信息。（4）如果庫存充足，則在訂單表orders中插入新的訂單記錄，并更新商品表products的庫存數(shù)量。（5）返回訂單號或成功消息作為輸出。CREATEPROCEDUREplace_order(INuser_idINT,INproduct_idINT,INquantityINT,OUTorder_idINT)BEGINDECLAREstock_countINT;STARTTRANSACTION;#檢查庫存是否足夠SELECTquantityINTOstock_countFROMproductsWHEREproduct_id=product_id;IFstock_count>=quantityTHEN#插入訂單記錄INSERTINTOorders(user_id,product_id,quantity,order_date)VALUES(user_id,product_id,quantity,NOW());#更新庫存數(shù)量UPDATEproductsSETquantity=quantity-stock_countWHEREproduct_id=product_id;#獲取訂單號SELECTLAST_INSERT_ID()INTOorder_id;COMMIT;ELSE#庫存不足，拋出異?；蚍祷劐e(cuò)誤信息ENDIF;END6.4綜合實(shí)訓(xùn)：電商平臺視圖、游標(biāo)、觸發(fā)器的應(yīng)用1．視圖設(shè)計(jì)創(chuàng)建一個(gè)名為“order_details”的視圖，顯示訂單詳情，包括訂單號、商品名稱、商品價(jià)格和購買數(shù)量等信息。使用以下SQL語句創(chuàng)建視圖。CREATEVIEWorder_detailsASSELECTo.order_id,,p.price,o.quantityFROMordersoJOINproductspONduct_id=duct_id;2．游標(biāo)設(shè)計(jì)創(chuàng)建一個(gè)處理訂單的存儲過程process_orders()，使用游標(biāo)來逐行遍歷訂單表中的訂單數(shù)據(jù)，并計(jì)算訂單總金額。在游標(biāo)循環(huán)中，累加每個(gè)訂單的商品價(jià)格和數(shù)量，得到訂單的總金額。使用以下SQL語句創(chuàng)建游標(biāo)并進(jìn)行處理。DELIMITER$$CREATEPROCEDUREprocess_orders()BEGINDECLAREdoneINTDEFAULTFALSE;DECLAREorder_idINT;DECLAREproduct_idINT;DECLAREquantityINT;DECLAREtotal_amountDECIMAL(10,2)DEFAULT0;#創(chuàng)建游標(biāo)，選擇需要處理的數(shù)據(jù)DECLAREcurCURSORFORSELECTorder_id,product_id,quantityFROMorders;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;OPENcur;#循環(huán)處理游標(biāo)中的每一行數(shù)據(jù)read_loop:LOOPFETCHcurINTOorder_id,product_id,quantity;IFdoneTHENLEAVEread_loop;ENDIF;#這里根據(jù)實(shí)際需求進(jìn)行訂單處理邏輯的設(shè)計(jì)#計(jì)算訂單總金額SETtotal_amount=total_amount+(SELECTpriceFROMproductsWHEREproduct_id=product_id)*quantity;ENDLOOP;CLOSEcur;#輸出訂單總金額SELECTtotal_amountAStotal_amount;END$$DELIMITER;3．觸發(fā)器設(shè)計(jì)創(chuàng)建一個(gè)觸發(fā)器，在商品表中實(shí)現(xiàn)插入新商品時(shí)自動更新商品總數(shù)。創(chuàng)建另一個(gè)觸發(fā)器，在訂單表中實(shí)現(xiàn)插入新訂單時(shí)自動更新訂單總數(shù)。使用以下SQL語句創(chuàng)建觸發(fā)器。#更新商品總數(shù)的觸發(fā)器CREATETRIGGERupdate_product_countAFTERINSERTONproductsFOREACHROWBEGINUPDATEstatisticsSETproduct_count=product_count+1;END;#更新訂單總數(shù)的觸發(fā)器CREATETRIGGERupdate_order_countAFTERINSERTONordersFOREACHROWBEGINUPDATEstatisticsSETorder_count=order_count+1;END;7.9綜合實(shí)訓(xùn)：電商平臺查詢索引應(yīng)用#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterface

canbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshopDatabasechanged#查詢商品表productsmysql>SELECT*FROMproducts;+|+|

|

|+3rowsinset(0.01sec)#查詢訂單表ordersmysql>SELECT*FROMorders;+----------+---------+------------+----------+------------+|order_id|user_id|product_id|quantity|order_date|+----------+---------+------------+----------+------------+|1001|1|1|2|NULL||1002|2|2|1|NULL||1003|3|3|3|NULL|+----------+---------+------------+----------+------------+3rowsinset(0.00sec)#在訂單表orders的商品編號字段上添加普通索引mysql>ALTERTABLEordersADDINDEXidx_product_id(product_id);QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0#查看訂單表orders的索引情況mysql>SHOWINDEXFROMorders;+-----------+-----------------+--------------------+-------------+|Table|Non_unique|Key_name|Collation|+-----------+-----------------+--------------------+-------------+|orders|0|PRIMARY|A||orders|1|idx_product_id|A|+-----------+-----------------+--------------------+-------------+2rowsinset(0.01sec)/*查詢訂單的完整信息，可以看到訂單表orders使用了主鍵索引，且反向查找商品表products使用了eq_ref外鍵關(guān)聯(lián)，查詢效率高*/mysql>EXPLAINSELECTo.order_id,o.user_id,duct_id,o.quantity,duct_

name,p.priceFROMordersoLEFTJOINproductspONduct_id=duct_idORDERBYo.order_idDESC\G***************************1.row***************************id:1select_type:SIMPLEtable:opartitions:NULLtype:indexpossible_keys:NULLkey:PRIMARYkey_len:4ref:NULLrows:3filtered:100.00Extra:Backwardindexscan***************************2.row***************************id:1select_type:SIMPLEtable:ppartitions:NULLtype:eq_refpossible_keys:PRIMARYkey:PRIMARYkey_len:4ref:duct_idrows:1filtered:100.00Extra:NULL2rowsinset,1warning(0.00sec)#使用商品表products的商品名稱和商品價(jià)格字段創(chuàng)建聯(lián)合索引mysql>ALTERTABLEproductsADDINDEXidx_product_name_price

(product_name,price);QueryOK,0rowsaffected(0.06sec)Records:0Duplicates:0Warnings:0#查詢所有商品的商品名稱和商品價(jià)格mysql>EXPLAINSELECTproduct_name,priceFROMproducts\G***************************1.row***************************id:1select_type:SIMPLEtable:productspartitions:NULLtype:indexpossible_keys:NULLkey:idx_product_name_pricekey_len:409ref:NULLrows:3filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)#查詢商品價(jià)格在一定范圍內(nèi)的商品mysql>EXPLAINSELECTproduct_name,priceFROMproductsWHEREpriceBETWEEN10

AND50\G***************************1.row***************************id:1select_type:SIMPLEtable:productspartitions:NULLtype:indexpossible_keys:idx_product_name_pricekey:idx_product_name_pricekey_len:409ref:NULLrows:3filtered:33.33Extra:Usingwhere;Usingindex1rowinset,1warning(0.01sec)#在訂單表orders的用戶編號字段上添加普通索引mysql>ALTERTABLEordersADDINDEXidx_user_id(user_id);QueryOK,0rowsaffected(0.06sec)Records:0Duplicates:0Warnings:0/*查詢某個(gè)用戶的所有訂單信息，使用訂單表orders的idx_user_id索引使用商品表products的主鍵索引*/mysql>EXPLAINSELECTduct_name,o.quantity,o.user_idFROMordersoJOINproductspONduct_id=duct_idWHEREo.user_id=123\G***************************1.row***************************id:1select_type:SIMPLEtable:opartitions:NULLtype:refpossible_keys:idx_product_id,idx_user_idkey:idx_user_idkey_len:5ref:constrows:1filtered:100.00Extra:Usingwhere***************************2.row***************************id:1select_type:SIMPLEtable:ppartitions:NULLtype:eq_refpossible_keys:PRIMARYkey:PRIMARYkey_len:4ref:duct_idrows:1filtered:100.00Extra:NULL2rowsinset,1warning(0.00sec)/*查詢訂單中購買某個(gè)商品的用戶列表，使用訂單表orders的idx_product_id索引同時(shí)使用臨時(shí)表temporary，查詢效率不高*/mysql>EXPLAINSELECTDISTINCTuser_idFROMordersWHEREproduct_id=789\G***************************1.row***************************id:1select_type:SIMPLEtable:orderspartitions:NULLtype:refpossible_keys:idx_product_id,idx_user_idkey:idx_product_idkey_len:5ref:constrows:1filtered:100.00Extra:Usingtemporary1rowinset,1warning(0.00sec)/*使用訂單表orders的idx_user_id索引，同時(shí)使用臨時(shí)表temporary和文件排序filesort效率不高*/mysql>EXPLAINSELECTuser_id,SUM(quantity)AStotal_salesFROMordersGROUPBYuser_idORDERBYtotal_salesDESCLIMIT1\G***************************1.row***************************id:1select_type:SIMPLEtable:orderspartitions:NULLtype:indexpossible_keys:idx_user_idkey:idx_user_idkey_len:5ref:NULLrows:3filtered:100.00Extra:Usingtemporary;Usingfilesort1rowinset,1warning(0.00sec)#按商品編號字段升序排列，按庫存數(shù)量字段降序排列，可以看出沒有使用索引mysql>EXPLAINSELECTproduct_idFROMordersORDERBYproduct_id,quantityDESC\G***************************1.row***************************id:1select_type:SIMPLEtable:orderspartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:3filtered:100.00Extra:Usingfilesort1rowinset,1warning(0