MySQL数据库应用-国家开放大学电大学习网形考作业题目答案

MySQL数据库应用

实验1-1参见4.1节内容，完成MySQL数据库的安装与配置。

MySQL数据库的安装与配置。下载后的MySQL安装文件是mysql-5.5.12-win32.msi，运行该程序可以进行MySQL安装，在安装完成后会显示如图4-2，在图4-2中选中LaunchtheMySQLInstanceConfigurationWizard复选框，单击Finish按钮，进行配置，显示如图4-3所示的对话框。选择使用哪种配置方式在图4-3中，选中DetailedConfiguration进行详细配置，单击Next按钮，下一界面选中DeveloperMachine（开发者机器）单选按钮，单击Next按钮；继续选中MultifunctionalDatabase（多功能数据库），单击Next按钮；选择InnoDB表空间保存位置，单击Next按钮；下一界面中选择服务器并发访问人数；设置端口号和服务器SQL模式（MySQL使用的默认端口是3306，在安装时，可以修改为其他的，如3307，但是一般情况下，不要修改默认的端口号，除非3306端口已经被占用）；选中ManualSelectedDefaultCharacterSet/Collation（设置默认字符集编码为utf8），单击Next按钮，选中InstallAsWindowsService和IncludeBinDirectoryinWindowsPATH复选框，针对Windows系统设置，单击Next按钮，打开如图4-4所示的对话框；在图4-4中输入数据库的密码“111”，单击Next按钮（注意：在安装MySQL数据库时，一定要牢记在上述步骤中设置的默认用户root的密码，这是我们在访问MySQL数据库时必须使用的），打开如图4-5所示的对话框。图4-4输入数据库的密码图图4-5确认配置对话框在图4-5中单击Execute按钮，执行前面进行的各项配置，配置完成后的效果如图4-6所示。图4-6完成配置到此MySQL已安装成功，如果要查看MySQL的安装配置信息，则可以通过MySQL安装目录下的my.ini文件来完成。如图4-7所示。图4-7my.ini文件的配置信息4.1.3启动、连接、断开和停止MySQL服务器MySQL安装完成后，便形成了Windows下的一个MySQL服务器，当这个服务器启动后，用户才可以对MySQL数据库进行访问。根据具体需要，也可以通过系统服务器或者命令提示符（DOS）启动、连接和关闭MySQL。资料：服务器和客户端客户机/服务器结构，通常有一台或多台服务器以及大量的客户机。服务器配备大容量存储器并安装数据库系统，用于数据的存放和数据检索；客户端安装专用的软件，负责数据的输入、运算和输出。当一台连入网络的计算机向其他计算机提供各种网络服务(如数据、文件的共享等)时，它就被叫做服务器；而那些用于访问服务器资料的计算机则被叫做客户机。客户机/服务器结构并不一定是从物理分布的角度来定义，它所体现的是一种网络数据访问的实现方式，也可以在一台机器上既有服务器又有客户端。采用这种结构的系统目前应用非常广泛。如宾馆、酒店的客房登记、结算系统，超市的POS系统，银行、邮电的网络系统等。数据库服务器，就是集中存放数据的地方，是指安装MySQL的那台机器，是“数据库引擎”，为客户端应用提供服务，这些服务可以是查询、更新、事务管理、索引、高速缓存、查询优化、安全及多用户存取控制等，一个服务器可以同时服务多个客户端的请求。客户端可以是远程通过网络使用服务器上的MySQL，也可以是本地直接使用服务器上的MySQL，客户端通过得知远程服务器的ip地址或者端口号以及一些密码信息等首先与服务器创建连接，然后使用MySQL数据库。1．启动、停止MySQL服务器启动和停止MySQL服务器的方法有两种：系统服务器和命令提示符（DOS）。（1）通过系统服务器启动和停止MySQL服务器Windows下选择“开始”/“控制面板”/“系统和安全”/“管理工具”/“服务”打开Windows服务管理器。右击服务器列表中的MySQL服务，在弹出的快捷菜单中选择相应命令即可完成MySQL服务的各种操作，如启动、重新启动、停止、暂停和恢复，如图4-8所示。图4-8通过系统服务启动、停止MySQL服务器（2）在命令提示符下启动和停止MySQL服务器选择“开始”/“运行”命令，在弹出的“运行”窗口中输入“cmd”，按Enter键进入命令提示符窗口。在命令提示符下输入：\>netstartmysql，将启用MySQL服务器；输入：\>netstoptmysql，即可停止MySQL服务器。在命令提示符下启动和停止MySQL服务器的运行结果如图4-9所示。图4-9在命令提示符下启动和停止MySQL服务器2．连接、断开MySQL服务器在通过SQL语句使用MySQL数据库之前，还必须与MySQL服务器创建连接，需要启动MySQL服务器才能连接成功，一个服务器可以同时服务多个用户，每个用户必须首先与MySQL服务器创建连接，在连接过程中可以进行SQL语言操作，在操作完成之后，可以断开与MySQL服务器的连接。（1）连接MySQL服务器连接MySQL服务器通过mysql命令实现。在MySQL服务器启动后，选择“开始”/“运行”命令，在弹出的“运行”窗口中输入“cmd”，进入命令提示符窗口，在命令提示符下输入：\>mysql-uroot-h-ppassword在连接MySQL服务器时，MySQL服务器所在地址可以省略不写。输入完命令语句后，按Enter键即可连接MySQL服务器，如图4-10所示。图4-10连接MySQL服务器如果用户在使用mysql命令连接MySQL服务器时弹出如图4-11所示的信息，则说明用户未设置系统的环境变量。图4-11连接MySQL服务器出错用户没有将MySQL服务器的bin文件夹位置添加到Windows的“环境变量”/“系统变量”/path中，从而导致命令不能执行，这个环境变量的设置方法如例4-2所示。例环境变量设置完成后，再使用mysql命令即可成功连接MySQL服务器。（2）断开MySQL服务器连接到MySQL服务器后，可以通过在MySQL提示符下输入exit或者quit命令断开MySQL连接，格式如下：mysql>quit;实验1-2初始数据库创建数据库ShoppingcreatedatabaseShopping;实验1-33-4createtableautoparts(ap_idint(11)auto_incrementprimarykeyCOMMENT'汽车配件编号',ap_nameTINYTEXTCOMMENT'汽车配件名称',image_link1VARCHAR(50)COMMENT'商品图片链接1',image_link2VARCHAR(50)COMMENT'商品图片链接2',image_link3VARCHAR(50)COMMENT'商品图片链接3',introductionTextCOMMENT'描述介绍',is_saleBoolCOMMENT'是否促销',old_priceDECIMAL(5,2)COMMENT'原价',priceDECIMAL(5,2)COMMENT'现价',weightint(11)COMMENT'重量',is_generalboolCOMMENT'是否通用',virtual_inventoryint(11)COMMENT'虚拟库存',inventoryint(11)COMMENT'实际库存',productive_yearDATETIMECOMMENT'生产日期',shelve_yearDATETIMECOMMENT'上架日期',hot_productint(11)COMMENT'可销售件数',second_class_scidint(11)COMMENT'分类编号',brandTINYTEXTCOMMENT'配件品牌',foreignkey(second_class_scid)referencescategory(category_id));--不知道这个外键是不是category的主键3-5createtablecategory(category_idint(11)notnullauto_incrementprimarykeyCOMMENT'类别编号',category_nameCHAR(50)notnullCOMMENT'类别名称',category_describeTINYTEXTCOMMENT'类别描述',category_c_id int(11)COMMENT'父类别ID',foreignkey(category_c_id)referencescategory(category_id));3-6createtableclient(c_idint(11)auto_incrementprimarykeyCOMMENT'会员编号',imageVARCHAR(50)COMMENT'会员头像',c_nameCHAR(50)notnullCOMMENT'会员名称',passwordCHAR(50)notnullCOMMENT'密码',phone_numberVARCHAR(20)notnullCOMMENT'电话',emailVARCHAR(50)COMMENT'电子邮箱',create_timeDateTimeCOMMENT'创建日期',c_kindint(11)COMMENT'类别编号',foreignkey(c_kind)referencesclient_kind(kid));3-73-8createtableshopping_cart(autoparts_apidint(11)COMMENT'汽车配件编号',client_cidint(11)COMMENT'会员编号',numberint(11)notNULLCOMMENT'数量',add_timeDATETIMEnotnullCOMMENT'添加时间',foreignkey(autoparts_apid)referencesautoparts(ap_id),foreignkey(client_cid)referencesclient_kind(kid));3-9createtableshopping_order(oidint(11)auto_incrementprimarykeyCOMMENT'订单编号',order_statusCHAR(20)COMMENT'状态',order_dateDateTimenotnullComment'下单日期',client_cidintCOMMENT'客户编号',goods_priceDECIMAL(5,2)notnullCOMMENT'货品总价',carriage_priceDECIMAL(5,2)notnullCOMMENT'运费',total_priceDECIMAL(5,2)notnullCOMMENT'订单总价',total_weightint(11)notnullcomment'总重量',distribution_type_dpidVARCHAR(50)COMMENT'配送类型',courier_numberVARCHAR(50)COMMENT'快递单号',reDistribution_type_dpidVARCHAR(50)COMMENT'退货配送类型',return_courier_numberVARCHAR(50)COMMENT'退货快递单号',is_carriage_freeBOOLCOMMENT'是否包邮',has_paiedBOOLCOMMENT'是否已付款',is_arayacakBOOLCOMMENT'是否自提',order_nameVARCHAR(50)notnullCOMMENT'收获人姓名',telephoneVARCHAR(50)notNULLcomment'电话',arrivetimeDateCOMMENT'送达时间',address_aidDATECOMMENT'送达时间',pay_typeVARCHAR(50)notNULLCOMMENT'支付类型',return_priceDECIMAL(5,2)COMMENT'退款金额',foreignkey(client_cid)referencesclient_kind(kid));3-10createtableorder_has_autoparts(autoparts_apidint(11)COMMENT'汽车配件编号',order_oidint(11)COMMENT'订单编号',deal_priceDECIMAL(5,2)notNULLCOMMENT'商品单价',numberint(11)notnullcomment'数量',return_numberint(11)COMMENT'退货数量',timeDateTimeCOMMENT'添加时间',foreignkey(autoparts_apid)referencesautoparts(ap_id),foreignkey(order_oid)referencesshopping_order(oid));3-11createtablecomment(comment_idint(11)primaryKEYCOMMENT'评论编号',commentsTINYTEXTCOMMENT'评论描述',autoparts_apidint(11)COMMENT'汽车配件编号',client_cidint(11)COMMENT'客户编号',foreignkey(autoparts_apid)referencesautoparts(ap_id),foreignkey(client_cid)referencesclient_kind(kid));实验1-4Showuseshopping;showtables;DECSCRIBEdescribeclient;实验1-5ALTERTABLE查看当前表结构useshopping;describeclient_kind;添加一个列ALTERTABLEclient_kindADDcolumntestint(11);describeclient_kind;删除test列ALTERTABLEclient_kindDROPtest;describeclient_kind;RENAMETABLE查看原有的表showtables;重命名renametableclienttoclient_bak;showtables;实验1-6复制一张表CREATETABLEtestAS(SELECT*FROMclient);showtables;删除test表droptabletest;showtables;实验1-7启动netstartmysql停止netstopmysql连接mysql-h主机名-u用户名-p用户密码mysql-hlocalhost-uroot-p123456断开quit;实验1-8showdatabases;showdatabases;USEDATABASEusetest_database;DROPDATABASEdropdatabasetest_database;showdatabases;实验2.1查询商品名称为“挡风玻璃”的商品信息。select*fromautopartswhereap_name='挡风玻璃';查询ID为1的订单。select*fromshopping_orderwhereoid=1实验2.2select*fromautopartswhereis_sale=1andprice<1000;实验2.3查询所有对商品ID为1的商品发表过评论的用户ID。selectdistinct(comment.client_cid)as用户IDfromcommentleftjoinautopartsoncomment.autoparts_apid=autoparts.ap_id;查询此汽车用品网上商城会员的创建时间段，1年为一段。selectc_id,year(create_time)fromclientgroupbyyear(create_time),c_id;实验2.4查询类别ID为1的所有商品，结果按照商品ID降序排列。select*fromautopartswheresecond_class_scid=1orderbyap_iddescSsselect*fromclientgroupbyyear(now()),c_nameorderbyc_name;实验2.5查询每个用户的消费总金额（所有订单）。selectclient_cid用户ID,SUM(total_price)总价fromshopping_ordergroupbyclient_cid;查询类别价格一样的各种商品数量总和。selecta.ap_name商品名称,sum(oha.number)商品数量fromautopartsaleftjoinorder_has_autopartsohaona.ap_id=oha.autoparts_apidleftjoinshopping_ordersoonoha.order_oid=so.oidwhere1=1groupbyoha.autoparts_apid,a.second_class_scid,a.price实验2.6查询类别的数量。selectcount(category_name)fromcategory查询汽车用品网上商城的每天的接单数。selectorder_date日期,count(oid)订单数fromshopping_orderwhereorder_status=1groupbyday(order_date),oid;实验2.7selectorder_date日期,sum(total_price)每次订单总价fromshopping_orderwhereorder_status=1groupbyday(order_date),total_price;实验2.8selectorder_date日期,avg(total_price)每次订单总价fromshopping_orderwhereorder_status=1groupbyday(order_date),total_price;实验2.9selectmax(hot_product)fromautoparts实验2.10selectmin(price)fromautoparts实验2.11查询所有订单的发出者名字。selectdistinct(client.c_name)fromclientinnerjoinshopping_orderonshopping_order.client_cid=client.c_id查询每个用户购物车中的商品名称。selectautoparts.ap_namefromautopartsinnerjoinshopping_cartonautoparts.ap_id=shopping_cart.autoparts_apidwhereclient_cid=1实验2.12查询列出所有用户ID，以及他们的评论，如果有的话。selectc1.c_name姓名,ments评论fromclientc1leftjoincommentc2onc1.c_id=c2.client_cid实验2.13查询用户ID为1的客户的订单信息和客户名。selectc1.c_name姓名,c2.*fromclientc1leftjoinshopping_orderc2onc1.c_id=c2.client_cidwherec1.c_id=1查询每个用户的购物车中的商品价格，并且按照价格顺序排列。selectc1.ap_name商品名称,c1.pricefromautopartsc1leftjoinshopping_cartc2onc2.autoparts_apid=c1.ap_idorderbyc1.priceasc实验2.14selectso.client_cidfromshopping_ordersowhereso.oidin(selectorder_oidfromorder_has_autoparts)实验2.151.select

*

from

client

where

DATE_FORMA(create_time,'yyyy')

=

'2021'

and

client_cid

in

(

select

client_cid

from

shopping_order

having

sum(total_price)2.select

*

from

client

where

DATE_FORMA(create_time,'yyyy')

=

'2021'

and

client_cid

not

in

(

select

client_cid

from

shopping_order

having

sum(total_price)实验2.16select

*

from

client

where

exists

(select

*

from

client

a

where

a.c_id

=

'100')实验2.17select

ap_id

from

autoparts

where

price

>

any(select

goods_price

from

shopping_order

)实验2.18select

ap_id

from

autoparts

where

price

>

all(select

goods_price

from

shopping_order

)

实验2.191.select

*

from

(

select

*

from

autoparts

where

Price

<

5

UNION

select

*

from

autoparts

where

second_class_scid

in

(1,2)

)

t

group

by

ap_id2.select

*

from

(

select

*

from

autoparts

where

Price

<

5

UNION

all

select

*

from

autoparts

where

second_class_scid

in

(1,2)

)

t

group

by

ap_id 实验训练3、数据增删改操作【实验3-1】(1)(2)>insertintoautopartsselect*fromautoparts【实验3-2】>deletefromautopartswhereApid=1>deletefromorderwhereOid=1>deletefromOrder_has_AutopartswhereAutoparts_apid=1>deletefromCommentwhereComment_id=1>truncatetableshoppingcart

>truncatetableComment【实验3-3】>UPDATEshopping.autopartsSETApname='方向盘33'WHERE`Apid`=3;

>UPDATEshopping.categorySETName='宝马零件2'WHERECategory_ID=1;

>UPDATEshopping.clientSETCname='libbo33'WHEREcid=1;>UPDATEshopping.clientkindSETName='中级会员'WHEREKid=1;>UPDATEmentSETComments='这个操作盘非常牛逼33'WHEREComment_id=1;>UPDATEshopping.orderSETAddress_aid='广州北京路002号'WHEREOid=1;>UPDATEshopping.order_has_autopartsSETreturn_number=100WHEREAutoparts_apid=1;>UPDATEshopping.shoppingcartSETNumber=3WHEREAutoparts_apid=1;实验训练4、数据库系统维护【实验6-1】>createuser'Teacher'@'localhost'identifiedby'T99999';>createuser'Student'@'localhost'identifie