《MySQL數(shù)據(jù)庫項目式教程》項目九 綜合項目案例

MySQL項目九綜合項目案例2019年7月學(xué)習(xí)數(shù)據(jù)庫設(shè)計的方法與步驟掌握E-R圖的繪制方法學(xué)習(xí)目標MySQL了解項目需求分析需具備的能力具有綜合運用MySQL數(shù)據(jù)庫的能力MySQL

項目分析一一、項目分析MySQL1.需求概述

某公司是一家民營的手機銷售企業(yè)，主要從事各類手機的批發(fā)，現(xiàn)有客戶10000余家?，F(xiàn)將為該公司開發(fā)一套手機銷售管理系統(tǒng)，對公司的手機銷售業(yè)務(wù)進行計算機管理，以保證數(shù)據(jù)的安全性，提高工作效率。根據(jù)公司的需求，需要設(shè)計一個手機銷售管理數(shù)據(jù)庫，使用MySQL5.7作為管理系統(tǒng)的數(shù)據(jù)庫軟件，以滿足手機銷售系統(tǒng)的存庫查詢、客戶管理、訂單管理等業(yè)務(wù)操作。一、項目分析MySQL2.項目準備數(shù)據(jù)庫：MySQL5.7.25。操作系統(tǒng)：Windows操作系統(tǒng)。環(huán)境準備會使用SQL語句創(chuàng)建數(shù)據(jù)庫和表，并添加各種約束。會使用常見的SQL語句，如insert語句、update語句、delete語句。會使用子查詢進行查詢。會創(chuàng)建并使用索引、視圖。技能準備0102一、項目分析MySQL3.問題分析表1手機庫存管理業(yè)務(wù)業(yè)務(wù)功能描述庫存管理進貨需要增加庫存，客戶下訂單后需減少對應(yīng)品牌庫存庫存查詢查詢各品牌手機庫存管理客戶信息管理客戶的基本信息管理訂單信息管理訂單的基本信息一、項目分析MySQL3.問題分析表2會員信息管理業(yè)務(wù)數(shù)據(jù)功能描述姓名會員的名稱性別會員的性別會員郵箱會員的聯(lián)系郵箱聯(lián)系電話會員的聯(lián)系電話地址會員的通信地址一、項目分析MySQL3.問題分析表3訂單信息管理業(yè)務(wù)數(shù)據(jù)功能描述訂單數(shù)量各品牌手機的訂單數(shù)量訂購時間各品牌手機的訂單產(chǎn)生的時間發(fā)貨時間各品牌手機的訂單發(fā)貨的時間MySQL項目設(shè)計二二、項目設(shè)計MySQL1.數(shù)據(jù)庫設(shè)計（E-R圖）會員訂單訂貨庫存會員編號會員姓名密碼性別訂單號會員編號手機編號訂單狀態(tài)訂購日期mn查詢會員郵箱聯(lián)系電話通信地址訂購數(shù)量發(fā)貨時間手機編號品牌型號顏色內(nèi)存大小單價庫存數(shù)量mnMySQL2.將E-R圖轉(zhuǎn)化為關(guān)系模式二、項目設(shè)計01庫存表（stock）：（手機編號、品牌、型號、顏色、內(nèi)存大小、單價、庫存數(shù)量）。其中，用手機編號來唯一標識各手機信息，所以主鍵為手機編號。02會員表（user）：（會員編號、會員姓名、密碼、性別、會員郵箱、聯(lián)系電話、通信地址）。其中，用會員編號來唯一標識各會員信息，所以主鍵為會員編號。03訂單表（ordertb）：（訂單號、會員編號、手機編號、訂購數(shù)量、訂單狀態(tài)、訂購日期、發(fā)貨時間）。其中，一個會員編號可對應(yīng)多個訂單編號，而一個手機編號也有可能對應(yīng)多個訂單號。MySQL3.確定數(shù)據(jù)表結(jié)構(gòu)二、項目設(shè)計字段名字段說明數(shù)據(jù)類型長度允許為空約束備注mobID手機編號varchar50非空主鍵

brand品牌varchar50非空

model型號varchar30非空

color顏色varchar30非空

memSize內(nèi)存大小varchar10非空

price單價float

非空

stockNum庫存數(shù)量int

非空

表1手機庫存管理業(yè)務(wù)MySQL3.確定數(shù)據(jù)表結(jié)構(gòu)二、項目設(shè)計表2會員表user結(jié)構(gòu)字段名字段說明數(shù)據(jù)類型長度允許為空約束備注uId會員編號varchar10非空主鍵

uName會員姓名varchar50非空

password密碼varchar20非空

sex性別char2非空

只能為“男”或“女”email會員郵箱varchar30可

phone電話號碼varchar20可

address通信地址varchar50可

默認“地址不詳”MySQL3.確定數(shù)據(jù)表結(jié)構(gòu)二、項目設(shè)計表3訂單表ordertb結(jié)構(gòu)字段名稱字段說明數(shù)據(jù)類型長度可否為空約束備注oid訂單號int10非空主鍵自動編號uid會員編號varchar10非空外鍵引用user表主鍵sid鞋編號varchar50非空外鍵引用stock表主鍵orderNum訂購數(shù)量int

非空

status訂單狀態(tài)tinyint

非空

1表已處理，0表待處理orderTime訂購日期datetime

非空

deliveryTime發(fā)貨時間datetime

非空

MySQL任務(wù)實施三三、任務(wù)實施MySQLcreatedatabasemobileSale;1.創(chuàng)建數(shù)據(jù)庫，SQL語句如下：三、任務(wù)實施MySQLcreatetablestock(mobIDvarchar(50)primarykey,brandvarchar(50)notnull,modelvarchar(30)notnull,colorvarchar(30)notnull,memSizevarchar(10)notnull,pricefloatnotnull,stockNumintnotnull);2.創(chuàng)建庫存表stock，SQL語句如下：三、任務(wù)實施MySQLcreatetableuser(uIDvarchar(10)primarykey,uNamevarchar(50)notnull,passwdvarchar(20)notnull,sexchar(2)notnullcheck(sexin('男','女')),emailvarchar(30),phonevarchar(30),addressvarchar(50)default'地址不詳');3.創(chuàng)建用戶表user，SQL語句如下：三、任務(wù)實施MySQLcreatetableordertb(orderIDvarchar(10),uIDvarchar(10)notnull,mobIDvarchar(50)notnull,orderNumintnotnull,orderTimedatenotnull,statustinyint(1),deliveryTimedate,primarykey(orderID));4.創(chuàng)建訂單表ordertb，SQL語句如下：三、任務(wù)實施MySQLinsertintostockvalues('m00001','華為','P20','亮黑色','64GB',3288,2312),('m00002','華為','P20','亮黑色','128GB',3488,1798),('m00003','華為','P20','極光色','64GB',3388,2499),('m00004','華為','P20','極光色','128GB',3488,1133),('m00005','華為','P30','亮黑色','64GB',3988,580),('m00006','華為','P30','亮黑色','128GB',4388,400),('m00007','華為','P30','極光色','64GB',3988,340),('m00008','華為','P30','極光色','128GB',4288,2010),('m00009','小米','小米8','黑色','64GB',3499,1920),('m00010','小米','小米8','白色','128GB',3699,2311);5.對庫存表stock的執(zhí)行插入命令，SQL語句如下：三、任務(wù)實施MySQLinsertintouservalues('u0001','n01','123456','男','34687653@',,'天津市河?xùn)|區(qū)'),('u0002','n02','123456','男','56256781@',,'山東省濟南市'),('u0003','n03','123456','女','20987122@',,'四川省成都市');6.對用戶表user的執(zhí)行插入命令，SQL語句如下：三、任務(wù)實施MySQLinsertintoordertbvalues('E00001','u0001','m00001',200,'2019-06-22',1,'2019-06-30'),('E00002','u0002','m00002',150,'2019-06-22',1,'2019-06-30'),('E00003','u0002','m00003',50,'2019-07-05',1,'2019-07-15'),('E00004','u0003','m00004',48,'2019-08-10',1,'2019-08-20'),('E00005','u0003','m00005',135,'2019-09-26',1,'2019-09-29');7.對訂單表ordertb的執(zhí)行插入命令，SQL語句如下：三、任務(wù)實施MySQLinsertintostockvalues('m00011','小米','小米8','灰色','128GB',3799,500);8.添加新手機數(shù)據(jù)，SQL語句如下：三、任務(wù)實施MySQLdeletefromstockwheremobID='m00011';9.將手機編號為“m00011”的手機信息刪除，SQL語句如下：三、任務(wù)實施MySQLupdatestocksetstockNum=stockNum+150wheremodel='P30'andcolor='極光色'andmemSize='64GB';10.將華為P30，顏色“極光色”，128GB內(nèi)存的手機庫存增加150臺，SQL語句如下：三、任務(wù)實施MySQLselect*fromstockwhereprice=(selectmax(price)fromstock);11.分別查詢庫存中價格最高手機庫存信息，SQL語句如下：三、任務(wù)實施MySQL方法一：selecto.orderID訂單號,s.brand品牌,s.model型號,s.color顏色,s.memSize內(nèi)存大小,o.orderNum訂單數(shù)量fromstockassinnerjoinordertbasoons.mobID=o.mobIDwheres.model='P20';12.查詢?nèi)A為P20手機的銷售情況，包括訂單編號、手機品牌、型號、顏色、內(nèi)存大小、訂購數(shù)量，要求列名顯示為中文，SQL語句如下：方法二：selecto.orderID訂單號,s.brand品牌,s.model型號,s.color顏色,s.memSize內(nèi)存大小,o.orderNum訂單數(shù)量fromstockass,ordertbasowheres.mobID=o.mobIDands.model='P20';三、任務(wù)實施MySQLaltertableordertbaddindexidx_uID(uID);12.在ordertb表中，在會員編號列上創(chuàng)建普通索引，索引名為idx_uid，SQL語句如下：三、任務(wù)實施MySQLcreateviewuid_viewasselectu.uName,o.orderID,o.mobID,o.orderNum,o.orderTime,o.deliveryTimefromuseru,ordertbowhereu.uID=o.uID;13.在手機銷售管理數(shù)據(jù)庫中，創(chuàng)建一個按會員編號查詢的訂單信息的視圖，視圖名為uid_view，要求該視圖能顯示各會員單位的訂單信息，包含會員名稱、訂單號、手機編號、訂購數(shù)量、訂購時間、發(fā)貨時間SQL語句如下：三、任務(wù)實施MySQLcreateviewuid_viewasselectu.uName,o.orderID,o.mobID,o.orderNum,o.orderTime,o.deliveryTimefro