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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 ZzPlIl}\  
|LA./%U  
-M}#-qwf  
一、基础 ;u!qu$O  
1、说明:创建数据库 0Qvbc}KP8  
CREATE DATABASE database-name 4*W ??(=j  
2、说明:删除数据库 Uj&2'>MJ$  
drop database dbname B Jp\a7`;  
3、说明:备份sql server ?1JVzZ4H  
--- 创建 备份数据的 device ;Pik},  
USE master =vLeOX  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' \tTZ N  
--- 开始 备份 =8S*t5  
BACKUP DATABASE pubs TO testBack =,&PD(.  
4、说明:创建新表 +h^>?U,  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) | Zx  
根据已有的表创建新表: X=)Ue  
A:create table tab_new like tab_old (使用旧表创建新表) "M5P-l$p}  
B:create table tab_new as select col1,col2... from tab_old definition only MkZm =Sf  
5、说明:删除新表 M7{w7}B0@  
drop table tabname 8X`iMFa.P  
6、说明:增加一个列 :RR<-N5+  
Alter table tabname add column col type p%~#~5t,  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 8#NtZ  
7、说明:添加主键: Alter table tabname add primary key(col) YKq,`7"%  
说明:删除主键: Alter table tabname drop primary key(col) r=6-kC!T9  
8、说明:创建索引:create [unique] index idxname on tabname(col....) 62K7afH  
删除索引:drop index idxname T{v(B["!$  
注:索引是不可更改的,想更改必须删除重新建。 cmF&1o3_  
9、说明:创建视图:create view viewname as select statement O_aZ\28};C  
删除视图:drop view viewname kx8\]'  
10、说明:几个简单的基本的sql语句 }yZ9pTB.?E  
选择:select * from table1 where 范围 xWlj.Tjt}  
插入:insert into table1(field1,field2) values(value1,value2) p Ux ~  
删除:delete from table1 where 范围 FI++A`  
更新:update table1 set field1=value1 where 范围 S05+G}[$  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! BYuF$[3ya&  
排序:select * from table1 order by field1,field2 [desc] 4d3]L` f  
总数:select count as totalcount from table1 nsFOtOdd  
求和:select sum(field1) as sumvalue from table1 0FmYM@Wc  
平均:select avg(field1) as avgvalue from table1 3Z#k9c_b  
最大:select max(field1) as maxvalue from table1 9 lE[oAC  
最小:select min(field1) as minvalue from table1 lR[[]Yn  
hI*gw3V  
@~% R%Vu  
aOHf#!/"sb  
11、说明:几个高级查询运算词 ei)ljvvmHP  
D+?/MrP  
[2I1W1pd  
A: UNION 运算符 Xh"JyDTj3  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 89T xd9X  
B: EXCEPT 运算符 XB*)d 9'8  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 O@r%G0Jge  
C: INTERSECT 运算符 UN#XP$utY  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 ~pA_E!3W  
注:使用运算词的几个查询结果行必须是一致的。 lPyGL-Q  
12、说明:使用外连接 .&dW?HS  
A、left outer join: oLK-~[p  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 f tW-  
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 )8]O|Z-CU  
B:right outer join: S~L$sqt  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 rC.z772y%  
C:full outer join: {]1o($.u  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Yl%1e|WV  
mne4uW  
- y[nMEE  
二、提升  (c;F%m|  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) rZ`ob x\S  
法一:select * into b from a where 1<>1 9r.Os  
法二:select top 0 * into b from a *TQXE:vZ[  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) umZy=KHj  
insert into b(a, b, c) select d,e,f from b; 0o~? ]C  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) KDr?<"2L  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 9TRS#iVL+*  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. -N;$L~`iAt  
4、说明:子查询(表名1:a 表名2:b) l&l&e OE  
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) UFBggT\  
5、说明:显示文章、提交人和最后回复时间 ZHT_o\  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b o?(({HH  
6、说明:外连接查询(表名1:a 表名2:b) /H.w0fu&.S  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 94 58.!3  
7、说明:在线视图查询(表名1:a ) %+gYZv-  
select * from (SELECT a,b,c FROM a) T where t.a > 1; =Hplg>h)  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 AsJN~<0h  
select * from table1 where time between time1 and time2 Hx[YHu KL^  
select a,b,c, from table1 where a not between 数值1 and 数值2 ax$ashFO/!  
9、说明:in 的使用方法 ~< %%n'xmm  
select * from table1 where a [not] in ('值1','值2','值4','值6') tY~gn|M  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 .vsrZ_y?  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) <[mT*  
11、说明:四表联查问题: QND{3Q  
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 ..... 5(RFk Zn4[  
12、说明:日程安排提前五分钟提醒 h[~JCYA  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 +(n&>7 5  
13、说明:一条sql 语句搞定数据库分页 ?O3E.!Q|  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 V45A>#?U  
14、说明:前10条记录 87WIDr  
select top 10 * form table1 where 范围 ;NNYJqWd^]  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)  uYVlF@]  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) CT5\8C  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 8,iBG! RF  
(select a from tableA ) except (select a from tableB) except (select a from tableC) IzVb  
17、说明:随机取出10条数据 lz2B,#  
select top 10 * from tablename order by newid() 3z7SK Gy  
18、说明:随机选择记录 nvY3$ Ty  
select newid() K8[vJ7(!|  
19、说明:删除重复记录 Y,BzBUWK  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ZL/iX~}a'  
20、说明:列出数据库里所有的表名 {8+FxmH  
select name from sysobjects where type='U' ROcI.tL  
21、说明:列出表里的所有的 8R?X$=$]!.  
select name from syscolumns where id=object_id('TableName') "Bl ]_YPv  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 ;e,_F/@`  
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 x(oL\I_Z  
显示结果: to9~l"n.s  
type vender pcs }j<:hD QP  
电脑 A 1 Ze+p;v  
电脑 A 1 '}#=I 9=ss  
光盘 B 2 6S]K@C=r  
光盘 A 2 *IBT!@*Q&  
手机 B 3 <u "xHl8Io  
手机 C 3 4<%(Y-_sF  
23、说明:初始化表table1 $ 9bIUJ  
TRUNCATE TABLE table1 "#zSk=52z  
24、说明:选择从10到15的记录 y!_*CYZ~m  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc qTc-Z5  
9C&Xs nk  
I`hltJM'  
38ac~1HjE  
三、技巧 Gy}WZ9{  
1、1=1,1=2的使用,在SQL语句组合时用的较多 dy/\>hu  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, 5cahbx1"  
如: r'bctFsD  
if @strWhere !='' : :;YS9e  
begin aumWU{j=  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere ~N "rr.w  
end \S #Mc  
else K"Vo'9R[_  
begin !O|d,)$q  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' WcRTv"4&  
end 2gP^+.  
我们可以直接写成 `^ FAD   
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere VpmwN`  
2、收缩数据库 gbvM2  
--重建索引 wJ.?u]f@  
DBCC REINDEX K]c|v i_D  
DBCC INDEXDEFRAG B%y?+4;zA  
--收缩数据和日志 pXn(#n<  
DBCC SHRINKDB %[3?vX  
DBCC SHRINKFILE NsbC0xLd  
3、压缩数据库 2ed4xh V  
dbcc shrinkdatabase(dbname) @4sEHk 3  
4、转移数据库给新用户以已存在用户权限 R<\5 q%@G  
exec sp_change_users_login 'update_one','newname','oldname' tE|W8=be/  
go jnF-kia  
5、检查备份集 !9 7U2L4  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' \iN3/J4  
6、修复数据库 Buxn!s  
ALTER DATABASE [dvbbs] SET SINGLE_USER W^U6O&-K  
GO kdmmfw  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK r.-U=ql  
GO UXs=7H".  
ALTER DATABASE [dvbbs] SET MULTI_USER Sr$&]R]^  
GO -@*[   
7、日志清除 j%w}hGW%,  
SET NOCOUNT ON 6?B'3~ r  
DECLARE @LogicalFileName sysname, Evjvaa^  
        @MaxMinutes INT, |[6jf!F  
        @NewSize INT AQ}(v,DOb  
USE    tablename            -- 要操作的数据库名 &P2tzY'  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 Q4~/Tl;  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. [Eq7!_ 3  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) |A .U~P):  
-- Setup / initialize K!AW8FnHkZ  
DECLARE @OriginalSize int XSfl'Fll D  
SELECT @OriginalSize = size U2hPsF4f  
  FROM sysfiles #:q$sKQ_$  
  WHERE name = @LogicalFileName whH_<@!  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + JXT%@w>I  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + Z}X oWT2f  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' ,=Q;@Z4 vJ  
  FROM sysfiles /R/\>'{E&c  
  WHERE name = @LogicalFileName yM_ta '^$  
CREATE TABLE DummyTrans F+!w[}0  
  (DummyColumn char (8000) not null) %R?B=W7 ;Q  
DECLARE @Counter  INT, K[,d9j`^  
        @StartTime DATETIME, _1>Xk_  
        @TruncLog  VARCHAR(255) G 51l_  
SELECT  @StartTime = GETDATE(), XIep3l*  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' Ca2He}r`  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) -'!K("  
EXEC (@TruncLog)  _%r+?I  
-- Wrap the log if necessary. 62-,!N 1-  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired O {hM  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  !sTOo  
      AND (@OriginalSize * 8 /1024) > @NewSize  W't?aj I|  
  BEGIN -- Outer loop. 0fOx&"UAB  
    SELECT @Counter = 0 DfPC@` k  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) h4iz(*  
      BEGIN -- update Y5dt/8Jo  
        INSERT DummyTrans VALUES ('Fill Log')  \OzPDN  
        DELETE DummyTrans [ClDKswq  
        SELECT @Counter = @Counter + 1 2`Dqu"TWh  
      END  yuef84~  
    EXEC (@TruncLog)  E%.w6-  
  END  o$4i{BL  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + " Y1]6 Zu  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + cr wui8  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' sY- ] Q  
  FROM sysfiles T"bH{|:%*=  
  WHERE name = @LogicalFileName bmid;X|  
DROP TABLE DummyTrans q.}M^iDe  
SET NOCOUNT OFF +VSq[P  
8、说明:更改某个表 o[A y2"e?  
exec sp_changeobjectowner 'tablename','dbo' "VIoV u  
9、存储更改全部表 $`a>y jma  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch `F(ghC  
@OldOwner as NVARCHAR(128), tz^2?wO  
@NewOwner as NVARCHAR(128) Rfx}[!<{N  
AS c>$PLO^  
DECLARE @Name  as NVARCHAR(128) n%Rl$  
DECLARE @Owner  as NVARCHAR(128) {0(:5%  
DECLARE @OwnerName  as NVARCHAR(128) )'1rZb5  
DECLARE curObject CURSOR FOR ^ ,cwm:B@  
select 'Name'  = name, RV=Z$  
  'Owner'  = user_name(uid) .="/n8B  
from sysobjects V7gv@<1<y  
where user_name(uid)=@OldOwner L vPcH  
order by name &s{" Vc9]  
OPEN  curObject yIq. m=  
FETCH NEXT FROM curObject INTO @Name, @Owner  %"jp':  
WHILE(@@FETCH_STATUS=0) &^7^7:Y=?  
BEGIN    Yk^clCB{A(  
if @Owner=@OldOwner j@o \d%.'!  
begin lSG"c+iV  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) \jpm   
  exec sp_changeobjectowner @OwnerName, @NewOwner W5SCm(QS5  
end vyA `Z1  
-- select @name,@NewOwner,@OldOwner hI#1Ybl  
FETCH NEXT FROM curObject INTO @Name, @Owner `;c{E%qeq  
END 2=%R>&]*  
close curObject )IFFtU~,  
deallocate curObject au;ZAXM|  
GO (DnrJ.QU}t  
10、SQL SERVER中直接循环写入数据 VpO+52&  
declare @i int ! N!A%  
set @i=1 xb (Cd  
while @i<30 ;1MRBk,  
begin |19zjhl  
  insert into test (userid) values(@i) C f(g  
  set @i=@i+1 dI%#cf1  
end S|Yz5)*  
小记存储过程中经常用到的本周,本月,本年函数 vmGGdj5aI  
Dateadd(wk,datediff(wk,0,getdate()),-1) a W9_[#z5  
Dateadd(wk,datediff(wk,0,getdate()),6) 'V`Hp$r  
Dateadd(mm,datediff(mm,0,getdate()),0) e h6\y7 9g  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) v1`*}.#  
Dateadd(yy,datediff(yy,0,getdate()),0) x-@}x@n&[  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) bm\Zp  
上面的SQL代码只是一个时间段 %+bw2;a6  
Dateadd(wk,datediff(wk,0,getdate()),-1) ytyX:e"  
Dateadd(wk,datediff(wk,0,getdate()),6) F8pP(Wl  
就是表示本周时间段. .l:x!  
下面的SQL的条件部分,就是查询时间段在本周范围内的: 45(n!"u65  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) O/ ih9,  
而在存储过程中 U{Xx)l/o  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) YVW`|'7)|  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) z#u<]] 5  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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