测试数据库系统理解sql语句

系统理SQL语ascdescMssqlaccessSQLDCL—数据控制语言1CREATEDATABASEdatabase-namedropdatabase3sqldeviceUSEmasterEXECsp_addumpdevice'disk','testBack',BACKUPDATABASEpubsTOtestBackcreatetabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)A：createtabletab_newliketab_oldB：createtabletab_newasselectcol1,col2…fromtab_old5droptabletabnameAltertabletabnameaddcolumncolvarchar7、说明：添加主键：Altertabletabnameaddprimary说明：删除主键：Altertabletabnamedropprimary8、说明：创建索引：create[unique]indexidxnameontabname(col….)删除索引：dropindexidxname9、说明：创建视图：createviewviewnameasselect删除视图：dropview10sql选择：select*fromtable1where插入：insertintotable1(field1,field2)删除：deletefromtable1where更新：updatetable1setfield1=value1where查找：select*fromtable1wherefield1like’%value1%’like排序：select*fromtable1orderbyfield1,field2总数：selectcountastotalcountfrom求和：selectsum(field1)assumvaluefromtable1平均：selectavg(field1)asavgvaluefromtable1最大：selectmax(field1)asmaxvaluefromtable1最小：selectmin(field1)asminvaluefromtable1A：UNIONUNION（TABLE1TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时（即UNIONALLTABLE1自TABLE2。B：EXCEPTEXCEPTTABLE1TABLE2复行而派生出一个结果表。当ALL随EXCEPT一起使用时(EXCEPTALL)，不C：INTERSECTINTERSECTTABLE1TABLE2行而派生出一个结果表。当ALL随INTERSECT一起使用时(INTERSECTA、leftoutersql:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINba.a=B：rightouterC：fulloutersql1、说明 表( 结构,源表名：a新表名：b)(Access可用法一：select*intobfromawhere法二：selecttop0*intobfrom2、说明：拷贝表(拷贝数据,源表名：a：b)(Accessinsertintob(a,b,c)selectd,e,ffromb;3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)(Accessinsertintob(a,b,c)selectd,e,ffrombinwhere例子：..frombin'"&Server.MapPath(".")&"\data.mdb"&"'41：aselecta,b,cfromawhereaIN(selectdfrombselecta,b,cfromawhereaIN(1,2,3)5selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b61：aselecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=7、说明：视图查询(表名1：aselect*from(SELECTa,b,cFROMa)Twheret.a>betweenselect*fromtable1wheretimebetweentime1andselecta,b,c,fromtable1whereanotbetween1and9、说明：inselect*fromtable1wherea[not]in1’,’2’,’4’,’值10deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1)11select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....12、说明：日程安排提 提sql:select*fromwheredatediff('minute',f13sqlselecttop10b.*from(selecttop20主键字段,排序字段from表名orderbydesc)a,bwhereb.a.orderbya.排序字段1410selecttop10*formtable1where15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b)16TableATableBTableC(selectafromtableA)except(selectafromtableB)except(selectafromtableC)1710selecttop10*fromtablenameorderbyselectnewid()19Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2,...)20selectnamefromsysobjectswhere21selectnamefromsyscolumnswhere22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便selectcase。selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablenamegroupbytypetypevender电脑A电脑A光盘B光盘ABCTRUNCATETABLEtable1241015selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc随机选择数据库记录的方法（RandomizeSQLASP“找个随机数”然后打印出来。实际上常见的解RNumber=Int(Rnd*499)WhileNotIfobjRec("ID")=RNumber...这里是执行...endif1500（500就是数据库内记录的总数ID的值、检查其是RNumberTHENRNumber495，500RNumber=Int(Rnd*499)+sql="SELECT*FROMCustomersWHEREID="&RNumbersetobjRec=ObjConn.Execute(SQL)Response.WriteRNumber&"="&objRec("ID")&""&RNumberID，你只需要检查匹配情况即可。只要你对以上代码的工作满意，你自可按需操作“随机”记录。Recordset示SQL为了取出几条随机选择的记录并存放在同一recordset内，你可以三sql="SELECT*FROMCustomersWHEREID="&RNumber&"ORID="&RNumber2&"ORID="&RNumber3假如你想选出10条记录（也许是每次页面装载时的10条的列表，你BETWEENSELECT语句只显示一种可能（ID：sql="SELECT*FROMCustomersWHEREIDBETWEEN"&RNumber&"AND"&RNumber&"+9"9随机若干条记录，测试Access：SELECTtop10*FromORDERBYRnd(id)sqlserver:selecttopn*from表名orderbynewid()mysqlelect*From表名OrderByrand()LimitnAccess,AccessAccessSQLelecttable1.fd1,table1,fd2,table2.fd2Fromtable1leftjointa