use master

create database bbs
on primary
(
name='bbs',
filename='D:\SQL作业.mdf',
size=5MB,
filegrowth=1MB,
maxsize=5MB
)
log on
(
name='bbs_log',
filename='D:\SQL作业_log.ldf',
size=5MB,
filegrowth=1MB,
maxsize=5MB
)
go

use bbs

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



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

create table bbsSection --建立板块表
(
sID int primary key,
sName varchar(10) not null,
sUid int foreign key(sUid)references bbsUsers(useID)
)
go


--主贴表(bbsTopic)
--主贴编号 tID int 主键 标识列,
--发帖人编号 tUID int 外键 引用用户信息表的用户编号
--版块编号 tSID int 外键 引用版块表的版块编号 (标明该贴子属于哪个版块)
--贴子的标题 tTitle varchar(100) 不能为空
--帖子的内容 tMsg text 不能为空
--发帖时间 tTime datetime 
--回复数量 tCount int

create table bbsTopic --建立主贴表
(
tID int primary key,
tUID int foreign key(tUID) references bbsUsers(useID),
tSID int foreign key(tSID) references bbsSection(sID),
tTitle varchar(100) not null,
tMsg varchar(50) not null,
tTime datetime,
tCount int 
)
go


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

create table bbsReply --建立回贴表
(
rID int identity primary key,
rUID int foreign key(rUID) references bbsUsers(useID),
rTID int foreign key(rTID) references bbsTopic(tID),
rMsg text not null,
rTime datetime
)
go

--.现在有3个会员注册成功,请用一次插入多行数据的方法向bbsUsers表种插入3行记录,记录值如下:
--小雨点 女 20 0
--逍遥 男 18 4
--七年级生 男 19 2

select * from bbsUsers
insert into bbsUsers(useID,uName,uSex,uAge,uPoint) values(1,'小雨点','女',20,0)
insert into bbsUsers(useID,uName,uSex,uAge,uPoint) values(2,'逍遥','男',18,4)
insert into bbsUsers(useID,uName,uSex,uAge,uPoint) values(3,'七年级生','男',19,2)

--.将bbsUsers表中的用户名和积分两列备份到新表bbsPoint表中,提示查询部分列:select 列名1,列名2 from 表名
	--插入多行:
		--先创建好空表,然后再插入数据,
		--直接插入数据,然后自动生成表。
	--insert into bbsPoint select ,uPoint from bbsUsers
	--select uName,uPoint into bbsPoint from bbsUsers

	select uName,uPoint into bbsPoint from bbsUsers

	--给论坛开设4个板块
	 --名称 版主名
	 --技术交流 小雨点
	 --读书世界 七年级生
	 --生活百科 小雨点
	 --八卦区 七年级生
select * from bbsSection

insert bbsSection values(1,'技术交流',1)
insert bbsSection values(2,'读书世界',2)
insert bbsSection values(3,'生活百科',3)
insert bbsSection values(4,'八卦区',3)
--向主贴和回帖表中添加几条记录
	 --主贴:
	 --发帖人 板块名 帖子标题 帖子内容 发帖时间 回复数量
	 --逍遥 八卦区 范跑跑 谁是范跑跑 2008年7月8日 1
	 --七年级生 技术交流 .NET 与JAVA的区别是什么呀? 2008年9月1日 2
	 --小雨点 生活百科 今年夏天最流行什么 有谁知道今年夏天最流行什么呀? 2008年9月10日 0
select * from bbsTopic

insert into bbsTopic(tID,tTitle,tMsg,tTime,tCount)
select 1,'范跑跑','谁是范跑跑','2008-7-8',1 union
select 2,'.NET','与JAVA的区别是什么呀?','2008-9-1',2 union
select 3,'今年夏天最流行什么呀?','有谁知道今年夏天最流行什么呀?','2008-9-10',0

--回帖:
	 --分别给上面三个主贴添加对应的回帖,回帖的内容,时间,回帖人自定
select * from bbsReply

insert into bbsReply(rMsg,rTime)
select '我是范跑跑','2008-7-10' union
select '没区别','2008-9-2' union
select '最流行冰激凌','2008-9-10'


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

select (tTitle),sum(tCount)发帖总数 from bbsTopic group by tTitle having sum(tCount)>=0

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

select (rID) from bbsReply group by rID 

--3.在主贴表中统计每个用户的发的主帖的总数
select * from bbsTopic

select (tTitle),sum(tCount)发帖总数 from bbsTopic group by tTitle having sum(tCount)>=0]

--4.在主贴表中统计每个用户发的主贴的回复数量总和

--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量

--6.在用户信息表中查询出积分最高的用户的用户名,性别,年龄和积分
select * from bbsUsers

select uName 用户名,uSex 性别,uAge 年龄,max(uPoint) 积分 from bbsUsers group by uName,uPoint,uSex,uAge having max(uPoint)>2

--8.在用户信息表(bbsUsers)中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来(用多种方法实现)


--9.在用户信息表(bbsUsers)中将用户名的第一个字为"小",第三字为"大"的用户信息查询出来
select * from bbsUsers

select useID,uName,uSex,uAge,uPoint from bbsUsers where uName='小__' or uName='__大'
--10.在主贴表(bbsTopic)中将在2008年9月10日 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来,并且为列取上对应的中文列名

--11.在主贴表(bbsTopic)中将帖子的标题是以‘!’结尾的帖子的发帖人编号和回复数量查询出来
select * from bbsTopic

select tUID,tCount from bbsTopic where tMsg='!'