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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 0uf'6<fR  
gDmwJr  
Nm 0kMq|h  
一、基础 zgdOugmmt_  
1、说明:创建数据库 {Y%X  
CREATE DATABASE database-name Z{|U!tn  
2、说明:删除数据库 XU}|Ud562  
drop database dbname `Xbk2KD p  
3、说明:备份sql server $:YJ<HvG<  
--- 创建 备份数据的 device y'9 bs  
USE master & m'ttUG?  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' ?d -$lI  
--- 开始 备份 3xJ_%AD\'  
BACKUP DATABASE pubs TO testBack ~\ 9bh6%R  
4、说明:创建新表 S&C  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) l&z)Q/>?pZ  
根据已有的表创建新表: gGiLw5o,  
A:create table tab_new like tab_old (使用旧表创建新表) r# }`{C;+5  
B:create table tab_new as select col1,col2... from tab_old definition only 9\|n2$H:  
5、说明:删除新表 -F+dRzxH  
drop table tabname 2{!^"iW  
6、说明:增加一个列 4gTD HQP  
Alter table tabname add column col type }- Jw"|^W  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 DJtKLG0  
7、说明:添加主键: Alter table tabname add primary key(col) ;(kU:b|j  
说明:删除主键: Alter table tabname drop primary key(col) QDRgVP  
8、说明:创建索引:create [unique] index idxname on tabname(col....) ;plzJ6>  
删除索引:drop index idxname I.<>6ISI@  
注:索引是不可更改的,想更改必须删除重新建。 39W6"^q"o  
9、说明:创建视图:create view viewname as select statement 6E!CxXUX  
删除视图:drop view viewname >?$+hZz<  
10、说明:几个简单的基本的sql语句 0nF>E@j^[  
选择:select * from table1 where 范围 mxYsP6&  
插入:insert into table1(field1,field2) values(value1,value2) O^D$ ~ ]  
删除:delete from table1 where 范围 7DU"QeLeb  
更新:update table1 set field1=value1 where 范围 3zO'=gwJ  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 0aMw  
排序:select * from table1 order by field1,field2 [desc] / ;%[:x  
总数:select count as totalcount from table1 # !?5^O  
求和:select sum(field1) as sumvalue from table1 [8k7-}[  
平均:select avg(field1) as avgvalue from table1 B}.G(-u?7  
最大:select max(field1) as maxvalue from table1 rmCrP(  
最小:select min(field1) as minvalue from table1 f3 lKdXnP  
;P-xKRU!Xx  
^oFg5  
Kf XE=v{t  
11、说明:几个高级查询运算词 (9N75uCa  
3 ;F=EMz{  
sLV bFN`  
A: UNION 运算符 ^AWM/aY  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 GdqT4a\S  
B: EXCEPT 运算符 oEHUb?(p  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 NXv u}&H  
C: INTERSECT 运算符 \ORNOX:  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 $vS`w4Y  
注:使用运算词的几个查询结果行必须是一致的。 3N?WpA768/  
12、说明:使用外连接 FTtGiGd|Zy  
A、left outer join: *g^U=t  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 .)W'{2J-  
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 lc%2Pi[X  
B:right outer join: 1*eWo~G  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 _MZqH8  
C:full outer join: Xj;nh?\u  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 T4`.rnzyRb  
mAk@Q|u  
.1u"16_  
二、提升 <;d?E%`  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) &Bbs\ ;  
法一:select * into b from a where 1<>1 a G^kL  
法二:select top 0 * into b from a 54kd>)|"ag  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) S6 F28 d[j  
insert into b(a, b, c) select d,e,f from b; g?ID}E ~<  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) VJMn5v[V  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 L;=<d  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. Gw6*0& 3')  
4、说明:子查询(表名1:a 表名2:b) [X.sCl|  
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) NZ `( d  
5、说明:显示文章、提交人和最后回复时间 d%Zt]1$  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 7d?'~}j  
6、说明:外连接查询(表名1:a 表名2:b) #/  1  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 5taYm'  
7、说明:在线视图查询(表名1:a ) pHlw&8(f"  
select * from (SELECT a,b,c FROM a) T where t.a > 1; 2G8f4vsC[  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 o$>A;<  
select * from table1 where time between time1 and time2 " 1YARGu  
select a,b,c, from table1 where a not between 数值1 and 数值2 ~S)o ('  
9、说明:in 的使用方法 B*A{@)_  
select * from table1 where a [not] in ('值1','值2','值4','值6') 0+b1R}!2  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 C8%Io l  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) 83UIH0(  
11、说明:四表联查问题: 6R1){,8  
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 ..... C6=7zYhR  
12、说明:日程安排提前五分钟提醒 F8km8lPQl  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 X8Px  
13、说明:一条sql 语句搞定数据库分页 =& ~*r  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 i~}[/^  
14、说明:前10条记录 qG=9zp4y?Y  
select top 10 * form table1 where 范围 h Ns<Ae  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) mT;1KE{J{  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) T_:"~ ]  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表  KTd,^h  
(select a from tableA ) except (select a from tableB) except (select a from tableC) yZbO{PMr  
17、说明:随机取出10条数据 bZk7)b;1o  
select top 10 * from tablename order by newid() RSG\3(  
18、说明:随机选择记录 |s :b9sfA  
select newid() dOArXp`s  
19、说明:删除重复记录 +1Oi-$ 2-  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ?<\ K!dA  
20、说明:列出数据库里所有的表名 ~p{.4n2:  
select name from sysobjects where type='U' Q_'3}:4  
21、说明:列出表里的所有的 zFh JLH*C  
select name from syscolumns where id=object_id('TableName') lL~T@+J~  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 0t<]Uf  
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 {w.rcObIw+  
显示结果: |D u.aN  
type vender pcs ~WK>+T,%  
电脑 A 1 "q4c[dna  
电脑 A 1 r#wMd9])  
光盘 B 2 ? &ew$%  
光盘 A 2 5_b`QO  
手机 B 3 s.]7c CY  
手机 C 3 }!b9L]  
23、说明:初始化表table1 Z[ }0K3,5  
TRUNCATE TABLE table1 Ob2H7 !  
24、说明:选择从10到15的记录 @a) x^d  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc pPm[<^\#S  
E_]L8UC;m  
/w{DyHT  
L)JpMf0  
三、技巧 .w^M?}dx  
1、1=1,1=2的使用,在SQL语句组合时用的较多 >}h/$bU  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, FLJdnL  
如: k6-Q3W[+a  
if @strWhere !='' %iY-}uhO  
begin Yw<K!'C  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere pc<")9U%/  
end WK]SHiHD  
else zr%lBHuW  
begin #q40  >)]  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' ?"\`u;  
end v bzeabm  
我们可以直接写成 ?J,hv'L]  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere &yv%"BPV  
2、收缩数据库 -XIjol(  
--重建索引 Ia$&SS)K  
DBCC REINDEX g4 _DEBh  
DBCC INDEXDEFRAG I&qT3/SVI  
--收缩数据和日志 Ce}wgKzr  
DBCC SHRINKDB oqHI`Tu  
DBCC SHRINKFILE 6*Jd8Bva\o  
3、压缩数据库 >l{<p(  
dbcc shrinkdatabase(dbname) h|"98PI  
4、转移数据库给新用户以已存在用户权限 cAIMt]_  
exec sp_change_users_login 'update_one','newname','oldname' ZurQr}  
go gbM#jhQ  
5、检查备份集 }OgzSnR  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' IF%^H K@  
6、修复数据库 7(lR$,bE;=  
ALTER DATABASE [dvbbs] SET SINGLE_USER *; . l/  
GO LF?83P,UJ#  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK Zso&.IATng  
GO s2L|J[Y"s  
ALTER DATABASE [dvbbs] SET MULTI_USER 'h_PJ%  
GO !1K<iz_8  
7、日志清除 VYI%U'9Q  
SET NOCOUNT ON t<sg8U.  
DECLARE @LogicalFileName sysname, $A,fO~  
        @MaxMinutes INT, DbFTNoVR  
        @NewSize INT Z=n# XJO15  
USE    tablename            -- 要操作的数据库名 8=OK8UaU  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 &Al9%W  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. pUki!TA  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) JS% &ipm  
-- Setup / initialize /Za'L#=R  
DECLARE @OriginalSize int ww82)m8  
SELECT @OriginalSize = size t=J\zyX!  
  FROM sysfiles 2KMLpO&De  
  WHERE name = @LogicalFileName |5S/h{gq  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + =XsdR?C  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + m{Jo'*%8f  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' y^_ 'g2H  
  FROM sysfiles ,$@nbS{Q]  
  WHERE name = @LogicalFileName od!"?F  
CREATE TABLE DummyTrans |\"vHt?@G  
  (DummyColumn char (8000) not null) _;",7bT80  
DECLARE @Counter  INT,  `W< 7.  
        @StartTime DATETIME, &-W5 T?Sl  
        @TruncLog  VARCHAR(255) +,<\LIP  
SELECT  @StartTime = GETDATE(), w~@.&  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 3/mVdU?U  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) QPjmIO  
EXEC (@TruncLog) :Jwc'y-]  
-- Wrap the log if necessary. ]YYjXg}%  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired (-Rh%ZHH  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  ^^QW<  
      AND (@OriginalSize * 8 /1024) > @NewSize  N#'+p5|>  
  BEGIN -- Outer loop. /-JBz U$  
    SELECT @Counter = 0 1$oVcDLl  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) IE!fNuR4  
      BEGIN -- update lob{{AB,!  
        INSERT DummyTrans VALUES ('Fill Log')  ).@8+}`  
        DELETE DummyTrans evryk,x  
        SELECT @Counter = @Counter + 1 q 1a}o%  
      END  #<|5<U  
    EXEC (@TruncLog)  I`w1IIY?m  
  END  !4d6wp"  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + J;4x-R$W  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + PI~LbDE  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' }yCJ#}  
  FROM sysfiles zvB!=  
  WHERE name = @LogicalFileName g=.~_&O  
DROP TABLE DummyTrans %:oyHlz%  
SET NOCOUNT OFF c0jdZ#H  
8、说明:更改某个表 [b-27\b  
exec sp_changeobjectowner 'tablename','dbo' peqoLeJI  
9、存储更改全部表 G4->7n N  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch {?m;DY v  
@OldOwner as NVARCHAR(128), T">-%-t  
@NewOwner as NVARCHAR(128) 2T/C!^iJ)  
AS x \B!0"~  
DECLARE @Name  as NVARCHAR(128) ?F'gh4  
DECLARE @Owner  as NVARCHAR(128) y]Q G;  
DECLARE @OwnerName  as NVARCHAR(128) hWpn~q  
DECLARE curObject CURSOR FOR '(A)^K>+  
select 'Name'  = name, &\8.y2=9p  
  'Owner'  = user_name(uid) *m:h0[[J  
from sysobjects nB2AmS  
where user_name(uid)=@OldOwner c\~H_ ~F  
order by name bA\TuB  
OPEN  curObject Q/r0p>  
FETCH NEXT FROM curObject INTO @Name, @Owner }ny ,Nl  
WHILE(@@FETCH_STATUS=0) e\ i K  
BEGIN    5g  ,u\`  
if @Owner=@OldOwner  {n}6  
begin J,;[n*s  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) ^Cb7R/R3  
  exec sp_changeobjectowner @OwnerName, @NewOwner %0T/>:1[E  
end \7z&iGe!  
-- select @name,@NewOwner,@OldOwner Zy^mSI4i  
FETCH NEXT FROM curObject INTO @Name, @Owner *A}QBZ  
END xExy?5H7  
close curObject q+2yp&zF  
deallocate curObject NfcY30}:  
GO 7><ne|%  
10、SQL SERVER中直接循环写入数据  b<v\  
declare @i int ) ?rJKr[`  
set @i=1 Ao)hb4ex  
while @i<30 1L1_x'tT%  
begin =$601r  
  insert into test (userid) values(@i) p%e! &:!  
  set @i=@i+1 RP'`\| |*  
end u%?u`n2'  
小记存储过程中经常用到的本周,本月,本年函数 e"(l  
Dateadd(wk,datediff(wk,0,getdate()),-1) 8;9GM^L  
Dateadd(wk,datediff(wk,0,getdate()),6) n's3!HQY[  
Dateadd(mm,datediff(mm,0,getdate()),0) bsVms,&  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) = aSHb[hO  
Dateadd(yy,datediff(yy,0,getdate()),0) /!ZeMY:x  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 6dTq&GZ\  
上面的SQL代码只是一个时间段 dq~p]h~,H  
Dateadd(wk,datediff(wk,0,getdate()),-1) AH`D&V  
Dateadd(wk,datediff(wk,0,getdate()),6) q:sDNj)R\  
就是表示本周时间段. 6W$ #`N>  
下面的SQL的条件部分,就是查询时间段在本周范围内的: `84pql,  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) -'+|r]  
而在存储过程中 eCdx(4(\a  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) QxvxeK!Y  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) )k0e}  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
批量上传需要先选择文件,再选择上传
认证码:
验证问题:
3+5=?,请输入中文答案:八 正确答案:八