go
create database bbs
on
(
  name='bbs',
  filename='D:\work\bbs.mfd',
  size=6mb,
  filegrowth=1mb,
  maxsize=unlimited
),
(
  name='bbs1',
  filename='D:\work\bbs1.nfd',
  size=6mb,
  filegrowth=1mb,
  maxsize=unlimited
)
log on
(
  name='bbs-log',
  filename='D:\work\bbs.lfd',
  size=6mb,
  filegrowth=1mb,
  maxsize=unlimited
)

go
use bbs

go
create table bbsUser                  --用户信息表
(
  UID int identity(1,1) ,
  uName varchar(10),
  uSex  varchar(2),
  uAge  int ,
  uPoint  int
)

go
alter table bbsUser
add constraint PK_bbsUser_UID primary key(UID)
alter table bbsUser
add constraint CK_bbsUser_uName check(uName is not null)
alter table bbsUser
add constraint QK_bbsUser_uName unique(uName)
alter table bbsUser
add constraint CK_bbsUser_uSex check(uSex is not null)
alter table bbsUser
add constraint CK1_bbsUser_uSex check(uSex='男'or uSex='女')
alter table bbsUser
add constraint CK_bbsUser_uAge check(uAge is not null)
alter table bbsUser
add constraint CK1_bbsUser_uAge check(uAge>=15 and uAge<=60)
alter table bbsUser
add constraint CK_bbsUser_uPoint check(uPoint is not null)
alter table bbsUser
add constraint CK1_bbsUser_uPoint check(uPoint>=0)

go
create table bbsSection             --版块表
(
  sID  int identity(1,1),
  sName  varchar(10) ,
  sUid   int
)

go
alter table bbsSection
add constraint PK_bbsSection_sID primary key (sID)
alter table bbsSection
add constraint CK_bbsSection_sName check(sName is not null )
alter table bbsSection
add constraint FK_bbsSection_sUid foreign key(sUid) references bbsUser(UID)

go
create table bbsTopic          --主贴表
(
  tID int identity primary key ,
  tUID int ,
  tSID int,
  tTitle nvarchar(100) not null ,
  tMsg text not null,
  tTime  datetime  ,
  tCount  int
)

go
alter table bbsTopic
add constraint FK_bbsTopic_tUID foreign key (tUID) references bbsUser(UID)
alter table bbsTopic
add constraint FK_bbsTopic_tSID foreign key (tSID) references bbsSection(sID)

go
create table bbsReply              --回帖表
(
  rID  int identity primary key,
  rUID  int,
  rTID  int,
  rMsg  text not null ,
  rTime  datetime 
)
go
alter table bbsReply
add constraint fk_bbsReply_rUID foreign key(rUID) references bbsUser(UID)
alter table bbsReply
add constraint fk_bbsReply_rTID foreign key(rTID) references bbsTopic(tID)

go
      -- 1.    小雨点  女  20  0
	  --       逍遥    男  18  4
	  --       七年级生  男  19  2
insert into bbsUser(uName,[uSex],[uAge],[uPoint])
values('小雨点' , '女',  20 , 0),('逍遥' ,'男',  18 , 4),('七年级生', '男' , 19 , 2)
select * from bbsUser
go
      --2.     将bbsUsers表中的用户名和积分两列备份到新表bbsPoint表中，
	  --       提示查询部分列:select 列名1，列名2 from 表名
create table bbsPoint
(
  uName nvarchar(10),
  uPoint int
)
insert into bbsUser([uName],[uPoint]) select * from bbsPoint
select * from bbsPoint

go
       --3.   .给论坛开设4个板块
	  --       名称        版主名
	  --        技术交流    小雨点
	  --       读书世界    七年级生
	  --       生活百科     小雨点
	  --       八卦区        逍遥
insert into bbsSection(sName,[sUid])
values ('技术交流','3'),('读书世界','3'),
       ('生活百科','1'),('八卦区','2')
select * from bbsSection

go
      --4.      向主贴和回帖表中添加几条记录    
insert into bbsTopic([tUID],[tSID],[tTitle],[tMsg],[tTime],[tCount])
values(2,4 ,'范跑跑' ,'谁是范跑跑','2008-7-8',1),
      (3,1 ,' .NET' ,' 与JAVA的区别是什么呀？','2008-9-1',2),
      (1,4 ,'今年夏天最流行什么' ,'有谁知道今年夏天最流行什么呀？','2008-9-10',0)
select * from bbsTopic
insert into bbsReply([rMsg],[rTime],[rTID])     --内容，时间，回帖人
values ('帅哥是范跑跑','2008-7-9',1),
       ('我也不知道','2008-9-2',2),
	   ('白色短袖','2008-9-11',3)
select * from bbsReply


go
      --5.因为会员“逍遥”发表了非法帖子，现将其从论坛中除掉，即删除该用户，请用语句实现
	  --（注意主外键，要删除主键，先要将引用了该主键的外键数据行删除）
delete from bbsUser where uName='2'



go
      --	6.因为小雨点发帖较多，将其积分增加10分
update bbsUser set uPoint=10 where UID=1
select * from bbsUser

go
      --	7.因为板块“生活百科”灌水的人太少，现决定取消该板块，即删除（注意主外键）
delete from bbsSection where sID=3
select * from bbsSection


go
      --	8.因回帖积累太多，现需要将所有的回帖删除
delete from bbsReply
select * from bbsReply
 
 --在论坛数据库中完成以下题目

--1.在主贴表中统计每个版块的发帖总数
 select sum(tUID) 发帖总数 from bbsTopic
--2.在回帖表中统计每个主贴的回帖总数量
    select sum(rUID)回帖总数 from bbsReply
--3.在主贴表中统计每个用户的发的主帖的总数
   select Sum(tcount) from bbsTopic
--4.在主贴表中统计每个用户发的主贴的回复数量总和
 select sum(tCount*tUID)  from bbsTopic
--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量
   select tID 主贴编号, avg(tCount) 平均回复数 from bbsTopic group by  tID  having avg(tCount)>3 
--6.在用户信息表中查询出积分最高的用户的用户名，性别，年龄和积分
  select top 1  uName,uSex,uAge,uPoint from bbsUser order by uPoint desc
--7.在主贴表中（bbsTopic）中将帖子的内容或标题中有“快乐”两字的记录查询出来
select * from bbsTopic where tMsg like '%快乐%'
--8.在用户信息表（bbsUsers）中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来（用多种方法实现）
select * from bbsUser where uAge between 15 and 20 and uPoint>10
select * from bbsUser where uAge>=15 and uAge<=20 and uPoint>10
--9.在用户信息表（bbsUsers）中将用户名的第一个字为“小”，第三字为“大”的用户信息查询出来
 select * from bbsUser where uName like '小_大&'
--10.在主贴表（bbsTopic）中将在2008-9-10 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来，并且为列取上对应的中文列名
 select*from bbsUsers
--11.在主贴表（bbsTopic）中将帖子的标题是以‘！’结尾的帖子的发帖人编号和回复数量查询出来
 select tUID 发帖人编号,tCount 回复数量 from bbsTopic where tMsg like '%！'