收发信息数据库以及操作

最新推荐文章于 2024-09-21 11:30:16 发布

loworth

最新推荐文章于 2024-09-21 11:30:16 发布

阅读量651

点赞数

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

本文链接：https://blog.csdn.net/loworth/article/details/2132359

版权

建表和建存储过程

-- 用户表

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 ' , ' 王五 '

-- 插入条消息