数据库之----SQL语句集锦

数据库基本----SQL语句大全 学会数据库是很实用D~~记录一些常用的sql语句...有入门有提高有见都没见过的...好全...收藏下... 其实一般用的就是查询,插入,删除等语句而已....但学学存储过程是好事...以后数据方面的东西就不用在程序里搞喽..而且程序与数据库只要一个来回通讯就可以搞定所有数据的操作.... 一、基础 1、说明：创建数据库 CreateDATABASEdatabase-name 2、说明：删除数据库 dropdatabasedbname 3、说明：备份sqlserver ---创建备份数据的device USEmaster EXECsp_addumpdevice‘disk‘,‘testBack‘,‘c:\mssql7backup\MyNwind_1.dat‘ ---开始备份 BACKUPDATABASEpubsTOtestBack 4、说明：创建新表 createtabletabname(col1type1[notnull][primarykey],col2type2[notnull],..) 根据已有的表创建新表： A：createtabletab_newliketab_old(使用旧表创建新表) B：createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly 5、说明：删除新表 droptabletabname 6、说明：增加一个列 Altertabletabnameaddcolumncoltype 注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 7、说明：添加主键：Altertabletabnameaddprimarykey(col) 说明：删除主键：Altertabletabnamedropprimarykey(col) 8、说明：创建索引：create[unique]indexidxnameontabname(col….) 删除索引：dropindexidxname 注：索引是不可更改的，想更改必须删除重新建。 9、说明：创建视图：createviewviewnameasselectstatement 删除视图：dropviewviewname 10、说明：几个简单的基本的sql语句 选择：select*fromtable1where范围 插入：insertintotable1(field1,field2)values(value1,value2) 删除：deletefromtable1where范围 更新：updatetable1setfield1=value1where范围 查找：select*fromtable1wherefield1like’%value1%’---like的语法很精妙，查资料! 排序：select*fromtable1orderbyfield1,field2[desc] 总数：selectcountastotalcountfromtable1 求和：selectsum(field1)assumvaluefromtable1 平均：selectavg(field1)asavgvaluefromtable1 最大：selectmax(field1)asmaxvaluefromtable1 最小：selectmin(field1)asminvaluefromtable1 11、说明：几个高级查询运算词 A：UNION运算符 UNION运算符通过组合其他两个结果表（例如TABLE1和TABLE2）并消去表中任何重复行而派生出一个结果表。当ALL随UNION一起使用时（即UNIONALL），不消除重复行。两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。 B：EXCEPT运算符 EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。当ALL随EXCEPT一起使用时(EXCEPTALL)，不消除重复行。 C：INTERSECT运算符 INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。当ALL随INTERSECT一起使用时(INTERSECTALL)，不消除重复行。 注：使用运算词的几个查询结果行必须是一致的。 12、说明：使用外连接 A、leftouterjoin： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c B：rightouterjoin: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 C：fullouterjoin： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。 二、提升 1、说明：复制表(只复制结构,源表名：a新表名：b)(Access可用) 法一：select*intobfromawhere1<>1 法二：selecttop0*intobfroma 2、说明：拷贝表(拷贝数据,源表名：a目标表名：b)(Access可用) insertintob(a,b,c)selectd,e,ffromb; 3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径)(Access可用) insertintob(a,b,c)selectd,e,ffrombin‘具体数据库’where条件 例子：..frombin‘"&Server.MapPath("."&"\data.mdb"&"‘where.. 4、说明：子查询(表名1：a表名2：b) selecta,b,cfromawhereaIN(selectdfromb或者:selecta,b,cfromawhereaIN(1,2,3) 5、说明：显示文章、提交人和最后回复时间 selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b 6、说明：外连接查询(表名1：a表名2：b) selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c 7、说明：在线视图查询(表名1：a select*from(Selecta,b,cFROMa)Twheret.a>1; 8、说明：between的用法,between限制查询数据范围时包括了边界值,notbetween不包括 select*fromtable1wheretimebetweentime1andtime2 selecta,b,c,fromtable1whereanotbetween数值1and数值2 9、说明：in的使用方法 select*fromtable1wherea[not]in(‘值1’,’值2’,’值4’,’值6’) 10、说明：两张关联表，删除主表中已经在副表中没有的信息 deletefromtable1wherenotexists(select*fromtable2wheretable1.field1=table2.field1 11、说明：四表联查问题： select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere..... 12、说明：日程安排提前五分钟提醒 SQL:select*from日程安排wheredatediff(‘minute‘,f开始时间,getdate())>5 13、说明：一条sql语句搞定数据库分页 selecttop10b.*from(selecttop20主键字段,排序字段from表名orderby排序字段desc)a,表名bwhereb.主键字段=a.主键字段orderbya.排序字段 14、说明：前10条记录 selecttop10*formtable1where范围 15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) selecta,b,cfromtablenametawherea=(selectmax(a)fromtablenametbwheretb.b=ta.b) 16、说明：包括所有在TableA中但不在TableB和TableC中的行并消除所有重复行而派生出一个结果表 (selectafromtableAexcept(selectafromtableB)except(selectafromtableC) 17、说明：随机取出10条数据 selecttop10*fromtablenameorderbynewid() 18、说明：随机选择记录 selectnewid() 19、说明：删除重复记录 Deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupbycol1,col2,...) 20、说明：列出数据库里所有的表名 selectnamefromsysobjectswheretype=‘U‘ 21、说明：列出表里的所有的 selectnamefromsyscolumnswhereid=object_id(‘TableName‘) 22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select中的case。 selecttype,sum(casevenderwhen‘A‘thenpcselse0end),sum(casevenderwhen‘C‘thenpcselse0end),sum(casevenderwhen‘B‘thenpcselse0end)FROMtablenamegroupbytype 显示结果： typevenderpcs 电脑A1 电脑A1 光盘B2 光盘A2 手机B3 手机C3 23、说明：初始化表table1 TRUNCATETABLEtable1 24、说明：选择从10到15的记录 selecttop5*from(selecttop15*fromtableorderbyidasc)table_别名orderbyiddesc 三、技巧 1、1=1，1=2的使用，在SQL语句组合时用的较多 “where1=1”是表示选择全部“where1=2”全部不选， 如： if@strWhere!=‘ begin set@strSQL=‘selectcount(*)asTotalfrom[‘+@tblName+‘]where‘+@strWhere end else begin set@strSQL=‘selectcount(*)asTotalfrom[‘+@tblName+‘]‘ end 我们可以直接写成 set@strSQL=‘selectcount(*)asTotalfrom[‘+@tblName+‘]where1=1安定‘+@strWhere 2、收缩数据库 --重建索引 DBCCREINDEX DBCCINDEXDEFRAG --收缩数据和日志 DBCCSHRINKDB DBCCSHRINKFILE 3、压缩数据库 dbccshrinkdatabase(dbname) 4、转移数据库给新用户以已存在用户权限 execsp_change_users_login‘update_one‘,‘newname‘,‘oldname‘ go 5、检查备份集 RESTOREVERIFYONLYfromdisk=‘E:\dvbbs.bak‘ 6、修复数据库 AlterDATABASE[dvbbs]SETSINGLE_USER GO DBCCCHECKDB(‘dvbbs‘,repair_allow_data_loss)WITHTABLOCK GO AlterDATABASE[dvbbs]SETMULTI_USER GO 7、日志清除 SETNOCOUNTON DECLARE@LogicalFileNamesysname, @MaxMinutesINT, @NewSizeINT USEtablename--要操作的数据库名 Select@LogicalFileName=‘tablename_log‘,--日志文件名 @MaxMinutes=10,--Limitontimeallowedtowraplog. @NewSize=1--你想设定的日志文件的大小(M) --Setup/initialize DECLARE@OriginalSizeint Select@OriginalSize=size FROMsysfiles Wherename=@LogicalFileName Select‘OriginalSizeof‘+db_name()+‘LOGis‘+ CONVERT(VARCHAR(30),@OriginalSize)+‘8Kpagesor‘+ CONVERT(VARCHAR(30),(@OriginalSize*8/1024))+‘MB‘ FROMsysfiles Wherename=@LogicalFileName CreateTABLEDummyTrans (DummyColumnchar(8000)notnull) DECLARE@CounterINT, @StartTimeDATETIME, @TruncLogVARCHAR(255) Select@StartTime=GETDATE(), @TruncLog=‘BACKUPLOG‘+db_name()+‘WITHTRUNCATE_ONLY‘ DBCCSHRINKFILE(@LogicalFileName,@NewSize) EXEC(@TruncLog) --Wrapthelogifnecessary. WHILE@MaxMinutes>DATEDIFF(mi,@StartTime,GETDATE())--timehasnotexpired AND@OriginalSize=(SelectsizeFROMsysfilesWherename=@LogicalFileName) AND(@OriginalSize*8/1024)>@NewSize BEGIN--Outerloop. Select@Counter=0 WHILE((@Counter<@OriginalSize/16)AND(@Counter<50000)) BEGIN--update InsertDummyTransVALUES(‘FillLog‘) DeleteDummyTrans Select@Counter=@Counter+1 END EXEC(@TruncLog) END Select‘FinalSizeof‘+db_name()+‘LOGis‘+ CONVERT(VARCHAR(30),size)+‘8Kpagesor‘+ CONVERT(VARCHAR(30),(size*8/1024))+‘MB‘ FROMsysfiles Wherename=@LogicalFileName DropTABLEDummyTrans SETNOCOUNTOFF 8、说明：更改某个表 execsp_changeobjectowner‘tablename‘,‘dbo‘ 9、存储更改全部表 CreatePROCEDUREdbo.User_ChangeObjectOwnerBatch @OldOwnerasNVARCHAR(128), @NewOwnerasNVARCHAR(128) AS DECLARE@NameasNVARCHAR(128) DECLARE@OwnerasNVARCHAR(128) DECLARE@OwnerNameasNVARCHAR(128) DECLAREcurObjectCURSORFOR select‘Name‘=name, ‘Owner‘=user_name(uid) fromsysobjects whereuser_name(uid)=@OldOwner orderbyname OPENcurObject FETCHNEXTFROMcurObjectINTO@Name,@Owner WHILE(@@FETCH_STATUS=0) BEGIN if@Owner=@OldOwner begin set@OwnerName=@OldOwner+‘.‘+rtrim(@Name) execsp_changeobjectowner@OwnerName,@NewOwner end --select@name,@NewOwner,@OldOwner FETCHNEXTFROMcurObjectINTO@Name,@Owner END closecurObject deallocatecurObject GO 10、SQLSERVER中直接循环写入数据 declare@iint set@i=1 while@i<30 begin insertintotest(userid)values(@i) set@i=@i+1 end

本文地址：http://www.45fan.com/a/question/51539.html