tmpdb.sql

来自「分页其他人不需帐号就可自由下载此源码」· SQL 代码 · 共 87 行

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,  -- 用户编号
	usName varchar(36) not null,-- unique(usName),check(len(usName) > 4),-- 用户账号
	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号码
	usEmail varchar(60) null, --check (usEmail like'%@%.%'), -- 用户邮箱
    usScore int not null, -- 用户积分
	usOnLine int null, -- 在线状态
	usOther1 varchar(300) null, -- 预留字段1
	usOther2 varchar(300) null, -- 预留字段2
	usOther3 varchar(300) null, -- 预留字段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','系统测试','123','123','3243243211','[e-mail]',12
go
exec pro_insertUserInfo 'ericc','ericc','瑞克','123','123','3243243210','[e-mail]',33
go
exec pro_insertUserInfo 'henry','henry','亨利','123','123','3243243221','[e-mail]',45
go
exec pro_insertUserInfo 'jieke','jieke','杰克','123','123','3243243223','[e-mail]',124
go
exec pro_insertUserInfo 'aming','aming','阿明','123','123','3243243266','[e-mail]',24
go
exec pro_insertUserInfo 'wanglin','wanglin','王陵','123','123','3243243256','[e-mail]',24
go
exec pro_insertUserInfo 'hexiao','hexiao','何晓','123','123','3243243223','[e-mail]',76
go
exec pro_insertUserInfo 'binbin','binbin','彬彬','123','123','3243243252','[e-mail]',344
go
exec pro_insertUserInfo 'liming','liming','李明','123','123','3243243254','[e-mail]',123
go
exec pro_insertUserInfo 'xiaowang','xiaowang','小王','xiaowang','xiaowang','3243243243','[e-mail]',55
go
exec pro_insertUserInfo 'dingding','dingding','丁丁','123','123','3243243254','[e-mail]',136
go
exec pro_insertUserInfo 'lizhe','lizhe','李哲','123','123','3243243254','[e-mail]',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