SQL server子查询

exec xp_cmdshell 'md E:/project'

--先判断数据库是否存在如果存在就删除
if exists(select * from sysdatabases where name='bbsDB')
drop database bbsDB
--创建数据库文件
create database bbsDB
--主数据库文件
 on primary
(
 name='bbsDB_data',--为主要数据库文件命名
 filename='E:/project/bbsDB_data.mdf',--主数据库文件的路径
 size=10mb--初始大小
)
log on(
--日志文件
 name='bbsDB_log',
 filename='E:/project/bbsDB_log.ldf',
 size=3mb,
 maxsize=20mb--最大增长量为
)
go
use bbsDB
drop table BBSUsers

create table BBSUsers(
 UID   int identity(1,1) primary key  not null,--标识列自增长
 UName   varchar(15)  not null,--用户名，昵称
 UPassword  varchar(16)   not null,--密码不能少于6位数默认为（888888）
 USex   bit    not null,--性别1代表男 
 UEmail  varchar(20)       null, --电子邮件必须包含@默认值为（P@P.com）
 UClass   int    not null,--用户的等级(1)
 UregDate datetime      null,--注册日期（注册时系统时间）
 Uremark  varchar(255)      null, --备注信息（备注）
 Upoint  int   not null,--用户的积分，点数（20）
 UBirthday   datetime       null,--用户生日
 Ustate  int       null--状态（0默认为离线）
)
go

select UPassword from BBSUsers

alter table BBSUsers-- 为密码添加检查约束长度大于=6的长度
 add constraint CK_upassword check(len(UPassword)>=6)
alter table BBSUsers--为密码添加默认约束(888888)
 add constraint DE_upassword default ('888888') for UPassword
alter table BBSUsers--Email检查约束@
 add constraint CK_uemail check (UEmail like '%@%')
alter table BBSUsers--为E-mail添加默认约束P@P.com
 add constraint DE_uemail default('P@P.com')for UEmail
alter table BBSUsers--为性别添加默认约束约束
 add constraint DE_usex default('1') for USex
alter table BBSUsers--用户等级设置默认约束
 add constraint DE_uclass default('1')for UClass
alter table BBSUsers--为注册日期设置默认约束
 add constraint DE_uregDate default('注册时系统时间')for UregDate
alter table BBSUsers--为备注设置默认约束
 add constraint DE_uremark default('备注')for Uremark
alter table BBSUsers--为状态设置默认约束
 add constraint DEu_state default ('0')for Ustate

create table bbsTopic(
 TID   int identity(1,1) primary key not null,--贴子编号，自动增长
 TSID   int    not null,--版块编号；外键，引用bbsSecton表的主键SID
 TUID   int    not null,--发贴人ID；外键，引用bbsUsers表的主键UID
 TReplyCount  int           null,--回复数量
 TFace   int        null,--发贴表情
 TTopic   varchar(20)  not null,--标题
 TContents  varchar(50)  not null,--正文必须大于6个字符
 TTime   datetime     null,--发贴时间
 TClickCount  int      null,--点击数
 Tstate   int   not null,--状态，例如是否被锁，是否为精华贴
 TlastReply  datetime      null--最后回复时间，必须晚于发贴时间
)
go

-------drop table bbsTopic
alter table bbsTopic
 add constraint CK_tlastreply check (TlastReply>TTime)
alter table bbsTopic--引用bbsSecton表的主键UID
 add constraint FK_tsid foreign key(TSID) references bbsSection(SID)
alter table bbsTopic --引用bbsSecton表的主键UID
 add constraint FK_tuid foreign key(TUID) references bbsUsers(UID)
alter table bbsTopic --为默认约束
 add constraint DE_treplycount default ('0') for TReplyCount
alter table bbsTopic--为TContents设置检查约束
 add constraint CK_tcontents check (len(TContents)>=6)
alter table bbsTopic--为发贴时间设置约束
 add constraint DE_time default ('当天') for TTime
alter table bbsTopic--为点击设置默认约束
 add constraint DE_tclickcount default('0')for TClickCount
alter table bbsTopic --为状态设置默认约束
 add constraint DE_tstate default('1') for Tstate

create table bbsReply(
 RID  int  identity(1,1) primary key not null,--自动编号，贴子编号
 RTID  int   not null,--主贴ID；外键，引用bbsTopic表的主键TID
 RSID  int   not null,--版块ID；外键，引用bbsSection表的主键SID
 RUID  int   not null,--回贴人ID，外键，引用bbsUsers表的主键UID
 Rface  int       null,--回贴表情
 Rcontents varchar(50)  not null,--正文，必须大于6个字符
 Rtime  datetime         null,--回贴时间
 RclickCount int      null --点击数
)
go

------------drop table bbsReply
alter table bbsReply--为RTID设置外键约束
 add constraint FK_rtid foreign key(RTID) references bbsTopic(TID)
alter table bbsReply--为RSID设置外键约束
 add constraint FK_rsid foreign key(RSID) references bbsSection(SID)
alter table bbsReply--为RUID设置外键约束
 add constraint FK_ruid foreign key(RUID) references bbsUsers(UID)

create table bbsSection(
 SID  int identity(1,1) primary key not null,--版块编号，自动增长
 Sname   varchar(32)  not null,--版块名称
 SmasterID  int    not null,--版主的用户ID，外键；引用用户表bbsUsers的UID
 Sprofile  varchar(50)     null,--版面简介
 SclickCount  int       null,--点击率
 StopicCount  int       null--发贴数
)
go
-------------drop table bbssection

alter table bbsSection--外键约束引用bbsusers的UID
 add constraint FK_smasterid foreign key(SmasterID) references BBSUsers(UID)

 --作业
/*
  建库，如果存在则先删除。请问T-SQL语句如何写？其原因是什么?
  答：if exists (select * from sysdatabases where name='数据名')
   drop database 数据库名
   先删除是为了不和以前的数据库重名
  建表，如果存在则先删除。请问T-SQL语句如何写？其原因是什么？
  答：if exists(select * from sysobjects where name='表名')
   drop table 表名
   先删除表是为不覆盖以前的表
  char(6)与varchar(6)的区别?
  答：cahr 是不可变长、varchar是可变长度
  约束的类型有哪些?
  SQL server 三层安全模型是指？
  答：登陆权限、访问权限、创建表的权限
  如何修改sa帐号的密码?
  答: exec xp_password 'sa','pass'
      */

------------------------------------------------------------------------------6.17作业
print '系统的版本号：'+@@version
print '服务器的名称'+@@servername
print '最后的错误号为：'+@@error
--用户表
 insert into bbsusers (UName,UPassword,UEmail,UBirthday,USex,UClass,Uremark,UregDate,Ustate,Upoint) values( '可卡因','HYXS007','ss@hotmail.com','1978-07-09','1','1','我要去公..','2007-10-10','1','200')
 insert into bbsusers (UName,UPassword,UEmail,UBirthday,USex,UClass,Uremark,UregDate,Ustate,Upoint) values( '新酸果冻',default,'yy@hotmail.com','1980-02-12','0','2','走遍天崖..','2007-10-10','2','600')
 insert into bbsusers (UName,UPassword,UEmail,UBirthday,USex,UClass,Uremark,UregDate,Ustate,Upoint) values( '冬篱儿','fangfong','bb@sohu.com','1976-10-03','1','3','爱迷失在..','2007-10-10','1','1200')
 insert into bbsusers (UName,UPassword,UEmail,UBirthday,USex,UClass,Uremark,UregDate,Ustate,Upoint) values( 'Super','master','dd@hotmail.com','1977-05-16','1','5','BBS大斑竹','2007-10-10','1','5000')

select * from BBSUsers
--发贴表
 insert into bbsTopic (TSID,TUID,TReplyCount,TFace,TTopic,TContents,TTime,TClickCount,Tstate,TlastReply)values( 1,1,2,1,'还是jsp中..','jsp文件中读..','2007-10-11',200,1,'2007-10-12')
 insert into bbsTopic (TSID,TUID,TReplyCount,TFace,TTopic,TContents,TTime,TClickCount,Tstate,TlastReply)values( 2,2,0,2,'部署.net中..','项目包括wi','2007-10-11',0,1,'2007-10-12' )

select * from bbstopic
--回复表
 insert into bbsReply (RTID,RSID,RUID,Rface,Rcontents,Rtime,RclickCount)values( 1,1,1,2,'数据库连接池在..','2007-10-12',100)
 insert into bbsReply (RTID,RSID,RUID,Rface,Rcontents,Rtime,RclickCount)values( 1,1,4,4,'public static DataSo..','2007-10-12',200)
 insert into bbsReply (RTID,RSID,RUID,Rface,Rcontents,Rtime,RclickCount)values( 2,2,2,3,'帮测试人员架ASP.NET环','2007-10-10',0)

select * from bbsreply

--版块表
 insert into bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)values( 'Java技术',2,'讨论Java相关技术,包括J2EE,J2ME,J2SE',500,1)
 insert into bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)values( '.NET',3,'讨论Web Service/XML.NET Remoting',800,1)
 insert into bbsSection (Sname,SmasterID,Sprofile,SclickCount,StopicCount)values( 'Linux/Unix社区',4,'包含系统维护与使用区，程序开发区',0,0)

select * from bbssection

-----查询可卡因的状态
select uname as 昵称, uclass as 等级,uremark as 个人说明,upoint as 积分 ,Ustate as 状态 from bbsusers where uname='可卡因'

-----查询可卡因的发贴情况
declare @name varchar(15)
set @name='可卡因'
declare @id int
select @id=uid from bbsusers where uname=@name
select TSID as 版块编号,TTopic as 标题,TContents as 正文,TTime as 发贴时间 from bbstopic where tuid =@id

------查询可卡因的回贴情况

select rtid as 主贴ID, rsid as 版块ID,Rcontents as 正文,Rtime as 回贴时间 from bbsreply where ruid=@id

------判断状态是否有权限发贴
declare @uu int
 select @uu=ustate from bbsusers where uid=@id
 if(@uu=1)
 begin
  print '可卡因的权限：有权发贴'
 end
    else
 begin
  print '可卡因的权限：无权发贴'
end

-----等级评定

print '开始提分，请稍后....'
print '提升分值：350'

declare @point int
set @point=350
update bbsusers set Upoint=Upoint+@point --为用户提分

set nocount on--不影响多少行

print'---------------加分后的用户级别情况---------------'

select uname as 昵称,星级=case
  when Upoint<500 then '★'
  when Upoint between 500 and 1000 then '★★'
  when Upoint between 1000 and 2000 then '★★★'
  when Upoint between 2000 and 4000 then '★★★★'
  when Upoint between 4000 and 5000 then '★★★★★'
  else
   '★★★★★★'
 end ,Upoint as '积分'
from bbsusers

---查询心酸果冻的发贴数如果发贴数>0显示总数和具体内容 否显示总数=0
declare @name varchar(15)
 set @name='新酸果冻'
declare @uid int
select @uid=uid from bbsusers where uname=@name
declare @Tin int
select @Tin=count(*) from bbstopic where tuid=@uid--统计发贴数

if(@Tin>0)
 begin
  print '新酸果冻发贴数：'+convert(varchar,@Tin)+'贴子如下:'
  select TTime as 发贴时间, TClickCount as 点击率,TTopic as 主题,TContents as 内容 from bbstopic where tuid=@uid
  
 end
 else
 begin
  print '新酸果冻发贴数：0'
 end
---查询心酸果冻的发贴数如果回贴数>0显示总数和具体内容 否显示总数=0
declare @Rin int
 select @Rin=count(*) from bbsreply where ruid=@uid--统计回贴数
if(@Rin>0)
 begin
  print '新酸果冻的回贴数：'+convert(varchar,@Rin)+'贴子如下：'
  select RTime as 回贴时间, RClickCount as 点击率,Rcontents as 内容 from bbsreply where ruid=@uid
  
 end
 else
 begin
  print '新酸果冻回贴数:0'
 end
----判断发贴级别
  if((@Tin+@Rin) <20)
   begin
    print '新酸果冻贴数总计：'+convert(varchar,(@Tin+@Rin))+'贴补功臣级别：新手上路'
   end
   else if((@Tin+@Rin) between 20 and 100)
   begin
    print '新酸果冻贴数总计：'+convert(varchar,(@Tin+@Rin))+'贴补功臣级别：贴子红人'
   end
   else
   begin
    print '新酸果冻贴数总计：'+convert(varchar,(@Tin+@Rin))+'贴补功臣级别：论坛元老'
   end

---作业
/*
 1.为什么要在语句后面加个GO？
  答：在语句后面加个GO是作为批处理，也是为了提升语句的执行速度，
 2.全局变量不能定义，但在查询分析器中为什么可以这样写：declare @@b int
  答：可以这样写没有错误，因为全局是系统定义，我们只可以调用，写declare @@b int 系统当成declare @b int
 3.批处理中包含多条语句，如果有一条语句执行错误，是不是整个批处理都停止执行？
  答：如果整个批处理有错误,是先把错误之前的语句执行，然后在停止，
   如果用事务可以回滚原来的位置
   degin transaction 开始事务
   commit transaction 提交事务(判断事务是否有错)
   rollback transaction 回滚事务(返回原来的位置)
 4.break和return有什么区别?
  答：break如果在逻辑运算中为终止
      return 是返回函数的数据
 5.预习提：简述子查询和联接查询的区别.
  答：子查询是在查询语句中嵌套查询语句可以多表查询，
  联接查询是两张表相互连接多个表，可以包含多个表的数据
         */

select * from bbsreply--回贴表
select * from bbstopic--发贴表
select * from BBSUsers
select * from bbssection

-----------------------------------------------------------------------------6.19作业
--------论坛人气评估
declare @tclickcount int
declare @RclickCount int
select @tclickcount=TClickCount from bbstopic--统计发贴数
select @RclickCount=RclickCount from bbsreply--统计回贴数
print '------->>>>>>各位大虾注意了，本论坛即将发布年度无记名评奖<<<<<<<<<<-----'
if((@tclickcount+@RclickCount)>1000)
 begin
  print '论坛人气年度评估：  人气熊旺旺，大家辛苦了!'
 end
 else
 begin
  print '论坛人气年度评估：  一般般，大家多多努力!'
 end
-----年度品牌版块
select Sname as 版块名称,StopicCount as 主贴数量 ,Sprofile as 版面简介 from bbssection where StopicCount>0

------年度倒胃版块

select top 1 Sname as 版块名称,StopicCount as 主贴数量,Sprofile as 版面简介 from bbssection order by StopicCount asc

-----年度回贴人气最IN的前两名获奖作者
select top 2 uname as 大名 ,uclass as 星级 from bbsusers  where  uid in (select ruid from bbsreply) order by uclass desc

----请下列版块的斑竹加加油哦！

 
select Sname as 版块名称,StopicCount as 主贴量 from bbssection where SclickCount<500 or StopicCount<0

---------------------------------阶段二

select * from bbsreply--回贴表
select * from bbstopic--发贴表
select * from BBSUsers
select * from bbssection

---为发贴表建立一个触发器如果插入的话就在版块+1
create trigger bbstopic_bbtion
on bbsTopic
for insert
as
declare @tsid int
select @tsid=tsid from inserted
update bbssection set stopiccount=stopiccount+1 where sid=@tsid

---添加主贴
insert into bbsTopic values(2,2,0,1,'什么是.NET啊','我靠！微软的.NET广告超过半个北京城啊.','2007-10-11',0,1,getdate())

--用户发主贴后，酌情加分：如果主贴是新题（没人提及过）,加100分，否则加50分
declare @ttopic varchar(20)
set @ttopic='什么是.NET啊'
declare @name varchar(20)
select @name=uname from bbsusers where uid in(select tuid from bbstopic where ttopic=@ttopic)
if exists(select * from bbstopic where ttopic=@ttopic)--判断结果集合是否为空
 begin
  update BBSUsers set upoint=upoint+100 where uname=@name
 end
else
 begin
  update BBSUsers set upoint=upoint+50 where uname=@name
 end

select  uname as 发贴作者,bbstopic.TTime as 发贴时间,bbstopic.TTopic as  主题,bbstopic.TContents as 内容 from BBSUsers inner join bbstopic on BBSUsers.uid=bbstopic.tuid where uname='新酸果冻'

-----------添加后的等级评定
select uname as 昵称,星级=case
  when Upoint<500 then '★'
  when Upoint between 500 and 1000 then '★★'
  when Upoint between 1000 and 2000 then '★★★'
  when Upoint between 2000 and 4000 then '★★★★'
  when Upoint between 4000 and 5000 then '★★★★★'
  else
   '★★★★★★'
 end ,Upoint as '积分'
from bbsusers

--------------------第三阶段

select * from bbstopic--发贴表
select * from bbsreply--回贴表
select * from BBSUsers
select * from bbssection

-----为回贴表建立一个触发器
create  trigger bbsusers_bbsreply
on bbsreply
for insert
as
declare @rid int---声明一个变量并且赋值
select @rid=rid from inserted---查询零时表
update bbstopic set tclickcount=tclickcount+1,TReplyCount=TReplyCount+1 where TID = (select RTID from bbsreply where rid=@rid)---发贴表回复+1.点击+1
update bbssection set sclickcount=sclickcount+1 where sid =(select rsid from bbsreply where rid=@rid)--版块+1
update bbsreply set RclickCount=RclickCount+1 where rid=@rid---回贴表中点击+1

drop trigger bbsusers_bbsreply
---插入数据行
insert into bbsreply values(3,2,1,3,'.NET是微软力推的企业级信息网络共享平台.',getdate(),0)
---声明一个变量装回帖内容
declare @rcontents varchar(50)
---为变量赋值
set @rcontents='.NET是微软力推的企业级信息网络共享平台.'
---声明一个变量装姓名
declare @name varchar(10)
select @name=uname from bbsusers where uid in(select ruid from bbsreply where Rcontents=@rcontents)
if exists ((select count(*) from bbsreply where Rcontents=@rcontents)=0)
 begin
  update BBSUsers set upoint=upoint+100 where uname=@name 
 end
else
 begin
  update BBSUsers set upoint=upoint+50 where uname=@name
 end

------作业
 /*
 1.在学员系统中，使用子查询，统计缺考的学员名单。
  答;以上课的数据表为列：select * from student where stuNO in(select stuNO from stuMarks ))
   如果成绩为空的话就说明这个学生缺考学生
`    可以建立一个视图来统计缺考成绩
 2.改进综合案例，显示加分科目，笔试成绩，机试成绩，加分值是多少。
  答：以上课的数据表为列：
   
 3.预习作业：什么是索引？什么是事务？什么是视图？
  答：索引可分为两中索引聚集索引和非聚集索引
   事务是判断一个数据是否成立如果成立则继续否则返回开始值
    视图是建立一个虚拟的一张表
    */
 create table emp(
 id int identity(1,1)not null,--学员ID自增长量
 name varchar(10) not null,---学员姓名
 age int not null,----学员年龄
 wh varchar(10) not null----学员学历
 )
 go

 insert into emp values('moner','29','本科以上')
 insert into emp values('rose','20','本科以上')
 insert into emp values('aa','21','本科以上')
 insert into emp values('bb','21','大专')
 insert into emp values('cc','21','大专')
 insert into emp values('dd','21','高中')
 insert into emp values('d4','26','高中')
 insert into emp values('ee','21','初中以下')
 insert into emp values('ef','27','初中以下')
 insert into emp values('eg','35','初中以下')
 insert into emp values('d6','28','高中')
 insert into emp values('d9','33','高中')

----要求：统计出表中学历为本科以上，大专，高中，初中以下各有多少人，平均年龄，占总人数年的百分比。
 select count(*) from emp
 select count(*) from emp where wh='本科以上'
 select count(*) from emp where wh='大专'
 select count(*) from emp where wh='高中'
 select count(*) from emp where wh='初中以下'
select top 1 本科以上=(select count(*) from emp where wh='本科以上'),大专=(select count(*) from emp where wh='大专'),高中=(select count(*) from emp where wh='高中'),初中以下=(select count(*) from emp where wh='初中以下'),平均年龄=(select sum(age) from emp)/(select count(*) from emp),站总人数的百分比=((select count(*) from emp where age>=(select avg(age) from emp))*100/(select count(*) from emp)) from emp