SqlServer ,storedprocedure操作

USE [Role]
GO

/*Create a table*/
IF OBJECT_ID (dbo.Users, U) IS NOT NULL
    DROP TABLE Users
GO
CREATE TABLE [dbo].[Users](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [Password] [varchar](20) NOT NULL,
    [TrueName] [varchar](50) NULL,
    [sex] [varchar](2) NULL,
    [Birthday] [datetime] NULL,
    [Phone] [varchar](20) NULL,
    [Email] [varchar](50) NULL,
    [CreateTime] [datetime] NULL,
 CONSTRAINT [PK_Accounts_Users] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/*Create procedure about select*/
IF OBJECT_ID(GetUserBy,p) IS NOT NULL
    DROP PROCEDURE GetUserBy
GO
CREATE PROCEDURE GetUserBy
    @id int
    AS
    BEGIN
        SELECT ID,UserName,Password,TrueName,sex,Birthday,Phone,Email FROM [Users] WHERE [Users].[ID]=@id
    END
GO

EXECUTE GetUserBy @id=1
GO

/*Insert*/
CREATE PROCEDURE Insertuser
     @userName varchar(50)
    ,@password varchar(20)
    ,@trueName varchar(50)
    ,@sex varchar(2)
    ,@birthday datetime
    ,@phone varchar(20)
    ,@email varchar(20)
    AS
    BEGIN
        INSERT INTO [Users]([UserName],[Password],[TrueName],[sex],[Birthday],[Phone],[Email],[CreateTime])
         VALUES(@userName,@password,@trueName,@sex,@birthday,@phone,@email,GETDATE())
    END
GO

EXECUTE Insertuser @userName=Admin,@password=123456
,@truename=Adminstrator,@sex=,@birthday=2014-08-31
,@phone=12345677,@email=[email protected]
Go
/*Insert user return id*/
CREATE PROCEDURE InsertUserReturenId
     @userName varchar(50)
    ,@password varchar(20)
    ,@trueName varchar(50)
    ,@sex varchar(2)
    ,@birthday datetime
    ,@phone varchar(20)
    ,@email varchar(20)
    AS
    BEGIN
        INSERT INTO [Users]([UserName],[Password],[TrueName],[sex],[Birthday],[Phone],[Email],[CreateTime])
         VALUES(@userName,@password,@trueName,@sex,@birthday,@phone,@email,GETDATE());
         SELECT @@IDENTITY
    END
GO

EXECUTE InsertUserReturenId @userName=Admin,@password=123456
,@truename=Adminstrator,@sex=,@birthday=2014-08-31
,@phone=12345677,@email=[email protected]
Go

/*Update*/
CREATE PROCEDURE UpdateUser
     @id int
    ,@password varchar(20)
    AS
    BEGIN
        UPDATE [Users] SET [Password]=@password WHERE [ID]=@id
    END
GO

EXECUTE UpdateUser @id=15,@password=1234567
GO
/*Delete*/
CREATE PROCEDURE DeleteUserById
    @id int
    AS
    BEGIN
        DELETE FROM [Users] WHERE [ID]=@id
    END
GO

EXECUTE DeleteUserById @id=1
GO    

/**Clear table**/

Truncate table [Users]
GO

/*loop insert data*/
DECLARE
 @userName varchar(50)
,@password varchar(20)
,@trueName varchar(50)
,@sex varchar(2)
,@birthday datetime
,@phone varchar(20)
,@email varchar(20)
,@i int

SET @userName=Admin
SET @password=123456
SET @trueName=Adminstrator
SET @sex=
SET @birthday=2014-08-31
SET @phone=111111111
SET @email=[email protected]
SET @i=30

WHILE @i<31
BEGIN
    SET @userName=admin+CONVERT(varchar(2),@i)
    INSERT INTO [Users]([UserName],[Password],[TrueName],[sex],[Birthday],[Phone],[Email],[CreateTime])
     VALUES(@userName,@password,@trueName,@sex,@birthday,@phone,@email,GETDATE())
     SET @i=@i+1
END
GO

/*Page and sort*/
CREATE PROCEDURE QueryUserByPage1
    @pageSize int
    ,@currentPage int
    ,@searchName varchar(50)
    ,@flag int
    AS
    DECLARE @startPgae int,@endPage int,@sort varchar(50),@sql varchar(1000)
    SET @startPgae=@currentPage * @pageSize
    SET @endPage=@startPgae + @pageSize -1
    SET @sort=
            CASE WHEN @flag=1 THEN ID
                 WHEN @flag=2 THEN UserName
                 WHEN @flag=3 THEN CreateTime
    END
    BEGIN 
        SELECT ROW_NUMBER() OVER(ORDER BY @sort DESC) AS userId FROM [Users]
         WHERE useId BETWEEN @startPgae AND @endPage
    END
GO

/*Pgae*/
IF OBJECT_ID (QueryUserByPage, p) IS NOT NULL
    DROP PROCEDURE QueryUserByPage
GO
CREATE PROCEDURE QueryUserByPage
    @pageSize int
    ,@currentPage int
    ,@searchName varchar(50)
    AS
    DECLARE @startPgae int,@endPage int
    SET @startPgae=(@currentPage-1) * @pageSize + 1
    SET @endPage=@startPgae + @pageSize - 1
    SET @searchName=%+RTRIM(@searchName)+%
    BEGIN 
         SELECT * FROM
          (
          SELECT *, ROW_NUMBER() OVER(ORDER BY ID DESC) AS userId
           FROM [Users]
            WHERE 
            [UserName] LIKE @searchName
          )
          tuser
         WHERE tuser.userId BETWEEN @startPgae AND @endPage
    END
GO


/*拼接字符串分页*/
CREATE PROCEDURE QueryUserByPage2
        @start int
        ,@end int
    AS
    DECLARE  @sql  nvarchar(600)
    set @sql=select top +str(@end-@start+1)+ * from Users where ID not in(select top+str(@start-1)+ ID from Users)
    BEGIN
        execute(@sql)
    END
GO

QueryUserByPage2 @start=2,@end=10
GO

 


EXECUTE QueryUserByPage @pageSize=10,@currentPage=1,@searchName=1 GO DECLARE @name varchar(50) ,@description varchar(50) ,@i int SET @description=teacher SET @i=1 WHILE @i<=10 BEGIN SET @name = teacher + CONVERT(varchar(2),@i) INSERT INTO [Roles] VALUES(@name,@description) SET @i=@i+1 END GO SELECT * from Roles TRUNCATE table Roles GO SELECT * FROM [UserRoles] LEFT JOIN [Roles] ON [UserRoles].[RoleID]=[Roles].[ID] WHERE [UserRoles].[UserID]=1
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like%value1%---like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
随机产生
select top 10 * from Users order by newid()
左外连接 (Left  Jion):包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行
select * from test1 left join test2 on test1.id = test2.id
右外连接 (Rigt Jion):包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行

select * from test1 right join test2 on test1.id = test2.id


 
declare @name varchar(50),@flag int
SET @name = 
  CASE 
     WHEN @flag =  0 THEN Mfg item - not for resale
     WHEN @flag < 50 THEN Under $50
     WHEN @flag >= 50 and @flag < 250 THEN Under $250
     WHEN @flag >= 250 and @flag < 1000 THEN Under $1000
     ELSE Over $1000
  END
IF @cost <= @compareprice 
BEGIN
    PRINT These products can be purchased for less than 
    $+RTRIM(CAST(@compareprice AS varchar(20)))+.
END

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。