聊天室系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)案例

1、聊天室系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)、數(shù)據(jù)字典用戶表users 列名數(shù)據(jù)類型大小約束描述useridint主鍵用戶IDuser namevarchar20非空用戶名nickn amevarchar20非空昵稱telepho nevarchar20非空 emailvarchar20郵件ageint18到60之間年齡sexvarchar4男或女性別addressvarchar200默認(rèn)“地址不詳?shù)刂穜emarkvarchar200備注roleidint外鍵角色I(xiàn)D角色表roles 列名數(shù)據(jù)類型大小約束描述roleidint主鍵角色I(xiàn)Drole namevarchar20非空角色名稱好友關(guān)系表(friendship

2、 )列名數(shù)據(jù)類型大小約束描述useridint外鍵用戶IDfrie ndidint外鍵好友IDgroupidint外鍵分組ID分組表(user_group )列名數(shù)據(jù)類型大小約束描述groupidint主鍵分組IDgroup namevarchar20非空分組名稱聊天記錄表(chat_log )列名數(shù)據(jù)類型大小約束描述logidint主鍵記錄IDsen deridvarchar外鍵發(fā)送人IDreceiveridint外鍵接收人IDsen dtimevarchar20非空發(fā)送時(shí)間sendcontentvarchar200非空聊天內(nèi)容remarkvarchar200備注E-R圖三、物理模型圖use

3、r_groupVOroupid0 roupname .friendshipu曲Id filE-!由 d grcupsdusers lOQid it sendedd receive rid sendtime sflndcontent remark -ircrianCV1 useridustrname.1nlccnarne. va/riiar(20) telephone1email :.age.seaddressrem varclTartJOO roleidrolesroleid- irtrolename apdi3r(20)四、數(shù)據(jù)庫創(chuàng)立CREATE DATABASE chatingroomCR

4、EATE 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

5、),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 varch

6、ar(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

7、 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 varchar200 not null, remark varchar 200, 五、數(shù)據(jù)庫調(diào)試- Records of rolesINSERT INTO roles VALUES 1, 管理員 ;INSER

8、T INTO roles VALUES 2, 普通用戶 ;- Records of usersINSERT INTO users VALUES 1, admin, 管理員 , , , 32,北區(qū)遼寧路 1000 號, 系統(tǒng)管理員 , 1,123;INSERT INTO users VALUES 2, testuser1, 市市南區(qū)山東路 1200號, 普通用戶 , 2, 123;INSERT INTO users VALUES 3, testuser2, 市嶗山區(qū)苗嶺路 1300號, 普通用戶 , 2, 123;INSERT INTO users VALUES 4, testuser3, 市黃

9、島區(qū)長江路 1400號, 普通用戶 , 2, 123;張小三 , , , 23, 男, 青島王小五 , , , 28, 男, 青島鳳飛飛 , , , 21, 女, 青島男, 青島市市- Records of user_groupINSERT INTO user_group VALUES 1, 我的好友 ;INSERT INTO user_group VALUES 2, 陌生人 ;INSERT INTO user_group VALUES 3, 黑名單 ;INSERT INTO user_group VALUES 4, 我的網(wǎng)友 ;- Records of friendshipINSERT IN

10、TO 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 、根據(jù)用戶名和密碼查詢用戶信息需要角色名稱- 2 、插入用戶信息- 3 、修改用戶表添加一個(gè)狀態(tài)列： status int default 1 - 4 、更新用戶狀態(tài) =