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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Bp &6x;MJf  
i B%XBR  
{GUb'J  
一、基础 &K06}[J  
1、说明:创建数据库 +*n] tlk  
CREATE DATABASE database-name USE   
2、说明:删除数据库 ah 4kA LO  
drop database dbname *]FgfttES  
3、说明:备份sql server 'n>K^rA  
--- 创建 备份数据的 device P`}$-#DF  
USE master Pg7>ce  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' e%pu.q\gK  
--- 开始 备份 {V.Wk  
BACKUP DATABASE pubs TO testBack Z/xV\Ggx  
4、说明:创建新表 /CIx$G  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) SrSG{/{  
根据已有的表创建新表: s;s0}Td_1  
A:create table tab_new like tab_old (使用旧表创建新表)  NI^{$QMj  
B:create table tab_new as select col1,col2... from tab_old definition only b([:,T7  
5、说明:删除新表 ] F*|U`  
drop table tabname v,n);  
6、说明:增加一个列 7`Qde!+C  
Alter table tabname add column col type >+L7k^[,0  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 |Es0[cU  
7、说明:添加主键: Alter table tabname add primary key(col) Ny[Q T*nV  
说明:删除主键: Alter table tabname drop primary key(col) (viWY  
8、说明:创建索引:create [unique] index idxname on tabname(col....) =ntft SH  
删除索引:drop index idxname j(&GVy^;?  
注:索引是不可更改的,想更改必须删除重新建。 HB%K|&!+  
9、说明:创建视图:create view viewname as select statement QQ*gFP.Ao  
删除视图:drop view viewname 6j_ 678  
10、说明:几个简单的基本的sql语句 ol50d73B  
选择:select * from table1 where 范围 aXC!t  
插入:insert into table1(field1,field2) values(value1,value2) B@d1xjp)']  
删除:delete from table1 where 范围 SK?I.  
更新:update table1 set field1=value1 where 范围 VXiui'/(  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! WmNA5;<Q  
排序:select * from table1 order by field1,field2 [desc] Ys-^7 y_  
总数:select count as totalcount from table1 @]*[c})/  
求和:select sum(field1) as sumvalue from table1 `4_c0 q)N4  
平均:select avg(field1) as avgvalue from table1 B\f"Iirw  
最大:select max(field1) as maxvalue from table1 g- XKP  
最小:select min(field1) as minvalue from table1 N5yJ'i~,M  
>A<Df  
*E.LP1xP  
cbfD B^_  
11、说明:几个高级查询运算词 ;;M"hI3@  
]7*kWc2  
;3mL^  
A: UNION 运算符 Is ot4HLM  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 Ha?G=X  
B: EXCEPT 运算符 lHcA j{6  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 C(}^fJ6r  
C: INTERSECT 运算符 JT}.F!q6E  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 xg?auje  
注:使用运算词的几个查询结果行必须是一致的。 }*h47t}  
12、说明:使用外连接 V- /YNRV  
A、left outer join: d<!IGt4Ky  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 ,}KwP*:Z  
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 |hc\jb  
B:right outer join: 0Yp>+:#  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 '(tj[&aL  
C:full outer join: @`6}`k  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 X6'H`E[  
5 5^tfu   
W8y$ Ve8m  
二、提升 r|<6Aae&  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) r5[4h'f  
法一:select * into b from a where 1<>1 6s5yyy=L%~  
法二:select top 0 * into b from a +^Fp&K+^  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) c+~Lp SQ  
insert into b(a, b, c) select d,e,f from b; >:%BNeO  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) #,TELzUVE  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 -;vT<G3  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. ) y`i@S}J  
4、说明:子查询(表名1:a 表名2:b) yWNOG 2qAP  
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) &f"T,4Oh  
5、说明:显示文章、提交人和最后回复时间 7|Xe&o<n  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b L1:nfH&:'  
6、说明:外连接查询(表名1:a 表名2:b) b;*c:{W)  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c EZ/^nG  
7、说明:在线视图查询(表名1:a ) W+K.r?G<j  
select * from (SELECT a,b,c FROM a) T where t.a > 1; 0iV;g`%  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 Yh$fQ:yi\&  
select * from table1 where time between time1 and time2 drI\iae{^  
select a,b,c, from table1 where a not between 数值1 and 数值2 <*_o0;h|  
9、说明:in 的使用方法 d+0^u(gc!8  
select * from table1 where a [not] in ('值1','值2','值4','值6') nZxSMN0]  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 +mLD/gK`  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 7k'gt/#up  
11、说明:四表联查问题: &sdx`,  
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 ..... _KN: o10U  
12、说明:日程安排提前五分钟提醒 @`S.@^%7fO  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 w:Ra7ExP  
13、说明:一条sql 语句搞定数据库分页 $R?@L  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 Ik Qe~;Y  
14、说明:前10条记录 _$5@uL{n"^  
select top 10 * form table1 where 范围 `w+1C&>^[  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) 4v Lw?_".  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) >L=;"+B0U&  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 modC6d%  
(select a from tableA ) except (select a from tableB) except (select a from tableC) F\-Si!~oOz  
17、说明:随机取出10条数据 lov%V*tL  
select top 10 * from tablename order by newid() hl<y4y&|  
18、说明:随机选择记录 r%|A$=[Q  
select newid() xG1?F_]  
19、说明:删除重复记录 `c9'0*-  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) M$H`^Pv  
20、说明:列出数据库里所有的表名 cJ2PI  
select name from sysobjects where type='U' jM@?<1  
21、说明:列出表里的所有的 V'I T1~  
select name from syscolumns where id=object_id('TableName') !3V{2-y$-  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 )b0];&hw]  
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 7h`^N5H.q  
显示结果: r**f,PDZ  
type vender pcs Z9VR]cf?  
电脑 A 1 [~)x<=H8{  
电脑 A 1 #ua^{OrC/  
光盘 B 2 GyK(Vb"h6  
光盘 A 2 q/x/N5HU  
手机 B 3 ~)?|J  
手机 C 3 nmg{%P  
23、说明:初始化表table1 c]NN'9G!{  
TRUNCATE TABLE table1 #)]E8=}  
24、说明:选择从10到15的记录 (hN?:q?'  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc W-m"@<Z  
E30Z`$cz:  
iD714+N(  
#ouE r-=  
三、技巧  n}OU Y  
1、1=1,1=2的使用,在SQL语句组合时用的较多 |vz9Hs$@l  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, 'yr{^Pek  
如: ~b6GrY"vB  
if @strWhere !='' yLqF ,pvO  
begin /[{auUxSX  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere I .P6l*$  
end `*k@4.J{  
else 'Wp @b678  
begin dp<$Zw8BE  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' vBoO'l9'M  
end 9yL6W'B!  
我们可以直接写成 `ET& VV  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere oM-[B h]A  
2、收缩数据库 Sc_5FX\Yx  
--重建索引 `HyF_m>\  
DBCC REINDEX J^:n* C  
DBCC INDEXDEFRAG J|5Ay1eF-  
--收缩数据和日志 (NaK3_  
DBCC SHRINKDB t^U^Tr  
DBCC SHRINKFILE SiTeB)/  
3、压缩数据库 cz(G]{N  
dbcc shrinkdatabase(dbname) 2Wl{Br.  
4、转移数据库给新用户以已存在用户权限 FM\[].  
exec sp_change_users_login 'update_one','newname','oldname' 328L)BmW  
go V|: qow:F  
5、检查备份集 Z&Pu8zG /m  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' lDN?|YG  
6、修复数据库 z_n \5.  
ALTER DATABASE [dvbbs] SET SINGLE_USER D/:3R ZF  
GO fGarUV  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK %b?uW] j:  
GO th 2<o5  
ALTER DATABASE [dvbbs] SET MULTI_USER b-%l-u  
GO + zp0" ,2B  
7、日志清除 :0I l|aB  
SET NOCOUNT ON ;;Tq$#vd  
DECLARE @LogicalFileName sysname, ;4kT?3$l  
        @MaxMinutes INT, g~)3WfC$[  
        @NewSize INT NwpS)6<-  
USE    tablename            -- 要操作的数据库名 1Es qQz*$u  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 ^l(^z fsZ  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. ^P$7A]!  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) HeozJ^u\?  
-- Setup / initialize $[z<oN_Q  
DECLARE @OriginalSize int ?cK]C2Ak  
SELECT @OriginalSize = size $5A^'q  
  FROM sysfiles ,g|2NjUAc  
  WHERE name = @LogicalFileName (Ilsk{aB;A  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + 0*yJ %  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + [h-norB((  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' {y-`QS  
  FROM sysfiles (p,}'I#i*  
  WHERE name = @LogicalFileName #pA[k -  
CREATE TABLE DummyTrans J-XTN"O  
  (DummyColumn char (8000) not null)  zy>}L #  
DECLARE @Counter  INT, C}Qt "-%  
        @StartTime DATETIME, (0C&z/  
        @TruncLog  VARCHAR(255) AC4 l<:Yh  
SELECT  @StartTime = GETDATE(), x~+-VF3/  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' V^rW?Do  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) 8zmv 5trt  
EXEC (@TruncLog) (U9a@ 1  
-- Wrap the log if necessary. rQj~[Y.c  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 1exfCm  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  0>@[o8  
      AND (@OriginalSize * 8 /1024) > @NewSize  $ $4W}Ug3U  
  BEGIN -- Outer loop. ti9}*8  
    SELECT @Counter = 0 ;_tO+xL&  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) &t3Jv{  
      BEGIN -- update w2zp#;d  
        INSERT DummyTrans VALUES ('Fill Log')  3.),bm  
        DELETE DummyTrans - _t&+5]  
        SELECT @Counter = @Counter + 1 c0[k T  
      END  Zi{0-m6+  
    EXEC (@TruncLog)  ?\ Q0kr.T%  
  END   AP w6  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + {ERjeuDm]  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + ],&\%jd<  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' ])N%^Qe$U  
  FROM sysfiles (oYW]c}G,  
  WHERE name = @LogicalFileName .@k*p>K  
DROP TABLE DummyTrans KyLp?!|>  
SET NOCOUNT OFF MZ~.(&  
8、说明:更改某个表 Pfan7fq+  
exec sp_changeobjectowner 'tablename','dbo' ny1 \4C  
9、存储更改全部表 fA^SD"xf  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch )`Ed_F}k  
@OldOwner as NVARCHAR(128), it,w^VU_]  
@NewOwner as NVARCHAR(128) k?j Fh6%  
AS ipZHSA  
DECLARE @Name  as NVARCHAR(128) 9,WG!4:+W  
DECLARE @Owner  as NVARCHAR(128) .$wLLE^*  
DECLARE @OwnerName  as NVARCHAR(128) aU(tu2  
DECLARE curObject CURSOR FOR H.~bD[gA  
select 'Name'  = name, r0btC@Hxy  
  'Owner'  = user_name(uid) D9o*8h2$  
from sysobjects :Tb7r6  
where user_name(uid)=@OldOwner _6rKC*Pe1  
order by name 98UlNP  
OPEN  curObject h=[-Er'B  
FETCH NEXT FROM curObject INTO @Name, @Owner #T"64%dX  
WHILE(@@FETCH_STATUS=0) QJSr:dP4dG  
BEGIN    ;BVDt  
if @Owner=@OldOwner } yq  
begin Tz` ,{k  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) x+^Vg3 q  
  exec sp_changeobjectowner @OwnerName, @NewOwner f3 &/r  
end |!Ists  
-- select @name,@NewOwner,@OldOwner sN]Z #7  
FETCH NEXT FROM curObject INTO @Name, @Owner rPO}6lsc  
END `qu] Pxk  
close curObject x'i0KF   
deallocate curObject #LWg"i  
GO a))*F!}c  
10、SQL SERVER中直接循环写入数据 <25ccE9^c  
declare @i int &7Kb]Ti  
set @i=1 g1V)$s 7  
while @i<30 s0!kwrBsp  
begin SqqDV)Uih1  
  insert into test (userid) values(@i) J]\^QMX  
  set @i=@i+1 ^PQM;"  
end u[EK#%  
小记存储过程中经常用到的本周,本月,本年函数 _FsB6 G]mc  
Dateadd(wk,datediff(wk,0,getdate()),-1) EfKntrom[  
Dateadd(wk,datediff(wk,0,getdate()),6) -tyaE  
Dateadd(mm,datediff(mm,0,getdate()),0) } 07r  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) xwOE+  
Dateadd(yy,datediff(yy,0,getdate()),0) @p]UvqtB@  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 8\_*1h40s  
上面的SQL代码只是一个时间段 qTy v.#{y  
Dateadd(wk,datediff(wk,0,getdate()),-1) KPggDKS  
Dateadd(wk,datediff(wk,0,getdate()),6) JqEb;NiP)5  
就是表示本周时间段. $5L(gn[  
下面的SQL的条件部分,就是查询时间段在本周范围内的: 'tuBuYD\  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) la`"$f  
而在存储过程中 Hirr=a3  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) -'ZxN'*%  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) V16%Ne  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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