目录
4.创建卖家信息表“seller_姓名全拼”,表中字段信息如下:
5.创建买家信息表“buyer_姓名全拼”,表中字段信息如下:
6.创建地址表“address_姓名全拼”,表中字段信息如下:
7.创建产品种类表“product_type_姓名全拼”,表中字段信息如下:
8.创建产品表“product_姓名全拼”,表中字段信息如下:
10.创建订单详情表“order_detail_姓名全拼”,表中字段信息如下:
1.user_zhongjinlin表所有字段批量插入用户表数据
2.seller_zhongjinlin表所有字段批量插入卖家信息表数据
3.buyer_zhongjinlin表指定字段批量插入买家信息表数据
4.address_zhongjinlin表指定字段批量插入地址表数据
5.product_type_zhongjinlin表所有字段批量插入产品种类表数据
6.product_zhongjinlin表指定字段插入产品表数据
7.product_zhongjinlin所有字段插入产品表数据
8.product_zhongjinlin表指定字段插入产品表数据
9.product_zhongjinlin表所有字段插入产品表数据
11.order_zhongjinlin表订单表指定字段插入数据
12.order_detail_zhongjinlin表订单详情表指定字段插入数据
13.修改订单详情表中O_20201102_00001订单P_20190102_00002产品的采购数量为1
14.查看O_20201102_00001订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
16.根据订单号分组查看订单号、订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
17.根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据
4.查看O_20201102_00002订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
6.根据订单号分组查看订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
7.根据上述代码计算出的值修改订单表中O_20201102_00002订单的总价、实付款数据
12.修改订单详情表中O_20201102_00002订单P_20190203_00002产品的折后单价为180
13.修改订单详情表中O_20201102_00002订单P_20190203_00001产品的折后单价为200
14.根据订单号分组查看实付款(sum(采购数量*折扣单价))
15.根据上述代码计算出的值修改订单表中O_20201102_00002订单的实付款数据
18.查看O_20201102_00002订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
2.创建买家信息视图“view_buyer_info_姓名全拼”查看上述内容
5.创建产品信息视图“view_product_ info_姓名全拼”查看上述内容
7.查看产品信息视图中已经有过订单销售记录的产品数据(子查询 in)
8.查看订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
9.创建订单信息视图“view_order_ info_姓名全拼”查看上述内容
1.备份所有数据库,文件名为“all_姓名全拼.sql”(截两张图 命令+文件)
2.备份电子商城数据库,文件名为“mall_姓名全拼.sql”(截两张图 命令+文件)
任务一、二
- 安装并配置MySQL
1.打开控制台
编辑
2.登录MySQL
编辑
- 数据库、表的基本操作
1.创建电子商城数据库“mall_姓名全拼”
create database mall_zhongjinlin;
编辑
2.使用电子商城数据库
use mall_zhongjinlin;
编辑
3.创建用户表“user_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
char |
11 |
主键 |
注册手机号 |
||
username |
varchar |
20 |
非空,唯一 |
用户名 |
|
password |
varchar |
20 |
非空 |
密码 |
|
question |
text |
非空 |
找回密码问题 |
||
answer |
text |
非空 |
找回密码问题答案 |
create table user_zhongjinlin(
-> phone char(11) primary key comment "注册手机号",
-> username varchar(20) not null unique comment "用户名",
-> password varchar(20) not null comment "密码",
-> question text not null comment "找回密码问题",
-> answer text not null comment "找回密码问题答案"
-> );
编辑
4.创建卖家信息表“seller_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
id |
char |
16 |
主键 |
卖家ID(S_DATE_XXXXX) |
|
phone |
char |
11 |
外键(user.phone) |
非空,唯一 |
注册手机号 |
open_date |
date |
非空 |
开业时间 |
||
name |
varchar |
50 |
非空 |
店铺名称 |
|
nickname |
varchar |
30 |
非空 |
掌柜昵称 |
create table seller_zhongjinlin(
-> id char(16) primary key comment "卖家ID(S_DATE_XXXXX)",
-> phone char(11) not null unique comment "注册手机号",
-> open_date date not null comment "开业时间",
-> name varchar(50) not null comment "店铺名称",
-> nickname varchar(30) not null comment "掌柜昵称",
-> constraint fk_phone foreign key(phone) references user_zhongjinlin(phone)
-> );
编辑
5.创建买家信息表“buyer_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
id |
char |
16 |
主键 |
买家ID(B_DATE_XXXXX) |
|
phone |
char |
11 |
外键(user.phone) |
非空,唯一 |
注册手机号 |
nickname |
varchar |
30 |
非空 |
买家昵称 |
|
gender |
enum(“miss”,”mr”) |
默认miss |
性别 |
||
height |
int |
3 |
身高cm |
||
weight |
double |
体重kg |
create table buyer_zhongjinlin(
-> id char(16) primary key comment "卖家ID(S_DATE_XXXXX)",
-> phone char(11) not null unique comment "注册手机号",
-> nickname varchar(30) not null comment "买家昵称",
-> gender enum("miss","mr") default "miss" comment "性别",
-> height int(3) comment "身高cm",
-> weight double comment "体重kg",
-> constraint fk_phone2 foreign key(phone) references user_zhongjinlin(phone)
-> );
编辑
6.创建地址表“address_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
id |
char |
16 |
主键 |
地址ID (A_DATE_XXXXX) |
|
buyer_id |
char |
16 |
外键(buyer.id) |
非空 |
买家ID |
contact_phone |
char |
11 |
非空 |
收货人联系方式 |
|
detail_address |
text |
非空 |
详细地址 |
||
is_default |
enum(“yes”,”no”) |
默认 no |
是否默认地址 |
create table address_zhongjinlin(
-> id char(16) primary key comment "地址ID (A_DATE_XXXXX)",
-> buyer_id char(16) not null comment "买家ID",
-> contact_phone char(11) not null comment "收货人联系方式",
-> detail_address text not null comment "详细地址",
-> is_default enum("yes","no") default "no" comment "是否默认地址",
-> constraint fk_buyer_id foreign key(buyer_id) references buyer_zhongjinlin(id)
-> );
编辑
7.创建产品种类表“product_type_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
code |
char |
6 |
主键 |
产品种类编码(TXXXXX) |
|
name |
varchar |
30 |
非空 |
产品种类名称 |
create table product_type_zhongjinlin(
-> code char(6) primary key comment "产品种类编码(TXXXXX)",
-> name varchar(30) not null comment "产品种类名称"
-> );
编辑
8.创建产品表“product_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
id |
char |
16 |
主键 |
产品编号(P_DATE_XXXXX) |
|
seller_id |
char |
16 |
外键(seller.id) |
非空 |
卖家ID |
type_id |
char |
6 |
外键(product_type.code) |
非空 |
产品种类编码 |
name |
varchar |
100 |
非空 |
产品名称 |
|
picture |
text |
产品展示图 |
|||
unit_price |
double |
非空 |
单价 |
||
quantity |
int |
10 |
默认 100 |
库存数量 |
create table product_zhongjinlin(
-> id char(16) primary key comment "产品编号(P_DATE_XXXXX)",
-> seller_id char(16) not null comment "卖家ID",
-> type_id char(6) not null comment "产品种类编码",
-> name varchar(100) not null comment "产品名称",
-> picture text comment "产品展示图",
-> unit_price double not null comment "单价",
-> quantity int(10) default "100" comment "库存数量",
-> constraint fk_seller_id foreign key(seller_id) references seller_zhongjinlin(id),
-> constraint fk_type_id foreign key(type_id) references product_type_zhongjinlin(code)
-> );
编辑
9.创建订单表“order_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
id |
char |
16 |
主键 |
订单编号(O_DATE_XXXXX) |
|
seller_id |
char |
16 |
外键(seller.id) |
非空 |
卖家ID |
buyer_id |
char |
16 |
外键(buyer.id) |
非空 |
买家ID |
address_id |
char |
16 |
外键(address.id) |
非空 |
地址ID |
total_price |
double |
默认0 |
总价 |
||
actrual_payment |
double |
默认0 |
实付款 |
create table order_zhongjinlin(
-> id char(16) primary key comment "订单编号(O_DATE_XXXXX)",
-> seller_id char(16) not null comment "卖家ID",
-> buyer_id char(16) not null comment "买家ID",
-> address_id char(16) not null comment "地址ID",
-> total_price double default "0" comment "总价",
-> actrual_payment double default "0" comment "实付款",
-> constraint fk_seller_id2 foreign key(seller_id) references seller_zhongjinlin(id),
-> constraint fk_buyer_id2 foreign key(buyer_id) references buyer_zhongjinlin(id),
-> constraint fk_address_id foreign key(address_id) references address_zhongjinlin(id)
-> );
编辑
10.创建订单详情表“order_detail_姓名全拼”,表中字段信息如下:
字段名 |
数据类型 |
长度 |
主、外键 |
其他约束 |
备注信息 |
id |
int |
10 |
主键 |
自增 |
|
order_id |
char |
16 |
外键(order.id) |
非空 |
订单编号 |
product_id |
char |
16 |
外键(product.id) |
非空 |
产品编号 |
purchase_quantity |
int |
3 |
默认1 |
采购数量 |
|
discount_unit_price |
double |
非空 |
产品折后价 |
create table order_detail_zhongjinlin(
-> id int(10) primary key auto_increment,
-> order_id char(16) not null comment "订单编号",
-> product_id char(16) not null comment "产品编号",
-> purchase_quantity int(3) default"1" comment "采购数量",
-> discount_unit_price double not null comment "产品折后价",
-> constraint fk_order_id foreign key(order_id) references order_zhongjinlin(id),
-> constraint fk_product_id foreign key(product_id) references product_zhongjinlin(id)
-> );
编辑
任务三 对表中数据进行基本操作
1.user_zhongjinlin表所有字段批量插入用户表数据
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_zhongjinlin 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.seller_zhongjinlin表所有字段批量插入卖家信息表数据
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_zhongjinlin values
-> ("S_20200703_00001",13812345678,"2020-07-03","ledin","ledin"),
-> ("S_20201030_00001",18212345678,"2020-10-30","hal","hal");
编辑
3.buyer_zhongjinlin表指定字段批量插入买家信息表数据
phone |
nickname |
height |
weight |
|
B_20200422_00001 |
13212345678 |
funny shop |
168 |
52 |
B_20200911_00001 |
13112345678 |
cool girl |
165 |
47 |
insert into buyer_zhongjinlin (id,phone,nickname,height,weight) values
-> ("B_20200422_00001",13212345678,"funny shop",168,52),
-> ("B_20200911_00001",13112345678,"cool girl",165,47);
编辑
4.address_zhongjinlin表指定字段批量插入地址表数据
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_zhongjinlin (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.product_type_zhongjinlin表所有字段批量插入产品种类表数据
name |
|
T00001 |
coat |
T00002 |
shirt |
T00003 |
shorts |
T00004 |
pants |
T00005 |
jeans |
T00006 |
polo |
insert into product_type_zhongjinlin values
-> ("T00001","coat"),
-> ("T00002","shirt"),
-> ("T00003","shorts"),
-> ("T00004","pants"),
-> ("T00005","jeans"),
-> ("T00006","polo");
编辑
6.product_zhongjinlin表指定字段插入产品表数据
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_zhongjinlin (id,seller_id,type_id,name,picture,unit_price) values
-> ("P_20190102_00001","S_20200703_00001","T00003","blue shorts","p123.jpg","168.8");
编辑
7.product_zhongjinlin所有字段插入产品表数据
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_zhongjinlin values
-> ("P_20190102_00002","S_20200703_00001","T00001","coat","coat1.jpg",62.2,43);
编辑
8.product_zhongjinlin表指定字段插入产品表数据
id |
seller_id |
type_id |
name |
unit_price |
P_20190203_00001 |
S_20201030_00001 |
T00006 |
black polo |
239.9 |
insert into product_zhongjinlin (id,seller_id,type_id,name,unit_price) values
-> ("P_20190203_00001","S_20201030_00001","T00006","black polo","239.9");
编辑
9.product_zhongjinlin表所有字段插入产品表数据
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_zhongjinlin values
-> ("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8",23);
编辑
10.查看产品表所有字段数据
select * from product_zhongjinlin;
编辑
11.order_zhongjinlin表订单表指定字段插入数据
id |
seller_id |
buyer_id |
address_id |
O_20201102_00001 |
S_20200703_00001 |
B_20200422_00001 |
A_20201103_00004 |
insert into order_zhongjinlin (id,seller_id,buyer_id,address_id) values
-> ("O_20201102_00001","S_20200703_00001","B_20200422_00001","A_20201103_00004");
编辑
12.order_detail_zhongjinlin表订单详情表指定字段插入数据
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_zhongjinlin (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_zhongjinlin set purchase_quantity=1
-> where product_id="P_20190102_00002";
编辑
14.查看O_20201102_00001订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
select a.order_id,a.product_id,a.purchase_quantity,a.discount_unit_price,
-> b.quantity-a.purchase_quantity,b.quantity,b.unit_price from order_detail_zhongjinlin a
-> inner join product_zhongjinlin b on a.product_id=b.id where a.order_id="O_20201102_00001";
编辑
15.修改产品表中库存数量为采购后数量
update product_zhongjinlin set quantity=99 where unit_price=168.8;
update product_zhongjinlin set quantity=42 where unit_price=62.2;
编辑
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_zhongjinlin a inner join product_zhongjinlin b on a.product_id=b.id
-> group by a.order_id;
编辑
17.根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据
update order_zhongjinlin set total_price=231,actrual_payment=190 where id="O_20201102_00001";
编辑
18.查看O_20201102_00001订单的订单编号id表“order_姓名全拼”、采购数量purchase_quantity表“order_detail_姓名全拼”、折后价格discount_unit_price表“order_detail_姓名全拼” 、店铺名称name表“seller_姓名全拼”、买家昵称nickname表“buyer_姓名全拼”、详细地址detail_address表“address_姓名全拼”、产品名称name表“product_姓名全拼”
select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_zhongjinlin c inner join product_zhongjinlin f on c.id=f.seller_id
-> inner join order_detail_zhongjinlin b on f.id=b.product_id
-> inner join order_zhongjinlin a on b.order_id=a.id
-> inner join address_zhongjinlin e on a.address_id=e.id
-> inner join buyer_zhongjinlin d on e.contact_phone=d.phone;
编辑
任务四、使用事务操作表中数据
1.开启事务
start transaction;
编辑
2.订单表指定字段插入数据
id |
seller_id |
buyer_id |
address_id |
O_20201102_00002 |
S_20201030_00001 |
B_20200911_00001 |
A_20201103_00007 |
insert into order_zhongjinlin (id,seller_id,buyer_id,address_id) values
-> ("O_20201102_00002","S_20201030_00001","B_20200911_00001","A_20201103_00007");
编辑
3.订单详情表指定字段插入数据
order_id |
product_id |
purchase_quantity |
discount_unit_price |
O_20201102_00002 |
P_20190203_00001 |
1 |
230 |
O_20201102_00002 |
P_20190203_00002 |
2 |
190 |
insert into order_detail_zhongjinlin (order_id,product_id,purchase_quantity,discount_unit_price) values
-> ("O_20201102_00002","P_20190203_00001",1,230),
-> ("O_20201102_00002","P_20190203_00001",2,190);
编辑
4.查看O_20201102_00002订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
select a.order_id,a.product_id,a.purchase_quantity,a.discount_unit_price,
-> b.quantity-a.purchase_quantity,b.quantity,b.unit_price from order_detail_zhongjinlin a
-> inner join product_zhongjinlin b on a.product_id=b.id where a.order_id="O_20201102_00002";
编辑
5.修改产品表中库存数量为采购后数量
update product_zhongjinlin set quantity=99 where unit_price=239.9;
update product_zhongjinlin set quantity=21 where unit_price=198.8;
编辑
6.根据订单号分组查看订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
select a.order_id,sum(a.purchase_quantity*b.unit_price),sum(a.purchase_quantity*a.discount_unit_price)
-> from order_detail_zhongjinlin a inner join product_zhongjinlin b on a.product_id=b.id
-> group by a.order_id;
编辑
7.根据上述代码计算出的值修改订单表中O_20201102_00002订单的总价、实付款数据
update order_zhongjinlin set total_price=231,actrual_payment=719.7 where id="O_20201102_00002";
编辑
8.查看订单表所有字段数据
select * from order_zhongjinlin ;
编辑
9.查看订单详情表所有字段数据
select * from order_detail_zhongjinlin ;
编辑
10.提交事务
commit;
编辑
11.开启事务
start transaction;
编辑
12.修改订单详情表中O_20201102_00002订单P_20190203_00002产品的折后单价为180
update order_detail_zhongjinlin set discount_unit_price=180 where order_id="O_20201102_00002" and product_id="P_20190203_00002";
编辑
13.修改订单详情表中O_20201102_00002订单P_20190203_00001产品的折后单价为200
update order_detail_zhongjinlin set discount_unit_price=200 where order_id="O_20201102_00002" and product_id="P_20190203_00001";
编辑
14.根据订单号分组查看实付款(sum(采购数量*折扣单价))
select sum(a.purchase_quantity*a.discount_unit_price) from
-> order_detail_zhongjinlin a inner join product_zhongjinlin b on a.product_id=b.id
-> group by a.order_id;
编辑
15.根据上述代码计算出的值修改订单表中O_20201102_00002订单的实付款数据
update order_zhongjinlin set actrual_payment=560 where id="O_20201102_00002";
编辑
16.查看订单详情表所有字段数据
select * from order_detail_zhongjinlin;
编辑
17.回滚事务
rollback;
编辑
18.查看O_20201102_00002订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_zhongjinlin c inner join product_zhongjinlin f on c.id=f.seller_id
-> inner join order_detail_zhongjinlin b on f.id=b.product_id
-> inner join order_zhongjinlin a on b.order_id=a.id
-> inner join address_zhongjinlin e on a.address_id=e.id
-> inner join buyer_zhongjinlin d on e.contact_phone=d.phone
-> where b.order_id = "O_20201102_00002";
编辑
任务五、创建并使用视图
1.查看买家昵称、性别、联系方式、详细地址、是否默认地址
select a.nickname,a.gender,b.contact_phone,b.detail_address,b.is_default from address_zhongjinlin b inner join buyer_zhongjinlin a on b.buyer_id=a.id;
编辑
2.创建买家信息视图“view_buyer_info_姓名全拼”查看上述内容
create view view_buyer_info_zhongjinlin as select a.nickname,a.gender,b.contact_phone,b.detail_address,b.is_default from
-> address_zhongjinlin b inner join buyer_zhongjinlin a on b.buyer_id=a.id;
编辑
3.查看买家信息视图买家昵称含有“h”的数据
select * from view_buyer_info_zhongjinlin where nickname like "%h%";
编辑
4.查看产品种类编码、产品种类名称、产品名称、单价、库存
select a.code,a.name,b.id,b.unit_price,b.quantity from product_type_zhongjinlin a inner join product_zhongjinlin b on a.code=b.type_id;
编辑
5.创建产品信息视图“view_product_ info_姓名全拼”查看上述内容
create view view_product_info_zhongjinlin as select a.code,a.name,b.id,b.unit_price,b.quantity from
-> product_type_zhongjinlin a inner join
-> product_zhongjinlin b on a.code=b.type_id;
编辑
6.查看订单详情表中的所有产品名称
select b.name from order_detail_zhongjinlin a inner join product_zhongjinlin b on a.product_id=b.id;
编辑
7.查看产品信息视图中已经有过订单销售记录的产品数据(子查询 in)
select * from product_zhongjinlin where
-> type_id in (select code from view_product_info_zhongjinlin);
编辑
8.查看订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_zhongjinlin c inner join product_zhongjinlin f on c.id=f.seller_id
-> inner join order_detail_zhongjinlin b on f.id=b.product_id
-> inner join order_zhongjinlin a on b.order_id=a.id
-> inner join address_zhongjinlin e on a.address_id=e.id
-> inner join buyer_zhongjinlin d on e.contact_phone=d.phone
编辑
9.创建订单信息视图“view_order_ info_姓名全拼”查看上述内容
create view view_order_info_zhongjinlin as select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name a from seller_zhongjinlin c inner join product_zhongjinlin f on c.id=f.seller_id
-> inner join order_detail_zhongjinlin b on f.id=b.product_id
-> inner join order_zhongjinlin a on b.order_id=a.id
-> inner join address_zhongjinlin e on a.address_id=e.id
-> inner join buyer_zhongjinlin d on e.contact_phone=d.phone
-> ;
编辑
10.查看订单信息视图中采购数量不为1的数据
select * from view_order_info_zhongjinlin where purchase_quantity!=1 ;
编辑
任务六、备份数据库
1.备份所有数据库,文件名为“all_姓名全拼.sql”(截两张图 命令+文件)
\software\mysql-8.0.28-winx64\mysql-8.0.28-winx64\biefensjk>mysqldump -uroot -prain --all-databases>all_zhongjinlin.sql
编辑
编辑
2.备份电子商城数据库,文件名为“mall_姓名全拼.sql”(截两张图 命令+文件)
D:\software\mysql-8.0.28-winx64\mysql-8.0.28-winx64\biefensjk>mysqldump -uroot -prain mall_zhongjinlin>mall_zhongjinlin.sql
编辑
编辑
3.删除电子商城数据库
drop database mall_zhongjinlin;
编辑
4.创建电子商城数据库“mall_姓名全拼”
create database mall_zhongjinlin;
编辑
5.退出MySQL登录
\q
编辑
6.执行电子商城数据库备份文件
D:\software\mysql-8.0.28-winx64\mysql-8.0.28-winx64\biefensjk>mysql -u root -prain mall_zhongjinlin<mall_zhongjinlin.sql
编辑
7.登录MySQL
mysql -u用户名 -p密码
编辑
8.使用电子商城数据库
use mall_zhongjinlin;
编辑
9.查看所有表
show tables;
编辑