测试-sql面试题InsertInto数据表名称字段名

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有书select*frombookwherepricein(30,40,50,60)字段名称[Not]Like"通配符"列出BOOK表中含电的所有记select*frombookwherepublishinglike*电*'列出BOOK表中第一个字是电的所有记录select*frombookwherepublishinglike'电*'--selectSum/Count/Avg/Max/MinAsFrom名sumselectsum(priceasfrombookcount量：统计BOOKselectcount(id)as数量frombookAVG算出BOOK价selectavg(price)as价格frombookMAX列出BOOK的selectmax(price)asfrombookMINselectmin(price)as宜书from交叉联接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行聚合函数名称。包括SUM、AVG、MIN、MAXCOUNT。SELECTRateId,ConnectionFee,RateFROMUTC_RateORDERBYCOMPUTESUM(ConnectionFee) SELECTRateId,ConnectionFee,RateFROMUTC_RateORDERBYCOMPUTESUM(ConnectionFee)SUM(RateBY puteby用法（小计指定SELECTAVG(DISTINCTRate)—-计算不重复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语句一起使用。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：据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：数据SQL>deletefromwhererowidin(selectrowidfroma3*wheree.rowid>(selectmin(x.rowid)fromaxwherex.bm=e.bmandSQL>答字段Id（可以自增）NameParendId其中ParendId点的ID查询ParendId参赛id，查询所有这个职位下的职位的记录。一个系统，有两，news表，new_class表，表结构如下：其中2的newsID字段是关联的，请写出您写出cla是101101的标题的SQL语句答：selectN.titlefromNewsN,News_classCwhereN.NewsId=C.NewsIdandC写出一条SqlA第31第40（SQLServer,以自动增长的ID作为主键,注意：IDSelecttop10*fromawhereidnotin(selecttop30idfromSQLSERVER服务器中，给定表table1中有两个字段ID、LastUpdateDate，ID表示更新的事务selecttop1idfromtable1orderbylastupdateselectidfromtable1wherelastupdate=(selectmax(lastipdate)from写出一条SqlA第31第40（SQLServer,以自动增长的ID作为主键,注意：IDSelecttop10*fromawhereidnotin(selecttop30idfrom有一人事表SQL句将EGTB1EGTB2Selectdeffdate(ss,2005-03-20','2005-