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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 JV/,QWar  
=az$WRV+7!  
aFSZYyPxwv  
一、基础 ,f1wN{P  
1、说明:创建数据库 eP2 yU  
CREATE DATABASE database-name Q.|2/6hD7[  
2、说明:删除数据库 {'ZnxK'  
drop database dbname |-|BM'Y  
3、说明:备份sql server A |&EI-In  
--- 创建 备份数据的 device r"Bf@va  
USE master _ xC~44  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' -12v/an]L7  
--- 开始 备份 YG8oy!Zl  
BACKUP DATABASE pubs TO testBack g/@CESfm'  
4、说明:创建新表 9e=*jRs]l^  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) PT4`1Oy}/1  
根据已有的表创建新表: k@Tt,.];  
A:create table tab_new like tab_old (使用旧表创建新表) cnc$^[c  
B:create table tab_new as select col1,col2... from tab_old definition only H{XW?O^@  
5、说明:删除新表 @<PL  
drop table tabname 4Oy c D  
6、说明:增加一个列 _YJwF1e+M  
Alter table tabname add column col type vLke,MKW  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 us cR/d  
7、说明:添加主键: Alter table tabname add primary key(col) }n=NHHtJ  
说明:删除主键: Alter table tabname drop primary key(col) bk?\=4B:E  
8、说明:创建索引:create [unique] index idxname on tabname(col....) y,x~S\>+  
删除索引:drop index idxname ) )F.|w  
注:索引是不可更改的,想更改必须删除重新建。 O>Sbb2q?"  
9、说明:创建视图:create view viewname as select statement QCo^#-   
删除视图:drop view viewname gvJJ.IX]+  
10、说明:几个简单的基本的sql语句 gMvvDP!Wp  
选择:select * from table1 where 范围 pE< ' '`  
插入:insert into table1(field1,field2) values(value1,value2) F,zJdJ  
删除:delete from table1 where 范围 O92Yd$S  
更新:update table1 set field1=value1 where 范围 !+6l.`2WI  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 0%t|?@HoN  
排序:select * from table1 order by field1,field2 [desc]  ;E&XFTdO  
总数:select count as totalcount from table1 3q>"#+R.t  
求和:select sum(field1) as sumvalue from table1 ,*4"d._Y  
平均:select avg(field1) as avgvalue from table1 ; M(}fV]  
最大:select max(field1) as maxvalue from table1 [Ok8l='  
最小:select min(field1) as minvalue from table1 >H1d9y +Z  
\\qg2yI  
?*@h]4+k'  
[GuDMl3hC  
11、说明:几个高级查询运算词 \f  LBw0  
}B- A*TI<h  
Dpd$&Wr0Y  
A: UNION 运算符 UE4#j \  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 cTnbI4S;  
B: EXCEPT 运算符 Y'5ck(  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 f+6l0@K2  
C: INTERSECT 运算符 GCKl [<9*  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 US|vYd}u+  
注:使用运算词的几个查询结果行必须是一致的。 %)D7Dr  
12、说明:使用外连接 fUL"fMoU  
A、left outer join: =gIYa  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 wj^I1;lO  
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 "Pc,+>vh  
B:right outer join: = I(s7=Liu  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 =/;_7|ssd  
C:full outer join: JdHc'WtS!|  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 Kq$Zyf=E  
ie!4z34  
W!k6qTz)  
二、提升 3EvA 5K.  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) #+;=ijyF  
法一:select * into b from a where 1<>1 taQ[>x7b  
法二:select top 0 * into b from a 6`C27  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 7|-xM>L$A  
insert into b(a, b, c) select d,e,f from b; $ZRN#x@  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) zEW:Xe)  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 fq|2E&&v  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. _&/Zab5  
4、说明:子查询(表名1:a 表名2:b) %\cC]<>  
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) @nP}q!y  
5、说明:显示文章、提交人和最后回复时间 {Y[D!W2y  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b DVJc-.x8  
6、说明:外连接查询(表名1:a 表名2:b) q UnFEg  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c arP+(1U  
7、说明:在线视图查询(表名1:a ) pqSE|3*l  
select * from (SELECT a,b,c FROM a) T where t.a > 1; pJz8e&wyLM  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 {yHfE,  
select * from table1 where time between time1 and time2 L\ %_<2  
select a,b,c, from table1 where a not between 数值1 and 数值2 \bOjb\ w$  
9、说明:in 的使用方法 fhmr*E'J  
select * from table1 where a [not] in ('值1','值2','值4','值6') -z$0S%2?  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 }gW/heUE  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) w8 $Qh%J'<  
11、说明:四表联查问题: 6iG<"{/U5  
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 ..... O+?zn:  
12、说明:日程安排提前五分钟提醒 kPH^X}O$  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 {*<C!Qg  
13、说明:一条sql 语句搞定数据库分页  >Gu0&  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 ,NEs{! T  
14、说明:前10条记录 3kCbD=yF  
select top 10 * form table1 where 范围 i =N\[&  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) Wu( 8 G  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) `tG_O  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 kZ9< j+.  
(select a from tableA ) except (select a from tableB) except (select a from tableC) <6C9R>  
17、说明:随机取出10条数据 j>xVy]v=|  
select top 10 * from tablename order by newid() N o(f0g.  
18、说明:随机选择记录 2.D!4+&  
select newid() /8}+# h)[  
19、说明:删除重复记录 _oTT3[7P  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) x\.i `ukx  
20、说明:列出数据库里所有的表名 U.U.\   
select name from sysobjects where type='U' es[5B* 5  
21、说明:列出表里的所有的 ^P/D8cXa4  
select name from syscolumns where id=object_id('TableName') b@/ON}gX  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 cJEz>Z6[  
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 49oW 'j  
显示结果: $ bNe0  
type vender pcs w3qf7{b  
电脑 A 1 rA,Y_1b *  
电脑 A 1 !rg0U<bO!  
光盘 B 2 @>2rz  
光盘 A 2 7;'.5,-3c  
手机 B 3 XDk o{jEJ  
手机 C 3 YeC,@d[  
23、说明:初始化表table1 npcBpGL{  
TRUNCATE TABLE table1 D?}m h1#  
24、说明:选择从10到15的记录 yvWzc uL#  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc 0DB<hpC:5  
\'+{X(]  
i @9 Qb  
I"sobZ`  
三、技巧 W}k?gg=  
1、1=1,1=2的使用,在SQL语句组合时用的较多 ,{?bM  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, ]ZGvRA&  
如: ckN(`W,xp  
if @strWhere !='' $&=;9="  
begin {]\uR-a(o  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 3Ge<G  
end HwW[M[qA  
else u45h{i-e  
begin ~hz@9E]O  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 7e4tUAiuU  
end SKSAriS~  
我们可以直接写成 A Ok7G?Y  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere h0 GdFWN  
2、收缩数据库 92aDHECo  
--重建索引 4 uy@ {  
DBCC REINDEX V87ee,  
DBCC INDEXDEFRAG i %hn  
--收缩数据和日志 t+!gzZ  
DBCC SHRINKDB Ot$cmBhw!  
DBCC SHRINKFILE r(1pvcWY-  
3、压缩数据库  df4^C->:  
dbcc shrinkdatabase(dbname) CESe}^)n  
4、转移数据库给新用户以已存在用户权限 Wytvs*\`  
exec sp_change_users_login 'update_one','newname','oldname' EkStb#  
go rfXF 01I  
5、检查备份集 "UoCT7X  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' ~I\r1Wj;  
6、修复数据库 O3C)N I\i  
ALTER DATABASE [dvbbs] SET SINGLE_USER 0Dm`Ek3A7x  
GO |t65# 1  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK :*P___S=  
GO =*:[(Py1  
ALTER DATABASE [dvbbs] SET MULTI_USER W|H4i;u  
GO s/G5wRl<  
7、日志清除 {`K]sa7`  
SET NOCOUNT ON [wy3Ld  
DECLARE @LogicalFileName sysname, m>uI\OY{n  
        @MaxMinutes INT, Tc3ih~LvG  
        @NewSize INT z<[.MH`ln  
USE    tablename            -- 要操作的数据库名 <S8I"8{Mb  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 *M5$ h*;v  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. 2>MP:yY;K  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) Eo { 1y  
-- Setup / initialize XuFm4DEJ  
DECLARE @OriginalSize int }U?gKlLg  
SELECT @OriginalSize = size p21=$?k!;  
  FROM sysfiles @%G'U&R{  
  WHERE name = @LogicalFileName D2TXOPH  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + SJ@8[n.x  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 7:VEM;[d  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' Xw*%3'  
  FROM sysfiles ;ad9{":J#B  
  WHERE name = @LogicalFileName !QQ<Ai!E  
CREATE TABLE DummyTrans k\Z;Cmh>  
  (DummyColumn char (8000) not null) 1FD7~S|  
DECLARE @Counter  INT, ^C:{z)"h  
        @StartTime DATETIME, f^6&Fb>  
        @TruncLog  VARCHAR(255)  g`)/x\  
SELECT  @StartTime = GETDATE(), igRDt{}  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' ^i`3cCFB<  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) E2qB:  
EXEC (@TruncLog) lk*0c {_L  
-- Wrap the log if necessary. {m+S{dWp  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired "]SJbuzh  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  gQI(=in  
      AND (@OriginalSize * 8 /1024) > @NewSize  $dx1[ V+_  
  BEGIN -- Outer loop. 6z p@#vYI  
    SELECT @Counter = 0 >uyeI&z  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) c69U1  
      BEGIN -- update s=q%:uCO  
        INSERT DummyTrans VALUES ('Fill Log')  ,NaV [ "9$  
        DELETE DummyTrans ~51kiQW  
        SELECT @Counter = @Counter + 1 nj~$%vmA  
      END  PUQ_w  
    EXEC (@TruncLog)  =#.8$oa^  
  END  %)<oX9E  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + f\x@ C)E  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + _o&,  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' P;L)1 g  
  FROM sysfiles (s V]UGrZ  
  WHERE name = @LogicalFileName j#LV7@H.e?  
DROP TABLE DummyTrans .fLiXx  
SET NOCOUNT OFF vy{rwZ$  
8、说明:更改某个表 x%IXwP0  
exec sp_changeobjectowner 'tablename','dbo' Eo7 _v  
9、存储更改全部表 oN&rq6eN  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch o7c%\v[  
@OldOwner as NVARCHAR(128), `r~`N`o5A  
@NewOwner as NVARCHAR(128) _:ZFCDO  
AS 9&[) (On74  
DECLARE @Name  as NVARCHAR(128) fR]p+\#8u*  
DECLARE @Owner  as NVARCHAR(128) E,*JPK-A x  
DECLARE @OwnerName  as NVARCHAR(128) mc0sdb,c$  
DECLARE curObject CURSOR FOR 3ZW/$KP/  
select 'Name'  = name, nJldz;  
  'Owner'  = user_name(uid) 12:h49AP  
from sysobjects Y91 e1PsV  
where user_name(uid)=@OldOwner NSMjr_  
order by name @b ::6n/u  
OPEN  curObject :c~9>GCE&  
FETCH NEXT FROM curObject INTO @Name, @Owner PSP1>-7)w  
WHILE(@@FETCH_STATUS=0) Zzw}sZ?8  
BEGIN    5(iSOsb  
if @Owner=@OldOwner lQp89*b?=U  
begin AND7jEn  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) m{:"1]  
  exec sp_changeobjectowner @OwnerName, @NewOwner (!3Yc:~RE  
end {~j /XB  
-- select @name,@NewOwner,@OldOwner `G"|MM>P  
FETCH NEXT FROM curObject INTO @Name, @Owner (B>yaM#5  
END lgCHGv2@  
close curObject D+ah ok  
deallocate curObject hb /8Q  
GO h"VpQhi  
10、SQL SERVER中直接循环写入数据 dAYI DE  
declare @i int 'WKu0Yi^'  
set @i=1 "B|nhd  
while @i<30 mC*W2#1pF  
begin S F&M (=w<  
  insert into test (userid) values(@i) p<of<YU)  
  set @i=@i+1 rt5oRf:wY  
end Kf:2%_DB  
小记存储过程中经常用到的本周,本月,本年函数 RJtix uvh@  
Dateadd(wk,datediff(wk,0,getdate()),-1) _qC+'RE3  
Dateadd(wk,datediff(wk,0,getdate()),6) [<en1  
Dateadd(mm,datediff(mm,0,getdate()),0) yM(_P0  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) #6*V7@9]3|  
Dateadd(yy,datediff(yy,0,getdate()),0) Z-4K?;g'k  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) X;s 3y{ku  
上面的SQL代码只是一个时间段 t/v@vJ`vSH  
Dateadd(wk,datediff(wk,0,getdate()),-1) ~=`f]IL  
Dateadd(wk,datediff(wk,0,getdate()),6) =,&u_>Dp  
就是表示本周时间段. zVp[YOS&c  
下面的SQL的条件部分,就是查询时间段在本周范围内的: jGk7=}nw  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) o-\ok|,)#j  
而在存储过程中 "?oo\op  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) 8eOl@}bV  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 'sm[CNzS  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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