了解sql server 数据库常用函数和高级函数

最新推荐文章于 2024-02-01 18:57:11 发布

Marzlam

最新推荐文章于 2024-02-01 18:57:11 发布

阅读量599

点赞数

分类专栏： Sql 文章标签：设计模式 U盘安装系统 kafka

本文链接：https://blog.csdn.net/Marzlam/article/details/103296675

版权

Sql 专栏收录该内容

26 篇文章 0 订阅

订阅专栏

数据库的工作核心就是四个字：增删改查对应英文也就是 CRUD

将项目业务逻辑所涉及的方方面面映射到数据中，也就是model 数据库表字段这些

查（Retrieve）：

关键字： select from 条件：where and or like %(not like) in (not in)

分组：group by 函数：COUNT()，还有 MAX(),MIN(),SUM() ， HAVING（分组之后查数量结合count 和group by） DISTINCT去重复 case when then end

增（Create）：

关键字： insert into 表名 values(列)

改（update）:

关键字： update 表名 set 列=新内容 where 条件

更新一个表用另一个表的内容

update table1

　　set field1=table2.field1,field2=table2.field2

　　from table2 where table1.id=table2.id

删（delete）:

关键字：delete 表名 where 条件 drop表名是删表

判断空的条件 where a is null(is not null)

二元三元运算 isnull(a,b) a为空则b iif(a,b,c) a满足则b 不然c

转换类型 cast(a as b) 将a字段转换成b类型 convert（b,a）将 a字段转换b类型

Try_Convert 转换成功/转换不成功为NULL 比如根据身份证拿出生日期 CSRQ=TRY_CONVERT(datetime,substring (ZJHM,7,8)) 比如20210150 截取的这种不合理的日期那就是null 合理的就正常

decimal(10,2) 一共10位数 2个小数

eg:CONVERT(varchar(100),CAST(联系电话 as decimal(38,0))) 先将float 电话 cast 成decimal 然后convert到varchar使用

切割找内容 parsename 根据.找（从后面找）可以替换.然后使用它

select parsename(replace('1/3','/','.'),1) 结果3 select parsename(replace('1/3','/','.'),2) 结果1

charindex('123','12345')>0 判断是否包含某个字符

eq:iif(charindex('.',出生年月)>0,(REPLACE(出生年月,'.','-')+'-01') 如果有 2021.01 这种字符需要转日期的时候就可以这样处理日期必须满足年月日才可转

连接：

inner join：select 表A inner join 表B on 条件1 and 条件2

left join: select 表A left join 表B on 条件1 and 条件2

outer apply 和 cross apply 得连接以及切割

SQL Server outer apply 和 cross apply - Brambling - 博客园

并集： UNION

UNION 与 UNION ALL 表示并集，可以把两个 SELECT 查询的结果合并成一个，前提是两个 SELECT 所查询的列数量和字段类型一致。不同的是 UNION 会去除重复行，而 UNION ALL 不会去除重复行

常用的30个函数：SQL Server 常用函数使用方法_cc-CSDN博客

常用的日期转换格式函数：sql 时间转换格式 convert(varchar(10),字段名,转换格式) - 水狼一族 - 博客园

常用的数学函数：

　　ceiling（n）：返回不小于n的最小整数

　　floor（n）：返回不大于n的最大整数

　　round（m，n）：四舍五入，n为小数的位数

　　rand：随机生成[0,1]之间的float类型值

　　sign（expr）：当expr为整数、0、负数时分别返回1、0、-1

　　sqrt（expr）：返回expr的平方根

　　abs（expr）：返回expr的绝对值

　　power（expr，n）：返回expr的n次方的值

高级函数：ROW_NUMBER()函数主要是定义一列行数来后续操作使用

分组的话，就是每组都从1开始标记行号

解释一下：ROW_NUMBER() 为每一行返回一个行号, partition by 表示分组，这里表示根据 tourtype 分组，然后我们按照排序。就会得到每个分组内的数量排序的行号。

窗口函数：

SELECT TOP 1000 [TourOrderId],
PackageId
,[Status],
   last_value(status) over (order by tourorderid rows BETWEEN 1 PRECEDING AND CURRENT ROW) as aaa
FROM [Peony191031].[dbo].[TourOrders]

首先是 last_value(status) 要看status的最后一行然后是范围order by tourorderid rows BETWEEN 1 PRECEDING AND CURRENT ROW 范围肯定是先order by排序然后 bteween 范围这个意思是当前行和前一行

这个语句意思是：查找当前行和前一行的最后一行

SELECT TOP 1000 [TourOrderId],
PackageId
,[Status],
   sum(status) over (order by tourorderid rows BETWEEN 2 PRECEDING AND CURRENT ROW) as aaa
FROM [Peony191031].[dbo].[TourOrders]

这个语句意思是：查找当前行和前2行的总和

也可以看第一行first_value（status） nth_value(status,4) 第4行后面写范围这个可以举一反三

for xml path 函数

该函数主要是让多行变成xml格式放在一行上，一般用个逗号分隔

那就会多一个逗号出来在用stuff

sql stuff函数用于删除指定长度的字符，并可以在制定的起点处插入另一组字符。sql stuff函数中如果开始位置或长度值是负数，或者如果开始位置大于第一个字符串的长度，将返回空字符串。如果要删除的长度大于第一个字符串的长度，将删除到第一个字符串中的第一个字符。

select name,stuff((select ','+Position from MHBasicLevelCadre where name= a.Name for xml path('')),1,1,'') '职位'
from [dbo].[MHBasicLevelCadre] a group by name having count(*)>1

切记过滤是在外面过滤然后select 拼接的时候where 匹配一下这样既过滤又匹配

反用 for xml path 的效果行变列

SELECT a.SysNo,b.[value]
FROM (SELECT SysNo,[value]=CAST('<v>'+REPLACE([KeyWord],',','</v><v>')+'</v>' AS xml) FROM SearchKeyConfig) a
OUTER APPLY (SELECT [value]=T.C.value('.','varchar(50)') FROM a.[value].nodes('/v') AS T(C)) b

case when then else 函数

1：常用的情况是行列之间的转换，如下图