SQL2008 学习总结

1.对于SQL查询语句，如果查询的字段都一样，尽量使用同一个sql，否则每一个查询语句占新的内存，这样可以提高性能。
2.事务：
SqlTransaction trans = null;
try
{
trans = conn.BeginTransaction();
trans.Commit();
}
catch (Exception ex)
{
    trans.Rollback();
    throw ex;
}

3模糊查询

    StringBuilder strSQL = new StringBuilder();
    strSQL.AppendFormat(@"SELECT  {0}

      [Code]
     ,ISNULL((select top 1[Name] from [dbo].[t_Country] where [Code] = a.[Country]) '') [CountryName]

          //ISNULL(a,b)意为a为空，则赋值为b,且isNull内得到的值不可为集合

      FROM  [dbo].[t_Customer] a where  1=1, ((topCounts > 0) ? "top " + topCounts.ToString() : "") ) ;

           if (!string.IsNullOrEmpty(buyerId))
            {
                strSQL.AppendFormat(@" and a.[BuyerId] like N'%' + @BuyerId +'%'");
            }

           if (!string.IsNullOrEmpty(type))
            {
                  strSQL.AppendLine(@" and a.[Type] in (" + type + @")");
            }

4.备份表
SELECT * INTO new_table_name  FROM old_tablename

备份列
SELECT column_name(s) INTO new_table_name [INexternaldatabase] FROM old_tablename

 

5.用临时表删除重复数据
select distinct * into #tmp from [t_Product]
delete from [t_Produc]
insert into [t_Product] select * from #tmp --从临时表将数据插回

6.删除约束
alter table [t_Shop] drop constraint  外键名

 

7.分页查询
declare @pagesize int,@pageIndex int;
set @pagesize=5
set @pageIndex=1
select  * from
(select ROW_NUMBER() over(order by ProductNo) as rowIndex ,ProductName from t_Product) as a
where a.rowIndex between @pagesize*(@pageIndex-1)+1 and @pagesize*@pageIndex

 8.关于Case When..then
select Product,
CASE [type]
WHEN  1 then 'a'
WHEN  2 then b'
WHEN 3 then c'
WHEN 4 then 'd'
WHEN 5 then 'e'
WHEN  6 then 'f' END AS [typeName]
from t_Product

如果列为null值，让其显示为0
(CASE isnull(c.varCount,0) when 0 then 0 else c.varCount end) as varCount

9.查询数据库中的所有表字段信息

SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
from syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties  g on a.id=g.major_id  and a.colid=g.minor_id    
left join sys.extended_properties f on d.id=f.major_id and f.minor_id  =0
--where
--    d.name like 't_EmailTicket%'    --如果只查询指定表,加上此条件
order by
    a.id,a.colorder

10.DateTime SmallDateTime
DateTime值一共需要8个字节，前四个年月日，后四个时分秒
DateTime支持的日期范围是 1753-1-1到9999-12-31

SmallDateTime值一共需要4个字节，两个年月日，两个时间
SmallDateTime支持的范围从1900-1-1到2079-6-6

11.检查表对象是否存在
select OBJECT_ID('t_Product')
返回为null即不存在

检查临时表是否存在需指定tempdb数据库前缀，否则SQL Server只在当前数据库中查找
select OBJECT_ID('tempdb..#a')

12.临时表和联合插入

create table #t_pro
(
id int not null,
name nvarchar(10),
quantity int,
)
insert into #t_pro(id,name,quantity)
select '1','a' ,1
union all select '2','b' ,2
union all select '3','c' ,3

 

13.把一张表的某些类列数据插到另一张表

 insert  into table1  (id,name)

select id name from table2

where name like '张三'

14.关于有中括号的字符串模糊查询：

根据传入的字符串去判断t_user表中是否有符合类似该名字的数据

Select * From t_user Where @name Like REPLACE(name,'[','[[]') 

注：如果名字中有中括号 [ 需要替换成”[[]“，否则 中括号 会转义

15解除数据库资源占用：
exec sp_who_lock
exec sp_who
kill 81   // sp_who的blk列

16.Convert函数

select convert(bit,'')  将empty转换为bit类型，得到结果为0
select convert(int,'111') 将111字符串转换为int类型

17.增加字段使其值为该表另一列

alter table Table1 add name1 as name

18.decimal四舍五入

select ROUND(748.588, 1)

select ROUND(748.588, 2)

select ROUND(748.588, 2,0) --四舍五入

select ROUND(748.588, 2,1)  未四舍五入

19.STUFF函数，多行数据某一字段连接成串
select names=(STUFF(
(select ';' +add_description from a_dddw_def for xml path('')),1,1,''))

20.游标

declare @oldSaleId nvarchar(20),@OldOrderno  nvarchar(20),@sheetseries  nvarchar(20)
declare @StockRowCnt int
begin
        declare mycursor cursor for
            select oldOrderNo,oldSaleId from #temp
            open mycursor
            fetch next from mycursor into
                 @oldOrderNo, @oldSaleId    
            while(@@fetch_status = 0)
            begin
                select @StockRowCnt=count(*) from ProdInDetail  where OldOrderNo=@oldOrderNo
                            
                if (@StockRowCnt>0)
                select top 1 @sheetseries=SheetSeries from ProdInDetail  where OldOrderNo=@oldOrderNo and SheetSeries in(select SheetSeries from ProdIn where isnull(OldSalerID,'')='')
                UPDATE ProdIn set OldSalerID=@oldSaleId where SheetSeries=@sheetseries and  isnull(OldSalerID,'')=''
        
                fetch next from mycursor into  @oldOrderNo, @oldSaleId                    
            end    
            close mycursor    
            deallocate mycursor
        end    

21.更新某个字段为其他字段组成的xml类型

update a set CD_Xml=(SELECT[CD_SKU] 'sku'
      ,[CD_Name] '备件名称'
      ,[CD_Type] '所属设备'
      ,[CD_Unit] '计量单位'
      ,[CD_Quantity] '现存数量'
      ,[CD_Price] '价格'
      ,[CD_Amount] '金额'
      ,[CD_Spec] '规格'
      ,[CD_Model] '型号'
  FROM [CK_CKData] as [Table] where a.CD_Id=[Table].CD_Id FOR XML AUTO,TYPE,Elements) from  CK_CKData a 

23.查询每月的数据量

select  convert(varchar(7), bi_publishTime, 120) 月份,count(bi_id)数量
from BuyingItem with(nolock)  

where charindex('测试',BI_Name)<1
group by convert(varchar(7), bi_publishTime, 120)
order by convert(varchar(7), bi_publishTime, 120)

查询结果：

月份 数量
2011-12 13
2012-03 8
2012-04 7
2012-05 5
2012-06 8