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='!'