精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换

2022-11-12 09:52:46
内容摘要
这篇文章主要为大家详细介绍了精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换，具有一定的参考价值，可以用来参考一下。 对此感兴趣的朋友，看看idc笔记做的技术笔记
文章正文

这篇文章主要为大家详细介绍了精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

*说明：复制表(只复制结构,源表名：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_DATEFROMTABLE1,(SelectX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATEFROM(SelectNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHANDFROMTABLE2WhereTO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X,(SelectNUM,UPD_DATE,STOCK_ONHANDFROMTABLE2WhereTO_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)BWhereA.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))ASDECFROM(Selecta.userper,a.tel,a.standfee,b.telfeedate,b.factrationFROMTELFEESTANDa,TELFEEbWherea.tel=b.telfax)aGROUPBYa.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)asbegindeclare@strvarchar(2000)set@str=''select@str=@str+','+rtrim(role2)froma_testwhere[name]=@contentselect@str=right(@str,len(@str)-1)return@strendgo--调用：select[name],dbo.join_str([name])role2froma_testgroupby[name]--selectdistinctname,dbo.uf_test(name)froma_test*快速比较结构相同的两表结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？============================给你一个测试方法，从northwind中的orders表取数据。select*inton1fromordersselect*inton2fromordersselect*fromn1select*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*fromn1unionselect*fromn2)agroupbyOrderIDhavingcount(*)>1)union--n2中存在但在n1中不存的在10728,10730select*fromn1whereOrderIDnotin(selectOrderIDfromn2)union--n1中存在但在n2中不存的在11000,11001select*fromn2whereOrderIDnotin(selectOrderIDfromn1)*四种方法取表里n到m条纪录：1.selecttopm*into临时表(或表变量)fromtablenameorderbycolumnname--将topm笔插入setrowcountnselect*from表变量orderbycolumnnamedesc2.selecttopn*from(selecttopm*fromtablenameorderbycolumnname)aorderbycolumnnamedesc3.如果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',true4.如果表里有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_distcreateprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))--f_key表示是分组字段﹐即主键字段asbegindeclare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeintegerselect@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'exec(@sql)opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxselect@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_keyif@type=56select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@idif@type=167select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''exec(@sql)fetchcur_rowsinto@id,@maxendclosecur_rowsdeallocatecur_rowssetrowcount0endselect*fromsystypesselect*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---------------------------A69A24B36B25C67C34就是要按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(*)>12、将不重复的记录记入temp1表:inserttemp1select[标志字段id],count(*)from[表名]groupby[标志字段id]havingcount(*)=13、作一个包含所有不重复记录的表：select*intotemp2from[表名]where标志字段idin(select标志字段idfromtemp1)4、删除重复表:delete[表名]5、恢复表：insert[表名]select*fromtemp26、删除临时表:droptabletemp1droptabletemp2================================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_distcreateprocedureup_distinct(@t_namevarchar(30),@f_keyvarchar(30))--f_key表示是分组字段﹐即主键字段asbegindeclare@maxinteger,@idvarchar(30),@sqlvarchar(7999),@typeintegerselect@sql='declarecur_rowscursorforselect'+@f_key+',count(*)from'+@t_name+'groupby'+@f_key+'havingcount(*)>1'exec(@sql)opencur_rowsfetchcur_rowsinto@id,@maxwhile@@fetch_status=0beginselect@max=@max-1setrowcount@maxselect@type=xtypefromsyscolumnswhereid=object_id(@t_name)andname=@f_keyif@type=56select@sql='deletefrom'+@t_name+'where'+@f_key+'='+@idif@type=167select@sql='deletefrom'+@t_name+'where'+@f_key+'='+''''+@id+''''exec(@sql)fetchcur_rowsinto@id,@maxendclosecur_rowsdeallocatecur_rowssetrowcount0endselect*fromsystypesselect*fromsyscolumnswhereid=object_id('a_dist')*行列转换--普通假设有张学生成绩表(CJ)如下NameSubjectResult张三语文80张三数学90张三物理85李四语文85李四数学92李四物理82想变成姓名语文数学物理张三809085李四859282declare@sqlvarchar(4000)set@sql='selectName'select@sql=@sql+',sum(caseSubjectwhen'''+Subject+'''thenResultend)['+Subject+']'from(selectdistinctSubjectfromCJ)asaselect@sql=@sql+'fromtestgroupbyname'exec(@sql)行列转换--合并有表A,idpid111213212231如何化成表B:idpid11,2,321,231创建一个合并的函数createfunctionfmerg(@idint)returnsvarchar(8000)asbegindeclare@strvarchar(8000)set@str=''select@str=@str+','+cast(pidasvarchar)from表Awhereid=@idset@str=right(@str,len(@str)-1)return(@str)Endgo--调用自定义函数得到结果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语句来更改用户的密码修改别人的,需要sysadminroleEXECsp_passwordNULL,'newpassword','User'如果帐号为SA执行EXECsp_passwordNULL,'newpassword',sa*怎么判断出一个表的哪些字段不允许为空？selectCOLUMN_NAMEfromINFORMATION_SCHEMA.COLUMNSwhereIS_NULLABLE='NO'andTABLE_NAME=tablename*如何在数据库里找到含有相同字段的表？a.查已知列名的情况Selectb.nameasTableName,a.nameascolumnnameFromsyscolumnsaINNERJOINsysobjectsbONa.id=b.idANDb.type='U'ANDa.name='你的字段名字'*未知列名查所有在不同表出现过的列名Selecto.nameAstablename,s1.nameAscolumnnameFromsyscolumnss1,sysobjectsoWheres1.id=o.idAndo.type='U'AndExists(Select1Fromsyscolumnss2Wheres1.name=s2.nameAnds1.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_isNullAbleFROMSyscolumnsasAJOINSystypesasTON(A.xType=T.xUserTypeANDA.Id=Object_id('sysobjects'))LEFTJOIN(SysIndexesasIJOINSyscolumnsasA1ON(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))LEFTJOINSysCommentsasMON(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(*)FROMsysobjectsWhere(namein(SelectnameFROMsysindexesWhere(id=a.id)AND(indidin(SelectindidFROMsysindexkeysWhere(id=a.id)AND(colidin(SelectcolidFROMsyscolumnsWhere(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'字段说明'FROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertypeinnerjoinsysobjectsdona.id=d.idandd.xtype='U'andd.name<>'dtproperties'leftjoinsyscommentseona.cdefault=e.idleftjoinsyspropertiesgona.id=g.idANDa.colid=g.smallidorderbyobject_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（选择目标）对话框中，选择MicrosoftOLE　DBProviderforSQL　Server，选择数据库服务器，然后单击必要的验证方式。○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,namefromTestInsertINTOOPENROWSET('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')...xactions2、将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')...xactions3、将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.Connectioncn.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代码：InsertINTOOPENDATASOURCE('Microsoft.JET.OLEDB.4.0','ExtendedProperties=Excel8.0;Datasource=C:\training\inventur.xls')...[Filiale1$](bestand,produkt)VALUES(20,'Test')总结：利用以上语句，我们可以方便地将SQLSERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！

注：关于精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换的内容就先介绍到这里，更多相关文章的可以留意

代码注释
上一篇SQL Server连接中三个常见的错误分析下一篇SQL Server 安装时的1045错误

作者：喵哥笔记

IDC笔记

学的不仅是技术，更是梦想！