Sql Server数据库小知识点总结

　　把我在开发时候遇到的一点小知识持续更新在这里~

1.where条件时常变

where UserID='1' 这里的UserID呢，它的值是经常在变化的，有时候要查2，有时候要查3的，有时候要查全部人！这里定义一个函数传参数的时候，查全部人这里需要搞一个if分支判断一下，但是呢，不需要~~好的办法来了

select * from 表 Where UserID="+UserID+" 

大概就是这样子，UserID是一个参数，你有需要的时候直接传给我1或者2或者其他数字都OK，但是你不需要这个条件的时候，只需要传给我UserID这个就行了，UserID=UserID会查出所有的数据~

2.联表查询都知道，现在来试试三表查询

 Sql = "select * from VehicleRegistration a inner join TB_DictData b on a.VehicleID=b.Name inner join T_ACL_User c on a.UserID=c.ID  where b.Value='" + Value + "'";

多个inner join on 就ok了

3.出现这样的错误：

消息 8120，级别 16，状态 1，第 6 行
选择列表中的列 'a.Value' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

原因以及解决办法：

凡是在group by后面出现的字段，必须同时在select后面出现；凡是在select后面出现的、同时未在聚合函数中出现的字段，必须同时出现在group by后面”，检查sql是否符合上述法则。

其实这个group by还有一些需要注意的地方，在select后面的如果是 as 某一列存在的话是不需要出现在group by后面的，还有，如果select后面出现聚合函数，例如SUM，count等，则一定要有group by存在 

 

4.书写sql语句的方法(一个资深的同事建议，写在using语句里面可以自动释放数据库链接)

  public static DataTable SqlConnectionInformation()
        {
            string connstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;    
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();   
                } 
                string sql = "select * from table";
                SqlCommand com = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                adapter.Fill(dt);  
            }

            return dt; 
        
        }

 5.要学会使用in啊，我脑残的写了一大串子...

select * from WEB_ItemInfo i where id in(26933,26934,26935,26936,26937,26938,26939,26940,26941)

我傻逼的写了9次，然后用union连接了起来

6.学会使用CTE公用表表达式

with t as
(
 select * from ......
)
select * from t where .....

　　这是简单的CTE，可以写复杂一点，比如想对表的查询结果继续进行操作，那么就多写几个CTE

with t as(
select CONVERT(varchar(8), a.ItemDate, 112) as datekey ,left(i.Name,5)as Name,a.ItemValue
from Web_ItemInfo i inner join AnalysisData a  on i.ID in(26933,26934,26935,26936,26937,26938,26939,26940,26941) and i.ID=a.ItemID
),
t1 as(
select  convert(varchar(20),'蜀云泉真帅啊') as plantname ,convert(varchar(20),'音乐家')  as divisionname,t.datekey,t.Name,SUM(ItemValue) as data from t where datekey>= dbo.GetUpdateDateKey()
group by Name,datekey
),
t2 as(
select convert(varchar(20),'蜀云泉真帅啊') as plantname,convert(varchar(20),'音乐家')  as divisionname,
convert(varchar(12),replace(date_day,'-','')) as datekey,b.pumpname,
(isnull(h0,0)+isnull(h1,0)+isnull(h2,0)+isnull(h3,0)+isnull(h4,0)+isnull(h5,0)
+isnull(h6,0)+isnull(h7,0)+isnull(h8,0)+isnull(h9,0)
+isnull(h10,0)+isnull(h11,0)+isnull(h12,0)+isnull(h13,0)+isnull(h14,0)
+isnull(h15,0)+isnull(h16,0)+isnull(h17,0)+isnull(h18,0)+isnull(h19,0)
+isnull(h20,0)+isnull(h21,0)+isnull(h22,0)+isnull(h23,0))/60 as data
from web_daydata a inner join web_pumpconfig b on b.scid=a.id
where date_day>=dbo.GetUpdateDate()
),
t3 as(
    select * from t1 union all select * from t2 
)
select e.Equipmentid,11 as PlantID,18 asDivisionID,t3.* from t3 inner join SZWG.dbo.Dim_Equipment e on 11=e.PlantID and 18=e.DivisionID and Name=e.EquipmentName

7.连表查询还可以这样来，不需要写那么多的inner join 

select * from table1 1,table2 2 .....

8.pivot和unpivot行转列和列转行

with t as(
SELECT P.Date_Month,P.Supplier,P.data
FROM 
(
   select Date_Month,sum(d1) as d1, sum(d2) as d2,sum(d3) as d3,sum(d4) as d4,sum(d5) as d5,sum(d6) as d6,sum(d7) as d7,sum(d8) as d8,sum(d9) as d9,sum(d10) as d10,sum(d11) as d11,sum(d12) as d12,sum(d13) as d13,sum(d14) as d14,sum(d15) as d15,sum(d16) as d16,sum(d17) as d17,sum(d18) as d18,sum(d19) as d19,sum(d20) as d20,
sum(d21) as d21,sum(d22) as d22,sum(d23) as d23,sum(d24) as d24,sum(d25) as d25,sum(d26) as d26,sum(d27) as d27,sum(d28) as d28,sum(d29) as d29,sum(d30) as d30,sum(d31) as d31 from Web_MonthData_FuTianWasteWaterTreatmentPlant  where ID in (40204,40218)  group by Date_Month

)T
UNPIVOT 
(
    data FOR Supplier IN
    (d1, d2,d3,d4,d5,d6,d7,d8,d9,d10,d11,d12,d13,d14,d15,d16,d17,d18,d19,d20,
    d21,d22,d23,d24,d25,d26,d27,d28,d29,d30,d31 )
) P
)
select replace(Date_Month+RIGHT(replace(supplier,'d','0'),2),'-','') as datekey,'福田水质净化厂' as plantname, 
'一期' as divisionname,data as ItemValue,'预处理区用电' as 电种类 from t

 9.筛选某列只要数字的(如果=1说明是数字类型，如果=0说明不是数字类型)

where dbo.isReallyNumeric(b.daydata)=1

10.SSIS抽数据的时候做增量，这个应该不属于sql的知识范畴，暂且写在这里吧

t1.ItemDate>=isnull((select ConfiguredValue from  dbo.[SSIS Configurations] where configurationfilter='updatedate'),'2008-01-01')

 11.sql表里的数据生成insert脚本

 12.取得一个表的所有列名，但是不包括XX (BENG_TB是表名)

select name from syscolumns where  id=object_id('BENG_TB') and name not in ('DATE','TIME')

　　需要注意的是 ，object_id里面必须写表名，你写数据库.dbo.表名都不行，所以这东西只能在该表存在的数据库中使用了

 13.SQL行列转换中的IN不能写select语句怎么办？动态SQL可以实现！

declare @sql nvarchar(4000)
select  @sql=isnull(@sql+',','')+quotename(Name) from syscolumns where  id=object_id('BENG_TB') and name not in ('DATE','TIME')
declare @sqlstr varchar(4000)
set @sqlstr='select a.*,b.NodeID,b.TagName,301 as FactoryID from
(
SELECT rtrim(convert(varchar,replace([Date],''/'',''-'')))+'' ''+ ltrim(convert(varchar,[Time])) [DateTime] ,P.SupplyNum,p.Supplier
FROM 
(
    SELECT top 1 *
     FROM [QY_DATA].[dbo].[BENG_TB]
)T
UNPIVOT 
(    
    SupplyNum FOR Supplier IN
    ('+@sql+')
) P) a inner join Realtime5.dbo.Web_Node b on a.Supplier=b.TagName
' 
exec(@sqlstr)

动态SQL，屌屌的~

 14.order by多个字段排序(因为他们是字符格式，所以我都转换了一下格式)

order by CONVERT(datetime,DATE,120) desc,CONVERT(datetime,TIME,120) desc

 15.多表联表更新

update 表1 set Name=a.TagDescription  from 表2 a where ID=a.NodeID

还可以这样

update a set
a.h0=b.h0,a.h1=b.h1,a.h2=b.h2,a.h3=b.h3,a.h4=b.h4,a.h5=b.h5,a.h6=b.h6,a.h7=b.h7,
a.h8=b.h8,a.h9=b.h9,a.h10=b.h10,a.h11=b.h11,a.h12=b.h12,a.h13=b.h13,a.h14=b.h14,
a.h15=b.h15,a.h16=b.h16,a.h17=b.h17,a.h18=b.h18,a.h19=b.h19,a.h20=b.h20,a.h21=b.h21,
a.h22=b.h22,a.h23=b.h23 from [IPMLBZ].[PMIS5].[dbo].Web_daydata a inner join [PMIS5].[dbo].Web_daydata b
on a.id = b.ID and a.ID= 23459 and a.date_day= b.date_day and a.Date_Day= '2018-08-27' 

但是这里需要注意一点，a.ID=这里，貌似不能用in (1,2,3)，不能使用in，只能用等于号。这个不清楚为什么。

 16.获取当前日期并判断是星期几

select   
case   datepart(dw,getdate()) 
when   1   then   '星期天 ' 
when   2   then   '星期一 ' 
when   3   then   '星期二 ' 
when   4   then   '星期三 ' 
when   5   then   '星期四 ' 
when   6   then   '星期五 ' 
when   7   then   '星期六 ' 
end

但是上面这个方法很麻烦，这里有一个更简单的方法来判断输入的日期是周几

select datename(weekday,'2018-06-01')

17.数据库有的时候，我新建的表打不出来？说不存在？

ctrl+shift+R  按这个键，就会刷新，就可以打出表名了，还不行的话你多按几下....

 

18.偷师学艺，数据库53错误

在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误。未找到或无法访问服务器。请验证实例名称是否正确并且 SQL Server 已配置为允许远程连接。 (provider: 命名管道提供程序, error: 40 - 无法打开到 SQL Server 的连接)
可以按照下面的步骤来做，就好了
打开Sql server 管理配置器或者在命令行输入：SQLServerManager10.msc

然后重启一个sql server的服务，重新打开SQL Server Management Studio，在服务器名称输入：(local)或者127.0.0.1，即可登录数据库了。

 

 

 19.根据in()里面的数据进行排序，也就是固定排序

我查询的时候in()了一些数字，但是查出来之后自动排序了。我不想排序，可以使用charindex()方法，如下：

select * from AnalysisData where ItemID in(
11002,114101,214011
)  
Order By charindex(','+RTRIM(cast(ItemID as varchar(10)))+ ',',',11002,114101,214011,')

这样就可以按照in里面的数字进行排序了

 

20.接着19讲，Order by这里我又理解了一下

Order by应该只能用在语句的最后，例如：

select '当前' h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝')
,* from [dbo].[WaterQuality] (@date1,@date2)
where winame in('pH值','浑浊度','臭和味','氨氮','游离余氯','总余氯','细菌总数','总大肠菌群','铝')

union 

select '同比' h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝')
,* from [dbo].[WaterQuality] (Convert(varchar(10),(Dateadd(YEAR,-1,@date1)),23),Convert(varchar(10),(Dateadd(YEAR,-1,@date2)),23))
where winame in('pH值','浑浊度','臭和味','氨氮','游离余氯','总余氯','细菌总数','总大肠菌群','铝') 

union

select '环比' h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝')
,* from [dbo].[WaterQuality] (Convert(varchar(10),(Dateadd(MONTH,-1,@date1)),23),Convert(varchar(10),(Dateadd(MONTH,-1,@date2)),23))
where winame in('pH值','浑浊度','臭和味','氨氮','游离余氯','总余氯','细菌总数','总大肠菌群','铝')
order by h,charindex(','+RTRIM(cast(winame as varchar(100)))+ ',',',pH值,浑浊度,臭和味,氨氮,游离余氯,总余氯,细菌总数,总大肠菌群,铝')

我原来三个语句，当前，同比，环比都写了Order by  但是就不能union了，  所以我觉得Order by是应该写在最后的。然后我排序的固定内容也写在了select后面才得以实行

 21.判断除数不能为0

Sql Server好像没有判断是否为0的函数，只有一个NULLIF可以用一下，这个是判断如果是0就设置为NULL，还可以用

1/NULLIF(列名,0)

22. 一个表的数据查询结果插入另外一个表

insert into VaeDB.dbo.A(Name)  select Name from B group by Name

23. Sql Server修改查询结果

 

使用编辑查询结果的方式，适合小数据量的时候，有时候不想写update语句

先在表上点击编辑前200行，然后选择显示SQL窗格，如图

这个时候直接输入sql语句，查询的结果可以在下面直接修改，少量数据还是很方便的

 

24.去除所有重复的数据,或者得到所有不重复的数据,使用的是group by和having的组合搭配,巧妙的是min函数,这样重复的数据我只拿第一个,这不就不重复了吗

网上全都是介绍distinct的,这个distinct不好用,只能看一列数据,没用而且效率很低

--这个是所有不重复的数据
select ReplaceProductName, min(ProductAlterId) as minProductAlterId from ProductAlter group by ReplaceProductName having COUNT(ReplaceProductName)>=1 order by minProductAlterId

--这个是所有重复的数据
select * from ProductAlter a inner join (
select ReplaceProductName, min(ProductAlterId) as minProductAlterId from ProductAlter group by ReplaceProductName having COUNT(ReplaceProductName)>1 ) as aa
on a.ReplaceProductName=aa.ReplaceProductName and a.ProductAlterId<>aa.minProductAlterId order by a.ProductAlterId

25. 去除字段中所有的空格

单纯的使用Ltrim或者Rtrim有时候是不行的,因为空格中存在换行符,回车等,所以要先替换掉这些特殊字符,再去除空格

LTRIM(RTRIM(REPLACE(REPLACE( 字段A,char(13),''),char(10),'') ))

26.传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

我在使用SUBSTRING方法的时候报了上面的错误,原因是因为这个字段得需要判断一下是否包含这个字符,例如

select SUBSTRING(PartNumber,0,CHARINDEX('-',PartNumber)-1) from dbo.Series

上面这个就是错误的,因为PartNumber字段里面可能有不包含 -  的数据,所以就报错了,正确的做法是再加一个判断,如下

select SUBSTRING(PartNumber,0,CHARINDEX('-',PartNumber)-1) from dbo.Series where CHARINDEX('-',PartNumber) > 0