SQL笔记记录

SQL笔记

字符串分割

--第一个
select substring('1,2,3,4',1,CHARINDEX(',','1,2,3,4')-1)
--第二个
select SUBSTRING(STUFF('1,2,3,4',1,CHARINDEX(',','1,2,3,4'),''),1,CHARINDEX(',',STUFF('1,2,3,4',1,CHARINDEX(',','1,2,3,4'),''))-1)
--第三个
select SUBSTRING(STUFF(STUFF('1,2,3,4',1,CHARINDEX(',','1,2,3,4'),''),1,CHARINDEX(',',STUFF('1,2,3,4',1,CHARINDEX(',','1,2,3,4'),'')),'') ,1,CHARINDEX(',',STUFF(STUFF('1,2,3,4',1,CHARINDEX(',','1,2,3,4'),''),1,CHARINDEX(',',STUFF('1,2,3,4',1,CHARINDEX(',','1,2,3,4'),'')),'') )-1)
--最后一个
select SUBSTRING('1,2,3,4',len('1,2,3,4')-(CHARINDEX(',',REVERSE('1,2,3,4'))-2),2)
--REVERSE以相反的字符顺序返回一个字符串。

--查看','最后一次出现的位置
select len('1,2,3,4')-(CHARINDEX(',',REVERSE('1,2,3,4'))-1)
--出现的次数
select LEN('1,2,3,4')-LEN(REPLACE('1,2,3,4',',','')) 

字符串分割返回表值

create function [dbo].[StrIntToTable](@str varchar(max))
returns @tableName table
(
   strList varchar(100)
)
as
begin
set @str = @str+','
declare @insertStr varchar(100) --截取后的第一个字符串
declare @newstr varchar(max) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(',',@str)-1)
set @newstr = stuff(@str,1,charindex(',',@str),'')
insert @tableName values(@insertStr)
while(len(@newstr)>0)
begin
   set @insertStr = replace(left(@newstr,charindex(',',@newstr)-1),' ','')
   insert @tableName values(@insertStr)
   set @newstr = replace((stuff(@newstr,1,charindex(',',@newstr),'')),' ','')
end
return
end
--select * from StrIntToTable( '1,2,3,4')

SQL语句增加列、修改列、删除列

增加列：
alter table tableName add columnName varchar(30)  

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

修改列类型：
alter table tableName alter column columnName varchar(4000)  

修改列的名称：

EXEC  sp_rename   'tableName.column1' , 'column2'  (把表名为tableName的column1列名修改为column2)
下面的示例将 TerritoryID 表中的 Sales.SalesTerritory 列重命名为 TerrID。 将以下示例复制并粘贴到查询窗口中，然后单击“执行” 
  USE AdventureWorks2012;  
GO  
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';  
GO  
删除列： alter table tableName drop column columnName4,指定表中某列默认数据ALTER TABLE dbo.doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ;

联表更新

UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);

查询表字段说明与类型

create FUNCTION [dbo].[GetColumn_Description]
(	
@TableName nvarchar(50)
)
RETURNS TABLE 
AS
RETURN 
(
	select a.*,D.ORDINAL_POSITION as 字段序号,D.DATA_TYPE AS '数据类型',D.CHARACTER_MAXIMUM_LENGTH as 字符串长度,D.COLUMN_DEFAULT as 默认值 from (
SELECT
A.name AS '数据库名',
B.name AS '字段名',
ISNULL( C.value,'') AS '字段说明'
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
--left join
WHERE A.name = @TableName) a left join  information_schema.columns  D on a.字段名=D.COLUMN_NAME and D.TABLE_NAME=a.数据库名

)

--	select * from GetColumn_Description('CenterChargeBill')

动态执行sql (sp_executesql)

--必须是Nvarchar类型
declare @test Nvarchar(500)
set @test='select 123,'+'getdate()'
exec sp_executesql @test

SQL内置函数：

短日期
Convert(char(10),getdate(),126) 时间不要小时分钟秒

Convert(char(10),getdate(),108) 取时分秒

cast(AVG(sanR)as decimal(18,3)) avgnz 取余数

Substring（）:截取字符串，如：select substring('abcdefg',1,4)得到的结果是abcd，即从第一位开始，截取4位

isnull(列名,'') :Sql ISNULL() 函数 使用指定的替换值替换 NULL。如果指定的对象不为 NULL，那么返回该表达式的值；否则返回你想要的替换的值比如你这里的：isnull(列名,'')如果列名不为null的话，它会返回指定列名的值，null的话则会返回''。

LTRIM()函数,用于去除字符串左边多余的空格，不管有多少个空格，一次性全部清除。

RTRIM()返回删除字符串右边的空格后的字符串。

Stuff():删除指定长度的字符，并在指定的起点处插入另一组字符。SELECT STUFF('abcdef', 2, 3, 'ijklmn') 结果 aijklmnef

GETDATE():获取系统当前时间

CONVERT() 函数是把日期转换为新数据类型的通用函数。convert(varchar,getdate(),120),120 或者 20    yyyy-mm-dd hh:mi:ss(24h) 
Convert(char(10),getdate(),126)
   
MONTHS_BETWEEN(date1，date2) date1-date2日期值之间间隔了多少个月

SELECT DATEDIFF(hour, '2017-01-01 6:15:12', '2017-01-02 7:18:20') 时间段间隔多少小时

select DATEDIFF(SECOND,'2019-11-14 14:21:18.050',getdate())时间段间隔多少秒

row_number() over(order by begintime) as xuhao   数据库序号

DATEADD(hour,1,getdate())数据库时间字段增加时间

ROUND((cefengHDtou+cefengHDzhong+cefengHDwei)/3, 2) pingjun        保留余数后两位

dateadd(d,-30,GETDATE()) sql加减时间

--获取当前星期几，datepart(weekday,getdate())老外周日是1，所以-1
select datename(weekday,getdate())

select datepart(weekday,getdate())-1
select datepart(yyyy,getdate())

select datepart(mm,getdate())

select datepart(dd,getdate())

full join 不要条件的关联表

union all 不要条件的关联表

raiserror('该用户在K3无用户名!',18,18)   数据库抛出报错

convert(decimal(18,4),'123.1234456') 取余数

select charindex('c', 'abcdef')   --结果为3，查询c在abcdef中的位置

select REPLACE('ABCDEF','E','e')  --结果ABCDeF，把E替换成e

select b.addtime,c.pgman, c.zhuangtai,* 
from T_YeWuChanPinZX  a
outer apply (select max(addtime) addtime from PingGuInfo where ywbianhao=a.SQbianhao) b
left join PingGuInfo   c on b.addtime=c.addtime     --两张表同一个字段中取最大的addtime

select datename(weekday,'2019-11-10 15:52:11.607')  --查询时间当天是星期几

select dbo.f_sunday('2019-11-09 15:52:11.607','2019-11-11 15:52:11.607')  --查询两个时间内包涵周末的次数

charindex(d.FName,@xiaozu)>0 代替in查询里的值，存成变量查询        in ()

 DateDiff(dd,jinxiangDate,getdate())=0查询当天数据

DECLARE @i int,@x int
set @i=1
set @x = 1
while(@i <= 3)
begin
print @x
set @x = @x + 1
set @i = @i+1
end                --数据库循环，显示1,2,3

--having求最大分数大于70的人的姓名
select name,max(fenshu) from ceshi  group by name having max(fenshu)>70

表值函数是当做表来查询    select * from fenGe('10-2-3','-')
标量值函数当做字段来查询  select dbo.f_sunday('2019-11-09 15:52:11.607','2019-11-11 15:52:11.607')

列的数据合并生同一行
select code,
(select (code+',') from productSmallTwo where pro_code='G.571B113451505001604103' and place in(4,6,7) for xml path('')) code2
from productSmallTwo where pro_code='G.571B113451505001604103' and place in(4,6,7)

ISNUMERIC(code )=1 判断返回的列是不是数字

列转行
select n.*,r.容量,r.串,r.并,(r.串*r.并) '电芯数' from NewXiaoShouJiHua n
left join (
select pro_code,
SUM(case when place=4 then case when ISNUMERIC(code )=1 then CONVERT(int,code) else CONVERT(int,miaoShu) end end) '容量',
SUM(case when place=6 then CONVERT(int,miaoShu) end) '串',
SUM(case when place=7 then case when ISNUMERIC(miaoShu )=1 then CONVERT(int,miaoShu) else CONVERT(int,code) end end) '并'
from [192.168.1.6].[geRuiPu].[dbo].productSmallTwo where pro_code like 'G.%'
group by pro_code
)r on n.pro_code=r.pro_code

列转行，pivot函数
select * from tb pivot(max(分数) for 课程 in (语文,数学,物理)) a

select distinct a.*,b.* from
(select row_number() over (order by 生产日期 asc ) 序号,生产日期,K3批号,型号,小组,生产数,合格品,不良数 as 不良数KKK,不良率 as 不良率KKK  from #table 
where 工段='三次电压内阻测试' group by 生产日期,K3批号,型号,生产数,合格品,不良数,不良率,小组 ) a
full join (select * from (select 小组  as 小组1,生产日期 as 生产日期KKK,K3批号 as K3批号KKK,型号 as 型号KKK,生产数 as 生产数KKK,合格品 as 合格品KKK,不良数,不良率,type,num from #table where 工段='三次电压内阻测试' and 生产数 <> 合格品) a PIVOT(sum(num) for [type] in ([压降率不合格],[压降率离群])) as piv ) b
on a.生产日期= b.生产日期KKK and a.K3批号 = b.K3批号KKK and isnull(a.不良数KKK,0) = isnull(b.不良数,0) and a.小组=b.小组1


根据float排序
SELECT top 1 num from qualityLog WHERE e_id=24 AND listDetail='成品电压' AND jiXing='' AND shengChanHao='ZN1911290022' Order by cast(num as float)

declare @Name nvarchar(50)--提取字符串中的数字
set @Name=@shengchanhao

while patindex('%[^0-9]%',@Name)>0
begin
set @Name=stuff(@Name,patindex('%[^0-9]%',@Name),1,'')
end

判断是否包含字母
select PATINDEX('%[A-Za-z]%', ‘ads23432')=0

判断是否包含数字
PATINDEX('%[0-9]%', ‘234sdf')=0

去掉空格和换行符
select REPLACE(REPLACE(REPLACE(REPLACE('LP 43SC3000J',CHAR(13),''),CHAR(10),''),CHAR(9),''),' ','')

update多表更新
		update yanShou
		set finishMan='系统确认',finishDate=getdate()
		from yanShou 
		inner join sheBeiInfo
		on yanShou.sheBeiCode=sheBeiInfo.sheBeiCode
		where sheBeiInfo.fname='光明厂' and yanShou.baoXiuDate<'2020-08-01'

修改表字段类型
ALTER TABLE ZhuangPeiGongYi ALTER COLUMN jrz_line varchar(50);