数据库第十四次作业——电子商城项目

r安装并配置MySQL

1、打开控制台

使用win加R组合键，此时会跳出运行窗口，点确定啦！

 2 登录MySQL

• 数据库、表的基本操作

1、创建电子商城数据库“mall_姓名全拼”

2、使用电子商城数据库

3.创建用户表“user_姓名全拼”，表中字段信息如下：

字段名	数据类型	长度	主、外键	其他约束	备注信息
phone	char	11	主键		注册手机号
username	varchar	20		非空，唯一	用户名
password	varchar	20		非空	密码
question	text			非空	找回密码问题
answer	text			非空	找回密码问题答案

create table user_huyongliang (
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
);

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_huyongliang(
    -> id char(16) comment"卖家ID(S_DATE_XXXXX)" primary key,
    ->  phone char(11) comment"注册手机号" not null unique,
    ->  open_date date comment"开业时间" not null,
    ->  name varchar(50) comment"店铺名称" not null,
    ->  constraint fk_phone foreign key (phone) references user_huyongliang(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_huyongliang(
    -> id char(16) comment"买家ID(B_DATE_XXXXX)" primary key,
    -> phone1 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 fk_phone1 foreign key (phone1) references user_huyongliang(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_huyongliang (
id char(16) comment"地址ID (A_DATE_XXXXX)" primary key,
buyer_id char(16) comment"买家ID" not null,
conteact_phone char(11) comment"收货人联系方式" not null,
detail_address text comment"详细地址" not null,
is_default enum("yes","no") comment"是否默认地址" default "no",
constraint fk_buyer_id foreign key (buyer_id) references buyer_huyongliang(id)
);

7,创建产品种类表“product_type_姓名全拼”，表中字段信息如下：

字段名	数据类型	长度	主、外键	其他约束	备注信息
code	char	6	主键		产品种类编码(TXXXXX)
name	varchar	30		非空	产品种类名称

 create table product_type_huyongliang (
code char(6) comment"产品种类编码(TXXXXX)" primary key,
name varchar(30) comment"产品种类名称" not null
);

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_huyongliang (
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,
picture text comment"产品展示图",
unit_price double comment"单价" not null,
quantity int(10) comment"库存数量" default "100",
constraint fk_seller_id foreign key (seller_id) references seller_huyongliang(id),
constraint fk_type_id foreign key (type_id) references product_type_huyongliang(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_huyongliang (
id char(16) comment"订单编号(O_DATE_XXXXX)" primary key,
seller_id1 char(16) comment"卖家ID" not null,
buyer_id1 char(16) comment"买家ID" not null,
address_id char(16) comment"地址ID" not null,
total_price double comment"总价" default "0",
actrual_payment double comment"实付款" default "0",
constraint fk_seller_id1 foreign key (seller_id1) references seller_huyongliang(id),
constraint fk_buyer_id1 foreign key (buyer_id1) references buyer_huyongliang(id),
constraint fk_address_id foreign key (address_id) references address_huyongliang(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			非空	产品折后价

 table order_detail_huyongliang (
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) comment"采购数量" default "1",
discount_unit_price double comment"产品折后价" not null,
constraint fk_order_id foreign key (order_id) references order_huyongliang(id),
constraint fk_product_id foreign key (product_id) references product_huyongliang(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_huyongliang 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_huyongliang values
("S_20200703_00001","13812345678","2020-07-03","ledin","ledin"),
("S_20201030_00001","18212345678","2020-10-30","hla","hla");
 

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_huyongliang(id,phone1,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_huyongliang (id,buyer_id,conteact_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_huyongliang(code,name) 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_huyongliang (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_huyongliang (id,seller_id,type_id,name,picture,unit_price,quantity) 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_huyongliang (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_huyongliang (id,seller_id,type_id,name,picture,unit_price,quantity) values
("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8","23");
  

10.查看产品表所有字段数据

select * from product_huyongliang;

11   ,订单表指定字段插入数据

id	seller_id	buyer_id	address_id
O_20201102_00001	S_20200703_00001	B_20200422_00001	A_20201103_00004

insert into order_huyongliang (id,seller_id1,buyer_id1,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	4

insert into order_detail_huyongliang (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");
 

update order_detail_huyongliang set purchase_quantity =1 where id =2;
 

select a.order_id,a.product_id,b.quantity,a.purchase_quantity,b.quantity-a.purchase_quantity 采购后数量,
b.unit_price,a.discount_unit_price from order_detail_huyongliang a inner join product_lexiang b on a.product_id = b.id; 

update product_huyongliang set
quantity = quantity - 1 where id ="P_20190102_00001" or id ="P_20190102_00002";

select a.order_id,sum(a.purchase_quantity * b.unit_price) 订单总价,sum(a.purchase_quantity * a.discount_unit_price) 实付款
from order_detail_lexiang a inner join product_huyongliang b on a.product_id = b.id group by a.order_id; 

update order_huyongliang set 
total_price = 231,actrual_payment = 190 
where id ="O_20201102_00001";

 select c.order_id,a.name,b.name,g.detail_address,e.nickname,c.purchase_quantity,c.discount_unit_price from
seller_huyongliang a inner join product_lexiang b on a.id = b.seller_id
inner join order_detail_huyongliangc on b.id = c.product_id
inner join order_huyongliang f on c.order_id = f.id
inner join buyer_huyongliang  e on f.buyer_id1 = e.id
inner join address_huyongliang g on e.id = g.buyer_id;

任务四、使用事务操作表中数据

1、开启事务

 

select c.order_id,a.name,b.name,g.detail_address,e.nickname,c.purchase_quantity,c.discount_unit_price from
seller_huyongliang a inner join product_huyongliang b on a.id = b.seller_id
inner join order_detail_huyongliang c on b.id = c.product_id
inner join order_huyongliang f on c.order_id = f.id
inner join buyer_ huyongliang e on f.buyer_id1 = e.id
inner join address_huyongliang  g on e.id = g.buyer_id;

任务五、创建并使用视图

1、查看买家昵称、性别、联系方式、详细地址、是否默认地址

select a.nickname,a.gender,a.phone1,b.detail_address,b.is_default from
buyer_huyongliang a inner join address_huyongliang  b on
a.id = b.buyer_id;

任务六、备份数据库

1、备份所有数据库，文件名为“all_姓名全拼.sql”（截两张图 命令+文件）

mysqldump -uroot -pzxc123456 --all -databases>all_huyongliang .sql

mysqldump -uroot -pzxc123456 mall_huyongliang>mall_huyongliang.sql

3、删除电子商城数据库

 drop database mall_huyongliang;

4、创建电子商城数据库“mall_姓名全拼”

 create database mall_huyongliang;

5、退出MySQL登录

\q

 6、执行电子商城数据库备份文件

mysql -uroot -pzxc123456 mall_lexiang<mall_lexiang.sql

7、登录MySQL

mysql -u root -p

use mall_huyongliang;
9、查看所有表

 show tables;