聊天室系统数据库设计案例
聊天室系统数据库设计
一、数据字典
用户表（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', '管理员', '[1**********]', '[email protected]', '32', '男', '青岛市市北区辽宁路1000号', '系统管理员', '1',123);
INSERT INTO users VALUES ('2', 'testuser1', ' 张小三', '[1**********]', '[email protected]', '23', '男', '青岛市市南区山东路1200号', '普通用户', '2', 123);
INSERT INTO users VALUES ('3', 'testuser2', '王小五', '[1**********]', '[email protected]', '28', '男', '青岛市崂山区苗岭路1300号', '普通用户', '2', 123);
INSERT INTO users VALUES ('4', 'testuser3', '凤飞飞', '[1**********]', '[email protected]', '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、查询所有组信息. -- --------------------------------