Oracle SQL高级查询技巧：深入解析UNION、INTERSECT、MINUS与WITH子句

列举一些sql高级查询语句

1.集合运算：学习Oracle中集合运算的相关语句，掌握Union、Unionall、Minus和Interest的使用，能够描述组合运算，将多个查询组合成一个查询，并控制返回行的顺序。
包含集合操作的查询称为复合查询。
参见表1-1。
表1-1。
OperatorReturnscontentUNION每个查询选择的所有唯一行的并集不包含重复值。
UNIONALL每个查询选择的所有行的完整并集，包括所有重复项。
INTERSECT组成为：每个查询选择所有非重复MINUS行的交集在第一个查询中，而不是在后续查询中，并且结果行不重复。
差集的所有集合操作与等号具有相同的优先级该语句包含多个集合操作并且没有。
括号明确指示不同的顺序，Oracle服务器从左到右进行计算。
您应该使用括号来显式指定INTERSECT查询中与附加集合运算的赋值顺序。
Unionall的效率普遍高于Union。
1.1.union和unionallUNION操作（常见）UNION操作返回查询选择的所有行。
使用UNION操作返回多个表中的所有行，但删除所有重复行。
原则：选择的列数和列数据类型必须与查询中使用的所有SELECT语句相匹配。
列名称不必相同。
＆#1048707;？对所有选定的列执行常见操作。
＆#1048707;？不要忽略重复检查中的NULL值。
＆#1048707;？IN操作的优先级高于UNION操作。
默认情况下，输出按SELECT子句的第一列升序排序。
UNIONALL操作使用UNIONALL操作返回多个查询的所有行。
原理：与Union不同，默认情况下不会过滤重复行，并且输出不会排序。
􀂃?不能使用DISTINCT关键字。
用法：Selectstatementunion|unionallSelectstatement;1.2.Intersect交集运算交集运算使用交集运算可返回多个查询中的所有公共行。
没有重复的行。
原则：查询中的SELECT语句选择的列的数量和数据类型在查询中使用的所有SELTCT语句中必须相同，但列名不必相同。
＆#1048707;？对重叠表进行反向排序不会改变结果。
􀂃?Intersect不会忽略空值。
使用：SelectstatementintersectallSelectstatement;1.3.减差运算，减法运算返回第一个查询返回的行（第一个SELECT语句减去第二个SELECT语句）。
原则：查询中的SELECT语句选择的列的数量和数据类型在查询中使用的所有SELTCT语句中必须相同，但列名不必相同。
􀂃对于MINUS操作，WHERE子句中的所有列都必须位于SELECT子句中。
集合运算的原理？两个SELECT列表中的表达式必须在数量和数据类型上匹配。
可以使用括号来改变执行顺序吗？1.每个人SELECT语句接受列名、别名或位置标记。
除UNIONALL外，重复行将被自动删除。
结果中的列名称是第一个查询中出现的列名称，默认情况下，输出按升序排列。
2、Exists的使用只能用于子查询。
如果结果匹配，则内部查询结束。
无论匹配与否，都会返回所有结果数据。
通过使用“exists”，可以将子查询的结果设置为常量，不影响查询效果，效率很高。
例如，如果您查询销售部门中所有员工的姓名，则比较将如下所示：您希望首先运行外部查询，并使用每个值从内部查询中检索一个值。
在许多情况下，EXISTS更好，因为它要求您指定可以调用INDEX扫描的连接条件。
然而，当子查询结果非常小时，IN往往更好。
通常，您希望首先运行返回较小结果集的查询。
In和existent的比较：当子查询的结果集比较小时，优先选择in。
如果外部查询小于子查询，则优先选择现有查询。
因为当使用“in”时，Oracle首先查询子查询，然后匹配外层查询。
当使用exists时，Oracle首先查询外表，然后匹配内表。
最优匹配原则是将最小的数据集与最大的数据集进行匹配。
使用inselectlast_name,titlefroms_empwheredept_idin(selectidfroms_deptwherename='Sales');xists(select'x'--将查询结果设置为常量，提高效率froms_deptswheres.id=e.dept_idands.name='Sales');2.2noteexists的使用与“exists”含义相反，也用于子查询中替换notin。
其他一切都一样。
例如，查询非销售部门的员工姓名，选择“selectlast_name,titlefroms_empewherenotexists(select'x”)——将查询结果设置为常量，提高效率froms_deptswheres.id=e.dept_idands.name='销售量'）;3.新的with子句9i语法1.使用with子句允许子查询重用相同的with查询块并通过select调用它，通常当多次使用with查询时。
2、with子句返回的结果存放在用户的临时表空间中，只执行一次查询，提高效率。
3.如果有多个查询，第一个查询使用“with”，后面的查询不要使用“with”，并用逗号分隔。
5.最后一个with子句和后面的查询之间不能有逗号。
查询必须位于方括号中。
6.当定义了with子句但未在查询中使用时。
32035错误：with子句中定义的查询名称未加引号。
（至少有一个with查询的名称没有被引用。
解决方案是删除未引用的with查询。
）7.前面的with子句定义的查询可以在后面的with子句中使用。
With子句的目的是重用查询。
语法：Withalias_nameas(select1),--as和select中的括号不能省略。
alias_name2as(select2),--后面没有逗号分隔...alias_names(selectn)--后面查询Select之间没有逗号...比如查询销售部门员工姓名：--withclausewithaas(selectidfroms_deptwherename='销售'orderbyid)selectlast_name,titlefroms_empwheredept_idin(select*froma);-select查询别名和with子句允许您在复杂查询中预定义一个结果集，然后在查询中重复使用它，不要使用它，会报错。
此外，with子句维护一个临时表。
例如，在查询中使用时，必须使用Withcntas(selectcount(*)fromtable)Selectcnt+1fromdual。
必须是Withcntas(selectcount(*)shumufromuser_tables)Selectshumu+1fromcnt;are-直接引用with子查询中的列别名。
With查询示例：查询工资总额高于所有部门平均工资总额的部门。
部门表s_dept，员工表s_emp。
分析：执行这个查询，首先需要计算所有部门的工资总额，然后计算工资总额的平均工资，然后过滤掉工资总额高于所有部门平均工资的部门。
那么第一步就是查询出所有部门的工资总额。
第二步，从第一步得到的结果表中查询平均工资。
最后用两个查询进行比较，结果是工资总额高于平均工资：with--step1:查询部门名称以及部门的工资总额dept_costsas(selecta.name,sum(b.Salary)dept_totalfroms_depta,s_empbwherea.id=b.dept_idgroupbya.name),--step2:使用前面with查询的结果来做部门平均总工资avg_costsas(selectsu)计算m(dept_total)/count(*)dept_avgfromdept_costs)-步骤3：用查询比较两者并输出查询结果。
selectname,dept_totalfromdept_costswheredept_total>(selecttdept_avgfromavg_costs)orderbyname;从上面的查询可以看出，前面的with查询的结果可以被后面的with查询复用，并且with查询的结果列支持使用别名。
所有with查询都必须引用最后一个查询，否则会报ora-32035错误。
如果还有一个需求：一次查询，如果查询的结果行不满足10的倍数，则填充空白行，直到查询的行数为10的倍数。
示例：select*fromtrademark查询。
withcntas(select10-mod(count(*),10)shumufromtrademark)-查询有多少空行比10的倍数更差。
selectid,namefromtrademarkunionall–添加空行。
selectnull,null–填充空行。
fromdualconnectbyrownum<=(selectshumufromcnt);--1对于0之间的connectby，可以使用10g之前写的子查询用cntas(select10-mod(count(*),10)shumufromtrademark)-查询，有多少个空行比10的倍数差selectid,namefromtrademarkunionall--addBlankrowsselectnull,null–从all_objectswhererow填充空白行num<=(selectshumufromcnt);--使用all_objects行多4.mergeinto合并数据语法：(可省略)MERGEINTOtable_nameAStable_aliasUSING(table|view|sub_query)ASaliasON(joincondition)WHENMATCHEDTHENUPDATESEtcol1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)-多列用逗号分隔//可以使用列值（列值）；不指定。
功能：更新或插入源数据（来自实际的表、视图、子查询）指定的表（必须实际存在）基于on条件，优点是避免多次插入和更新操作。
Merge是一个目标运算符，不允许在合并语句中的同一行上进行插入或更新操作。
这种语法只需要一次全表扫描即可完成全部工作，其执行效率比INSERT+UPDATE要高。
示例如下：droptablet;CREATETABLETASSELECTROWNUMID,A.*fromDBA_OBJECTSA;droptablet1;CREATETABLET1ASSELECTROWNUMID,OWNER,TABLE_NAME,CAST('TABLE'ASVARCHAR2(100))OBJECT_TYPEfromDBA_TABLES;select*fromdba_objects;select*fromdba_tables;MERGEINTOT1USINGTON(T.OWNER=T1.OWNERANDT.OBJECT_NAME=T1.TABLE_NAMEANDT.OBJECT_TYPE=T1.OBJECT_TYPE)WHENMATCHEDTHENUPDATESETT1.ID=T.IDWHENNOTMATCHEDTHENINSERTVALUES(T.ID,T.OWNER,T.OBJECT_NAME,T.OBJECT_TYPE);--insert后不写意味着所有列都插入成了MERGEINTOT1USINGTON(T.OWNER=T1.OWNERANDT.OBJECT_NAME=T1.TABLE_NAME)WHENMATCHEDTHENUPDATESETT1.ID=T.IDWHENNOTMATCHEDTHENINSERTVALUES(T.ID,T.OWNER,T.OBJECT_NAME,T.OBJECT_TYPE);-常见错误：连接条件无法获取稳定的行。
您可以使用以下子查询MERGEINTOT1USING(SE)LECTOWNER,OBJECT_NAME,MAX(ID)IDfromTGROUPBYOWNER,OBJECT_NAME)TON(T.OWNER=T1.OWNERANDT.OBJECT_NAME=T1.TABLE_NAME)WHENMATCHEDTHENUPDATESETT1.ID=T.IDWHENNOTMATCHEDTHENINSERTVALUES(T.ID,T.OWNER,T.OBJECT_NAME);SELECTID,OWNER,OBJECT_NAME,OBJECT_TYPEfromTMINUSSELECT*fromT1;droptablesubs;createtablesubs(msidnumber(9),ms_typechar(1),areacodenumber(3));droptableacct;createtableacct(msidnumber(9),bill_monthnumber(6),areacodenumber(3),feenumber(8,2)default0.00);insertintosubsvalues(905310001,0,531);insertintosubsvalues(905320001,1,532);insertintosubsvalues(905330001,2,533);commit;mergeintoaccta-表手术usingsubsbon(a.msid=b.msid)–使用原始数据源表并制定条件。
匹配更新时条件必须在括号内eta.areacode=b.areacode-如果匹配则执行更新操作。
语法与直接更新不同。
不需要指定表名不需要指定表名。
指定要插入的字段时，请在插入后使用括号。
指定如果不指定则全部insert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode);另外MERGE语句的UPDATE不能改变连接使用的列，否则会报错select*fromacctis报;select*fromacct;--10g新函数，单个操作mergeintoacctausingsubsbon(a.msid=b.msid)whennotmatchedthen--如果只有一个Notmatched则只进行插入，如果只有一个则不进行更新存在匹配，则仅在ert(a.msid,a.bill_month,a.areacode)values(b.msid,'200702',b.areacode);updateacctsetareacode=800wheremsid=905320001;deletefromacctwhere中执行更新操作Areacode=533orareacode=531;insertintoacctvalues(905320001,'200702',800,0.00);--删除重复行deletefromsubsbwhereb.rowid<(selectmax(a.rowid)fromsubsawhea.msid=b.msidanda.ms_type=b.ms_typeanda.areacode=b.areacode);--10g新函数，合并操作后只能使用适当的更新操作，并使用deletewhere子句删除条件在目标表中确定。
mergeineinenacctusingsubsbon(a.msid=b.msid)当匹配时然后更新seta.areacode=b.areacodedeletewhere(b.ms_type!=0)当NOTMATCHEDtheninsert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode)whereb.ms_type=0；--10g新功能，插入并更新满足条件的Mergeintoacctausingsubsbon(a.msid=b.msid)当匹配时更新seta.areacode=b.areacodewhereb.ms_type=0当NOTMATCHEDtheninsert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode)whereb.ms_type=0;select*fromsubswherems_type=0；

用sql语句怎么查一个表的信息

sqlserver查询表中的所有信息。
查询语法为：select*from表名语法中的“*”代表一切。

实际操作示例：某数据有一个名为“user_user”的表，该表中存储了用户信息。

1.“user_user”表，SQL语句：select*fromuser_user

2.“user_use”表中的所有个人信息及部分字段。
SQL语句：selectuser_show_id,user_name,pinyin,passwordfromuser_user

3.条件查询“user_user”表。
以user_name为查询条件查询所有信息。
SQL语句：select*fromuser_userwhereuser_name='李丹'

4.模糊查询'user_user'表。
以user_name为模糊查询条件查询所有信息。
SQL语句：select*fromuser_userwhereuser_namelike'%张%'