常用SQL代码

获取刚insert的ID INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES (‘Accountant’,12,125) ; SELECT @@IDENTITY AS ‘Identity’ select嵌套 select * from table where tname in (select ttname from ttable) select not in (select) 对access 文本 varchar(50) 长度不固定 char(5) 长度固定 最大255 备注 text 最大65535 数字 int 长整型 smallint 整型 tinyint 字节 decimal(p,s) p 精度和s 数值范围的十进位整数，精度p是指全部有几个数(digits)大小值，s是指小数点後有几位数 p>s float 32位元的实数和double64位元的实数都是双精度 日期时间 smalldatetime datetime 自动编号 IDENTITY (1, 1) 开始值 增长值 货币 money +default 0 默认值 0 +not null 必填字段 null 不是必填字段 +primary key 定义主键 If IsSql = 1 Then SQL = “Create TABLE [Dv_MoneyLog] ([Log_ID] int IDENTITY (1, 1) NOT NULL CONSTRAINT PK_Dv_MoneyLog PRIMARY KEY,” Else SQL = “Create TABLE [Dv_MoneyLog] ([Log_ID] int IDENTITY (1, 1) NOT NULL CONSTRAINT PrimaryKey PRIMARY KEY,” End If 数据库分页代码 方案一 Select TOP 10 * FROM TestTable Where (ID NOT IN (Select TOP 50 id FROM TestTable orDER BY id)) orDER BY ID Select TOP 每页显示 * FROM TestTable Where (ID NOT IN (Select TOP 每页显示*页码 id FROM TestTable orDER BY id)) orDER BY ID 方案二 Select TOP 10 * FROM TestTable Where (ID > (Select MAX(id) FROM (Select TOP 20 id FROM TestTable orDER BY id) AS T)) orDER BY ID Select TOP 页大小 * FROM TestTable Where (ID > (Select MAX(id) FROM (Select TOP 页大小*页码 id FROM TestTable orDER BY id) AS T)) orDER BY ID 方案三 create procedure XiaoZhengGe @sqlstr nvarchar(4000), –查询字符串 @currentpage int, –第N页 @pagesize int –每页行数 as set nocount on declare @P1 int, –P1是游标的id @rowcount int exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output select ceiling(1.0*@rowcount/@pagesize) as 总页数–,@rowcount as 总行数,@currentpage as 当前页 set @currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch @P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off