mysql实例电子城项目&数据库作业（13） 任务三 对表的数据进行基本操作

1.所有字段批量插入用户表数据

phone	username	password	question	answer
13812345678	anne	annnepassword	favorite book	harry potter
18212345678	frank	Frankpassword	Favorite song	lonely
13212345678	alan	Alanpassword	First love	carry
13112345678	peter	Peterpassword	Who is your father	jack

insert into user_zhanghaili

    -> values (13812345678,"anne","annepassword","favorite book","harry potter"),

    -> (18212345678,"frank","frankpassword","favorite song","lonely"),

    -> (13212345678,"alan","alanpassword","first love","carry"),

    -> (13112345678,"peter","peterpassword","who is your father","jack");

2.所有字段批量插入卖家信息表数据

id	phone	open_date	name	nickname
S_20200703_00001	13812345678	2020-07-03	ledin	ledin
S_20201030_00001	18212345678	2020-10-30	hla	hla

insert into seller_zhanghaili values

    -> ("S_20200703_00001",13812345678,"2020-07-03","ledin","ledin"),

    -> ("S_20201030_00001",18212345678,"2020-10-30","hla","hla");

3.指定字段批量插入买家信息表数据

id	phone	nickname	height	weight
B_20200422_00001	13212345678	funny shop	168	52
B_20200911_00001	13112345678	cool girl	165	47

insert into buyer_zhanghaili

    -> (id,phone,nickname,height,weight) values

->  ("B_20200911_00001",13112345678,"cool girl",165,47)，

("B_20200422_00001",13212345678,"funny shop",168,52);

4.指定字段批量插入地址表数据

id	buyer_id	contact_phone	detail_address
A_20201103_00004	B_20200422_00001	13212345678	gray street
A_20201103_00005	B_20200422_00001	13212345678	funny street
A_20201103_00006	B_20200422_00001	13212345678	frank street
A_20201103_00007	B_20200911_00001	13112345678	rock street

insert into address_zhanghaili (id,buyer_id,contact_phone,detail_address)

    -> values ("A_20201103_00004","B_20200422_00001",13212345678,"gray street"),

    -> ("A_20201103_00005","B_20200422_00001",13212345678,"funny street"),

    -> ("A_20201103_00006","B_20200422_00001",3212345678,"frank street"),

    -> ("A_20201103_00007","B_20200911_00001",13112345678,"rock street");

5.所有字段批量插入产品种类表数据

code	name
T00001	coat
T00002	shirt
T00003	shorts
T00004	pants
T00005	jeans
T00006	polo

insert into product_type_zhanghaili values

    -> ("T00001","coat"),

    -> ("T00002","shirt"),

    -> ("T00003","shorts"),

    -> ("T00004","pants"),

    -> ("T00005","jeans"),

    -> ("T00006","polo");

6.指定字段插入产品表数据

id	seller_id	type_id	name	picture	unit_price
P_20190102_00001	S_20200703_00001	T00003	blue shorts	p123.jpg	168.8

 insert into product_zhanghaili (id,seller_id,type_id,name,picture,unit_price)

-> values ("P_20190102_00001","S_20200703_00001","T00003","blue shorts","p123.jpg","168.8");

7.所有字段插入产品表数据

id	seller_id	type_id	name	picture	unit_price	quantity
P_20190102_00002	S_20200703_00001	T00001	coat	coat1.jpg	62.2	43

insert into product_zhanghaili (id,seller_id,type_id,name,picture,unit_price,quantity)

    ->  values ("P_20190102_00002","S_20200703_00001","T00001","coat","coat1.jpg","62.6","43");

8.指定字段插入产品表数据

id	seller_id	type_id	name	unit_price
P_20190203_00001	S_20201030_00001	T00006	black polo	239.9

insert into product_zhanghaili(id,seller_id,type_id,name,unit_price)

    -> values ("P_20190203_00001","S_20201030_00001","T00006","black polo","239.9");

9.所有字段插入产品表数据

id	seller_id	type_id	name	picture	unit_price	quantity
P_20190203_00002	S_20201030_00001	T00005	jeans	12.jpg	198.8	23

insert into product_zhanghaili values ("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8","23");

10.查看产品表所有字段数据

select*from product_zhanghaili;

11.订单表指定字段插入数据

id	seller_id	buyer_id	address_id
O_20201102_00001	S_20200703_00001	B_20200422_00001	A_20201103_00004

insert into order_zhanghaili(id,seller_id,buyer_id,address_id)

    -> values ("O_20201102_00001","S_20200703_00001","B_20200422_00001","A_20201103_00004");

12.订单详情表指定字段插入数据

order_id	product_id	purchase_quantity	discount_unit_price
O_20201102_00001	P_20190102_00001	1	150
O_20201102_00001	P_20190102_00002	2	40

insert into order_detail_zhanghaili(order_id,product_id,purchase_quantity,discount_unit_price)

    -> values ("O_20201102_00001","P_20190102_00001",1,150),

->  ("O_20201102_00001","P_20190102_00002",2,40);

13.修改订单详情表中O_20201102_00001订单P_20190102_00002产品的采购数量为1

update order_detail_zhanghaili set purchase_quantity=1 where product_id="P_20190102_00002";

14.查看O_20201102_00001订单的订单编号、产品编号、库存数量、采购数量、采购后数量（库存数量-采购数量）、产品单价、折后单价

select a.order_id,a.product_id,b.quantity,a.purchase_quantity,b.quantity-a.purchase_quantity 采购数量,

    -> b.unit_price,a.discount_unit_price from order_detail_zhanghaili a inner join product_zhanghaili b on a.product_id=b.id;

15.修改产品表中库存数量为采购后数量

update product_zhanghaili set quantity = quantity - 1 where id=" P_20190102_00001" or id=" P_20190102_00002";

16.根据订单号分组查看订单号、订单总价（sum(采购数量*产品单价)）、实付款（sum(采购数量*折扣单价)）

select a.order_id,sum(a.purchase_quantity*b.unit_price) 订单总价,sum(a.purchase_quantity*a.discount_unit_price) 实付款

    -> from order_detail_zhanghaili a inner join product_zhanghaili b on a.product_id = b.id group by a.order_id;

17.根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据

update order_zhanghaili set total_price=231,actrual_payment=190 where id="O_20201102_00001";

18.查看O_20201102_00001订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格

select c.order_id,a.name,b.name,g.detail_address,e.nickname,c.purchase_quantity,c.discount_unit_price from

    -> seller_zhanghaili a inner join product_zhanghaili b on a.id=b.seller_id

    -> inner join order_detail_zhanghaili c on b.id=c.product_id

    -> inner join order_zhanghaili f on c.order_id=f.id

    -> inner join buyer_zhanghaili e on f.buyer_id=e.id

    -> inner join address_zhanghaili g on e.id=g.buyer_id;