hooyantsing's Blog

第43次课程

字数统计: 1.8k阅读时长: 9 min
2019/07/21

源辰74班

第43次课程

07.21.2019

**内容    **

Oracle_项目案例[张影]

1.案例_BBS

–原始表结构及数据如下

create table tbl_user(

  userid int primary key,  –账户

  uname varchar2(20),      –用户名

  upass varchar2(100),     –密码

  head varchar2(100),      –头像

  regtime date,            –注册时间

  gender int –性别

);

create sequence seq_tbl_user_uid;

drop sequence seq_tbl_user_uid;

insert into tbl_user (userid,uname,upass,head,regtime,gender) values(seq_tbl_user_uid.nextval,’a’,’a’,’1.gif’,sysdate,1);

insert into tbl_user (userid,uname,upass,head,regtime,gender) values(seq_tbl_user_uid.nextval,’b’,’b’,’2.gif’,sysdate,1);

select * from tbl_user;

delete from tbl_user;

–板块

create table tbl_board(

  boardid int primary key,    –板块地址

  boardname varchar2(50),     –板块名称

  parentid int –父板块

);

create sequence seq_tabl_board_boardid;

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’.net’,0);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’java’,0);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’db’,0);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’project’,0);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’ado.net‘,1);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’asp.net‘,1);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’vb.net‘,1);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’jsp’,2);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’struts’,2);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’hibernate’,2);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’sql’,3);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’oracle’,3);

insert into tbl_board(boardid,boardname,parentid) values(seq_tabl_board_boardid.nextval,’mysql’,3);

select * from tbl_board;

–主题帖

create table tbl_topic(

  topicid int primary key ,

  title varchar2(50),

content varchar2(1000),

  publishtime TIMESTAMP,

  modifytime TIMESTAMP,

  userid int,

  boardid int

);

create sequence seq_tbl_topic_topicid;

drop sequence seq_tbl_topic_topicid;

alter table tbl_topic

add constraint FK_topic_uid

foreign key(userid) references tbl_user(userid);

alter table tbl_topic

add constraint FK_topic_boardid

foreign key(boardid) references tbl_board(boardid);

insert into tbl_topic(topicid,title,content,publishtime,modifytime,userid,boardid)

values(seq_tbl_topic_topicid.nextval,’jsp  good’,’ good,i agree’,sysdate,sysdate,1,8);

insert into tbl_topic(topicid,title,content,publishtime,modifytime,userid,boardid)

values(seq_tbl_topic_topicid.nextval,’jsp is very good’,’very good,i agree’,sysdate,sysdate,1,8);

select * from tbl_topic;

delete from tbl_topic;

create table tbl_reply(

  replyid int primary key ,

  title varchar2(50),

content varchar2(1000),

  publishtime TIMESTAMP,

  modifytime TIMESTAMP,

  userid int,

  topicid int

);

create sequence seq_tbl_reply_replyid;

alter table tbl_reply

add constraint FK_reply_uid

foreign key(userid) references tbl_user(userid);

alter table tbl_reply

add constraint FK_reply_topicid

foreign key(topicid) references tbl_topic(topicid);

_—————————————————————    _

–需求二:求出某个板块下按分页条件查询的贴子

–输出要求:1.分页 2.统计每个帖子下有多少回复数 3.某一个板块开始

–图片二的效果

—————————————————————

–基础数据

insert into tbl_reply(replyid,title,content,publishtime,modifytime,userid,topicid)

values (seq_tbl_reply_replyid.nextval,’jsp is very good reply’,’very good,i agree reply’,sysdate,sysdate,2,2);

select * from tbl_reply;

—————-

–自己练习,已废弃

select a.boardid as 地址,a.parentid as 板块,a.boardname as 论坛,count(tbl_topic.topicid) as 发帖数,nvl(tbl_user.uname,’帖子的作者’)

||’[‘||

nvl(to_char(tbl_topic.publishtime,’yyyy-mm-dd hh:mm:ss’),’这个帖子最后发布的时间’)

||’]’ as 最后发表

from tbl_board a

left join tbl_topic on tbl_topic.boardid = a.boardid

left join tbl_user on tbl_topic.userid = tbl_user.userid

where a.parentid != 0

group by a.boardid,a.boardname,a.parentid,tbl_user.uname,tbl_topic.publishtime

order by 板块 asc;

—————-

–======================================================================================———————

–1.先查出板块最新帖子的修改时间

select boardid,max(modifytime) as modifytime

from tbl_topic

group by  boardid;

–2.再查出每个帖子的信息,关键是发帖人的信息(名字,编号),贴子标题,贴子编号

select topicid,title,modifytime,tbl_user.userid as userid,uname,boardid

from tbl_topic

left join tbl_user

on tbl_topic.userid = tbl_user.userid;

–3.将上面两步整合,求每个板块最后一个贴子的信息

select title,uname,a.modifytime,topicid,a.boardid

from (

select boardid,max(modifytime) as modifytime

from tbl_topic

group by  boardid

     ) a,

     (

select topicid,title,modifytime,tbl_user.userid as userid,uname,boardid

from tbl_topic

left join tbl_user

on tbl_topic.userid = tbl_user.userid

     ) b

where a.boardid = b.boardid and a.modifytime = b.modifytime;

–4.统计每个板块的贴子数

select tbl_board.boardid as boardid,boardname,parentid,count(tbl_topic.topicid)

from tbl_board

left join tbl_topic

on tbl_board.boardid = tbl_topic.boardid

group by tbl_board.boardid,boardname,parentid;

–5.使用左外联将板块信息和板块下最新贴子信息整合

select b.boardid as boardid,boardname,parentid,total,topicid,title,modifytime,userid,uname

from (

select title,uname,a.modifytime,topicid,a.boardid,userid

from (

select boardid,max(modifytime) as modifytime

from tbl_topic

group by  boardid

           ) a,

          (

select topicid,title,modifytime,tbl_user.userid as userid,uname,boardid

from tbl_topic

left join tbl_user

on tbl_topic.userid = tbl_user.userid

           ) b

where a.boardid = b.boardid and a.modifytime = b.modifytime

     ) a

right join

     (

select tbl_board.boardid as boardid,boardname,parentid,count(tbl_topic.topicid) as total

from tbl_board

left join tbl_topic

on tbl_board.boardid = tbl_topic.boardid

group by tbl_board.boardid,boardname,parentid

     ) b

on a.boardid = b.boardid;

—————=================================================================================——

–1.统计每个贴子的回复数量

select topicid,count(replyid) as total

from tbl_reply

group by topicid;

–2.按分页条件查这个boardid下当前页的贴子信息

–boardid:以后由java程序传来的参数

–pages:第几页

–pagesize:每页几条

select *

from (

select rownum as rn,a.*

from(

select topicid,title,content,publishtime,modifytime,tbl_user.userid as userid,uname,boardid

from tbl_topic

inner join tbl_user

on tbl_topic.userid = tbl_user.userid

where boardid = 8

order by modifytime desc

            ) a

where rownum <= 5

      )

where rn >= 1;

–3.合并以下

select a.topicid,title,content,publishtime,modifytime,userid,uname,boardid,total

from (

select *

from (

select rownum as rn,a.*

from(

select topicid,title,content,publishtime,modifytime,tbl_user.userid as userid,uname,boardid

from tbl_topic

inner join tbl_user

on tbl_topic.userid = tbl_user.userid

where boardid = 8

order by modifytime desc

                  ) a

where rownum <= 5

            )

where rn >= 1

      ) a

left join

     (

select topicid,count(replyid) as total

from tbl_reply

group by topicid

     ) b

on a.topicid = b.topicid

order by modifytime desc;

–4.更高级:优化版

select a.topicid,title,content,publishtime,modifytime,userid,uname,boardid,total

from (

select *

from (

select rownum as rn,a.*

from(

select topicid,title,content,publishtime,modifytime,tbl_user.userid as userid,uname,boardid

from tbl_topic

inner join tbl_user

on tbl_topic.userid = tbl_user.userid

where boardid = 8

order by modifytime desc

                  ) a

where rownum <= 5

            )

where rn >= 1

      ) a

left join

     (

select topicid,count(replyid) as total

from tbl_reply

where topicid in(

select topicid

from (

select rownum as rn,a.*

from (

select topicid

from tbl_topic

inner join tbl_user

on tbl_topic.userid = tbl_user.userid

where boardid = 8

order by modifytime desc

                                 ) a

where rownum <= 5

                            )

where rn >= 1

                       )

group by topicid

      ) b

on a.topicid = b.topicid

order by modifytime desc;

目标

**1.PNG

**

讲师源码图

**1.png

**

**2.png

**

2.奇偶行统计

/*

只能依靠标识列的值来进行判断和选取

因为数据行可能存在增加、修改和删除,因此标识列的数据值并不“完全可靠”,例如标识列值为3,并不一定是第三行,因为如果第二行被删除了,它就是第二行,实际上也是偶数行

根据我们前面使用过的SELECT…INTO,可以创建一张新表,顺便创建新的标识列,再在新的标识列上执行奇偶判断

奇数判断依据:标识列值%2不等于0;偶数判断依据:标识列值%2等于0

*/

create table test1(

       tid int primary key,

       tname varchar2(10),

       price number(6)

);

insert into test1 values(1,’苹果’,56);

insert into test1 values(3,’香蕉’,435);

insert into test1 values(5,’橘子’,234);

insert into test1 values(6,’火龙果’,187);

insert into test1 values(7,’猕猴桃’,323);

insert into test1 values(8,’樱桃’,450);

insert into test1 values(10,’西瓜’,686);

commit;

–奇偶行统计

select

decode(rn,1,’奇数行统计’,’偶数行统计’),sum(a.price) as 结果

from(

select t.*,mod(rownum,2) rn

from test1 t

     ) a

group by rn;

342e6a440303710c436ca87eb2f2f843.png

564228a141cf0007a12c774fc4cb296c.png

3.extract() 拆分日期类型

4.png

CATALOG