Oracle数据库-聊天室项目(共16页)

1、精选优质文档-倾情为你奉上第一部分 案例描述 案例目的学习Oracle数据库的基本概念、建库、建表、基本增删改查，复杂查询（条件、关联、复合），同时学习数据库对象：视图、索引的使用，还有oralce独有的两个数据库对象：序列和同义词的使用方法和场合，培养学生关于数据库的设计能力与数据的操作能力。案例覆盖技能点1、  Oracle数据库基本概念2、  建库、建表、建约束3、  实现基本增删改查等数据的操作1)  对数据库进行增加2)  删除3)  修改4、  根据业务的需要建视图、索引、序列和同义词1)  建视图2)

2、  建索引3)  建序列4)  建同义词5、  高级查询技术1)  会使用 group by、关键字2)  会使用CASEWHENTHEN3)  会使用关联查询 4)  会使用子查询 第二部分  需求和开发环境使用技术和开发环境Oracle10g聊天系统最终要实现的功能与QQ大致相同，主要包括聊天（支持文本、语音和视频）、传送文件和远程协助等功能。在此，只实现其最基本功能以文本形式进行聊天。*/*1. 注册用户管理包括用户的注册、用户基本信息的修改（包括修改密码）、用户的删除、用户的登录验证，以及用户信息

3、的查看和查找等。2. 管理员管理包括管理员的添加、修改、删除、登录验证和查询等。3. 好友管理包括申请添加好友、对添加好友的验证信息进行处理、删除好友（从好友列表中删除）等。4. 聊天信息管理包括发出聊天信息、显示聊天信息等。1）用户表表名  t_user（用户表）列名  描述  数据类型（精度范围）  空/非空  约束条件Userid  用户编号  NUMBER(38)  非空  主键（自增），初始值10001Pwd  密码  NVARCHAR2(100)  非空 

4、0;Nickname  昵称  NVARCHAR2(10)  非空  Sex  性别  NUMBER(1)    CHECKBirthday  出生日期  DATE    Currstate  当前状态  NUMBER(1)    默认值0，外键friendshippolicy  交友策略  NUMBER(1)    外键2）用户状态表表名  t_onlinestate（用户在线状态表）

5、列名  描述  数据类型（精度范围）  空/非空  约束条件Stateid  状态编号  NUMBER(1)  非空  主键Statedesc  状态描述  NVARCHAR2(10)  非空  唯一用户状态表里的数据如下：编号  用户在线状态1  在线2  离线3  隐身4  忙碌3）交友策略表表名  t_friendshippolicy（交友策略表）列名  描述  数据类型（精度范围）

6、60;空/非空  约束条件Polid  编号  NUMBER(1)  非空  主键Policy  交友策略  NVARCHAR2(20)  非空  唯一交友策略表数据如下：编号  交友策略1  允许任何人加我为好友2  不允许任何人加我为好友3  经过验证才允许别人加我为好友4）好友表表名  t_friend（好友表）列名  描述  数据类型（精度范围）  空/非空  约束条件Ufid  编号  

7、NUMBER(38)  非空  主键（自增）Userid  用户编号  NUMBER(38)  非空  外键Friendid  好友编号  NUMBER(38)  非空  外键4）聊天信息表表名  t_message（聊天消息表）列名  描述  数据类型（精度范围）  空/非空  约束条件Messageid  编号  NUMBER(38)  非空  主键（自增）Fromuserid  信息发出者

8、 NUMBER(38)  非空  外键Touserid  信息接收者  NUMBER(38)  非空  外键Content  信息内容  NVARCHAR2(50)    messagetype  信息类型  NUMBER(1)    CHECKState  是否已读  NUMBER(1)    默认值，CHECKSendtime  发出时间  DATE    默认值

9、5）管理员表表名  t_admin（管理员表）列名  描述  数据类型（精度范围）  空/非空  约束条件Adminid  编号  NUMBER(10)  非空  主键（自增）adminname  管理员名称  NVARCHAR2(20)  非空  唯一Adminpwd  密码  NVARCHAR2(100)  非空  第三部分 问题分析和推荐步骤1.  创建数据库和相应的表1)  创建数据库Ø

10、 利用现有的数据库，利用示例用户scott的身份登录。在scott模式下准备新建表（本来应该新建数据库，为新数据库创建用户并为新用户授权，以新的新用户登录来创建数据表）Ø  利用命令创建数据库比较繁琐，可以不要求使用2)  创建数据表Ø  创建以上5个数据表1）用户表create or replace table t_user(-（用户表）-列名  描述  数据类型（精度范围）  空/非空  约束条件    Userid NUMBER(38) primary key not

11、null,-  非空  主键（自增），初始值10001    Pwd NVARCHAR2(100) not null,-  非空      Nickname NVARCHAR2(10) not null,-  非空      Sex NUMBER(1)  CHECK( Sex in (0,1),    Birthday DATE,        Currstate NUMBER(1) default 0 r

12、eferences t_onlinestate(Stateid),-     默认值0，外键    friendshippolicy NUMBER(1) references t_friendshippolicy(Polid)-    外键);insert into t_user(Pwd,Nickname,Sex,Birthday,Currstate,friendshippolicy) values        ('shihua','shihua',0

13、,sysdate,1,1);select * from t_user;drop sequence seq_user;create sequence seq_user    increment by 1    start with 10001    maxvalue     nocycle    cache 10;create or replace trigger tir_user before insert on t_user for each rowbegin  selec

14、t seq_user.nextval          into :new.Userid from dual;end;2）用户状态表create table t_onlinestate(-（用户在线状态表）-列名  描述  数据类型（精度范围）  空/非空  约束条件    Stateid NUMBER(1) primary key not null,- 非空  主键    Statedesc NVARCHAR2(10) unique not n

15、ull-  非空  唯一);insert into t_onlinestate values (1,'在线');insert into t_onlinestate values (2,'离线');insert into t_onlinestate values (3,'隐身');insert into t_onlinestate values (4,'忙碌');select * from t_onlinestate;/*用户状态表里的数据如下：编号  用户在线状态1  在线2  离

16、线3  隐身4  忙碌*/3）交友策略表create table t_friendshippolicy(-（交友策略表）-列名  描述  数据类型（精度范围）  空/非空  约束条件    Polid NUMBER(1) primary key not null,-  非空  主键    Policy NVARCHAR2(20) unique not null-  非空  唯一);insert into t_friendshippolicy values

17、(1,'允许任何人加我为好友');insert into t_friendshippolicy values (2,'不允许任何人加我为好友');insert into t_friendshippolicy values (3,'经过验证才允许别人加我为好友');select * from t_friendshippolicy;/*交友策略表数据如下：编号  交友策略1  允许任何人加我为好友2  不允许任何人加我为好友3  经过验证才允许别人加我为好友*/4）好友表create table t_frien

18、d(-（好友表）-列名  描述  数据类型（精度范围）  空/非空  约束条件    Ufid NUMBER(38) primary key not null,- 非空  主键（自增）    Userid NUMBER(38) not null references t_user(Userid),- 非空  外键    Friendid NUMBER(38) not null references t_user(Userid) - 非空  外键);insert in

19、to t_friend(userid,friendid) values (10004,10005);insert into t_friend(userid,friendid) values (10004,10006);insert into t_friend(userid,friendid) values (10005,10004);insert into t_friend(userid,friendid) values (10005,10006);select * from t_friend;create sequence seq_friend; create or replace

20、 trigger tir_friend before insert on t_friend for each rowbegin  select seq_friend.nextval          into :new.Ufid from dual;end;4）聊天信息表create table t_message(-（聊天消息表）-列名  描述  数据类型（精度范围）  空/非空  约束条件    Messageid NUMBER(38) primary

21、 key not null,-  非空  主键（自增）    Fromuserid NUMBER(38) not null references t_user(Userid),-    非空  外键    Touserid NUMBER(38)  not null references t_user(Userid),- 非空  外键    Content1 VARCHAR2(50),       messagetype NUM

22、BER(1) CHECK(messagetype in (0,1),    State NUMBER(1) default 0 CHECK(State in (0,1),    Sendtime DATE default sysdate-    默认值);insert into t_message(fromuserid,touserid,content1,messagetype) values       (10004,10005,'相聚离开 都有时候',0);insert in

23、to t_message(fromuserid,touserid,content1,messagetype) values       (10007,10008,'相聚离开 都有时候',0);select * from t_message;create sequence seq_message; create or replace trigger tir_messaged before insert on t_message for each rowbegin  select seq_message.next

24、val          into :new.Messageid from dual;end;5）管理员表create table t_admin(-（管理员表）-列名  描述  数据类型（精度范围）  空/非空  约束条件    Adminid NUMBER(10) primary key not null,-  非空  主键（自增）    adminname NVARCHAR2(20) unique not null,-  

25、;非空  唯一    Adminpwd NVARCHAR2(100) not null-  非空  );insert into t_admin(adminname,adminpwd) values ('shihua','shihua');select * from t_admin;create sequence seq_admin; create or replace trigger tir_admin before insert on t_admin for each rowbegin

26、60; select seq_admin.nextval          into :new.Adminid from dual;end;3)  创建表主键的约束Ø  按照设计要求建立主键和其他约束2. 增加数据表的数据1)  增加表数据2)  交友策略表和在线状态表一定按要求添加相应的数据3创建视图、序列1)  用序列实现自增主键。用户编号使用序列实现自动增长以保证唯一。所有保证唯一的都可以使用该序列2)  创建视图：查询某个好友的聊天记录drop view v

27、_message;create view v_message asselect fromuserid,touserid,Content1,sendtime from t_message;select * from v_message where fromuserid = &fromuserid and touserid = &touserid;3)  创建视图：查询某个用户的所有好友。create view v_friend asselect userid,friendid from t_friend;select * from v_friend where user

28、id = &userid;4数据的查询1)  注册会员的过程，其实就是将用户提供的一些基本信息输入到用户表的过程，用一条INSERT语句就可以解决问题，考虑到可能会有大量的用户进行注册，为提高系统性能，将此功能设计成一个存储过程。-insert into t_user(Pwd,Nickname,Sex,Birthday,Currstate,friendshippolicy) values        ('shihua','shihua',0,sysdate,1,1);insert into t

29、_user(Pwd,Nickname,Sex,Birthday,Currstate,friendshippolicy) values        ('&密码','&真实姓名',&性别0或1,sysdate,&当前状态请输入1,&交友状态请输入1);-create or replace procedure register_proc(  pwd varchar2,  nickname varchar2,  sex number,  b

30、irthday date,  currstate number,  friendshippolicy number)isbegin  insert into t_user values(userid_seq.nextval,pwd,nickname,sex,birthday,currstate,friendshippolicy);end;set serveroutput on;exec register_proc('123','曹操',1,'01-1月-1995',3,3);2)  从需求来看，不管是基本查

31、询，还是高级查询，都需要根据用户输入的不同而返回不同的结果集，因此，这种情况下可以使用存储过程来完成3)  查询每个用户的所有好友select userid,friendid from v_friend group by friendid,userid;-create or replace procedure all_friend_procis    cursor all_friend_cur is select t_friend.userid from t_friend ;    v_userid t_friend.userid%type;&

32、#160;   v_friendid t_friend.friendid%type;    v_nickname t_user.nickname%type;begin  for fd in all_friend_cur loop    select t_friend.userid,t_friend.friendid,t_user.nickname     into v_userid,v_friendid,v_nickname from t_friend,t_user     where t_friend.userid = t_user.userid and t_friend.userid = fd.userid;    dbms_output.put_line(v_userid|' '|v_friendid|' '|v_nickname);  end loop;end;-4)  查询每个用户和不同好友的所有聊天记录select f.userid,f.friendid,m.cont