任务一:安装并配置MySQL
1.打开控制台
2.登录MySQL
任务二:数据库、表的基本操作
1.创建电子商城数据库“mall_姓名全拼”
create database mall_chenzhiyuan;
2.使用电子商城数据库
3.创建用户表“user_姓名全拼”,表中字段信息如下:
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
char | 11 | 主键 | 注册手机号 | ||
username | varchar | 20 | 非空,唯一 | 用户名 | |
password | varchar | 20 | 非空 | 密码 | |
question | text | 非空 | 找回密码问题 | ||
answer | text | 非空 | 找回密码问题答案 |
create table user_chenzhiyuan(
-> 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_chenzhiyuan(
-> 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_seller_chenzhiyuan_phone foreign key(phone) references user_chenzhiyuan(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_chenzhiyuan(
-> id char(16) primary key comment"买家ID(B_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_buyer_chenzhiyuan_phone foreign key(phone) references user_chenzhiyuan(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_chenzhiyuan(
-> 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_address_chenzhiyuan_buyer_id foreign key(buyer_id) references buyer_chenzhiyuan(id)
-> );
7.创建产品种类表“product_type_姓名全拼”,表中字段信息如下:
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
code | char | 6 | 主键 | 产品种类编码(TXXXXX) | |
name | varchar | 30 | 非空 | 产品种类名称 |
create table product_type_chenzhiyuan(
-> 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_chenzhiyuan(
-> 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_product_chenzhiyuan_seller_id foreign key(seller_id) references seller_chenzhiyuan(id),
-> constraint fk_product_chenzhiyuan_type_id foreign key(type_id) references product_type_chenzhiyuan(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_chenzhiyuan(
-> 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_order_chenzhiyuan_seller_id foreign key(seller_id) references seller_chenzhiyuan(id),
-> constraint fk_order_chenzhiyuan_buyer_id foreign key(buyer_id) references buyer_chenzhiyuan(id),
-> constraint fk_order_chenzhiyuan_address_id foreign key(address_id) references address_chenzhiyuan(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_chenzhiyuan(
-> 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_detail_chenzhiyuan_order_id foreign key(order_id) references order_chenzhiyuan(id),
-> constraint fk_order_detail_chenzhiyuan_product_id foreign key(product_id) references product_chenzhiyuan(id)
-> );
任务三:对表中数据进行基本操作
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_chenzhiyuan 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_chenzhiyuan values
-> ("S_20200703_00001",13812345678,"2020-07-03","ledin","ledin"),
-> ("S_20201030_00001",18212345678,"2020-10-30","hal","hal");
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_chenzhiyuan (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_chenzhiyuan (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.所有字段批量插入产品种类表数据
code | name |
T00001 | coat |
T00002 | shirt |
T00003 | shorts |
T00004 | pants |
T00005 | jeans |
T00006 | polo |
insert into product_type_chenzhiyuan 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_chenzhiyuan (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_chenzhiyuan 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_chenzhiyuan (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_chenzhiyuan values
-> ("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8",23);
10.查看产品表所有字段数据
select * from product_chenzhiyuan
11.订单表指定字段插入数据
id | seller_id | buyer_id | address_id |
O_20201102_00001 | S_20200703_00001 | B_20200422_00001 | A_20201103_00004 |
insert into order_chenzhiyuan (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_chenzhiyuan (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_chenzhiyuan 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_chenzhiyuan a
->inner join product_chenzhiyuan b on a.product_id=b.id
->where a.order_id="O_20201102_00001";
15.修改产品表中库存数量为采购后数量
update product_chenzhiyuan set quantity=99 where unit_price=168.8;
update product_chenzhiyuan 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_chenzhiyuan a inner join product_chenzhiyuan b on a.product_id=b.id
group by a.order_id;
17.根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据
update order_chenzhiyuan set total_price=231,actrual_payment=190 where id="O_20201102_00001";
18.查看O_20201102_00001订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_chenzhiyuan c inner join product_chenzhiyuan f on c.id=f.seller_id
-> inner join order_detail_chenzhiyuan b on f.id=b.product_id
-> inner join order_chenzhiyuan a on b.order_id=a.id
-> inner join address_chenzhiyuan e on a.address_id=e.id
-> inner join buyer_chenzhiyuan 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_chenzhiyuan (id,seller_id,buyer_id,address_id) values
-> ("O_20201102_00002","S_20201030_00001","B_20200911_00001","A_20201103_00007");
3.订单详情表指定字段插入数据
insert into order_detail_chenzhiyuan (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_chenzhiyuan a
-> inner join product_chenzhiyuan b on a.product_id=b.id where a.order_id="O_20201102_00002";
5.修改产品表中库存数量为采购后数量
update product_chenzhiyuan set quantity=99 where unit_price=239.9;
update product_chenzhiyuan 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_chenzhiyuan a inner join product_chenzhiyuan b on a.product_id=b.id
-> group by a.order_id;
7.根据上述代码计算出的值修改订单表中O_20201102_00002订单的总价、实付款数据
update order_chenzhiyuan set total_price=231,actrual_payment=719.7 where id="O_20201102_00002";
8.查看订单表所有字段数据
select * from order_chenzhiyuan ;
9.查看订单详情表所有字段数据
select * from order_detail_chenzhiyuan ;
10.提交事务
commit;
11.开启事务
start transaction;
12.修改订单详情表中O_20201102_00002订单P_20190203_00002产品的折后单价为180
update order_detail_chenzhiyuan 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_chenzhiyuan 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_chenzhiyuan a inner join product_chenzhiyuan b on a.product_id=b.id
-> group by a.order_id;
15.根据上述代码计算出的值修改订单表中O_20201102_00002订单的实付款数据
update order_chenzhiyuan set actrual_payment=560 where id="O_20201102_00002";
16.查看订单详情表所有字段数据
select * from order_detail_chenzhiyuan;
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_chenzhiyuan c inner join product_chenzhiyuan f on c.id=f.seller_id
-> inner join order_detail_chenzhiyuan b on f.id=b.product_id
-> inner join order_chenzhiyuan a on b.order_id=a.id
-> inner join address_chenzhiyuan e on a.address_id=e.id
-> inner join buyer_chenzhiyuan 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_chenzhiyuan b inner join buyer_chenzhiyuan a on b.buyer_id=a.id;
2.创建买家信息视图“view_buyer_info_姓名全拼”查看上述内容
create view view_buyer_info_chenzhiyuan as select a.nickname,a.gender,b.contact_phone,b.detail_address,b.is_default from
-> address_chenzhiyuan b inner join buyer_chenzhiyuan a
-> on b.buyer_id=a.id;
3.查看买家信息视图买家昵称含有“h”的数据
select * from view_buyer_info_chenzhiyuan where nickname like "%h%";
4.查看产品种类编码、产品种类名称、产品名称、单价、库存
select a.code,a.name,b.id,b.unit_price,b.quantity from product_type_chenzhiyuan a inner join product_chenzhiyuan b on a.code=b.type_id;
5.创建产品信息视图“view_product_ info_姓名全拼”查看上述内容
create view view_product_info_chenzhiyuan as select a.code,a.name,b.id,b.unit_price,b.quantity from
product_type_chenzhiyuan a inner join
product_chenzhiyuan b on a.code=b.type_id;
6.查看订单详情表中的所有产品名称
select b.name from order_detail_chenzhiyuan a inner join product_chenzhiyuan b on a.product_id=b.id;
7.查看产品信息视图中已经有过订单销售记录的产品数据(子查询 in)
select * from product_chenzhiyuan where
type_id in (select code from view_product_info_chenzhiyuan);
8.查看订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_chenzhiyuan c inner join product_chenzhiyuan f on c.id=f.seller_id
inner join order_detail_chenzhiyuan b on f.id=b.product_id
inner join order_chenzhiyuan a on b.order_id=a.id
inner join address_chenzhiyuan e on a.address_id=e.id
inner join buyer_chenzhiyuan d on e.contact_phone=d.phone;
9.创建订单信息视图“view_order_ info_姓名全拼”查看上述内容
create view view_order_info_chenzhiyuan as select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name a from seller_chenzhiyuan c inner join product_chenzhiyuan f on c.id=f.seller_id
inner join order_detail_chenzhiyuan b on f.id=b.product_id
inner join order_chenzhiyuan a on b.order_id=a.id
inner join address_chenzhiyuan e on a.address_id=e.id
inner join buyer_chenzhiyuan d on e.contact_phone=d.phone;
10.查看订单信息视图中采购数量不为1的数据
select * from view_order_info_chenzhiyuan where purchase_quantity!=1 ;
任务六:备份数据库
1.备份所有数据库,文件名为“all_姓名全拼.sql”(截两张图 命令+文件)
2.备份电子商城数据库,文件名为“mall_姓名全拼.sql”(截两张图 命令+文件)
3.删除电子商城数据库
drop database mall_chenzhiyuan;
4.创建电子商城数据库“mall_姓名全拼”
create database mall_chenzhiyuan;
5.退出MySQL登录
6.执行电子商城数据库备份文件
7.登录MySQL
8.使用电子商城数据库
9.查看所有表