use master
go

if exists(select * from sysdatabases where [name] = 'tmpDB')
begin
	drop database tmpDB
end
go

create database tmpDB
go

use tmpDB
go

create table UserInfo
(
	usID int primary key identity(1,1) not null,  -- 用戶編號(hào)
	usName varchar(36) not null,-- unique(usName),check(len(usName) > 4),-- 用戶賬號(hào)
	usPwd varchar(36) not null,-- check(len(usPwd) >4), -- 用戶密碼
	usNickName varchar(36) not null, -- 用戶呢稱
    usQus varchar(100) not null, -- 密碼提示問題
    usAns varchar(100) not null, -- 密碼提示問題答案
	usQQ varchar(10) null, -- QQ號(hào)碼
	usEmail varchar(60) null, --check (usEmail like'%@%.%'), -- 用戶郵箱
    usScore int not null, -- 用戶積分
	usOnLine int null, -- 在線狀態(tài)
	usOther1 varchar(300) null, -- 預(yù)留字段1
	usOther2 varchar(300) null, -- 預(yù)留字段2
	usOther3 varchar(300) null, -- 預(yù)留字段3		
)
go

select usID,usNickName,usName,usPwd,usQus ,usAns,usQQ,usEmail,usScore,usOnLine from UserInfo

-- 添加用戶信息
create procedure pro_insertUserInfo
	@usName varchar(36),
	@usPwd varchar(36),
	@usNickName varchar(36),
    @usQus varchar(100),
    @usAns varchar(100),
	@usQQ varchar(10),
	@usEmail varchar(60),
	@usScore int,
	@usOther1 varchar(300) = '',
	@usOther2 varchar(300) = '',
	@usOther3 varchar(300) = ''
as
begin
	insert into UserInfo
		values(@usName,@usPwd,@usNickName,@usQus,@usAns,@usQQ,@usEmail,@usScore,1,@usOther1,@usOther2,@usOther3)
end
go
-- 
exec pro_insertUserInfo 'admin','admin','系統(tǒng)測(cè)試','123','123','3243243211','admin@yeah.net',12
go
exec pro_insertUserInfo 'ericc','ericc','瑞克','123','123','3243243210','eric@yeah.net',33
go
exec pro_insertUserInfo 'henry','henry','亨利','123','123','3243243221','henry@yeah.net',45
go
exec pro_insertUserInfo 'jieke','jieke','杰克','123','123','3243243223','jieke@yeah.net',124
go
exec pro_insertUserInfo 'aming','aming','阿明','123','123','3243243266','aming@yeah.net',24
go
exec pro_insertUserInfo 'wanglin','wanglin','王陵','123','123','3243243256','wanglin@yeah.net',24
go
exec pro_insertUserInfo 'hexiao','hexiao','何曉','123','123','3243243223','hexiao@yeah.net',76
go
exec pro_insertUserInfo 'binbin','binbin','彬彬','123','123','3243243252','binbin@yeah.net',344
go
exec pro_insertUserInfo 'liming','liming','李明','123','123','3243243254','liming@yeah.net',123
go
exec pro_insertUserInfo 'xiaowang','xiaowang','小王','xiaowang','xiaowang','3243243243','xiaowang@yeah.net',55
go
exec pro_insertUserInfo 'dingding','dingding','丁丁','123','123','3243243254','dingding@yeah.net',136
go
exec pro_insertUserInfo 'lizhe','lizhe','李哲','123','123','3243243254','lizhe@yeah.net',545
go


select * from UserInfo order by  usID  desc
select count(*) from UserInfo

select top 10 usID,usNickName,usName,usPwd,usQus ,usAns,usQQ,usEmail,usScore,usOnLine from UserInfo where usID not in (select top 20 usID from UserInfo order by usID desc) order by usID desc

select top 10 usID,usNickName,usName,usPwd,usQus ,usAns,usQQ,usEmail,usScore,usOnLine from UserInfo where usID not in (select top 0 usID from UserInfo order by usID desc) order by usID desc