《SQLServer特殊功能语句.docx》由会员分享，可在线阅读，更多相关《SQLServer特殊功能语句.docx（32页珍藏版）》请在三一办公上搜索。
1、SQL文档TOPSELECTTOP查询前几或者前百分之几F-_F 口 n 1_|SELECT COUNT（古）FROM City18以SELECT TOP 5 古 FROM GCity-SELECT TOP 30 PERCENT * FROM ECityJ Fie如I忸Messages |No column nameJ_:13、 1IDCity |J_:1北京_2_22_上海_4_沈阳INSERT18； w| 2Q 21| 22SELECTi INSERT INTO City CitySELECT-FROM CityL SELECT TOP 10 古 FROM CityTOP 5 * FROM
2、 CityTOP 2 CityJJ Results 壹 Messages |ID,CitV1北京|22天津33上海44沈阳旧Citv1i 1北京|22天津33上海44沈阳55化京66天律IB； 19| 2Q 21 洞/SQLQuery36.sql.5DB (sd (55)*5aLQuEry3+5C|l.j SELECT TOP 5 * FROM CltyINSERT INTO Citv (CityiSELECT TOP 50 PERCENT City-FROH CityLSELECT TOP 10 * FROM QClty热1:1北京|22天津_33上海_4_沈阳思J顷|11北京_2_2天津_
3、3_3上海4沈阳55北京66天津UPDATESQLQuery36.sql.SDB (sa (55)* 5QLQuery34.sql. ,5DB (sa1专18|空迎iSELECTUPDATE-SELECTTOP 5 * FROM CityTOP|1 City SET City = N1O1O1TOP 5 市 FROM CityII Results | Lj) Message? |ID ICity1Ll.北京22天津33上海J_4沆阳52_长春J_：101022天津33上海44沈阳_5_5长春ID CitvSQLQueryS&.sql.SDB5习广5QLQuery34,sql, ,.5DB5日(
4、53J广1* 倔 ig| 2QlSELECTUPDATE-SELECTTOP 5 由 FROM CityTOP 50|) PERCENT Cit-7 SET City = N1 O1O1TOP 5 土 FROM CityjjJ Flewul住 | 金 Messages |IDCityJ_L1北京_2_2天津_3_3上海_4_4沈阳55长春IDCityJ_:1010_2_2010_3_3010_4_4沆阳_5_5长春DELETE_r _ i-a 17 is 项 洞urTOP 10 韦 FROM Cit.ySELECTDELETE TOP(3 FROH 0City-SELECTTOP 5 * FR
5、OM City4|J Results _J Messages |_| ResultsID )Cityw北京2天津3上海44沈阳55长寿_6_67石家庄ID5,长春2 |6 哈尔滨3 |7 石冢庄SQLQuer 【 1Q 19 ZQ Zl 22l_ 23 彝变量TOP后面是括号的，实际上可以使用变量或者表达式3e.sql.SDB (sa (55)* SQLQuery34.sql. .5DB (sa (53)*DECLARE CMT IMTSET CNT = 2SELECT COUNT(*) FROM CitVSELECT TOP(CWT + 5i * FROM CityUPDATE TOP (C
6、MT) City SET City = :NT 不认识SELECT TOP(CNT + 50) PERCENT * FROM City DELETE TOP(CNT + li FROM City-SELECT TOP (SELECT COUNT - 3 FROM CITYI Fl esults 直 Messages (No column name批量INSERTINSERTINTO TABLE1(A,B)SELECT col1,col2 FROM TABLE2UPDATE使用一个表的数据更改另一个表的数据,类似于表连接UPDATEaSETa.column=b.columnFROMTABLE1
7、aINNER JOIN TABLE2b ONa.ID =b.ID3iji_ijuEryLtD.aqi.3UD- nLuer yto.bLii. . judiq乙)isiis|18 UPDATE19|商2 1|22j23：SELECTSELECT* FROM GCity古 FROM AliasaSET a. City = b MaineFROM City a-IN1TER JOIPJ Alias b OM a ID = to.CitylDL SELECT * FROM City叵匚如J_口北京_2_天津J_M上海J_沈阳_5_长舂J Results | 壹 M&ssages Name魔都日T 3
8、 | 4 盛京ID匚加1:1北平_2_p天津三魇都盛京_5_长春DELETE使用一个表的数据删除另一个表的数据,类似于表连接DELETE aFROMTABLE1 aINNERJOINTABLE2 b ON a.ID = b.IDsijLijuer-很 17 tag ig| 204b.sqi.uu sa bLJueryHi.sqi. .ut； sa i明广* FROM City* FROM GAliasSELECTSELECTDELETE FROM Cit.y a-INNER JOIM Alias b OU a ID = b CitylD1- SELECT 宣 FROM City天津 上海沈阳长
9、春5树结构递归（存储省空间）和闭包表（查询省时间）递归（邻接表:依赖父节点）经常用的父节点ParenlD，SQL里最新功能有递归查询16SELECT * FROM Address171Bg Zl| 切w23：24ZSZ6洞 洞19 ; WITH CTE uecLiusion AS (SELECT ID ParentID NAMEFROM AddressWHERE ID = 1UNION ALLSELECT a IDf a ParentIDf a NAMEFROM Adduess aINNER JOIN CTE_recur5ion to OU a Parent ID = fe IPSELECT
10、* FROM CTE recursionJ ResultsMessages IDParentIDName1| 1i 0中国220英国330美园441江宁_5_51北京664沈阳714大连88G皇姑99610102枪敦11113纽约IDPareritl DNAME110中国241辽宁51北京_4_64沈阳514大连686皇姑79E大东闭包表（祖先-后裔）一般用在经常查询树结构 在闭包表的设计中，额外创建了一张TreePaths的表（空间换取时间）TreePaths表存储了所有祖先-后代的关系的记录（包括自己到自己）444546SQLQuery34. sql.aster (sa (59) SQLQ
11、uerT.sql .ster (sa (58)* | 文SELECT * FROM AddressSELECT * FROM TreePathsS ResultsIDName |11中国2._2英国33美国44坷宇_5_5北京Ge沈阳1_7大连88皇姑99大东1010伦敦1111纽约ID|AncestorDescendantJ_| 1. . 21114315_4_416517_6_G18719_8_822J_921010133111311121441314G1414715148161.4917155181GG19168SQLQuety34s：qL.m*r (ga (S9) yQLQiietyg
12、.sql .ster (sa (56)* f SQLQuery45? 46j 铜 48| 雹: 50( 51| 52, m；SELECT * FROM AddressSELECT * FROM TreePathsSELECT a.方rROM Address aINNER JOIN TreePaths b ON a.ID = b.Descendant inner join Address c ON to.Ancestor = c.ID- where c. Nare =】中国】1LJ Results 壹 Messages ID| Name |J_i ii中国22英国J_ 3美国J_ 4辽宁兰_ 5
13、北京_ 6沈阳2_ 7大连_ 8皇姑9大东1010伦敦1111纽约IDName |1j 1中国24辽宁35北京46沈阳_5_7大连68皇姑79大东5QLQuer/34.sc|l. .aster (sa (55) 5QLQuery47.sql .ster (sa (56)* SQLQueryE45：用同 4S| E 5152匚7 :SELECT 古 FROM Add.tess-SELECT 卡 FROM TreePatlisSELECT c.市FROK kd.d.ress aI NITER JOIPT Tr P at lis b OPT a ID = b D s c iidatiti line
14、r join. Addr ess c OM b JLn.c es tor = c ID-where a. Maine =】大东Jj Results | |_j Messages ID| Name1| 1:中国l|22英国J_3美国44辽宁Is5北京Is_6沆阳IF7大连_8_8皇姑llg9大京1010伦敦1111纽约IDName1i 1 i中国|24辽宁I 3G沈阳_4_a太东另外，闭包表还可以优化，如增加一个path_length字段，自我引用为0,直接子节点为1, 再一下层为2, 一次类推，查询直接自子节点就变得很简单。设计表数量查询子查询树插入删除引用完整性邻接表1简单简单简单简单是枚举
15、路径1简单简单简单简单否嵌套集1困难简单困难困难否闭包表2简单简单简单简单是1、邻接表是最方便的设计，并且很多软件开发者都了解它。并且在递归查询 的帮助下，使得邻接表的查询更加高效。2、枚举路径能够很直观地展示出祖先到后代之间的路径，但由于不能确保引 用完整性，使得这个设计比较脆弱。枚举路径也使得数据的存储变得冗余。3、嵌套集是一个聪明的解决方案，但不能确保引用完整性，并且只能使用于 查询性能要求较高，而其他要求一般的场合使用它。4、闭包表是最通用的设计，并且最灵活，易扩展，并且一个节点能属于多棵 树，能减少冗余的计算时间。但它要求一张额外的表来存储关系，是一个空间换取 时间的方案。改数据在表
16、上右键选择Edit Top 200 Rows= XQuery 山|母出:回Change Tjjie集1国 , I写甘瓯伐二|与窗A 二 A*旬Object EKplorer，甲USEft215JMMD.ssctTMEatalog SQLQuBryS sq . MD0 (无(弱)SQLQuary4?.sq. .FTE(5a (E4)ConnectT 智IDTenartlDCle-itlDDepartnrenHDProcLirtLneDSLani 55/nonymsgnZ1Z0W1 Proganoatility| Serxice Broker| Stcraqe=$=：K 彩 ：3u21100W彩
17、海餐中uEID00W5ecLrty5u渗00wV-. jfuffirR)盅:嵌 薮喙&u2300014!P.1DBI DaUbase Diagrams| Tables：7u21D0014S：S:家苦w：sIE：u2120014Q System Tabless=：sy11小11114二I dbo.Jentcradb口 ClEntFtodLtUre100Z310nWdboaDictLangu=igeTerk=iritILnZ3t0nwJ dbo-DownLoadflrril120Z1600w_3 dbD.FileFiclid3 dbo.FifeFlefcl5idueT dba.FilaR.al13
18、0Z1200wamWil颇I叫Am1 dbc.FiteSeverJ dbo.FilaVer j dbo.IhigltErri展开编辑前200条界面后，点击SQL （如图中的红圈），会出现查询的SQL在SQL中添加WHERE条件，可以选定你想要改的具体数据行，直接可以手动修改数据改完后点击一下其他行，立刻更改数据，想看结果，点击叹号（如图中的红圈）如此，修改少数几条数据时，不会出现误操作。El闿FROM WHERE.USERZ LjLTMMD.etTMUtalogf41ayjUBtya+.H. MDB (sa (M)J y3QUguffV47. pq. .MDB(3 (54) SELECT7OP
19、(2D：i；i Dj TeruitEDj drUD, DepMUD, FTOLtLlneiIDi 5LanoueiDi TLangudjeilDi Dieted, CreateCn, CfeMefly, Mcdfyt TMNanWj FltaWuSj IndjstryldLAssetTTrlC-atdag(ID 二 5)normsProgmmab 叫Seruice BrckfifIElnreeSeoul珈M5C6(0fflne)MCfiDatabaseTaH或Ij SsteirilEbleg1 o.ClBntCrg1 o.ClBntPr(xiulLinBl dbci,口tctLamuaoa伯n
20、artTenarWDpartmentCDFrodudtllreED5Lnguii 簧】口TLngua 康的J：IM&J：QblEwt Ewplorar开窗函数OVER聚合函数5QLQuery32.sql .ster (sa (54)* 5QLQuery28.sql. .aster (sa (53)3QLQuery26. sql . .ster(5cidSELECT COUNTFROM TMSSDB.dtoo.enUS WordSQLQuery32.sql .ster (sa (54)* 5QLQuery28.sql. .aster (sa (53)5QLQuery26.sql . .ster
21、(sa (57)*1| SELECT ID, COUNT (*) FROM TMSSDB . dbo . enUS_Word妇_J MessagesHsg S1Z0, Level 16, State 1, Line 1Ccli-uoii 1 TMSSDB. dlzio- enUS_TiJcird- ID1 is invalid in the select- list because it is not coiitai5i.Qunr32.6ql ca (54养/吟邮.心.*财住貌 | SXQuwty.Lql. .st J _j 亡七 |IDiNo calumnafTMlWord| |No cdu
22、rriFkWiel0NCDNiZiR3a1*673NCDNCDga相5NCDNCHOC3_B.9倒NCENCELL3Q.135933hKEncsrtfi:ale-eJ.1Z77KMCENOEKQ.蛔rxencrfsELBE15ncBnew8a14124rEft 凶 ng日0idlddmamd6H176EF见B4.G蹄NcfNd2EL.4B95ndrclg2Q的。心f-2tf2051Wnrgregia243B7rh方B-14442心nckofized23此WgnchFiChrCrt2H231W的MCHNLHAN2jELijiennchrciiou!-&J_117752rthrchciifi230
23、1195B7NChNO寻5盅夷1392*Whrhtftk23a.诚英！mhnchaioe423EL.NCH套&谣?由NCHNCHAFljeipcp230恢暗nchnchwrwl爸_B.1334?rthr*f h 小EZiQ.1377D1mhnchacclabzfispecbi.isize291ZP7IEhCH ivhZ3rvh.MrrJlah-iicrwhiiliwKISUM() OVER(PARTITION BY 分组条件 ORDER BY 排序条件)排名函数ROW_NUMBER()RANK()DENSE_RANK()NTILE(4)ReuI坛 | J Mecsaoes 千安石超匡撤王壬石王
24、妄石开窗函教.可-IIS- Egs叫* TM解析单词.叫-.EH 3 (53)分解项目目命吾言代.心(57)倒建祟集主引.ESELECTSELECTiiiu.i FROHSELECTSELECTJiui.i FROH 9： num rRon 口数据表ORDER HT 金摭 DE3C：* FROM M敦姑表* r RDW_NUMBER () CVER (PARTIT ION E 姓名 ORDER HY 垒成 DESC*r DENSE RANKf) CVER (PARTIT ION EY 姓名frr RANK() CVERiPARTITICfJ EY 支上名 OEDER BY 至箭 DESC j工
25、安石姓名 |金5 | PMErTrurn1 G i工宗石I n I ru-n I.以sa (55).1 ；由国吉I, 赴匡侃3 巡匡胤1 赵压剧5 王妄石王&右王安右工安石制匡胤翻匡胤退匡周翅匡撤制匡胤翻匡胤退匡周6千安石10C7王壬石10CC王安石田王&右UNION (慢)合并且去重UNIONALL (快)合并不去重INTERSECT交集，和表连接效果差不多，但是必须列个数相同，列类型可转换EXCEPT差集，SELECT ID FROM TABLE1EXCEPTSELECT ID FROM TABLE2得到第一个结果集中，和第二个结果集不相同的数据CROSSAPPLY, OUTERAPPLY
26、用来连接两个表，使第一个表的每个列都在第二个表中使用一次SELECT a.字段,b.字段FROM () a CROSS APPLY(where 字段=a.字段)bXML逗号组合列/ 辑| W|旭鼬堂V = JF -日 DECLARE口原表WELE ( 姓名 MVARCHAR 10), 金额 DECIMAL I IS, 2逗号分81蛆合.sql -.r (si(52)*SQLQuerySS.sql, .aster (sa(S3) 5QLQuery26,sql.ster (sa(57q一23|垂免g|10i盘i送14? 15| ie!i硬INSERTITJSERTIMSERTINSERTINSER
27、TIHSEKTINSERTITJSERTSELECTINTOINTOINTOINTOINTOINTOINTOINTO日原表姓名金蓊 归原表姓名金版 日原表姓名金 0原表姓名金楚 刊原表姓名金额 刊原表（姓名金版 口原表姓名金赣 8原表姓名金箭* FROM E原表VALUES(N1VALUES(N1VALUES(N1VJLLUES (WVALUES(WVALUESVALUES(NVALUES(N胤胤Ja胤石石石石国EJ国国务多还节sfefe王王王王I 11 I-.|1 _y1 i o 5姓名(SELECT 8NVERT 皆收IUR :叽 金颔)+ 1 |tRQ1I 质表 miiERE 姓名=t
28、姓名 FQR ZUL PJTH ( 1 1 )金箭 FROM E底表t-GROUP EY 姓名白 SELECT0 2C 2 is口 Results Message?姓名金额|180.002_赴匡周50.003赵匡屈13.00_4_赴匡胤100.00p王安石18.00_6_王安石100.00p王安石0.008王安右59.00姓名金额1住安右18.00,100.00,0.00,59.00, llllllllllllllf2赴匡照80.0030.00,13.00.100.00,逗号分割列zr (h 5E)K 5QLQlet沱9,如,，皿ber 3 慎助5QLQuet2& ,州，点如 3 He dec
29、lare 口l亨.在 tjlble ( Y 7-S NVARCHkR ： in), W 会敏 NVARCHkR ： I n -in匚 匚dis-FEJGDil-s:3!尘5:6?B! 111- 111111I WTO 口原表(姓名.金额 I UJiLtTESINgUL rHSO, SD, 13 H ! | UU5IRT IMTO E.巨去 姓名 金额 UJ1LLTE5寸三安石*时1. , 59)SELECT ， FROH G原表一一SELECTCONVERT | ZiNL, 1 1 4 REPLACE J . 1 / 1 ) +FK-IL足原表SELECT :姓名y金额FROKSELECT .
30、-CONVERT i KMLr REFL 色。E i 宇施，z :;:dt3 )FROH :原表)h十 1 | Ktnl2 1；ICRC3S ATPLT |SELECT Xodc,vLoic;al：.z，./ UAECEUR(IO)1)金额FRCffl 氏理!L；/U忘/data ) Node node)V巷g _|逾1!盈虱 fin rr 1 =； nn2王安石违.ILL,灼3I R 肱uk#M匚必agew，归金割1;赳匡I3L:如2赳默L603制匡IM134超国用1005T77u106王翔1001王安石UaT珂il53PIVOTUNPIVOT行转列，列转行下图是结果集转置SQL,tr (d
31、SOLQuerySCj sql . acter sa (53) 5QLQucry26.3ql. .stcr (sa (57)：*DECLARE E原而 TAELE (舛字I MVJlRCHAb! I 1UIKT TIKIKJ:L.：iIL-b 16 17IS 19 2 0 2 1insert INTO (3原京姓名,数学活反9 insert INTO演表姓名数学语文夕 insert into (3原夸姓名,数学诂丈? INSERT into源表姓名数学语文夕 INSERT into鲸表舛名，数学语文夕ikicAr-ArAr处LUEW赵巨胤七扣mi 13 LOOVALUES13, LDD 0 3
32、9VALUES, 133 7 j 5 j-iVALUES,27, 17 7 37VALUES (K, 1, 3Z 3 70SELECT姓名外语政治数学语文TROJI I!原表SELECT *Fmu E原表tUNPIVOT FOR 程 IM (怠学二：诰艾：外饴政治)upvt_PIVOT (MAX (data) FCR姓名 功担匡清匚，：王奁七,【祖冲之铁木真河骨打】Pt新函数一个表(结果集)A更新另一个表B【有判断条件】，根据判断条件的真假，插入新数据 或者更新旧有数据动态SQL5QLQuery36.sql .ster (sa (52)*2j4 SET QSQL = 1SW789：无变量H D
33、ECLARE QSQL DIV ARCH AR 4000SELECT TOP 1000 TTord f Heading FROM Access .dtoc .Pictionary-PRINT (0SQL)| -EXEC (QSQLJ1|J Results | Messages WordMeaningJ_:A日一,一个.一只，一件站 E： Id :一道2a adjoinn：毗连3a bad accidertri横事4a bad cropn：欠产5a bad lotri：狐薛狗党6a bad plann；下策J_ .1a bad reputation.亚史1 I. !-11 1有变量（可以OUTP
34、UT）5QLQuer37.sql . ,5ter (汩 153)*5QLQuery36.sql .ster (w (52)*20 DECLARE SQL WARCHAR ( 4000) ,Word PJVARCHARf 10 3： SET Word = ! trippingly 3:翩Bf 翩然；! trippingly1白 SET GSQL =:l311SELECT TOP ! Word,MeaningFROM Access . dbo . Dict-ionaryWHERE Word = Word_0-PRINT(GSQL)-EXEC sp executesql SQL M1 QliJord
35、 WARCHAR (10) 1 , QliJordJ Results | Messages Ward Meaning存储过程内容f *13 -ST | 2=1 | 白 3 华51 世旦(寸盘 =I = = .+日亍存储过程内容.wql -.B (sa (55)* 批量,sql - USER. .cess (sa (53) 5QLQuery3S.sql.e 1汩 SELECT R0UTINE_NiME7R0UTINE_DEFINITI0N 辫 FROM INFORMATION_SCHEMA.ROUTINES3 L HHERE ROUTINE_DEFINITION like 1Joh_TM1时 J Results | Mes
