【网学网提醒】：网学会员,鉴于大家对sql2000技巧集十分关注，会员在此为大家搜集整理了“sql2000技巧集”一文，供大家参考学习!
sqlserver2000语句导出数据到EXCEL
*===================导入/导出Excel的基本方法===================*/
从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
/*========================================================*/
EXECmaster..xp_cmdshell'bcp"execweberp.dbo.DailyCash_Excel@PayAccount=null,@StartDate=null,@EndDate=null,@TrackStation=null"queryout"C:\authors.xls"-c-S"(local)"-U"sa"-P"password"'
--如果接受数据导入的表已经存在
insertinto表select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
--如果导入数据并生成表
select*into表from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
/*========================================================*/
--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insertintoOPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select*from表
--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXECmaster..XP_cmdshell'bcp数据库名.dbo.表名out"c:test.xls"/c-/S"服务器名"/U"用户名"-P"密码"'
--导出查询的情况
EXECmaster..xp_cmdshell'bcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c:\test.xls"/c-/S"服务器名"/U"用户名"-P"密码"'
/*--说明:
c:test.xls为导入/导出的Excel文件名.
sheet1$为Excel文件的工作表名,一般要加上$才能正常使用.
--*/
/*--数据导出EXCEL
导出表中的数据到Excel,如果文件不存在,将自动创建文件,如果表不存在,将自动创建表
调用示例
ExportToExcel@tbname='test',@path='c:\',@fname='test.xls'
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[ExportToExcel]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[ExportToExcel]
GO
createprocExportToExcel
@Pac,--要导出的表名,注意只能是表名/视图名
@pathnvarchar(1000),--文件存放目录
@fnamenvarchar(250)=''--文件名,默认为表名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)
--参数检测
ifisnull(@fname,'')=''set@fname=@tbname+'.xls'
--检查文件是否已经存在
ifright(@path,1)<>'\'set@path=@path+'\'
createtable#tb(abit,bbit,cbit)
set@sql=@path+@fname
insertinto#tbexecmaster..xp_fileexist@sql
--数据库创建语句
set@sql=@path+@fname
ifexists(select1from#tbwherea=1)
set@constr='DRIVER={MicrosoftExcelDriver(*.xls)};DSN=TT;READONLY=FALSE'
+';CREATE_DB='+@s
ql+';DBQ='+@sql
else
set@constr='Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel8.0;HDR=YES'
+';DATABASE='+@sql+'"'
--连接数据库
print@constr
exec@err=sp_oacreate'adodb.connection',@objout
if@err<>0gotolberr
exec@err=sp_oamethod@obj,'open',null,@constr
if@err<>0gotolberr
--创建表的SQL
select@sql='',@fdlist=''
select@fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+']'
+casewhenb.namein('char','nchar','varchar','nvarchar')then
'text('+cast(casewhena.length>255then255elsea.lengthendasvarchar)+')'
whenb.namein('tynyint','int','bigint','tinyint')then'int'
whenb.namein('smalldatetime','datetime')then'datetime'
whenb.namein('money','smallmoney')then'money'
elseb.nameend
FROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertype
whereb.namenotin('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
andobject_id(@tbname)=id
select@sql='createtable['+@tbname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
print@sql
exec@err=sp_oamethod@obj,'execute',@outout,@sql
if@err<>0gotolberr
exec@err=sp_oadestroy@obj
--导入数据
set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@tbname+'$])'
exec('insertinto'+@sql+'('+@fdlist+')select'+@fdlist+'from'+@tbname)
return
lberr:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
select@sql,@constr,@fdlist
go
CREATEPROCSqlToExcel
(
@Pathvarchar(100),--文件存放路径
@Fnamevarchar(100),--文件名字
@SheetNamevarchar(80),---工作表名字
@SqlStrvarchar(8000)--查询语句,如果查询语句中使用了orderby,请加上top100percent,注意,如果导出表/视图,用上面的存储过程
)
AS
SETNOCOUNTON
declare@sqlvarchar(8000)
declare@objint--OLE对象
declare@constrvarchar(8000)
declare@errint
declare@outint
declare@fdlistvarchar(8000)
declare@tbnamesysname--临时表
declare@Srcnvarchar(200)
declare@Descnvarchar(200)
set@tbname='##tmp_'+convert(varchar(38),newid())
exec('select*into['+@tbname+']from'+'('+@sqlStr+')A')
select@fdlist=''
set@sql=@path+@fname
set@constr='DRIVER={MicrosoftExcelDriver(*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";
DBQ='+@sql
--生成Excel的列
set@sql=''
select@sql=@sql+','+'['+a.name+']'+casewhenb.namelike'%char'thencasewhena.length>255then'memo'else'text('+cast(a.lengthasvarchar)+')'end
whenb.namelike'%int'orb.name='bit'then'int'
whenb.namelike'%datetime'then'datetime'
whenb.namelike'%money'then'money'
whenb.namelike'%text'then'memo'
elseb.name
end,
@fdlist=@fdlist+','+'['+a.name+']'
fromtempdb..syscolumnsajointempdb..systypesbona.xtype=b.xusertype
whereb.namenotin('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
andidin(selectidfromtempdb..sysobjectswherename=@tbname)orderbycolorder
if@@rowcount=0return
set@fdlist=substring(@fdlist,2,8000)
--连接数据库
exec@err=sp_oacreate'adodb.connection',@objout
if@err<>0gotolberror
exec@err=sp_oamethod@obj,'open',null,@constr
if@err<>0gotolberror
--创建工作薄
select@sql='createtable['+@sheetname
+']('+substring(@sql,2,8000)+')'
exec@err=sp_oamethod@obj,'execute',@outout,@sql--@sql为excute方法提供参数
if@err<>0gotolberror
exec@err=sp_oadestroy@obj
--导入数据
set@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel8.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
--print@sql
exec('insertinto'+@sql+'('+@fdlist+')select'+@fdlist+'from['+@tbname+']')
exec('droptable['+@tbname+']')
return
lberror:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as错误号
,@srcas错误源,@descas错误描述
select@sql,@constr,@fdlist
**********************************************************************
*****************************************************************
************************************************************
Sql常用语法
下列语句部分是Mssql语句，不可以在access中使用。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)首先,简要介绍基础语句：
1、说明：创建数据库
CREATEDATABASEdatabase-name
2、说明：删除数据库
dropdatabasedbname
3、说明：备
份sqlserver
---创建备份数据的device
USEmaster
EXECsp_addumpdevice'disk','testBack','c:mssql7backupMyNwind_1.dat'
---开始备份
BACKUPDATABASEpubsTOtestBack
4、说明：创建新表
createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)根据已有的表创建新表：
A：createtabletab_newliketab_old(使用旧表创建新表)
B：createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
5、说明：删除新表droptabletabname
6、说明：增加一个列
Altertabletabnameaddcolumncoltype注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键：Altertabletabnameaddprimarykey(col)说明：删除主键：Altertabletabnamedropprimarykey(col)
8、说明：创建索引：create[unique]indexidxnameontabname(col….)删除索引：dropindexidxname注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：createviewviewnameasselectstatement删除视图：dropviewviewname
10、说明：几个简单的基本的sql语句选择：select*fromtable1where范围插入：insertintotable1(field1,field2)s(1,2)删除：deletefromtable1where范围更新：updatetable1setfield1=1where范围查找：select*fromtable1wherefield1like’%1%’---like的语法很精妙，查资料!排序：select*fromtable1orderbyfield1,field2[desc]总数：selectcount*astotalcountfromtable1求和：selectsum(field1)assumfromtable1平均：selectavg(field1)asavgfromtable1最大：selectmax(field1)asmaxfromtable1最小：selectmin(field1)asminfromtable1
11、说明：几个高级查询运算词
A：UNION运算符
UNION运算符通过组合其他两个结果表（例如TABLE1和TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时（即UNIONALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。
B：EXCEPT运算符
EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。当ALL随EXCEPT一起使用时(EXCEPTALL)，不消除重复行。
C：INTERSECT运算符
INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。当ALL随INTERSECT一起使用时(INTERSECTALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。
12、说明：使用外连接
A、leftouterjoin：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
B：righto
uterjoin:右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：fullouterjoin：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
不错的sql语句
1、说明：复制表(只复制结构,源表名：a新表名：b)(Access可用)法一：select*intobfromawhere1<>1法二：selecttop0*intobfroma
2、说明：拷贝表(拷贝数据,源表名：a目标表名：b)(Access可用)
insertintob(a,b,c)selectd,e,ffromb;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)(Access可用)
insertintob(a,b,c)selectd,e,ffrombin‘具体数据库’where条件例子：..frombin'"&;Server.MapPath(".")&;"data.mdb"&;"'where..
4、说明：子查询(表名1：a表名2：b)
selecta,b,cfromawhereaIN(selectdfromb)或者:selecta,b,cfromawhereaIN(1,2,3)
5、说明：显示文章、提交人和最后回复时间
selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
6、说明：外连接查询(表名1：a表名2：b)
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
7、说明：在线视图查询(表名1：a)
select*from(SELECTa,b,cFROMa)Twheret.a>1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,notbetween不包括
select*fromtable1wheretimebetweentime1andtime2
selecta,b,c,fromtable1whereanotbetween数值1and数值2
9、说明：in的使用方法
select*fromtable1wherea[not]in(‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1)
11、说明：四表联查问题：
select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
12、说明：日程安排提前五分钟提醒
SQL:select*from日程安排wheredatediff('minute',f开始时间,getdate())>5
13、说明：一条sql语句搞定数据库分页
selecttop10b.*from(selecttop20主键字段,排序字段from表名orderby排序字段desc)a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段
14、说明：前10条记录
selecttop10*formtable1where范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b)
16、说明：包括所有在TableA中但不在TableB和TableC中的行并消除所有
重复行而派生出一个结果表
(selectafromtableA)except(selectafromtableB)except(selectafromtableC)
17、说明：随机取出10条数据
selecttop10*fromtablenameorderbynewid()
18、说明：随机选择记录
selectnewid()
19、说明：删除重复记录
Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2,...)
20、说明：列出数据库里所有的表名
selectnamefromsysobjectswheretype='U'
21、说明：列出表里的所有的
selectnamefromsyscolumnswhereid=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select中的case。
selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablenamegroupbytype显示结果：
typevenderpcs电脑A1电脑A1光盘B2光盘A2手机B3手机C3
23、说明：初始化表table1
TRUNCATETABLEtable1
24、说明：选择从10到15的记录
selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc
sql技巧
如何删除一个表中重复的记录？
createtablea_dist(idint,namevarchar(20))
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
execup_distinct'a_dist','id'
select*froma_dist
createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger
select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'
exec(@sql)
opencur_rows
fetchcur_rowsinto@id,@max
while@@fetch_status=0
begin
select@max=@max-1
setrowcount@max
select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key
if@type=56
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
if@type=167
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
exec(@sql)
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows
setrowcount0
end
select*fromsystypes
select*fromsyscolumnswhereid=object_id('a_dist')
查询数据的最大排序问题（只能用一条语句写）
CREATETABLEhard(quchar(11),cochar(11),jenumeric(3,0))
insertintohardvalues('A','1',3)
insertintohardvalues('A','2',4)
insertintohardvalues('A','4',2)
insertintohardvalues('A','6',9)
insertintohardvalues('B','1',4)
insertintohardvalues('B','2',5)
insertintohardvalues('B','3',6)
ins
ertintohardvalues('C','3',4)
insertintohardvalues('C','6',7)
insertintohardvalues('C','2',3)
要求查询出来的结果如下：
qucoje
---------------------------
A69
A24
B36
B25
C67
C34
就是要按qu分组，每组中取je最大的前2位！！
而且只能用一句sql语句！！！
select*fromhardawherejein(selecttop2jefromhardbwherea.qu=b.quorderbyje)
求删除重复记录的sql语句？
怎样把具有相同字段的纪录删除，只留下一条。
例如，表test里有id,name字段
如果有name相同的记录只留下一条，其余的删除。
name的内容不定，相同的记录数不定。
有没有这样的sql语句？
==============================
A:一个完整的解决方案：
将重复的记录记入temp1表:
select[标志字段id],count(*)intotemp1from[表名]
groupby[标志字段id]
havingcount(*)>1
2、将不重复的记录记入temp1表:
inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=1
3、作一个包含所有不重复记录的表：
select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)
4、删除重复表:
delete[表名]
5、恢复表：
insert[表名]select*fromtemp2
6、删除临时表:
droptabletemp1
droptabletemp2
================================
B:
createtablea_dist(idint,namevarchar(20))
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
execup_distinct'a_dist','id'
select*froma_dist
createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger
select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'
exec(@sql)
opencur_rows
fetchcur_rowsinto@id,@max
while@@fetch_status=0
begin
select@max=@max-1
setrowcount@max
select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key
if@type=56
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
if@type=167
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
exec(@sql)
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows
setrowcount0
end
select*fromsystypes
select*fromsyscolumnswhereid=object_id('a_dist')
行列转换--普通
假设有张学生成绩表(CJ)如下
NameSubjectResult
张三语文80
张三数学90
张三物理85
李四语文85
李四数学92
李四物理82
想变成
语文数学物理
张三809085
李四859282
declare@sqlvarchar(4000)
set@sql='selectName'
select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultend)['+Subject+']'
from(selectdistinctSubjectfromCJ)asa
select@sql=@sql+'fromtestgroupbyname'
exec(@sql)
行列转换--合并
有表A,
idpid
11
12
13
21
22
31
如何化成表B:
idpid
11,2,3
21,2
31
创建一个合并的函数
createfunctionfmerg(@idint)
returnsvarchar(8000)
as
begin
declare@strvarchar(8000)
set@str=''
select@str=@str+','+cast(pidasvarchar)from表Awhereid=@id
set@str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
selectdistinctid,dbo.fmerg(id)from表A
如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare@objidint,@objnamechar(40)
set@objname='tablename'
select@objid=idfromsysobjectswhereid=object_id(@objname)
select'Column_name'=namefromsyscolumnswhereid=@objidorderbycolid
或
SELECT*FROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME='users'
通过SQL语句来更改用户的密码
修改别人的,需要sysadminrole
EXECsp_passwordNULL,'newpassword','User'
如果帐号为SA执行EXECsp_passwordNULL,'newpassword',sa
怎么判断出一个表的哪些字段不允许为空？
selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhereIS_NULLABLE='NO'andTABLE_NAME=tablename
如何在数据库里找到含有相同字段的表？
a.查已知列名的情况
SELECTb.nameasTableName,a.nameascolumnname
FromsyscolumnsaINNERJOINsysobjectsb
ONa.id=b.id
ANDb.type='U'
ANDa.name='你的字段名字'
未知列名查所有在不同表出现过的列名
Selecto.nameAstablename,s1.nameAscolumnname
Fromsyscolumnss1,sysobjectso
Wheres1.id=o.id
Ando.type='U'
AndExists(
Select1Fromsyscolumnss2
Wheres1.name=s2.name
Ands1.id<>s2.id
)
查询第xxx行数据
假设id是主键：
select*from(selecttopxxx*fromyourtable)aawherenotexists(select1from(selecttopxxx-1*fromyourtable)bbwhereaa.id=bb.id)
如果使用游标也是可以的
fetchabsolute[number]from[cursor_name]
行数为绝对行数
SQLServer日期计算
a.一个月的第一天
SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
b.本周的星期一
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
c.一年的第一天
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
d.季度的第一天
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
e.上个月
的最后一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
f.去年的最后一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
g.本月的最后一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
h.本月的第一个星期一
selectDATEADD(wk,DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
),0)
i.本年的最后一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))。
获取表结构[把'sysobjects'替换成'tablename'即可]
SELECTCASEIsNull(I.name,'')
When''Then''
Else'*'
EndasIsPK,
Object_Name(A.id)ast_name,
A.nameasc_name,
IsNull(SubString(M.text,1,254),'')aspbc_init,
T.nameasF_DataType,
CASEIsNull(TYPEPROPERTY(T.name,'Scale'),'')
WHEN''ThenCast(A.precasvarchar)
ELSECast(A.precasvarchar)+','+Cast(A.scaleasvarchar)
ENDasF_Scale,
A.isnullableasF_isNullAble
FROMSyscolumnsasA
JOINSystypesasT
ON(A.xType=T.xUserTypeANDA.Id=Object_id('sysobjects'))
LEFTJOIN(SysIndexesasI
JOINSyscolumnsasA1
ON(I.id=A1.idandA1.id=object_id('sysobjects')and(I.status&;0x800)=0x800ANDA1.colid<=I.keycnt))
ON(A.id=I.idANDA.name=index_col('sysobjects',I.indid,A1.colid))
LEFTJOINSysCommentsasM
ON(M.id=A.cdefaultandObjectProperty(A.cdefault,'IsConstraint')=1)
ORDERBYA.ColidASC
提取数据库内所有表的字段详细说明的SQL语句
SELECT
(casewhena.colorder=1thend.nameelse''end)N'表名',
a.colorderN'字段序号',
a.nameN'字段名',
(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''
end)N'标识',
(casewhen(SELECTcount(*)
FROMsysobjects
WHERE(namein
(SELECTname
FROMsysindexes
WHERE(id=a.id)AND(indidin
(SELECTindid
FROMsysindexkeys
WHERE(id=a.id)AND(colidin
(SELECTcolid
FROMsyscolumns
WHERE(id=a.id)AND(name=a.name)))))))AND
(xtype='PK'))>0then'√'else''end)N'主键',
b.nameN'类型',
a.lengthN'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数',
(casewhena.isnullable=1then'√'else''end)N'允许空',
isnull(e.text,'')N'默认值',
isnull(g.[value],'')ASN'字段说明'
FROMsyscolumnsa
leftjoinsystypesb
ona.xtype=b.xusertype
innerjoinsysobjectsd
ona.id=d.idandd.xtype='U'andd.name<>'dtproperties'
leftjoinsyscommentse
ona.cdefault=e.id
leftjoinsyspropertiesg
ona.id=g.idANDa.colid=g.smallid
orderbyobject_name(a.id),a.colorder
快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
selectrowsfromsysindexeswhereid=object_id('test')andindidin(0,1)
update2setKHX
H=(ID+1)\22行递增编号
update[23]setid1='No.'+right('00000000'+id,6)whereidnotlike'No%'//递增
update[23]setid1='No.'+right('00000000'+replace(id1,'No.',''),6)//补位递增
deletefrom[1]where(id%2)=1
奇数
替换表名字段
update[1]setdomurl=replace(domurl,'Upload/Imgswf/','Upload/Photo/')wheredomurllike'%Upload/Imgswf/%'
截位
SELECTLEFT(表名,5)
截位
SELECTLEFT(表名,5)
（MSSQLServer）SQL语句导入导出大全
--
--
select*fromopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select*fromcustomerwherecountry!="USA"orderbycountry')
go
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insertintoopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:\',
'select*from[aa.DBF]')
select*from表
说明:
SourceDB=c:\指定foxpro表所在的文件夹
aa.DBF指定foxpro表的文件名.
insertintoopenrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)select*from数据库名..B表
insertintoB表selet*fromopenrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)
*********************导入xml文件
DECLARE@idocint
DECLARE@docvarchar(1000)
--sampleXMLdocument
SET@doc='
Customerwasverysatisfied
Important
HappyCustomer.
'
--CreateaninternalrepresentationoftheXMLdocument.
EXECsp_xml_preparedocument@idocOUTPUT,@doc
--ExecuteaSELECTstatementusingOPENXMLrowsetprovider.
SELECT*
FROMOPENXML(@idoc,'/root/Customer/Order',1)
WITH(oidchar(5),
amountfloat,
commentntext'text()')
EXECsp_xml_removedocument@idoc
用bcp实现的存储过程
ifexists(select1fromsysobjectswherename='File2Table'andobjectproperty(id,'IsProcedure')=1)
dropprocedureFile2Table
go
createprocedureFile2Table
@servernamevarchar(200)--服务器名
,@usernamevarchar(200)--用户名,如果用NT验证方式,则为空''
,@passwordvarchar(200)--密码
,@tbnamevarchar(500)--数据库.dbo.表名,如果不指定:.dbo.表名,则
导出数据库的所有用户表
,@filenamevarchar(1000)--导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isoutbit--1为导出,0为导入
as
declare@sqlvarchar(8000)
if@tbnamelike'%.%.%'--如果指定了表名,则直接导出单个表
begin
set@sql='bcp'+@tbname
+casewhen@isout=1then'out'else'in'end
+'"'+@filename+'"/w'
+'/S'+@servername
+casewhenisnull(@username,'')=''then''else'/U'+@usernameend
+'/P'+isnull(@password,'')
execmaster..xp_cmdshell@sql
end
else
begin--导出整个数据库,定义游标,取出所有的用户表
declare@m_tbnamevarchar(250)
ifright(@filename,1)<>'\'set@filename=@filename+'\'
set@m_tbname='declare#tbcursorforselectnamefrom'+@tbname+'..sysobjectswherextype=''U'''
exec(@m_tbname)
open#tb
fetchnextfrom#tbinto@m_tbname
while@@fetch_status=0
begin
set@sql='bcp'+@tbname+'..'+@m_tbname
+casewhen@isout=1then'out'else'in'end
+'"'+@filename+@m_tbname+'.txt"/w'
+'/S'+@servername
+casewhenisnull(@username,'')=''then''else'/U'+@usernameend
+'/P'+isnull(@password,'')
execmaster..xp_cmdshell@sql
fetchnextfrom#tbinto@m_tbname
end
close#tb
deallocate#tb
end
go
想用
select*intoopendatasource(...)fromopendatasource(...)
实现将一个Excel文件内容导入到一个文本文件
假设Excel中有两列，第一列为，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分开。
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.
insertinto
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'
,'Excel5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls'
--,Sheet1$)
)...[Sheet1$]
如果你想直接插入并生成文本文件,就要用bcp
declare@sqlvarchar(8000),@tbnamevarchar(50)
--首先将excel表内容导入到一个全局临时表
select@tbname='[##temp'+cast(newid()asvarchar(40))+']'
,@sql='select,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into'+@tbname+'from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls''
)...[Sheet1$]'
exec(@sql)
--然后用bcp从全局临时表导出到文本文件
set@sql='bcp"'+@tbname+'"out"c:\aa.txt"/S"(local)
"/P""/c'
execmaster..xp_cmdshell@sql
--删除临时表
exec('droptable'+@tbname)
用bcp将文件导入导出到数据库的存储过程:
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[p_binaryIO]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[p_binaryIO]
GO
Createprocp_binaryIO
@servenamevarchar(30),--服务器名称
@usernamevarchar(30),--用户名
@passwordvarchar(30),--密码
@tbnamevarchar(500),--数据库..表名
@fdnamevarchar(30),--字段名
@fnamevarchar(1000),--目录+文件名,处理过程中要使用/覆盖:@filename+.bak
@tjvarchar(1000)='',--处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀
@isoutbit=1--1导出((默认),0导入
AS
declare@fname_invarchar(1000)--bcp处理应答文件名
,@fsizevarchar(20)--要处理的文件的大小
,@m_tbnamevarchar(50)--临时表名
,@sqlvarchar(8000)
--则取得导入文件的大小
if@isout=1
set@fsize='0'
else
begin
createtable#tb(可选名varchar(20),大小int
,创建日期varchar(10),创建时间varchar(20)
,上次写操作日期varchar(10),上次写操作时间varchar(20)
,上次访问日期varchar(10),上次访问时间varchar(20),特性int)
insertinto#tb
execmaster..xp_getfiledetails@fname
select@fsize=大小from#tb
droptable#tb
if@fsizeisnull
begin
print'文件未找到'
return
end
end
--生成数据处理应答文件
set@m_tbname='[##temp'+cast(newid()asvarchar(40))+']'
set@sql='select*into'+@m_tbname+'from(
selectnullas类型
unionallselect0as前缀
unionallselect'+@fsize+'as长度
unionallselectnullas结束
unionallselectnullas格式
)a'
exec(@sql)
select@fname_in=@fname+'_temp'
,@sql='bcp"'+@m_tbname+'"out"'+@fname_in
+'"/S"'+@servename
+casewhenisnull(@username,'')=''then''
else'"/U"'+@usernameend
+'"/P"'+isnull(@password,'')+'"/c'
execmaster..xp_cmdshell@sql
--删除临时表
set@sql='droptable'+@m_tbname
exec(@sql)
if@isout=1
begin
set@sql='bcp"selecttop1'+@fdname+'from'
+@tbname+caseisnull(@tj,'')when''then''
else'where'+@tjend
+'"queryout"'+@fname
+'"/S"'+@servename
+casewhenisnull(@username,'')=''then''
else'"/U"'+@usernameend
+'"/P"'+isnull(@password,'')
+'"/i"'+@fname_in+'"'
execmaster..xp_cmdshell@sql
end
else
begin
--为数据导入准备临时表
set@sql='selecttop0'+@fdname+'into'
+@m_tbname+'from'+@tbname
exec(@sql)
--将数据导入到临时表
set@sql='bcp"'+@m_tbname+'"in"'+@fname
+'"/S"'+@servename
+casewhenisnull(@username,'')=''then''
else'"/U
"'+@usernameend
+'"/P"'+isnull(@password,'')
+'"/i"'+@fname_in+'"'
execmaster..xp_cmdshell@sql
--将数据导入到正式表中
set@sql='update'+@tbname
+'set'+@fdname+'=b.'+@fdname
+'from'+@tbname+'a,'
+@m_tbname+'b'
+caseisnull(@tj,'')when''then''
else'where'+@tjend
exec(@sql)
--删除数据处理临时表
set@sql='droptable'+@m_tbname
end
--删除数据处理应答文件
set@sql='del'+@fname_in
execmaster..xp_cmdshell@sql
go
&nb, sp;
createtabletesttable2
(
idintIDENTITY,
dptIDint,
namevarchar(12)
)
insertintotesttable2values(1,'张三')
insertintotesttable2values(1,'李四')
insertintotesttable2values(2,'王五')
insertintotesttable2values(3,'彭六')
insertintotesttable2values(4,'陈七')
答案是：
SELECTtesttable2.*,ISNULL(department,'黑人')
FROMtesttable1rightjointesttable2ontesttable2.dptID=testtable1.ID
在面试应聘的SQLServer数据库开发人员时，我运用了一套标准的基准技术问题。下面这些问题是我觉得能够真正有助于淘汰不合格应聘者的问题。它们按照从易到难的顺序排列。当你问到关于主键和外键的问题时，后面的问题都十分有难度，因为答案可能会更难解释和说明，尤其是在面试的情形下。
你能向我简要叙述一下SQLServer2000中使用的一些数据库对象吗?
你希望听到的答案包括这样一些对象:表格、视图、用户定义的函数，以及存储过程;如果他们还能够提到像触发器这样的对象就更好了。如果应聘者不能回答这个基本的问题，那么这不是一个好兆头。
NULL是什么意思?
NULL(空)这个值是数据库世界里一个非常难缠的东西，所以有不少应聘者会在这个问题上跌跟头您也不要觉得意外。NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQLServer数据库里有ANSI_NULLS，当然在默认情况下会有，对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个UNKNOWN值进行比较，并在逻辑上希望获得一个答案。您必须使用ISNULL操作符。
什么是索引?SQLServer2000里有什么类型的索引?
任何有经验的数据库开发人员都应该能够很轻易地回答这个问题。一些经验不太多的开发人员能够回答这个问题，但是有些地方会说不清楚。简单地说，索引是一个数据结构，用来快速访问数据库表格或者视图里的数据。在SQLServer里，它们有两种形式:聚集索引和非聚集索引。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表格的哪个(或哪些)字段，这些字段都会按顺序被保存在表格。由于存在这种排序，所以每
个表格只会有一个聚集索引。非聚集索引在索引的叶级有一个行标识符。这个行标识符是一个指向磁盘上数据的指针。它允许每个表格有多个非聚集索引。
什么是主键?什么是外键?
主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。
什么是触发器?SQLServer2000有什么不同类型的触发器?
让未来的数据库开发人员知道可用的触发器类型以及如何实现它们是非常有益的。触发器是一种专用类型的存储过程，它被捆绑到SQLServer2000的表格或者视图上。在SQLServer2000里，有INSTEAD-OF和AFTER两种触发器。INSTEAD-OF触发器是替代数据操控语言(DataManipulation
Language，DML)语句对表格执行语句的存储过程。例如，如果我有一个用于TableA的INSTEAD-OF-UPDATE
触发器，同时对这个表格执行一个更新语句，那么INSTEAD-OF-UPDATE触发器里的代码会执行，而不是我执行的更新语句则不会执行操作。
AFTER触发器要在DML语句在数据库里使用之后才执行。这些类型的触发器对于监视发生在数据库表格里的数据变化十分好用。
您如何确一个带有名为Fld1字段的TableB表格里只具有Fld1字段里的那些值，而这些值同时在名为TableA的表格的Fld1字段里?
这个与关系相关的问题有两个可能的答案。第一个答案(而且是您希望听到的答案)是使用外键限制。外键限制用来维护引用的完整性。它被用来确保表格里的字段只保存有已经在不同的(或者相同的)表格里的另一个字段里定义了的值。这个字段就是候选键(通常是另外一个表格的主键)。
另外一种答案是触发器。触发器可以被用来保证以另外一种方式实现与限制相同的作用，但是它非常难设置与维护，而且性能一般都很糟糕。由于这个原因，微软建议开发人员使用外键限制而不是触发器来维护引用的完整性。
对一个投入使用的在线事务处理表格有过多索引需要有什么样的性能考虑?
你正在寻找进行与数据操控有关的应聘人员。对一个表格的索引越多，数据库引擎用来更新、插入或者删除数据所需要的时间就越多，因为在数据操控发生的时候索引也必须要维护。
你可以用什么来确保表格里的字段只接受特定范围里的值?
这个问题可以用多种方式来回答，但是只有一个答案是“好”答案。您希望听到的回答是Check限制，它在数据库表格里被定义，用来限制
输入该列的值。
触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。因此，微软建议使用Check限制而不是其他的方式来限制域的完整性。
如果应聘者能够正确地回答这个问题，那么他的机会就非常大了，因为这表明他们具有使用存储过程的经验。
返回参数总是由存储过程返回，它用来表示存储过程是成功还是失败。返回参数总是INT数据类型。
OUTPUT参数明确要求由开发人员来指定，它可以返回其他类型的数据，例如字符型和数值型的值。(可以用作输出参数的数据类型是有一些限制的。)您可以在一个存储过程里使用多个OUTPUT参数，而您只能够使用一个返回参数。
什么是相关子查询?如何使用这些查询?
经验更加丰富的开发人员将能够准确地描述这种类型的查询。相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会真正请求外部查询的值，从而形成一个类似于循环的状况。
什么是SQL注入式攻击？
所谓SQL注入式攻击，就是攻击者把SQL命令插入到Web表单的输入域或页面请求的查询字符串，欺骗服务器执行恶意的SQL命令。在某些表单中，用户输入的内容直接用来构造（或者影响）动态SQL命令，或作为存储过程的输入参数，这类表单特别容易受到SQL注入式攻击。常见的SQL注入式攻击过程类如：
⑴某个ASP.NETWeb应用有一个登录页面，这个登录页面控制着用户是否有权访问应用，它要求用户输入一个名称和密码。
⑵登录页面中输入的内容将直接用来构造动态的SQL命令，或者直接用作存储过程的参数。下面是ASP.NET应用构造查询的一个例子：
System.Text.StringBuilderquery=newSystem.Text.StringBuilder(
"SELECT*fromUsersWHERElogin='")
.Append(txtLogin.Text).Append("'ANDpassword='")
.Append(txtPassword.Text).Append("'");
⑶攻击者在用户名字和密码输入框中输入"'或'1'='1"之类的内容。
⑷用户输入的内容提交给服务器之后，服务器运行上面的ASP.NET代码构造出查询用户的SQL命令，但由于攻击者输入的内容非常特殊，所以最后得到的SQL命令变成：SELECT*fromUsersWHERElogin=''or'1'='1'ANDpassword=''or'1'='1'。
⑸服务器执行查询或存储过程，将用户输入的身份信息和服务器中保存的身份信息进行对比。
⑹由于SQL命令实际上已被注入式攻击修改，已经不能真正验证用户身份，所以系统会错误地授权给攻击者。
如果攻击者知道应用会将表单中输入的内容直接用于验证身份的查询，他就会尝试输入某些特殊的SQL字符串
篡改查询改变其原来的功能，欺骗系统授予访问权限。
系统环境不同，攻击者可能造成的损害也不同，这主要由应用访问数据库的安全权限决定。如果用户的帐户具有管理员或其他比较高级的权限，攻击者就可能对数据库的表执行各种他想要做的操作，包括添加、删除或更新数据，甚至可能直接删除表
如何防范SQL注入式攻击？
好在要防止ASP.NET应用被SQL注入式攻击闯入并不是一件特别困难的事情，只要在利用表单输入的内容构造SQL命令之前，把所有输入内容过滤一番就可以了。过滤输入内容可以按多种方式进行。
⑴对于动态构造SQL查询的场合，可以使用下面的技术：
第一：替换单引号，即把所有单独出现的单引号改成两个单引号，防止攻击者修改SQL命令的含义。再来看前面的例子，"SELECT*fromUsersWHERElogin='''or''1''=''1'ANDpassword='''or''1''=''1'"显然会得到与"SELECT*fromUsersWHERElogin=''or'1'='1'ANDpassword=''or'1'='1'"不同的结果。
第二：删除用户输入内容中的所有连字符，防止攻击者构造出类如"SELECT*fromUsersWHERElogin='mas'--ANDpassword=''"之类的查询，因为这类查询的后半部分已经被注释掉，不再有效，攻击者只要知道一个合法的用户登录名称，根本不需要知道用户的密码就可以顺利获得访问权限。
第三：对于用来执行查询的数据库帐户，限制其权限。用不同的用户帐户执行查询、插入、更新、删除操作。由于隔离了不同帐户可执行的操作，因而也就防止了原本用于执行SELECT命令的地方却被用于执行INSERT、UPDATE或DELETE命令。
⑵用存储过程来执行所有的查询。SQL参数的传递方式将防止攻击者利用单引号和连字符实施攻击。此外，它还使得数据库权限可以限制到只允许特定的存储过程执行，所有的用户输入必须遵从被调用的存储过程的安全上下文，这样就很难再发生注入式攻击了。
⑶限制表单或查询字符串输入的长度。如果用户的登录名字最多只有10个字符，那么不要认可表单中输入的10个以上的字符，这将大大增加攻击者在SQL命令中插入有害代码的难度。
⑷检查用户输入的合法性，确信输入的内容只包含合法的数据。数据检查应当在客户端和服务器端都执行——之所以要执行服务器端验证，是为了弥补客户端验证机制脆弱的安全性。
在客户端，攻击者完全有可能获得网页的源代码，修改验证合法性的脚本（或者直接删除脚本），然后将非法内容通过修改后的表单提交给服务器。因此，要保证验证操作确实已经执行，唯一的办法就是在服务器端也执行验证。你可以使用许多内建的验证对象，例如Regular
ExpressionValidator，它们能够自动生成验证用的客户端脚本，当然你也可以插入服务器端的方法调用。如果找不到现成的验证对象，你可以通过CustomValidator自己创建一个。
⑸将用户登录名称、密码等数据加密保存。加密用户输入的数据，然后再将它与数据库中保存的数据比较，这相当于对用户输入的数据进行了"消毒"处理，用户输入的数据不再对数据库有任何特殊的意义，从而也就防止了攻击者注入SQL命令。System.Web.Security.FormsAuthentication类有一个HashPasswordForStoringInConfigFile，非常适合于对输入数据进行消毒处理。
⑹检查提取数据的查询所返回的记录数量。如果程序只要求返回一个记录，但实际返回的记录却超过一行，那就当作出错处理
Sql常见题目
为管理岗位业务培训信息，建立3个表:
S(S#,SN,SD,SA)S#,SN,SD,SA分别代表、学员、所属单位、学员年龄
C(C#,CN)C#,CN分别代表课程编号、课程名称
SC(S#,C#,G)S#,C#,G分别代表、所选修的课程编号、学习成绩
1.使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员和
--实现代码:
SelectSN,SDFROMSWhere[S#]IN(Select[S#]FROMC,SCWhereC.[C#]=SC.[C#]ANDCN=N'税收基础')
2.使用标准SQL嵌套语句查询选修课程编号为’C2’的学员和所属单位
--实现代码:
SelectS.SN,S.SDFROMS,SCWhereS.[S#]=SC.[S#]ANDSC.[C#]='C2'
3.使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员和所属单位
--实现代码:
SelectSN,SDFROMSWhere[S#]NOTIN(Select[S#]FROMSCWhere[C#]='C5')
4.使用标准SQL嵌套语句查询选修全部课程的学员和所属单位
--实现代码:
SelectSN,SDFROMSWhere[S#]IN(Select[S#]FROMSCRIGHTJOINCONSC.[C#]=C.[C#]GROUPBY[S#]HAVINGCOUNT(*)=COUNT([S#]))
5.查询选修了课程的学员人数
--实现代码:
Select学员人数=COUNT(DISTINCT[S#])FROMSC
6.查询选修课程超过5门的学员和所属单位
--实现代码:
SelectSN,SDFROMSWhere[S#]IN(Select[S#]FROMSCGROUPBY[S#]HAVINGCOUNT(DISTINCT[C#])>5)
题目2:
问题描述:
S(SNO,SNAME)学生关系。SNO为，SNAME为
C(CNO,CNAME,CTEACHER)课程关系。CNO为课程号，CNAME为课程名，CTEACHER为任课教师
SC(SNO,CNO,SCGRADE)选课关系。SCGRADE为成绩
1.找出没有选修过“李明”老师讲授课程的所有学生
--实现代码:
SelectSNAMEFROMSWhereNOTEXISTS(Select*FROMSC,CWhereSC.CNO=C.CNOANDCNAME='李明'ANDSC.SNO=S.SNO)
2.列出有二门以上(含两门)不及格课程的学生及其平均成绩
--实现代码:
SelectS.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)FROMS,SC,(SelectSNOFROMSCWhereSCGRAD
E<60GROUPBYSNOHAVINGCOUNT(DISTINCTCNO)>=2)AWhereS.SNO=A.SNOANDSC.SNO=A.SNOGROUPBYS.SNO,S.SNAME
3.列出既学过“1”号课程，又学过“2”号课程的所有学生
--实现代码:
SelectS.SNO,S.SNAMEFROMS,(SelectSC.SNOFROMSC,CWhereSC.CNO=C.CNOANDC.CNAMEIN('1','2')GROUPBYSNOHAVINGCOUNT(DISTINCTCNO)=2)SCWhereS.SNO=SC.SNO
4.列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的
--实现代码:
SelectS.SNO,S.SNAMEFROMS,(SelectSC1.SNOFROMSCSC1,CC1,SCSC2,CC2WhereSC1.CNO=C1.CNOANDC1.NAME='1'ANDSC2.CNO=C2.CNOANDC2.NAME='2'ANDSC1.SCGRADE>SC2.SCGRADE)SCWhereS.SNO=SC.SNO
5.列出“1”号课成绩比“2”号课成绩高的所有学生的及其“1”号课和“2”号课的成绩
--实现代码:
SelectS.SNO,S.SNAME,SC.[1号课成绩],SC.[2号课成绩]FROMS,(SelectSC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADEFROMSCSC1,CC1,SCSC2,CC2WhereSC1.CNO=C1.CNOANDC1.NAME='1'ANDSC2.CNO=C2.CNOANDC2.NAME='2'ANDSC1.SCGRADE>SC2.SCGRADE)SCWhereS.SNO=SC.SNO
求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
例如：6，7，8，9，10条记录均符合
ID主叫号码被叫号码通话起始时间通话结束时间通话时长
19829000002154665466562007-02-0109:49:53.0002007-02-0109:50:16.00023
2982900000215466546662007-02-0109:50:29.0002007-02-0109:50:41.00012
3982900000215466546662007-02-0109:50:58.0002007-02-0109:51:12.00014
46829090007551333298662007-02-0110:04:31.0002007-02-0110:07:13.000162
57829000007552557086382007-02-0110:48:26.0002007-02-0110:49:23.00057
67829000007558211191092007-02-0110:49:39.0002007-02-0110:52:55.000196
7782900000357309283702007-02-0111:30:45.0002007-02-0111:31:58.00073
87829000008711388899042007-02-0111:33:47.0002007-02-0111:35:00.00073
9682900000357309283792007-02-0111:52:20.0002007-02-0111:54:56.000156
106829000002985218111992007-02-0112:44:45.0002007-02-0112:45:04.00019
答案：
SELECTDISTINCTa.*FROMdbo.hcaleftjoindbo.hcb
ONa.主叫号码=b.主叫号码
WHEREa.id<>b.idAND(DATEDIFF(second,a.通话起始时间,b.通话结束时间)>10AND
DATEDIFF(second,b.通话起始时间,a.通话结束时间)>10)
SqlServer关于按周统计的问题
统计SqlServer里一个销售明细表里某个时间段的销售额，而且要按周进行比较，以下是该语句的写法：
selectsum(销售金额),datename(week,销售日期-1)fromsaleswhere销售日期betweebegindateandenddategroupbydatename(week,销售日期-1)
注意：这里之所以要把销售日期-1是因为sqlserver默认的一周的第一天是星期天，而我们习惯的统计是以星期一到星期天计算的，所以减一。
临时表
临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。
临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号(#)打头；它们仅对当前的用户连接是可见的；当用户从SQLServer实例断开连接时被删除。全局临时表的名称以两个数字符号(##)打头，创建后对任何用户都是可见的，当所有引用该表的用户从SQLServer断开连接时被删除。
例如，如果创建了employees表，则任何在数据库中有使用该表的安全权限的用户都可以使用该表，除非已将其删除。如果数据库会话创建了本地临时表#employees，则仅会话可以使用该表，会话断开连接后就将该表删除。如果创建了##employees全局临时表，则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用，则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该表，则SQLServer将在您断开连接并且所有其他会话不再使用该表时将其删除
视图
视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是SELECT语句。SELECT语句的结果集构成视图所返回的虚拟表。用户可以采用引用表时所使用的方法，在Transact-SQL语句中引用视图名称来使用此虚拟表
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图在数据库中并不是以数据值存储集形式存在，除非是索引视图。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。分布式查询也可用于定义使用多个异类源数据的视图。例如，如果有多台不同的服务器分别存储您的单位在不同地区的数据，而您需要将这些服务器上结构相似的数据组合起来，这种方式就很有用。
通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少
视图种类：索引视图和索引视图
SQLServer2005查询处理器对索引视图和非索引视图将区别对待：
索引视图的行以表的格式存储在数据库中。如果查询优化器决定使用查询计划的索引视图，则索引视图将按照基表的处理方式进行处理。
只有非索引视图的定义才存储，而不存储视图的行。查询优化器将视图定义中的逻辑纳入执行计划，而该执行计划是它为引用非索引视图的SQL语句生成的。
SQLServer查询优化器用于
决定何时使用索引视图的逻辑与用于决定何时对表使用索引的逻辑相似。如果索引视图中的数据包括所有或部分SQL语句，而且查询优化器确定视图的某个索引是低成本的访问路径，则不论查询中是否引用了该视图的名称，查询优化器都将选择此索引
当SQL语句引用非索引视图时，分析器和查询优化器将分析SQL语句的源和视图的源，然后将它们解析为单个执行计划。没有单独用于SQL语句或视图的计划
创建视图前请考虑如下准则：
只能在当前数据库中创建视图。但是，如果使用分布式查询定义视图，则新视图所引用的表和视图可以存在于其他数据库甚至其他服务器中。
视图名称必须遵循标识符的规则，且对每个架构都必须唯一。此外，该名称不得与该架构包含的任何表的名称相同。
您可以对其他视图创建视图。MicrosoftSQLServer2005允许嵌套视图。但嵌套不得超过32层。根据视图的复杂性及可用内存，视图嵌套的实际限制可能低于该值。
不能将规则或DEFAULT定义与视图相关联。
不能将AFTER触发器与视图相关联，只有INSTEADOF触发器可以与之相关联。
定义视图的查询不能包含COMPUTE子句、COMPUTEBY子句或INTO关键字。
定义视图的查询不能包含ORDERBY子句，除非在SELECT语句的选择列表中还有一个TOP子句。
定义视图的查询不能包含指定查询提示的OPTION子句。
定义视图的查询不能包含TABLESAMPLE子句。
不能为视图定义全文索引定义。
不能创建临时视图，也不能对临时表创建视图。
不能删除参与到使用SCHEMABINDING子句创建的视图中的视图、表或函数，除非该视图已被删除或更改而不再具有架构绑定。另外，如果对参与具有架构绑定的视图的表执行ALTERTABLE语句，而这些语句又会影响该视图的定义，则这些语句将会失败。
尽管查询引用一个已配置全文索引的表时，视图定义可以包含全文查询，仍然不能对视图执行全文查询。
下列情况下必须指定视图中每列的名称：
视图中的任何列都是从算术表达式、内置函数或常量派生而来。
视图中有两列或多列原应具有相同名称（通常由于视图定义包含联接，因此来自两个或多个不同表的列具有相同的名称）。
希望为视图中的列指定一个与其源列不同的名称。（也可以在视图中重命名列。）无论重命名与否，视图列都会继承其源列的数据类型。
若要创建视图，必须获得数据库所有者授予您创建视图的权限，并且如果使用架构绑定创建视图，您必须对视图定义中所引用的表或视图具有适当权限
CREATEVIEW[schema_name.]view_name[(column[,...n])]
[WITH
[,...n]]
ASselect_statement[;]
[WITHCHECKOPTION]
::=
{
[ENCRYPTION]
[SCHEMABINDING]
[VIEW_METADATA]}
********************************
******************************************
*******************************************************
*说明：复制表(只复制结构,源表名：a新表名：b)
select*intobfromawhere1<>1
*说明：拷贝表(拷贝数据,源表名：a目标表名：b)
insertintob(a,b,c)selectd,e,ffromb;
*说明：显示文章、提交人和最后回复时间
selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b
*说明：外连接查询(表名1：a表名2：b)
selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c
*说明：日程安排提前五分钟提醒
select*from日程安排wheredatediff('minute',f开始时间,getdate())>5
*说明：两张关联表，删除主表中已经在副表中没有的信息
deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)
*说明：--
SQL:
SelectA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE
FROMTABLE1,
(SelectX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATE
FROM(SelectNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHAND
FROMTABLE2
WhereTO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X,
(SelectNUM,UPD_DATE,STOCK_ONHAND
FROMTABLE2
WhereTO_CHAR(UPD_DATE,'YYYY/MM')=
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')||'/01','YYYY/MM/DD')-1,'YYYY/MM'))Y,
WhereX.NUM=Y.NUM（+）
ANDX.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)B
WhereA.NUM=B.NUM
*说明：--
select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名称='"&;strdepartmentname&;"'and专业名称='"&;strprofessionname&;"'orderby性别,生源地,高考总成绩
*从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
Selecta.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')AStelyear,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'01',a.factration))ASJAN,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'02',a.factration))ASFRI,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'03',a.factration))ASMAR,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'04',a.factration))ASAPR,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'05',a.factration))ASMAY,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'06',a.factration))ASJUE,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'07',a.factration))ASJUL,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'08',a.factration))ASAGU,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'09',a.factration))ASSEP,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'10',a.factration))ASOCT,
SUM(decode(TO_CHAR(a.telfeedate,'mm'),'11',a.factration))ASNOV,
SUM(decode(TO_CHAR(a.telfeedate,
'mm'),'12',a.factration))ASDEC
FROM(Selecta.userper,a.tel,a.standfee,b.telfeedate,b.factration
FROMTELFEESTANDa,TELFEEb
Wherea.tel=b.telfax)a
GROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')
*说明：四表联查问题
select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....
*说明：得到表中最小的未使用的ID号
*Select(CASEWHENEXISTS(Select*FROMHandlebWhereb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleIDFROMHandleWhereNOTHandleIDIN(Selecta.HandleID-1FROMHandlea)
*一个SQL语句的问题:行列转换
select*fromv_temp
上面的视图结果如下:
user_namerole_name
-------------------------
系统管理员管理员
feng管理员
feng一般用户
test一般用户
想把结果变成这样:
user_namerole_name
---------------------------
系统管理员管理员
feng管理员,一般用户
test一般用户
===================
createtablea_test(namevarchar(20),role2varchar(20))
insertintoa_testvalues('李','管理员')
insertintoa_testvalues('张','管理员')
insertintoa_testvalues('张','一般用户')
insertintoa_testvalues('常','一般用户')
createfunctionjoin_str(@contentvarchar(100))
returnsvarchar(2000)
as
begin
declare@strvarchar(2000)
set@str=''
select@str=@str+','+rtrim(role2)froma_testwhere[name]=@content
select@str=right(@str,len(@str)-1)
return@str
end
go
--调用：
select[name],dbo.join_str([name])role2froma_testgroupby[name]
--selectdistinctname,dbo.uf_test(name)froma_test
*快速比较结构相同的两表
结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
============================
给你一个测试方法，从northwind中的orders表取数据。
select*inton1fromorders
select*inton2fromorders
select*fromn1
select*fromn2
--添加主键，然后修改n1中若干字段的若干条
altertablen1addconstraintpk_n1_idprimarykey(OrderID)
altertablen2addconstraintpk_n2_idprimarykey(OrderID)
selectOrderIDfrom(select*fromn1unionselect*fromn2)agroupbyOrderIDhavingcount(*)>1
应该可以，而且将不同的记录的ID显示出来。
下面的适用于双方记录一样的情况，
select*fromn1whereorderidin(selectOrderIDfrom(select*fromn1unionselect*fromn2)agroupbyOrderIDhavingcount(*)>1)
至于双方互不存在的记录是比较好处理的
--删除n1,n2中若干条记录
deletefromn1whereorderIDin('10728','10730')
deletefromn2whereorderIDin('11000','11001')
--*************************************************************
--双方都有该记录却不完全相同
select*fromn1whereorderidin(selectOrderIDfrom(select*fromn1unionsele
ct*fromn2)agroupbyOrderIDhavingcount(*)>1)
union
--n2中存在但在n1中不存的在10728,10730
select*fromn1whereOrderIDnotin(selectOrderIDfromn2)
union
--n1中存在但在n2中不存的在11000,11001
select*fromn2whereOrderIDnotin(selectOrderIDfromn1)
*四种方法取表里n到m条纪录：
1.
selecttopm*into临时表(或表变量)fromtablenameorderbycolumnname--将topm笔插入
setrowcountn
select*from表变量orderbycolumnnamedesc
2.
selecttopn*from(selecttopm*fromtablenameorderbycolumnname)aorderbycolumnnamedesc
3.如果tablename里没有其他identity列，那么：
selectidentity(int)id0,*into#tempfromtablename
取n到m条的语句为：
select*from#tempwhereid0>=nandid0<=m
如果你在执行selectidentity(int)id0,*into#tempfromtablename这条语句的时候报错,那是因为你的DB中间的selectinto/bulkcopy属性没有打开要先执行：
execsp_dboption你的DB名字,'selectinto/bulkcopy',true
4.如果表里有identity属性，那么简单：
select*fromtablenamewhereidentitycolbetweennandm
*如何删除一个表中重复的记录？
createtablea_dist(idint,namevarchar(20))
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
execup_distinct'a_dist','id'
select*froma_dist
createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger
select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'
exec(@sql)
opencur_rows
fetchcur_rowsinto@id,@max
while@@fetch_status=0
begin
select@max=@max-1
setrowcount@max
select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key
if@type=56
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
if@type=167
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
exec(@sql)
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows
setrowcount0
end
select*fromsystypes
select*fromsyscolumnswhereid=object_id('a_dist')
*查询数据的最大排序问题（只能用一条语句写）
CreateTABLEhard(quchar(11),cochar(11),jenumeric(3,0))
insertintohardvalues('A','1',3)
insertintohardvalues('A','2',4)
insertintohardvalues('A','4',2)
insertintohardvalues('A','6',9)
insertintohardvalues('B','1',4)
insertintohardvalues('B','2',5)
insertintohardvalues('B','3',6)
insertintohardvalues('C','3',4)
insertintohardvalues('C','6',7)
insertintohardvalues('C','2',3)
要求查询出来的结果如下：
qucoje
---------------------
------
A69
A24
B36
B25
C67
C34
就是要按qu分组，每组中取je最大的前2位！！
而且只能用一句sql语句！！！
select*fromhardawherejein(selecttop2jefromhardbwherea.qu=b.quorderbyje)
*求删除重复记录的sql语句？
怎样把具有相同字段的纪录删除，只留下一条。
例如，表test里有id,name字段
如果有name相同的记录只留下一条，其余的删除。
name的内容不定，相同的记录数不定。
有没有这样的sql语句？
==============================
A:一个完整的解决方案：
将重复的记录记入temp1表:
select[标志字段id],count(*)intotemp1from[表名]
groupby[标志字段id]
havingcount(*)>1
2、将不重复的记录记入temp1表:
inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=1
3、作一个包含所有不重复记录的表：
select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)
4、删除重复表:
delete[表名]
5、恢复表：
insert[表名]select*fromtemp2
6、删除临时表:
droptabletemp1
droptabletemp2
================================
B:
createtablea_dist(idint,namevarchar(20))
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
insertintoa_distvalues(1,'abc')
execup_distinct'a_dist','id'
select*froma_dist
createprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))
--f_key表示是分组字段﹐即主键字段
as
begin
declare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeinteger
select@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'
exec(@sql)
opencur_rows
fetchcur_rowsinto@id,@max
while@@fetch_status=0
begin
select@max=@max-1
setrowcount@max
select@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_key
if@type=56
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@id
if@type=167
select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''
exec(@sql)
fetchcur_rowsinto@id,@max
end
closecur_rows
deallocatecur_rows
setrowcount0
end
select*fromsystypes
select*fromsyscolumnswhereid=object_id('a_dist')
*行列转换--普通
假设有张学生成绩表(CJ)如下
NameSubjectResult
张三语文80
张三数学90
张三物理85
李四语文85
李四数学92
李四物理82
想变成
语文数学物理
张三809085
李四859282
declare@sqlvarchar(4000)
set@sql='selectName'
select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultend)['+Subject+']'
from(selectdistinctSubjectfromCJ)asa
select@sql=@sql+'fromtestgroupbyname'
exec(@sql)
行列转换--
合并
有表A,
idpid
11
12
13
21
22
31
如何化成表B:
idpid
11,2,3
21,2
31
创建一个合并的函数
createfunctionfmerg(@idint)
returnsvarchar(8000)
as
begin
declare@strvarchar(8000)
set@str=''
select@str=@str+','+cast(pidasvarchar)from表Awhereid=@id
set@str=right(@str,len(@str)-1)
return(@str)
End
go
--调用自定义函数得到结果
selectdistinctid,dbo.fmerg(id)from表A
*如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare@objidint,@objnamechar(40)
set@objname='tablename'
select@objid=idfromsysobjectswhereid=object_id(@objname)
select'Column_name'=namefromsyscolumnswhereid=@objidorderbycolid
或
Select*FROMINFORMATION_SCHEMA.COLUMNSWhereTABLE_NAME='users'
*通过SQL语句来更改用户的密码
修改别人的,需要sysadminrole
EXECsp_passwordNULL,'newpassword','User'
如果帐号为SA执行EXECsp_passwordNULL,'newpassword',sa
*怎么判断出一个表的哪些字段不允许为空？
selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhereIS_NULLABLE='NO'andTABLE_NAME=tablename
*如何在数据库里找到含有相同字段的表？
a.查已知列名的情况
Selectb.nameasTableName,a.nameascolumnname
FromsyscolumnsaINNERJOINsysobjectsb
ONa.id=b.id
ANDb.type='U'
ANDa.name='你的字段名字'
*未知列名查所有在不同表出现过的列名
Selecto.nameAstablename,s1.nameAscolumnname
Fromsyscolumnss1,sysobjectso
Wheres1.id=o.id
Ando.type='U'
AndExists(
Select1Fromsyscolumnss2
Wheres1.name=s2.name
Ands1.id<>s2.id
)
*查询第xxx行数据
假设id是主键：
select*from(selecttopxxx*fromyourtable)aawherenotexists(select1from(selecttopxxx-1*fromyourtable)bbwhereaa.id=bb.id)
如果使用游标也是可以的
fetchabsolute[number]from[cursor_name]
行数为绝对行数
*SQLServer日期计算
a.一个月的第一天
SelectDATEADD(mm,DATEDIFF(mm,0,getdate()),0)
b.本周的星期一
SelectDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
c.一年的第一天
SelectDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
d.季度的第一天
SelectDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
e.上个月的最后一天
Selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
f.去年的最后一天
Selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
g.本月的最后一天
Selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
h.本月的第一个星期一
selectDATEADD(wk,DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
),0)
i.本年的最后一天
Selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))。
*获取表结构[把's
ysobjects'替换成'tablename'即可]
SelectCASEIsNull(I.name,'')
When''Then''
Else'*'
EndasIsPK,
Object_Name(A.id)ast_name,
A.nameasc_name,
IsNull(SubString(M.text,1,254),'')aspbc_init,
T.nameasF_DataType,
CASEIsNull(TYPEPROPERTY(T.name,'Scale'),'')
WHEN''ThenCast(A.precasvarchar)
ELSECast(A.precasvarchar)+','+Cast(A.scaleasvarchar)
ENDasF_Scale,
A.isnullableasF_isNullAble
FROMSyscolumnsasA
JOINSystypesasT
ON(A.xType=T.xUserTypeANDA.Id=Object_id('sysobjects'))
LEFTJOIN(SysIndexesasI
JOINSyscolumnsasA1
ON(I.id=A1.idandA1.id=object_id('sysobjects')and(I.status&;0x800)=0x800ANDA1.colid<=I.keycnt))
ON(A.id=I.idANDA.name=index_col('sysobjects',I.indid,A1.colid))
LEFTJOINSysCommentsasM
ON(M.id=A.cdefaultandObjectProperty(A.cdefault,'IsConstraint')=1)
ORDERBYA.ColidASC
*提取数据库内所有表的字段详细说明的SQL语句
Select
(casewhena.colorder=1thend.nameelse''end)N'表名',
a.colorderN'字段序号',
a.nameN'字段名',
(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''
end)N'标识',
(casewhen(Selectcount(*)
FROMsysobjects
Where(namein
(Selectname
FROMsysindexes
Where(id=a.id)AND(indidin
(Selectindid
FROMsysindexkeys
Where(id=a.id)AND(colidin
(Selectcolid
FROMsyscolumns
Where(id=a.id)AND(name=a.name)))))))AND
(xtype='PK'))>0then'√'else''end)N'主键',
b.nameN'类型',
a.lengthN'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数',
(casewhena.isnullable=1then'√'else''end)N'允许空',
isnull(e.text,'')N'默认值',
isnull(g.[value],'')ASN'字段说明'
FROMsyscolumnsa
leftjoinsystypesb
ona.xtype=b.xusertype
innerjoinsysobjectsd
ona.id=d.idandd.xtype='U'andd.name<>'dtproperties'
leftjoinsyscommentse
ona.cdefault=e.id
leftjoinsyspropertiesg
ona.id=g.idANDa.colid=g.smallid
orderbyobject_name(a.id),a.colorder
*快速获取表test的记录总数[对大容量表非常有效]
快速获取表test的记录总数:
selectrowsfromsysindexeswhereid=object_id('test')andindidin(0,1)
update2setKHXH=(ID+1)\22行递增编号
update[23]setid1='No.'+right('00000000'+id,6)whereidnotlike'No%'//递增
update[23]setid1='No.'+right('00000000'+replace(id1,'No.',''),6)//补位递增
deletefrom[1]where(id%2)=1
奇数
*替换表名字段
update[1]setdomurl=replace(domurl,'Upload/Imgswf/','Upload/Photo/')wheredomurllike'%Upload/Imgswf/%'
*截位
SelectLEFT(表名,5)
熟悉SQLSERVER2000的数据库管理员都知道，其DTS可以进行数据的导入导出，其实，我们也可以使用Transact-SQL语句进行导入导出操作。在Transact-SQL语句中，我
们主要使用OpenDataSource函数、OPENROWSET函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQLSERVER、ACCESS、EXCEL数据转换，详细说明如下：
一、SQLSERVER和ACCESS的数据导入导出
常规的数据导入导出：
使用DTS向导迁移你的Access数据到SQLServer，你可以使用这些步骤:
○1在SQLSERVER企业管理器中的Tools（工具）菜单上，选择DataTransformation
○2Services（数据转换服务），然后选择czdImportData（导入数据）。
○3在ChooseaDataSource（选择数据源）对话框中选择MicrosoftAccessastheSource，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。
○4在ChooseaDestination（选择目标）对话框中，选择MicrosoftOLEDBProviderforSQLServer，选择数据库服务器，然后单击必要的验证方式。
○5在SpecifyTableCopy（指定表格复制）或Query（查询）对话框中，单击Copytables（复制表格）。
○6在SelectSourceTables（选择源表格）对话框中，单击SelectAll（全部选定）。下一步，完成。
Transact-SQL语句进行导入导出：
1.在SQLSERVER里查询access数据:
Select*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\DB.mdb";UserID=Admin;Password=')...表名
2.将access导入SQLserver
在SQLSERVER里运行:
Select*INTOnewtableFROMOPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','DataSource="c:\DB.mdb";UserID=Admin;Password=')...表名
3.将SQLSERVER表里的数据插入到Access表中
在SQLSERVER里运行：
insertintoOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\DB.mdb";UserID=Admin;Password=')...表名(列名1,列名2)select列名1,列名2fromsql表
实例：
insertintoOPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\db.mdb';'admin';'',Test)selectid,namefromTest
InsertINTOOPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\trade.mdb';'admin';'',表名)Select*FROMsqltablename
二、SQLSERVER和EXCEL的数据导入导出
1、在SQLSERVER里查询Excel数据:
Select*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\book1.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...[Sheet1$]
下面是个查询的示例，它通过用于Jet的OLEDB提供程序查询Excel电子表格。
Select*FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\Finance\account.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions
2、将Excel的数据导入SQLserver:
Select*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\book1.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...[Sheet1$]
实例:
Select*intonewtableFROMOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\Finance\account.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions
3、
将SQLSERVER中查询到的数据导成一个Excel文件
T-SQL代码：
EXECmaster..xp_cmdshell'bcp库名.dbo.表名outc:\Temp.xls-c-q-S"servername"-U"sa"-P""'
参数：S是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式
实例:EXECmaster..xp_cmdshell'bcpsaletesttmp.dbo.CusAccountoutc:\temp1.xls-c-q-S"pmserver"-U"sa"-P"sa"'
EXECmaster..xp_cmdshell'bcp"Selectau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryoutC:\authors.xls-c-Sservername-Usa-Ppassword'
在VB6中应用ADO导出EXCEL文件代码：
DimcnAsNewADODB.Connection
cn.open"Driver={SQLServer};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute"master..xp_cmdshell'bcp"Selectcol1,col2FROM库名.dbo.表名"queryoutE:\DT.xls-c-Sservername-Usa-Ppassword'"
4、在SQLSERVER里往Excel插入数据:
insertintoOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource="c:\Temp.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...table1(A1,A2,A3)values(1,2,3)
T-SQL代码：
InsertINTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0','ExtendedProperties=Excel8.0;Datasource=C:\training\inventur.xls')...[Filiale1$](bestand,produkt)VALUES(20,'Test')
总结：利用以上语句，我们可以方便地将SQLSERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便
详细出处参考：jb51.net/article/13666.htm
**************************
就是一个sql语句
在查询分析里面执行execmaster..xp_cmdshell'bcp
settledb.dbo.shanghu--表名（带上数据库名）
out
c:\temp1.xls--你要导出到的execl文件的路径
-c
-q
-s"gnetdata/gnetdata"--可以省略
-u"sa"--用户
-p"密码"'
