【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“sql面试技巧大全”一文，供大家参考学习
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：rightouterjoin:右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
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)
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(sele
cttopxxx-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)
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)
截位
SELECTLEFT(表名,5)
（MSSQLServer）SQL语句导入导出大全
/*******导出到excel
EXECmaster..xp_cmdshell'bcpSettleDB.dbo.shanghuoutc:\temp1.xls-c-q-S"GNETDATA/GNETDATA"-U"sa"-P""'
/***********导入Excel
SELECT*
FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0',
'DataSource="c:\test.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions
SELECTcast(cast(科目编号asnumeric(10,2))asnvarchar(255))+''转换后的别名
FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0',
'DataSource="c:\test.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions
select*fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel5.0;HDR=YES;DATABASE=c:\Book1.xls',Sheet1$)
HDR=YES;Excel第一行当成标题行
HDR=NO;第一行不当成标题行
/**导入文本文件
EXECmaster..xp_cmdshell'bcp"dbname..tablename"inc:\DT.txt-c-Sservername-Usa-Ppassword'
/**导出文本文件
EXECmaster..xp_cmdshell'bcp"dbname..tablename"outc:\DT.txt-c-Sservername-Usa-Ppassword'
或
EXECmaster..xp_cmdshell'bcp"Select*fromdbname..tablename"queryoutc:\DT.txt-c-Sservername-Usa-Ppassword'
导出到TXT文本，用逗号分开
execmaster..xp_cmdshell'bcp"库名..表名"out"d:\tt.txt"-c-t,-Usa-Ppassword'
BULKINSERT库名..表名
FROM'c:\test.txt'
WITH(
FIELDTERMINATOR=';',
ROWTERMINATOR='\n'
)
--/*dBaseIV文件
select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBaseIV;HDR=NO;IMEX=2;DATABASE=C:\','select*from[客户资料4.dbf]')
--*/
--/*dBaseIII文件
select*from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBaseIII;HDR=NO;IMEX=2;DATABASE=C:\','select*from[客户资料3.dbf]')
--*/
--/*FoxPro数据库
select*
fromopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:\',
'select*from[aa.DBF]')
--*/
/**************导入DBF文件****************/
select*fromopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select*fromcustomerwherecountry!="USA"orderbycountry')
go
/*****************导出到DBF***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句
insertintoopenrowset('MSDASQL',
'Driver=MicrosoftVisualFoxProDriver;SourceType=DBF;SourceDB=c:\',
'select*from[aa.DBF]')
select*from表
说明:
SourceDB=c:\指定foxpro表所在的文件夹
aa.DBF指定foxpro表的文件名.
/*************导出到Access********************/
insertintoopenrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)select*from数据库名..B表
/*************导入Access********************/
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实现的存储过程
/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
execfile2table'zj','','','xzkh_sa..地区资料','c:\zj.txt',1
----导出整个数据库
execfile2table'zj','','','xzkh_sa','C:\docman',1
--导入调用示例
----导入单个表
execfile2table'zj','','','xzkh_sa..地区资料','c:\zj.txt',0
----导入
整个数据库
execfile2table'zj','','','xzkh_sa','C:\docman',0
*/
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
/**********************Excel导到Txt****************************************/
想用
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将文件导入导出到数据库的存储过程:
/*--bcp-二进制文件的导入导出
支持image,text,ntext字段的导入/导出
image适合于二进制文件;text,ntext适合于文本数据文件
注意:导入时,将覆盖满足条件的所有行
导出时,将把所有满足条件的行也出到指定文件中
此存储过程仅用bcp实现
邹建2003.08-----------------*/
/*--调用示例
--数据导出
execp_binaryIO'zj','','','acc_演示数据..tb','img','c:\zj1.dat'
--数据导出
execp_binaryIO'zj','','','acc_演示数据..tb','img','c:\zj1.dat','',0
--*/
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
/**导入文本文件
EXECmaster..xp_cmdshell'bcp"dbname..tablename"inc:\DT.txt-c-Sservername-Usa-Ppassword'
改为如下，不需引号
EXECmaster..xp_cmdshell'bcpdbname..tablenameinc:\DT.txt-c-Sservername-Usa-Ppassword'
/**导出文本文件
EXECmaster..xp_cmdshell'bcp"dbname..tablename"outc:\DT.txt-c-Sservername-Usa-Ppassword'
此句需加引号
sql常见面试题
sql理论题
1．触发器的作用？
答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
2。什么是存储过程？用什么来调用？
答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果
某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。
3。索引的作用？和它的优点缺点是什么？
答：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。
3。什么是内存泄漏？
答：一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free或者delete释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。
4。维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？
答：我是这样做的，尽可能使用约束，如check,主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。
5。什么是事务？什么是锁？
答：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。
锁：在所以的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。
6。什么叫视图？游标是什么？
答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
7。为管理业务培训信息，建立3个表：
S(S#,SN,SD,SA)S
#,SN,SD,SA分别代表，学员，所属单位，学员年龄
C(C#,CN)C#,CN分别代表课程编号，课程名称
SC(S#,C#,G)S#,C#,G分别代表，所选的课程编号，学习成绩
（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员和?
答案：selects#,snfromswhereS#in(selectS#fromc,scwherec.c#=sc.c#andcn=’税收基础’)
(2)使用标准SQL嵌套语句查询选修课程编号为’C2’的学员和所属单位?
答：selectsn,sdfroms,scwheres.s#=sc.s#andsc.c#=’c2’
(3)使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员和所属单位?
答：selectsn,sdfromswheres#notin(selects#fromscwherec#=’c5’)
(4)查询选修了课程的学员人数
答：select学员人数=count(distincts#)fromsc
(5)查询选修课程超过5门的学员和所属单位?
答：selectsn,sdfromswheres#in(selects#fromscgroupbys#havingcount(distinctc#)>5)
是查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：
selecttop10*fromAwhereID>(selectmax(ID)from(selecttop30IDfromAorderbyA
)T)orderbyA
要求是查询表A中存在ID重复三次以上的记录,完整的查询语句如下：
select*from(selectcount(ID)ascountfromtablegroupbyID)TwhereT.count>3
createtabletesttable1
(
idintIDENTITY,
departmentvarchar(12)
)
select*fromtesttable1
insertintotesttable1values('设计')
insertintotesttable1values('市场')
insertintotesttable1values('售后')
/*
结果
iddepartment
1设计
2市场
3售后
*/
createtabletesttable2
(
idintIDENTITY,
dptIDint,
namevarchar(12)
)
insertintotesttable2values(1,'张三')
insertintotesttable2values(1,'李四')
insertintotesttable2values(2,'王五')
insertintotesttable2values(3,'彭六')
insertintotesttable2values(4,'陈七')
/*
用一条SQL语句，怎么显示如下结果
iddptIDdepartmentname
11设计张三
21设计李四
32市场王五
43售后彭六
54黑人陈七
*/
答案是：
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命令中插入有害代码的难度。
⑷检查用户输入的合法性，确信输入的内容只包含合法的数据。数据检查应当在客户端和服务器端都执行——之所以要执行服务器端验证，是为了弥补客户端验证机制脆弱的安全性。
在客户端，攻击者完全有可能获得网页的源代码，修改验证合法性的脚本（或者直接删除脚本），然后将非法内容通过修改后的表单提交给服务器。因此，要保证验证操作确实已经执行，唯一的办法就是在服务器端也执行验证。你可以使用许多内建的验证对象，例如RegularExpressionValidator，它们能够自动生成验证用的客户端脚本，当然你也可以插入服务器端的方法调用。如果找不到现成的验证对象，你可以通过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,(SelectSNOFROMSCWhereSCGRADE<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)
