聊天室系统数据库设计案例

聊天室系统数据库设计一、数据字典用户表（users）列名数据类型大小约束描述useridint主键用户IDusernamevarchar20非空用户名nicknamevarchar20非空昵称telephonevarchar20非空电话emailvarchar20邮件ageint18到60之间年龄sexvarchar4男或女性别addressvarchar200默认“地址不详”地址remarkvarchar200备注roleidint外键角色ID角色表（roles）列名数据类型大小约束描述roleidint主键角色IDrolenamevarchar20非空角色名称好友关系表（friendship）列名数据类型大小约束描述useridint外键用户IDfriendidint外键好友IDgroupidint外键分组ID分组表（user_group）列名数据类型大小约束描述groupidint主键分组IDgroupnamevarchar20非空分组名称聊天记录表（chat_log）列名数据类型大小约束描述logidint主键记录IDsenderidvarchar外键发送人IDreceiveridint外键接收人IDsendtimevarchar20非空发送时间sendcontentvarchar200非空聊天内容remarkvarchar200备注二、E-R图三、物理模型图用户角色聊天记录分组好友关系四、数据库创建CREATEDATABASEchatingroomCREATETABLEroles(roleidintCONSTRAINTr_PRIMPRIMARYKEY,rolenamevarchar(20)NOTNULL,)CREATETABLEusers(useridintCONSTRAINTu_PRIMPRIMARYKEY,usernamevarchar(20)NOTNULL,nicknamevarchar(20)NOTNULL,telephonevarchar(20)NOTNULL,emailvarchar(20),ageintcheck(agebetween18and60),sexvarchar(4)check(sex='男'orsex='女'),addressvarchar(200)DEFAULT'地址不详',remarkvarchar(200),roleidintCONSTRAINTr_FOREFOREIGNKEYREFERENCESroles(roleid),statusintDEFAULT'1',passwordvarchar(10)DEFAULT'123',)CREATETABLEuser_group(groupidintCONSTRAINTg_PRIMPRIMARYKEY,groupnamevarchar(20)NOTNULL,)CREATETABLEfriendship(useridintCONSTRAINTu_FOREFOREIGNKEYREFERENCESusers(userid),friendidintCONSTRAINTf_FOREFOREIGNKEYREFERENCESusers(userid),gruopidintCONSTRAINTg_FOREFOREIGNKEYREFERENCESuser_group(groupid),)CREATETABLEchat_log(logidintCONSTRAINTl_PRIMPRIMARYKEY,senderidintCONSTRAINTs_FOREFOREIGNKEYREFERENCESusers(userid),recriveridintCONSTRAINTm_FOREFOREIGNKEYREFERENCESusers(userid),sendtimevarchar(20)notnull,sendcontentvarchar(200)notnull,remarkvarchar(200),)五、数据库调试--------------------------------Recordsofroles------------------------------INSERTINTOrolesVALUES('1','管理员');INSERTINTOrolesVALUES('2','普通用户');--------------------------------Recordsofusers------------------------------INSERTINTOusersVALUES('1','admin','管理员','15066968808','[e-mail]','32','男','青岛市市北区辽宁路1000号','系统管理员','1',123);INSERTINTOusersVALUES('2','testuser1','张小三','18600023456','[e-mail]','23','男','青岛市市南区山东路1200号','普通用户','2',123);INSERTINTOusersVALUES('3','testuser2','王小五','15399002344','[e-mail]','28','男','青岛市崂山区苗岭路1300号','普通用户','2',123);INSERTINTOusersVALUES('4','testuser3','凤飞飞','18666866686','[e-mail]','21','女','青岛市黄岛区长江路1400号','普通用户','2',123);--------------------------------Recordsofuser_group------------------------------INSERTINTOuser_groupVALUES('1','我的好友');INSERTINTOuser_groupVALUES('2','陌生人');INSERTINTOuser_groupVALUES('3','黑名单');INSERTINTOuser_groupVALUES('4','我的网友');--------------------------------Recordsoffriendship------------------------------INSERTINTOfriendshipVALUES('1','2','1');INSERTINTOfriendshipVALUES('1','3','1');INSERTINTOfriendshipVALUES('1','4','1');INSERTINTOfriendshipVALUES('2','3','1');INSERTINTOfriendshipVALUES('2','4','2');INSERTINTOfriendshipVALUES('3','2','1');INSERTINTOfriendshipVALUES('3','4','4');------------------------------------1、根据用户名和密码查询用户信息（需要角色名称）--2、插入用户信息--3、修改用户表添加一个状态列：statusintdefault1--4、更新用户状态=0或1--5、查看所有聊天记录信息------------------------------------6、根据发送人、接收人和时间来查询聊天记录------------------------------------7、根据用户的id查询好友信息（要查询的内容有用户ID，好友ID，组ID，用户名，好有名，组名）对三张表进行查询：friendship，user_group，user（连两次）------------------------------------8、查询所有角色信息----------------------------------------------------------------------9、查询所有组信息.----------------------------------