SQL SERVER常用语法汇总

阅读目录

• 一、SQL分类
• 二、基础语句
• 三、sql技巧
• 四、（MS SQL Server）SQL语句导入导出大全

create database db-name

drop database db-name 

--- 创建 备份
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

drop table tabname

Alter table tabname add column col type

Alter table tabname add primary key(col)
Alter table tabname drop primary key(col)

create [unique] index idxname on tabname(col….)
drop index idxname

create view viewname as select statement
drop view viewname

查询：select * from table1 where 范围
插入：insert into table1(field1,field2) s(1,2)
删除：delete from table1 where 范围
更新：update table1 set field1=1 where 范围
查找：select * from table1 where field1 like ’%1%’ ---like的语法
排序：select * from table1 order by field1,field2 [desc]
总数：select count * as totalcount from table1
求和：select sum(field1) as sum from table1
平均：select avg(field1) as avg from table1
最大：select max(field1) as max from table1
最小：select min(field1) as min from table1

法一：select * into b from a where 1<>1法二：select top 0 * into b from a

insert into b(a, b, c) select d,e,f from b; 

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3) 

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 

select * from (SELECT a,b,c FROM a) T where t.a > 1; 

select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’) 

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 

SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 

select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

select top 10 * form table1 where 范围 

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) 

(select a from tableA ) except (select a from tableB) except (select a from tableC) 

select top 10 * from tablename order by newid() 

select newid()

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 

select name from sysobjects where type='U'

select name from syscolumns where id=object_id('TableName') 

select type,
sum(case vender when 'A' then pcs else 0 end),
sum(case vender when 'C' then pcs else 0 end),
sum(case vender when 'B' then pcs else 0 end)
FROM tablename group by type

type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3

TRUNCATE TABLE table1 

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

create table a_dist(id int,name varchar(20))insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')exec up_distinct 'a_dist','id'select * from a_dist

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))insert into hard values ('A','1',3)
insert into hard values ('A','2',4)
insert into hard values ('A','4',2)
insert into hard values ('A','6',9)
insert into hard values ('B','1',4)
insert into hard values ('B','2',5)
insert into hard values ('B','3',6)
insert into hard values ('C','3',4)
insert into hard values ('C','6',7)
insert into hard values ('C','2',3)

qu co je
----------- ----------- -----
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4就是要按qu分组，每组中取je最大的前2位,且只能用一句sql语句。

select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)

1）将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>12）将不重复的记录记入temp1表:
insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=13）作一个包含所有不重复记录的表：
select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)4）删除重复表:
delete [表名]5）恢复表：
insert [表名] select * from temp26）删除临时表:
drop table temp1
drop table temp2

假设有张学生成绩表(CJ)如下Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82想变成姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82

declare @sql varchar(4000)
set @sql = 'select Name'select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as aselect @sql = @sql+' from test group by name'exec(@sql)

有表A,id pid
1 1
1 2
1 3
2 1
2 2
3 1如何化成表B:id pid
1 1,2,3
2 1,2
3 1

create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)return(@str)
End
go

select distinct id,dbo.fmerg(id) from 表A

declare @objid int,@objname char(40)
set @objname = 'tablename'select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'

修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'如果帐号为SA执行
EXEC sp_password NULL, 'newpassword', sa

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='字段名'

Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)

select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id) 

fetch absolute [number] from [cursor_name]

a. 一个月的第一天
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))。

SELECT CASE IsNull(I.name, '')
When '' Then ''
Else '*'
End as IsPK,
Object_Name(A.id) as t_name,
A.name as c_name,
IsNull(SubString(M.text, 1, 254), '') as pbc_init,
T.name as F_DataType,
CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
WHEN '' Then Cast(A.prec as varchar)
ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
ORDER BY A.Colid ASC

SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder

select rows from sysindexes where id = object_id('test') and indid in (0,1)update 2 set KHXH=(ID+1)\2 2行递增编号
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
delete from [1] where (id%2)=1奇数
替换表名字段
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'截位
SELECT LEFT(表名, 5)截位
SELECT LEFT(表名, 5)

EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactionsSELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactionsselect * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\Book1.xls',Sheet1$)HDR=YES;Excel第一行当成标题行
HDR=NO;第一行不当成标题行

EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword' 

EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)

--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
--*/--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
--*/--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*/

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go

insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')select * from 表

insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表

insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)

exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',1

exec file2table 'zj','','','xzkh_sa','C:\docman',1 

exec file2table 'zj','','','xzkh_sa..地区资料','c:\zj.txt',0

exec file2table 'zj','','','xzkh_sa','C:\docman',0 

insert into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=C:\'
)...[aa#txt]
--,aa#txt)
--*/select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
--,Sheet1$)
)...[Sheet1$]

declare @sql varchar(8000),@tbname varchar(50) 

select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into '+@tbname+' from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
)...[Sheet1$]'exec(@sql)

set @sql='bcp "'+@tbname+'" out "c:\aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql

exec('drop table '+@tbname) 

exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:\zj1.dat' 

exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:\zj1.dat','',0

set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
set @sql='select * into '+@m_tbname+' from(
select null as 类型
union all select 0 as 前缀
union all select '+@fsize+' as 长度
union all select null as 结束
union all select null as 格式
) a'exec(@sql)select @fname_in=@fname+'_temp'
,@sql='bcp "'+@m_tbname+'" out "'+@fname_in
+'" /S"'+@servename
+case when isnull(@username,'')='' then ''
else '" /U"'+@username end
+'" /P"'+isnull(@password,'')+'" /c'exec master..xp_cmdshell @sql

set @sql='drop table '+@m_tbname
exec(@sql)if @isout=1
begin
set @sql='bcp "select top 1 '+@fdname+' from '
+@tbname+case isnull(@tj,'') when '' then ''
else ' where '+@tj end
+'" queryout "'+@fname
+'" /S"'+@servename
+case when isnull(@username,'')='' then ''
else '" /U"'+@username end
+'" /P"'+isnull(@password,'')
+'" /i"'+@fname_in+'"'exec master..xp_cmdshell @sql
end
else
begin

set @sql='select top 0 '+@fdname+' into '
+@m_tbname+' from ' +@tbnameexec(@sql)

set @sql='bcp "'+@m_tbname+'" in "'+@fname
+'" /S"'+@servename
+case when isnull(@username,'')='' then ''
else '" /U"'+@username end
+'" /P"'+isnull(@password,'')
+'" /i"'+@fname_in+'"'exec master..xp_cmdshell @sql

set @sql='update '+@tbname
+' set '+@fdname+'=b.'+@fdname
+' from '+@tbname+' a,'
+@m_tbname+' b'
+case isnull(@tj,'') when '' then ''
else ' where '+@tj endexec(@sql)

set @sql='drop table '+@m_tbname
end

set @sql='del '+@fname_inexec master..xp_cmdshell @sql
go

EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'改为如下，不需引号EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'

EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'此句需加引号