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

case when

-- case 列 when 条件 then 值 ... ELSE 值 END
CASE col
    WHEN '1'
        THEN 1
    WHEN '2'
        THEN 2
    ELSE 0
END
//
CASE
    WHEN col IS NULL
        THEN 0
    ELSE 1
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()

每小组特定值

这是一个常用的需求,一张表中的数据,可以按一个或者多个字段分组,在每个分组数据中查出符合要求的那一条或者多条记录.

最简单例子:找出订单表中,每一天的金额最大的那一个订单,要列出所有字段.

使用开窗函数 OVER(PARTITION BY col ORDER BY col) 解决.使用group by不够方便.

所谓开窗函数,可以将数据分组,PARTITION BY 分组字段,ORDER BY 排序字段.

-- 第一步,按每天分组订单记录,并且每个分组按金额由大到小排列.
-- OVER函数前的,ROW_NUMBER()函数是关键,它为每组生成行号.执行结果会多出一个rowNumber字段(别名).
SELECT col1, col2, ROW_NUMBER() OVER(PARTITION BY 每天日期 ORDER BY 金额 DESC) rowNumber
FROM 订单表

-- 第二步就简单了,每组行号为1的就是每天的最大金额记录,在外套一层查询就得到结果了
SELECT col1, col2
FROM
(SELECT col1, col2, ROW_NUMBER() OVER(PARTITION BY 每天日期 ORDER BY 金额 DESC) rowNumber
FROM 订单表) 订单表开窗函数结果集别名
WHERE 订单表开窗函数结果集别名.rowNumber=1

由于要列出所有字段,所以使用group by不是特别方便.