SQL Server常用语句

1. 查询单张表所有数据

SELECT * FROM USER

2. 排序

SELECT * FROM USER ORDER BY CREATE_TIME        -- 默认正序排列
SELECT * FROM USER ORDER BY CREATE_TIME ASC    -- 依照时间正序排列
SELECT * FROM USER ORDER BY CREATE_TIME DESC   -- 按照时间倒序排列

SELECT * FROM USER ORDER BY CREATE_TIME DESC,UPDATE_TIME DESC  -- 进行多列排序时，先按照创建时间排序，再按照修改时间按排序

3. 查询指定条数数据

SELECT TOP 2 * FROM USER ORDER BY CREATE_TIME DESC   -- 查询倒数两条数据
SELECT * FROM T_03ProdInstr WHERE ProdInstrNo NOT IN (SELECT TOP 3 ProdInstrNo FROM T_03ProdInstr ORDER BY ProdInstrNo)  -- 从第四行开始查询所有数据

4. 查询字段&别名的命名方式

SELECT NICK_NAME '昵称',REAL_NAME '真实姓名' FROM USER
SELECT NICK_NAME AS '昵称',REAL_NAME AS '真实姓名' FROM USER

5. 日期区间查询

SELECT * FROM USER WHERE CREATE_TIME = '2019-09-23 11:35:34.267'
SELECT * FROM USER WHERE CREATE_TIME >= '2018-09-09' AND CREATE_TIME <= '2019-09-25'
SELECT * FROM USER WHERE CREATE_TIME BETWEEN '2018-09-09' AND '2019-09-25'

6. ISNULL

SELECT NAME,PHONE,EMAIL,ISNULL(UPDATE_TIME,'未进行修改') FROM USER -- 当修改时间为空时显示未进行修改

7. CASE

SELECT NAME,CASE Gender WHEN '0' THEN '女' WHEN '1' THEN '男' END AS '性别' FROM USER