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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 9gFC]UVWh  
`Z{7Ut^)  
U $+rlw}  
一、基础 nI 6`/  
1、说明:创建数据库 F Hv|6zUX  
CREATE DATABASE database-name Abj`0\  
2、说明:删除数据库 4 0Du*5M  
drop database dbname t?/#:J*_7  
3、说明:备份sql server uy B ?-Y+  
--- 创建 备份数据的 device tUt l>>6Iu  
USE master VQX#P<  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' kcQ |Zg  
--- 开始 备份 tnp]wZ  
BACKUP DATABASE pubs TO testBack Jx 'p\*  
4、说明:创建新表 1{DHlyA6g  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) s,0,w--=  
根据已有的表创建新表: FO*Py)/rX  
A:create table tab_new like tab_old (使用旧表创建新表) ?4xTA  
B:create table tab_new as select col1,col2... from tab_old definition only ?bbguwo~F  
5、说明:删除新表 N 4Dyec\  
drop table tabname }lWEbQ)(!  
6、说明:增加一个列 JeiW z1t  
Alter table tabname add column col type gvz&ppcG  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 B&tl6?7h  
7、说明:添加主键: Alter table tabname add primary key(col) Yh4e\]ql~N  
说明:删除主键: Alter table tabname drop primary key(col) )'nGuL-w!i  
8、说明:创建索引:create [unique] index idxname on tabname(col....) S9mj/GpL3  
删除索引:drop index idxname NC0x!tJ#7  
注:索引是不可更改的,想更改必须删除重新建。 TVKuvKH8U  
9、说明:创建视图:create view viewname as select statement XO\P4x :c  
删除视图:drop view viewname @ j' I  
10、说明:几个简单的基本的sql语句 [_${N,1  
选择:select * from table1 where 范围 m%m8002  
插入:insert into table1(field1,field2) values(value1,value2) aN.Phn:  
删除:delete from table1 where 范围 Ww@R ewo  
更新:update table1 set field1=value1 where 范围 U{HJNftdpm  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! #Jg )HU9  
排序:select * from table1 order by field1,field2 [desc] MLaH("aen  
总数:select count as totalcount from table1 <1V!-D4xu  
求和:select sum(field1) as sumvalue from table1 l9#vr  
平均:select avg(field1) as avgvalue from table1 m ?LOd9  
最大:select max(field1) as maxvalue from table1 8{@#N:SY  
最小:select min(field1) as minvalue from table1 'R?;T[s%  
wn5CaP(]8  
><5tnBP|+L  
vxK}f*d  
11、说明:几个高级查询运算词 7+=fD|Cl  
)9J&M6LX  
i9uJ%nd:  
A: UNION 运算符 ,+%$vV .g\  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 9aYCU/3  
B: EXCEPT 运算符 3-srt^>w*  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 BY6QJkI9x  
C: INTERSECT 运算符 6 C;??Y>b  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 <Sb W QbN  
注:使用运算词的几个查询结果行必须是一致的。 |h@'~c  
12、说明:使用外连接 pzHN:9r  
A、left outer join: ~ 8RN  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 4Rp2  
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 td&W>(3d  
B:right outer join: WTZP}p1  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 /c8F]fkZ=  
C:full outer join: Fz~-m#Ts  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 &Nt4dp`qj  
v|"Nx42  
S~/zBFo-  
二、提升 NAlYfbp  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) :dLfM)8}  
法一:select * into b from a where 1<>1 7xz|u\?_2  
法二:select top 0 * into b from a N)WAzH  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 3u3(BY{"\F  
insert into b(a, b, c) select d,e,f from b; `)/G5 fB  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) sTU`@}}  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 '/J}T -,Z  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. t[Xx LG*  
4、说明:子查询(表名1:a 表名2:b) )E'iC  
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) EQET:a:g  
5、说明:显示文章、提交人和最后回复时间 Re0ma%~LP  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b E?m(&O j  
6、说明:外连接查询(表名1:a 表名2:b) Wf +j/RxTi  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ^ duNEu0*  
7、说明:在线视图查询(表名1:a ) .'saUcVg:  
select * from (SELECT a,b,c FROM a) T where t.a > 1; 6V KsX+sd  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 PTXy:>]M  
select * from table1 where time between time1 and time2 _p"nR  
select a,b,c, from table1 where a not between 数值1 and 数值2 K1AI:$H  
9、说明:in 的使用方法 b7^VWX%  
select * from table1 where a [not] in ('值1','值2','值4','值6') 9s8B>(L  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 g@BQ!}_#5  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) mx y>  
11、说明:四表联查问题: SxdH %agM  
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 ..... mFC0f?nr  
12、说明:日程安排提前五分钟提醒 8%nb1CA  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 Y9F!HM-`  
13、说明:一条sql 语句搞定数据库分页 k4FxdX  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 hiZE8?0+~N  
14、说明:前10条记录 ! ~u;CMR  
select top 10 * form table1 where 范围 5*l~7R  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) wu "6Kyu  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) nw){}g  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 u=JI 1  
(select a from tableA ) except (select a from tableB) except (select a from tableC) hqd}L~o:  
17、说明:随机取出10条数据 s-!Bpr16o0  
select top 10 * from tablename order by newid() K+5S7wFDZ  
18、说明:随机选择记录 %]S~PKx  
select newid() q-P$ \":  
19、说明:删除重复记录 wg\*FfQn  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) - |n\  
20、说明:列出数据库里所有的表名 xL,Lb}){%  
select name from sysobjects where type='U' Z6F>SL  
21、说明:列出表里的所有的 :_E q(r  
select name from syscolumns where id=object_id('TableName') St}j^i  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 >DeG//rv  
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 >bWx!M]  
显示结果: ?&W1lYY  
type vender pcs eY^;L_7}p  
电脑 A 1 ~GZ!;An  
电脑 A 1 %T4htZa  
光盘 B 2 Nn5z   
光盘 A 2 {KYbsD  
手机 B 3 }<6xZy  
手机 C 3 >Ng7q?h   
23、说明:初始化表table1 `KBgVhS>  
TRUNCATE TABLE table1 x;ym_UZ6e  
24、说明:选择从10到15的记录 k$1ya7-@  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc t>p!qKrE'J  
*NzHY;e  
teh$W<C  
o7Ms]AblT  
三、技巧 8q*MhH>6I  
1、1=1,1=2的使用,在SQL语句组合时用的较多 $Ay j4|_-  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, mVFz[xI  
如: ug*#rpb  
if @strWhere !='' ENW>bS8 e`  
begin 04>dxw)8  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere SJ$N]<d  
end cz<8Kb/XV  
else 2]W"sT[  
begin c^0Yu Bps[  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' )vVt{g  
end 9i4!^DM_  
我们可以直接写成 ." 9t<<!  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere ]4f;%pE  
2、收缩数据库 JRQ{Q"`)  
--重建索引 Fr/3Qp@S  
DBCC REINDEX 5A(zQ'6  
DBCC INDEXDEFRAG ` QC  
--收缩数据和日志 ~xI1@^ r  
DBCC SHRINKDB OL+dx`Y  
DBCC SHRINKFILE 2D|2/ >[  
3、压缩数据库 M JJ]8:%  
dbcc shrinkdatabase(dbname) ;4XvlcGo  
4、转移数据库给新用户以已存在用户权限 ?Ne@OMc  
exec sp_change_users_login 'update_one','newname','oldname' I L dRN  
go #Hm*<s.  
5、检查备份集 Fr{u=0 X  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' A$*#n8 ,  
6、修复数据库 V/w:^@5+p  
ALTER DATABASE [dvbbs] SET SINGLE_USER Q|40 8EM  
GO )X*?M?~\  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK Fe8JsB-  
GO WmU5YZ(mAq  
ALTER DATABASE [dvbbs] SET MULTI_USER z"/Mva3|  
GO K"~Tk`[0Q  
7、日志清除 c`:hEQs  
SET NOCOUNT ON [^wEKRt&  
DECLARE @LogicalFileName sysname, p<>x qU  
        @MaxMinutes INT, u 6&<Bv  
        @NewSize INT C9l5zb~D  
USE    tablename            -- 要操作的数据库名 jwsl"zL  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 WT(inf[  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. c)H (w  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) /*g9drwaa  
-- Setup / initialize |Z%I3-z_DS  
DECLARE @OriginalSize int Hg8n`a;R  
SELECT @OriginalSize = size d D6I @N)X  
  FROM sysfiles dre@V(\;hQ  
  WHERE name = @LogicalFileName Nxk3uF^  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + RQ?T~ASs  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + >7@kwj-f)  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' aoNTRJ c$  
  FROM sysfiles w)x`zVwO  
  WHERE name = @LogicalFileName  _@es9  
CREATE TABLE DummyTrans Wxjv=#3  
  (DummyColumn char (8000) not null) HI}$Z =C  
DECLARE @Counter  INT, ]RYk Y7>`  
        @StartTime DATETIME,  y!6+jrI  
        @TruncLog  VARCHAR(255) dc#Db~v}k  
SELECT  @StartTime = GETDATE(), f1R&Q  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' vCC}IDd  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) /4 zO  
EXEC (@TruncLog) NOOP_:(7H  
-- Wrap the log if necessary. Q _}i8p '  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 3/ sKRU  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  )9_jr(s  
      AND (@OriginalSize * 8 /1024) > @NewSize  g@WGd(o0)  
  BEGIN -- Outer loop. KX*Hev'K  
    SELECT @Counter = 0 C N9lK29F)  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) KM$5ZbCF:  
      BEGIN -- update o`^GUY}  
        INSERT DummyTrans VALUES ('Fill Log')  .H1 kl)~V  
        DELETE DummyTrans BoZ G^  
        SELECT @Counter = @Counter + 1 u rGk_.f  
      END  |&IS ZFSv  
    EXEC (@TruncLog)  QusEWq)}<  
  END  +3VDapfin  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + /EY ^ui  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + zG z^T  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' z1\G,mJK  
  FROM sysfiles u7|{~D&f  
  WHERE name = @LogicalFileName i4T U}.h8  
DROP TABLE DummyTrans jF}zv  
SET NOCOUNT OFF YJgw%UVJ5m  
8、说明:更改某个表 VdpkE0  
exec sp_changeobjectowner 'tablename','dbo' ?sl 7C gl  
9、存储更改全部表 T!6H5>zA  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch $cO"1mu  
@OldOwner as NVARCHAR(128), !Ju?REH   
@NewOwner as NVARCHAR(128) k*bfq?E a  
AS &s!"pEZWck  
DECLARE @Name  as NVARCHAR(128) # k+Gg w  
DECLARE @Owner  as NVARCHAR(128) f~Dl;f~H_;  
DECLARE @OwnerName  as NVARCHAR(128) riI0k{   
DECLARE curObject CURSOR FOR fShf4G_w\  
select 'Name'  = name, A.9,p  
  'Owner'  = user_name(uid) =YX/]g|9K  
from sysobjects 1E Lzzn  
where user_name(uid)=@OldOwner 8$avPD3jx  
order by name Cna@3)_  
OPEN  curObject S~vbISl  
FETCH NEXT FROM curObject INTO @Name, @Owner ,hK0F3?H>  
WHILE(@@FETCH_STATUS=0) +3e(psdg  
BEGIN    yWIM,2x}  
if @Owner=@OldOwner k/nOz*  
begin zypZ3g{vz  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) c L*D_)?8  
  exec sp_changeobjectowner @OwnerName, @NewOwner ^SCZ  
end =Mq=\T  
-- select @name,@NewOwner,@OldOwner /LhAQpUQT5  
FETCH NEXT FROM curObject INTO @Name, @Owner ]?,47,[<  
END @f0~a  
close curObject BQf}S +  
deallocate curObject Kzxzz6R?  
GO JGIN<J85e  
10、SQL SERVER中直接循环写入数据 \s;]Tg  
declare @i int t~p9iGX<  
set @i=1 u;DF$   
while @i<30 ZZTPAmIr  
begin ~fsAPIQ  
  insert into test (userid) values(@i) 3y>.1  
  set @i=@i+1 .hgc1  
end 1rC8] M.N  
小记存储过程中经常用到的本周,本月,本年函数 oUZwZ_yKW  
Dateadd(wk,datediff(wk,0,getdate()),-1) VS^%PM#:/  
Dateadd(wk,datediff(wk,0,getdate()),6) g'Ft5fQ"o/  
Dateadd(mm,datediff(mm,0,getdate()),0) )8&Q.? T  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) V~5vVY_HG&  
Dateadd(yy,datediff(yy,0,getdate()),0) %Ua*}C   
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) AEK* w4  
上面的SQL代码只是一个时间段 Z!6\KV]  
Dateadd(wk,datediff(wk,0,getdate()),-1) N;D (_:^  
Dateadd(wk,datediff(wk,0,getdate()),6) mT@UQCG  
就是表示本周时间段. <(d ^2-0  
下面的SQL的条件部分,就是查询时间段在本周范围内的: :RHNV  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) }*$-rieg  
而在存储过程中 xN2M| E]  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) %xLziF  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6)  AO;+XP=  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容
认证码:
验证问题:
10+5=?,请输入中文答案:十五