USE [Role]GO/*Create a table*/IF OBJECT_ID (‘dbo.Users‘, ‘U‘) IS NOT NULL    DROP TABLE UsersGOCREATE 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 GetUserByGOCREATE PROCEDURE GetUserBy    @id int    AS    BEGIN        SELECT ID,UserName,Password,TrueName,sex,Birthday,Phone,Email FROM [Users] WHERE [Users].[ID]=@id    ENDGOEXECUTE GetUserBy @id=1GO/*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())    ENDGOEXECUTE 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    ENDGOEXECUTE 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    ENDGOEXECUTE UpdateUser @id=15,@password=‘1234567‘GO/*Delete*/CREATE PROCEDURE DeleteUserById    @id int    AS    BEGIN        DELETE FROM [Users] WHERE [ID]=@id    ENDGOEXECUTE DeleteUserById @id=1GO    /**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 intSET @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=30WHILE @i<31BEGIN    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+1ENDGO/*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    ENDGO/*Pgae*/IF OBJECT_ID (‘QueryUserByPage‘, ‘p‘) IS NOT NULL    DROP PROCEDURE QueryUserByPageGOCREATE 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    ENDGO

/*拼接字符串分页*/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)    ENDGOQueryUserByPage2 @start=2,@end=10GO

EXECUTE QueryUserByPage @pageSize=10,@currentPage=1,@searchName=‘1‘GODECLARE     @name varchar(50)    ,@description varchar(50)    ,@i intSET    @description=‘teacher‘SET @i=1WHILE @i<=10BEGIN    SET @name = ‘teacher‘ + CONVERT(varchar(2),@i)    INSERT INTO [Roles] VALUES(@name,@description)    SET @i=@i+1ENDGOSELECT * from RolesTRUNCATE table RolesGOSELECT * 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 intSET @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