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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 %9!, PeRe  
?B"k9+%5ej  
N%k6*FBp~  
一、基础 M(a lc9tn  
1、说明:创建数据库 '*~_!lE5  
CREATE DATABASE database-name )oRF/Xx`g  
2、说明:删除数据库 mu[:b  
drop database dbname M6 W {mek  
3、说明:备份sql server \L"Vx9xT  
--- 创建 备份数据的 device 1'[RrJ$Q  
USE master  0#AS>K5  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' F?wfh7q  
--- 开始 备份 ]{Ytf'bG  
BACKUP DATABASE pubs TO testBack 4Y)rgLFj  
4、说明:创建新表 *,:>EcDr  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) s^@?+<4:  
根据已有的表创建新表: I$Bu6x!  
A:create table tab_new like tab_old (使用旧表创建新表) XvU^DEfW  
B:create table tab_new as select col1,col2... from tab_old definition only .S l{m[nV8  
5、说明:删除新表 `5V=U9zdE  
drop table tabname Z-fQ{&a{  
6、说明:增加一个列 c&{1Z&Y  
Alter table tabname add column col type .K=r.tf~  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 f.%mp$~T  
7、说明:添加主键: Alter table tabname add primary key(col) .>Gnb2  
说明:删除主键: Alter table tabname drop primary key(col) %MQU&H9[  
8、说明:创建索引:create [unique] index idxname on tabname(col....) &o$z[ b  
删除索引:drop index idxname 7S_rN!E1i*  
注:索引是不可更改的,想更改必须删除重新建。 sO,%Ok1  
9、说明:创建视图:create view viewname as select statement ETw7/S${  
删除视图:drop view viewname hGPo{>xR  
10、说明:几个简单的基本的sql语句 mIK-a{?G  
选择:select * from table1 where 范围 TzC'x WO  
插入:insert into table1(field1,field2) values(value1,value2) !\ IgTt,  
删除:delete from table1 where 范围 QUPZe~G>L  
更新:update table1 set field1=value1 where 范围 (aAv7kB&  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! {{G`0i2KV  
排序:select * from table1 order by field1,field2 [desc] Uq:WW1=kh  
总数:select count as totalcount from table1 -bN;nSgb  
求和:select sum(field1) as sumvalue from table1 OT*C7=  
平均:select avg(field1) as avgvalue from table1 Z r}5)ZR.  
最大:select max(field1) as maxvalue from table1 _.9):i2<SF  
最小:select min(field1) as minvalue from table1 x}Y  
|,3>A@  
TSGJ2u5ie%  
 `UC  
11、说明:几个高级查询运算词 #Sxk[[KwH*  
cmLGMlFT  
.l| [e  
A: UNION 运算符 ^PnXnH?  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 r\OunGUP  
B: EXCEPT 运算符 WIe7>wkC  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 gn2*'_V~3  
C: INTERSECT 运算符 ,N[N;Uoj  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 [1-1^JY  
注:使用运算词的几个查询结果行必须是一致的。 w1aev  
12、说明:使用外连接 }e7os0;s  
A、left outer join: o$*aAgS+  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 gx-ib/_f1  
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 ,g.*Mx`-  
B:right outer join: 'pCZx9 *c  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 k$u\\`i]oC  
C:full outer join: DChqcdx~~  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 {XHAQ9'  
wLF;nzv  
3pxZk%  
二、提升 ;_o1{?~  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) y9K U&L2  
法一:select * into b from a where 1<>1 SdOa#U)  
法二:select top 0 * into b from a )\ `AD#  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) zTgY=fuz  
insert into b(a, b, c) select d,e,f from b; j20/Q)=h  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) Lro[ |A  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 +-DF3(  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. OcA_m.  
4、说明:子查询(表名1:a 表名2:b) Q[j'FtP%  
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) e -!6m #0  
5、说明:显示文章、提交人和最后回复时间 scf.> K2  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b (E{>L).~  
6、说明:外连接查询(表名1:a 表名2:b) WH>=*\  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c (Dy6I;S  
7、说明:在线视图查询(表名1:a ) >@b]t,rrK  
select * from (SELECT a,b,c FROM a) T where t.a > 1; R`[jkJrc  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 B]KR*  
select * from table1 where time between time1 and time2 DFgQ1:6[  
select a,b,c, from table1 where a not between 数值1 and 数值2 ?Uq;>  
9、说明:in 的使用方法 z\d{A7  
select * from table1 where a [not] in ('值1','值2','值4','值6') 8 #m,TOp  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 InO;DA\  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) prHM}n{0  
11、说明:四表联查问题: s+tPHftp  
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 ..... Wq5 }SM  
12、说明:日程安排提前五分钟提醒 CIxa" MW  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 1@dB*Jt  
13、说明:一条sql 语句搞定数据库分页 #x?Ku\ts  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 O$qxo &  
14、说明:前10条记录 8t1XZ  
select top 10 * form table1 where 范围 S55h}5Y  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) O'm5k l  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) &z;bX-"E  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 TANv)&,|9  
(select a from tableA ) except (select a from tableB) except (select a from tableC) _>8rTk`/h  
17、说明:随机取出10条数据 _#UiY ffa*  
select top 10 * from tablename order by newid() @ 0'j;")XV  
18、说明:随机选择记录 L;7u0Yg  
select newid() Wc*jTip  
19、说明:删除重复记录 e(=() :4is  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) D6$*#D3U  
20、说明:列出数据库里所有的表名 x%v[(*F#y  
select name from sysobjects where type='U' e3 #0r  
21、说明:列出表里的所有的 4. =jKj9j  
select name from syscolumns where id=object_id('TableName') -> ^Ex`  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 _Gu;=H,~&  
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 w4nU86oZYl  
显示结果: w)rd--9f  
type vender pcs bu?4$O  
电脑 A 1 [!~= m  
电脑 A 1 !*?|*\B^I  
光盘 B 2 `WUyffS/!  
光盘 A 2 &<=?O a  
手机 B 3 wit rC>  
手机 C 3 o7r7HmA@  
23、说明:初始化表table1 %`_Rl>@K=  
TRUNCATE TABLE table1 khc1<BBsT  
24、说明:选择从10到15的记录 n5DS  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc fN_qJm#:$y  
V&h{a8xa$  
E/3i _R  
VMee"'08  
三、技巧 2q NA\-0i>  
1、1=1,1=2的使用,在SQL语句组合时用的较多 [.(,v n?6  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, ^Q\Hy\  
如: Ao U Pq  
if @strWhere !='' l(y,lK=YP1  
begin 68?> #o865  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere +SB>>  
end :R-_EY$k6  
else Q}: $F{  
begin {>3J96  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' :cxA  
end +n%d,Pz  
我们可以直接写成 @DNwzdP  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere Y#5v5  
2、收缩数据库 J2Mq1*Vpq  
--重建索引 {E;oirv&  
DBCC REINDEX ri`;   
DBCC INDEXDEFRAG uq2C|=M-x\  
--收缩数据和日志 64L;np>  
DBCC SHRINKDB f<{f/lU@  
DBCC SHRINKFILE 2oF1do;  
3、压缩数据库 Dr)jB*yK  
dbcc shrinkdatabase(dbname) .OpG2P  
4、转移数据库给新用户以已存在用户权限 .6LlkM6[g  
exec sp_change_users_login 'update_one','newname','oldname' _-T^YeQ/  
go bzXeG;c<7  
5、检查备份集 `h'7X(  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' ~>#?.f  
6、修复数据库 {pc  (b  
ALTER DATABASE [dvbbs] SET SINGLE_USER x[y}{T  
GO #Dea$  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK p9E/#U8A_  
GO wVq9t|V  
ALTER DATABASE [dvbbs] SET MULTI_USER 8 :;]tt  
GO ;nx.:f  
7、日志清除 bt};Pn{3  
SET NOCOUNT ON SsEpuEn  
DECLARE @LogicalFileName sysname, JvsL]yRT  
        @MaxMinutes INT, }BUm}.-{u,  
        @NewSize INT RW<10:  
USE    tablename            -- 要操作的数据库名 4?fpk9c{2  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 O I0N(V  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. 'T|EwrS j  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) !Ln 'Mi_B  
-- Setup / initialize hD[r6c  
DECLARE @OriginalSize int AHo}K\O?r  
SELECT @OriginalSize = size (;;.[4,y  
  FROM sysfiles zsLMROo3  
  WHERE name = @LogicalFileName 9X&=?+f  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + kWacc&*|  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + H _JE)a:+  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' !' 0PM[  
  FROM sysfiles [C/{ru&E  
  WHERE name = @LogicalFileName &ty-aB=F  
CREATE TABLE DummyTrans &Hyy .a  
  (DummyColumn char (8000) not null) qj/Zk [  
DECLARE @Counter  INT, WH"'Ju5}  
        @StartTime DATETIME, {<$tEj:  
        @TruncLog  VARCHAR(255) FUXJy{n6"2  
SELECT  @StartTime = GETDATE(), 01&@8z'E  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 2acT w#  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) h;#046-7  
EXEC (@TruncLog) ;.Ld6JRunw  
-- Wrap the log if necessary. I4|"Ztw  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired C23p1%#1  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  Vh1y]#w  
      AND (@OriginalSize * 8 /1024) > @NewSize  C}|.z  
  BEGIN -- Outer loop. %{7*o5`  
    SELECT @Counter = 0 P3IBi_YyG1  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) kl[(!"p  
      BEGIN -- update | TG6-e_  
        INSERT DummyTrans VALUES ('Fill Log')  F!phTu  
        DELETE DummyTrans j sD]v)LB  
        SELECT @Counter = @Counter + 1 C=(Q0-+L|  
      END  (?g+.]Dt,  
    EXEC (@TruncLog)  4x<H=CJC  
  END  teI?.M9r  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + xC9{hXg!  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + lU%oU&P/"S  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' TFm[sO0RZ  
  FROM sysfiles =1k%T{>  
  WHERE name = @LogicalFileName [y}h   
DROP TABLE DummyTrans j{'_sI{{  
SET NOCOUNT OFF JS/ChoU  
8、说明:更改某个表 KxD/{0F  
exec sp_changeobjectowner 'tablename','dbo' EP"Z58&$R  
9、存储更改全部表 op/_ :#&'  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch Uf|uFGb  
@OldOwner as NVARCHAR(128), )o~/yB7  
@NewOwner as NVARCHAR(128) $f _C~O  
AS 9XYm8g'X  
DECLARE @Name  as NVARCHAR(128) ce#Iu#qT  
DECLARE @Owner  as NVARCHAR(128) xAl8e  
DECLARE @OwnerName  as NVARCHAR(128) 4x&Dz0[[S  
DECLARE curObject CURSOR FOR <;yS&8  
select 'Name'  = name, QVJpX;u  
  'Owner'  = user_name(uid) Q"D5D rj  
from sysobjects '&hd^9]Lo  
where user_name(uid)=@OldOwner d"IZt;s/,  
order by name Phk3Jv  
OPEN  curObject 2 S~(P  
FETCH NEXT FROM curObject INTO @Name, @Owner `d^Q!QxE  
WHILE(@@FETCH_STATUS=0) |5%T)  
BEGIN    by0K:*C  
if @Owner=@OldOwner x`FTy&g  
begin + kT ]qH  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) pdR\Ne0P*  
  exec sp_changeobjectowner @OwnerName, @NewOwner @87Y/_l  
end W!R0:-  
-- select @name,@NewOwner,@OldOwner :<bhQY  
FETCH NEXT FROM curObject INTO @Name, @Owner |O6/p7+.  
END M)!"R [V  
close curObject $./aK J1B  
deallocate curObject 7G^Q2w  
GO *r[V[9+y-D  
10、SQL SERVER中直接循环写入数据 kX+9U"` C  
declare @i int :*&c'  
set @i=1 d/jP2uu A  
while @i<30 `A%WCd60Tc  
begin tc/  
  insert into test (userid) values(@i) =Gu&0f  
  set @i=@i+1 u8.Tu7~  
end .)$MZyo  
小记存储过程中经常用到的本周,本月,本年函数 z/+{QBen8  
Dateadd(wk,datediff(wk,0,getdate()),-1) EPH n"YK  
Dateadd(wk,datediff(wk,0,getdate()),6) T*SLM"x  
Dateadd(mm,datediff(mm,0,getdate()),0) 54Rp0o tv  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) |&{S ~^$  
Dateadd(yy,datediff(yy,0,getdate()),0) AX$r,KmE  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) q?Csm\Y  
上面的SQL代码只是一个时间段 fz`)CWo:  
Dateadd(wk,datediff(wk,0,getdate()),-1) 4ryG_p52l  
Dateadd(wk,datediff(wk,0,getdate()),6) 1KrJS(.  
就是表示本周时间段. 8#lq:  
下面的SQL的条件部分,就是查询时间段在本周范围内的: ~tt\^:\3~S  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ` 6*]cn#(  
而在存储过程中 lH`TF_  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) $l"%o9ICG  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) =?0v,;F9|  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
欢迎提供真实交流,考虑发帖者的感受
认证码:
验证问题:
3+5=?,请输入中文答案:八 正确答案:八