大數(shù)據(jù)應用基礎（人郵微課版）-課后提升參考答案 項目2-6 MySQL數(shù)據(jù)庫設計-浪潮可視化大數(shù)據(jù)工具應用

大數(shù)據(jù)應用基礎（人郵微課版）?課后提升參考答案

一、請根據(jù)給出資料完成數(shù)據(jù)庫的概念模型、邏輯模型以及物理

模型設計

（1）概念模型設計

依據(jù)需求分析可知，該模型涉及4個實體，若干屬性，且實體之間的關系清晰，可利用

E?R圖工具完成概念模型的設計，如下圖所示。

圖概念模型設計

（2）邏輯模型設計

利用概念模型結果，依據(jù)概念模型轉換邏輯模型的方法，將E?R圖轉換成邏輯模型，具

體轉換方法如下：

1）實體的轉換：將E?R圖中的每個實體均轉換為一個邏輯模型。

科目信息（科目代碼,科目名稱，科目類別，余額方向，數(shù)量單位）其中，“科目代碼”

為主鍵。

憑證信息（憑證編號,附單據(jù)數(shù)，制單人，制單時間，借方合計，貸方合計，憑證狀態(tài)）

其中“憑證編號”為主鍵。

賬簿信息（賬簿編號,科目代碼，累計借方，累計貸方，初期余額）其中“賬簿編號”

為主鍵。

2）聯(lián)系的轉換：

本案例中“科目信息”與“憑證信息”為包含關系，其類型為1：N,根據(jù)轉換原則，

將1端實體主鍵添加到多端實體轉換的模型中作為外鍵，即將“科目信息”中的“科目代碼”

添加到“憑證信息”模型中作為外鍵；“憑證信息”與“賬簿信息”為過賬關系，其類型為

1：1,需將一端實體主鍵添加到另一端實體轉換的模型中作為外鍵，即將“憑證信息”的主

鍵“憑證編碼”添加到“賬簿信息”模型中作為外鍵；“賬簿信息”與“科目余額”為包含

關系，關系類型為N:l,同理，需將1端實體主鍵添加到多端實體轉換的模型中作為外鍵。

最終轉換結果如下：

科目信息（科目代碼,科目名稱，科目類別，數(shù)量單位）其中，“科目代碼”為主鍵。

憑證信息（憑證編號,附單據(jù)數(shù)，制單人，制單時間，借方合計，貸方合計，憑證狀態(tài)，

科目代碼）其中“憑證編號”為主鍵，“科目代碼”為外鍵。

賬簿信息（賬簿編號,科目代碼，累計借方，累計貸方，初期余額，憑證編號）其中“賬

簿編號”為主鍵，“憑證編號”作為外鍵。

（3）物理模型設計

本階段對邏輯模型的成果進行物理模型的設計，結合實際的數(shù)據(jù)存儲要求，對“科目信

息”、“憑證信息”、“賬簿信息”的物理模型設計如下：

表科目信息表結構

字段名字段類型約束備注

KmIDchar(20)主鍵科目代碼

KmNamechar(20)唯一約束科目名稱

科目類別包括：資產(chǎn)、負債、共同、權益、

KmKindset成本、損益科目類別

Unitchar(10)數(shù)量單位

表憑證信息表結構

字段名字段類型約束備注

PzIDchar(4)主鍵憑證編號

FDJINT附單據(jù)數(shù)

ZDRchar(20)制單人

ZDSJdatetime自動填充系統(tǒng)時間制單時間

JFHJdecimal(12,2)借方合計

DFHJdecimal(12,2)貸方合計

PzZtchar(2)默認值為“否”憑證狀態(tài)

表賬簿信息表結構

字段名字段類型約束備注

Zbidchar(20)主鍵賬簿編號

KmIDchar(20)外鍵科目代碼

PzIDchar(4)外鍵憑證編號

LJJFdecimal(12,2)累計借方

LJDFdecimal(12,2)累計貸方

CQYEdecimal(12,2)初期余額

二、依據(jù)數(shù)據(jù)庫設計階段成果完成數(shù)據(jù)庫的實施

本案例數(shù)據(jù)庫管理系統(tǒng)采用MySQL8.0以及圖形界面工具Navicato

（1）數(shù)據(jù)庫的創(chuàng)建

1）啟動MySQL8.0

可以通過windows的“服務”工具啟動MySQL服務。（具體操作過程參見實訓一）

2）打開Navicat工具，連接MySQL服務器，創(chuàng)建悠樂公司財務數(shù)據(jù)庫。

步驟一：啟動Navicat,連接服務器。（具體操作過程參見實訓一）

步驟二：創(chuàng)建悠樂公司財務數(shù)據(jù)庫。

（2）表及表約束的創(chuàng)建

數(shù)據(jù)庫創(chuàng)建完成后即可進行表及約束的新建。雙擊打開“悠樂公司財務管理數(shù)據(jù)庫”，

右擊“表”命令，在右側的表設計界面即可完成表的創(chuàng)建，具體方法參見實訓二，在此將3

個表約束的實現(xiàn)方法進行講解。

1)科目信息表

主鍵設置唯一約束設置

WgXXBK

mm

?｛耍lid

檢查約束設置

2）憑證信息表

主鍵“憑證編號”的設置方法同上，此處不再贅述。

“制單時間”列要求自動填充系統(tǒng)時間默認值約束的設置

3）賬簿信息表

“賬簿編號”主鍵約束設置方法此處不再贅述。

外鍵約束設置

****%.WMmHMRHC>■AWUI???*?■

三、完成數(shù)據(jù)庫數(shù)據(jù)的增、刪、改及數(shù)據(jù)的導入導出

在進行數(shù)據(jù)的增、刪、改及數(shù)據(jù)的導入導出之前，首先需要明確表的結構（如字段的數(shù)

據(jù)類型及長度等）、約束（主鍵、外鍵、唯一等）及表之間的關聯(lián)（如下圖），可以看出，科

目信息表以及憑證信息表為主鍵表，而賬簿信息表為外鍵表，根據(jù)外鍵約束的要求，其取值

范圍需要參照主鍵表中數(shù)據(jù)的取值范圍，即賬簿信息表中“KmlD”列的取值范圍要參照科

目信息表中“KmlD”列的取值范圍，那么在輸入數(shù)據(jù)的時候需要先錄入科目信息表的數(shù)據(jù)。

憑證信息表同賬簿信息表的關系同理。

此部分操作方法同實訓二、實訓三，請參照完成，此處不再贅述。

憑證信息&

科目信息表賬簿信息表

PzIDchar(4)

,KmlD:char(20)Zbidchar(20)

FDJnt(O)

?KmNamechar(20)0KmlD:char(20)

ZDR:char(20)

KmKindset.PzIDchar(4)

ZDSJ:timestamp(O)

Unitchar(10)LJJFdecimal(12,2)

JFHJdecimal(12,2)

LJDF：decimal(12,2)

DFHJ:decimal(12,2)

CQYEdecimal(12,2)

PzZtchar(2)

圖表關系不意圖

（1）請在“科目信息表”中依次錄入以下數(shù)據(jù)

記錄1：KmID為1001；KmName為庫存現(xiàn)金；KmKind為資產(chǎn)；Unit為元。

記錄2：KmID為1001；KmName為銀行存款；KmKind為資產(chǎn)；Unit為元。

記錄3：KmID為1003；KmName為銀行存款；KmKind為資產(chǎn)；Unit為元。

經(jīng)過實操，發(fā)現(xiàn)記錄1可以正常存儲，記錄2不能存儲到數(shù)據(jù)庫中，原因是違反了

主鍵約束要求，即作為主健的KmlD列不能出現(xiàn)重復值，將記錄2的KmlD值輸入為

1002,記錄2可正常存儲：記錄3不能正常存儲的原因是違反了KmName列的唯一

約束，將該列值修改為存放中央銀行款項即可。

（2）請修改表中的數(shù)據(jù)

請修改記錄2、記錄3的數(shù)據(jù)，使其能正常存入數(shù)據(jù)庫。

（3）請刪除表中的數(shù)據(jù)

請科目信息表中的記錄1、記錄2及記錄3.

（4）請將“科目信息表.txt"中的數(shù)據(jù)導入到數(shù)據(jù)庫中，再將數(shù)據(jù)庫中的“科目信息表”

數(shù)據(jù)導出，保存為“科目信息表.xlsx”。

四、進行數(shù)據(jù)庫及表的備份與還原

請將悠樂公司財務管理數(shù)據(jù)店進行備份后還原。其操作步驟請參照任務三。

拓展一概覽產(chǎn)品信息

(1)查看products表的表結構。

DESCRIBEproducts;

FieldTypeNullKeyDefaultExtra

prodidintNO0

prodbrandvarchar(20)NO

prodcategoryvarchar(50)YES

prodpricedecimal(8,2)YES

proddescribevarchar(255)YES

(2)查看products表中的數(shù)據(jù)。

SELECT*FROMproducts;

prodjdp(od_b(andprodjategorypcod.pckeproddesaibe

%乂0華里物'4899.00AppleiPhone11(A2223)12fiGB原色的則g電信4G手機雙卡雙待

6941/h*手機599.00Rcdmi9A5000mAh大電?大展I8大字體大臺■1300萬AJIBtfl八核處理器人版第鎖4

6Q4?nppn手機ipoonn中晶手加almp■海P4Rnn萬?章雙“壬磯沖浪麗ftGR*1?R

69432手機1299Q0RedmiNole95G天磯800U18WW充4&)0萬超治三的云81灰6GB?IZSGB游戲智能

6X4革果手機6799.00AppleiPhone12(A2404)128GB場色支挎移切取通電信5G雙卡雙待手機

6945oppo手機2999.00OPPORenoS5G6400萬水光人像酗65WH甄充12*256G8星河入券全網(wǎng)通手機

6946鈉手機239X)0天語(K-Touch)V9雙屏0含老人制超長待機移動聯(lián)通2G雙卡雙待老年手機手寫功能

6947小米手機1999.00RedmiNote9Pro5G一億像索虢龍750G33WW充120Hz刷新率*海星辰8GB。256

6948饞手機1099.00RedmiNote94G6000mAh大電池虢龍662處理器18WR充羽鼻黑6GB?128GB和

6949華為手機1699.005E?PUy4TPro1UR81咐片OLEDWBffitt4?00萬聞耽友拍三Bl22SWB1R快充全

6950vivo手機2698.00vivoiQOONeo35G8GB*128GB夜.黑高通虢龍865144Hz競速屏文體雙播44WW

6951vivo手機4198X)0vivo1QOO712GB*256GB傳奇版聯(lián)龍888120W1fiW］充KPL防*事電競手機雙模

6953小米手機1399.00Redmi10X5G天磯820雙5GM4800萬海光相機柘<鉆展6GB?128G8深海塞游

+-xc■物園..

SELECT?FROMproducts只讀音弼電0.223s第1條記錄(M5947條)

(3)顯示products表部分字段的數(shù)據(jù)，并設定各字段的中文別名。

SELECTprod_brandAS'品牌\pnxl_calcgoryAS'類別prod_priccAS成本單價',prod_describeAS'描述'

FROMproducts;

品牌類別成本單價描述

13蘋果4899.00AppleiPhone11

小米599.00Redmi9ASOOOrr

oppo1299.00京品手機realme具

小米1299.00RedmiNote95G

蘋果6799.00AppleiPhone12

oppo2999.00OPPORenoS5G

中興239.00天語(K-Touch)>

小米1999.00RedmiNote9Pr(

小米1099.00RedmiNote94G

華為1699.00領Play4TPro慈

vivo2698.00vivoiQOONeo3

vivo4198.00vivoiQOO712GI

小米1399.00Redmi10X5G天

+-??XC■

SELEC只讀查詢時間:0.277s第1條記錄供5947條)

(4)將products表的類別、品牌兩字段去重顯示。

SELECTDISTINCTprod_brand,prod_categoryFROMproducts;

prodbrandprodcategory*

，蘋果

小米

oppo

中興

華為

vivo

■yu浦

諾基亞

摩代羅拉

金立

PYTHON圖書

C聊

腳本編程圖書

+-<?xc■HS

查詢時間:0.111第1條記錄供26條)

(5)將products表中的數(shù)據(jù)按類別升序、品牌升序、價格降序進行顯示。

商品類別、商品品牌按照漢字拼音的英文字母升序，再按照單價降序排序，如：“電腦”

排在“手機”的前面，“戴爾”排在“華碩”前面，“華碩”排在“華為”的前面：

SELECT*FROMproducts

ORDERBYCONVERT(prod_categoryusinggbk),CONVERT(prod_brandusinggbk),prod_priccDESC;

信息結果i剖析狀態(tài)

prod_idprod_brandprod_categoryprod_priceprod_describe

?5342畛費25979.00?KDELLXPS17-970017英寸4KR5藍光全面就gi+U屏5g記本電腦(i710875H32G1T

6009畛費23979.00?KDELLXPS17-9700110875H16GS12GR

6013畛越23979.00?KDELLXPS17-970017英寸物鴕1087SH16GS12GR

6449IKK電箱22979.00RYDELLXPS17-970017英寸防觥全面屏設計筆107S0H16611

5326附電噴19979.00RYDELLXPS15-9S001S.6英寸4KJS靛光全面屏設計輕者筆記本電腦(i7-10750H32G

5462畛百18999.00?K/DELLXPS9300/9310/二合一13.4英寸全面屏超輕薄設計筆記本電腦可選Evo認證9

6110威爾電跑18989.00RYDELLXPS13-9310J3.4英寸4幅輕》?$8?筆(+Yi7-1185G732G

6500畛電跑18979.00RYDELLXPS1S-9S001S.6英寸英特爾康光設計筆記本電劇i7-1O75OH16GS

+■.xC

SELECT?FROMproductsORDERBYCONVERl(prodcategoryusing只讀i5詢時間:0.463s第1條記錄(共5947條)口口

(6)將products表中的數(shù)據(jù)按類別升序、品牌升序、價格降序排序后，用分頁表格顯

示，每頁20條記錄，查看顯示第3頁的數(shù)據(jù)。

查詢先按照商品類別、商品品牌的漢字拼音的英文字母升序再按照單價降序排序的第

11行到第20行商品。使用LIMIT10,10,第1個10表示從第11行開始，第2個10表示顯

年10行數(shù)據(jù)：

SELECT*FROMproducts

ORDERBYCONVERT(prod_categoryusinggbk),CONVERT(prod_brandusinggbk),prod_priceDESC

LIMIT10J0;

信息結果1創(chuàng)析狀態(tài)

prodjdprodbrandprodcategoiyprodpriceproddescribe

?6087畛16979.00筋DELLXPS13-930013.4英寸4K防盤屏超百商務箔￡4^618(i7-1065G716

S14SBKK電愴16968.00RYDELLXPS1S-9S0015.詼寸英1§^??17防眸設計第以??。7-1075(^16G1

6071喲百15999.00戢爾DEI!XPS13-9310EVO嚴苛認證13.侵寸4幽魅光超輕薄觸控筆記本電腦(H—fti

6067畛電腦15999.00畛DELLXPS13-9310￡VO嚴苛認證13.4英寸4般疏光超輕薄觸控筆記楣腦(十Tti

6275戴爾電的15999.00RYDELLXPS13-931OEVO嚴苛認證13.4英寸4K防藍光超輕薄觸控筆記本電-2

5663畛百15979.00?J?DELLXPS15-950015.6英寸防延*設計第￡^?(i7-10750H16G512GG

5879戴爾電幽13999.00RYDELLXPS13-931OEVO嚴苛認證13.4英寸

5731戴爾百13999.00附DEI!XPS13-930013.4英寸4K防延解超崛i7-1065G78G51；

6118畛電腦10999.00?^DELLXPS9S0015.6英寸酷型超輕麟^^面屏設^5?筆阱詢51030(^

5122戴爾電9999.00好(DELL)靈越750015.侯寸十f礪壓IBaGTX專業(yè)獨顯創(chuàng)意設計師筆記本電腦2M

+-v*XC

SELECT,FROMproductsORDERBYCONVERT(prodcategoryusinggbk),COR只讀否詢時間0.179s第1(共10條)

拓展二查詢產(chǎn)品特征

(1)查詢商品表，類別為“手機”，品牌不為“蘋果”，旦價格在1000~2000元。

方法一：

SELECT*FROMproducts

WHEREprod_calegory='手機'ANDprod_brand<>'蘋果'ANDprod_priceBETWEEN1000AND2000

ORDERBYprod_price;

方法二：

SELECT*FROMproducts

WHEREprod_category='手機'ANDprod_brand!='蘋果'ANDprod_price>=1000ANDprod_price<=2000

ORDERBYprod_price;

信息結果1剖析狀態(tài)

prodjdprod_brandprod_cotcgoryprod_priccprod_dcscribc

>7169物覲1018.00榮耀叼30智能老人罅笛用相3020(11新長娜1300萬大5^010^1幻夜黑368+64

7822華為手機1038.00榮暢玩9A全網(wǎng)通4G手機6.3英寸珍珠全面屏震撼大音，益水技窣4GB+64GB

8167小米手機1047.00【現(xiàn)貨速發(fā))小米Redmi紅米9手機可選分阻免思老人機學生機碇索黑全網(wǎng)通6G+128

8172小米手機1047.00【現(xiàn)貨速發(fā)】小米Redmi紅米9手機可選分期免息老人機學生機破素黑全網(wǎng)通6G+12&

8703小米環(huán)1048.00小*IDK9瓢敢盜全網(wǎng)通(6GB+128GB)

9003小米手機1049.00小米Redmi紅米Note8手機星云紫全網(wǎng)通(6GB?64GB)

9205小米環(huán)1049.00小米Redmitt*1OX^VUG5020mAh±f&*千謝天際益全網(wǎng)通(4GB128GB)

8104小米手機10SS.00小米紅米9Redmi9游戲智能手機里只吉66B+128GB

8522vivo手機1079.00vivoiQOOUlx4G手機［購機5司禮］大解幕大電池內(nèi)充千元機拍照學生游戲手機安卓智

7682vivo手機1098.00vivoY3s4G手機【享碎屏險】大屏大電池閃充學生拍照美頷千元機而戲手機智能安卓全W

+-?XC

SELECT*FROMproductsWHEREprodcategoryANDprodbrand只讀沓詢時間:0.419s第1條記錄供655條)

(2)查詢商品表,類別為“手機”，品牌為“華為”、“小米”、“vivo”、“oppo”，且描述中包

含“老人”，按單價升序排序。

方法一：

SELECT*FROMproducts

WHEREprodqtegory」手機，ANDprcd.biandin(華為？小米Lvivo'bppo)ANDprod_describcLIKE%老

人％，

ORDERBYprod_price;

方法二：

【小知識】:優(yōu)先級：()>not>and>or。

SELECT*FROMproducts

WHEREprod_category='手機'ANDprod_describeLIKE'%老人％'AND

(prod_brand='華為'ORprod_brand='小米'ORprod_brand='vivo'ORprod_brand='oppo');

ORDERBYprod_price;

信息結果1狀態(tài)

prodidprodbrandprcxicategoryprodpriceproddescribe

>%250小米-手機-289.00小米翥親QinQF燈磯功能機備用機學生手機老人手機4G全網(wǎng)通超長待機雙卡直板按朝

9246小米手機289.00小米多減QinQF9手機功能機苗用機學生手機老人手機4G全網(wǎng)通超長待機雙卡直板按W

8836小米手機289.00〃冰生態(tài)21KE/4G全場通21克F9小學生AI功能電話小愛同學老人手機移動聯(lián)通電信老年)

9446小米手機299.00多辛QNQF9全網(wǎng)通4G智雌鍵移動聯(lián)通電信老人手機定位小米無攝像頭老人機小愛同之

9894小米手機389.00小米有目多親AI電話QinB+小愛同學接錚直板老人手機支持聯(lián)通移動雙卡雙待遠旌向

8764小米手機389.00小米有品多余Qin手機AI電話S+小愛同學按鍵面板老人手機聯(lián)通移動雙卡學生機雙符園

8753小米手機399.00多宗QIN1S+全網(wǎng)通4J人手機小愛同學空手機定位無患像頭大屏按犍老年手機小米老，

9297小米手機399.00小米生存米米多親Qin1s向電話移動聯(lián)通電信VoUE老人手機雙卡雙待直板手機IS+燃

7886小米手機399.00小米多減Qin1S+ai手機小愛同學4G全網(wǎng)通笛用機直板按犍微信學生三潸能熱點戒網(wǎng)老4

9504小米手機399.00多親QIN全網(wǎng)通4G老人手機小米按St大屏大字大聲8S倡老人機小愛同學生老年手機？

+--?XC■

SELECT,FROMproductsWHEREprod_category='手機'ANDprodbrand只讀查詢時間0.195s第1條記錄(共73條)

(3)查詢商品表，類別為“電腦”，單價下調15%后，價格低于3000元的商品，按單

價升序排序。

SELECTprod_id,prod_d_describe,prod_price*0.85ASprod_price_dFROMproducts

WHEREprod_calegory=，電腦，ANDprod_price*0.85<3000

ORDERBYprod_price_d;

信息剖析狀態(tài)

prod^idprod_b<d<xiprod_de$cribeprojj)rke_d

5493宏亞宏星(acef)gtX21515.6英寸輕簿商務辦公宏里&記本電髓2?四【四賅N4120/82124.1SOO

6446覆修7Z9(acef)BWEX2I515。莫、J注薄街話“公史星筆記小電購套餐五［0t^N4120/12209.1500

6741宏基宏曷(acet)gFX215156英寸輕簿商務辦公宏■筆記本電182?三［QKN4120/82209.1500

5783聯(lián)忸織SKenovo渾記本V14N4020英特爾島汨大屏長薄窄邊商務辦公家用電卻4英寸4G72294.1500

6279宏基宏昌(acet)—fX21515.6英寸輕薄商務辦公宏*記本電182?二【四核N4120/12294.1500

6284宏W宏號(acer)"舞EX2I515.6英寸長廊S務辦公宏SS筆記本電做套餐二【四修N4120/12294.1500

6503聯(lián)想聯(lián)想(Lenovo)8BIBE41-55較薄筆記本14英寸商務辦公學生網(wǎng)課電腦手提到84G內(nèi)存；2294.1500

5627聯(lián)想聯(lián)想(lenovo)筆記本V14N4020英特爾離漕大屏號落窄邊商務辦公家用電腦14英寸4GK2379.1500

6697聯(lián)想聯(lián)用(lenow)筆記本⑷-55輕薄筆記本14英寸商務辦公學生手提電崎定《14G+12&X2464.1500

6702聯(lián)想聯(lián)想(Lenow)筆記本14155粒薄筆記本14英寸商務辦公^生手提電就定》!46”28612464.1SOO

5872宏基宏易(Acer)ffi5FX?1515.6英寸輕3Hs務辦公宏星筆記本電腦N4120四核處理號2番匹2549.1500

5875聯(lián)想聯(lián)想(Lenoro)筆記本14155輕薄筆記本14英寸商務辦公學生手提電一旗84GS56GI2549.1500

6855威爾1B爾(DELL)靈越500c3501/3400礴"01超極本學生辦公女生箜薄本手提筆記本電2S49.1S00

回回

SELECTpcod_id.prod_brdnd,prod_de$cribe,p?)d_price?0_85ASprod_prke_dFROMproductsWHI只讀玄詢時間：0235s第I供101*)

(4)查詢商品表，類別為“圖書”，且品類為“python”，將單價分成3段顯示：小于40

元顯示為“低價位”，大于等于40元且小于100元顯示為“中價位”，大于等于100元顯示為“高

價位”。

SELECTprod_d_describe,prod_price,

CASEWHENprod_price<40THEN'低價位'WHENprod_price>=40ANDprod_price<100THEN'中價位'

ELSE'高價位'ENDprod_price_q

FROMproducts

WHEREprod_category='圖書'ANDprod_brand="python'

ORDERBYprod_price_q;

prodjdproddescribeprodpnceprodpnceq-

>-62Python病片從入門到實線第2版(圖靈出㈤62.40中價質

881精通Python(微課視施電3.8?坂S4.00中價位

72利用Python進行數(shù)據(jù)分析(恚書第2版)98.20中價位

885Python網(wǎng)絡庵刨入門到IB通6520中價位

78Pythonfiag分析從入門到實線(全彩版)46.10中價位

886Python3快SA門與注662.10中價位

1508零Htt學Pytho<*蝌(少JUBW；版)65.20中價位

1507自然語言處理實應利用Python?之分下和生成文本49.50中價位

96Python3網(wǎng)絡爬蟲開發(fā)實戰(zhàn)(圖星出心)69.704MM8

893蟲術一Python笆技(博文視點出屁)48.50機T位

894蟲術——Python*技(用文視點出品)48.50中價W

100PythonWS從入門到減6笛2版58.50中價位

iniPythnnBtttt