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

聊天室系统数据库设计一、数据字典

用户表（users）

角色表（roles）

好友关系表（friendship）

分组表（user_group）

聊天记录表（chat_log）

二、E-R图

三、物理模型图

用户

角色

聊天记录分组

好友关系

四、数据库创建

CREATE DATABASE chatingroom

CREATE 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 DEFAULT'1',

password varchar(10) DEFAULT'123',

)

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', '凤飞飞', '', '', '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、查看所有聊天记录信息

-- --------------------------------

-- 6、根据发送人、接收人和时间来查询聊天记录

-- --------------------------------

-- 7、根据用户的id查询好友信息（要查询的内容有用户ID，好友ID，组ID，用户名，好有名，组名）对三张表进行查询：friendship，user_group，user（连两次）

-- --------------------------------

-- 8、查询所有角色信息

-- --------------------------------

-- --------------------------------

-- 9、查询所有组信息.

-- --------------------------------