任务三 对表中数据进行基本操作
1.user_77表所有字段批量插入用户表数据
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_77 values
-> (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");
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_77 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_77(id,phone,nickname,height,weight) values
-> ("B_20200422_00001","13212345678","funny shop","168","52"),
-> ("B_20200911_00001","13112345678","cool girl","165","47");
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_77 (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","13212345678","frank street"),
-> ("A_20201103_00007","B_20200911_00001","13112345678","rock street");
5.所有字段批量插入产品种类表数据
name | |
T00001 | coat |
T00002 | shirt |
T00003 | shorts |
T00004 | pants |
T00005 | jeans |
T00006 | polo |
insert into product_type_77 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_77(id,seller_id,type_id,name,picture,unit_price) values
-> ("P_20190102_00001","S_20200703_00001","T00003","blue shorts","p123.jpg","168.8");
7.所有字段插入产品表数据
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_77 values
-> ("P_20190102_00002","S_20200703_00001","T00001","coat","coat1.jpg","62.2","43");
8.指定字段插入产品表数据
id | seller_id | type_id | name | unit_price |
P_20190203_00001 | S_20201030_00001 | T00006 | black polo | 239.9 |
insert into product_77(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_77 values
-> ("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8","23");
10.查看产品表所有字段数据
select * from product_77;
11.订单表指定字段插入数据
id | seller_id | buyer_id | address_id |
O_20201102_00001 | S_20200703_00001 | B_20200422_00001 | A_20201103_00004 |
insert into order_77(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_77(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_77 set
-> purchase_quantity=1 where id=2;
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_77 a inner join product_77 b on a.product_id = b.id;
15.修改产品表中库存数量为采购后数量
update product_77 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_77 a inner join product_77 b on a.product_id = b.id group by a.order_id;
17.根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据
update order_77 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_77 a inner join product_77 b on a.id = b.seller_id
-> inner join order_detail_77 c on b.id = c.product_id
-> inner join order_77 f on c.order_id = f.id
-> inner join buyer_77 e on f.buyer_id = e.id
-> inner join address_77 g on e.id = g.buyer_id;