【MySQL 数据库应用】-购物网站系统数据库设计
【MySQL 数据库应用】-购物网站系统数据库设计
- 项目开发背景
- 开发项目的目标及设计要求
- 项目目标
- 设计要求
- 数据库需求分析
- 用例和用例图
- 数据需求
- 处理需求
- 安全性与完整性要求
- 主键约束
- 唯一性约束
- 外键约束
- ON DELETE 和 ON UPDATE 触发动作
- 数据库设计与实现
- 数据字典
- 用户信息数据结构的描述
- 地址信息数据结构的描述
- 商品类别数据结构的描述
- 商品数据结构的描述
- 购物车数据结构的描述
- 订单数据结构的描述
- 订单项数据结构的描述
- 概念模型及逻辑结构
- 实体图及 E-R 图
- E-R 图转换为关系模式
- 表结构设计
- 编写程序
- 视图
- 视图 1：获取所有商品及其所属类别名称
- 视图 2：获取所有订单以及用户信息和地址详情
- 视图3：获取用户的购物车内容
- 视图4：获取用户的收货地址列表课程名
- 视图5：获取用户的订单详情
- 存储过程
- 存储过程1：添加商品到购物车
- 存储过程2：创建订单
- 存储过程3：更新订单状态
- 触发器及事务
- 事件
- 数据完整性
- 定义各表的主键（在建表的时候定义)
- 删除用户表时同时删除 地址表表中相关行的行为
- 将类别表删除或修改时，商品表的类别要同步删除或修改
- 删除或修改用户时，同时删除或修改购物车中与该用户关联的数据
- 删除商品时，同时删除购物车中与该商品关联的数据
- 在删除用户时，同时删除与该用户关联的订单数据
- orders与 address 表的关联：在删除或更新关联行时不采取任何动作。
- 实现级联删除或更新。在删除或更新订单时，相关的订单项也会被删除或更新
- item与 product 表的关联，在删除或更新关联行时不采取任何动作限制条件}
- 数据更新
- 插入数据
- 修改数据
- 删除数据
- 权限管理
- 由sa给组长授予管理员权限
- 由组长创建项目角色，并授予项目角色相关权限
- 由组长将组员与项目角色捆绑
- 由组长给组员授予登录本组项目数据库的权限
- 数据库备份与恢复
- 数据库备份
- 数据库恢复
- 前端界面设计
- 项目代码下载
项目开发背景
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：获取所有商品及其所属类别名称CREATE VIEW 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_nameFROM product pJOIN type t ON p.t_id = t.t_id;-- 查询所有商品及其所属类别名称SELECT * FROM vw_Products;
视图 2：获取所有订单以及用户信息和地址详情
-- 视图2：获取所有订单以及用户信息和地址详情CREATE VIEW 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_detailFROM orders oJOIN `user` u ON o.u_id = u.u_idJOIN address a ON o.a_id = a.a_id;-- 查询所有订单以及用户信息和地址详情SELECT * FROM vw_Orders;
视图3：获取用户的购物车内容
-- 视图3：获取用户的购物车内容CREATE VIEW 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_imageFROM cart cJOIN product p ON c.p_id = p.p_id;-- 查询指定用户的购物车内容SELECT * FROM vw_Cart WHERE u_id = 5;
视图4：获取用户的收货地址列表课程名
-- 视图4：获取用户的收货地址列表CREATE VIEW vw_Addresses ASSELECT a.a_id, a.u_id, a.a_name, a.a_phone, a.a_detail, a.a_state, u.u_nameFROM address aJOIN `user` u ON a.u_id = u.u_id;-- 查询指定用户的收货地址列表SELECT * FROM vw_Addresses WHERE u_id = 4;
视图5：获取用户的订单详情
-- 视图5：获取用户的订单详情CREATE VIEW 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_detailFROM orders oJOIN `user` u ON o.u_id = u.u_idJOIN address a ON o.a_id = a.a_id;-- 查询指定用户的订单详情SELECT * FROM vw_UserOrders WHERE u_name = 'user4'; -- 使用用户名来指定用户
存储过程
存储过程1：添加商品到购物车
-- 存储过程1：添加商品到购物车DELIMITER //CREATE PROCEDURE sp_AddToCart(IN user_id INT,IN product_id INT,IN quantity INT)BEGININSERT INTO 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 //CREATE PROCEDURE sp_CreateOrder(IN user_id INT,IN address_id INT)BEGINDECLARE order_id VARCHAR(64);SET order_id = UUID(); -- 使用 UUID() 函数生成唯一订单编号INSERT INTO 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 cWHERE c.u_id = user_id;DELETE FROM cart WHERE u_id = user_id; -- 清空购物车中该用户的商品END//DELIMITER ;-- 调用存储过程 sp_CreateOrder 创建订单CALL sp_CreateOrder(4, 4); -- 查询结果SELECT * FROM orders;
存储过程3：更新订单状态
-- 存储过程3：更新订单状态DELIMITER //CREATE PROCEDURE sp_UpdateOrderStatus(IN order_id VARCHAR(64),IN new_state INT)BEGINUPDATE ordersSET o_state = new_stateWHERE o_id = order_id;END//DELIMITER ;-- 调用存储过程 sp_UpdateOrderStatus 更新订单状态CALL sp_UpdateOrderStatus('order2', 4); -- 查询结果SELECT * FROM orders;触发器及事务
-- 触发器，在将商品添加到订单时，将相关商品从购物车中移除。-- 同时，我们将在这个过程中使用事务来确保操作的一致性。DELIMITER //CREATE TRIGGER trg_AddToOrderAFTER INSERT ON ordersFOR 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 > 0 THENDELETE FROM cart WHERE p_id = NEW.p_id AND u_id = NEW.u_id;END IF;END;//DELIMITER ;-- 事务，将商品添加到购物车并创建订单START TRANSACTION; -- 开始事务CALL sp_AddToCart(3, 3, 2); -- 将商品添加到购物车CALL sp_CreateOrder(3, 4); -- 创建订单COMMIT; -- 提交事务，保存更改
事件
-- 创建每日数据备份的事件DELIMITER //CREATE EVENT IF NOT EXISTS daily_data_backupON SCHEDULE EVERY 1 DAYSTARTS TIMESTAMP(NOW() + INTERVAL 1 DAY)ON COMPLETION PRESERVEDOBEGINSET @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;DEALLOCATE PREPARE stmt;INSERT INTO backup_logs (backup_name, backup_time) VALUES (@backup_file_name, NOW());END;//DELIMITER ;数据完整性
定义各表的主键（在建表的时候定义)
-- 定义用户表主键u_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 定义地址表主键a_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 定义商品类别表主键t_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 定义商品表主键p_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 定义购物车表主键c_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,-- 定义订单表主键o_id VARCHAR(64) NOT NULL PRIMARY KEY,-- 定义订单项表主键i_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
删除用户表时同时删除 地址表表中相关行的行为
-- 删除 user 表的记录时同时删除 address 表中相关行的行为，可以使用 ON DELETE CASCADE 来定义外键约束。ALTER TABLE addressADD CONSTRAINT FK_u_a_fk -- 添加一个名为 FK_u_a_fk 的约束FOREIGN KEY (u_id) -- 该约束是针对 u_id 列的外键约束REFERENCES user (u_id) -- 指定引用的主键表和主键列，这里是 user 表的 u_id 列ON DELETE CASCADE -- 指定在删除关联行时自动删除相关行ON UPDATE CASCADE; -- 指定在更新关联行时自动更新相应行
将类别表删除或修改时，商品表的类别要同步删除或修改
ALTER TABLE productADD CONSTRAINT FK_t_p_fkFOREIGN KEY (t_id)REFERENCES type (t_id)ON DELETE CASCADEON UPDATE CASCADE;
删除或修改用户时，同时删除或修改购物车中与该用户关联的数据
-- 外键约束与用户表 user 的关联ALTER TABLE cartADD CONSTRAINT FK_u_c_fkFOREIGN KEY (u_id)REFERENCES `user`(u_id)ON DELETE CASCADEON UPDATE CASCADE;
删除商品时，同时删除购物车中与该商品关联的数据
-- 外键约束与商品表 product 的关联ALTER TABLE cartADD CONSTRAINT FK_cart_productFOREIGN KEY (p_id)REFERENCES product (p_id)ON DELETE CASCADEON UPDATE CASCADE;
在删除用户时，同时删除与该用户关联的订单数据
-- 添加外键约束与 user 表的关联；可以保证在删除用户时，同时删除与该用户关联的订单数据。ALTER TABLE ordersADD CONSTRAINT FK_u_o_fkFOREIGN KEY (u_id)REFERENCES `user` (u_id)ON DELETE CASCADEON UPDATE CASCADE;
orders与 address 表的关联：在删除或更新关联行时不采取任何动作。
ALTER TABLE ordersADD CONSTRAINT FK_a_o_fkFOREIGN KEY (a_id)REFERENCES address (a_id)ON DELETE NO ACTIONON UPDATE NO ACTION;
实现级联删除或更新。在删除或更新订单时，相关的订单项也会被删除或更新
ALTER TABLE itemADD CONSTRAINT FK_item_ordersFOREIGN KEY (o_id)REFERENCES orders (o_id)ON DELETE CASCADEON UPDATE CASCADE;
item与 product 表的关联，在删除或更新关联行时不采取任何动作限制条件}
ALTER TABLE itemADD CONSTRAINT FK_p_i_fk FOREIGN KEY (p_id)REFERENCES product (p_id)ON DELETE NO ACTIONON UPDATE NO ACTION;
数据更新
插入数据
-- 向 user 表插入虚拟数据INSERT INTO user (u_name, u_password, u_email, u_sex, u_status, u_code, u_role)VALUES('user1', 'password1', 'user1@example.com', '男', 1, 'code1', 1),('user2', 'password2', 'user2@example.com', '女', 1, 'code2', 1),('user3', 'password3', 'user3@example.com', '男', 1, 'code3', 1),('user4', 'password4', 'user4@example.com', '女', 1, 'code4', 1),('user5', 'password5', 'user5@example.com', '男', 0, 'code5', 1),('user6', 'password6', 'user6@example.com', '女', 0, 'code6', 1),('user7', 'password7', 'user7@example.com', '男', 1, 'code7', 1),('user8', 'password8', 'user8@example.com', '女', 1, 'code8', 1),('user9', 'password9', 'user9@example.com', '男', 1, 'code9', 1),('user10', 'password10', 'user10@example.com', '女', 1, 'code10', 1);-- 向 address 表插入虚拟数据INSERT INTO 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);-- 商品类别表插入虚拟数据INSERT INTO type (t_name, t_info)VALUES ('电子产品', '包括手机、电脑、平板等电子设备'),('服装', '包括男装、女装、童装等各种服装'),('家居用品', '包括家具、家饰、厨具等家居用品');-- 商品表插入虚拟数据INSERT INTO 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更新类别名称和描述UPDATE type SET t_name = '数码产品', t_info = '包括手机、电脑、相机等数码设备' WHERE t_id = 1;-- 根据类别名称更新类别描述UPDATE type SET 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.99 WHERE p_name = 'MacBook Pro';-- 修改购物车中特定用户和商品的数量和小计UPDATE cart SET c_num = 3, c_count = 15.99 WHERE u_id = 2 AND p_id = 1;-- 更新订单状态为已付款UPDATE orders SET o_state = 1 WHERE o_id = 'order1';-- 修改订单项的数量和小计UPDATE item SET i_num = 3, i_count = 29.99 WHERE i_id = 1;
删除数据
-- 删除 user 表中的数据DELETE FROM user WHERE u_id = 1;-- 删除 address 表中的数据DELETE FROM address WHERE a_id = 2;-- 根据类别名称删除类别DELETE FROM type WHERE t_name = '电子产品';-- 根据类别ID删除类别DELETE FROM type WHERE t_id = 1;-- 删除所有商品DELETE FROM product;-- 根据商品名称删除商品DELETE FROM product WHERE p_name = 'iPhone 12';-- 根据商品ID删除商品DELETE FROM product WHERE p_id = 1;-- 删除特定用户的购物车记录DELETE FROM cart WHERE u_id = 1;-- 删除特定商品的购物车记录DELETE FROM cart WHERE p_id = 2;-- 删除订单DELETE FROM orders WHERE o_id = 'order1';-- 删除用户ID为2的所有订单DELETE FROM orders WHERE u_id = 2;-- 删除指定的订单项DELETE FROM item WHERE i_id = 2;
权限管理
由sa给组长授予管理员权限
在这里插入代码片
-- 创建组长账户CREATE USER '组长'@'localhost' IDENTIFIED BY 'password';-- 赋予管理员权限给组长GRANT ALL PRIVILEGES ON *.* TO '组长'@'localhost' WITH GRANT OPTION;
由组长创建项目角色，并授予项目角色相关权限
-- 由组长创建项目角色，并授予项目角色相关权限CREATE ROLE 项目角色;GRANT SELECT, INSERT, UPDATE, DELETE ON MyShop.orders TO 项目角色;
由组长将组员与项目角色捆绑
-- 由组长将组员与项目角色捆绑CREATE USER '组员'@'localhost' IDENTIFIED BY 'password';GRANT SELECT, INSERT, UPDATE, DELETE ON MyShop.orders TO '组员'@'localhost';
由组长给组员授予登录本组项目数据库的权限
-- 给予组员登录本组项目数据库的权限GRANT USAGE ON *.* TO '组员'@'localhost';
数据库备份与恢复
数据库备份
要在 MySQL 中备份数据库，可以使用 mysqldump 命令行工具。
-- 备份整个数据库：mysqldump -u root -pAa2803345646 MyShop > backup.sql
数据库恢复
mysql -u root -p MyShop
前端界面设计
项目代码下载
下载：代码与实验报告（包括平时作业的）
- 用户表（{user}）：{u_name} 列被定义为唯一性约束。