create database bbs--创建数据库

use bbs
go

create table bbsUsers--创建用户信息表
(
  UID int identity primary key,--用户编号  UID int 主键  标识列
  uName varchar(10) unique not null,--用户名    uName varchar(10)  唯一约束 不能为空
  uSex varchar(2) check(uSex='男' or uSex='女') not null,--性别      uSex  varchar(2)  不能为空 只能是男或女
  uAge int check(uAge>15 and uAge<60) not null ,--年龄      uAge  int  不能为空 范围15-60
  uPoint int not null check(uPoint>=0) ,--积分      uPoint  int 不能为空  范围 >= 0
);
go

alter table bbsUsers
drop constraint UQ__bbsUsers__9B2C5D809A8D5FB5

insert into bbsUsers(uName,uSex,uAge,uPoint)
 values('小明大','男',45,1) ,
        ('小方大','男',58,5) ,    
		('小梁','男',45,4) ,    
		('小黄','女',56,3) ,    
		('小燕','女',25,2) ,    
        ('小春','女',16,11) ,
	    ('小吹','男',28,4)   ;
		go 


create table bbsTopic--主贴表
(
tID int identity primary key,--主贴编号  tID  int 主键  标识列
tUID int,--发帖人编号  tUID  int 外键  引用用户信息表的用户编号
tSID int  ,--版块编号    tSID  int 外键  引用版块表的版块编号    （标明该贴子属于哪个版块）
tTItle varchar(100) not null,--贴子的标题
tMsg text not null,--帖子的内容
tTime datetime,--发帖时间
tCount int--回复数量 
);
go

insert into bbsTopic(tUID,tSID,tTItle,tMsg,tTime,tCount)
values(1,2,'论老张','老张如何管理自己 快乐',getdate(),5),
       (1,3,'论老张 快乐','老张如何教人做事',getdate(),4),
	   (1,5,'论老张!','老张如何让别人喜欢!','2008-9-10 12:10:00',11),
	   (1,4,'论老张','老张如何娶到自己喜欢的女孩',getdate(),2),
       (1,6,'论老张','老张如何促使别人陪他一起跑步',getdate(),0), 
	   (1,8,'论老张','老张如何被别人抛弃',getdate(),1),
	   (1,7,'论老张','老张如何出差过程中，不中途跑回来',getdate(),3);
	   go



create table bbsReply--回帖表
(
  rID int identity primary key,--回贴编号  rID  int 主键  标识列，
  rUID int  ,--回帖人编号  rUID  int 外键  引用用户信息表的用户编号
  rTID int ,--对应主贴编号    rTID  int 外键  引用主贴表的主贴编号    （标明该贴子属于哪个主贴）
  rMsg text not null,--回帖的内容  rMsg  text  不能为空
  rTime datetime ,--回帖时间    rTime  datetime
);
go

insert into bbsReply(rUID,rTID,rMsg,rTime)
   values(2,1,'你问他',getdate()),
         (2,2,'他就这样，你要原谅他',getdate()),
		 (2,3,'别想',getdate()),
		 (2,4,'呼呼呼，不可能',getdate()),
		 (2,5,'简直了，这没啥好说的',getdate()),
		 (2,1,'他所作的一切',getdate()),
		 (2,7,'把他拐到远一些的地方去',getdate());
		 go



create table bbsSection --版块表
(
  sID int identity  primary key ,--版块编号  sID  int 标识列 主键
  sName varchar(10),--版块名称  sName  varchar(10)  不能为空
  sUid int ,--版主编号  sUid   int 外键  引用用户信息表的用户编号
);
go

insert into bbsSection(sName,sUid)
values('论老张',1),
       ('论老张',2),
	   ('论老张',3),
       ('论老张',4),
	   ('论老张',5),
       ('论老张',6),
	   ('论老张',7);
	   go



--1.在主贴表中统计每个版块的发帖总数
   select count(*) 发帖总数 from bbsTopic 


--2.在回帖表中统计每个主贴的回帖总数量
   select count(*) 回帖总数 from bbsReply
  

--3.在主贴表中统计每个用户的发的主帖的总数
   select tUID 用户编号,sum(tID) 主贴总数 from bbsTopic group by tUID


--4.在主贴表中统计每个用户发的主贴的回复数量总和
   select  tUID 发帖人编号,sum(tCount) 回复总量 from bbsTopic group by tUID
   

--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量
   select tID 主贴编号,avg(tCount) 平均回复量 from bbsTopic group by tID having avg(tCount)>3


--6.在用户信息表中查询出积分最高的用户的用户名，性别，年龄和积分
   select top 1 uName 积分最高的用户名,uSex 积分最高的人的性别,uAge 积分最高的人的年龄,uPoint 积分最高的人的积分
    from bbsUsers
    order by uPoint desc
	
--7.在主贴表中（bbsTopic）中将帖子的内容或标题中有“快乐”两字的记录查询出来
   select tTItle 帖子内容, tMsg 帖子标题 from bbsTopic where tTItle like '%快乐%' or tMsg like '%快乐%'



--8.在用户信息表（bbsUsers）中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来（用多种方法实现）
   select uAge 用户年龄 ,uPoint 用户积分 from bbsUsers group by uAge,uPoint having uAge>=15 and uAge<=20 and uPoint>10


--9.在用户信息表（bbsUsers）中将用户名的第一个字为“小”，第三字为“大”的用户信息查询出来
   select * from bbsUsers where uName like '小_大'


--10.在主贴表（bbsTopic）中将在2008-9-10 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来，并且为列取上对应的中文列名
   select tTime 回帖时间 , tCount 回复数量 from bbsTopic 
   group by tTime,tCount
   having tTime>'2008-9-10 12:00:00' and tCount>10


--11.在主贴表（bbsTopic）中将帖子的标题是以‘！’结尾的帖子的发帖人编号和回复数量查询出来
   select tUID 发帖人编号,tTItle 帖子标题, tCount 回复数量 from bbsTopic 
   where tTItle like '%!'