数据库SQL查询语句大全

经典SQL查询语句大全

一、基础1、说明：创建数据库CREATEDATABASEdatabase-name2、说明：删除数据库dropdatabasedbname3、说明：备份sqlserver--创建备份数据的deviceUSEmasterEXECsp_addumpdevice'disk','testBack','c:\mssql7backup\MyNwind_1.dat'—开始备份BACKUPDATABASEpubsTOtestBack4、说明：创建新表createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)根据已有的表创建新表：A：createtabletab_newliketab_old(使用旧表创建新表)B:createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly5、说明：删除新表droptabletabname6、说明：增加一个列Altertabletabnameaddcolumncoltype注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。7、说明：添加主键：Altertabletabnameaddprimarykey(col)说明：删除主键：Altertabletabnamedropprimarykey(col)8、说明：创1/77建索弓]:create[unique]indexidxnameontabname(col…,)删除索引：dropindexidxname注：索引是不可更改的，想更改必须删除重新建。9、说明：创建视图：createviewviewnameasselectstatement删除视图：dropviewviewname10、说明：几个简单的基本的sql语句选择：select*fromtabletwhere围插入：insertintotable1(field1,field2)values(value1,value2)删除：deletefromtabletwhere围更新：updatetabletsetfield1=value1where围查找：select*fromtable1wherefield1like'%value1%'——like的语法很精妙，查资料！排序：select*fromtable1orderbyfield1,field2[desc]总数：selectcount(field1)astotalcountfromtable1求和：selectsum(field1)assumvaluefromtable1平均：selectavg(field1)asavgvaluefromtable1最大：selectmax(field1)asmaxvaluefromtable1最小：selectmin(field1)asminvaluefromtable111、说明：几个高级查询运算词A：UNION运算符UNION运算符通过组合其他两个结果表(例如TABLE1和TABLE2)并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时(即UNIONALL)，不消除重复行。两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。B：EXCEPT运算符2/77EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。当ALL随EXCEPT一起使用时（EXCEPTALL），不消除重复行。C：INTERSECT运算符INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。当ALL随INTERSECT一起使用时（INTERSECTALL），不消除重复行。注：使用运算词的几个查询结果行必须是一致的。12、说明：使用外连接A、left（outer）join:左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。SQL:selecta.a,a,b,a,c,b,c,b,d,b.ffromaLEFTOUTJOINbONa.a=b.cB：right（outer）join:右外连接（右连接）：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。C:full/cross（outer）join：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。12、分组:Groupby:一表，一旦分组完成后，查询后只能得到组相关的信息。组相关的信息：（统计信息）count,sum,max,min,avg分组的标准）在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据在selecte统计函数中的字段，不能和普通的字段放在一起；3/7713、对数据库进行操作：分离数据库：sp_detach_db;附加数据库：sp_attach_db后接表明，附加需要完整的路径名14.如何修改数据库的名称:sp_renamedb'old_name','new_name'二、提升1、说明：复制表（只复制结构，源表名：a新表名：b）（Access可用）法一：select*intobfromawhere1<>1（仅用于SQlServer）法二：selecttop0*intobfroma2、说明：拷贝表（拷贝数据,源表名：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）4/775、说明：显示文章、提交人和最后回复时间selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b6、说明：外连接查询(表名1：a表名2：b)selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c7、说明：在线视图查询(表名1：a)select*from(SELECTa,b,cFROMa)Twheret.a>1;8、说明：between的用法,between限制查询数据图时包括了边界值,notbetween不包括select*fromtablelwheretimebetweentimelandtime2selecta,b,c,fromtablelwhereanotbetween数值1and数值29、说明：in的使用方法select*fromtablelwherea[not]in('值1‘，’值2‘，’值4‘，‘值6')10、说明：两关联表，删除主表中已经在副表中没有的信息deletefromtablelwherenotexists(select*fromtable2wheretable1.field1=table2.field1)5/7711、说明：四表联查问题：select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere 12、说明：日程安排提前五分钟提醒SQL:select*from日程安排wheredatediff('minute',f开始时间，getdate())>513、说明：一条sql语句搞定数据库分页selecttop10b.*from(selecttop20主键字段，排序字段from表名orderby排序字段desc)a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段具体实现：关于数据库分页：declarestartint,endintsqlnvarchar(600)setsql=’selecttop,+str(end-start+1)+’+fromTwhereridnotin(selecttop,+str(str-1)+,RidfromTwhereRid>-1)’execsp_executesqlsql注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免top的字段如果是逻辑索引的，查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引)6/7714、说明：前10条记录selecttop10*formtablelwhere围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、说明：删除重复记录1),deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2，…)2),selectdistinct*intotempfromtablenamedeletefromtablename7/77insertintotablenameselect*fromtemp评价：这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段altertabletablename--添加一个自增歹“addcolumn_bintidentity(1,1)deletefromtablenamewherecolumn_bnotin(selectmax(column_b)fromtablenamegroupbycolumn1,column2….)altertabletablenamedropcolumncolumn_b20、说明：列出数据库里所有的表名selectnamefromsysobjectswheretype='U'//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电脑A18/77电脑A1光盘B2光盘A2手机B3手机C323、说明：初始化表tablelTRUNCATETABLEtable124、说明：选择从10到15的记录selecttop5*from(selecttop15*fromtableorderbyidasc)tabl©_别名orderbyiddesc三、技巧1、1=1，1=2的使用，在SQL语句组合时用的较多“where1=1”是表示选择全部 “where1=2”全部不选，如：ifstrWhere!=''beginsetstrSQL='selectcount(*)asTotalfrom['+tblName+']where'+strWhereend9/77elsebeginsetstrSQL='selectcount(*)asTotalfrom['+tblName+']'end我们可以直接写成错误！未找到目录项。setstrSQL='selectcount(*)asTotalfrom['+tblName+']where1=1安定'+strWhere2、收缩数据库--重建索引DBCCREINDEXDBCCINDEXDEFRAG--收缩数据和日志DBCCSHRINKDBDBCCSHRINKFILE3、压缩数据库dbccshrinkdatabase(dbname)4、转移数据库给新用户以已存在用户权限execsp_change_users_login'update_one','newname','oldname'go10/775、检查备份集RESTOREVERIFYONLYfromdisk='E:\dvbbs.bak'6、修复数据库ALTERDATABASE[dvbbs]SETSINGLE_USERGODBCCCHECKDB('dvbbs',repair_allow_data_loss)WITHTABLOCKGOALTERDATABASE[dvbbs]SETMULTI_USERGO7、日志清除SETNOCOUNTONDECLARELogicalFileNamesysname,MaxMinutesINT,NewSizeINTUSEtablename--要操作的数据库名SELECTLogicalFileName='tablename_log',一日志文件名MaxMinutes=10,--Limitontimeallowedtowraplog.NewSize=1--你想设定的日志文件的大小®)11/77Setup/initializeDECLAREOriginalSizeintSELECTOriginalSize=sizeFROMsysfilesWHEREname=LogicalFileNameSELECT'OriginalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),OriginalSize)+'8Kpagesor'+CONVERT(VARCHAR(30),(OriginalSize*8/1024))+'MB'FROMsysfilesWHEREname=LogicalFileNameCREATETABLEDummyTrans(DummyColumnchar(8000)notnull)DECLARECounterINT,StartTimeDATETIME,TruncLogVARCHAR(255)SELECTStartTime=GETDATE(),TruncLog='BACKUPLOG'+db_name()+'WITHTRUNCATE_ONLY'DBCCSHRINKFILE(LogicalFileName,NewSize)EXEC(TruncLog)--Wrapthelogifnecessary.12/77WHILEMaxMinutes>DATEDIFF(mi,StartTime,GETDATE())一timehasnotexpiredANDOriginalSize=(SELECTsizeFROMsysfilesWHEREname=LogicalFileName)AND(OriginalSize*8/1024)>NewSizeBEGIN-Outerloop.SELECTCounter=0WHILE((Counter<OriginalSize/16)AND(Counter<50000))BEGIN-updateINSERTDummyTransVALUES('FillLog')DELETEDummyTransSELECTCounter=Counter+1ENDEXEC(TruncLog)ENDSELECT'FinalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),size)+'8Kpagesor'+CONVERT(VARCHAR(30),(size*8/1024))+'MB'FROMsysfilesWHEREname=LogicalFileNameDROPTABLEDummyTransSETNOCOUNTOFF13/778、说明：更改某个表execsp_changeobjectowner'tablename','dbo'9、存储更改全部表CREATEPROCEDUREdbo.User_ChangeObjectOwnerBatchOldOwnerasNVARCHAR(128),NewOwnerasNVARCHAR(128)ASDECLAREName asNVARCHAR(128)DECLAREOwner asNVARCHAR(128)DECLAREOwnerNameasNVARCHAR(128)DECLAREcurObjectCURSORFORselect'Name'=name,'Owner'=user_name(uid)fromsysobjectswhereuser_name(uid)=OldOwnerorderbynameOPENcurObjectFETCHNEXTFROMcurObjectINTOName,OwnerWHILE(FETCH_STATUS=0)BEGIN14/77ifOwner=OldOwnerbeginsetOwnerName=OldOwner+'.'+rtrim(Name)execsp_changeobjectownerOwnerName,NewOwnerend--selectname,NewOwner,OldOwnerFETCHNEXTFROMcurObjectINTOName,OwnerENDclosecurObjectdeallocatecurObjectGO10、SQLSERVER中直接循环写入数据declareiintseti=1whilei<30begininsertintotest(userid)values(i)seti=i+1end案例：有如下表，要求就裱中所有没有与格的成^，在每次增是0.1的基磁上，使他佗刚好与格：NamescoreZhangshan80Lishi59Wangwu50Songquan6915/77while((selectmin(score)fromtb_table)<60)beginupdatetb_tablesetscore=score*1.01wherescore<60if(selectmin(score)fromtb_table)>60breakelsecontinueend数据开发-经典.按姓氏笔画排序：Select*FromTableNameOrderByCustomerNameCollateChinese_PRC_Stroke_ci_as〃从少到多.数据库加密：selectencrypt('原始密码’)selectpwdencryptC原始密码’)selectpwdcompare('原始密码‘，‘加密后密码’)=1一一样；否则不一样encrypt('原始密码’)selectpwdencryptC原始密码’)selectpwdcompareC原始密码‘，‘加密后密码’)=1一一样；否则不一样.取回表中字段：declarelistvarchar(1000),sqlnvarchar(1000)selectlist=list+','+fromsysobjectsa,syscolumnsbwherea.id=b.idand='表A'16/77setsql='select'+right(list,len(list)-1)+'from表Aexec(sql).查看硬盘分区：EXECmaster..xp_fixeddrives.比较A,B表是否相等：if(selectchecksum_agg(binary_checksum(*))fromA)(selectchecksum_agg(binary_checksum(*))fromB)print'相等’elseprint'不相等’.杀掉所有的事件探察器进程：DECLAREhcforeachCURSORGLOBALFORSELECT'kill'+RTRIM(spid)FROMmaster.dbo.sysprocessesWHEREprogram_nameIN('SQLprofiler',N'SQL事件探查器’)EXECsp_msforeach_worker'?,.记录搜索：开头到N条记录SelectTopN*From表N到M条记录（要有主索引ID）17/77SelectTopM-N*From表WhereIDin(SelectTopMIDFrom表)OrderbyIDDescN到结尾记录SelectTopN*From表OrderbyIDDesc案例例如1：一表有一万多条记录，表的第一个字段RecID是自增长字段，写一个SQL语句，找出表的第31到第40个记录。selecttop10recidfromAwhererecidnotin(selecttop30recidfromA)分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。selecttop10recidfromAwhere 是从索引中查找，而后面的selecttop30recidfromA则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。解决方案1，用orderbyselecttop30recidfromAorderbyricid如果该字段不是自增长，就会出现问题2，在那个子查询中也加条件：selecttop30recidfromAwhererecid>-1例2查询表中的最后以条记录并不知道这个表共有多少数据，以与表结构。sets='selecttop1*fromTwherepidnotin(selecttop'+str(count-1)+'pidfromT)'printsexecsp_executesqls9：获取当前数据库中的所有用户表selectNamefromsysobjectswherextype='u'andstatus>=010:获取某一个表的所有字段selectnamefromsyscolumnswhereid=object_id('表名’)selectnamefromsyscolumnswhereidin(selectidfromsysobjectswheretype='u'andname='表名’)两种方式的效果一样18/7711：查看与某一个表相关的视图、存储过程、函数5©1©9a.*fromsysobjectsa,syscommentsbwherea.id=b.idandb.textlike'%表名始:查看当前数据库中所有存储过程selectnameas存储过程名称fromsysobjectswherextype='P'：查询用户创建的所有数据库select*frommaster..sysdatabasesDwheresidnotin(selectsidfrommaster..sysloginswherename='sa')或者selectdbid,nameASDB_NAMEfrommaster..sysdatabaseswheresid<>0x01:查询某一个表的字段和数据类型selectcolumn_name,data_typefrominformation_schema.columnswheretable_name='表名’:不同服务器数据库之间的数据操作--创建服务器execsp_addlinkedserver'ITSV','','SQLOLEDB','远程服务器名或ip地址’execsp_addlinkedsrvlogin'ITSV','false',null,'用户名'，'密码'--查询示例select*fromITSV.数据库名.dbo.表名--导入示例select*into表fromITSV.数据库名.dbo.表名--以后不再使用时删除服务器execsp_dropserver'ITSV','droplogins'19/77一连接远程/局域网数据(openrowset/openquery/opendatasource)一1、openrowset--查询示例select*fromopenrowset（'SQLOLEDB','sql服务器名‘；‘用户名’；‘密码‘，数据库名.dbo.表名）--生成本地表select*into表fromopenrowset（'SQLOLEDB','sql服务器名‘；‘用户名‘；‘密码’，数据库名.dbo.表名）一把本地表导入远程表insertopenrowset（'SQLOLEDB','sql服务器名‘；‘用户名';'密码',数据库名.dbo.表名）select*from本地表--更新本地表updatebsetb.歹ijA=a.歹4Afromopenrowset（'SQLOLEDB','sql服务器名‘；‘用户名‘；'密码',数据库名.dbo.表名）asainnerjoin本地表bona.column1=b.column1--openquery用法需要创建一个连接-首先创建一个连接创建服务器execsp_addlinkedserver'ITSV','','SQLOLEDB','远程服务器名或ip地址’-查询select*20/77FROMopenquery（ITSV,'SELECT*FROM数据库.dbo.表名’）一把本地表导入远程表insertopenquery（ITSV,'SELECT*FROM数据库.dbo.表名'）select*from本地表-更新本地表updatebsetb.歹ijB=a.歹4BFROMopenquery（ITSV,'SELECT*FROM数据库.dbo.表名'）asainnerjoin本地表bona.歹4A=b.歹4A-3、opendatasource/openrowsetSELECT*FROMopendatasource（'SQLOLEDB','DataSource=ip/ServerName;UserID=登陆名；Password：密码'）.test.dbo.roy_ta一把本地表导入远程表insertopendatasource（'SQLOLEDB','DataSource=ip/ServerName;UserID=登陆名;Password：密码’）.数据库.dbo.表名select*from本地表21/77SQLServer基本函数SQLServer基本函数1.字符串函数长度与分析用1,datalength(Char_expr)返回字符串包含字符数，但不包含后面的空格2,substring(expression,start,length)取子串，字符串的下标是从“1"，start为起始位置,^吧均为字符串长度，实际应用中以len(expression)取得其长度3,right(char_expr,int_expr)返回字符串右边第int_expr个字符，还用left于之相反4,isnull(check_expression,replacement_value)如果check_expression卷空，即返回replacement_value的值，不卷空，就返回check_expression字符操作类22/775,Sp_addtype自定羲数握J真型例如：EXECsp_addtypebirthday,datetime,,NULL,6,setnocount{on|off}使返回的结果中不包含有关受Transact-SQL语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SETNOCOUNT设置是在执行或运行时设置，而不是在分析时设置。SETNOCOUNT为ON时，不返回计数(表示受Transact-SQL语句影响的行数)。SETNOCOUNT为OFF时，返回计数常识在SQL查询中：from后最多可以跟多少表或视图：256在SQL语句中出现Orderby,查询时，先排序，后取在SQL中，一个字段的最大容量是8000，而对于nvarchar(4000),由于nvarchar是Unicode码。SQLServer2000同步复制技术实现步骤23/77一、预备工作1.发布服务器，订阅服务器都创建一个同名的windows用户，并设置一样的密码,做为发布快照文件夹的有效访问用户-管理工具-计算机管理-用户和组-右键用户-新建用户一建立一个隶属于administrator组的登陆windows的用户（SynUser）2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作：我的电脑一D:\新建一个目录，名为：PUB-右键这个新建的目录-属性--共享-选择"共享该文件夹"--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户（SynUser）具有对该文件夹的所有权限-确定3.设置SQL代理（SQLSERVERAGENT）服务的启动用户（发布/订阅服务器均做此设置）开始--程序--管理工具--服务-右键SQLSERVERAGENT-属性--登陆--选择"此账户"-输入或者选择第一步中创建的windows登录用户名（SynUser）--"密码"中输入该用户的密码4.设置SQLServer身份验证模式，解决连接时的权限问题（发布/订阅服务器均做此设置）企业管理器-右键SQL实例一属性-安全性--身份验证一选择"SQLServer和Windows"-确定.在发布服务器和订阅服务器上互相注册企业管理器一右键SQLServer组一新建SQLServer注册...-下一步--可用的服务器中，输入你要注册的远程服务器名--添加-下一步一连接使用，选择第二个"SQLServer身份验证"-下一步--输入用户名和密码（SynUser）-下一步一选择SQLServer组，也可以创建一个新组-下一步--完成.对于只能用IP,不能用计算机名的，为其注册服务器别名（此步在实施中没用到）（在连接端配置，比如，在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP）开始一程序—MicrosoftSQLServer一客户端网络实用工具--别名--添加--网络库选择"tcp/ip"--服务器别名输入SQL服务器名24/77--连接参数一服务器名称中输入SQL服务器ip地址--如果你修改了SQL的端口，取消选择"动态决定端口"，并输入对应的端口号二、正式配置1、配置发布服务器打开企业管理器，在发布服务器(B、C、D)上执行以下步骤：(1)从［工具］下拉菜单的［复制］子菜单中选择［配置发布、订阅服务器和分发］出现配置发布和分发向导(2)［下一步］选择分发服务器可以选择把发布服务器自己作为分发服务器或者其他sql的服务器(选择自己)(3)［下一步］设置快照文件夹采用默认\\servername\Pub(4)［下一步］自定义配置可以选择：是，让我设置分发数据库属性启用发布服务器或设置发布设置否，使用下列默认设置(推荐)(5)［下一步］设置分发数据库名称和位置采用默认值(6)［下一步］启用发布服务器选择作为发布的服务器(7)［下一步］选择需要发布的数据库和发布类型(8)［下一步］选择注册订阅服务器(9)［下一步］完成配置2、创建出版物发布服务器B、C、D上(1)从［工具］菜单的［复制］子菜单中选择［创建和管理发布］命令(2)选择要创建出版物的数据库，然后单击［创建发布］(3)在［创建发布向导］的提示对话框中单击［下一步］系统就会弹出一个对话框。对话框上的容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)(4)单击［下一步］系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER允许在不同的数据库如orACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQLSERVER2000"的数据库服务器(5)单击［下一步］系统就弹出一个定义文章的对话框也就是选择要出版的表注意：如果前面选择了事务发布则再这一步中只能选择带有主键的表(6)选择发布名称和描述(7)自定义发布属性向导提供的选择：是我将自定义数据筛选,启用匿名订阅和或其他自定义属性否根据指定方式创建发布(建议采用自定义的方式)(8)［下一步］选择筛选发布的方式(9)［下一步］可以选择是否允许匿名订阅1)如果选择署名订阅，则需要在发布服务器上添加订阅服务器方法：［工具］-＞［复制］-＞［配置发布、订阅服务器和分发的属性］-＞［订阅服务器］中添加否则在订阅服务器上请求订阅时会出现的提示：改发布不允许匿名订阅如果仍然需要匿名订阅则用以下解决办法［企业管理器］-＞［复制］-＞［发布容］-＞［属性］-＞［订阅选项］选择允许匿名请求订阅2)如果选择匿名订阅，则配置订阅服务器时不会出现以上提示25/77(10)［下一步］设置快照代理程序调度(11)［下一步］完成配置当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库有数据srv1.库名..author有字段：id,name,phone,srv2.库名..author有字段：id,name,telphone,adress要求：srv1.库名..author增加记录则srv1.库名..author记录增加srv1.库名..author的phone字段更新，则srv1.库名..author对应字段telphone更新一米/-大致的处理步骤-1.在srv1上创建连接服务器，以便在srv1中操作srv2,实现同步execsp_addlinkedserver'srv2','','SQLOLEDB','srv2的sql实例名或ip'execsp_addlinkedsrvlogin'srv2','false',null,'用户名‘，‘密码’go-2.在srv1和srv2这两台电脑中，启动msdtc(分布式事务处理服务)，并且设置为自动启动。我的电脑--控制面板--管理工具--服务--右键DistributedTransactionCoordinator--属性--启动--并将启动类型设置为自动启动go-然后创建一个作业定时调用上面的同步处理存储过程就行了企业管理器-管理一SQLServer代理-右键作业--新建作业--"常规"项中输入作业名称--"步骤"项--新建--"步骤名"中输入步骤名--"类型"中选择"Transact-SQL脚本(TSQL)"--"数据库"选择执行命令的数据库一"命令"中输入要执行的语句：execp_process--确定--"调度"项--新建调度--"名称"中输入调度名称--"调度类型"中选择你的作业执行安排--如果选择"反复出现"26/77--点"更改"来设置你的时间安排然后将SQLAgent服务启动，并设置为自动启动，否则你的作业不会被执行设置方法：我的电脑一控制面板一管理工具一服务一右键SQLSERVERAGENT一属性一启动类型--选择"自动启动"--确定.-3.实现同步处理的方法2,定时同步-在srv1中创建如下的同步处理存储过程createprocp_processas-更新修改过的数据updatebsetname=,telphone=i.telphonefromsrv2.库名.dbo.authorb,authoriwhereb.id=i.idand(<>orb.telphone<>i.telphone)-插入新增的数据insertsrv2.库名.dbo.author(id,name,telphone)selectid,name,telphonefromauthoriwherenotexists(select*fromsrv2.库名.dbo.authorwhereid=i.id)-删除已经删除的数据(如果需要的话)deletebfromsrv2.库名.dbo.authorbwherenotexists(select*fromauthorwhereid=b.id)go27/77SQL查询语句关键字方法distinct关键字显示没有重复记录的商品名称，商品价格和商品类别列表。selectdistinctware_name,pricefromt_ware;使用计算列查询所有商品价格提高20%后的价格。selectware_id,ware_name,price*1.2fromt_ware;列的别名a）不使用asselectware_id,ware_name,price*1.2asprice_raisefromt_ware;b）使用asselectware_id,ware_name,price*1.2price_raisefromt_ware;28/77使用逻辑表达式not显示商品价格不大于100的商品selectware_id,ware_name,price,category_idfromt_warewherenotprice>100;and显示商品价格大于100且商品类别编号为5的商品selectware_id,ware_name,price,category_idfromt_warewherenotprice>100;or显示商品类别编号为5或6或7的商品selectware_id,ware_name,price,category_idfromt_warewherecategory_id=5orcategory_id=6orcategory_id=7;使用between关键字显示商品价格在200元至1000元之间的商品(留心一下，是半开区间还是封闭区间？)selectware_id,ware_name,price,category_idfromt_warewherepricebetween200and1000;29/77使用in关键字显示商品类别为5,6,7且价格不小于200元的商品selectware_id,ware_name,price,category_idfromt_warewherecategory_idin(5,6,7)andprice>=200;使用like子句进行模糊查询%(百分号)表示0到n个任意字符selectware_id,ware_name,price,category_idfromt_warewhereware_namelike'%纯棉%;_(下划线)表示单个的任意字符selectware_id,ware_name,price,category_idfromt_warewhereware_namelike'%长袖_恤%;转义字符escape的使用selectware_id,ware_name,price,category_idfromt_warewhereware_namelike'%\%%'escape'\';30/77使用orderby给数据排序select* from t_ware_category where parent_id=0 order by seq;select* from t_ware_category where parent_id=0 order by seqasc;select* from t_ware_category where parent_id=0 order by seqdesc;rownuma)查询前20条商品记录selectware_id,ware_name,pricefromt_warewhererownum<=20;b)查询第11条至第20条记录selectware_id,ware_name,pricefromt_warewhererownum<=10andware_idnotin(selectware_idfromt_warewhererownum<=10);常用统计函数sumO返回一个数字列或计算列的总和selectsum(price)fromt_ware;avg()对一个数字列或计算列求平均值31/77minO返回一个数字列或一个数字表达式的最小值max()返回一个数字列或一个数字表达式的最大值811玳0返回满面足select语句中指定的条件的记录值多表查询和笛卡儿乘积查询商品编号，商品名称，商品价格和商品类别名称selectt_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_name from t_ware,t_ware_category wheret_ware.category_id=t_ware_category.category_id;使用joina)左连接select32/77t_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_namefromt_wareleftjoint_ware_categoryont_ware.category_id=t_ware_category.category_id;selectw.ware_id,w.ware_name,w.price,wc.category_namefromt_warewleftjoint_ware_categorywconw.category_id=wc.category_id;b)右连接selectt_ware.ware_id,t_ware.ware_name,t_ware.price,t_ware_category.category_namefromt_wareleftjoint_ware_categoryont_ware.category_id=t_ware_category.category_id;使用unionselectware_id,ware_namefromt_warewhereware_namelike'%T恤％'unionselectware_id,ware_namefromt_warewhereware_namelike'%手提包%使用groupbya)统计每个二级类别下有多少商品，以与商品总价值selectw.category_id,wc.category_name,count(w.ware_id),sum(w.price)fromt_warewleftjoint_ware_categorywcon33/77w.category_id=wc.category_idgroupbyw.category_id,wc.category_name;b)统计每个一级类别下有多少商品，以与商品总价值selectwc2.category_id,wc2.category_name,sum(w.price)fromt_warewleftjoint_ware_categorywconw.category_id=wc.category_idleftjoint_ware_categorywc2onwc.parent_id=wc2.category_idgroupbywc2.category_id,wc2.category_name;使用having对结果进行筛选selectw.category_id,wc.category_name,count(w.ware_id),sum(w.price)fromt_warewleftjoint_ware_categorywconw.category_id=wc.category_idgroupbyw.category_id,wc.category_namehavingsum(w.price)>1000;34/77SQL查询语句精华使用简要一、简单查询简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分另U说明所查询列、查询的表或视图、以与搜索条件等。例如，下面的语句查询testtable表中为“三”的nickname字段和email字段。35/77SELECTnickname,emailFROMtesttableWHEREname='三'（一）选择列表选择列表（select_list）指出所查询列，它可以是一组列名列表、星号、表达式、变量（包括局部变量和全局变量）等构成。1、选择所有列例如，下面语句显示testtable表中所有列的数据：SELECT*FROMtesttable2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序一样。例如：SELECTnickname,emailFROMtesttable3、更改列标题在选择列表中，可重新指定列标题。定义格式为：36/77列标题=列名列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：SELECT昵称=nickname,电子=emailFROMtesttable4、删除重复行SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。5、限制返回的行数使用TOPn[PERCENT]选项限制返回的数据行数，TOPn说明返回n行，而TOPnPERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例如：SELECTTOP2*FROMtesttableSELECTTOP20PERCENT*FROMtesttable37/77（二）FROM子句FROM子句指定SELECT语句查询与与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定：SELECTusername,citytable.cityidFROMusertable,citytableWHEREusertable.cityid=citytable.cityid在FROM子句中可用以下两种格式为表或视图指定别名：表名as别名表名别名（二）FROM子句FROM子句指定SELECT语句查询与与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使38/77用对象名限定这些列所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定：SELECTusername,citytable.cityidFROMusertable,citytableWHEREusertable.cityid=citytable.cityid在FROM子句中可用以下两种格式为表或视图指定别名：表名as别名表名别名例如上面语句可用表的别名格式表示为：SELECTusername,b.cityidFROMusertablea,citytablebWHEREa.cityid=b.cityidSELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。例如：SELECTa.au_fname+a.au_lnameFROMauthorsa,titleauthorta(SELECTtitle_id,titleFROMtitlesWHEREytd_sales>1000039/77）AStWHEREa.au_id=ta.au_idANDta.title_id=t.title_id此例中，将SELECT返回的结果集合给予一别名t，然后再从中检索数据。（三）使用WHERE子句设置查询条件WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据：SELECT*FROMusertableWHEREage>20WHERE子句可包括各种条件运算符：比较运算符（大小比较）：>、>=、=、<、<=、<>、！>、!<围运算符（表达式值是否在指定的围）：BETWEEN…AND…NOTBETWEEN…AND…列表运算符（判断表达式是否为列表中的指定项）：IN（项1,项2……）NOTIN（项1,项2……）模式匹配符（判断值是否与指定的字符通配格式相符）：LIKE、NOTLIKE空值判断符（判断表达式是否为空）：ISNULL>NOTISNULL逻辑运算符（用于多条件的逻辑连接）：NOT>AND>OR1、围运算符例：ageBETWEEN10AND30相当于age>=10ANDage<=302、列表运算符例：countryIN（'Germany','China'）40/773、模式匹配符例：常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。可使用以下通配字符：百分号％：可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即％%。下划线_：匹配单个任意字符，它常用来限制表达式的字符长度。方括号[]：指定一个字符、字符串或围，要求所匹配对象为它们中的任一个。厂]:其取值也[]一样，但它要求所匹配对象为指定字符以外的任一个字符。例如：限制以Publishing结尾，使用LIKE"Publishing'限制以A开头：LIKE'[A]%'限制以A开头外：LIKE'「A]%'4、空值判断符例WHEREageISNULL5、逻辑运算符：优先级为NOT、AND、OR（四）查询结果排序使用ORDERBY子句对查询返回的结果按一列或多列排序。ORDER8丫子句的语法格式为：ORDERBY{column_name[ASC|DESC]}[，…n]其中ASC表示升序，为默认值，DESC为降序。ORDERBY不能按ntext、text和image数据类型进行排序。例如：41/77SELECT*FROMusertableORDERBYagedesc,useridASC另外，可以根据表达式进行排序。二、联合查询UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，即执行联合查询。UNION的语法格式为：select_statementUNION[ALL]selectstatement[UNION[ALL]selectstatement][…n]其中selectstatement为待联合的SELECT查询语句。ALL选项表示将所有行合并到结果集合中。不指定该项时，被联合查询结果集合中的重复行将只保留一行。联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。在使用UNION运算符时，应保证每个联合查询语句的选择列表中有一样数量的表达式，并且每个查询选42/77择表达式应具有一样的数据类型，或是可以自动将它们转换为一样的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。例如：查询1UNION（查询2UNION查询3）三、连接查询通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。连接可以在SELECT语句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以，在Transact-SQL中推荐使用这种方法。43/77SQL-92标准所定义的FROM子句的连接语法格式为：FROMjoin_tablejoin_typejoin_table[ON(join_condition)]其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。join_type指出连接类型，可分为三种：连接、外连接和交叉连接。连接INNER人^"使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，连接又分为等值连接、自然连接和不等连接三种。外连接分为左外连接(LEFTOUTERJOIN或LEFTJOIN)、右外连接(RIGHTOUTERJOIN或RIGHTJOIN)和全外连接(FULLOUTERJOIN或FULLJOIN)三种。与连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。交叉连接(CROSSJOIN)没有WHERE子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。连接操作中的ON(join_condition)子句指出连接条件，它由被连接表中的列44/77和比较运算符、逻辑运算符等构成。无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：SELECTp1.pub_id,p2.pub_id,p1.pr_infoFROMpub_infoASp1INNERJOINpub_infoASp2ONDATALENGTH（p1.pr_info）=DATALENGTH（p2.pr_info）（一）连接连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。连接分三种：1、等值连接：在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。2、不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括＞、＞=、＜=、＜、！＞、！＜和。。3、自然连接：在连接条件中使用等于（=）运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。45/77例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和：SELECT*FROMauthorsASaINNERJOINpublishersASpONa.city=p.city又如使用自然连接，在选择列表中删除authors和publishers表中重复列（city和state）：SELECTa.*,p.pub_id,p.pub_name,p.countryFROMauthorsASaINNERJOINpublishersASpONa.city=p.city（二）外连接连接时，返回查询结果集合中的仅是符合查询条件（WHERE搜索条件或HAVING条件）和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表（左外连接时）、右表（右外连接时）或两个边接表（全外连接）中的所有数据行。如下面使用左外连接将论坛容和作者信息连接起来：SELECTa.*,b.*FROMluntanLEFTJOINusertableasbONa.username=b.username下面使用全外连接将city表中的所有作者以与user表中的所有作者，以与他们所在的城市：SELECTa.*,b.*FROMcityasaFULLOUTERJOINuserasb46/77ONa.username=b.username（三）交叉连接交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家，则下列交叉连接检索到的记录数将等于6*8=48行。SELECTtype,pub_nameFROMtitlesCROSSJOINpublishersORDERBYtype47/77SQL查询语句精华大全一、简单查询简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的48/77表或视图、以与搜索条件等。例如，下面的语句查询testtable表中为“三”的nickname字段和email字段。SELECTnickname,emailFROMtesttableWHEREname='三'（一）选择列表选择列表（select_list）指出所查询列，它可以是一组列名列表、星号、表达式、变量（包括局部变量和全局变量）等构成。1、选择所有列例如，下面语句显示testtable表中所有列的数据：SELECT*FROMtesttable2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序一样。例如：SELECTnickname,emailFROMtesttable49/773、更改列标题在选择列表中，可重新指定列标题。定义格式为：列标题=列名列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：SELECT昵称=nickname,电子=emailFROMtesttable4、删除重复行SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在5£1£。丁返回的结果集合中只保留一行。5、限制返回的行数使用TOPn[PERCENT]选项限制返回的数据行数，TOPn说明返回n行，而TOPnPERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。例如：SELECTTOP2*50/77FROMtesttableSELECTTOP20PERCENT*FROMtesttable（二）FROM子句FROM子句指定SELECT语句查询与与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定：SELECTusername,citytable.cityidFROMusertable,citytableWHEREusertable.cityid=citytable.cityid在FROM子句中可用以下两种格式为表或视图指定别名：表名as别名表名别名例如上面语句可用表的别名格式表