题目：购物系统数据库设计

一、场景描述

随着互联网的普及，越来越多的人选择网上购物。购物系统已经成为了必然趋势，购物系统的主要资源是商品。商品由用户从网上查看价格、品质、评论等选择商品，用户下单付款后商家发货处理。商品种类齐全，有食品类、衣物类、电器类等等。
用户先进行平台注册。需要注册昵称、姓名、账号密码、性别、手机号等基本信息，注册成功登录后可进行查看商品，商品有商品名称、商品分类及图片展示等，用户可直接通过商品ID查看需要购买的商品，便捷省时。可通过商品ID字段查询对应的价格信息，选择好商品便可直接下单。
管理员可在后台查看用户购买信息记录，购买次数和购买该类商品的商品总价格，也可对商品价格数量进行更改等操作。

二、建表语句

（1）用户表

创建表

 CREATE TABLE `user` (

 `id` int(0) NOT NULL AUTO_INCREMENT,

 `nickname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

 `name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

 `password` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

 `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

 `sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

 `gmt_create` datetime(0) NULL DEFAULT NULL,

 `gmt_modified` datetime(0) NULL DEFAULT NULL,

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

添加数据

INSERT INTO `user` VALUES (1, '飞在天空中的野草帽', '韩信', '123', '15578722321', '男', '2022-01-04 21:57:27', '2022-01-04 21:57:36');

INSERT INTO `user` VALUES (2, '小聪明', '花木兰', '432', '13252134567', '女', '2022-01-05 21:58:25', '2022-01-05 21:58:32');

INSERT INTO `user` VALUES (3, '海阔天空', '刘备', '567', '13245678902', '男', '2022-01-06 21:59:35', '2022-01-06 21:59:42');

INSERT INTO `user` VALUES (4, 'fly', '小乔', '789', '13255678932', '女', '2022-01-07 22:01:09', '2022-01-07 22:01:13');

INSERT INTO `user` VALUES (5, '会飞的猪', '凯', '786', '15523456788', '男', '2022-01-08 22:02:27', '2022-01-08 22:02:34');

（2）商品表

创建表

CREATE TABLE `product` (

 `id` int(0) NOT NULL AUTO_INCREMENT,

 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',

 `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '类型',

 `gmt_create` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

 `gmt_modified` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;   

添加数据

INSERT INTO `product` VALUES (1, '三只松鼠奥尔良小鸡腿鸡翅中160g*1袋网红休闲零食小吃熟', '食品', '2022-01-04 22:07:57', '2022-01-04 22:07:53');

INSERT INTO `product` VALUES (2, 'CP正大食品炸鸡盐酥鸡1000g炸鸡肉韩式炸鸡鸡米花鸡肉原', '食品', '2022-01-05 22:09:04', '2022-01-05 22:08:59');

INSERT INTO `product` VALUES (3, '溜溜梅 “梅”好全家桶三件套 休闲青梅', '食品', '2022-01-06 22:10:08', '2022-01-06 22:10:02');

INSERT INTO `product` VALUES (4, '沃隆每日坚果礼盒装1220g混合坚果整箱零食大礼包过年送礼年货', '食品', '2022-01-05 22:11:06', '2022-01-05 22:11:01');

INSERT INTO `product` VALUES (5, ' 夏诗文 人间小巴黎 仿兔毛设计感立领环保皮草毛绒绒', '衣服', '2022-01-11 22:12:20', '2022-01-11 22:12:17');

INSERT INTO `product` VALUES (6, '秋冬卫衣男加绒加厚2021新款长袖t恤秋装衣服潮流', '衣服', '2022-01-07 22:13:15', '2022-01-07 22:13:10');

（3）价格表

创建表

CREATE TABLE `price` (

 `id` int(0) NOT NULL AUTO_INCREMENT,

 `p_id` int(0) NULL DEFAULT NULL COMMENT '商品id',

 `price` decimal(10, 2) NULL DEFAULT NULL COMMENT '价格',

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

添加数据

INSERT INTO `price` VALUES (1, 1, 19.90);

INSERT INTO `price` VALUES (2, 2, 39.90);

INSERT INTO `price` VALUES (3, 3, 99.00);

INSERT INTO `price` VALUES (4, 4, 139.00);

INSERT INTO `price` VALUES (5, 5, 498.00);

INSERT INTO `price` VALUES (6, 6, 39.90);

（4）购物车表

创建表

CREATE TABLE `car` (

 `id` int(0) NOT NULL AUTO_INCREMENT,

 `uid` int(0) NULL DEFAULT NULL COMMENT '用户id',

 `pid` int(0) NULL DEFAULT NULL COMMENT '商品id',

 `gm_num` int(0) NULL DEFAULT NULL COMMENT '购买数量',

 `total` decimal(10, 2) NULL DEFAULT NULL COMMENT '总价',

 PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 

添加数据

INSERT INTO `car` VALUES (1, 1, 1, 2, 19.90);

INSERT INTO `car` VALUES (2, 1, 2, 3, 119.70);

INSERT INTO `car` VALUES (3, 2, 4, 2, 278.00);

INSERT INTO `car` VALUES (4, 3, 2, 1, 39.90);

INSERT INTO `car` VALUES (5, 4, 3, 1, 99.00);

INSERT INTO `car` VALUES (6, 5, 4, 1, 139.00);

INSERT INTO `car` VALUES (7, 3, 5, 1, 498.00);

INSERT INTO `car` VALUES (8, 4, 5, 1, 498.00);

INSERT INTO `car` VALUES (9, 4, 6, 1, 39.90);

INSERT INTO `car` VALUES (10, 2, 2, 1, 39.90);

三、检索语句

1、分别用通配符和正则表达式匹配韩信的user_name语句

通配符：

select user.name from user where name='韩信';

正则表达式：

select user.name from user where name regexp '韩信'; 

2 请写出：检索内容格式为“id-phone(name)的语句

select concat(id,'-',phone,concat('(',name,')')) as 'id-phone(name)' from go.`user`;

3、查看选择食品的人数的语句

select count(type) as type_num from product where type='食品';

4、查看购买今年上架商品的人数

select count(distinct(car.uid)) as number from car,product 
where car.pid=product.id 
and year(product.gmt_create)=year(NOW());

5、写出购买“溜溜梅 "梅"好全家桶三件套 休闲青梅”的用户信息

select user.* 
from user,car,product 
where user.id=car.uid and car.pid=product.id
and product.`name`='溜溜梅 “梅”好全家桶三件套 休闲青梅';

6、检索每个商品用户的购买信息？

select product.`name` as p_name ,user.*  
from user 
left join car on `user`.id=car.uid 
left join product on car.pid=product.id 
group by product.`name`;

7、查询用户小乔购买上面的数量

select user.name, count(product.id) as number 
from user,car,product 
where user.id=car.uid and car.pid=product.id
and user.`name`='小乔'

8、通过视图查询各个用户名字及手机号

create view userinfo as
select name,phone from user;
select * from userinfo;

9、找出消费50元以上的用户一共购买了几件物品，检索出对应的商品名称及价格

select user.name, product.`name`,product.type,price.price
from user,car,product,price
where user.id=car.uid and car.pid=product.id  and product.id=price.p_id
and price.price>50

10、查询出平均消费最高的用户信息

select user.*, avg(car.total)  
from user 
left join car on user.id=car.uid 
group by user.id 
order by avg(car.total) desc 
limit 0,1

11、所有购买了衣服类型商品的用户名，及对应商品名称及价格

select t1.name as u_name, t3.`name` as p_name,t4.price
from user as t1
left join car as t2 on t1.id=t2.uid
left join product as t3 on t2.pid=t3.id
left join price as t4 on t3.id=t4.p_id
where t3.type='衣服';

12、查询每个商品的购买数量及对应的商品类型

select t2.`name` as p_name,t2.type, sum(t1.gm_num) as num
from car as t1
left join product as t2 on t1.pid=t2.id 
group by t2.id;

13、查询每个用户购买商品数量，用户名称，商品名称

select t1.name as u_name, t3.`name` as p_name,t2.gm_num
from user as t1
left join car as t2 on t1.id=t2.uid
left join product as t3 on t2.pid=t3.id
group by t3.id;

14、构建视图所有购买了食品类型商品的用户名，及商品信息

select t1.name as u_name, t3.* ,t4.price
from user as t1
left join car as t2 on t1.id=t2.uid
left join product as t3 on t2.pid=t3.id
left join price as t4 on t3.id=t4.p_id
where t3.type='食品';
select * from products;

15、构建查询每个商品的购买数量及对应的商品类型的存储过程

mysql> delimiter $$
mysql> create procedure test1()
    -> begin
    -> select t2.`name` as p_name,t2.type, sum(t1.gm_num) as num
    -> from car as t1
    -> left join product as t2 on t1.pid=t2.id
    -> group by t2.id;
    -> end $$
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> call test1();
+-------------------------------------------------------------------------------------+--------+------+
| p_name                                                                              | type   | num  |
+-------------------------------------------------------------------------------------+--------+------+
| 三只松鼠奥尔良小鸡腿鸡翅中160g*1袋网红休闲零食小吃熟                                | 食品   |    2 |
| CP正大食品炸鸡盐酥鸡1000g炸鸡肉韩式炸鸡鸡米花鸡肉原                                 | 食品   |    5 |
| 沃隆每日坚果礼盒装1220g混合坚果整箱零食大礼包过年送礼年货                           | 食品   |    3 |
| 溜溜梅 “梅”好全家桶三件套 休闲青梅                                                  | 食品   |    1 |
|  夏诗文 人间小巴黎 仿兔毛设计感立领环保皮草毛绒绒                                   | 衣服   |    2 |
| 秋冬卫衣男加绒加厚2021新款长袖t恤秋装衣服潮流                                       | 衣服   |    1 |
+-------------------------------------------------------------------------------------+--------+------+
6 rows in set (0.01 sec)

Query OK, 0 rows affected (0.04 sec)