SQL server积累

---

前言

这是对sqlserver的一些积累

---

一、操作字符串

1.CHARINDEX函数

返回字符串中某个子串首次出现的起始位置，当CHARINDEX返回值大于0时则包含，为0不包含

SELECT CHARINDEX( '456' , '123456' )
-- 结果是4，因为'456'的'4'是第4个字符

2.STUFF函数

STUFF(string,start,length,string)
先删除字符串中的一个子串，再插入一个新的子串作为替换

• 示例1：替换

SELECT STUFF('123456',2,1,'9')	
-- 结果是193456，从第2个字符串开始，替换长度是1，9是替换后的值
SELECT STUFF('132456',2,2,'23')	
-- 结果是123456，从第2个字符串开始，替换长度是2，23是替换后的值

• 示例2：插入字符串

SELECT STUFF('1235',4,0,'4')
-- 结果是12345，在第4个字符串的地方(原本为5的地方)，插入4

• 示例3：合并行

SELECT role_id, STUFF((SELECT ',' + permission_id
                        FROM sys_role_permission o2
                        WHERE o2.role_id = o1.role_id
                        FOR XML PATH('')), 1, 1, '') AS permission_id
FROM sys_role_permission o1
GROUP BY role_id
-- 在 role_id 列上分组，将role_permission_id合并成一行

3.REPLACE函数

REPLACE(“被搜索的字符串”,“被替换的字符串”,“替换的字符串”)，如果有某个参数为 NULL，此函数返回 NULL

SELECT REPLACE('132456','32','23')
-- 结果是123456，查找132456中是否含有32，有就替换，没有返回132456

4.Substring函数

截取指定位置和指定长度的字符串

SELECT Substring('123456',1,3)
-- 结果是123，从第1个字符串开始，截取长度为3的字符串

5.LEFT和RIGHT函数

从左或右开始截取指定长度的字符串

SELECT LEFT('123456',3)
-- 结果是123，从左开始，截取长度为3的字符串
SELECT RIGHT('123456',3)
-- 结果是456，从右开始，截取长度为3的字符串

6.UPPER和LOWER函数

将字符串转换为大写或小写

7.LEN和DATALENGTH函数

返回字符串中的字符数以及字符串中的字节数

SELECT LEN('123') 
SELECT DATALENGTH('123')
-- 如果有空格也会计算

注：普通字符，字符串的字符数和字节数是一致的，这是因为一个字符只占一个字节的存储空间，Unicode字符，字符串的字符数和字节数是不一致的，一个字符占用两个字节的空间，故字符数是字节数的一半

二、常用功能

1.分页

1.offset /fetch next关键字

2012版本及以上才有
公式：

-- 分页查询公式-offset /fetch next
select * from 表名
order by 主键 或 其他索引列 
-- @pageIndex：页码、@pageSize：每页记录数
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;

示例：

-- 分页查询第2页，每页有10条记录
select * from user
order by uid
offset 10 rows
fetch next 10 rows only ;

2.row_number()关键字

SQL查询语句增加自增的序号一般也用这个
公式：

-- 分页查询公式-row_number()
select * 
from (
	-- rownumber是别名，可按自己习惯取
	select row_number() over(order by 主键 asc) as rownumber,* 
	from 表名) temp_row
where rownumber>((@pageIndex-1)*@pageSize) and rownumber <= (@pageIndex*@pageSize)

示例：

-- 查询第1页，每页有10条记录
select * from (
select ROW_NUMBER() over(order by id) as nums,*
from user
) temp_row
where nums > 0 and nums <= 10

用row_number()分页时，要注意某些where条件的位置
示例：

-- 小李的序号是15，就不会查询出来，导致结果为空
select * from (
select ROW_NUMBER() over(order by id) as nums,*
from user
) temp_row
where nums > 0 and nums <= 10 and userName = '小李'
-- 正确的写法：
select * from (
select ROW_NUMBER() over(order by id) as nums,*
from user
where userName = '小李'  -- 条件应该优先放子查询里
) temp_row
where nums > 0 and nums <= 10 

2.批量插入和批量更新

1.select into 和insert into select

它们之间最大的区别就是：select into 会自动创建表，而 insert into select 不会

1、select into

-- 把表2的数据插入表1，表1不存在就自动创建
select * into table_1 from table_2

-- 把表2指定的字段插入表1
select column_1,column_2 into table_1 from table_2

-- where 1=2 根据表2创建相同结构的表1，但不复制数据
select * into table_1 from table_2 where 1=2

2、insert into select

-- 将表2的数据全部复制到表1，两张表的字段必须相同，两张表必须都是存在的
INSERT INTO table_1 SELECT * FROM table_2;

-- 如果只希望复制某些列到另一张表中，则可以使用下面的 SQL 语句
INSERT INTO table_2(column_1,column_2) SELECT column_1,column_2 FROM table_1;

2.merge into

可以同时完成新增、修改和删除
公式：

merge into 目标表 T
using 源表 S
on(T.条件字段1=S.条件字段1 and T.条件字段2=S.条件字段2 ……)  
when matched then update set T.字段=S.字段 --目标表别称T和源表别称S都不要省略
when  not matched then insert (T.字段1,T.字段2……)values(S.字段1,S.字段2……) --目标表别称T可省略,源表别称S不可省略

示例：

merge into table_1 as T
using table_2 as S 
on T.id = S.id 
when matched -- 当条件成立时，目标表T的那条数据被更新
then update set T.name = S.name
when not matched -- 目标表T中没有的id，源表中有，则新增
then insert (id,name)values(S.id, S.name)
when not matched by S -- 目标表中存在，源表中不存在，则删除
then delete
-- 最后也可以把刚刚做过变动的数据进行输出
output $ACTION as [ACTION], Inserted.id AS 插入的id,
Inserted.name AS 插入的信息,
Deleted.id AS 删除的id,
Deleted.name AS 删除的信息 ;

3.临时表

临时表前要加“#”，“#”代表局部临时表，“##”代表全局临时表
创建临时表：

-- 根据现有的表创建指定列的临时表
SELECT 列1,列2... INTO #Temp1 FROM table where 1=2

-- 手动创建临时表，和创建实体表一致
create table #Temp1(字段1 约束条件,字段2 约束条件,.....)

-- 创建临时表前后都要判断删除临时表
IF OBJECT_ID('tempdb..#TMP1') IS NOT NULL  DROP TABLE #TMP1

大数据量插入，首选SqlBulkCopy，插入临时表进行交互

3.SQL小技巧

1. 查询表时后尾加上 WITH ( NOLOCK ) ,防止锁表；
2. SQL的where后面，优先 ==，然后like，最后in；
3. UNION 会去掉结果集中的重复记录，UNION ALL 不会；
4. 写SQL时对数字进行操作时，要用ISNULL(Number,0)进行判断是否为null；
5. 自增id重置为1；

   DBCC CHECKIDENT('表名', RESEED, 0);
   

6. 查询数据库的端口号；

   exec sys.sp_readerrorlog 0, 1, 'listening';
   

7. 手动往数字自增列插入；

   SET IDENTITY_INSERT table_Name ON; -- 开启
   -- 插入语句
   SET IDENTITY_INSERT table_Name OFF;  -- 关闭
   

8. 自定义排序

   select * from teble_exaple order by(
   case class
   when ‘C’ then 1,
   when ‘A’ then 2,
   when ‘D’ then 3
   else 4 
   end desc
   

)
```
9. 查询表1在表2中但不在表3的数据

SELECT t1.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Id = t2.Id
LEFT JOIN Table3 t3 ON t1.Id = t3.Id
WHERE ISNULL( t3.Id,'')='';	

10. 待补充

---

总结

待补充