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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Ti>}To}B5  
}$s QmR R  
gZ=$bR  
一、基础 R#s_pW{op  
1、说明:创建数据库  lHE+o;-  
CREATE DATABASE database-name i#PR Tbc  
2、说明:删除数据库 mB%m<Zo\U  
drop database dbname ( geV(zT  
3、说明:备份sql server N]&hw&R{Q  
--- 创建 备份数据的 device ruy?#rk  
USE master nPH\Lra  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' $9Gra#  
--- 开始 备份 <eZrb6a'  
BACKUP DATABASE pubs TO testBack Bf" ZmG9  
4、说明:创建新表 SBY0L.  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) ^!x qOp!  
根据已有的表创建新表: n%!50E6*:  
A:create table tab_new like tab_old (使用旧表创建新表) 1yTw*vH F  
B:create table tab_new as select col1,col2... from tab_old definition only T#HF! GH]  
5、说明:删除新表 .`oKd@I*"  
drop table tabname W!1 B~NH#  
6、说明:增加一个列 Ii>#9>!F  
Alter table tabname add column col type }d@;]cps  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 ::@JL  
7、说明:添加主键: Alter table tabname add primary key(col) J!}R>mR  
说明:删除主键: Alter table tabname drop primary key(col) ajX] ui  
8、说明:创建索引:create [unique] index idxname on tabname(col....) rw?wlBEG%  
删除索引:drop index idxname !04 ^E  
注:索引是不可更改的,想更改必须删除重新建。 }&%&0$%  
9、说明:创建视图:create view viewname as select statement |*L/ m0'L  
删除视图:drop view viewname WN o+%  
10、说明:几个简单的基本的sql语句 &iT^IkA{  
选择:select * from table1 where 范围 kD6Iz$tr  
插入:insert into table1(field1,field2) values(value1,value2) 4v2JrC;  
删除:delete from table1 where 范围 5Hs !s+  
更新:update table1 set field1=value1 where 范围 2FGCf} ,  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! ?i}wm`  
排序:select * from table1 order by field1,field2 [desc] *=77|Dba  
总数:select count as totalcount from table1 s:I 8~Cc  
求和:select sum(field1) as sumvalue from table1 JC}T*h>Ee  
平均:select avg(field1) as avgvalue from table1 6mjD@  
最大:select max(field1) as maxvalue from table1 `0-i>>  
最小:select min(field1) as minvalue from table1 5'_:>0}  
kqGydGh*"  
u3sr"w&  
m`jGBSlw_  
11、说明:几个高级查询运算词 l I2UpfkBP  
l>)+HoD  
FPEab69  
A: UNION 运算符 Ad4-aWH  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 |WW'qg]Uu  
B: EXCEPT 运算符 }{v0}-~@  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 4 &0MB>m  
C: INTERSECT 运算符 ,,-j5Y  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 jI$7vmO  
注:使用运算词的几个查询结果行必须是一致的。 ZL9|/ PY  
12、说明:使用外连接 ,.&D{ $1W  
A、left outer join: o@YEd d  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 r$%,k*X^ k  
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 mOFp!(  
B:right outer join: 2t7=GA+j  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 Ah zV?6e  
C:full outer join: f?"909&  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 fLV@~T|  
NC|VZwQtm  
x_= 3 !)  
二、提升 )7Oj  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) Hq,znRz~`  
法一:select * into b from a where 1<>1 ;9qwB  
法二:select top 0 * into b from a qnO/4\qq  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) 5'EoB^`8N~  
insert into b(a, b, c) select d,e,f from b; jjg&C9w T  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) ,Uy~O(F t  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 Po.izE!C  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. zhU^~4F  
4、说明:子查询(表名1:a 表名2:b) g5 y*-t  
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) o^u}(wZ{  
5、说明:显示文章、提交人和最后回复时间 =E&1e;_xlE  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b (R!.=95@  
6、说明:外连接查询(表名1:a 表名2:b) )F6p+i="  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c C6d#+  
7、说明:在线视图查询(表名1:a ) ?y^ ix+ M  
select * from (SELECT a,b,c FROM a) T where t.a > 1; ZOL#Q+U  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 !KHbsOT?9  
select * from table1 where time between time1 and time2 3GZrVhU?m  
select a,b,c, from table1 where a not between 数值1 and 数值2 M ED_#OS  
9、说明:in 的使用方法 a(x#6  
select * from table1 where a [not] in ('值1','值2','值4','值6') T=fVD8  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 Vtk}>I@%  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) bW zUWLa  
11、说明:四表联查问题: ^k!u  
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 ..... Hlj3z3  
12、说明:日程安排提前五分钟提醒 q&,uJo  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 ; $UB@)7%  
13、说明:一条sql 语句搞定数据库分页 ,k m`-6.2?  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 M\kct7Y  
14、说明:前10条记录 ?q"9ZYX<  
select top 10 * form table1 where 范围 KzB9 mMrO  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) bbWW|PtWwP  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) W}k)5<C4v  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 4*Z>-<W=  
(select a from tableA ) except (select a from tableB) except (select a from tableC) Zy6>i2f4f  
17、说明:随机取出10条数据 >P2QL>P  
select top 10 * from tablename order by newid() &tw{d DD6  
18、说明:随机选择记录 D*8oFJub  
select newid() ;(LC{jY  
19、说明:删除重复记录 lV?OYS|4i  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) &I/C^/F&  
20、说明:列出数据库里所有的表名 i.+#a2   
select name from sysobjects where type='U' >  !WFY  
21、说明:列出表里的所有的 5ma~Pjt8}  
select name from syscolumns where id=object_id('TableName') hy@e(k|S]U  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 > Cx;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 _Tf0L<A'R  
显示结果: 2X=*;r"{J  
type vender pcs 9tB:1n}  
电脑 A 1 'z Qp64]F  
电脑 A 1 iRL|u~bj  
光盘 B 2 q)]S:$?BT  
光盘 A 2 @oFuX.  
手机 B 3 u~27\oj,  
手机 C 3 ~<=wTns!  
23、说明:初始化表table1 8uB6C0,6?  
TRUNCATE TABLE table1 ~93+Oxg  
24、说明:选择从10到15的记录 6Ou[t6  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc M_\)<a(8  
`R:HMO[ow  
tEllkHyef  
6CzN[R}  
三、技巧 k7bfgb {  
1、1=1,1=2的使用,在SQL语句组合时用的较多 3 yM!BTlX  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, "C]_pWk  
如: +Z/aG k;  
if @strWhere !='' $9<P3J 1  
begin y?V#LW[^E  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere {c=H#- A  
end &fwb?Vn4  
else u]t#Vf-$u  
begin y!kM#DC^  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' |z.Ov&d4)(  
end zA&]#mc  
我们可以直接写成 WO{9S%ck  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere h?&S*)1  
2、收缩数据库 $-_@MT~  
--重建索引 S{)'1J_0  
DBCC REINDEX 8MCSU'uQ  
DBCC INDEXDEFRAG OyTp^W`&  
--收缩数据和日志 <{A|Xs  
DBCC SHRINKDB UC?i>HsJrX  
DBCC SHRINKFILE (k>I!Z/&2  
3、压缩数据库 M!] g36h[  
dbcc shrinkdatabase(dbname) I#](mRJ6  
4、转移数据库给新用户以已存在用户权限 gz`P~7-w:  
exec sp_change_users_login 'update_one','newname','oldname' !T26#>mV  
go 1&JB@F9!  
5、检查备份集 yA-UXKT  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' i>AKXJ+  
6、修复数据库 \oAxmvt  
ALTER DATABASE [dvbbs] SET SINGLE_USER =/qj vY  
GO r`d.Wy Zj  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK OeY+Yt0  
GO ?L6ACi`9  
ALTER DATABASE [dvbbs] SET MULTI_USER qeoj  
GO r!O4]j_3  
7、日志清除 ;O * o  
SET NOCOUNT ON GZNfx8zsY+  
DECLARE @LogicalFileName sysname, (khMjFOg  
        @MaxMinutes INT, e{`DvfY21  
        @NewSize INT #6qLu  
USE    tablename            -- 要操作的数据库名 2W=am_\0e.  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 N^By#Z  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. "%{J$o  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) #wZBWTj.  
-- Setup / initialize uHpSE?y/  
DECLARE @OriginalSize int [}=/?(5  
SELECT @OriginalSize = size rTLo6wI  
  FROM sysfiles t[?O*>  
  WHERE name = @LogicalFileName u7ER  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + *6 1G<I  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + agxR V  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' @1G`d53N  
  FROM sysfiles D*o[a#2_  
  WHERE name = @LogicalFileName 8i?h{G IMV  
CREATE TABLE DummyTrans rQD7ZN_ R  
  (DummyColumn char (8000) not null) ,#QLc  
DECLARE @Counter  INT, ~:lN("9OI  
        @StartTime DATETIME, mRC6m K>  
        @TruncLog  VARCHAR(255) \j3XT}  
SELECT  @StartTime = GETDATE(), d"JI4)%  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' <{b#nPc!,#  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) XKT2u!Lx  
EXEC (@TruncLog) 9x9~u8j  
-- Wrap the log if necessary. vd!|k5t[d  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired $Xr9<)?,  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  z9[BQ(9t  
      AND (@OriginalSize * 8 /1024) > @NewSize  4?9cyv4H  
  BEGIN -- Outer loop. z2.ZxL"*  
    SELECT @Counter = 0 dzwto;  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) (.54`[2+L  
      BEGIN -- update zWEt< `1M  
        INSERT DummyTrans VALUES ('Fill Log')  4GTB82V$  
        DELETE DummyTrans f8?c[%br  
        SELECT @Counter = @Counter + 1 \3v}:E+3  
      END  !aub@wH3  
    EXEC (@TruncLog)  >J No2  
  END  4mJ[Wr\y  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + ImVHX~ qHJ  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + )rFcfS+/  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' dTW3mF4=  
  FROM sysfiles q2KWSh5  
  WHERE name = @LogicalFileName $mp'/]  
DROP TABLE DummyTrans t|y`Bl2  
SET NOCOUNT OFF $6p|}<u  
8、说明:更改某个表 8rA?X*|S!  
exec sp_changeobjectowner 'tablename','dbo' &WGG kn  
9、存储更改全部表 M]$_>&"  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch 5W=jQ3 C  
@OldOwner as NVARCHAR(128), nI6[y)j  
@NewOwner as NVARCHAR(128) *ioVLt,:R  
AS 1P+Te,I  
DECLARE @Name  as NVARCHAR(128) i VIpe  
DECLARE @Owner  as NVARCHAR(128) b#[7A  
DECLARE @OwnerName  as NVARCHAR(128) 4Sxt<7[f  
DECLARE curObject CURSOR FOR woCFkO;'O  
select 'Name'  = name, L 2:N@TP  
  'Owner'  = user_name(uid) RTR@p =ck  
from sysobjects 3m9ab"  
where user_name(uid)=@OldOwner )dgo oq  
order by name  zL,B?  
OPEN  curObject oDEvhN T  
FETCH NEXT FROM curObject INTO @Name, @Owner SYsbe 5j  
WHILE(@@FETCH_STATUS=0) !Cv:,q  
BEGIN    N N;'QiE  
if @Owner=@OldOwner ,>Lj>g{~  
begin zgh~P^Z  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) @on\@~Ug  
  exec sp_changeobjectowner @OwnerName, @NewOwner 5*W<6ia  
end XLNR%)l  
-- select @name,@NewOwner,@OldOwner k^Q>  
FETCH NEXT FROM curObject INTO @Name, @Owner R |h(SXa  
END BE]PM nI  
close curObject wkwsBi  
deallocate curObject )+S^{tt  
GO 1SYBq,[])  
10、SQL SERVER中直接循环写入数据 9 L^:N)-  
declare @i int +`)4jx)r/  
set @i=1 >^fkHbgNQ  
while @i<30 eQvdi|6  
begin S=bdue  
  insert into test (userid) values(@i) 8P#jC$<  
  set @i=@i+1 DNN60NX 5Q  
end Q7-d]xJ^  
小记存储过程中经常用到的本周,本月,本年函数 x.OCE`  
Dateadd(wk,datediff(wk,0,getdate()),-1) c/q -WEKL  
Dateadd(wk,datediff(wk,0,getdate()),6) xEfz AJ5&  
Dateadd(mm,datediff(mm,0,getdate()),0) w0FkKJV  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) M >BcYbXf  
Dateadd(yy,datediff(yy,0,getdate()),0) CkJ\v%JAW  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) @3:oo /;  
上面的SQL代码只是一个时间段 A!&hjV`  
Dateadd(wk,datediff(wk,0,getdate()),-1) OAhCW*B  
Dateadd(wk,datediff(wk,0,getdate()),6) /Ky xOb)  
就是表示本周时间段. f*}H4H EO  
下面的SQL的条件部分,就是查询时间段在本周范围内的: jZ8#86/#{  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) 1hQeuG  
而在存储过程中 +bbhm0f  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) i!jR>+  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) *Bgk3(n)  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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