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