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

聊天室系统数据库设计一、数据字典用户表（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图用户角色聊天记录分组好友关系三、物理模型图4、 数据库创建CREATE DATABASE chatingroomCREATE TABLE roles(roleid int CONSTRAINT r_PRIM PRIMARY KEY,rolename varchar(20) NOT NULL,)CREATE TABLE users(userid int CONSTRAINT u_PRIM PRIMARY KEY,username varchar (20) NOT NULL,nickname varchar(20) NOT NULL,telephone varchar(20) NOT NULL,email varchar (20),age int check(age between 18 and 60),sex varchar(4) check(sex=男or sex=女),address varchar(200) DEFAULT地址不详,remark varchar (200),roleid int CONSTRAINT r_FORE FOREIGN KEY REFERENCES roles(roleid),status int DEFAULT1,password varchar(10) DEFAULT123,)CREATE TABLE user_group(groupid int CONSTRAINT g_PRIM PRIMARY KEY,groupname varchar(20) NOT NULL,)CREATE TABLE friendship(userid int CONSTRAINT u_FORE FOREIGN KEY REFERENCES users(userid),friendid int CONSTRAINT f_FORE FOREIGN KEY REFERENCES users(userid),gruopid int CONSTRAINT g_FORE FOREIGN KEY REFERENCES user_group(groupid),)CREATE TABLE chat_log(logid int CONSTRAINT l_PRIM PRIMARY KEY,senderid int CONSTRAINT s_FORE FOREIGN KEY REFERENCES users(userid),recriverid int CONSTRAINT m_FORE FOREIGN KEY REFERENCES users(userid),sendtime varchar (20) not null,sendcontent varchar(200) not null,remark varchar (200), )五、数据库调试- - Records of roles- -INSERT INTO roles VALUES (1, 管理员);INSERT INTO roles VALUES (2, 普通用户);- - Records of users- -INSERT INTO users VALUES (1, admin, 管理员, , 32, 男, 青岛市市北区辽宁路1000号, 系统管理员, 1,123);INSERT INTO users VALUES (2, testuser1, 张小三, , 23, 男, 青岛市市南区山东路1200号, 普通用户, 2, 123);INSERT INTO users VALUES (3, testuser2, 王小五, , 28, 男, 青岛市崂山区苗岭路1300号, 普通用户, 2, 123);INSERT INTO users VALUES (4, testuser3, 凤飞飞, testuser3hotmail.co, 21, 女, 青岛市黄岛区长江路1400号, 普通用户, 2, 123);- - Records of user_group- -INSERT INTO user_group VALUES (1, 我的好友);INSERT INTO user_group VALUES (2, 陌生人);INSERT INTO user_group VALUES (3, 黑名单);INSERT INTO user_group VALUES (4, 我的网友);- - Records of friendship- -INSERT INTO friendship VALUES (1, 2, 1);INSERT INTO friendship VALUES (1, 3, 1);INSERT INTO friendship VALUES (1, 4, 1);INSERT INTO friendship VALUES (2, 3, 1);INSERT INTO friendship VALUES (2, 4, 2);INSERT INTO friendship VALUES (3, 2, 1);INSERT INTO friendship VALUES (3, 4, 4);- - 1、根据用户名和密码查询用户信息（需要角色名称）- 2、插入用户信息- 3、修改用户表添加一个状态列：status int default 1- 4、更新用户状态=0或1- 5、查看所有聊天