sql 使用整理

水星曙光發表於2013-06-18
SQL

今天使用檢視查詢東西，為了方便直接select * 查出來的都行全部都錯亂了，看來sql 超過20個以上的欄位為了效率和安全，禁止用select * 

-------------查一個表的所有欄位的----------------------

select 'b.' + name + ',' from syscolumns where id = object_id('T_Retire_Bill')

-------------查兩個表有沒有重複的----------------------

select name from syscolumns a
where id = object_id('V_Retire_All')
and not exists(
select * from syscolumns 
where id = object_id('T_Retire_Bill')
and name = a.name
)

 --------------------------------

case when 動態增加where 條件

僅支援08及以上版本

where 1= 1 
and

(
　　case 
　　　　when ( @state is null or @state = '') then 1
　　　　when StPending.StatusFlag = @state then 1 else 0
　　end
)=1

 

create proc usp_search
@city int
as
begin
    declare @sql varchar(8000)
    set @sql = N'select * from TestTable where 1=1 '
    if(@city <> -1)
        set @sql = @sql + ' and cityId = @city '
    exec sp_execute_sql @sql, N'@city int', @city
end
go


create proc usp_search
@city int
as
begin
    select * from TestTable where 1=1 and (@city = -1 or cityId = @city)
end
go


 

ALTER PROC [dbo].[ehai_SalesClueBlackList]
 @CellPhone  VARCHAR(11),
 @CompanyName VARCHAR(50),
 @IsUsed CHAR(1),
 @Page INT ,
 @Rows INT ,
 @Sort VARCHAR(50) ,
 @Order VARCHAR(20) ,
 @RowCount INT OUT 
AS
BEGIN
SELECT @RowCount=COUNT(*) FROM SalesClueBlackList
SELECT  * FROM 
(
    SELECT ROW_NUMBER() OVER ( ORDER BY CellPhone DESC ) rowNum,
    s.ID,s.CellPhone,s.CompanyName,s.IsUsed 
    FROM SalesClueBlackList S
    WHERE 1 = 1
    AND (CellPhone=@CellPhone OR @CellPhone='')    
    AND (CompanyName LIKE '%'+@CompanyName+ '%' OR @CompanyName='')
    AND (IsUsed=@IsUsed OR @IsUsed='')
) as Salse
WHERE Salse.rowNum BETWEEN ( @Page - 1 ) * @Rows + 1 AND @Rows * @Page
END

 

-------------------------------------

SQL中取

當月天數
select day(dateadd(ms,-3,DATEADD(m, DATEDIFF(m,0,getdate())+1,0))) 
當月第一天
select   dateadd(d,-day(getdate())+1,getdate())  
當月最後一天
select   dateadd(d,-day(getdate()),dateadd(m,1,getdate()))
本年最後一天
select   dateadd(d,-day(getdate()),dateadd(m,12,getdate()))
當月第一個星期一
SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '')

----------------------------------------------------

SQL跨庫查詢。

 

select * from OPENDATASOURCE( 
'SQLOLEDB', 
'Data Source=遠端ip;User ID=sa;Password=密碼' 
).庫名.dbo.表名 

insert 本地庫名..表名 select * from OPENDATASOURCE( 
'SQLOLEDB', 
'Data Source=遠端ip;User ID=sa;Password=密碼' 
).庫名.dbo.表名 

或使用聯結伺服器: 
--建立linkServer 
exec sp_addlinkedserver '別名','','SQLOLEDB','192.168.2.5' 

--登陸linkServer 
exec sp_addlinkedsrvlogin '別名','false',null,'sa','1234' 

--查詢 
select * from 別名.庫名.dbo.表名 

--以後不再使用時刪除連結伺服器 
exec sp_dropserver '別名','droplogins'

---------------------------------------------------------------- 

索引操作

建立非聚集索引：
CREATE INDEX 索引名  ON 表名(欄位名);

建立聚集索引：

CREATE UNIQUE CLUSTERED INDEX 索引名 ON 表名(欄位名);

 

查詢索引

查詢 select index_col('表名',1,索引次序)

-- 檢視某個表的索引
SELECT * FROM sys.sysindexes
WHERE id=object_id('RelactionGraph')
 
-- 檢視整個庫的索引
SELECT * FROM sys.sysindexes
 
-- 檢視所有庫的索引
IF object_id('tempdb..#')IS NOT NULL
    DROP TABLE #
SELECT * INTO # FROM sys.sysindexes WHERE 1=2
 
INSERT INTO #
    EXEC sys.sp_MSforeachdb @command1='Select * from ?.sys.sysindexes'
SELECT * FROM #
 
還有一個更好的方法：EXEC sp_helpindex '表名'
------------------------------------------------------
 
檢視LINQ生成的SQL語句
var query = from p in data.SelfAcctStatementDetails where p.AcctID == "Lingzhi" select p;
query = query.Where(o => o.AcctName == "ssss").Where(o=>o.Amount > 20);
System.Data.Objects.ObjectQuery<SelfAcctStatementDetail> parents = query as System.Data.Objects.ObjectQuery<SelfAcctStatementDetail>; 
if (parents != null) { string sql = parents.ToTraceString(); }

 ---------------------------------------------------------

字元分隔

create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--實現split功能 的函式
as 
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return 
end

-----------------------------------------------------------------------

查詢結果集，拼接字串

declare @sql as varchar(8000)
select @sql=''
Select @sql=@sql+【欄位名】
from 【表名】
select @sql


例1：
declare @a varchar(8000)
select @a=isnull(@a+',','')+ltrim(id) from SelfAcctOrder order by id
select @a

例2：
select stuff((select ','+[name] from tb for xml path('')),1,1,'')

 

 

 

相關文章