-- 创建数据库
CREATE DATABASE TicketSalesSystem;
GO
USE TicketSalesSystem;
GO
-- 创建票价类型表
CREATE TABLE TicketType (
TypeID INT PRIMARY KEY IDENTITY(1,1),
TypeName NVARCHAR(20) NOT NULL CHECK (TypeName IN ('老年', '小孩', '成人', '团体')),
BasePrice DECIMAL(10,2) NOT NULL,
DiscountRate DECIMAL(5,2) DEFAULT 1.0,
Description NVARCHAR(255),
CreatedAt DATETIME DEFAULT GETDATE(),
UpdatedAt DATETIME DEFAULT GETDATE()
);
GO
-- 创建营业员表
CREATE TABLE Salesperson (
SalespersonID INT PRIMARY KEY IDENTITY(1,1),
EmployeeID NVARCHAR(20) UNIQUE NOT NULL,
Name NVARCHAR(50) NOT NULL,
Password NVARCHAR(100) NOT NULL,
Role NVARCHAR(20) DEFAULT 'staff' CHECK (Role IN ('admin', 'staff')),
IsActive BIT DEFAULT 1,
CreatedAt DATETIME DEFAULT GETDATE(),
LastLogin DATETIME NULL
);
GO
-- 创建门票销售表
CREATE TABLE TicketSale (
SaleID INT PRIMARY KEY IDENTITY(1,1),
TypeID INT NOT NULL,
SalespersonID INT NOT NULL,
Quantity INT NOT NULL CHECK (Quantity > 0),
UnitPrice DECIMAL(10,2) NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL,
SaleTime DATETIME DEFAULT GETDATE(),
IsRefund BIT DEFAULT 0,
CONSTRAINT FK_TicketSale_TicketType FOREIGN KEY (TypeID) REFERENCES TicketType(TypeID),
CONSTRAINT FK_TicketSale_Salesperson FOREIGN KEY (SalespersonID) REFERENCES Salesperson(SalespersonID)
);
GO
-- 创建退票记录表
CREATE TABLE RefundRecord (
RefundID INT PRIMARY KEY IDENTITY(1,1),
SaleID INT NOT NULL,
RefundQuantity INT NOT NULL CHECK (RefundQuantity > 0),
RefundAmount DECIMAL(10,2) NOT NULL,
RefundTime DATETIME DEFAULT GETDATE(),
Reason NVARCHAR(255),
CONSTRAINT FK_RefundRecord_TicketSale FOREIGN KEY (SaleID) REFERENCES TicketSale(SaleID)
);
GO
-- 创建操作日志表
CREATE TABLE AuditLog (
LogID INT PRIMARY KEY IDENTITY(1,1),
UserID INT NOT NULL,
ActionType NVARCHAR(20) NOT NULL,
ActionDetails NVARCHAR(500) NOT NULL,
ActionTime DATETIME DEFAULT GETDATE(),
CONSTRAINT FK_AuditLog_Salesperson FOREIGN KEY (UserID) REFERENCES Salesperson(SalespersonID)
);
GO
-- 创建索引优化查询性能
CREATE INDEX IX_TicketSale_SaleTime ON TicketSale(SaleTime);
CREATE INDEX IX_TicketSale_Salesperson ON TicketSale(SalespersonID);
CREATE INDEX IX_TicketSale_Type ON TicketSale(TypeID);
CREATE INDEX IX_RefundRecord_SaleID ON RefundRecord(SaleID);
CREATE INDEX IX_AuditLog_ActionTime ON AuditLog(ActionTime);
GO
-- 存储过程1: 统计指定日期的门票销售情况
CREATE PROCEDURE sp_DailySalesSummary
@TargetDate DATE
AS
BEGIN
SELECT
TT.TypeName AS 票种类型,
SUM(TS.Quantity) AS 总票数,
SUM(TS.TotalAmount) AS 总收入
FROM TicketSale TS
JOIN TicketType TT ON TS.TypeID = TT.TypeID
WHERE CAST(TS.SaleTime AS DATE) = @TargetDate
AND TS.IsRefund = 0
GROUP BY TT.TypeName
ORDER BY 总收入 DESC;
END;
GO
-- 存储过程2: 统计指定月份的门票销售情况
CREATE PROCEDURE sp_MonthlySalesSummary
@Year INT,
@Month INT
AS
BEGIN
SELECT
TT.TypeName AS 票种类型,
SUM(TS.Quantity) AS 总票数,
SUM(TS.TotalAmount) AS 总收入
FROM TicketSale TS
JOIN TicketType TT ON TS.TypeID = TT.TypeID
WHERE YEAR(TS.SaleTime) = @Year
AND MONTH(TS.SaleTime) = @Month
AND TS.IsRefund = 0
GROUP BY TT.TypeName
ORDER BY 总收入 DESC;
END;
GO
-- 存储过程3: 统计指定日期各种价格的门票销售情况
CREATE PROCEDURE sp_DailySalesByPrice
@TargetDate DATE
AS
BEGIN
SELECT
TS.UnitPrice AS 销售单价,
COUNT(TS.SaleID) AS 销售笔数,
SUM(TS.Quantity) AS 总票数,
SUM(TS.TotalAmount) AS 总收入
FROM TicketSale TS
WHERE CAST(TS.SaleTime AS DATE) = @TargetDate
AND TS.IsRefund = 0
GROUP BY TS.UnitPrice
ORDER BY TS.UnitPrice DESC;
END;
GO
-- 存储过程4: 统计指定营业员指定日期的收费情况
CREATE PROCEDURE sp_SalespersonDailySummary
@SalespersonID INT,
@TargetDate DATE
AS
BEGIN
SELECT
SP.Name AS 营业员姓名,
COUNT(TS.SaleID) AS 交易笔数,
SUM(TS.Quantity) AS 总票数,
SUM(TS.TotalAmount) AS 总收入
FROM TicketSale TS
JOIN Salesperson SP ON TS.SalespersonID = SP.SalespersonID
WHERE SP.SalespersonID = @SalespersonID
AND CAST(TS.SaleTime AS DATE) = @TargetDate
AND TS.IsRefund = 0
GROUP BY SP.Name;
-- 详细交易列表
SELECT
TT.TypeName AS 票种类型,
TS.Quantity AS 数量,
TS.UnitPrice AS 单价,
TS.TotalAmount AS 金额,
FORMAT(TS.SaleTime, 'HH:mm:ss') AS 销售时间
FROM TicketSale TS
JOIN TicketType TT ON TS.TypeID = TT.TypeID
WHERE TS.SalespersonID = @SalespersonID
AND CAST(TS.SaleTime AS DATE) = @TargetDate
AND TS.IsRefund = 0
ORDER BY TS.SaleTime DESC;
END;
GO
-- 存储过程5: 处理门票销售
CREATE PROCEDURE sp_ProcessTicketSale
@TypeID INT,
@SalespersonID INT,
@Quantity INT,
@SaleID INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @UnitPrice DECIMAL(10,2);
DECLARE @TotalAmount DECIMAL(10,2);
-- 获取票价信息
SELECT @UnitPrice = BasePrice * DiscountRate
FROM TicketType
WHERE TypeID = @TypeID;
IF @UnitPrice IS NULL
RAISERROR('无效的票种ID', 16, 1);
-- 计算总金额
SET @TotalAmount = @UnitPrice * @Quantity;
-- 插入销售记录
INSERT INTO TicketSale (
TypeID,
SalespersonID,
Quantity,
UnitPrice,
TotalAmount
)
VALUES (
@TypeID,
@SalespersonID,
@Quantity,
@UnitPrice,
@TotalAmount
);
SET @SaleID = SCOPE_IDENTITY();
-- 记录操作日志
INSERT INTO AuditLog (UserID, ActionType, ActionDetails)
VALUES (
@SalespersonID,
'SALE',
CONCAT('销售门票: 票种ID=', @TypeID, ', 数量=', @Quantity, ', 金额=', @TotalAmount)
);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
END CATCH
END;
GO
-- 存储过程6: 处理门票退票
CREATE PROCEDURE sp_ProcessTicketRefund
@SaleID INT,
@RefundQuantity INT,
@Reason NVARCHAR(255),
@RefundID INT OUTPUT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @MaxQuantity INT;
DECLARE @UnitPrice DECIMAL(10,2);
DECLARE @RefundAmount DECIMAL(10,2);
-- 获取原始销售信息
SELECT
@MaxQuantity = Quantity,
@UnitPrice = UnitPrice
FROM TicketSale
WHERE SaleID = @SaleID;
IF @MaxQuantity IS NULL
RAISERROR('无效的销售ID', 16, 1);
-- 检查可退票数量
DECLARE @AlreadyRefunded INT = 0;
SELECT @AlreadyRefunded = ISNULL(SUM(RefundQuantity), 0)
FROM RefundRecord
WHERE SaleID = @SaleID;
DECLARE @Remaining INT = @MaxQuantity - @AlreadyRefunded;
IF @RefundQuantity > @Remaining
RAISERROR('退票数量超过可退数量', 16, 1);
-- 计算退款金额
SET @RefundAmount = @UnitPrice * @RefundQuantity;
-- 插入退票记录
INSERT INTO RefundRecord (
SaleID,
RefundQuantity,
RefundAmount,
Reason
)
VALUES (
@SaleID,
@RefundQuantity,
@RefundAmount,
@Reason
);
SET @RefundID = SCOPE_IDENTITY();
-- 更新销售记录状态
IF @RefundQuantity = @Remaining
UPDATE TicketSale SET IsRefund = 1 WHERE SaleID = @SaleID;
-- 记录操作日志
DECLARE @SalespersonID INT = (SELECT SalespersonID FROM TicketSale WHERE SaleID = @SaleID);
INSERT INTO AuditLog (UserID, ActionType, ActionDetails)
VALUES (
@SalespersonID,
'REFUND',
CONCAT('退票处理: 销售ID=', @SaleID, ', 数量=', @RefundQuantity, ', 金额=', @RefundAmount)
);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
END CATCH
END;
GO
-- 存储过程7: 获取所有有效票种
CREATE PROCEDURE sp_GetActiveTicketTypes
AS
BEGIN
SELECT
TypeID,
TypeName,
BasePrice,
DiscountRate,
ROUND(BasePrice * DiscountRate, 2) AS ActualPrice,
Description
FROM TicketType
ORDER BY TypeName;
END;
GO
-- 存储过程8: 添加新票种
CREATE PROCEDURE sp_AddTicketType
@TypeName NVARCHAR(20),
@BasePrice DECIMAL(10,2),
@DiscountRate DECIMAL(5,2) = 1.0,
@Description NVARCHAR(255) = NULL
AS
BEGIN
IF NOT @TypeName IN ('老年', '小孩', '成人', '团体')
RAISERROR('无效的票种名称', 16, 1);
INSERT INTO TicketType (
TypeName,
BasePrice,
DiscountRate,
Description
)
VALUES (
@TypeName,
@BasePrice,
@DiscountRate,
@Description
);
RETURN SCOPE_IDENTITY();
END;
GO
-- 存储过程9: 营业员登录验证
CREATE PROCEDURE sp_VerifySalespersonLogin
@EmployeeID NVARCHAR(20),
@Password NVARCHAR(100),
@IsValid BIT OUTPUT
AS
BEGIN
SET @IsValid = 0;
IF EXISTS (
SELECT 1
FROM Salesperson
WHERE EmployeeID = @EmployeeID
AND Password = @Password
AND IsActive = 1
)
BEGIN
SET @IsValid = 1;
-- 更新最后登录时间
UPDATE Salesperson
SET LastLogin = GETDATE()
WHERE EmployeeID = @EmployeeID;
END
END;
GO
-- 存储过程10: 月度销售趋势分析
CREATE PROCEDURE sp_MonthlySalesTrend
@Year INT
AS
BEGIN
SELECT
MONTH(SaleTime) AS 月份,
SUM(TotalAmount) AS 月收入,
COUNT(SaleID) AS 交易笔数,
SUM(Quantity) AS 总票数
FROM TicketSale
WHERE YEAR(SaleTime) = @Year
AND IsRefund = 0
GROUP BY MONTH(SaleTime)
ORDER BY 月份;
END;
GO
-- 创建触发器: 更新票种时自动更新更新时间戳
CREATE TRIGGER trg_UpdateTicketTypeTimestamp
ON TicketType
AFTER UPDATE
AS
BEGIN
UPDATE TicketType
SET UpdatedAt = GETDATE()
WHERE TypeID IN (SELECT TypeID FROM inserted);
END;
GO
-- 创建触发器: 退票后更新销售记录状态
CREATE TRIGGER trg_AfterRefund
ON RefundRecord
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE TS
SET TS.IsRefund = CASE
WHEN (TS.Quantity - i.RefundQuantity) <= 0 THEN 1
ELSE 0
END
FROM TicketSale TS
INNER JOIN inserted i ON TS.SaleID = i.SaleID;
END;
GO
-- 创建视图: 销售明细视图
CREATE VIEW vw_SaleDetails
AS
SELECT
TS.SaleID,
TS.SaleTime,
SP.Name AS SalespersonName,
TT.TypeName,
TS.Quantity,
TS.UnitPrice,
TS.TotalAmount,
CASE WHEN TS.IsRefund = 1 THEN '已退票' ELSE '有效' END AS Status,
ISNULL(RR.RefundQuantity, 0) AS RefundedQuantity
FROM TicketSale TS
JOIN Salesperson SP ON TS.SalespersonID = SP.SalespersonID
JOIN TicketType TT ON TS.TypeID = TT.TypeID
LEFT JOIN (
SELECT SaleID, SUM(RefundQuantity) AS RefundQuantity
FROM RefundRecord
GROUP BY SaleID
) RR ON TS.SaleID = RR.SaleID;
GO
-- 插入初始数据
INSERT INTO TicketType (TypeName, BasePrice, DiscountRate, Description)
VALUES
('成人', 100.00, 1.00, '标准成人票'),
('小孩', 50.00, 1.00, '1.2-1.5米儿童票'),
('老年', 60.00, 1.00, '65岁以上老人票'),
('团体', 80.00, 0.90, '10人及以上团体票');
GO
INSERT INTO Salesperson (EmployeeID, Name, Password, Role)
VALUES
('admin001', '管理员', 'admin123', 'admin'),
('staff001', '张三', 'staff123', 'staff'),
('staff002', '李四', 'staff456', 'staff');
GO
-- 插入示例销售记录
DECLARE @SaleID INT;
EXEC sp_ProcessTicketSale 1, 2, 2, @SaleID OUTPUT;
EXEC sp_ProcessTicketSale 2, 2, 1, @SaleID OUTPUT;
EXEC sp_ProcessTicketSale 3, 3, 3, @SaleID OUTPUT;
EXEC sp_ProcessTicketSale 4, 3, 10, @SaleID OUTPUT;
GO
-- 插入示例退票记录
DECLARE @RefundID INT;
EXEC sp_ProcessTicketRefund 1, 1, '行程变更', @RefundID OUTPUT;
GO数据库编写完后给出下一个步骤，使用后端使用Java，前端使用html
最新发布
本文围绕MySQL展开，在设置crash safe相关参数为双1的前提下，介绍了WAL机制、Redo log和组提交的作用。重点阐述了binlog组提交的三个阶段（Flush、Sync、Commit）及各阶段队列的作用，最后分析了Sync阶段参数导致的bug，该bug已在部分版本修复。
扫一扫
676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
