推广网站高端网站建设哪家更专业

推广网站,高端网站建设哪家更专业,招商网站大全五金电器,wordpress php 模板修改汽车用品网上商城系统概述#xff1a;汽车用品网上商城数据库由以下8张表组成#xff1a;autoparts,category,client,clientkind,comment,order,order_has_autoparts,shoppingcart#xff0c;分别为汽车配件表、商品类别表、用户表、用户类别表、评论表、订单表、订单明细表…汽车用品网上商城系统概述汽车用品网上商城数据库由以下8张表组成autoparts,category,client,clientkind,comment,order,order_has_autoparts,shoppingcart分别为汽车配件表、商品类别表、用户表、用户类别表、评论表、订单表、订单明细表、购物车配件表。功能结构图页面展示形式1“汽车用品网上商城”前台功能【实验7-1】主页中查询汽车配件对应的SQL操纵编写一存储过程实现查询特定汽车配件信息的功能。【实验7-2】操作购物车往购物车表中添加记录并对购物车中某一条记录作删除操作。【实验7-3】提交订单编写一存储过程给定会员编号、收货人姓名、收货人地址后在订单表中生成订单信息同时将购物车中已有的该会员的购物记录追加到订单明细表中。代码如下:CREATE DEFINERrootlocalhost 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_cidscid; select number into snum from shopping.shoppingcart where client_cidscid; select price into sprice from autoparts where apidsapid; select weight*snum into sweight from autoparts where apidsapid; set sgood_pricesprice*snum; set sweightsweight*snum; set stimenow(); 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_datestime; 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))beginselect * from shopping.autoparts where apnamepname;endcall see_autoparts(轮胎)【实验7-2】操作购物车往购物车表中添加记录并对购物车中某一条记录作删除操作。insert into shoppingcart(autoparts_apid,client_cid,number,add_time) values(10,12,1,now());delete from shoppingcart where autoparts_apid10 and client_cid12;【实验7-3】提交订单编写一存储过程给定会员编号、收货人姓名、收货人地址后在订单表中生成订单信息同时将购物车中已有的该会员的购物记录追加到订单明细表中。CREATE DEFINERrootlocalhost PROCEDURE submit_order(in scid int,in sname varchar(50),in saddr text)BEGINdeclare 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_cidscid;select number into snum from shopping.shoppingcart where client_cidscid;select price into sprice from autoparts where apidsapid;select weight*snum into sweight from autoparts where apidsapid;set sgood_pricesprice*snum;set sweightsweight*snum;set stimenow();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_datestime;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,89895678qq.com,now(),23);delete from shopping.client where cid25;update shopping.client set cname萧炎 where cid24;select * from shopping.client where cid24;【实验7-5】管理商品对汽车配件表中数据进行增删改查。insert into shopping.autoparts(apname,is_sale,price,secondclass_scid) values(轮胎,0,200,1);delete from shopping.autoparts where apid10;update shopping.autoparts set apname发动机 where apid11;select * from shopping.autoparts;【实验7-6】管理类别对商品类别表进行增删改查。insert into shopping.category(name) values(轮胎系列);delete from shopping.category where category_id102;update shopping.category set name传动系统 where category_id101;select * from shopping.category;【实验7-7】管理订单修改订单表中的状态字段。update shopping.order set status已退款 where oid13;【实验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.txtload 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 apnamepname; 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_apid10 and client_cid12; 【实验7-3】提交订单编写一存储过程给定会员编号、收货人姓名、收货人地址后在订单表中生成订单信息同时将购物车中已有的该会员的购物记录追加到订单明细表中。 CREATE DEFINERrootlocalhost 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_cidscid; select number into snum from shopping.shoppingcart where client_cidscid; select price into sprice from autoparts where apidsapid; select weight*snum into sweight from autoparts where apidsapid; set sgood_pricesprice*snum; set sweightsweight*snum; set stimenow(); 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_datestime; 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,89895678qq.com,now(),23); delete from shopping.client where cid25; update shopping.client set cname萧炎 where cid24; select * from shopping.client where cid24; 【实验7-5】管理商品对汽车配件表中数据进行增删改查。 insert into shopping.autoparts(apname,is_sale,price,secondclass_scid) values(轮胎,0,200,1); delete from shopping.autoparts where apid10; update shopping.autoparts set apname发动机 where apid11; select * from shopping.autoparts; 【实验7-6】管理类别对商品类别表进行增删改查。 insert into shopping.category(name) values(轮胎系列); delete from shopping.category where category_id102; update shopping.category set name传动系统 where category_id101; select * from shopping.category; 【实验7-7】管理订单修改订单表中的状态字段。 update shopping.order set status已退款 where oid13; 【实验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;