所有字段批量插入用户表数据
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_qiufaqing values(
-> '13812345678','anne','annnepassword','favorite book','harry potter'),
-> ('18212345678','frank','Frankpassword','Favorite song','lonely'),
-> ('13212345678','alan','Alanpassword','First love','carry'),
-> ('13112345678','pete','Peterpassword','Who is your father','jack');
所有字段批量插入卖家信息表数据
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_qiufaqing values
-> ('S_20200703_00001','13812345678','2020-07-03','ledin','ledin'),
-> ('S_20201030_00001','18212345678','2020-10-30','hla','hla');
指定字段批量插入买家信息表数据
phone | nickname | height | weight | |
B_20200422_00001 | 13212345678 | funny shop | 168 | 52 |
B_20200911_00001 | 13112345678 | cool girl | 165 | 47 |
into buyer_qiufaqing
-> (id,phone,nickname,height,weight)
-> values
-> ('B_20200422_00001','13212345678','funny shop','168','52'),
-> ('B_20200911_00001','13112345678','cool girl','165','47');
指定字段批量插入地址表数据
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_qiufaqing(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');
所有字段批量插入产品种类表数据
name | |
T00001 | coat |
T00002 | shirt |
T00003 | shorts |
T00004 | pants |
T00005 | jeans |
T00006 | polo |
insert into product_type_qiufaqing values
-> ('T00001','coat'),
-> ('T00002','shirt'),
-> ('T00003','shorts'),
-> ('T00004','pants'),
-> ('T00005','jeans'),
-> ('T00006','polo');
指定字段插入产品表数据
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_qiufaqing(id,seller_id,type_id,name,picture,unit_price) values
('P_20190102_00001','S_20200703_00001','T00003','blue shorts','p123.jpg','168.8');
所有字段插入产品表数据
seller_id | type_id | name | picture | unit_price | quantity | |
P_20190102_00002 | S_20200703_00001 | T00001 | coat | coat1.jpg | 62.2 | 43 |
nsert into product_qiufaqing values
insert
into product_qiufaqingvalues
('P_20190102_00002','S_20200703_00001','T00001','coat','coat1.jpg','62.2','43');
指定字段插入产品表数据
id | seller_id | type_id | name | unit_price |
P_20190203_00001 | S_20201030_00001 | T00006 | black polo | 239.9 |
insert into product_qiufaqing(id,seller_id,type_id,name,unit_price) values
-> ('P_20190203_00001','S_20201030_00001','T00006','black polo','239.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_qiufaqing values
insert into
product_qiufaqing vlaues
('P_20190203_00002','S_20201030_00001','T00005','jeans','12.jpg','198.8','23');
查看产品表所有字段数据
订单表指定字段插入数据
id | seller_id | buyer_id | address_id |
O_20201102_00001 | S_20200703_00001 | B_20200422_00001 | A_20201103_00004 |
insert into order_qiufaqing(id,seller_id,buyer_id,address_id) values
('O_20201102_00001','S_20200703_00001','B_20200422_00001','A_20201103_00004');
订单详情表指定字段插入数据
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_qiufaqing(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');
修改订单详情表中O_20201102_00001订单P_20190102_00002产品的采购数量为1
update order_detail_qiufaqing set purchase_quantity = '1' where product_id = 'P_20190102_00002';
查看O_20201102_00001订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
select b.order_id,b.product_id,a.quantity,b.purchase_quantity,a.quantity-b.purchase_quantity,a.unit_price,b.discount_unit_price from
-> product_qiufaqing a inner join order_detail_qiufaqing b
-> on a.id = b.product_id where b.order_id = 'O_20201102_00001';
修改产品表中库存数量为采购后数量
update product_qiufaqing set quantity = "99" where type_id = "t00003";
update product_qiufaqing set quantity = "42" where type_id = "t00001";
根据订单号分组查看订单号、订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
select a.order_id,sum(a.purchase_quantity*b.unit_price),sum(a.purchase_quantity*a.discount_unit_price) from
-> product_qiufaqing b inner join order_detail_qiufaqing a
-> on b.id = a.product_id
-> group by a.order_id;
根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据
update order_qiufaqing set total_price = '231',
-> actrual_payment = '190' where id = 'O_20201102_00001';
查看O_20201102_00001订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
select a.order_id,b.seller_id,b.buyer_id,b.address_id,c.name,a.purchase_quantity,a.discount_unit_price from
-> order_detail_qiufaqing a inner join product_qiufaqing c on
-> c.id = a.product_id
-> inner join order_qiufaqing b on
-> b.id = a.order_id
-> where a.order_id = 'O_20201102_00001';