汽车用品网上商城系统概述：
汽车用品网上商城数据库由以下8张表组成：
autoparts,category,client,clientkind,comment,order,order_has_autoparts,shoppingcart，分别为汽车配件表、商品类别表、用户表、用户类别表、评论表、订单表、订单明细表、购物车配件表。
功能结构图：
页面展示形式：
1．“汽车用品网上商城”前台功能
【实验7-1】主页中查询汽车配件对应的SQL操纵，编写一存储过程，实现查询特定汽车配件信息的功能。
【实验7-2】操作购物车，往购物车表中添加记录，并对购物车中某一条记录作删除操作。
【实验7-3】提交订单，编写一存储过程，给定会员编号、收货人姓名、收货人地址后在订单表中生成订单信息，同时将购物车中已有的该会员的购物记录追加到订单明细表中。
代码如下:
CREATE DEFINER=`root`@`localhost` PROCEDURE `submit_order`(in scid int,in sname varchar(50),in saddr text) BEGIN declare sapid int; declare snum int; declare sprice decimal(5,2); declare sgood_price decimal(5,2); declare sweight int; declare stime datetime; declare soid int; select autoparts_apid into sapid from shopping.shoppingcart where client_cid=scid; select `number` into snum from shopping.shoppingcart where client_cid=scid; select price into sprice from autoparts where apid=sapid; select weight*snum into sweight from autoparts where apid=sapid; set sgood_price=sprice*snum; set sweight=sweight*snum; set stime=now(); insert into `order`(`status`,order_date,client_cid,goods_price,carriage_price,total_price,total_weight,`name`,telephone,address_aid,pay_type) values("已付款",stime,scid,sgood_price,0,sgood_price,sweight,sname,"13896582356",saddr,"微信支付"); select oid into soid from `order` where order_date=stime; insert into order_has_autoparts(autoparts_apid,order_oid,deal_price,`number`) values(sapid,soid,sprice,snum); END2．“汽车用品网上商城”后端功能
【实验7-4】管理会员，对会员表中数据进行增删改查。
【实验7-5】管理商品，对汽车配件表中数据进行增删改查。
【实验7-6】管理类别，对商品类别表进行增删改查。
【实验7-7】管理订单，修改订单表中的状态字段。
【实验7-8】查询统计，通过查询视图或者基表等手段，完成如下统计：本月销售汽车配件总数量、销售总金额、订单总数量、发生订单的会员数；最大的订单、最小的订单、消费金额最多的会员、消费金额最少的会员；卖的最好的汽车配件、卖得最差的汽车配件。
本月销售汽车配件总数量:
本月销售总金额:
本月订单总数量:
本月发生订单的会员数:
本月最大的订单：
本月最小的订单：
本月消费金额最多的会员：
本月消费金额最少的会员：
本月卖的最好的汽车配件:
本月卖得最差的汽车配件:
3．数据库备份与恢复
【实验7-9】对Shopping数据库进行备份
4．数据导入导出
【实验7-10】以文本格式导出汽车配件表，在Excel下完成汽车配件表的编辑（可选），将编辑后的汽车配件信息导入到Shopping数据库汽车配件表中。
导出汽车配件表:
导入汽车配件表:
【实验7-1】主页中查询汽车配件对应的SQL操纵，编写一存储过程，实现查询特定汽车配件信息的功能。
create procedure see_autoparts(in pname varchar(20))
begin
select * from shopping.autoparts where apname=pname;
end
call see_autoparts("轮胎")
【实验7-2】操作购物车，往购物车表中添加记录，并对购物车中某一条记录作删除操作。
insert into shoppingcart(autoparts_apid,client_cid,`number`,add_time) values(10,12,1,now());
delete from shoppingcart where autoparts_apid=10 and client_cid=12;
【实验7-3】提交订单，编写一存储过程，给定会员编号、收货人姓名、收货人地址后在订单表中生成订单信息，同时将购物车中已有的该会员的购物记录追加到订单明细表中。
CREATE DEFINER=`root`@`localhost` PROCEDURE `submit_order`(in scid int,in sname varchar(50),in saddr text)
BEGIN
declare sapid int;
declare snum int;
declare sprice decimal(5,2);
declare sgood_price decimal(5,2);
declare sweight int;
declare stime datetime;
declare soid int;
select autoparts_apid into sapid from shopping.shoppingcart where client_cid=scid;
select `number` into snum from shopping.shoppingcart where client_cid=scid;
select price into sprice from autoparts where apid=sapid;
select weight*snum into sweight from autoparts where apid=sapid;
set sgood_price=sprice*snum;
set sweight=sweight*snum;
set stime=now();
insert into `order`(`status`,order_date,client_cid,goods_price,carriage_price,total_price,total_weight,`name`,telephone,address_aid,pay_type)
values("已付款",stime,scid,sgood_price,0,sgood_price,sweight,sname,"13896582356",saddr,"微信支付");
select oid into soid from `order` where order_date=stime;
insert into order_has_autoparts(autoparts_apid,order_oid,deal_price,`number`) values(sapid,soid,sprice,snum);
END
【实验7-4】管理会员，对会员表中数据进行增删改查。
insert into shopping.client(cid,cname,`password`,phone_number,email,createtime,ckind) values(25,"唐三",'111111',"15888886666","89895678@qq.com",now(),23);
delete from shopping.client where cid=25;
update shopping.client set cname="萧炎" where cid=24;
select * from shopping.client where cid=24;
【实验7-5】管理商品，对汽车配件表中数据进行增删改查。
insert into shopping.autoparts(apname,is_sale,price,secondclass_scid) values("轮胎",0,200,1);
delete from shopping.autoparts where apid=10;
update shopping.autoparts set apname="发动机" where apid=11;
select * from shopping.autoparts;
【实验7-6】管理类别，对商品类别表进行增删改查。
insert into shopping.category(`name`) values("轮胎系列");
delete from shopping.category where category_id=102;
update shopping.category set name="传动系统" where category_id=101;
select * from shopping.category;
【实验7-7】管理订单，修改订单表中的状态字段。
update shopping.order set `status`="已退款" where oid=13;
【实验7-8】查询统计，通过查询视图或者基表等手段，完成如下统计：本月销售汽车配件总数量、销售总金额、订单总数量、发生订单的会员数；最大的订单、最小的订单、消费金额最多的会员、消费金额最少的会员；卖的最好的汽车配件、卖得最差的汽车配件。
select month(now()) as "本月",sum(sum_number) as "销售汽车配件总数量" from shopping.everyday_everyparts where month(everyday) =month(now());
select month(now()) as "本月",sum(income) as "销售总金额" from shopping.everyday where month(riqi)=month(now());
select month(now()) as "本月",sum(order_total) as "订单总数量" from shopping.everyday where month(riqi)=month(now());
select count(distinct cid) as "发生订单的会员数" from shopping.client_order;
select * from shopping.everyday_everyparts order by stotal_price desc limit 0,1;
select * from shopping.everyday_everyparts order by stotal_price asc limit 0,1;
select cid,sum(total_price) as stotal from shopping.client_order group by cid order by stotal desc limit 0,1;
select cid,sum(total_price) as stotal from shopping.client_order group by cid order by stotal asc limit 0,1;
select autoparts_apid,sum(sum_number) as num_total from shopping.everyday_everyparts group by autoparts_apid order by num_total desc limit 0,1;
select apid as "没有产生过订单的配件编号" from autoparts where apid not in(select autoparts_apid from order_has_autoparts);
【实验7-10】以文本格式导出汽车配件表，在Excel下完成汽车配件表的编辑（可选），将编辑后的汽车配件信息导入到Shopping数据库汽车配件表中。
use shopping;
select * from autoparts into outfile "d:/backup/autoparts.txt"
load data infile "d:/backup/autoparts.txt" replace into table shopping.autoparts;
【实验7-1】主页中查询汽车配件对应的SQL操纵，编写一存储过程，实现查询特定汽车配件信息的功能。 create procedure see_autoparts(in pname varchar(20)) begin select * from shopping.autoparts where apname=pname; end call see_autoparts("轮胎") 【实验7-2】操作购物车，往购物车表中添加记录，并对购物车中某一条记录作删除操作。 insert into shoppingcart(autoparts_apid,client_cid,`number`,add_time) values(10,12,1,now()); delete from shoppingcart where autoparts_apid=10 and client_cid=12; 【实验7-3】提交订单，编写一存储过程，给定会员编号、收货人姓名、收货人地址后在订单表中生成订单信息，同时将购物车中已有的该会员的购物记录追加到订单明细表中。 CREATE DEFINER=`root`@`localhost` PROCEDURE `submit_order`(in scid int,in sname varchar(50),in saddr text) BEGIN declare sapid int; declare snum int; declare sprice decimal(5,2); declare sgood_price decimal(5,2); declare sweight int; declare stime datetime; declare soid int; select autoparts_apid into sapid from shopping.shoppingcart where client_cid=scid; select `number` into snum from shopping.shoppingcart where client_cid=scid; select price into sprice from autoparts where apid=sapid; select weight*snum into sweight from autoparts where apid=sapid; set sgood_price=sprice*snum; set sweight=sweight*snum; set stime=now(); insert into `order`(`status`,order_date,client_cid,goods_price,carriage_price,total_price,total_weight,`name`,telephone,address_aid,pay_type) values("已付款",stime,scid,sgood_price,0,sgood_price,sweight,sname,"13896582356",saddr,"微信支付"); select oid into soid from `order` where order_date=stime; insert into order_has_autoparts(autoparts_apid,order_oid,deal_price,`number`) values(sapid,soid,sprice,snum); END 【实验7-4】管理会员，对会员表中数据进行增删改查。 insert into shopping.client(cid,cname,`password`,phone_number,email,createtime,ckind) values(25,"唐三",'111111',"15888886666","89895678@qq.com",now(),23); delete from shopping.client where cid=25; update shopping.client set cname="萧炎" where cid=24; select * from shopping.client where cid=24; 【实验7-5】管理商品，对汽车配件表中数据进行增删改查。 insert into shopping.autoparts(apname,is_sale,price,secondclass_scid) values("轮胎",0,200,1); delete from shopping.autoparts where apid=10; update shopping.autoparts set apname="发动机" where apid=11; select * from shopping.autoparts; 【实验7-6】管理类别，对商品类别表进行增删改查。 insert into shopping.category(`name`) values("轮胎系列"); delete from shopping.category where category_id=102; update shopping.category set name="传动系统" where category_id=101; select * from shopping.category; 【实验7-7】管理订单，修改订单表中的状态字段。 update shopping.order set `status`="已退款" where oid=13; 【实验7-8】查询统计，通过查询视图或者基表等手段，完成如下统计：本月销售汽车配件总数量、销售总金额、订单总数量、发生订单的会员数；最大的订单、最小的订单、消费金额最多的会员、消费金额最少的会员；卖的最好的汽车配件、卖得最差的汽车配件。 select month(now()) as "本月",sum(sum_number) as "销售汽车配件总数量" from shopping.everyday_everyparts where month(everyday) =month(now()); select month(now()) as "本月",sum(income) as "销售总金额" from shopping.everyday where month(riqi)=month(now()); select month(now()) as "本月",sum(order_total) as "订单总数量" from shopping.everyday where month(riqi)=month(now()); select count(distinct cid) as "发生订单的会员数" from shopping.client_order; select * from shopping.everyday_everyparts order by stotal_price desc limit 0,1; select * from shopping.everyday_everyparts order by stotal_price asc limit 0,1; select cid,sum(total_price) as stotal from shopping.client_order group by cid order by stotal desc limit 0,1; select cid,sum(total_price) as stotal from shopping.client_order group by cid order by stotal asc limit 0,1; select autoparts_apid,sum(sum_number) as num_total from shopping.everyday_everyparts group by autoparts_apid order by num_total desc limit 0,1; select apid as "没有产生过订单的配件编号" from autoparts where apid not in(select autoparts_apid from order_has_autoparts); 【实验7-10】以文本格式导出汽车配件表，在Excel下完成汽车配件表的编辑（可选），将编辑后的汽车配件信息导入到Shopping数据库汽车配件表中。 use shopping; select * from autoparts into outfile "d:/backup/autoparts.txt" load data infile "d:/backup/autoparts.txt" replace into table shopping.autoparts;
