SQL Server的使用笔记
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 =object_id('T_Operator') and ='OperatorID'
九、数据库中对象是否存在
1.表中的列
select count(1) from syscolumns where =object_id('T_Operator') and ='OperatorID'
2.表
select count(*) from sysobjects
where id = object_id(N'') 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
页:
[1]