【网学网提醒】：以下是网学会员为您推荐的SQL高级查询技巧，希望本篇文章对您学习有所帮助。
SQL高级查询技巧1.UNION,EXCEPT,INTERSECT运算符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)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。--------------------------------------------------------------------------------------------------2.外连接A、leftouterjoin：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。sql:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.cB：rightouterjoin:右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。C：fullouterjoin：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。--------------------------------------------------------------------------------------------------其次，大家来看一些不错的sql语句1、说明：复制表(只复制结构,源表名：a新表名：b)(Access可用)法一：select*intobfromawhere1<>1法二：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)5、说明：显示文章、提交人和最后回复时间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*fromtable1wheretimebetweentime1andtime2selecta,b,c,fromtable1whereanotbetween数值1and数值29、说明：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())>513、说明：一条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手机C323、说明：初始化表table1
TRUNCATETABLEtable124、说明：选择从10到15的记录selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc随机选择数据库记录的方法（使用Randomize函数，通过SQL语句实现）对存储在数据库中的数据来说，随机数特性能给出上面的效果，但它们可能太慢了些。你不能要求ASP“找个随机数”然后打印出来。实际上常见的解决方案是建立如下所示的循环：RandomizeRNumber=Int(Rnd*499)+1WhileNotobjRec.EOFIfobjRec("ID")=RNumberTHEN...这里是执行脚本...endifobjRec.MoveNextWend这很容易理解。首先，你取出1到500范围之内的一个随机数（假设500就是数据库内
记录的总数）。然后，你遍历每一记录来测试ID的值、检查其是否匹配RNumber。满足条件的话就执行由THEN关键字开始的那一块代码。假如你的RNumber等于495，那么要循环一遍数据库花的时间可就长了。虽然500这个数字看起来大了些，但相比更为稳固的企业解决方案这还是个小型数据库了，
后者通常在一个数据库内就包含了成千上万条记录。这时候不就死定了？采用SQL，你就可以很快地找出准确的记录并且打开一个只包含该记录的recordset，如下所示：RandomizeRNumber=Int(Rnd*499)+1sql="SELECT*FROMCustomersWHEREID="&;RNumbersetobjRec=ObjConn.Execute(SQL)Response.WriteRNumber&;"="&;objRec("ID")&;""&;objRec("c_email")
不必写出RNumber和ID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset没有包含其他内容，因此你很快就能找到你需要的记录这样就大大降低了处理时间。-------------------------------------------------------------------------------------再谈随机数现在你下定决心要榨干Random函数的最后一滴油，那么你可能会一次取出多条随机记录或者想采用一定随机范围内的记录。把上面的标准Random示例扩展一下就可以用SQL应对上面两种情况了。为了取出几条随机选择的记录并存放在同一recordset内，你可以存储三个随机数，然后查询数据库获得匹配这些数字的记录：sql="SELECT*FROMCustomersWHEREID="&;RNumber&;"ORID="&;RNumber2&;"ORID="&;RNumber3假如你想选出10条记录（也许是每次页面装载时的10条链接的列表），你可以用BETWEEN或者数学等式选出第一条记录和适当数量的递增记录。这一操作可以通过好几种方式来完成，但是SELECT语句只显示一种可能（这里的ID是自动生成的号码）：sql="SELECT*FROMCustomersWHEREIDBETWEEN"&;RNumber&;"AND"&;RNumber&;"+9"注意：以上代码的执行目的不是检查数据库内是否有9条并发记录。随机读取若干条记录，
测试过Access语法：SELECTtop10*From表名ORDERBYRnd(id)sqlserver:selecttopn*from表名orderbynewid()mysqlelect*From表名OrderByrand()LimitnAccess左连接语法(最近开发要用左连接,Access帮助什么都没有,网上没有Access的SQL说明,只有自己测试,现在记下以备后查)语法electtable1.fd1,table1,fd2,table2.fd2Fromtable1leftjointable2ontable1.fd1,table2.fd1where...使用SQL语句用...代替过长的字符串显示语法：SQL数据库：selectcasewhenlen(field)>10thenleft(field,10)+'...'elsefieldendasnews_name,news_idfromtablenameAccess数据库：SELECTiif(len(field)>2,left(field,2)+'...',field)FROMtablename;
Conn.Execute
说明Execute方法该方法用于执行SQL语句。根据SQL语句执行后是否返回记录集，该方法的使用格式分为以下两种：1．执行SQL查询语句时，将返回查询得到的记录集。用法为：Set对象变量名=连接对象.Execute("SQL查询语言")Execute方法调用后，会自动创建记录集对象，并将查询结果存储在该记录对象中，通过Set方法，将记录集赋给指定的对象保存，以后对象变量就代表了该记录集对象。2．执行SQL的操作性语言时，没有记录集的返回。此时用法为：连接对象.Execute"SQL操作性语句"[,RecordAffected][,Option]?RecordAffected为可选项，此出可放置一个变量，SQL语句执行后，所生效的记录数会自动保存到该变量中。通过访问该变量，就可知道SQL语句队多少条记录进行了操作。?Option可选项，该参数的取值通常为adCMDText，它用于告诉ADO，应该将Execute方法之后的第一个字符解释为命令文本。通过指定该参数，可使执行更高效。?BeginTrans、RollbackTrans、CommitTrans方法这三个方法是连接对象提供的用于事务处理的方法。BeginTrans用于开始一个事物；RollbackTrans用于回滚事务；CommitTrans用于提交所有的事务处理结果，即确认事务的处理。
事务处理可以将一组操作视为一个整体，只有全部语句都成功执行后，事务处理才算成功；若其中有一个语句执行失败，则整个处理就算失败，并恢复到处里前的状态。BeginTrans和CommitTrans用于标记事务的开始和结束，在这两个之间的语句，就是作为事务处理的语句。判断事务处理是否成功，可通过连接对象的Error集合来实现，Error集合的成员个数不若为0，则说明有错误发生，事务处理失败。Error集合中的每一个Error对象，代表一个错误信息。-----------------------------------------------------------------------------------------------transact---sql高级查询（下）5:使用having关键字来筛选结果6:使用compute和computeby子句7:使用嵌套查询8:分布式查询E:使用having关键字来筛选结果当完成对数据结果的查询和统计后,可以使用having关键字来对查询和计算的结果进行一步的筛选例:检索出work表中学历是大专或者是中专的人数select学历,count(学历)fromworkgroupby学历having学历in(\'大专\',\'中专\')说明:1:having关键字都与groupby用在一起.2:having不支持对列分配的别名例如:select学历,\'大于5的人数\'=count(学历)fromworkgroupby学历having大于5的人数>5[错错]改为:select学历,\'大于5的人数\'=count(学历)fromworkgroupby学历havingcount(学历)>5F:使用compute和computeby使用compute子句允许同时观察查询所得到各列的数据的细节
以及统计各列数据所产生的汇总列select*fromwork[查询所得到的各列的数据的细节]computemax(基本工资),min(基本工资)[统计之后的结果]这个例子中没有使用by关键字,返回的结果是最后添加了一行基本工资的最大值和最小值,也可增加by关键字.例:select*fromworkorderby学历computemax(基本工资),min(基本工资)by学历比较:select学历,max(基本工资),min(基本工资)fromworkgroupby学历
说明:1:compute子句必须与orderby子句用在一起2:compute子句可以返回多种结果集.一种是体现数据细节的数据集,可以按分类要求进行正确的分类；另一种在分类的基础上进行汇总产生结果.3:而groupby子句对每一类数据分类之后只能产生一个结果,不能知道细节G:使用嵌套查询查询中再查询,通常是以一个查询作为条件来供另一个查询使用例:有work表和部门表A:检索出在部门表中登记的所有部门的职工基本资料select*fromworkwhere部门编号in[notin](select部门编号fromdbo.部门)B:检索出在work表中每一个部门的最高基本工资的职工资料select*fromworkawhere基本工资=(selectmax(基本工资)fromworkbwherea.部门名称=b.部门名称)说明:由外查询提供一个部门名称给内查询,内查询利用这个部门名称找到该部门的最高基本工资,然后外查询根据基本工资判断是否等于最高工资,如果是的,则显示出来.相当于:select*fromwork,(select部门名称,max(基本工资)as基本工资fromworkgroupby部门名称ast)wherework.基本工资=t.基本工资andwork.部门名称=t.部门名称C:用嵌套work表和嵌套部门表,在嵌套work表中检索出和职工号都在嵌套部门存在的职工资料select*from嵌套workwhere职工号in(select职工号from嵌套部门)andin(selectfrom嵌套部门)[察看结果,分析原因]改:select*from嵌套worka,嵌套部门bwherea.职工号=b.职工号anda.=b.改:select*from嵌套workwhere职工号=(select职工号from嵌套部门)and=(selectfrom嵌套部门)[行吗?为什么,分析原因?]在嵌套中使用exists关键字[存在]例:1:用嵌套work表和嵌套部门表,在嵌套work表中检索出和职工号都在嵌套部门存在的职工资料select*from嵌套workawhereexists(select*from嵌套部门bwherea.=b.anda.职工号=b.职工号)2:在work表检索出在部门表没有的职工select*fromworkwherenotexists(select*from部门where部门.部门编号=work.部门编号)能否改成:select*fromworkwhereexists(select*from部门where部门.部门编号<>work.部门编号)
在列清单中使用select例:1:在work1表和部门表中检索出所有部门的部门名称和基本工资总和select部门
名称,(selectsum(基本工资)fromwork1bwherea.部门编号=b.部门编号)from部门a2:检索各部门的职工人数select部门编号,部门名称,(selectcount(职工号)fromwork1awherea.部门编号=b.部门编号)as人数from部门b3:在商品表和销售表中查询每一职工的,所属部门,销售总量select,所属部门,(selectsum(销售量)from商品销售awherea.职工号=b.职工号)as销售总量from嵌套部门bH:分布式查询我们以前的查询都只是基于一个服务器中的一个数据库的查询,如果一个查询是要跨越一个服务器,像这样的查询就是分布式查询,那么我们以看到分布查询就是数据源自于两个服务器.要进行分布式查询必须先创建一个“链接服务器”,以便让本地的用户能够映射到过程服务器.“链接服务器”的创立A:在“链接服务器”里面输入以后为了方便访问该链接服务器的名称[任意]B:在“提供程序名称”里面选择“MicrosoftOLEDBProviderforSQLServer”C:在“数据源”里面输入服务器的网络名D:本地登录,远程用户和远程密码里面分别输入一个本地登录用户,远程登录和远程密码以便让本地SQLServer登录映射为链接服务器上的用户E:访问方法:格式:链接服务器的名称.数据库名.dbo.表名链接服务器有两个特点:1:通过链接服务器不能删除链接源服务器的任何对像.2:能过链接服务器可以对链接源服务器的表进行insert,updae,delete操作.
视图1:什么是视图2:视图和查询的区别3:视图的优点4:如何创建和管理视图5:如何通过视图修改基本表的数据6:如何通过视图实现数据的安全性A:什么是视图:视图(view):从一个或几个基本表中根据用户需要而做成一个虚表
1:视图是虚表,它在存储时只存储视图的定义,而没有存储对应的数据2:视图只在刚刚打开的一瞬间,通过定义从基表中搜集数据,并展现给用户B:视图与查询的区别:视图和查询都是用由sql语句组成,这是他们相同的地方,但是视图和查询有着本质区别:它们的区别在于:1:存储上的区别:视图存储为数据库设计的一部分,而查询则不是.2:更新限制的要求不一样要注意:因为视图来自于表,所以通过视图可以间接对表进行更新,我们也可以通过update语句对表进行更新,但是对视图和查询更新限制是不同的,以下我们会知道虽然通过视图可以间接更新表但是有很多限制.3:排序结果:通过sql语句,可以对一个表进行排序,而视图则不行.比如:创建一个含有orderby子句的视图,看一下可以成功吗?C:视图的优点:为什么有了表还要引入视图呢？这是因为视图具有以下几个优点:1:能分割数据,简化观点可以通过select和where来定义视图,从而可以分割数据基表中某些对
于用户不关心的数据,使用户把注意力集中到所关心的数据列.进一步简化浏览数据工作.2:为数据提供一定的逻辑独立性如果为某一个基表定义一个视图,即使以后基本表的内容的发生改变了也不会影响“视图定义”所得到的数据3:提供自动的安全保护功能视图能像基本表一样授予或撤消访问许可权.4:视图可以间接对表进行更新,因此视图的更新就是表的更新D:视图的创建和管理视图的创建1:通过sql语句格式:createview视图名asselect语句试一试:分别创建关于一个表或多个表的视图[因为视图可以来自于多表]2:通过企业管理器说明:1:在完成视图的创立之后,就可以像使用基本表一样来使用视图2:在创建视图时,并非所有的select子查询都可用如:compute和computeby,orderby[除非与top一起连用]3:但在查询时,依然都可以用在创建时禁用的select子查询4:在视图创建时,必须为没有标题列指定标题[思考:能否不用select语句来创建一个视图]视图的删除:1:通过sql语句:dropview视图名
2:通过企业管理器说明:与删除表不同的是,删除视图后只是删除了视图了定义,并没有删除表中的数据.[查看相关性]修改视图的定义1:通过企业管理器2:通过sql语句:格式:alterview视图名as新的select语句浏览视图信息sp_helptext视图名[查看视图创建的语句]E:如何通过视图修改基本表的数据.1:在视图上使用insert语句通过视图插入数据与直接在表中插入数据一样,但视图毕竟不是基本表.因此在进行数据插入时还是有一定的限制1:如果视图上没有包括基本表中属性为notnull[不能为空]的列,那么插入操作会因为那些列是null值而失败.2:如果某些列因为某些规则或约束的限制而不能直接接受从视图插入的列时,插入会失败3:如果在视图中包含了使用统计函数的结果,或是包含计算列,则插入操作会失败4:不能在使用了distinct语句的视图中插入值5:不能在使用了groupby语句的视图中插入值2:使用update更新视图中的数据1:更新视图与更新表格一样,但是在视图中使用了多个基本表连接的情况下,每次更新操作只能更新来自基本表的一个数据列例如:创建以下视图:createviewdelasselect职工号,,部门名称,负责人fromwork1,部门wherework1.部门编号=部门.部门编号如果再执行下面的语句时:updatedelset职工号=\'001\',部门名称=\'wenda\'where职工号=\'01\'[出现错误]只能够改成:updatedelset职工号=\'001\'where职工号=\'01\'updatedelset部门名称=\'wenda\'where职工号=\'01\'2:不能在使用了distinct语句的视图中更新值3:不能在使用了groupby语句的视图中更新值3:使用delete删除视图中数据.通过视
图删除数据最终体现为从基本表中删除数据格式:delete视图名[where条件]
说明:当视图由两个以上的基表构成时,不允许删除视图的数据例如:建一个视图kkcreateviewkkasselect职工号,,性别,部门名称fromwork1,部门wherework1.部门编号=部门.部门编号[试着去删除]使用withcheckoption的视图如果不了解视图定义内容,则常常会发生向视图中输入不符合视图定义的数据的情况.比如:createviewxmasselect*fromworkwhere性别=\'男\'完全可以插入insertxmvalues(\'001\',\'女\',23,\'2400\'....)尽管从意义上来说是不合理的,但是上述语句是正确的.为了防止这种情况的发生,可以使用withcheckoption子句来对插入的或更改的数据进行限制.比如:createviewxmasselect*fromworkwhere性别=\'男\'withcheckoption使用schemabinding的视图[使用绑定到构架]我们知道视图是依赖于表,如果在一个表中创建一个视图,今后如果这个表被删除了,则这个视图将不可再用了.为了防止用户删除一个有视图在引用的表,可以在创建视图的时候加上schemabinding关键字.比如:createview基本工资withSCHEMABINDINGasselect,性别,基本工资fromdbo.work说明:1:不能使用“*”来创建此类型的视图2:创建此类型的视图时,一定要加上dbo.表名.3:如果在某个表中定义了此类视图,则用户将不能对表的结构进行修改,否则会删除这些绑定4:如果用户对表的结构进行列改名,则会删除绑定而且视图不可用.5:如果用户对表的结构进行列的类型或者大小修改,则会删除绑定但视图可用,此时用户可以删除视图所引用的表.使用withencryption对视图进行加密为了保护创建视图定义的原代码,可以对视图进行加密.比如:createviewkkwithencryptionasselect*fromworkwhere职称=\'经理\'用sp_helptext来查看一下.或用企业管理器查看一下.说明:如果应用此项用户将无法设计视图F:使用视图加强数据的安全一般通过使用视图共有三种途径加强数据的安全性A:对不同用户授予不同的使用权.
B:通过使用select子句限制用户对某些底层基表的列的访问C:通过使用where子句限制用户对某些底层基表的行的访问对不同用户授予不同的权限
