— 步骤1：创建数据库
CREATE DATABASE fgedu_ecommerce
ON PRIMARY (
NAME = fgedu_ecommerce_data,
FILENAME = ‘E:\SQLServer\Data\fgedu_ecommerce_data.mdf’,
SIZE = 2048MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 512MB
)
LOG ON (
NAME = fgedu_ecommerce_log,
FILENAME = ‘E:\SQLServer\Log\fgedu_ecommerce_log.ldf’,
SIZE = 1024MB,
MAXSIZE = 4096MB,
FILEGROWTH = 256MB
);
GO

— 步骤2：创建核心表
— 用户表
CREATE TABLE fgedu.users (
user_id INT PRIMARY KEY IDENTITY(1,1),
username VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(255),
created_at DATETIME NOT NULL DEFAULT GETDATE(),
updated_at DATETIME NOT NULL DEFAULT GETDATE()
);
GO

— 商品表
CREATE TABLE fgedu.products (
product_id INT PRIMARY KEY IDENTITY(1,1),
product_name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(18,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
category_id INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT GETDATE(),
updated_at DATETIME NOT NULL DEFAULT GETDATE()
);
GO

— 分类表
CREATE TABLE fgedu.categories (
category_id INT PRIMARY KEY IDENTITY(1,1),
category_name VARCHAR(100) NOT NULL,
parent_id INT,
created_at DATETIME NOT NULL DEFAULT GETDATE(),
updated_at DATETIME NOT NULL DEFAULT GETDATE()
);
GO

— 订单表
CREATE TABLE fgedu.orders (
order_id BIGINT PRIMARY KEY IDENTITY(1,1),
user_id INT NOT NULL,
order_date DATETIME NOT NULL DEFAULT GETDATE(),
total_amount DECIMAL(18,2) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT ‘PENDING’,
payment_method VARCHAR(50),
shipping_address VARCHAR(255),
tracking_number VARCHAR(100),
created_at DATETIME NOT NULL DEFAULT GETDATE(),
updated_at DATETIME NOT NULL DEFAULT GETDATE()
);
GO

— 订单详情表
CREATE TABLE fgedu.order_items (
order_item_id BIGINT PRIMARY KEY IDENTITY(1,1),
order_id BIGINT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(18,2) NOT NULL,
subtotal DECIMAL(18,2) NOT NULL,
created_at DATETIME NOT NULL DEFAULT GETDATE()
);
GO

— 步骤3：创建索引
CREATE INDEX IX_products_category_id ON fgedu.products(category_id);
CREATE INDEX IX_orders_user_id ON fgedu.orders(user_id);
CREATE INDEX IX_orders_status ON fgedu.orders(status);
CREATE INDEX IX_order_items_order_id ON fgedu.order_items(order_id);
CREATE INDEX IX_order_items_product_id ON fgedu.order_items(product_id);
GO

— 步骤4：创建存储过程
— 商品库存查询
CREATE PROCEDURE fgedu.sp_get_product_stock
@product_id INT
AS
BEGIN
SELECT product_id, product_name, stock
FROM fgedu.products
WHERE product_id = @product_id;
END;
GO

— 订单处理
CREATE PROCEDURE fgedu.sp_process_order
@user_id INT,
@order_items XML,
@total_amount DECIMAL(18,2),
@payment_method VARCHAR(50),
@shipping_address VARCHAR(255),
@out_order_id BIGINT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;

— 创建订单
INSERT INTO fgedu.orders (
user_id,
total_amount,
status,
payment_method,
shipping_address
) VALUES (
@user_id,
@total_amount,
‘PENDING’,
@payment_method,
@shipping_address
);

SET @out_order_id = SCOPE_IDENTITY();

— 处理订单详情
DECLARE @item_xml XML;
DECLARE @product_id INT;
DECLARE @quantity INT;
DECLARE @unit_price DECIMAL(18,2);
DECLARE @subtotal DECIMAL(18,2);

DECLARE item_cursor CURSOR FOR
SELECT
T.c.value(‘@product_id’, ‘INT’),
T.c.value(‘@quantity’, ‘INT’),
T.c.value(‘@unit_price’, ‘DECIMAL(18,2)’),
T.c.value(‘@subtotal’, ‘DECIMAL(18,2)’)
FROM @order_items.nodes(‘/items/item’) T(c);

OPEN item_cursor;
FETCH NEXT FROM item_cursor INTO @product_id, @quantity, @unit_price, @subtotal;

WHILE @@FETCH_STATUS = 0
BEGIN
— 检查库存
DECLARE @stock INT;
SELECT @stock = stock
FROM fgedu.products
WHERE product_id = @product_id;

IF @stock < @quantity BEGIN RAISERROR('商品库存不足', 16, 1); END; -- 减少库存 UPDATE fgedu.products SET stock = stock - @quantity, updated_at = GETDATE() WHERE product_id = @product_id; -- 插入订单详情 INSERT INTO fgedu.order_items ( order_id, product_id, quantity, unit_price, subtotal ) VALUES ( @out_order_id, @product_id, @quantity, @unit_price, @subtotal ); FETCH NEXT FROM item_cursor INTO @product_id, @quantity, @unit_price, @subtotal; END; CLOSE item_cursor; DEALLOCATE item_cursor; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH; END; GO -- 步骤5：创建触发器 -- 自动更新updated_at字段 CREATE TRIGGER fgedu.trg_update_users_updated_at ON fgedu.users AFTER UPDATE AS BEGIN UPDATE fgedu.users SET updated_at = GETDATE() WHERE user_id IN (SELECT user_id FROM inserted); END; GO CREATE TRIGGER fgedu.trg_update_products_updated_at ON fgedu.products AFTER UPDATE AS BEGIN UPDATE fgedu.products SET updated_at = GETDATE() WHERE product_id IN (SELECT product_id FROM inserted); END; GO CREATE TRIGGER fgedu.trg_update_orders_updated_at ON fgedu.orders AFTER UPDATE AS BEGIN UPDATE fgedu.orders SET updated_at = GETDATE() WHERE order_id IN (SELECT order_id FROM inserted); END; GO

执行结果：