小实例网友jiaquwl搜集的labview都是常用功能非常方便数据库指令

被过滤1)30CREATETABLE1122…nnn][TABLESPACE表空间名称][STORAGE）][ENABLE约束名][DISABLE约束名]INSERTINTOVALUES(12n)INSERTINTOSTUDENTVALUSE(1003,UPDATESET1＝1，2＝2，…WHERE条件；B18；应该执行以下语句：UPDATESTUDENTSETAGE=18WHERENAME='B';DELETEFROMWHEREDELETEFROMSTUDENTWHEREALTERTABLEADD(ALTERTABLESTUDENTADD(DEPARTMENTALTERTABLESTUDENTMODIFY(NAMEDROPTABLEDROPTABLESELECT[DISTINCT|ALL]{*||视图名*…|视图名|}*…|FROM|视图名|[@[,[模式名.]{表名|视图名|快照名}[@数据库链名][WHERE[STARTWITHCONNECTBY[GROUPBY…[HAVING[UNION|UNIONALL|INTERSECT|MINUS]SELECT[ORDERBY{表达式|[ASC|DESC]|位置[ASC|DESC]}]…]STUDENT查询年纪为12的学生SELECTSTUDENT.NAMEFROMSTUDENTWHERESELECTSTUDENT.NAMEFROMSTUDENTWHEREAGEBETWEEN12ANDSELECTSTUDENT.NAMEFROMSTUDENTWHEREAGENOTBETWEEN12AND查询所有以A开头的学生的SELECTSTUDENT.NAMEFROMSTUDENTWHERENAMELIKESELECTAVG(AGE),SUM(AGE),MAX(AGE),MIN(AGE),MAX(AGE)-SELECT*FROMSTUDENTORDERBYNOSELECT*FROMSTUDENTORDERBYNO精妙SQL语被过滤CreateDATABASEdatabase-namedropdatabasedbname3sqldeviceUSEmasterEXECsp_addumpdevice'disk','testBack',BACKUPDATABASEpubsTOtestBackcreatetabletabname(col1type1[notnull][primarykey],col2type2[notA：createtabletab_newliketab_old(使用旧表新增新表)B：createtabletab_newasselectcol1,col2…fromtab_olddefinitiondroptabletabnameAltertabletabnameaddcolumncol注：列增加后将不能移除。DB2加varchar类型的长度。7AltertabletabnameaddprimaryAltertabletabnamedropprimary8、说明：新增索引：createunique]indexidxnameon移除索引：dropindex9、说明：新增视图：createviewviewnameasselect移除视图：dropview10sql选项：select*fromtable1where插入：insertintotable1(field1,field2)移除：deletefromtable1where更新：updatetable1setfield1=value1where寻找：select*fromtable1wherefield1like%value1like排序：select*fromtable1orderbyfield1,field2总数：selectcountastotalcountfromtable1求和：selectsum(field1)assumvaluefromtable1平均：selectavg(field1)asavgvaluefromtable1最大：selectmax(field1)asmaxvaluefromtable1最小：selectmin(field1)asminvaluefromA：UNION运算符UNION（TABLE1TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时（即UNIONALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。B：EXCEPTEXCEPTTABLE1TABLE2出一个结果表。当ALL随EXCEPT一起使用时(EXCEPTALL)，不消除重复行。C：INTERSECTINTERSECTTABLE1TABLE2一个结果表。当ALL随INTERSECT一起使用时(INTERSECTALL)，不消除重复行。A、leftouterSQL:selecta.a,a.b,a.c,b.c,b.db.ffromaLEFTOUTJOINbONa.ab.cB：rightouterjoin:C：fullouter1、说明：表(只结构,源表名：a新表名：b)(Access可用)法一：select*intobfromawhere1<>1法二：selecttop0*intobfrom2、说明：拷贝表(拷贝数据,源表名：ab(Accessinsertintob(a,b,c)selectd,e,ffromb;3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)(Accessinsertintob(a,b,c)selectd,e,ffrombinwhere例子：..frombin'"&Server.MapPath(".")&"\data.mdb"&"'where..41：aselecta,b,cfromawhereaIN(selectdfrombselecta,b,cfromawhereaIN(1,2,3)selecta.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>8、说明：between,between,notbetweenselect*fromtable1wheretimebetweentime1andtime2selecta,b,c,fromtable1whereanotbetween1and29、说明：inselect*fromtable1wherea[not]in1246deletefromtable1wherenotexistsselect*fromtable2wheretable1.field1=table2.field1select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhereSQL:select*fromwheredatediff('minute',f13sqlselecttop10b.*from(selecttop20fromorderby字段desc)a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段selecttop10*formtable1where15ba法可以用于每月榜,每月热销产品分析,按科目成绩,等等.)selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwhere16TableATableBTableC(selectafromtableA)except(selectafromtableB)except(selectafromselecttop10*fromtablenameorderbyselectnewid()Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbyselectnamefromsysobjectswheretype='U'selectnamefromsyscolumnswhere22type、vender、pcstypecaseselectcase。selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablenamegroupbytypetypevenderpcs计算机A1计算机A光盘B光盘AB3C323table1TRUNCATETABLEtable1selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc「where1=1where1=2if@strWhere!=''set@strSQL='selectcount(*)asTotalfrom['+@tblName+']where'+set@strSQL='selectcount(*)asTotalfrom['+@tblName+']'set@strSQL'selectcount(*)asTotalfrom@tblNamewhere1=1'+DBCCREINDEXDBCCDBCCSHRINKDBDBCCSHRINKFILEdbccexecsp_change_users_login'update_one','newname','oldname'RESTOREVERIFYONLYfromAlterDATABASE[dvbbs]SETSINGLE_USERDBCCCHECKDB('dvbbs',repair_allow_data_loss)WITHTABLOCKAlterDATABASE[dvbbs]SETMULTI_USERSETNOCOUNTONDECLARE@LogicalFileNamesysname,@MaxMinutesINT,@NewSizeUSEtablenameSelect@LogicalFileNametablename_log',@MaxMinutes=10,--Limitontimeallowedtowraplog.@NewSize=1--你想设定的日志文件的大小(M)--Setup/initializeDECLARE@OriginalSizeintSelect@OriginalSize=sizeFROMsysfilesWherename=Select'OriginalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),@OriginalSize)+'8Kpagesor'+CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+'MB'FROMWherename=@LogicalFileNameCreateTABLEDummyTrans(DummyColumnchar(8000)notnull)DECLARE@CounterINT,@StartTimeDATETIME,@TruncLogSelect@StartTime=@TruncLog='BACKUPLOG'+db_name()+'WITHDBCCSHRINKFILE(@LogicalFileName,@NewSize)EXEC(@TruncLog)--WrapthelogifWHILE@MaxMinutes>DATEDIFF(mi,@StartTime,GETDATE())--timehasnotAND@OriginalSize=(SelectsizeFROMsysfilesWherename=@LogicalFileName)AND(@OriginalSize*8/1024)>@NewSizeBEGIN--Outerloop.Select@Counter=0WHILE((@Counter<@OriginalSize/16)AND(@Counter<50000))BEGIN--updateInsertDummyTransVALUES('FillLog')DeleteDummyTransSelect@Counter=@Counter+1EXEC(@TruncLog)Select'FinalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),size)+'8Kpagesor'+CONVERT(VARCHAR(30),(size*8/1024))+'MB'FROMWherename=@LogicalFileNameDropTABLEDummyTransSETNOCOUNTexecsp_changeo