sqlserver

测试工具: smss2016 数据库: localdb2014

数据库~表查询

-- 查询所有数据库
SELECT * FROM master.sys.databases

-- 查询当前数据库所有表
SELECT * FROM sys.objects where type='U'

-- 查询表"user"的所有字段信息
SELECT * FROM sys.columns
WHERE object_id = (SELECT object_id FROM sys.tables WHERE type='U' AND name='user')

-- 查询表"user"的字段名和类型
SELECT c.name 列名,t.name 列类型
FROM sys.columns c
JOIN sys.systypes t ON t.xusertype=c.system_type_id
WHERE c.object_id = 
        (SELECT object_id FROM sys.tables WHERE type='U' AND name='user')

-- 查询表"user"的字段名~类型~长度~字段说明
-- 注意:列类型为char时长度有参考用.如果是nchar(前面有n,可以支持中文),查出的长度要除以2.中文字符长度为2个ascii字符长度
SELECT c.name 列名,t.name 列类型,c.max_length 列长度,i.value 列说明
FROM sys.columns c
JOIN sys.systypes t ON t.xusertype=c.system_type_id
LEFT JOIN sys.extended_properties i ON i.minor_id=c.column_id AND i.major_id=c.object_id
WHERE c.object_id = 
        (SELECT object_id FROM sys.tables WHERE type='U' AND name='user')

like

-- 参数化LIKE查询
SELECT * FROM 表名 WHERE 列名 like '%'+@ParaName+'%'

-- 在SMSS工具里执行模糊查寻时,如果查找的参数是中文,可尝试在前面加 N
SELECT * FROM 表名 WHERE 列名 like N'%汉%'

in

-- 参数化IN查询
SELECT * FROM 表名 WHERE 列名 IN (@para1,@para2,....多个参数占位符可以循环拼接)

while

-- while循环
DECLARE @i int   
SET @i=1   
          
WHILE @i<30 BEGIN
            insert into test (userid) values(@i)
            set @i=@i +1
            END

if else

-- 条件
IF(1=1)
    BEGIN
        SELECT '对'
    END
ELSE
    BEGIN
        SELECT '错'
    END

时间间隔

--查询当天:   
SELECT * FROM info where DateDiff(dd,datetime,getdate())=0   
  
--查询24小时内的:   
SELECT * FROM info where DateDiff(hh,datetime,getDate())<=24

--本月记录   
SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0   
  
--本周记录   
SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0

分页row_number

-- ROW_NUMBER分页1:分为两步.可以写在事务里.经测试,在总记录数少于三万条时,时间效率低
SELECT count(pk) from 表 WHERE 1=1
             
SELECT * FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY [CreateTime] DESC) RowNumber
          ,[col1],[col2]
     FROM 表
     WHERE 1=1
     ) A 
WHERE A.RowNumber BETWEEN @StartRowIndex AND @EndRowIndex
ORDER BY A.[CreateTime] DESC

-- ROW_NUMBER分页2:分页同时计算总条数.经测试,在总记录数在三万以内时,比分两步算法时间效率高
SELECT * FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY [CreateTime] DESC) RowNumber
          ,COUNT(pk) OVER() AS ListCount
          ,[col1],[col2]
     FROM 表
     WHERE 1=1
     ) A 
WHERE A.RowNumber BETWEEN @StartRowIndex AND @EndRowIndex
ORDER BY A.[CreateTime] DESC

分页OFFSET

-- OFFSET分页1:分为两步,总条数和分页 使用OFFSET,SQL2012开始可用.时间效率比rownumber高一点
SELECT count(pk) from 表 WHERE 1=1
              
SELECT ROW_NUMBER() OVER (ORDER BY [CreateTime] DESC) RowNumber
          ,[col1],[col2]
FROM 表
WHERE 1=1
ORDER BY [CreateTime] DESC
OFFSET @OffSetRows ROWS FETCH NEXT @PageSize ROWS ONLY

-- OFFSET分页2,同时计算总条数 
SELECT ROW_NUMBER() OVER (ORDER BY [CreateTime] DESC) RowNumber
          ,COUNT(pk) OVER() AS ListCount
          ,[col1],[col2]
FROM 表
WHERE 1=1
ORDER BY [CreateTime] DESC
OFFSET @OffSetRows ROWS FETCH NEXT @PageSize ROWS ONLY  

guid

--生成一个GUID
SELECT REPLACE(NEWID(), '-', '')

返回自增长id

-- 返回自增主键
INSERT INTO 表([col1]) VALUES(@col1);SELECT SCOPE_IDENTITY()