sqlserver 【数据库原理】MyShop 商城数据库设计（SQL server）

MyShop 商城数据库设计

项目背景定义课程设计要求概念结构设计逻辑结构设计数据结构的描述用户信息数据结构的描述地址信息数据结构的描述商品类别数据结构的描述商品数据结构的描述购物车数据结构的描述订单数据结构的描述订单项数据结构的描述

物理结构设计用户表结构地址表结构商品类别表结构商品表结构购物车表结构订单表结构订单项表结构各表之间的关系图编写视图及存储过程视图1：获取所有商品及其所属类别名称视图2：获取所有订单以及用户信息和地址详情视图3：获取用户的购物车内容视图4：获取用户的收货地址列表课程名)视图5：获取用户的订单详情存储过程1：添加商品到购物车存储过程2：创建订单存储过程3：更新订单状态

数据完整性删除用户表时同时删除地址表表中相关行的行为

完整代码项目代码及报告下载

声明：未经允许，请勿转载

项目背景

MyShop商城是一个在线购物平台，致力于提供便捷的购物体验。为了满足用户需求，商城需要一个可靠、高效的数据库系统来管理商品、用户和订单信息。数据库系统应具备性能、可靠性和扩展性，并通过合理的设计和优化提高系统的响应速度和数据一致性。目标是设计和实现MyShop商城的数据库系统，提供良好的购物体验。

定义

在项目中，我们需要明确一些关键概念和术语的定义，以便在数据库设计和开发过程中保持一致性和清晰性。以下是一些重要的定义：

用户表（user）：存储用户的基本信息，包括用户ID、账号、密码、邮箱、性别、激活状态和角色。地址表（address）：存储用户的收货地址信息，包括地址ID、用户ID、收件人、联系电话、详细地址和默认地址状态。商品类别表（type）：存储商品的类别信息，包括类别ID、类别名称和描述。商品表（product）：存储商品的详细信息，包括商品ID、类别ID、商品名称、上市时间、商品图片路径、价格、热门指数和描述。购物车表（cart）：存储用户的购物车信息，包括购物车ID、用户ID、商品ID、小计金额和商品数量。订单表（orders）：存储用户的订单信息，包括订单编号、用户ID、地址ID、总金额、下单时间和订单状态。订单项表（item）：存储订单中每个商品的详细信息，包括订单项ID、订单编号、商品ID、小计金额和商品数量

课程设计要求

构造较优的数据库模式，规范化地建立数据库应用系统、5 个视图与 3 给存储过程，使之能够有效地、安全地存储数据（每个表至少录入 10 条记录，并包含本组成员的相关信息），满足用户的信息处理需求

概念结构设计

逻辑结构设计

用户表关系模式：用户（用户编号，用户名，用户密码，用户邮箱，用户性别，用户状态，激活码，用户角色）地址表关系模式：地址（地址编号，用户编号，收件人姓名，收件人电话，详细地址，是否默认地址）商品类别表关系模式：商品类别（类别编号，类别名称，类别描述）商品表关系模式：商品（商品编号，类别编号，商品名称，上市时间，商品图片路径，商品价格，热门指数，商品描述）购物车表关系模式：购物车（购物车编号，用户编号，商品编号，小计金额，商品数量）订单表关系模式：订单（订单编号，用户编号，地址编号，总金额，下单时间，订单状态）订单项表关系模式：订单项（订单项编号，订单编号，商品编号，小计金额，商品数量）

这些关系模式描述了数据库中的表结构和各个表之间的关系。每个关系模式使用中文描述了表中的字段含义和它们的数据类型。通过这些关系模式，可以了解每个表的字段含义和它们之间的关联关系。

数据结构的描述

用户信息数据结构的描述

数据结构名： 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

物理结构设计

数据库物理设计是后半段。将一个给定逻辑结构实施到具体的环境中时，逻辑数据模型要选取一个具体的工作环境，这个工作环境提供了数据存储结构与存取方法，这个过程就是数据库的物理设计

用户表结构

地址表结构

商品类别表结构

商品表结构

购物车表结构

订单表结构

订单项表结构

各表之间的关系图

编写视图及存储过程

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

CREATE VIEW vw_Products

SELECT 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

JOIN type t ON p.t_id = t.t_id;

-- 使用视图 vw_Products 查询所有商品及其所属类别名称

SELECT * FROM vw_Products;

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

CREATE VIEW vw_Orders

SELECT 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;

-- 查询视图 vw_Orders 中的所有订单以及用户信息和地址详情

SELECT * FROM vw_Orders;

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

CREATE VIEW vw_Cart

SELECT 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;

-- 查询视图 vw_Cart 中指定用户的购物车内容

SELECT * FROM vw_Cart WHERE u_id = 5;

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

CREATE VIEW vw_Addresses

SELECT 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;

-- 查询视图 vw_Addresses 中指定用户的收货地址列表

SELECT *

FROM vw_Addresses

WHERE u_id = 4;

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

CREATE VIEW vw_UserOrders

SELECT 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

-- 查询视图 vw_UserOrders 中指定用户的订单详情

SELECT *

FROM vw_UserOrders

WHERE u_name = 'user4'; -- 使用用户名来指定用户

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

CREATE PROCEDURE sp_AddToCart

@user_id INT,

@product_id INT,

@quantity INT

BEGIN

INSERT 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;

-- 调用存储过程 sp_AddToCart 将商品添加到购物车

EXEC sp_AddToCart @user_id = 4, @product_id = 3, @quantity = 2;

--查询结果

SELECT * FROM cart;

存储过程2：创建订单

CREATE PROCEDURE sp_CreateOrder

@user_id INT,

@address_id INT

BEGIN

DECLARE @order_id VARCHAR(64);

SET @order_id = CONCAT('ORD', REPLACE(CONVERT(VARCHAR(30), GETDATE(), 121), ':', ''));-- 生成订单编号，格式为ORD+当前时间的字符串表示（去除冒号）

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), GETDATE(), 0

FROM cart c

WHERE c.u_id = @user_id;

DELETE FROM cart WHERE u_id = @user_id; -- 清空购物车中该用户的商品

END;

-- 调用存储过程 sp_CreateOrder 创建订单

EXEC sp_CreateOrder @user_id = 4, @address_id = 14;

--查询结果

SELECT * FROM orders;

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

CREATE PROCEDURE sp_UpdateOrderStatus

@order_id VARCHAR(64),

@new_state INT

BEGIN

UPDATE orders

SET o_state = @new_state

WHERE o_id = @order_id;

END;

-- 调用存储过程 sp_UpdateOrderStatus 更新订单状态

EXEC sp_UpdateOrderStatus @order_id = 'order2', @new_state = 4;

--查询结果

SELECT * FROM orders;

数据完整性

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

ALTER TABLE address

ADD 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; -- 指定在更新关联行时自动更新相应行

完整代码

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'MyShop')

DROP DATABASE MyShop; -- 如果数据库存在，则删除数据库

CREATE DATABASE MyShop; -- 创建数据库

USE MyShop; -- 使用MyShop数据库

IF OBJECT_ID('address', 'U') IS NOT NULL

DROP TABLE address; -- 如果地址表存在，则删除地址表

IF OBJECT_ID('cart', 'U') IS NOT NULL

DROP TABLE cart; -- 如果购物车表存在，则删除购物车表

IF OBJECT_ID('item', 'U') IS NOT NULL

DROP TABLE item; -- 如果订单项表存在，则删除订单项表

IF OBJECT_ID('orders', 'U') IS NOT NULL

DROP TABLE orders; -- 如果订单表存在，则删除订单表

IF OBJECT_ID('product', 'U') IS NOT NULL

DROP TABLE product; -- 如果商品表存在，则删除商品表

IF OBJECT_ID('type', 'U') IS NOT NULL

DROP TABLE type; -- 如果类别表存在，则删除类别表

IF OBJECT_ID('[user]', 'U') IS NOT NULL

DROP TABLE [user]; -- 如果用户表存在，则删除用户表

--用户表

CREATE TABLE [user]

(

u_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- 用户实体的主键属性

u_name VARCHAR(20) NOT NULL, -- 用户账号

u_password VARCHAR(64) NOT NULL, -- 用户密码

u_email VARCHAR(50) NOT NULL, -- 用户的邮箱！用于激活使用！

u_sex VARCHAR(4), -- 用户性别！

u_status INT, -- 用户的激活状态 0 未激活 1 激活

u_code VARCHAR(64), -- 邮件激活码

u_role INT -- 用户 0 管理员 1

);

-- 向 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);

-- 查询 user 表中的所有数据

SELECT * FROM [user];

-- 更新 user 表中的数据

UPDATE [user]

SET u_password = 'newpassword'

WHERE u_id = 1;

-- 删除 user 表中的数据

DELETE FROM [user]

WHERE u_id = 1;

--地址表

CREATE TABLE address

(

a_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- 地址实体的唯一主键列

u_id INT, -- 用户实体的主键属性

a_name VARCHAR(30), -- 地址的收件人

a_phone VARCHAR(14), -- 收件人电话

a_detail VARCHAR(200), -- 收货人详细地址

a_state INT CHECK (a_state IN (0, 1)) -- 是否是默认地址 0 不是， 1 是默认地址，限制"a_state"的值为0或1

);

--用于修改 address 表的，添加了一个名为 FK_u_a_fk 的外键约束

--删除 user 表的记录时同时删除 address 表中相关行的行为，可以使用 ON DELETE CASCADE 来定义外键约束。

ALTER TABLE address

ADD 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; -- 指定在更新关联行时自动更新相应行

-- 向 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);

-- 查询 address 表中的所有数据

SELECT * FROM address;

-- 更新 address 表中的数据

UPDATE address

SET a_name = 'newname'

WHERE a_id = 1;

-- 删除 address 表中的数据

DELETE FROM address

WHERE a_id = 2;

--商品类别表

CREATE TABLE type

(

t_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- 类别的主键id

t_name VARCHAR(20), -- 类别的名称

t_info VARCHAR(200) -- 类别的描述

);

-- 商品类别表插入虚拟数据

INSERT INTO type (t_name, t_info)

VALUES ('电子产品', '包括手机、电脑、平板等电子设备'),

('服装', '包括男装、女装、童装等各种服装'),

('家居用品', '包括家具、家饰、厨具等家居用品');

-- 查询所有类别

SELECT * FROM type;

-- 根据类别名称查询类别

SELECT * FROM type WHERE t_name = '电子产品';

-- 根据类别ID查询类别

SELECT * FROM type WHERE t_id = 1;

-- 删除所有类别

DELETE FROM type;

-- 根据类别名称删除类别

DELETE FROM type WHERE t_name = '电子产品';

-- 根据类别ID删除类别

DELETE FROM type WHERE t_id = 1;

-- 根据类别ID更新类别名称和描述

UPDATE type SET t_name = '数码产品', t_info = '包括手机、电脑、相机等数码设备' WHERE t_id = 1;

-- 根据类别名称更新类别描述

UPDATE type SET t_info = '包括男装、女装、童装等各种时尚服饰' WHERE t_name = '服装';

--商品表

CREATE TABLE product

(

p_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- 商品的唯一主键

t_id INT, -- 类别的主键id

p_name VARCHAR(50), -- 商品的名称

p_time DATE, -- 商品的上市时间

p_image VARCHAR(100), -- 商品图片的路径

p_price DECIMAL(12, 2), -- 商品的价格

p_state INT, -- 商品的热门指数

p_info VARCHAR(200) -- 商品的描述

);

ALTER TABLE product

ADD CONSTRAINT FK_t_p_fk

FOREIGN KEY (t_id)

REFERENCES type (t_id)

ON DELETE CASCADE

ON UPDATE CASCADE;

-- 商品表插入虚拟数据

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, '厨具套装');

-- 查询所有商品

SELECT * FROM product;

-- 根据商品名称查询商品

SELECT * FROM product WHERE p_name = 'iPhone 12';

-- 根据商品ID查询商品

SELECT * FROM product WHERE p_id = 1;

-- 删除所有商品

DELETE FROM product;

-- 根据商品名称删除商品

DELETE FROM product WHERE p_name = 'iPhone 12';

-- 根据商品ID删除商品

DELETE FROM product WHERE p_id = 1;

-- 根据商品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';

--购物车

CREATE TABLE cart

(

c_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- 购物车的唯一标识自增的主键列

u_id INT, -- 用户实体的主键属性

p_id INT, -- 商品的唯一主键

c_count DECIMAL(12, 2), -- 购物车小计

c_num INT -- 购物车商品数量

);

--外键约束与用户表 user 的关联：这样设置的外键约束可以保证在删除用户时，同时删除购物车中与该用户关联的数据。

ALTER TABLE cart

ADD CONSTRAINT FK_u_c_fk

FOREIGN KEY (u_id)

REFERENCES [user](u_id)

ON DELETE CASCADE

ON UPDATE CASCADE;

--外键约束与商品表 product 的关联：这样设置的外键约束可以保证在删除商品时，同时删除购物车中与该商品关联的数据

ALTER TABLE cart

ADD CONSTRAINT FK_cart_product

FOREIGN KEY (p_id)

REFERENCES product (p_id)

ON DELETE CASCADE

ON UPDATE CASCADE;

-- 向 cart 表插入虚拟数据

INSERT INTO cart (u_id, p_id, c_count, c_num)

VALUES

(2, 1, 10.99, 2),

(2, 3, 24.99, 1),

(4, 2, 15.99, 3),

(5, 1, 10.99, 1),

(6, 3, 24.99, 2),

(7, 2, 15.99, 1),

(8, 1, 10.99, 3),

(9, 3, 24.99, 1),

(10, 2, 15.99, 2);

-- 查询所有购物车记录

SELECT * FROM cart;

-- 查询特定用户的购物车记录

SELECT * FROM cart WHERE u_id = 2;

-- 查询特定商品的购物车记录

SELECT * FROM cart WHERE p_id = 2;

-- 删除特定用户的购物车记录

DELETE FROM cart WHERE u_id = 1;

-- 删除特定商品的购物车记录

DELETE FROM cart WHERE p_id = 2;

-- 清空购物车表的所有记录

DELETE FROM cart;

-- 修改购物车中特定用户和商品的数量和小计

UPDATE cart SET c_num = 3, c_count = 15.99 WHERE u_id = 2 AND p_id = 1;

--订单表

CREATE TABLE orders

(

o_id VARCHAR(64) NOT NULL PRIMARY KEY, -- 订单编号是字符串类型但是也是唯一标识主键。

u_id INT, -- 用户实体的主键属性

a_id INT, -- 地址实体的唯一主键列

o_count DECIMAL(12, 2), -- 订单的总金额

o_time DATETIME, -- 订单的详细时间

o_state INT -- 订单状态 0 未付款，1 已经付款未发货 2 发货待收货 3 收货待评价 4 订单完成 5 退货状态

);

-- 添加外键约束与 user 表的关联；可以保证在删除用户时，同时删除与该用户关联的订单数据。

ALTER TABLE orders

ADD CONSTRAINT FK_u_o_fk

FOREIGN KEY (u_id)

REFERENCES [user] (u_id)

ON DELETE CASCADE

ON UPDATE CASCADE;

--外键约束与 address 表的关联：在删除或更新关联行时不采取任何动作。这样可以避免循环引用的问题。

ALTER TABLE orders

ADD CONSTRAINT FK_a_o_fk

FOREIGN KEY (a_id)

REFERENCES address (a_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

INSERT INTO orders (o_id, u_id, a_id, o_count, o_time, o_state)

VALUES ('order1', 4, 4, 100.00, '2023-06-01 10:00:00', 0),

('order2', 5, 5, 150.00, '2023-06-02 12:30:00', 1),

('order3', 6, 6, 200.00, '2023-06-03 15:45:00', 2),

('order4', 7, 7, 120.00, '2023-06-04 09:15:00', 3),

('order5', 8, 8, 180.00, '2023-06-05 14:00:00', 4);

-- 查询所有订单

SELECT * FROM orders;

-- 根据订单编号查询订单

SELECT * FROM orders WHERE o_id = 'order1';

-- 根据用户ID查询订单

SELECT * FROM orders WHERE u_id = 4;

-- 根据订单状态查询订单

SELECT * FROM orders WHERE o_state = 2;

-- 更新订单状态为已付款

UPDATE orders SET o_state = 1 WHERE o_id = 'order1';

-- 删除订单

DELETE FROM orders WHERE o_id = 'order1';

-- 删除用户ID为2的所有订单

DELETE FROM orders WHERE u_id = 2;

-- 清空订单表

DELETE FROM orders;

--订单项表，用于存储订单中每个商品的详细信息。每个订单可以包含多个订单项，每个订单项对应一个具体的商品。

CREATE TABLE item

(

i_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, -- 订单项的唯一标识使用 IDENTITY(1,1) 来指定自增长属性并作为主键。

o_id VARCHAR(64), -- 订单编号是字符串类型但是也是唯一标识

p_id INT, -- 商品的唯一主键

i_count DECIMAL(12, 2), -- 订单项的小计

i_num INT -- 订单项的数量

);

-- 外键约束与订单表 orders 的关联：实现级联删除或更新。在删除或更新订单时，相关的订单项也会被删除或更新。

ALTER TABLE item

ADD CONSTRAINT FK_item_orders

FOREIGN KEY (o_id)

REFERENCES orders (o_id)

ON DELETE CASCADE

ON UPDATE CASCADE;

-- 外键约束与 product 表的关联：外键列：p_id 引用表：product 引用列：p_id 动作限制条件：在删除或更新关联行时不采取任何动作限制条件

ALTER TABLE item

ADD CONSTRAINT FK_p_i_fk FOREIGN KEY (p_id)

REFERENCES product (p_id)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

-- 向 item 表插入5条虚拟数据

INSERT INTO item (o_id, p_id, i_count, i_num)

VALUES

('order1', 1, 10.99, 2),

('order1', 2, 5.99, 1),

('order2', 3, 15.99, 3),

('order3', 1, 8.99, 2),

('order3', 4, 12.99, 1);

-- 修改订单项的数量和小计

UPDATE item SET i_num = 3, i_count = 29.99 WHERE i_id = 1;

-- 删除指定的订单项

DELETE FROM item WHERE i_id = 2;

-- 查询所有订单项

SELECT * FROM item;

-- 根据订单编号查询订单项

SELECT * FROM item WHERE o_id = 'order3';

-- 根据商品ID查询订单项

SELECT * FROM item WHERE p_id = 1;

--视图 5个，存储过程3个

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

CREATE VIEW vw_Products

SELECT 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

JOIN type t ON p.t_id = t.t_id;

-- 使用视图 vw_Products 查询所有商品及其所属类别名称

SELECT * FROM vw_Products;

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

CREATE VIEW vw_Orders

SELECT 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;

-- 查询视图 vw_Orders 中的所有订单以及用户信息和地址详情

SELECT * FROM vw_Orders;

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

CREATE VIEW vw_Cart

SELECT 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;

-- 查询视图 vw_Cart 中指定用户的购物车内容

SELECT * FROM vw_Cart WHERE u_id = 5;

-- 视图4：获取用户的收货地址列表

CREATE VIEW vw_Addresses

SELECT 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;

-- 查询视图 vw_Addresses 中指定用户的收货地址列表

SELECT *

FROM vw_Addresses

WHERE u_id = 4;

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

CREATE VIEW vw_UserOrders

SELECT 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

-- 查询视图 vw_UserOrders 中指定用户的订单详情

SELECT *

FROM vw_UserOrders

WHERE u_name = 'user4'; -- 使用用户名来指定用户

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

CREATE PROCEDURE sp_AddToCart

@user_id INT,

@product_id INT,

@quantity INT

BEGIN

INSERT 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;

-- 调用存储过程 sp_AddToCart 将商品添加到购物车

EXEC sp_AddToCart @user_id = 4, @product_id = 3, @quantity = 2;

--查询结果

SELECT * FROM cart;

-- 存储过程2：创建订单

CREATE PROCEDURE sp_CreateOrder

@user_id INT,

@address_id INT

BEGIN

DECLARE @order_id VARCHAR(64);

SET @order_id = CONCAT('ORD', REPLACE(CONVERT(VARCHAR(30), GETDATE(), 121), ':', ''));-- 生成订单编号，格式为ORD+当前时间的字符串表示（去除冒号）

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), GETDATE(), 0

FROM cart c

WHERE c.u_id = @user_id;

DELETE FROM cart WHERE u_id = @user_id; -- 清空购物车中该用户的商品

END;

-- 调用存储过程 sp_CreateOrder 创建订单

EXEC sp_CreateOrder @user_id = 4, @address_id = 14;

--查询结果

SELECT * FROM orders;

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

CREATE PROCEDURE sp_UpdateOrderStatus

@order_id VARCHAR(64),

@new_state INT

BEGIN

UPDATE orders

SET o_state = @new_state

WHERE o_id = @order_id;

END;

-- 调用存储过程 sp_UpdateOrderStatus 更新订单状态

EXEC sp_UpdateOrderStatus @order_id = 'order2', @new_state = 4;

--查询结果

SELECT * FROM orders;

--权限控制

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

EXEC sp_addrole '组长'; --创建组长角色

-- 给组长授予管理员权限

ALTER ROLE db_owner ADD MEMBER 组长;

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

CREATE ROLE 项目角色;

GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO 项目角色;

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

CREATE LOGIN [组员] WITH PASSWORD = 'password';

USE MyShop;

CREATE USER [组员] FOR LOGIN [组员];

EXEC sp_addrolemember '项目角色', '组员';

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

USE [MyShop];

ALTER ROLE [db_datareader] ADD MEMBER [组员];

ALTER ROLE [db_datawriter] ADD MEMBER [组员];

--授予组员对目标数据库的读取和写入权限

项目代码及报告下载

下载 https://download.csdn.net/download/weixin_66397563/87978059

参考链接

评论可见，请评论后查看内容，谢谢！！！

您阅读本篇文章共花了：

金钥匙

sqlserver 【数据库原理】MyShop 商城数据库设计（SQL server）

sqlserver microsoft 【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤

数据库 oracle 大数据数据分析【SQL应知应会】表分区（一）• MySQL版

发表评论取消回复

金钥匙

sqlserver 【数据库原理】MyShop 商城数据库设计（SQL server）

sqlserver microsoft 【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤

数据库 oracle 大数据 数据分析 【SQL应知应会】表分区（一）• MySQL版

相关文章

发表评论取消回复

数据库 oracle 大数据数据分析【SQL应知应会】表分区（一）• MySQL版