Fork me on GitHub
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='a@136.com'
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='a@136.com'
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='q@138.com'
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

 

posted on 2014-08-31 16:40  lingfeng95  阅读(884)  评论(0编辑  收藏  举报