登录Mysql
创建电子商城数据库“mall_姓名全拼”
使用电子商城数据库
任务三、对表中数据进行基本操作
1.所有字段批量插入用户表数据
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 |
2.所有字段批量插入卖家信息表数据
phone | open_date | name | nickname | |
S_20200703_00001 | 13812345678 | 2020-07-03 | ledin | ledin |
S_20201030_00001 | 18212345678 | 2020-10-30 | hla | hla |
3.指定字段批量插入买家信息表数据
phone | nickname | height | weight | |
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 |
5.所有字段批量插入产品种类表数据
name | |
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 |
7.所有字段插入产品表数据
seller_id | type_id | name | picture | unit_price | quantity | |
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 |
9.所有字段插入产品表数据
id | seller_id | type_id | name | picture | unit_price | quantity |
P_20190203_00002 | S_20201030_00001 | T00005 | jeans | 12.jpg | 198.8 | 23 |
10.查看产品表所有字段数据
11.订单表指定字段插入数据
id | seller_id | buyer_id | address_id |
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 |
13.修改订单详情表中O_20201102_00001订单P_20190102_00002产品的采购数量为1
14.查看O_20201102_00001订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
15.修改产品表中库存数量为采购后数量
16.根据订单号分组查看订单号、订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
17.根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据查看O_20201102_00001订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格