【网学网提醒】：网学会员为需要朋友们搜集整理了sql高级语法相关资料，希望对各位网友有所帮助!
语句高级用法SQL语句-SELECT高级用法-SELECT高级语法
SELECT语句高级用法1,使用groupby子句有表life_unite_order如图:
执行:selectsum(unite_id),projnamefromlife_unite_orderwhereprojnamein('凯旋大厦','双安商场')groupbyprojname结果为如下图:
看懂没有?上面就是一个利用groupby分组的实例我问一下大家如果执行下面这条语句selectsum(unite_id),unite_id,projnamefromlife_unite_orderwhereprojnamein('凯旋大厦','双安商场')groupbyprojname,unite_id会是什么结果呢?呵呵!其实会出现下面的结果如图:
聪明的你看明白了吗?下面介绍让人睡觉的
groupby分组理论
通过这个结果可以看出,所有的统计函数都是对查询出的每一行数据进行分类以后再进行统计计算.所以在结果集合中,对所进行分类的列的每一种数据都有一行统计结果值与之对应.说明:GROUPBY子句中不支持对列分配的假名,也不支持任何使用了统计函数的集合列.另外,对SELECT后面每一列数据除了出现在统计函数中的列以外,都必须在GROUPBY子句中应用.
GROUPBY子句用来为结果集中的每一行产生聚合值.如果聚合函数没有使用GROUPBY子句,则只为SELECT语句报告一个聚合值.当SELECT语句中包含GROUPBY关键字时,对可以在选择列表中指定的项有一些限制.在该选择列表中所允许的项目是:
分组列.为分组列中的每个值只返回一个值的表达式,例如将列名作为其中一个参数的聚合函数.这些函数称为矢量聚合.
groupby子句将表分为几组,此子句通常与为每个这样的组生产总结值的聚集函数组合.使用不带聚集的groupby子句与在select子句中使用的distinct(或unique)关键字很相似.selectdistinctcustomer_numfromorders;selecctcustomer_numfromordersgroupbycustomer_num;groupby子句将行收集到组中,因此每一组中的每一行具有相同的客户号.在没有选择任何其它列的情况下,结果是唯一的customer_num值的列表.selectorder_num,count(*)number,sum(total_price)pricefromitemsgroupby1orderby3;selecto.order_num,sum(i.total_price)fromorderaso,itemsasiwhereo.order_date>'01/01/98'ando.customer_num=110ando.order_num=i.order_numgroupbyo.order_num;使用having子句要完成groupby子句,使用having子句来在构成组之后将一个或多个限制条件应用于这些组.having子句对组的影响类似于where子句限制个别行的方式,使用having子句的一个优点是可以在搜索条件中包括聚集,而在where子句的搜索条件中却不能包括聚集.每个having条件将组的一列或一个聚集表达式与组的另一个聚集表达式或与常量作比较.可以使用having来对列值或组列表中的聚集值设置条件.下面的语句返回具有两个商品以上的订单
上每个商品的平均总价格.having子句在每个构成的测试组,并选择由两行以上构成的那些组.selectorder_num,count(*)number,avg(total_price)averagefromitemsgroupbyorder_numhavingcount(*)>2;
如果不带groupby子句使用having子句,则该having条件适应与满足搜索条件的所有行.换言之,满足搜索条件的所有行构成一个组.selectavg(total_price)averagefromitemshavingcount(*)>2;selecto.order_num,sum(i.total_price)price,paid_date-order_datespanfromorderso,itemsiwhereo.order_date>'01/01/98'ando.customer_num>110ando.order_num=i.order_numgroupby1,3havingcount(*)<5orderby3intotemptemptab1;创建高级连接自连接连接不一定都是涉及两个不同的表.可以将表连接至它本身,缠结自连接.当想要将列中的值与同一列中的其它值进行比较时,将表连接至它自身非常有用.进行自连接时我们需要一个机制来区分一个表的两个实例.在FROMclause(子句)中我们可以给这个表取不同的别名,然后在语句的其它需要使用到该别名的地方用dot(点)来连接该别名和字段名.与表之间的连接一样,可以在自连接中使用算术表达式.可以测试空值,可以使用orderby子句来以升序或将序对指定列中的值进行排序.selectx.order_num,x.ship_weight,x,ship_date,y.order_num,y.ship_weight,y.ship_datefromorderx,orderywherex.ship_weight>=5*y.ship_weightandx.ship_dateisnotnullandy.ship_dateisnotnullorderbyx.ship_date;如果想要将自连接的结果存储到临时表中,则将Intotemp子句追加到select语句中,并至少对一组列指定显示标号,以重命名这些列.否则,重复列名将导致错误,并且不会创建临时表.selectx.order_numorders1,x.po_numpurch1,x.ship_dateship1,y.order_numorders2,y.po_numpurch2,y.ship_dateship2fromorderx,ordersywherex.ship_weight>=5*y.ship_weightandx.ship_dateisnotnullandy.ship_dateisnotnullorderbyorders1,orders2intotempshipping;自连接子句三次
selects1.manu_code,s2.manu_code,s3.manu_code,s1.stock_num,s1.descriptonfromstocks1,stocks2,stocks3wheres1.stock_num=s2.stock_numands2.stock_num=s3.stock_numands1.manu_code
selectemp.employee_num,emp.gross_pay,emp.level,emp.detp_num,mgr.employee_num,mgr.gross_pay,mgr.detp_num,mgr.levelfrompayrollemp,payrollmgrwhereemp.gross_pay>mgr.gross_payandemp.level 使用相关子查询来检索并列示预定的10种价格最高的商品selectorder_num,total_pricefromitemsawhere10>(selectcount(*)fromitemsbwhereb.total_price 连接使其中一个表成为控制表(也成为外部表),控制其它从属表(也成为内部表).在内连接或简单连接中,结果只保护满足连接条件的行组合,废弃不满足连接条件的行.在外连接中,结果包含满足连接条件的行与控制表中的行(如果在从属表中找不到匹配的行将废弃这些行)的组合.在从属表中没有相匹配的行的控制表在从属表选择的列中包含null值.外连接允许在应用连接条件之前将连接过虑器应用于内部表.关键字开始外连接.ANSI外连接语法用leftjoin,leftouterjoin,rightjoin和rightouterjoin关键字开始外连接.关键字是可选的.子句中指定连接条件和可选连接过虑器.outer关键字是可选的.查询可在on子句中指定连接条件和可选连接过虑器.where子句指定后连接过虑器.过虑器.
SQLFULLJOIN关键字
PreviousPageNextPage
SQLFULLJOIN关键字
只要其中某个表存在匹配,FULLJOIN关键字就会返回行.
FULLJOIN关键字语法
SELECTcolumn_name(s)FROMtable_name1FULLJOINtable_name2ONtable_name1.column_name=table_name2.column_name注释:在某些数据库中,FULLJOIN称为FULLOUTERJOIN.
用在例子中的):原始的表(用在例子中的:用在例子中的
"Persons"表:
Id_PLastNameFirstName123AdamsBushCarterJohnGeorgeThomasAddressCity
OxfordStreetLondonFifthAvenueNewYork
ChanganStreetBeijing
"Orders"表:
Id_OOrderNoId_P123457789544678224562456234764331165
全连接(全连接(FULLJOIN)实例)
现在,我们希望列出所有的人,以及他们的定单,以及所有的定单,以及定购它们的人.您可以使用下面的SELECT语句:SELECTPersons.LastName,Persons.FirstName,Orders.OrderNoFROMPersonsFULLJOINOrdersONPersons.Id_P=Orders.Id_PORDERBYPersons.LastName结果集:
LastNameFirstNameOrderNo
AdamsAdamsCarterCarterBush
JohnJohnThomasThomasGeorge
22456245627789544678
34764
FULLJOIN关键字会从左表(Persons)和右表(Orders)那里返回所有的行.如果"Persons"中的行在表"Orders"中没有匹配,或者如果"Orders"中的行在表"Persons"中没有匹配,这些行同样会列出.
左外连接在左外连接的语法中,外连接的控制表显示在开始外连接的关键字的左边.左外连接返回连接条件为true的所有行,除此之外,还返回控制表中的所有其它行并将从属表中的相应值显示为Null.selectc.customer_num,c.lname,cpany,c.phone,u.call_dtime,u.call_descrfromcustomercleftouterjoincust_callsuonc.customer_num=u.customer_num;
selectc.customer_num,c.lname,cpany,c.phone,u.call_dtime,u.call_descrfromcustomercleftouterjoincust_callsuonc.customer_num=u.customer_numwhereu.customer_numisnull;右外连接在右外连接的语法中,外连接的控制表显示在开始外连接的关键字右边,右外连接返回连接条
件为true的所有行,除此之外,还返回控制表中的所有其它行并将从属表中的相应值显示为nullselectc.customer_num,c.fname,c.lname,o.order_num,o.order_date,o.customer_numfromcustomercrightouterjoinordersoon(c.customer_num=o.customer_num);简单连接selectc.customer_num,c.lname,cpany,c.phone,u.call_dtime,u.call_descrfromcustomerc,cust_callsuwherec.customer_num=u.customer_num;对两个表的简单外连接selectc.customer_num,c.lname,cpany,c.phone,u.call_dtime,u.call_descrfromcustomerc,outercust_callsuwherec.customer_num=u.customer_num;cust_calls表前面的附加关键字outer使该表成为从属表.外连接导致查询返回有关所有客户的信息,而不管他们是否已经致电客户服务中心,检索控制表customer的所有行,并且将Null值指定给从属表cust_calls的列.
与第三个表进行简单连接的外连接这种外连接也称为嵌套简单连接selectc.customer_num,c.lname,o.order_num,i.stock_num,i.manu_code,i.quantityfromcustomerc,leftouterjoin(orderso,itemsi)wherec.customer_num=o.customer_numando.order_num=i.order_numandmanu_codein('KAR','SHM')ORDERBYlanme;
将两个表与第三个表进行外连接作为两个表中的每一个与第三个表的外连接的结果的外连接.在此第三中类型的外连接中,连接关系可能仅仅使与从属表之间的关系.selectc.customer_num,c.lname,o.order_num,order_date,call_dtimefromcustomerc,outerorderso,outercust_callsxwherec.customer_num=o.customer_numandc.customer_num=x.customer_numorderbylnameintotempservice;组合外连接的连接要实现多级嵌套,可以创建使用三种外连接类型的任何组合的连接.使用ansi语法,创建作为对两个表与另一个外连接的简单外连接组合结果的连接.selectc.customer_num,c.lname,o.order_num,stock_num,manu_code,quantityfromcustomerc,outer(orderso,outeritemsi)wherec.customer_num=o.customer_numando.order_num=i.order_numandmanu_codein('KAR','SHM')ORDERBYlname;
select语句中的子查询下列情况定义数据库服务器支持子查询类型:1,嵌套在另一个select语句的投影列表中的select语句2,嵌套在另一个select语句(或insert,delete或update语句中)的where子句中的select语句.相关子查询相关子查询使引用不在其from子句中的列或表的子查询.该列可以在projection子句或在where子句中.要查找查询引用的表,搜索不相关的列直到找到相关为止.projection子句中的子查询可以在另一个select语句的投影列表中发生.selectcustomer.customer_num,(selectsum(ship_charge)fromorderswherecustomer.customer_num=orders.customer_num)astotal_ship_chgfromcustomer;
子查询基础知识
子查询是一个SELECT查询,它返回单个值且嵌套在SELECT,INSERT,UPDATE,DELETE语句或其它子查询中.任何允
许使用表达式的地方都可以使用子查询.下例中,一个子查询用作SELECT语句中名为MaxUnitPrice的列表达式.
SELECTOrd.OrderID,Ord.OrderDate,(SELECTMAX(OrdDet.UnitPrice)FROMNorthwind.dbo.[OrderDetails]ASOrdDetWHEREOrd.OrderID=OrdDet.OrderID)ASMaxUnitPriceFROMNorthwind.dbo.OrdersASOrd子查询也称为内部查询或内部选择,而包含子查询的语句也称为外部查询或外部选择.许多包含子查询的Transact-SQL语句都可以改为用联接表示.而其它一些问题只能由子查询提出.在Transact-SQL中,包括子查询的语句和不包括子查询但语义上等效的语句在性能方面通常没有区别.但是,在一些必须检查存在性的情况中,使用联接会产生更好的性能.否则,为确保消除重复值,必须为外部查询的每个结果都处理嵌套查询.所以在这些情况下,联接方式会产生更好的效果.下面的示例显示返回相同结果集的SELECT子查询和SELECT联接:/*SELECTstatementbuiltusingasubquery.*/SELECTProductNameFROMNorthwind.dbo.ProductsWHEREUnitPrice=(SELECTUnitPriceFROMNorthwind.dbo.ProductsWHEREProductName='SirRodney''sScones')/*SELECTstatementbuiltusingajointhatreturnsthesameresultset.*/SELECTPrd1.ProductNameFROMNorthwind.dbo.ProductsASPrd1JOINNorthwind.dbo.ProductsASPrd2ON(Prd1.UnitPrice=Prd2.UnitPrice)WHEREPrd2.ProductName='SirRodney''sScones'
嵌套在外部SELECT语句中的子查询包括以下组件:
包含标准选择列表组件的标准SELECT查询.包含一个或多个表或者视图名的标准FROM子句.可选的WHERE子句.可选的GROUPBY子句.可选的HAVING子句.
子查询的SELECT查询总是使用圆括号括起来.且不能包括COMPUTE或FORBROWSE子句,如果同时指定TOP子句,则可能只包括ORDERBY子句.
子查询可以嵌套在外部SELECT,INSERT,UPDATE或DELETE语句的WHERE或HAVING子句内,或者其它子查询中.尽管根据可用内存和查询中其它表达式的复杂程度不同,嵌套限制也有所不同,但嵌套到32层是可能的.个别查询可能会不支持32层嵌套.任何可以使用表达式的地方都可以使用子查询,只要它返回的是单个值.如果某个表只出现在子查询中而不出现在外部查询中,那么该表中的列就无法包含在输出中(外部查询的选择列表).包括子查询的语句通常采用以下格式中的一种:
WHEREexpression[NOT]IN(subquery)WHEREexpressioncomparison_operator[ANY|ALL](subquery)WHERE[NOT]EXISTS(subquery)
在某些Transact-SQL语句中,子查询可以像一个独立的查询一样进行评估.从概念上讲,子查询结果将代入外部查询中(尽管不必知道MicrosoftSQLServer实际上如何通过子查询处理Transact-SQL语句).有三种基本的子查询.它们是:
在通过IN引入的列表或者由ANY或ALL修改
的比较运算符的列表上进行操作.通过无修改的比较运算符引入,并且必须返回单个值.通过EXISTS引入的存在测试.
使用FROM子句
在每一条要从表或视图中检索数据的SELCET语句中,都需要使用FROM子句.用FROM子句可以:
列出选择列表和WHERE子句中所引用的列所在的表和视图.可用AS子句为表和视图的名称指定别名.联接类型.这些类型由ON子句中指定的联接条件限定.
FROM子句是用逗号分隔的表名,视图名和JOIN子句的列表.Transact-SQL具有扩展功能,可支持在FROM子句中指定除表或视图之外的其它对象.这些对象返回结果集,也就是OLEDB术语中所说的行集,该结果集构成了虚拟表.然后SELECT语句就像操作表一样操作这些结果集.FROM子句可以指定:
一个或多个表或视图.例如:
SELECT*FROMShippers
两个表或视图之间的联接:SELECTCst.CustomerID,Cst.CompanyName,Cst.ContactName,Ord.ShippedDate,Ord.FreightFROMNorthwind.dbo.OrdersASOrdJOINNorthwind.dbo.CustomersASCstON(Cst.CustomerID=Ord.CustomerID)
一个或多个派生表,这些派生表是FROM子句中的SELECT语句,以别名或用户指定的名称来引用这些派生表.FROM子句中SELECT语句的结果集构成了外层SELECT语句所用的表.例如,下面的SELECT语句使用派生表查找是否有哪家书店备有pubs数据库中所有种类的书籍:SELECTST.stor_id,ST.stor_nameFROMstoresASST,(SELECTstor_id,COUNT(DISTINCTtitle_id)AStitle_countFROMsalesGROUPBYstor_id)ASSAWHEREST.stor_id=SA.stor_idANDSA.title_count=(SELECTCOUNT(*)FROMtitles)
注意,其中ASSA是必须的,必须为FROM子句中的SELECT语句指定别名
where子句中的子查询下来关键子在select语句的where子句中引入子查询allanyinexists使用all在子查询前面使用关键字all来确定对返回的每个值的比较是否为true.如果子查询不返回任何值,则搜索条件为true.(如果字查询不返回任何值,则对于所有零值条件为true)selectorder_num,stock_num,manu_code,total_pricefromitemswheretotal_price selectdistinctorder_numfromitemswheretotal_price>any(selecttotal_pricefromitemswhereorder_num=1005);单值子查询如果你指定子查询可能对外部级别查询返回刚好一个值,则不需要包括关键字all或any.可如同对待函数一样对待函数一样对待只返回一个值的子查询.这种子查询通常使用聚集函数,原因是聚集函数总是返回单个值.selectorder_numfromitemswhe
restock_num=9andquantity=(selectmax(quantity)fromitemswherestock_num=9);相关子查询selectpo_num,ship_datefromordersmainwhere10>(selectcount(idstinctship_date)fromorderssubwheresub.ship_date 集合运算标准集合运算联合,相交和差异允许你出来数据库信息.这三种运算允许你使用select语句在执行更新,插入或删除之后检查数据库的完整性.联合联合运算使用union关键字或运算符来将两个查询组合成单个符合查询.可以在两个或多个select语句之间使用union运算符来联合它们,并产生一个临时表,它包含存在于任何一个原始表或所有原始表中的行.还可以在视图的定义中使用union运算符.例子:selectdistinctstock_num,manu_codefromstockwhereunit_price<25.00unionselectstock_num,manu_codefromitemswherequantity>3;selectdistinctstock_num,manu_codefromstockwhereunit_price<25.00unionselectstock_num,manu_codefromitemswherequantity>3orderby2;将orderby与union配合使用selectdistinctstock_num,manu_codefromstockwhereunit_price<25.unionselectstock_num,manu_codefromitemswherequantity>3orderby2;使用unionall缺省情况下,union关键字排除重复的行.要保留重复的行,添加可选的关键字allselectstock_num,manu_codefromstockwhereunit_price<25.00
unionallselectstock_num,manu_codefromitemswherequantity>3orderby2intotempstockitem;使用不同的列名selectdistinctstatefromcustomerwherecustomer_numbetween120and15unionselectdistinctcodefromstatewheresnamematches'*a';将union与多个表配合使用selectstock_num,manu_codefromstockwhereunit_price>600.00unionallselectstock_num,manu_codefromctalogwherecatalog_num=10025unionallselectstock_num,manu_codefromitemswherequantity=10orderby2;相交两个行集的相交产生一个表,它包含同时存在于两个原始表的行.使用关键字exists或in来引入显示两个集合相交的子查询selectstock_num,manu_code,unit_pricefromstockwherestock_numin(selectstock_numfromitems)orderbystock_num;差异两个行集之间的差异产生一个表,它被包含在第一个行集中但不在第二个行集中的行,使用关键字notexists或notin来引入两个集合之间的差异的子查询.selectstock_num,manu_code,unit_pricefromstockwherestock_numnotin(selectstock_numfromitems)orderbystock_num;
下面开始学T-SQL的语法一.注释--单行注释,从这到本行结束为注释,类似C++,c#
中///*…*/多行注释,类似C++,C#中/*…*/二.变量(int,smallint,tinyint,decimal,float,real,money,smallmoney,text,image,char,varchar...)...语法:DECLARE{{@local_variabledata_type}}[,...n]例如:declare@IDint--申明一个名为@ID的变量,类型为int型三.在SQLServer窗口中打印出变量的值语法:PRINT'anyASCIItext'|@local_variable|@@FUNCTION|string_expr四.变量赋值
例如:--从数据表中取出第一行数据的ID,赋值给变量@id,然后打印出来Declare@IDint
Set@ID=(selecttop(1)categoryIDfromcategories)Print@ID在SQL中,我们不能像代码那样直接给变量赋值,例如@id=1,如果要达到这样的功能,可以这样写:Declare@IDintSet@ID=(select1)--类似@ID=1Select@id=1--类似@ID=1Print@ID五.变量运算(+,-,*,/,……)以下必要时候省略变量申明Set@ID=(select1+5)--类似@ID=1+5Set@ID=(select1-@ID)--类似@ID=1-@ID六.比较操作符>(greaterthan).<(lessthan).=(equals).<=(lessthanorequalto).>=(greaterthanorequalto).!=(notequalto).<>(notequalto).!<(notlessthan).!>(notgreaterthan).没什么说的七.语句块:Begin…end将多条语句作为一个块,类似与C++,C#中的{}例如:BeginSet@ID1=(select1)Set@ID2=(select2)End八.If,if…else…语法:IFBoolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]例如:If@idisnotnullPrint'@idisnotnullif@ID=1beginSet@ID=(select1+1)endelsebeginset@ID=(select1+2)end
上面的例子用到了比较操作符,语句块,和IF的语法.九.执行其他存储过程EXEC例如EXECdbo.[SalesbyYear]@Beginning_Date='1/01/90',@Ending_Date='1/01/08'十.事务语法:BEGINTRAN[SACTION][transaction_name|@tran_name_variable]例如BEGINTRAN--做某些操作,例如Insertinto…if@@error<>0BEGINROLLBACKTRANENDelseBEGINCOMMITTRANEND十一.游标我们可以在存储过程中用Select语句取出每一行数据进行操作,这就需要用到游标.语法:DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]]例如:DECLARE@au_idvarchar(11),@au_fnamevarchar(20)–申明变量--申明一个游标DECLAREauthors_cursorCURSORFORSELECTau_id,au_fnameFROMauthors--打开游标OPENauthors_cursor--取出值FETCHNEXTFROMauthors_cursorINTO@au_id,@au_fname--循环取出游标的值WHILE@@FETCH_STATUS=0BEGIN
Print@au_idPrint@au_fnamePrint''FETCHNEXTFROMauthors_cursorINTO@au_id,@au_fnameENDCLOSEauthors_cursor–关闭游标DEALLOCATEauthors_cursor--释放游标我觉得上面的是存储过程常用的一些东东,如果要更深入的了解,更详细的帮助,请参考SQLServer的帮助文
档
ROW_NUMBER()OVER(partitionbycidORDERBYidDESC):
列出序号,根据cid去分组,更加id去排序.列出行号.
SELECTROW_NUMBER()OVER(partitionbycidORDERBYidDESC)nmb,*FROMgf_disk
RANK()OVER(partitionbycidORDERBYidDESC):
列出分区内的排名,根据cid去分组,更加id去排序.列出行号.
NTILE(4)OVER(partitionbycidORDERBYidDESC):分发一个分区内的行到4个组中.SELECTNTILE(4)OVER(partitionbycidORDERBYidDESC)nmb,*FROMgf_disk
