Sql server基础使用（二）：查询语句进阶

对所学Sqlserver知识进行一下记录总结，某些用法之间还可以关联使用，欢迎补充和讨论。本人比较懒，结果的截图暂时就先不传了，什么时候想起来再说吧~~

对字段重命名

----重命名字段名， as 可有可无,看自己习惯,也可以按实际要求自定义一个字段
SELECT userName, userName AS 带as的姓名,userName 无as姓名,'abc' 自定义的字段 FROM dbo.basic_User

查询当前时间

----查询当前时间  时间为sql所在服务器的时间
SELECT GETDATE() 当前时间

查询时间的进阶

----查询时间的进阶，比如：只查询年-月-日，只查询年，或月，或日等等，至于CONVERT的用法，在后面说明
SELECT GETDATE() 当前时间,CONVERT(VARCHAR(30),GETDATE(),23) 只查询年月日,YEAR(GETDATE()) 年,MONTH(GETDATE()) 月,DAY(GETDATE()) 日

----查询时间的进阶，比如：查询两天后的时间/日期
SELECT GETDATE(),DATEADD(DAY,2,GETDATE())两天后的时间,DATEADD(YEAR,2,GETDATE())两年后的时间,DATEADD(MONTH,2,GETDATE())两月后的时间

----查询时间的进阶，比如：计算两个日期之间的差
SELECT '2023-01-01'日期1,'2023-01-05'日期2,DATEDIFF(DAY,'2023-01-01','2023-01-05')日期差

查询一个新的 GUID

----查询一个新的 GUID
SELECT NEWID() 新的GUID

CASE 用法

----CASE 用法 一定一定不要忘了最后的 END,有几中写法,这里就列举2种吧,注:THEN 后面的返回值 和 ELSE 后面的返回值 类型要相同
SELECT userName,age,(CASE WHEN age=21 THEN '年轻' WHEN age=20 THEN '更年轻' ELSE '非常年轻' END)处理后的字段 FROM dbo.basic_User
SELECT userName,age,(CASE age WHEN 21 THEN '年轻' WHEN 20 THEN '更年轻' ELSE '非常年轻' END)处理后的字段 FROM dbo.basic_User

字段中 NULL 的处理

----字段中 NULL 的处理 也是有多种方法,这里列举两种
SELECT Remark,ISNULL(Remark,'已处理')处理后的字段 FROM dbo.basic_User
SELECT Remark,(CASE WHEN Remark IS NULL THEN '已处理' ELSE Remark END)处理后的字段 FROM dbo.basic_User
----注:需要注意字段的类型和处理后字段的类型，比如Remark是varchar类型，isnull 的时候返回值也要是varchar类型，如果使用 CASE 处理的话，THEN 后面的返回值 和 ELSE 后面的返回值 类型要相同
SELECT Remark,
(CASE WHEN Remark IS NULL THEN '已处理' ELSE Remark END)处理后的字段,----THEN 和 ELSE 后面的返回值都是 varchar
(CASE WHEN Remark IS NULL THEN 0 ELSE 1 END)处理后的字段1----THEN 和 ELSE 后面的返回值都是 int
FROM dbo.basic_User

求和、个数统计、平均值、最大/最小值

----字段求和,比如求各个部门人员的年龄和 注意:除了sum字段/count字段,AVG字段,还有其他字段的话,一定要加 GROUP BY
SELECT DeptID,SUM(age)agesum FROM dbo.basic_User GROUP BY DeptID
----个数统计,比如统计各个部门人员的个数
SELECT DeptID,COUNT(*)counts FROM dbo.basic_User GROUP BY DeptID
----平均值,比如统计各个部门人员的年龄平均值
SELECT DeptID,AVG(age)平均值 FROM dbo.basic_User GROUP BY DeptID
----最大/最小值,比如统计各个部门人员的年龄最大最下值
SELECT DeptID,AVG(age)平均值,MAX(age)maxage,MIN(age)minage FROM dbo.basic_User GROUP BY DeptID
----求数据表总行数/总年龄和 等, 此时不用 GROUP BY
SELECT COUNT(*),SUM(age),AVG(age),MAX(age)maxage,MIN(age)minage FROM dbo.basic_User

替换字符串

----替换字符串 使用 REPLACE , 比如将字段或某字符串中的 '的'替换为'aaaa' ,3个参数: 1.源字符 2.要被替换的字符(可以是多个连在一起的字符'的家门') 3.要替换成的字符
SELECT Remark,REPLACE(Remark,'的','aaaa')字段替换,REPLACE('啊啊随时随地的家门口了就','的家门','aaaa')替换特定字符串 FROM dbo.basic_User

查询某个字符在字段中的位置

----查询某个字符在字段中的位置，注：返回的是第一次出现的位置,
----比如:字段值='按不出的风格和' CHARINDEX('的',字段值,0) 返回值为4 ,CHARINDEX('的风',字段值,0) 返回值也为4
----需要三个参数 1.要查找的字符串 2.源字段或某个值 3.从某一位开始查找
SELECT Remark,CHARINDEX('的',Remark,0)结果,CHARINDEX('的风',Remark,0)结果1 FROM dbo.basic_User

截取字符串

----从 左/右 截取3位
SELECT Remark,LEFT(Remark,3)左,RIGHT(Remark,3)右 FROM dbo.basic_User

----从指定位置截取 用 SUBSTRING函数,包含3个参数,1.源字段或某个值 2.开始位置 3.截取长度
----比如 从第二个字符截取3个字符
SELECT Remark,SUBSTRING(Remark,2,3)结果 FROM dbo.basic_User

----SUBSTRING 与 CHARINDEX 结合,下面这句话的意思就是 从字段中找到 '的'的位置,然后从此处往后查找3个字符
SELECT Remark,SUBSTRING(Remark,CHARINDEX('的',Remark,0),3)结果 FROM dbo.basic_User

拼接字符串

----拼接字符串 直接用 + 拼接即可 但需要注意的是,拼接的字段或自定义的字符串要转换为varchar/nvarchar类型
SELECT userName+CAST(age AS VARCHAR(30))姓名加年龄,userName+'|'+CAST(age AS VARCHAR(30))姓名加年龄1,'aaaa'+userName+'ccccc' 自定义拼接 FROM dbo.basic_User

类型转换

----字段/数据 类型转换 有两种(我所知,待补充),CAST 和 CONVERT, 转换时一定要注意,任何类型都可以转换为 varchar/nvarchar , 
----但是 varchar/nvarchar 转换为其他类型时容易报错,只有特殊情况下可以转换,比如字符串 '15'可转换为 int/float/decimal ,'2020-01-01'可以转换为日期
SELECT CAST(age AS VARCHAR(30))cast转换,CONVERT(VARCHAR(30),age)convert转化 FROM dbo.basic_User
----这里还需要补充一下 CONVERT 还有一些其他的用法,比如上面提到过的 转换 日期，后面有补充

ROW_NUMBER()OVER(ORDER BY )用法

----ROW_NUMBER()OVER(ORDER BY )用法 主要功能就是给数据按照自定义的顺序添加序号 ORDER BY 后面的可以是某个字段,某几个字段,或几个字段的拼接,或随机数
SELECT ROW_NUMBER()OVER(ORDER BY userName)序号,userName,age FROM dbo.basic_User

普通排序和按随机数排序  / 倒序

----普通排序和按随机数排序  后面加 DESC为倒序 ：ORDER BY userName DESC
SELECT * FROM dbo.basic_User ORDER BY userName----按userName排序
SELECT * FROM dbo.basic_User ORDER BY userName,age----按userName排序 然后 在此基础上按 age排序
SELECT * FROM dbo.basic_User ORDER BY userName+Remark----按userName+Remark 拼接成的字符串排序
SELECT * FROM dbo.basic_User ORDER BY NEWID()----按随机数排序,每次所得结果不同

CONVERT 用法（补充）

----CONVERT 用法, 语句及查询结果：
SELECT CONVERT(varchar(100), GETDATE(), 0)---- 05 16 2006 10----57AM
SELECT CONVERT(varchar(100), GETDATE(), 1)---- 05/16/06
SELECT CONVERT(varchar(100), GETDATE(), 2)---- 06.05.16
SELECT CONVERT(varchar(100), GETDATE(), 3)---- 16/05/06
SELECT CONVERT(varchar(100), GETDATE(), 4)---- 16.05.06
SELECT CONVERT(varchar(100), GETDATE(), 5)---- 16-05-06
SELECT CONVERT(varchar(100), GETDATE(), 6)---- 16 05 06
SELECT CONVERT(varchar(100), GETDATE(), 7)---- 05 16, 06
SELECT CONVERT(varchar(100), GETDATE(), 8)---- 10----57----46
SELECT CONVERT(varchar(100), GETDATE(), 9)---- 05 16 2006 10----57----46----827AM
SELECT CONVERT(varchar(100), GETDATE(), 10)---- 05-16-06
SELECT CONVERT(varchar(100), GETDATE(), 11)---- 06/05/16
SELECT CONVERT(varchar(100), GETDATE(), 12)---- 060516
SELECT CONVERT(varchar(100), GETDATE(), 13)---- 16 05 2006 10----57----46----937
SELECT CONVERT(varchar(100), GETDATE(), 14)---- 10----57----46----967
SELECT CONVERT(varchar(100), GETDATE(), 20)---- 2006-05-16 10----57----47
SELECT CONVERT(varchar(100), GETDATE(), 21)---- 2006-05-16 10----57----47.157
SELECT CONVERT(varchar(100), GETDATE(), 22)---- 05/16/06 10----57----47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23)---- 2006-05-16
SELECT CONVERT(varchar(100), GETDATE(), 24)---- 10----57----47
SELECT CONVERT(varchar(100), GETDATE(), 25)---- 2006-05-16 10----57----47.250
SELECT CONVERT(varchar(100), GETDATE(), 100)---- 05 16 2006 10----57AM
SELECT CONVERT(varchar(100), GETDATE(), 101)---- 05/16/2006
SELECT CONVERT(varchar(100), GETDATE(), 102)---- 2006.05.16
SELECT CONVERT(varchar(100), GETDATE(), 103)---- 16/05/2006
SELECT CONVERT(varchar(100), GETDATE(), 104)---- 16.05.2006
SELECT CONVERT(varchar(100), GETDATE(), 105)---- 16-05-2006
SELECT CONVERT(varchar(100), GETDATE(), 106)---- 16 05 2006
SELECT CONVERT(varchar(100), GETDATE(), 107)---- 05 16, 2006
SELECT CONVERT(varchar(100), GETDATE(), 108)---- 10----57----49
SELECT CONVERT(varchar(100), GETDATE(), 109)---- 05 16 2006 10----57----49----437AM
SELECT CONVERT(varchar(100), GETDATE(), 110)---- 05-16-2006
SELECT CONVERT(varchar(100), GETDATE(), 111)---- 2006/05/16
SELECT CONVERT(varchar(100), GETDATE(), 112)---- 20060516
SELECT CONVERT(varchar(100), GETDATE(), 113)---- 16 05 2006 10----57----49----513
SELECT CONVERT(varchar(100), GETDATE(), 114)---- 10----57----49----547
SELECT CONVERT(varchar(100), GETDATE(), 120)---- 2006-05-16 10----57----49
SELECT CONVERT(varchar(100), GETDATE(), 121)---- 2006-05-16 10----57----49.700
SELECT CONVERT(varchar(100), GETDATE(), 126)---- 2006-05-16T10----57----49.827
SELECT CONVERT(varchar(100), GETDATE(), 130)---- 18 ???? ?????? 1427 10----57----49----907AM
SELECT CONVERT(varchar(100), GETDATE(), 131)---- 18/04/1427 10----57----49----920AM