-
安装并配置MySQL
- 打开控制台
- 登录MySQL
-
数据库、表的基本操作
- 创建电子商城数据库“mall_姓名全拼”
- 使用电子商城数据库
- 创建用户表“user_姓名全拼”,表中字段信息如下:
代码:
create table user_chengweiqiang(
phone char(11) comment"注册手机号" primary key,
-> username varchar(20) comment"用户名" not null unique,
-> password varchar(20) comment"密码" not null,
-> question text comment"找回密码问题" not null,
-> answer text comment"找回密码问题答案" not null);
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
char | 11 | 主键 | 注册手机号 | ||
username | varchar | 20 | 非空,唯一 | 用户名 | |
password | varchar | 20 | 非空 | 密码 | |
question | text | 非空 | 找回密码问题 | ||
answer | text | 非空 | 找回密码问题答案 |
- 创建卖家信息表“seller_姓名全拼”,表中字段信息如下:
-
create table seller_chengweiqiang( -> id char(16) comment"卖家id" primary key, -> phone char(11) comment"注册手机号" not null unique, -> open_date date comment"开业时间" not null, -> name varchar(50) comment"店铺名称" not null, -> nickname varchar(30) comment"掌柜名称" not null, -> constraint fk_phone foreign key(phone) references user_chengweiqiang(phone));
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
id | char | 16 | 主键 | 卖家ID(S_DATE_XXXXX) | |
phone | char | 11 | 外键(user.phone) | 非空,唯一 | 注册手机号 |
open_date | date | 非空 | 开业时间 | ||
name | varchar | 50 | 非空 | 店铺名称 | |
nickname | varchar | 30 | 非空 | 掌柜昵称 |
- 创建买家信息表“buyer_姓名全拼”,表中字段信息如下:
-
create table buyer_chengweiqiang( -> id char(16) comment"买家ID(B_DATE_XXXX" primary key , -> phone char(11) comment"注册手机号" not null unique, -> nickname varchar(30) comment"买家昵称" not null, -> gender enum("miss","mr") comment"性别" default"miss", -> height int(3) comment"身高", -> weight double comment"体重", -> constraint fk1_phone foreign key(phone) references user_chengweiqiang(phone));
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
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 |
- 创建地址表“address_姓名全拼”,表中字段信息如下:
create table address_chengweiqiang(
-> id char(16) comment"地址" primary key,
-> buyer_id char(16) comment"买家id" not null, ,
-> contact_phone char(11) comment"收货人联系方式" not null ,
-> detail_address text comment"详细地址" not null,
-> is_default enum("yes","no") comment"是 否默认地址" default"no",
-> constraint fk2_buyer_id foreign key(buyer_id) references buyer_chengweiqiang(id));
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
id | char | 16 | 主键 | mall_chengweiqiangXXXX) | |
buyer_id | char | 16 | 外键(buyer.id) | 非空 | 买家ID |
contact_phone | char | 11 | 非空 | 收货人联系方式 | |
detail_address | text | 非空 | 详细地址 | ||
is_default | enum(“yes”,”no”) | 默认 no | 是否默认地址 |
- 创建产品种类表“product_type_姓名全拼”,表中字段信息如下:
-
create table product_chengweiqiang( -> code char(6) comment"产品种类编码(TXXXXX)" primary key, -> name varchar(30) comment"产品种类名称" not null);
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
code | char | 6 | 主键 | 产品种类编码(TXXXXX) | |
name | varchar | 30 | 非空 | 产品种类名称 |
- 创建产品表“product_姓名全拼”,表中字段信息如下:
-
create table product_chengweiqiang( -> id char(16) comment"产品编号(P_DATE_XXXXX)" primary key, -> seller_id char(16) comment"卖家id" not null, -> type_id char(6) comment"产品种类编码" not null, -> name varchar(100) comment"产品名称" not null , -> priture text comment"产品展示图", -> unit_price double comment"单价" not null, -> quantity int(10) comment"库存数量" default"100", -> constraint fk3_seller_id foreign key (seller_id) references seller_chengweiqiang(id), -> constraint fk4_type_id foreign key (type_id) references product_type(code));
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
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 | 库存数量 |
- 创建订单表“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 | 实付款 |
- 创建订单详情表“order_detail_姓名全拼”,表中字段信息如下:
-
create table order_detail( -> id int(10) primary key auto_increment, -> order_id char(16) comment"订单编号" not null , -> product_id char(16) comment"铲平编号" not null, -> purchase_quantity int(3) default"1" comment"采购数量", -> discount_unit_price double comment"产品折后价格" not null, -> constraint fk12_order_id foreign key(order_id) references order_chengweiqiang(id), -> constraint fk34_product_id foreign key(product_id) references product_chengweiqiang(id));
字段名 | 数据类型 | 长度 | 主、外键 | 其他约束 | 备注信息 |
id | int | 10 | 主键 | 自增 | |
order_id | char | 16 | 外键(order.id) | 非空 | 订单编号 |
product_id | char | 16 | 外键(product.id) | 非空 | 产品编号 |
purchase_quantity | int | 3 | 默认1 | 采购数量 | |
discount_unit_price | double | 非空 | 产品折后价 |
插入数据
- 所有字段批量插入用户表数据
-
insert into user_chengweiqiang 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");
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 |
- 所有字段批量插入卖家信息表数据
-
insert into seller_chengweiqiang values -> ("S_20200703_00001","13812345678","2020-07-03","ledin","ledin"),("S_20201030_00001","18212345678","2020-10-30","hla","hla");
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 buyer_chengweiqiang(id,phone,nickname,height,weight) values -> ("B_20200422_00001","13212345678","funny shop","168","52"),("B_20200911_00001","13112345678","cool girl","165","47");
phone | nickname | height | weight | |
B_20200422_00001 | 13212345678 | funny shop | 168 | 52 |
B_20200911_00001 | 13112345678 | cool girl | 165 | 47 |
- 指定字段批量插入地址表数据
-
insert into address_chengweiqiang(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");
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 product_type values -> ("T00001","coat"), -> ("T00002","shirt"), -> ("T00003","shorts"), -> ("T00004","pants"), -> ("T00005","jeans"), -> ("T00006","polo");
name | |
T00001 | coat |
T00002 | shirt |
T00003 | shorts |
T00004 | pants |
T00005 | jeans |
T00006 | polo |
- 指定字段插入产品表数据
-
insert product_chengweiqiang(id,seller_id,type_id,name,priture,unit_price) values -> ("P_20190102_00001","S_20200703_00001","T00003","blue shorts","p123.jpg","168.8");
id | seller_id | type_id | name | picture | unit_price |
P_20190102_00001 | S_20200703_00001 | T00003 | blue shorts | p123.jpg | 168.8 |
- 所有字段插入产品表数据
insert product_chengweiqiang values
-> ("P_20190102_00002","S_20200703_00001","T00001","coat","coat1.jpg","62.2","43");
seller_id | type_id | name | picture | unit_price | quantity | |
P_20190102_00002 | S_20200703_00001 | T00001 | coat | coat1.jpg | 62.2 | 43 |
- 指定字段插入产品表数据
insert product_chengweiqiang(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 | unit_price |
P_20190203_00001 | S_20201030_00001 | T00006 | black polo | 239.9 |
- 所有字段插入产品表数据
insert product_chengweiqiang values
-> ("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8","23");
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 order_chengweiqiang(id,seller_id,buyer_id,address_id) values
-> ("O_20201102_00001","S_20200703_00001","B_20200422_00001","A_20201103_00004");
id | seller_id | buyer_id | address_id |
O_20201102_00001 | S_20200703_00001 | B_20200422_00001 | A_20201103_00004 |
- 订单详情表指定字段插入数据
-
insert into order_detail(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");
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 |
id | seller_id | buyer_id | address_id |
O_20201102_00002 | S_20201030_00001 | B_20200911_00001 | A_20201103_00007 |
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 |
查看订单信息视图中采购数量不为1的数
- 修改订单详情表中O_20201102_00001订单P_20190102_00002产品的采购数量为1
-
查看O_20201102_00001订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价update order_detail set purchase_quantity="1" where product_id="P_20190102_00002";
-
select a.order_id,a.product_id,b.quantity ,b.quantity-a.purchase_quantity 采购后数量, -> b.unit_price,a.discount_unit_price from order_detail a inner join product_chengweiqiang b -> on a.product_id = b.id;
- 修改产品表中库存数量为采购后数量
-
update product_chengweiqiang set quantity="99" where id="P_20190102_00001"; update product_chengweiqiang set quantity="42" where id="P_20190102_00002";
- 根据订单号分组查看订单号、订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
-
select a.order_id,a.purchase_quantity*b.unit_price 订单总价格,a.purchase_quantity*discount_unit_price 实付款 from order_detail a inner join product_chengweiqiang b on a.product_id = b.id;
- 根据上述代码计算出的值修改订单表中O_20201102_00001订单的总价、实付款数据
-
update order_chengweiqiang set total_proce = "239.9",actrual_payment="230" where id="O_20201102_00002";
- 查看O_20201102_00001订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价
-
select a.id,b.name,c.nickname,d.detail_address,e.name,f.purchase_quantity,f.discount_unit_price from order_chengweiqiang a inner join seller_chengweiqiang b on a.seller_id = b.id -> inner join buyer_chengweiqiang c on a.buyer_id = c.id -> inner join address_chengweiqiang d on a.address_id = d.id -> inner join product_chengweiqiang e on b.id = e.seller_id -> inner join order_detail f on a.id=f.order_id -> where f.order_id ="O_20201102_00001";
-
任务四、使用事务操作表中数据
- 开启事务
-
start transaction;
- 订单表指定字段插入数据
-
insert into order_chengweiqiang(id ,seller_id ,buyer_id ,address_id ) values -> ("O_20201102_00002","S_20201030_00001","B_20200911_00001","A_20201103_00007");
- 订单详情表指定字段插入数
-
insert into order_detail (order_id,product_id ,purchase_quantity,discount_unit_price)values -> ("O_20201102_00002","P_20190203_00001","1","230"), -> ("O_20201102_00002","P_20190203_00002","2","190");
- 查看O_20201102_00002订单的订单编号、产品编号、库存数量、采购数量、采购后数量(库存数量-采购数量)、产品单价、折后单价
-
select a.order_id,a.product_id,a.purchase_quantity,b.quantity,b.quantity-a.purchase_quantity 采购后数量,b.unit_price,a.discount_unit_price from order_detail a inner join product_chengweiqiang b on a.product_id = b.id;
- 修改产品表中库存数量为采购后数量
-
update product_chengweiqiang set quantity = "99" where id ="P_20190102_00001" or id="P_20190203_00001"; update product_chengweiqiang set quantity = "42" where id ="P_20190203_00002"; update product_chengweiqiang set quantity = "21" where id ="P_20190203_00002";
- 根据订单号分组查看订单总价(sum(采购数量*产品单价))、实付款(sum(采购数量*折扣单价))
-
select a.order_id,a.purchase_quantity*b.unit_price 订单总价格,a.purchase_quantity*discount_unit_price 实付款 from order_detail a inner join product_chengweiqiang b on a.product_id = b.id;
- 根据上述代码计算出的值修改订单表中O_20201102_00002订单的总价、实付款数据
-
update order_chengweiqiang set total_proce = "239.9",actrual_payment="230" where id="O_20201102_00002";
- 查看订单表所有字段数据
- 查看订单详情表所有字段数据
- 提交事务
- 开启事务
- 修改订单详情表中O_20201102_00002订单P_20190203_00002产品的折后单价为180
-
update order_detail set discount_unit_price="180" where order_id="O_20201102_00002" and product_id="P_20190203_00002";
- 修改订单详情表中O_20201102_00002订单P_20190203_00001产品的折后单价为200
-
update order_detail set discount_unit_price="200" where order_id="O_20201102_00002" and product_id="P_20190203_00001";
- 根据订单号分组查看实付款(sum(采购数量*折扣单价))
-
select a.order_id,a.purchase_quantity*discount_unit_price 实付款 from order_detail a inner join product_chengweiqiang b on a.product_id = b.id;
- 根据上述代码计算出的值修改订单表中O_20201102_00002订单的实付款数据
-
update order_chengweiqiang set actrual_payment="360" where id="O_20201102_00002";
- 查看订单详情表所有字段数据
- 回滚事务
- 查看O_20201102_00002订单的订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
-
select a.id,b.name,c.nickname,d.detail_address,e.name,f.purchase_quantity,f.discount_unit_price from order_chengweiqiang a inner join seller_chengweiqiang b on a.seller_id = b.id inner join buyer_chengweiqiang c on a.buyer_id = c.id inner join address_chengweiqiang d on a.address_id = d.id inner join product_chengweiqiang e on b.id = e.seller_id inner join order_detail f on a.id=f.order_id where f.order_id ="O_20201102_00002";
- 查看买家昵称、性别、联系方式、详细地址、是否默认地址
-
select a.nickname,a.gender,a.phone,b.detail_address,b.is_default from buyer_chengweiqiang a inner join address_chengweiqiang b on a.id=b.buyer_id;
- 创建买家信息视图“view_buyer_info_姓名全拼”查看上述内容
-
create view view_buyer_info_chengweiqiang as select a.nickname,a.gender,a.phone,b.detail_address,b.is_default from buyer_chengweiqiang a inner join address_chengweiqiang b on a.id=b.buyer_id;
- 查看买家信息视图买家昵称含有“h”的数据
-
select * from view_buyer_info_chengweiqiang where nickname like "%h%";
- 查看产品种类编码、产品种类名称、产品名称、单价、库存
-
select a.code,a.name,b.name,b.unit_price,b.quantity from product_type a -> inner join product_chengweiqiang b on a.code=b.type_id;
- 创建产品信息视图“view_product_ info_姓名全拼”查看上述内容
-
create view view_product_info_chengweiqiang as select a.code,a.name,b.name1,b.unit_price,b.quantity from product_type a inner join product_chengweiqiang b on a.code=b.type_id;
- 查看订单详情表中的所有产品名称
-
select a.product_id,b.name1 from order_detail a inner join product_chengweiqiang b on a.product_id = b.id;
- 查看产品信息视图中已经有过订单销售记录的产品数据(子查询 in)
- 查看订单编号、店铺名称、买家昵称、详细地址、产品名称、采购数量、折后价格
-
select a.id,b.name,c.nickname,d.detail_address,e.name1,f.purchase_quantity,f.discount_unit_price from order_chengweiqiang a inner join seller_chengweiqiang b on a.seller_id = b.id -> inner join buyer_chengweiqiang c on a.buyer_id = c.id -> inner join address_chengweiqiang d on a.address_id = d.id -> inner join product_chengweiqiang e on b.id = e.seller_id -> inner join order_detail f on a.id=f.order_id -> ;
- 创建订单信息视图“view_order_ info_姓名全拼”查看上述内容
-
create view view_order_info_chengweiqiang as select a.id,b.name,c.nickname,d.detail_address,e.name1,f.purchase_quantity,f.discount_unit_price from order_chengweiqiang a inner join seller_chengweiqiang b on a.seller_id = b.id inner join buyer_chengweiqiang c on a.buyer_id = c.id inner join address_chengweiqiang d on a.address_id = d.id inner join product_chengweiqiang e on b.id = e.seller_id inner join order_detail f on a.id=f.order_id;
任务六、备份数据库
- 备份所有数据库,文件名为“all_姓名全拼.sql”(截两张图 命令+文件)
-
mysqldump -u root -p123456 --all-databases>D:\MYSQL\mysql-8.0.28-winx64\all_chengweiqiang.sql
- 备份电子商城数据库,文件名为“mall_姓名全拼.sql”(截两张图 命令+文件)
-
mysqldump -u root -p123456 mall_chengweiqiang>D:\MYSQL\mysql-8.0.28-winx64\mll_chengweiqiang.sql
- 删除电子商城数据库
- 创建电子商城数据库“mall_姓名全拼”
- 退出MySQL登录
- 执行电子商城数据库备份文件
- 登录MySQL
- 使用电子商城数据库
- 查看所有表