1.sql查看所有表名
select name from sysobjects where xtype='u' order by name
2.sql查看所有表名及行数
SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY a.name,b.rows DESC
3.新增一个字段，并设置默认值，默认值一直不能设置成功
alter table '表名' add '字段名' varchar(10) default ('2004')4.统计数据库里的表
create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20)) exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'" insert into inf_tbName select name,rows,reserved,data,index_size,unused,isdo='0' from #t drop table #t
5.修改字段默认值
alter table bsi_flightTake drop constraint DF__bsi_fligh__statu__681373AD alter table bsi_flightTake add constraint DF__bsi_fligh__statu__681373AD default(0) for status
6.新建一个一样的表
SELECT * into inf_domain FROM inf_domain20150914 WHERE 1=2
7.sql日期增加
select * from map_interfaceNotify where isPost='11' and ExecDate > DATEADD(day,-5,getdate())
8.sql日期相减
SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate 结果：1 （后面的减前面的）
9.sql修改字段中部分内容
update ibeflight.dbo.map_InterfaceNotify set notifyurl=replace(notifyurl,'https://ws.jzdata.com','https://ws2.jzdata.com') where SignupCode like'TRACEID_%'
10.修改sql字段类型
alter table bsi_Passbehaviortrace alter column Email varchar(500)
11.查询每个航班的第一条数据
select * from his_Passbehaviorlog where hid in (select shid from (select flightNo,max(hid) as shid from his_Passbehaviorlog where traceID=@p0 group by flightNo) as a) and flightstatus='计划'
12.sql参数化执行
Declare @sql nvarchar(2000),@date1 char(6),@inum int
set @date1=Left(Replace('2016-04-06','-',''),6)
set @sql='select @bflight=count(*) from his_actualflight_'+@date1+' where flightNO=''MU2964'' and citypair=''XMNDLC'''
Exec sp_executesql @sql,N'@bflight int output',@bflight=@inum output
if @inum>0
select 1
else
select 213.类型转换
select 'TRACEID_'+ convert(varchar,hid) from bsi_Passbehaviortrace
14.insert into select
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
15.随机查询
Order by newid()
16.group by加筛选条件
select flightno,count(1) as num from inf_actualflight group by flightno having count(flightno)>1
17.select from select
select max(a) from (select count(*) as a,count(distinct pid) as b,convert(varchar(10),createdate,120) dt from his_Passbehaviortrace where account='XXX' group by convert(varchar(10),createdate,120)) t
18.数据删除，包括id自增
truncate table Sys_Catalog
19.sql单引号匹配
select top 100 * from bsi_Passbehaviortrace --where policyno like'%''%' order by hid desc
20.2表关联更新
update a set a.depcity=b.airportname from hisIBEdata..tmp_flightPNR as a left join ibeflight..knb_Airport3 as b on a.citypair=b.airportcode
21.sql匹配特殊符号[]
select * from ibeflight..knb_Airports2 where airportcode like'%[%]%'
22.替换空值
case when (FlightStatus is null or FlightStatus='') then '9999999' else FlightStatus end
23.字母大小写转换
LOWER() UPPER()
