SQL Server的使用笔记

woyoudn

SQL Server的使用笔记

一、比较复杂的update语句：update .. from 

　　update T_BuyMaterialReceiveStoreDetail
　　set QCID = a.QCID,
　　QCQty = isnull(a.QCQty,a.Qty),
　　QCPricePercent = isnull(a.QCPricePercent,1),
　　QCFailedQty = isnull(a.QCFailedQty,0),
　　QCWasteQty = isnull(a.QCWasteQty,0),
　　QCShortQty = isnull(a.QCShortQty,0),
　　QCPercent = isnull(a.QCPercent,1),
　　Note = a.Note,
　　DeterMineResult=a.DeterMineResult
　　from (select * from T_Temp_BuyMaterialReceiveStoreDetail
　　where OperateID = @OperateID
　　and OperateTime = @OperateTime
　　and QCID = QCID) a
　　where T_BuyMaterialReceiveStoreDetail.BuyID = a.BuyID
　　and T_BuyMaterialReceiveStoreDetail.MaterialItemID = a.MaterialItemID
　　and T_BuyMaterialReceiveStoreDetail.ReceiveID = a.ReceiveID
　　and T_BuyMaterialReceiveStoreDetail.StoreID = a.StoreID
　　and T_BuyMaterialReceiveStoreDetail.MaterialColorID = a.MaterialColorID
　　and T_BuyMaterialReceiveStoreDetail.MaterialSizeID = a.MaterialSizeID
　　and T_BuyMaterialReceiveStoreDetail.SaleID = a.SaleID
　　and T_BuyMaterialReceiveStoreDetail.StyleID = a.StyleID
　　
　　二、判断varchar型的数据是否空
　　len(Note)= 0 
　　
　　三、将数据库的表的表结构的ntext类型的字段改为nvarchar(max)
select 'alter table ' +  c.name+' alter column '+a.name+' nvarchar(max)' 
from sys.columns a
left join sys.types b on a.system_type_id = b.system_type_id and b.name='ntext'
left join sys.objects c on a.object_id = c.object_id and c.Type='U'
where b.name='ntext' and c.Type='U'
　　四、巧妙的将返回的所有行的记录以一行字符串的形式输出
 
DECLARE @STR VARCHAR(8000)
SELECT @STR = ISNULL(@STR,'')+salecode+char(13) FROM t_sale 
SELECT @STR 
五、处理bit的类型的数据不能max
max(cast(a.State as int))
六、对调数据库中的两行记录的某个字段的值
update T_ReportConfig
set Array = case
when ReportConfigID = 17 then (select Array from T_ReportConfig where  ReportConfigID=18)
else (select Array from T_ReportConfig where  ReportConfigID=17)
end
where ReportConfigID in (17,18)



个人解释：sql编译时，先计算了()中的值，所以巧妙的达到了交互两个值


七、delete exists
delete from A 
where exists(select * 
from B a
where A.Column = a.Column)
八、OBJECT_ID (Transact-SQL)
Returns the database object identification number of a schema-scoped object. 对象标识号
语法：
OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] 
  object_name' [ ,'object_type' ] )
例子：
select count(1) from syscolumns where [id]=object_id('T_Operator') and [name]='OperatorID'
九、数据库中对象是否存在
1.表中的列
select count(1) from syscolumns where [id]=object_id('T_Operator') and [name]='OperatorID'
2.表

select count(*) from sysobjects 
where id = object_id(N'[T_Operator]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
十、SqlServer字符串前加N

加上 N 代表存入数据库时以 Unicode 格式存储。
N'string' 表示string是个Unicode字符串


Unicode 字符串的格式与普通字符串相似，但它前面有一个 N 标识符（N 代表 SQL-92 标准中的国际语言 (National Language)）。
Unicode 常量确实有排序规则，主要用于控制比较和区分大小写
十一、OBJECTPROPERTY
http://msdn.microsoft.com/en-us/library/aa276849(v=sql.80).aspx
Returns information about objects in the current database