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

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

項(xiàng)目分析一一、項(xiàng)目分析MySQL1.需求概述

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

brand品牌varchar50非空

model型號(hào)varchar30非空

color顏色varchar30非空

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

price單價(jià)float

非空

stockNum庫存數(shù)量int

非空

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

uName會(huì)員姓名varchar50非空

password密碼varchar20非空

sex性別char2非空

只能為“男”或“女”email會(huì)員郵箱varchar30可

phone電話號(hào)碼varchar20可

address通信地址varchar50可

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

非空

status訂單狀態(tài)tinyint

非空

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

非空

deliveryTime發(fā)貨時(shí)間datetime

非空

MySQL任務(wù)實(shí)施三三、任務(wù)實(shí)施MySQLcreatedatabasemobileSale;1.創(chuàng)建數(shù)據(jù)庫，SQL語句如下：三、任務(wù)實(shí)施MySQLcreatetablestock(mobIDvarchar(50)primarykey,brandvarchar(50)notnull,modelvarchar(30)notnull,colorvarchar(30)notnull,memSizevarchar(10)notnull,pricefloatnotnull,stockNumintnotnull);2.創(chuàng)建庫存表stock，SQL語句如下：三、任務(wù)實(shí)施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ù)實(shí)施MySQLcreatetableordertb(orderIDvarchar(10),uIDvarchar(10)notnull,mobIDvarchar(50)notnull,orderNumintnotnull,orderTimedatenotnull,statustinyint(1),deliveryTimedate,primarykey(orderID));4.創(chuàng)建訂單表ordertb，SQL語句如下：三、任務(wù)實(shí)施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.對(duì)庫存表stock的執(zhí)行插入命令，SQL語句如下：三、任務(wù)實(shí)施MySQLinsertintouservalues('u0001','n01','123456','男','34687653@',,'天津市河?xùn)|區(qū)'),('u0002','n02','123456','男','56256781@',,'山東省濟(jì)南市'),('u0003','n03','123456','女','20987122@',,'四川省成都市');6.對(duì)用戶表user的執(zhí)行插入命令，SQL語句如下：三、任務(wù)實(shí)施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.對(duì)訂單表ordertb的執(zhí)行插入命令，SQL語句如下：三、任務(wù)實(shí)施MySQLinsertintostockvalues('m00011','小米','小米8','灰色','128GB',3799,500);8.添加新手機(jī)數(shù)據(jù)，SQL語句如下：三、任務(wù)實(shí)施MySQLdeletefromstockwheremobID='m00011';9.將手機(jī)編號(hào)為“m00011”的手機(jī)信息刪除，SQL語句如下：三、任務(wù)實(shí)施MySQLupdatestocksetstockNum=stockNum+150wheremodel='P30'andcolor='極光色'andmemSize='64GB';10.將華為P30，顏色“極光色”，128GB內(nèi)存的手機(jī)庫存增加150臺(tái)，SQL語句如下：三、任務(wù)實(shí)施MySQLselect*fromstockwhereprice=(selectmax(price)fromstock);11.分別查詢庫存中價(jià)格最高手機(jī)庫存信息，SQL語句如下：三、任務(wù)實(shí)施MySQL方法一：selecto.orderID訂單號(hào),s.brand品牌,s.model型號(hào),s.color顏色,s.memSize內(nèi)存大小,o.orderNum訂單數(shù)量fromstockassinnerjoinordertbasoons.mobID=o.mobIDwheres.model='P20';12.查詢?nèi)A為P20手機(jī)的銷售情況，包括訂單編號(hào)、手機(jī)品牌、型號(hào)、顏色、內(nèi)存大小、訂購數(shù)量，要求列名顯示為中文，SQL語句如下：方法二：selecto.orderID訂單號(hào),s.brand品牌,s.model型號(hào),s.color顏色,s.memSize內(nèi)存大小,o.orderNum訂單數(shù)量fromstockass,ordertbasowheres.mobID=o.mobIDands.model='P20';三、任務(wù)實(shí)施MySQLaltertableordertbaddindexidx_uID(uID);12.在ordertb表中，在會(huì)員編號(hào)列上創(chuàng)建普通索引，索引名為idx_uid，SQL語句如下：三、任務(wù)實(shí)施MySQLcreateviewuid_viewasselectu.uName,o.orderID,o.mobID,o.orderNum,o.orderTime,o.deliveryTimefromuseru,ordertbowhereu.uID=o.uID;13.在手機(jī)銷售管理數(shù)據(jù)庫中，創(chuàng)建一個(gè)按會(huì)員編號(hào)查詢的訂單信息的視圖，視圖名為uid_view，要求該視圖能顯示各會(huì)員單位的訂單信息，包含會(huì)員名稱、訂單號(hào)、手機(jī)編號(hào)、訂購數(shù)量、訂購時(shí)間、發(fā)貨時(shí)間SQL語句如下：三、任務(wù)實(shí)施MySQLcreateviewuid_viewasselectu.uName,o.orderID,o.mobID,o.orderNum,o.orderTime,o.deliveryTimefro