社区应用 最新帖子 精华区 社区服务 会员列表 统计排行 社区论坛任务 迷你宠物
  • 5370阅读
  • 0回复

[转贴]精妙SQL语句收集

级别: 店掌柜
发帖
5692
铜板
103378
人品值
1520
贡献值
26
交易币
0
好评度
5373
信誉值
0
金币
0
所在楼道

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Kd}cf0  
vU Bk oC2Q  
|__\Vn  
一、基础 VgG*y#Qf$  
1、说明:创建数据库 #mY*H^jI]~  
CREATE DATABASE database-name UP=0>jjbn:  
2、说明:删除数据库 @2Xw17[f35  
drop database dbname Wj2]1A  
3、说明:备份sql server Z\8TpwD2  
--- 创建 备份数据的 device -E~pCN(E  
USE master ~6!{\un   
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' !` S ?  
--- 开始 备份 |,CWk|G  
BACKUP DATABASE pubs TO testBack )f]E<*k'E  
4、说明:创建新表 c"R`7P  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) eaP,MkK&  
根据已有的表创建新表: N}x \Ll  
A:create table tab_new like tab_old (使用旧表创建新表) }8cL+JJU  
B:create table tab_new as select col1,col2... from tab_old definition only m@o/W  
5、说明:删除新表 TNBFb_F  
drop table tabname j3|Ek  
6、说明:增加一个列 "o&_tB;O  
Alter table tabname add column col type +@D [%l|  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 t(/b'Peq  
7、说明:添加主键: Alter table tabname add primary key(col) W 0^.Dx  
说明:删除主键: Alter table tabname drop primary key(col) A `\2]t$z  
8、说明:创建索引:create [unique] index idxname on tabname(col....) nokk! v/  
删除索引:drop index idxname v>zeK  
注:索引是不可更改的,想更改必须删除重新建。 I$sJ8\|gw'  
9、说明:创建视图:create view viewname as select statement !7ct=L  
删除视图:drop view viewname +r[u4?  
10、说明:几个简单的基本的sql语句 bTB/M=M  
选择:select * from table1 where 范围 xC;b<~zN  
插入:insert into table1(field1,field2) values(value1,value2) HN,E+ dQ  
删除:delete from table1 where 范围 -1t"(v  
更新:update table1 set field1=value1 where 范围 xZAc~~9tD  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! L?!*HS7 m  
排序:select * from table1 order by field1,field2 [desc] Fy^*@&  
总数:select count as totalcount from table1 x,YC/J  
求和:select sum(field1) as sumvalue from table1 /CX_@%m}e=  
平均:select avg(field1) as avgvalue from table1 HRO :U%  
最大:select max(field1) as maxvalue from table1 Aa t _5p  
最小:select min(field1) as minvalue from table1 =*0<.Lo':  
KK" uSC  
nxH=Ut7{  
{8D`A;KD  
11、说明:几个高级查询运算词 -U;2 b_  
uP bvN[~t  
Ut4cli&cC  
A: UNION 运算符 VS0 &[bl  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 l6ayV  
B: EXCEPT 运算符 NT?Gl(  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 7 J$  
C: INTERSECT 运算符 %rVC3}  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 V&82U w  
注:使用运算词的几个查询结果行必须是一致的。 q9rY++Tv  
12、说明:使用外连接 3]DUUXg$  
A、left outer join: Wr"-~PP  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 fsqK(io28  
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 ''P.~~ezr5  
B:right outer join: & Ji!*~sE  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 9`kxyh</  
C:full outer join: ~i 'Ib_%h  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 ;w ";s$  
[#S[= %  
fT1/@  
二、提升 <A?- *  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) ]5W|^%  
法一:select * into b from a where 1<>1 .s-X %%e\  
法二:select top 0 * into b from a 2lNZwV7  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) rn3GBWC_C  
insert into b(a, b, c) select d,e,f from b; rvjPm5[t  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 9^ITP!~e*  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 b^b@W^\hn  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. 0Q>f,}W%>  
4、说明:子查询(表名1:a 表名2:b) P)x&9OHV  
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) qP? V{N  
5、说明:显示文章、提交人和最后回复时间 @{16j# 'R  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b b*w izd  
6、说明:外连接查询(表名1:a 表名2:b) X5i?B b.  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c Gkci_A*  
7、说明:在线视图查询(表名1:a ) sd|5oz )  
select * from (SELECT a,b,c FROM a) T where t.a > 1; kj_ o I5<'  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括  =`fJ  
select * from table1 where time between time1 and time2 -_&"Q4FR;+  
select a,b,c, from table1 where a not between 数值1 and 数值2  5,  
9、说明:in 的使用方法 ?K]Cs&E4  
select * from table1 where a [not] in ('值1','值2','值4','值6') 'J(rIH3U  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 $<R\|_6J  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) M6J~%qF^  
11、说明:四表联查问题: $g? ]9}p  
select * 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 ..... :D(4HXHK%  
12、说明:日程安排提前五分钟提醒 le1  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 h:{rjXK  
13、说明:一条sql 语句搞定数据库分页 <u>l#weG,  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 i> Wsc?  
14、说明:前10条记录 ?K9&ye_rgw  
select top 10 * form table1 where 范围 B:5\+_a!  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) ;{mKt%#  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) ! h7?Ap  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 :t?Z  
(select a from tableA ) except (select a from tableB) except (select a from tableC)  Er( I6  
17、说明:随机取出10条数据 :l~^un|<2Y  
select top 10 * from tablename order by newid() -Lh\]  
18、说明:随机选择记录 Ni]V)wGE;  
select newid() =.19 7)e  
19、说明:删除重复记录 H +Dv-*i  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 3ZRi@=kWz  
20、说明:列出数据库里所有的表名 /'KCW_Q  
select name from sysobjects where type='U' nT.i|(xd.  
21、说明:列出表里的所有的 QN`K|,}H^  
select name from syscolumns where id=object_id('TableName') 1.p2{  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 g \]2?vY.  
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 type ;MH((M/AN  
显示结果: 5[<" _  
type vender pcs #O3Y#2lI  
电脑 A 1 9eOP:/'}w  
电脑 A 1 .W4P/P w'  
光盘 B 2 -|s w\Q  
光盘 A 2 N.r8dC  
手机 B 3 f.Wip)g  
手机 C 3 (bpO>4(S  
23、说明:初始化表table1 CG@3z@*?.  
TRUNCATE TABLE table1 BPgY_f  
24、说明:选择从10到15的记录 45g:q  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc !h\.w9o[  
b EB3 #uc  
kw,eTB<;R  
ZBw]H'sT  
三、技巧 kg0X2^#b  
1、1=1,1=2的使用,在SQL语句组合时用的较多 Sg#$ B#g  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, x"/DCcZ  
如: k:1p:&*m  
if @strWhere !='' aMa ICM  
begin @E Srj[  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere aU&p7y4C@  
end 3$<u3Zi6  
else  UZJ^ e$N  
begin 7X+SK&PX  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' SZVNu*G!H  
end yjcZTvjJ  
我们可以直接写成 u@ MUcW  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere b$7p`Ay  
2、收缩数据库 eBUexxBY  
--重建索引 )\nKr;4MH  
DBCC REINDEX ['~E _z  
DBCC INDEXDEFRAG HW|5'opF  
--收缩数据和日志 z;T_%?u  
DBCC SHRINKDB XPJsnu  
DBCC SHRINKFILE V { #8+  
3、压缩数据库 G;RFY!o  
dbcc shrinkdatabase(dbname) HpbSf1VvAf  
4、转移数据库给新用户以已存在用户权限 2bu,_<K.  
exec sp_change_users_login 'update_one','newname','oldname' l', +l{\Z  
go <V[Qs3uo(  
5、检查备份集 1Ce7\A  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' Z5x&P_.x[  
6、修复数据库 RCZ"BxleU  
ALTER DATABASE [dvbbs] SET SINGLE_USER r{+P2MPW  
GO QMO.Bnek  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK :V,agAMn  
GO (!cG*FrN  
ALTER DATABASE [dvbbs] SET MULTI_USER R1sWhB99  
GO > nHaMj  
7、日志清除 sd5%Szx  
SET NOCOUNT ON ??Lda='  
DECLARE @LogicalFileName sysname, E;`@S  
        @MaxMinutes INT, exW|c~|m{A  
        @NewSize INT V{A_\  
USE    tablename            -- 要操作的数据库名 3?%?J^/a  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 ]1Wh3C  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. DxM$4  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) KM-d8^\:  
-- Setup / initialize N.~zQVO#R  
DECLARE @OriginalSize int -hd@<+;E  
SELECT @OriginalSize = size #BLx +mLq  
  FROM sysfiles pL [JGn  
  WHERE name = @LogicalFileName \&!qw[;O  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + k-V3l  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + &\Ze<u  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' ]Rk4"i  
  FROM sysfiles ` x|=vu-  
  WHERE name = @LogicalFileName .}n-N #  
CREATE TABLE DummyTrans 19h@fA[:  
  (DummyColumn char (8000) not null) #gq!L  
DECLARE @Counter  INT, ?hC,49  
        @StartTime DATETIME, {>v5~G  
        @TruncLog  VARCHAR(255) nrEG4X9  
SELECT  @StartTime = GETDATE(), e=ITAH3b  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' VTUY#+3  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) 0<3->uK  
EXEC (@TruncLog) }xa~U,#5  
-- Wrap the log if necessary. L'?7~Cdls  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired n0a|GZyO]  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  !"d"3coQ?  
      AND (@OriginalSize * 8 /1024) > @NewSize  'w$jVX/  
  BEGIN -- Outer loop. FF5|qCV/z  
    SELECT @Counter = 0 IGnP#@`5]  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 5eLm  
      BEGIN -- update SSQB1c  
        INSERT DummyTrans VALUES ('Fill Log')  V|3^H^\5P  
        DELETE DummyTrans ,=IGqw  
        SELECT @Counter = @Counter + 1 7g7[a/Bts  
      END  >%\&tS'  
    EXEC (@TruncLog)  M*gbA5  
  END  ln1!%B;  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + v\Y8+dD  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + zJ*(G_H  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 9$q35e  
  FROM sysfiles j LM}hwJ8  
  WHERE name = @LogicalFileName ` n#Db  
DROP TABLE DummyTrans L*#W?WMM v  
SET NOCOUNT OFF *)Us   
8、说明:更改某个表 8a8CY,n{  
exec sp_changeobjectowner 'tablename','dbo' 31GqWN`>$  
9、存储更改全部表 8wEUly  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch 2MU$OI0|  
@OldOwner as NVARCHAR(128), \1ncr4  
@NewOwner as NVARCHAR(128) BjyV&1tRV!  
AS $P h#pM(  
DECLARE @Name  as NVARCHAR(128) 6 h%,%  
DECLARE @Owner  as NVARCHAR(128) Tlm::S   
DECLARE @OwnerName  as NVARCHAR(128) Fks #Y1rI  
DECLARE curObject CURSOR FOR JP,yRb\  
select 'Name'  = name, .du2;` [$r  
  'Owner'  = user_name(uid) n&%0G2m:  
from sysobjects 9;7|MPbR  
where user_name(uid)=@OldOwner (V x2*Aw]  
order by name OLZs}N+;]  
OPEN  curObject h(K}N5`  
FETCH NEXT FROM curObject INTO @Name, @Owner ucYweXsO3  
WHILE(@@FETCH_STATUS=0) hiKyU! )Hv  
BEGIN    z -c1,GOD  
if @Owner=@OldOwner C=Tq/L w  
begin {ePtZyo0  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) vR7S !  
  exec sp_changeobjectowner @OwnerName, @NewOwner ^M)+2@6  
end 7G+E+A5o&  
-- select @name,@NewOwner,@OldOwner K>vi9,4/ks  
FETCH NEXT FROM curObject INTO @Name, @Owner $%6.lQ  
END yvWM]A  
close curObject 9RPZj>ezjA  
deallocate curObject Q~f mVWq  
GO Ge`PVwn  
10、SQL SERVER中直接循环写入数据 c6T[2Ig  
declare @i int =D&XE*qkZ  
set @i=1 R>t?6HOcp  
while @i<30 Itz[%Dbiq9  
begin YuUJgt .1  
  insert into test (userid) values(@i) wEF"'T  
  set @i=@i+1 z"c,TlVN3  
end 4YSVy2x  
小记存储过程中经常用到的本周,本月,本年函数 5gSe=|we*p  
Dateadd(wk,datediff(wk,0,getdate()),-1) YU`}T<;bg  
Dateadd(wk,datediff(wk,0,getdate()),6) ,4j^ lgJ  
Dateadd(mm,datediff(mm,0,getdate()),0) D (WdI  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 9~J#> C0}  
Dateadd(yy,datediff(yy,0,getdate()),0) +bGO"*  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) &. MUSqo9  
上面的SQL代码只是一个时间段 \1O wZ@  
Dateadd(wk,datediff(wk,0,getdate()),-1) t"Bp # U1  
Dateadd(wk,datediff(wk,0,getdate()),6) `&:>?Y/X2  
就是表示本周时间段. SyI\ulmL  
下面的SQL的条件部分,就是查询时间段在本周范围内的: QM24cm T  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) }` YtXD-o  
而在存储过程中 R; ui 4wg6  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 7~~suQ{F4  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) }X6w"  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

简单生活
执著追求
别笑我浅溥,天真的以为用一腔真诚就能感动这个冷漠的世界。
也别说我幼稚,竟想用不长的人生去诠释繁杂的红尘。
然而除了真诚,我还能给你什么,的确我真的一无所有!

描述
快速回复

您目前还是游客,请 登录注册
温馨提示:欢迎交流讨论,请勿纯表情、纯引用!
认证码:
验证问题:
10+5=?,请输入中文答案:十五