【MySQL 数据库应用】-购物网站系统数据库设计

【MySQL 数据库应用】-购物网站系统数据库设计

项目开发背景

MyShop商城致力于提供流畅的购物体验，旨在为用户打造一个便捷的在线购物平台，涵盖了商品浏览、购物车管理、结算付款等功能。同时提供了后台管理系统，方便商家管理商品信息和订单。该项目需要一个可靠高效的数据库系统来管理商品、用户和订单信息。这个数据库系统必须具备出色的性能和可靠性，通过设计和优化提高系统响应速度和数据一致性。我们的目标是设计实现MyShop商城的数据库系统，从而为用户提供卓越的购物体验。通过这个项目，我们不仅希望学习MySQL数据库开发技术，更致力于为用户创造一个简单实用的购物平台。

开发项目的目标及设计要求

项目目标

本项目旨在通过学习MySQL数据库应用，着重于购物网站系统的数据库设计。我们的目标是构建一个高效可靠的数据库系统，有效管理商品、用户和订单信息，从而提供顶尖的购物体验。通过理论与实践相结合，强调培养团队的协作沟通、数据库设计能力，并专注于最新技术的应用与掌握。同时，我们致力于建立"MyShop商城"，为现代消费者打造一个便捷高效的在线购物平台，集成商品浏览、购物车管理、结算付款等功能，并借助后台管理系统提供用户友好的购物环境。我们的目标是通过学习MySQL数据库开发技术，构建安全、高效的系统，满足用户的购物需求。

设计要求

为确保系统的稳定性、安全性和完整性，该项目设计要求包括功能模块设计、数据库结构设计、数据安全性、数据一致性与完整性以及前后端交互实现,要求如下：

• 功能模块设计：划分并设计清晰的功能模块，涵盖用户管理、商品展示、购物车管理、订单处理、支付功能等，确保每个模块的功能完备并相互协调。
• 数据库结构设计：创建恰当的表结构，涵盖用户信息、商品数据、订单详情等核心信息，确保表之间的关联性和数据一致性，以支持系统各项功能的有效运作。
• 数据安全性：引入适当的加密技术，如密码加密、传输加密等，确保用户信息在存储和传输过程中的安全，以防止数据泄露或被未授权者访问。
• 一致性与完整性：设计数据约束和验证规则，确保数据的一致性和完整性，避免数据冗余和错误，以提供准确可靠的信息支持系统运作。
• 前后端实现：使用Java与jsp技术实现前后端交互，确保系统各功能模块间数据的有效传递与协调运作，以提供用户流畅的体验。

数据库需求分析

用例和用例图

数据需求

MyShop 商城系统的数据需求包括用户数据、地址数据、商品数据、购物车数据、订单数据和订单项数据。用户数据包括账号、密码、邮箱等信息，地址数据包括收货地址和联系方式，商品数据包括商品类别和详细信息，购物车数据用于记录用户选择的商品，订单数据用于管理用户的订单信息，订单项数据用于记录订单中每个商品的详细信息。通过满足这些数据需求，MyShop 商城系统可以实现用户管理、地址管理、商品展示、购物车管理、订单管理和订单项管理的功能。

处理需求

MyShop 商城系统的功能和数据处理方式，主要包括用户管理、商品管理、购物车管理、订单管理、地址管理等方面。用户管理涵盖注册、登录、账号管理和权限控制；商品管理包括商品信息的录入、更新和查询；购物车管理允许用户管理购物车中的商品；订单管理涉及订单的生成、处理和管理；地址管理用于管理用户的收货地址；通过对这些处理需求进行分析，可以确保系统能够满足商城的核心功能需求，并提供良好的用户体验和商城运营效果。

安全性与完整性要求

主键约束

• 用户表（user）：{u_id} 列被定义为主键。
• 地址表（address）：{a_id} 列被定义为主键。
• 类别表（type）：{t_id} 列被定义为主键。
• 商品表（product）：{p_id} 列被定义为主键。
• 购物车表（cart）：{c_id} 列被定义为主键。
• 订单表（orders）：{o_id} 列被定义为主键。
• 订单项表（item）：{i_id} 列被定义为主键。

唯一性约束

• 用户表（{user}）：{u_name} 列被定义为唯一性约束。

外键约束

• 地址表（address）：{u_id} 定义为外键约束，引用用户表（user）的 u_id}列。
• 商品表（product）：t_id定义为外键约束，引用类别表（type）的 t_id 列。
• 购物车表（cart）：u_id定义为外键约束，引用用户表（user）的 u_id 列。
• 购物车表（cart）：p_id 定义为外键约束，引用商品表（product）的 p_id列。
• 订单表（orders）：u_id定义为外键约束，引用用户表（user）的 u_id 列。
• 订单表（orders）：a_id 定义为外键约束，引用地址表（address）的 a_id列。
• 订单项表（item）：o_id定义为外键约束，引用订单表（orders）的 o_id 列。
• 订单项表（item）：p_id定义为外键约束，引用商品表（product）的 p_id列。

ON DELETE 和 ON UPDATE 触发动作

• 在删除关联行时，购物车表（cart）和订单表（orders）的外键约束使用 ON DELETE CASCADE
• 触发动作，表示删除关联行时自动删除相关行。 在更新关联行时，地址表（address）的外键约束使用 ON UPDATE CASCADE 触发动作，表示更新关联行时自动更新相应行。

这些约束条件确保了数据库中的数据一致性和完整性，限制了不正确的操作并保护了数据的准确性和可靠性。

数据库设计与实现

数据字典

用户信息数据结构的描述

数据结构名：user
说明：用于存储用户的账号信息和相关属性。
组成：u_id，u_name，u_password，u_email，u_sex，u_status，u_code，u_role

地址信息数据结构的描述

数据结构名： address
说明： 用于存储用户的收货地址信息。
组成：a_id，u_id，a_name，a_phone，a_detail，a_state

商品类别数据结构的描述

数据结构名：type
说明：用于存储商品的分类信息。
组成：t_id，t_name，t_info

商品数据结构的描述

数据结构名：product
说明：用于存储具体的商品信息。
组成：p_id，t_id，p_name，p_time，p_image，p_price，p_state，p_info

购物车数据结构的描述

数据结构名：cart
说明：用于存储用户的购物车信息。
组成： c_id，u_id，p_id，c_count，c_num

订单数据结构的描述

数据结构名：orders
说明：用于存储用户的订单信息。
组成： o_id，u_id，a_id，o_count，o_time，o_state

订单项数据结构的描述

数据结构名： item
说明：用于存储订单中每个商品的详细信息。
组成：i_id，o_id，p_id，i_count，i_num

概念模型及逻辑结构

实体图及 E-R 图

E-R 图转换为关系模式

表结构设计

编写程序

视图

视图 1：获取所有商品及其所属类别名称

-- 视图1：获取所有商品及其所属类别名称CREATEVIEW vw_Products ASSELECT p.p_id, p.p_name, p.p_time, p.p_image, p.p_price, p.p_state, p.p_info, t.t_name
FROM product p
JOINtype t ON p.t_id = t.t_id;-- 查询所有商品及其所属类别名称SELECT*FROM vw_Products;

视图 2：获取所有订单以及用户信息和地址详情

-- 视图2：获取所有订单以及用户信息和地址详情CREATEVIEW vw_Orders ASSELECT o.o_id, o.o_count, o.o_time, o.o_state, u.u_name, u.u_email, u.u_sex, a.a_name, a.a_phone, a.a_detail
FROM orders o
JOIN`user` u ON o.u_id = u.u_id
JOIN address a ON o.a_id = a.a_id;-- 查询所有订单以及用户信息和地址详情SELECT*FROM vw_Orders;

视图3：获取用户的购物车内容

-- 视图3：获取用户的购物车内容CREATEVIEW vw_Cart ASSELECT c.c_id, c.u_id, c.p_id, c.c_count, c.c_num, p.p_name, p.p_price, p.p_image
FROM cart c
JOIN product p ON c.p_id = p.p_id;-- 查询指定用户的购物车内容SELECT*FROM vw_Cart WHERE u_id =5;

视图4：获取用户的收货地址列表课程名

-- 视图4：获取用户的收货地址列表CREATEVIEW vw_Addresses ASSELECT a.a_id, a.u_id, a.a_name, a.a_phone, a.a_detail, a.a_state, u.u_name
FROM address a
JOIN`user` u ON a.u_id = u.u_id;-- 查询指定用户的收货地址列表SELECT*FROM vw_Addresses WHERE u_id =4;

视图5：获取用户的订单详情

-- 视图5：获取用户的订单详情CREATEVIEW vw_UserOrders ASSELECT o.o_id, o.o_count, o.o_time, o.o_state, u.u_name, u.u_email, u.u_sex, a.a_name, a.a_phone, a.a_detail
FROM orders o
JOIN`user` u ON o.u_id = u.u_id
JOIN address a ON o.a_id = a.a_id;-- 查询指定用户的订单详情SELECT*FROM vw_UserOrders WHERE u_name ='user4';-- 使用用户名来指定用户

存储过程

存储过程1：添加商品到购物车

-- 存储过程1：添加商品到购物车DELIMITER//CREATEPROCEDURE sp_AddToCart(IN user_id INT,IN product_id INT,IN quantity INT)BEGININSERTINTO cart (u_id, p_id, c_num, c_count)VALUES(user_id, product_id, quantity,(SELECT p_price FROM product WHERE p_id = product_id)* quantity);END//DELIMITER;-- 调用存储过程 sp_AddToCart 将商品添加到购物车CALL sp_AddToCart(4,3,2);-- 查询结果SELECT*FROM cart;

存储过程2：创建订单

-- 存储过程2：创建订单DELIMITER//CREATEPROCEDURE sp_CreateOrder(IN user_id INT,IN address_id INT)BEGINDECLARE order_id VARCHAR(64);SET order_id = UUID();-- 使用 UUID() 函数生成唯一订单编号INSERTINTO orders (o_id, u_id, a_id, o_count, o_time, o_state)SELECT order_id, user_id, address_id,SUM(c.c_count),NOW(),0FROM cart c
    WHERE c.u_id = user_id;DELETEFROM cart WHERE u_id = user_id;-- 清空购物车中该用户的商品END//DELIMITER;-- 调用存储过程 sp_CreateOrder 创建订单CALL sp_CreateOrder(4,4);-- 查询结果SELECT*FROM orders;

存储过程3：更新订单状态

-- 存储过程3：更新订单状态DELIMITER//CREATEPROCEDURE sp_UpdateOrderStatus(IN order_id VARCHAR(64),IN new_state INT)BEGINUPDATE orders
SET o_state = new_state
WHERE o_id = order_id;END//DELIMITER;-- 调用存储过程 sp_UpdateOrderStatus 更新订单状态CALL sp_UpdateOrderStatus('order2',4);-- 查询结果SELECT*FROM orders;

触发器及事务

-- 触发器，在将商品添加到订单时，将相关商品从购物车中移除。-- 同时，我们将在这个过程中使用事务来确保操作的一致性。DELIMITER//CREATETRIGGER trg_AddToOrder
AFTERINSERTON orders
FOR EACH ROWBEGINDECLARE cart_count INT;-- 获取购物车中相应商品的数量SELECT c_num INTO cart_count FROM cart WHERE p_id = NEW.p_id AND u_id = NEW.u_id;-- 如果购物车中有相关商品，则将其移除IF cart_count >0THENDELETEFROM cart WHERE p_id = NEW.p_id AND u_id = NEW.u_id;ENDIF;END;//DELIMITER;-- 事务，将商品添加到购物车并创建订单STARTTRANSACTION;-- 开始事务CALL sp_AddToCart(3,3,2);-- 将商品添加到购物车CALL sp_CreateOrder(3,4);-- 创建订单COMMIT;-- 提交事务，保存更改

事件

-- 创建每日数据备份的事件DELIMITER//CREATE EVENT IFNOTEXISTS daily_data_backup
ON SCHEDULE EVERY 1DAY
STARTS TIMESTAMP(NOW()+INTERVAL1DAY)ON COMPLETION PRESERVE
DOBEGINSET@backup_file_name= CONCAT('backup_', DATE_FORMAT(NOW(),'%Y%m%d_%H%i%s'),'.sql');SET@backup_path='/path/to/backup/directory/';SET@backup_command= CONCAT('mysqldump -u username -pPassword MyShop > ',@backup_path,@backup_file_name);PREPARE stmt FROM@backup_command;EXECUTE stmt;DEALLOCATEPREPARE stmt;INSERTINTO backup_logs (backup_name, backup_time)VALUES(@backup_file_name,NOW());END;//DELIMITER;

数据完整性

定义各表的主键（在建表的时候定义)

-- 定义用户表主键
u_id INTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 定义地址表主键
a_id INTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 定义商品类别表主键
t_id INTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 定义商品表主键
p_id INTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 定义购物车表主键
c_id INTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 定义订单表主键
o_id VARCHAR(64)NOTNULLPRIMARYKEY,-- 定义订单项表主键
i_id INTNOTNULLAUTO_INCREMENTPRIMARYKEY

删除用户表时同时删除 地址表表中相关行的行为

-- 删除 user 表的记录时同时删除 address 表中相关行的行为，可以使用 ON DELETE CASCADE 来定义外键约束。ALTERTABLE address
ADDCONSTRAINT FK_u_a_fk -- 添加一个名为 FK_u_a_fk 的约束FOREIGNKEY(u_id)-- 该约束是针对 u_id 列的外键约束REFERENCESuser(u_id)-- 指定引用的主键表和主键列，这里是 user 表的 u_id 列ONDELETECASCADE-- 指定在删除关联行时自动删除相关行ONUPDATECASCADE;-- 指定在更新关联行时自动更新相应行

将类别表删除或修改时，商品表的类别要同步删除或修改

ALTERTABLE product
ADDCONSTRAINT FK_t_p_fk
FOREIGNKEY(t_id)REFERENCEStype(t_id)ONDELETECASCADEONUPDATECASCADE;

删除或修改用户时，同时删除或修改购物车中与该用户关联的数据

-- 外键约束与用户表 user 的关联ALTERTABLE cart
ADDCONSTRAINT FK_u_c_fk
FOREIGNKEY(u_id)REFERENCES`user`(u_id)ONDELETECASCADEONUPDATECASCADE;

删除商品时，同时删除购物车中与该商品关联的数据

-- 外键约束与商品表 product 的关联ALTERTABLE cart
ADDCONSTRAINT FK_cart_product
FOREIGNKEY(p_id)REFERENCES product (p_id)ONDELETECASCADEONUPDATECASCADE;

在删除用户时，同时删除与该用户关联的订单数据

-- 添加外键约束与 user 表的关联；可以保证在删除用户时，同时删除与该用户关联的订单数据。ALTERTABLE orders
ADDCONSTRAINT FK_u_o_fk
FOREIGNKEY(u_id)REFERENCES`user`(u_id)ONDELETECASCADEONUPDATECASCADE;

orders与 address 表的关联：在删除或更新关联行时不采取任何动作。

ALTERTABLE orders
ADDCONSTRAINT FK_a_o_fk
FOREIGNKEY(a_id)REFERENCES address (a_id)ONDELETENOACTIONONUPDATENOACTION;

实现级联删除或更新。在删除或更新订单时，相关的订单项也会被删除或更新

ALTERTABLE item
ADDCONSTRAINT FK_item_orders
FOREIGNKEY(o_id)REFERENCES orders (o_id)ONDELETECASCADEONUPDATECASCADE;

item与 product 表的关联，在删除或更新关联行时不采取任何动作限制条件}

ALTERTABLE item
ADDCONSTRAINT FK_p_i_fk FOREIGNKEY(p_id)REFERENCES product (p_id)ONDELETENOACTIONONUPDATENOACTION;

数据更新

插入数据

-- 向 user 表插入虚拟数据INSERTINTOuser(u_name, u_password, u_email, u_sex, u_status, u_code, u_role)VALUES('user1','password1','[email protected]','男',1,'code1',1),('user2','password2','[email protected]','女',1,'code2',1),('user3','password3','[email protected]','男',1,'code3',1),('user4','password4','[email protected]','女',1,'code4',1),('user5','password5','[email protected]','男',0,'code5',1),('user6','password6','[email protected]','女',0,'code6',1),('user7','password7','[email protected]','男',1,'code7',1),('user8','password8','[email protected]','女',1,'code8',1),('user9','password9','[email protected]','男',1,'code9',1),('user10','password10','[email protected]','女',1,'code10',1);-- 向 address 表插入虚拟数据INSERTINTO address (u_id, a_name, a_phone, a_detail, a_state)VALUES(1,'user1','1234567890','City1',1),(2,'user2','9876543210','City2',0),(3,'user3','1111111111','City3',1),(4,'user4','2222222222','City4',0),(5,'user5','3333333333','City5',1),(6,'user6','4444444444','City6',0),(7,'user7','5555555555','City7',1),(8,'user8','6666666666','City8',0),(9,'user9','7777777777','City9',1),(10,'user10','8888888888','City10',0);-- 商品类别表插入虚拟数据INSERTINTOtype(t_name, t_info)VALUES('电子产品','包括手机、电脑、平板等电子设备'),('服装','包括男装、女装、童装等各种服装'),('家居用品','包括家具、家饰、厨具等家居用品');-- 商品表插入虚拟数据INSERTINTO product (t_id, p_name, p_time, p_image, p_price, p_state, p_info)VALUES(1,'iPhone 12','2021-01-01','image1.jpg',999.99,10,'最新款iPhone手机'),(1,'MacBook Pro','2021-02-01','image2.jpg',1999.99,8,'高性能笔记本电脑'),(2,'T-shirt','2021-03-01','image3.jpg',19.99,5,'简约款T恤'),(2,'Dress','2021-04-01','image4.jpg',49.99,7,'时尚连衣裙'),(3,'Sofa','2021-05-01','image5.jpg',599.99,6,'舒适沙发'),(3,'Table Lamp','2021-06-01','image6.jpg',29.99,4,'台灯'),(1,'AirPods','2021-07-01','image7.jpg',149.99,9,'无线耳机'),(2,'Jeans','2021-08-01','image8.jpg',39.99,6,'经典牛仔裤'),(2,'Shoes','2021-09-01','image9.jpg',79.99,7,'时尚鞋子'),(3,'Cookware Set','2021-10-01','image10.jpg',199.99,8,'厨具套装');

修改数据

-- 更新 address 表中的数据UPDATE address SET a_name ='newname'WHERE a_id =1;-- 根据类别ID更新类别名称和描述UPDATEtypeSET t_name ='数码产品', t_info ='包括手机、电脑、相机等数码设备'WHERE t_id =1;-- 根据类别名称更新类别描述UPDATEtypeSET t_info ='包括男装、女装、童装等各种时尚服饰'WHERE t_name ='服装';-- 根据商品ID更新商品名称和描述UPDATE product SET p_name ='iPhone 13', p_info ='最新款iPhone手机'WHERE p_id =1;-- 根据商品名称更新商品价格UPDATE product SET p_price =1099.99WHERE p_name ='MacBook Pro';-- 修改购物车中特定用户和商品的数量和小计UPDATE cart SET c_num =3, c_count =15.99WHERE u_id =2AND p_id =1;-- 更新订单状态为已付款UPDATE orders SET o_state =1WHERE o_id ='order1';-- 修改订单项的数量和小计UPDATE item SET i_num =3, i_count =29.99WHERE i_id =1;

删除数据

-- 删除 user 表中的数据DELETEFROMuserWHERE u_id =1;-- 删除 address 表中的数据DELETEFROM address WHERE a_id =2;-- 根据类别名称删除类别DELETEFROMtypeWHERE t_name ='电子产品';-- 根据类别ID删除类别DELETEFROMtypeWHERE t_id =1;-- 删除所有商品DELETEFROM product;-- 根据商品名称删除商品DELETEFROM product WHERE p_name ='iPhone 12';-- 根据商品ID删除商品DELETEFROM product WHERE p_id =1;-- 删除特定用户的购物车记录DELETEFROM cart WHERE u_id =1;-- 删除特定商品的购物车记录DELETEFROM cart WHERE p_id =2;-- 删除订单DELETEFROM orders WHERE o_id ='order1';-- 删除用户ID为2的所有订单DELETEFROM orders WHERE u_id =2;-- 删除指定的订单项DELETEFROM item WHERE i_id =2;

权限管理

由sa给组长授予管理员权限

在这里插入代码片

-- 创建组长账户CREATEUSER'组长'@'localhost' IDENTIFIED BY'password';-- 赋予管理员权限给组长GRANTALLPRIVILEGESON*.*TO'组长'@'localhost'WITHGRANTOPTION;

由组长创建项目角色，并授予项目角色相关权限

-- 由组长创建项目角色，并授予项目角色相关权限CREATE ROLE 项目角色;GRANTSELECT,INSERT,UPDATE,DELETEON MyShop.orders TO 项目角色;

由组长将组员与项目角色捆绑

-- 由组长将组员与项目角色捆绑CREATEUSER'组员'@'localhost' IDENTIFIED BY'password';GRANTSELECT,INSERT,UPDATE,DELETEON MyShop.orders TO'组员'@'localhost';

由组长给组员授予登录本组项目数据库的权限

-- 给予组员登录本组项目数据库的权限GRANTUSAGEON*.*TO'组员'@'localhost';

数据库备份与恢复

数据库备份

要在 MySQL 中备份数据库，可以使用 mysqldump 命令行工具。

-- 备份整个数据库：
mysqldump -u root -pAa2803345646 MyShop >backup.sql

数据库恢复

mysql -u root -p MyShop <backup.sql

前端界面设计

项目代码下载

下载：代码与实验报告（包括平时作业的）