今天使用视图查询东西,为了方便直接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
)=1create 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
gocreate 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','' --查询
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=2INSERT 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 > );
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,'')