常用SQL~~!
--1.根据一个表的数据修改另一表的结束时间
2.获取需要的日期格式
select convert(char(10),getDate(),112) --yyyymmdd
select convert(char(10),getDate(),111) --yyyy/mm/dd
select convert(char(10),getDate(),105) --dd-mm-yyyy
3.统计
4.--查询必填字段为空的
5.添加字段/修改字段类型
if col_length('tblcmbase_Server', 'Mn_BarCode') is null
alter table tblcmbase_Server
add BarCode nvarchar(50)
GO
--修改字段类型
alter table tblcmbase_Server
alter column ServerHeight int
go
6.常用SQL
insert into ##tablename select * from tablename2
--删除表前备份表
select * into temp from tblstmTaskList
--列出数据库里所有的表名
select name from sysobjects where type='U'
--列出表里面的所有字段
select name from syscolumns where id=object_id('tblstmTaskList')
-选择从10到15的记录
select top 5 * from (select top 15 * from tblstmTaskList order by id asc) a order by id desc
--修改描述字段中的错别字
update _format_pendingmsg set description = replace(description,'相关连接','相关链接')
7.修改
update
t
set
t.Ct_dpid = i.ci_id
from
ContractInfo_temp t,
clientinfo i
where
t.Ct_dpid=i.Ci_ShortName
go
8.统计SQL
2 select typeDef2 . item, isnull(temp. Count, 0) as Count
3 from tblmtIncidentTypeDef as typeDef2
4 left join (select typeDef . Item, count(1) as Count
5 from tblmtIncidentList as list,
6 tblmtIncidentTypeDef as typeDef
7 where typeDef . ID = list . IncidentTypeID
8 and list . CreateTime between '2009-03-01' and '2009-03-31'
9 group by typeDef . Item) as temp on typeDef2 . item = temp. item
10 order by typeDef2 . OrderNo
11
12
13
14 --SQL2
15 Select BranchCode,
16 (select Count(1)
17 from tblmtIncidentList as list
18 where list . RelateBranchCode = Brd . BranchCode
19 and list . CreateTime between '2009-03-01' and '2009-03-31'
20 and (select count(1)
21 from tblmtIncidentFlowList
22 where FlowID = list.ID) = 0) as NotRepeat,
23 (select Count(1)
24 from tblmtIncidentList as list
25 where list . RelateBranchCode = Brd . BranchCode
26 and list . CreateTime between '2009-03-01' and '2009-03-31'
27 and list.currstatus = 'X') as Fini,
28 (select Count(1)
29 from tblmtIncidentList as list
30 where list . RelateBranchCode = Brd .
31 BranchCode
32 and list . CreateTime between '2009-03-01' and '2009-03-31'
33 and list.currstatus <> 'X'
34 and ExpectSolveDate <= getdate()) as OT
35 from tblsysBranchInfo as Brd
36 where Brd . BranchCode in ('SGS', 'GZ', 'SZ', 'DG', 'FS', 'ST', 'ZH', 'HZ', 'ZS', 'JM', 'SG', 'HY', 'MZ', 'SW', 'YJ', 'ZJ', 'MM', 'ZQ', 'QY', 'CZ', 'JY', 'YF','SZQ')
9.连接字符串
Set @str = ''
Select @str = @str + cast(Number as Varchar(50)) + ',' from ClassNo Where Class = @Class
Set @str = SubString(@str,1,len(@str)-1)
10.如何取得一个数据表的所有列名
方法如下:先从sysobjects系统表中取得数据表的systemid,然后再syscolumns表中取得该数据表的所有列名。
SQL语句如下:
Declare @objid int,@objname char(40)
set @objname = 'ColumnToRow'
--第1种方法
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
--或也可以写成
select name as 'Column_name' from syscolumns where id = @objid order by colid
--第2种方法:
Select name as 'Column_Name' from SysColumns where id = object_id(@objname) Order by colid
11.怎么判断出一个表的哪些字段不允许为空
Select Column_Name from information_schema.Columns where is_nullable = 'No' and Table_Name = @objname
12.如何在数据库里找到含有相同字段的表?
Select a.name as Columnname,b.name as tablename from SysColumns a inner join sysobjects b on a.id = b.id
and b.type = 'U' and a.name = '您要查找的字段名'
b. 未知列名查所有在不同表出现过的列名
Select s.name as tablename,s1.name as columnname from SysColumns s1,Sysobjects s
Where s1.id = s.id and s.Type = 'U' and Exists (Select 1 from syscolumns s2 where s1.name = s2.name and s1.id <> s2.id)
13.SQL Server日期计算
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))