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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Jn)DZv8?  
7r wNjY#  
v}BXH4&Y  
一、基础 fkJElO-F  
1、说明:创建数据库 Fps:6~gD  
CREATE DATABASE database-name TqTz  
2、说明:删除数据库 do=s=&T  
drop database dbname s!WI:E7  
3、说明:备份sql server {=<m^ 5b9  
--- 创建 备份数据的 device H:.l:PJ  
USE master =JfSg'7  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' (v1~p3H  
--- 开始 备份 AIw<5lW  
BACKUP DATABASE pubs TO testBack fQ_(2+ FM  
4、说明:创建新表 nbdjk1E`~  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 6>j0geFyE2  
根据已有的表创建新表: vDcYz,  
A:create table tab_new like tab_old (使用旧表创建新表) W^N|+$g>H  
B:create table tab_new as select col1,col2... from tab_old definition only sH /08Z  
5、说明:删除新表 R/?ZbMn]!  
drop table tabname p@jwHlX  
6、说明:增加一个列 N=~aj7B%  
Alter table tabname add column col type L}\~)  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 vw[i.af  
7、说明:添加主键: Alter table tabname add primary key(col) j/uu&\e  
说明:删除主键: Alter table tabname drop primary key(col) 5;q{9wvqO  
8、说明:创建索引:create [unique] index idxname on tabname(col....) 5Za%EaW%G  
删除索引:drop index idxname C}Ucyzfr,p  
注:索引是不可更改的,想更改必须删除重新建。 D%!GY1wdn  
9、说明:创建视图:create view viewname as select statement wi@Qf6(mn  
删除视图:drop view viewname JpC_au7CX  
10、说明:几个简单的基本的sql语句 nV3I6  
选择:select * from table1 where 范围 -8m3L  
插入:insert into table1(field1,field2) values(value1,value2) .vWwYG  
删除:delete from table1 where 范围 Uc d~-D  
更新:update table1 set field1=value1 where 范围 v]X*(e  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! j-TRa,4bN  
排序:select * from table1 order by field1,field2 [desc] p}K.-S`MQ  
总数:select count as totalcount from table1 N Nw0 G&  
求和:select sum(field1) as sumvalue from table1 ~NG+DyGa=  
平均:select avg(field1) as avgvalue from table1 a<CACWsN.T  
最大:select max(field1) as maxvalue from table1 = ow=3Ku  
最小:select min(field1) as minvalue from table1 e]-%P(}Z  
V?&P).5)  
U *:E|'>  
ciGJtD&P  
11、说明:几个高级查询运算词 Ri;_ 8v[H|  
"WZ|   
njckPpyb@  
A: UNION 运算符 IA680^  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 t$g@+1p4  
B: EXCEPT 运算符 B/Z-Cpz]  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 S<eB&qT$  
C: INTERSECT 运算符 S pk8u4  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 JasA w7  
注:使用运算词的几个查询结果行必须是一致的。 e\r%"~v  
12、说明:使用外连接 ~}RfepM  
A、left outer join: cG,zO-H  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 M.\V/OX  
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 y3OF+;E  
B:right outer join: k8z1AP  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 >)+N$EN  
C:full outer join: wEC,Mbn  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 9PdD=9HH  
' r/xBj[Z  
:*Y2na)qQ  
二、提升 SM\qd4  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) n&3}F?   
法一:select * into b from a where 1<>1 v?0F  
法二:select top 0 * into b from a J?C:@Q  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 9qUkw&}H  
insert into b(a, b, c) select d,e,f from b; qKTzigjj  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) LB`{35b-  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 5GbC}y>  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. oI-,6G}  
4、说明:子查询(表名1:a 表名2:b) E8BIb 'b;  
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) \P7<q,OGS  
5、说明:显示文章、提交人和最后回复时间 &%m%b5  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b JJK-+a6cX  
6、说明:外连接查询(表名1:a 表名2:b) )+|Y;zC9  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c J4u>77I  
7、说明:在线视图查询(表名1:a ) Xy r'rm5+b  
select * from (SELECT a,b,c FROM a) T where t.a > 1; r1:CHIwK  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 Xw3j(`w$,  
select * from table1 where time between time1 and time2 gloJ;dE B  
select a,b,c, from table1 where a not between 数值1 and 数值2 /36gf  
9、说明:in 的使用方法 -iL:D<!Cb_  
select * from table1 where a [not] in ('值1','值2','值4','值6') pQ>|d H+.  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 S%aup(wu6  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) Da8 |eN}   
11、说明:四表联查问题: W4UK?#S+  
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 ..... .><-XJ  
12、说明:日程安排提前五分钟提醒 uVTacN%X  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 hZlHY9[t?  
13、说明:一条sql 语句搞定数据库分页 <@P. 'rE  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 FbRGfHL[  
14、说明:前10条记录 )FNn  
select top 10 * form table1 where 范围 Ft?eqDS1  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) HLOr Dlj7  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) sC0u4w>Y  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 +abb[  
(select a from tableA ) except (select a from tableB) except (select a from tableC) u /]P  
17、说明:随机取出10条数据 *jvP4Nz)k  
select top 10 * from tablename order by newid() V0BT./ B\<  
18、说明:随机选择记录 c g)> A  
select newid() Lq;T\m_de  
19、说明:删除重复记录 fp*6Dv_  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) D<|$ZuB4  
20、说明:列出数据库里所有的表名 ,H%[R+)  
select name from sysobjects where type='U' m2"wMt"*V  
21、说明:列出表里的所有的 1wFW&|>1  
select name from syscolumns where id=object_id('TableName') |L`U2.hb  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 X</Sl>[8  
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 Np%Q-T\  
显示结果: 7e\Jg/FU  
type vender pcs J/ Lf(;C_  
电脑 A 1 `hdff0  
电脑 A 1 :;eQ*{ `\  
光盘 B 2 0KTO )K  
光盘 A 2  %S%IW  
手机 B 3 (qvH=VTwP  
手机 C 3 kDDC@A $  
23、说明:初始化表table1 2Z%n "z68  
TRUNCATE TABLE table1 _ xTpW  
24、说明:选择从10到15的记录 A]TEs)#*7)  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc :"MHmm=uU8  
KE ?NQMU  
f;Oh"Yt  
2]I l:>n,  
三、技巧 <D3mt Q  
1、1=1,1=2的使用,在SQL语句组合时用的较多 1Acs0` 3  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, a6j& po  
如: ?&-1(&  
if @strWhere !='' B{R[z%Y  
begin ,dR.Sac v  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere }6!/Nb  
end "+T`{$Z=C  
else }oA>0Nw$K  
begin C1e@{>  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' "3\y~<8%'  
end 8-NycG&)  
我们可以直接写成  $8rnf  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere \c CH/  
2、收缩数据库 v}N\z2A  
--重建索引 +/Y2\ s  
DBCC REINDEX .Y{x!Q"  
DBCC INDEXDEFRAG 9Y'pT.Gy b  
--收缩数据和日志 _%=CW' B  
DBCC SHRINKDB 7)V"E-6h  
DBCC SHRINKFILE +T@BOYhgq  
3、压缩数据库 8 5X}CCQ  
dbcc shrinkdatabase(dbname) _wK.n.,S~  
4、转移数据库给新用户以已存在用户权限 XQ9W y  
exec sp_change_users_login 'update_one','newname','oldname' Y4 HN1  
go 6"* <0  
5、检查备份集 Lo1ySLo$G  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' i7-~"g  
6、修复数据库 yy=hCjQ)  
ALTER DATABASE [dvbbs] SET SINGLE_USER ;7qIm83  
GO dh7`eAMY   
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK UjQz   
GO &{.IUg  
ALTER DATABASE [dvbbs] SET MULTI_USER 2I [zV7 @t  
GO 'Og@<~/Xy  
7、日志清除 dhob]8b  
SET NOCOUNT ON 6!"wiM"]  
DECLARE @LogicalFileName sysname, 0>Snps3*Z  
        @MaxMinutes INT, }+n|0xK  
        @NewSize INT |Q 3d7y  
USE    tablename            -- 要操作的数据库名 m%`YAD@2z  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 r[i^tIv6As  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. 7/IL" D  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) ;,[0bmL  
-- Setup / initialize GNMOHqg4  
DECLARE @OriginalSize int qx5`lm~L  
SELECT @OriginalSize = size W+#Q>^Q>  
  FROM sysfiles AE}cHBwZE  
  WHERE name = @LogicalFileName 'JMW.;Lh?X  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + _x<NGIz  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + !2KQi=Ng  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' oYkd%N9P  
  FROM sysfiles -i:WA^yKgw  
  WHERE name = @LogicalFileName uKP4ur@1  
CREATE TABLE DummyTrans ; zJb("n  
  (DummyColumn char (8000) not null) Sc[#]2 }  
DECLARE @Counter  INT, !6'N-b1  
        @StartTime DATETIME, U<w8jVE  
        @TruncLog  VARCHAR(255) A+RW=|:  
SELECT  @StartTime = GETDATE(), ~r.R|f]IQ  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' >n09K8 A  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) Lmte ~oBi  
EXEC (@TruncLog) f(|k0$EIu  
-- Wrap the log if necessary. C(XV YND3  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired $t;:"i>  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  "u^Erj# /  
      AND (@OriginalSize * 8 /1024) > @NewSize  oPmz$]_Z  
  BEGIN -- Outer loop. j9h/`Bn  
    SELECT @Counter = 0 '8;bc@cE  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) E#KZZ lbx  
      BEGIN -- update Mr)t>4  
        INSERT DummyTrans VALUES ('Fill Log')  x fb .Z(  
        DELETE DummyTrans |0i{z(B  
        SELECT @Counter = @Counter + 1 4^W!,@W  
      END  0/)2RmF  
    EXEC (@TruncLog)  X hq ss),  
  END  :|W=2( >  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + A'jvm@DvQI  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + y47N(;vy  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' ?sk>Mzr  
  FROM sysfiles  +|LM"  
  WHERE name = @LogicalFileName ircF3P>a?  
DROP TABLE DummyTrans ,&UKsrs_  
SET NOCOUNT OFF K KB+o)*W  
8、说明:更改某个表 x:FZEyalG  
exec sp_changeobjectowner 'tablename','dbo' AO7[SHDZ  
9、存储更改全部表 _2jL]mB  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch v?VDASR2`  
@OldOwner as NVARCHAR(128), Jo { :]:  
@NewOwner as NVARCHAR(128) \78E>(`'  
AS MrR`jXz  
DECLARE @Name  as NVARCHAR(128) !.6n=r8 d  
DECLARE @Owner  as NVARCHAR(128) `D77CC]vU  
DECLARE @OwnerName  as NVARCHAR(128) %aNm j)L  
DECLARE curObject CURSOR FOR d=vuy   
select 'Name'  = name, 'f6H#V*C  
  'Owner'  = user_name(uid) (mIjG)4t  
from sysobjects 60~;UBm5O  
where user_name(uid)=@OldOwner i O$87!  
order by name EHC7b^|3}  
OPEN  curObject }:2##<"\t  
FETCH NEXT FROM curObject INTO @Name, @Owner =de'Yy:\-  
WHILE(@@FETCH_STATUS=0) a$=~1@  
BEGIN    $Lp [i <O]  
if @Owner=@OldOwner %R >n5m  
begin Y1h)aQ5{  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) mXz*Gi  
  exec sp_changeobjectowner @OwnerName, @NewOwner ,>-jZtm  
end zr|DC] 3  
-- select @name,@NewOwner,@OldOwner Rn?JMM]  
FETCH NEXT FROM curObject INTO @Name, @Owner ,m{R m0  
END \\R}3 >Wc  
close curObject u+j\PWOtm  
deallocate curObject +cH>'OXoB  
GO UH MJ(.Wa-  
10、SQL SERVER中直接循环写入数据 D&):2F^9.  
declare @i int Z[nHo'  
set @i=1 ~z:]rgX  
while @i<30 _Ay^v#a  
begin ]Kt@F0U<o  
  insert into test (userid) values(@i) nB`|VYmOP1  
  set @i=@i+1 }<^mUG  
end d^=)n-!T  
小记存储过程中经常用到的本周,本月,本年函数 >=0]7k;  
Dateadd(wk,datediff(wk,0,getdate()),-1) !Aj}sh{  
Dateadd(wk,datediff(wk,0,getdate()),6) k ;^$Pd?t  
Dateadd(mm,datediff(mm,0,getdate()),0) Lng. X8D  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 67(s\  
Dateadd(yy,datediff(yy,0,getdate()),0) t1w]L  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) #uU(G\^T  
上面的SQL代码只是一个时间段 UDJjw  
Dateadd(wk,datediff(wk,0,getdate()),-1) !`e`4y*N  
Dateadd(wk,datediff(wk,0,getdate()),6) Z{B  e  
就是表示本周时间段. r.e K;  
下面的SQL的条件部分,就是查询时间段在本周范围内的: 1%v6d !  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) `^[k8Z(  
而在存储过程中 RvAgv[8  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) O]tR~a  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 9K@ I  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
认证码:
验证问题:
10+5=?,请输入中文答案:十五