大数据应用基础（人邮微课版）-课后提升参考答案 项目2-6 MySQL数据库设计-浪潮可视化大数据工具应用

大数据应用基础（人邮微课版）•课后提升参考答案

一、请根据给出资料完成数据库的概念模型、逻辑模型以及物理

模型设计

（1）概念模型设计

依据需求分析可知，该模型涉及4个实体，若干属性，且实体之间的关系清晰，可利用

E・R图工具完成概念模型的设计，如下图所示。

图概念模型设计

（2）逻辑模型设计

利用概念模型结果，依据概念模型转换逻辑模型的方法，将E・R图转换成逻辑模型，具

体转换方法如下：

1）实体的转换：将E・R图中的每个实体均转换为一个逻辑模型。

科目信息（科目代码,科目名称，科目类别，余额方向，数量单位）其中，“科目代码”

为主键。

凭证信息（凭证编号,附单据数，制单人，制单时间，借方合计，贷方合计，凭证状态）

其中“凭证编号”为主键。

账簿信息（账簿编号,科目代码，累计借方，累计贷方，初期余额）其中“账簿编号”

为主键。

2）联系的转换：

本案例中“科目信息”与“凭证信息”为包含关系，其类型为1：N,根据转换原则，

将1端实体主键添加到多端实体转换的模型中作为外键，即将“科目信息”中的“科目代码”

添加到“凭证信息”模型中作为外键；“凭证信息”与“账簿信息”为过账关系，其类型为

1：1,需将一端实体主键添加到另一端实体转换的模型中作为外键，即将“凭证信息”的主

键“凭证编码”添加到“账簿信息”模型中作为外键；“账簿信息”与“科目余额”为包含

关系，关系类型为N:l,同理，需将1端实体主键添加到多端实体转换的模型中作为外键。

最终转换结果如下：

科目信息（科目代码,科目名称，科目类别，数量单位）其中，“科目代码”为主键。

凭证信息（凭证编号,附单据数，制单人，制单时间，借方合计，贷方合计，凭证状态，

科目代码）其中“凭证编号”为主键，“科目代码”为外键。

账簿信息（账簿编号,科目代码，累计借方，累计贷方，初期余额，凭证编号）其中“账

簿编号”为主键，“凭证编号”作为外键。

（3）物理模型设计

本阶段对逻辑模型的成果进行物理模型的设计，结合实际的数据存储要求，对“科目信

息”、“凭证信息”、“账簿信息”的物理模型设计如下：

表科目信息表结构

字段名字段类型约束备注

KmIDchar(20)主键科目代码

KmNamechar(20)唯一约束科目名称

科目类别包括：资产、负债、共同、权益、

KmKindset成本、损益科目类别

Unitchar(10)数量单位

表凭证信息表结构

字段名字段类型约束备注

PzIDchar(4)主键凭证编号

FDJINT附单据数

ZDRchar(20)制单人

ZDSJdatetime自动填充系统时间制单时间

JFHJdecimal(12,2)借方合计

DFHJdecimal(12,2)贷方合计

PzZtchar(2)默认值为“否”凭证状态

表账簿信息表结构

字段名字段类型约束备注

Zbidchar(20)主键账簿编号

KmIDchar(20)外键科目代码

PzIDchar(4)外键凭证编号

LJJFdecimal(12,2)累计借方

LJDFdecimal(12,2)累计贷方

CQYEdecimal(12,2)初期余额

二、依据数据库设计阶段成果完成数据库的实施

本案例数据库管理系统采用MySQL8.0以及图形界面工具Navicato

（1）数据库的创建

1）启动MySQL8.0

可以通过windows的“服务”工具启动MySQL服务。（具体操作过程参见实训一）

2）打开Navicat工具，连接MySQL服务器，创建悠乐公司财务数据库。

步骤一：启动Navicat,连接服务器。（具体操作过程参见实训一）

步骤二：创建悠乐公司财务数据库。

（2）表及表约束的创建

数据库创建完成后即可进行表及约束的新建。双击打开“悠乐公司财务管理数据库”，

右击“表”命令，在右侧的表设计界面即可完成表的创建，具体方法参见实训二，在此将3

个表约束的实现方法进行讲解。

1)科目信息表

主键设置唯一约束设置

WgXXBK

mm

•｛耍lid

检查约束设置

2）凭证信息表

主键“凭证编号”的设置方法同上，此处不再赘述。

“制单时间”列要求自动填充系统时间默认值约束的设置

3）账簿信息表

“账簿编号”主键约束设置方法此处不再赘述。

外键约束设置

****%.WMmHMRHC>■AWUI»»«*«■

三、完成数据库数据的增、删、改及数据的导入导出

在进行数据的增、删、改及数据的导入导出之前，首先需要明确表的结构（如字段的数

据类型及长度等）、约束（主键、外键、唯一等）及表之间的关联（如下图），可以看出，科

目信息表以及凭证信息表为主键表，而账簿信息表为外键表，根据外键约束的要求，其取值

范围需要参照主键表中数据的取值范围，即账簿信息表中“KmlD”列的取值范围要参照科

目信息表中“KmlD”列的取值范围，那么在输入数据的时候需要先录入科目信息表的数据。

凭证信息表同账簿信息表的关系同理。

此部分操作方法同实训二、实训三，请参照完成，此处不再赘述。

凭证信息&

科目信息表账簿信息表

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）请在“科目信息表”中依次录入以下数据

记录1：KmID为1001；KmName为库存现金；KmKind为资产；Unit为元。

记录2：KmID为1001；KmName为银行存款；KmKind为资产；Unit为元。

记录3：KmID为1003；KmName为银行存款；KmKind为资产；Unit为元。

经过实操，发现记录1可以正常存储，记录2不能存储到数据库中，原因是违反了

主键约束要求，即作为主健的KmlD列不能出现重复值，将记录2的KmlD值输入为

1002,记录2可正常存储：记录3不能正常存储的原因是违反了KmName列的唯一

约束，将该列值修改为存放中央银行款项即可。

（2）请修改表中的数据

请修改记录2、记录3的数据，使其能正常存入数据库。

（3）请删除表中的数据

请科目信息表中的记录1、记录2及记录3.

（4）请将“科目信息表.txt"中的数据导入到数据库中，再将数据库中的“科目信息表”

数据导出，保存为“科目信息表.xlsx”。

四、进行数据库及表的备份与还原

请将悠乐公司财务管理数据店进行备份后还原。其操作步骤请参照任务三。

拓展一概览产品信息

(1)查看products表的表结构。

DESCRIBEproducts;

FieldTypeNullKeyDefaultExtra

prodidintNO0

prodbrandvarchar(20)NO

prodcategoryvarchar(50)YES

prodpricedecimal(8,2)YES

proddescribevarchar(255)YES

(2)查看products表中的数据。

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星河入券全网通手机

6946钠手机239X)0天语(K-Touch)V9双屏0含老人制超长待机移动联通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传奇版联龙888120W1fiW］充KPL防*事电竞手机双模

6953小米手机1399.00Redmi10X5G天矶820双5GM4800万海光相机柘<钻展6GB»128G8深海塞游

+-xc■物园..

SELECT♦FROMproducts只读音弼电0.223s第1条记录(M5947条)

(3)显示products表部分字段的数据，并设定各字段的中文别名。

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表中的数据按类别升序、品牌升序、价格降序进行显示。

商品类别、商品品牌按照汉字拼音的英文字母升序，再按照单价降序排序，如：“电脑”

排在“手机”的前面，“戴尔”排在“华硕”前面，“华硕”排在“华为”的前面：

SELECT*FROMproducts

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

信息结果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表中的数据按类别升序、品牌升序、价格降序排序后，用分页表格显

示，每页20条记录，查看显示第3页的数据。

查询先按照商品类别、商品品牌的汉字拼音的英文字母升序再按照单价降序排序的第

11行到第20行商品。使用LIMIT10,10,第1个10表示从第11行开始，第2个10表示显

年10行数据：

SELECT*FROMproducts

ORDERBYCONVERT(prod_categoryusinggbk),CONVERT(prod_brandusinggbk),prod_priceDESC

LIMIT10J0;

信息结果1创析状态

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专业独显创意设计师笔记本电脑2M

+-v*XC

SELECT,FROMproductsORDERBYCONVERT(prodcategoryusinggbk),COR只读否询时间0.179s第1(共10条)

拓展二查询产品特征

(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剖析状态

prodjdprod_brandprod_cotcgoryprod_priccprod_dcscribc

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

7822华为手机1038.00荣畅玩9A全网通4G手机6.3英寸珍珠全面屏震撼大音，益水技窣4GB+64GB

8167小米手机1047.00【现货速发)小米Redmi红米9手机可选分阻免思老人机学生机碇索黑全网通6G+128

8172小米手机1047.00【现货速发】小米Redmi红米9手机可选分期免息老人机学生机破素黑全网通6G+12&

8703小米环1048.00小*IDK9瓢敢盗全网通(6GB+128GB)

9003小米手机1049.00小米Redmi红米Note8手机星云紫全网通(6GB♦64GB)

9205小米环1049.00小米Redmitt*1OX^VUG5020mAh±f&*千谢天际益全网通(4GB128GB)

8104小米手机10SS.00小米红米9Redmi9游戏智能手机里只吉66B+128GB

8522vivo手机1079.00vivoiQOOUlx4G手机［购机5司礼］大解幕大电池内充千元机拍照学生游戏手机安卓智

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;

方法二：

【小知识】:优先级：()>not>and>or。

SELECT*FROMproducts

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

(prod_brand='华为'ORprod_brand='小米'ORprod_brand='vivo'ORprod_brand='oppo');

ORDERBYprod_price;

信息结果1状态

prodidprodbrandprcxicategoryprodpriceproddescribe

>%250小米-手机-289.00小米翥亲QinQF灯矶功能机备用机学生手机老人手机4G全网通超长待机双卡直板按朝

9246小米手机289.00小米多减QinQF9手机功能机苗用机学生手机老人手机4G全网通超长待机双卡直板按W

8836小米手机289.00〃冰生态21KE/4G全场通21克F9小学生AI功能电话小爱同学老人手机移动联通电信老年)

9446小米手机299.00多辛QNQF9全网通4G智雌键移动联通电信老人手机定位小米无摄像头老人机小爱同之

9894小米手机389.00小米有目多亲AI电话QinB+小爱同学接铮直板老人手机支持联通移动双卡双待远旌向

8764小米手机389.00小米有品多余Qin手机AI电话S+小爱同学按键面板老人手机联通移动双卡学生机双符园

8753小米手机399.00多宗QIN1S+全网通4J人手机小爱同学空手机定位无患像头大屏按犍老年手机小米老，

9297小米手机399.00小米生存米米多亲Qin1s向电话移动联通电信VoUE老人手机双卡双待直板手机IS+燃

7886小米手机399.00小米多减Qin1S+ai手机小爱同学4G全网通笛用机直板按犍微信学生三潸能热点戒网老4

9504小米手机399.00多亲QIN全网通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;

信息剖析状态

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联忸织SKenovo浑记本V14N4020英特尔岛汨大屏长薄窄边商务办公家用电却4英寸4G72294.1500

6279宏基宏昌(acet)—fX21515.6英寸轻薄商务办公宏*记本电182♦二【四核N4120/12294.1500

6284宏W宏号(acer)"舞EX2I515.6英寸长廊S务办公宏SS笔记本电做套餐二【四修N4120/12294.1500

6503联想联想(Lenovo)8BIBE41-55较薄笔记本14英寸商务办公学生网课电脑手提到84G内存；2294.1500

5627联想联想(lenovo)笔记本V14N4020英特尔离漕大屏号落窄边商务办公家用电脑14英寸4GK2379.1500

6697联想联用(lenow)笔记本⑷-55轻薄笔记本14英寸商务办公学生手提电崎定《14G+12&X2464.1500

6702联想联想(Lenow)笔记本14155粒薄笔记本14英寸商务办公^生手提电就定》!46”28612464.1SOO

5872宏基宏易(Acer)ffi5FX?1515.6英寸轻3Hs务办公宏星笔记本电脑N4120四核处理号2番匹2549.1500

5875联想联想(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进行数据分析(恚书第2版)98.20中价位

885Python网络庵刨入门到IB通6520中价位

78Pythonfiag分析从入门到实线(全彩版)46.10中价位

886Python3快SA门与注662.10中价位

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

1507自然语言处理实应利用Python®之分下和生成文本49.50中价位

96Python3网络爬虫开发实战(图星出心)69.704MM8

893虫术一Python笆技(博文视点出屁)48.50机T位

894虫术——Python*技(用文视点出品)48.50中价W

100PythonWS从入门到减6笛2版58.50中价位

iniPythnnBtttt