006 数据库学习笔记--字符串操作函数 + 索引

常用字符串操作函数：

 

select case when LEFT('-123456',1) ='-' then '-123456'
when LEFT('-123456',1) <>'-' then '123456' end as "test"

case when
left((str(CONVERT(decimal(18,2), (1-sum( case ImageScore when 1 then 1 when 2 then 1 else 0 end)))/(COUNT(*) )* 100,5,2)+'%'),1)='-' then '0%'
else str(CONVERT(decimal(18,2), (1-sum( case ImageScore when 1 then 1 when 2 then 1 else 0 end)))/(COUNT(*) )* 100,5,2)+'%'
end as "PassRate"

 

--返回字符串中指定的子串出现的开始位置(索引从1开始)
select CHARINDEX('34','1234567890123') as startIndex
--返回字符串中指定的子串出现的开始位置(索引从1开始，字串前必须加%)
select PATINDEX('%34%','1234567890123') as startIndex

--大小写转化
select LOWER('abc') 小写,UPPER('abc') 大写

--去空格;	LTRIM去除左侧空格;		RTRIM去除左侧空格  
select LTRIM(' abc  ') 去除左侧空格,RTRIM('  abc   ') 去除右侧空格

--截取字符串 retrun 1234
--从左侧截取 截取几位
select LEFT('123456',4) as 从左侧开始截取截取4位 --return1234
--从右侧截取 截取几位
select RIGHT('123456',4) as 从左侧开始截取截取4位 --return3456
--从第几位开始截取，截取多长
select SUBSTRING('123456',2,4) as 从左侧第二个位置开始截取4位 -- return 2345

--将指定字符串重复指定次数
select REPLICATE('123',2)  -- return 123123

--字符串翻转
select REVERSE('123') --return 321

--替换字符串内容（将12替换为ab）
select REPLACE('123456','12','ab') --return ab3456

--替换字符串 可指定长度和位置（从第三位开始替换2为，替换为ab）
select STUFF('123456',3,2,'ab') -- return 12ab56

--应用
select 
(stuff((select '|' + b.DIRECTION from QUEUEDETAIL b where b.APPOINTSID = a.APPOINTSID for xml path('')),1,1,''))as DIRECTIONALL,
(stuff((select '|' + b.PARTOFCHECK  from QUEUEDETAIL b where b.APPOINTSID = a.APPOINTSID for xml path('')),1,1,''))as PARTOFCHECKALL
from QUEUEDETAIL b,HIS_QUEUE a

 

--示例
declare @TableNameL varchar(200), @TableName sysname = 'DictPublic',@LengthStr varchar(100)
set @TableNameL = (select (lower(left('helloWpf',1))+SUBSTRING('helloWpf',2,len('helloWpf'))))
set @TableName = (select (lower(left(@TableName,1))+SUBSTRING(@TableName,2,len(@TableName))))
set @LengthStr='获取字符串长度：'+ convert(varchar,len('helloWpf'))

print @TableNameL
print @TableName
print @LengthStr 

select @TableNameL as test1,@TableName as test2,@LengthStr as 获取字符串长度
go

 索引：可理解为目录，可快速定位查询数据，避免扫描整个表，从而提升查询数据的速度，提升系统的效率。

缺点：占用存储空间，索引过多，会损害系统性能(索引并不是越多越好)

SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）

注：主键列默认为聚簇索引，聚簇索引只能有一个，非聚簇索引可以有多个；一般建议在对表创建索引时，使用日期类型的列创建且创建为降序。

 在创建复合索引是建议将日期列作为首列，同时在使用列上应遵循——使用频繁使用的列

注：在需要对多个列建立索引时，建议为其创建组合索引——使用频繁使用的列、小数据类型的列

在创建索引时，不制定索引类型，默认为非聚集索引

索引分为：聚集索引、非聚集索引

1.聚集索引：Clustered--表的存储顺序

在聚集索引中，每张表只能有一个；

在聚集索引中，索引的顺序决定表中数据的顺序(逻辑顺序和物理顺序一致)；

在聚集索引中，在表中创建主键列或唯一约束时，会自动创建聚集索引(即在表中添加主键列时，数据库引擎会自动创建约束和索引)；

聚集索引效率比非聚集索引效率要高；

2.非聚集索引：NonClustered

非聚集索引，可以有多个；

在非在聚集索引中，索引的结构完全独立与数据行的结构；

在非在聚集索引中，数据表中的数据行和索引的顺序不一致(逻辑顺序和物理顺序一致)；

非聚集索引效率比聚集索引效率低；

 查看指定表的索引 

exec sp_helpindex his_queue

go
exec sp_helpindex queuedetail

 应用示例：（注：DROP_EXISTING = OFF/ON, --索引如存在，则是否删除已存在索引）

USE DB
GO 
IF NOT EXISTS(SELECT Name FROM SysColumns WHERE id=Object_Id('[HIS_QUEUE]') AND Name = 'InsertDateTime')
BEGIN  
   ALTER TABLE HIS_QUEUE ADD InsertDateTime  datetime DEFAULT (CONVERT([date],CONVERT([varchar](100),getdate(),(23)),(23)))    
END
GO
IF NOT EXISTS(SELECT Name FROM SysColumns WHERE id=Object_Id('[QUEUEDETAIL]') AND Name = 'InsertDateTime')
BEGIN
   ALTER TABLE QUEUEDETAIL ADD InsertDateTime  datetime DEFAULT (CONVERT([date],CONVERT([varchar](100),getdate(),(23)),(23))) 
END
GO
update QUEUEDETAIL set InsertDateTime=(CONVERT([datetime], QUEUEDETAIL.APPOINTDATE  ,(20)))
update HIS_QUEUE set InsertDateTime=(CONVERT([datetime], HIS_QUEUE.[InsertDate]  ,(20)))
GO
 
/****** Object:  Index [DF_HISQUEUE_InsertDateTime]    Script Date: 2023-05-31 10:21:54 ******/
 
IF EXISTS (SELECT name FROM sys.indexes 
            WHERE name = N'DF_HISQUEUE_InsertDateTime')  
DROP INDEX [DF_HISQUEUE_InsertDateTime] ON [dbo].[HIS_QUEUE] WITH ( ONLINE = OFF )
GO
 
/****** Object:  Index [DF_HISQUEUE_InsertDateTime]    Script Date: 2023-05-31 10:21:54 ******/
CREATE CLUSTERED INDEX [DF_HISQUEUE_InsertDateTime] ON [dbo].[HIS_QUEUE]
(
    InsertDateTime desc,
    [APPOINTSDATE] DESC,
    [APPKIND] DESC,
    [HISEXAMNO] DESC,
    [HISID] DESC,
    [NAME] DESC,
    [QUEUENAME] DESC,
    [EXECUTEDEPT] DESC
 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
/****** Object:  Index [DF_QUEUEDETAIL_InsertDateTime]    Script Date: 2023-05-31 10:34:41 ******/
IF EXISTS (SELECT name FROM sys.indexes 
            WHERE name = N'DF_QUEUEDETAIL_InsertDateTime')  
DROP INDEX [DF_QUEUEDETAIL_InsertDateTime] ON [dbo].[QUEUEDETAIL] WITH ( ONLINE = OFF )
GO
 
/****** Object:  Index [DF_QUEUEDETAIL_InsertDateTime]    Script Date: 2023-05-31 10:34:41 ******/
CREATE CLUSTERED INDEX [DF_QUEUEDETAIL_InsertDateTime] ON [dbo].[QUEUEDETAIL]
(
    InsertDateTime desc,
    [APPOINTDATE] DESC,
    [APPKIND] DESC,
    [HISEXAMNO] DESC,
    [QUEUENAME] DESC,
    DIRECTION DESC,
    --CheckItemRemark DESC,
    PARTOFCHECK DESC
)
WITH 
(
　　PAD_INDEX = OFF, 
　　STATISTICS_NORECOMPUTE = OFF, 
　　SORT_IN_TEMPDB = OFF, 
　　DROP_EXISTING = OFF, --索引如存在，则是否删除已存在索引；索引不存在不可ON
　　ONLINE = OFF, 
　　ALLOW_ROW_LOCKS = ON, 
　　ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO