MYSQL第十四次作业---电子商城数据库搭建
- 安裝并配置MySQL?
1.打開控制臺
Win+r鍵
2.登錄MYSQL
- 數據庫、表的基本操作
use mall_tushi;
| 字段名 | 數據類型 | 長度 | 主、外鍵 | 其他約束 | 備注信息 |
| phone | char | 11 | 主鍵 | 注冊手機號 | |
| username | varchar | 20 | 非空，唯一 | 用戶名 | |
| password | varchar | 20 | 非空 | 密碼 | |
| question | text | 非空 | 找回密碼問題 | ||
| answer | text | 非空 | 找回密碼問題答案 |
?create table user_tushi(
? ? -> ?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"找回密碼問題答案"
? ? -> );
2.創建賣家信息表“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_tushi(
? ? -> 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_tushi_phone foreign key(phone) references user_tushi(phone)
? ? -> );?
3.創建買家信息表“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_tushi(
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_tushi_phone foreign key(phone) references user_tushi(phone)
);
4.創建地址表“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_tushi(
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_tushi_buyer_id foreign key(buyer_id) references buyer_tushi(id)
);?
5.創建產品種類表“product_type_姓名全拼”，表中字段信息如下：
| 字段名 | 數據類型 | 長度 | 主、外鍵 | 其他約束 | 備注信息 |
| code | char | 6 | 主鍵 | 產品種類編碼(TXXXXX) | |
| name | varchar | 30 | 非空 | 產品種類名稱 |
create table product_type_tushi(
code char(6)primary key comment"產品種類編碼(TXXXXX)",
name varchar(30) not null comment"產品種類名稱"
);?
6.創建產品表“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_tushi(
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_zhongzheng_seller_id foreign key(seller_id) references seller_tushi(id),
constraint fk_product_tushi_type_id foreign key(type_id) references product_type_tushi(code)
);?
7.創建訂單表“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_tushi(
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_tushi_seller_id foreign key(seller_id) references seller_tushi(id),
constraint fk_order_tushi_buyer_id foreign key(buyer_id) references buyer_tushi(id),
constraint fk_order_tushi_address_id foreign key(address_id) references address_tushi(id)
);?
8.創建訂單詳情表“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_tushi(
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_tushi_order_id foreign key(order_id) references order_tushi(id),
constraint fk_order_detail_tushi_product_id foreign key(product_id) references product_tushi(id)
);?
數據庫第十四次作業
——電子商城項目
任務三、對表中數據進行基本操作
| 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_tushi 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");
1.所有字段批量插入賣家信息表數據
| 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_tushi values
("S_20200703_00001",13812345678,"2020-07-03","ledin","ledin"),
("S_20201030_00001",18212345678,"2020-10-30","hal","hal");
2.指定字段批量插入買家信息表數據
| id | phone | nickname | height | weight |
| B_20200422_00001 | 13212345678 | funny shop | 168 | 52 |
| B_20200911_00001 | 13112345678 | cool girl | 165 | 47 |
?insert into buyer_tushi (id,phone,nickname,height,weight) values
("B_20200422_00001",13212345678,"funny shop",168,52),
("B_20200911_00001",13112345678,"cool girl",165,47);
3.指定字段批量插入地址表數據
| 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_tushi (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");
4.所有字段批量插入產品種類表數據
| code | name |
| T00001 | coat |
| T00002 | shirt |
| T00003 | shorts |
| T00004 | pants |
| T00005 | jeans |
| T00006 | polo |
?insert into product_type_tushi values
("T00001","coat"),
("T00002","shirt"),
("T00003","shorts"),
("T00004","pants"),
("T00005","jeans"),
("T00006","polo");
5.指定字段插入產品表數據
| 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_tushi (id,seller_id,type_id,name,picture,unit_price) values
("P_20190102_00001","S_20200703_00001","T00003","blue shorts","p123.jpg","168.8");
6.所有字段插入產品表數據
| 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_tushi values
("P_20190102_00002","S_20200703_00001","T00001","coat","coat1.jpg",62.2,43);
7.指定字段插入產品表數據
| id | seller_id | type_id | name | unit_price |
| P_20190203_00001 | S_20201030_00001 | T00006 | black polo | 239.9 |
?insert into product_tushi (id,seller_id,type_id,name,unit_price) values
("P_20190203_00001","S_20201030_00001","T00006","black polo","239.9");
8.所有字段插入產品表數據
| 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_tushi values
("P_20190203_00002","S_20201030_00001","T00005","jeans","12.jpg","198.8",23);
??
9.查看產品表所有字段數據
select * from product_tushi;?
10.訂單表指定字段插入數據
| id | seller_id | buyer_id | address_id |
| O_20201102_00001 | S_20200703_00001 | B_20200422_00001 | A_20201103_00004 |
?insert into order_tushi (id,seller_id,buyer_id,address_id) values
("O_20201102_00001","S_20200703_00001","B_20200422_00001","A_20201103_00004");
11.訂單詳情表指定字段插入數據
| 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_tushi (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);
12.修改訂單詳情表中O_20201102_00001訂單P_20190102_00002產品的采購數量為1?
?update order_detail_tushi set purchase_quantity=1
where product_id="P_20190102_00002";
13.查看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_tushi a
inner join product_tushi b on a.product_id=b.id where a.order_id="O_20201102_00001";
14.修改產品表中庫存數量為采購后數量
?update product_tushi set quantity=99 where unit_price=168.8;
update product_tushi set quantity=42 where unit_price=62.2;
15.根據訂單號分組查看訂單號、訂單總價（sum(采購數量*產品單價)）、實付款（sum(采購數量*折扣單價)）
select a.order_id,sum(a.purchase_quantity*b.unit_price),sum(a.purchase_quantity*a.discount_unit_price)
from order_detail_tushi a inner join product_tushi b on a.product_id=b.id
group by a.order_id;
16.根據上述代碼計算出的值修改訂單表中O_20201102_00001訂單的總價、實付款數據
?update order_tushi set total_price=231,actrual_payment=190 where id="O_20201102_00001";
17.查看O_20201102_00001訂單的訂單編號、店鋪名稱、買家昵稱、詳細地址、產品名稱、采購數量、折后價格
select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_tushi c inner join product_tushi f on c.id=f.seller_id
inner join order_detail_tushi b on f.id=b.product_id
inner join order_tushi a on b.order_id=a.id
inner join address_tushi e on a.address_id=e.id
inner join buyer_tushi 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_tushi(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 |
?
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_tushi a
inner join product_tushi b on a.product_id=b.id where a.order_id="O_20201102_00002";
5.修改產品表中庫存數量為采購后數量
update product_tushi set quantity=99 where unit_price=239.9;
update product_tushi 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_tushi a inner join product_tushi b on a.product_id=b.id
group by a.order_id;
7.根據上述代碼計算出的值修改訂單表中O_20201102_00002訂單的總價、實付款數據
?update order_tushi set total_price=231,actrual_payment=719.7 where id="O_20201102_00002";
?
8.查看訂單表所有字段數據
select * from order_tushi ;
9.查看訂單詳情表所有字段數據
?select * from order_detail_tushi ;
10.提交事務
commit;
11.開啟事務
start transaction;
12.修改訂單詳情表中O_20201102_00002訂單P_20190203_00002產品的折后單價為180
update order_detail_tushi 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_tushi 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_tushi a inner join product_tushi b on a.product_id=b.id
group by a.order_id;
15.根據上述代碼計算出的值修改訂單表中O_20201102_00002訂單的實付款數據
update order_tushi set actrual_payment=560 where id="O_20201102_00002";
16.查看訂單詳情表所有字段數據
select * from order_detail_tushi;
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_tushi c inner join product_tushi f on c.id=f.seller_id
inner join order_detail_tushi b on f.id=b.product_id
inner join order_tushi a on b.order_id=a.id
inner join address_tushi e on a.address_id=e.id
inner join buyer_tushi d on e.contact_phone=d.phone
where b.order_id = "O_20201102_00002";
任務五、創建并使用視圖
查看買家昵稱、性別、聯系方式、詳細地址、是否默認地址
select a.nickname,a.gender,b.contact_phone,b.detail_address,b.is_default from address_tushi b inner join buyer_tushi a on b.buyer_id=a.id;
?
創建買家信息視圖“view_buyer_info_姓名全拼”查看上述內容
create view view_buyer_info_tushi as select a.nickname,a.gender,b.contact_phone,b.detail_address,b.is_default from address_tushi b inner join buyer_tushi a on b.buyer_id=a.id;
?
查看買家信息視圖買家昵稱含有“h”的數據
select * from view_buyer_info_tushi where nickname like "%h%";
?
查看產品種類編碼、產品種類名稱、產品名稱、單價、庫存
?select a.code,a.name,b.id,b.unit_price,b.quantity from product_type_tushi a inner join product_tushi b on a.code=b.type_id;
?
創建產品信息視圖“view_product_ info_姓名全拼”查看上述內容
create view view_product_info_tushi as select a.code,a.name,b.id,b.unit_price,b.quantity from
? ? -> ?product_type_tushi a inner join
? ? -> product_tushi b on a.code=b.type_id;
?
查看訂單詳情表中的所有產品名稱
select b.name from order_detail_tushi a inner join product_tushi b on a.product_id=b.id;
?
查看產品信息視圖中已經有過訂單銷售記錄的產品數據（子查詢 in）
?select * from product_tushi where
? ? -> ?type_id in (select code from view_product_info_tushi);
查看訂單編號、店鋪名稱、買家昵稱、詳細地址、產品名稱、采購數量、折后價格
mysql> select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name from seller_tushi c inner join product_tushi f on c.id=f.seller_id
? ? -> inner join order_detail_tushi b on f.id=b.product_id
? ? -> inner join order_tushi a on b.order_id=a.id
? ? -> ?inner join address_tushi e on a.address_id=e.id
? ? -> ?inner join buyer_tushi d on e.contact_phone=d.phone;
?
?
創建訂單信息視圖“view_order_ info_姓名全拼”查看上述內容
create view view_order_info_tushi as select a.id,b.purchase_quantity,b.discount_unit_price,c.name,d.nickname,e.detail_address,f.name a from seller_tushi c inner join
? ? -> product_tushi f on c.id=f.seller_id
? ? -> inner join order_detail_tushi b on f.id=b.product_id
? ? -> ?inner join order_tushi a on b.order_id=a.id
? ? -> ?inner join address_tushi e on a.address_id=e.id
? ? -> ?inner join buyer_tushi d on e.contact_phone=d.phone;
?
查看訂單信息視圖中采購數量不為1的數據
?select * from view_order_info_tushi where ?purchase_quantity!=1 ;
任務六、備份數據庫
1.備份所有數據庫，文件名為“all_姓名全拼.sql”
?
總結
以上是生活随笔為你收集整理的MYSQL第十四次作业---电子商城数据库搭建的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： asciidoc转换html,Ascii
- 下一篇： 移动测试的常用工具