精妙SQL语句收集（转）

最近需要用到SQL
s!u/M@[Ri0C0所以从网上找到篇很精妙的介绍。51Testing软件测试网"r{.buE
原文转至CSDN http://blog.csdn.net/chen8148/archive/2007/08/14/1741991.aspx
F,DZ&`y+}Fk051Testing软件测试网|8o0N]WLdZy:b
精妙 SQL 语句收集51Testing软件测试网1c.mQ[,e!~ k

9W@
B&\.a^ n!?0SQL语句先前写的时候，很容易把一些特殊的用法忘记，我特此整理了一下SQL语句操作。51Testing软件测试网;paN` D6R6\
一、基础
/t2dUKV7hf.w01、说明：创建数据库51Testing软件测试网d5_}z%{H\1MB
CREATE DATABASE database-name51Testing软件测试网O#y'\ Gn8y mWye
2、说明：删除数据库51Testing软件测试网t G][%AR
drop database dbname51Testing软件测试网;iaK$DAU4WM,_X
3、说明：备份sql server51Testing软件测试网:N~:U5h`
--- 创建 备份数据的 device
rXO)N5zV*ssU0USE master
"QY*` KQ0EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
c
|wm&i.x0--- 开始 备份51Testing软件测试网-d+D;{A G
z
BACKUP DATABASE pubs TO testBack
:a*XK(UT04、说明：创建新表
]2fP"c%my*k'G9zyR0create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
~8gaPmi0根据已有的表创建新表：51Testing软件测试网wa|;wOr
A：create table tab_new like tab_old (使用旧表创建新表)51Testing软件测试网d1hy$RJX2G*Z
B：create table tab_new as select col1,col2... from tab_old definition only51Testing软件测试网-hAsd)^.hXkM
5、说明：删除新表51Testing软件测试网
hYN_&]:HA!f3j;ip
drop table tabname
%n#\ p$n#\ P@U06、说明：增加一个列51Testing软件测试网0Z2K,])e+`
L*SD
Alter table tabname add column col type
B.guO)GI]&P0注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。51Testing软件测试网1jBhoY1Pt'xaK
7、说明：添加主键： Alter table tabname add primary key(col)51Testing软件测试网-}og}"bD)GV0V$F
说明：删除主键： Alter table tabname drop primary key(col)51Testing软件测试网$N9f7J%hj FG
8、说明：创建索引：create [unique] index idxname on tabname(col....)51Testing软件测试网9kyx#Xn+|
删除索引：drop index idxname51Testing软件测试网"@;mBEC
注：索引是不可更改的，想更改必须删除重新建。
9q6Op)Ix"oCDI09、说明：创建视图：create view viewname as select statement
/z"O*TUV0A0删除视图：drop view viewname
#h4E/{qTAf xpb010、说明：几个简单的基本的sql语句
7q5r(n;}"mgo0选择：select * from table1 where 范围51Testing软件测试网0}^#L`)X&IaQ6b
插入：insert into table1(field1,field2) values(value1,value2)51Testing软件测试网!q RT%n(W
删除：delete from table1 where 范围51Testing软件测试网]f
P'^0B^W*?2X
更新：update table1 set field1=value1 where 范围
SX&D6V;QCC0查找：select * from table1 where field1 like '%value1%' ---like的语法很精妙，查资料!
/[!u"e&qs-Ob0排序：select * from table1 order by field1,field2 [desc]51Testing软件测试网!A'i_i__~
总数：select count as totalcount from table151Testing软件测试网!Q2VAWD{
求和：select sum(field1) as sumvalue from table1
@jYC3t&Z N8vF0平均：select avg(field1) as avgvalue from table1
&Y E Uf]*H0最大：select max(field1) as maxvalue from table1
,SH+LCeM#wj
}3w~0最小：select min(field1) as minvalue from table151Testing软件测试网bhN9A ^e H,^
11、说明：几个高级查询运算词51Testing软件测试网q m2HF/Rp
A： UNION 运算符
+\|e N_0UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
DT_s)q^&SDF0B： EXCEPT 运算符51Testing软件测试网L} j)o+zkE3t
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
0N.c|1q U0C： INTERSECT 运算符
r$J[9\ w0INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。51Testing软件测试网"`0v6`h)|zD,iU)u
注：使用运算词的几个查询结果行必须是一致的。
b*A9R,[%^ S012、说明：使用外连接
RSHP:Ra0\-J0A、left outer join：51Testing软件测试网;X)G6Ns;A.q|S_
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。51Testing软件测试网Z%c3B+E/`|f2l
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
v4yKP;LD_0B：right outer join:51Testing软件测试网7S [7J:{$t.\l
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。51Testing软件测试网*tM7qsv2L,\
C：full outer join：51Testing软件测试网~o)Et5U'L-c$}1MH
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
$B1g,B2J+IEh0二、提升51Testing软件测试网 a'y9m/gu7R3hk*D(d
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)51Testing软件测试网:oHXiQ4k
法一：select * into b from a where 1<>1
T0ps3H^a:c|0法二：select top 0 * into b from a51Testing软件测试网2Rh Ah'V2T#_?
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)51Testing软件测试网b'n;I#QuN,_z
insert into b(a, b, c) select d,e,f from b;
:?/GRfU03、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)51Testing软件测试网/G K5tU#[6o2WZ%J~
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件
EM@.{1X0例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..51Testing软件测试网%V!F$?N6i\!W*@5q&F
4、说明：子查询(表名1：a 表名2：b)51Testing软件测试网F`.S*f$l
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)51Testing软件测试网3@c&^ ?!t)^ G
5、说明：显示文章、提交人和最后回复时间
7oGTq'`0select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
"X|5LKyW3fV'FG v06、说明：外连接查询(表名1：a 表名2：b)51Testing软件测试网!K6ynn2Gk r+e
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c51Testing软件测试网/v0eSjK
7、说明：在线视图查询(表名1：a )
2@lr4\t+H0select * from (SELECT a,b,c FROM a) T where t.a > 1;
&vSvgbd08、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
Yk*h#LV|R0select * from table1 where time between time1 and time251Testing软件测试网 K*?k8`|g6O-?%A O
select a,b,c, from table1 where a not between 数值1 and 数值2

MPe~uy5x09、说明：in 的使用方法
8o/l's
P"q4P2U;[
z0select * from table1 where a [not] in ('值1','值2','值4','值6')
`+_.XAD S({Pb010、说明：两张关联表，删除主表中已经在副表中没有的信息
[\:W`J%?j2x5g2w0delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )51Testing软件测试网o3Q1m/AvJTQ)sk
11、说明：四表联查问题：
3\$pj[Qq,dZ j0select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....51Testing软件测试网X@-^*IBz
12、说明：日程安排提前五分钟提醒
?igZ)hL4u&q0SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
3i#u&T+sZvt;o|e
a013、说明：一条sql 语句搞定数据库分页51Testing软件测试网2D:\LCB
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
3?,eXa*E#E014、说明：前10条记录
[3YO xT[ e&TU0select top 10 * form table1 where 范围
s"\8]b5RC Q015、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)51Testing软件测试网tE^4XH
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
H#CDhW4c){016、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表51Testing软件测试网x6I!X{,ZBa
(select a from tableA ) except (select a from tableB) except (select a from tableC)51Testing软件测试网(GS+QGL
17、说明：随机取出10条数据51Testing软件测试网"lGZ8P!R[v'D4p
select top 10 * from tablename order by newid()
J*`.vvF018、说明：随机选择记录
2u"zH OD bPF0select newid()
B;xs:D A[)Dw%Q0m019、说明：删除重复记录51Testing软件测试网D:J&Gg&^9\"]9z
[
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
,g i+WCW6tO&@020、说明：列出数据库里所有的表名51Testing软件测试网 vf0N:l9o$?"N9_3K
select name from sysobjects where type='U'51Testing软件测试网&O b,mM%m8F^x
21、说明：列出表里的所有的
7k
|Q;Gl:J0select name from syscolumns where id=object_id('TableName')
1Z JT bo o&j022、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。51Testing软件测试网K|B7G(w`
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type51Testing软件测试网2SQ4ZJfw$i5~`6Nv9o
显示结果：51Testing软件测试网
d#i9XHB8?m
type vender pcs51Testing软件测试网nv/M*P/JS*n
电脑 A 1
$RN,Z @f)ey4N0电脑 A 151Testing软件测试网~K@y:iO?'T/YLI
光盘 B 251Testing软件测试网7ag&wXb_p#j8n
光盘 A 251Testing软件测试网 \]mPq
手机 B 3
T0f3vO|{0手机 C 351Testing软件测试网 EFyPrf
23、说明：初始化表table151Testing软件测试网^3g%N#U7C)O
TRUNCATE TABLE table151Testing软件测试网
Zy7Kju
P
24、说明：选择从10到15的记录
(R+wM%dk-W0select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc51Testing软件测试网%X8X/r3Yj r!R
三、技巧51Testing软件测试网5@\M5hh]m1J*AI
1、1=1，1=2的使用，在SQL语句组合时用的较多
(W'h:]YH ~1AM!Wo0"where 1=1" 是表示选择全部   "where 1=2"全部不选，
7[cU:^*c0如：
3ow_W$q*]LQ0if @strWhere !=''
R!Pr H;E0Qw0begin
g~ KM5[9x }0set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere51Testing软件测试网af$KU8o:}8LRo
end
*_ I ^;C\^6\0else
rCO9C0F `u[0begin51Testing软件测试网7w!o,k;M2S@[pr5H x
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'51Testing软件测试网-lK2n^;L3|N
end51Testing软件测试网 L)Zw w&GJ
我们可以直接写成51Testing软件测试网(V7J_:A C s
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere
/a4yBv'N0c |T02、收缩数据库
^j8C-{V[s-]0--重建索引
0}["F!y D!o(E wJv+G0DBCC REINDEX51Testing软件测试网TbJ|f7X;?3~
DBCC INDEXDEFRAG51Testing软件测试网 J1dyRJ9Gb#TK1lp
--收缩数据和日志51Testing软件测试网$r"j WV+n7a+]:p
DBCC SHRINKDB51Testing软件测试网.viuOs+u,[6g
DBCC SHRINKFILE51Testing软件测试网gK,Ewu/Rz r
? M
3、压缩数据库
;V%A8P[
ks0dbcc shrinkdatabase(dbname)
{$iwu%Ig6i ^ z04、转移数据库给新用户以已存在用户权限51Testing软件测试网Os8{pOg
exec sp_change_users_login 'update_one','newname','oldname'51Testing软件测试网)Sj|d&NF
go
#y5O1wle&Y05、检查备份集51Testing软件测试网Tb3_.@&d Z
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'51Testing软件测试网o7A+y\0@u:D;x
6、修复数据库51Testing软件测试网DKo"C] \
ALTER DATABASE [dvbbs] SET SINGLE_USER51Testing软件测试网[ VGIWTM/l1L?.D
GO51Testing软件测试网4h.R]+vC2UZ.||
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK51Testing软件测试网!o!Zpe'p
GO51Testing软件测试网TNr6R
_.e7]-g
ALTER DATABASE [dvbbs] SET MULTI_USER51Testing软件测试网bT#~g-C1cu{
GO51Testing软件测试网w5J?1q!LQ
7、日志清除
Z O"z$K7o6R5u0SET NOCOUNT ON
P2qnHE)I5hRd0DECLARE @LogicalFileName sysname,51Testing软件测试网e LLqfnCWJ
        @MaxMinutes INT,51Testing软件测试网 E
F[+Y8O@'ak
        @NewSize INT

,A0Lhw:i a,q${051Testing软件测试网5p'OW0o;_J p,L%T`

USE     tablename             -- 要操作的数据库名51Testing软件测试网gz Y0lJ&D$bo]7m {
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名
-yJ0v]"uc/~u8U0@MaxMinutes = 10,               -- Limit on time allowed to wrap log.
)reo?8wA
SZ4J~B4p0        @NewSize = 1                  -- 你想设定的日志文件的大小(M)
"VrT M9y1z0-- Setup / initialize
F.d K*I)z/V3r$@0DECLARE @OriginalSize int51Testing软件测试网5MH3E9F`5N
SELECT @OriginalSize = size
#u[S"A%dJ0p@3D0  FROM sysfiles
/Z1[PcML$xa0  WHERE name = @LogicalFileName51Testing软件测试网0L4Kw|+_D#U}
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
\3L)c7K `q0        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +51Testing软件测试网`0E#j)TEO
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'51Testing软件测试网w7@Uf9E^1N#Qk
  FROM sysfiles51Testing软件测试网:~y/mL9p
  WHERE name = @LogicalFileName
3m/u4~-H7cOn ]{0CREATE TABLE DummyTrans51Testing软件测试网1V c-np'K1I ` ~j
  (DummyColumn char (8000) not null)

s+|dW s%W$w0

#r~
m)n&H ^z#b5D0DECLARE @Counter   INT,
ZI)D?5h l4^N\a0        @StartTime DATETIME,51Testing软件测试网t:}4j1@1y
        @TruncLog  VARCHAR(255)51Testing软件测试网9ghjj;Z+v)cm
SELECT  @StartTime = GETDATE(),51Testing软件测试网P*U1c6r
q
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'51Testing软件测试网MWKK_FF3Y"S4A#@
DBCC SHRINKFILE (@LogicalFileName, @NewSize)

iyU~/jxX9\Db-R9}0EXEC (@TruncLog)51Testing软件测试网ixxl;Q M
-- Wrap the log if necessary.51Testing软件测试网-k5siD8WV9[)Q
WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired51Testing软件测试网pu@ gkt,^
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 51Testing软件测试网(TW&j{iw/a {$?
      AND (@OriginalSize * 8 /1024) > @NewSize 51Testing软件测试网/i$k(cr?z'By
  BEGIN -- Outer loop.51Testing软件测试网6s8Z:Xf3u?@
    SELECT @Counter = 0
uh6B9MW0    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))51Testing软件测试网h UY/B&S3^7K a*\F
      BEGIN -- update
L Q6ap!pH0        INSERT DummyTrans VALUES ('Fill Log') 
-\ A6\.|Qfu0        DELETE DummyTrans51Testing软件测试网{G)rx|W[ IV
        SELECT @Counter = @Counter + 151Testing软件测试网 F*Y6] c+C8c&d?*A
      END  
[~I\Yl3ZM N0    EXEC (@TruncLog) 51Testing软件测试网4N)Q(ALF;n'T
  END  
fn7NOZ7?(X\y`0SELECT 'Final Size of ' + db_name() + ' LOG is ' +51Testing软件测试网t8pPa ?X
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
4dz-{}$tU;u%Z{ w0        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'51Testing软件测试网zG!n6?;P
Y.J8@I
  FROM sysfiles51Testing软件测试网T2i3a$PB"_^
  WHERE name = @LogicalFileName51Testing软件测试网#z`A,AB
DROP TABLE DummyTrans51Testing软件测试网"dn+BC)}_*QOJ
SET NOCOUNT OFF51Testing软件测试网 Q(e3X|5U
8、说明：更改某个表
X(D*G^7dz"lb0exec sp_changeobjectowner 'tablename','dbo'51Testing软件测试网2}$^~!A}z(k,q
9、存储更改全部表51Testing软件测试网t,A_x1Mqj
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch51Testing软件测试网+V[^&ybI-V.q
Q,x
 @OldOwner as NVARCHAR(128),51Testing软件测试网C#[1a5a1W H2SJ.[
 @NewOwner as NVARCHAR(128)51Testing软件测试网7G6J@qX
AS
d#I*Y)p:T4uO]0DECLARE @Name   as NVARCHAR(128)
b._jN;N(W kT0DECLARE @Owner  as NVARCHAR(128)
Ev^:Ei0q K~'Y2bi@0DECLARE @OwnerName  as NVARCHAR(128)
HSB:?,j-?;n0DECLARE curObject CURSOR FOR
S T N1eliQ9D S0 select 'Name'   = name,51Testing软件测试网
`%L:Q ^
XF'~ I#SK
  'Owner'   = user_name(uid)
W6E,F-fe(ub[0 from sysobjects
}bA8Bt@0 where user_name(uid)=@OldOwner
.{|Rh&q%\!T"U0lu0 order by name51Testing软件测试网o,c&]Y&~ch
OPEN  curObject
d1j t3od@V }0FETCH NEXT FROM curObject INTO @Name, @Owner
X];vZT BN)[U-f~0WHILE(@@FETCH_STATUS=0)51Testing软件测试网&[}!o ?]h].D-Q
BEGIN    
8A#^!`wx{6o0 if @Owner=@OldOwner51Testing软件测试网7l u(r-`G.Z
 begin51Testing软件测试网m1uH:dpp9~4o
  set @OwnerName = @OldOwner + '.' + rtrim(@Name)
'O9u X7Hm N[1Q0  exec sp_changeobjectowner @OwnerName, @NewOwner51Testing软件测试网FbG2w;s~5MB
 end51Testing软件测试网 b.e3w+p2m l)W_
-- select @name,@NewOwner,@OldOwner51Testing软件测试网r+v?s2@ s/zh w
 FETCH NEXT FROM curObject INTO @Name, @Owner51Testing软件测试网
w-UKIC;j
END
;s9{goF7j7cs-o)_Y0close curObject
z(_wv bR?0deallocate curObject51Testing软件测试网
n2r ` K E6l:Q
GO51Testing软件测试网!}VK K^N z,O

eaR!Z$r B2F X0Z010、SQL SERVER中直接循环写入数据51Testing软件测试网}5Lh*Tw2B5p
declare @i int51Testing软件测试网lxQ^#@~Drs
set @i=151Testing软件测试网 F0[f(nq2K1EL J
while @i<3051Testing软件测试网^_m#r:Y3Ibw
begin51Testing软件测试网Xi#eh5YL_Jt d
   insert into test (userid) values(@i)51Testing软件测试网JE?v|]'^
   set @i=@i+151Testing软件测试网 cI
YTE6{3~O0C+IA
end51Testing软件测试网$w&Vz$T;|3_M
小记存储过程中经常用到的本周，本月，本年函数51Testing软件测试网%I
~:T'I#X
Dateadd(wk,datediff(wk,0,getdate()),-1)
2PH(u4qC~K5Q
d0Dateadd(wk,datediff(wk,0,getdate()),6)51Testing软件测试网md\'sNmt9\?%M)Q
Dateadd(mm,datediff(mm,0,getdate()),0)
J(j:T D|-l mYz|0Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))51Testing软件测试网Bm6os7j
Dateadd(yy,datediff(yy,0,getdate()),0)51Testing软件测试网 |4G}(qO*L.M![t s
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
)A!K"t6P
i-V0上面的SQL代码只是一个时间段
p%ZA+Kn
i*f.c0Dateadd(wk,datediff(wk,0,getdate()),-1)51Testing软件测试网qE2xe4h m$V@IAg0o
Dateadd(wk,datediff(wk,0,getdate()),6)
p!xgh z5?T [0就是表示本周时间段.51Testing软件测试网@F,m
^(fOt;H
下面的SQL的条件部分,就是查询时间段在本周范围内的:51Testing软件测试网L2y|%A!\x7Q
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6)51Testing软件测试网}-x+K}5`!V[ Z
而在存储过程中51Testing软件测试网'G:_5]G2f:J ~5C
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1)
%[yis Fn0Xsc%e.]0select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6)

-a.WJ E*d(^n0
-B#n |*vPV$i0