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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 fP 1V1ao  
1{6BU!  
e^v5ai  
一、基础 nW)-bAV<  
1、说明:创建数据库 2P\k;T(  
CREATE DATABASE database-name %=p:\+`VI  
2、说明:删除数据库 wyzx9`5~d  
drop database dbname tG8)!  
3、说明:备份sql server UI:YzR  
--- 创建 备份数据的 device ~ZrSoVP=  
USE master gut[q  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' >?Y)evW  
--- 开始 备份 x"g-okLN  
BACKUP DATABASE pubs TO testBack -NzO,?  
4、说明:创建新表 vjo@aY.x  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) nP]tc  
根据已有的表创建新表: j@chSk"K  
A:create table tab_new like tab_old (使用旧表创建新表) '8%pEl^  
B:create table tab_new as select col1,col2... from tab_old definition only 2!-?  
5、说明:删除新表 G.j  R  
drop table tabname @|I:A  
6、说明:增加一个列 _(J;!,  
Alter table tabname add column col type bx!uHL=  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 2T3TD%  
7、说明:添加主键: Alter table tabname add primary key(col) K)-Gv|*t  
说明:删除主键: Alter table tabname drop primary key(col) XOOWrK7O  
8、说明:创建索引:create [unique] index idxname on tabname(col....) |X}H&wBWo  
删除索引:drop index idxname 3k%fY  
注:索引是不可更改的,想更改必须删除重新建。 %62W[Oh5  
9、说明:创建视图:create view viewname as select statement ,/m@<NyK  
删除视图:drop view viewname tKr.{#)  
10、说明:几个简单的基本的sql语句 ^oZz,q  
选择:select * from table1 where 范围 ^Rel-=Z$B  
插入:insert into table1(field1,field2) values(value1,value2) FI.Ae/(U  
删除:delete from table1 where 范围 `/|S.a#g  
更新:update table1 set field1=value1 where 范围 Kmk}Yz  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! 72i ]`   
排序:select * from table1 order by field1,field2 [desc] f82$_1s^  
总数:select count as totalcount from table1 C(w?`]Qs  
求和:select sum(field1) as sumvalue from table1 o0yyP,?yh  
平均:select avg(field1) as avgvalue from table1 <V> [H7  
最大:select max(field1) as maxvalue from table1 ;Avz%2#c`  
最小:select min(field1) as minvalue from table1 {z^6V\O5  
O?p8Gjf  
.2K4<UOAbm  
g]fdsZv  
11、说明:几个高级查询运算词 \BRx dK'  
SH oov  
,H_b@$]n8  
A: UNION 运算符 |iGfX,C|  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 7g* "AEk  
B: EXCEPT 运算符 P M x`P B  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 9EPE.+ns  
C: INTERSECT 运算符 2&e2/KEWR  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 TT85G&#  
注:使用运算词的几个查询结果行必须是一致的。 4t C-msTf  
12、说明:使用外连接 o {W4@:Ib  
A、left outer join: .J/x@  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 a&sVcsX  
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 U@ ;W^Mt  
B:right outer join: <yoCW?#  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 u~uR:E%'C  
C:full outer join: z%4E~u10  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 {Df97n%h;  
 #  
O^Y}fo'  
二、提升 =up!lg^M  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) \d"uR@$3mG  
法一:select * into b from a where 1<>1 T[ ~8u9/  
法二:select top 0 * into b from a 5l(8{,NDt  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) X0QY:?  
insert into b(a, b, c) select d,e,f from b; !!{!T;)l  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) f1Z  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 ;s^br17z~  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. CMcS4X9/}  
4、说明:子查询(表名1:a 表名2:b) IIn sq  
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) ].TAZ-4s  
5、说明:显示文章、提交人和最后回复时间 YK=o[nPmK  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b N7wKaezE  
6、说明:外连接查询(表名1:a 表名2:b) IIZu&iZo\  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c tny^sG/'  
7、说明:在线视图查询(表名1:a ) O_E\(So  
select * from (SELECT a,b,c FROM a) T where t.a > 1; z1K}] z%  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 JU6PBY~C'  
select * from table1 where time between time1 and time2 I[x+7Y0k9  
select a,b,c, from table1 where a not between 数值1 and 数值2 3 L:s5  
9、说明:in 的使用方法 Wl29xY}`{!  
select * from table1 where a [not] in ('值1','值2','值4','值6') Q; V*M  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 6'F4p1VG*I  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) &rc]3! B  
11、说明:四表联查问题: Np.] W(  
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 ..... v^;p]_c~2  
12、说明:日程安排提前五分钟提醒 |}FK;@'I6  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 CnXl 7"  
13、说明:一条sql 语句搞定数据库分页 kPuI'EPK  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 u%s@B1j  
14、说明:前10条记录 z#j)uD  
select top 10 * form table1 where 范围 O(_a6s+m  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) n[E#K`gg'  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) f%g^6[  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 =V[ey  
(select a from tableA ) except (select a from tableB) except (select a from tableC) "3?N*,U_  
17、说明:随机取出10条数据 @W|N1,sp  
select top 10 * from tablename order by newid() !5wuBJ0  
18、说明:随机选择记录 mY'c<>6t  
select newid() aFbIJm=!  
19、说明:删除重复记录 pA%Sybw+  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) + Cf  
20、说明:列出数据库里所有的表名 lMQ_S"  
select name from sysobjects where type='U' <*Ex6/j  
21、说明:列出表里的所有的 |e%o  
select name from syscolumns where id=object_id('TableName') l>kREfHq!{  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 v/s6!3pnl  
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 i3SrsVSG  
显示结果: {9,!XiF.:  
type vender pcs )-u0n] ,  
电脑 A 1 `pTCK9  
电脑 A 1 9>OPaL n  
光盘 B 2 W ZAkp|R  
光盘 A 2 'g@Yra&09  
手机 B 3 @[=K`n:n_  
手机 C 3 (v@)nv]U  
23、说明:初始化表table1 zK_+UT  
TRUNCATE TABLE table1 82>90e(CH]  
24、说明:选择从10到15的记录 a*D])Lu[  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc `"-ln'nw  
h(>eHP  
p$:ERI  
SKUri  
三、技巧 Il8,g+W]  
1、1=1,1=2的使用,在SQL语句组合时用的较多 $ Ith8p~  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, P@xb  
如: \\D(St  
if @strWhere !='' c@&`!e  
begin ?R MOy$L  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere HT% =o}y  
end nF)XZB 0F  
else *}@zxFe +  
begin nG~#o  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' Rn4Bl8z'>  
end jMAZ4M  
我们可以直接写成 sx]kH$  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere ?nwFc3qw  
2、收缩数据库 [#3*R_#8R  
--重建索引 Rt6(y #dF  
DBCC REINDEX \I[f@D-J  
DBCC INDEXDEFRAG Osk'zFiL<  
--收缩数据和日志 WxrG o o^  
DBCC SHRINKDB g2|qGfl{C  
DBCC SHRINKFILE 5Xr<~xr  
3、压缩数据库 ^DQp9$la  
dbcc shrinkdatabase(dbname) "dItv#<:}  
4、转移数据库给新用户以已存在用户权限 ^{m&2l&87  
exec sp_change_users_login 'update_one','newname','oldname' :,f~cdq=  
go ;dR4a@  
5、检查备份集 ALO0yc  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' })#SjFq<V  
6、修复数据库 iL6Yk @  
ALTER DATABASE [dvbbs] SET SINGLE_USER ,P.yl~'Al  
GO 2x<A7l)6  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK 2|re4  
GO |l#<vw wE  
ALTER DATABASE [dvbbs] SET MULTI_USER n{1;BW#H  
GO |ler\"Eu  
7、日志清除 Ig{ 3>vB  
SET NOCOUNT ON cOz/zD f5  
DECLARE @LogicalFileName sysname, xaw)iC[gI{  
        @MaxMinutes INT, U%?  
        @NewSize INT V0 O6\)/.  
USE    tablename            -- 要操作的数据库名 ~A-vIlGt!  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 {)5tov1  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. )%6h9xyXt  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) \}p!S$`  
-- Setup / initialize [/+}E X  
DECLARE @OriginalSize int Gkr?M^@K  
SELECT @OriginalSize = size m(0c|-  
  FROM sysfiles m.g2>r`NU  
  WHERE name = @LogicalFileName ^OZ*Le  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + # ^q87y  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + :g~X"C1s  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' J4l \  
  FROM sysfiles IJQ" *;  
  WHERE name = @LogicalFileName O+w82!<:  
CREATE TABLE DummyTrans 5 >c,#*  
  (DummyColumn char (8000) not null) W3M1> (  
DECLARE @Counter  INT, 5B)z}g^h  
        @StartTime DATETIME, 3X>x`  
        @TruncLog  VARCHAR(255) ->S# `"@$  
SELECT  @StartTime = GETDATE(), w40 -K5wt>  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' )xxpO$  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) \ y}!yrQ  
EXEC (@TruncLog) _+*+,Vx  
-- Wrap the log if necessary. vP. ^j7wB  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired \&jmSa=]l  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  pj9*$.{  
      AND (@OriginalSize * 8 /1024) > @NewSize  ] i:WP2  
  BEGIN -- Outer loop. DPg\y".4Y&  
    SELECT @Counter = 0 WV?3DzeR  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 0vjlSHS;`.  
      BEGIN -- update .kf FaK  
        INSERT DummyTrans VALUES ('Fill Log')  ~C31=\$  
        DELETE DummyTrans |1/UC"f  
        SELECT @Counter = @Counter + 1 ;%`oS.69  
      END  d #vo)>  
    EXEC (@TruncLog)  RqU^Q*/sF  
  END  ?igA+(.  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + p*5QV  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + P ?A:0a  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' Muay6b?  
  FROM sysfiles : pkOZ+t  
  WHERE name = @LogicalFileName 4 >`2vb  
DROP TABLE DummyTrans /73ANQ"  
SET NOCOUNT OFF C &~s<tcn  
8、说明:更改某个表 hYSzr-)  
exec sp_changeobjectowner 'tablename','dbo' Pu0 <Clh  
9、存储更改全部表 ~zO>Q4-k  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch sBq6,Iu  
@OldOwner as NVARCHAR(128), K*sav?c  
@NewOwner as NVARCHAR(128) 'jA>P\@8  
AS k"$E|$  
DECLARE @Name  as NVARCHAR(128) W&Xm_T[ Q  
DECLARE @Owner  as NVARCHAR(128) GC3WB4iY@U  
DECLARE @OwnerName  as NVARCHAR(128)  SCq:jI  
DECLARE curObject CURSOR FOR }v4T&/vt-  
select 'Name'  = name, I3^}$#>  
  'Owner'  = user_name(uid) <_ruVy0]  
from sysobjects {^*K@c  
where user_name(uid)=@OldOwner j0uu* )Rk  
order by name u5O`|I@R  
OPEN  curObject m/bP`-/,  
FETCH NEXT FROM curObject INTO @Name, @Owner 8_=MP[(H  
WHILE(@@FETCH_STATUS=0) 5i1E 5@~  
BEGIN    nmWo:ox4;(  
if @Owner=@OldOwner w^Atd|~gi  
begin Z!foD^&R  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) n]x%xnt  
  exec sp_changeobjectowner @OwnerName, @NewOwner VXP@)\!  
end "STd ;vR  
-- select @name,@NewOwner,@OldOwner T8W;Lb9hQ  
FETCH NEXT FROM curObject INTO @Name, @Owner E]c0+rh~  
END }l<:^lX  
close curObject ko+fJ&$  
deallocate curObject TMw6 EM  
GO }MIg RQ9  
10、SQL SERVER中直接循环写入数据 X0 ^~`g  
declare @i int EN/r{Cm$B  
set @i=1 pQCocy  
while @i<30 KbGz3O'u  
begin FI8 vABq  
  insert into test (userid) values(@i) /I48jO^2  
  set @i=@i+1 xRbtiFk9H  
end $i|d=D&t  
小记存储过程中经常用到的本周,本月,本年函数 dGG8k&  
Dateadd(wk,datediff(wk,0,getdate()),-1) "L&84^lmf  
Dateadd(wk,datediff(wk,0,getdate()),6) oYAHyCkVq  
Dateadd(mm,datediff(mm,0,getdate()),0) .r~M7 I  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0))  %X* *(  
Dateadd(yy,datediff(yy,0,getdate()),0) |crm{]7X  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 0)]?@"j  
上面的SQL代码只是一个时间段 [79iC$8B|  
Dateadd(wk,datediff(wk,0,getdate()),-1) ;iO5 8S3  
Dateadd(wk,datediff(wk,0,getdate()),6) k*K.ZS688  
就是表示本周时间段. uJSzz:\  
下面的SQL的条件部分,就是查询时间段在本周范围内的: e]*@|e4b  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) U W' @3#<?  
而在存储过程中 %\] x}IC  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) trz &]v=:  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) |a!]Iqz"N  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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