收发信息数据库以及操作

最新推荐文章于 2022-11-06 16:19:52 发布

loworth

最新推荐文章于 2022-11-06 16:19:52 发布

阅读量704

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 insert user table join 存储

本文链接：https://blog.youkuaiyun.com/loworth/article/details/2132359

本文介绍了一个基于SQL的消息系统的实现方案，包括用户表、消息表及附件表的设计，并提供了用于查看消息列表和详细信息的存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表和建存储过程

--用户表

CREATE TABLE [t_User]

(

[userID] INT IDENTITY(1,1),--自动增长

[userAccount] VARCHAR(15),--用户账号,主键

[userPassword] CHAR(32),--用户密码,最好经过加密，比如流行的MD5

[userName] NVARCHAR(5)--用户真实姓名

-- ..其他信息

PRIMARY KEY([userID]),

UNIQUE([userAccount])

) ON [PRIMARY]

--消息表

CREATE TABLE [t_Message]

(

[messageID] BIGINT IDENTITY(1,1),--编号,主键,自动增长

[messagePostUserID] INT,--发消息人,外键关联t_User表的UserID

[messageReceiveUserID] INT,--收消息人,外键关联t_User表的UserID

[messageTitle] NVARCHAR(25),--消息标题

[messageBody] NTEXT,--消息主体，该字段类型视消息主体内容大小而定，如果特别大，则设为NTEXT,否则用NVARCHAR即可

[messageTime] DATETIME DEFAULT GETDATE(),--发消息时间,默认值为当前系统的时间

[isRead] BIT DEFAULT 0,--收消息人是否阅读过

[isPostUserDelete] BIT DEFAULT 0,--发消息人是否删除

[isReceiveUserDelete] BIT DEFAULT 0--收消息人是否删除

PRIMARY KEY([messageID]),

FOREIGN KEY([messagePostUserID]) REFERENCES [t_User]([userID]),

FOREIGN KEY([messageReceiveUserID]) REFERENCES [t_User]([userID])

) ON [PRIMARY]

--附件表

CREATE TABLE [t_Message_Attachment]

(

[attachmentID] BIGINT IDENTITY(1,1),--编号，主键,自动增长

[messageID] BIGINT,--外键，关联消息表中的messageID

[AttachFile] BINARY--附件（需转换为二进制）

FOREIGN KEY([messageID]) REFERENCES [t_Message]([messageID]) ON DELETE CASCADE

) ON [PRIMARY]

--用户查看自己所发消息列表的存储过程

CREATE PROCEDURE usp_ViewMessageListByPostUser

(

@userAccount VARCHAR(15)--用户账号

)

BEGIN

SELECT [m].[messageID],[m].[messageTitle],[m].[messageTime],[m].[isRead],[m].[messagePostUserID],[u2].[userName]

FROM [t_Message] [m] JOIN [t_User] [u] ON [m].[messagePostUserID]=[u].[userID]

LEFT JOIN [t_User] [u2] ON [m].[messageReceiveUserID]=[u2].[userID]

WHERE [u].[userAccount]=@userAccount AND [m].[isPostUserDelete]=0

END

--用户查看自己所收消息列表的存储过程

CREATE PROCEDURE usp_ViewMessageListByReceiveUser

(

@userAccount VARCHAR(15)--用户账号

)

BEGIN

SELECT [m].[messageID],[m].[messageTitle],[m].[messageTime],[m].[isRead],[m].[messagePostUserID],[u2].[userName]

FROM [t_Message] [m] JOIN [t_User] [u] ON [m].[messageReceiveUserID]=[u].[userID]

LEFT JOIN [t_User] [u2] ON [m].[messagePostUserID]=[u2].[userID]

WHERE [u].[userAccount]=@userAccount AND [m].[isReceiveUserDelete]=0

END

--查看消息具体内容(不包括附件)存储过程

CREATE PROCEDURE usp_ViewMessageDetail

(

@messageID BIGINT,--消息编号

@isReceiveUser BIT=0--是否收件人阅读

)

BEGIN

SET XACT_ABORT ON

BEGIN TRAN--采用事务操作

IF @isReceiveUser=1 AND EXISTS(SELECT 1 FROM [t_Message] WHERE [messageID]=@messageID AND [isRead]=0)

BEGIN

UPDATE [t_Message] SET isRead=1 WHERE [messageID]=@messageID AND [isRead]=0

END

SELECT [m].*,[u].[userName] FROM [t_Message] [m]

LEFT JOIN [t_User] [u] ON CASE @isReceiveUser WHEN 0 THEN [m].[messagePostUserID] ELSE [m].[messageReceiveUserID] END=[u].[userID]

WHERE [m].[messageID]=@messageID

COMMIT TRAN

SET XACT_ABORT OFF

END

--查看消息附件

CREATE PROCEDURE usp_ViewMessageAttachment

(

@messageID BIGINT--消息编号

)

BEGIN

SELECT * FROM [t_Message_Attachment] WHERE [messageID]=@messageID ORDER BY attachmentID ASC

END

使用操作

--插入用户A和B

INSERT INTO [t_User]([userAccount],[userPassword],[userName])

SELECT 'A','E10ADC3949BA59ABBE56E057F20F883E','张三' UNION ALL

SELECT 'B','E10ADC3949BA59ABBE56E057F20F883E','李四' UNION ALL

SELECT 'C','E10ADC3949BA59ABBE56E057F20F883E','王五'

--插入条消息