0
0

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

MyShop 商城数据库设计

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

项目背景

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

定义

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

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

课程设计要求

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

概念结构设计

在这里插入图片描述

逻辑结构设计

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

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

数据结构的描述

用户信息数据结构的描述

数据结构名： 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：获取所有商品及其所属类别名称

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;-- 使用视图 vw_Products 查询所有商品及其所属类别名称SELECT*FROM vw_Products;

视图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;-- 查询视图 vw_Orders 中的所有订单以及用户信息和地址详情SELECT*FROM vw_Orders;

视图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;-- 查询视图 vw_Cart 中指定用户的购物车内容SELECT*FROM vw_Cart WHERE u_id =5;

视图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;-- 查询视图 vw_Addresses 中指定用户的收货地址列表SELECT*FROM vw_Addresses
WHERE u_id =4;

视图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

-- 查询视图 vw_UserOrders 中指定用户的订单详情SELECT*FROM vw_UserOrders
WHERE u_name ='user4';-- 使用用户名来指定用户

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

CREATEPROCEDURE sp_AddToCart
    @user_idINT,@product_idINT,@quantityINTASBEGININSERTINTO 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：创建订单

CREATEPROCEDURE sp_CreateOrder
    @user_idINT,@address_idINTASBEGINDECLARE@order_idVARCHAR(64);SET@order_id= CONCAT('ORD',REPLACE(CONVERT(VARCHAR(30), GETDATE(),121),':',''));-- 生成订单编号，格式为ORD+当前时间的字符串表示（去除冒号）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), GETDATE(),0FROM cart c
    WHERE c.u_id =@user_id;DELETEFROM cart WHERE u_id =@user_id;-- 清空购物车中该用户的商品END;-- 调用存储过程 sp_CreateOrder 创建订单EXEC sp_CreateOrder @user_id=4,@address_id=14;--查询结果SELECT*FROM orders;

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

CREATEPROCEDURE sp_UpdateOrderStatus
    @order_idVARCHAR(64),@new_stateINTASBEGINUPDATE orders
    SET o_state =@new_stateWHERE o_id =@order_id;END;-- 调用存储过程 sp_UpdateOrderStatus 更新订单状态EXEC sp_UpdateOrderStatus @order_id='order2',@new_state=4;--查询结果SELECT*FROM orders;

数据完整性

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

ALTERTABLE address
ADDCONSTRAINT FK_u_a_fk -- 添加一个名为 FK_u_a_fk 的约束FOREIGNKEY(u_id)-- 该约束是针对 u_id 列的外键约束REFERENCES[user](u_id)-- 指定引用的主键表和主键列，这里是 [user] 表的 u_id 列ONDELETECASCADE-- 指定在删除关联行时自动删除相关行ONUPDATECASCADE;-- 指定在更新关联行时自动更新相应行

完整代码

IFEXISTS(SELECT1FROM sys.databasesWHERE name ='MyShop')DROPDATABASE MyShop;-- 如果数据库存在，则删除数据库CREATEDATABASE MyShop;-- 创建数据库
GO

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

IF OBJECT_ID('address','U')ISNOTNULLDROPTABLE address;-- 如果地址表存在，则删除地址表IF OBJECT_ID('cart','U')ISNOTNULLDROPTABLE cart;-- 如果购物车表存在，则删除购物车表IF OBJECT_ID('item','U')ISNOTNULLDROPTABLE item;-- 如果订单项表存在，则删除订单项表IF OBJECT_ID('orders','U')ISNOTNULLDROPTABLE orders;-- 如果订单表存在，则删除订单表IF OBJECT_ID('product','U')ISNOTNULLDROPTABLE product;-- 如果商品表存在，则删除商品表IF OBJECT_ID('type','U')ISNOTNULLDROPTABLEtype;-- 如果类别表存在，则删除类别表IF OBJECT_ID('[user]','U')ISNOTNULLDROPTABLE[user];-- 如果用户表存在，则删除用户表--用户表CREATETABLE[user](
    u_id       INTNOTNULLIDENTITY(1,1)PRIMARYKEY,-- 用户实体的主键属性
    u_name     VARCHAR(20)NOTNULL,-- 用户账号
    u_password VARCHAR(64)NOTNULL,-- 用户密码
    u_email    VARCHAR(50)NOTNULL,-- 用户的邮箱！用于激活使用！
    u_sex      VARCHAR(4),-- 用户性别！
    u_status   INT,-- 用户的激活状态 0 未激活 1 激活
    u_code     VARCHAR(64),-- 邮件激活码
    u_role     INT-- 用户 0 管理员 1);-- 向 user 表插入虚拟数据INSERTINTO[user](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);-- 查询 user 表中的所有数据SELECT*FROM[user];-- 更新 user 表中的数据UPDATE[user]SET u_password ='newpassword'WHERE u_id =1;-- 删除 user 表中的数据DELETEFROM[user]WHERE u_id =1;--地址表CREATETABLE address
(
    a_id     INTNOTNULLIDENTITY(1,1)PRIMARYKEY,-- 地址实体的唯一主键列
    u_id     INT,-- 用户实体的主键属性
    a_name   VARCHAR(30),-- 地址的收件人
    a_phone  VARCHAR(14),-- 收件人电话
    a_detail VARCHAR(200),-- 收货人详细地址
    a_state  INTCHECK(a_state IN(0,1))-- 是否是默认地址 0 不是， 1 是默认地址，限制"a_state"的值为0或1);--用于修改 address 表的，添加了一个名为 FK_u_a_fk 的外键约束--删除 user 表的记录时同时删除 address 表中相关行的行为，可以使用 ON DELETE CASCADE 来定义外键约束。ALTERTABLE address
ADDCONSTRAINT FK_u_a_fk -- 添加一个名为 FK_u_a_fk 的约束FOREIGNKEY(u_id)-- 该约束是针对 u_id 列的外键约束REFERENCES[user](u_id)-- 指定引用的主键表和主键列，这里是 [user] 表的 u_id 列ONDELETECASCADE-- 指定在删除关联行时自动删除相关行ONUPDATECASCADE;-- 指定在更新关联行时自动更新相应行-- 向 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);-- 查询 address 表中的所有数据SELECT*FROM address;-- 更新 address 表中的数据UPDATE address
SET a_name ='newname'WHERE a_id =1;-- 删除 address 表中的数据DELETEFROM address
WHERE a_id =2;--商品类别表CREATETABLEtype(
    t_id   INTNOTNULLIDENTITY(1,1)PRIMARYKEY,-- 类别的主键id
    t_name VARCHAR(20),-- 类别的名称
    t_info VARCHAR(200)-- 类别的描述);-- 商品类别表插入虚拟数据INSERTINTOtype(t_name, t_info)VALUES('电子产品','包括手机、电脑、平板等电子设备'),('服装','包括男装、女装、童装等各种服装'),('家居用品','包括家具、家饰、厨具等家居用品');-- 查询所有类别SELECT*FROMtype;-- 根据类别名称查询类别SELECT*FROMtypeWHERE t_name ='电子产品';-- 根据类别ID查询类别SELECT*FROMtypeWHERE t_id =1;-- 删除所有类别DELETEFROMtype;-- 根据类别名称删除类别DELETEFROMtypeWHERE t_name ='电子产品';-- 根据类别ID删除类别DELETEFROMtypeWHERE t_id =1;-- 根据类别ID更新类别名称和描述UPDATEtypeSET t_name ='数码产品', t_info ='包括手机、电脑、相机等数码设备'WHERE t_id =1;-- 根据类别名称更新类别描述UPDATEtypeSET t_info ='包括男装、女装、童装等各种时尚服饰'WHERE t_name ='服装';--商品表CREATETABLE product
(
    p_id    INTNOTNULLIDENTITY(1,1)PRIMARYKEY,-- 商品的唯一主键
    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)-- 商品的描述);ALTERTABLE product
ADDCONSTRAINT FK_t_p_fk
FOREIGNKEY(t_id)REFERENCEStype(t_id)ONDELETECASCADEONUPDATECASCADE;-- 商品表插入虚拟数据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,'厨具套装');-- 查询所有商品SELECT*FROM product;-- 根据商品名称查询商品SELECT*FROM product WHERE p_name ='iPhone 12';-- 根据商品ID查询商品SELECT*FROM product WHERE p_id =1;-- 删除所有商品DELETEFROM product;-- 根据商品名称删除商品DELETEFROM product WHERE p_name ='iPhone 12';-- 根据商品ID删除商品DELETEFROM 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.99WHERE p_name ='MacBook Pro';--购物车CREATETABLE cart
(
    c_id    INTNOTNULLIDENTITY(1,1)PRIMARYKEY,-- 购物车的唯一标识 自增的主键列
    u_id    INT,-- 用户实体的主键属性
    p_id    INT,-- 商品的唯一主键
    c_count DECIMAL(12,2),-- 购物车小计
    c_num   INT-- 购物车商品数量);--外键约束与用户表 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;-- 向 cart 表插入虚拟数据INSERTINTO 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;-- 删除特定用户的购物车记录DELETEFROM cart WHERE u_id =1;-- 删除特定商品的购物车记录DELETEFROM cart WHERE p_id =2;-- 清空购物车表的所有记录DELETEFROM cart;-- 修改购物车中特定用户和商品的数量和小计UPDATE cart SET c_num =3, c_count =15.99WHERE u_id =2AND p_id =1;--订单表CREATETABLE orders
(
    o_id    VARCHAR(64)NOTNULLPRIMARYKEY,-- 订单编号是字符串类型但是也是唯一标识 主键。
    u_id    INT,-- 用户实体的主键属性
    a_id    INT,-- 地址实体的唯一主键列
    o_count DECIMAL(12,2),-- 订单的总金额
    o_time  DATETIME,-- 订单的详细时间
    o_state INT-- 订单状态 0 未付款，1 已经付款未发货 2 发货待收货 3 收货待评价 4 订单完成 5 退货状态);-- 添加外键约束与 user 表的关联；可以保证在删除用户时，同时删除与该用户关联的订单数据。ALTERTABLE orders
ADDCONSTRAINT FK_u_o_fk
FOREIGNKEY(u_id)REFERENCES[user](u_id)ONDELETECASCADEONUPDATECASCADE;--外键约束与 address 表的关联：在删除或更新关联行时不采取任何动作。这样可以避免循环引用的问题。ALTERTABLE orders
ADDCONSTRAINT FK_a_o_fk
FOREIGNKEY(a_id)REFERENCES address (a_id)ONDELETENOACTIONONUPDATENOACTION;INSERTINTO 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 =1WHERE o_id ='order1';-- 删除订单DELETEFROM orders WHERE o_id ='order1';-- 删除用户ID为2的所有订单DELETEFROM orders WHERE u_id =2;-- 清空订单表DELETEFROM orders;--订单项表，用于存储订单中每个商品的详细信息。每个订单可以包含多个订单项，每个订单项对应一个具体的商品。CREATETABLE item
(
    i_id    INTNOTNULLIDENTITY(1,1)PRIMARYKEY,-- 订单项的唯一标识 使用 IDENTITY(1,1) 来指定自增长属性并作为主键。
    o_id    VARCHAR(64),-- 订单编号是字符串类型但是也是唯一标识
    p_id    INT,-- 商品的唯一主键
    i_count DECIMAL(12,2),-- 订单项的小计
    i_num   INT-- 订单项的数量);-- 外键约束与订单表 orders 的关联：实现级联删除或更新。在删除或更新订单时，相关的订单项也会被删除或更新。ALTERTABLE item
ADDCONSTRAINT FK_item_orders
FOREIGNKEY(o_id)REFERENCES orders (o_id)ONDELETECASCADEONUPDATECASCADE;-- 外键约束与 product 表的关联：外键列：p_id 引用表：product 引用列：p_id  动作限制条件：在删除或更新关联行时不采取任何动作限制条件ALTERTABLE item
ADDCONSTRAINT FK_p_i_fk FOREIGNKEY(p_id)REFERENCES product (p_id)ONDELETENOACTIONONUPDATENOACTION;-- 向 item 表插入5条虚拟数据INSERTINTO 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.99WHERE i_id =1;-- 删除指定的订单项DELETEFROM 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：获取所有商品及其所属类别名称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;-- 使用视图 vw_Products 查询所有商品及其所属类别名称SELECT*FROM vw_Products;-- 视图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;-- 查询视图 vw_Orders 中的所有订单以及用户信息和地址详情SELECT*FROM vw_Orders;-- 视图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;-- 查询视图 vw_Cart 中指定用户的购物车内容SELECT*FROM vw_Cart WHERE u_id =5;-- 视图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;-- 查询视图 vw_Addresses 中指定用户的收货地址列表SELECT*FROM vw_Addresses
WHERE u_id =4;-- 视图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

-- 查询视图 vw_UserOrders 中指定用户的订单详情SELECT*FROM vw_UserOrders
WHERE u_name ='user4';-- 使用用户名来指定用户-- 存储过程1：添加商品到购物车CREATEPROCEDURE sp_AddToCart
    @user_idINT,@product_idINT,@quantityINTASBEGININSERTINTO 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：创建订单CREATEPROCEDURE sp_CreateOrder
    @user_idINT,@address_idINTASBEGINDECLARE@order_idVARCHAR(64);SET@order_id= CONCAT('ORD',REPLACE(CONVERT(VARCHAR(30), GETDATE(),121),':',''));-- 生成订单编号，格式为ORD+当前时间的字符串表示（去除冒号）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), GETDATE(),0FROM cart c
    WHERE c.u_id =@user_id;DELETEFROM cart WHERE u_id =@user_id;-- 清空购物车中该用户的商品END;-- 调用存储过程 sp_CreateOrder 创建订单EXEC sp_CreateOrder @user_id=4,@address_id=14;--查询结果SELECT*FROM orders;-- 存储过程3：更新订单状态CREATEPROCEDURE sp_UpdateOrderStatus
    @order_idVARCHAR(64),@new_stateINTASBEGINUPDATE orders
    SET o_state =@new_stateWHERE 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 项目角色;GRANTSELECT,INSERT,UPDATE,DELETEON orders TO 项目角色;--由组长将组员与项目角色捆绑CREATE LOGIN [组员]WITH PASSWORD ='password';USE MyShop;CREATEUSER[组员]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
本文转载自: https://blog.csdn.net/weixin_66397563/article/details/131492881
版权归原作者 汐ya~ 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【数据库原理】MyShop 商城数据库设计（SQL server）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

网络安全法-网络运行安全

使用selenium/drissionpage时如何阻止chrome自动跳转http到https

docker desktop 里部署的Open WebUI 管理员密码忘记了的处理方法

在ubuntu20.04中搭建onsite比赛运行环境

利用开源的低代码表单设计器FcDesigner高效管理和渲染复杂表单结构

Kafka学习笔记

【前端】浏览器输入url到页面呈现发生了什么？

文章导航