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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 +%\j$Pv  
>m1V9A  
XWQ0V  
一、基础 >#U <#  
1、说明:创建数据库 z\8yB`8b^  
CREATE DATABASE database-name MH;%Y"EI  
2、说明:删除数据库 dG?a"/MA  
drop database dbname Q]5^Eiq8  
3、说明:备份sql server 67\Ojl~(1  
--- 创建 备份数据的 device *>p(]_s,  
USE master },aWCvJL  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' Zt2@?w;  
--- 开始 备份 9Pp|d"6]y  
BACKUP DATABASE pubs TO testBack M6*{#Y?  
4、说明:创建新表 tZCe?n]  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) fP1OH&Ar  
根据已有的表创建新表: sVdK^|j  
A:create table tab_new like tab_old (使用旧表创建新表) ('6g)@=\U  
B:create table tab_new as select col1,col2... from tab_old definition only &qP-x98E?  
5、说明:删除新表 q;zf|'&*7C  
drop table tabname tq:tY}:4  
6、说明:增加一个列 %=4ak]As  
Alter table tabname add column col type 9r+O!kF(  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 q+n1~AT  
7、说明:添加主键: Alter table tabname add primary key(col) UdW(\%  
说明:删除主键: Alter table tabname drop primary key(col) y*b.eO  
8、说明:创建索引:create [unique] index idxname on tabname(col....) c_bVF 'Bz  
删除索引:drop index idxname q[OTaSQ~u^  
注:索引是不可更改的,想更改必须删除重新建。 .7gE^  
9、说明:创建视图:create view viewname as select statement iq uTT~  
删除视图:drop view viewname Rw\C0'  
10、说明:几个简单的基本的sql语句 _+ 04M)q0  
选择:select * from table1 where 范围 ?wf+{x-dPP  
插入:insert into table1(field1,field2) values(value1,value2) _6UAeZ*M  
删除:delete from table1 where 范围 <I%9O:R  
更新:update table1 set field1=value1 where 范围 5J+V:Xu{  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! ?5v5:U(A  
排序:select * from table1 order by field1,field2 [desc] !HjNx%o5<  
总数:select count as totalcount from table1 mHEf-6|C`  
求和:select sum(field1) as sumvalue from table1 7 Jx-W|  
平均:select avg(field1) as avgvalue from table1 C{hcK 1-K  
最大:select max(field1) as maxvalue from table1 <j 9Mt=8M  
最小:select min(field1) as minvalue from table1 "x|NG,<[9  
%L13Jsw  
l \^nC2  
+Sd,l>8\  
11、说明:几个高级查询运算词 G(0y|Eq  
i`KZ,   
Ryq"\Q>+  
A: UNION 运算符  4SffP/  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 loUl$X.u  
B: EXCEPT 运算符 fEw=I7{Y  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 ^'[@M'`~L  
C: INTERSECT 运算符 R,+/A8[j  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 L=HVdeE  
注:使用运算词的几个查询结果行必须是一致的。 |^PLZ>  
12、说明:使用外连接 MFH"$t+  
A、left outer join: Sn0gTsZ  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 0)oN[  
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 k<Tez{<  
B:right outer join: TwuX-b  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 F%#*U82  
C:full outer join: !-5S8b  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 3K#mF7)a  
{y^|ET7  
80$0zbw$  
二、提升 .FKJ yzL  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) xEiX<lguyN  
法一:select * into b from a where 1<>1 Sc'c$/  
法二:select top 0 * into b from a pH\^1xj =  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) k?HrD"k"  
insert into b(a, b, c) select d,e,f from b; }PFt  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) &=-e`=qJ'6  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 i2:+h}o$e  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. XW?ybH6  
4、说明:子查询(表名1:a 表名2:b) 9fuJJ3L[  
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) iTLW<wG  
5、说明:显示文章、提交人和最后回复时间 <4Q12:  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b m(Y.X=EZr  
6、说明:外连接查询(表名1:a 表名2:b) -jVaS w t  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c Be{/2jU%  
7、说明:在线视图查询(表名1:a ) Cfr<D3&,]  
select * from (SELECT a,b,c FROM a) T where t.a > 1; JEsLF{  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 ;wbUk5Tf/  
select * from table1 where time between time1 and time2 =a9etF%B  
select a,b,c, from table1 where a not between 数值1 and 数值2 ~#x :z ^U  
9、说明:in 的使用方法 z9M.e.  
select * from table1 where a [not] in ('值1','值2','值4','值6') "brRME3  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 }. xrJ52Tz  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) B.YMP;7>  
11、说明:四表联查问题: ;vJ\]T ml  
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 ..... 2Io6s '  
12、说明:日程安排提前五分钟提醒 Ns2,hQFc  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 m4"N+_j  
13、说明:一条sql 语句搞定数据库分页 3ximNQ} S  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 9k\)tWe  
14、说明:前10条记录 ut$,?k!M  
select top 10 * form table1 where 范围 Hwp{<  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) (LRM~5KVg  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) +5HnZ?E\  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 V#NG+U.B  
(select a from tableA ) except (select a from tableB) except (select a from tableC) m Ztv G,  
17、说明:随机取出10条数据 T A\4uy6o  
select top 10 * from tablename order by newid() ou'~{-_xd  
18、说明:随机选择记录 VT% KN`l  
select newid() gMs+?SNHAh  
19、说明:删除重复记录 i*S|qX7``  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) CGC-"A/W  
20、说明:列出数据库里所有的表名 pcy<2UV  
select name from sysobjects where type='U' 5{13 V*<  
21、说明:列出表里的所有的 <&5m N  
select name from syscolumns where id=object_id('TableName') yuHZ&e  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 X(k{-|9]  
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 KdT[*-  
显示结果: DH:GI1Yu>I  
type vender pcs GIm " )}W  
电脑 A 1 1~2R^#rm  
电脑 A 1 jg [H}  
光盘 B 2 sdJ%S*)5G$  
光盘 A 2 22`oFXb'  
手机 B 3 dGW {l]N  
手机 C 3 !K3})& w  
23、说明:初始化表table1 r{\BbUnf)  
TRUNCATE TABLE table1 y=AsgJ  
24、说明:选择从10到15的记录 +T0op4  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc O' +"d%2'  
Q2/MnM  
L[?nST18%  
H8@8MFz\  
三、技巧 "z^(dF|  
1、1=1,1=2的使用,在SQL语句组合时用的较多 q,B3ru.?d  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, e74zR6  
如: B%tIwUE2  
if @strWhere !='' Vb@ 4(Q  
begin J I<3\=:+  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere FR:d^mL  
end 7}be>(  
else d2rL 8jW  
begin \q~w<%9Dq  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' -2F@~m|  
end hv* >%p  
我们可以直接写成 . yZm^&  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere QsiJ%O Q  
2、收缩数据库 Q}kfM^i  
--重建索引 ~U6" ?  
DBCC REINDEX ^P`NMSw  
DBCC INDEXDEFRAG wV\%R,bZj  
--收缩数据和日志 egm)a   
DBCC SHRINKDB P|e`^Frxt  
DBCC SHRINKFILE pDu{e>S|:  
3、压缩数据库 'p%= <0vrr  
dbcc shrinkdatabase(dbname) ZJ;LD*  
4、转移数据库给新用户以已存在用户权限 *'D=1{WZ!  
exec sp_change_users_login 'update_one','newname','oldname'  gH %y  
go w |_GV}#_  
5、检查备份集 \6sqyWI %  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' xXX/]x>  
6、修复数据库 A\K,_&x1Z  
ALTER DATABASE [dvbbs] SET SINGLE_USER )^4hQ3BS  
GO NYBe"/}GS  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK KOjluP  
GO gQ37>  
ALTER DATABASE [dvbbs] SET MULTI_USER ![ZmV  
GO 57~Uqt  
7、日志清除 [,=d7*b(l  
SET NOCOUNT ON _%Bz,C8  
DECLARE @LogicalFileName sysname, No) m/17y  
        @MaxMinutes INT, CSL#s^4T  
        @NewSize INT gv#4#]  
USE    tablename            -- 要操作的数据库名 Ia2(Km  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 C.~ j'5N  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. ?GdsOg^  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) _\.{6""  
-- Setup / initialize k#O,j pbB  
DECLARE @OriginalSize int $X*mdji  
SELECT @OriginalSize = size #~^btL'dHF  
  FROM sysfiles AoYaVlKG8  
  WHERE name = @LogicalFileName IdPn%)>6  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + bd!U)b(}OV  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + Cq>6rn  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' < f(?T`  
  FROM sysfiles -ynBi;nH  
  WHERE name = @LogicalFileName 1dFa@<5  
CREATE TABLE DummyTrans V<8K@/n@  
  (DummyColumn char (8000) not null) 62[8xn=(%  
DECLARE @Counter  INT, 3HZ~.  
        @StartTime DATETIME, J~KX|QY.S  
        @TruncLog  VARCHAR(255) 8eluO ?p  
SELECT  @StartTime = GETDATE(), G"T\=cQz  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' k=[pm5ZvT~  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) q-nER<  
EXEC (@TruncLog) G?`-]FMO  
-- Wrap the log if necessary. ["y6b*;x  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 9#7J:PfZ<  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  zB*euHIqZ  
      AND (@OriginalSize * 8 /1024) > @NewSize  L@RIZu>ZW+  
  BEGIN -- Outer loop. @o>EBZ7MS  
    SELECT @Counter = 0 - v]Qhf&>  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) )%mg(O8uL  
      BEGIN -- update g5+7p@'fV  
        INSERT DummyTrans VALUES ('Fill Log')  }`xdWY  
        DELETE DummyTrans dAc ?O-~  
        SELECT @Counter = @Counter + 1 2*[QZ9U[@  
      END  5RF4]$zT  
    EXEC (@TruncLog)  0,_b)  
  END  ;o0#(xVz  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + }7ehF6  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + zI^]esX!2_  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' kA4@`YCl  
  FROM sysfiles [dB$U}SEj  
  WHERE name = @LogicalFileName *6Q|}b[qcD  
DROP TABLE DummyTrans ~`qEWvPn  
SET NOCOUNT OFF 1l5J P|x  
8、说明:更改某个表 n[,XU|2  
exec sp_changeobjectowner 'tablename','dbo' 6)qp*P$L  
9、存储更改全部表 rh!;|xB|+  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch 7" 4z+w  
@OldOwner as NVARCHAR(128), -)v@jlg02  
@NewOwner as NVARCHAR(128) p@~ic#X  
AS irbw'^;y  
DECLARE @Name  as NVARCHAR(128) R_ ZK0ar  
DECLARE @Owner  as NVARCHAR(128) O^Q ,-=tA\  
DECLARE @OwnerName  as NVARCHAR(128) c6&Q^p|CF  
DECLARE curObject CURSOR FOR 0 Y>M=|  
select 'Name'  = name, -fy9<  
  'Owner'  = user_name(uid) ` qUX.  
from sysobjects o.m:3!RW  
where user_name(uid)=@OldOwner B(_WZa!  
order by name F]EBD8/b  
OPEN  curObject ;AX8aw,  
FETCH NEXT FROM curObject INTO @Name, @Owner j+rG7z){K  
WHILE(@@FETCH_STATUS=0) VwyVEZt  
BEGIN    yVX8e I  
if @Owner=@OldOwner m&*JMA;^  
begin d%_OT0Ei  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) s?2$ue&-f  
  exec sp_changeobjectowner @OwnerName, @NewOwner ~g6 3qs  
end g^7MMlY%  
-- select @name,@NewOwner,@OldOwner o*5U:'=5}  
FETCH NEXT FROM curObject INTO @Name, @Owner `JB?c  
END q_V0+qH  
close curObject PL X>-7@  
deallocate curObject ,WD X(  
GO nhT-Ido  
10、SQL SERVER中直接循环写入数据 v+G=E2Lhv  
declare @i int -F@L}|  
set @i=1 6gH{ R$7L=  
while @i<30 t)f-mQz)  
begin k&/OU:7Y  
  insert into test (userid) values(@i) -0Cnp/Yj@  
  set @i=@i+1 ~q+hV+fa>  
end Q>Qibr  
小记存储过程中经常用到的本周,本月,本年函数 "4o=,$E=  
Dateadd(wk,datediff(wk,0,getdate()),-1) ea'&xs#GK  
Dateadd(wk,datediff(wk,0,getdate()),6) H[ m <RaG8  
Dateadd(mm,datediff(mm,0,getdate()),0) M|,mr~rRG  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) `=UWqb(K_  
Dateadd(yy,datediff(yy,0,getdate()),0) rHjq1-t  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) FAsFjRS  
上面的SQL代码只是一个时间段 - VxDNT}Tr  
Dateadd(wk,datediff(wk,0,getdate()),-1) zFz10pH  
Dateadd(wk,datediff(wk,0,getdate()),6) oGa^/:6L  
就是表示本周时间段. wE]K~y!`  
下面的SQL的条件部分,就是查询时间段在本周范围内的: q1?&Ev^  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) s{0aBeq  
而在存储过程中 8NBT|N~N  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) m3bCZ 9iE  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) n_?tN\M  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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