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

[转贴]精妙SQL语句收集

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

SQL语句先前写的时候,很容易把一些特殊的用法忘记,我特此整理了一下SQL语句操作。 )X-~+X91 S  
;yd[QT<I<  
N=4`jy =  
一、基础 !l2=J/LJj  
1、说明:创建数据库 qU!xh )  
CREATE DATABASE database-name }~/u%vI@M5  
2、说明:删除数据库 Wk3R6 V  
drop database dbname (H=7(  
3、说明:备份sql server z +NxO !y  
--- 创建 备份数据的 device 4q%hn3\  
USE master m3o+iYkMD  
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' #Z%?lx"Q0  
--- 开始 备份 M@)^*=0H  
BACKUP DATABASE pubs TO testBack @log=^  
4、说明:创建新表 _Nze="Pt  
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 2oL~N*^C  
根据已有的表创建新表: B^8]quOH  
A:create table tab_new like tab_old (使用旧表创建新表) y9<]F6TT  
B:create table tab_new as select col1,col2... from tab_old definition only d:|(l^]{r  
5、说明:删除新表 UC@ &! kM  
drop table tabname 42 6l:>D(  
6、说明:增加一个列 gZ{q85C.>  
Alter table tabname add column col type fMg3  
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。 sqKLz  
7、说明:添加主键: Alter table tabname add primary key(col) h5@v:4Jjo~  
说明:删除主键: Alter table tabname drop primary key(col) \gtI4zl*J  
8、说明:创建索引:create [unique] index idxname on tabname(col....) Z?XgY\(a(Q  
删除索引:drop index idxname  k2]Q~  
注:索引是不可更改的,想更改必须删除重新建。 3RYg-$NK[  
9、说明:创建视图:create view viewname as select statement o *\c V 6  
删除视图:drop view viewname 'VH%cz*  
10、说明:几个简单的基本的sql语句 mn5mdrv3WZ  
选择:select * from table1 where 范围 [):&R1U  
插入:insert into table1(field1,field2) values(value1,value2) I,rs&m?/m  
删除:delete from table1 where 范围 s]=bg+v?j  
更新:update table1 set field1=value1 where 范围 M mihWD02  
查找:select * from table1 where field1 like '%value1%' ---like的语法很精妙,查资料! X{8/]'(  
排序:select * from table1 order by field1,field2 [desc] a04I.5!  
总数:select count as totalcount from table1 Z{' .fq2A  
求和:select sum(field1) as sumvalue from table1 W.nQYH  
平均:select avg(field1) as avgvalue from table1 NhP&sQO  
最大:select max(field1) as maxvalue from table1 6x6xv:\  
最小:select min(field1) as minvalue from table1 c UJUZ@ol  
6LVJ*sjSy  
a?^xEye  
=aL=SC+  
11、说明:几个高级查询运算词 .W[[Z;D  
l8O12  
,2*^G;J1  
A: UNION 运算符 L\O}q  
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 >9?BJv2  
B: EXCEPT 运算符 y[L7=Td  
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 K/^70;/!.  
C: INTERSECT 运算符 d5b \kRr  
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL),不消除重复行。 4tZnYGvqe  
注:使用运算词的几个查询结果行必须是一致的。 'Cc(}YY0C  
12、说明:使用外连接 K9-?7X  
A、left outer join: u0 & aw  
左外连接(左连接):结果集几包括连接表的匹配行,也包括左连接表的所有行。 r$=YhI/=  
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 V={`k$p  
B:right outer join: Er 4P  
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。 @|7Ma/8v  
C:full outer join: tA,#!Z0  
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。 OfSy_#aEK  
-~wGJM VA  
WKHEU)'!  
二、提升  'Dh+v3O  
1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) N sUFM  
法一:select * into b from a where 1<>1 w-[A"M]I  
法二:select top 0 * into b from a $ N7J:Q  
2、说明:拷贝表(拷贝数据,源表名:a 目标表名:b) (Access可用) rSGt`#E-s.  
insert into b(a, b, c) select d,e,f from b; C^dnkuA  
3、说明:跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用) Gp<7i5  
insert into b(a, b, c) select d,e,f from b in '具体数据库' where 条件 %cPz>PTW@  
例子:..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where.. !i"Z  
4、说明:子查询(表名1:a 表名2:b) hqPpRSv'  
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) )_7OHV *3  
5、说明:显示文章、提交人和最后回复时间 z3 zN^ZT  
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b WJB/X"J  
6、说明:外连接查询(表名1:a 表名2:b) >Ei-Spy>Xl  
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c #7wOr78  
7、说明:在线视图查询(表名1:a ) oH[4<K>  
select * from (SELECT a,b,c FROM a) T where t.a > 1; ig] hY/uT  
8、说明:between的用法,between限制查询数据范围时包括了边界值,not between不包括 jjs1Vj1@<  
select * from table1 where time between time1 and time2 4sj:%% UE  
select a,b,c, from table1 where a not between 数值1 and 数值2 ^CZ)!3qd1  
9、说明:in 的使用方法 M*& tVG   
select * from table1 where a [not] in ('值1','值2','值4','值6') S6J7^'h  
10、说明:两张关联表,删除主表中已经在副表中没有的信息 yUZ;keQ_Tw  
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 ) f]P&>j|  
11、说明:四表联查问题: d8Keyi8[  
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 ..... O{B[iy(C  
12、说明:日程安排提前五分钟提醒 3]*_*<D  
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 3`W=rIMli  
13、说明:一条sql 语句搞定数据库分页 z / YF7wrx  
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段 m/2LwN  
14、说明:前10条记录 EPY64 {  
select top 10 * form table1 where 范围 <G5d{rKZ  
15、说明:选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.) . q=sC?D  
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b) /1h 0 l;  
16、说明:包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表 6" s}<  
(select a from tableA ) except (select a from tableB) except (select a from tableC) zsQhydTR  
17、说明:随机取出10条数据 7DG{|%\HF  
select top 10 * from tablename order by newid() )$h<9e  
18、说明:随机选择记录 A;pVi;7  
select newid() W~TT`%[  
19、说明:删除重复记录 2J^jSgr50d  
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) ;M<jQntqS{  
20、说明:列出数据库里所有的表名 ]A;{D~X^w  
select name from sysobjects where type='U' ("UzMr,  
21、说明:列出表里的所有的 > @Ux8#  
select name from syscolumns where id=object_id('TableName') -ZmccT"8  
22、说明:列示type、vender、pcs字段,以type字段排列,case可以方便地实现多重选择,类似select 中的case。 O{sb{kk  
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 G!y~Y]e  
显示结果: kQr\ktN\  
type vender pcs K):MT[/"  
电脑 A 1 B{:JD^V!  
电脑 A 1 qre.^6x  
光盘 B 2 5<?O S &B  
光盘 A 2 ciq'fy  
手机 B 3 G=[ =[o\  
手机 C 3 b~M3j&  
23、说明:初始化表table1 b r"4 7i  
TRUNCATE TABLE table1 (c{<JYEC  
24、说明:选择从10到15的记录 %E!^SF?Y  
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc tkN5 |95  
~AD%aHR  
F?+K~['i  
3#d5.Ut  
三、技巧 INm21MS$  
1、1=1,1=2的使用,在SQL语句组合时用的较多 ~"<AYJlO  
"where 1=1" 是表示选择全部  "where 1=2"全部不选, pH?tr  
如: {V> >a  
if @strWhere !='' rv(Qz|K@  
begin -^y$RJC  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere YQB.3  
end HzW`j"\  
else  CB<i  
begin YKjm_)8]w  
set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 8=]R6[,fD  
end -SZW[T<N"  
我们可以直接写成 l7{Xy_66  
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere a<Ru)Q?=  
2、收缩数据库 LX4*3c|i,  
--重建索引 I?) .D?o  
DBCC REINDEX C *\ =Q  
DBCC INDEXDEFRAG .?gpI Zv  
--收缩数据和日志 ' (JSU   
DBCC SHRINKDB ;P` z ?>J:  
DBCC SHRINKFILE D6 2xC5  
3、压缩数据库 kW v)+  
dbcc shrinkdatabase(dbname) yq3i=RB(  
4、转移数据库给新用户以已存在用户权限 e}Y|' bG  
exec sp_change_users_login 'update_one','newname','oldname' vm3B>ACJ  
go %fS__Tb#u  
5、检查备份集 MX=mGfoa  
RESTORE VERIFYONLY from disk='E:\dvbbs.bak' |.A#wjF9  
6、修复数据库 qusX]Tst z  
ALTER DATABASE [dvbbs] SET SINGLE_USER 3Mvm'T:[  
GO W9!EjXg  
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK 2#sJ`pdQ  
GO G~oGBq6Gz  
ALTER DATABASE [dvbbs] SET MULTI_USER MroJ!.9  
GO z|VQp,ra  
7、日志清除 ryd*Ha">I  
SET NOCOUNT ON {x3"/sF  
DECLARE @LogicalFileName sysname, ~^U(GAs  
        @MaxMinutes INT, 4g}eqW  
        @NewSize INT D ^ mfWJS  
USE    tablename            -- 要操作的数据库名 QLq^[ >n  
SELECT  @LogicalFileName = 'tablename_log',  -- 日志文件名 w7.I0)MH  
@MaxMinutes = 10,              -- Limit on time allowed to wrap log. __}j {Buk  
        @NewSize = 1                  -- 你想设定的日志文件的大小(M) I8|7~jRB  
-- Setup / initialize Q4gsOx P  
DECLARE @OriginalSize int +?xW%omy  
SELECT @OriginalSize = size R$'0<y8E*]  
  FROM sysfiles gm**9]k^{  
  WHERE name = @LogicalFileName oW:p6d  
SELECT 'Original Size of ' + db_name() + ' LOG is ' + L-7?:  
        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + J#tGQO  
        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' e8HGST`  
  FROM sysfiles *\?t W]8<  
  WHERE name = @LogicalFileName 8pc=Oor2Tv  
CREATE TABLE DummyTrans MGH(= w1  
  (DummyColumn char (8000) not null)  pw^$WK  
DECLARE @Counter  INT, WU:~T.Su  
        @StartTime DATETIME, ;\N{z6  
        @TruncLog  VARCHAR(255) G(LGa2;Zg  
SELECT  @StartTime = GETDATE(), f'hrS}e  
        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' }i32  
DBCC SHRINKFILE (@LogicalFileName, @NewSize) Pt/dH+r`%  
EXEC (@TruncLog) JLS|G?#0  
-- Wrap the log if necessary. 9v=fE2`-  
WHILE    @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 3BBw:)V  
      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  3"ALohlL  
      AND (@OriginalSize * 8 /1024) > @NewSize  /D]?+<h1  
  BEGIN -- Outer loop. _]SV@q^  
    SELECT @Counter = 0 _f9XY  
    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) ZK =`Y@  
      BEGIN -- update 8IErLu}  
        INSERT DummyTrans VALUES ('Fill Log')  b?6-lYE>L  
        DELETE DummyTrans z1LN|+\}  
        SELECT @Counter = @Counter + 1 `lAe2l^  
      END  xPFNH`O&  
    EXEC (@TruncLog)  OH2Xxr[bQ  
  END  =(ULfz[:  
SELECT 'Final Size of ' + db_name() + ' LOG is ' + ]8)nIT^EP  
        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + &5]&6TD6  
        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 0n5{Wr$  
  FROM sysfiles B}Q.Is5  
  WHERE name = @LogicalFileName \>Y2I 4x<  
DROP TABLE DummyTrans Hw 7   
SET NOCOUNT OFF ),9^hJ1+@  
8、说明:更改某个表 9#K,@X5 j  
exec sp_changeobjectowner 'tablename','dbo' w +QXSa_D  
9、存储更改全部表 ^_6.*Mvx  
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch sEpY&6*  
@OldOwner as NVARCHAR(128), Eiqx1ZM  
@NewOwner as NVARCHAR(128) Igowz7  
AS Z`L-UQJ .  
DECLARE @Name  as NVARCHAR(128) huj 6Ysr  
DECLARE @Owner  as NVARCHAR(128) "~ 1:7{k  
DECLARE @OwnerName  as NVARCHAR(128) 'CE3 |x\%K  
DECLARE curObject CURSOR FOR EbEQ@6t  
select 'Name'  = name, "E4;M/  
  'Owner'  = user_name(uid) !j'9>G{T  
from sysobjects > /,7j:X  
where user_name(uid)=@OldOwner C&Nga `J  
order by name 8UH c,np  
OPEN  curObject FsZW,  
FETCH NEXT FROM curObject INTO @Name, @Owner #G'Y 2l  
WHILE(@@FETCH_STATUS=0) _J'V5]=4  
BEGIN    :~K c"Pg  
if @Owner=@OldOwner } 0su[gy[  
begin IYeX\)Gv&  
  set @OwnerName = @OldOwner + '.' + rtrim(@Name) H/qv%!/o  
  exec sp_changeobjectowner @OwnerName, @NewOwner Ne{2fV>8Ay  
end C%hMh/Li;  
-- select @name,@NewOwner,@OldOwner :A+nmz!z  
FETCH NEXT FROM curObject INTO @Name, @Owner HYd&.*41rE  
END 6Fp}U  
close curObject 1C,=1bY  
deallocate curObject 05]y*I  
GO j<H5i}  
10、SQL SERVER中直接循环写入数据 B=E<</i  
declare @i int IM""s]  
set @i=1 P ?- #d\qi  
while @i<30 xq#YBi,  
begin N3J T[7  
  insert into test (userid) values(@i) uB;\nj5'D  
  set @i=@i+1 !Ee#jCXS  
end *V@>E2@  
小记存储过程中经常用到的本周,本月,本年函数 _gAU`aO^  
Dateadd(wk,datediff(wk,0,getdate()),-1) " 3ryp A  
Dateadd(wk,datediff(wk,0,getdate()),6) uVnbOqR<X  
Dateadd(mm,datediff(mm,0,getdate()),0) X~m*`UH  
Dateadd(ms,-3,dateadd(mm,datediff(m,0,getdate())+1,0)) 1y\ -Iz^  
Dateadd(yy,datediff(yy,0,getdate()),0) "pQFIV,  
Dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) ]yc&ffe%  
上面的SQL代码只是一个时间段 |=R@nn   
Dateadd(wk,datediff(wk,0,getdate()),-1) teRK#: .P  
Dateadd(wk,datediff(wk,0,getdate()),6) An cka  
就是表示本周时间段. u"WqI[IV  
下面的SQL的条件部分,就是查询时间段在本周范围内的: "x;|li3;  
Where Time BETWEEN Dateadd(wk,datediff(wk,0,getdate()),-1) AND Dateadd(wk,datediff(wk,0,getdate()),6) 3aD\J_  
而在存储过程中 0l.\KF  
select @begintime = Dateadd(wk,datediff(wk,0,getdate()),-1) XTzz/.T;Z  
select @endtime = Dateadd(wk,datediff(wk,0,getdate()),6) ^0 zWiX  
评价一下你浏览此帖子的感受

精彩

感动

搞笑

开心

愤怒

无聊

灌水

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

描述
快速回复

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