SQL Server知识点滴札记

本文主要是介绍SQL Server知识点滴札记，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、从日期时间提取日期、时间

提取日期：convert(varchar(10),a.RegisterDate,120)

提取时间：convert(varchar(8),a.RegisterDate,108)

2、sql里怎么算某个日期到至今一共有多少时间

select datediff(dd,'07-09-01',getdate()) 天数

几天：DD

几月：MM

几年：YY

时分秒依些类推

3、可以用已有字段来构造新的字段

select '产品消费' as type,SS_ConsumeGoodsDetail.POS as 付款方式,SS_Consume.CustomerId,(case PState when '1' then '冲账' else '' end ) as 状态,SS_Consume.ShopId,SS_Consume.SaleDate,SS_Consume.InvoicesNo,0.00 as UseCard,0.00 as Cash,0.00 as jifen,0.00 as Bank,0.00 as DiYongQuan,0.00 as ProjectUseCard,0.00 as GoodsUseCard,isnull(SS_ConsumeGoodsDetail.UseCard,0.00) as NormalUseCard,0.00 as SendUseCard,0.00 as ProjectItemQty,CardDetailId from SS_Consume,SS_ConsumeGoodsDetail,bd_CardDetail,bd_CardClass  where SS_Consume.mdid=SS_ConsumeGoodsDetail.mdid and SS_ConsumeGoodsDetail.CardDetailId=bd_CardDetail.Id and bd_CardDetail.CardClassId=bd_CardClass.Id and LEFT(CardKindNo,3)<>'003' and LEFT(CardKindNo,3)<>'002003' and SS_Consume.fstate='1'and SaleDate>='2020-01-01' and SaleDate<='2020-12-25' and SS_Consume.Shopid='720000400030' and CardDetailId='770091005158' 

NormalUseCard(正常划卡)、SendUseCard(赠送划卡)都是在用usecard构造出来的

4、列拼接，把单独一列数据，然后拼接成一行数据。比如一列中有三行，分别是“天”“地”“人”。你现在想得到一行数据“天地人”

SELECT STUFF((SELECT ','+user_name FROM dbo.users FOR XML PATH('')),1,1,'')

5、sql字段名要符变量名定义

6、去重一般用distinct + 字段，分统统计一盘会加distinct去重

7、SQL Server CONVERT() 函数用不同的格式显示日期/时间数据

CONVERT() 函数是把日期转换为新格式进行显示

CONVERT(date_type(length),data_to_be_converted,style)

该函数共有三个参数。

一. date_type(length) 要进行转换的数据类型，长度可选。

二. date_to_be_converted 需要进行转换的数据。

三. style 规定日期时间的输出格式。

8、SQL SERVER FOR XML PATH 用法 

有时候我们需要把多行数据，合并成一行显示，并用逗号或者其他方式分隔显示，这时候我们可以使用FOR XML PATH的方式，来实现需求。测试数据如下：

--测试数据
if not object_id(N'Tempdb..#T') is nulldrop table #T
Go
Create table #T([Province] nvarchar(22),[City] nvarchar(23))
Insert #T
select N'河北',N'石家庄' union all
select N'河北',N'唐山' union all
select N'河北',N'秦皇岛' union all
select N'山西',N'太原' union all
select N'山西',N'大同'
Go
--测试数据结束

我们想按照省份显示该省所有的市区，并且每个省份只显示一条数据，写法如下：

SELECT  [Province] ,STUFF(( SELECT  ',' + #T.[City]FROM    #TWHERE   [Province] = a.[Province]FORXML PATH('')), 1, 1, '') AS value
FROM    #T a
GROUP BY a.[Province] 

  结果如下：

 这样我们就通过FOR XML PATH的方式实现了我们的需求，当然FOR XML PATH还有其他很多用法，而且合并显示的方法也有其他写法，后续慢慢补充。

9、BLOB转字符串

CAST(字段 as varchar(500)) as 字段

10、dbGrid显示ntext类型字段

privateprocedure Getaddress(Sender: TField; var Text: String;DisplayText: Boolean);{ Private declarations }public{ Public declarations }end;varForm1: TForm1;implementation{$R *.dfm}procedure TForm1.Getaddress(Sender: TField; var Text: String;DisplayText: Boolean);
beginText:=Sender.AsString;
end;procedure TForm1.ADOQuery1AfterOpen(DataSet: TDataSet);
beginADOQuery1.FieldByName('address').OnGetText:=Getaddress;
end;

11、上月最后一天和上月第一天

 -----上月最后一天

DATEADD(DD,-DAY(@saledate),@saledate)

--上月第一天

DATEADD(MONTH,-1,DATEADD(DD,-DAY(@saledate)+1,@saledate) ) 

select  DATEADD(DD,-DAY('2021-01-09'),'2021-01-09')
select  DATEADD(MONTH,-1,DATEADD(DD,-DAY('2021-01-09')+1,'2021-01-09') ) 

12、现有列的 ANSI_PADDING 设置为 “off”。将以 ANSI_PADDING 为 “on” 的设置创建新列。

执行 SET ANSI_PADDING ON 即可

13、要执行以下语句，表字段名、类型、顺序、列数（字段数）都要一样

insert into bd_employees select * from 医美乐.dbo.bd_employees

14、SQL server判断字符串是否包含某个字符串

函数：CHARINDEX

通过CHARINDEX如果能够找到对应的字符串，则返回该字符串位置，否则返回0

基本语法如下：

　　CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

      expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。

　　expressionToSearch ：用于被查找的字符串。

      start_location：开始查找的位置，为空时默认从第一位开始查找

CHAEINDEX示例

　　1.简单用法　　

　　select charindex('test','this Test is Test')
、

2.增加开始位置

　　select charindex('test','this Test is Test',7)

3.大小写敏感

　　select charindex('test','this Test is Test'COLLATE Latin1_General_CS_AS)

查询结果：

　　返回结果为0，因为大小写敏感，找不到test所以返回的就是0，默认情况下， SQL SERVER是大小不敏感的，所以我们简单示例中返回结果不为0，但是有些时候我们需要特意去区分大小写，因此专门SQL SERVE提供了特殊的关键字用于查询时区分大小写，其中CS为Case-Sensitve的缩写。

　　4.大小写不敏感

　　select charindex('Test','this Test is Test'COLLATE Latin1_General_CI_AS)

我们也可以这样说明是大小写不敏感，其中CI是Case-InSensitve的缩写，即大小写不敏感，当然我们没必要多此一举

5、ntext不能直接distinct

可用cast转成varchar就可以了

6、字段不能以数字开头

7、xml for path 和stuff搭配使用

(select STUFF((select '/' + bt.EmployeeNo from SS_Employee_Card at,bd_employees bt where at.EmployeeId=bt.Id and at.CardDetailId=a.id  FOR XML PATH('')),1,1,'')) as 员工编号

8、字符串截取

substring(remarks1,charindex(':',remarks1)+1,len(remarks1)-charindex(':',remarks1)) as remarks

9、判断两个时间的时间间隔

SELECT DATEDIFF(DAY,LastDate,GETDATE()) AS DiffDate from bd_Customers where DATEDIFF(DAY,LastDate,GETDATE()) < 365

10、备份数据库命令

命令:BACKUP DATABASE [素问道] TO  DISK = N''D:\每日备份bak\素问道_backup_2021_06_29_060004_7313270.bak'' WITH NOFORMAT, NOINIT,  NAME = N''素问道_backup_2021_06_29_060004_7303505'', SKIP, REWIND, NOUNLOAD,  STATS = 10 

GO

11、将日期转成标准日期

例：2021-3-21这是不标准日期

通过convert(date,'2021-3-21') 转成标准日期：2021-03-21

不转的话作日期比较会不准确，如：‘2021-3-21’会大于2021-07-01

12、日期字符串转日期类型

字符串要符合日期标准格式

日期和时间合并转日期：convert(datetime,concat(date,' ', CONVERT(varchar(20), begintime, 120)))

ps:108取时间部分 120取整个日期时间 

13、identity 值初始化 0
DBCC CHECKIDENT('Table',RESEED,1)

14、获取随机数

select rand()  -- 0~1之内的随机数

15、设置小数据位函数Round(数值，保留小数位)

16、两个表连接不加条件就是交叉连接

SELECT (convert(varchar(10),Device.ID)+convert(varchar(10),Today_TimePirce.TimeNum)+convert(varchar(10),Today_TimePirce.WeekName))as MainIndex,Device.ID as ThermostatID,IPAddress,ChangeCount,Today_TimePirce.*
FROMDevice,(SELECT * FROM TimePrice WHERE WeekName=1)AS Today_TimePirce

17、SqlServer中 SET DATEFIRST更改

在 SQL Server 中默认情况下，每周的开始都是从周日开始算起的，如果默认星期一呢？

这里有三种方式可以解决这个问题：

一：直接通过 SET DATEFIRST VALUE 来更改重新生成新的 DimDate，然后每次需要单独计算 Week Number 的时候根据 Date Key 关联一下就可以了，但这样就需要不断 JOIN DimDate，每一条记录都要 LookUp 一遍

二：在存储过程中需要使用到  Week Number 的时候，就先设置一下 SET DATEFIRST 然后在使用 DATEPART() 函数来获取 Week Number

SET DATEFIRST 1   --定义日期周一开始 

三：直接写一个函数，每次调用一下就可以了

SELECT @@DATEFIRST  --7
SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 2

18、存储过程执行SQL语句

set @QStr='select * from table'

Exec(@QStr) 

19、要用于恢复贝份文件不能放桌面，因为在桌面恢复不了 

20、 恢复数据库的时候，数据库在独占使用，否则恢复不了

21、Union前后Select查询语句不能用order by 

22、比较日期

CONVERT(date,TimeRecord_Today.CreateRecordTime)<CONVERT(date,@CurrentDateTime) 

23、系统自带全局变量@@RowCount判断查询记录数，插入、更新影响记录数，独立于同一个会话期，不同会话期值是等于各自的操作影响记录数

24、sqlserver重置自增id

 DBCC CHECKIDENT (要改的表名,RESEED,从几开始) 

25、INSERT INTO TimeRecord_Today select 【字段】from 表

字段顺序要和TimeRecord_Today字段顺序一致

例如：TimeRecord_Today字段顺序是

[EnMeterID],[EnMeterENum],[BeginTime],[EndTime],
[BeginAluHeat],[EndAluHeat],[PlusUseHeat],[HeatPrice],
[BeginAluCool],[EndAluCool],[PlusUseCool],[CoolPrice],
[IsEnable],[TimeNum],[WeekName],[SumTimeRecord_Cool],[SumTimeRecord_Heat],[CreateRecordTime],[IsStatistic],
[BeginAluFlow],[EndAluFlow],[FlowPrice],
[bRecord]=1,[plusUseFlow],[SumTimeRecord_Flow],[BeginTimeOperTime],[EndTimeOperTime],
[BException],[Remark],[ChangeCount]

INSERT INTO TimeRecord_Today
select [EnMeterID],[EnMeterENum],[BeginTime],[EndTime],
[BeginAluHeat]=rAluHeat,[EndAluHeat]=rAluHeat,[PlusUseHeat],[HeatPrice],
[BeginAluCool]=rAluCool,[EndAluCool]=rAluCool,[PlusUseCool],[CoolPrice],
[IsEnable],[TimeNum],[WeekName],[SumTimeRecord_Cool],[SumTimeRecord_Heat],[CreateRecordTime],[IsStatistic],
[BeginAluFlow]=rAluFlow,[EndAluFlow]=rAluFlow,[FlowPrice],
[bRecord]=1,[plusUseFlow],[SumTimeRecord_Flow],[BeginTimeOperTime]=GetDate(),[EndTimeOperTime]=GetDate(),
[BException],[Remark],#TimeRecord.[ChangeCount] from #TimeRecord,EnMeter

26、同一段代码里,不允许2次create同名的临时表,即使是在不同的程序分支里

27、SQL CASE WHEN 语句的嵌套使用方式 

 select id,userid,  ys,casewhen  pj_ys is NULLthen ( case when  pj_ys1 is NUll then ys else pj_ys1 end) else pj_ys   end tfrom dbo.tbl_Emplyeeblogs

28、查询结果中相同记录只取一条可用top 1 字段或distinct 字段1，字段2过滤

29、存储过程局部临时表作用域只在存储过程内，如果要在存储过程外访问，那就得用全局临时表 

30、替换字符串中字符Replace,截取子字符串substring

31、获取每月最后一天

select eomonth('20230331') 

32、where语名字符串条件只能用单引号，不能用双引号。 

这篇关于SQL Server知识点滴札记的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---