经典SQL语句大全

1SQLCREATEDATABASEdatabase-namedropdatabasedbnamesqlserver---deviceUSEmasterEXECsp_addumpdevice'disk','testBack','c:\mssql7backup\MyNwind_1.dat'---BACKUPDATABASEpubsTOtestBackcreatetabletabname(col1type1[notnull][primarykey],col2type2[notnull],..)createtabletab_newliketab_old()createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly2droptabletabnameAltertabletabnameaddcolumncoltypeDB2varcharAltertabletabnameaddprimarykey(col)Altertabletabnamedropprimarykey(col)create[unique]indexidxnameontabname(col….)dropindexidxnamecreateviewviewnameasselectstatementdropviewviewname10sqlselect*fromtable1whereinsertintotable1(field1,field2)values(value1,value2)deletefromtable1whereupdatetable1setfield1=value1whereselect*fromtable1wherefield1like’%value1%’---like!select*fromtable1orderbyfield1,field2[desc]3selectcountastotalcountfromtable1selectsum(field1)assumvaluefromtable1selectavg(field1)asavgvaluefromtable1selectmax(field1)asmaxvaluefromtable1selectmin(field1)asminvaluefromtable111UNIONUNIONTABLE1TABLE2ALLUNIONUNIONALLTABLE1TABLE2EXCEPTEXCEPTTABLE1TABLE2ALLEXCEPT(EXCEPTALL)INTERSECTINTERSECTTABLE1TABLE2ALLINTERSECT(INTERSECTALL)12leftouterjoin4SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.crightouterjoin:full/crossouterjoin12:Groupby:count,sum,max,min,avg)SQLServertext,ntext,imageselecte13sp_detach_db;sp_attach_db14.:sp_renamedb'old_name','new_name'5ab)(Access)select*intobfromawhere11SQlServerselecttop0*intobfromaab)(Access)insertintob(a,b,c)selectd,e,ffromb;)(Access)insertintob(a,b,c)selectd,e,ffrombin‘’where..frombin'&Server.MapPath(.)&\data.mdb&'where..ab)selecta,b,cfromawhereaIN(selectdfromb):selecta,b,cfromawhereaIN(1,2,3)selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)bab)selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c6a)select*from(SELECTa,b,cFROMa)Twheret.a1;between,between,notbetweenselect*fromtable1wheretimebetweentime1andtime2selecta,b,c,fromtable1whereanotbetween1and2inselect*fromtable1wherea[not]in(‘1’,’2’,’4’,’6’)10deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1)11select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....12SQL:select*fromwheredatediff('minute',f,getdate())513sqlselecttop10b.*from(selecttop20fromorder7bydesc)a,bwhereb.=a.orderbya.declare@startint,@endint@sqlnvarchar(600)set@sql=’selecttop’+str(@end-@start+1)+’+fromTwhereridnotin(selecttop’+str(@str-1)+’RidfromTwhereRid-1)’execsp_executesql@sqltopRidtoptop1410selecttop10*formtable1where15.)8selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b)16TableATableBTableC(selectafromtableA)except(selectafromtableB)except(selectafromtableC)1710selecttop10*fromtablenameorderbynewid()18selectnewid()191),deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2,...)2),selectdistinct*intotempfromtablenamedeletefromtablenameinsertintotablenameselect*fromtemp3),9altertabletablename--addcolumn_bintidentity(1,1)deletefromtablenamewherecolumn_bnotin(selectmax(column_b)fromtablenamegroupbycolumn1,column2,...)altertabletablenamedropcolumncolumn_b20selectnamefromsysobjectswheretype='U'//U21selectnamefromsyscolumnswhereid=object_id('TableName')22typevenderpcstypecaseselectcaseselecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablenamegroupbytypetypevenderpcsA1A1B2A210B3C323table1TRUNCATETABLEtable1241015selecttop5*from(selecttop15*fromtableorderbyidasc)table_orderbyiddesc1=11=2SQL“where1=1”“where1=2”if@strWhere!=''beginset@strSQL='selectcount(*)asTotalfrom['+@tblName+']where'+@strWhereendelsebegin11set@strSQL='selectcount(*)asTotalfrom['+@tblName+']'endset@strSQL='selectcount(*)asTotalfrom['+@tblName+']where1=1'+@strWhere--DBCCREINDEXDBCCINDEXDEFRAG--DBCCSHRINKDBDBCCSHRINKFILEdbccshrinkdatabase(dbname)execsp_change_users_login'update_one','newname','oldname'goRESTOREVERIFYONLYfromdisk='E:\dvbbs.bak'12ALTERDATABASE[dvbbs]SETSINGLE_USERGODBCCCHECKDB('dvbbs',repair_allow_data_loss)WITHTABLOCKGOALTERDATABASE[dvbbs]SETMULTI_USERGOSETNOCOUNTONDECLARE@LogicalFileNamesysname,@MaxMinutesINT,@NewSizeINTUSEtablename--SELECT@LogicalFileName='tablename_log',--@MaxMinutes=10,--Limitontimeallowedtowraplog.@NewSize=1--(M)Setup/initializeDECLARE@OriginalSizeintSELECT@OriginalSize=sizeFROMsysfiles13WHEREname=@LogicalFileNameSELECT'OriginalSizeof'+db_name()+'LOGis'+CONVERT(VARCHAR(30),@OriginalSize)+'8Kpagesor'+CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+'MB'FROMsysfilesWHEREname=@LogicalFileNameCREATETABLEDummyTrans(DummyColumnchar(8000)notnull)DECLARE@CounterINT,@StartTimeDATETIME,@TruncLogVARCHAR(255)SELECT@StartTime=GETDATE(),@TruncLog='BACKUPLOG'+db_name()+'WITHTRUNCATE_ONLY'DBCCSHRINKFILE(@LogicalFileName,@NewSize)EXEC(@TruncLog)--Wrapthelogifnecessary.WHILE@MaxMinutesDATEDIFF(mi,@StartTime,GETDATE())--timehasnotexpiredAND@OriginalSize=(SELECTsizeFROMsysfilesWHEREname=14@LogicalFileName)AND(@OriginalSize*8/1024)