use master
go
if exists(select name from sys.databases where name='tt_go')
drop database tt_go
go
create database tt_go
on primary --表示主文件
(
name='my_db',
filename='F:\my_db.mdf',
size=3mb,
filegrowth=10%,
maxsize=100mb
)
go
use tt_go
go
------------------------------------------------------------------------------创建用户表
if object_id('userinfo','u') is not null
drop table userinfo
go
create table userinfo
(
userid int identity(1,1),--用户ID
username varchar(20)not null,--用户名
quanxian int default 1,--用户权限
userpwd varchar(80)not null,--用户密吗
usersex char(2)check(usersex in('女','男'))default '男',--用户性别
userloginIP varchar(50),--用户上次登录的IP地址
userloginzctime datetime default getdate(),--用户注册时间
userlogintime datetime default getdate(),--用户上次登陆的时间
constraint userinfo_pk primary key(userid)
)
go
insert into userinfo(username,quanxian,userpwd)values('admin',0,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('c',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('d',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('e',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('f',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('g',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('h',1,'21232F297A57A5A743894A0E4A801FC3')
SELECT * FROM userinfo
--------------------------------------------------------------------添加用户
alter proc adduserinfo
(
@username varchar(50),--用户名
@userquanxian int,--用户权限
@userpwd varchar(80),--用户密吗
@usersex char(2),--用户性别
@zcztai varchar(20) output
)
as
declare @yhm int
set @yhm=(select count(0) from userinfo where username=@username)
if(@yhm>0)
begin
set @zcztai='用户名已存在！'
end
else
begin
insert into userinfo(username,quanxian,userpwd,usersex)values(@username,@userquanxian,@userpwd,@usersex)
set @zcztai='成功添加!'
end
go
declare @ee varchar(20)
exec adduserinfo '19',1,'admi/;嗄".;>A;.n','男',@ee output
print @ee
go
update userinfo set
------------------------------------------------------------------------修改密码
alter proc upuser
(
@mima varchar(80)--要修改的密码
)
as
update userinfo set userpwd=@mima
go
----------------------------------------------------------返回用户表数据的的总条数
alter proc ztiaoshu
@int int output
as
set @int=(select count(0)from userinfo)
go
declare @int int
exec ztiaoshu @int output
print @int
go
create proc gztiaoshu--返回用户表管理员数据的的总条数
@int int output
as
set @int=(select count(0)from userinfo where quanxian=0)
go
drop proc gztiaoshu
select userid 用户ID, username 用户名,quanxian 权限,userloginIP 上次登陆IP, usersex 性别,userloginzctime 用户注册的时间,userlogintime 上次登陆时间 from userinfo
go
-------------------------------------------------------------创建登陆的存储过程
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------请个位大大- - 帮忙注释下 谢谢了
create proc my_login
(
@username varchar(50),--用户名
@userpwd varchar(50),--密码
@thisloginIP varchar(50),--登陆的IP
@islogin int output,
@scIP varchar(50) output,--上次登录的ip地址
@sctime datetime output,--上次登陆的时间
@scquanxian int output--用户权限
)
as
declare @key int
set @key=(select count(0)from userinfo where username=@username and userpwd=@userpwd)
if(@key=1)
begin
set @scIP=(select userloginIP from userinfo where username=@username )
set @sctime=(select userlogintime from userinfo where username=@username)
set @scquanxian=(select quanxian from userinfo where username=@username)
update userinfo set userlogintime=getdate(),userloginIP=@thisloginIP where username=@username
set @islogin=0--代表登陆成功
end
else
begin
declare @hasname int
set @hasname=(select count(0)from userinfo where username=@username)
if(@hasname=1)
set @islogin=1--代表密码错误
else
set @islogin=2--代表用户名错误
end
go
--------------------------------------------------------------------------创建企业简介表
create table qybiao
(
gsjianjie varchar(5000)--公司简介
)
go
select * from qybiao
go
create proc bigs
@goshijianjie varchar(6000) output
as
set @goshijianjie=(select gsjianjie from qybiao)
insert into qybiao values('sfjdjsfoajojfogdjoafgasdjfgodjaofgdsjfodjasofjdsjifvojdoifijaoidsf')
--------------------------------------------------------------------------创建用户留言表
create table yhliuyan
(
yhname varchar(50),--用户名
yhly varchar(2000),--用户留言内容
yhlytime datetime default getdate()
)
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------创建新闻类型表
if object_id('titletype','u')is not null
drop table xinwen
go
create table titletype
(
f_id int identity(1,1),--新闻类型ID
f_name varchar(100)--新闻类的名称
constraint title_pk primary key(f_id)
)
go
insert into titletype('今日热点')
insert into titletype('娱乐新闻')
insert into titletype('体育新闻')
insert into titletype('政治新闻')
insert into titletype('')
-------------------------------------------------------创建分页的存储过程
create proc fenye
@primarykeyName varchar(20),--主键名字
@pageIndex int,--当前页的是第几页
@pageSize int,--每页有多少条记录
@sql varchar(2000)--条件语句
as
begin
declare @sqlstr varchar(2000)
if(@sql='')
begin
set @sqlstr='select top '+cast(@pageSize as varchar)+'* from '+@tableName+' where '+@primarykeyName+' not in(select top(('+cast(@pageIndex as varchar)+'-1)*'+cast(@pageSize as varchar)+')'+@primarykeyName+' from '+@tableName+' order by '+@primarykeyName+')order by '+@primarykeyName
end
else
begin
set @sqlstr='select top '+cast(@pageSize as varchar)+'* from '+@tableName+' where '+@primarykeyName+' not in(select top(('+cast(@pageIndex as varchar)+'-1)*'+cast(@pageSize as varchar)+')'+@primarykeyName+' from '+@tableName+' order by '+@primarykeyName+')and '+@sql+' order by '+@primarykeyName
end
print @sqlstr
exec(@sqlstr)
end
go
exec fenye 'userinfo','userid',1,2,''
------------------------------------------------两个参数的分页存储过程（区间分页）
alter proc qjfenye
@qishi int,
@jiewei int,
@table varchar(20),
@ID varchar(20),
@where varchar(200)
as
SET NOCOUNT ON;
begin
declare @sqlstr varchar(2000)
set @sqlstr='select top ('+cast(@jiewei as varchar)+'-'+'('+cast(@qishi as varchar)+'-1))*from '+@table+' where '+@ID+' not in(select top ('+cast(@qishi as varchar)+'-1) '+@ID+' from '+@table+' order by '+@ID+') and '+@where+' order by '+@ID
print @sqlstr
exec(@sqlstr)
end
go
exec qjfenye 1,20,'userinfo',userid,'quanxian=0'
go
select count(0)from userinfo
go
------------------------------------------------------------
select * from userinfo
create PROCEDURE [dbo].[AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@ParentId int=NULL,
@AreaLevel int=NULL,
@Children int=NULL,
@AreaName nvarchar(50)=NULL,
@Path nvarchar(MAX)=NULL,
@Status int=NULL,
@Alt int=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'
IF (NOT @Path IS NULL) SET @Path='%'+@Path+'%'
IF (@PageSize>0)
BEGIN
--创建中记录数
DECLARE @SumCount int
--创建临时表
Select
Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
INTO #temp_Area
FROM userinfo Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
--设置总记录数为刚操作的记录数
SET @SumCount=@@RowCount
DECLARE @TotalPage int
IF(@SumCount%@PageSize=0)
BEGIN
SET @TotalPage=@SumCount/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@SumCount/@PageSize,0)+1
END
Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
FROM #temp_Area
Where
Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
END
ELSE
BEGIN
Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
END
7 个解决方案
#1
好长~~~~·
#2
这么多?
#3
都是比较平常的语句,作了一些注释,但有些语句还有问题.
use master
go
--如果存在数据库则删除
if exists(select name from sys.databases where name='tt_go')
drop database tt_go
go
--创建数据库
create database tt_go
on primary --表示主文件
(
name='my_db',
filename='F:\my_db.mdf',
size=3mb,
filegrowth=10%,
maxsize=100mb
)
go
use tt_go
go
--创建用户表
if object_id('userinfo','u') is not null
drop table userinfo
go
create table userinfo
(
userid int identity(1,1),--用户ID
username varchar(20)not null,--用户名
quanxian int default 1,--用户权限
userpwd varchar(80)not null,--用户密吗
usersex char(2)check(usersex in('女','男'))default '男',--用户性别
userloginIP varchar(50),--用户上次登录的IP地址
userloginzctime datetime default getdate(),--用户注册时间
userlogintime datetime default getdate(),--用户上次登陆的时间
constraint userinfo_pk primary key(userid)
)
go
--插入数据
insert into userinfo(username,quanxian,userpwd)values('admin',0,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('c',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('d',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('e',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('f',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('g',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('h',1,'21232F297A57A5A743894A0E4A801FC3')
SELECT * FROM userinfo
---存储过程,用于添加用户
alter proc adduserinfo
(
@username varchar(50),--用户名
@userquanxian int,--用户权限
@userpwd varchar(80),--用户密吗
@usersex char(2),--用户性别
@zcztai varchar(20) output
)
as
declare @yhm int
--判断用户是否存在
set @yhm=(select count(0) from userinfo where username=@username)
if(@yhm>0)
begin
set @zcztai='用户名已存在！'
end
else
begin
--不存在则添加
insert into userinfo(username,quanxian,userpwd,usersex)values(@username,@userquanxian,@userpwd,@usersex)
set @zcztai='成功添加!'
end
go
--添加用户
declare @ee varchar(20)
exec adduserinfo '19',1,'admi/;嗄".;>A;.n','男',@ee output
print @ee
go
--这句update有问题,后面差东西呢!
update userinfo set
------------------------------------------------------------------------修改密码
alter proc upuser
(
@mima varchar(80)--要修改的密码
)
as
update userinfo set userpwd=@mima
go
---建存储过程,返回用户表数据的的总条数
alter proc ztiaoshu
@int int output
as
set @int=(select count(0)from userinfo)
go
--获取用户数
declare @int int
exec ztiaoshu @int output
print @int
go
create proc gztiaoshu--返回用户表管理员数据的的总条数
@int int output
as
set @int=(select count(0)from userinfo where quanxian=0)
go
drop proc gztiaoshu
select userid 用户ID, username 用户名,quanxian 权限,userloginIP 上次登陆IP, usersex 性别,userloginzctime 用户注册的时间,userlogintime 上次登陆时间 from userinfo
go
---创建登陆的存储过程
create proc my_login
(
@username varchar(50),--用户名
@userpwd varchar(50),--密码
@thisloginIP varchar(50),--登陆的IP
@islogin int output,
@scIP varchar(50) output,--上次登录的ip地址
@sctime datetime output,--上次登陆的时间
@scquanxian int output--用户权限
)
as
declare @key int
set @key=(select count(0)from userinfo where username=@username and userpwd=@userpwd)
if(@key=1)
begin
set @scIP=(select userloginIP from userinfo where username=@username )
set @sctime=(select userlogintime from userinfo where username=@username)
set @scquanxian=(select quanxian from userinfo where username=@username)
update userinfo set userlogintime=getdate(),userloginIP=@thisloginIP where username=@username
set @islogin=0--代表登陆成功
end
else
begin
declare @hasname int
set @hasname=(select count(0)from userinfo where username=@username)
if(@hasname=1)
set @islogin=1--代表密码错误
else
set @islogin=2--代表用户名错误
end
go
--------------------------------------------------------------------------创建企业简介表
create table qybiao
(
gsjianjie varchar(5000)--公司简介
)
go
select * from qybiao
go
create proc bigs
@goshijianjie varchar(6000) output
as
set @goshijianjie=(select gsjianjie from qybiao)
insert into qybiao values('sfjdjsfoajojfogdjoafgasdjfgodjaofgdsjfodjasofjdsjifvojdoifijaoidsf')
--------------------------------------------------------------------------创建用户留言表
create table yhliuyan
(
yhname varchar(50),--用户名
yhly varchar(2000),--用户留言内容
yhlytime datetime default getdate()
)
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------创建新闻类型表
if object_id('titletype','u')is not null
drop table xinwen
go
create table titletype
(
f_id int identity(1,1),--新闻类型ID
f_name varchar(100)--新闻类的名称
constraint title_pk primary key(f_id)
)
go
insert into titletype('今日热点')
insert into titletype('娱乐新闻')
insert into titletype('体育新闻')
insert into titletype('政治新闻')
insert into titletype('')
-------------------------------------------------------创建分页的存储过程
create proc fenye
@primarykeyName varchar(20),--主键名字
@pageIndex int,--当前页的是第几页
@pageSize int,--每页有多少条记录
@sql varchar(2000)--条件语句
as
begin
declare @sqlstr varchar(2000)
if(@sql='')
begin
--用动态存储过程获取指定页上的记录
set @sqlstr='select top '+cast(@pageSize as varchar)+'* from '+@tableName+' where '+@primarykeyName+' not in(select top(('+cast(@pageIndex as varchar)+'-1)*'+cast(@pageSize as varchar)+')'+@primarykeyName+' from '+@tableName+' order by '+@primarykeyName+')order by '+@primarykeyName
end
else
begin
set @sqlstr='select top '+cast(@pageSize as varchar)+'* from '+@tableName+' where '+@primarykeyName+' not in(select top(('+cast(@pageIndex as varchar)+'-1)*'+cast(@pageSize as varchar)+')'+@primarykeyName+' from '+@tableName+' order by '+@primarykeyName+')and '+@sql+' order by '+@primarykeyName
end
print @sqlstr
exec(@sqlstr)
end
go
exec fenye 'userinfo','userid',1,2,''
------------------------------------------------两个参数的分页存储过程（区间分页）
alter proc qjfenye
@qishi int,
@jiewei int,
@table varchar(20),
@ID varchar(20),
@where varchar(200)
as
SET NOCOUNT ON;
begin
declare @sqlstr varchar(2000)
--动态语句分页
set @sqlstr='select top ('+cast(@jiewei as varchar)+'-'+'('+cast(@qishi as varchar)+'-1))*from '+@table+' where '+@ID+' not in(select top ('+cast(@qishi as varchar)+'-1) '+@ID+' from '+@table+' order by '+@ID+') and '+@where+' order by '+@ID
print @sqlstr
exec(@sqlstr)
end
go
exec qjfenye 1,20,'userinfo',userid,'quanxian=0'
go
select count(0)from userinfo
go
------------------------------------------------------------区域查找
select * from userinfo
create PROCEDURE [dbo].[AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@ParentId int=NULL,
@AreaLevel int=NULL,
@Children int=NULL,
@AreaName nvarchar(50)=NULL,
@Path nvarchar(MAX)=NULL,
@Status int=NULL,
@Alt int=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'
IF (NOT @Path IS NULL) SET @Path='%'+@Path+'%'
--根据页记录数查找指定页的记录
IF (@PageSize>0)
BEGIN
--创建中记录数
DECLARE @SumCount int
--创建临时表
Select
Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
INTO #temp_Area
FROM userinfo Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
--设置总记录数为刚操作的记录数
SET @SumCount=@@RowCount
DECLARE @TotalPage int
--计算页记录位置
IF(@SumCount%@PageSize=0)
BEGIN
SET @TotalPage=@SumCount/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@SumCount/@PageSize,0)+1
END
Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
FROM #temp_Area
Where
Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
END
ELSE
BEGIN
--如果页容量设置为0,则将满足条件的记录全部输出
Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
END
#4
另外,这里面没有触发器,主要是一些存储过程.
#5
hOCHANGA
#6
-,-不好意思
那个位大大能不能告诉我一本SQL最好有案例的详细的书谢谢了
#7
晴天好热心.感动...
#1
好长~~~~·
#2
这么多?
#3
都是比较平常的语句,作了一些注释,但有些语句还有问题.
use master
go
--如果存在数据库则删除
if exists(select name from sys.databases where name='tt_go')
drop database tt_go
go
--创建数据库
create database tt_go
on primary --表示主文件
(
name='my_db',
filename='F:\my_db.mdf',
size=3mb,
filegrowth=10%,
maxsize=100mb
)
go
use tt_go
go
--创建用户表
if object_id('userinfo','u') is not null
drop table userinfo
go
create table userinfo
(
userid int identity(1,1),--用户ID
username varchar(20)not null,--用户名
quanxian int default 1,--用户权限
userpwd varchar(80)not null,--用户密吗
usersex char(2)check(usersex in('女','男'))default '男',--用户性别
userloginIP varchar(50),--用户上次登录的IP地址
userloginzctime datetime default getdate(),--用户注册时间
userlogintime datetime default getdate(),--用户上次登陆的时间
constraint userinfo_pk primary key(userid)
)
go
--插入数据
insert into userinfo(username,quanxian,userpwd)values('admin',0,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('c',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('d',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('e',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('f',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('g',1,'21232F297A57A5A743894A0E4A801FC3')
insert into userinfo(username,quanxian,userpwd)values('h',1,'21232F297A57A5A743894A0E4A801FC3')
SELECT * FROM userinfo
---存储过程,用于添加用户
alter proc adduserinfo
(
@username varchar(50),--用户名
@userquanxian int,--用户权限
@userpwd varchar(80),--用户密吗
@usersex char(2),--用户性别
@zcztai varchar(20) output
)
as
declare @yhm int
--判断用户是否存在
set @yhm=(select count(0) from userinfo where username=@username)
if(@yhm>0)
begin
set @zcztai='用户名已存在！'
end
else
begin
--不存在则添加
insert into userinfo(username,quanxian,userpwd,usersex)values(@username,@userquanxian,@userpwd,@usersex)
set @zcztai='成功添加!'
end
go
--添加用户
declare @ee varchar(20)
exec adduserinfo '19',1,'admi/;嗄".;>A;.n','男',@ee output
print @ee
go
--这句update有问题,后面差东西呢!
update userinfo set
------------------------------------------------------------------------修改密码
alter proc upuser
(
@mima varchar(80)--要修改的密码
)
as
update userinfo set userpwd=@mima
go
---建存储过程,返回用户表数据的的总条数
alter proc ztiaoshu
@int int output
as
set @int=(select count(0)from userinfo)
go
--获取用户数
declare @int int
exec ztiaoshu @int output
print @int
go
create proc gztiaoshu--返回用户表管理员数据的的总条数
@int int output
as
set @int=(select count(0)from userinfo where quanxian=0)
go
drop proc gztiaoshu
select userid 用户ID, username 用户名,quanxian 权限,userloginIP 上次登陆IP, usersex 性别,userloginzctime 用户注册的时间,userlogintime 上次登陆时间 from userinfo
go
---创建登陆的存储过程
create proc my_login
(
@username varchar(50),--用户名
@userpwd varchar(50),--密码
@thisloginIP varchar(50),--登陆的IP
@islogin int output,
@scIP varchar(50) output,--上次登录的ip地址
@sctime datetime output,--上次登陆的时间
@scquanxian int output--用户权限
)
as
declare @key int
set @key=(select count(0)from userinfo where username=@username and userpwd=@userpwd)
if(@key=1)
begin
set @scIP=(select userloginIP from userinfo where username=@username )
set @sctime=(select userlogintime from userinfo where username=@username)
set @scquanxian=(select quanxian from userinfo where username=@username)
update userinfo set userlogintime=getdate(),userloginIP=@thisloginIP where username=@username
set @islogin=0--代表登陆成功
end
else
begin
declare @hasname int
set @hasname=(select count(0)from userinfo where username=@username)
if(@hasname=1)
set @islogin=1--代表密码错误
else
set @islogin=2--代表用户名错误
end
go
--------------------------------------------------------------------------创建企业简介表
create table qybiao
(
gsjianjie varchar(5000)--公司简介
)
go
select * from qybiao
go
create proc bigs
@goshijianjie varchar(6000) output
as
set @goshijianjie=(select gsjianjie from qybiao)
insert into qybiao values('sfjdjsfoajojfogdjoafgasdjfgodjaofgdsjfodjasofjdsjifvojdoifijaoidsf')
--------------------------------------------------------------------------创建用户留言表
create table yhliuyan
(
yhname varchar(50),--用户名
yhly varchar(2000),--用户留言内容
yhlytime datetime default getdate()
)
go
--------------------------------------------------------------------------
--------------------------------------------------------------------------创建新闻类型表
if object_id('titletype','u')is not null
drop table xinwen
go
create table titletype
(
f_id int identity(1,1),--新闻类型ID
f_name varchar(100)--新闻类的名称
constraint title_pk primary key(f_id)
)
go
insert into titletype('今日热点')
insert into titletype('娱乐新闻')
insert into titletype('体育新闻')
insert into titletype('政治新闻')
insert into titletype('')
-------------------------------------------------------创建分页的存储过程
create proc fenye
@primarykeyName varchar(20),--主键名字
@pageIndex int,--当前页的是第几页
@pageSize int,--每页有多少条记录
@sql varchar(2000)--条件语句
as
begin
declare @sqlstr varchar(2000)
if(@sql='')
begin
--用动态存储过程获取指定页上的记录
set @sqlstr='select top '+cast(@pageSize as varchar)+'* from '+@tableName+' where '+@primarykeyName+' not in(select top(('+cast(@pageIndex as varchar)+'-1)*'+cast(@pageSize as varchar)+')'+@primarykeyName+' from '+@tableName+' order by '+@primarykeyName+')order by '+@primarykeyName
end
else
begin
set @sqlstr='select top '+cast(@pageSize as varchar)+'* from '+@tableName+' where '+@primarykeyName+' not in(select top(('+cast(@pageIndex as varchar)+'-1)*'+cast(@pageSize as varchar)+')'+@primarykeyName+' from '+@tableName+' order by '+@primarykeyName+')and '+@sql+' order by '+@primarykeyName
end
print @sqlstr
exec(@sqlstr)
end
go
exec fenye 'userinfo','userid',1,2,''
------------------------------------------------两个参数的分页存储过程（区间分页）
alter proc qjfenye
@qishi int,
@jiewei int,
@table varchar(20),
@ID varchar(20),
@where varchar(200)
as
SET NOCOUNT ON;
begin
declare @sqlstr varchar(2000)
--动态语句分页
set @sqlstr='select top ('+cast(@jiewei as varchar)+'-'+'('+cast(@qishi as varchar)+'-1))*from '+@table+' where '+@ID+' not in(select top ('+cast(@qishi as varchar)+'-1) '+@ID+' from '+@table+' order by '+@ID+') and '+@where+' order by '+@ID
print @sqlstr
exec(@sqlstr)
end
go
exec qjfenye 1,20,'userinfo',userid,'quanxian=0'
go
select count(0)from userinfo
go
------------------------------------------------------------区域查找
select * from userinfo
create PROCEDURE [dbo].[AreaSelect]
@PageSize int=0,
@CurrentPage int=1,
@Identifier int=NULL,
@ParentId int=NULL,
@AreaLevel int=NULL,
@Children int=NULL,
@AreaName nvarchar(50)=NULL,
@Path nvarchar(MAX)=NULL,
@Status int=NULL,
@Alt int=NULL
AS
BEGIN
SET NOCOUNT ON;
IF (NOT @AreaName IS NULL) SET @AreaName='%'+@AreaName+'%'
IF (NOT @Path IS NULL) SET @Path='%'+@Path+'%'
--根据页记录数查找指定页的记录
IF (@PageSize>0)
BEGIN
--创建中记录数
DECLARE @SumCount int
--创建临时表
Select
Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt
INTO #temp_Area
FROM userinfo Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
--设置总记录数为刚操作的记录数
SET @SumCount=@@RowCount
DECLARE @TotalPage int
--计算页记录位置
IF(@SumCount%@PageSize=0)
BEGIN
SET @TotalPage=@SumCount/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@SumCount/@PageSize,0)+1
END
Select TOP (@PageSize) Identifier,ParentId,AreaLevel,Children,AreaName,
Path,Status,Alt,@TotalPage as totalPage,@SumCount as SumCount
FROM #temp_Area
Where
Identifier NOT IN (Select Top (@PageSize*(@CurrentPage-1))Identifier FROM #temp_Area))
END
ELSE
BEGIN
--如果页容量设置为0,则将满足条件的记录全部输出
Select Identifier,ParentId,AreaLevel,Children,AreaName,Path,Status,Alt FROM Area Where
(@Identifier IS NULL or Identifier=@Identifier)AND
(@ParentId IS NULL or ParentId=@ParentId)AND
(@AreaLevel IS NULL or AreaLevel=@AreaLevel)AND
(@Children IS NULL or Children=@Children)AND
(@AreaName IS NULL or AreaName Like @AreaName)AND
(@Path IS NULL or Path Like @Path)AND
(@Status IS NULL or Status=@Status)AND
(@Alt IS NULL or Alt=@Alt)
order by AreaName asc
END
END
#4
另外,这里面没有触发器,主要是一些存储过程.
#5
hOCHANGA
#6
-,-不好意思
那个位大大能不能告诉我一本SQL最好有案例的详细的书谢谢了
#7
晴天好热心.感动...