SQL常见写法
查询
简单查询
//field 字段
//Table 表
SELECT field FROM Table
简单过滤重复(DISTINCT )
关键词 DISTINCT 用于返回唯一不同的值。
SELECT DISTINCT 列名称 FROM 表名称
简单联表查询(Join)
不使用 Join 关键字
SELECT Table1.field,Table1.field
FROM Table1, Table2
WHERE Table1.Id=Table2.Id
Join关键字
内连接(INNER JOIN)
左连接(LEFT JOIN)
右连接(RIGHT JOIN)
全连接(FULL JOIN)
合并结果集
UNION
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
UNION ALL
列名统一
//INNER JOIN
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
//LEFT JOIN
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
//RIGHT JOIN
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
//FULL JOIN
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
//UNION
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
//UNION ALL
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
分页查询
BETWEEN AND
操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS 'RowNumberForSplit'
FROM table
WHERE 条件 ) temp
WHERE RowNumberForSplit BETWEEN (@pageSize * (@pageIndex - 1) + 1) AND (@pageSize * @pageIndex);
Top用法
//通过使用Top关键字达到分页效果
SELECT TOP(PageSize*PageIndex) * FROM table WHERE table.id NOT IN
(SELECT TOP(PageSize*(PageIndex-1)) idFROM table)
SQL Server 2012的OFFSET-FETCH筛选
SELECT * FROM table ORDER BY AddTime
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
类型转换
时间戳转换(MSSQL)
--普通时间 转 13 位时间戳
SELECT CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 08:00:00.000', GETDATE())) * 60000 + DATEPART(S,GETDATE()) * 1000 + DATEPART(MS, GETDATE())
--13位时间戳 转 普通时间
SELECT DATEADD (MS ,CONVERT(BIGINT,1636687923000) % 60000 ,DATEADD(MI,CONVERT(BIGINT,1636687923000) / 60000,'1970-01-01 08:00:00.000'))
--普通时间转换成10位时间戳
SELECT DATEDIFF(S,'1970-01-01 08:00:00', GETDATE())
--10位时间戳转换成普通时间
SELECT DATEADD(S,1636687923,'1970-01-01 08:00:00')