SQL小语句集锦

一、SQL 禁用、开启表的所有约束

--禁用所有约束
exec sp_msforeachtable "alter table ? nocheck CONSTRAINT all"
--再启用所有外键约束
exec sp_msforeachtable "alter table ? check constraint all"

 

二、获取一个表的行/列

select count(*) from tbTestWellBasicInfo

select count(a.name) as count from syscolumns a where a.id=object_id('tbTestWellBasicInfo') 

 

三、获取某一个表中某一列的最大/最小值 

select max(wXCoordinate) from tbTestWellBasicInfo

 

四、textbox里不断输入，下面的datagridview里的数据不断变化

select * from tb_Book where bkID like '%'+@bkID+'%'

 

五、根据条件显示井，四个comboBox输入任意多个，进行查询并显示：

  string sql = "select * from tbTestWellBasicInfo where wEndDate='" + wEndDate + "' and wClass like '%'+'"+wClass+"'+'%' and wType like'%'+'"+wType+"'+'%' and wTestStatus like '%'+'"+wTestStatus+"'+'%'";

 

六、删除表数据

delete  from dbo.tbFiles

 

七、选择字段，分组，并统计每组的记录数

select WHID,count(*) from dbo.biz_OldMaterialInfo group by WHID

 

八、在A表中的字段b中而不在C表的字段d中

select distinct b from A except select distinct d from C

 

九、查询字符串中某个字符的个数

例：查询字符串‘05011045’中‘0’有几个

select len('05011045')-len(replace('05011045','0',''))

 

十、时间格式化

SELECT CONVERT(NVARCHAR(4),GETDATE(),120)

 

十一、分页查询函数的拼成的语句

select cnt.totalcnt,basetbl.* from (select row_number() over(order by MRID) as rowindex, * from mr_Plan where  (DepartmentId=9)) as basetbl,(select count(1) as totalcnt from mr_Plan where  (DepartmentId=9)) as cnt