測(cè)試-sql面試題InsertInto數(shù)據(jù)表名稱字段名

InsertInto稱(字段名稱1,2,values(字段值1,字段值2,...)insertintouser(username,password,age)values('李老四','6666',45)Update名稱Set稱=字段值,字段名稱=字段值,...[Where件DeleteFrom下列查詢返回在LONDON（倫敦）或SEATTLE（西雅圖）的所有雇員：SELECT*FROMemployeesWHEREUPPER(city)IN('LONDON'，'SEATTLE')下面示例利用DATEDIFF定在pubsSELECTDATEDIFF(day,OrderDate,getdate())ASno_of_daysFROM返回字符串"wonderfultitlesnotes的位置SELECTCHARINDEX('wonderful',顯示工作站的名稱：selecthost_name()asClientComputertitlestitles中18索前9SELECTTOP50PERCENTtitleFROMtitles字段名稱[NotBetween始值and列出BOOK中3050select*frombookwherepricebetween30and字段名稱[Not]In(列出值1,列出值BOOK格為30,40,50,60有書(shū)select*frombookwherepricein(30,40,50,60)字段名稱[Not]Like"通配符"列出BOOK表中含電的所有記select*frombookwherepublishinglike*電*'列出BOOK表中第一個(gè)字是電的所有記錄select*frombookwherepublishinglike'電*'--selectSum/Count/Avg/Max/MinAsFrom名sumselectsum(priceasfrombookcount量：統(tǒng)計(jì)BOOKselectcount(id)as數(shù)量frombookAVG算出BOOK價(jià)selectavg(price)as價(jià)格frombookMAX列出BOOK的selectmax(price)asfrombookMINselectmin(price)as宜書(shū)from交叉聯(lián)接SELECT*FROMtable1CROSSJOINselectx.[name],y.[name]fromxleftjoinyonx.[refid]=y.idselecty.[name],x.[name]fromxrightjoinyonx.[refid]y.idSELECTt1.ProductID,t2.VendorID,t2.NameFROMtable1t1,table2t2WHEREt1.VendorID=t2.VendorIDANDStandardPrice>$10ANDNameLIKE'F%'SELECTProductID,talbe2.VendorID,NameFROMtable1JOINtable2ON(table1.VendorID=table2.VendorID)WHEREStandardPrice>$10ANDNameLIKECOMPUTEby可選BY行聚合函數(shù)名稱。包括SUM、AVG、MIN、MAXCOUNT。SELECTRateId,ConnectionFee,RateFROMUTC_RateORDERBYCOMPUTESUM(ConnectionFee) SELECTRateId,ConnectionFee,RateFROMUTC_RateORDERBYCOMPUTESUM(ConnectionFee)SUM(RateBY puteby用法（小計(jì)指定SELECTAVG(DISTINCTRate)—-計(jì)算不重復(fù)Rate的avgFROMUTC_RateSELECTdistinctRateFROM表 SelectSELECT*intonewtableFROMSELECTCountry,FirstName,LastNameFROMEmployeesUNIONSELECTCountry,NULL,NULLFROMRegions;子查詢SELECTtitleFROMtitlesWHEREadvance>(SELECTFROMpublishersINNERJOINtitlesONtitles.pub_id=publishers.pub_idWHEREpub_name='NewMoonSELECTtitleFROMtitlesWHEREadvance>all(SELECTFROMpublishersINNERJOINtitlesONtitles.pub_id=publishers.pub_idWHEREpub_name='NewMoonSELECTfieldFROMtable1WHEREidIN(SELECTFROMWHEREtitle_idIN(SELECTtitle_idFROMtable3WHEREtitle_idlike--EXISTSNOTEXISTS查找交集與差集SELECTDISTINCTfieldFROMtable1WHEREEXISTS(SELECT*FROMtable2WHEREtable1.field=SELECTDISTINCTtable1.fieldFROMtable1INNERJOINtable2ONtable1.field=SELECTDISTINCTFROMtable1WHERENOTEXISTS(SELECT*FROMtable2WHEREtable1.field=SELECTDISTINCTFROMWHEREfieldNOTIN(SELECTfieldFROMtable2)指定組或聚合的搜索條件。HAVING只能與SELECT語(yǔ)句一起使用。HAVING通常在GROUPBY子句中使用。如果不使用GROUPBY子句，則HAVING的行為與WHERE子句一樣。SELECTid,SUM(price)FROMSalesGROUPBYidHAVINGSUM(price10.00--$10.00IDORDERBYid更新表UPDATESETprice=price*2WHEREpub_idIN(SELECTpub_idFROMpublishersWHEREpub_name='NewMoon1：找出那些工資高于他們所在部門的managerselectw.ename,w.sal,w.mgr,m.empno,m.ename,m.salfromempw,empmwherew.mgr=m.empnoand2:哪些人是selectdistinctm.enamefromempw,empmwherew.mgr=m.empnoselectenamefromempwhereempnoin(selectmgrfromselectenamefromempowhereexists(select'a'fromempiwhere3：哪些人不是selectm.enamefromempm,empwwherem.empno=w.mgr(+)andw.mgrisselectenamefromempwhereempnonotin(selectmgrfromempwheremgrisnotselectenamefromempowherenotexists(select'a'fromempiwhereselectename,emp.deptno,dept.deptno,dnamefromemp,deptwhereemp.deptno(+)=dept.deptnoandename(+)='SMITH'andenameisnullSQL>selectrownum,last_name,from(selectlast_name,fromorderbysalarywhereROWNUM 123不用max5：據(jù)SQL>selectlast_name,from(selectrownuma,froms_empb)4*wherea=3SQL> SQL>lselectlast_name,from(selectrownuma,froms_empbwhererownum<=5)4*whereabetween3and5SQL> SQL>selectlast_name,dept_id,froms_empwheresalary>(selectfromwhere10rowsSQL>selecta.last_name,a.salary,a.dept_id,froms_empa,(selectdept_id,avg(salary)fromgroupbydept_id)wherea.dept_id=b.dept_id6*anda.salary>b.avgsalSQL>10rowsSQL>selectrowid,e.*froma2*wheree.rowid>(selectmin(x.rowid)fromaxwherex.bm=e.bmandx.mc=e.mc); AAABdcAAGAAAAYyAAE1111AAABdcAAGAAAAYyAAF1112AAABdcAAGAAAAYyAAG1113AAABdcAAGAAAAYyAAH11149：數(shù)據(jù)SQL>deletefromwhererowidin(selectrowidfroma3*wheree.rowid>(selectmin(x.rowid)fromaxwherex.bm=e.bmandSQL>答字段Id（可以自增）NameParendId其中ParendId點(diǎn)的ID查詢ParendId參賽id，查詢所有這個(gè)職位下的職位的記錄。一個(gè)系統(tǒng)，有兩，news表，new_class表，表結(jié)構(gòu)如下：其中2的newsID字段是關(guān)聯(lián)的，請(qǐng)寫(xiě)出您寫(xiě)出cla是101101的標(biāo)題的SQL語(yǔ)句答：selectN.titlefromNewsN,News_classCwhereN.NewsId=C.NewsIdandC寫(xiě)出一條SqlA第31第40（SQLServer,以自動(dòng)增長(zhǎng)的ID作為主鍵,注意：IDSelecttop10*fromawhereidnotin(selecttop30idfromSQLSERVER服務(wù)器中，給定表table1中有兩個(gè)字段ID、LastUpdateDate，ID表示更新的事務(wù)selecttop1idfromtable1orderbylastupdateselectidfromtable1wherelastupdate=(selectmax(lastipdate)from寫(xiě)出一條SqlA第31第40（SQLServer,以自動(dòng)增長(zhǎng)的ID作為主鍵,注意：IDSelecttop10*fromawhereidnotin(selecttop30idfrom有一人事表SQL句將EGTB1EGTB2Selectdeffdate(ss,2005-03-20','2005-