use master
go

create database bbs
go
use bbs
go

create table bbsUsers
(
	UID int identity(1,1) primary key,	
	uName varchar(10) unique not null,	
	uSex  varchar(2) check(uSex='男' or uSex='女') not null,	
	uAge  int not null check(uAge>=15 and uAge<=60),	
	uPoint  int not null  check(uPoint>=0),
);
go

create table bbsSection
(
	sID  int identity(1,1) primary key,	
	sName  varchar(10) not null,	
	sUid   int references bbsUsers(UID)	,
);
go

create table bbsTopic
(
	tID  int identity(1,1) primary key,	
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,	
	tMsg  nvarchar(max) not null,
	tTime  datetime,	
	tCount  int ,
);
go
 


create table bbsReply
(
	rID  int identity(1,1) primary key,
	rUID  int references bbsUsers(UID),
	rTID  int references bbsTopic(tID),
	rMsg  nvarchar(max) not null ,
	rTime  datetime,
)
go

insert into bbsUsers(uName,uSex,uAge,uPoint)
select '小雨点','女',20,0 union
select  '逍遥','男',18,4 union 
select '七年级生','男',19,2 union 
select '小丸子','女',17,12 union
select '小心大熊','女',28,15
go

insert into bbsSection(sName,sUid) values ('技术交流',1)
insert into bbsSection(sName,sUid) values('读书世界',2)
insert into bbsSection(sName,sUid) values('生活百科',1)
insert into bbsSection(sName,sUid) values('八卦区',2)
go

insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (2,4,'范跑跑','谁是范跑跑','2008-7-8',1)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (3,1,'.NET','与JAVA的区别是什么呀？','2008-9-1',2)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (1,3,'今年夏天最流行什么 ','有谁知道今年夏天最流行什么呀？','2008-9-10',0)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (4,2,'读书使我快乐! ','你们都喜欢读什么书呢?','2011-9-10',13)

insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (1,3,'先有鸡还是先有蛋，终于有答案了!','一个英国的科学家在经过了一系列的研究之后，发现鸡蛋的形成是需要一种特别的蛋白质，这个特殊的蛋白质是在母鸡们的身体中产生的，如果没有这种特殊的蛋白质就不会形成鸡蛋。换句话说，如果没有母鸡就不会产生这些个物质，也就不会有鸡蛋。因此，先有鸡后有蛋才是符合科学。接下来，我们就不禁又要问：既然是先有鸡后有蛋，那么鸡又是怎么来的呢？',getdate(),0)
go

insert into bbsReply(rUID,rTID,rtime,rMsg)
values(1,1,'2008-7-10','就那啥，网红呗')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(1,2,'2008-9-2','.net是一个跨语言的平台；java是一个开源的跨平台的语言；')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(2,2,'2008-9-12','如果开发windows应用那还是.net 好一些，因为windows和.net都是微软的产品，所以在开发window应用方面.net更兼容一些。')

insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','秘密花园')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 21:43:00','草房子')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','我爸爸')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 21:43:00','活了一百万次的猫')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','红字')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 21:43:00','洛丽塔')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','哈姆雷特')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 21:43:00','苏菲的世界')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','小王子')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 21:43:00','红鞋子')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','一片叶子落下来')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 21:43:00','沉重的肉身')
insert into bbsReply(rUID,rTID,rtime,rMsg)
values(5,4,'2011-9-11 20:43:00','文化苦旅')
go

select * from bbsUsers

select * from bbsSection

select * from bbsTopic

select * from bbsReply





select * from bbsTopic
select tSID,count(tUID) 发帖总数 from bbsTopic
group by tSID ;
select tSID,count(tID) 发帖总数 from bbsTopic
group by tSID ;
create table bbsTopic
(
	tID  int identity(1,1) primary key,	
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,	
	tMsg  nvarchar(max) not null,	
	tTime  datetime,	
	tCount  int	,
);
go


insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (2,4,'范跑跑','谁是范跑跑','2008-7-8',1)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (3,1,'.NET','与JAVA的区别是什么呀？','2008-9-1',2)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (1,3,'今年夏天最流行什么 ','有谁知道今年夏天最流行什么呀？','2008-9-10',0)
insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (4,2,'读书使我快乐! ','你们都喜欢读什么书呢?','2011-9-10',13)

insert into bbsTopic(tUID,tSID,tTitle,tMsg,tTime,tCount)
values (1,3,'先有鸡还是先有蛋，终于有答案了!','一个英国的科学家在经过了一系列的研究之后，发现鸡蛋的形成是需要一种特别的蛋白质，这个特殊的蛋白质是在母鸡们的身体中产生的，如果没有这种特殊的蛋白质就不会形成鸡蛋。换句话说，如果没有母鸡就不会产生这些个物质，也就不会有鸡蛋。因此，先有鸡后有蛋才是符合科学。接下来，我们就不禁又要问：既然是先有鸡后有蛋，那么鸡又是怎么来的呢？',getdate(),0)
go

create table bbsReply
(
	rID  int identity(1,1) primary key,
	rUID  int references bbsUsers(UID),
	rTID  int references bbsTopic(tID),
	rMsg  nvarchar(max) not null,
	rTime  datetime,
);
go
select  rTID,COUNT(rUID) 回帖数量  from bbsReply
group by  rTID
--
select * from bbsReply

create table bbsTopic
(
	tID  int identity(1,1) primary key,	
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,
	tMsg  nvarchar(max) not null,	
	tTime  datetime,	
	tCount  int	,
);
go

select tUID 用户,count(tSID) 主贴的总数 from bbsTopic
group by tUID
select * from bbsTopic
create table bbsTopic
(
	tID  int identity(1,1) primary key,
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,	
	tMsg  nvarchar(max) not null,	
	tTime  datetime,	
	tCount  int	,
);
go
select tUID 用户,sum(tCount) 主贴的回复数量总和 from bbsTopic
group by tUID

select tID 用户,sum(tCount) 主贴的回复总数 from bbsTopic
group by tID
select * from bbsTopic
create table bbsTopic
(
	tID  int identity(1,1) primary key,	
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,
	tMsg  nvarchar(max) not null,	
	tTime  datetime,	
	tCount  int	,
);
go

select * from bbsTopic
group by ;
select  tSID,avg(tcount) 平均回复数量 from bbsTopic
where tCount>3
group by  tSID 



create table bbsTopic
(
	tID  int identity(1,1) primary key,	
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,	
	tMsg  nvarchar(max) not null,	
	tTime  datetime,	
	tCount  int	-
)


create table bbsUsers
(
	UID int identity(1,1) primary key,	
	uName varchar(10) unique not null,	
	uSex  varchar(2) check(uSex='男' or uSex='女') not null,	
	uAge  int not null check(uAge>=15 and uAge<=60),	
	uPoint  int not null  check(uPoint>=0)	
)
go
select * from bbsUsers
select top 1 *  from bbsUsers order by uPoint desc
group by  uName,uSex ,uAge 

order by itemName
select * from bbsTopic where tTitle tMsg  like '%快乐%';
select * from bbsTopic
select * from bbsUsers where uAge between '15' and '20'  and uPoint>10
select * from bbsUsers where uAge >=15 and uAge<=20  and uPoint>10
select * from bbsUsers where uAge like '[15-20]' and uPoint>10--不行
select * from bbsUsers where uName like '小%__'
select * from bbsUsers where uName like '%__大%'
select * from bbsTopic where tTime >'2008-9-10 12:00:00' and tCount>10;
delete from bbsTopic;
create table bbsTopic
(
	tID  int identity(1,1) primary key,	
	tUID  int references bbsUsers(UID),	
	tSID  int references bbsSection(sID),	
	tTitle  varchar(100) not null,	
	tMsg  nvarchar(max) not null,	
	tTime  datetime,	
	tCount  int	,
);
go
select * from bbsTopic where tTitle like '%!'