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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 a O"nD_7  
.UT,lqEkv  
{0A[v}X ~  
一、基础 hVT=j ?~  
1、说明:创建数据库 DSDl[;3O{s  
CREATE DATABASE database-name D<_,>{$gW  
2、说明:删除数据库 }QWTPRn  
drop database dbname E+^} B/"  
3、说明:备份sql server T}w*K[z $  
--- 创建 备份数据的 device =& Tu`m  
USE master 6uCk0 B|  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' BqLtTo?'  
--- 开始 备份 r'9=k x  
BACKUP DATABASE pubs TO testBack Y6;0khp  
4、说明:创建新表 =XacG}_  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) |oBdryi  
根据已有的表创建新表: a! 0?L0_W&  
A:create table tab_new like tab_old (使用旧表创建新表) 7/D9n9F  
B:create table tab_new as select col1,col2... from tab_old definition only siss_1J  
5、说明:删除新表 2#n$x*CY  
drop table tabname ZHiICh|et%  
6、说明:增加一个列 s!j(nUd/  
Alter table tabname add column col type Eis%)oE  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 `jUS{ 3^  
7、说明:添加主键: Alter table tabname add primary key(col) ArmL,  
说明:删除主键: Alter table tabname drop primary key(col) \[IdR^<YM  
8、说明:创建索引:create [unique] index idxname on tabname(col....) +%Bf y4F6  
删除索引:drop index idxname WB=<W#?w7%  
注:索引是不可更改的,想更改必须删除重新建。 SVg@xu+  
9、说明:创建视图:create view viewname as select statement Wy^[4|6  
删除视图:drop view viewname 7>#L  
10、说明:几个简单的基本的sql语句 ziLr }/tg  
选择:select * from table1 where 范围 bn*{*=(|  
插入:insert into table1(field1,field2) values(value1,value2) 8)-t91hkL  
删除:delete from table1 where 范围 j quSR=  
更新:update table1 set field1=value1 where 范围 YzhN|!;!k  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! @KW+?maW  
排序:select * from table1 order by field1,field2 [desc] ?9('o\N:  
总数:select count as totalcount from table1 /K1$_   
求和:select sum(field1) as sumvalue from table1 l9ifUh e  
平均:select avg(field1) as avgvalue from table1 =qS^Wz.  
最大:select max(field1) as maxvalue from table1 9A} kkMB:  
最小:select min(field1) as minvalue from table1 }lNuf u  
,8J*S  
LKf5r,C  
!aW*dD61  
11、说明:几个高级查询运算词 :`>+f.)  
Z z; <P  
~U7\ LBF  
A: UNION 运算符 )Py+jc.  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 Z '>eT)  
B: EXCEPT 运算符 uu@'02G8  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 G8(i).Q  
C: INTERSECT 运算符 M;p q2$   
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 :LIKp;  
注:使用运算词的几个查询结果行必须是一致的。 l6`d48U  
12、说明:使用外连接 L"[wa.<  
A、left outer join: 1&@wb'MBs.  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 "mP*}VF  
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 wh7a|  
B:right outer join: Y3MR:{}  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 k,NU,^ &  
C:full outer join: &W!d}, ;  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 a5U2[Ko80  
bF Y)o Z  
7]. IT(  
二、提升 3 ?|; on  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) <0Egkz3s  
法一:select * into b from a where 1<>1 AXU!-er$  
法二:select top 0 * into b from a Acq>M^E3  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) ^0ZKHR(}e  
insert into b(a, b, c) select d,e,f from b; j=jrzG+`  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) 1M~:]}*<  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 dW^_tzfF7  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. w7t"&=pF7  
4、说明:子查询(表名1:a 表名2:b) A6x_!  
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) dT@SO  
5、说明:显示文章、提交人和最后回复时间  0[!gk]p  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b NX{-D}1X=  
6、说明:外连接查询(表名1:a 表名2:b) CTbhwY(/  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c KGE-RK  
7、说明:在线视图查询(表名1:a ) NK#"qK""k  
select * from (SELECT a,b,c FROM a) T where t.a > 1; zS"zb  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 zRJKIm  
select * from table1 where time between time1 and time2 %qjyk=z+Z  
select a,b,c, from table1 where a not between 数值1 and 数值2 #&2N,M!Q  
9、说明:in 的使用方法 wyzBkRg.  
select * from table1 where a [not] in ('值1','值2','值4','值6') d>"$^${  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 #2vG_B<M)  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) \,bFm,kC?  
11、说明:四表联查问题: |8U7C\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 ..... 6!Ji>h.Ak  
12、说明:日程安排提前五分钟提醒 u*-<5& X  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 ZRq}g:  
13、说明:一条sql 语句搞定数据库分页 O7'^*"S  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 E~@HC5.M  
14、说明:前10条记录 F']%q 0  
select top 10 * form table1 where 范围 ; 7v7V  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) Y <i}"eI*  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) -"dy z(  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 rRG\:<a  
(select a from tableA ) except (select a from tableB) except (select a from tableC) Rb}&c)4  
17、说明:随机取出10条数据 f}ij=Y9  
select top 10 * from tablename order by newid() 79B`w #  
18、说明:随机选择记录 u-M$45vct  
select newid() *0@; kD=  
19、说明:删除重复记录 nIKT w  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) E4aCGg  
20、说明:列出数据库里所有的表名 !2M[  
select name from sysobjects where type='U' sS2E8Z2  
21、说明:列出表里的所有的 2gR_1*|  
select name from syscolumns where id=object_id('TableName') +-YuBVHL  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 T&MS_E&;  
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 M*@ aA XM  
显示结果: QDT{Xg* I  
type vender pcs =?*"V-l  
电脑 A 1 a;owG/\p  
电脑 A 1 +P)[|y +e  
光盘 B 2 !#gE'(J;c  
光盘 A 2 -%gd')@SfD  
手机 B 3 nC{rs+P  
手机 C 3 /z?7ic0  
23、说明:初始化表table1 M"l rwun^  
TRUNCATE TABLE table1 oUKbzr/C  
24、说明:选择从10到15的记录 0?;Hmq3  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc [T#a1!  
xI\s9_"Qy  
Y^m=_*1g5  
n*4X/K  
三、技巧 ;)pV[3[  
1、1=1,1=2的使用,在SQL语句组合时用的较多 4bi\$   
"where 1=1" 是表示选择全部  "where 1=2"全部不选, } 9s  
如: |laKntv2  
if @strWhere !='' MkGq%AE`Y  
begin V42*4hskL  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere : It W|  
end k3.p@8@:  
else T9<nD"=:  
begin WHLKf  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' gN'i+mQcu  
end v.v%k2;  
我们可以直接写成 E0A|+P '?  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere SFgIY]  
2、收缩数据库 bYB}A :  
--重建索引 &j@J<*k  
DBCC REINDEX 5Zm_^IS  
DBCC INDEXDEFRAG l@J|p#0q  
--收缩数据和日志 RGuHXf  
DBCC SHRINKDB j3-6WUO  
DBCC SHRINKFILE >^GCSPe  
3、压缩数据库 g E+OQWu  
dbcc shrinkdatabase(dbname) Z3~*R7G8>  
4、转移数据库给新用户以已存在用户权限 D2 cIVx3:(  
exec sp_change_users_login 'update_one','newname','oldname' q>4i0p8^  
go e+ w  
5、检查备份集 9v,8OK)  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' m`q> _*  
6、修复数据库 \.|A,G=  
ALTER DATABASE [dvbbs] SET SINGLE_USER  CF92AY  
GO ^&/&I9z  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK .eXA.9 |jm  
GO 'J0s%m|j  
ALTER DATABASE [dvbbs] SET MULTI_USER Ngc+<  
GO w$:)wyR-  
7、日志清除 =usDI<3r  
SET NOCOUNT ON _`[6jhNa!  
DECLARE @LogicalFileName sysname, #$B,8LFz,$  
        @MaxMinutes INT, yzR=:0J  
        @NewSize INT U`_vF~el~  
USE    tablename            -- 要操作的数据库名 )&!@O$RS8(  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 E!l1a5qB  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. 5GL+j%7  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) G-?9;w'@  
-- Setup / initialize b<78K5'  
DECLARE @OriginalSize int gO!h<1!  
SELECT @OriginalSize = size je3n'^m  
  FROM sysfiles <7] Y\{+  
  WHERE name = @LogicalFileName ioCkPj  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + R+hS;F nh%  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + q$'&RG  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' oxXW`C<  
  FROM sysfiles 0BE^qe  
  WHERE name = @LogicalFileName :I(gz~u6  
CREATE TABLE DummyTrans )nxIxr0d-  
  (DummyColumn char (8000) not null) iB`]Z@ZC  
DECLARE @Counter  INT, -"K:ve(K  
        @StartTime DATETIME, U)]natB  
        @TruncLog  VARCHAR(255) A@AGu#W  
SELECT  @StartTime = GETDATE(), <X&:tZ #/  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 7lPk~0  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) u3brb'Y+  
EXEC (@TruncLog) #e269FwN  
-- Wrap the log if necessary. 0-f-  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired E'6P>6l5  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  lS-i9U/,>  
      AND (@OriginalSize * 8 /1024) > @NewSize  geSo#mV  
  BEGIN -- Outer loop. 1)Bi>X  
    SELECT @Counter = 0 :.df(1(RL  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) e-)1K  
      BEGIN -- update tSa%ZkS  
        INSERT DummyTrans VALUES ('Fill Log')  K# < Wt5  
        DELETE DummyTrans H,` XCG  
        SELECT @Counter = @Counter + 1 `~TGVa`D  
      END  tah%jRfT&  
    EXEC (@TruncLog)  =Fl4tY#X  
  END  h l'k_<a*  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + gdNp2b  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 7/!C  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' K): sq{  
  FROM sysfiles :#jv4N  
  WHERE name = @LogicalFileName .cog9H'  
DROP TABLE DummyTrans 'p]qN;`'O$  
SET NOCOUNT OFF 0\*<k`dY  
8、说明:更改某个表 %$ ?Q%  
exec sp_changeobjectowner 'tablename','dbo' d's`~HOU2  
9、存储更改全部表 *3Z#r  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch xTm&`Xo  
@OldOwner as NVARCHAR(128), u5M{s;{11r  
@NewOwner as NVARCHAR(128) ofCP>Z-  
AS N6%q%7F.:  
DECLARE @Name  as NVARCHAR(128) 4 jro4B`  
DECLARE @Owner  as NVARCHAR(128) )E2Lf ]  
DECLARE @OwnerName  as NVARCHAR(128) &r!>2$B\  
DECLARE curObject CURSOR FOR /*HSAjv  
select 'Name'  = name, H9!*DA<W  
  'Owner'  = user_name(uid) Te#[+B?  
from sysobjects p:%E>K1<  
where user_name(uid)=@OldOwner ^ ?9 ~R"  
order by name ! NE q|Y  
OPEN  curObject @$G K<jl  
FETCH NEXT FROM curObject INTO @Name, @Owner imQNfNm  
WHILE(@@FETCH_STATUS=0) 2Jv4l$$;*  
BEGIN    SX;IUvVE5  
if @Owner=@OldOwner y-k-E/V}  
begin vb!KuI!:p  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) E#p6A5  
  exec sp_changeobjectowner @OwnerName, @NewOwner o!S_j^p[C  
end _nq n|  
-- select @name,@NewOwner,@OldOwner }cmL{S  
FETCH NEXT FROM curObject INTO @Name, @Owner ,DLNI0uV  
END ')RK(I  
close curObject YO+d+5  
deallocate curObject q[K)bg{HB  
GO 6d8  
10、SQL SERVER中直接循环写入数据 SUhP e+  
declare @i int ,Z"sh*  
set @i=1 /VkJ+%}+j  
while @i<30 A79SAheX#  
begin 6V/mR~F1r  
  insert into test (userid) values(@i) c[q3O**  
  set @i=@i+1 WLH2B1_):  
end R8*4E0\br  
小记存储过程中经常用到的本周,本月,本年函数 Qz;" b!  
Dateadd(wk,datediff(wk,0,getdate()),-1) &LYZQ?|  
Dateadd(wk,datediff(wk,0,getdate()),6) t[~i})yS  
Dateadd(mm,datediff(mm,0,getdate()),0) / KM+PeO  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) !<ucwWY,  
Dateadd(yy,datediff(yy,0,getdate()),0) >=Veu; A  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 0IuU4h5Fr  
上面的SQL代码只是一个时间段 ly+7klQ;.  
Dateadd(wk,datediff(wk,0,getdate()),-1) B4=gMVp1  
Dateadd(wk,datediff(wk,0,getdate()),6) m%puD 9  
就是表示本周时间段. 6m&I_icM  
下面的SQL的条件部分,就是查询时间段在本周范围内的: J( 60eTwQ  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) (fS4qz:&l  
而在存储过程中 v<4zcMv  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) tnntHQ&b  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) 4V5*6O9(u  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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