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

聊天室系统数据库设计一、数据字典
用户表（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、查询所有组信息.
-- --------------------------------。