【数据库原理】MyShop 商城数据库设计（SQL server）-Toy模板网

这篇具有很好参考价值的文章主要介绍了【数据库原理】MyShop 商城数据库设计（SQL server）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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

项目背景

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 条记录，并包含本组成员的相关信息），满足用户的信息处理需求

概念结构设计

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

逻辑结构设计

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

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

数据结构的描述

用户信息数据结构的描述

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

物理结构设计

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

用户表结构

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

地址表结构

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

商品类别表结构

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

商品表结构

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

购物车表结构

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

订单表结构

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

订单项表结构

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

各表之间的关系图

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

编写视图及存储过程

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

CREATE VIEW vw_Products
AS
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
AS
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
AS
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
AS
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
AS
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
AS
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
AS
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
AS
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; -- 创建数据库
GO

USE MyShop; -- 使用MyShop数据库
GO

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
AS
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
AS
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
AS
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
AS
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
AS
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
AS
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
AS
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
AS
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 [组员];

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