北大青鸟s2Sql第三章

--声明临时表
declare @myTable table
(
  id varchar(30) ,
  score int
)
insert into @myTable select examNo,writtenExam from stuMarks

select * from @myTable

--case的多种用法
select name,score,case score
                      when  null then '缺考'
                      else
                          '已考'
                   end
 from students,score
where id = stuid

select name,score,case
                      when score is not null then score
                      when score is  null then '缺考'
                   end
 from students left join score
on id = stuid

select score = case
  when score is null then 0
                else
                    score
         end
 from students left join score
on id=stuid

create table Users
(
UID int identity(1,1) primary key not null ,
UName varchar(32) not null,
UPassword varchar(16) default '8888' check(len(UPassword)>=6)not null,
USex Bit default(1) not null,
URegDate datetime default(getdate()) not null ,
UState int default(1), --是否在线,隐身 0离线 1隐身 2上线
UPoint int default(20) --用户积分
)

create table userLog
(
TID int identity(1,1) primary key not null,
TUID int foreign key references Users(UID) not null, --用户ID
TReplyCount int, --回复数量
TTopic varchar(255) not null, --标题
TContents ntext not null, --正文
TTime datetime, --发贴时间
TClickCount int, --点击数
TFlag int not null, --状态,例如是否被锁
)
create table userMusic
(
  tid int identity(1,1) primary key,
  tuid int foreign key references Users(UID) not null,
  musicname varchar(20),
  path varchar(50),
  register datetime default(getdate())
)
--drop table userLog
--drop table userMusic

insert into Users values('痴心男孩','123456',1,'2005-07-8',0,300)
insert into Users values('痘痘','123456',0,'2007-05-3',0,300)
insert into userLog values(2,0,'2008新劳动法','2008新劳动法不爽','2008-1-14',100,0)
insert into userLog values(1,0,'过年了列','哈哈!!!!','2008-1-14',100,0)
insert into userMusic values(1,'爱我还是他','http://www.hao123.com',getdate())
insert into userMusic values(2,'高手','http://www.hao123.com',getdate())

--注册时间超过一年就每月送积分
update users set upoint=case
   when datediff(yy,uregdate,getdate())=1 then upoint+10
   when datediff(yy,uregdate,getdate())=2 then upoint+20
   else
       upoint
   end

判断数据库是否存在

if db_id('hu') is not null
  drop database hu