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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 Ez+8B|0P  
V4ybrUWK  
cB6LJ}R  
一、基础 XD%GNZ  
1、说明:创建数据库 ' j\~> a3\  
CREATE DATABASE database-name W.(Q u-AE(  
2、说明:删除数据库 i<M F8 $  
drop database dbname cKB1o0JsYJ  
3、说明:备份sql server xz-?sD/xe  
--- 创建 备份数据的 device =<X4LO)C  
USE master sdBB(  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' J%IKdxa  
--- 开始 备份 Y^QG\6q  
BACKUP DATABASE pubs TO testBack #'5{ ?Cb  
4、说明:创建新表 IOY<'t+  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) {XH3zMk[  
根据已有的表创建新表: O$7r)B6Cs  
A:create table tab_new like tab_old (使用旧表创建新表) {ceY:49  
B:create table tab_new as select col1,col2... from tab_old definition only 6lm<>#_  
5、说明:删除新表 w7~cY=  
drop table tabname `h~-  
6、说明:增加一个列 fwi -   
Alter table tabname add column col type P@0J!  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 m[nrr6 G"  
7、说明:添加主键: Alter table tabname add primary key(col) 32p9(HQ  
说明:删除主键: Alter table tabname drop primary key(col) #s{EIj~YR_  
8、说明:创建索引:create [unique] index idxname on tabname(col....) 9vBW CCf  
删除索引:drop index idxname 1cS*T>`  
注:索引是不可更改的,想更改必须删除重新建。 _2WW0  
9、说明:创建视图:create view viewname as select statement p2hPLq  
删除视图:drop view viewname C6qGCzlG`  
10、说明:几个简单的基本的sql语句 ZyV^d3F@$  
选择:select * from table1 where 范围 6$t+Q~2G!  
插入:insert into table1(field1,field2) values(value1,value2) X2`n&JE  
删除:delete from table1 where 范围 H#3Ma1z  
更新:update table1 set field1=value1 where 范围 %zN~%mJG  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! B D [<>Wm  
排序:select * from table1 order by field1,field2 [desc] 1sq1{|NW~  
总数:select count as totalcount from table1 G_ #MXFWt  
求和:select sum(field1) as sumvalue from table1 A#gy[.Bb  
平均:select avg(field1) as avgvalue from table1 AG|:mQO  
最大:select max(field1) as maxvalue from table1 jxZ_-1  
最小:select min(field1) as minvalue from table1 kR <\iT0j  
G[]h1f!  
2p~G][  
,L; y>::1  
11、说明:几个高级查询运算词 {[bB$~7Eu  
%>1C ($^  
@v/ 8}n  
A: UNION 运算符 VuuF _y;  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 HE-ErEtGB  
B: EXCEPT 运算符 q:m qA$n  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 6%MM)Vj+u  
C: INTERSECT 运算符 \^dYmU  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 $\L=RU!c}  
注:使用运算词的几个查询结果行必须是一致的。 -kb;h F}.  
12、说明:使用外连接 fr&K^je\  
A、left outer join: vofBS   
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 !gi3J @  
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 {(Ba  
B:right outer join: o4,9jk$  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 [x0*x~1B  
C:full outer join: VP^{-mDph  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 ~n%]u! 6  
"YN6o_*]  
o[g]Va*8  
二、提升 AIw~@*T  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 2 ;Q|h$ n  
法一:select * into b from a where 1<>1 7|<-rjz^  
法二:select top 0 * into b from a ?+L7Bd(EF%  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) /a\]Dwj5  
insert into b(a, b, c) select d,e,f from b; E0eZal],  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)  kZ=yb-~  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 nX<yB9bXDg  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. FLQ^J3A,I  
4、说明:子查询(表名1:a 表名2:b) ZFtN~Tg  
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) 2,p= %  
5、说明:显示文章、提交人和最后回复时间 @1V?94T1  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 9Yji34eDZ  
6、说明:外连接查询(表名1:a 表名2:b) alyA#zao|  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c ht*;,[ea  
7、说明:在线视图查询(表名1:a ) 64SRW8AH  
select * from (SELECT a,b,c FROM a) T where t.a > 1; tY60~@YO&  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 21hTun"W  
select * from table1 where time between time1 and time2 j#9n.i %h  
select a,b,c, from table1 where a not between 数值1 and 数值2 X + B=?|M  
9、说明:in 的使用方法 l>pnY%(A  
select * from table1 where a [not] in ('值1','值2','值4','值6') ]C>h_,EZc  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 G$jw#a[L  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) UbP$WIrq  
11、说明:四表联查问题: sbV_h;<  
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 ..... H4BuxM_r  
12、说明:日程安排提前五分钟提醒 C`\yc_b9Pf  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 "S psSQ  
13、说明:一条sql 语句搞定数据库分页 }KV)F,`  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 @XH@i+ {B  
14、说明:前10条记录 YGZa##i  
select top 10 * form table1 where 范围 #3YYE5cB  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) XZxzw*Y1J  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) F#|mN0op  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 FeJKXYbk<  
(select a from tableA ) except (select a from tableB) except (select a from tableC) )NlxW5  
17、说明:随机取出10条数据 ;>B06v  
select top 10 * from tablename order by newid() 73kL>u  
18、说明:随机选择记录 B;xGTl@8  
select newid() iU a `<  
19、说明:删除重复记录 [;hCwj#  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) W1p5F\ wt  
20、说明:列出数据库里所有的表名 \x+"1  
select name from sysobjects where type='U' b0R{cj=<[  
21、说明:列出表里的所有的 ]eIV'lP,j/  
select name from syscolumns where id=object_id('TableName') ,N`D{H"F  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 z7um9g  
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 -[.A6W  
显示结果: b#E!wMClS  
type vender pcs f3HleA&&  
电脑 A 1 k;?E,!{  
电脑 A 1 #~`]eM5`J  
光盘 B 2 N3rQ]HZiP  
光盘 A 2 sn?YD'>k  
手机 B 3 wQ/Z:  
手机 C 3 .>{I S4  
23、说明:初始化表table1 XbKNH>  
TRUNCATE TABLE table1 6b9 &V`  
24、说明:选择从10到15的记录 *q |3QHZ  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc mY !LGN  
~7Jj\@68  
_4#&!b6  
;f)o_:(JJ  
三、技巧 K5SP8<.  
1、1=1,1=2的使用,在SQL语句组合时用的较多 JW-!m8  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, JypXQC}~  
如: }g|)+V\A  
if @strWhere !='' #Bgq]6G2  
begin |=W=H6h*  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere z^=e3~-J  
end 1xE*quhrh  
else K]Onb{QY  
begin J,=: ] t  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' zm('\KvT  
end EU"J'?  
我们可以直接写成 I~.d/!>Z  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere K:g:GEDgf  
2、收缩数据库 -L%2*`-L$  
--重建索引 KGoHn6jM  
DBCC REINDEX @Xb>GPVe#L  
DBCC INDEXDEFRAG ie%_-  
--收缩数据和日志 X0"f>.Lg  
DBCC SHRINKDB |U4t 8  
DBCC SHRINKFILE x3++JG  
3、压缩数据库 wH Z!t,g  
dbcc shrinkdatabase(dbname) a`pY&xq::  
4、转移数据库给新用户以已存在用户权限 ]bnxOk  
exec sp_change_users_login 'update_one','newname','oldname' xYu~}kMu  
go L)nVNY@Mc  
5、检查备份集 _ z4rx  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' /1li^</|p`  
6、修复数据库 Rg[e~##  
ALTER DATABASE [dvbbs] SET SINGLE_USER {t/!a0\HS  
GO >qF KXzI  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK `4EOy:a  
GO b"FsT  
ALTER DATABASE [dvbbs] SET MULTI_USER y]jh*KD[  
GO pO+1?c43  
7、日志清除 3sZK[Y|ax  
SET NOCOUNT ON Gm2rjpZeq  
DECLARE @LogicalFileName sysname, ?ha}&##  
        @MaxMinutes INT, o mjLQp[%  
        @NewSize INT l.)N  
USE    tablename            -- 要操作的数据库名 n&C9f9S  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 fd,~Yj$R?  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. oNU* q.Q  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) $GO'L2oLwn  
-- Setup / initialize I(<G;ft<}  
DECLARE @OriginalSize int xY'g7<})$  
SELECT @OriginalSize = size _@OYC<  
  FROM sysfiles W"-nzdAJ5  
  WHERE name = @LogicalFileName F>b6fUtR  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + `2fuV]FW  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + f.P( {PN  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'  yY| .  
  FROM sysfiles 8_,ZJ9l ;  
  WHERE name = @LogicalFileName Ksp;bfe  
CREATE TABLE DummyTrans G2^DukK.  
  (DummyColumn char (8000) not null) =5|5j!i=q  
DECLARE @Counter  INT, a,4g`?  
        @StartTime DATETIME, aBI]' D;  
        @TruncLog  VARCHAR(255) .~U9*5d  
SELECT  @StartTime = GETDATE(), m5p~>]}fYF  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' ;?o C=c  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) Td  F<  
EXEC (@TruncLog) 8 KkpXaz  
-- Wrap the log if necessary. )g --=w3  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired Na6z,TW  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  @ubz?5  
      AND (@OriginalSize * 8 /1024) > @NewSize  c~'kW`sNV  
  BEGIN -- Outer loop. J9 =gv0  
    SELECT @Counter = 0 % *G)*n  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) (>K$gAQH  
      BEGIN -- update 9>#|~P&FE  
        INSERT DummyTrans VALUES ('Fill Log')  /)I9+s#q9o  
        DELETE DummyTrans X2uX+}h*tA  
        SELECT @Counter = @Counter + 1 }gW}Vr <  
      END  V</T$V$  
    EXEC (@TruncLog)  JC"K{ V{  
  END  5 DB>zou   
SELECT 'Final Size of ' + db_name() + ' LOG is ' + aHC;p=RQ\A  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 5gD)2Q6  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' .dCP8|  
  FROM sysfiles &%X Jf~IQ  
  WHERE name = @LogicalFileName [bv@qBL  
DROP TABLE DummyTrans kkBU<L2  
SET NOCOUNT OFF H040-Q;S'  
8、说明:更改某个表  ^qqHq  
exec sp_changeobjectowner 'tablename','dbo' {0e{!v  
9、存储更改全部表 AfN   
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch n ;5?^Un%  
@OldOwner as NVARCHAR(128), rhTk}2@h  
@NewOwner as NVARCHAR(128) ~5uNw*H  
AS Q) BoWd  
DECLARE @Name  as NVARCHAR(128) ]&U|d  
DECLARE @Owner  as NVARCHAR(128) mzB#O;3=  
DECLARE @OwnerName  as NVARCHAR(128) 9yo[T(8  
DECLARE curObject CURSOR FOR K=x1m M+RK  
select 'Name'  = name, ,R}KcZG)  
  'Owner'  = user_name(uid) yl<$yd0Zdu  
from sysobjects a{HgIQg_>R  
where user_name(uid)=@OldOwner FRt/{(jro  
order by name W 'a~pB1I  
OPEN  curObject XOg(k(&T  
FETCH NEXT FROM curObject INTO @Name, @Owner W4*BR_H&*  
WHILE(@@FETCH_STATUS=0) pE/3-0;}N  
BEGIN    hav?mnVJ  
if @Owner=@OldOwner 7(8  
begin *rz(}(r  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) G=!bM(]R~  
  exec sp_changeobjectowner @OwnerName, @NewOwner |ak C  
end pZ~> l=-  
-- select @name,@NewOwner,@OldOwner J5p!-N`NS  
FETCH NEXT FROM curObject INTO @Name, @Owner _@pf1d$  
END $v<hW A]>  
close curObject >?W;>EUH  
deallocate curObject y2"S\%7$h  
GO uU(G_E ?  
10、SQL SERVER中直接循环写入数据 e1^{  
declare @i int 8^/Ek<Q b|  
set @i=1 k\&IFSp  
while @i<30 2<o[@w  
begin #X@<U <R  
  insert into test (userid) values(@i) QGv:h[b_  
  set @i=@i+1 0tm_}L$g=b  
end 7cmr *y  
小记存储过程中经常用到的本周,本月,本年函数 pL}j ZTo  
Dateadd(wk,datediff(wk,0,getdate()),-1) '"]>`=R  
Dateadd(wk,datediff(wk,0,getdate()),6) M |Q  
Dateadd(mm,datediff(mm,0,getdate()),0) #D LT-G0  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) @z$pPo0fW  
Dateadd(yy,datediff(yy,0,getdate()),0) TQ\wHJ  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) v(@+6#&  
上面的SQL代码只是一个时间段 JIbzh?$aD  
Dateadd(wk,datediff(wk,0,getdate()),-1) z@l!\m-  
Dateadd(wk,datediff(wk,0,getdate()),6) 9CK\tx&  
就是表示本周时间段. .)^3t ~  
下面的SQL的条件部分,就是查询时间段在本周范围内的: f-v ND'@  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) ~?`9i>3W~  
而在存储过程中 `YmI'  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) vi!r8k  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) IJ_ 'w[k  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

您目前还是游客,请 登录注册
如果您在写长篇帖子又不马上发表,建议存为草稿
认证码:
验证问题:
10+5=?,请输入中文答案:十五